Home/ Questions/Q 83105
In Process

Oraask Latest Questions

Question
albert
  • 0
  • 0
Explorer
Asked: 2025-05-18T09:13:39+03:00 In:

Query to Get Oracle Fusion Users and their Attached Roles

  • 0
  • 0

I need assistance with writing a SQL query to retrieve a list of users and their attached roles in Oracle Fusion. Specifically, I am looking for a query that can:

  1. Identify all users within the Oracle Fusion system.
  2. List the roles assigned to each user.
  3. Include relevant details such as user IDs, usernames, role names, and any other pertinent information.

Could someone provide a sample query or guide me through the process of constructing this query?

Share

    Related Questions

    1 Answer

    1. Hassan AbdElrahman
      Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

      Here’s a sample SQL query that you can use to retrieve a list of users and their attached roles in Oracle Fusion:

      /* For Oraask.com */
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 = :p_username
ORDER BY pu.username, prdt.role_name

      You can adjust the query based on your specific requirements. If you need further customization or have any questions, let me know!

    Leave an answer

    You must login to add an answer.

    Need An Account,