Native Schema for Oracle HCM Cloud HDL Data Format

Introduction

This post supplements another post "Load Data into Oracle HCM Cloud Using ICS" by providing more details on creating NXSD schema for HDL format.

Oracle Fusion Apps (cloud or on-prem) provide text file based batch data loading capabilities through a SOAP API. This feature can be used for integrating external applications with HCM Cloud via ICS and/or SOACS.  The text files are in different native formats depending on the specific Fusion application being discussed. For example, ERP Cloud requires CSV format and HCM requires a much more complex native format called HDL (HCM Data Loader) format. When integrating Fusion Apps Cloud with ICS or SOACS, translations between XML and native format are often required. This translation can be performed in a file adapter or an FTP adapter in ICS and SOACS. In ICS, the focus of this article, there is a Stage File Activity (currently available as an early adopter feature) that allows reading and writing temporary files with translation.  In all cases, translation rules are defined in a native schema file (.nxsd). So building a correct NXSD file becomes a critical task. For CSV, a commonly used native format, there is a wizard tool available for drag and drop mapping between XML and fields in native format. However, for a more complex native format such as HDL, the wizard does not provide much help.

This post demonstrates a way to manually create a native schema for HCM HDL format. Using this native schema as a working example and a starting point, other variations of native schemas for HDL format can be built quickly for future projects.

Testing Tool

When creating an NXSD file manually, it is very beneficial to test your NXSD file in small increments and test often. I found this command line NXSD tool blogged by Srimant very helpful. You can find details on how to run this tool on Srimant's blog.

HCM HDL Format

In my experience, the best way to create an NXSD schema is by starting from the native schema. So first let's look at the HDL format in detail.

We will use HCM Talent Profile data as our target to translate. A sample of Talent Profile data is shown in the following image and can be downloaded at TalentProfile.dat.

From the sample Talent Profile data file, we can see the following characteristics of HDL format:

• Field separator is | (a vertical line)
• Each line contains either metadata that describes objects that follow or an data object itself
• The metadata line starts with METADATA followed by the object name. The rest of the fields in the line are object property names
• The data object line starts with an operation name (MERGE, DELETE and etc.) followed by the same object name as the one in the metadata line. The rest of the fields are property values corresponding to the property names specified in the metadata line.
• Though not shown in the sample, there can be multiple data object lines under each object metadata line.

The sample Talent Profile data file contains two HCM objects: TalentProfile and ProfileItem. TalentProfile object is specified by the metadata in line one and object value in line two. ProfileItem object is specified in line three and four.

Parsing HDL Data File

Identifying field structure of a line

Compared to the more common CSV format, HDL format has one important distinction:

Each line in an HDL file can have different field structure while a CSV file usually contains the same field structure for all lines in the same file.

So in order to parse an HDL file, we must first identify the field structure of each line in it. For the sample Talent Profile file, we can use the first two fields of each line as an indicator for its field structure:

• "METADTA|TalentProfile|" indicates a metadata line for object name TalentProfile
• "MERGE|TalentProfile|" indicates a data line for object value of TalentProfile
• "METADTA|ProfileItem|" indicates a metadata line for object name ProfileItem
• "MERGE|ProfileItem|" indicates a data line for object value of ProfileItem

In NXSD schema, we can use a conditional parse attribute "startsWith" to identify field structure of each line. The following NXSD schema element instructs the parser that if a line starts with "METADATA|TalentProfile|", parse it based on the element type TalentProfileLabel.

 <element ref="tns:TalentProfileLabel" minOccurs="0" nxsd:startsWith="METADATA|TalentProfile|" />

Handling Order of Data Objects

In HDL, a metadata line is always followed by one more more lines of data object values of the same type. However, different data objects (metadata line and data lines combined) can appear in different order in an HDL file.

NXSD provides a "choice" construct to allow testing of each line against a set of predefined conditional elements in order to determine the correct element type of the line. For our sample data file, there are four different types of lines. The following choice construct tests each line in our sample file against all four possible conditional elements. A match is guaranteed. Also notice that the choice construct supports multiple data object lines under any metadata line as well.

<choice maxOccurs="unbounded" minOccurs="0">
    <element ref="tns:TalentProfileLabel" minOccurs="0" nxsd:startsWith="METADATA|TalentProfile|"/>
    <element ref="tns:TalentProfile" minOccurs="0" nxsd:startsWith="MERGE|TalentProfile|"/>
    <element ref="tns:ProfileItemLabel" minOccurs="0" nxsd:startsWith="METADATA|ProfileItem|"/>
    <element ref="tns:ProfileItem" minOccurs="0" nxsd:startsWith="MERGE|ProfileItem|"/>
</choice>

