Oracle Fast Formula: Time Entry Rule (Part 4)

Fast Formula Time Entry Rule OTL Hands-On

May 21, 2026 • 15 min read • Oracle HCM Cloud

The TER Series Part 4 of 4

1. OTL Foundations · 2. The Input Contract · 3. Algorithm: Routing & Overlap · 4. The State Machine

The State Machine: Continuous-Hours Tracking, End to End
Part 4 of 4 — The TER Series

AM

Abhishek Mohanty

Oracle ACE Apprentice · AIOUG Member · Oracle HCM Cloud Consultant & Technical Lead

The first three posts covered what TER does, the input contract, and the first half of the algorithm. Now the final piece: the continuous-hours state machine that makes the whole formula stateful, plus the OTL configuration that has to exist for any of this to fire, plus a full end-to-end trace of Sarah's broken Tuesday timecard.

Continuous-Hours State Machine

This is the heart of the formula. The rule says "no worker shall log more than 6 hours of continuous Regular Hours without a meal break." Simple to state, easy to get wrong. You can't write it as a stateless IF inside the loop — the formula has to remember what came before.

The state diagram

Two states. Four transitions. Every line of Block 8's code maps to one of them.

Idle is the starting state. Active is where the formula spends most of its time when work is happening. The four transitions cover every case: a stretch begins, continues without a break, resumes after a gap, or ends because the worker took a meal break.

Why this is the formula's hardest concept

Most production bugs in continuous-hours validation come down to one of two mistakes. First: writing EXTEND as "start > prev stop" instead of "start = prev stop", which makes any tiny gap incorrectly extend the stretch. Second: forgetting that meal breaks force a RESET, which makes the formula keep counting after the worker has already eaten. Both pass UAT and fail audits months later. Remember the four transitions and you remember the rule.

The annotated code

With the state model in mind, the code reads like a direct translation of the diagram. Block 8a is the gate (which entries qualify). Block 8b is the EXTEND/RESTART decision. Block 8c computes hours. Block 8d compares against thresholds.

Block 8 · Continuous-hours tracker Annotated

/* gate — only count Reg Hours, real punches, no meal yet */
IF (aiTimeType = p_reg_type
    AND aiStartTime <> NullDate
    AND aiStopTime <> NullDate
    AND l_qty_only = 'N'
    AND l_meal_taken = 'N') THEN
