Running the latest extract version using Payroll Flow

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.

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“

Once we created the flow. Open the flow

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.

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.

Let’s check the parameters passed to this run.

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.

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.