California Straight Time and Unpaid Time calculation - Time Calculation Rule

California overtime is notoriously two-layered: daily overtime (hours over 8 in a day) and weekly overtime (hours over 40 in a week) can both apply to the same timecard, and the two don't always agree on which hours should be paid at which rate. The tricky part shows up with partial weeks—such as a new hire starting mid-week or an employee terminating on a Wednesday—where a rigid "40 hours" weekly baseline no longer reflects what the employee was actually scheduled to work.

This post breaks down a production-tested pair of Oracle Time and Labor (OTL) formulas: XXGEN_US_CA_STRAIGHT_TIME_FF, a Time Calculation Rule that partitions reported hours into regular, straight-time surplus, and unpaid deficit buckets, and its companion helper formula, XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE, which dynamically resolves the employee's true scheduled hours for the period instead of relying on a hardcoded 40-hour limit.

📌 Fast Formula Technical Profile

Primary Formula	XXGEN_US_CA_STRAIGHT_TIME_FF (Time Calculation Rule)
Companion Utility	XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE (Workforce Management Utility)
Summation Level	TIMECARD (Whole period evaluation at submit/save)
Output Mappings	OUT_MEASURE_UNDER, OUT_MEASURE_STRAIGHT_TIME, OUT_MEASURE_UNPAID_TIME

---

Business Context & Architectural Highlights

• Dynamic Schedule Threshold: Instead of comparing worked hours against a flat 40 hours, the formula calls the companion schedule utility at the HEADER record position to resolve the employee's exact scheduled hours for the timecard window.
• Absence Credit Protection: Approved absence hours recorded on the timecard are accumulated alongside worked regular time, ensuring that paid leave reduces the remaining weekly schedule obligation rather than unfairly pushing worked time into uncompensated deficits.
• Unpaid Shortfall Identification: When an employee works fewer hours than contracted and has no approved leave to cover the gap, the formula isolates the unworked shortfall into an unpaid attribute for automated payroll docking or administrative audit.
• Bulk Array Performance: The formula processes the timecard in a single bulk loop across parallel context arrays (HWM_CTXARY_*), providing significant performance advantages over line-by-line recalculations.

Formula Array Inputs

Input Name	Description & Role
HWM_CTXARY_RECORD_POSITIONS	Flags the record type for each array index: HEADER, DETAIL, or END_PERIOD.
HWM_CTXARY_HWM_MEASURE_DAY	Daily measured hours associated with each record position.
measure	The reported duration hours being reclassified.
StartTime / StopTime	Period date-time boundaries passed on the HEADER row to query scheduled hours.
PayrollTimeType	Identifies worked time rows (e.g., Regular Hours) for inclusion in the weekly accumulation.
AbsenceType	Flags absence rows so approved leave credits toward the weekly schedule requirement.

Rule Header & Input Parameters

Parameter	Source	Purpose
hSumLvl	Get_Hdr_Text(rule_id, 'RUN_SUMMATION_LEVEL', 'TIMECARD')	Summation level the rule executes under (defaults to TIMECARD).
hExecType	Get_Hdr_Text(rule_id, 'RULE_EXEC_TYPE', 'CREATE')	Determines hCreateYn, flagging whether this execution is an entry creation run.
pCategoryId	get_rvalue_number(rule_id, 'WORKED_TIME_CONDITION', 0)	Rule parameter identifying which pay time type category represents eligible worked time.
pMaxHrs	get_rvalue_number(rule_id, 'DEFINED_LIMIT', 0)	Static limit configured on the rule. Note: In this dynamic formula, this value is logged for reference, but the schedule-fetched weekly_sch_hrs governs the threshold comparison.

Output Variables

Output Variable	Meaning & Target Classification
OUT_MEASURE_UNDER	Portion of each detail record's regular hours remaining under the schedule threshold (ordinary regular time).
OUT_MEASURE_STRAIGHT_TIME	Surplus hours logged beyond the dynamic weekly schedule, assigned to the last detail line in the period.
OUT_MEASURE_UNPAID_TIME	The deficit calculated when total regular hours and absences fail to reach the contracted schedule baseline.

