HCM Data Loader in Fusion
HCM Data Loader in Fusion
In this article we will go through the basics of HCM Data Loader aka HDL which is one of the most
familiar data loading tools in Fusion HCM.
These are the most frequently used File line instructions and is not an exhaustive list. There are some
business object specific file line instructions that can be used. However it would be a new topic of
discussion to get into business object wise file line instructions. Exhaustive list of all SET operations have
been listed at SET Operations on HDL file
Example:
METADATA|Job|JobCode|SetCode|Name|EffectiveStartDate|EffectiveEndDate|ActiveStatus
MERGE|Job|ACC1|COMMON|Accountant|2012/02/01||A
File Discriminators
Sample for Job Business Object. We can see that it has two discriminators Job and JobGrade.
Line Validation
First METADATA line is defined for each object and the next line is followed by the
corresponding MERGE/DELETE data Line.
One .dat file cannot have same METADATA Line repeated more than once.
Each METADATA line should have a valid discriminator
Contain unique reference for itself using any one of the supported key resolutions
Child component must include a unique reference to its parent.
MERGE/DELETE Line should contain the values of the same attributes as defined in the
corresponding METADATA Line
We will discuss some of the important Parameters which impact the HDL loads.
HCM Data Loader Scope: Limited/Full.. This parameter was introduced in R10 and at that time FBL
and HDL both were used for data loads. Now in R13 Oracle is decommissioning the FBL tool
completely, this parameter is defaulted to Full for all new customers since R11 release.
Enable Audit Data: Yes/No - Specifies whether audit data is captured for business objects loaded using
HCM Data Loader. If you set this parameter to Yes, then audit must also be enabled for Oracle Fusion
Applications.
Enable Load Group Size Calculation: Specifies whether the load-group size is to be calculated
automatically. Set to Yes to enable the optimal load-group size to be calculated based on the number of
objects to process and the available concurrent threads. When Enable Load Group Size
Calculation is Yes, the Load Group Size parameter doesn't appear on the Schedule Request page. Also,
the default value of the Load Group Size parameter is ignored.
Purge Person Enabled Key: Key value supplied by Oracle Support to enable the Purge Person Data in
Test Environments process. You can't enable and run this process in production environments.
SET DISABLE_POST_PROCESS_TASKS
By default, these processes run automatically after we load worker.dat file:
Refresh Manager Hierarchy
Update Person Search Keywords
To prevent Refresh Manager Hierarchy from running automatically, include this SET instruction in the
Worker.dat file:
SET DISABLE_POST_PROCESS_TASKS RefreshManagerHierarchy
To prevent Update Person Search Keywords from running automatically, include this SET instruction in
the Worker.dat file:
SET DISABLE_POST_PROCESS_TASKS UpdatePersonSearchKeywords
Tip: To prevent both processes from running automatically, use the SET INVOKE_POST_PROCESS
instruction.
SET INVOKE_POST_PROCESS
By default, these processes run automatically after we load workers:
Refresh Manager Hierarchy
Update Person Search Keywords
To prevent both processes from running automatically, include this SET instruction in the Worker.dat
file:
SET INVOKE_POST_PROCESS N
SET FILE_DELIMITER
Use this instruction to change the default file delimiter, which is the vertical bar (|). For example, to
change it to a comma, include this instruction:
SET FILE_DELIMITER ,
The new value can be up to 10 characters.
SET FILE_ESCAPE
Use this instruction to change the default file escape character, which is the backslash (\). For example, to
change it to the equal sign (=), include this instruction:
SET FILE_ESCAPE =
The new value can be up to 10 characters.
SET FILE_NEWLINE
Use this instruction to change the default file newline character, which is the character n. For example, to
change it to the string newline, include this instruction:
SET FILE_NEW_LINE newline
The new value can be up to 10 characters. For example, you could set the newline character to newline
and the file delimiter to comma (,) using the following SET commands:
SET FILE_DELIMITER ,
SET FILE_NEW_LINE newline
In this case, METADATA and MERGE lines could appear as follows:
METADATA,Address,AddressLine1
MERGE,Address,TheSteading\newlineKier Allan
SET PURGE_FUTURE_CHANGES
When you create business objects containing date-effective components, you include this instruction at
the start of the file:
SET PURGE_FUTURE_CHANGES Y -> Replace mode
This instruction sets the date-effective maintenance mode to Replace. In this mode, which is the default
mode, the contents of the .dat file replace any existing data.
A single record replaces existing date effective records for the date range specified on the update.
SET PURGE_FUTURE_CHANGES N -> Retain Mode
This instruction sets the date-effective maintenance mode to Retain. In this mode, any existing future-
dated changes are retained.
Example:
To change the working hours to 37.5 from 2012/01/10 without affecting future-dated records, you use this
data file:
SET PURGE_FUTURE_CHANGES N
METADATA|Assignment|SourceSystemId|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|
EffectiveEndDate|ActionCode|NormalHours
MERGE|Assignment|223724|2012/01/10||Y|#RETAIN|ASG_CHANGE|37.5
If you know any other SET operations post them in the comments section, I will update the post with
those details.
We can use User Keys/Source Keys when we load data for Business Objects. For using Source Keys, we
first need to define the Source System Owner and then use the defined Source system owner in the HDL
file.
Once the Source System Owner has been defined, we can use this Source System Owner in the HDL file.
Sample HDL:
METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|PersonNumber|
ActionCode|ReasonCode|StartDate|CountryOfBirth|CategoryCode MERGE|Worker|
HRC_SQLLOADER|PER_11111|1945/02/12|11111|HIRE|NEWHIRE|1945/02/12|US|PER_EIT
Sample CostAllocationAccount.dat HDL file
In the below post, we will try to see how to load the Cost Allocation Account Information. The Cost
Allocation Account can be loaded at multiple levels Job, Position, Organization, Payroll, Payment
Method, Assignment, Assignment Terms, Payroll Relationship, Person, Element Entry and Element Link
(Element Eligibility) levels.
How the Payroll Costing works is out of scope of this article.. We will try and see how this information
can be loaded using HDL..Source Type would be different for costing at each of the above levels.
However out of my enthusiasm, I searched for options to get that HDL file from staging tables and finally
figured out a way to get it using BI Report. Below is the SQL that need to be used on Data Model to get
the HDL file as output.
SQL Query:
SELECT fl.TEXT
,bo.data_file_name
FROM fusion.hrc_dl_file_lines fl
,fusion.hrc_dl_data_set_bus_objs bo
,fusion.hrc_dl_data_sets ds
WHERE ds.ucm_content_id = 'UCMFA00014838' -- Replace this with the actual
content id
AND fl.data_set_bus_obj_id = bo.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
ORDER BY fl.seq_num ASC
HCM Data Loader Overview
23
Intro
A week ago I started to learn new bulk loader feature HCM Data Loader which was recently released (You can see
details here HCM Data Loader: Notice of General Availability (Doc ID 2022629.1))
I got ready to spend a lot of time, but I was wrong. It wasn’t very difficult to understand how it works.
HCM Data Loader is very flexible and easy in use. And I would like to share a little experience working with HDL.
1. Finally you don’t need to think about internal GUIDs of business objects. (Supports user keys for all
objects. Knowledge of Oracle HCM Cloud internal IDs is not required)
2. HDL can both create/update records and DELETE records. (Of course if you don’t have references from
dependent objects)
3. You can use |SourceSystemOwner|SourceSystemId| Attributes to hold references from third-party systems
4. Now you can load translations for translatable business objetcs
5. You can load attachments
6. You can bulk load photos for employees
How it works
1. HDL Parameters
First of all you need to check HCM Data Loader Configuration Parameters. As far as I know starting from Release
10 HDL will be the main tool for bulk loading data into the system. In Release 9 you can still decide what tool you
wish to use (FBL or HDL). To make sure that HDL will be accessible you have to set HCM Data Loader Scope
parameter to Full.
To load data using HCM Data Loader, you must have the Human Capital Management
Integration Specialist job role.
In addition you have to add the HCM Data Loader Duty to the Human Capital Management
Integration Specialist Job Role.
To achieve full automation you can use tools for transferring HDL files to content Server and
invoke web service (https://<url-to-your-environment>/hcmCommonDataLoader/HCMDataLoader?WSDL )
to submit import process.
See details here: Automating HCM Data Loader
Submit Import and Load process you can from Data Exchange Work Area:
And finally you can see my successfully loaded worker with the personal photo:
After loading workers, you usually need to run ESS jobs to perform specific operations:
Worklist?
Watchlist feature provides a set of shortcuts to items that you want to track or
that require your attention. Watchlist is used to see a count for each item and
to navigate to the relevant work area where you can view or perform tasks on
the item. For example, a Watchlist item is for expense reports awaiting
submission. You can see how many expense reports you have created but not
yet submitted, and click the Watchlist item link to navigate to the work area
where these reports appear.
Whereas Worklist feature presents workflow tasks that require your approval
or other action, as well as notifications for you to view. Worklist is used to
manage tasks, access their details, or take appropriate action. For example,
some managers might see approval tasks for invoices, and select a particular
task to approve, reject, or otherwise act on the corresponding invoice.
management?
When we
create Rating Model for performance management we do define Rating
Distribution to tell what is the minimum and maximum percentage of worker
should fall in each rating level. This is more commonly known as
Normalization. Now the Distribution Threshold is a filed to control “Number
of eligible workers a manager must have to see distribution target“.
Q : How many sections a performance template can hold
14.
1. Competency Section
2. Goal Section
3. Worker Final Feedback Section
4. Manager Final Feedback Section
5. Questionnaire Section
6. Overall Review Section
15.Q : What are the different absence type patterns available
in Fusion Absence Management?
An absence pattern contains a predefined set of rules that you can use as a
starting point to create an absence type. The pattern that you select
determines whether special fields appear on the absence type pages, as well
as the options available to display and process various aspects of absence
recording. For example, the Illness or injury pattern displays a field that
enables you to select whether the absence type applies for childbirth or
adoption placement.
Illness or injury
Childbirth or placement
Generic absence
Q : What are the different plan types available in Fusion
16.
Absence Management?
There are three different plan types available in Fusion Absence Management,
which are given below:
Accrual
Use this type to create absence plans that enable workers to accrue time for
the purpose of taking paid leave, for example, a vacation plan. Our
requirement qualifies for this plan type.
Qualification
Use this type to create an absence plan where workers qualify for the plan as a
result of events, such as long term illness or maternity, and receive payments
during the absence period.
No Entitlement
Create absence plans of this type to track paid or unpaid absences without
maintaining an accrual balance or providing leave entitlements, such as
periodic accruals. You can also use plans of this type in combination with a
qualification plan. For example, use a no-entitlement plan to pay workers if
they are not eligible for a standard maternity absence qualification plan.
comment section
What is instance qualifier?
What if content type, content item and free form content type?
What minimum roles you should have to initiate fusion HCM
implementation?
What are the key steps you need to follow for fusion performance
management?
How Fusion is different than R12/PeopleSoft?
What is the difference between Legal Entity and Legal Employer?
18. Q : What is Oracle Sandbox?
We have good collection of interview questions on Sandbox, which you can
access here » How to replace oracle fusion logo with custom logo?
19.Q : Here are some fusion individual compensation plan
interview questions
Question : Is it possible to set up the approvals based on the amount entered
for the individual compensation plan?
Question : You have setup Individual Compensation Plan how would you bulk
enrollment large employees?
Question : How to add Validation for Individual Compensation, for example
an employee can be granted special bonus only once in a lifetime.
Question : What is the difference between workforce compensation and
Individual compensation
To see answer of these question, please read article Oracle Fusion Individual
Compensation Plan Explained With Examples
Q : May I have additional interview questions on fusion
20.
compensation
For interview questions on fusion workforce compensation please visit the
page Fusion Compensation Interview Questions
21. Do you have any question?
If you have any question to ask, please do ask them in our forum. All you need
to do is click on link Ask Question and you are done.
22. Additional Oracle Fusion HCM interview questions:
For oracle Fusion certification or interview you may like to see more questions.
We suggest to visit our Interview Question section and
select category Fusion. Please also share your Oracle Fusion HCM interview
Question in the comments box below.If you have any question, just ask here