Search for:
HDL – Convert Contingent Workers to Workers

There are scenario’s where customers want to convert their non permanent staff to permanent staff in bulk or vice versa. In HCM terminology, this causes changes in system person type of a person. Let us take an example where a customer wants to hire all the contingent workers working in the company as permanent staff.

As mentioned earlier, converting a contingent worker into worker/employee person type will change the system person type of the person. This is a two step process:

  1. Terminate all the CWK records.
  2. Rehire them as workers.

Performing these steps manually for 100+ records will be a tough task and is error prone process. So, it is better to use HDL for this.

First, we will terminate CWK records. Below is the sample file for same:


MERGE|WorkRelationship|300012012120031|Test Legal Employer|2023/01/01|30001212121223|C|2024/05/08|TERMINATE_PLACEMENT|Y

Below sample SQL can be used to get the various IDs:


Once the termination file is loaded successully, we need to rehire the persons as Worker. Sample file for REHIRE:

MERGE|WorkRelationship|1234|Test Legal Employer|Y|2024/05/09|E

MERGE|WorkTerms|REHIRE|2024/05/09|4712/12/31|1|Y|ACTIVE_PROCESS|ET|Test BU|Y|Test Legal Employer|1234|2024/05/09|E|ET1234

MERGE|Assignment|REHIRE|2024/05/09|4712/12/31|1|Y|ACTIVE_PROCESS|E|Test BU|Y|1234|FR|2024/05/09|E|Test Legal Employer|E1234|ET1234

Please note for rehire, only Work Relationship, Work Terms and Assignment METADATA are required.

HDL – Sample file to upload Department DFF attributes

Sample file to upload/update/correct DFF values on department using User Keys:

METADATA|Organization|Name|ClassificationName|EffectiveStartDate|EffectiveEndDate|FLEX:PER_ORGANIZATION_UNIT_DFF|testAttribute(PER_ORGANIZATION_UNIT_DFF=Global Data Elements)

MERGE|Organization|Test Organization|Department|1951/01/01|4712/12/31|Global Data Elements|Xyz


MERGE|OrgUnitClassification|Test Organization|Department|COMMON|1951/01/01|4712/12/31
HDL – Update Assignment Attributes with User Keys

Below is an example of updating assignment category using only user keys:

MERGE|WorkTerms|ET000123|000123|US Inc Legal Employer|2014/04/07|E|LOCATION_CHANGE||2023/04/28|4712/12/31|Y|1

MERGE|Assignment|E000123|000123|US Inc Legal Employer|2014/04/07|E|LOCATION_CHANGE||ET000123|2023/04/28|4712/12/31|Y|1|FR

If you want to use surrogate IDs/ integration keys, then you can check the below post:

HDL – Sample file to load Licenses and Certifications to employee profile

Sample file to load licenses and certifications to employee profile:


MERGE|ProfileItem|300000123456789|PERS_300000123456789|9989|103|Oracle Global HR|2024/07/01|||||||||||HRC_SQLLOADER|HRC_SQLLOADER_PERS_300000123456789_Oracle Global HR

Sample useful queries:

Query to get source system ID and owner details for existing profiles:

select hrb.profile_id
     , hikm.source_system_id 
	 , hikm.source_system_owner 
 where hrb.profile_id = hikm.surrogate_id

Query to get profile id for a worker:

select papf.person_number
     , hrb.profile_id  
     , hrb.profile_code 
  from HRT_PROFILES_B hrb,
       PER_ALL_PEOPLE_F papf
 where papf.PERSON_ID =  hrb.PERSON_ID
   and papf.person_number = <>
Security – Deactivating Users in Bulk

With the security concerns regarding access to Oracle HCM applications, I have seen many customers asking for a way to restrict access to a particular DEV/TEST environment having unmasked data. One option in such scenario’s is to keep only the admin user accounts active in the particular environment and deactivate all other user accounts. This way, the user roles data is kept intact and access is restricted to only a set of limited users.