(

Block 8a · Five-condition gate

Diagram for this annotation · Three concepts together

Part 1 · The legal rule — cap measures continuous work, not total daily hours

12 HOURS · WITH MEAL — OK

3h work

meal

9h work (post-meal)

Total: 12h. Continuous: 3h pre-meal.

Meal interrupted the count → legal cap not breached.

7 HOURS · NO MEAL — VIOLATION

7h continuous, no break

Total: 7h. Continuous: 7h.

Cap is 6h. Worker should have taken meal at 5h.

The cap measures uninterrupted work. A meal break is the legal reset trigger. Block 8a is where this rule meets the data.

Part 2 · The gate — five conditions, all must hold

Each condition rules out one specific case that shouldn't count:

1

aiTimeType = p_reg_type

filter to Reg Hours only — leave/holiday don't count toward the cap

2

aiStartTime <> NullDate

need a real start — can't measure stretch from a missing punch

3

aiStopTime <> NullDate

need a real stop — same reason

4

l_qty_only = 'N'

qty-only placeholders fail even though they have fake punches (00:00, 23:59)

5

l_meal_taken = 'N'

most consequential: meal already taken → gate locks for rest of day

If any one of the five fails → entry is silently skipped — no error, no warning, just continues to next iteration.

Part 3 · Why condition 5 locks the gate for the rest of the day

Gate state through Sarah's day:

Reg 09—12

GATE OPEN

Meal 12—13

flag flips 'Y'

Reg 13—15

GATE CLOSED

Reg 15—18

GATE CLOSED

Once l_meal_taken flips to 'Y' (when the meal entry is processed in Block 6e), every subsequent Reg Hours entry on the same day fails condition 5. The stretch tracker stops accumulating. New work after the meal is treated as a fresh shift — which it legally is.

1The legal rule this block enforces

• Across most labour jurisdictions, regulations cap uninterrupted work at 5 or 6 hours. After that point, a meal break is legally required — the worker must stop, eat, and rest before continuing.
• The cap measures continuous work, not total daily hours. A worker can log 12 hours total in a day without violating the cap, as long as those hours are split by an actual meal break in the middle. The break is what resets the count.
• This block is the formula's enforcement of that rule. Its job is to track the running length of the current uninterrupted stretch and fire warnings or errors when that stretch crosses the threshold.
• The challenge is figuring out which timecard entries actually count toward "continuous work" and which don't. Marker rows clearly don't. Meal breaks clearly don't. But what about qty-only placeholders? What about Reg Hours entries with missing punches? What about Reg Hours entries that come after the worker already took their meal?
• The five-condition gate is the formula's answer to all those questions. Each condition rules out one specific case that shouldn't count, and the AND combines them all into a single "this entry qualifies" check.

2The five conditions, each one earning its place

IF (aiTimeType = p_reg_type AND aiStartTime <> NullDate AND aiStopTime <> NullDate AND l_qty_only = 'N' AND l_meal_taken = 'N') THEN ...

• Condition 1: aiTimeType = p_reg_type. Only Regular Hours counts toward the cap. Annual Leave, Sick Leave, Public Holiday entries don't represent active work, so they shouldn't extend the stretch. This first check filters them out.
• Condition 2 & 3: Real start and stop times must be present. Without both endpoints, the formula can't measure duration — you can't compute a stretch from missing punches. These checks would normally be redundant after Block 6c (which flags missing punches as errors), but the defensive check here ensures Block 8 doesn't crash on data that Block 6c already flagged.
• Condition 4: l_qty_only = 'N'. Qty-only placeholders fail the gate even though they technically have punch times (00:00 and 23:59). They're not real work intervals, so they shouldn't accumulate against the legal cap. Block 6b detected the placeholder pattern and set this flag earlier in the same iteration.
• Condition 5: l_meal_taken = 'N'. Once a meal break has been logged anywhere on this day, the gate stays closed for every subsequent Reg Hours entry. This is the most consequential of the five conditions and deserves its own discussion below.
• All five conditions must be TRUE simultaneously for the entry to enter the stretch tracker. If any one fails, the entry is silently skipped — no error, no warning, just continues to the next iteration.

3Why l_meal_taken locks the gate for the rest of the day

• The reasoning is rooted in the legal definition. The continuous-work cap measures uninterrupted work before a meal break. Once the worker eats, they've satisfied the meal-break requirement — the legal counter resets in their favour. The pre-meal stretch is the only one that needed validating.
• What about the post-meal stretch? Could it also exceed 6 hours and need flagging? In theory yes, but in practice it doesn't happen in office environments — workers stop work at the end of the schedule, not 6+ hours after lunch. Manufacturing with double-shifts is different, but those LEs would configure their rules differently or implement custom logic.
• By locking the gate at l_meal_taken = 'Y', the formula trusts that the meal break did its legal job. Subsequent Reg Hours entries are tracked elsewhere (they still go into the day buffer for overlap testing in Block 7) but they're excluded from continuous-hours validation.
• The flag was set by Block 6e when the loop encountered a meal-break entry. It stays 'Y' through every subsequent iteration of the same day. Block 7c resets it to 'N' at the day boundary, giving tomorrow a clean slate.
• If your business case really does need post-meal stretches tracked separately (24-hour manufacturing with rotating breaks, perhaps), removing condition 5 from the gate is the architectural change. Don't carve out a special case in the middle of the algorithm; rethink the gate.

The gate is a bouncer at the door of the stretch tracker. Five conditions, all must hold, every one earning its place. Block 6's earlier work (qty-only detection, meal-break recognition) flows into this gate through shared flags — the formula coordinates across blocks through these shared signals, keeping each block's own logic focused.

  /* state transition: idle -> active, or continue */
  IF (inStretch = 'N') THEN
  ( stretchStart = aiStartTime
    stretchEnd   = aiStopTime
    inStretch    = 'Y'
  )
  ELSE
  ( IF (aiStartTime = stretchEnd) THEN
    ( stretchEnd = aiStopTime           // EXTEND
    )
    ELSE
    ( stretchStart = aiStartTime         // RESTART
      stretchEnd   = aiStopTime
    )
  )

Block 8b · State transitions

Diagram for this annotation · Four concepts together

Part 1 · The state machine — idle vs active, and when each path fires

Entry passes gate (Block 8a OK)

↓

inStretch = 'N' ?

YES (idle) →

START

stretchStart = ai · flag='Y'

NO (active) →

aiStartTime = stretchEnd ?

(when 'no')

YES (touches) →

EXTEND

stretchEnd = ai (start unchanged)

NO (gap) →

RESTART

stretchStart = ai · stretchEnd = ai

Two states (idle/active), three transitions inside Block 8b. Resets back to idle happen in Block 6e (meal) or Block 7c (END_DAY).

Part 2 · EXTEND — when the worker's punches touch (no gap)

EXAMPLE · EXTEND

Entry 1: 09—11

Entry 2: 11—13 (touches!)

0910111213

EXTEND fires

stretch: 09→13 (4h)

Part 3 · RESTART — when a gap appears (any size)

EXAMPLE · RESTART

Entry 1: 09—11

gap

Entry 2: 11:30—14

091011121314

RESTART fires

stretch: 11:30→14 (2.5h)

Part 4 · Why even a tiny gap forces restart — not a bug, a feature

The condition aiStartTime = stretchEnd is strict equality. A 1-minute gap (11:00 stop, 11:01 start) fails the equality check — treated as RESTART, not EXTEND. That's intentional: any deliberate gap signals the worker stopped working, even briefly. The state machine doesn't try to be lenient about "almost touching" because tolerance would let cumulative drift build up across many entries.

1The state machine, simplified

• The continuous-hours tracker is a small state machine with two states: idle (inStretch = 'N', no active stretch being measured) and active (inStretch = 'Y', currently tracking a stretch with known start and end).
• Once an entry passes the gate from Block 8a, the formula must decide what to do with it. The decision depends entirely on which state the tracker is currently in.
• From idle: any qualifying entry transitions to active. The new stretch begins at this entry's start time and currently ends at this entry's stop time. The flag flips to 'Y'.
• From active: there are two sub-decisions. If this entry continues the stretch seamlessly (its start matches the previous stop), extend. If there's a gap, restart from this new entry. Either way the tracker stays active.
• A meal break in Block 6e or an END_DAY in Block 7c forces the tracker back to idle by clearing the state variables. From there the cycle begins again.

2The EXTEND path: when work continues seamlessly

IF (aiStartTime = stretchEnd) THEN stretchEnd = aiStopTime // extend

• The EXTEND path fires when the new entry's start time exactly matches the previous stretch's end time. The worker stopped one Reg Hours entry and immediately started another, with no gap in between.
• The action is minimal: just move stretchEnd forward to this entry's stop time. The start stays where it was; the stretch grows by the duration of the new entry.
• Example: a worker logged Reg Hours 09:00–11:00, then logged 11:00–13:00 (perhaps they switched task codes at 11:00 but kept working). The stretch was 09:00–11:00 (2 hours). After EXTEND, it becomes 09:00–13:00 (4 hours). One unbroken run of 4 hours of work.
• This is the case where the formula correctly recognises that splitting a continuous work session into multiple Reg Hours rows (for cost-centre tracking, say) doesn't break continuity. The worker hasn't actually stopped working; they've just paused to change which project they're billing.

3The RESTART path: when there's a gap

ELSE stretchStart = aiStartTime // restart from here stretchEnd = aiStopTime

• The RESTART path fires when the new entry's start doesn't match the previous stop — there's a gap in time between them. The worker did something between the two entries that wasn't logged as Reg Hours.
• The action: discard the previous stretch entirely and begin a fresh one from this entry. Both stretchStart and stretchEnd get rewritten.
• Example: a worker logged Reg Hours 09:00–11:00, then logged 11:30–14:00. The 30-minute gap from 11:00 to 11:30 isn't accounted for — maybe a coffee break, maybe a chat with a colleague, maybe a personal phone call. The formula doesn't know and doesn't need to. The gap itself is enough proof that continuous work was interrupted.
• After RESTART, the stretch is 11:30–14:00 (2.5 hours), not 09:00–14:00 (5 hours). The earlier work isn't lost — it might have already triggered a warning when it completed at 11:00 — but it no longer accumulates against the new stretch.

4Why even a tiny gap forces a restart

• The legal cap measures continuous work, not total daily hours. The two are different. A worker can legitimately log 12 hours of total work in a day without violating any cap, as long as that work isn't continuous.
• What does "continuous" mean? The formula's interpretation: punches that don't touch each other are not part of the same stretch. If there's any visible gap between the previous stop and the next start, continuity is considered broken.
• This is a deliberately strict interpretation. A worker who logged 09:00–11:00, then 11:01–14:00 has only a 1-minute gap, but the formula still treats them as separate stretches. If you wanted to allow such tiny gaps to be ignored, you'd need to add a tolerance test (e.g. (aiStartTime - stretchEnd) < (5/1440) still extends, allowing 5-minute gaps to count as continuous).
• For most office work, the strict interpretation is correct — even small gaps imply some kind of break, and breaks are exactly what the legal cap is designed to encourage. If your business has different requirements (pure manufacturing, perhaps), the tolerance can be added with one additional check.
• Adding the two stretches together as if they were one would misrepresent reality. A worker who took a 30-minute break in the middle of their morning isn't doing the same thing as a worker who powered through 5 straight hours. The formula needs to distinguish those cases, and the strict gap-based reset is how it does.

Adjacent entries extend the stretch; gaps reset it. The strict interpretation of "continuous" reflects the legal definition, not just the arithmetic. If your jurisdiction allows small gaps to count as continuous (rare), add a tolerance test — but the default should be strict.

  /* compute span via Julian-day arithmetic */
  endMins = TO_NUMBER(TO_CHAR(stretchEnd, 'J'))*1440
            + TO_NUMBER(TO_CHAR(stretchEnd, 'HH24'))*60
            + TO_NUMBER(TO_CHAR(stretchEnd, 'MI'))
  stMins  = TO_NUMBER(TO_CHAR(stretchStart, 'J'))*1440
            + TO_NUMBER(TO_CHAR(stretchStart, 'HH24'))*60
            + TO_NUMBER(TO_CHAR(stretchStart, 'MI'))
  contHrs = (endMins - stMins) / 60

Block 8c · Cross-midnight safe

Diagram for this annotation · Four concepts together

Part 1 · Why naive same-day math breaks for graveyard shifts

// the naive approach
contMins = (stop_hour×60 + stop_min) - (start_hour×60 + start_min)

CASE A · SAME-DAY — works fine

08:30 → 14:45

(14×60 + 45) - (8×60 + 30)

= 885 - 510 = 375 mins

✓ 6.25 hours — correct

CASE B · CROSS-MIDNIGHT — breaks

23:00 → 03:00 (next day)

(3×60 + 0) - (23×60 + 0)

= 180 - 1380 = -1200 mins

✗ Negative 20 hours — nonsense

The bug survives UAT (test data is typically office hours) and surfaces in production with the first night-shift submission.

Part 2 · The fix — Julian Day Numbers turn dates into a continuous integer count

// the cross-midnight-safe approach
contMins = TO_NUMBER(TO_CHAR(stretchEnd,   'J'))×1440 + (h×60+m)
         - TO_NUMBER(TO_CHAR(stretchStart, 'J'))×1440 - (h×60+m)

'J' format mask — gives Julian Day Number (continuous count since 4713 BCE)

Part 3 · The graveyard-shift example, worked through

Stretch: 23:00 on day 100 — 03:00 on day 101 (next morning)

end_mins:     101 × 1440 + 3×60 + 0 = 145,620

stretch_mins: 100 × 1440 + 23×60 + 0 = 145,380

---

diff:        145,620 − 145,380 = 240 minutes

contHrs:    240 / 60 = 4 hours ✓ CORRECT

Notice the Julian Day numbers (100, 101) are arbitrary — the formula doesn't care about the absolute magnitude. The arithmetic just needs them to always increase as time moves forward, which they do, by definition.

Part 4 · The defensive-engineering value

One extra character per TO_CHAR call (the 'J' format mask). No measurable performance difference. Catches an entire class of opaque production bugs — the negative-minutes nonsense that surfaces with night-shift workers. Risk-reward is dramatically asymmetric in favour of always including it.

1The problem this block exists to solve

• The continuous-hours calculation needs to compute a single number: how many hours has the current stretch been running? In office-hours timecards (09:00–17:00), this is trivial — subtract the start time from the end time, you're done.
• But timecards aren't all office hours. A graveyard-shift worker might punch in at 23:00 (11 PM) and punch out at 03:00 (3 AM) the next day. That's 4 hours of work. Any formula that gets that wrong is going to fire false errors at exactly the workers least equipped to argue back.
• The naive approach — (stop_hour × 60 + stop_min) − (start_hour × 60 + start_min) — gives a wildly wrong answer for cross-midnight stretches. The arithmetic (3 × 60) − (23 × 60) = −1200 minutes — negative twenty hours, obviously broken.
• The naive approach works for any stretch contained within a single calendar day. It silently fails the moment the stretch crosses midnight. And the bug is exactly the kind that survives UAT (where test data is typically office-hours) and surfaces only in production once a night-shift worker submits their first timecard.

2Julian Day Numbers, explained

• The Julian Day Number is a continuous count of days since a fixed reference date in 4713 BCE. Every calendar day on Earth has its own Julian Day Number, and these numbers increase monotonically — today is one more than yesterday, no matter what month, year, or calendar system you're using.
• Oracle's TO_CHAR(date, 'J') format mask returns the Julian Day Number as a string. TO_NUMBER converts it to a numeric value the formula can do arithmetic on.
• The trick: combine the Julian Day with the time-of-day to produce a single absolute minute count. Multiply the day number by 1440 (the number of minutes in a day) and add the hours-and-minutes within that day. The result is a single number that uniquely identifies a moment in time and always increases as time moves forward.
• Two such numbers can be subtracted directly to get the elapsed minutes between them — regardless of whether they're on the same day, adjacent days, or even weeks apart. The maths just works.

3The graveyard-shift example, worked through

23:00 day 100 → 100×1440 + 23×60 = 145,380 mins 03:00 day 101 → 101×1440 + 3×60 = 145,620 mins ————— diff: 145,620 − 145,380 = 240 mins = 4 hours ✓

• 23:00 on Julian day 100 becomes 100 × 1440 + 23 × 60 + 0 = 145,380 minutes since the Julian epoch.
• 03:00 on Julian day 101 (the next day) becomes 101 × 1440 + 3 × 60 + 0 = 145,620 minutes since the same epoch.
• The difference is 240 minutes — exactly 4 hours. Correct.
• Notice the Julian day numbers (100, 101) are arbitrary — they happen to be small here for readability, but in real Oracle they'd be 7-digit numbers. The maths still works the same way; only the absolute magnitude changes.
• Try the same calculation for any other cross-midnight pair and you'll get the right answer every time. The formula doesn't need a special case for "is this stretch cross-midnight?" — the Julian arithmetic handles it uniformly.

4Why this safeguard is so often skipped

• The naive same-day calculation is easier to write and easier to read. It works for the vast majority of timecards your formula will ever see, because most workers don't have shifts that cross midnight.
• The Julian Day approach looks more complex on the page, even though it's only one extra character per TO_CHAR call (the 'J' format mask). Developers under deadline pressure often skip the safeguard because they can't immediately see when it would matter.
• Then a manufacturing client goes live with a night shift, or a 24/7 healthcare client adds graveyard rotations to their rollout. The formula breaks on day one of production for those workers, and the bug is opaque (negative minutes? what?) until someone with prior context recognises the pattern.
• The cost of including Julian arithmetic from day one is negligible — one extra character per call, no measurable performance difference. The cost of not including it is a production incident with a hard-to-diagnose bug. The risk-reward is dramatically asymmetric in favour of always including it.
• This is a small example of defensive engineering: pay tiny costs upfront to remove entire classes of bugs that would otherwise surface at the worst possible time. The pattern generalises beyond TER — any time math operation that might cross a boundary (midnight, year-end, daylight-saving) deserves the same treatment.

Cross-midnight safety costs one extra character per TO_CHAR call: the 'J' format mask. The performance impact is unmeasurable. The bug it prevents is opaque, hard to reproduce, and surfaces in production with night-shift workers — exactly when you can least afford it. Pay the cost upfront, every time.

  /* threshold check — error wins over warning */
  IF (contHrs > p_max_cont_err
      AND l_day <> 'SAT'
      AND l_day <> 'SUN'
      AND length(hol) = 0) THEN
  ( OUT_MSG[nidx] = ... p_msg_cont_err )
  ELSE
  ( IF (contHrs > p_max_cont_warn
        AND l_day <> 'SAT'
        AND l_day <> 'SUN'
        AND length(hol) = 0) THEN
    ( OUT_MSG[nidx] = ... p_msg_cont_warn )
  )
)

