Oracle Transactional Business Intelligence

Need to find where the Certificate Number is in the Subject Area
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):
Answers
-
Did you try the folder named "Person Profile" in that subject area? There is a "Licenses and Certifications" subfolder with "Certificate Number" to choose
.
-
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
-
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
-
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
-
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.
-
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?
-
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.
-
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)
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
-
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.
-
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
-
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:
Thanks.
-
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
-
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.
-
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
-
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.