Requisition Pending Approval for multiple Approves SQL Report in Oracle Fusion ORC.

 If you have any queries, please reach out to sathwikhcm@gmail.com

YouTube Link: https://www.youtube.com/watch?v=wusdFrOw3RE 

 

WITH RAW_DATA AS (
SELECT DISTINCT
IRV.REQUISITION_NUMBER,
IRV.TITLE,
WFS.ENDDATE,
WFS.STARTDATE,
WFS.ASSIGNEE
FROM
FA_FUSION_SOAINFRA.WFTASK WFT,
FA_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC WFS,
IRC_REQUISITIONS_VL IRV,
IRC_PHASES_VL IPV,
IRC_STATES_VL ISV

WHERE
WFT.IDENTIFICATIONKEY = TO_CHAR(IRV.REQUISITION_ID)
AND IRV.CURRENT_PHASE_ID = IPV.PHASE_ID
AND IRV.CURRENT_STATE_ID = ISV.STATE_ID
AND WFT.TASKID = WFS.TASKID
AND IPV.NAME = 'Approval'
AND ISV.NAME = 'Pending'
AND (WFS.ENDACTION IN ('OUTCOME_UPDATE','COMPLETE')
OR WFS.ENDACTION IS NULL)
--AND IRV.REQUISITION_NUMBER = '10175'
),

ROWNUM_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
ASSIGNEE,
STARTDATE,
ENDDATE,
(ROW_NUMBER() OVER (PARTITION BY REQUISITION_NUMBER ORDER BY STARTDATE)) ROW_NUM1
FROM
RAW_DATA
),

MAIN_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
MIN(CASE WHEN ROW_NUM1=1 THEN ASSIGNEE END) ASSIGNEE1,
MIN(CASE WHEN ROW_NUM1=1 THEN STARTDATE END) STARTDATE1,
MIN(CASE WHEN ROW_NUM1=1 THEN ENDDATE END) ENDDATE1,
MIN(CASE WHEN ROW_NUM1=2 THEN ASSIGNEE END) ASSIGNEE2,
MIN(CASE WHEN ROW_NUM1=2 THEN STARTDATE END) STARTDATE2,
MIN(CASE WHEN ROW_NUM1=2 THEN ENDDATE END) ENDDATE2
FROM
ROWNUM_TABLE

GROUP BY REQUISITION_NUMBER,
TITLE
)

SELECT DISTINCT IRV.REQUISITION_NUMBER,
IRV.TITLE REQ_TITLE,
HAP.NAME POSITION_NAME,
HAP.POSITION_CODE,
HAOU_LE.NAME LE_NAME,
HAOU_BU.NAME BU_NAME,
HAOU_DEP.NAME DEP_NAME,
HLA.LOCATION_NAME,
PJ.NAME JOB_NAME,
PG.NAME GRADE_NAME,
MT.ASSIGNEE1 APPROVER1,
MT.STARTDATE1 ASSIGNED1DATE,
MT.ENDDATE1 APPROVED1DATE,
(CASE WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NULL THEN 'Pending'
WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NOT NULL THEN 'Completed' END) APPROVER1STATUS,
MT.ASSIGNEE2 APPROVER2,
MT.STARTDATE2 ASSIGNED2DATE,
MT.ENDDATE2 APPROVED2DATE,
(CASE WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NULL THEN 'Pending'
WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NOT NULL THEN 'Completed' END) APPROVER2STATUS

FROM
MAIN_TABLE MT,
HR_ALL_POSITIONS HAP,
HR_LOCATIONS_ALL HLA,
HR_ALL_ORGANIZATION_UNITS HAOU_DEP,
HR_ALL_ORGANIZATION_UNITS HAOU_BU,
HR_ALL_ORGANIZATION_UNITS HAOU_LE,
PER_JOBS PJ,
PER_GRADES PG,
IRC_REQUISITIONS_VL IRV

