SELECT DISTINCT
pp.creation_date creation_date,
ppf.first_name first_name,
ppf.last_name last_name,
hl.location_code location_code,
hl.location_name location_name,
hl.town_or_city town,
hl.country country,
pd.name department,
pu.username username,
pu.active_flag active_flag,
au.user_id user_id,
au.user_login user_login,
r.role_name role_name,
r.description description
FROM
per_persons pp,
per_all_people_f papf,
per_person_names_f_v ppf,
hr_locations_all_f_vl hl,
per_departments pd,
per_all_assignments_m paaf,
per_users pu,
ase_user_vl au,
ase_user_role_mbr aurm,
ase_role_vl r
WHERE
1 = 1
AND au.user_guid = pu.user_guid (+)
AND pu.person_id = papf.person_id (+)
AND papf.person_id = pp.person_id (+)
AND pp.person_id = ppf.person_id (+)
AND ppf.person_id = paaf.person_id (+)
AND paaf.location_id = hl.location_id (+)
AND paaf.organization_id = pd.organization_id (+)
AND trunc(sysdate) BETWEEN nvl(ppf.effective_start_date, trunc(sysdate)) AND nvl(ppf.effective_end_date, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(papf.effective_start_date, trunc(sysdate)) AND nvl(papf.effective_end_date, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(paaf.effective_start_date, trunc(sysdate)) AND nvl(paaf.effective_end_date, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(hl.effective_start_date, trunc(sysdate)) AND nvl(hl.effective_end_date, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(pd.effective_start_date, trunc(sysdate)) AND nvl(pd.effective_end_date, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(au.effective_start_date, trunc(sysdate)) AND nvl(au.effective_end_date, trunc(sysdate))
AND aurm.user_id = au.user_id
AND r.role_id = aurm.role_id
AND au.user_login = :p_user_login