Payroll Transformation Formula for HCM Data Loader

Payroll Transformation Formula for HCM Data Loader

If we have the data in flat files i.e text, csv, dat etc., those data we need to load into system then we must convert that data to HDL format and upload.

Steps to load the flat file to HCM Data Loader:

1.       1. Upload the flat file into content server by manually or webservices.

2.       2 .Prepare the Payroll Transformation Formula for HCM Data Loader.

3.       3. Run the Load Data from file payroll flow by manually or webservices.

Upload file to content Server:  Navigator à Tools à File Import and Export

Click add à upload the flat file and select account hcm/dataloader/import  à save and close

Enable the File Encryption:

Before loading the encrypted file be ensure the parameter is set in payroll process configuration.

My Client Groups à quick Actions à Payroll à Payroll Process Configuration

In Default Groups à Select the parameter à Payroll Batch Loader Encryption Type Valid Values are PGPSIGNED, PGPUNSIGNED and PGPX509UNSIGNED. Default No encryption.

Create a fast formula of type HCM Data Loader:

Setup and Maintenance à Fast Formula à Create New formula à Select type HCM Data Loader

Formula has the input variables:

OPERATION, FileName, FileDiscriminator, LINENO and LINEREPEATNO are Input Variables for this formula type. Depending on the business object we can add the extra input variables : POSITION1, POSITION2, …, POSITIONn.

Example: HDL_Transformation_Formula.txt is the file uploaded in Content Server

E509|GRADE_CHANGE|2019/12/08|G1

E512|GRADE_CHANGE|2020/11/10|G2

Mapping of Positions with the file as shown in above.

1.       OPERATION

2.       FileName

3.       FileDiscriminator

4.       LINENO

5.       LINEREPEATNO

1. Operation:

The values to the OPERATION that we can use are:

FILETYPE,DELIMITER,READ,MAP,NUMBEROFBUSINESSOBJECTS,METADATALINEINFORMATION

a.     FILETYPE --> OUTPUTVALUE --> DELIMITED

For FILETYPE Operation output value is DELIMITED

 

b.     DELIMITER --> OUTPUTVALUE -->   |

For DELIMITER Operation output value is |

If we are using the comma separated flat file then use ‘,’.

Note: By default it is ‘|’

 

c.     READ --> OUTPUTVALUE --> NONE

For READ Operation output value is NONE

 

d.     MAP --> Business Object Attributes --> returns the business object attributes

For MAP Operation output value is the business object attributes

 

e.     NUMBEROFBUSINESSOBJECTS --> OUTPUTVALUE -->Ex: 1

For NUMBEROFBUSINESSOBJECTS Operation output value is 1

In Flat file if we need to create the 2 business objects then give this as 2.

 

f.      METADATALINEINFORMATION --> METADATAn --> Ex:METADATA1

For METADATALINEINFORMATION Operation output value is METADATAn

If we need the two METADATA ‘s then we have to create METADATA1,METADATA2

2. FileName: Business object file Name

3. FileDiscriminator: Business Object file discriminator

4. LINENO : It gives the which number.

5.LINEREPEAT And LINEREPEATNO: When we want to process the same line for multiple times then we will use these.

Example:

FileName = 'ElementEntry'

FileDiscriminator = 'ElementEntryValue'

BusinessOperation = 'MERGE'

LINEREPEAT allows a single line of input to be processed multiple times.

If it is 'Y' then one more time it will be processed. If it is 'N' then it stops processing that particular line.

LINEREPEATNO indicates the number of repetitions.

If a line in a file is processing first time it will be '1'. And if the same line processing second time it is '2'.

BusinessOperation :  This is used for MERGE/DELETE/COMMENT operations to be performed in HDL.

Example:

IF LINEREPEATNO=1 THEN

 (

   BusinessOperation='MERGE'

   BusinessObject='Element Entry'

              FileName = 'ElementEntry'

              FileDiscriminator ='ElementEntry'

              LINEREPEAT = 'Y'

   RETURN BusinessOperation,FileDiscriminator,FileName

   )

