Using Flow Schedule Fast Formula to Schedule Extracts
Using Flow Schedule Fast Formula to Schedule Extracts

 990 total views

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.

Want to learn Fusion Technical tools? Checkout this article

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

Creating Flow Schedule type fast formula to schedule hcm extracts at adhoc frequency in fusion hcm

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.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

This Post Has 3 Comments

  1. Viswanadh

    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

  2. Sricharan

    Yes Viswanadh. Thanks for correcting. I will update the article right away.

  3. Viswanadh

    Thank you so much Sricharan.

Leave a Reply