How to schedule a HCM Extract using a Flow Schedule Fast Formula in Oracle HCM Cloud environment

Here in this example I am going to schedule a HCM Extract using Flow Schedule Fast Formula.
I have tried to dynamically schedule the HCM Extract using Common Lookup and Value Set.
As of now, for each and every HCM Extract I have created seperate Flow Schedule Fast Formulas.
I am still trying to use single Flow Schedule Fast Formula to schedule multiple HCM Extracts.
The issue why I am not able to use single Flow Schedule Fast Formula for multiple HCM Extracts is,
while submitting the HCM Extracts I am not able to pass the HCM Extract Name as parameter in the Flow Schedule Fast Formula.
If anyone is able to do it, please share the details in the comment section.

Now the question arise, what we have made dynamic here. You can change the schedule of the Extract any time as per Business requirement from the
Common Lookup section. Everytime you don't need to change the Fast Formula to change the Schedule timing.

This Flow Schedule Fast Formula is still in development and I am trying to add multiple functionality to this.
As of now please consider this as a sample Flow Schedule Fast Formula.

Step 1 : Create a Common Lookup. Here we will pass the HCM Extract name and the schedule details of HCM Extract.
 a> Navigate to Setup and Maintenance --> Tasks --> Manage Common Lookups

 b> Create a new Common Lookup. Provide the details as below :
  1> Lookup Type : SM_HCM_FLOW_SCHEDULE_CL (an example)
  2> Meaning : Common Lookup for Flow Schedule Details
  3> Description : Common Lookup for Flow Schedule Details
  4> Module : Global Human Resources
  5> Lookup Code : SM_HCM_ABC_INTEGRATION_V1 (passing the HCM Extract name here; an example
  6> Display Sequence : 1 (pass accordingly)
  7> Enabled : mark it checked
  8> Start Date : 01/01/1901 (pass accordingly)
  9> End Date : 12/31/4712 (pass accordingly)
  10> Meaning : Daily1 (Here this value I am using to check how frequently the process will run)
  11> Description : Hourly (Here this value I am using in what mode the process will run)
  12> Tag : 4 (Here this value I am using in what time span this process will run ; example : in every 4 hours)

 c> Save the Common Lookup.

Note : Here in the Common Lookup, I will pass the details of the Schedule, how the HCM Extract is going to be run.
 Depending on this configuration, the Flow Schedule Fast Formula should be taken care.

Step 2 : Create a Table type Value Set Code. This Value Set will pass the Scheduling details of HCM Extract in the Flow Schedule Fast formula
   to set the next Scheduling date.
 a> Navigate to Setup and Maintenance --> Tasks --> Manage Value Sets

 b> Create a new Value Set. Provide the details as below :
  1> Value Set Code : SM_HCM_FLOW_SCHEDULE_VS (an example)
  2> Description : Value Set for ABC Integration
  3> Module : Global Human Resources
  4> Validation Type : Table
  5> Value Data Type : Character
  6> From Clause : FND_LOOKUP_VALUES_TL FLVT,FND_LOOKUP_VALUES_B FLVB
  7> Value Column Name : FLVB.LOOKUP_TYPE||'*'||FLVB.LOOKUP_CODE||'*'||FLVT.MEANING||'*'||FLVT.DESCRIPTION||'*'||FLVB.TAG
  8> Value Column Type : VARCHAR2
  9> ID Column Name : FLVB.LOOKUP_TYPE||'*'||FLVB.LOOKUP_CODE||'*'||FLVT.MEANING||'*'||FLVT.DESCRIPTION||'*'||FLVB.TAG
  10> ID Column Type : VARCHAR2
  11> WHERE Clause :      FLVT.LOOKUP_TYPE   = FLVB.LOOKUP_TYPE
     AND FLVT.LOOKUP_CODE  = FLVB.LOOKUP_CODE
     AND FLVT.VIEW_APPLICATION_ID = FLVB.VIEW_APPLICATION_ID
     AND FLVB.ENABLED_FLAG   = 'Y'
     AND FLVT.LANGUAGE  = USERENV('LANG')
     AND FLVT.LOOKUP_TYPE  = :{PARAMETER.LOOKUP_TYPE}
     AND FLVB.LOOKUP_CODE LIKE    :{PARAMETER.LOOKUP_CODE}

 c> Save the Value Set.

Step 3 :

Sample Flow Schedule Fast Formula :(The full Fast Formula I have written here, there may be some typo. Please correct accordingly if any. Please consider this as an example)
/****************************************************************************************************************************************
     
Formula Name   : Schedule HCM Extract using Flow Schedule Fast Formula
Formula Type   : Flow Schedule 
Formula Description : Flow Schedule Formula to return a date time from Saturday 6 pm to Friday 6 pm (as an example)
Returns   : NEXT_SCHEDULED_DATE
     
Formula Results  : NEXT_SCHEDULED_DATE; This will be a date time value with yyyy-MM-dd HH:mm:ss format
Effective Date  : 01/01/1901 
     
Name                          Date             Version        Description
-----------------------     ------------          ---------        ------------------ 
Sourav Mazumder     01/01/2020      1.0            Initial Version
     
****************************************************************************************************************************************/

INPUTS ARE SUBMISSION_DATE(DATE), SCHEDULED_DATE(DATE)
SM_GET_HCM_ABC_UPD_SCHED_DETAILS = ' '

SM_VS_LOOKUP_TYPE = 'SM_HCM_FLOW_SCHEDULE_CL'
SM_VS_LOOKUP_CODE = 'SM_HCM_ABC_INTEGRATION_%'

SM_GET_HCM_ABC_UPD_SCHED_DETAILS = GET_VALUE_SET('SM_HCM_FLOW_SCHEDULE_VS','|=LOOKUP_TYPE='''||SM_VS_LOOKUP_TYPE||''''||'|LOOKUP_CODE='''||SM_VS_LOOKUP_CODE||'''')

SM_SCHEDULE_TYPE = SUBSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,2)+1),((INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,3))-((INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,2)+1))))

SM_SCHEDULE_TIME_TYPE = SUBSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,3)+1),((INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,4))-((INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,3)+1))))

SM_SCHEDULE_TIME = SUBSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,4)+1),((LENGTH(SM_GET_HCM_ABC_UPD_SCHED_DETAILS)+1)-((INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,4)+1))))

SM_PRINT_VAR_0 = ESS_LOG_WRITE('SM_GET_HCM_ABC_UPD_SCHED_DETAILS :' + SM_GET_HCM_ABC_UPD_SCHED_DETAILS)
SM_PRINT_VAR_1 = ESS_LOG_WRITE('SM_SCHEDULE_TYPE :' + SM_SCHEDULE_TYPE + '-' + TO_CHAR(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,2)+1))
SM_PRINT_VAR_2 = ESS_LOG_WRITE('SM_SCHEDULE_TIME_TYPE :' + SM_SCHEDULE_TIME_TYPE + '-' + TO_CHAR(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,3)+1))
SM_PRINT_VAR_3 = ESS_LOG_WRITE('SM_SCHEDULE_TIME :' + SM_SCHEDULE_TIME + '-' + TO_CHAR(INSTR(SM_GET_HCM_ABC_UPD_SCHED_DETAILS,'*',1,4)+1))

SM_GET_SUBMISSION_DATE = TO_CHAR(SUBMISSION_DATE)
SM_GET_SCHEDULED_DATE  = TO_CHAR(SCHEDULED_DATE)
SM_PRINT_SUBMISSION_DATE = ESS_LOG_WRITE('SM_GET_SUBMISSION_DATE :' + SM_GET_SUBMISSION_DATE)
SM_PRINT_SCHEDULED_DATE  = ESS_LOG_WRITE('SM_GET_SCHEDULED_DATE  :' + SM_GET_SCHEDULED_DATE)

SM_GET_DAY_END = TO_NUMBER(SUBSTR(TO_CHAR(SCHEDULED_DATE,'YYYY-MM-DD HH24:mm:ss'),12,2))
SM_GET_DAY_NUMBER = TO_CHAR(SCHEDULED_DATE,'D')
SM_PRINT_DAY_END = ESS_LOG_WRITE('SM_GET_DAY_END :' + TO_CHAR(SM_GET_DAY_END))
SM_PRINT_DAY_NUMBER = ESS_LOG_WRITE('SM_GET_DAY_NUMBER :' + SM_GET_DAY_NUMBER)

SM_GET_SUNDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-05','YYYY-MM-DD'),'D')
SM_GET_MONDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-06','YYYY-MM-DD'),'D')
SM_GET_TUESDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-07','YYYY-MM-DD'),'D')
SM_GET_WEDNESDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-08','YYYY-MM-DD'),'D')
SM_GET_THURSDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-09','YYYY-MM-DD'),'D')
SM_GET_FRIDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-10','YYYY-MM-DD'),'D')
SM_GET_SATURDAY_NUMBER  = TO_CHAR(TO_DATE('2020-01-11','YYYY-MM-DD'),'D')

IF UPPER(SM_SCHEDULE_TIME_TYPE) = 'HOURLY' THEN
 NEW_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,((1/24)*TO_NUMBER(SM_SCHEDULE_TIME)))

IF UPPER(SM_SCHEDULE_TIME_TYPE) = 'MINUTELY' THEN
 NEW_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,((1/24)*(1/60)*TO_NUMBER(SM_SCHEDULE_TIME)))

IF (SM_GET_DAY_NUMBER = SM_GET_FRIDAY_NUMBER AND SM_GET_DAY_END > 23) THEN
 NEXT_SCHEDULED_DATE = ADD_DAYS(SCHEDULED_DATE,1)
ELSE
 NEXT_SCHEDULED_DATE = NEW_SCHEDULED_DATE

SM_PRINT_NEXT_SCHEDULED_DATE = ESS_LOG_WRITE('NEXT_SCHEDULED_DATE :' + TO_CHAR(NEXT_SCHEDULED_DATE))

RETURN NEXT_SCHEDULED_DATE
/***************************************************************************************************************************************/

Step 4 : Now navigate to My Clients Group --> Data Exchange --> Submit Extract. Search the HCM Extract you want to submit using the Flow Schedule.

   In the Schedule section click on the dropdown and change the value to "Using a schedule". Now in the Frequency pass the Flow Schedule Fast
          Formula name. Put the Start Date when the HCM Extract should run for the first time. Pass the End date upto when the HCM  Extract should
   run using the Flow Schedule.

How to give access to other Users to view the Output of HCM Extract submitted by another User in Oracle HCM Cloud environment

Please follow the below steps to let other Users, view the Output of HCM Extract, submitted by another User :(Option names or some minor changes may happen as per Oracle Release update)

Step 1 : Create a Role from Security Console with the following information :
         a> Navigate to Home --> Tools --> Security Console
         b> Go to "Roles" tab and search for option "Create Role" and click on it.
         c> Provide the Role Name, Role Code. Choose Role Category as 'HCM - Job Roles'.
              Provide Description(optional).
         d> Now click on the "Next" button and go to the next tab named "Function Security Policies".
         e> Under "Function Security Policies" tab, click on the "Add Function Security Policy" button.
              A search popup will appear and search for the Privilege named "View Output Document",
              code "CMK_VIEW_OUTPUT_DOC_PRIV". Now select the Privilege and click on the        
             "Add Privilege to Role" button.
        f> Once the Privilege is added, click on the "Next" button and go to the next tab named
             "Data Security Policies".
       g> Under "Data Security Policies" tab, click on the "Create Data Security Policy" button.
            A popup will appear. Provide a name for the "Policy Name" option. Pass the Start Date
            value. Now click on the search button of "Database Resource" and search for the Database
            Resource named as "Payroll Checklist for Table PAY_CHECKLISTS". Select the Database
            Resource name and click on "OK" button. Set the "Data Set" value as "All values".
            Set the "Actions" as "View Payroll Flow" and click on "OK".
       h> Now continue to go on to the next tabs one by one, clicking on the "Next" buttons and go to
            the last tab called "Summary". Click on "Save and Close" button and create the new Role.

Step 2 : Assign the newly created Role to the required Users.
        a> Navigate to Home --> Tools --> Security Console
        b> Go to "Users" tab and search for the User who needs access to see the required output from
             HCM Extract submitted by other User.
        c> Now open the User and assign the newly created Role to the User.

Step 3 : Set Owner for HCM Extract
        a> Navigate to Home --> My Client Groups --> Data Exchange --> HCM Extracts
        b> Under HCM Extracts, click on the "Refine Extracts" link and the "Payroll Flow Patters"
             section will appear. In the "Flow Pattern" search for the HCM Extract name, we want the
             output to be accessible for.
        c> In the search result select the HCM Extract and click on the "Edit" button. The "Tasks" tab
             will appear. In the "Tasks" tab select the Flow Task Name with the same name as HCM
             Extract name nd click on the "Go to Task" button.
        d> Now the "Task Details" tab will appear and click on the "Next" button.
        e> Now under the "Task Details", the "Owner" section will appear. Choose "Owner Type"
             as "Group"from the Dropdown List. From the "Owner" Dropdown List, choose the Role
             Name which has been created at Step 1.
        f> Submit the changes.

Step 4 : Navigate to Home --> Tools --> Scheduled Processes and submit the Process named
             "Import User and Role Application Security Data". The process run should be completed
             and successful.

Step 5 : Now submit the HCM Extract which has been refined at Step 3. But make sure the HCM
             Extract should be run with an User, who doesn't have assigned the newly created Role at
             Step 1.

Step 6 : Now login with the User, who had the newly created Role assigned to his/her User Profile.
             Navigate to Home --> Payroll --> Checklist and search for the "Payroll Process Flow Name"
             with which the HCM Extract was submitted. Now select the Payroll Process Flow Name
             and navigate to the Task Details page through "Go to Task" button. Now try to see the Output
             from there and the output will be visible for the User having the newly created Role, assigned
             to its User Profile.

 
            

SQL for getting the Delivery Option details for HCM Extracts in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):
-----------------------------------

SELECT 
                PEDT.DEFINITION_NAME
               ,PEDT.DESCRIPTION
               ,PEDOB.BASE_DELIVERY_OPTION_NAME
               ,PEDOB.DATE_TO
               ,PEDOB.DATE_FROM
               ,PEDOB.DELIVERY_TYPE
               ,PEDOB.BIP_REPORT_NAME
               ,PEDOB.BIP_TEMPLATE_NAME
               ,PEDOB.OUTPUT_TYPE
               ,PEDOB.OUTPUT_NAME
               ,PEDOB.OUTPUT_DIRECTORY_NAME
               ,PEDOB.FILTER_NODE
               ,PEDOB.BURSTING_NODE
               ,PEDOB.LEGISLATION_CODE
               ,PEDOB.MANDATORY_FLAG
               ,PEDODP.OPTION_TYPE
               ,PEDODP.OPTION_VALUE
FROM PER_EXT_DEFINITIONS_TL PEDT, PER_EXT_DELIVERY_OPTIONS_B PEDOB
 , PER_EXT_DELIVERY_OPTION_DTLS PEDODP
WHERE PEDT.DEFINITION_NAME LIKE '<pass the HCM Extract Name>'
AND       PEDT.EXT_DEFINITION_ID                   =    PEDOB.EXT_DEFINITION_ID
AND       PEDOB.EXT_DELIVERY_OPTION_ID   =    PEDODP.EXT_DELIVERY_OPTION_ID

SQL for getting the ID value of Payroll Configuration Group (Threading Name) for running HCM Extracts in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):
-----------------------------------

SELECT 
                TO_CHAR(ACTION_PARAM_GROUP_ID) , ACTION_PARAM_GROUP_ID
FROM FUSION.PAY_ACTION_PARAM_GROUPS
WHERE ACTION_PARAM_GROUP_NAME = <pass the Threading Name here>

SQL for getting Person's User Role details in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):
-----------------------------------

SELECT 
               P.PERSON_NUMBER
              ,U.USERNAME
              ,U.SUSPENDED
              ,RD.ROLE_COMMON_NAME
              ,RDT.ROLE_NAME
FROM PER_USERS U, PER_ALL_PEOPLE_F P
 ,PER_USER_ROLES UR, PER_ROLES_DN RD, PER_ROLES_DN_TL RDT
WHERE   U.PERSON_ID          =    P.PERSON_ID
AND         U.USER_ID               =    UR.USER_ID
AND         UR.ROLE_ID             =    RD.ROLE_ID
AND         UR.ROLE_ID             =    RDT.ROLE_ID

AND         UR.ACTIVE_FLAG    =    ‘Y’
ORDER BY U.USERNAME

SQL for getting all the Role names present in Oracle HCM Cloud environment and also assigned to Users

Sample SQL Query (You can modify as required):
-----------------------------------

SELECT 
               DISTINCT RDT.ROLE_NAME
FROM PER_USERS U, PER_ALL_PEOPLE_F P
 ,PER_USER_ROLES UR, PER_ROLES_DN RD, PER_ROLES_DN_TL RDT
WHERE   U.PERSON_ID          =    P.PERSON_ID
AND         U.USER_ID               =    UR.USER_ID
AND         UR.ROLE_ID             =    RD.ROLE_ID
AND         UR.ROLE_ID             =    RDT.ROLE_ID

SQL to get HDL file details using UCM Content Id or HDL zip File name in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):
-----------------------------------

SELECT
               HDDS.DATA_SET_ID
              ,HDDS.REQUEST_ID
              ,HDDS.UCM_CONTENT_ID
              ,HDDS.DATA_SET_NAME
              ,HDDS.DATA_SET_STATUS
              ,HDDSBO.DATA_SET_BUS_OBJ_ID
              ,HDDSBO.BUSINESS_OBJECT_ID
              ,HDDSBO.DATA_FILE_NAME
              ,HDFL.LINE_ID
              ,HDFL.SEQ_NUM
              ,HDFL.TEXT
FROM HRC_DL_DATA_SETS HDDS, HRC_DL_DATA_SET_BUS_OBJS HDDSBO,
 HRC_DL_FILE_LINES HDFL
WHERE HDDS.DATA_SET_ID                                 =   HDDSBO.DATA_SET_ID
AND      HDDS.REQUEST_ID                                   =   HDDSBO.REQUEST_ID
AND      HDDS.REQUEST_ID                                   =   HDFL.REQUEST_ID
AND      HDDSBO.DATA_SET_BUS_OBJ_ID         =   HDFL.DATA_SET_BUS_OBJ_ID
AND      HDDS.UCM_CONTENT_ID                       =   <pass UCM Content Id here>
AND      HDDS.DATA_SET_NAME                          =   <pass Zip file name here>
ORDER BY HDDSBO.DATA_FILE_NAME, HDFL.SEQ_NUM