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