Introduction

One of the major challenges consultants have faced over the last couple of years with respect to HCM Data Load is to automate the entire process of Downloading Data from the HCM Cloud Application, Making Transformation (Updates/Edits to them) and reloading the same back to the application using HCM Data Loader.

Things are relatively easy if one is loading data from a legacy application to HCM Cloud as in most cases one has access to database where they can use pl/sql programs to first get data in HDL format and then use transformation technique to make changes to data and then upload the HDL file in Oracle HCM Cloud Application.

Imagine, you are asked to do the entire activity in HCM Cloud itself. Meaning say your application is already gone live and then there is a requirement to say change the location of all employees in the system from Location A to Location B . One would need to create a new assignment record which would start one day after the most recent assignment record. In this scenario one would have to perform the following steps:

1. Extract All Eligible Records in HDL format
2. Make Changes to the record
3. Upload the new record into application

In this, example we would try to demonstrate the same. For simplicity sake we would only use one Worker Record (Person#898453 for this example) and just modify the value of one attribute say “WorkAtHomeFlag”. Which means if the value of “Work from Home” is No or Null the new record should have a value of Yes and if the value of Work from Home is Yes it should be changed to No .

So without further ado, lets gets started.

Extract All Eligible Records in HDL Format

In one of the previous article ( Generating EText Output From HCM Extract In Oracle Fusion HCM Cloud Application ) we have seen how to create eText output.

So, we will create an HCM Extract which will fetch the following fields:

*Field Highlighted in Green is the one which is to be changed. All other fields are basic fields which are required for any update/correction action required.

*Field Highlighted in Green is the one which is to be changed. All other fields are basic fields which are required for any update/correction action required.

We would have two data groups created namely WorkerDataGroup and WorkTermsDataGroup . WorkerDataGroup is the Root Data Group.

Some Details related to Data Group are in table below:

Also we should have the following Connection between the Root Data Group (WorkerDataGroup) and WorkTermsDataGroup

Once we have all these details populated we would need to define the Extract Delivery Options. Details for which are in below table

Make Changes to the Record

Now that we are all set from extracting the data from application. Let’s next try to focus on the transformation logic applied. We would apply the logic on the eText Template and the reason for the same is:

1. We do not want to use any transformation in HCM Extract
2. All Transformation are done in eText template

This, however is the approach I followed, and one may choose to have a different rule applied.

As discussed, we would be creating a new record and only try to modify the value of “WorkAtHomeFlag”. This eventually means that we will have the Effective Start Date value changed to current value + 1 for both WorkTerms and Assignment Record and the value of “WorkAtHomeFlag” altered based on current value (i.e from ‘N’ to ‘Y’ , ‘Y’ to ‘N’ or even Null to ‘Y’)

Upload the New Record into Application

Once we are done with the above setup we will get Worker.txt file which will have all the data but HDL only supports .dat file extension and so to ensure we get the file with same filename and also it triggers the “HCM Data Loader” we have to perform the following steps:

1. Add a parameter named “Auto Load” to the UpdateAssignmentExtract
2. Add “Initiate HCM Data Loader” to UpdateAssignmentExtract payroll flow

Add “Auto Load” Parameter to UpdateAssignmentExtract

We would need to add a new parameter. Details of the parameter are in below table

Add Initiate HCM Data Loader Payroll Flow to UpdateAssignmentExtract Payroll Flow

As a next step we would also need to Add “Initiate HCM Data Loader” Payroll flow as part of UpdateAssignmentExtract Payroll Flow. We can either navigate to Payroll->Checklist->Search for UpdateAssignmentExtract Payroll Flow Pattern or use Refine Extracts->Search for UpdateAssignmentExtract

Once the search results are retrieved we need to click on “Edit” (pencil icon) and choose the following task

*Please make sure to select the payroll flow which has the description of  “Generate HCM Data Loader File and optionally perform a Data Load” as the other payroll flow task which has similar name will not serve the purpose here (both have different definition)

Once we add the payroll flow task we should click on “Go To Task” and add the following details:

Now the setup part is complete.

Verification

In order to confirm that the automatic load process is working as expected we need to perform the following steps:

1. Verify Application Data Before Load
2. Run UpdateAssignmentExtract
3. Check the Worker.dat file which is zipped inside UpdateAssignment.zip
4. Verify Application Data After Load

Verify Application Data Before Load

We would navigate to Person Management -> Search for Person Number 898453 and check the Assignment Record.

We could see that the most recent Assignment Record has Effective Start Date as 10/14/18 (i.e 2018/10/14 in YYYY/MM/DD format) and the value of Working at Home field is No

This means that the new Assignment Record should have an Effective Start Date of 10/15/18 (i.e 2018/10/15 in YYYY/MM/DD format) and the value of Working At Home should be Yes .

Run UpdateAssignmentExtract

We would now submit the UpdateAssignmentExtract with Auto Load parameter value as ‘Y’

When we click on “Ok and View Checklist ” it would take us to the checklist page where we can check the status of the tasks.

We can see that the payroll task has successfully completed.

Also, when we check the extract run details for “SampleRun10” we can find that a content id has been generated (DL_WORKER_1416194)

Check the Worker.dat file zipped inside UpdateAssignment.zip

Once we click on the Download icon next to DL_WORKER_1416494 , UpdateAssignment.zip file gets downloaded. The zip file contains Worker.dat file which has the HDL file as prepared by combination of UpdateAssignmentExtract and the UpdateWorkAtHomeFlag eText template. We could see that the transformation rule have been applied and so the effective start date is 2018/10/15 and also WorkAtHomeFlag value is ‘Y’

Verify Application Data After Load

Before we look at the Assignment Detail for Person# 898453 let us have a look at the “ Import and Load Data” screen

And now if we quickly look at the Assignment Record for 898453 23 could see that a new assignment record has got created effective 10/15/2018 and also the Work From Home value has changed to Yes

Summary

So, this is how one can automate the HCM data load in Oracle HCM Cloud Application. While I have only used a single attribute “WorkAtHomeFlag” to demonstrate how this feature works one can use any other attribute or a combination of attribute and update the same.

You may download the Extract Definition, eText RTF and the sample Worker.dat file from below link:

UpdateAssignmentExtract (Extract Definition)

UpdateWorkAtHomeFlag (eText RTF File)

Worker.dat (Sample File Generated by HCM Extract in this example)

With this I have come to the end of the article and I hope this will be of some use to you all.

Thanks all for your time and have a nice day!

Ashish Harbhajanka

Add comment