Oracle HCM: Retrieve Roles Assigned to a Specific User

SELECT  PU.USERNAME,
PU.ACTIVE_FLAG act, PRD.ACTIVE_FLAG act1, PUR.ACTIVE_FLAG ACT2,
(SELECT PAPF.PERSON_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID = PU.PERSON_ID AND ROWNUM = 1) GGG
,(SELECT PPNF.FULL_NAME FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PU.PERSON_ID AND ROWNUM = 1) FNAME
,PRDT.ROLE_NAME, PRD.ROLE_COMMON_NAME, PUR.CREATED_BY, PUR.CREATION_DATE
FROM  PER_USERS PU, PER_USER_ROLES PUR, PER_ROLES_DN PRD,  PER_ROLES_DN_TL PRDT
WHERE  PU.USERNAME in ('haleema.richardson@uk.g4s.com') 
AND   PU.USER_ID = PUR.USER_ID
AND   PU.BUSINESS_GROUP_ID = PUR.BUSINESS_GROUP_ID
AND   PUR.ROLE_ID = PRD.ROLE_ID 
AND   PRD.ROLE_ID = PRDT.ROLE_ID 
order by ACT2

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