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