Need to find where the Certificate Number is in the Subject Area

PamS

Summary: Need to retrieve the Certificate Number under Licenses and Certifications for an employee

 "- All Profile Content"."Content Type" = 'Licenses and Certifications'

Content (required): Subject Area is "Workforce Profiles"-"Person Profile Real Time"

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

User_OPDFK

Did you try the folder named "Person Profile" in that subject area? There is a "Licenses and Certifications" subfolder with "Certificate Number" to choose

.

PamS

Thanks for the suggestion - I hadn't thought to look in the Licenses Folder - unfortunately the field appears to be blank even though my screen is showing data.

I need to show the field on a report as well as build a loader for it

Any other thoughts/suggestions?

Pam

User_OPDFK

Above the Licenses and Certifications folder there is the "All Profile Content" folder.

I've found our license/certificate numbers also in the "ItemText2402" and "ItemText2403" has the license/certificate name

PamS

Thanks - I just checked and unfortunately those fields are showing as blank as well :-(

I can see the value on the screen - I just can't seem to find where it is stored in the subject area or base table.

Pam

User_OPDFK

There are a couple of tables you can use datamodel to enter a query to select * from hrt_certification_items_v then look at the sample data.

The link below is the guide for the tables. there are a couple of tables for the profile data where the certification is stored.

https://docs.oracle.com/en/cloud/saas/human-resources/20b/oedmh/profile-management.html#hrtcertificationitemsv-9078

PamS

Thanks for the help - I was able to find the Certificate Number in the subject area - My filter was eliminating the records I was looking for.

Second part of my problem is that I need to create a loader to enter in the Certification Number. I am looking in Business Objects Talent Management -> Content Item but I unfortunately don't see the Certificate Number there

Am I looking in the right place?

Mandeep Gupta

Hi Pam,

You will need to use ContentItem.dat only. So, you are at correct place.

Check the attached excel file to find the attribute to store certificate number.

Let me know if you need any more details.

Thanks.

Profile_Management_Content_Template_Definitions.xlsx

PamS

Thanks Mandeep

Would you happen to have a sample of this loader? I took a look at the spreadsheet but I don't see any keys relating to the employee

I downloaded the .dat file but I am not sure what fields I need to map (had to change it to a .txt so that I could attach it here)

ContentItem.txt

I also don't see ITEM_TEXT240_2 (or even TEXT240) which according to the spreadsheet that you provided is where the Certificate Number would go.

Thanks

Pam

Mandeep Gupta

Hi Pam,

My apologies. You would need to use Talent Profile -> Profile Item HDL for uploading this data.

Below is sample file:

METADATA|ProfileItem|ProfileItemId|ProfileId|ProfileCode|ContentItem|ContentItemId|ContentType|ContentTypeId|CountryCountryCode|CountryGeographyCode|CountryId|DateFrom|Importance|InterestLevel|ItemClob1File|ItemClob2File|ItemClob3File|ItemClob4File|ItemClob5File|ItemDate1|ItemDate10|ItemDate2|ItemDate3|ItemDate4|ItemDate5|ItemDate6|ItemDate7|ItemDate8|ItemDate9|ItemDecimal1|ItemDecimal2|ItemDecimal3|ItemDecimal4|ItemDecimal5|ItemNumber1|ItemNumber10|ItemNumber2|ItemNumber3|ItemNumber4|ItemNumber5|ItemNumber6|ItemNumber7|ItemNumber8|ItemNumber9|ItemText20001|ItemText20002|ItemText20003|ItemText20004|ItemText20005|ItemText2401|ItemText24010|ItemText24011|ItemText24012|ItemText24013|ItemText24014|ItemText24015|ItemText2402|ItemText2403|ItemText2404|ItemText2405|ItemText2406|ItemText2407|ItemText2408|ItemText2409|ItemText301|ItemText3010|ItemText3011|ItemText3012|ItemText3013|ItemText3014|ItemText3015|ItemText302|ItemText303|ItemText304|ItemText305|ItemText306|ItemText307|ItemText308|ItemText309|Mandatory|QualifierCode1|QualifierId1|QualifierCode2|QualifierId2|QualifierSetCode1|QualifierSetCode2|RatingLevelCode1|RatingLevelId1|RatingLevelCode2|RatingLevelId2|RatingLevelCode3|RatingLevelId3|RatingModelCode1|RatingModelId1|RatingModelCode2|RatingModelId2|RatingModelCode3|RatingModelId3|SectionId|SectionName|SourceId|SourceKey1|SourceKey2|SourceKey3|SourceType|StateCountryCode|StateGeographyCode|StateProvinceId|DateTo|SourceSystemId|SourceSystemOwner

MERGE|ProfileItem|||PERSON_300000001111|Analytics Professional||CERTIFICATION||IN|||2020/01/01|||||||||||2022/12/31|2023/01/01||2020/01/01||2023/06/01|2019/12/31||||||||2000|1000|2020||||||Test University|https://www.xyz.com||||Test Certificate|||||||CN09091112||XYZ|||2|||Y||||||||||N|Y||A||||||||||||||||||||||||||||||||PP_30000000111_CERT|HRC_SQLLOADER

This contains list of all the attributes available in HDL. You can remove the unwanted ones and create a similar one.

Let me know if you face any issues.

Thanks.

PamS

Hi Mandeep

I created a TalentProfile.dat file using the following headers and information but keep getting an error

METADATA|ProfileItem|ProfileCode|ContentItem|ContentTypeId|ItemText2401|SourceSystemId|SourceSystemOwner

MERGE|ProfileItem|929347_PERSON|APEGS|103|12345|PP_929347_PERSON_LC1|LEGACY

MERGE|ProfileItem|929347_PERSON|EGBC|103|67890|PP_929347_PERSON_LC2|LEGACY

error:

An error occurred. To review details of the error run the HCM Data Loader Error Analysis Report diagnostic test. Message details: Index: 0, Size: 0.

I tried to find the HCM Data Loader Error Analysis Report but couldn't find it.

I created my own sourcesystemid - does this need to match something in the system? If so, how would I find that out?

Thanks

Pam

Mandeep Gupta

Hi Pam,

SectionId is a mandatory attribute which you will need to pass. Also, please try adding DateFrom and DateTo fields as well.

SourceSystemId is fine. Please check if the source system owner exist in the HRC%SOURCE%SYSTEM% lookup.

Check the below link to find section id:

http://fusionhcmconsulting.com/2021/02/hdl-query-to-find-sectionid-for-talent-profile-item-load/

Thanks.

PamS

Thanks again Mandeep

I now am able to get the loader to work BUT it isn't updating an existing record - it is adding a new one

METADATA|ProfileItem|ContentItemId|ContentTypeId|ItemText2402|SourceSystemOwner|SourceSystemId|ContentType|ContentItem|ProfileCode|SectionId|DateFrom

MERGE|ProfileItem|300000002696418|103|123456|HRC_SQLLOADER|PP_929347_PERSON_LC1|PERSON_CERTIFICATION|APEGS|929347_PERSON|1901|2021/08/19

MERGE|ProfileItem|300000002696312|103|789012|HRC_SQLLOADER|PP_929347_PERSON_LC2|PERSON_CERTIFICATION|EGBC|929347_PERSON|1901|2021/08/19

Am I missing something else that will allow it to update the record instead of adding a new one

Also - do you know of a SQL query that could pull all of the pieces together (I am going to have to do a mass load and would like to run a report that gives me all the keys

Thanks

Pam

Mandeep Gupta

Hi Pam,

To update existing records,you will need to use the existing source system keys. For that you will need to query hrc_integration_key_map table.

Use below joins:

HRT_PROFILE_ITEMS HPI, HRC_INTEGRATION_KEY_MAP HIKM, HRT_PROFILES_B HPB

WHERE HIKM.SURROGATE_ID = HPI.PROFILE_ITEM_ID 

AND HPI.PROFILE_ID = HPB.PROFILE_ID

You can check the sample queries at below link:

http://fusionhcmconsulting.com/2021/02/reports-bip-query-to-get-content-item-details/

Thanks.

PamS

Hi Mandeep,

Thank you for all of the help. I have gotten the loader to work for those records that need to be updated.

When Inserting a new record, what Source System Id do I use or can I have the loader generate this key?

Pam

Mandeep Gupta

Hi Pam,

You can use User Keys and skip the Source System Id and Source System owner attributes. In this application will auto generate these Ids. However, it is advisable to use these keys using HDL. You can put it any logic to generate the source system ID like Talent Profile_ProfileCode_ContentItem_Date From

Any code that uniquely identifies the record.

Thanks.