SELECT function_name
FROM (SELECT DISTINCT
( SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id) function_name
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('&user_name') )
CONNECT BY
me.menu_id = PRIOR me.sub_menu_id)
WHERE function_name IS NOT NULL
MINUS
SELECT function_name
FROM (SELECT DISTINCT
( SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id) function_name
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('&user_name')
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
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('&user_name')
AND rf.responsibility_id = r.responsibility_id
AND rf.rule_type = 'F'
AND ff.function_id = rf.action_id
FROM (SELECT DISTINCT
( SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id) function_name
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('&user_name') )
CONNECT BY
me.menu_id = PRIOR me.sub_menu_id)
WHERE function_name IS NOT NULL
MINUS
SELECT function_name
FROM (SELECT DISTINCT
( SELECT function_name
FROM fnd_form_functions f
WHERE f.function_id = me.function_id) function_name
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('&user_name')
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
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('&user_name')
AND rf.responsibility_id = r.responsibility_id
AND rf.rule_type = 'F'
AND ff.function_id = rf.action_id
No comments:
Post a Comment