Query to get user details in Oracle Fusion

Query to get user details in Oracle Fusion

Hi friends , we are going to discuss about the Query to get user details in Oracle Fusion. We will share the detail sql query which helps to share the complete oracle fusion users created in oracle fusion application. We will share some of the important tables too which helps to store the oracle fusion user information's in oracle fusion. Using these tables and sql queries you can develop the custom BIP reports about oracle fusion users. User and roles sql query is one of the important sql queries in oracle fusion which we required in our day to day operations. Please find below the complete detail about Query to get user details in Oracle Fusion.

Important Tables using in query to get user details in Oracle Fusion

1.per_user_roles   
2.per_users     
3.per_roles_dn_tl   
4.per_roles_dn     
5.per_person_names_f

Detail sql query to get user details in Oracle Fusion

SELECT pu.username,
         ppnf.full_name,
         prd.abstract_role,
         prd.job_role,
         prd.data_role,
         prd.duty_role,
         prd.active_flag,
         prdt.role_id,
         prdt.role_name,
         prd.role_common_name,
         pu.user_id,
         prdt.description,
         TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
         TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
    FROM 
         per_roles_dn_tl   prdt,
         per_roles_dn      prd,
         per_person_names_f ppnf,
         per_user_roles    pur,
         per_users         pu
   WHERE     1 = 1
         AND pu.user_id = pur.user_id
         AND prdt.role_id = pur.role_id
         AND NVL (pu.suspended, 'N') = 'N'
         AND ppnf.person_id = pu.person_id
         AND ppnf.name_type = 'GLOBAL'
         AND NVL (pu.start_date, SYSDATE) <= SYSDATE
         AND NVL (pu.end_date, SYSDATE) >= SYSDATE
         AND pu.active_flag = 'Y'
         AND prdt.language = USERENV ('lang')
         AND prdt.role_id = prd.role_id
         AND pu.username =:pusername
ORDER BY 1,2


Query 2:-
Kindly user the following table.column name to be used in your SQL reports.

+ Username = PER_USERS.username
+ Person Name = PER_PERSON_NAMES_F.full_name
+ Email = PER_EMAIL_ADDRESSES.email_address.

Eg :
select user_id ,start_date , end_date , username , person_id from PER_USERS;
select title , first_name , middle_names , last_name , full_name , name_type, person_id from PER_PERSON_NAMES_F;
select email_address ,email_type , date_from, date_to , person_id from PER_EMAIL_ADDRESSES ;