Oracle Absence Management Tables & Configuration Guide

Oracle Fusion HCM's Absence Management module provides comprehensive leave and time-off tracking capabilities. Understanding the underlying database structure is essential for HR analysts, developers, and consultants who need to create custom reports, integrate with external systems, or troubleshoot absence-related issues.

This comprehensive guide covers the core absence management tables, their relationships, configuration patterns, and practical SQL examples for common absence tracking scenarios. From simple vacation balance queries to complex accrual calculations, you'll learn how to leverage Oracle's absence data model effectively.

Absence Management Data Model Overview

Oracle's Absence Management follows a hierarchical data model that supports complex leave policies, accrual rules, and approval workflows. The core concept revolves around absence plans that define leave types, entitlements that grant employees access to leave, and absence records that track actual time off.

This model supports various absence scenarios including vacation, sick leave, personal time, sabbaticals, and complex regional leave types mandated by local legislation.

Core Absence Tables

ANC_ABSENCE_PLANS - Absence Plan Configuration

The ANC_ABSENCE_PLANS table defines leave policies and their associated rules. This is where absence types, accrual methods, carryover rules, and plan parameters are configured.

Sample Query: Active Absence Plans

-- Get all active absence plans with their configuration
SELECT 
    ap.PLAN_NAME,
    ap.ABSENCE_TYPE_CODE,
    ap.PLAN_UOM,
    ap.ACCRUAL_METHOD,
    ap.CARRYOVER_LIMIT,
    CASE WHEN ap.NEGATIVE_BALANCE_ALLOWED = 'Y' 
         THEN 'Yes' ELSE 'No' END as ALLOWS_NEGATIVE_BALANCE,
    CASE WHEN ap.APPROVAL_REQUIRED = 'Y' 
         THEN 'Yes' ELSE 'No' END as REQUIRES_APPROVAL,
    ap.EFFECTIVE_START_DATE,
    ap.EFFECTIVE_END_DATE
FROM ANC_ABSENCE_PLANS ap
WHERE TRUNC(SYSDATE) BETWEEN ap.EFFECTIVE_START_DATE 
                          AND NVL(ap.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))
ORDER BY ap.ABSENCE_TYPE_CODE, ap.PLAN_NAME;

ANC_PER_ABSENCE_ENTITLEMENTS - Employee Entitlements

This table links employees to absence plans and defines their specific entitlements, including annual allocations, start dates, and plan-specific parameters.

Sample Query: Employee Entitlements Summary

-- Employee entitlements with plan details
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PLAN_NAME,
    ap.ABSENCE_TYPE_CODE,
    e.ANNUAL_ENTITLEMENT,
    ap.PLAN_UOM,
    e.START_DATE,
    e.END_DATE,
    CASE WHEN e.END_DATE IS NULL OR e.END_DATE > TRUNC(SYSDATE) 
         THEN 'Active' ELSE 'Expired' END as ENTITLEMENT_STATUS
FROM ANC_PER_ABSENCE_ENTITLEMENTS e
JOIN PER_ALL_PEOPLE_F p 
  ON e.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
