Advanced HCM data loader (HDL) processing using OIC HCM adapter.


Oracle Integration Cloud (OIC) Integration provides adapter for most popular SaaS applications, including Oracle HCM Cloud.  This post focuses on integrating with HCM data loader (HDL) tool of HCM using OIC HCM adapter, multiple HDL file processing and fault handling. Information in this post is applicable to release 18.3.1 of OIC.

HCM Adapter basics

Refer to documentation here, for more information about HCM adapter. At a high level, HCM adapter makes it easier to integrate with HCM cloud using REST , HDL. HCM Extracts and ATOM feeds. As with all OIC adapters,a connection is first created using  Oracle HCM Cloud adapter with endpoints and credentials to the target HCM cloud environment. This connection is then used as invocation target in integration flows, at which time the specifics of the integration such as HDL or ATOM feeds is configured. Note that HCM adapter does not generate HDL-formatted file at this time, although this can be done using OIC. Refer to another A-team blog for guidance on generating HDL files within an integration flow. Below are the sample configuration screens for using HCM adapter for HDL imports.

Figure:HCM adapter connection


Figure:HCM adapter invoke configuration for HDL

Integration logic and Implementation

The integration flow elaborated here was demonstrated to a customer who had the following requirements:

  • Multiple HDL files from several source systems should be processed for each run, from FTP location
  • Files should be processed in alpha numeric order of file name
  • For each file, a email notification with processing status must be sent
  • If there are failures prior to submission, processing for the file must be repeated, upto 3 times

Here is a graphical representation of the flow.

Figure:Integration logical flow

Multiple file processing

There are two steps in processing the HDL files for this use case. First, a list of files at the FTP location that match a pattern is obtained. OIC FTP adapter provides a “List Files” action for this purpose. This list is sorted by last-updated date and time by FTP adapter. Once a list of files is obtained, next step is to sort this list by filename. A comma separated list of filenames is prepared and sent to a javascript function for sorting. This is done to demonstrate the ability to implement custom logic using JavaScript.  With a sorted list of files names, each file is downloaded into OIC and sent to HCM cloud for processing. For more information on file processing in OIC, refer to this A-team blog.

Figure:List files in an FTP location


JavaScript function to sort filenames

JavaScript functions can be developed with any editor and then registered with OIC. In order to be used in an integration, a function must return a value. A JavaScript library could have other functions, used within the library. Refer to OIC documentation for more information on extending using JavaScript.

/** Function to sort CSV list of values
 * @param {string} csvString - list of strings separated by comma
 * @returns {string} sorted list of strings
function csvListSort(csvString)
	var sortedCsvString=csvString.slice(0, -1).split(",").sort().join(",");
	return sortedCsvString;
/** Function to return first item in CSV list of values
 * @param {string} csvString - list of strings separated by comma
 * @returns {string} first or the only item. Empty if the input is blank
function csvReturnFirst(csvString)
	var strArray=csvString.split(",");
	var sortedCsvString=strArray[0];
	return sortedCsvString;

Code Snippet:Java script functions to sort list of filenames and to get the first item on list

Figure:Registering a JavaScript library

Implementing retry logic

Note that several OIC adapters have built-in retry logic. This improves resiliency of Integrations deployed on OIC even without an explicit retry. Additional logic explained here improves the integration further, if desired.  Each HDL file in the list is downloaded into OIC, encoded into a Base64 string and uploaded into UCM endpoint on the same HCM application instance. UCM service invoked over a SOAP endpoint, using SOAP adapter. Upon successful upload into UCM, HDM Import and Load function is invoked. OIC provides scope feature, where a set of activities can be placed inside a scope and associated with one or more exception handlers. Any exception within the scope will trigger the exception handler. In this implementation, retry logic is implemented as follows:

  • File download, UCM and HDL activities are placed inside a scope, in a while loop
  • A retry counter is set to 3 and a exception flag set to false before processing a file
  • If there is an exception, an email notification is sent and exception flag is set to true
  • Upon exiting the scope, if exception flag is true, while loop repeated, until retry counter is 0
  • Retry counter is reduced for each retry for a specific file and reset to 3 for each new file

Figure:A glance at use Scope to define activities for retry-logic

Finding status of HDL job

Finally, the integration also sends the status of HDL import in HCM cloud, through a notification. The HDL getDataSetStatus operation is repeated upto 5 times or until the status is either SUCCESS or ERROR, with one minute delay before the next status check. Until processing completes, the status of the data set remains UNKNOWN. Processing time for HDL files might vary, depending on the size of the file and other activities on HCM cloud. As a result, obtaining a final status of SUCCESS or ERROR might not always be practical. After 5 attempts, if the status remains UNKNOWN, the same is sent via notification.

Figure:Logic to check HDL status


In this post ,we looked at some techniques for advanced HDL file processing using Oracle Integration Cloud. The snippets and techniques provided can also be used as a basis for other integration implementations on OIC.


Ateam blog on using ICS to integrate with HCM cloud: Data into Oracle HCM Cloud Using ICS

Using HCM Cloud Adatper:

OIC file handling primer:

Integration Cloud – File Handling Primer

Add Your Comment