Search for:
Jan 25 2022
HDL – Sample file to update user name to email address
SELECT 'METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner' Headerrow, 1 dataorder
FROM DUAL
UNION
SELECT 'METADATA|User'
      ||'|'||papf.person_number 
      ||'|'||pea.email_address
      ||'|'||hikm.source_system_id
      ||'|'||hikm.source_system_owner Headerrow, 2 dataorder
  FROM per_all_people_f papf
      ,per_email_addresses pea
	  ,per_users pu
	  ,hrc_integration_key_map hikm
 WHERE papf.person_id = pea.person_id
   and pu.person_id = pea.person_id
   and pu.user_id = hikm.surrogate_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
order by dataorder

Sample File:

METADATA|User|PersonNumber|Username|SourceSystemId|SourceSystemOwner
MERGE|User|11101|[email protected]|300000120863888|FUSION
Mandeep Gupta 1 Comments
Jan 25 2022
HDL – Query to generate worker email in HDL format
select 'METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner'
  from dual
UNION ALL
select 'MERGE|PersonEmail' 						||'|'||												
       NULL 									||'|'||										
       papf.person_id							||'|'||								
       to_char(ppos.date_start,'RRRR/MM/DD')	||'|'||					    
       'W1' 									||'|'||				
	   'Y'													||'|'||		
       'sendmail-discard_'||papf.person_number||'@xyz.com' 	||'|'||
      'PER_EMAIL_'||papf.person_number						||'|'||
      'HRC_SQLLOADER' 												
  from per_all_people_f papf
      ,per_periods_of_service ppos
 where 1=1
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and papf.person_id = ppos.person_id
   and ppos.date_start = (select MAX(ppos2.date_start) from per_periods_of_service ppos2
                           where ppos2.person_id = ppos.person_id)

Sample HDL File:

METADATA|PersonEmail|EmailAddressId|PersonId|DateFrom|EmailType|PrimaryFlag|EmailAddress|SourceSystemId|SourceSystemOwner
MERGE|PersonEmail||100000015400564|2021/03/15|W1|Y|[email protected]|PER_EMAIL_00603|HRC_SQLLOADER
Mandeep Gupta 0 Comments
Jan 16 2022
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')
Mandeep Gupta 0 Comments