Introduction

You can use HCM Data Loader to bulk-load date-effective history and maintain that history.

Note:

It's expected that you understand how to create and upload files using HCM Data Loader (HDL). If you're new to HDL, you should complete the Create and Maintain Data with HCM Data Loader (HDL) tutorial first.

Objectives

In this tutorial you will.

• Create and load date-effective history and multiple-changes per day.
• Correct and update existing date-effective records.
• Upload historic changes, retaining future values and overwriting future values.
• Delete date-effective records within the history.

Prerequisites

To complete this tutorial, you will need:

• Access to import and load data using HCM Data Loader.
• A text editor to create your files.
• A file compressor to zip your business object data files.

It's also recommended that you complete the Create and Maintain Data with HCM Data Loader (HDL) tutorial before attempting this tutorial. Task 10 refers to data you would create within this tutorial.

Task 1: Create an Object with Date-effective History

In this step you'll learn how to load date-effective history when creating a new object.

1. Create a new file, using any text editor and enter the following:

METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|Name|ActiveStatus|JobFamilyCode|RegularTemporary|FullPartTime|StandardWorkingHours|StandardWorkingFrequency
MERGE|Job|SALES|COMMON|2023/03/01|4712/12/31|Sales|ACTIVE|SALES|R|PART_TIME|24|W
MERGE|Job|SALES|COMMON|2020/01/01|2023/02/28|Sales|ACTIVE|SALES|R|PART_TIME|16|W
MERGE|Job|SALES|COMMON|2010/01/01|2019/12/31|Sales|ACTIVE||R|PART_TIME|16|W

These three file lines are for the same job, identified by the JobCode SALES and SetCode COMMON attribute values.

Tip:

When a record has no end-date you can specify a value of 4712/12/31 to the EffectiveEndDate attribute. Alternatively, leave it blank.

Note:

When supplying date-effective history ensure that the date ranges are consecutive and that there are no gaps in the date range. All records must be valid. HDL will group these records together and load them as a single object. If an error is found on any record within the object, the whole object will fail to load.
2. Save your file, naming it Job.dat.
3. Download this JobFamily.dat file.
4. Compress the Job.dat and JobFamily.dat files to a zip file named 1 Create Job.zip.

Note:

Your Job.dat file refers to job families created by the JobFamily.dat file.
5. Import and load the zip file. It will create this data:

   The following date-effective records are created for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Mar-2023		Sales	SALES	24	PART_TIME
   1-Jan-2020	28-Feb-2023	Sales	SALES	16	PART_TIME
   1-Jan-2010	31-Dec-2019	Sales		16	PART_TIME

   In summary:

   • On 1-Jan-2010 the job was created.
   • On 1-Jan-2020 the Job Family Code was populated.
   • On 1-Mar-2023 the Standard Working Hours increased from 16 to 24.

   Tip:

   You can supply date-effective records in any order in the .dat file but no break in the dates can occur.

   Note:

   When you create a date-effective object, only the first date-effective record is processed as a new record. Later-dated records are updates to the first record. To retain values from the previous date-effective record, you can either leave those attributes blank or repeat the value that's to continue.

Task 2: Correct the Latest Date-Effective Record

In this step you'll upload a correction to the latest date-effective record for your job.

1. Create a new file and enter the following:

METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|StandardWorkingHours
MERGE|Job|SALES|COMMON|2023/03/01|4712/12/31|32

As a record for the job already exists on the 1-Mar-2023 it will be corrected, rather than a new date-effective split being created.

Tip:

As only the StandardWorkingHours are being corrected you only need to supply that attribute. Other attributes will remain unchanged.
2. Save your file naming it Job.dat and compress it to a zip file named 2 Correct Job.zip
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Mar-2023		Sales	SALES	32	PART_TIME
   1-Jan-2020	28-Feb-2023	Sales	SALES	16	PART_TIME
   1-Jan-2010	31-Dec-2019	Sales		16	PART_TIME

In summary:

• On the 1-Mar-2023 the Standard Working Hours were corrected from 24 to 32.
• No changes were made to any other attributes on this record.

Task 3: Create a Date-Effective Update

In this task you'll upload a date-effective update for your job.

1. Create a new file and enter the following:

METADATA|Job|JobCode|SetCode|EffectiveStartDate|StandardWorkingHours
MERGE|Job|SALES|COMMON|2023/03/15|40

As you've supplied an EffectiveStartDate that doesn't yet exist for the job a new date-effective split will be created.

Tip:

If you don't supply a value for the EffectiveEndDate the change will run until the end of time.
2. Save your file naming it Job.dat and compress it to a zip file named 3 Update Job.zip
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   15-Mar-2023		Sales	SALES	40	PART_TIME
   1-Mar-2023	14-Mar-2023	Sales	SALES	32	PART_TIME
   1-Jan-2020	28-Feb-2023	Sales	SALES	16	PART_TIME
   1-Jan-2010	31-Dec-2019	Sales		16	PART_TIME

In summary:

• A new date-effective update is created on the 15-Mar-2023 increasing the Standard Working Hours to 40.
• The effective end date on the record that previously ran to the end-of-time is updated to the 14-Mar-2023, the day prior to the new effective start date.

Task 4: Delete Future Date-Effective Splits

In this task you'll replace the last two date-effective splits with a new record.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES Y
METADATA|Job|JobCode|SetCode|EffectiveStartDate|StandardWorkingHours|FullPartTime
MERGE|Job|SALES|COMMON|2023/01/01|40|FULL_TIME

The SET PURGE_FUTURE_CHANGES Y instruction tells HDL to replace any date-effective records that exist within the date range specified by the EffectiveStartDate and EffectiveEndDate attributes.

2. Save your file naming it Job.dat and compress it to a zip file named 4 Replace DE Split in Job.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales	SALES	40	FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales	SALES	16	PART_TIME
   1-Jan-2010	31-Dec-2019	Sales		16	PART_TIME

In summary:

• The date-effective records starting on 1-Mar-2023 and 15-Mar-2023 are deleted as they existed after the EffectiveStartDate of 1-Jan-2023.
• A new record is created from the 1-Jan-2023 updating the Standard Working Hours and Full Part Time values.
• The effective end date of the record that immediately precedes 1-Jan-2023 has its effective end date updated to 31-Dec-2022.
• Attributes not supplied with values in the file are not updated, but instead rolled forward from the previously dated record, such as the Job Family Code.

Task 5: Upload a Historic Change Retaining Future Dated Values

In this step you'll insert a date-effective record without overwriting any values on future dated-records.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES N
METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|Name
MERGE|Job|SALES|COMMON|2015/01/01|#RETAIN|Sales Consultant

The SET PURGE_FUTURE_CHANGES N instruction tells HDL to retain any future date-effective splits that exist.

The #RETAIN value supplied to the EffectiveEndDate attribute tells HDL to retain the attribute values on all future date-effective records that exist.

2. Save your file naming it Job.dat and compress it to a zip file named 5 Retain Future Changes Job.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales	SALES	40	FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales	SALES	16	PART_TIME
   1-Jan-2015	31-Dec-2019	Sales Consultant		16	PART_TIME
   1-Jan-2010	31-Dec-2014	Sales		16	PART_TIME

In summary:

• A new record is created on the 1-Jan-2015 setting the Name to Sales Consultant.
• The new record has an effective end date derived as the day before the effective start date of the next dated record. i.e., the next existing record starts on 1-Jan-2020, so the new record will end on 31-Dec-2019.
• All future dated records are kept. The Name value does not roll forward due to use of the #RETAIN token supplied to the EffectiveEndDate attribute.
• Attributes not supplied with values in the file will inherit their values from the record that previously existed on the 1-Jan-2015; the value of the EffectiveStartDate attribute specified in the file.

Task 6: Upload a Historic Change Updating Future Dated Records

In this step you'll correct all existing date-effective records from the start date specified by the EffectiveStartDate attribute.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES N
METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|Name
MERGE|Job|SALES|COMMON|2010/01/01|#ALL|Sales Consultant

The SET PURGE_FUTURE_CHANGES N instruction tells HDL to retain any future date-effective splits that exist.

The #ALL value supplied to the EffectiveEndDate attribute tells HDL to roll forward the supplied attribute values across all future date-effective records.

2. Save your file naming it Job.dat and compress it to a zip file named 6 Replace Future Changes Job.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales Consultant	SALES	40	FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales Consultant	SALES	16	PART_TIME
   1-Jan-2015	31-Dec-2019	Sales Consultant		16	PART_TIME
   1-Jan-2010	31-Dec-2014	Sales Consultant		16	PART_TIME

In summary:

• All date-effective splits are retained.
• All records from the EffectiveStartDate going forward are corrected with the Sales Consultant name change, except the record dated 1-Jan-2015 as there was no change to make.
• Only attribute values supplied in the file are rolled forward.

Task 7: Delete a Date-Effective Record

In this step you'll delete the unnecessary date-effective record that starts on 1st Jan 2015 as the attribute values are identical to the record that immediately predates it.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES Y
METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate
MERGE|Job|SALES|COMMON|2010/01/01|2019/12/31

The SET PURGE_FUTURE_CHANGES Y instruction tells HDL to replace any date-effective records that exist within the date range specified by the EffectiveStartDate and EffectiveEndDate attributes.

As only the attributes to identify the job are supplied, no corrections or updates are made to any attributes by this file.

2. Save your file naming it Job.dat and compress it to a zip file named 7 Delete Date Effective Splits.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales Consultant	SALES	40	FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales Consultant	SALES	16	PART_TIME
   1-Jan-2010	31-Dec-2019	Sales Consultant		16	PART_TIME

In summary:

• To delete a date-effective record, supply the EffectiveStartDate with the effective start date of the last record you want to retain. Specify the EffectiveEndDate with the effective end date of the last record you need to delete.
• In this example only the record starting 1-Jan-2015 is to be deleted:
  • Specify 2010/01/01 to the EffectiveStartDate, which is the effective start date of the last record to keep. This ensures no new date-splits are created as the 1-Jan-2010 record already exists.
  • Specify 2022/12/31 to the EffectiveEndDate, which is the effective end date of the last record to be deleted. This ensures there are no changes to records after this date.

Task 8: Delete Attribute Values

In this step you'll delete the value of an attribute for a date-range.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES N
METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|StandardWorkingHours|StandardWorkingFrequency
MERGE|Job|SALES|COMMON|2010/01/01|#ALL|#NULL|#NULL

The SET PURGE_FUTURE_CHANGES N instruction tells HDL to retain any future date-effective splits that exist.

The #NULL value supplied to the StandardWorkingHours and StandardWorkingFrequency attributes tells HDL to remove the value for these attributes.

The #ALL value supplied to the EffectiveEndDate attribute tells HDL to roll forward the attribute values on all future date-effective records that exist.

Tip:

You can also supply the end date for the change, such as 4712/12/31 to roll forward this change until the end of time. However, if the object has been end-dated, i.e., the last effective date is something other than 4712/12/31, the update will fail.
2. Save your file naming it Job.dat and compress it to a zip file named 8 Delete Attribute Values.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales Consultant	SALES		FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales Consultant	SALES		PART_TIME
   1-Jan-2010	31-Dec-2019	Sales Consultant			PART_TIME

In summary:

• Values for the Standard Working Hours and Standard Working Frequency (which isn't shown in this table), are both deleted from the effective start date across all later dated records.

Task 9: Alter the Logical Start Date

In this step you'll adjust the first effective start date for the job. This will replace the first existing date-effective split.

Tip:

Use the View Business Objects task to determine if an object supports updates to the first or last effective date.

1. Create a new file and enter the following:

SET PURGE_FUTURE_CHANGES Y
METADATA|Job|JobCode|SetCode|EffectiveStartDate|EffectiveEndDate|ReplaceFirstEffectiveStartDate|Name
MERGE|Job|SALES|COMMON|2000/01/01|2019/12/31|Y|Sales Consultant

The SET PURGE_FUTURE_CHANGES Y instruction tells HDL to replace any future date-effective splits that exist within the date-range specified by the EffectiveStartDate and EffectiveEndDate attributes.

When the attribute ReplaceFirstEffectiveStartDate is set to Y, HDL will adjust the logical start date to that specified by the EffectiveStartDate attribute.

Note:

As the job name is mandatory and translated, you need to also specify the Name attribute so the first effective start date of the translation record can also be adjusted.
2. Save your file naming it Job.dat and compress it to a zip file named 9 Adjust Start Date.zip.
3. Import and load your zip file. This will have the following impact on your job:

   The following date-effective records exist for the Sales job.

   Effective Start Date	Effective End Date	Name	Job Family Code	Standard Working Hours	Full Part Time
   1-Jan-2023		Sales Consultant	SALES		FULL_TIME
   1-Jan-2020	31-Dec-2022	Sales Consultant	SALES		PART_TIME
   1-Jan-2000	31-Dec-2019	Sales Consultant			PART_TIME

In summary:

• The first effective start date is changed to the EffectiveStartDate supplied in your file.
• The EffectiveEndDate specified in this example was the end-date of the existing first date-effective record 31 Dec 2019.

Note:

If you want to retain existing splits and create a new record before those from the new start date, instead use the SET PURGE_FUTURE_CHANGES N instruction to retain future dated records.

Tip:

You can adjust the effective end date for objects that support it using the ReplaceLastEffectiveEndDate attribute.

Task 10: Loading Multiple Changes Per Day

The employment terms and assignment records within the Worker object hierarchy support multiple changes per day (MCPD). This allows you to track individual updates made on the same effective start date.

When you load date-effective history for an object with multiple changes on a single date, the EffectiveSequence value must start at 1 and increase sequentially. You can't repeat an EffectiveSequence value for the same logical object on the same date, nor can gaps exist in the sequence. If only one change exists for an effective start date, then EffectiveSequence must be 1.

For example:

METADATA|Assignment|SourceSystemId|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|...
MERGE|Assignment|2724|2012/06/02||1|Y|...
MERGE|Assignment|2724|2012/03/04|2012/06/01||3|Y...
MERGE|Assignment|2724|2012/03/04|2012/03/04|2|N|...
MERGE|Assignment|2724|2012/03/04|2012/03/04|1|N|...
MERGE|Assignment|2724|2010/06/08|2012/03/03|1|Y|...

This example observes these rules:

• No gaps exist in the dates.
• The unique key is identical for all date-effective records.
• EffectiveSequence starts at 1 for all new effective start dates and is sequential for matching effective start dates.
• EffectiveLatestChange is Y when the record is the last change on an effective start date. Otherwise, EffectiveLatestChange is N.
• The EffectiveEndDate matches the EffectiveStartDate when EffectiveLatestChange is N.

1. Create a new file and enter the following:

METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|PersonNumber|StartDate|DateOfBirth|ActionCode
MERGE|Worker|EMP|MCPD|2005/02/08|HDL-MCPD|2005/02/08|1966/04/21|HIRE

METADATA|PersonName|SourceSystemOwner|SourceSystemId|EffectiveStartDate|PersonId(SourceSystemId)|NameType|LegislationCode|Title|LastName|FirstName
MERGE|PersonName|EMP|MCPD_NME|2005/02/08|MCPD|GLOBAL|US|MRS.|Hugh|Lorraine

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|LegalEmployerName|DateStart|WorkerType|PrimaryFlag
MERGE|WorkRelationship|EMP|MCPD_POS|MCPD|Vision Corporation|2005/02/08|E|Y

METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|PeriodOfServiceId(SourceSystemId)|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentName|AssignmentNumber|PrimaryWorkTermsFlag
MERGE|WorkTerms|EMP|MCPD_TRM|MCPD_POS|WORK_HOURS_CHANGE|2010/05/10|4712/12/31|2|Y|ET-MCPD|ET-MCPD|Y
MERGE|WorkTerms|EMP|MCPD_TRM|MCPD_POS|JOB_CHANGE|2010/05/10|2010/05/10|1|N|ET-MCPD|ET-MCPD|Y
MERGE|WorkTerms|EMP|MCPD_TRM|MCPD_POS|HIRE|2005/02/08|2010/05/09|1|Y|ET-MCPD|ET-MCPD|Y

METADATA|Assignment|SourceSystemOwner|SourceSystemId|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsAssignmentId(SourceSystemId)|AssignmentName|AssignmentNumber|AssignmentStatusTypeCode|PersonTypeCode|BusinessUnitShortCode|PrimaryAssignmentFlag|JobId(SourceSystemId)|JobId(SourceSystemOwner)|StandardHours|StandardFrequencyDB
MERGE|Assignment|EMP|MCPD_ASG|WORK_HOURS_CHANGE|2010/05/10|4712/12/31|2|Y|MCPD_TRM|MCPD Example|E-MCPD|ACTIVE_PROCESS|Employee|Vision Operations|Y|MGR|VISION|37.5|W
MERGE|Assignment|EMP|MCPD_ASG|JOB_CHANGE|2010/05/10|2010/05/10|1|N|MCPD_TRM|MCPD Example|E-MCPD|ACTIVE_PROCESS|Employee|Vision Operations|Y|MGR|VISION|37.5|W
MERGE|Assignment|EMP|MCPD_ASG|HIRE|2005/02/08|2010/05/09|1|Y|MCPD_TRM|MCPD Example|E-MCPD|ACTIVE_PROCESS|Employee|Vision Operations|Y|SCN|VISION|40|W

Tip:

The jobs referenced by this file are created in the Create and Maintain Data with HCM Data Loader (HDL) tutorial.

You'll also need to update the value of the LegalEmployerName attribute on the WorkRelationship and the BusinessUnitShortCode on the Assignment records to value available on your environment.

Note:

Ensure you create the source system owner in the application prior to loading this file or change the SourceSystemOwner to a value that already exists.
2. Save your file naming it Worker.dat and compress it to a zip file named 10 MCPD.zip.
3. Import and load your zip file. This will create a worker with these date-effective records for the Assignment:

   The following date-effective records exist for the worker's assignment.

   Effective Start Date	Effective End Date	Effective Sequence	Action	Job	Standard Hours
   10-May-2010		2	WORK_HOURS_CHANGE	MGR	37.5
   10-May-2010	10-May-2010	1	JOB_CHANGE	MGR	40
   8-Feb-2005	9-May-2010	1	HIRE	SCN	40

Tip:

When supplying date-effective changes to an Assignment record, you must create the same date-effective splits on the WorkTerms record.

Next Steps

The following tutorials will further expand your HCM Data Loader knowledge:

• Understanding HCM Data Loader (HDL) Business Objects
• Loading Flexfield Segments, Images and Attachments with HCM Data Loader (HDL)

Related Links

• Overview of Loading Date-Effective Objects

Acknowledgements

• Authors - Ema Johnson (Senior Principal Product Manager)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Making Date-Effective Changes with HCM Data Loader (HDL)

F85255_01

August 2023

Copyright © 2023, Oracle and/or its affiliates.