1. Administering Fast Formulas
  2. DAVE_FIND_TIME_GAP Function

DAVE_FIND_TIME_GAP Function

To look back at previous time cards and find the specified gap between time entries based on the start or stop time of each time entry, use the DAVE find time gap function.

Parameters

Parameter Data Type Comments
resource_id NUMBER Resource ID (Person ID ) is available as a context value for current time card: GET_CONTEXT(HWM_RESOURCE_ID, 0)
max_lookback_days NUMBER The number of days that the function looks back to find the gap. The number must be more than 0 and less than 91. Valid values: ( 1 to 90)
gap_date_compare_type TEXT

Indicates which dates are compared to determine the gap.

Valid values: (START_START, START_STOP, STOP_START, STOP_STOP)

  • START_START: Compares the start time of one time entry to the start time of another time entry
  • START_ STOP: Compares the start time of one time entry to the stop time of another time entry
  • STOP_START: Compares the stop time of one time entry to the start time of another time entry
  • STOP_STOP: Compares the stop time of one time entry to the stop time of another time entry.
gap_limit_minutes NUMBER The number of minutes between 2 time entries.
first_detail_start DATE The start date and time of the current time card entry that the function uses as the point to begin looking back for the gap. The function begins with the time card that includes the date and start time, and looks back through older time cards until if finds the gap or reaches the maximum days to look back.
first_detail_stop DATE The stop date and time of the current time card entry that the function uses as the point to begin looking back for the gap. The function begins with the time card that includes the date and stop time, and looks back through older time cards until if finds the gap or reaches the maximum days to look back.
rec_grp_type TEXT

For the record group select reported or calculated values

Values: (REPORTED, CALCULATED)
time_card_ui_type TEXT In memory processing

Time values: (REPORTED, CALCULATED)
time_card_status TEXT

The time card status could include one or more of these values, separated by comma: (SUBMITTED, APPROVED, SAVED)

A Null or blank status means select all values
time_Category_id TEXT

Filter selection results by the time category ID:

  • Use the category ID selected for the main rule using the parameter type "Time category" (TIME_CATEGORY), or
  • Select a specific time category for this function using the parameter type "Function time category" (ORA_HWM_FF_CAT), or
  • Include all time card entries (no filter by time category) by setting this parameter to Null
assignment_Ids_Filter TEXT

Filter selection results by one or more assignment IDs (subresource ID):

  • To include all assignments (no filter by assignment), set this parameter to Null
  • To filter by more than one assignment ID, use a comma to separate values
  • The current assignment ID is available from the formula input HWM_CTXARY_SUBRESOURCE_ID [idx]

Outputs

Parameter Data Type Comments
Status TEXT

Request status, possible values are:

  • 'NO_REC_FOUND'
  • 'SUCCESS'
  • 'FAILED'
status_log TEXT The log file containing detailed information related to the 'FAILED' status and corresponding process data.
gap_found_in_minutes NUMBER

Values:

  • Less than zero (-1): No gap found
  • Greater than zero: Number of minutes between Gap_before_...and gap_after_...dates, based on gap_date_compare_type.
gap_before_start DATE Start date and time of the time entry before the gap
gap_before_stop DATE Stop date and time of the time entry before the gap
gap_after_start DATE Start date and time of the time entry after the gap
gap_after_stop DATE Stop date and time of the time entry after the gap

Example

Sample input parameters:

MAX_LOOKBACK_DAYS = 30
GAP_DATE_COMPARE_TYPE = STOP_START
GAP_LIMIT_MINUTES = 1440 Minutes (24 hours * 60)
FIRST_DETAIL_START = 4/22/18 8:00 AM
FIRST_DETAIL_STOP = 4/22/18 5:00 PM

Using the sample input parameters, the DAVE_FIND_TIME_GAP function returns these values:

GAP_FOUND_IN_MINUTES = 1620 Minutes (27 hours * 60)
GAP_BEFORE_START = 4/6/18 7:00 AM
GAP_BEFORE_STOP = 4/6/18 4:00 PM
GAP_AFTER_START = 4/7/18 7:00 PM
GAP_AFTER_STOP = 4/7/18 10:00 PM

The values

Gap in Hours
Date Start Time Stop Time START_START START_STOP STOP_START STOP_START
Mon, Apr 02, 2018 4/2/18 8:00 AM 4/2/18 11:00 AM NA NA NA NA
Tue, Apr 03, 2018 4/3/18 9:30 AM 4/3/18 11:20 AM 25.50 27.33 22.50 24.33
Wed, Apr 04, 2018 4/4/18 7:00 PM 4/4/18 10:00 PM 33.50 36.50 31.67 34.67
Thu, Apr 05, 2018 4/5/18 8:00 AM 4/5/18 5:00 PM 13.00 22.00 10.00 19.00
Fri, Apr 06, 2018 4/6/18 7:00 AM 4/6/18 4:00 PM 23.00 32.00 14.00 23.00
Sat, Apr 07, 2018 4/7/18 7:00 PM 4/7/18 10:00 PM 36.00 39.00 27.00 30.00
Sun, Apr 08, 2018 4/8/18 8:00 AM 4/8/18 5:00 PM 13.00 22.00 10.00 19.00
Mon, Apr 09, 2018 4/9/18 4:00 PM 4/9/18 10:00 PM 32.00 38.00 23.00 29.00
Tue, Apr 10, 2018 4/10/18 10:00 AM 4/10/18 7:00 PM 18.00 27.00 12.00 21.00
Wed, Apr 11, 2018 4/11/18 3:00 PM 4/11/18 9:00 PM 29.00 35.00 20.00 26.00
Thu, Apr 12, 2018 4/12/18 5:00 AM 4/12/18 4:00 PM 14.0 25.00 8.00 19.00
Fri, Apr 13, 2018 4/13/18 3:00 PM 4/13/18 7:00 PM 34.00 38.00 23.00 27.00
Sat, Apr 14, 2018 4/14/18 6:00 PM 4/14/18 10:00 PM 27.00 31.00 23.00 27.00
Sun, Apr 15, 2018 4/15/18 7:00 PM 4/15/18 10:00 PM 25.00 28.00 21.00 24.00
Mon, Apr 16, 2018 4/16/18 8:00 AM 4/16/18 4:00 PM 13.00 21.00 10.00 18.00
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA
Tue, Apr 17, 2018 4/17/18 1:00 PM 4/17/18 6:00 PM NA NA NA NA