ELSE

( 

   BusinessOperation='MERGE'

   BusinessObject='Element Entry Value'

              FileName = 'ElementEntry'

              FileDiscriminator = 'ElementEntry'

               LINEREPEAT = 'N'

   RETURN BusinessOperation,FileDiscriminator,FileName

 )

 If the Element entry details and Element entry value details both are stored in the same line, then we need to process the same line twice. So LINEREPEAT is ‘Y’ for Element Entry and ‘N’ for Element Entry Value.  If ‘Y’ is there it will process the same line again, for ‘N’ it will go to the next line after it process that line.

Return values for this Formula type:

RETURN BusinessOperation,FileName,FileDiscriminator,Attribute1,Attribute2, …, Attributen

Mapping for flex fields :

Syntax for PersonId(SourceSystemId) :  "PersonId(SourceSystemId)"= POSITIONn

Ex: "PersonId(SourceSystemId)"= POSITION21

Return values example:

RETURN BusinessOperation,FileName,FileDiscriminator,Attribute1,...,"PersonId(SourceSystemId)",...,Attribute25

Should be in double quotes

Roles and Privileges:

The privileges that we required to submit payroll flow and load HCM Data are PAY_SUBMIT_PAYROLL_FLOW_PRIV and HRC_LOAD_HCM_DATA_PRIV.

Human Capital Management Integration Specialist role is required for checklists, Data Exchange and to load data from a file flow pattern.

Fast formula mapping with Flat file example1:

Flat file:

E509|GRADE_CHANGE|2019/12/08|G1

Requirement : For Grade change we have create an element entry value ABC.

INPUTS  ARE OPERATION (text),

LINENO (number),

POSITION1 (text),

POSITION2 (text),

POSITION3 (text),

POSITION4 (text),

LINEREPEATNO (number)

 

DEFAULT FOR LINEREPEATNO IS 1

DEFAULT FOR LINENO       IS 1

DEFAULT FOR POSITION1    IS 'NO DATA'

DEFAULT FOR POSITION2    IS 'NO DATA'

DEFAULT FOR POSITION3    IS '1900/01/01'

DEFAULT FOR POSITION4    IS 'NO DATA'

 

IF OPERATION='FILETYPE' THEN  

                  OUTPUTVALUE='DELIMITED'

ELSE IF OPERATION='DELIMITER' THEN  

                  OUTPUTVALUE='|'

ELSE IF OPERATION='READ' THEN  

                  OUTPUTVALUE='NONE'

ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN  

                  (

                  OUTPUTVALUE = '1'  

                  RETURN OUTPUTVALUE

                  )

ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN

                  (

                   METADATA1[1] = 'ElementEntry' /*FileName*/

     METADATA1[2] = 'ElementEntryValue' /*FileDiscriminator*/     

                   METADATA1[3] = 'LegislativeDataGroupName'     

                   METADATA1[4] = 'EffectiveStartDate'     

                   METADATA1[5] = 'EffectiveEndDate'     

                   METADATA1[6] = 'ElementName'     

                   METADATA1[7] = 'MultipleEntryCount'     

                   METADATA1[8] = 'AssignmentNumber'     

                   METADATA1[9] = 'InputValueName'     

                   METADATA1[10] = 'ScreenEntryValue'     

                   METADATA1[11] = 'EntryType'     

                   RETURN METADATA1

                   )

