In Part 1 the formula derived l_working_hours and l_monthly_hours — the daily and monthly thresholds. Knowing the threshold isn't enough. The same hour worked on a Tuesday afternoon doesn't pay the same as that hour worked on a Sunday or on a public holiday.

Most OT regimes have at least four distinct day types — regular weekday, Saturday, Sunday, and gazetted public holiday — and each one routes worked hours into a different OT bucket. GET_DATE_DAY_OF_WEEK alone doesn't distinguish a regular Wednesday from Wednesday-when-Diwali-falls-on-a-Wednesday. The formula has to do that disambiguation itself.

This post walks through the four Oracle HCM Cloud constructs the formula uses to make that decision: the day-of-week function, an ADD_DAYS-based scan for the next Friday, a CALL_FORMULA invocation that resolves the public holiday calendar, and a text-DBI fork on PER_ASG_FULL_PART_TIME.

GET_DATE_DAY_OF_WEEK Return Values and Date-to-Day-Name Conversion

GET_DATE_DAY_OF_WEEK is a built-in Fast Formula function that takes a date and returns the corresponding day name as a three-letter uppercase string. It is locale-independent — the return values do not change with the user's session language:

The TCR formula calls it once per measure period entry against the timecard's start time:

From here, every subsequent branch in the formula compares against l_week_day using equality on those exact string literals.

The FRI-Anchored Weekly Compare with ADD_DAYS and WHILE 1=1 LOOP

Day-of-week alone tells you what day it is. It doesn't tell you whether you've crossed into a new OT week. Most OT regimes accumulate worked hours against a weekly cap that resets every Friday at midnight — so the formula needs to know when "this Friday" passes to clear the running weekly counter.

The pattern used here is a WHILE 1=1 LOOP that walks forward day by day from the current timecard entry until it lands on a Friday, then stores that date as the reset boundary:

Three Oracle HCM Cloud constructs are worth pausing on here:

• ADD_DAYS(date, integer) — adds the integer to the date and returns a date. Negative integers walk backwards. Fast Formula evaluates this purely as arithmetic on the underlying date column; there is no calendar consultation, no holiday awareness, no DST adjustment.
• WHILE 1=1 LOOP — Fast Formula has no native FOR-loop construct that increments by 1. The idiomatic pattern is an infinite WHILE with manual counter advance and an explicit EXIT when the termination condition is met. This is safer than WHILE (j <= 7) because the loop body decides when to leave — useful when there are multiple exit conditions.
• The implicit bound — since Friday occurs every seven days, the loop is guaranteed to exit within seven iterations. Fast Formula has no infinite-loop detection at compile time, so loops that could run forever (e.g., a typo in the condition) will hit the runtime governor and abort the rule with a vague "formula execution exceeded threshold" error. The seven-day natural ceiling here makes this pattern safe.

CALL_FORMULA Pattern for Worker Holiday Schedule Resolution

Knowing it's a Wednesday isn't enough. The formula has to know whether this Wednesday is also a public holiday — and the public holiday list lives in a Worker Holiday Calendar attached to the worker's LDG, not in any DBI the TCR formula can read directly.

The escape hatch is CALL_FORMULA, which invokes another Fast Formula and binds its inputs and outputs by name. The TCR delegates holiday resolution to a utility formula:

The syntax is non-obvious if you've never seen it before. The two arrow operators distinguish input bindings from output bindings:

Once the call returns, the formula reads l_holiday_count — set to 1 if the date falls on a gazetted public holiday for the worker's LDG, 0 otherwise — and uses that as the first fork in the day-type decision tree.

Inside the Called Formula — How Bindings Map to INPUTS and RETURN

For the CALL_FORMULA bindings to resolve cleanly, the called formula must declare its inputs and return its outputs using exactly the labels the caller specifies. Here's the skeleton of XX_HOLIDAY_LOOKUP_FF showing both sides of the contract:

Three contracts to notice between the caller and the called formula:

• Input names match the caller's bind labels. The caller writes l_holiday_cat > 'holiday_category', so the called formula must declare holiday_category in its INPUTS block with a matching type. Typos here fail at runtime, not at compile time.
• Output names match the caller's bind labels. The caller writes l_holiday_count < 'OUT_COUNT', so the called formula must have a local called OUT_COUNT and include it in the RETURN statement.
• Formula type must be compatible. Both the caller and the called formula here are Time Calculation Rules. Mismatched types compile but fail at runtime with a "formula not found in compatible type" error — one of the more frequent debugging dead-ends in custom utility design.

The GET_VALUE_SET call is the standard Fast Formula escape hatch for querying tables that aren't exposed as DBIs. The value set itself is configured under Setup and Maintenance → Manage Value Sets as a Table value set with the SQL that joins the calendar event repository against the worker's holiday eligibility profile. Keeping the SQL out of Fast Formula and inside the value set keeps the formula portable and lets the database optimizer cache the query plan.

Externalizing the Public Holiday Category via a Rule Input Parameter

The holiday category bind is the one parameter most engineers miss. The Worker Holiday Calendar can hold multiple categories of non-working days — gazetted public holidays, optional restricted holidays, bridge days, company-declared shutdowns. Each category has its own OT treatment.

The TCR formula reads which category counts as a "real" holiday for OT purposes from a rule input parameter, populated when the rule is configured under Setup and Maintenance → Manage Time Calculation Rules:

get_rvalue_text reads a text parameter off the rule definition by name ('HOLIDAY_CATEGORY_CODE'), falling back to 'PH' if the parameter wasn't configured. The same formula deployed for two different LDGs can therefore treat different calendar categories as the "real" holiday set — without recompiling.

This is one of the cleanest separations of policy and code in the Fast Formula language. The rule parameter is the policy. The formula is the engine. Reuse follows naturally.

The PER_ASG_FULL_PART_TIME Fork — Why Default 'X' Matters

Even after day-type is resolved, the formula has one more fork: part-time workers don't trigger the monthly threshold the same way full-time workers do. A part-timer's "normal" daily hours might be 4. A full-timer's might be 8. Both can rack up OT, but the bucket-fill mechanics differ.

The split is gated by PER_ASG_FULL_PART_TIME, a text DBI whose values are 'FULL_TIME' or 'PART_TIME' drawn from the worker's assignment record. Notice the default at the top of the formula:

The fallback is 'X', not ' '. The reason is deliberate. Both branches in the fork compare against specific values:

If the DBI is null and the default was a blank string, the comparison ' ' = 'FULL_TIME' returns false and the ELSE branch fires — silently routing a worker with missing data through the part-time path. 'X' as the sentinel makes the missing-data case explicit in logs — PER_ASG_FULL_PART_TIME: X in the rule execution log is a clear signal that the worker's assignment is incomplete and the calculation should be reviewed.

This is a small thing. It saves hours of debugging in production.

Day-Type Branching Flow — SAT, SUN, Weekday, and Public Holiday Paths

With all four constructs in hand — day-of-week, weekly compare, holiday count, full-time flag — the formula assembles them into a four-way fork. Once the call to CALL_FORMULA returns and l_holiday_count is populated, the decision tree fires:

The cascade is evaluated top-down with IF / ELSE IF / ELSE. The public holiday check is the outermost gate because a holiday that falls on a Sunday is still a holiday — the formula must not let Sunday's OT 200% logic override the holiday's OT 200% logic just because the comparison happens to produce the same multiplier. The two paths fill different output buckets downstream, which matters for payroll element mapping in Part 4.

Each branch ends by zeroing l_reg (regular hours), since worked hours on a non-regular day don't contribute to the weekly or monthly normal-time accumulator. Only the weekday branch can leave l_reg populated.

Why Use CALL_FORMULA Instead of Inlining the Holiday Lookup

A reasonable question — why route through a separate utility formula at all? The TCR could read the holiday calendar inline with array DBIs like PER_HOLIDAY_DATE_LIST and avoid the call overhead.

The separation pays for itself three ways:

• Reuse. The same holiday-resolution formula is called from absence accrual matrices, entry validations, and other TCR rules. Centralizing it means changes to the calendar interpretation (new category filters, date-range adjustments) ripple to all callers without touching them.
• Testability. The utility formula can be unit-tested by directly executing it from the Fast Formula UI with sample input values — much easier than running an entire TCR for one date.
• Compilation scope. Array DBIs like PER_HOLIDAY_DATE_LIST require specific contexts (LDG_ID, EFFECTIVE_DATE) that not every formula type supplies. Putting them in a dedicated formula with the right type isolates the context dependency to one place.

CALL_FORMULA has overhead — every call instantiates a new evaluation context. For a TCR that fires once per measure period entry, that overhead is negligible. For a payroll batch processing tens of thousands of assignments, you'd profile before relying on it heavily.