SELECT PPSLV.PERSON_NUMBER,
PPNF.FULL_NAME PERSON_NAME,
AAPFT.NAME PLAN,
FLVT.MEANING ACCRUAL_TYPE,
ACC_DTLS.VALUE ACCRUAL_VALUE,
ACC_ENTRY.END_BAL ACCRUAL_BALANCE
FROM PER_PERSON_SECURED_LIST_V PPSLV,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_PERSON_NAMES_F PPNF,
PER_PERIODS_OF_SERVICE PPOS,
PER_PERSON_TYPES_TL PPTL,
PER_LEGAL_EMPLOYERS PLE,
ANC_ABSENCE_PLANS_F_TL AAPFT,
ANC_ABSENCE_PLANS_F AAPF,
ANC_PER_ACCRUAL_ENTRIES ACC_ENTRY,
ANC_PER_ACRL_ENTRY_DTLS ACC_DTLS,
FND_LOOKUP_VALUES_TL FLVT
WHERE PAAM.PERSON_ID= PPSLV.PERSON_ID
AND PPOS.PERSON_ID = PPSLV.PERSON_ID
AND PPNF.PERSON_ID= PPSLV.PERSON_ID
AND ACC_ENTRY.PERSON_ID = PPSLV.PERSON_ID
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND ACC_ENTRY.PRD_OF_SVC_ID= PAAM.PERIOD_OF_SERVICE_ID
AND PAAM.PERSON_TYPE_ID = PPTL.PERSON_TYPE_ID
AND PAAM.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID
AND AAPFT.ABSENCE_PLAN_ID= AAPF.ABSENCE_PLAN_ID
AND ACC_ENTRY.PLAN_ID = AAPF.ABSENCE_PLAN_ID
AND ACC_ENTRY.PER_ACCRUAL_ENTRY_ID=ACC_DTLS.PER_ACCRUAL_ENTRY_ID
AND ACC_ENTRY.ACCRUAL_PERIOD =
(SELECT MAX (ACC_ENTRY1.ACCRUAL_PERIOD) FROM ANC_PER_ACCRUAL_ENTRIES ACC_ENTRY1 WHERE ACC_ENTRY1.ACCRUAL_PERIOD <=
TO_DATE (SUBSTR (TRUNC (SYSDATE), 1, 10))
AND ACC_ENTRY1.PERSON_ID= ACC_ENTRY.PERSON_ID
AND ACC_ENTRY1.PRD_OF_SVC_ID = ACC_ENTRY.PRD_OF_SVC_ID AND ACC_ENTRY1.PLAN_ID = ACC_ENTRY.PLAN_ID)
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE ='E'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PLE.STATUS = 'A'
AND PPTL.LANGUAGE = USERENV ('LANG')
AND AAPF.PLAN_STATUS = 'A'
AND AAPFT.LANGUAGE = 'US'
AND FLVT.LOOKUP_TYPE = 'ANC_ACCRUAL_ENTRY_TYPE'
AND FLVT.LOOKUP_CODE = ACC_DTLS.TYPE
AND FLVT.LANGUAGE = 'US'
AND TRUNC (SYSDATE) BETWEEN PPSLV.EFFECTIVE_START_DATE AND PPSLV.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PLE.EFFECTIVE_START_DATE AND PLE.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN AAPF.EFFECTIVE_START_DATE AND AAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN AAPFT.EFFECTIVE_START_DATE AND AAPFT.EFFECTIVE_END_DATE
