Wednesday 18 September 2019

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
  1. 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
  1. Scheduling the Custom Flow Pattern automates the Element Entry Creation.

5 comments: