Saturday, 19 September 2026

Oracle HCM Absence Design Guide: When Can You Skip the Absence Plan?

During functional and technical design phases of an Oracle Cloud Absence Management implementation, consultants and solution architects frequently encounter a fundamental configuration question: Does every Absence Type mandate an associated Absence Plan?

A frequent anti-pattern across enterprise implementations is the blanket creation of an Absence Plan for every single Absence Type "just to be safe." While this approach might appear harmless during the initial sandbox prototyping stage, it creates substantial technical debt down the line. It causes unnecessary batch processing overhead during scheduled accrual calculations, introduces UI bloat across employee self-service pages, complicates security profiling, and adds administrative maintenance hurdles. Conversely, failing to associate a plan when one is strictly necessary leads to uncalculated salary adjustments, absent ledger tracking, and broken integration handoffs with Global Payroll.

This technical guide provides an exhaustive architectural deep dive into the functional distinctions, underlying data models, Fast Formula impacts, Global Payroll interfaces, and batch performance implications of deploying standalone Absence Types versus plan-linked configurations.


1. Deconstructing Core Responsibilities: Absence Types vs. Absence Plans

To establish clean configuration boundaries, it is essential to understand the separate responsibilities each object holds in the Oracle Fusion data schema:

A. Absence Type (The Front-End & Transactional Layer)
The Absence Type serves as the entry portal for the worker and manager. It defines how time off is requested, validated, categorized, and approved. Its functional and technical scope includes:

  • Pattern Definition: Classifies the absence into core engine behaviors: Generic Absence, Illness or Injury, or Childbirth or Adoption.
  • Duration Units of Measure (UOM): Determines whether the duration is evaluated in Calendar Days, Elapsed Days, Working Days, or Schedule Hours via the employee’s work schedule or work pattern.
  • Validation Rules: Enforces minimum and maximum durations per single submission, advance notice periods, waiting periods, and retroactive date entry restrictions.
  • Workflow & Approvals: Directly interfaces with the Transaction Console (TAC) and Oracle BPM Worklist to route approval workflows based on line manager, matrix manager, or HR specialist hierarchies.
  • User Interface Behavior: Controls attachment requirements, entry reasons, descriptive flexfields (DFFs), and Redwood Express Mode / Visual Builder Studio display rules.

B. Absence Plan (The Back-End Calculation & Financial Ledger)
The Absence Plan operates as the background engine that executes business logic, rules, and mathematical balances. Its functional and technical scope includes:

  • Balance Maintenance: Functions as a balance ledger maintaining opening balances, periodic accruals, manual adjustments, forfeitures, carryover expiries, and terminal disbursements.
  • Entitlement Matrices: Evaluates service-based tiers (e.g., tenure-based accrual bands or statutory sick leave matrices) via length-of-service banding or user-defined Fast Formulas.
  • Payment Band Splitting: Automatically segments a single continuous absence request into multiple payment brackets (such as 100% full pay, 50% half pay, and unpaid days).
  • Payroll Calculation Hand-off: Feeds rate definitions and calculation components into Oracle Fusion Global Payroll via employee calculation cards.
  • Batch Processing: Evaluated during nightly Enterprise Scheduler Service (ESS) jobs such as Update Accrual Plan Enrollments and Calculate Accruals and Balances.
Key Architectural Rule: The Absence Type captures that a worker is absent from their schedule. The Absence Plan evaluates whether that absence is paid or unpaid, assesses entitlements, updates running balance ledgers, and passes payment calculation directives to payroll engines.

2. Detailed Functional Decision Matrix

Use the following matrix during design sessions to map client policy requirements to the correct technical architecture:

