In this post we will see important query to get user assigned roles details in Oracle Fusion Cloud Application, you can use this query to build an Fusion BIP Report for fetching all the users with their respective assigned roles name.
Important table of User and Roles in Oracle Fusion Cloud Application
ase_role_vl
ase_user_role_mbr
ase_user_b
per_users
per_person_names_f
per_all_people_f
per_email_addresses
Query to get user assigned roles in Oracle Fusion Cloud Application
SELECT DISTINCT PU.user_id ,PU.username ,PPNF.first_name ,PPNF.middle_names ,PPNF.last_name ,PEA.email_address ,PAPF.person_number ,PAPF.person_id ,ARV.code ,ARV.role_name FROM ase_role_vl ARV ,ase_user_role_mbr AURM ,ase_user_b AUB ,per_users PU ,per_person_names_f PPNF ,per_all_people_f PAPF ,per_email_addresses PEA WHERE ARV.role_id = AURM.role_id AND AUB.user_id = AURM.user_id AND AUB.user_guid = PU.user_guid AND PU.person_id = PPNF.person_id AND PPNF.person_id = PAPF.person_id AND PAPF.person_id = PEA.person_id AND TRUNC(SYSDATE) BETWEEN PAPF.effective_start_date AND NVL(PAPF.effective_end_date, TRUNC(SYSDATE)) AND TRUNC(SYSDATE) BETWEEN PPNF.effective_start_date AND NVL(PPNF.effective_end_date, TRUNC(SYSDATE)) AND TRUNC(SYSDATE) BETWEEN PEA.date_from AND NVL(PEA.date_to, TRUNC(SYSDATE)) AND NVL(ARV.effective_start_date,SYSDATE) <= SYSDATE AND NVL(ARV.effective_end_date,SYSDATE) >= SYSDATE AND PPNF.name_type = 'GLOBAL' --SE,GLOBAL -- Added distinct to avoid this AND ARV.code = NVL(:p_role_code,ARV.code) AND ARV.role_name = NVL(:p_role_name,ARV.role_name) AND PU.username = NVL(:p_user_name,PU.username ) AND TRUNC(SYSDATE) BETWEEN ARV.effective_start_date AND NVL(ARV.effective_end_date, TRUNC(SYSDATE)) AND TRUNC(SYSDATE) BETWEEN AURM.effective_start_date AND NVL(AURM.effective_end_date, TRUNC(SYSDATE)) AND TRUNC(SYSDATE) BETWEEN AUB.effective_start_date AND NVL(AUB.effective_end_date, TRUNC(SYSDATE)) AND PU.active_flag= 'Y' AND TRUNC(SYSDATE) BETWEEN PU.start_date AND NVL(PU.end_date, TRUNC(SYSDATE))