SQL Query for Absence Plan Configuration Details in Oracle Fusion HCM
Comprehensive SQL query to fetch absence plan configuration details in Oracle Fusion HCM. Extract accrual methods, formulas, eligibility profiles, and more.
If you’re working with Oracle Fusion HCM Absence Management, you already know how complex it can be to track and understand absence plan configurations directly from the application. An absence plan defines how employee leaves are accrued, managed, and controlled—covering everything from entitlement rules to carry-forward limits and eligibility criteria.
However, accessing all these details from the UI can be time-consuming and limited. That’s where this comprehensive SQL query for absence plan configuration details becomes extremely valuable.
This guide walks you through a production-ready SQL query that extracts complete absence plan configuration data from Oracle Fusion backend tables in a single report.
Why This Query Is Critical
Tracking absence plan configurations manually is inefficient. This query helps you extract complete absence plan details from Oracle Fusion HCM, enabling you to:
✅ Get a complete overview of all absence plans in your system ✅ Analyze accrual and entitlement setup for each plan ✅ Identify Fast Formula usage for custom business logic ✅ Validate eligibility profiles linked to plans ✅ Review carry-forward and ceiling limits for compliance ✅ Audit configuration changes over time ✅ Build BI Publisher reports for management dashboards
This makes it an essential tool for both functional consultants and technical developers working on Oracle Fusion HCM implementations and ongoing support.
Real-World Business Use Cases
This absence plan configuration query is highly useful in practical scenarios:
✅ Oracle Fusion HCM Implementations - Understanding as-is configurations ✅ Absence Plan Configuration Audits - Reviewing setup completeness ✅ Troubleshooting Leave Balance Issues - Validating plan setup ✅ Data Validation During Migration - Ensuring accurate data transfer ✅ Client Requirement Analysis - Understanding current state vs. desired ✅ BI Publisher Reporting - Building management dashboards ✅ Compliance Documentation - Maintaining audit trails
Complete Production-Ready SQL Query
/******************************************************************************
REPORT ID : GCS_REPORT_012
REPORT NAME : ABSENCE PLAN CONFIGURATION DETAILS REPORT
REPORT TYPE : BIP REPORT
DESCRIPTION : THIS REPORT FETCHES THE ABSENCE PLAN CONFIGURATION
DETAILS FROM ORACLE FUSION.
CHANGE HISTORY:
NAME DATE VERSION COMMENTS
GrowCloudSKills 19-MAR-2026 V1.0 INITIAL VERSION
******************************************************************************/
SELECT
ABVL.NAME AS ABSENCE_PLAN_NAME
,DECODE(ABVL.PLAN_PERIOD_TYPE, 'C', 'Calendar', 'RB', 'Rolling Backward',
ABVL.PLAN_PERIOD_TYPE) AS PLAN_PERIOD_TYPE
,DECODE(ABVL.PLAN_UOM, 'H', 'Hours', ABVL.PLAN_UOM)
AS PLAN_UNIT_OF_MEASURE
,DECODE(ABVL.ACC_DEFINITION_TYPE, 'M', 'Matrix', 'F', 'Formula',
ABVL.ACC_DEFINITION_TYPE) AS ACCRUAL_DEFINITION_TYPE
,AATV.NAME AS ABSENCE_TYPE_NAME
,DECODE(ABVL.ENTL_METHOD_CD, 'A', 'Accrual', 'N', 'No Entitlement',
'Q', 'Qualification', ABVL.ENTL_METHOD_CD) AS ENTITLEMENT_METHOD
,ABVL.CARRY_OVER_FLAT_AMT AS CARRY_OVER_FLAT_AMOUNT
,ABVL.CEIL_LIMIT_FLAT_AMT AS CEILING_LIMIT_FLAT_AMOUNT
,ABVL.LEGISLATION_CODE AS LEGISLATION_CODE
,ABVL.PAY_RATE_FACTOR AS PAY_RATE_FACTOR
,DECODE(ABVL.OTHER_ADJUSTMENT_FLAG, 'Y', 'Yes', '')
AS ADJUSTMENT_ENABLED_FLAG
,DECODE(ABVL.ENROLLMENT_START_RULE, 'F', 'Formula', 'E', 'As Of Event',
'ASD', 'Absence Start Date', ABVL.ENROLLMENT_START_RULE)
AS ENROLLMENT_START_RULE
,DECODE(ABVL.ENTL_DEFINITION_TYPE, 'M', 'Matrix', 'F', 'Formula',
ABVL.ENTL_DEFINITION_TYPE) AS ENTITLEMENT_TYPE
,DECODE(AAPF.ACCRUAL_METHOD_CD, 'F', 'Front-Loaded', 'I', 'Incremental',
AAPF.ACCRUAL_METHOD_CD) AS ACCRUAL_METHOD
,TO_CHAR(ABVL.EFFECTIVE_START_DATE, 'DD-MM-YYYY',
'NLS_DATE_LANGUAGE=AMERICAN') AS EFFECTIVE_START_DATE
,TO_CHAR(ABVL.EFFECTIVE_END_DATE, 'DD-MM-YYYY',
'NLS_DATE_LANGUAGE=AMERICAN') AS EFFECTIVE_END_DATE
,FFV.FORMULA_NAME AS ENROLLMENT_FORMULA_NAME
,FFTV.FORMULA_TYPE_NAME AS ENROLLMENT_FORMULA_TYPE
,FFV1.FORMULA_NAME AS CARRY_OVER_FORMULA_NAME
,FFTV1.FORMULA_TYPE_NAME AS CARRY_OVER_FORMULA_TYPE
,FFV2.FORMULA_NAME AS PARTIAL_ACCRUAL_FORMULA_NAME
,FFTV2.FORMULA_TYPE_NAME AS PARTIAL_ACCRUAL_FORMULA_TYPE
,FFV3.FORMULA_NAME AS ENROLL_END_DATE_FORMULA_NAME
,FFTV3.FORMULA_TYPE_NAME AS ENROLL_END_DATE_FORMULA_TYPE
,FFV4.FORMULA_NAME AS ENROLL_START_DATE_FORMULA_NAME
,FFTV4.FORMULA_TYPE_NAME AS ENROLL_START_DATE_FORMULA_TYPE
,(
SELECT BEP.NAME
FROM ANC_PLAN_ELIG_PROFILES_F ELIG
,BEN_ELIGY_PRFL BEP
WHERE 1 = 1
AND ELIG.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
AND BEP.ELIGY_PRFL_ID = ELIG.BEN_ELIG_PROFILE_ID
AND TRUNC(SYSDATE) BETWEEN ELIG.EFFECTIVE_START_DATE
AND ELIG.EFFECTIVE_END_DATE
) AS ELIGIBLE_PROFILE_NAME
,(
SELECT BEP.PROFILE_TYPE
FROM ANC_PLAN_ELIG_PROFILES_F ELIG
,BEN_ELIGY_PRFL BEP
WHERE 1 = 1
AND ELIG.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
AND BEP.ELIGY_PRFL_ID = ELIG.BEN_ELIG_PROFILE_ID
AND TRUNC(SYSDATE) BETWEEN ELIG.EFFECTIVE_START_DATE
AND ELIG.EFFECTIVE_END_DATE
) AS ELIGIBLE_PROFILE_TYPE
FROM ANC_ABSENCE_PLANS_VL ABVL
,ANC_ABSENCE_TYPE_PLANS_F ATPF
,ANC_ABSENCE_TYPES_VL AATV
,FF_FORMULAS_VL FFV
,FF_FORMULAS_VL FFV1
,FF_FORMULAS_VL FFV2
,FF_FORMULAS_VL FFV3
,FF_FORMULAS_VL FFV4
,FF_FORMULA_TYPES_VL FFTV
,FF_FORMULA_TYPES_VL FFTV1
,FF_FORMULA_TYPES_VL FFTV2
,FF_FORMULA_TYPES_VL FFTV3
,FF_FORMULA_TYPES_VL FFTV4
,ANC_ABSENCE_PLANS_F AAPF
WHERE 1=1
AND ABVL.ABSENCE_PLAN_ID = ATPF.ABSENCE_PLAN_ID
AND AATV.ABSENCE_TYPE_ID = ATPF.ABSENCE_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN ABVL.EFFECTIVE_START_DATE AND ABVL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ATPF.EFFECTIVE_START_DATE AND ATPF.EFFECTIVE_END_DATE
AND ABVL.PLAN_STATUS = 'A'
AND FFV.FORMULA_ID(+) = ABVL.ENROLLMENT_START_FORMULA_ID
AND FFV.FORMULA_TYPE_ID = FFTV.FORMULA_TYPE_ID(+)
AND FFV1.FORMULA_TYPE_ID = FFTV1.FORMULA_TYPE_ID(+)
AND FFV1.FORMULA_ID(+) = ABVL.CARRY_OVER_FORMULA_ID
AND FFV2.FORMULA_TYPE_ID = FFTV2.FORMULA_TYPE_ID(+)
AND FFV2.FORMULA_ID(+) = AAPF.PARTIAL_ACCRUAL_FORMULA_ID
AND ATPF.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
AND FFV3.FORMULA_TYPE_ID = FFTV3.FORMULA_TYPE_ID(+)
AND FFV3.FORMULA_ID(+) = ABVL.ENROLLMENT_END_FORMULA_ID
AND FFV4.FORMULA_TYPE_ID = FFTV4.FORMULA_TYPE_ID(+)
AND FFV4.FORMULA_ID(+) = ABVL.ENROLLMENT_START_FORMULA_ID
AND TRUNC(SYSDATE) BETWEEN FFV.EFFECTIVE_START_DATE(+)
AND FFV.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN FFV1.EFFECTIVE_START_DATE(+)
AND FFV1.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN FFV2.EFFECTIVE_START_DATE(+)
AND FFV2.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN FFV3.EFFECTIVE_START_DATE(+)
AND FFV3.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN FFV4.EFFECTIVE_START_DATE(+)
AND FFV4.EFFECTIVE_END_DATE(+)
ORDER BY AATV.NAME;
Query Output Explained
This query returns detailed configuration information for each absence plan:
| Column | Description |
|---|---|
| ABSENCE_PLAN_NAME | Name of the absence plan (e.g., Sick Leave, Annual Leave) |
| PLAN_PERIOD_TYPE | Calendar vs. Rolling backward period |
| PLAN_UNIT_OF_MEASURE | Hours or days |
| ACCRUAL_DEFINITION_TYPE | Matrix or Formula based |
| ACCRUAL_METHOD | Front-loaded or incremental |
| ENTITLEMENT_METHOD | How leaves are entitled (Accrual, No Entitlement, Qualification) |
| CARRY_OVER_FLAT_AMOUNT | Maximum carry-forward limit |
| CEILING_LIMIT_FLAT_AMOUNT | Maximum balance ceiling |
| FORMULA_NAMES | Names of Fast Formulas used |
| ELIGIBLE_PROFILE_NAME | Eligibility profile linked to the plan |
Customizing the Query
1. Fetch Specific Absence Plans
Add this condition to filter by plan name:
AND ABVL.NAME = 'Sick Leave'
Or for multiple plans:
AND ABVL.NAME IN ('Sick Leave', 'Annual Leave', 'Casual Leave')
2. Include Inactive Plans
Remove this condition:
AND ABVL.PLAN_STATUS = 'A'
This will include archived/inactive plans.
3. Filter by Legislation
Add this condition for a specific country/legislation:
AND ABVL.LEGISLATION_CODE = 'IN'
4. Include Effective Date Range
Add date filters for historical data:
AND TRUNC(ABVL.EFFECTIVE_START_DATE) >= TO_DATE('01-01-2020', 'DD-MM-YYYY')
Key Tables Used
Understanding the tables helps with modifications:
| Table | Purpose |
|---|---|
| ANC_ABSENCE_PLANS_VL | Absence plan master (translatable) |
| ANC_ABSENCE_PLANS_F | Absence plan fact table with accrual methods |
| ANC_ABSENCE_TYPES_VL | Absence type definitions |
| FF_FORMULAS_VL | Fast Formulas referenced by plans |
| ANC_PLAN_ELIG_PROFILES_F | Eligibility profiles linked to plans |
| BEN_ELIGY_PRFL | Eligibility profile master |
Why This Query Uses Outer Joins
This query uses outer joins (+) for formula columns because:
- Not all plans use formulas - Some use matrix-based accruals
- Not all formula fields are populated - Plans may use only some formulas
- Outer joins prevent missing records - Plans without formulas still appear in results
This ensures you get complete data even if some columns are NULL.
Performance Optimization Tips
For large Oracle Fusion environments:
✅ Use bind parameters - For plan names or legislation codes ✅ Filter by effective dates - Avoid historical data if not needed ✅ Create indexes on commonly filtered columns ✅ Test in non-production first - Validate before production use ✅ Monitor query execution time - Optimize if needed ✅ Run during off-peak hours - Minimize impact on users
Real-World Application Examples
Example 1: Audit Preparation
Get all absence plans and their configuration to audit against requirements documentation.
Example 2: BI Publisher Report
Use this query as the data model for a management dashboard showing plan details.
Example 3: Migration Validation
Compare absence plans between source and target systems post-migration.
Example 4: Eligibility Analysis
Identify which employees are eligible for which plans based on profile links.
FAQ: Common Questions
Can I use this in BI Publisher?
Yes, absolutely! This query is perfectly suited for:
- BI Publisher Data Models
- OTBI custom SQL reports
- HCM Extract validations
- Custom BIP reports
What if I need employee-level enrollment?
You’ll need to join to the ANC_PER_PLAN_ENROLLMENT table. See our other queries for employee enrollment details.
How do I export this to Excel?
Use SQL*Plus, SQL Developer, or your BI tool’s export feature. Results are standard Oracle result sets.
What if formulas don’t exist?
The outer joins handle this gracefully—formula names will show as NULL if not applicable to a plan.
Conclusion
Understanding absence plan configurations in Oracle Fusion is critical for:
- Functional consultants implementing absence management
- Technical developers building custom reports
- HR teams managing leave policies
- System administrators supporting the system
- Audit and compliance teams documenting configurations
This production-ready SQL query provides complete transparency into your absence plan setup. Whether you’re designing new plans, troubleshooting issues, or building reports, you now have a powerful tool to extract and analyze configuration data.
Master this query and you’ll significantly improve your ability to manage, audit, and support absence management in Oracle Fusion HCM!
💡 Master Absence Management Configuration
Learning to query absence plan configurations helps you:
- Build accurate, compliant leave policies
- Troubleshoot balance calculation issues
- Design BI Publisher reports
- Support payroll integration
- Audit configurations for compliance
🚀 Continue Learning
- Subscribe to GrowCloudSkills for more advanced SQL queries and absence management guides
- Follow us on LinkedIn for daily Oracle Fusion tips, best practices, and implementation insights
- 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 ✅ Real-world implementation examples from actual consulting projects ✅ Complete step-by-step guides for complex technical and functional topics ✅ Industry best practices based on years of Oracle Fusion consulting ✅ Supportive community of Oracle Fusion professionals
Whether you’re just beginning 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 succeed!
Happy querying and master absence plan configuration in Oracle Fusion! 🚀