Block 8d · Error wins

Diagram for this annotation · Four concepts together

Part 1 · Two thresholds, two audiences — warning for worker, error for legal

clean

warn

error

0h2h4h5h (warn)6h (error)7h

WARNING · soft

Audience: the worker

"You've been working 5 hours straight — take a break soon." Doesn't block submission. Worker can ignore but is informed.

ERROR · hard

Audience: legal/compliance

"You've exceeded the 6-hour cap." Blocks submission. Worker must split the entry or add a meal break.

Part 2 · Why error wins — IF/ELSE structure ensures only one fires

// the structure
IF (contHrs > p_max_cont_err) THEN
  OUT_MSG[nidx] = error message
ELSE
  IF (contHrs > p_max_cont_warn) THEN
    OUT_MSG[nidx] = warning message

The nested IF/ELSE guarantees mutual exclusion: if the error condition fires, the warning branch isn't even checked. The worker sees the more severe message; the less severe one is suppressed.

Part 3 · What goes wrong with naive IF / IF instead

THE BUG — two independent IFs

IF (contHrs > p_max_cont_err)  THEN OUT_MSG[nidx] = error
IF (contHrs > p_max_cont_warn) THEN OUT_MSG[nidx] = warning

At 7 hours continuous: both conditions fire. First the error writes. Then the warning overwrites it. Worker sees only the warning — less severe message wins. Legal cap silently violated.

✗ The more dangerous error gets hidden by the milder warning.

Part 4 · The weekend & holiday short-circuits

Both threshold checks include three guard conditions:

• l_day <> 'SAT'
• l_day <> 'SUN'
• length(hol) = 0 (no public holiday)

Weekend and holiday work is governed by different rules — usually paid at premium and not subject to the same continuous-hours cap. These guards prevent false flags on those days.

TAKEAWAY: Use IF/ELSE structure when thresholds overlap. The order matters — check the more severe condition first. Add the weekend/holiday guards uniformly to every threshold check.

1Two thresholds, two purposes

• The continuous-hours validation has two distinct thresholds rather than one. The soft warning (default 5 hours, parameter p_max_cont_warn) gives the worker advance notice that they're approaching the legal cap. The hard error (default 6 hours, parameter p_max_cont_err) blocks submission entirely once the cap is exceeded.
• The two-tier design serves different audiences. The warning is for the worker — a heads-up that says "you're getting close to needing a meal break". It doesn't block submission; it just informs.
• The error is for legal compliance. Once the worker actually crosses the cap, the formula refuses to let the timecard through — not because the formula is being mean, but because the labor regulation forbids it.
• The gap between the two thresholds (warning at 5h, error at 6h) is deliberate. It gives the worker an hour of grace to wrap up what they're doing and take a break. A single threshold at the cap would be too abrupt; a single threshold at warning would be ineffective. Two thresholds with an hour of separation is the design that serves both audiences.

