Automate Loads - Transformation Formula

   Payroll Transformation Formula - Load Automation 


In general, when new hires are onboarded certain information are not available and HR would get the information only when the worker onboards officially.

HR generally collates them and arranges for a technical bulk load.

Here is a simple design to automate those loads.

Lets consider we need to load the National Identifier numbers for the workers.

Here is the transformation formula that can be used to convert an input file of text into HDL for loading all kinds of National Identifiers

1.      Upload Transformation Formula

/****************************************************************************************/

INPUTS ARE OPERATION (text),

LINENO (number),

POSITION1 (text),

POSITION2 (text),

POSITION3 (text),

POSITION4 (text)

 

DEFAULT FOR LINENO 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'

 

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] = 'Worker'                        /* FileName - Worker.dat */

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

METADATA1[3] = 'PersonNumber'

METADATA1[4] = 'PersonId'

METADATA1[5] = 'LegislationCode'

METADATA1[6] = 'NationalIdentifierNumber'

METADATA1[7] = 'NationalIdentifierType'

METADATA1[8] = 'SourceSystemId'

METADATA1[9] = 'SourceSystemOwner'

RETURN METADATA1

)

 

IF POSITION1 = 'PersonNumber' THEN

(

LINEREPEAT = 'N'

RETURN LINEREPEAT

) /* To skip header row */

ELSE IF OPERATION = 'MAP' THEN

(

/*Batch Related Outputs*/

FileName = 'Worker'

FileDiscriminator = 'PersonNationalIdentifier'

 

/* Derive Person ID using value set */

l_person_id = GET_VALUE_SET('Get_Person_Id','|=PersonNumber=''' || TRIM(POSITION1) || '''')

PersonNumber = TRIM(POSITION1)

PersonId = l_person_id

LegislationCode = TRIM(POSITION2)

NationalIdentifierNumber = TRIM(POSITION3)

/* Derive NationalIdentifierType based on input */

IF UPPER(TRIM(POSITION4)) = 'AADHAR' THEN

(

NationalIdentifierType = 'ORA_HRX_IN_AADHAR_NUM'

)

ELSE IF UPPER(TRIM(POSITION4)) = 'PF' THEN

(

NationalIdentifierType = 'HRX_IN_PF_NUM'

)

ELSE IF UPPER(TRIM(POSITION4)) = 'UAN' THEN

(

NationalIdentifierType = 'HRX_IN_UAN_NUM'

)

ELSE

(

NationalIdentifierType = TRIM(POSITION4)

)

/* Derive SourceSystemId = PersonNumber + NationalIdentifierType + sysdate */

l_sysdate = TO_CHAR(GET_SYSDATE(), 'YYYYMMDD')

SourceSystemId = PersonNumber || '_' || NationalIdentifierType || '_' || l_sysdate

SourceSystemOwner = 'HRC_SQLLOADER'

BusinessOperation = 'MERGE'

RETURN BusinessOperation, FileName, FileDiscriminator, PersonNumber, PersonId, LegislationCode, NationalIdentifierNumber, NationalIdentifierType, SourceSystemId, SourceSystemOwner

)

ELSE OUTPUTVALUE = 'NONE' 

RETURN OUTPUTVALUE 

2.      Prepare Data File

We would need the Source file to be in text format as below

 

PersonNumber|LegislationCode|NationalIdentifierNumber|NationalIdentifierType

1000|IN|TSXX000|PF

1001|IN|100000|UAN

 

3.      Upload File via HDL Flow

Navigate to My Workforce → Submit a Flow → Select 'Load Data from File' → Provide a Payroll Flow name → Upload the text file

It will then generate a ContentID of WCC which can be selected from the LOV ContentId Parameter

 

4.      Click 'Submit'

 

5.     View Flow

Once the Submitted Flow is Complete, we can see the data updated in the Oracle System

 

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud HCM Open Ended Absences Update

  Oracle Cloud HCM Open Ended Absences       In Oracle Cloud HCM, when there exist open ended absences like Maternity and Sickness and Employee is terminated, it is seen that there is no end date updated to these open absences as of Termination Date. This leads to ambiguity in reporting and payroll To address this issue, worked on an automation process which will pick such records and put an end date same as the termination date.   Design Steps   1.       Build a BIP report to list the Open ended absences for Terminated employees 2.      Write the Bursting definition to feed the BIP report output to Extract 3.      Desing the ETF template to build the HDL Template for End dating Open Absence 4.      Design a HCM Extract that will take the output fed from BIP Report and Auto Loads as   HDL process 5.   ...
Read more

Oracle HCM Absence Orphan Records

Image
Sometimes We come across a less known Error while submitting an Absence in Oracle Cloud HCM with an Error Code ANC-3405194 Likely caused when  Update of Accrual plan is done after an accrual process has already run till a date in future. OR  Entering accrual via HDL in a previous plan period and missing to run calculate accrual. This is Resolved when we Delete the Orphaned records found by Below Query Query to identify the Orphan Absence Records in the System SELECT  per_accrual_entry_dtl_id,         ppl.person_number       ,         ass.assignment_number   ,         plans_tl.NAME           ,         TYPE                    ,         procd_date FROM    fusion.anc_per_acrl_entry_dtls acrl_dtls ,         fusion.per_all_people_f ppl  ...
Read more
Image
           Deploying an AI agent on Redwood Page                                    1.     N avigate to the AI Agent Studio from the Tools Menu             2.    Select any available delivered AI agent and Copy it to be used by    your business.                         3.     Run it to see all the Profiles and Config are good.         4.     Publish the AI Agent              5.       Navigate to My Client Groups -> Guided Journeys      6.       Create New Journey      7.       Create Draf...
Read more