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
)