Home » SQL & PL/SQL » SQL & PL/SQL » Users with access to a function (11i)
Users with access to a function [message #416649] Mon, 03 August 2009 13:59 Go to next message
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 #416650 is a reply to message #416649] Mon, 03 August 2009 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is unreadable.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Users with access to a function [message #416654 is a reply to message #416649] Mon, 03 August 2009 14:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 #416667 is a reply to message #416654] Mon, 03 August 2009 15:13 Go to previous messageGo to next message
jbrooks09
Messages: 2
Registered: August 2009
Junior Member
what about the logic. can someone help me out with the logic?
Re: Users with access to a function [message #416668 is a reply to message #416649] Mon, 03 August 2009 15:34 Go to previous message
BlackSwan
Messages: 25036
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.
Previous Topic: query not returning values
Next Topic: converting ibatis sql code to pl/sql
Goto Forum:
  


Current Time: Sun Dec 04 06:54:59 CST 2016

Total time taken to generate the page: 0.20366 seconds