A TCR formula doesn't run once per worker. It runs once per measure period entry — and a single timecard week can produce dozens of those entries (start/stop pairs, breaks, absences, end-of-day markers, period summaries). The formula has to walk all of them, allocate hours into the right output buckets, and emerge with consistent totals.

The walking is done with a WHILE loop over Oracle's HWM_CTXARY_* array database items. The arrays are parallel: position 1 in one array refers to the same timecard entry as position 1 in every other array. That single design choice shapes how the rest of the formula reads.

This post walks through the iteration mechanics: how the arrays are sized, how positions are accessed safely, what the aiRecPosition phase marker means at each step, and why every well-written TCR closes the loop with a defensive raise_error trap.

The HWM_CTXARY Array DBI Family — Per-Entry Data Surfaced as Parallel Tracks

Oracle's Time and Labor framework exposes the current measure period's data to the formula as a set of indexed array DBIs, all named with the HWM_CTXARY_ prefix (HWM = Workforce Management, CTXARY = context array). Common members of the family:

• HWM_CTXARY_RECORD_POSITIONS — text array; each cell holds a phase marker like 'DETAIL', 'END_DAY', or a higher-level boundary marker.
• HWM_CTXARY_HWM_MEASURE_DAY — number array; the day-of-period to which each entry belongs (1, 2, 3 …).
• Per-entry input variables — measure, StartTime, StopTime, PayrollTimeType, AbsenceType — declared in the INPUTS ARE block and exposed as arrays.

All of them share the same indexing. Position 1 of RECORD_POSITIONS tells you the phase of timecard entry 1; position 1 of StartTime tells you when that same entry began; position 1 of measure tells you its hour value. The loop's job is to step through positions 1 through N and assemble a per-entry picture from these parallel tracks.

Array Cardinality with .count and the WHILE Loop Skeleton

Fast Formula array DBIs expose a .count method that returns the number of populated positions. The TCR captures this once at the top and uses it as the loop's upper bound:

A few details worth flagging:

• 1-based indexing. Fast Formula arrays start at index 1, not 0. nidx = 0 with increment-then-access inside the loop yields positions 1, 2, 3 ... wMaAry — the increment happens before any array access, which is the safe pattern.
• Capture .count once. Reading HWM_CTXARY_RECORD_POSITIONS.count inside the loop condition would force the DBI to resolve on every iteration. Capturing it in wMaAry beforehand cuts the DBI resolution cost to one call. Oracle's own Fast Formula performance guidance specifically calls this out.
• No FOR loop in Fast Formula. The language has no FOR i IN 1..N construct. WHILE with manual counter advance is the idiomatic substitute. Forgetting the increment is the most common cause of runaway loops — which is exactly the failure mode the raise_error guard at the end of the loop catches.

Null-Safe Array Access with the .exists() Method

Parallel arrays don't always carry data at every position. Look back at the visualization: at nidx=3 the position is 'END_DAY' — a phase marker — but measure, StartTime, StopTime have no values there. Reading measure[3] directly would raise "no data found" and abort the rule.

The defensive read is the .exists() method, which returns true only when the array has a populated cell at the given index:

A common shortcut is to assume that if RECORD_POSITIONS exists at a given index, all parallel arrays must too. Don't. Phase markers like END_DAY and END_PERIOD deliberately leave the data tracks empty. .exists() is cheap; the runtime error from a wrong assumption is not.

The aiRecPosition Phase Markers — DETAIL, END_DAY, and Period Boundaries

The single most important value in the loop is aiRecPosition. It tells the formula what kind of position the current nidx represents, and therefore which branch of the formula's logic should run:

The canonical phase-branch inside the loop reads like this:

Note the aiMeasureDay > 0 guard alongside the DETAIL check. Some installations populate DETAIL phase markers with placeholder rows that have no actual measure day — typically header or pre-allocation records. Skipping those keeps the allocation logic from running against null data.

The END_DAY Reset Pattern — Why l_total Returns to Zero Mid-Iteration

The l_total reset on END_DAY is one of those patterns that looks wrong at first glance. Why zero the daily total in the middle of a loop?

Because l_total is a per-day accumulator, not a per-period one. It builds up during the DETAIL entries of one calendar day, gets compared against the daily threshold to determine OT, and must start fresh on the next day's DETAIL entries. The END_DAY position is the only safe place to do the reset, because:

• Resetting inside the DETAIL branch would zero out partial-day accumulations before they were used.
• Resetting at the start of the next day's first DETAIL would require knowing it's the first one, which means tracking yet another flag.
• Resetting on every iteration would prevent any same-day accumulation at all.

The END_DAY marker exists precisely so the framework can give formulas a cheap, deterministic reset point. Period-level accumulators (l_period_regular, l_period_night_total) are deliberately not reset on END_DAY — they keep accumulating across days and only end with the formula execution itself.

The Infinite-Loop Guard — raise_error at nidx > 1000

Fast Formula has no compile-time loop termination analysis. A WHILE loop whose increment line is accidentally inside an IF branch that never fires will run until the runtime governor terminates the rule with a vague "formula execution exceeded threshold" error — long after the timecard submission has already failed for the user.

A defensive ceiling check inside the loop catches this earlier, with a useful error message:

Why 1000? It's a sanity ceiling, not a hard requirement. A single worker's measure period entries rarely exceed 200 in normal use (semi-monthly period × multiple daily entries × phase markers). Anything past 1000 means something is wrong upstream — either the loop counter isn't advancing, or the array has unexpected entries.

Four practical notes on raise_error:

• It takes ffs_id and rule_id as required parameters so the OTL audit log can attribute the error to the specific Fast Formula session and rule instance.
• The return value (ex) is assigned but never used — the call's side effect is the error raise. Some installations omit the assignment; both work.
• The error message string is what the worker sees in the timecard submission failure. Concatenating ffName into it makes diagnosis dramatically faster when multiple TCRs are chained together.
• Once raised, raise_error terminates the entire formula execution. Logs written via add_log before the raise are flushed to the audit trail; logs after are lost.

The Complete Loop Skeleton

Putting all five elements together — array cardinality capture, the WHILE bound, increment-before-access, .exists()-guarded reads, phase-branch logic, daily reset, and the runaway guard:

Every line earns its place. The cardinality capture cuts DBI calls. The 1-based increment-then-access avoids off-by-one errors. The .exists() guard prevents no data found crashes on phase-marker rows. The DETAIL+aiMeasureDay compound condition keeps allocation logic from running against placeholders. The END_DAY reset prevents accumulator bleed-through. The 1000-iteration ceiling catches runaway loops before the runtime governor does.

Strip any one of these out and the TCR still compiles. Skip enough of them and it will quietly produce wrong totals in production — the failure mode the next post in this series picks up.