Let us now understand, the kind of users which can exist in Fusion HCM environment. There can be system users (seeded), service accounts, worker accounts (users tied to a person), standalone user accounts (for vendors/ SI partners). So, it is really important to filter the right set of user accounts which should be deactivated. Also, the method of deactivation can vary depending upon the type of user.

Bulk deactivation of users can be performed using either HDL or by using SCIM REST API. While HDL is bulk data upload tool but it has its own set of limitations. HDL can’t be used to deactivate standalone users i.e. the users which don’t have an associated person record. To deactivate standalone users, REST API should be used.

I will discuss both the approaches in details. Let us first find a way to store the admin user accounts which should remain active. My preferred way of doing this is to create a Common Lookup and add the details (user names) in this lookup. This is because lookup values can be updated easily using a spreadsheet loader.

Below is the sample lookup (XX_ACTIVE_USER_ACCOUNTS) which I created to store the admin user names:

Next step is to add the user accounts in the meaning attribute:

Two user accounts – [email protected] and [email protected] have been added. The next steps will be to filter these record from the deactivation steps.

Let us now discuss the first approach which is to deactive user accounts using HDL. Below SQL query can be used to get a list of all required active user accounts in User.dat HDL format:

SELECT 'METADATA|User|UserId|Suspended' datarow
      ,1 seq
       || pu.user_id
       || '|Y' datarow
      ,2 seq 
  FROM per_users pu
 WHERE pu.person_id IS NOT NULL
   AND pu.created_by NOT IN ('anonymous')
   AND pu.username NOT LIKE 'FUSION%APPS%'
   AND pu.username NOT IN ('AIACS_AIAPPS_LHR_STAGE_APPID','FAAdmin','FAWService','FAWService_APPID','FIISUSER','HCMSI-98f0f163a79a46c58fa4572e41fac8ed_scim_client_APPID','IDROUser','IDRWUser',						'OCLOUD9_osn_APPID','PSCR_PROXY_USER','PUBLIC','app_monitor1','app_monitor',						  'em_monitoring2','fa_monitor','faoperator','oamAdminUser','puds.pscr.anonymous.user','weblogic_idm','anonymous'
  AND pu.suspended = 'N'
  AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
                                    FROM fnd_lookup_values flv
                                   WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
                                     AND flv.language = 'US'
                                     AND flv.enabled_flag = 'Y'

So, the above query will return only those active user accounts which are attached to a person record and don’t exist in the custom lookup XX_ACTIVE_USER_ACCOUNTS.

**Suspended Flag in PER_USERS table indicate if the user is active (N) or inactive (Y).

Next step is to create a BIP data model and a report and save the output data in excel format. From excel, copy the data in a Notepad and save the file as User.dat.

Sample Output in excel format:

Copy the data except for “DATAROW” and paste it in a Notepad. Save the file as User.dat:

zip the User.dat file and upload it in HCM using Data Exchange -> Import and Load.

Once the load is successful, please run – ‘Send Pending LDAP Requests’ ESS job. This should deactivate all the extracted users.

You can run quick queries on per_users to make sure that the user accounts have been deactivated.

Second approach is to use SCIM REST API to bulk deactivate user accounts. I recommend to use this approach only for those users where no person record is attached to the user account.

Please check below MOS note for details on the step by step instructions on SCIM REST API:

Fusion Security: Using SCIM REST API (Doc ID 2346455.1)

Please note that in order to run this REST API, the user should have – IT Security Manager role.

Sample url to bulk deactivate users :-
Sample Payload:


Please note (taken from above Oracle note):

The bulkId attribute value should be set to UNIQUE value, while creating user accounts in BULK. This is required as per IETF SCIM Specifications while creating new resources using POST method. You may use a common value for the bulkId attribute while using PATCH, DELETE, PUT methods in a Bulk operation.

The main challenge with this approach is to get the correct JSON Payload for multiple users from system. I have created a BIP report for this which will generate the output data in required JSON format. Below is the sample code:

 data_row, 1 seq 
},' data_row, 2 seq
 FROM per_users pu
 WHERE pu.person_id IS NOT NULL
   AND pu.created_by NOT IN ('anonymous')
   AND pu.username NOT LIKE 'FUSION%APPS%'
   AND pu.suspended = 'N'
   AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
                                    FROM fnd_lookup_values flv
                                   WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
                                     AND flv.language = 'US'
                                     AND flv.enabled_flag = 'Y'
}' data_row, 3 seq
  FROM dual

