Search for:
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