---

Array Processing Walkthrough

1. Loop Initialization: The formula iterates through the timecard array from nidx = 1 up to wMaAry, evaluating the record position, measure, pay time type, and absence flags for each row.
2. HEADER Phase: Resets weekly_sch_hrs = 0 and calls XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE using the record's start/end boundaries. The returned total becomes the dynamic threshold for the timecard.
3. DETAIL Phase (Regular Hours): Adds the hours to running total calc_reg_hours_wk. If the cumulative sum crosses weekly_sch_hrs, the overflow is carved out as straight time (st_time_cur), the running total is capped, and the net regular portion is assigned to OUT_MEASURE_UNDER.
4. DETAIL Phase (Absence Entries): Approved absence hours also accumulate into calc_reg_hours_wk, ensuring authorized leave fulfills schedule requirements without altering absence balances.
5. END_PERIOD Phase: If accumulated hours met the schedule and straight time was generated, st_time is written to OUT_MEASURE_STRAIGHT_TIME[st_time_idx]. If reported hours fall short of the schedule, the deficit is output to OUT_MEASURE_UNPAID_TIME[st_time_idx].
6. Runaway Safety Guard: A hard stop raises an error if iterations exceed 1000, protecting against endless execution loops on malformed timecards.

Worked Scenarios

📈 SCENARIO A: SCHEDULE MET WITH SURPLUS (STRAIGHT TIME ACCRUAL)

Employee Assigned Schedule: 37.5 Hours. The employee logs 40 Hours of Regular Hours across the week.

Time Line	Reported	Running Total	Formula Outcome
Mon – Thu (4 × 8.0 hrs)	32.0 hrs	32.0 hrs (< 37.5)	OUT_MEASURE_UNDER = 32.0 hrs
Friday (8.0 hrs)	8.0 hrs	40.0 hrs (> 37.5)	Splits: 5.5 Regular (under) + 2.5 Straight Time stored
END_PERIOD Close	Total: 40.0 hrs	Met Schedule	OUT_MEASURE_STRAIGHT_TIME[st_time_idx] = 2.5 hrs

Result: The 2.5 hours worked beyond the 37.5-hour scheduled threshold are reclassified into straight time, while the initial 37.5 hours remain standard regular hours.

📉 SCENARIO B: SCHEDULE SHORTFALL (UNPAID TIME CALCULATION)

Employee Assigned Schedule: 37.5 Hours (7.5 hrs/day). The worker logs 3 days (22.5 Hours) and records no authorized leave for the remaining days.

Time Line	Reported	Running Total	Formula Outcome
Mon – Wed (3 × 7.5 hrs)	22.5 hrs	22.5 hrs (< 37.5)	OUT_MEASURE_UNDER = 22.5 hrs (kept as regular)
Thu – Fri	0.0 hrs	22.5 hrs (Deficit)	No hours entered
END_PERIOD Close	Total: 22.5 hrs	22.5 < 37.5	OUT_MEASURE_UNPAID_TIME[st_time_idx] = 15.0 hrs (37.5 − 22.5)

Result: The engine outputs 15.0 hours of unpaid time. When mapped in the Time Consumer Set to a payroll reduction element, this automates salary docking without manual recalculations.

---

Primary Formula: XXGEN_US_CA_STRAIGHT_TIME_FF

XXGEN_US_CA_STRAIGHT_TIME_FF.ff

/* +======================================================================+
   | Formula Name : XXGEN_US_CA_STRAIGHT_TIME_FF                          |
   | Formula Type : Time Calculation rule                                 |
   | Description  : Calculates straight time and unpaid hours using      |
   |                published weekly schedule as dynamic threshold.       |
   +======================================================================+ */

