1 Reply Latest reply on Oct 7, 2015 12:07 PM by Srini Chavali-Oracle

    Alert Notification send numerous emails

    1631850

      I am creating an Alert that will notify supervisors of staff members who are due for probation report. I am using Operating System Script(Unix) as source.I have already developed the script.

       

      Now the challenge is if there are 3 employee records then the alert will send 3 different emails with each employee record. Now I want to add a condition that will iterate through records and send only one email listing all employee records.


      See below the SQL statement for the alert:

      select papf.employee_number

      , papf.full_name

      , papf.start_date date_joint

      , pps.ADJUSTED_SVC_DATE

      , paaf.date_probation_end

      -- , papfs.employee_number

      , papfs.full_name supervisor

      , papfs.email_address supervisor_email_address

      , paaf.probation_period

      , haou.name

      'test@gmail.com' mail

       

      into

      &employee_number

      , &full_name

      , &date_joint

      , &adj_serv_date

      , &prob_end_date

      , &supervisor

      , &supervisor_email_address

      , &probation_period,

      , &department

      ,&tt

      from apps.per_all_assignments_f paaf,

      apps.per_all_people_f papf,

      apps.hr_all_organization_units haou,

      apps. per_all_people_f papfs,

      apps.per_periods_of_service pps

      where paaf.person_id = papf.person_id

      --and papf.employee_number = '720040049'

      and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

      and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date

      and trunc(sysdate) between papfs.effective_start_date and papfs.effective_end_date

      and haou.organization_id = paaf.organization_id

      and papfs.person_id = paaf.supervisor_id

      and pps.period_of_service_id = paaf.period_of_service_id

      and paaf.date_probation_end is not null

      and ( months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') )= 9 -- trunc(sysdate)+7

      or

      months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') ) = 6

      or

      months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') ) = 3

      or

      months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') )= 0)

      and probation_period = 12

      and paaf.primary_flag = 'Y'

       

      then action type is as follows:

       

      Action Type : Operating Sytem Script

       

      Text : if [[ -n "&employee_number" ]]

               then

               echo "Employee Number: &employee_number"

               fi|mutt -s "Attachment" -a /u01/ProgressReport.doc &tt

       

       

       

      Your assistance will be highly appreciated.

       

      Thank you