In this article we will look into the HSDL Transformation Formula to load the User Defined Table object using the HSDL Template.
It is similar to HDL Transformation formula as it uses the same Formula Type of “HCM Data Loader“, however it uses the HSDL template as well to pass the input file values to HSDL Template Fields and there is a separate flow which needs to be used.
Let’s take the sample business object as User-Defined Table which we will try to load using HDL FF and HSDL Template.
If you are new to UDT, please go through the below posts before proceeding further to get an idea on What is UDT, how to create it and how to load it using HDL, HDL FF.
Below are the high level steps that needs to be followed:
- Create UDT
- Create HSDL Template for UDT
- Export the CSV from HSDL and prepare sample input file
- Create HDL FF using HSDL Template to load UDT
- Finally run the flow “Load Spreadsheet from File”
Table of Contents
1. Creating User-Defined Table
I have created the UDT with Name “TEST_UDT” which looks like below.
2. Create HSDL Template for UDT
I have created an HSDL template (Test_UDT) by selecting the fields LDG, UDT Name, Row, Column Instance values like below:
After creating the template we can export it to XML/CSV format to check the header data for the selected fields.
XML will look like:
CSV will look like:
3. Export the CSV from HSDL and prepare sample input file
Date format should be YYYY/MM/DD on the input file. Else the load would fail even though the process runs to success on the payroll flow.
We need to prepare the sample file based on the CSV export that was taken in the above step.
US Legislative Data Group|TEST_UDT|12345|2020/09/01|FIRST_COLUMN|12345_1ST_COL_VALUE US Legislative Data Group|TEST_UDT|12345|2020/09/01|SECOND_COLUMN|12345_2ND_COL_VALUE
4. Create HDL FF using HSDL Template to load UDT
We need to use the fields from CSV/XML Export in the HDL FF and assign values to those fields from the input file. We also need to input the HSDL Template Name in the HDL FF.
HDL FF using HSDL Template:
/* HDL FF using HSDL Template to load User-Defined Table */ /* FF Name: TEST_UDT_HSDL_FF */ /* Created by Sricharan Monigari */ /* Inputs */ INPUTS ARE OPERATION (text), LINENO (number), LINEREPEATNO (number),POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text), POSITION6 (text), POSITION7 (text), POSITION8 (text) DEFAULT FOR POSITION1 IS 'NO DATA' DEFAULT FOR POSITION2 IS 'NO DATA' DEFAULT FOR POSITION3 IS 'NO DATA' DEFAULT FOR POSITION4 IS 'NO DATA' DEFAULT FOR POSITION5 IS 'NO DATA' DEFAULT FOR LINEREPEATNO IS 1 IF OPERATION='FILETYPE' THEN OUTPUTVALUE='DELIMITED' ELSE IF OPERATION='DELIMITER' THEN OUTPUTVALUE='|' ELSE IF OPERATION='READ' THEN OUTPUTVALUE='NONE' ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN ( OUTPUTVALUE = '1'/*Always be 1*/ RETURN OUTPUTVALUE ) ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN ( METADATA1 = 'Test_UDT' /*TemplateName*/ METADATA1 = 'UserDefinedTable' /*FileDiscriminator*/ METADATA1 = 'UserDefinedTable_UserTableCode' METADATA1 = 'UserDefinedTable_LegislativeDataGroupName' METADATA1 = 'UserDefinedTableRow_RowName' METADATA1 = 'UserDefinedTableRow_RowLowRangeOrName' METADATA1 = 'UserDefinedTableRow_EffectiveStartDate' METADATA1= 'UserDefinedTableColumnInstance_UserColumnCode' METADATA1= 'UserDefinedTableColumnInstance_Value' RETURN METADATA1 /*You can return only one METADATA for the respective template*/ ) ELSE IF OPERATION='MAP' THEN ( FileName = 'Test_UDT' BusinessOperation = 'HSDL' FileDiscriminator = 'UserDefinedTable' UserDefinedTable_LegislativeDataGroupName = POSITION1 UserDefinedTable_UserTableCode = POSITION2 UserDefinedTableRow_RowName = POSITION3 UserDefinedTableRow_RowLowRangeOrName = POSITION3 UserDefinedTableRow_EffectiveStartDate = POSITION4 UserDefinedTableColumnInstance_UserColumnCode = POSITION5 UserDefinedTableColumnInstance_Value = POSITION6 RETURN BusinessOperation,FileDiscriminator,FileName,UserDefinedTable_UserTableCode,UserDefinedTable_LegislativeDataGroupName,UserDefinedTableRow_RowName,UserDefinedTableRow_RowLowRangeOrName,UserDefinedTableRow_EffectiveStartDate,UserDefinedTableColumnInstance_UserColumnCode,UserDefinedTableColumnInstance_Value ) ELSE OUTPUTVALUE='NONE' RETURN OUTPUTVALUE /* End Formula Text */
5. Finally run the flow “Load Spreadsheet from File”
Submitting the “Load Spreadsheet from File” flow for the HSDL FF. HRC_LOAD_DATA_USING_HSDL_PRIV privilege is required to access this Flow Pattern.
Load Spreadsheet from File contains two tasks:
1.Generate Data Loader File task reads the data file line by line, producing an equivalent HCM Spreadsheet Data Loader format for each line. Finally, it creates a compressed file of all of the transformed data files and uploads it to Oracle WebCenter Content server.
2.Initiate Spreadsheet Data Loader task takes the compressed file generated by the Generate Data Loader File task and invokes the HCM Spreadsheet Data Loader. The HCM Spreadsheet Data Loader creates the required data in the HCM Cloud.
Once the status turns to successful, we need to verify the data on Import and Load data screen by clearing the Created By field
Let’s check the UDT to verify if the data got loaded:
Dat file generated by the Generate Data Loader File which will be input to Initiate Spreadsheet Data loader task.
Advantages of this approach:
HDL Business object can have multiple parent-child relationships and if we have to create HDL template, we will have to create multiple METADATA one for each parent and child and then loop through the FF multiple times for each line of input data. Instead if we go with this approach and create an HSDL template, we can have Parent and Child on a single line and we are better off with a simple HDL FF.
HSDL Template Files used:
Hope this post was helpful in making you understand the usage of HSDL templates in HDL Transformation Formula.