My Wonderful World of Oracle
ORACLE FOUNDATION: User Responsibilities

One of the reoccurring tasks of an Oracle developer, is having to continually check what a users’ oracle responsibility is, or if they have multiple, are. There are multiple ways to obtain this information.
You could ask the user, but often, they do not use every responsibility assigned to them and the information you get from any user, as a rule, needs to be validated.

You could request this information from a business analyst. This is viable, but then you are on the analysts time. The object here is to accomplish as much as you can with as little involvement as possible from outside of the development group.

The following query can be used to pull all oracle users and their associated responsibilities. It validates that the user is a current user and that the responsibility is both enabled and hasn’t been end dated for that user.

SELECT u.user_name, responsibility_name responsibility, a.start_date
FROM apps.fnd_user_resp_groups_all a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL
and u.user_id = a.user_id
order by responsibility_name

Of course if you need to filter or are looking for specific user(s) or responsibility(ies), you can add condition statements to refine the query.

If you find that this is a repeated task, you can create a view out of the query and run as needed, or base a report from the view to have UI and standard run process. To turn this query into a view, execute the following:

Create or replace view xxfnd_user_responsibilitites as(
SELECT u.user_name, responsibility_name responsibility, a.start_date
FROM apps.fnd_user_resp_groups_all a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL
and u.user_id = a.user_id
order by responsibility_name)

  1. rjhampton-oracle posted this