HDL Transformation Formula using HSDL Template for UDT Load
HDL Transformation Formula using HSDL Template for UDT Load

 388 total views

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.
https://fusionhcmknowledgebase.com/tag/udt/

Below are the high level steps that needs to be followed:

  1. Create UDT
  2. Create HSDL Template for UDT
  3. Export the CSV from HSDL and prepare sample input file
  4. Create HDL FF using HSDL Template to load UDT
  5. Finally run the flow “Load Spreadsheet from File”

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:

UserDefinedTable_LegislativeDataGroupName,UserDefinedTable_UserTableCode,UserDefinedTableRow_RowName,UserDefinedTableRow_RowLowRangeOrName,UserDefinedTableRow_EffectiveStartDate,UserDefinedTableColumnInstance_UserColumnCode,UserDefinedTableColumnInstance_Value

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.

Sample File:

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[1] = 'Test_UDT' /*TemplateName*/
METADATA1[2] = 'UserDefinedTable' /*FileDiscriminator*/
METADATA1[3] = 'UserDefinedTable_UserTableCode'
METADATA1[4] = 'UserDefinedTable_LegislativeDataGroupName'
METADATA1[5] = 'UserDefinedTableRow_RowName'
METADATA1[6] = 'UserDefinedTableRow_RowLowRangeOrName'
METADATA1[7] = 'UserDefinedTableRow_EffectiveStartDate'
METADATA1[8]= 'UserDefinedTableColumnInstance_UserColumnCode'
METADATA1[9]= '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:

Test_UDT.CSV -> CSV Export of HSDL Template
Test_UDT.xml -> XML Export of HSDL Template

Hope this post was helpful in making you understand the usage of HSDL templates in HDL Transformation Formula.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

Sricharan

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