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:
, papf.start_date date_joint
-- , papfs.employee_number
, papfs.full_name supervisor
, papfs.email_address supervisor_email_address
from apps.per_all_assignments_f paaf,
apps. per_all_people_f papfs,
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
months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') ) = 6
months_between ( date_probation_end, to_date('30-SEP-2015','DD/MM/YYYY') ) = 3
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" ]]
echo "Employee Number: &employee_number"
fi|mutt -s "Attachment" -a /u01/ProgressReport.doc &tt
Your assistance will be highly appreciated.