Search for:
Oct 16 2023
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

Mandeep Gupta 0 Comments
Jan 6 2023
HDL – Script to DELETE positions data

In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'Position' ||'|'||
       hapf.Position_Id||'|'||
       to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
       to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
  from hr_all_positions_f hapf
)
Mandeep Gupta 0 Comments