You can create a BIP data model and report to get data from this query. Extract the data in excel format and copy it to a notepad. Then you need to remove the highlighted comma in order for this JSON payload to work.

You can use SOAP UI/Postman to run the REST API and provide the output from Notepad as JSON input. Once the API runs successfully, the suspended flag will get changed to Y in per_users table.

HDL – Loading Person Profile Items

In order to load person profile items, a person should have a profile code. There are two ways to create the profile code for a person record.

  1. From UI :- When any business user clicks on Talent Profile for a worker, a profile code is automatically generated in the backend. Profile code is not visible in UI and is always stored in the backend.
  2. Using HDL :- Profile codes can be loaded in bulk using TalentProfile.dat business object of HCM Data Loader (HDL).

It is advisable to load profile codes in bulk as part of data migration using HDL. But there are cases where a user will click on Talent Profile of a worker just after migrating the Core data for the worker. In this case, a profile code is generated in HRT_PROFILES_B table. So, when TalentProfile.dat is used, the profile record will fail for this particular worker.

Below SQL can be used to get list of all active workers who don’t have a talent profile code yet:

select * 
  from per_all_people_f papf
     , per_periods_of_service ppos
 where papf.person_id = ppos.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and ppos.actual_termination_date is NULL
   and not exists (select 1 from HRT_PROFILES_B hpb where papf.person_id = hpb.person_id )
HDL – Sample file to end date element entry value

All recurring element entires are loaded/ created with an effective end date of 31-Dec-4712. But I have seen scenarios, where the business have requirements to end date an element entry as of a certain date. To achieve this in Bulk, one can use ElementEntry.dat HDL business object.

Let us take an example, where an employee has an element entry with an effective end date of 31-Dec-4712:

The effective end date is set to blank which is equivalent to 31-Dec-4712 in the backend table.

So, let us assume the business has requested to end date this particular element as of 31-Jan-2024 for all employees. To achieve, this we need to pull the existing data from element entries table for this particular element. Below SQL can be used to get the ID values and to verify the results before and after HDL load:

SELECT DISTINCT peevf.element_entry_value_id
       ,peef.effective_start_date ele_sd
       ,peef.effective_end_date ele_ed
   FROM per_all_assignments_m paam
       ,pay_element_types_f petf
       ,pay_element_entries_f peef
       ,pay_element_entry_values_f peevf
 WHERE 1=1
   AND paam.person_id = peef.person_id
   AND peef.element_type_id = petf.element_type_id
   AND peef.element_entry_id = peevf.element_entry_id
   AND paam.ASSIGNMENT_TYPE in ('E')
   AND paam.primary_assignment_flag = 'Y'
   AND petf.base_element_name = 'Test XYZ Bonus'
   AND paam.assignment_number = 'E2121212'
   AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
   AND trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

A normal HDL file with new effective end date will just create a date split in the data. To avoid this a new attribute called ReplaceLastEffectiveEndDate should be added in the HDL file which will updated the effective end date from 31-Dec-4712 to 31-Jan-2024.


MERGE|ElementEntry|E2121212|Test XYZ Bonus|2012/01/31|2024/01/31|GB Legislative Data Group|1|E|Y

Once this HDL is run successfully, the effective end date will get updted.

HDL – Sample file to update Grade name and Grade Step Name

File for Grade Name change:

MERGE|Grade|30001221212|New Grade Name|GRADE_9|COMMON|1951/01/01|

File for Grade Step Name change:

MERGE|GradeStep|30001451452|30001221212|New GSN Level 1|1951/01/01|

Save the files as Grade.dat and upload them.

HDL – Sample HDL file to End Date Position Valid Grades

