HDL Transformation Formula – User-Defined Table(UDT) Load
HDL Transformation Formula – User-Defined Table(UDT) Load

 472 total views

In this article we will look into loading the User-Defined Table (UDT) using the HDL FF by reading a pipe delimited flat file.

If you are new to User-Defined Table, please check out this article

If you are new to HDL Transformation Formula, please go through the below posts to understand better

Once you have the idea on HDL FF and UDT, you can read through the below content to know how to create HDL FF to load the UDT using HDL.

We can load data into the UDT using HDL by using the Business Object UserDefinedTable.dat. This business object has 4 sections, one for creating UDT Properties, UDT Rows, UDT Columns and UDT Column Instances.

If we have a requirement to load data into the UDT on a regular basis on some schedule, we can create the HDL FF to load the Rows and Column Instances using the HDL FF. The basic structure of UDT which includes the UDT Properties and UDT Columns should be created first (either manually or using HDL file) which is a one time task.

Once the structure of UDT is ready, then we can load the rows and column instances for those rows on an on-going basis.

Sample Structure of UDT :

UDT Name – XXHR_UDT
Row – EMPLID
Columns – NAME, COMPANY and SSN

Sample Flat file:

Employee Number|Name|Company|NID
324334234|Test, Employee 1|1234|123456789
234324343|Test, Employee 2||234567809
324324343|Test, Employee 3|1234|432434345

HDL Transformation Formula to convert Flat file to HDL File

/* HDL Transformation Formula to load data into User-Defined Table from a pipe delimited flat file */
/* Author: Sricharan Monigari */
/* Inputs  */
INPUTS ARE  OPERATION (text),
            LINENO (number), LINEREPEATNO (number), POSITION1 (text), POSITION2 (text), POSITION3 (text), POSITION4 (text), POSITION5 (text)

DEFAULT FOR LINENO IS 1
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 LINEREPEATNO IS 1
DEFAULT FOR GLOBAL_PAY_INTERFACE_EXTRACTION_DATE IS ' '

/* Calculations */
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'
   RETURN OUTPUTVALUE
   )
ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN
    (
    METADATA1[1] = 'UserDefinedTable' /*FileName*/ /*Reserved*/
    METADATA1[2] = 'UserDefinedTableRow' /*FileDiscriminator*/ /*Reserved*/
    METADATA1[3] = 'EffectiveEndDate'
    METADATA1[4] = 'EffectiveStartDate'
    METADATA1[5] = 'RowLowRangeOrName'
    METADATA1[6] = 'UserTableCode'
	METADATA1[7] = 'LegislativeDataGroupName'
	METADATA1[8] = 'RowName'

    METADATA2[1] = 'UserDefinedTable' /*FileName*/ /*Reserved*/
    METADATA2[2] = 'UserDefinedTableColumnInstance' /*FileDiscriminator*/ /*Reserved*/
    METADATA2[3] = 'EffectiveEndDate'
    METADATA2[4] = 'EffectiveStartDate'
    METADATA2[5] = 'Value'
    METADATA2[6] = 'LegislativeDataGroupName'
    METADATA2[7] = 'UserColumnCode'
    METADATA2[8] = 'RowLowRangeOrName'
	METADATA2[9] = 'UserTableCode'
    RETURN METADATA1, METADATA2 /*Only two as Return value for NUMBEROFBUSINESSOBJECTS  is 2*/
    )
