CWB Reusable Query
There comes scenario when you want to know the number of employee successfully enrolled in the plan after running CWB participation process. While running the CWB participation process either we give person selection rule, benefit groups or leave the field blank but we need to know the employees who have been successfully enrolled. The following query will give you all those details along with their supervisor name, organization, etc.
P.S. in the last line please do not forget to change the date to the effective date you set while running your participation process. These dates should match to select correct employees enrolled with the plan.
SELECT DISTINCT papf.employee_number "Employee Number",
papf.full_name "Employee Name",
(SELECT NAME FROM apps.per_all_assignments_f paaf, apps.hr_all_organization_units haou
WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND paaf.organization_id = haou.organization_id
AND paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y') "Organization Name",
(SELECT papf1.full_NAME FROM apps.per_all_assignments_f paaf, apps.per_all_people_f papf1
WHERE TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papf1.effective_start_date AND papf1.effective_end_date
AND paaf.supervisor_id = papf1.person_id
AND paaf.person_id = papf.person_id
AND paaf.primary_flag = 'Y') "Supervisor Name"
FROM apps.per_all_people_f papf
WHERE TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id IN
(SELECT person_id FROM apps.ben_cwb_person_rates WHERE lf_evt_ocrd_dt = TO_DATE('00-00-0000','DD-MM-YYYY')) -- Change your date here.