There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.

You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.

To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.

Below is the sample file:

MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y

EffectiveStartDate – Earliest Grade Start Date

EffectiveEndDate – Date on which you want to end date the grade.

Once the file is loaded successfully, below is how the data will look in the backend:

Below SQL query can be used to extract valid grades data:

	   TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,
	   TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,
	   pjfv.POSITION_CODE, job_name,
	   pgfv.grade_code, grade_name,
  FROM per_valid_grades_f pvgf,
       HR_ALL_POSITIONS_F_VL pjfv,
       per_grades_f_vl pgfv 
 WHERE 1=1
   AND pvgf.position_id = pjfv.position_id
   AND pvgf.grade_id = pgfv.grade_id
   AND pvgf.effective_start_date BETWEEN pjfv.effective_start_date AND pjfv.effective_end_date
   AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date
HDL – Sample HDL files load Content Items

To bulk upload items catalogs in HCM profiles, you can use ContentItem.dat. Each of the template have certain mandatory attributes like Context Name, Value Set Name or Value Set Id:

S0, before you start preparing the file, you need to have below information handy:

  1. Context Name – This is a mandatory attribute. If you don’t pass the value in your HDL file, you will get below error:

The values 3000122xxxxx aren’t valid for ContentItemValueSetId.

You can get the context name from HRT_CONTENT_TYPES_B table:

2. Content Item Value Set Name/Id: This is again a mandatory attribute. You can get the Content Value Set Name/ Id from HRT_CONTENT_TP_VALUESETS_TL table.

Once you have the details, you can prepare ContentItem.dat.

Below are the sample files for different item catalog templates:

For Establishments:


MERGE|ContentItem|Indian Institute of Technology, Bombay|EDUCATIONAL_ESTABLISHMENT|Establishment||IIT_B|1951/01/01|||IIT_B|HRC_SQLLOADER 
MERGE|ContentItem|Indian Institute of Management, Ahemdabad|EDUCATIONAL_ESTABLISHMENT|Establishment||IIM_A|1951/01/01|||IIT_B|HRC_SQLLOADER 

For Licenses and Certifications:


MERGE|ContentItem|Oracle Global Human Resources 2023|CERTIFICATION|Licenses and Certifications||O_GHR_2023|1951/01/01|||O_GHR_2023|HRC_SQLLOADER MERGE|ContentItem|Oracle Benefits 2023|CERTIFICATION|Licenses and Certifications||O_BEN_2023|1951/01/01|||O_BEN_2023|HRC_SQLLOADER

For Degrees:


MERGE|ContentItem|Higher National Certificate|DEGREE|Degrees||XX_Higher National Certificate|1951/01/01|||CI_XX_Higher National Certificate|HRC_SQLLOADER 

For Competencies:


MERGE|ContentItem|COMPETENCY|Competencies|Accounting Standards and Principles||XX_ASAP|1951/01/01||To check knowledge on Accounting Standards and Principles.|5|PROFICIENCY|||XX_ASAP|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assessing Talent||XX_AT|1951/01/01||To check knowledge on Assessing Talent.|5|PROFICIENCY|||XX_AT|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assurance and Reporting||XX_AAR|1951/01/01||To check knowledge on Assurance and Reporting.|5|PROFICIENCY|||XX_AAR|HRC_SQLLOADER

For Languages:



Please note that Rating Mode Id is mandatory for loading competencies. You can find the rating model id and rating model code from hrt_rating_models_b table.

Once the data load is successful, you can run below queries to extract loaded data:


In this article, I will talk about affect of using SET PURGE_FUTURE_CHANGES in HCM Data Loader file. HCM use date tracked functionality for most of the objects in HCM, be it work structures, worker or payroll related objects.

I have seen many scenarios, where I had to go in past and make an update on the existing past date without touching the current existing record. There are numerous scenarios like these where HCM technical consultants are expected to make use of HDL to update the past dated records. Oracle has designed PURGE_FUTURE_CHANGES SET command specifically for these scenarios.