Business Scenario / Policy Plan Required? Plan Type Architectural & Configuration Rationale
Informational & Remote Work (WFH, Travel, Training) No Plan None Pure calendar availability tracking. Blocks employee schedules in Time & Labor without balance accrual, ceiling limits, or salary changes.
Statutory Bereavement / Jury Duty (Fixed Non-Cumulative Caps) No Plan* None When policy states "up to 3 consecutive days paid as standard salary per event," enforce validation limits via Type settings without a plan. If an annual cumulative bank must be tracked across multiple events, a plan is required.
Annual / Earned Vacation Leave Yes Accrual Plan Tracks earned units, proration rules, carryover ceilings, vestings, and terminal balance cash-outs upon employment end.
Maternity, Paternity & Long-Term Illness Yes Qualification Plan Splits entry duration into tiered compensation brackets (e.g., 100% pay for first 30 days, 50% for next 30 days, unpaid thereafter) based on tenure and rolling backward/forward evaluation windows.
Unpaid Leave (Leave Without Pay - LWOP) Yes No Entitlement Plan Acts as an unaccrued calculation bridge. Evaluates no running balance, but creates an automated deduction record on payroll calculation cards to dock base pay.
Overtime In Lieu / Compensatory Time (TOIL) Yes Compensatory Plan Stores hours earned through approved overtime (either manually or transferred from Time and Labor) and applies specific expiration policies (e.g., must be redeemed within 90 days).
Leave Donation / Catastrophic Leave Pool Yes Donation Plan Provides the recipient account structure to receive leave hours debited from donating colleagues' accrual plans.

3. Technical Mechanics: Global Payroll Integration

One of the most consequential reasons to configure an Absence Plan is the automated transfer of payment calculations into Oracle Fusion Global Payroll. The absence-to-payroll pipeline functions through dedicated objects:

A. Standalone Absence Type Behavior (No Plan Linked)
When an employee books a standalone Absence Type, the application writes entries to the core table ANC_PER_ABSENCE_ENTRIES. However, the system does not generate entries in payroll calculation tables. Unless an administrator manually keys an element entry into the worker's payroll records, the payroll run processes their standard earnings without any deduction or special rate calculation. Therefore, standalone types are unsuitable for leaves that modify earnings or require separate line items on the payslip.

B. Plan-Linked Absence Behavior (Via Calculation Cards)
When an Absence Plan is associated with a Payroll Element, the end-to-end interface operates as follows:

  • The plan is linked to a pre-configured Payroll Element with the primary classification Absences.
  • When an absence entry is submitted and approved, the absence engine creates a record under the worker’s Calculation Card: Calculation Component - Absence.
  • The payroll run checks the calculation component, fetches the absence units, applies the assigned Rate Definition (e.g., standard basic pay rate, 50% band rate, or average earnings rate), and executes the payroll formula.
  • For unpaid leaves, a No-Entitlement Plan sends deduction units into payroll, cleanly reducing standard gross wages without requiring manual HR input.

4. Engine Overhead: Fast Formulas & Batch Processing Performance

A frequently neglected area during implementation is system performance. Enrolling thousands of workers into unnecessary plans introduces severe overhead into daily and monthly scheduled processes.

A. Fast Formula Execution Cycle
Every plan enrolled to an employee evaluates complex Fast Formula hooks, including:

  • Global Absence Accrual: Runs to calculate repeating earned amounts, length-of-service banding, and step progressions.
  • Global Absence Proration: Calculates partial-period earnings during hire, mid-period tenure changes, or assignment status changes.
  • Global Absence Ceiling: Enforces maximum balance limits at accrual time or term ends.
  • Global Absence Carryover: Evaluates unused balance transfers and manages balance expiration dates.

B. Impact on ESS Batch Processes
Enterprise scheduled jobs evaluate these plans across your entire workforce:

  • Update Accrual Plan Enrollments: Evaluates eligibility profiles and formulas for every enrolled worker to confirm plan participation.
  • Calculate Accruals and Balances: Processes all plan formulas and calculates balance adjustments across assignments.
Performance Takeaway: In an enterprise with 50,000 employees, creating an unnecessary plan for a purely informational absence adds 50,000 extra formula evaluations to nightly and weekend ESS maintenance jobs. Keeping informational absences as standalone Absence Types eliminates this processing overhead.

5. User Experience: Redwood UI Differences

Oracle HCM's Redwood user interface dynamically alters the front-end layout based on whether an Absence Type has an underlying Absence Plan attached:

  • Standalone Absence Type Experience: When the employee selects a standalone type, the interface presents a simplified form showing date selectors, duration totals, reason picklists, and comment/attachment cards. The page does not render balance tiles, projected balances, or accrual warnings. This creates a clean experience for quick tasks like recording work-from-home or training days.
  • Plan-Linked Experience: When the employee selects a plan-linked type, the interface dynamically displays balance metrics. It calculates and renders the current available balance, balance at the date of absence, projected accruals, pending approvals, and carryover expiry warnings. For Qualification Plans, it outlines remaining entitlement bands (e.g., displaying how many days remain at full pay vs. half pay).