JOIN ANC_ABSENCE_PLANS ap 
  ON e.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
  AND TRUNC(SYSDATE) BETWEEN ap.EFFECTIVE_START_DATE 
                          AND NVL(ap.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND (e.END_DATE IS NULL OR e.END_DATE >= TRUNC(SYSDATE))
ORDER BY p.PERSON_NUMBER, ap.ABSENCE_TYPE_CODE;

ANC_PER_ABS_ENTRIES - Absence Records

This table stores individual absence records - the actual time off requests and approvals. Each row represents a specific absence period with dates, duration, status, and approval information.

Sample Query: Recent Absence Requests

-- Recent absence requests with approval status
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME as EMPLOYEE_NAME,
    ap.PLAN_NAME,
    ae.START_DATE,
    ae.END_DATE,
    ae.DURATION,
    ae.DURATION_UOM,
    ae.APPROVAL_STATUS,
    mgr.DISPLAY_NAME as APPROVED_BY,
    ae.SUBMITTED_DATE,
    ae.COMMENTS
FROM ANC_PER_ABS_ENTRIES ae
JOIN PER_ALL_PEOPLE_F p 
  ON ae.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
JOIN ANC_ABSENCE_PLANS ap 
  ON ae.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
LEFT JOIN PER_ALL_PEOPLE_F mgr 
  ON ae.APPROVED_BY = mgr.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN mgr.EFFECTIVE_START_DATE 
                          AND mgr.EFFECTIVE_END_DATE
WHERE ae.SUBMITTED_DATE >= TRUNC(SYSDATE) - 30  -- Last 30 days
ORDER BY ae.SUBMITTED_DATE DESC;

ANC_PER_ABSENCE_ENTRIES - Balance Transactions

This table tracks all balance movements including accruals, deductions, adjustments, and carryovers. It provides a complete audit trail of how absence balances change over time.

Sample Query: Balance Movement History

-- Employee balance movement history
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PLAN_NAME,
    ae.TRANSACTION_DATE,
    ae.TRANSACTION_TYPE,
    ae.QUANTITY,
    ae.UNIT_OF_MEASURE,
    ae.RUNNING_BALANCE,
    ae.COMMENTS
FROM ANC_PER_ABSENCE_ENTRIES ae
JOIN PER_ALL_PEOPLE_F p 
  ON ae.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
JOIN ANC_ABSENCE_PLANS ap 
  ON ae.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
WHERE p.PERSON_NUMBER = :employee_number  -- Specific employee
  AND ae.TRANSACTION_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year
ORDER BY ae.TRANSACTION_DATE DESC, ae.ABSENCE_ENTRY_ID DESC;

Key Configuration Tables

ANC_ACCRUAL_PLANS - Accrual Configuration

Accrual plans define how leave balances accumulate over time. This includes accrual rates, frequencies, and calculation rules.

ANC_ABSENCE_CALENDARS - Working Calendar Integration

This table manages the relationship between absence plans and working calendars, defining which days count as working days for absence calculations.

Common Absence Management Queries

Employee Balance Summary

One of the most common requirements is to show current absence balances for employees:

-- Current absence balances for all active employees
WITH current_balances AS (
    SELECT 
        ae.PERSON_ID,
        ae.ABSENCE_PLAN_ID,
        ae.RUNNING_BALANCE
    FROM ANC_PER_ABSENCE_ENTRIES ae
    INNER JOIN (
        SELECT 
            PERSON_ID,
            ABSENCE_PLAN_ID,
            MAX(TRANSACTION_DATE) as MAX_TRANSACTION_DATE
        FROM ANC_PER_ABSENCE_ENTRIES
        GROUP BY PERSON_ID, ABSENCE_PLAN_ID
    ) latest ON ae.PERSON_ID = latest.PERSON_ID
                AND ae.ABSENCE_PLAN_ID = latest.ABSENCE_PLAN_ID
                AND ae.TRANSACTION_DATE = latest.MAX_TRANSACTION_DATE
)
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PLAN_NAME,
    ap.ABSENCE_TYPE_CODE,
    ent.ANNUAL_ENTITLEMENT,
    NVL(cb.RUNNING_BALANCE, 0) as CURRENT_BALANCE,
    ap.PLAN_UOM,
    CASE 
        WHEN NVL(cb.RUNNING_BALANCE, 0) < 0 THEN 'Negative'
        WHEN NVL(cb.RUNNING_BALANCE, 0) = 0 THEN 'Zero'
        WHEN NVL(cb.RUNNING_BALANCE, 0) > ent.ANNUAL_ENTITLEMENT THEN 'Over Entitlement'
        ELSE 'Normal'
    END as BALANCE_STATUS
FROM PER_ALL_PEOPLE_F p
JOIN ANC_PER_ABSENCE_ENTITLEMENTS ent 
  ON p.PERSON_ID = ent.PERSON_ID
  AND (ent.END_DATE IS NULL OR ent.END_DATE >= TRUNC(SYSDATE))
JOIN ANC_ABSENCE_PLANS ap 
  ON ent.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
  AND TRUNC(SYSDATE) BETWEEN ap.EFFECTIVE_START_DATE 
                          AND NVL(ap.EFFECTIVE_END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))
