Oracle Fusion Cloud: Query to Get Assigned Roles of a User

Below is query to get roles assigned to user(s). You can define username by using p_user_name parameter while running the query, or leave it empty to get all user's roles.

  SELECT pu.username
       , NAME.full_name
       , prdv.role_id
       , prdv.role_name
       , TO_CHAR (TRUNC (pur.start_date), 'MM/DD/YYYY') role_start_date
       , ppnf.effective_end_date
    FROM per_user_roles pur
       , per_users pu
       , per_roles_dn_vl prdv
       , per_person_names_f ppnf
   WHERE pur.user_id = pu.user_id
     AND pu.person_id = NAME.person_id(+)
     AND pur.role_id = prdv.role_id
     AND NVL (pu.suspended, 'N') = 'N'
     AND NAME.name_type = 'GLOBAL'
     AND TRUNC (SYSDATE) BETWEEN TRUNC (ppnf.effective_start_date) AND TRUNC (ppnf.effective_end_date)
     AND pu.username = nvl(:p_user_name, pu.username)
ORDER BY pu.username
       , prdv.role_name