2The 6.25-hour scenario, in detail

• Picture a stretch that has grown to 6.25 hours. The worker started at 08:30 and hasn't taken a meal break; it's now 14:45 and they're still going.
• The stretch crosses the warning threshold (5 hours, at 13:30) and then crosses the error threshold (6 hours, at 14:30). At 6.25 hours, both conditions in the threshold check evaluate TRUE.
• Technically, both messages would apply. The worker has earned the warning ("approaching cap") and earned the error ("exceeded cap"). The formula now has a choice: surface both messages, or just one?
• Surfacing both creates noise. The worker sees two red markers on the same row and has to figure out which one to address. The error message ("exceeded") is more actionable than the warning ("approaching"), so the warning becomes redundant.
• The right behaviour is to surface only the more serious message — the error. The warning is implicitly subsumed (if you've exceeded the cap, you've also approached it). This is the principle the IF/ELSE structure enforces.

3Two parallel IFs vs one IF/ELSE

// WRONG — two parallel IFs IF contHrs > 6 THEN OUT_MSG[nidx] = "ERROR" IF contHrs > 5 THEN OUT_MSG[nidx] = "WARN" // at 6.25h: error written, then overwritten by warn // RIGHT — IF/ELSE IF contHrs > 6 THEN OUT_MSG[nidx] = "ERROR" ELSE IF contHrs > 5 THEN OUT_MSG[nidx] = "WARN" // at 6.25h: error written; warning branch skipped