6. Architecture Checklist & Troubleshooting Guide

Before creating a new plan in your setup workbook, walk through these evaluation criteria:

  1. Balance Ledger Needed? Does HR need a persistent record of accrued, used, and remaining units over time?
    Yes: Configure an Accrual Plan.
  2. Tiered Compensation Rules? Does payment change across the leave period based on tenure or statutory rules?
    Yes: Configure a Qualification Plan.
  3. Direct Payroll Reductions? Must taking this absence automatically dock the employee's base salary via element entries?
    Yes: Configure a No-Entitlement Plan linked to an Absence Element.
  4. Simple Visibility & Approval? Does the business only need calendar blocking and line-manager workflow approvals?
    Yes: Keep it as a Standalone Absence Type.

Troubleshooting Common Setup Traps:

  • "Unpaid leave was entered, but employee received full salary." This happens when Unpaid Leave is configured as a standalone type without a No-Entitlement plan, preventing rate reduction components from reaching the payroll calculation card.
  • "The employee cannot see balance cards on the Redwood Time Off page." Verify that the Absence Plan is linked directly to the Absence Type in the Type > Plans and Reasons setup tab, and that the worker is actively enrolled in the plan.
  • "Accrual batch job execution times are growing rapidly." Audit your configuration for informational absences inadvertently tied to accrual plans. Unlink unnecessary plans to reduce formula evaluation cycles.

Designing absence management setups around these principles helps keep your configurations clean, your batch maintenance runs fast, and your payroll integrations dependable across your Oracle Cloud HCM deployment.

Friday, 18 September 2026

Prorating Annual Leave Accrual for Mid-Period Enrollments and Un-Enrollments

Most Annual Leave accrual plans define a fixed number of days an employee earns over a full calendar year. But employees rarely join or leave exactly on 1 January or 31 December — they join mid-year, transfer between plans mid-year, or leave the organization partway through it. Without special handling, the accrual engine either grants the full annual entitlement regardless of actual tenure in the period, or under/over-counts partial months in an inconsistent way.

This post shares a working Fast Formula, XYZ_ANNUAL_LEAVE_PRORATION_FF, of type Global Absence Partial Period Accrual Rate Formula, that prorates the Annual Leave accrual for employees with a partial period of participation — whether that partial period is caused by a hire, a termination, or a mid-period plan enrollment/un-enrollment — using a common HR rounding convention: the 15th-of-the-month rule.

Business Requirement

  • An employee who joins or is enrolled in the plan on or before the 15th of a month should receive accrual credit for that whole month.
  • An employee who joins or is enrolled after the 15th of a month should NOT receive accrual credit for that partial month — credit starts from the 1st of the following month.
  • An employee who terminates or is un-enrolled on or before the 15th of a month should NOT receive accrual credit for that partial month — credit stops at the end of the previous month.
  • An employee who terminates or is un-enrolled after the 15th of a month SHOULD receive accrual credit for that whole month.
  • The final accrual is the full annual amount, scaled by the ratio of eligible participation months to total months in the calendar/accrual period.

Formula Type: Global Absence Partial Period Accrual Rate Formula

This formula type is attached at the Accrual Plan level and is invoked by the accrual engine specifically when an employee's participation does not span the full accrual period — for example, in their hire year or termination year, or around a mid-year plan transfer. It must return a single numeric value: the prorated accrual amount for that partial period.

Input Description
IV_ACCRUAL The full-period accrual amount before proration (e.g., the standard annual entitlement).
IV_ACCRUALPERIODSTARTDATE / ENDDATE Start and end date of the accrual period being processed.
IV_PLANENROLLMENTSTARTDATE / ENDDATE The employee's enrollment start/end date in the source accrual plan.
IV_CALEDARSTARTDATE / ENDDATE Start and end date of the plan's calendar (used to compute total months in the period).

Logic Walkthrough

  • Resolve the true start of participation (ld_st_date) as the latest of hire date, accrual period start, and plan enrollment start.
  • If the day-of-month of ld_st_date is after the 15th, round the accrual start forward to the 1st of the next month (no credit for that partial month); otherwise round back to the 1st of the current month (full credit).
  • Resolve the true end of participation (ld_end_date) as the earliest of termination date, accrual period end, and plan enrollment end.
  • If the day-of-month of ld_end_date is on or before the 15th, round the accrual end back to the end of the previous month (no credit); otherwise round forward to the end of the current month (full credit).
  • Compute ln_term_duration — the total months spanned by the plan's calendar period.
  • Compute ln_participation_duration — months between the rounded start and end dates.
  • Scale the full accrual: ln_prorated_accrual = IV_ACCRUAL × (ln_participation_duration ÷ ln_term_duration), round it, and return it.
