• Post category:Fast Formula
  • Post comments:3 Comments
  • Post last modified:June 12, 2020
  • Reading time:3 mins read
Using Flow Schedule Fast Formula to Schedule Extracts
Using Flow Schedule Fast Formula to Schedule Extracts
- Using Flow Schedule Fast Formula to Schedule Extracts
Total Visits: 28

In this article we will look into how to schedule the HCM Extracts at ad-hoc schedules or complex schedules which cannot be handled using a simple way.

In our previous article we saw how to Schedule/Cancel Schedule for HCM ExtractsClick here if you have missed to look at it.

The previous article covers scheduling Extract at Once/Daily/Weekly/Monthly frequency only. However if we have a requirement to schedule extract for every 2 hours/only on weekdays we will have to use the Flow Schedule type Fast Formula and use it while scheduling HCM Extracts.

Steps to create Flow Schedule Formula:

Navigate to Setup and Maintenance > Define Fast Formula
Click Create

image 34 - Using Flow Schedule Fast Formula to Schedule Extracts

Provide a name to Fast Formula and select the type as “Flow Schedule” and fill in other parameters and click Continue.

Flow Schedule Formula to run extracts only on weekdays:

INPUTS ARE SCHEDULED_DATE(DATE)
/* 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-01', 'YYYY-MM-DD')
SUNDAY_NUMBER = TO_CHAR(A_SUNDAY, 'D')
/* Starting at scheduled_date, get next_scheduled_day by using the day number.*/
NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE, 1)
SCHEDULED_DAY_NUMBER = TO_CHAR(NEXT_SCHEDULED_DATE, 'D')
WHILE (SCHEDULED_DAY_NUMBER = SATURDAY_NUMBER OR
SCHEDULED_DAY_NUMBER = SUNDAY_NUMBER) LOOP
(
/* Go to next day. */
NEXT_SCHEDULED_DATE = ADD_DAYS( NEXT_SCHEDULED_DATE, 1)
SCHEDULED_DAY_NUMBER = TO_CHAR(NEXT_SCHEDULED_DATE, 'D')
)
RETURN NEXT_SCHEDULED_DATE

Copy this code, Save and Compile the Formula. Once compiled, you are ready to schedule the extracts using this weekday schedule.

We will look into the explanation of this formula now:

  • Inputs for this formula type are SUBMISSION_DATE and SCHEDULED_DATE and Outputs are NEXT_SCHEDULED_DATE. SUBMISSION_DATE is the datetime of the date on which the flow is Submitted. Most of the times, we will be using the SCHEDULED_DATE which stores the start date of the schedule.
  • First we are getting the Day Number for Saturday and Sunday.
  • Next we are getting the  Day Number of the Day Number of SCHEDULED_DATE and adding two to it if the next schedule falls on saturday or 1 to it if the next schedule falls on sunday (using while loop).
  • Final outcome would be monday if the next schedule falls over saturday/sunday.
  • In this way we can achieve ad-hoc schedules using Fast Formula.

Flow Schedule formula to execute extract for every 2 hours:

/*************************************************************/
FORMULA NAME: Schedule for every two hours
FORMULA TYPE: Flow Schedule
DESCRIPTION: Formula to return a date time.
Returns NEXT_SCHEDULED_DATE;
Formula Results :
NEXT_SCHEDULED_DATE This is 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,2/24)
/* Returns */
RETURN NEXT_SCHEDULED_DATE
/* End Formula Text */

The ADD_DAYS function is used to add the number of days to the next schedule. In this case we need 2 hours to be added and hence 2/24 hours.. so the next schedule will be 2 hours from previous schedule.

Tip: You can create Flow Schedule Formula to cater to complex scheduling patterns. Ex: Extract should run 2 days prior to pay period end date and 2 days after the new pay period starts. You can create a Valueset and formula to achieve such complex requirements.

  • Hi Sricharan,

    It is very useful. Thank you so much for sharing the code.

    I think small correction on Weekdays FF at line 12, it should be
    NEXT_SCHEDULED_DATE = ADD_DAYS( SCHEDULED_DATE, 1)

    Please check once.

    Thanks,
    Viswanadh K

  • In visible box by plugintheme