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

 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'))