Example: An employee joins on 10-Mar (on or before the 15th, so March counts) and the plan calendar runs Jan–Dec (12 months). Rounded participation is Mar–Dec = 10 months. For a 24-day annual entitlement: 24 × (10 ÷ 12) = 20 days. If the same employee had instead joined on 20-Mar (after the 15th), March would not count, participation would be Apr–Dec = 9 months, and the entitlement would be 24 × (9 ÷ 12) = 18 days.

Full Formula Source

/***************************************************************************
FORMULA NAME: XYZ_ANNUAL_LEAVE_PRORATION_FF
FORMULA TYPE: Global Absence Partial Period Accrual Rate Formula
DESCRIPTION: This formula returns the accrual for mid-period enrollments and
             un-enrollments for the Annual Leave absence plan.

Change History:
Name        Date        Version     Comments
-------------------------------------------------------------------------------
Aditya                  DRAFT 1A    Initial Version
*******************************************************************************/

DEFAULT FOR IV_ACCRUAL IS 0
DEFAULT FOR ACP_HIRE_DATE IS '1901/01/01 00:00:00' (date)
DEFAULT FOR ACP_TERMINATION_DATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_ACCRUALPERIODENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_PLANENROLLMENTENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_CALEDARSTARTDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR IV_CALEDARENDDATE IS '4712/12/31 00:00:00' (date)
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE IS '47121231'

INPUTS ARE IV_ACCRUAL, IV_ACCRUALPERIODSTARTDATE, IV_ACCRUALPERIODENDDATE,
           IV_PLANENROLLMENTSTARTDATE, IV_PLANENROLLMENTENDDATE,
           IV_CALEDARSTARTDATE, IV_CALEDARENDDATE

ln_hr_assignment_id = GET_CONTEXT(HR_ASSIGNMENT_ID,0)
ld_effective_date = GET_CONTEXT(EFFECTIVE_DATE,'4712/12/31 00:00:00'(date))
ln_leg_group_id = GET_CONTEXT(LEGISLATIVE_DATA_GROUP_ID,0)
LN_PERSON_ID = GET_CONTEXT(PERSON_ID,-1)

ld_hire_date = ACP_HIRE_DATE
ld_term_date = ACP_TERMINATION_DATE

/* to check if hire/enrollment date is on or before the 15th of the month */
ld_st_date = GREATEST(GREATEST(ld_hire_date,IV_ACCRUALPERIODSTARTDATE),IV_PLANENROLLMENTSTARTDATE)
ln_st_day = to_number(to_char(ld_st_date,'DD'))

IF ln_st_day > 15 THEN
(
  /* accrual start date is from next month */
  ld_accrual_period_st_date = ADD_DAYS(LAST_DAY(ld_st_date),1)
)
ELSE
(
  /* accrual start date should be from 1st of current month */
  ld_accrual_period_st_date = ADD_MONTHS(ADD_DAYS(LAST_DAY(ld_st_date),1),-1)
)

/* to check if termination/un-enrollment date is on or after the 15th of the month */
ld_end_date = LEAST(LEAST(ld_term_date,IV_ACCRUALPERIODENDDATE),IV_PLANENROLLMENTENDDATE)
ln_end_day = to_number(to_char(ld_end_date,'DD'))

IF ln_end_day <= 15 THEN
(
  ld_accrual_period_end_date = ADD_MONTHS(LAST_DAY(ld_end_date),-1)
)
ELSE
(
  ld_accrual_period_end_date = LAST_DAY(ld_end_date)
)

ln_term_duration = 1 + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'yy'))
                  - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'yy'))) * 12
                  + (TO_NUMBER(TO_CHAR(IV_CALEDARENDDATE, 'mm'))
                  - TO_NUMBER(TO_CHAR(IV_CALEDARSTARTDATE, 'mm')))

ln_participation_duration = months_between(ADD_DAYS(ld_accrual_period_end_date,1),ld_accrual_period_st_date)

