SQL Query to Fetch Absence Accrual Entry Details in Oracle Fusion HCM
Learn how to fetch absence accrual entry details in Oracle Fusion HCM using SQL queries. Complete guide with examples for tracking leave accruals and balances.
Managing employee leave balances is important, but understanding how those balances are built over time is where real insights lie. In Oracle Fusion HCM, absence accruals are calculated based on plan configuration, employee eligibility, and transactional events like accruals, adjustments, and carryovers.
This comprehensive guide shares a powerful SQL query that extracts detailed absence accrual entry data, including transaction-level breakdown, adjustment reasons, and processed dates—giving you complete transparency into leave balance calculations.
What Is Absence Accrual in Oracle Fusion?
Absence accrual refers to the process of accumulating leave balances over time based on predefined rules. Examples include:
✅ Monthly accrual - E.g., 1.5 days per month ✅ Front-loaded accrual - E.g., 18 days at the start of the year ✅ Prorated accrual - For new joiners or employees with variable service periods ✅ Adjusted accruals - Manual adjustments for corrections or special circumstances
These accruals are calculated using plan rules, formulas, and employee service periods, making it essential to understand how they accumulate.
Why This Query Is Critical
Standard HR reports often show only final leave balances. But this query helps you go much deeper by showing:
✅ Detailed accrual transactions - Each accrual entry separately ✅ Adjustment entries and reasons - Why changes were made ✅ Processed dates - When accruals were processed ✅ Balance types - Accrual vs. Adjustment vs. Carryover ✅ Enrollment period details - Plan enrollment timeline ✅ Transaction visibility - Complete audit trail of balance changes
This level of detail is essential for:
- Payroll auditing and validation
- HR reporting and analytics
- Absence management troubleshooting
- Compliance and audit trails
- Employee leave balance queries
Complete Production-Ready SQL Query
/******************************************************************************
REPORT ID : GCS_REPORT_11
REPORT NAME : ABSENCE ACCRUAL ENTRY DETAILS REPORT
REPORT TYPE : BIP REPORT
DESCRIPTION : THIS REPORT FETCHES THE ABSENCE ACCRUAL ENTRY DETAILS
FROM ORACLE FUSION.
CHANGE HISTORY:
NAME DATE VERSION COMMENTS
GrowCloudSkills 21-MAR-2026 V1.0 INITIAL VERSION
******************************************************************************/
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 to your absence plan name */
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
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;
Query Customization Guide
1. Change the Absence Plan Name
Modify this line to match your absence plan:
AND AAPFT.NAME = 'Sick Leave' /* Change to your absence plan name */
Examples:
AND AAPFT.NAME = 'Casual Leave'
AND AAPFT.NAME = 'Annual Leave'
AND AAPFT.NAME = 'Unpaid Leave'
2. Fetch Data for Specific Employees
Add this condition:
AND PAPF.PERSON_NUMBER IN ('1001', '1002', '1003')
Or for a single employee:
AND PAPF.PERSON_NUMBER = '1001'
3. Filter by Date Range
Add date filtering to optimize performance:
AND TRUNC(AEDTLS.PROCD_DATE) BETWEEN TO_DATE('01-01-2026', 'DD-MM-YYYY')
AND TO_DATE('31-12-2026', 'DD-MM-YYYY')
4. Include All Absence Plans
Remove this line to get data for all plans:
AND AAPFT.NAME = 'Sick Leave'
Understanding the Key Tables
| Table Name | Purpose |
|---|---|
| PER_ALL_PEOPLE_F | Employee master data |
| PER_ALL_ASSIGNMENTS_F | Employee assignment details |
| ANC_ABSENCE_PLANS_F | Absence plan definitions |
| ANC_PER_ACCRUAL_ENTRIES | Core accrual transactions |
| ANC_PER_ACRL_ENTRY_DTLS | Detailed accrual entry lines |
| ANC_PER_PLAN_ENROLLMENT | Employee enrollment in plans |
| FND_COMMON_LOOKUPS | Lookup values for meanings |
Sample Output Explanation
| Column | Meaning |
|---|---|
| PERSON_NUMBER | Employee ID |
| EMPLOYEE_NAME | Full employee name |
| PLAN_NAME | Name of absence plan (Sick Leave, Annual Leave) |
| PROCESSED_DATE | Date when accrual was processed |
| BALANCE_TYPE_MEANING | Type of transaction (Accrual, Adjustment, Carryover) |
| DETAILED_ACCRUAL_BALANCE | Number of hours/days in this transaction |
| ADJUSTMENT_REASON_MEANING | Reason if this is an adjustment |
| ENROLLMENT_START_DATE | When employee enrolled in this plan |
Real-World Business Use Cases
This query is essential for:
✅ Payroll Reconciliation - Validate leave balances before payroll processing ✅ Audit Reports - Track all accrual changes and adjustments ✅ Employee Queries - Answer “Why is my balance X?” questions ✅ Compliance - Maintain complete audit trail of leave accruals ✅ Analytics - Build dashboards showing accrual trends ✅ Issue Debugging - Identify incorrect accruals quickly ✅ Data Migration - Validate data when moving to new systems
Performance Optimization Tips
For large Oracle Fusion environments with millions of records:
✅ Always use date filters - Critical for performance ✅ Filter by specific employees - Reduce unnecessary joins ✅ Test in non-production first - Validate before prod runs ✅ Create indexes on PERSON_ID, PLAN_ID if needed ✅ Monitor query execution time - Optimize joins if needed ✅ Use bind variables - Improves query plan caching
FAQ: Common Questions
Which Oracle Fusion modules does this use?
This query uses:
- Oracle Fusion Absence Management - Core module
- Oracle Fusion Core HR - For employee data
- Oracle Fusion Payroll - For leave integration
Can this be used in BI Publisher reports?
Yes, absolutely! This query is suitable for:
- BI Publisher Data Models
- OTBI custom SQL
- HCM Extract validations
- Custom BIP reports
What if I need historical accruals?
Remove effective date filters to see all versions (including inactive). This shows accrual history.
How do I include multiple absence plans?
Modify the WHERE clause:
AND AAPFT.NAME IN ('Sick Leave', 'Annual Leave', 'Casual Leave')
Conclusion
Understanding how absence accruals are calculated and tracked in Oracle Fusion is essential for:
- HR professionals managing leave balances
- Payroll teams validating leave before payroll
- Technical consultants building reports
- System administrators troubleshooting issues
This production-ready SQL query gives you complete transparency into your absence accrual data. Whether you’re auditing balances, building reports, or troubleshooting employee issues, you now have a powerful tool at your disposal.
Master this query and you’ll significantly improve your ability to manage, audit, and report on absence data in Oracle Fusion HCM!
💡 Master Absence Management in Oracle Fusion
Learning to work with absence accrual data effectively enables you to:
- Build accurate absence reports
- Troubleshoot leave balance issues
- Create compliant HR processes
- Support payroll operations
- Improve employee self-service
🚀 Continue Your Learning Journey
- Subscribe to GrowCloudSkills for more SQL queries and absence management guides
- Follow us on LinkedIn for daily Oracle Fusion tips and best practices
- Watch our video tutorials on YouTube for visual step-by-step learning
About GrowCloudSkills
GrowCloudSkills is your trusted partner for mastering Oracle Fusion Cloud Applications through:
✅ Production-ready SQL queries you can use immediately in your environment ✅ Real-world implementation examples based on actual consulting projects ✅ Complete step-by-step guides for complex technical and functional topics ✅ Industry best practices based on years of Oracle Fusion consulting experience ✅ Supportive community of Oracle Fusion professionals
Whether you’re just starting your Oracle Fusion journey or you’re an experienced architect designing enterprise solutions, we’re here to help you succeed.
Connect With Us
Have questions about this query or other Oracle Fusion topics? Drop a comment below or reach out on LinkedIn. We’d love to help you on your Oracle Fusion journey!
Happy querying and master absence management in Oracle Fusion! 🚀