Query to get user email in Oracle Fusion

 Query to get user email in Oracle Fusion

Hi Friends, we are going to discuss about the Query to get user email in Oracle Fusion. We will share detail sql query which will help you to extract the complete oracle fusion users with their emails addresses in system. Using this sql query , you can find out all the active oracle fusion users with their roles and privileges. We will also try to share the important oracle fusion DB tables too which helps to store the oracle fusion users information's in oracle fusion. This is an very important sql query using this , we can develop some custom bip reports too which helps to extract the oracle fusion user and roles related information in oracle fusion. In oracle fusion , there are around 4 to 5 important tables which help to holds the complete user details. Please find below the complete details about Query to get user email in Oracle Fusion.

Query to get user email in Oracle Fusion

Important Tables about Query to get user email in Oracle Fusion

1.per_roles_dn_tl   

2.per_roles_dn   

3.per_person_names_f

4.per_user_roles

5.per_users  

6.PER_EMAIL_ADDRESSES

Detail SQL Query about Query to get user email in Oracle Fusion

Here below is the detail sql query to extract the user email information's in oracle fusion.

SELECT pu.username "User Name",

         ppnf.full_name "Employee Name",

         prd.abstract_role "Absteact Role Name",

         prd.job_role "Job Role Name",

         prd.data_role "User Data Role",

         prd.duty_role "Duty Role",

         prd.active_flag,

         prdt.role_name,

         prd.role_common_name,

         prdt.description,

         TO_CHAR (pur.start_date, 'DD-MON-YYYY') user_role_start_date,

         TO_CHAR (pur.end_date, 'DD-MON-YYYY') user_role_end_date,

PEA.email_address "User EMAIL",

PEA.email_type  "Email Type"

    FROM 

         per_roles_dn_tl   prdt,

         per_roles_dn      prd,

         per_person_names_f ppnf,

         per_user_roles    pur,

         per_users         pu,

 PER_EMAIL_ADDRESSES PEA

   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 = PEA.person_id

        AND NVL (PEA.start_date, SYSDATE) <= SYSDATE

         AND NVL (PEA.end_date, SYSDATE) >= SYSDATE

         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 =:p_user_name

ORDER BY 1,2

Query to get user email in Oracle Fusion