Validating Overlapping Time Entries in Oracle Fusion Time and Labor with a Fast Formula
Introduction
Employees frequently enter time on timecards with overlapping start and stop times, for example one entry from 09:00–13:00 and another from 12:00–17:00. Left unchecked, this inflates hours and causes payroll and compliance issues. In this post I share a Time Entry Rule (TER) Fast Formula that detects overlapping DETAIL time records and raises an error on each affected row.
Business Scenario
The requirement was to stop a timecard from being saved or submitted when two worked-time entries overlap, while ignoring absence entries. The formula compares every pair of detail rows once and flags both rows in each overlapping pair so the user can see exactly which entries need correction.
Formula Details
| Item | Value |
|---|---|
| Formula Name | XX_VALIDATE_TIME_OVERLAP_RULE_FF |
| Formula Type | Time Entry Rule (TER) |
| Purpose | Throws an error when two DETAIL records overlap based on Start/Stop times |
Inputs
| Input | Description |
|---|---|
HWM_CTXARY_RECORD_POSITIONS | Array of record positions (e.g. DETAIL) for each row |
StartTime | Start time of each entry |
StopTime | Stop time of each entry |
PayrollTimeType | Payroll time type of each entry |
AbsenceType | Absence type of each entry (0 / empty for worked time) |
Rule Template Parameters
| Parameter | Description |
|---|---|
MESSAGE_CODE | Message returned on overlap (default XX_TIME_OVERLAP_MSG) |
WORKED_TIME_CONDITION | Time category ID for worked time (default 0) |
How It Works
- Defaults and inputs: Default values are declared for every input so the formula never fails on missing data.
- Context and parameters: The formula reads the formula and rule IDs from context, then fetches the message code and time category from the rule template.
- Outer loop: It iterates through each row. Only
DETAILrows that are not absences are evaluated. - Normalize times: Start and stop times are truncated to the minute with
TRUNC(..., 'MI')so seconds do not cause false results. - Inner loop: Each row is compared only with subsequent rows (
jidx > nidx), which avoids duplicate comparisons. The absence type is read for each compared row, and absence rows are skipped while the loop continues through the remaining records. - Overlap test: Two entries overlap when
currStart < otherStopANDcurrStop > otherStart. Entries that merely touch (one ends exactly when the next begins) are not treated as overlapping. - Error output: The message from
GET_OUTPUT_MSGis written to the output array at both row positions, and the array is returned. - Logging:
ADD_RLOGwrites entry, comparison, error, and exit messages, which makes troubleshooting through the rule log straightforward.
The Fast Formula
/* +======================================================================+
| Formula Name : XX_VALIDATE_TIME_OVERLAP_RULE_FF |
| Formula Type : Time Entry Rule (TER) |
| |
| Description : |
| Validates overlapping time entries based on Start/Stop times. |
| Throws error when two DETAIL records overlap. |
+======================================================================+ */
/* ===============================
Default Values
=============================== */
DEFAULT FOR HWM_CTXARY_RECORD_POSITIONS IS EMPTY_TEXT_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,
StartTime,
StopTime,
PayrollTimeType,
AbsenceType
/* ===============================
Context
=============================== */
ffs_id = GET_CONTEXT(HWM_FFS_ID, 0)
rule_id = GET_CONTEXT(HWM_RULE_ID, 0)
NullDateTime = '1900/01/01 00:00:00'(DATE)
RecPositionDetail = 'DETAIL'
/* ===============================
Rule Template Parameters
=============================== */
pMsgcd = get_rvalue_text(rule_id, 'MESSAGE_CODE', 'XX_TIME_OVERLAP_MSG')
pTimeCatId = get_rvalue_number(rule_id, 'WORKED_TIME_CONDITION', 0)
/* ===============================
Header Values
=============================== */
sum_lvl = Get_Hdr_Text(rule_id, 'RUN_SUMMATION_LEVEL', 'DETAIL')
/* ===============================
Logging
=============================== */
l_status = add_rlog (
ffs_id,
rule_id,
'Enter: XX_VALIDATE_TIME_OVERLAP_RULE_FF'
)
/* ===============================
Initialize
=============================== */
out_msg_ary = EMPTY_TEXT_NUMBER
max_ary = HWM_CTXARY_RECORD_POSITIONS.COUNT
nidx = 0
/* ===============================
Main Loop
=============================== */
WHILE (nidx < max_ary) LOOP
(
nidx = nidx + 1
currPos = HWM_CTXARY_RECORD_POSITIONS[nidx]
aiAbs_nidx = 0
IF (AbsenceType.EXISTS(nidx)) THEN ( aiAbs_nidx = AbsenceType[nidx] )
IF (currPos = RecPositionDetail and aiAbs_nidx = 0) THEN
(
currStart = NullDateTime
currStop = NullDateTime
IF (StartTime.EXISTS(nidx)) THEN
(
currStart = trunc(StartTime[nidx],'MI')
)
IF (StopTime.EXISTS(nidx)) THEN
(
currStop = trunc(StopTime[nidx],'MI')
)
jidx = 0
WHILE (jidx < max_ary) LOOP
(
jidx = jidx + 1
aiAbs_jidx = 0
IF (AbsenceType.EXISTS(jidx)) THEN ( aiAbs_jidx = AbsenceType[jidx] )
/* Compare only subsequent, non-absence rows to avoid duplicates */
IF (jidx > nidx AND aiAbs_jidx = 0) THEN
(
comparePos = HWM_CTXARY_RECORD_POSITIONS[jidx]
IF (comparePos = RecPositionDetail) THEN
(
otherStart = NullDateTime
otherStop = NullDateTime
IF (StartTime.EXISTS(jidx)) THEN
(
otherStart = trunc(StartTime[jidx],'MI')
)
IF (StopTime.EXISTS(jidx)) THEN
(
otherStop = trunc(StopTime[jidx],'MI')
)
l_status = add_rlog(
ffs_id,
rule_id,
'Compare Row=' || TO_CHAR(nidx) ||
' [' || TO_CHAR(currStart) ||
' - ' || TO_CHAR(currStop) || ']'
||
' With Row=' || TO_CHAR(jidx) ||
' [' || TO_CHAR(otherStart) ||
' - ' || TO_CHAR(otherStop) || ']'
)
/* ===============================
Overlap Logic
=============================== */
IF (
currStart < otherStop
AND
currStop > otherStart
)
THEN
(
msg_txt = GET_OUTPUT_MSG('HWM', pMsgcd)
out_msg_ary[nidx] = msg_txt
out_msg_ary[jidx] = msg_txt
l_status = add_rlog(
ffs_id,
rule_id,
'ERROR Overlap detected Row=' ||
TO_CHAR(nidx) ||
' overlaps Row=' ||
TO_CHAR(jidx)
)
)
)
)
)
)
)
/* ===============================
Exit
=============================== */
l_status = add_rlog (
ffs_id,
rule_id,
'Exit: XX_VALIDATE_TIME_OVERLAP_RULE_FF'
)
RETURN out_msg_arySetup Steps
- Create the message
XX_TIME_OVERLAP_MSGin the HWM application (Manage Messages) with your error text. - Create the Fast Formula of type Time Entry Rule and paste the code above.
- Create or edit a time entry rule template and reference the formula, setting the
MESSAGE_CODEandWORKED_TIME_CONDITIONparameters. - Add the rule to a rule set and attach it to the relevant time processing profile.
- Enter overlapping entries on a timecard and review the rule log to confirm the behavior.
Tips and Considerations
- Use the rule log (
ADD_RLOGoutput) while testing; remove or reduce verbose logging in production for performance. - Entries with a missing start or stop time default to
1900/01/01, so review how incomplete entries should be handled in your implementation. - Parameters such as
pTimeCatIdandPayrollTimeTypeare read but not used in the overlap test. They are available for extending the logic, for example to restrict checks to specific time categories. - Complexity is O(n²) per timecard, which is fine for typical daily entry counts.
Conclusion
This formula gives timekeepers immediate, row-level feedback on overlapping entries and keeps bad data out of payroll. It can be extended to filter by time category, project, or payroll time type. I hope it helps you in your Oracle Fusion Time and Labor implementations. Feedback and enhancements are welcome in the comments.
No comments:
Post a Comment