In this article we will look into how to use the Flow Schedule Fast Formula to schedule the HCM Extracts at adhoc frequencies which cannot be handled by the seeded options of Daily/Weekly/Monthly frequencies.
Table of Contents
Flow Schedule Fast Formula
This type of Fast Formula is used to create custom schedules for HCM Extracts. By default, Oracle provides Daily/Weekly/Monthly schedules. However, if there is a requirement to schedule the extract at hourly interval/alternate days/weekdays only then we will have to write the custom Fast Formula and use it to schedule HCM Extracts. This formula takes the input parameters as SCHEDULED_DATE and returns the NEXT_SCHEDULED_DATE.
Sample 1: Schedule every 30 minutes
Logic to schedule Extracts every 30minutes. We must add the 30 minutes to the input SCHEDULED_DATE. We need to convert the minutes into days. 30 / (24 * 60) = 0.02083
/*********************************************************************
FORMULA NAME: Flow Schedule
FORMULA TYPE: Flow Schedule
DESCRIPTION: This runs every 30 mins
Returns NEXT_SCHEDULED_DATE;
Formula Results :
NEXT_SCHEDULED_DATE This will be a date time value with yyyy-MM-dd HH:mm:ss format.
*********************************************************************/
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Calculations */
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,0.02083)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
Sample 2: Schedule every 30 min between 8AM to 8PM EST.
Logic to schedule from 8AM to 8PM EST for every half an hour. We need to convert time zone into UTC and do the comparison.
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Calculations */
/* Logic to schedule next day at 8AM if current run is 8PM */
if ( to_number(to_char(SCHEDULED_DATE,'HH24'))=0 and to_number(to_char(SCHEDULED_DATE,'mi'))=0 ) Then
(
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,1/2)
)
/* Logic to schedule every half an hour */
else
(
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,1/48)
)
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
Sample 3: Schedule every 6 hours
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Calculations */
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,0.25)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
Sample 4: Schedule every weekday
/* Inputs */
INPUTS ARE SCHEDULED_DATE(DATE)
/* Calculations */
add = 1
/* Get day number for Friday. */
A_FRIDAY = TO_DATE('2012-01-06', 'YYYY-MM-DD') FRIDAY_NUMBER = TO_CHAR(A_FRIDAY, 'D')
day_number = to_char(SCHEDULED_DATE, 'D') if (day_number = FRIDAY_NUMBER) then add = 3 NEXT_SCHEDULED_DATE =ADD_DAYS(SCHEDULED_DATE, add)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
Sample 5: Schedule every 5 minutes
/* Inputs */
INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
/* Calculations */
NEXT_SCHEDULED_DATE =ADD_DAYS(SCHEDULED_DATE,0.003472222222)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
Sample6: Schedule on 1st of every month
Logic to schedule extract on 1st of every month. If 1st falls on Saturday or Sunday then we need to schedule on Monday.
/* Inputs */
INPUTS ARE SCHEDULED_DATE(DATE)
/* Calculations */
add = 1
corrected_schedule_date(date)= SCHEDULED_DATE
/*** if previous run happens on 2nd or 3rd of month set back to first *********/
IF to_char(SCHEDULED_DATE,'DD') = '2' OR to_char(SCHEDULED_DATE,'DD') = '3' THEN
corrected_schedule_date = to_date( '1/'|| to_char( SCHEDULED_DATE,'MM/YYYY HH24:MI:SS'), 'DD/MM/YYYY HH24:MI:SS')
NEXT_SCHEDULED_DATE = ADD_MONTHS(corrected_schedule_date, 1)
/* Get day number for Saturday. */
A_SATURDAY = TO_DATE('2012-01-07', 'YYYY-MM-DD')
SATURDAY_NUMBER = TO_CHAR(A_SATURDAY, 'D')
/* Get day number for Sunday. */
A_SUNDAY = TO_DATE('2012-01-08', 'YYYY-MM-DD')
SUNDAY_NUMBER = TO_CHAR(A_SUNDAY, 'D')
day_number = to_char(NEXT_SCHEDULED_DATE, 'D')
if (day_number = SATURDAY_NUMBER) then add = 2
if (day_number = SUNDAY_NUMBER) then add = 1
NEXT_SCHEDULED_DATE =ADD_DAYS(NEXT_SCHEDULED_DATE, add)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */
The above samples can be used as a starting point when you have a requirement to create flow schedule formulas for adhoc frequency.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
