To fetch the assignment details as eff date and the accrual balance.

 /*Report Name: Annual Balance Healthy Check V2 DATE: 30-05-2020 

Created BY: Partha */

select rownum,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME,

PLE.NAME LE_NAME,

PJ.NAME JOB,

PGF.NAME GRADE,

HAP.NAME POSITION,

HTL.NAME DEPARTMENT,

PAAF.assignment_number,

TO_CHAR(PPOS.DATE_START,'DD-MM-YYYY') AS "HIRE DATE",

TO_CHAR(PPOS.ACTUAL_TERMINATION_DATE,'DD-MM-YYYY') AS "Termination DATE",

HVL.LOCATION_NAME LOCATION,

---------------------------CURRENT_BALANCE----------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

--AAPV.NAME 

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE,

   ANC_ABSENCE_PLANS_VL  AAPV 

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

  /*(SELECT PAPF1.PERSON_ID FROM PER_ALL_PEOPLE_F PAPF1 

WHERE TRUNC(:eff_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE

AND PAPF1.PERSON_NUMBER = :P_PERSON_NUMBER)

*/

AND APAE.PL_ID =AAPV.ABSENCE_PLAN_ID(+)

                   -- AND APAE.PROCD_DATE = NVL(:eff_date,SYSDATE)

   AND APAE.PROCD_DATE <= :eff_date

   AND UPPER(AAPV.NAME) ='ANNUAL LEAVE'

   AND AAPV.PLAN_STATUS = 'A'

--AND (:eff_date)BETWEEN APAE.PROCD_DATE AND APAE.PROCD_DATE

),

                 2),

           0)  CURRENT_BALANCE,

 

----------------------------DURATION-----------------------

   /*

   NVL((select sum(ABE.DURATION) taken_leave

from ANC_PER_ABS_ENTRIES ABE

where ABE.PERSON_ID = PAPF.PERSON_ID

AND ABE.ABSENCE_TYPE_ID IN ('300000112735590','300000112735532')

AND ABE.ABSENCE_STATUS_CD = 'SUBMITTED'

AND ABE.START_DATE <= :eff_date

--AND TRUNC(:eff_date) BETWEEN ABE.START_DATE AND ABE.END_DATE

),

0) DURATION,

*/

------------------------CONSUMED_DAYS-----------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

                    AND APAE.TYPE = 'ABS'

--and APAE.pl_id = 300000112735584

                    AND APAE.PROCD_DATE <= :eff_date 

),

                 2),

           0) *(-1) CONSUMED_DAYS,

/*

(SELECT SUM(APAS.used) 

FROM ANC_PER_ACCRUAL_ENTRIES APAS

WHERE APAS.PERSON_ID = PAPF.PERSON_ID

AND ACCRUAL_PERIOD <= :eff_date

--AND APAS.PLAN_ID = '300000112735584'

) CONSUMED_DAYS,

*/

------------------------------SYSTEM_ACCRUAL-------------------------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

--AAPV.NAME 

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE,

   ANC_ABSENCE_PLANS_VL  AAPV 

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

  /*(SELECT PAPF1.PERSON_ID FROM PER_ALL_PEOPLE_F PAPF1 

WHERE TRUNC(:eff_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE

AND PAPF1.PERSON_NUMBER = :P_PERSON_NUMBER)

*/

AND APAE.PL_ID =AAPV.ABSENCE_PLAN_ID(+)

                   -- AND APAE.PROCD_DATE = NVL(:eff_date,SYSDATE)

   AND APAE.PROCD_DATE <= :eff_date

   AND UPPER(AAPV.NAME) ='ANNUAL LEAVE'

   AND AAPV.PLAN_STATUS = 'A'

--AND (:eff_date)BETWEEN APAE.PROCD_DATE AND APAE.PROCD_DATE

),

                 2),

           0)

   

   +

   nvl(round((SELECT SUM(APAE.VALUE) BAL

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

                    AND APAE.TYPE = 'ABS'

--and APAE.pl_id = 300000112735584

                    AND APAE.PROCD_DATE <= :eff_date 

),

                 2),

           0) *(-1) SYSTEM_ACCRUAL

from

PER_ALL_PEOPLE_F PAPF,

    PER_PERSON_NAMES_F PPNF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_GRADES_F_VL PGF,

PER_JOBS_F_TL PJ,

HR_ALL_POSITIONS_F_TL HAP,

HR_ORGANIZATION_UNITS_F_TL HTL,

PER_PERIODS_OF_SERVICE PPOS,

PER_LEGAL_EMPLOYERS PLE,

HR_LOCATIONS_ALL_F_VL HVL

WHERE

PAPF.PERSON_ID = PPNF.PERSON_ID

AND PAAF.PERSON_ID = PAPF.PERSON_ID

AND PAAF.PERSON_ID = PPNF.PERSON_ID

AND PJ.JOB_ID = PAAF.JOB_ID

AND PGF.GRADE_ID = PAAF.GRADE_ID

AND HAP.POSITION_ID = PAAF.POSITION_ID

AND HTL.ORGANIZATION_ID = PAAF.ORGANIZATION_ID

AND PAAF.PERSON_ID = PPOS.PERSON_ID

AND PAAF.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID

AND PPOS.ACTUAL_TERMINATION_DATE IS NULL

AND HVL.LOCATION_ID = PAAF.LOCATION_ID

AND PAAF.ASSIGNMENT_NUMBER = nvl(:Per_num,PAAF.ASSIGNMENT_NUMBER)

---------------------------------DATE CONDITION----------------------

AND TRUNC(:eff_date) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN HAP.EFFECTIVE_START_DATE AND HAP.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN HTL.EFFECTIVE_START_DATE AND HTL.EFFECTIVE_END_DATE

AND TRUNC (:eff_date) BETWEEN PLE.EFFECTIVE_START_DATE AND PLE.EFFECTIVE_END_DATE

AND TRUNC (:eff_date) BETWEEN HVL.EFFECTIVE_START_DATE AND HVL.EFFECTIVE_END_DATE

--AND TRUNC (SYSDATE) BETWEEN APTL.EFFECTIVE_START_DATE AND APTL.EFFECTIVE_END_DATE

--------------------------------LANGUAGE--------------------------------

AND PPNF.NAME_TYPE = 'GLOBAL'

AND PJ.LANGUAGE = 'US'

AND HAP.LANGUAGE = 'US'

AND HTL.LANGUAGE = 'US'

--AND APTL.LANGUAGE = 'US'

-- AND PAPF.PERSON_NUMBER = nvl(:Per_num,PAPF.PERSON_NUMBER)

--AND HVL.LANGUAGE = 'US'

order by rownum desc