Query to Retrieve All Profile Items for a Given Person Number in Oracle HCM

SELECT PAPF.PERSON_NUMBER, HPB.PROFILE_CODE,
(SELECT  HCTB.CONTEXT_NAME
FROM    HRT_CONTENT_TYPES_B HCTB
WHERE   HCTB.CONTENT_TYPE_ID = HPI.CONTENT_TYPE_ID) CONTENT_TYPE_NAME,
(SELECT  HCIT.NAME FROM HRT_CONTENT_ITEMS_B HCIB , HRT_CONTENT_ITEMS_TL HCIT
  WHERE  HCIB.CONTENT_ITEM_ID = HCIT.CONTENT_ITEM_ID
   AND    HCIB.CONTENT_ITEM_ID = HPI.CONTENT_ITEM_ID ) CONTENT_ITEM_NAME,
 HPI.DATE_FROM , HPI.DATE_TO,
 HPI.CREATED_BY, HPI.CREATION_DATE, HPI.LAST_UPDATE_DATE, HPI.LAST_UPDATED_BY
 FROM   HRT_PROFILE_ITEMS HPI, HRT_PROFILES_B HPB, PER_ALL_PEOPLE_F PAPF
 WHERE  HPI.PROFILE_ID = HPB.PROFILE_ID 
 AND     HPB.PERSON_ID = PAPF.PERSON_ID 
 AND     PAPF.PERSON_NUMBER = 7662
 ORDER BY PAPF.PERSON_NUMBER

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