Users with access to a function [message #416649] |
Mon, 03 August 2009 13:59  |
jbrooks09
Messages: 2 Registered: August 2009
|
Junior Member |
|
|
Hello,
I am trying to create a script to return a listing of users with access to a function. The script takes into account the menu exclusions as well. I have the following so far, but I cannot seem to get it to work. Can someone help me?
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
|
|
|
|
Re: Users with access to a function [message #416654 is a reply to message #416649] |
Mon, 03 August 2009 14:08   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>I cannot seem to get it to work.
Neither can we without DDL for tables & DML for test data.
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
|
|
|
|
Re: Users with access to a function [message #416668 is a reply to message #416649] |
Mon, 03 August 2009 15:34  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
>what about the logic. can someone help me out with the logic?
We don't know what you have or what you want.
We are NOT mind readers!
No Operating System name or version
No Oracle version number (SELECT * from v$version).
No DDL.
No DML.
No expected results.
No help is possible at this time.
|
|
|