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:

SET PURGE_FUTURE_CHANGES Y

METADATA|Position|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate

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