This query selects the PERSON_NUMBER from PER_ALL_PEOPLE_F for employees who have an allocated checklist with the category ‘ONBOARD’. It joins checklist and task tables to ensure related onboarding tasks exist and filters records based on the current effective date. The result is limited to one record.
SELECT PAPFF3.PERSON_NUMBER
FROM
PER_ALLOCATED_CHECKLISTS_VL PACVL1,PER_ALLOCATED_TASKS_VL PATVVL2,Per_ALL_PEOPLE_F PAPFF3
WHERE
PACVL1.CHECKLIST_CATEGORY = 'ONBOARD'
AND PAPFF3.PERSON_ID = PACVL1.PERSON_ID
AND PACVL1.ALLOCATED_CHECKLIST_ID = PATVVL2.ALLOCATED_CHECKLIST_ID
--AND PATVVL2.TASK_NAME = 'Put your Cost Allocation Checklist Name'
--AND PACVL1.ALLOCATED_CHECKLIST_ID = :{PARAMETER.ALLOCATED_CHECKLIST_ID}
AND TRUNC(SYSDATE) BETWEEN PAPFF3.EFFECTIVE_START_DATE AND PAPFF3.EFFECTIVE_END_DATE
AND rownum = 1 Output Results
Related Posts
SQL to pull Position and Parent Position
Posted on
SQL to Fetch the Bank Account Details
Posted on