But before adding this command in your HDL (.dat) file, it is very important to understand the working of this command, otherwise it may unwantedly purge/ change data which is beyond recovery.

Let us start with an example. Below is existing employment data for a worker with Person Number – 120:

Later on, it has been found that Grade should have been changed Grade 6 from 16-Jun-2022 onwards but there is no data for this in the database currently..

So, in this case a new row with Grade update to Grade 6 should be inserted starting on 16-Jun-2022.

So, the technical consultant has created an an HDL with below format:



As you can see, EffectiveStartDate = 2022/06/16. So, the intention here is to insert a new row starting 2022/06/16.

But unfortuantely, running above HDL will replace all the existing future rows, i.e. the row with effective start date of 2023/01/01 will be purged from database. Below is how the new data will look once the above HDL load is done:

This happens because the default system setting for update mode is set to REPLACE. This can be verified using “Configure HCM Data Loader” task from Setup and Maintenance:

So, in order to preserve the future dated rows, you have to use SET command:




Executing this will preserve the future dated rows. But it will change the data in future rows as per current row data.

Now if the requirement is to keep the future dated rows and their data intact, you should pass #RETAIN in EffectiveEndDate attribute as shown in below example:




With #RETAIN value of Asg Attribute 1 is retained on 01-Jan-2023 row.

HDL – Sample file to load absence reason

Absence reasons can be loaded in BULK using AbsenceReason.dat.

BaseName is a mandatory attribute in the file. If BaseName is not passed, user get below error:

An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: {MESSAGE}.

An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: Please check the stack trace for more details.	

BaseName has to be provided in UPPER CASE concatenated with an underscore (_) and legislation code.


If the above format is not followed, HDL throws below error:

		There was a problem uploading your data.

Clicking on the error Message, shows below:

Below is the successfully loaded file:

MERGE|AbsenceReason|2024/01/15|2024/12/31|IN|A|Test Absence Reason 1|TEST ABSENCE REASON 1_US|Test Absence Reason 1|HRC_SQLLOADER|TEST ABSENCE REASON 1_US
MERGE|AbsenceReason|2024/01/16|2024/12/31|IN|A|Test Absence Reason 2|TEST ABSENCE REASON 2_US|Test Absence Reason 2|HRC_SQLLOADER|TEST ABSENCE REASON 2_US
HDL – Sample HDL to Load Agents information in ORC

HCM Data Loader supports bulk uploading for Agents for Oracle recruiting cloud. Currently, there is no HDL support for loading Agencies. And idea is already submitted for same:

Coming to Agents bulk upload, you can used below HDL:

MERGE|Agent|[email protected]|ExecFinder|67 High Street, London|Agency|Name|Active|ExecFinder.Agent1|HRC_SQLLOADER

MERGE|AgentContext|[email protected]|Agency.Name|ExecFinder.Agent1|HRC_SQLLOADER

MERGE|AgentContextLocation|[email protected]|Agency.Name|Germany|ExecFinder.Agent1-Germany|HRC_SQLLOADER
HDL – DELETE future dated position record

There are scenarios when we want to delete a future dated row from an object. Please note that for objects like Positions/Locations etc which are date tracked, one can make use of HDL with SET command to delete the future dated row.

Let us take an example, where we have below data on Position:

Position Name – Test Position

Effective Start Date – 01- Jan-2023 – Record creation

Effective Start Date – 01-Oct-2023 – Record updated (let us say Standard working hours).

Now the requirement is to delete the row with effective start date – 01-Oct-2023.

In such cases, below HDL can be used:



MERGE|Position|BU Name|Pos Code|2023/01/01|4712/12/31

BIP – Extract Absence Entries in HDL Format

Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and the absence entries were made against the new plan.

So, the approach taken was to take a backup of all absence entries from PROD, enroll the employees into new plan and reupload the absence entries.

A BIP report was developed to take a backup of existing absence entries in HDL format. Below is the query for same:

|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || 'PerAbsenceEntryId'
|| CHR (124) || 'AbsenceType'
|| CHR (124) || 'AbsenceTypeId'
|| CHR (124) || 'AbsenceStatus'
|| CHR (124) || 'ApprovalStatus'
|| CHR (124) || 'AssignmentNumber'
|| CHR (124) || 'AssignmentId'
|| CHR (124) || 'EmployerId'
|| CHR (124) || 'PersonNumber'
|| CHR (124) || 'PersonId'
|| CHR (124) || 'StartDate'
|| CHR (124) || 'StartTime'
|| CHR (124) || 'StartDateDuration'
|| CHR (124) || 'EndDate'
|| CHR (124) || 'EndTime'
|| CHR (124) || 'EndDateDuration'
|| CHR (124) || 'AbsenceReasonId'
|| CHR (124) || 'SubmittedDate'
|| CHR (124) || 'PlannedEndDate'
|| CHR (124) || 'NotificationDate'
|| CHR (124) || 'ConfirmedDate'
|| CHR (124) || 'SourceSystemId' 
|| CHR (124) || 'SourceSystemOwner'  AS DATA_ROW
select  'MERGE'
|| CHR (124) || 'PersonAbsenceEntry'
|| CHR (124) || apae.per_absence_entry_id
|| CHR (124) ||
|| CHR (124) || apae.absence_type_id
|| CHR (124) || apae.absence_status_cd
|| CHR (124) || apae.approval_status_cd
|| CHR (124) || paam.assignment_number
|| CHR (124) || paam.assignment_id
|| CHR (124) || apae.legal_entity_id
|| CHR (124) || papf.person_number
|| CHR (124) || apae.person_id
|| CHR (124) || to_char(apae.start_date,'RRRR/MM/DD')
|| CHR (124) || apae.start_time
|| CHR (124) || apae.start_date_duration
|| CHR (124) || to_char(apae.end_date,'RRRR/MM/DD')
|| CHR (124) || apae.end_time
|| CHR (124) || apae.end_date_duration
|| CHR (124) || apae.absence_type_reason_id
|| CHR (124) || to_char(apae.submitted_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.planned_end_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.notification_date,'RRRR/MM/DD')
|| CHR (124) || to_char(apae.confirmed_date,'RRRR/MM/DD')
|| CHR (124) || hikm.source_system_id
|| CHR (124) || hikm.source_system_owner
     PER_ALL_PEOPLE_F papf,
WHERE hikm.surrogate_id = apae.per_absence_entry_id
  AND apae.absence_type_id = aatft.absence_type_id
  AND apae.assignment_id = paam.assignment_id
  AND apae.person_id = papf.person_id
  AND paam.person_id = papf.person_id
  AND aatft.language = 'US'
  AND LIKE 'Annual%'
  AND TRUNC(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  AND apae.start_date BETWEEN paam.effective_start_date AND paam.effective_end_date

Once the data is extracted, you need to make sure that Source System Owner is updated from FUSION to HRC_SQLLOADER. Source System Owner is set to FUSION when an entry is created from UI.

Once the output of the BIP is ready, Change “MERGE” to “DELETE” to delete all the absence entries. Then enroll the workers in new plan, do the required changes in BIP extract and upload the data back in Fusion HCM.

HDL – Sample file to close benefit certifications

Starting 23C, a new HDL object has been added to close any pending actions for benefits. The certification type is validated against a lookup called – BEN_ENRT_CTFN_TYP

Below is a sample file to close any pending certification actions against “POC”:

MERGE|CloseEnrollmentCertification|12|First Name 1|Last Name 1|POC|Test Benefits Program|Test Optical|Employee + 1|2023/02/01||12_First Name 1_Last Name 1_POC|HRC_SQLLOADER
MERGE|CloseEnrollmentCertification|13|First Name 2|Last Name 2|POC|Test Benefits Program|Test Medical|Employee + 1||2023/02/01|12_First Name 2_Last Name 2_POC|HRC_SQLLOADER

HDL – Sample file to load Person Habits

Attributes like student status, tobacco consumption which impact worker benefits can be loaded using PersonHabits.dat.

Sample File:

MERGE|PersonHabits|HRC_SQLLOADER|PERSON_HABIT_12|Test LE|12|2021/12/03|4712/12/31|PART_TIME|
MERGE|PersonHabits|HRC_SQLLOADER|PERSON_HABIT_13|Test LE|13|2021/09/24|4712/12/31|FULL_TIME|

HDL – Person EFF Information loaded using HDL is not visible in UI

Many a times, even after loading Person EFF information successfully, using HCM Data loader, the information is not available in UI.

In such cases, please make sure that following fields are passed correctly:

InformationType – Name of extra information type e.g. ‘XYZ Medical History’

EFF_CATEGORY_CODE – EFF Category Context. For example, for person EIT, the value will be PER_EIT

CategoryCode – EFF Category Context. For example, for person EIT, the value will be PER_EIT

PeiInformationCategory – Name of extra information type e.g. ‘XYZ Medical History’

Sample HDL file to load Worker EFF information:

METADATA|WorkerExtraInfo|PersonNumber|PersonId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|FLEX:PER_PERSON_EIT_EFF|PeiInformationCategory|CategoryCode|InformationType|medicalStatus(PER_PERSON_EIT_EFF=XYZ Medical Informaton))|EFF_CATEGORY_CODE

MERGE|WorkerExtraInfo|998812||2022/04/01|4712/12/31|FUSION_998812_1|HRC_SQLLOADER|XYZ Medical Informaton|XYZ Medical Informaton|PER_EIT|XYZ Medical Informaton|Normal|PER_EIT
BIP – Extract Document Record File Link from Content Server

Below query can be used to extract the document record from content server.

SELECT papf.person_number		"Person Number",
       ppnf.first_name			"First Name",
       ppnf.last_name			"Last Name",
       fdt.file_name 			"Attached File Name",
       fdt.dm_version_number 	"Document Id",
       fdt.dm_document_id 		"UCM Content Id",
       (SELECT 'https://'||external_virtual_host
          FROM fusion.ask_deployed_domains
         WHERE deployed_domain_name = 'FADomain')
	   || chr(38) 
	   || 'dID='
       || fdt.dm_version_number
       || '&dDocName='
       || fdt.dm_document_id
       || '&allowInterrupt=1' 	"UCM File Link"  
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       hr_documents_of_record hdor,
       fnd_attached_documents fad,
       fnd_documents_tl fdt
 WHERE 1=1
   AND hdor.person_id = papf.person_id
   AND papf.person_id = ppnf.person_id
   AND hdor.documents_of_record_id = fad.pk1_value
   AND fad.document_id = fdt.document_id
   AND fdt.language = 'US'
   AND fad.entity_name = 'HR_DOCUMENTS_OF_RECORD'
   AND ppnf.name_type = 'GLOBAL'
   AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   AND trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
HDL – Sample file to create Pending Worker Record

Below is a sample file to create Pending Worker record using HDL:




MERGE|WorkRelationship||2023/07/01|P|ADD_PEN_WKR|GB Legal Employer|||HDL|TestEmpWR_123|TestEmp_123|Y

MERGE|WorkTerms||2023/07/01|4712/12/31||Y|1|GB Legal Employer|P|2023/07/01|ACTIVE_PROCESS|GB Business Unit|ADD_PEN_WKR|Y|Member|HDL|TestEmpWT_123|TestEmp_123|TestEmpWR_123

MERGE|Assignment|ADD_PEN_WKR|2023/07/01|4712/12/31|1|Y|||ACTIVE_PROCESS|GB Business Unit||P|2023/07/01|GB Legal Employer|Y|Member|HDL|TestEmpASG_123|TestEmp_123|TestEmpWT_123|2023/07/10

ReadyToConvert Flag on Work relationship is used to convert the Pending worker record to Employee record. If this flag is set to Y, it will appear in NewPerson Dashboard with Automatic conversion marked as Yes.

Run Convert Pending Workers Automatically Process will pick the pending worker and convert it into Worker.