ELSE IF OPERATION = 'MAP' THEN

                  (

                  /*Batch Related Outputs*/     

                  FileName = 'ElementEntry'     

                  FileDiscriminator = 'ElementEntryValue'     

                  LegislativeDataGroupName = 'IN Legislative Data Group'     

                  EffectiveStartDate       = POSITION3     

                  EffectiveEndDate         = '4712/12/31'     

                  AssignmentNumber         = POSITION1     

                  InputValueName           = 'test'     

                  MultipleEntryCount       = 1     

                  EntryType                = 'E'         

                  change = POSITION2

    BusinessOperation = 'MERGE'

                                    IF(change='GRADE_CHANGE')   THEN        

                                          (

                                                         BusinessObject    = 'Element Entry Value'            

                                                         ElementName       = 'ABC Element Name'           

                                                         ScreenEntryValue  = ‘Grade Change’           

                                                         LINEREPEAT        = 'N'           

                                                         BusinessOperation = 'MERGE'

                                                         )          

      RETURN BusinessOperation,FileName,FileDiscriminator,LegislativeDataGroupName,EffectiveStartDate,EffectiveEndDate,ElementName,MultipleEntryCount,AssignmentNumber,InputValueName,ScreenEntryValue,EntryType,LINEREPEAT,LINEREPEATNO

    )

 

ELSE  OUTPUTVALUE = 'NONE'

RETURN OUTPUTVALUE

 

Navigator à Payroll à payroll checklist à submit a payroll flow à select Load data from file

To run this payroll flow :

Need to give the Content Id of the Flat File and select Transformation Fast Formula required to convert the Flat file.

The HDL zip file name is created with the  payroll Flow name.

Fast formula mapping with Flat file example2:

Requirement : For grade change we need to create an element entry and element entry values load into system.

Flat file:

E509|GRADE_CHANGE|2019/12/08|G1

E510|GRADE_CHANGE|2019/12/10|G2

FF Name: Transformation_FF_Element_Entries

INPUTS  ARE OPERATION (text),

LINENO (number),

POSITION1 (text),

POSITION2 (text),

POSITION3 (text),

POSITION4 (text),

LINEREPEATNO (number)

 

DEFAULT FOR LINEREPEATNO IS 1

DEFAULT FOR LINENO       IS 1

DEFAULT FOR POSITION1    IS 'NO DATA'

DEFAULT FOR POSITION2    IS 'NO DATA'

DEFAULT FOR POSITION3    IS '1900/01/01'

DEFAULT FOR POSITION4    IS 'NO DATA'

 

IF OPERATION='FILETYPE' THEN  

                  OUTPUTVALUE='DELIMITED'

ELSE IF OPERATION='DELIMITER' THEN  

                  OUTPUTVALUE='|'

ELSE IF OPERATION='READ' THEN  

                  OUTPUTVALUE='NONE'

ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN  

                  (

                  OUTPUTVALUE = '2'  

                  RETURN OUTPUTVALUE

                  )

ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN

                  (

                   METADATA1[1]  = 'ElementEntry'/*FileName*/

                   METADATA1[2]  = 'ElementEntry'/*FileDiscriminator*/

     METADATA1[3]  = 'EffectiveStartDate'

                   METADATA1[4]  = 'MultipleEntryCount'

                   METADATA1[5] = 'AssignmentNumber'

                   METADATA1[6] = 'CreatorType'

                   METADATA1[7] = 'ElementName'

                   METADATA1[8] = 'LegislativeDataGroupName'

                   METADATA1[9] = 'EntryType'

                   

                   METADATA2[1] = 'ElementEntry' /*FileName*/

     METADATA2[2] = 'ElementEntryValue' /*FileDiscriminator*/     

                   METADATA2[3] = 'LegislativeDataGroupName'     

                   METADATA2[4] = 'EffectiveStartDate'     

                   METADATA2[5] = 'EffectiveEndDate'     

                   METADATA2[6] = 'ElementName'     

                   METADATA2[7] = 'MultipleEntryCount'     

                   METADATA2[8] = 'AssignmentNumber'     

                   METADATA2[9] = 'InputValueName'     

                   METADATA2[10] = 'ScreenEntryValue'     

                   METADATA2[11] = 'EntryType'  

    

                   RETURN METADATA1,METADATA2

                   )