ELSE IF OPERATION='MAP' THEN
(
    FileName           = 'UserDefinedTable'
    BusinessOperation  = 'MERGE'
	UserTableCode      = 'XXHR_UDT'
	EffectiveStartDate = '2000/01/01'
	EffectiveEndDate   = '4712/12/31'
	LegislativeDataGroupName = 'US LDG'
	TODAYS_DATE = TO_CHAR(TO_DATE(GLOBAL_PAY_INTERFACE_EXTRACTION_DATE, 'YYYY/MM/DD'), 'YYYYMMDD')
	RowLowRangeOrName = trim(replace(POSITION1,'"',' ')) || '-' || TODAYS_DATE
	FIRST_COLUMN = trim(replace(POSITION1,'"',' '))
	RowName = trim(replace(POSITION1,'"',' ')) || '-' || TODAYS_DATE
	
		IF LINEREPEATNO=1 THEN
		 (
           IF FIRST_COLUMN = 'Employee Number' OR FIRST_COLUMN = 'Employee_Number' THEN
		     (LINEREPEAT = 'N'
			  /*dummy = ESS_LOG_WRITE('Skipping Header Row')*/
		      RETURN LINEREPEAT) /* To skip header row */
		   ELSE
		     (
		      FileDiscriminator='UserDefinedTableRow'
		      LINEREPEAT = 'Y'
              /*dummy = ESS_LOG_WRITE('FileDiscriminator: '||FileDiscriminator)
              dummy = ESS_LOG_WRITE('LINEREPEAT: '||LINEREPEAT)*/			   
		      RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveEndDate,EffectiveStartDate,RowLowRangeOrName,UserTableCode,LegislativeDataGroupName,RowName,LINEREPEAT,LINEREPEATNO			 
			 )
		 )
		ELSE
		(
         IF LINEREPEATNO>1 and LINEREPEATNO < 5 THEN
         (
		   If LINEREPEATNO = 4 then
		      (LINEREPEAT = 'N')
		   else
		      (LINEREPEAT = 'Y')

		   FileDiscriminator='UserDefinedTableColumnInstance'
		   If LINEREPEATNO = 2 AND POSITION2 <> 'NO DATA' THEN
		      (UserColumnCode = 'NAME'
			  Value = trim(replace(POSITION2,'"',' ')))
		   else if LINEREPEATNO = 3 AND POSITION3 <> 'NO DATA' THEN
		      (UserColumnCode = 'COMPANY'
			  Value = trim(replace(POSITION3,'"',' ')))
		   else if LINEREPEATNO = 4 AND POSITION4 <> 'NO DATA'  THEN
		      (UserColumnCode = 'SSN'
			  Value = trim(replace(POSITION4,'"',' ')))
           else IF LINEREPEATNO < 5 THEN
		      (RETURN LINEREPEAT)
			  
           /*dummy = ESS_LOG_WRITE('FileDiscriminator: ' || FileDiscriminator || ' - ' || 'UserColumnCode: '||UserColumnCode || ' - ' || 'Value: '|| Value)*/

			  if isnull(Value) = 'N' then
			  (
			    If LINEREPEATNO = 4 then
		           (LINEREPEAT = 'N')
		        else
		           (LINEREPEAT = 'Y')
				RETURN LINEREPEAT
			  )		   

		   RETURN BusinessOperation,FileName,FileDiscriminator,EffectiveEndDate,EffectiveStartDate,Value,LegislativeDataGroupName,UserColumnCode,RowLowRangeOrName,UserTableCode,LINEREPEAT,LINEREPEATNO
		   )
        )
)
ELSE
    OUTPUTVALUE='NONE'
RETURN OUTPUTVALUE
/* End Formula Text */

This HDL FF will read the input file and convert it to HDL file loading the rows and column instance values for those corresponding rows. I have added logic to add the system date concatenated to the row value as those row values might get repeated again. In the query we have to handle that date by trimming it.

The HDL FF will skip the record if it encounters the Header record
The HDL FF will loop through multiple times using the LINEREPEAT flag till all the columns are read through.
The HDL FF will not create a column instance row if the input file doesn’t have value for a column.

Sample Output HDL file that gets created with this FF:

METADATA|UserDefinedTableRow|DisplaySequence|EffectiveEndDate|EffectiveStartDate|RowHighRange|RowLowRangeOrName|UserTableCode|LegislativeDataGroupName|RowName
MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||324334234-20200827|XXHR_UDT|US LDG|324334234-20200827
MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||234324343-20200827|XXHR_UDT|US LDG|234324343-20200827
MERGE|UserDefinedTableRow||4712/12/31|2000/01/01||324324343-20200827|XXHR_UDT|US LDG|324324343-20200827
METADATA|UserDefinedTableColumnInstance|EffectiveEndDate|EffectiveStartDate|Value|LegislativeDataGroupName|UserColumnCode|RowLowRangeOrName|UserTableCode
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 1|US LDG|NAME|324334234-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|1234|US LDG|COMPANY|324334234-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|123456789|US LDG|SSN|324334234-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 2|US LDG|NAME|234324343-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|234567809|US LDG|SSN|234324343-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|Test, Employee 3|US LDG|NAME|324324343-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|1234|US LDG|COMPANY|324324343-20200827|XXHR_UDT
MERGE|UserDefinedTableColumnInstance|4712/12/31|2000/01/01|432434345|US LDG|SSN|324324343-20200827|XXHR_UDT

We can see that DAT file has been generated to create the rows and column instances for the input data.

Hope this helps when you have similar requirement on Uuser-Defined Tables. Feel free to modify it as necessary.

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