Reusable Queries

CWB query to get employee successfully enrolled in the plan

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.

Few other reusable queries if you are interested in

Show More

Related Articles


Adblock Detected

Please consider supporting us by disabling your ad blocker