OIC HCM - Upload file to UCM and run ImportAndloadData operarion using HCMDataLoader service

Usecase: Here we will perform the following actions:

1. First upload file to UCM
2. ImportAndLoad using HCMDataLoader service.

Note: We can upload a file to Oracle Webcenter Content(Universal Content manager) with the oracle HCM cloud adapter. The File to upload can be either encrypted or unencrypted.

Detailed steps:

Upload to UCM:

Configure Oracle HCM Cloud adapter endpoint >> select Send files to HCM Cloud. >> select Security group =FAFusionImportExport and Doc acoount= hcm$/dataloader$/import$ >> map the following:

File reference = zip file response reference

DocTitle = zip properties >> file name

DocType = "Document"

Import And load:

Configure Oracle HCM Cloud adapter >> select option = Query, Create , Update or delete infomration >> Browse by Services >> select HCMDataLoader >> operation = importAndLoadData >> map the following

Content id = dDocName of the upload ucm response

Parameters = optional like "ImportConcurrentThreads=8"

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/hcm-adapter/oracle-hcm-cloud-adapter-capabilities.html

OIC HCM - discuss about different HCM import and load job status

Please find below table for different job status and their meanings:

NOT STARTED

The process has not started yet. It is waiting or ready. If this value is returned please poll again after some wait.

DATA_SET_UNPROCESSED

The process is running, but the data set has not been processed yet.

IN_PROGRESS

The process is running.

COMPLETED

The data set completed successfully. Job is completed, you can fetch the output.

CANCELLED

Either data set load or data set import was cancelled. Job is cancelled.

ERROR

The data set or process in error. Job has ended in error.

OIC HCM - How to import business objects with the HCM Data Loader from OIC Integrations

Usecase: Here, we will see how to import data for business objects wih the HCM data loader from OIC Integration.

Here, I will import Grade.dat file.

Before proceeding further, read my below blogs:

1. About delimiter data (.dat) file format
2. How to Generate Business Object Mapping File in HCM
3. Modifying Template and Integration Mapping File (nxsd) for OIC

Highlevel steps:

1. Read the source file , it can be fetched from a ftp server, rest or soap service etc.
2. Write Grade.dat file in Stage action Using integration mapping file(nxsd ).
3. Zip the file using stage.
4. Drag and drop HCM connection and configure and select Import Bulk Data using HCM data loader(HDL) and submit an HCM data loader job
5. Drag and drop HCM adapter again and configure with Query the status of an HCM Data loader job

Integration flow:

Detailed steps with Screenshots:

Create Oracle HCM Cloud Connection

Read file using FTP adapter

Write file to stage:

Zip the file

Import the zipped file

HDL query status check:

Reference:

https://blogs.oracle.com/integration/post/a-simple-guide-to%C2%A0oracle-hcm-data-loader%C2%A0hdl-job-support-in-oracle-hcm-cloud-adapter

OIC HCM - Advantages Of HCM Data Loader (HDL)

1. HDLhas the capability to access most of the Business Objects.
2. Support for Partial and Incremental data loading. (no need to create record fully)
3. If your HCM instance is configured with flex fields or user-defined fields, then you are capable of load values in those fields.
4. Comprehensive bulk loading capabilities.
5. User-managed loading or Automated using Web services this allows the tool to be fully autonomous and without user-initiated data load.
6. Images/Documents can be loaded with records so that the system can associate them.
7. Bulk loading of HCM data from any source.
8. Flexible, pipe-delimited file format.
9. Stage Table Maintenance.
10. Capability to track and monitor in real-time.
11. Ability to set the number of threads that are driving a process.

OIC HCM - Importing and Loading Your File from HCM

Follow the below steps:

Step1: In the application, on the home page, click My Client Groups > Data Exchange

Step2: On the Data Exchange page, click Import and Load Data

Step3: Click Import File on the page header.

Step4: Drag and drop your .zip file from your file explorer to the Choose File button. Alternatively, click the Choose File button to search and select your file.

Step5: Click Submit. >> The process parameters are displayed, click Submit.

Tip: You don’t need to change the parameters values.

Step6: Click OK on the Submitted confirmation page. You’re returned to the Import and Load Data page. Go to Recent File Loads 

We can the Grade.zip - import and load are in progress.

Expand the details.

