SQL Query to get the profile code, content type name, content type id, content section id and much more

SELECT HPB.PROFILE_CODE,papf.person_number,HRT_CONT_TYPE.CONTENT_TYPE_NAME,
HRT_PROF_ITEMS.*
FROM

hrt_profiles_vl HRT_PROF,
hrt_profile_items HRT_PROF_ITEMS,
hrt_content_types_vl HRT_CONT_TYPE,
HRT_CONTENT_ITEMS_VL HRT_CONT_ITEMS,
PER_ALL_PEOPLE_F PAPF,
HRT_PROFILES_B HPB

WHERE

HRT_PROF.profile_id(+)= HRT_PROF_ITEMS.profile_id
AND HRT_CONT_TYPE.content_type_id(+)= HRT_PROF_ITEMS.content_type_id
AND HRT_PROF_ITEMS.CONTENT_ITEM_ID=HRT_CONT_ITEMS.CONTENT_ITEM_ID(+)
AND HRT_PROF.PERSON_ID = PAPF.PERSON_ID
AND HPB.profile_id = HRT_PROF_ITEMS.profile_id

--AND TRUNC(SYSDATE) BETWEEN HRT_PROF_ITEMS.DATE_FROM AND NVL(HRT_PROF_ITEMS.DATE_TO, TRUNC(SYSDATE+1))
--AND TRUNC(SYSDATE) BETWEEN HRT_CONT_ITEMS.DATE_FROM AND NVL(HRT_CONT_ITEMS.DATE_TO, TRUNC(SYSDATE+1))
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

--AND HRT_CONT_TYPE.CONTENT_TYPE_NAME  = 'Risk of Loss'
--AND PAPF.PERSON_NUMBER = '12345'

ORDER BY  HRT_CONT_ITEMS.NAME ASC

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.