Tag: Work Relationship

BIP, HDL, Report, reports, Work Relationship

BIP – Query to find persons with multiple active work relationships

SELECT papf.person_number
 FROM per_all_people_f papf
 WHERE 1=1
  AND EXISTS (SELECT 1 FROM per_periods_of_service ppos
               WHERE ppos.person_id = papf.person_id
                 AND (ppos.actual_termination_date IS NULL or ppos.actual_termination_date>= TRUNC(SYSDATE))
               GROUP BY ppos.person_id 
              HAVING COUNT (ppos.period_of_service_id) >1
            )

ByMandeep Gupta

HCM Data Loader, HDL, Work Relationship

HDL – Sample file to cancel Pending Worker Work Relationship

In case, you want to cancel work relationship of a Pending worker, below HDL can be used. Make sure to pass CancelWorkRelationshipFlag as Y.

Sample HDL:

METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId|CancelWorkRelationshipFlag
DELETE|WorkRelationship|FUSION|300000092554146|300000092554127|Y

You can extract the relevant Ids using below SQL:

select hikm.source_system_owner, hikm.source_system_id, ppos.person_id
  from per_periods_of_service ppos
      ,hrc_integration_key_map hikm
 where ppos.period_of_service_id = hikm.surrogate_id
   and ppos.person_id in (select person_id from per_all_people_f
where person_number = '1894')

Tag Archive Work Relationship

BIP – Query to find persons with multiple active work relationships

HDL – Sample file to cancel Pending Worker Work Relationship

Latest Posts

Categories

Recent Comments

Archives