DEFAULT FOR HWM_CTXARY_RECORD_POSITIONS   is EMPTY_TEXT_NUMBER 
DEFAULT FOR HWM_CTXARY_HWM_MEASURE_DAY    is EMPTY_NUMBER_NUMBER 
DEFAULT FOR measure                       is EMPTY_NUMBER_NUMBER  
DEFAULT FOR StartTime                     is EMPTY_DATE_NUMBER
DEFAULT FOR StopTime                      is EMPTY_DATE_NUMBER
DEFAULT FOR PayrollTimeType               IS EMPTY_TEXT_NUMBER
DEFAULT FOR AbsenceType                   IS EMPTY_NUMBER_NUMBER 

INPUTS ARE 
  HWM_CTXARY_RECORD_POSITIONS,
  HWM_CTXARY_HWM_MEASURE_DAY,
  measure, 
  StartTime,
  StopTime,
  PayrollTimeType,
  AbsenceType  

ffs_id  = GET_CONTEXT(HWM_FFS_ID, 0)
rule_id = GET_CONTEXT(HWM_RULE_ID, 0)  
ffName  = 'XXGEN_US_CA_STRAIGHT_TIME_FF '
rLog    = add_rlog(ffs_id, rule_id, '>>> Enter - ' || ffName) 
 
NullDate     = '01-JAN-1900'(DATE)  
NullDateTime = '1900/01/01 00:00:00'(DATE)   
NullText     = '**FF_NULL**'

measure_period = GET_CONTEXT(HWM_MEASURE_PERIOD, 0)  

hSumLvl   = Get_Hdr_Text(rule_id, 'RUN_SUMMATION_LEVEL', 'TIMECARD') 
hExecType = Get_Hdr_Text(rule_id, 'RULE_EXEC_TYPE', 'CREATE')  
hCreateYn = 'N' 

IF (upper(hExecType) = 'CREATE') THEN ( 
  hCreateYn = 'Y' 
) 

pCategoryId = get_rvalue_number(rule_id, 'WORKED_TIME_CONDITION', 0) 
pMaxHrs     = get_rvalue_number(rule_id, 'DEFINED_LIMIT', 0) 

OUT_MEASURE_UNDER         = EMPTY_NUMBER_NUMBER
OUT_MEASURE_STRAIGHT_TIME = EMPTY_NUMBER_NUMBER
OUT_MEASURE_UNPAID_TIME   = EMPTY_NUMBER_NUMBER

wMaAry            = HWM_CTXARY_RECORD_POSITIONS.count   
nidx              = 0
st_time           = 0
st_time_cur       = 0
st_time_idx       = 0
calc_reg_hours_wk = 0
weekly_sch_hrs    = 0

