HDL Transformation Fast Formula

Type – HCM Data Loader

In Oracle Fusion HCM, an HCM Data Loader formula is a Fast Formula with the formula type set to HCM Data Loader. It is used when the source file is not already in standard HDL format and needs to be transformed before loading. Instead of manually preparing a .dat file, the formula reads each source-file row and converts it into the metadata and merge lines required by HCM Data Loader.

This type of formula is especially useful for integrations and scheduled automations because it separates source-data generation from HDL formatting. A BI Publisher report, third-party extract, payroll vendor file, or custom text file can produce a simple delimited output, and the HCM Data Loader formula can take responsibility for converting that output into the correct HDL business-object structure.

The formula works by responding to specific operations sent by the Load Data From File process. For example, the FILETYPE operation tells the process whether the source file is delimited or fixed-width, the DELIMITER operation defines the separator used in the file, and the METADATALINEINFORMATION operation returns the HDL metadata structure. The most important operation is usually MAP, where the formula maps each incoming position value to the corresponding HDL attribute.

In this blog’s solution, the formula reads the pipe-delimited BI Publisher output and maps fields such as performance document name, assignment number, manager person number, participant role, section name, section type, and rating name. It then creates the required PerfDocComplete and RatingsAndComments HDL rows so that the calculated overall rating can be loaded back into the employee’s performance document.

A key advantage of this approach is flexibility. If the report output changes, only the mapping logic in the formula may need to be adjusted. The business object structure, file naming, and HDL load sequence can remain consistent. This makes the design easier to maintain and suitable for recurring integrations where the same transformation must run repeatedly.

Key Parts of the Formula

Line repeat logic: LINEREPEATNO allows one source row to generate more than one HDL row, which is useful when a parent and child component must be loaded together.

Inputs: The formula receives values such as OPERATION, LINENO, LINEREPEATNO, and positional fields like POSITION1, POSITION2, and so on.

Defaults: Default values protect the formula from blank or missing fields and help avoid runtime failures during transformation.

File configuration: The formula defines the source file type and delimiter so the process knows how to read the incoming file.

Metadata definition: The formula returns the HDL component names and attributes required for the target business objects.

Mapping logic: The formula assigns each incoming source value to the correct HDL attribute and returns the output row to be generated.

Formula Example –

			
/**********************************************************
FORMULA NAME : GENERATE_PERFDOC_COMPLETE_HDL
FORMULA TYPE : HCM Data Loader
DESCRIPTION  : Generate Performance Document HDL
**********************************************************/
INPUTS ARE
OPERATION (text),
LINENO (number),
LINEREPEATNO (number),
POSITION1 (text),
POSITION2 (text),
POSITION3 (text),
POSITION4 (text),
POSITION5 (text),
POSITION6 (text),
POSITION7 (text),
POSITION8 (text),
POSITION9 (text),
POSITION10 (text),
POSITION11 (text),
POSITION12 (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 POSITION6 IS 'NO DATA'
DEFAULT FOR POSITION7 IS 'NO DATA'
DEFAULT FOR POSITION8 IS 'NO DATA'
DEFAULT FOR POSITION9 IS 'NO DATA'
DEFAULT FOR POSITION10 IS 'NO DATA'
DEFAULT FOR POSITION11 IS 'NO DATA'
DEFAULT FOR POSITION12 IS 'NO DATA'
DEFAULT FOR LINEREPEATNO IS 1
DEFAULT FOR LINENO IS 1
/**********************************************************
FILE CONFIGURATION
**********************************************************/
IF OPERATION = 'FILETYPE' THEN
(
   OUTPUTVALUE = 'DELIMITED'
   RETURN OUTPUTVALUE
)
ELSE IF OPERATION = 'DELIMITER' THEN
(
   OUTPUTVALUE = '|'
   RETURN OUTPUTVALUE
)
ELSE IF OPERATION = 'READ' THEN
(
   OUTPUTVALUE = 'NONE'
   RETURN OUTPUTVALUE
)
ELSE IF OPERATION = 'NUMBEROFBUSINESSOBJECTS' THEN
(
   OUTPUTVALUE = '2'
   RETURN OUTPUTVALUE
)
/**********************************************************
METADATA
**********************************************************/
ELSE IF OPERATION = 'METADATALINEINFORMATION' THEN
(
   /****************************************
   OBJECT 1 : PerfDocComplete
   ****************************************/
   METADATA1[1] = 'PerfDocComplete'
   METADATA1[2] = 'PerfDocComplete'
   METADATA1[3] = 'AssignmentNumber'
   METADATA1[4] = 'CustomaryName'
   METADATA1[5] = 'Operation'
   /****************************************
   OBJECT 2 : RatingsAndComments
   ****************************************/
   METADATA2[1] = 'PerfDocComplete'
   METADATA2[2] = 'RatingsAndComments'
   METADATA2[3] = 'CustomaryName'
   METADATA2[4] = 'AssignmentNumber'
   METADATA2[5] = 'ParticipantPersonNumber'
   METADATA2[6] = 'ParticipantRoleTypeCode'
   METADATA2[7] = 'SectionName'
   METADATA2[8] = 'SectionTypeCode'
   METADATA2[9] = 'RatingName'
   RETURN METADATA1, METADATA2
)
/**********************************************************
MAP LOGIC
**********************************************************/
ELSE IF OPERATION = 'MAP' THEN
(
   /* SKIP HEADER */
   IF POSITION1 = 'PERSON_NUMBER' THEN
   (
      LINEREPEAT = 'N'
      RETURN LINEREPEAT
   )
   BusinessOperation = 'MERGE'
   /************************************************
   FIRST OBJECT : PerfDocComplete
   ************************************************/
   IF LINEREPEATNO = 1 THEN
   (
      LINEREPEAT = 'Y'
      FileName          = 'PerfDocComplete'
      FileDiscriminator = 'PerfDocComplete'
      AssignmentNumber = TRIM(POSITION4)
      CustomaryName    = TRIM(POSITION2)
      
      /* Yahan par humne input parameter ko overwrite kar diya hai */
      OPERATION        = TRIM(POSITION9)
      RETURN BusinessOperation,
             FileName,
             FileDiscriminator,
             AssignmentNumber,
             CustomaryName,
             OPERATION,
             LINEREPEAT,
             LINEREPEATNO
   )
   /************************************************
   SECOND OBJECT : RatingsAndComments
   ************************************************/
   ELSE IF LINEREPEATNO = 2 THEN
   (
      LINEREPEAT = 'N'
      FileName          = 'PerfDocComplete'
      FileDiscriminator = 'RatingsAndComments'
      CustomaryName             = TRIM(POSITION2)
      AssignmentNumber          = TRIM(POSITION4)
      ParticipantPersonNumber   = TRIM(POSITION5)
      ParticipantRoleTypeCode   = TRIM(POSITION10)
      SectionName               = TRIM(POSITION11)
      SectionTypeCode           = TRIM(POSITION12)
      RatingName                = TRIM(POSITION3)
      RETURN BusinessOperation,
             FileName,
             FileDiscriminator,
             CustomaryName,
             AssignmentNumber,
             ParticipantPersonNumber,
             ParticipantRoleTypeCode,
             SectionName,
             SectionTypeCode,
             RatingName,
             LINEREPEAT,
             LINEREPEATNO
   )
)
ELSE
(
   OUTPUTVALUE = 'NONE'
   RETURN OUTPUTVALUE
)
RETURN OUTPUTVALUE
/*********************** END ************************/

		