HDL – Query to generate worker email in HDL format

ByMandeep Gupta 
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|sendmail-discard_00603@xyz.com|PER_EMAIL_00603|HRC_SQLLOADER

Similar Posts

  • HDL – Load Position Model Profile Extra Info

    ByMandeep Gupta

    For Position Model profiles, there are additional attributes like Description, Qualification and Responsibilities. To load these, one should make use of ModelProfileExtraInfo child business object of TalentProfile.dat. Attributes values for Description, Qualification and Responsibilities should be stored in txt files. The txt file(s) should then be placed under the ClobFiles folder and should be zipped…

  • Reports (BIP) – Tips/ Shortcuts

    ByMandeep Gupta

    To display the date in DD-Mon-RRRR format, please use below in your SQL query: to_char(date_of_birth,’DD-Mon-RRRR’, ‘nls_date_language=American’) Use below condition to get current user id: fnd_global.USER_GUID — Use below function to convert the amount in Arabic: <?xdoxslt:toCheckNumber(‘ar-SA’, TOTAL, ‘AED’,’CASE_UPPER’)?> — Using LISTAGG to show multiple rows into a single cell: — Getting Person Id of logged…

  • HDL – Inactivating Worker Unions

    ByMandeep Gupta

    HCM Data Loader can be used to bulk upload worker unions. In Cloud HCM, Worker Unions are just another type of Organization. You can run below query to find different Organization Types and their codes: https://fusionhcmconsulting.com/2021/01/reports-bip-organization-classifications-in-fusion/ Below is the sample file to inactivate a worker union: METADATA|Organization|OrganizationId|EffectiveStartDate|EffectiveEndDate|Name|ClassificationCode|ClassificationName|InternalAddressLine|ActionReasonCode|LocationSetId|LocationCode|LocationSetCode|EstablishmentId|EstablishmentName|GUID|SourceSystemOwner|SourceSystemIdMERGE|Organization||1951/01/01|2021/05/12|Test WU2|ORA_PER_UNION||||||||||FUSION|300000217254520MERGE|Organization||2021/05/13|4712/12/31|Test WU2|ORA_PER_UNION||||||||||FUSION|300000217254520 METADATA|OrgUnitClassification|OrgUnitClassificationId|EffectiveStartDate|EffectiveEndDate|OrganizationId(SourceSystemId)|OrganizationName|ClassificationCode|ClassificationName|CategoryCode|SetCode|Status|LegislationCode|GUID|SourceSystemOwner|SourceSystemIdMERGE|OrgUnitClassification||1951/01/01|2021/05/12|300000217254520|Test WU2|ORA_PER_UNION||||A|IN||FUSION|300000217254521MERGE|OrgUnitClassification||2021/05/13|4712/12/31|300000217254520|Test WU2|ORA_PER_UNION||||I|IN||FUSION|300000217254521 The file makes…