ELSE IF OPERATION = 'MAP' THEN

                  (

                  /*Batch Related Outputs*/     

                  FileName = 'ElementEntry'                    

                  LegislativeDataGroupName = 'IN Legislative Data Group'     

                  EffectiveStartDate       = POSITION3     

                  EffectiveEndDate         = '4712/12/31'     

                  AssignmentNumber         = POSITION1     

                  InputValueName           = 'test'     

                  MultipleEntryCount       = 1     

                  EntryType                = 'E'         

                  change = POSITION2

    BusinessOperation = 'MERGE'

                 

/*METADATA|ElementEntry|EffectiveStartDate|MultipleEntryCount|AssignmentNumber|CreatorType|ElementName|LegislativeDataGroupName|EntryType

MERGE|ElementEntry|2019/12/08|1|E509|H|ABC Element Name|IN Legislative Data Group|E */

                 

                  IF( LINEREPEATNO=1 ) THEN     

  (

                                    IF(change='GRADE_CHANGE')   THEN        

                                          (

                                                         FileDiscriminator = 'ElementEntry'  

                                                         CreatorType       = 'H'         

                                                         ElementName       = 'ABC Element Name'                       

                                                         LINEREPEAT        = 'Y'           

                                                         BusinessOperation = 'MERGE'

                                                         )

                  RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveStartDate,MultipleEntryCount,AssignmentNumber,CreatorType,ElementName,LegislativeDataGroupName,EntryType,LINEREPEAT,LINEREPEATNO

)                                  

/*METADATA|ElementEntryValue|LegislativeDataGroupName|EffectiveStartDate|EffectiveEndDate|ElementName|MultipleEntryCount|AssignmentNumber|InputValueName|ScreenEntryValue|EntryType

MERGE|ElementEntryValue|IN Legislative Data Group|2019/12/08|4712/12/31|ABC Element Name|1|E509|test|Grade Change|E*/

                  IF( LINEREPEATNO=2 ) THEN     

  (

                                    IF(change='GRADE_CHANGE')   THEN        

                                          (

                                                         FileDiscriminator = 'ElementEntryValue'             

                                                         ElementName       = 'ABC Element Name'           

                                                         ScreenEntryValue  = 'Grade Change'           

                                                         LINEREPEAT        = 'N'           

                                                         BusinessOperation = 'MERGE'

                                                         )

                  RETURN BusinessOperation,FileDiscriminator,FileName,LegislativeDataGroupName,EffectiveStartDate,EffectiveEndDate,ElementName,MultipleEntryCount,AssignmentNumber,InputValueName,ScreenEntryValue,EntryType,LINEREPEAT,LINEREPEATNO

)                                                       

    )

ELSE  OUTPUTVALUE = 'NONE'

RETURN OUTPUTVALUE

Navigator à Payroll à Payroll checklist à submit a payroll flow à select Load data from file

To run this payroll flow :

Need to give the Content Id of the Flat File and select Transformation Fast Formula required to convert the Flat file.

The HDL zip file name is created with the  payroll Flow name.

Output file:

METADATA|ElementEntry|EffectiveStartDate|MultipleEntryCount|AssignmentNumber|CreatorType|ElementName|LegislativeDataGroupName|EntryType

MERGE|ElementEntry|2019/12/08|1|E509|H|ABC Element Name|IN Legislative Data Group|E

MERGE|ElementEntry|2019/12/10|1|E510|H|ABC Element Name|IN Legislative Data Group|E

METADATA|ElementEntryValue|LegislativeDataGroupName|EffectiveStartDate|EffectiveEndDate|ElementName|MultipleEntryCount|AssignmentNumber|InputValueName|ScreenEntryValue|EntryType

MERGE|ElementEntryValue|IN Legislative Data Group|2019/12/08|4712/12/31|ABC Element Name|1|E509|test|Grade Change|E

MERGE|ElementEntryValue|IN Legislative Data Group|2019/12/10|4712/12/31|ABC Element Name|1|E510|test|Grade Change|E

File Loaded successfully in Data Exchange à Import and Load

you can see this post in my friend blog https://fusionhcmknowledgebase.com/2020/07/hdl-transformation-formula-with-elemententry , here you can find Oracle cloud HCM technical stuff. 

It may helpful for you. please visit blog for more information and share your ideas.

Thanks

Sainath