Running the latest extract version using Payroll Flow
Running the latest extract version using Payroll Flow

 628 total views

In this article we will look into the familiar issue of versioning of HCM Extracts and the issues faced with it and the work around for those issues.

Normally we develop the HCM Extract in DEV/TEST and after proper testing and UAT that interface will be moved to Production. In Production the interface will be scheduled to send over the output to third party systems using FTP/Email.

The requirements keep changing/there might be issues with existing extract and we have to modify the extract in DEV/TEST. Now when you are moving the changes, you cannot manually do the changes for HCM Extract in PROD as you might have controlled access or the list of changes is huge and you might miss some changes if you update the existing one. What we do is create a new version of extract and use that going forward in Production.

We cannot delete the HCM Extracts if those are run atleast once. We can at max inactivate them for them not to be shown over in the HCM Extract search results/payroll flow search results.

If the extract has already been scheduled in PROD, then that schedule needs to be stopped and the new version should be scheduled again. We need to repeat this process whenever there are changes to this extract and they are migrated to PROD.

In this article I am going through a work around using which a single payroll flow can fetch the latest extract version automatically and there is no need to cancel the existing schedule as it will fetch the new extract and run it whenever a new version of extract is created.

We need to follow some steps and the extract naming convention should be followed to achieve this functionality.

Extract Naming Convention should have a fixed string concatenated with Version Number. Ex: SM_Demo_V1, SM_Demo_V2 etc. or it can also be like SMDEMOV1 or SMDEMOV2.

For ease of use, I will consider an existing Extract instead of trying to create a new one from scratch.

Consider the extract name as SM_Demo_V1. So by default a Payroll Flow Pattern will be created with SM_Demo_V1 name. And normally if we have to run the extract, we will navigate to Payroll > Checklist > Submit a Process or Report and search for SM_Demo_V1 and then run the extract.

Snapshot of Extract Report Category and Output Name
Snapshot of Extract Report Category and Output Name

Here is the snapshot of the Extract SM_Demo_V1. By Default the Report Category is created with the Extract Name. Report Category is the important driving field while submitting the extract which derives which extract version needs to be run.

Now Let’s clone the SM_Demo_V1 payroll flow into SM_Demo_Interface.

Navigate to Payroll > Checklist > Manage Payroll Flow Patterns.
Search for SM_Demo_V1, Click on the Copy flow button and provide the new flow name as “SM_Demo_Interface

Cloned flow which will fetch the latest version
Cloned flow which will fetch the latest version

Once we created the flow. Open the flow

Modify the Report Category to add the Post-SQL to fetch latest version

Navigate to Parameters tab and Click on Edit button at the right top to modify the flow.

Now, we will modify the Post-SQL for Report Category

The default Report Category will have Post-SQL like below:

SELECT to_char(report_category_id)
FROM pay_report_categories
WHERE base_category_name = 'SM_Demo_V1'

Now, we will use the below post-SQL to fetch the latest version of extract:

SELECT to_char(report_category_id)
FROM pay_report_categories
WHERE base_category_name LIKE 'SM_Demo_V%'
AND replace(base_category_name, 'SM_Demo_V', '') = (
SELECT max(replace(base_category_name, 'SM_Demo_V', ''))
FROM pay_report_categories
WHERE base_category_name LIKE 'SM_Demo_V%')

Modify the ‘SM_Demo_V‘ with the extract name that you are planning to use. This query pulls the Report Category Name of the Latest Extract version. Report Category is the key for executing the extract.

Now, let’s run the payroll flow and check the XML file.

It shows the Report Category Name as SM_Demo_V1 as we have only one version of the extract.

Now, lets’ create the V2 version of the extract.

SM_Demo_V2 version of extract
SM_Demo_V2 version of extract

We can see that the V2 version has the Report Category as “SM_Demo_V2” which is the extract name.

Now when I run the SM_Demo_Interface flow it picks up the “SM_Demo_V2” Report Category and the extract and runs it.

SM_Demo_Interface ran to success
SM_Demo_Interface ran to success

Let’s check the parameters passed to this run.

We can see that Report Category is passed as SM_Demo_V2

Even though the Base Extract Name is passed as SM_Demo_V1, the extract tagged to the Report Category will run which in our case is SM_Demo_V2 and generates the output file accordingly.

XML showing SM_Demo_V2 being picked up
XML showing SM_Demo_V2 being picked up

Note: This is one of the interesting feature of HCM Extracts and can be used when working on multiple changes to the same extract. Report Category is the key driving factor to get the latest version of the Extract when submitting the payroll flow.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply