HDL – SET PURGE_FUTURE_CHANGES Command

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:

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|4712/12/31|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|4712/12/31|300000066966140|FUSION|ASG_CHANGE|GRADE6

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:

SET PURGE_FUTURE_CHANGES N

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|4712/12/31|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|4712/12/31|300000066966140|FUSION|ASG_CHANGE|GRADE6

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:

SET PURGE_FUTURE_CHANGES N

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2022/06/16|#RETAIN|300000066966135|FUSION|ASG_CHANGE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|GradeCode
MERGE|Assignment|300000066966140|300000066966135|Y|1|2022/06/16|#RETAIN|300000066966140|FUSION|ASG_CHANGE|GRADE6

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