• The wrong shape uses two independent IF statements. At 6.25 hours, the first IF fires and writes the error message into OUT_MSG[nidx]. Then the second IF fires (because 6.25 also exceeds 5) and overwrites the same slot with the warning message. The worker sees the warning, misses the error.
• This is exactly backwards. The worker is over the legal cap, but the message they see says they're approaching it. The misinformation is worse than no message at all.
• The right shape uses IF/ELSE. The first branch checks the more severe condition. If it matches, the error fires and the second branch is skipped entirely. The warning never gets a chance to overwrite the error.
• This single structural choice is the difference between a formula that almost-works and one that does what the legal team actually intended. The two versions look superficially similar — same conditions, same messages, same data — but their behaviour at the boundary case is opposite.
• The general principle: when multiple conditions can fire on the same row, ensure mutual exclusivity through IF/ELSE structure. Don't trust write-order to produce the right outcome — encode the priority directly in the control flow.

4Why both thresholds are suspended on weekends and holidays

• Both threshold checks include the same suppression conditions: l_day <> 'SAT' AND l_day <> 'SUN' AND length(hol) = 0. If the day is a weekend or a public holiday, neither error nor warning fires.
• The reasoning is legal. Most labor regulations explicitly exempt non-working days from continuous-work caps. The cap is designed to enforce rest during normal working hours; on a Saturday or a public holiday, normal working hours don't apply, and the cap doesn't either.
• The holiday check uses length(hol) = 0, where hol is a string containing the holiday name fetched from the holiday value set. If the day is a public holiday, hol contains the holiday name and has nonzero length; the condition fails and the threshold check is suppressed. If it's a regular workday, hol is empty, the condition passes, and the threshold check proceeds.
• The weekend check uses simple string comparison against the day name. If you're rolling out to a region where the weekend isn't Saturday-Sunday (some jurisdictions use Friday-Saturday or other configurations), this is one of the places to adjust — ideally by parameterising the weekend days so the formula stays portable.

Two thresholds, both suspended on non-working days, with IF/ELSE ensuring the more severe message wins when both conditions match. The structural choice between IF/ELSE and two parallel IFs is invisible in the code shape but decisive in worker experience — encode priority through control flow, not through write-order.

That's the algorithm in full. Setup runs once. The loop runs N times — classifying each row, buffering Reg Hours into the day buffer, advancing the stretch tracker, and at every END_DAY running pairwise overlap then resetting. Block 8 fires last on each row, comparing the running stretch against the soft and hard caps. Whatever flags accumulated across the run land in OUT_MSG, which the framework reads on return.

Setup Dependencies

The formula itself is one piece of a much larger picture. There's a layer of prerequisites that must exist before the formula will compile cleanly or fire correctly, and there's a six-step rule pipeline from the raw formula to a worker actually running it. Both layers matter: miss something in prerequisites and the formula compiles successfully but throws at runtime; miss something in the pipeline and the formula never reaches the worker.

Prerequisites — what must exist first

Before you even compile the formula, six artefacts must exist in the target environment. None of them are part of the formula source — they're separate setup items that the formula references.

Prerequisite	Where	Why the formula needs it
Custom messages registered	Setup and Maintenance → Manage Messages (Application = HXT)	Every get_output_msg('HXT', p_msg_xxx) call resolves a message name into translated text. The five XX_* messages must exist before runtime — the formula compiles fine without them but throws when a code path fires.
Payroll Time Type values	Setup and Maintenance → Manage Common Lookups → Lookup Type for payroll time types	The literal strings 'Regular Hours' and 'Meal Break' in p_reg_type and p_break_type must match actual configured payroll time types. If a worker's time card uses 'Reg Hrs' instead, the gate aiTimeType = p_reg_type never matches and every rule silently skips.
Public Holidays value set	Setup and Maintenance → Manage Value Sets	GET_VALUE_SET('XX_HOLIDAY_CALENDAR_VS', ...) looks up the holiday calendar at runtime. Value set must exist with the WHERE clause that scopes by date and legal entity. Test it with the pay_ff_functions.gvs() BIP query before attaching to the formula.
Profile option for rule logging	Setup and Maintenance → Manage Administrator Profile Values → ORA_HWM_RULES_LOG_LEVEL	Every add_rlog call in the formula writes to a buffer that's only persisted if logging is enabled. Set Site-level value to Fine or Finer in non-production. Without this, your debug logs vanish and the Analyze Rule Processing Details UI shows nothing.
Time Consumer Sets	Setup and Maintenance → Manage Time Consumer Sets	Tells the framework where validated time goes after rules run — Payroll, Project Costing, both, or neither. Without a consumer set linked to the worker's profile, time cards have nowhere to land even after the formula approves them.
Repeating Time Periods	Setup and Maintenance → Manage Repeating Time Periods	Defines the time card period (weekly, biweekly, monthly). Drives the END_PERIOD boundary marker in the input array. Use delivered periods or create custom ones — either way, one must be linked to the worker's processing profile.

