HCM HR/Payroll Approval Reminder Emails

Content

My task is to send out emails to assignees that they have pending HR/Payroll approvals on Wednesday and Friday mornings. I wrote a data model containing this data:

SELECT DISTINCT txnh.module_identifier Process_Category,
                wft.creator Requestor_ID,
                wft.assignees Current_Assignee,
                decode(NVL(SUBSTR(wft.assignees, 0, INSTR(wft.assignees, ',')-1), wft.assignees),
                    'HRIS_APPROVER_DATA', 'HRIS@Sample.com',
                    'Compensation_Manager', 'Compensation@Sample.com',
                    (Select pea.email_address
                    From per_all_people_f papf,
                         per_email_addresses pea
                    Where 1=1
                    and sysdate between papf.effective_start_date and papf.effective_end_date
                    and pea.person_id(+)=papf.person_id