WHERE
IRV.REQUISITION_NUMBER = MT.REQUISITION_NUMBER
AND IRV.POSITION_ID = HAP.POSITION_ID
AND HAP.LOCATION_ID = HLA.LOCATION_ID(+)
AND HAP.ORGANIZATION_ID = HAOU_DEP.ORGANIZATION_ID(+)
AND HAP.BUSINESS_UNIT_ID = HAOU_BU.ORGANIZATION_ID(+)
AND IRV.LEGAL_EMPLOYER_ID = HAOU_LE.ORGANIZATION_ID(+)
AND HAP.JOB_ID = PJ.JOB_ID(+)
AND HAP.ENTRY_GRADE_ID = PG.GRADE_ID(+)

Employee Goals approved and total goals submitted status Report in Oracle Fusion HCM.

Select Distinct
HG.Person_ID
,papf.Person_Number
,ppnf.Display_Name
,TO_CHAR(HG.TARGET_COMPLETION_DATE,'DD-MM-YYYY') AS "TARGET_COMPLETION_DATE"
,TO_CHAR(HG.Start_Date,'DD-MM-YYYY') AS "GOAL_START_DATE"
--,HG.CATEGORY_CODE
,HG.GOAL_TYPE_CODE
--,HG.GOAL_NAME
,(select
    count(e.GOAL_PLAN_NAME)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE='BC_BGI'
and a.STATUS_CODE <>'CANCEL'
and e.GOAL_PLAN_NAME='Goals'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "BGoals"


,(select
    count(e.GOAL_PLAN_ID)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE='BC_WOG'
and e.GOAL_PLAN_NAME='WOGoals'
and a.STATUS_CODE <>'CANCEL'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "WOGoal"

,(select
    count(e.GOAL_PLAN_ID)
from
    HRG_GOALS a
    ,HRG_GOAL_PLANS_VL b
    ,HRT_REVIEW_PERIODS_TL  c
    ,HRG_GOAL_PLAN_GOALS  d
    ,HRG_GOAL_PLANS_TL e
where a.person_id=hg.person_id
--and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
and a.STATUS_CODE <>'CANCEL'
AND a.GOAL_TYPE_CODE='PERFORMANCE'
AND a.GOAL_ID = d.GOAL_ID
AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'
and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) as     "Total Number of Goals"
,(case  when (select
                  count(e.GOAL_PLAN_ID)
                from
                    HRG_GOALS a
                    ,HRG_GOAL_PLANS_VL b
                    ,HRT_REVIEW_PERIODS_TL  c
                    ,HRG_GOAL_PLAN_GOALS  d
                    ,HRG_GOAL_PLANS_TL e
                where a.person_id=hg.person_id
               -- and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
                and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
                and a.STATUS_CODE <>'CANCEL'
                AND a.GOAL_TYPE_CODE='PERFORMANCE'
                AND a.GOAL_ID = d.GOAL_ID
                AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
                AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
                AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
                AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
                AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) >= 4
              then 'Minimum Goals Added'    
        when (select
                   count(e.GOAL_PLAN_ID)
               from
                   HRG_GOALS a
                   ,HRG_GOAL_PLANS_VL b
                   ,HRT_REVIEW_PERIODS_TL  c
                   ,HRG_GOAL_PLAN_GOALS  d
                   ,HRG_GOAL_PLANS_TL e
               where a.person_id=hg.person_id
               and e.GOAL_PLAN_NAME in ('WOGoals','BGoals')
              -- and a.CATEGORY_CODE  in ('BC_WOG','BC_BGI')
               and a.STATUS_CODE <>'CANCEL'
               AND a.GOAL_TYPE_CODE='PERFORMANCE'
               AND a.GOAL_ID = d.GOAL_ID
               AND e.GOAL_PLAN_ID = d.GOAL_PLAN_ID
               AND c.REVIEW_PERIOD_ID = b.REVIEW_PERIOD_ID
               AND d.GOAL_PLAN_ID = b.GOAL_PLAN_ID
               AND b.GOAL_PLAN_ID = e.GOAL_PLAN_ID
               AND GOAL_VERSION_TYPE_CODE = 'ACTIVE'  and (c.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))) <4
        then 'No Minimum Added'
end) as "Final_Status"
,'Approved' Status
,(
    Select PAPF_MANAGER.PERSON_NUMBER
    From PER_PERSON_NAMES_F PPNF_MANAGER,
    PER_ASSIGNMENT_SUPERVISORS_F PASF,
    PER_ALL_PEOPLE_F PAPF_MANAGER,
    PER_ASSIGNMENT_SECURED_LIST_V PAAM_MANAGER
    Where PAPF.PERSON_ID=PASF.PERSON_ID
    AND PASF.MANAGER_TYPE = 'LINE_MANAGER'
    AND PAPF_MANAGER.PERSON_ID=PASF.MANAGER_ID
    AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE AND PASF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN PAPF_MANAGER.EFFECTIVE_START_DATE AND PAPF_MANAGER.EFFECTIVE_END_DATE
    AND PAPF_MANAGER.PERSON_ID =PPNF_MANAGER.PERSON_ID
    AND PPNF_MANAGER.NAME_TYPE='GLOBAL'
    AND PAAM.ASSIGNMENT_ID=PASF.ASSIGNMENT_ID
    AND TRUNC(SYSDATE) BETWEEN PPNF_MANAGER.EFFECTIVE_START_DATE AND PPNF_MANAGER.EFFECTIVE_END_DATE
    and PAAM_MANAGER.PERSON_ID=PAPF_MANAGER.PERSON_ID
    AND PAAM_MANAGER.PRIMARY_FLAG='Y'
    AND PAAM_MANAGER.ASSIGNMENT_STATUS_TYPE='ACTIVE'
    AND PAAM_MANAGER.PRIMARY_ASSIGNMENT_FLAG='Y'
    AND TRUNC(SYSDATE) BETWEEN PAAM_MANAGER.EFFECTIVE_START_DATE AND PAAM_MANAGER.EFFECTIVE_END_DATE
) "Reporting Manager Number"

,TO_CHAR(ppos.Date_Start,'yyyy/mm/dd') hiredate
,HAOU.NAME Department
,ppnf.FIRST_NAME
,ppnf.LAST_NAME
,paam.ASSIGNMENT_NUMBER
,PJ.NAME JOB_TITLE
,pgft.NAME   Grade
,PAAM.ASS_ATTRIBUTE1 "Plan Eligility"
,fabuv.BU_NAME
,HRPL.REVIEW_PERIOD_NAME
From
 HRG_GOALS HG
,PER_ALL_PEOPLE_F papf
,PER_PERSON_NAMES_F ppnf
,PER_ALL_ASSIGNMENTS_M paam
,PER_PERIODS_OF_SERVICE ppos
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ
,PER_GRADES_F_TL pgft
,Fun_all_business_units_v fabuv
,HRG_GOAL_PLANS_VL HGVL
,HRT_REVIEW_PERIODS_TL HRPL
,HRG_GOAL_PLAN_GOALS HGPG
,HRG_GOAL_PLANS_TL HGPT
Where
    HG.PERSON_ID                 = papf.PERSON_ID
AND HG.GOAL_VERSION_TYPE_CODE = 'ACTIVE'
AND    papf.PERSON_ID               = ppnf.PERSON_ID
AND ppnf.NAME_TYPE               = 'GLOBAL'
AND papf.PERSON_ID               = paam.PERSON_ID
AND paam.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND paam.EFFECTIVE_LATEST_CHANGE = 'Y'
AND paam.ASSIGNMENT_TYPE         = 'E'
AND paam.ASSIGNMENT_STATUS_TYPE  = 'ACTIVE'
AND paam.assignment_id           = hg.assignment_id
--AND HG.CATEGORY_CODE in ('BGI','WOG')
AND HG.GOAL_TYPE_CODE='PERFORMANCE'
AND papf.PERSON_ID = ppos.PERSON_ID(+)
AND paam.PERIOD_OF_SERVICE_ID = ppos.PERIOD_OF_SERVICE_ID(+)
AND ppos.PRIMARY_FLAG = 'Y'
AND paam.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN HAOU.EFFECTIVE_START_DATE(+) AND HAOU.EFFECTIVE_END_DATE(+)
AND PAAM.JOB_ID=PJ.JOB_ID(+)
and trunc(sysdate) between PJ.EFFECTIVE_START_DATE(+) and PJ.EFFECTIVE_END_DATE(+)
and paam.GRADE_ID = pgft.GRADE_ID(+)
AND TRUNC(SYSDATE) BETWEEN pgft.EFFECTIVE_START_DATE AND pgft.EFFECTIVE_END_DATE
and fabuv.BU_ID(+) = paam.BUSINESS_UNIT_ID
and fabuv.STATUS='A'
AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ppnf.EFFECTIVE_START_DATE AND ppnf.EFFECTIVE_END_DATE    
AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE
and (papf.PERSON_NUMBER in (:p_Person_Number) or 'ALL' in (:p_Person_Number || 'ALL'))
and HG.STATUS_CODE <>'CANCEL'
   AND HG.GOAL_ID = HGPG.GOAL_ID
   AND HGPT.GOAL_PLAN_ID = HGPG.GOAL_PLAN_ID
   AND HRPL.REVIEW_PERIOD_ID = HGVL.REVIEW_PERIOD_ID
   AND HGPG.GOAL_PLAN_ID = HGVL.GOAL_PLAN_ID
   AND HGVL.GOAL_PLAN_ID = HGPT.GOAL_PLAN_ID
   and to_char(hg.TARGET_COMPLETION_DATE(+),'yyyy/mm/dd')='2025/03/31'
   and (HRPL.REVIEW_PERIOD_NAME in (:p_REVIEW_PERIOD) or 'ALL' in (:p_REVIEW_PERIOD || 'ALL'))

SQL query will help the decide which employees to hire tomorrow in Oracle Fusion Cloud HCM

 The following SQL query will help the organization decide which employees to hire tomorrow. It will also prepare a report and scheduled as per businesses requirement in Oracle Fusion Cloud HCM

 

SELECT DISTINCT
        PAPF.PERSON_NUMBER as "EmployeeNumber",
         TO_CHAR (PAAM.PROJECTED_START_DATE,'DD-Mon-YYYY', 'nls_date_language=American') as "HireDate",
        PAAM.PROJECTED_START_DATE,
        PPNF.FIRST_NAME as "FirstName",
        PPNF.LAST_NAME as "LastName",
        PPNF.DISPLAY_NAME,
        PPNF.FIRST_NAME || (CASE WHEN PPNF.MIDDLE_NAMES IS NOT NULL THEN ' '||PPNF.MIDDLE_NAMES END) || ' '||PPNF.LAST_NAME as "FullName",
        PLDFT.LOCATION_NAME as "LocationName",
        PAPF_MGR.PERSON_NUMBER as "ManagerEmpNo",
        PPNF_MGR.DISPLAY_NAME as "ManagerName",
        HAPFT_MGR.NAME as "ManagerPostion",
        PJFT_MGR.NAME as "ManagerJob",
        PEA_MGR_W.EMAIL_ADDRESS "ManagerEmail",
        HL_WORK_CAT.MEANING as "WorkerCategory",
        HL_EMP_CAT.MEANING as "EmployeeCategory",
        (SELECT PEA.EMAIL_ADDRESS,
                   FROM PER_EMAIL_ADDRESSES PEA
                   WHERE EMAIL_TYPE ='W1'
                   AND PEA.PERSON_ID =PAPF.PERSON_ID AND Rownum=1 ) as "Work_Email"
FROM
        PER_ALL_ASSIGNMENTS_M PAAM,
        PER_PERIODS_OF_SERVICE PPOS,
        PER_ALL_PEOPLE_F PAPF,
        PER_PERSON_NAMES_F PPNF,
        --------------Location starts------------------
        PER_LOCATION_DETAILS_F PLDF,
        PER_LOCATION_DETAILS_F_TL PLDFT,
        -------------Manager starts--------------------
        PER_ASSIGNMENT_SUPERVISORS_F PASF,
        PER_ALL_PEOPLE_F PAPF_MGR,
        PER_PERSON_NAMES_F PPNF_MGR,
        PER_ALL_ASSIGNMENTS_M PAAM_MGR,
        HR_ALL_POSITIONS_F_TL HAPFT_MGR,
        PER_JOBS_F_TL PJFT_MGR,
        -----------------Manager emails--------
        PER_EMAIL_ADDRESSES PEA_MGR_W,
        ------------work category lookup--------
        HCM_LOOKUPS HL_WORK_CAT,
        ------------Employee category lookup--------
        HCM_LOOKUPS HL_EMP_CAT,
        ------------Phone category lookup--------
        PER_PHONES PP
WHERE    1=1
        AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
        AND PAAM.ASSIGNMENT_TYPE IN ('P')
        AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PAPF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PPNF.PERSON_ID
        AND PPNF.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
        -------------location starts-------------------
        AND PAAM.LOCATION_ID = PLDF.LOCATION_ID(+)
        AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
        AND PLDFT.LANGUAGE(+) = USERENV('LANG')
        AND TRUNC(SYSDATE) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)
        ----------------Manager starts-----------------
        AND PAAM.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID(+)
        AND PASF.MANAGER_TYPE(+) = 'LINE_MANAGER'
        AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)
        AND PASF.MANAGER_ID = PAPF_MGR.PERSON_ID(+)
        AND PASF.MANAGER_ASSIGNMENT_ID = PAAM_MGR.ASSIGNMENT_ID(+)
        AND PAAM_MGR.ASSIGNMENT_TYPE(+) = 'E'
        AND PAAM_MGR.EFFECTIVE_LATEST_CHANGE(+) = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM_MGR.EFFECTIVE_START_DATE(+) AND PAAM_MGR.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PAPF_MGR.EFFECTIVE_START_DATE(+) AND PAPF_MGR.EFFECTIVE_END_DATE(+)
        AND PAPF_MGR.PERSON_ID = PPNF_MGR.PERSON_ID(+)
        AND PPNF_MGR.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF_MGR.EFFECTIVE_START_DATE(+) AND PPNF_MGR.EFFECTIVE_END_DATE(+)
        AND PAAM_MGR.POSITION_ID = HAPFT_MGR.POSITION_ID(+)
        AND TRUNC(SYSDATE) BETWEEN HAPFT_MGR.EFFECTIVE_START_DATE(+) AND HAPFT_MGR.EFFECTIVE_END_DATE(+)
        AND HAPFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND PAAM_MGR.JOB_ID = PJFT_MGR.JOB_ID(+)
        AND PJFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND TRUNC(SYSDATE) BETWEEN PJFT_MGR.EFFECTIVE_START_DATE(+) AND PJFT_MGR.EFFECTIVE_END_DATE(+)
        -----------------Manager emails --------
        AND PAAM_MGR.PERSON_ID = PEA_MGR_W.PERSON_ID(+)
        AND PEA_MGR_W.EMAIL_TYPE(+) = 'W1'
        ------------work category lookup starts--------
        AND HL_WORK_CAT.LOOKUP_TYPE(+) = 'EMPLOYEE_CATG'
        AND HL_WORK_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYEE_CATEGORY
        ------------work category lookup ends----------
        AND HL_EMP_CAT.LOOKUP_TYPE(+) = 'EMP_CAT'
        AND HL_EMP_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYMENT_CATEGORY
        AND PP.PERSON_ID(+) = PAAM.PERSON_ID
        AND PP.PHONE_TYPE(+) = 'HM'
        AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE+1)
        ORDER BY PAAM.PROJECTED_START_DATE

