How to Load Talent Profile Content Using HDL (TalentProfile.dat)

Step 1: Retrieve ProfileCode Using SQL

SELECT  
    PER_ALL_PEOPLE_F.PERSON_NUMBER,
    HRT_PROFILES_B.PROFILE_CODE
FROM 
    FUSION.PER_ALL_PEOPLE_F PER_ALL_PEOPLE_F,
    FUSION.HRT_PROFILES_B HRT_PROFILES_B
WHERE 
    HRT_PROFILES_B.PERSON_ID = PER_ALL_PEOPLE_F.PERSON_ID;
-- Optional filter: AND PER_ALL_PEOPLE_F.PERSON_NUMBER = '46403';

 

Step 2: HDL Template Format for TalentProfile.dat

METADATA|ProfileItem|ProfileCode|ContentTypeId|SectionId|DateFrom|RatingModelId1|RatingModelId2|RatingLevelId1|RatingLevelId2|SourceSystemOwner|SourceSystemId

MERGE|ProfileItem|PERS_300000250309840|125|12501|2020/05/02|4||300000001770127||HRC_SQLLOADER|PER_5
MERGE|ProfileItem|PERS_300000250309840|115|1101|2020/05/02|12|13|300000001770137||HRC_SQLLOADER|PER_6
MERGE|ProfileItem|PERS_300000250309840|115|1101|2020/05/02|12|13||300000001770143|HRC_SQLLOADER|PER_7
MERGE|ProfileItem|PERS_300000250309840|113|1001|2020/05/02|6||300000001770133||HRC_SQLLOADER|PER_8

 

Reference Mappings:

Talent Area ContentTypeId SectionId
Performance Rating 125 12501
Risk of Loss 115 1101
Impact of Loss 115 1101
Career Potential 113 1001

 

Step 3: Verify Loaded Data (Post-Load Validation)

SELECT 
    papf.person_number, 
    HRTPB.profile_code, 
    HRT_CONT_TYPE.content_type_name,
    HRT_PROF_ITEMS.*
FROM 
    hrt_profiles_vl HRT_PROF,
    hrt_profile_items HRT_PROF_ITEMS,
    hrt_content_types_vl HRT_CONT_TYPE,
    HRT_CONTENT_ITEMS_VL HRT_CONT_ITEMS,
    PER_ALL_PEOPLE_F PAPF,
    HRT_PROFILES_B HRTPB
WHERE 
    HRT_PROF.profile_id(+) = HRT_PROF_ITEMS.profile_id
    AND HRT_CONT_TYPE.content_type_id(+) = HRT_PROF_ITEMS.content_type_id
    AND HRT_PROF_ITEMS.content_item_id = HRT_CONT_ITEMS.content_item_id(+)
    AND HRT_PROF.person_id = PAPF.person_id
    AND HRT_PROF_ITEMS.profile_id = HRTPB.profile_id
    AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
--  AND HRT_CONT_TYPE.content_type_name IN ('Career Potential','Risk of Loss','Performance Rating')
    AND PAPF.person_number = '300000010924813'

 

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.

SQL Queries + HDL Template
Posted by : Mohammad
Follow on Linkedin