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.
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.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM