In this article we will look into the basics of HCM Data Loader(HDL) Transformation Formula and some of the FAQs around it
Table of Contents
What is HDL Transformation Formula?
HDL Transformation Formula is a replacement for PBL (Payroll Batch Loader) Transformation Formula which was used to automate the loading of payroll related business objects from a flat file (.csv/.txt). As PBL is getting deprecated by 20D, the PBL formulas will also not work post 20D and hence Oracle has provided us an alternative in the form of HDL Transformation Formula.
With HDL Transformation Formula (aka HDL FF), we can get a flat file (fixed/delimited) converted into HDL DAT file format and get it loaded using HDL.
Where should we create HDL Transformation Formula?
HDL Transformation Formula is a Fast Formula with type as “HCM Data Loader” and it should be created from Fast Formulas task.
What are the use cases of HDL FF?
Most of the benefit/payroll vendors send the output files in a flat file with some delimiter. And Fusion system needs to read that and load it into the system. Previously PBL was capable of reading flat file and loading data into the system(PBL was used for payroll objects only). However it is being decommissioned by 20D release and hence Oracle released HDL FF from 19C release and they are introducing new functionality or features to it with every release.
90% of the customers who had benefits/timecards integrations used PBL transformation formula to load the element entries into Fusion. Similarly if there are any third party systems sending over data in flat file, this HDL FF can be used to convert it to HDL DAT format and load it into the system.
What business objects does HDL FF support?
As per the Oracle documentation, it supports creation of HDL FF for all business objects which are supported by HDL. As this FF converts the flat file data into HDL DAT file and then it’s the underlying HDL tool that loads the data into the system.
Can we load multiple business objects using single HDL FF?
Yes we can load multiple business objects using HDL FF. Most common requirement would loading the Element Entry and Costing Information. With a single line of element entry and costing data in the input file, you can write HDL FF and convert into ElementEntry.dat, CostAllocationV3.dat and CostAllocationAccountV3.dat and then zip all these three files and load it at one time.
Are there any samples available for HDL Transformation Formula?
Yes. Oracle has provided us a couple of Sample HDL FFs that we can use to learn HDL FF format and syntax. Click here for Sample HDL FFs
Does the HDL FF support both Fixed and Delimited files?
As per Oracle documentation it isn’t clear, however the product managers in Cloud Customer Connect mentioned that both formats are supported. However for both formats, we need to provide the syntax in the HDL FF as “DELIMITED” only.
How to invoke this HDL FF?
HDL Transformation Formula can be invoked using a payroll flow “Load data from file“. This Flow consists of two flow tasks:
1) Generate Data Loader File – This flow uses the Content ID and HDL FF as input and it reads the input file and applies the logic listed in HDL FF and creates HDL DAT files and zips them and uploads to UCM.
2) Initiate Data Loader –
Navigate to Payroll > Checklist
From right hand side tasks, select Submit a Payroll Flow
Search for the Flow “Load data from file“
Payroll Flow Name – Unique flow name
Content ID – Input content ID from UCM. Upload the input flat file to UCM and the Content ID should be input here. The input file can be in .txt/.csv/.dat format with delimiter or fixed format. This input file shouldn’t be zipped before uploading the UCM.
Transformation Formula – This is the place where we will input the HDL FF that will be developed to convert input file to HDL DAT file. Type of Formula should be HCM Data Loader only.
Process Configuration Group – This is used to specify any multi threading capability and logging enabled for tracing formula. These needs to be setup in Manage Payroll Process Configuration Quick Actions under My Client Groups.
How to check the Output of HDL FF?
Once the flow is run, we can check the status of these tasks from Payroll > Checklist. If you have any error, you can check the log files to get the details of error.
Can this HDL FF flow be invoked from Web Services?
Yes. We can invoke the HDL FF process using Web Services using the generic Payroll Flow SOAP services (Flow Actions Service web service).
Can we load the PGP encrypted files using HDL flow?
Yes we can load the PGP encrypted files as well using this HDL flow. You need to enable and specify the Payroll Batch Loader Encryption Type parameter on the payroll process configuration group. Valid values are PGPSIGNED, PGPUNSIGNED and NONE. We need to input the Payroll Process Configuration group while executing the flow in order to read the encrypted file.
Ensure that the encryption keys exist for the secure file transfer. This process involves creating a service request, generating PGP key pairs, and sharing the encryption keys.
Can we use HDL Transformation formula to invoke HSDL?
No. This is not supported. HDL Transformation Formula works with HDL only. It doesn’t work with HSDL. HDL FF and HSDL both use HDL in backend to process data.
Can we have headers on the input file? Can we skip them in HDL FF?
Yes we can have Header row on the input file. And we can skip the Header row from processing it by adding condition to check for the POSITION as header string and then return only the LINEREPEAT flag of N. So that the Header record will be skipped and the next line is taken up for processing.
Can we add COMMENTS to the HDL DAT file using HDL Transformation Formula?
Yes we can add the COMMENTS section at the top of the HDL DAT file for any instructions that can be used to inform others of the data that is being uploaded by the file.
Can we add the SET instructions on the HDL DAT file using HDL FF?
Currently the SET instructions are not supported by the HDL FF. So if you are planning to load Worker object and if you intend to use SET PURGE_FUTURE_CHANGES N or if you want to trigger life events, then you cannot use this method, as the HDL FF cannot set that flag and you will end up losing future data.
In the next article we will look into the building blocks of HDL FF and what coding we need to do in HDL FF to convert flat file to HDL DAT file.
Tip: HDL FF can be used to automate the HDL loads without using any middle-ware like OIC/ DellBoomi/ Mulesoft/ SOACS. And HDL supports lot of business objects than PBL which is getting decommissioned.
