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'