How You Load Objects with Multiple Changes per Day

For most date-effective objects, only one record exists for a specific date. Therefore, the object history shows only the last update to each attribute on any day.

However, for some objects, such as Assignment, a record exists for each update in a day. Such objects include an effective sequence number to show the update order. Changes are cumulative, and the record with the highest effective sequence number is available to transactions for that date. This topic describes how to load and maintain objects that support multiple changes per day.

Effective Sequence Number

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.

Effective Latest Change

When you report multiple changes on the same effective start date, the EffectiveLatestChange attribute of the last record in the sequence must be Y. For all earlier records, the attribute must be N. EffectiveLatestChange is a required attribute for records that support multiple changes per day. If only one change exists on an effective start date, then EffectiveLatestChange must be Y.

Effective Start and End Dates

The effective end date of any record with EffectiveLatestChange set to N must be the same as its effective start date.

Loading Date-Effective History with Multiple Changes Per Day: Example

This example loads date-effective history for an Assignment object.

METADATA|Assignment|SourceSystemId|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|EffectiveEndDate|...
MERGE|Assignment|2724|2010/06/08|1|Y|2012/03/03|...
MERGE|Assignment|2724|2012/03/04|1|N|2012/03/04|...
MERGE|Assignment|2724|2012/03/04|2|N|2012/03/04|...
MERGE|Assignment|2724|2012/03/04|3|Y|2012/06/01|...
MERGE|Assignment|2724|2012/06/02|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.

Updating Objects with Multiple Changes Per Day

When supplying a single update to an existing object that support multiple changes per day, you may not know the next available sequence number. In this case, leave the EffectiveSequence attribute blank. The next number in the existing sequence is generated automatically.

To correct an existing record, you must supply the existing values for these attributes to identify the record correctly:

Effective Start Date
Effective End Date
Effective Sequence
Effective Latest Change

For example:

METADATA|Assignment|SourceSystemId|EffectiveStartDate|EffectiveSequence|EffectiveLatestChange|EffectiveEndDate|...
MERGE|Assignment|2724|2010/06/08|3|N|4712/12/31|...

Note: Don't leave EffectiveSequence blank when you're loading multiple changes for a single day. Without this information, the order in which records with the same effective start date are processed is undefined. However to leave the EffectiveSequence blank, ensure that you include the SET PURGE_FUTURE_CHANGES N instruction in the data file. For more information on this instruction, refer Example of Retaining Attribute Values in Future-Dated Records. Additionally, for details on supplying values when updating Date-effective objects, refer Updating Date-Effective Objects.

Here's a tutorial on supplying date-effective history, corrections and updates. :Making Date-Effective Changes with HCM Data Loader (HDL)