HDL – Sample file to load Licenses and Certifications to employee profile

Sample file to load licenses and certifications to employee profile:

METADATA|TalentProfile|ProfileCode|PersonNumber|ProfileId|SourceSystemOwner|SourceSystemId
MERGE|TalentProfile|PERS_300000123456789|1234|300000123456789|HRC_SQLLOADER|PERS_300000123456789

METADATA|ProfileItem|ProfileId|ProfileCode|SectionId|ContentTypeId|ContentItem|DateFrom|DateTo|RatingModelCode1|RatingLevelCode1|RatingModelCode2|RatingLevelCode2|RatingModelCode3|RatingLevelCode3|ItemText301|ItemText302|ItemText303|SourceSystemOwner|SourceSystemId
MERGE|ProfileItem|300000123456789|PERS_300000123456789|9989|103|Oracle Global HR|2024/07/01|||||||||||HRC_SQLLOADER|HRC_SQLLOADER_PERS_300000123456789_Oracle Global HR

Sample useful queries:

Query to get source system ID and owner details for existing profiles:

select hrb.profile_id
     , hikm.source_system_id 
	 , hikm.source_system_owner 
  from HRT_PROFILES_ITEMS hrb,
       HRC_INTEGRATION_KEP_MAP hikm
 where hrb.profile_id = hikm.surrogate_id

Query to get profile id for a worker:

select papf.person_number
     , hrb.profile_id  
     , hrb.profile_code 
  from HRT_PROFILES_B hrb,
       PER_ALL_PEOPLE_F papf
 where papf.PERSON_ID =  hrb.PERSON_ID
   and TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   and papf.person_number = <>