/******************************************************************************
REPORT ID   : GCS_REPORT_11
REPORT NAME : ABSENCE ACCRUAL ENTRY DETAILS REPORT
REPORT TYPE : BIP REPORT
DESCRIPTION : THIS REPORT WILL FETCH THE ABSENCE ACCRUAL ENTRY DETAILS FROM ORACLE FUSION.
CHANGE HISTORY:
NAME                  DATE           VERSION          COMMENTS
*********************************************************************************
VAIBHAV CHAVAN     21-MAR-2026		  V1.0            INITIAL VERSION   
********************************************************************************/
/* THIS CODE IS WRITTEN FOR WWW.GROWCLOUDSKILLS.COM  */
SELECT 
     PAPF.PERSON_NUMBER AS PERSON_NUMBER
	,PPNF.DISPLAY_NAME AS EMPLOYEE_NAME
	,ASG.ASSIGNMENT_NAME AS ASSIGNMENT_NAME
	,AAPFT.NAME AS PLAN_NAME
	,AAPF.PLAN_UOM AS PLAN_UOM
	,APAE.END_BAL AS ACCRUAL_BALANCE
	,TO_CHAR(AEDTLS.PROCD_DATE, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') AS PROCESSED_DATE
	,AEDTLS.TYPE AS BALANCE_TYPE_CODE
	,(
		SELECT FCL.MEANING
		FROM FND_COMMON_LOOKUPS FCL
		WHERE FCL.LOOKUP_TYPE = 'ANC_ACCRUAL_ENTRY_TYPE'
			AND FCL.ENABLED_FLAG = 'Y'
			AND FCL.LOOKUP_CODE = AEDTLS.TYPE
		) AS BALANCE_TYPE_MEANING
    ,AEDTLS.VALUE AS DETAILED_ACCRUAL_BALANCE
	,AEDTLS.ADJUSTMENT_REASON AS ADJUSTMENT_REASON_CODE
	,(
		SELECT FCL.MEANING
		FROM FND_COMMON_LOOKUPS FCL
		WHERE FCL.LOOKUP_TYPE = 'ANC_ABS_PLAN_OTHER_REASONS'
			AND FCL.ENABLED_FLAG = 'Y'
			AND FCL.LOOKUP_CODE = AEDTLS.ADJUSTMENT_REASON
		) AS ADJUSTMENT_REASON_MEANING
	,CASE 
		WHEN AEDTLS.STATUS = 'A'
			THEN 'ACTIVE'
		ELSE ''
		END AS STATUS
	,TO_CHAR(APPE.ENRT_ST_DT, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') AS ENROLLMENT_START_DATE
	,TO_CHAR(APPE.ENRT_END_DT, 'DD-MM-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') AS ENROLLMENT_END_DATE
	
FROM PER_ALL_PEOPLE_F PAPF
	,PER_ALL_ASSIGNMENTS_F ASG
	,PER_PERIODS_OF_SERVICE PPOS
	,PER_PERSON_TYPES_TL PPTL
	,ANC_ABSENCE_PLANS_F_TL AAPFT
	,ANC_ABSENCE_PLANS_F AAPF
	,ANC_PER_ACCRUAL_ENTRIES APAE
	,ANC_PER_ACRL_ENTRY_DTLS AEDTLS
	,PER_PERSON_NAMES_F PPNF
	,ANC_PER_PLAN_ENROLLMENT APPE
	
WHERE ASG.PERSON_ID = PAPF.PERSON_ID
	AND PPOS.PERSON_ID = PAPF.PERSON_ID
	AND APAE.PERSON_ID = PAPF.PERSON_ID
	AND ASG.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
	AND APAE.PRD_OF_SVC_ID = ASG.PERIOD_OF_SERVICE_ID
	AND ASG.PERSON_TYPE_ID = PPTL.PERSON_TYPE_ID
	AND AAPFT.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
	AND APAE.PLAN_ID = AAPF.ABSENCE_PLAN_ID
	AND ASG.PRIMARY_FLAG = 'Y'
	AND ASG.ASSIGNMENT_TYPE = 'E'
	AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
	AND PPTL.LANGUAGE = USERENV('LANG')
	AND AAPF.PLAN_STATUS = 'A'
	AND AAPFT.LANGUAGE = 'US'
	
	AND AAPFT.NAME = 'Sick Leave' /* change the abs plan*/
	
	AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN AAPF.EFFECTIVE_START_DATE AND AAPF.EFFECTIVE_END_DATE
	AND SYSDATE BETWEEN AAPFT.EFFECTIVE_START_DATE AND AAPFT.EFFECTIVE_END_DATE
	AND APAE.PER_ACCRUAL_ENTRY_ID = AEDTLS.PER_ACCRUAL_ENTRY_ID
	AND AEDTLS.PL_ID = APAE.PLAN_ID
	AND AEDTLS.VALUE <> 0
	
	AND APAE.PERSON_ID = PPNF.PERSON_ID
	AND PPNF.NAME_TYPE = 'GLOBAL'
	AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
		
	AND APAE.PLAN_ID = APPE.PLAN_ID
	AND PAPF.PERSON_ID = APPE.PERSON_ID
	AND APAE.PERSON_EVENT_ID = APPE.PER_EVENT_ID
	
    /* THIS CODE IS WRITTEN FOR WWW.GROWCLOUDSKILLS.COM  */	
	AND APPE.ENRT_ST_DT = (
		SELECT MAX(APPE1.ENRT_ST_DT)
		FROM ANC_PER_PLAN_ENROLLMENT APPE1
			,PER_ALL_PEOPLE_F PAPF2
			,ANC_ABSENCE_PLANS_F_TL AAPFT2
		WHERE 1 = 1
			AND PAPF2.PERSON_ID = APPE1.PERSON_ID
			AND PAPF2.PERSON_ID = PAPF.PERSON_ID
			AND AAPFT2.ABSENCE_PLAN_ID = APPE1.PLAN_ID
			AND AAPFT2.NAME = AAPFT.NAME
			AND TRUNC(SYSDATE) BETWEEN PAPF2.EFFECTIVE_START_DATE AND PAPF2.EFFECTIVE_END_DATE
			AND TRUNC(SYSDATE) BETWEEN AAPFT2.EFFECTIVE_START_DATE AND AAPFT2.EFFECTIVE_END_DATE
		)
    AND APAE.ACCRUAL_PERIOD <= (
			SELECT NVL(MAX(APAE1.ACCRUAL_PERIOD),'')
			FROM
			  ANC_PER_ACCRUAL_ENTRIES APAE1
			, PER_ALL_PEOPLE_F PAPF1
			, ANC_ABSENCE_PLANS_F_TL AAPFT1
			WHERE 1=1
			AND PAPF1.PERSON_ID = APAE1.PERSON_ID
			AND PAPF1.PERSON_ID = PAPF.PERSON_ID
			AND AAPFT1.ABSENCE_PLAN_ID = APAE1.PLAN_ID
			AND AAPFT1.NAME = AAPFT.NAME
			AND APAE1.FIRST_LAST_PRD_IN_PL_TERM <> 'FIRSTLAST'
			AND TRUNC(SYSDATE) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
			AND TRUNC(SYSDATE) BETWEEN AAPFT1.EFFECTIVE_START_DATE AND AAPFT1.EFFECTIVE_END_DATE
       )		
ORDER BY 
     PAPF.PERSON_NUMBER
    ,AEDTLS.PROCD_DATE
	
  /*-------------- CODE ENDS HERE ----------------------*/
/* For more such queries, visit www.growcloudskills.com */