ln_prorated_accrual = IV_ACCRUAL * (ln_participation_duration / ln_term_duration)

accrual = round(ln_prorated_accrual)

RETURN accrual

Compilation

  • Compile the formula in the Fast Formula editor and resolve any syntax errors before attaching it to a live plan.

Attaching the Formula to the Accrual Plan

Navigate to Absences Administration → Accrual Plans, open the Annual Leave accrual plan, and go to the Rules tab. Under the Partial Accrual Period Rate section, set the formula to XYZ_ANNUAL_LEAVE_PRORATION_FF. Save and submit the change; it will then be invoked automatically for any employee whose participation in an accrual period is partial (new hires, terminations, and mid-period enrollment changes).

Testing and Validation

  • Test with a hire date on or before the 15th and confirm the joining month is credited.
  • Test with a hire date after the 15th and confirm the joining month is excluded.
  • Test with termination on or before the 15th (excluded) and after the 15th (included).

Conclusion

Mid-period proration is one of the most common gaps between an out-of-the-box Annual Leave accrual plan and what organizations actually need at go-live. This formula applies a simple, widely recognized 15th-of-the-month convention to decide which partial months count, then scales the full entitlement by the resulting participation ratio — giving predictable, auditable results for new hires, leavers, and mid-year plan transfers alike. The same GREATEST/LEAST plus day-of-month pattern can be reused for other accrual plans (Sick Leave, Casual Leave) by simply changing the referenced plan and the accrual amount input.

Saturday, 13 June 2026

Query to fetch Domain Name - Oracle HCM

 select external_virtual_host from ask_deployed_domains where deployed_domain_name = 'FADomain'


This will give you the current logged in instance URL of the Oracle HCM pod

Thursday, 21 May 2026

Balance Feeds Query - Oracle HCM

 SELECT 

    pbt.BALANCE_NAME,
    pet.ELEMENT_NAME,
    piv.BASE_NAME         AS INPUT_VALUE_NAME,
    pbf.SCALE,
    pbf.EFFECTIVE_START_DATE,
    pbf.EFFECTIVE_END_DATE
FROM PAY_BALANCE_FEEDS_F pbf
JOIN PAY_BALANCE_TYPES_VL pbt
    ON pbf.BALANCE_TYPE_ID = pbt.BALANCE_TYPE_ID
JOIN PAY_INPUT_VALUES_F piv
    ON pbf.INPUT_VALUE_ID = piv.INPUT_VALUE_ID
JOIN PAY_ELEMENT_TYPES_TL pet
    ON piv.ELEMENT_TYPE_ID = pet.ELEMENT_TYPE_ID
WHERE TRUNC(SYSDATE) BETWEEN piv.EFFECTIVE_START_DATE 
                         AND piv.EFFECTIVE_END_DATE
AND pet.LANGUAGE = USERENV('LANG')
AND UPPER(pbt.BALANCE_NAME) = UPPER('XX Salary Balance')
ORDER BY pbt.BALANCE_NAME,
         pet.ELEMENT_NAME

Thursday, 9 October 2025

Sample HDL for Payroll Balance Name Translation

METADATA|PayrollBalanceDefinitionTranslation|BalanceCode|Language|BalanceName|ReportingName|LegislativeDataGroupName

MERGE|PayrollBalanceDefinitionTranslation|Extended Medical Leave Entitlement Payment|E|Pago del derecho a licencia médica extendida|Pago del derecho a licencia médica extendida|PE Legislative Data Group


Balance Code would be the Base Balance Name from pay_balance_types table

Balance Name and Reporting Name would be the values which you would load in the required language

Wednesday, 13 August 2025

Get sysdate in fast formula

 L_SYSDATE = '4712/12/31 00:00:00' (date)

 L_SYSDATE = GET_CURRENT_DATE()

Thursday, 12 June 2025

HDL Lines

SELECT TEXT

FROM fusion.hrc_dl_file_lines
WHERE data_set_bus_obj_id IN
(SELECT data_set_bus_obj_id
FROM fusion.hrc_dl_data_set_bus_objs
WHERE data_set_id IN
(SELECT data_set_id
FROM fusion.hrc_dl_data_sets
WHERE ucm_content_id = 'UCMFA15238827'))
--and (TEXT like 'METADATA%' or TEXT like '%17019%')
order by SEQ_NUM