Search for:
Dec 30 2023
HDL – Sample HDL files load Content Items

To bulk upload items catalogs in HCM profiles, you can use ContentItem.dat. Each of the template have certain mandatory attributes like Context Name, Value Set Name or Value Set Id:

S0, before you start preparing the file, you need to have below information handy:

  1. Context Name – This is a mandatory attribute. If you don’t pass the value in your HDL file, you will get below error:

The values 3000122xxxxx aren’t valid for ContentItemValueSetId.

You can get the context name from HRT_CONTENT_TYPES_B table:

2. Content Item Value Set Name/Id: This is again a mandatory attribute. You can get the Content Value Set Name/ Id from HRT_CONTENT_TP_VALUESETS_TL table.

Once you have the details, you can prepare ContentItem.dat.

Below are the sample files for different item catalog templates:

For Establishments:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Indian Institute of Technology, Bombay|EDUCATIONAL_ESTABLISHMENT|Establishment||IIT_B|1951/01/01|||IIT_B|HRC_SQLLOADER 
MERGE|ContentItem|Indian Institute of Management, Ahemdabad|EDUCATIONAL_ESTABLISHMENT|Establishment||IIM_A|1951/01/01|||IIT_B|HRC_SQLLOADER

For Licenses and Certifications: 

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner  

MERGE|ContentItem|Oracle Global Human Resources 2023|CERTIFICATION|Licenses and Certifications||O_GHR_2023|1951/01/01|||O_GHR_2023|HRC_SQLLOADER MERGE|ContentItem|Oracle Benefits 2023|CERTIFICATION|Licenses and Certifications||O_BEN_2023|1951/01/01|||O_BEN_2023|HRC_SQLLOADER

For Degrees:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|PhD|DEGREE|Degrees||XX_PhD|1951/01/01|||CI_XX_PhD|HRC_SQLLOADER 
MERGE|ContentItem|Higher National Certificate|DEGREE|Degrees||XX_Higher National Certificate|1951/01/01|||CI_XX_Higher National Certificate|HRC_SQLLOADER

For Competencies:

METADATA|ContentItem|ContextName|ContentItemValueSetName|Name|ContentItemId|ContentItemCode|DateFrom|DateTo|ItemDescription|RatingModelId|RatingModelCode|CountryGeographyCode|CountryCountryCode|SourceSystemId|SourceSystemOwner

MERGE|ContentItem|COMPETENCY|Competencies|Accounting Standards and Principles||XX_ASAP|1951/01/01||To check knowledge on Accounting Standards and Principles.|5|PROFICIENCY|||XX_ASAP|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assessing Talent||XX_AT|1951/01/01||To check knowledge on Assessing Talent.|5|PROFICIENCY|||XX_AT|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assurance and Reporting||XX_AAR|1951/01/01||To check knowledge on Assurance and Reporting.|5|PROFICIENCY|||XX_AAR|HRC_SQLLOADER

For Languages:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Hindi|LANGUAGE|Languages||XX_Hindi|1951/01/01|||CI_Hindi|HRC_SQLLOADER
MERGE|ContentItem|Punjabi|LANGUAGE|Languages||XX_Punjabi|1951/01/01|||CI_Punjabi|HRC_SQLLOADER

Please note that Rating Mode Id is mandatory for loading competencies. You can find the rating model id and rating model code from hrt_rating_models_b table.

Once the data load is successful, you can run below queries to extract loaded data:

Reports (BIP) – Query to get Content Item Details
Mandeep Gupta 0 Comments
Jun 24 2021
BIP – Query to extract Content Items Details
SELECT  'METADATA'
       ,'ContentItem'
	   ,hctb.content_type_id
       ,hctb.context_name
       ,hcibt.name
       ,hctvt.value_set_name
       ,hg.geography_name
       ,hcibt.item_description
       ,to_char(hcib.date_from,'yyyy/mm/dd')
       ,hg.geography_code
       ,hcib.content_item_code
       ,to_char(hcib.date_to,'yyyy/mm/dd')
       ,hrmb.rating_model_code
       ,hrmb.rating_model_id
       ,hikm.source_system_id
       ,hikm.source_system_owner 
FROM HRT_CONTENT_ITEMS_B hcib
    ,HRT_CONTENT_ITEMS_TL hcibt
	,HRT_CONTENT_TYPES_B hctb
	,HZ_GEOGRAPHIES hg
	,HRT_RATING_MODELS_B hrmb
	,HRT_CONTENT_TP_VALUESETS_TL hctvt
	,HRC_INTEGRATION_KEY_MAP hikm
where hcib.CONTENT_ITEM_ID=hcibt.CONTENT_ITEM_ID
  AND hcibt.LANGUAGE=userenv('LANG')
  AND hcib.CONTENT_TYPE_ID=hctb.CONTENT_TYPE_ID(+)
  AND hikm.surrogate_id = hcib.CONTENT_ITEM_ID
  AND hcib.COUNTRY_ID=hg.GEOGRAPHY_ID(+)
  AND hg.GEOGRAPHY_TYPE(+)='COUNTRY'
  AND trunc(hcib.DATE_FROM) between hg.START_DATE(+) and nvl(hg.END_DATE(+),to_date('4712/12/31','YYYY/MM/DD'))
  AND hcib.RATING_MODEL_ID=hrmb.RATING_MODEL_ID(+)
  AND trunc(hcib.DATE_FROM) between hrmb.DATE_FROM(+) and nvl(hrmb.DATE_TO(+),to_date('4712/12/31','YYYY/MM/DD'))
  AND hcib.CONTENT_VALUE_SET_ID=hctvt.CONTENT_VALUE_SET_ID(+)
  AND hctvt.LANGUAGE(+)=userenv('LANG')
  AND hctb.CONTEXT_NAME = 'LANGUAGE'
Mandeep Gupta 0 Comments
Feb 9 2021
Reports (BIP) – Query to get Content Item Details

Use below sample queries to get details of loaded/created content items like – Languages, Degrees etc.

Query to get language details:

Select pr.person_id,
HCITL.NAME lang_name,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID1
and HRL.LANGUAGE = 'US') read_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID2
and HRL.LANGUAGE = 'US') write_a,
(select HRL.RATING_DESCRIPTION
from HRT_RATING_LEVELS_TL HRL
where HRL.RATING_LEVEL_ID = pi.RATING_LEVEL_ID3
and HRL.LANGUAGE = 'US') speak_a
from hrt_profiles_vl pr
,hrt_profile_items pi
,hrt_content_types_vl ct
,hrt_content_items_tl hcitl
where pi.profile_id=pr.profile_id
and pi.content_type_id=ct.content_type_id
and ct.content_type_name = 'Languages'
and hcitl.content_item_id = pi.content_item_id

Query to get Degree Details:

select hpb.person_id person_id,
hcitl.name degree,
item_text240_1 major,
est.name school,
item_decimal_1 gpa,
item_date_4 completion_date
from hrt_profiles_b hpb,hrt_profile_items hpi ,hrt_content_types_b hct,hrt_content_items_tl hcitl , hrt_establishments_vl est
where hpb.profile_id = hpi.profile_id
and hpi.content_type_id = hct.content_type_id
and hcitl.content_item_id = hpi.content_item_id
and est.establishment_id= hpi.item_number_9
and hct.context_name = 'DEGREE'
and hcitl.language ='US'
Mandeep Gupta 0 Comments