LEFT JOIN current_balances cb 
  ON p.PERSON_ID = cb.PERSON_ID 
  AND ent.ABSENCE_PLAN_ID = cb.ABSENCE_PLAN_ID
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
ORDER BY p.PERSON_NUMBER, ap.ABSENCE_TYPE_CODE;

Absence Usage Analytics

Analyzing absence patterns and usage trends:

-- Absence usage analysis by department and leave type
SELECT 
    org.NAME as DEPARTMENT,
    ap.PLAN_NAME,
    ap.ABSENCE_TYPE_CODE,
    COUNT(ae.ABSENCE_ATTENDANCE_ID) as TOTAL_ABSENCES,
    SUM(ae.DURATION) as TOTAL_DAYS_TAKEN,
    AVG(ae.DURATION) as AVERAGE_ABSENCE_LENGTH,
    COUNT(DISTINCT ae.PERSON_ID) as EMPLOYEES_USING_LEAVE,
    ROUND(AVG(ae.DURATION), 2) as AVG_DURATION
FROM ANC_PER_ABS_ENTRIES ae
JOIN PER_ALL_PEOPLE_F p 
  ON ae.PERSON_ID = p.PERSON_ID
  AND ae.START_DATE BETWEEN p.EFFECTIVE_START_DATE 
                         AND p.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON p.PERSON_ID = a.PERSON_ID
  AND ae.START_DATE BETWEEN a.EFFECTIVE_START_DATE 
                         AND a.EFFECTIVE_END_DATE
  AND a.PRIMARY_FLAG = 'Y'
JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
JOIN ANC_ABSENCE_PLANS ap 
  ON ae.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
WHERE ae.APPROVAL_STATUS = 'APPROVED'
  AND ae.START_DATE >= TRUNC(SYSDATE, 'YYYY')  -- Current year
  AND ae.DURATION_UOM = 'Days'
GROUP BY org.NAME, ap.PLAN_NAME, ap.ABSENCE_TYPE_CODE
HAVING COUNT(ae.ABSENCE_ATTENDANCE_ID) >= 3  -- Minimum sample size
ORDER BY org.NAME, TOTAL_DAYS_TAKEN DESC;

Pending Approvals Report

Critical for managers to track requests requiring approval:

-- Pending absence approvals by manager
SELECT 
    mgr.DISPLAY_NAME as MANAGER,
    COUNT(*) as PENDING_REQUESTS,
    MIN(ae.SUBMITTED_DATE) as OLDEST_REQUEST,
    MAX(ae.SUBMITTED_DATE) as NEWEST_REQUEST,
    SUM(ae.DURATION) as TOTAL_DAYS_PENDING
FROM ANC_PER_ABS_ENTRIES ae
JOIN PER_ALL_PEOPLE_F emp 
  ON ae.PERSON_ID = emp.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN emp.EFFECTIVE_START_DATE 
                          AND emp.EFFECTIVE_END_DATE
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON emp.PERSON_ID = a.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE 
                          AND a.EFFECTIVE_END_DATE
  AND a.PRIMARY_FLAG = 'Y'
JOIN PER_ALL_PEOPLE_F mgr 
  ON a.MANAGER_ID = mgr.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN mgr.EFFECTIVE_START_DATE 
                          AND mgr.EFFECTIVE_END_DATE
WHERE ae.APPROVAL_STATUS IN ('PENDING', 'SUBMITTED')
GROUP BY mgr.PERSON_ID, mgr.DISPLAY_NAME
ORDER BY PENDING_REQUESTS DESC;

Advanced Configuration Scenarios

Complex Accrual Rules

Oracle Absence Management supports sophisticated accrual patterns. Here's how to query employees with different accrual rates based on service length:

