Query to find roles assigned to user in oracle apps

Hi friends, We are going to discuss about the query to find roles assigned to user in oracle apps. We will share the detail sql query which will help to extract the complete roles/responsibilities assigned to user in oracle apps. Using this query, we can develop the custom reports in oracle apps which can help to extract the users and their assigned roles/responsibility in oracle apps. We will also share the important tables which helps to stores the oracle user and roles/responsibility details in oracle apps. Please find below the complete detail about Query to find roles assigned to user in oracle apps.

Query to find roles assigned to user in oracle apps

6 Important Table uses in Query to find roles assigned to user in oracle apps

1.fnd_user_resp_groups_direct

2.fnd_user

3.fnd_responsibility

4.fnd_responsibility_tl

5.fnd_application

6.fnd_application_tl         

Query to find roles assigned to user in oracle apps

Here below is the complete sql query  to find roles assigned to user in oracle apps.

SELECT fuser.user_name             "User Name",

       frt.responsibility_name     "Responsibility Name",

       fresp.responsibility_key    "Responsibility Key",

       fapp.application_short_name “Application Short Name”,

       furgd.start_date            "Resp Start Date",

       furgd.end_date              "Resp End Date"

  FROM fnd_user_resp_groups_direct furgd,

       fnd_user                    fuser,

       fnd_responsibility          fresp,

       fnd_responsibility_tl       frt,

       fnd_application             fapp,

       fnd_application_tl          fat

 WHERE 1 = 1

   AND furgd.user_id = fuser.user_id

   AND furgd.responsibility_id = frt.responsibility_id

   AND fresp.responsibility_id = frt.responsibility_id

   AND fapp.application_id = fat.application_id

   AND fresp.application_id = fat.application_id

   AND frt.language = USERENV('LANG')

   AND UPPER(fuser.user_name) = UPPER('&Enter_User_Name')

--AND (furgd.end_date IS NULL OR furgd.end_date >= TRUNC(SYSDATE))

 ORDER BY frt.responsibility_name; 

Query to find roles assigned to user in oracle apps