If we have a requirement to get Checklist status for Employees, below SQL can be leveraged.
Click Here for Customer Connect Discussion for this post
SELECT DISTINCT
PAPF.PERSON_NUMBER,
ACT.CHECKLIST_NAME,
ATT.TASK_NAME,
CASE
WHEN AC.CHECKLIST_STATUS = 'COM' THEN 'Completed'
WHEN AC.CHECKLIST_STATUS = 'INI' THEN 'Pending'
ELSE ' '
END AS CHECKLIST_STATUS,
TO_CHAR(AC.ALLOCATION_DATE, 'mm/dd/yyyy'),
TO_CHAR(AC.COMPLETION_DATE, 'mm/dd/yyyy'),
AC.CHECKLIST_CATEGORY,
AC.CHECKLIST_ID,
AC.ALLOCATED_CHECKLIST_ID,
AC.INITIATOR_PERSON_ID
from PER_ALLOCATED_CHECKLISTS AC
,PER_ALLOCATED_CHECKLISTS_TL ACT
,PER_ALLOCATED_TASKS AT
,PER_ALLOCATED_TASKS_TL ATT
,PER_ASSIGNMENT_SECURED_LIST_V AV
--,PER_ALL_ASSIGNMENTS_M AV
,PER_ALL_PEOPLE_F PAPF
where 1=1
AND AC.ALLOCATED_CHECKLIST_ID = ACT.ALLOCATED_CHECKLIST_ID
AND AT.ALLOCATED_CHECKLIST_ID = AC.ALLOCATED_CHECKLIST_ID
AND ATT.ALLOCATED_TASK_ID = AT.ALLOCATED_TASK_ID
AND AC.PERSON_ID = AV.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN AV.EFFECTIVE_START_DATE AND AV.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND AV.assignment_status_type IN ( 'ACTIVE', 'SUSPENDED' )
AND AV.effective_latest_change = 'Y'
AND AV.assignment_type IN ( 'E', 'C', 'N', 'O', 'P' )
--AND ATT.TASK_NAME = :Task_Name
-- Additional filter
AND ACT.CHECKLIST_NAME= :Checklist_Name
AND PAPF.PERSON_ID = AV.PERSON_ID
ORDER BY PAPF.PERSON_NUMBER