Task Expired for Checklist(Journeys) using SQL Query in Oracle Cloud HCM

The following SQL query will help with the task 'Expired for Checklist(Journeys)'  with Checklist completed for employees in Oracle Cloud HCM.

 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') as "ALLOCATION_DATE_",
    To_char(AC.completion_date, 'mm/dd/yyyy') as "COMPLETION_DATE",
    AC.checklist_category,
    AC.checklist_id,
    AC.allocated_checklist_id,
    AC.initiator_person_id,
    AT.STATUS,
    AV1.DISPLAY_NAME,
    PE.EMAIL_ADDRESS
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_PERSON_NAMES_F AV1,
    per_all_people_f PAPF,
    PER_EMAIL_ADDRESSES PE,
    PER_ALL_ASSIGNMENTS_M PAAM
WHERE 1 = 1
    AND AT.STATUS <> 'COM'
    AND AT.STATUS <> 'DEP'
    AND AT.STATUS <> 'INI'
    AND AT.STATUS <> 'INP'
    AND PAAM.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
    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 AV.person_id = AV1.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 ACT.checklist_name = 'ChecklistName'
    AND PAPF.person_id = AV.person_id
    AND PAPF.person_id = PE.person_id
    AND PE.EMAIL_TYPE = 'W1'
ORDER BY PAPF.person_number