Fusion HCM - Payroll Batch Loader using Transformation formula

In the below example, we are going to create element entries for Overtime earnings for an employee same as above, but now using the Transformation Formula.

Transformation Formula could be used while Automating Payroll Batch Loader

Data File preparation

1. Prepare a Data file (.csv/.txt) with required Input Values (Sample below)

12,E12,2019-09-01,8

2. Then Go to Navigator à Tools à File Import and Export and Place the Data file in WCC

(For Automation Process, Files needs to be automatically sent to WCC with UNIQUE_NAME)

Batch Loader Formula

Write a Batch Loader type Fast Formula to match the Data File (Sample below)

/* Inputs  */

INPUTS ARE OPERATION (text)

          ,LINENO (number)

          ,LINESEQUENCE (number)

          ,LINEREPEATNO (number)

          ,POSITION1 (text)

          ,POSITION2 (text)

          ,POSITION3 (text)

          ,POSITION4 (text)

DEFAULT FOR LINENO       IS 1

DEFAULT FOR LINEREPEATNO IS 1

DEFAULT FOR LINESEQUENCE IS 1

DEFAULT FOR POSITION1    IS 'NO DATA'

DEFAULT FOR POSITION2    IS 'NO DATA'

DEFAULT FOR POSITION3    IS 'NO DATA'

DEFAULT FOR POSITION4    IS 'NO DATA'

/* Calculations */

IF OPERATION='FILETYPE' THEN

   OUTPUTVALUE='DELIMITED'

ELSE IF OPERATION='DELIMITER' THEN

   OUTPUTVALUE=','

ELSE IF OPERATION='READ' THEN

   OUTPUTVALUE='NONE'

ELSE IF OPERATION='MAP' THEN

(

   MESSAGE=' '

   MESSAGELEVEL=' '

   LC_ERROR_FLAG='N'

   SUPPRESSINVALIDPARAMETER = 'Y'

   LC_DATE_FORMAT='YYYY-MM-DD'

   EFFECTIVE_END_DATE='4712-12-31'

   ACTIONREFERENCE      = ' '

   ELEMENT              = ' '

   /*Batch Related Outputs*/

   TASK='Element Entry'

  

   L_PERSON_NUMBER          = Trim(POSITION1)

   L_ASSIGNMENT_NUMBER      = Trim(POSITION2)

   L_EFFECTIVE_START_DATE   = Trim(POSITION3)

   HOURS                    = Trim(POSITION4)

   LC_ELEMENT_NAME          = 'Overtime'

  

   IF ISNULL(L_ASSIGNMENT_NUMBER) = 'Y' THEN

   (

    

   TASK                    ='Element Entry'

   LC_TASKACTION_CREATE_EE ='Create Element Entry'

   LC_TASKACTION_UPDATE_EE ='Update Element Entry'

   PERSON_NUMBER           = L_PERSON_NUMBER

   ASSIGNMENT_NUMBER       = L_ASSIGNMENT_NUMBER

   EFFECTIVE_AS_OF_DATE    = L_EFFECTIVE_START_DATE

   EFFECTIVE_START_DATE    = L_EFFECTIVE_START_DATE

     

   ACTIONREFERENCE         = LC_ELEMENT_NAME

   ELEMENT                 = LC_ELEMENT_NAME  

   /* To Check whether the element entry already exists (to understand update)*/

   LC_ELEMENT_ENTRY_CHECK = GET_VALUE_SET('XXX_ELEMENT_ENTRY_CHECK','|=P_ASSIGNMENT_NUMBER='''||L_ASSIGNMENT_NUMBER||''''||

                                                                    '|P_ELEMENT_NAME='''||LC_ELEMENT_NAME||''''||

                                                                    '|P_EFFECTIVE_DATE='''||L_EFFECTIVE_START_DATE||'''')  

  

   IF LC_ELEMENT_ENTRY_CHECK = 'Y' THEN

   (

   TASKACTION=LC_TASKACTION_UPDATE_EE

   )

   ELSE

   (

   TASKACTION=LC_TASKACTION_CREATE_EE

   )

  

    IF TASKACTION=LC_TASKACTION_CREATE_EE THEN

    (

      RETURN TASK,TASKACTION,LINESEQUENCE,ACTIONREFERENCE,SUPPRESSINVALIDPARAMETER,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,PERSON_NUMBER,ASSIGNMENT_NUMBER,HOURS,MESSAGE,MESSAGELEVEL

    )

    IF TASKACTION=LC_TASKACTION_UPDATE_EE THEN

    (

      RETURN TASK,TASKACTION,LINESEQUENCE,ACTIONREFERENCE,EFFECTIVE_AS_OF_DATE,PERSON_NUMBER,ASSIGNMENT_NUMBER,HOURS,MESSAGE,MESSAGELEVEL

    )

   )

   ELSE

   (

   LC_ERROR_FLAG = 'Y'

  

   IF LC_ERROR_FLAG = 'Y' THEN

    (

       TASKACTION='Create Element Entry'

       ACTIONREFERENCE = 'Error Processing'

       MESSAGELEVEL='F'

       File_Type = 'Overtime Hours File'

       File_Line = SUBSTR('Given employment record cannot be found. '||POSITION1,1,240)

       Line_Sequence = LINESEQUENCE

       MESSAGE =  Substr(MESSAGE + ' ~ LINESEQUENCE ' + To_Char(LINESEQUENCE) + ' ~ File Line ~ ' + File_Line ,1,4000)

       Error_Message = MESSAGE

       Error_Type = 'Message Level ' + MESSAGELEVEL

       RETURN TASK,TASKACTION,LINESEQUENCE,ACTIONREFERENCE,MESSAGE,MESSAGELEVEL,File_Type,File_Line,Line_Sequence,Error_Message,Error_Type

    )

   )

  

)

ELSE

   OUTPUTVALUE='NONE'

RETURN OUTPUTVALUE

/* End Formula Text */

Creating Payroll Flow for Loading and Transferring Batch

1.    Go to Navigator à Payroll Checklist à Tasks à Manage Payroll Flow Patterns

2.    Create a Custom Flow Pattern copying Seeded Load and Transfer Batch Flow Pattern

3. Update the Load Batch Task Details: Basic Information of the Custom Flow Pattern as below :

Name	Parameter Basis	Basis Value	Hint
Batch	Post SQL Bind	SELECT 'UNIQUE_BATCH' FROM DUAL	Query to get unique Batch
Batch Name	Post SQL Bind	SELECT 'UNIQUE_BATCH_NAME' FROM DUAL	Query to get unique Batch Name
Content Id	Post SQL Bind	SELECT MAX(ddocname) FROM revisions WHERE did IN (SELECT did FROM revisions WHERE ddoctitle LIKE 'UNIQUE_NAME'|| (SELECT TO_CHAR (SYSDATE, 'DD-MM-YYYY') || '%' FROM DUAL)) AND TRUNC(DCREATEDATE) = TRUNC(SYSDATE) AND ddocname LIKE 'UNIQUE_NAME%'	Query to pick the exact file to be processed from WCC
Transformation Formula	Post SQL Bind	select max(formula_id) from ff_formulas_vl ff, ff_formula_types ft where ff.formula_type_id = ft.formula_type_id and ft.formula_type_name = 'Batch Loader' and ff.formula_name = 'BATCH_LOADER_FORMULA_NAME'	Query to map the Batch Loader Formula Created

4. Scheduling the Custom Flow Pattern automates the Element Entry Creation.