Get the List of username, person no, role name, role code, department

select distinct papf.person_number,pu.username,prdt.role_name Role_Name,prd.role_common_name ,dept.name Department

from

per_All_people_F papf,

PER_USER_ROLES pur,

per_users pu,

per_roles_dn_tl prdt,

per_roles_dn prd,

per_all_assignments_m paam,

( SELECT hauft.organization_id,

hauft.NAME

FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf,

HR_ALL_ORGANIZATION_UNITS_F haouf,

HR_ORGANIZATION_UNITS_F_TL hauft

WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID

AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE

AND hauft.LANGUAGE = 'US'

AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE

AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE

AND houcf.CLASSIFICATION_CODE = 'DEPARTMENT'

AND TRUNC(SYSDATE) BETWEEN hauft.effective_start_date AND hauft.effective_end_date

) dept

where papf.person_id = pu.person_id

and pu.user_id = pur.user_id

and pur.role_id =prdt.role_id

and prdt.role_id=prd.role_id

and papf.person_id = paam.person_id

and prdt.source_lang = 'US'

AND paam.organization_id = dept.organization_id (+)

and paam.assignment_type='E'

and paam.PRIMARY_FLAG = 'Y'

and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

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

Posted by : Mohammad