The "compiles but throws" trap

Fast Formula's compile-time validation does not verify that referenced messages, lookup values, or value sets actually exist. Your formula will compile cleanly even if every XX_* message is missing — the failure surfaces only at runtime when that specific code path fires for a specific worker on a specific timecard. To verify before the formula lands in production, run: SELECT MESSAGE_NAME FROM FND_NEW_MESSAGES WHERE MESSAGE_NAME LIKE 'XX_%' AND APPLICATION_ID = (SELECT APPLICATION_ID FROM FND_APPLICATION WHERE APPLICATION_SHORT_NAME = 'HXT'). Should return all five names. Same approach for lookup values and value set existence.

The Rule Pipeline — six steps from formula to worker

Once prerequisites are in place, the formula travels through a six-step pipeline before a worker's time card actually runs through it. The most commonly missed step is Step 2: Rule Templates — you cannot create a Time Rule directly from a formula in OTL. The Rule Template is the bridge that exposes the formula's parameters and outputs to the rule-creation UI.

Step	Task	What to Set
1	My Client Groups → Time Management → Fast Formulas	Create the formula. Type = Time Entry Rules. Compile and verify no errors. The Manage Fast Formulas UI is plain-text — no syntax highlighting, no folding. Author your formula in a real editor and paste it in.
2	My Client Groups → Time Management → Rule Templates	Create a Time Entry Rule Template. Select the formula. Configure: Rule Classification (e.g., Business message), Reporting Level, Process Empty Time Card, Time Card Events That Trigger, Suppress Duplicate Messages Display. Configure each formula parameter (display name, value type, default value) and each output (display name, message severity for OUT_MSG — Information, Warning, or Error).
3	My Client Groups → Time Management → Rules	Create a Time Entry Rule from the template. This is where the actual parameter values live — SCHEDULE_START_HOUR=9, SCHEDULE_END_HOUR=18, MAX_CONTINUOUS_HRS_ERR=6, MAX_CONTINUOUS_HRS_WARN=5. These are what get_rvalue_number reads at runtime via rule_id. Different LEs use the same template with different rule values.
4	My Client Groups → Time Management → Rule Sets	Add the rule to a Time Entry Rule Set. The rule set bundles together all the validations that apply to a given worker population. Different LEs may share rules but bundle them into different sets.
5	My Client Groups → Time Management → Worker Time Processing Profiles	Attach the Rule Set to a Time Processing Profile, along with: Time Consumer Set, Repeating Time Period, default Payroll Time Type. The profile is what gets assigned to workers — it bundles every piece of OTL config they touch.
6	HCM Groups + Profile assignment (batch-driven via Evaluate HCM Group Membership)	Link workers to the Time Processing Profile via HCM Group membership. Run Evaluate HCM Group Membership for the date range. From this point, every timecard submission for matching workers runs through your formula.

Figure 07 · Setup Topology

How the prerequisites and pipeline connect

The formula sits in the middle. Prerequisites feed in from the left. The pipeline carries it out to the worker on the right.

Why Step 2 (Rule Template) is the most commonly missed

It's tempting to think "I have a Fast Formula, now I need to attach it to a worker." That skips the Rule Template, which is genuinely required. The template is what tells OTL: here is a formula, here are the parameters that need values when an admin creates a rule, here is what each output means, here is what severity OUT_MSG carries. Without it, the rule-creation UI has no way to render parameter fields or know how to interpret outputs.

Practically: the template defines what kind of rule this formula creates (the Rule Classification), and the rule defines the actual values. One template + many rules + many rule sets is the typical pattern for multi-LE rollouts. The template is reusable; the rule is entity-specific.

Why Step 6 (HCM Group + assignment) is the silent failure point

If the worker isn't linked to the Time Processing Profile through HCM Group membership, the formula never fires for them — silently. UAT testers usually have the profile manually assigned during testing, so this passes UAT cleanly. In production, the assignment is typically batch-driven by the Evaluate HCM Group Membership process. If the batch hasn't run, or the eligibility criteria excluded a population, those workers' timecards bypass the formula entirely. Submissions sail through with no validation, and the gap is invisible from the OTL side because there's no error — the formula simply never runs.

Validate Step 6 as part of go-live readiness, not just the formula compile. The query SELECT * FROM HWM_USER_TIME_PROCESSING_PROFILES WHERE PROFILE_ID = :your_profile_id should return rows for every worker who's supposed to be running this formula. If it doesn't, the HCM Group evaluation hasn't completed for them.

The Worked Example, End-to-End

Now that every block has been explained, here's how all of them work together on a real submission. We'll trace Sarah's timecard from the moment she clicks Submit through to the three error markers she sees on her screen.

The submission. Sarah's timecard for 14-Apr-2026 has four worker entries plus three system markers (HEADER, END_DAY, END_PERIOD). The framework hands the formula seven array slots in total, indexed [1] through [7].

