HDL – Query to delete phones data

Sometimes it is required to delete the uploaded phones data from Fusion HCM. Below query can be used to create a BIP report which in turn can be used to generate HDL file to delete phones data.

The query can be modified to delete phones data for contacts as well as for other person types.

Supported Version :- 20D

Query:

SELECT 'METADATA|PersonPhone|PhoneId|PhoneType|PersonId|DateFrom|SourceSystemId|SourceSystemOwner' D_ROW
FROM DUAL
union
SELECT 'DELETE|PersonPhone|'||
       pp.phone_id||'|'||
 
       pp.phone_type||'|'||
       pp.person_id||'|'||
       to_char(pp.date_from,'RRRR/MM/DD')||'|'||
       hikm.source_system_id||'|'||
       hikm.source_system_owner
  from per_phones pp,
       hrc_integration_key_map hikm
 where pp.phone_id= hikm.surrogate_id
   and exists (select 1 from per_periods_of_service where person_id=pp.person_id)