Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query Functions a User have Access in each Responsibility

606003Jan 26 2009 — edited Jan 26 2009
Hello,
The query below gives in output the list of functions a user can access taking in account all the functions and menus exclusions.
Does anybody have an idea about how to modify this query to extract also which responsibility each function belongs to?

Thank you
BR
DV




SELECT function_name, menu_id
FROM
(
SELECT DISTINCT
(
SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id
) function_name, menu_id
FROM fnd_menu_entries me
START WITH
me.menu_id IN
(
SELECT r.menu_id
FROM fnd_responsibility r, fnd_user_resp_groups rg, fnd_user u
WHERE rg.responsibility_id = r.responsibility_id
AND u.user_id = rg.user_id
AND u.user_name = UPPER('USERNAME_TO_CHECK')
AND (RG.END_DATE IS NULL
OR RG.END_DATE > SYSDATE)
)
CONNECT BY
me.menu_id = PRIOR me.sub_menu_id
)
WHERE function_name IS NOT NULL
MINUS
SELECT function_name, menu_id
FROM
(
SELECT DISTINCT
(
SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id
) function_name, menu_id
FROM fnd_menu_entries me
START WITH
me.menu_id IN
(
SELECT rf.action_id
FROM fnd_responsibility r, fnd_user_resp_groups rg, fnd_user u, fnd_resp_functions rf
WHERE rg.responsibility_id = r.responsibility_id
AND u.user_id = rg.user_id
AND u.user_name = UPPER('USERNAME_TO_CHECK')
AND rf.responsibility_id = r.responsibility_id
AND rf.rule_type = 'M'
)
CONNECT BY
me.menu_id = PRIOR me.sub_menu_id
)
WHERE function_name IS NOT NULL
MINUS
SELECT ff.function_name, menu_id
FROM fnd_responsibility r, fnd_user_resp_groups rg, fnd_user u, fnd_resp_functions rf, fnd_form_functions ff
WHERE rg.responsibility_id = r.responsibility_id
AND u.user_id = rg.user_id
AND u.user_name = UPPER('USERNAME_TO_CHECK')
AND rf.responsibility_id = r.responsibility_id
AND rf.rule_type = 'F'
AND ff.function_id = rf.action_id
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 23 2009
Added on Jan 26 2009
1 comment
2,827 views