Figure 06 · End-to-End · Gantt View

Sarah's Tuesday on a single 24-hour axis

All four worker entries laid out by index. Red shaded zones mark where the OVERLAP rule fires; coral pulse markers show which entry takes the flag.

FINAL OUT_MSG

OUT_MSG[3] = "Continuous work exceeds 6 hours ..."
OUT_MSG[4] = "Break outside working hours ..."
OUT_MSG[5] = "Overlapping entries ..."

What happens inside the loop, iteration by iteration

The formula runs through seven iterations of its WHILE loop, one per array index. Here's exactly what changes in each iteration:

Loop_Trace.xlsx Excel

Iter	Row	What the formula does	State after iteration
1	HEADER at [1]	Reads RECORD_POSITIONS[1] = 'HEADER'. The other arrays at [1] are empty — the .exists() guards skip those reads. No validation runs; no state changes.	Day buffer empty. Stretch tracker idle.
2	Reg Hours [2] 08:30–10:00	Block 6 reads the row. Block 6c confirms both punches present. Block 6d adds the entry to the day buffer. Block 8 starts a new stretch (1.5h, well under cap).	Day buffer = [(08:30, 10:00, idx 2)] Stretch = 08:30–10:00 (1.5h)
3	Reg Hours [3] 10:00–14:45	Block 6 reads the row. Block 6d adds it to the day buffer. Block 8 sees this entry's start (10:00) matches the previous stretch's end (10:00) — so it extends the stretch to 08:30–14:45 (6.25h). 6.25 > 6 → error fires on row [3]: "Continuous work exceeds 6 hours".	Day buffer has 2 entries. Stretch = 08:30–14:45 (6.25h, flagged) OUT_MSG[3] populated.
4	Meal Break [4] 19:00–20:00	Block 6 reads the row. Block 6e checks the meal break's window: 19:00–20:00 falls outside the 09:00–18:00 schedule. Error fires on row [4]: "Break outside working hours". Block 6e also flips l_meal_taken = 'Y'.	OUT_MSG[4] populated. Meal flag now 'Y'.
5	Reg Hours [5] 08:00–20:00	Block 6 reads the row. Block 6c confirms both punches present. Block 6d adds it to the day buffer. Block 8's gate is now closed (because l_meal_taken = 'Y') so the stretch tracker doesn't grow further.	Day buffer has 3 entries: [2], [3], [5].
6	END_DAY at [6]	Block 7 fires. The pairwise overlap test runs on the day buffer's three entries. Pair (2, 3) — touching at 10:00, no overlap. Pair (2, 5) — entry 5's range 08:00–20:00 contains entry 2's 08:30–10:00 → overlap. Pair (3, 5) — entry 5's range contains entry 3's 10:00–14:45 → overlap. Error fires on row [5] (the later entry in each conflicting pair): "Overlapping entries". Then Block 7c clears the day buffer, the stretch tracker, and the meal flag.	OUT_MSG[5] populated. All day-level state reset to empty.
7	END_PERIOD at [7]	The formula's WHILE loop reaches the end. RETURN out_msg_ary hands the populated array back to the framework.	Loop terminates.

Seven iterations, three errors, one return. Notice how each block's output (day buffer growth, stretch extension, meal flag) feeds into other blocks' decisions on later iterations.

The framework receives the array and renders three red error markers on Sarah's timecard, one beside each flagged row. The submission is blocked until she fixes all three.

Sarah sees three red error markers when the formula returns. She edits the times, removes the consolidated entry [5], moves the meal break to a real lunch slot, and resubmits. The formula re-runs from scratch on the corrected array. Clean OUT_MSG → submission accepted → timecard moves to approval.

Three layers.

Choose deliberately.

RECAP

Where this formula sits

"Validate first, calculate second." A clean separation between Layer 02 and Layer 03 keeps each formula focused and testable.

The TER formula is your last gate before bad data lands in the repository.

Build it once with care, parameterize the entity-specific values, and the same formula serves your whole rollout — one source of truth, configured per legal entity through rule parameters.

References

#	Source	What I used
1	Administering Fast Formulas — Time Entry Rule	Formula type contract, OUT_MSG output structure, framework arrays
2	Implementing Time and Labor — Validation Rules	Validation rule attachment, rule sets, processing profiles
3	Local labor regulation references (jurisdiction-specific)	Continuous-work caps, mandatory break requirements per locale
4	OTL Database Items Reference (REL11)	HWM_CTXARY_* prefix, .exists() patterns, sparse arrays

The TER Series · Complete

You now have the complete picture of how a production Oracle HCM Cloud TER formula works — from the OTL submission flow, through the input contract, through the algorithm and state machine, end to end. Bookmark this series as a reference for your next TER implementation.

---

AM

Abhishek Mohanty

Oracle HCM Cloud Consultant & Technical Lead — Fast Formulas, Time and Labor, Absence Management, Core HR, Redwood, HDL, OTBI.

Fast Formula Time Entry Rule OTL Time and Labor Oracle HCM Cloud