-- Accrual rates based on years of service
WITH service_years AS (
    SELECT 
        p.PERSON_ID,
        p.PERSON_NUMBER,
        p.DISPLAY_NAME,
        wr.START_DATE,
        FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), wr.START_DATE) / 12) as YEARS_OF_SERVICE
    FROM PER_ALL_PEOPLE_F p
    JOIN PER_WORK_RELATIONSHIPS wr 
      ON p.PERSON_ID = wr.PERSON_ID
      AND wr.PRIMARY_FLAG = 'Y'
      AND wr.TERMINATION_DATE IS NULL
    WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
      AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                              AND p.EFFECTIVE_END_DATE
)
SELECT 
    sy.PERSON_NUMBER,
    sy.DISPLAY_NAME,
    sy.YEARS_OF_SERVICE,
    ap.PLAN_NAME,
    ent.ANNUAL_ENTITLEMENT,
    CASE 
        WHEN sy.YEARS_OF_SERVICE < 1 THEN 'Probationary (10 days)'
        WHEN sy.YEARS_OF_SERVICE < 5 THEN 'Standard (15 days)'
        WHEN sy.YEARS_OF_SERVICE < 10 THEN 'Senior (20 days)'
        ELSE 'Veteran (25 days)'
    END as SERVICE_TIER
FROM service_years sy
JOIN ANC_PER_ABSENCE_ENTITLEMENTS ent 
  ON sy.PERSON_ID = ent.PERSON_ID
  AND (ent.END_DATE IS NULL OR ent.END_DATE >= TRUNC(SYSDATE))
JOIN ANC_ABSENCE_PLANS ap 
  ON ent.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
  AND ap.ABSENCE_TYPE_CODE = 'VACATION'
ORDER BY sy.YEARS_OF_SERVICE, sy.PERSON_NUMBER;

Year-End Carryover Processing

Managing year-end carryover is a critical process. Here's how to identify employees with carryover scenarios:

-- Year-end carryover analysis
WITH current_balances AS (
    SELECT 
        ae.PERSON_ID,
        ae.ABSENCE_PLAN_ID,
        ae.RUNNING_BALANCE
    FROM ANC_PER_ABSENCE_ENTRIES ae
    INNER JOIN (
        SELECT 
            PERSON_ID,
            ABSENCE_PLAN_ID,
            MAX(TRANSACTION_DATE) as MAX_DATE
        FROM ANC_PER_ABSENCE_ENTRIES
        WHERE TRANSACTION_DATE <= TO_DATE('31-DEC-' || TO_CHAR(SYSDATE, 'YYYY'), 'DD-MON-YYYY')
        GROUP BY PERSON_ID, ABSENCE_PLAN_ID
    ) latest ON ae.PERSON_ID = latest.PERSON_ID
                AND ae.ABSENCE_PLAN_ID = latest.ABSENCE_PLAN_ID
                AND ae.TRANSACTION_DATE = latest.MAX_DATE
)
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    ap.PLAN_NAME,
    cb.RUNNING_BALANCE as YEAR_END_BALANCE,
    ap.CARRYOVER_LIMIT,
    CASE 
        WHEN cb.RUNNING_BALANCE <= ap.CARRYOVER_LIMIT THEN cb.RUNNING_BALANCE
        ELSE ap.CARRYOVER_LIMIT
    END as CARRYOVER_AMOUNT,
    CASE 
        WHEN cb.RUNNING_BALANCE > ap.CARRYOVER_LIMIT 
        THEN cb.RUNNING_BALANCE - ap.CARRYOVER_LIMIT
        ELSE 0
    END as FORFEIT_AMOUNT
FROM current_balances cb
JOIN PER_ALL_PEOPLE_F p 
  ON cb.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
JOIN ANC_ABSENCE_PLANS ap 
  ON cb.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND ap.CARRYOVER_LIMIT > 0
  AND cb.RUNNING_BALANCE > 0
ORDER BY FORFEIT_AMOUNT DESC;

Integration and Data Quality Considerations

Data Validation Queries

Ensuring data integrity in absence management is crucial for accurate reporting and compliance:

-- Data quality checks for absence management
-- Check 1: Employees without entitlements
SELECT 
    'Employees without vacation entitlements' as ISSUE,
    COUNT(*) as ISSUE_COUNT
FROM PER_ALL_PEOPLE_F p
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
  AND NOT EXISTS (
      SELECT 1 FROM ANC_PER_ABSENCE_ENTITLEMENTS e
      JOIN ANC_ABSENCE_PLANS ap ON e.ABSENCE_PLAN_ID = ap.ABSENCE_PLAN_ID
      WHERE e.PERSON_ID = p.PERSON_ID
        AND ap.ABSENCE_TYPE_CODE = 'VACATION'
        AND (e.END_DATE IS NULL OR e.END_DATE >= TRUNC(SYSDATE))
  )