WHILE (nidx < wMaAry) LOOP (   
  nidx          = nidx + 1  
  tcMeasure     = 0  
  tcMeasureDay  = 0  
  aiPTT         = NullText
  aiAbs         = 0
  aiRecPosition = HWM_CTXARY_RECORD_POSITIONS[nidx] 

  IF (MEASURE.exists(nidx)) THEN ( tcMeasure = MEASURE[nidx] )  
  IF (HWM_CTXARY_HWM_MEASURE_DAY.exists(nidx)) THEN ( tcMeasureDay = HWM_CTXARY_HWM_MEASURE_DAY[nidx] ) 
  IF (PayrollTimeType.EXISTS(nidx)) THEN ( aiPTT = PayrollTimeType[nidx] ) 
  IF (AbsenceType.EXISTS(nidx)) THEN ( aiAbs = AbsenceType[nidx] ) 

  /* 1. Resolve Contractual Schedule at Header */
  IF (aiRecPosition = 'HEADER') THEN (
    weekly_sch_hrs = 0
    IF (STARTTIME.exists(nidx)) THEN ( aiStartTime = STARTTIME[nidx] ) 
    IF (STOPTIME.exists(nidx))  THEN ( aiStopTime  = STOPTIME[nidx] ) 

    ctx_personId    = GET_CONTEXT(HWM_RESOURCE_ID, 0)
    ctx_subResource = GET_CONTEXT(HWM_SUBRESOURCE_ID, 0)
    ctx_start_date  = GET_CONTEXT(HWM_CTX_SEARCH_START_DATE, NullDate)
    ctx_end_date    = GET_CONTEXT(HWM_CTX_SEARCH_END_DATE, NullDate)

    CALL_FORMULA('XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE',
      aiStartTime    > 'start_date_override',
      aiStopTime     > 'end_date_override',
      weekly_sch_hrs < 'TotalHrs' DEFAULT 0)  
  )

  /* 2. Detail Accumulation: Regular Hours */
  ELSE IF (aiRecPosition = 'DETAIL' AND aiPTT = 'Regular Hours') THEN (
    calc_reg_hours_wk = calc_reg_hours_wk + tcMeasure
    IF (calc_reg_hours_wk > weekly_sch_hrs) THEN (
      st_time_cur       = calc_reg_hours_wk - weekly_sch_hrs
      st_time           = st_time + st_time_cur
      calc_reg_hours_wk = weekly_sch_hrs
    ) ELSE (
      st_time_cur = 0
    )
    OUT_MEASURE_UNDER[nidx] = tcMeasure - st_time_cur
    st_time_idx             = nidx
  )

  /* 3. Detail Accumulation: Authorized Leave */
  ELSE IF (aiRecPosition = 'DETAIL' AND aiAbs > 0) THEN (    
    calc_reg_hours_wk = calc_reg_hours_wk + tcMeasure
    IF (calc_reg_hours_wk > weekly_sch_hrs) THEN (
      st_time_cur       = calc_reg_hours_wk - weekly_sch_hrs
      st_time           = st_time + st_time_cur
      calc_reg_hours_wk = weekly_sch_hrs
    ) ELSE (
      st_time_cur = 0
    )
  )

  /* 4. Period Boundary: Reconcile Straight Time or Deficit */
  ELSE IF (aiRecPosition = 'END_PERIOD') THEN (
    IF (calc_reg_hours_wk = weekly_sch_hrs AND st_time > 0) THEN (
      OUT_MEASURE_STRAIGHT_TIME[st_time_idx] = st_time
    ) ELSE IF (calc_reg_hours_wk < weekly_sch_hrs AND calc_reg_hours_wk > 0) THEN (
      OUT_MEASURE_UNPAID_TIME[st_time_idx] = (weekly_sch_hrs - calc_reg_hours_wk)      
    )
  )

  IF (nidx > 1000) THEN (
    ex = raise_error(ffs_id, rule_id, 'Formula ' || ffName || ' terminated due to possible end-less loop.') 
  )     
)

rLog = add_rlog(ffs_id, rule_id, '<< Exit - ' || ffName) 

RETURN OUT_MEASURE_UNDER, OUT_MEASURE_STRAIGHT_TIME, OUT_MEASURE_UNPAID_TIME

---

Companion Formula: XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE

This helper utility is what enables dynamic thresholds: it inspects the employee's assigned work schedule for the exact period range and aggregates scheduled hours while filtering out non-working holiday entries (objType <> 'CAL').

XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE.ff

/* +======================================================================+
   | Formula Name : XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE                    |
   | Formula Type : WORKFORCE_MANAGEMENT_UTILITY                          |
   | Description  : Aggregates employee work schedule with holidays       |
   |                excluded to yield dynamic period threshold.           |
   +======================================================================+ */

DEFAULT FOR start_date_override(Date) IS '01-JAN-1900'(DATE)  
DEFAULT FOR end_date_override(Date)   IS '01-JAN-1900'(DATE)  

