Query Functions a User have Access in each Responsibility
606003Jan 26 2009 — edited Jan 26 2009Hello,
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