Query To Find All Responsibilities In Oracle Apps

In order to find all responsibilities attached to a user you need to fire following query. However, you can directly see them if you have access to System Administrator responsibility in Oracle E-Business Suite. This query can be useful to you in case you have access to database but not to System Administrator responsibility due to your client’s security concerns.

Query To Find All Responsibilities In Oracle Apps

SELECT fuser.user_name             "User Name",
       frt.responsibility_name     "Responsibility Name",
       furgd.start_date            "Start Date",
       furgd.end_date              "End Date",
       fresp.responsibility_key    "Responsibility Key",
       fapp.application_short_name “Application Short Name"
  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;

To return only active responsibilities of that user remove comment from below condition

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

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.