Oracle Fusion: SQL Query to get the User Assigned Roles Access

 SELECT

    pu.user_id,

    pu.username,

    ppnf.full_name,

    prdt.role_id,

    prdt.role_name,

    prd.role_common_name,

    prdt.description,

    to_char(pur.start_date, 'DD-MON-YYYY') role_start_date,

    to_char(pur.end_date, 'DD-MON-YYYY')   role_end_date,

    prd.abstract_role,

    prd.job_role,

    prd.data_role,

    prd.duty_role,

    prd.active_flag

FROM

    per_user_roles     pur,

    per_users          pu,

    per_roles_dn_tl    prdt,

    per_roles_dn       prd,

    per_person_names_f ppnf

WHERE

        1 = 1

    AND pu.user_id = pur.user_id

    AND prdt.role_id = pur.role_id

    AND prdt.language = userenv('lang')

    AND prdt.role_id = prd.role_id

    AND nvl(pu.suspended, 'N') = 'N'

    AND ppnf.person_id = pu.person_id

    AND ppnf.name_type = 'GLOBAL'

    AND pu.active_flag = 'Y'

    AND nvl(pu.start_date, sysdate) <= sysdate

    AND nvl(pu.end_date, sysdate) >= sysdate

    AND pu.username = 'cr@gmail.com' -- Enter the Username 

ORDER BY

    pu.username,

    prdt.role_name