How to load data to Oracle cloud HCM, HDL, Oracle Fusion GUID, Oracle Fusion Surrogate ID

HCM Data Loader (HDL) Keys – Concept

HCM Data loader is the tool of choice to migrate and integrate data to Oracle HCM Cloud environment and supports multiple business objects. HCM Data loader business object has the predefined structures which are extendable in nature to consider the flex-fields and support the data load using various keys.

In this article we will discuss various key types supported by Oracle HCM Data loader a.k.a. HDL. Whether you are planning to implement coexistence with ongoing data sync, one-time bulk load or ongoing integrations you must select the right key type to identify records uniquely. If you select a wrong key, then it may result in huge amount of rework down the line. 

Supported Key Types: HCM Data Loader (HDL) supports 4 different types of keys as listed below (in the order of key resolution sequence)

• GUID – Oracle Fusion Global Unique ID
• Oracle Fusion Surrogate ID
• Source Keys
• User Keys

Here is how each of these keys can be explained:

• Oracle Fusion GUID – Integration Key Generated by Fusion. This is auto generated in Oracle Fusion when a record is created. Here are some of the key properties of GUID:

1. Hexadecimal value
2. Unique across all objects
3. Held in Integration Key Map

• Oracle Fusion Surrogate ID – Fusion Generated Unique ID. This is auto generated in Oracle Fusion when the record is created. Here are some of the key properties of Surrogate ID:

1. Numeric value
2. Unique only for the object type
3. Held on the object

• Source Keys – Source System Key Information. This can be used to track the legacy system information, however if not used these two are also autogenerated by Oracle Cloud. It has two values combined:

1. SourceSystemOwner
2. SourceSystemID
3. Held in Integration Key Map

• User Keys – These User Readable and Generated Keys. These are Natural values and it could be one or many attributes related to certain business objects for example: If we load worker, it could be Worker Number or ID. It can sometimes alternatives or updateable and held on the object definition.

**Keys that aren’t held on the object exist in the Integration Key Map table.

Supplying Keys in an HDL File

• CREATING a Record One can supply system keys or user keys (or both) while creating new objects. You can’t supply surrogate id or GUID because those fields are auto generated if the data load is successful.

Creating an Object Using Source Keys:

METADATA|Job|JobCode|EffectiveStartDate|EffectiveEndDate|Name|SetCode|SourceSystemOwner|SourceSystemId

MERGE|Job|SE|2010/01/01|4712/12/31|Software Engineer|COMMON|EBS-UK|12349

Creating an Object Using a User Key:

METADATA|Job|JobCode|EffectiveStartDate|EffectiveEndDate|Name|SetCode

MERGE|Job|SE|2010/01/01|4712/12/31|Software Engineer|COMMON

• Updating a Record One can supply any of the 4 key types while doing updates.

Updating an Object Using Source Keys:

METADATA|Job|EffectiveStartDate|EffectiveEndDate|Name|SourceSystemOwner|SourceSystemId

MERGE|Job|2010/01/01|4712/12/31|Software Engineer – Java|EBS-UK|12349

Updating an Object Using a User Key:

METADATA|Job|JobCode|EffectiveStartDate|EffectiveEndDate|Name|SetCode

MERGE|Job|SE|2010/01/01|4712/12/31|Software Engineer – Java|COMMON

Updating an Object Using the Fusion GUID:

METADATA|Job|GUID|EffectiveStartDate|EffectiveEndDate|Name

MERGE|Job|2342UJHFI2323|2010/01/01|4712/12/31|Software Engineer – Java

Updating an Object Using the Fusion Surrogate ID:

METADATA|Job|JobId|EffectiveStartDate|EffectiveEndDate|Name

MERGE|Job|13413|2010/01/01|4712/12/31|Software Engineer – Java