DEFAULT_DATA_VALUE FOR HWM_EMP_SCHD_MEASURE           IS 0 
DEFAULT_DATA_VALUE FOR HWM_EMP_SCHD_START_DATE_TIME   IS '01-JAN-1900'(DATE)  
DEFAULT_DATA_VALUE FOR HWM_EMP_SCHD_END_DATE_TIME     IS '01-JAN-1900'(DATE)  
DEFAULT_DATA_VALUE FOR HWM_EMP_SCHD_AVAILABILITY_CODE IS 'NA'
DEFAULT_DATA_VALUE FOR HWM_EMP_SCHD_OBJECT_TYPE       IS 'NA'

INPUTS ARE   
  start_date_override(DATE),
  end_date_override(DATE) 

ffs_id  = GET_CONTEXT(HWM_FFS_ID, 0) 
rule_id = GET_CONTEXT(HWM_RULE_ID, 0) 
ffName  = 'XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE'  

NullDate = '01-JAN-1900'(DATE)  
NullText = '**FF_NULL**' 

ctx_personId    = GET_CONTEXT(HWM_RESOURCE_ID, 0)
ctx_subResource = GET_CONTEXT(HWM_SUBRESOURCE_ID, 0)
ctx_start_date  = GET_CONTEXT(HWM_CTX_SEARCH_START_DATE, NullDate)
ctx_end_date    = GET_CONTEXT(HWM_CTX_SEARCH_END_DATE, NullDate) 

stDate  = trunc(start_date_override)
endDate = trunc(end_date_override) 

IF (start_date_override WAS DEFAULTED) THEN (
  stDate = trunc(ctx_start_date)
)  
IF (end_date_override WAS DEFAULTED) THEN (
  endDate = trunc(ctx_end_date)
)  
endDate = ADD_DAYS(trunc(endDate), 1)

max_loop  = 5000
EmpHrs    = 0 
TotalHrs  = 0
HrsSource = NullText
nidx      = 1

CHANGE_CONTEXTS(HWM_CTX_SEARCH_START_DATE = stDate, HWM_CTX_SEARCH_END_DATE = endDate) (  
  nidx      = 1
  HrsSource = 'EMP'
  
  WHILE (HWM_EMP_SCHD_MEASURE.EXISTS(nidx)) LOOP (
    EmpHrs  = HWM_EMP_SCHD_MEASURE[nidx] 
    SchAvl  = HWM_EMP_SCHD_AVAILABILITY_CODE[nidx]
    objType = HWM_EMP_SCHD_OBJECT_TYPE[nidx]
    
    /* Exclude unavailable days and public holidays */
    IF (SchAvl <> 'NVL' AND objType <> 'CAL') THEN (
      TotalHrs = TotalHrs + EmpHrs 
    )
    
    nidx = nidx + 1
    IF (nidx > max_loop) THEN ( 
      ex = raise_error(ffs_id, rule_id, 'Schedule query exceeded loop ceiling.')
    )  
  )
)   

RETURN TotalHrs, HrsSource

---

Rule Configuration in Time and Labor

The primary formula is attached to a Time Calculation Rule with summation set to Time Card. The companion formula XXGEN_UTIL_GET_EMPLOYMENT_SCHEDULE must be compiled first so the subroutine call resolves seamlessly during execution.

Figure 1: Configuring Time Calculation Rule parameters and mapping output groups to payroll time types.

Validation & Test Checklist

• Standard Full Week: Verify that hours exceeding the contractual schedule cleanly spill into OUT_MEASURE_STRAIGHT_TIME.
• Partial-Week Proration: Simulate mid-week new hires or terminations to confirm that weekly_sch_hrs evaluates against the active work window rather than a blanket 40 hours.
• Absence Combination: Log a combination of worked days and vacation/sick leave to ensure leaves correctly absorb the scheduled threshold requirement.
• Public Holiday Verification: Verify that holiday entries (objType = 'CAL') are correctly excluded from total scheduled hours.

Conclusion

Flat weekly thresholds fail whenever nonexempt staff, alternative schedules, or partial-week events occur. By combining a bulk Time Calculation Rule with a schedule-query utility, organizations achieve fair, auditable straight time allocations and automated unpaid shortfall deductions across all workforce variations.