Completing the Object Mapping Schema

To complete the NXSD schema, we need to define element types for metadata lines and data lines. In the sample file, we need four different element types.

1. "TalentProfileLabel" for TalentProfile metadata line
2. "TalentProfile" for TalentProfile data lines
3. "ProfileItemLabel" for ProfileItem metadata line
4. "ProfileItem" for ProfileItem data lines

The following is an example of an element type definition:

<element name="TalentProfileLabel">
    <complexType>
        <sequence>
            <element name="ProfileStatusCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />
            <element name="ProfileTypeCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />
            <element name="PersonNumberLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />
            <element name="ProfileCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />
            <element name="ProfileUsageCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />
        </sequence>
    </complexType>
</element>

Putting it All Together

You can see the full NXSD schema for the sample HDL file below.

<?xml version="1.0" encoding="UTF-8"?> <schema xmlns="http://www.w3.org/2001/XMLSchema"         xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd"         xmlns:tns="http://ichhcmpoc.oracle.com/TalentProfileFileData"         targetNamespace="http://ichhcmpoc.oracle.com/TalentProfileFileData"         elementFormDefault="qualified"         attributeFormDefault="unqualified"         nxsd:stream="chars"         nxsd:version="NXSD"         nxsd:encoding="US-ASCII"         nxsd:encodeLineTerminators="false" >    <element name="TalentProfileFileData">      <complexType>        <choice maxOccurs="unbounded" minOccurs="0">          <element ref="tns:TalentProfileLabel" minOccurs="0" nxsd:startsWith="METADATA|TalentProfile|" />          <element ref="tns:TalentProfile" minOccurs="0" nxsd:startsWith="MERGE|TalentProfile|" />          <element ref="tns:ProfileItemLabel" minOccurs="0" nxsd:startsWith="METADATA|ProfileItem|" />          <element ref="tns:ProfileItem" minOccurs="0" nxsd:startsWith="MERGE|ProfileItem|" />       </choice>      </complexType>    </element>    <!-- TalentProfileLabel -->    <element name="TalentProfileLabel">       <complexType>          <sequence>             <element name="ProfileStatusCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileTypeCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="PersonNumberLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileUsageCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />          </sequence>       </complexType>    </element>    <!-- TalentProfile -->    <element name="TalentProfile">       <complexType>          <sequence>             <element name="ProfileStatusCode" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileTypeCode" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="PersonNumber" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileCode" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileUsageCode" type="string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />          </sequence>       </complexType>    </element>    <!-- ProfileItemLabel -->    <element name="ProfileItemLabel">       <complexType>          <sequence>             <element name="SourceSystemOwnerLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="SourceSystemIdLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileCodeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ContentTypeLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ContentItemLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="DateFromLabel" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ItemText24010Label" type="string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />          </sequence>       </complexType>    </element>    <!-- ProfileItem -->    <element name="ProfileItem">       <complexType>          <sequence>             <element name="SourceSystemOwner" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="SourceSystemId" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ProfileCode" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ContentType" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ContentItem" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="DateFrom" type="string" nxsd:style="terminated" nxsd:terminatedBy="|" />             <element name="ItemText24010" type="string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" />          </sequence>       </complexType>    </element> </schema>

Testing the Translation

Using the testing tool mentioned above, you can test the NXSD schema by running a command similar to the following to translate HDL data file into XML data:

%JAVAHOME%\bin\java xlator.util.Translate -inbound -validate both -schema hcm-talentprofile.nxsd -root TalentProfileFileData -input TalentProfile.dat -output TalentProfile.xml

The resulting XML data file can be downloaded at TalentProfile.xml.

You can also test the reversed translation by running the following command. In this case, the input is XML data and output should be the HDL data file.

%JAVAHOME%\bin\java xlator.util.Translate -outbound -validate both -schema hcm-talentprofile.nxsd -root TalentProfileFileData -input TalentProfile.xml -output TalentProfile-out.dat

Final Thought on Using NXSD Schema for HDL in ICS and SOACS

Notice the translated TalentProfile.xml contains label elements, which are required for generating metadata lines in HDL data file. In a typical ICS or SOACS application, object values come from its input XML document. It is not necessary to include the label elements in the input XML document as well. Instead, we can use a mapping activity to add required label elements to create a full XML document ready for NXSD translation. An image of such mapping activity in ICS can be seen in the following:

Summary

This post demonstrates an NXSD schema that is able to translate between XML and HDL data format. It adds details on NXSD schema used in post "Load Data into Oracle HCM Cloud Using ICS".

We are working on another post that covers HCM Talent Data preparation for the POC described in "Load Data into Oracle HCM Cloud Using ICS".