The Import Status will indicate if the business object .dat files in your zip file imported into the staging tables correctly. Here you can see that import was successful.

The Load Status will indicate if the data is successfully loaded in the Oracle HCM Cloud application tables. The clock icon indicates that Load is still in progress.

There are various counts; your file contained 5 data lines, so the Total Lines should be 5. In this simple file the 5 lines represented 5 grade objects, so the Total Objects should also be 5. 2 grades lines failed, 3 load success, total 60% load success.

OIC HCM - about delimiter data (.dat) file

The HCM data loader provides a template file which is a .dat file for each supported business object hierarchy. 

The template contains:

A COMMENT line: 

Identifies the business object  its version, and the file-creation date.

Example,

COMMENT data for Business Object: Grade Version: V2 Created on: 14-05-2024

A METADATA line:

All files must include METADATA lines, to explain which attributes are included in the file and the order in which their values are supplied.

example,

METADATA|Grade|GradeCode|SetCode|GradeName|EffectiveStartDate|ActiveStatus

All attribute names and values are delimited by the pipe ‘|’ character by default.The string immediately after the METADATA instruction identifies the record type the attributes are for, in this case ‘Grade’. The values that follow are the names of the attributes available on the Grade record, that you want to supply data for.

Note: Ensure your text editor is using UTF-8 encoding.

A Data line:

The lines which holds the actual data for the business object to be imported.

Example,

MERGE|Grade|IC1|COMMON|Individual Contributor 1|2000/01/01|A

The MERGE instruction tells HDL to create the grade if it doesn’t already exist, or update it if it does.Again, the value immediately after the MERGE instruction identifies the record type the attributes for. The values that follow are the values for the attributes named in the corresponding METADATA line.

Add these MERGE lines to your file:

MERGE|Grade|IC2|COMMON|Individual Contributor 2|2000/01/01|A

MERGE|Grade|IC3|COMMON|Individual Contributor 3|2000/01/01|A

MERGE|Grade|M1|COMMON|Manager 1|2000/01/01|A

MERGE|Grade|M2|COMMON|Manager 2|2000/01/01|A

Each record needs to be uniquely identified. For grade records the user key is the combination of GradeCode and SetCode, i.e. IC2 and COMMON.

Save your file, naming it Grade.dat. Alternatively, you can download and edit the Grade.dat file.

Compress (zip) the Grade.dat into a filename of your choice, but it must have a .zip file extension. You have created your first HCM Data Loader file for bulk loading grades. Follow the next step to import this into the HDL staging tables and load the data into the application tables.

OIC HCM - Modifying Template and Integration Mapping File (nxsd) for OIC

Modifying Template and Integration Mapping File (nxsd) for OIC

• There is one to one correlation between the template file (sample .dat file) and Integration Mapping File (nxsd file).
• When we download these files they include all attributes available to be loaded into these business objects. All of these attributes are not required. Based on the business requirment we have to put the surrogate key and mandatory fields only.
• One thing to note for OIC is that when we use nxsd files, we need to map every attribute available as part of OIC mapping. The best way to achieve this is to modify the Template File (Sample .dat file) and then modify the nxsd file to ensure only those fields will remain that you will be loading using OIC.

Let’s take the example of Grade Business Object, when I download the default .dat file it brings in all attributes, 

Now first create a sample dat file as per requirement, 

remove all columns that you will not be loading data into via HDL. Once the requirements are clear. You will have to modify the relevant nxsd file.

Now let’s look at the original nxsd file that was downloaded from HCM.

Before we start removing the elements that do not appear in our sample .dat file. 

We need to pay close attention to the following:

1.  "terminated" element in the nxsd file. You will notice two values here 
1. "terminated" nxsd:terminatedBy="|": This appears on all elements as this is the main file delimiter. 
2. "terminated" nxsd:terminatedBy="${eol}" : This appears on the last element of the section

It is important to note these as when you will modify your nxsd file and remove the elements that are not required, chances are your last column name/element will change. For OIC to generate the next line it is key that you shall modify the terminatedBy with $eol to your new last column name/element. 

So here is my modified nxsd file related to the sample .dat file.

Now when I perform the mapping in OIC, this is how it looks.

Reference:

https://blogs.oracle.com/integration/post/practical-guide-to-use-hcm-data-loader-with-oracle-integration