UNION ALL

-- Check 2: Absences without corresponding entitlements  
SELECT 
    'Absences without entitlements',
    COUNT(*)
FROM ANC_PER_ABS_ENTRIES ae
WHERE NOT EXISTS (
    SELECT 1 FROM ANC_PER_ABSENCE_ENTITLEMENTS e
    WHERE e.PERSON_ID = ae.PERSON_ID
      AND e.ABSENCE_PLAN_ID = ae.ABSENCE_PLAN_ID
      AND ae.START_DATE BETWEEN e.START_DATE 
                            AND NVL(e.END_DATE, TO_DATE('4712-12-31', 'YYYY-MM-DD'))
  )

UNION ALL

-- Check 3: Overlapping absence periods
SELECT 
    'Overlapping absence periods',
    COUNT(*)
FROM ANC_PER_ABS_ENTRIES a1
WHERE EXISTS (
    SELECT 1 FROM ANC_PER_ABS_ENTRIES a2
    WHERE a1.PERSON_ID = a2.PERSON_ID
      AND a1.ABSENCE_ATTENDANCE_ID != a2.ABSENCE_ATTENDANCE_ID
      AND a1.APPROVAL_STATUS = 'APPROVED'
      AND a2.APPROVAL_STATUS = 'APPROVED'
      AND (a1.START_DATE BETWEEN a2.START_DATE AND a2.END_DATE
           OR a1.END_DATE BETWEEN a2.START_DATE AND a2.END_DATE
           OR (a1.START_DATE <= a2.START_DATE AND a1.END_DATE >= a2.END_DATE))
  );

Performance Optimization

Security Considerations

Best Practices for Absence Management Reporting

1. Establish Clear Metrics

• Utilization rates: Percentage of entitlement used by plan type
• Pattern analysis: Peak absence periods, seasonal trends
• Compliance monitoring: Approval timeframes, policy adherence
• Balance accuracy: Regular reconciliation of calculated vs. system balances

2. Regular Data Maintenance

-- Monthly data maintenance checklist query
SELECT 
    CHECK_TYPE,
    RESULT_COUNT,
    CASE WHEN RESULT_COUNT > 0 THEN 'Action Required' ELSE 'OK' END as STATUS
FROM (
    SELECT 'Expired Entitlements Not Closed' as CHECK_TYPE,
           COUNT(*) as RESULT_COUNT
    FROM ANC_PER_ABSENCE_ENTITLEMENTS 
    WHERE END_DATE < TRUNC(SYSDATE) - 90
      AND END_DATE IS NOT NULL
      
    UNION ALL
    
    SELECT 'Future-Dated Approved Absences',
           COUNT(*)
    FROM ANC_PER_ABS_ENTRIES 
    WHERE APPROVAL_STATUS = 'APPROVED'
      AND START_DATE > ADD_MONTHS(TRUNC(SYSDATE), 12)
      
    UNION ALL
    
    SELECT 'Long Pending Approvals',
           COUNT(*)
    FROM ANC_PER_ABS_ENTRIES 
    WHERE APPROVAL_STATUS IN ('PENDING', 'SUBMITTED')
      AND SUBMITTED_DATE < TRUNC(SYSDATE) - 30
);

3. Automated Monitoring

Set up automated monitoring for:

• Negative balances beyond policy limits
• Long-pending approval requests
• Unusual absence patterns that might indicate data issues
• Accrual calculation discrepancies

Mastering Oracle's Absence Management tables enables sophisticated leave tracking, compliance reporting, and workforce analytics. The hierarchical model supports complex business rules while maintaining data integrity and auditability.

Whether you're implementing new absence policies, troubleshooting balance issues, or building comprehensive absence analytics, understanding these core tables and their relationships is essential. Combine absence data with core HCM entities for comprehensive workforce reporting, and leverage REST APIs for real-time integrations with time tracking and payroll systems.

Remember that absence management directly impacts payroll calculations and should be integrated with your broader HCM reporting strategy for maximum effectiveness.