How to Bulk End-Date Element Entries Using HDL

The Scenario

Let’s say an employee has a recurring element entry that is currently open-ended, with an effective end date of 31-Dec-4712. The business now wants this element end-dated as of 31-Jan-2024 — and they want it applied to a whole population of employees, not just one.

Before making any changes, it’s good practice to pull the existing element entry data so you can verify the results both before and after the HDL load. The query below returns the entry, value, and assignment details needed for validation:

SELECT DISTINCT peevf.element_entry_value_id
       ,peef.element_entry_id
       ,petf.base_element_name
       ,peef.effective_start_date ele_sd
       ,peef.effective_end_date ele_ed
       ,peevf.effective_start_date
       ,peevf.effective_end_date
       ,paam.assignment_number
   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

Why a Standard HDL Won’t Work

Here’s the catch: if you simply submit a standard ElementEntry HDL file with a new effective end date, HDL won’t overwrite the existing 31-Dec-4712 row — it will create a date split instead. You’d end up with the original open-ended row still sitting there, plus a new row layered on top of it, which is not what the business asked for.

Example 1: End-Dating an Element Entry

Continuing with the example above, here’s the HDL file that end-dates the Test XYZ Bonus element for assignment E2121212 as of 31-Jan-2024:

METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate

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

Once this file loads successfully, the effective end date on the existing entry is updated in place — no extra split row, no cleanup required. Re-run your validation SQL afterward to confirm the effective_end_date now reflects 31-Jan-2024.

Example 2: Deleting Future-Dated Rows

The same ReplaceLastEffectiveEndDate attribute is also useful when you need to remove future-dated rows entirely and roll the record back to an earlier segment. Suppose your current element entry history looks like this:

If the requirement is to keep only the row starting 01-Jan-2026 and delete both future-dated rows after it, the following HDL achieves that in a single load:

METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate
MERGE|ElementEntry|E53871|Meal Allowance|2026/01/01|4712/12/31|IN LDG|1|E|Y

This effectively collapses the future date-effective rows back into the 01-Jan-2026 segment and re-opens it through 31-Dec-4712, removing the two later-dated splits.

Key Takeaways

• Every recurring element entry defaults to an open-ended 31-Dec-4712 effective end date.
• A standard HDL load with a new end date creates a date split — it does not replace the existing end date.
• Add ReplaceLastEffectiveEndDate = Y to the METADATA and MERGE lines to force HDL to update the existing end date in place.
• The same technique can be used to remove unwanted future-dated rows and collapse the history back to an earlier segment.
• Always validate before and after the load using a query against pay_element_entries_f and pay_element_entry_values_f.