| how to find query_only and standard functions (AOL) [message #173661] |
Tue, 23 May 2006 17:51  |
amankhan
Messages: 69
Registered: December 2005
Location: Texas
|
Member |
|
|
Hi,
There are many functions which are attached to menus and which inturn are attached to responsibility.
Also, we can have a function view only by creating the same function by entering QUERY_ONLY="YES" in parameter.
For ex:- I have a function called "Customers Standard" by which you can create customers. I can create another
function using the same form and make it query only by entering QUERY_ONLY="YES" in the parameters column.
My question is, I want to know for which responsibilities i have this query_only function attached and for
which reponsibilities i have this normal standard function is attached to. If it is Query_only, then i want
to populate a comment saying " Query Only Customer" and if not then populate with " Standard Customer Function".
I wrote a query which displays all the menus, sub-menus and user-function-names for all responsibilities. But
i need to populate another column with this "Query-only" or "Standard Customer" by checking the parameter values.
Like this, i need to check for all like "customer" , "invoices", "order entry", "vendors" etc etc.
If it is hard to understand my problem, to make it simple, " A Query to Display all the menus, sub-menus, sub-sub-menus,
user-function-names for all menus, sub-menus, sub-sub-menus for all reponsibilities. After writing this query,
I want to populate one more column in which i want to display whether it is query_only or not_query_only."
Help Appreciated
Thanks
|
|
|
|
|
|
|
|
| Re: how to find query_only and standard functions (AOL) [message #174142 is a reply to message #174049] |
Fri, 26 May 2006 03:50  |
manwadkar
Messages: 104
Registered: September 2005
Location: Washington DC
|
Senior Member |
|
|
Modify this query as you want..
SELECT DISTINCT
u.user_name, rtl.RESPONSIBILITY_NAME, ff.function_name
FROM fnd_compiled_menu_functions cmf
, fnd_form_functions ff
, fnd_responsibility r
, fnd_responsibility_TL rtl
, fnd_user_resp_groups urg
, fnd_user u
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.GRANT_FLAG='Y'
and r.APPLICATION_ID=urg.RESPONSIBILITY_APPLICATION_ID
AND u.user_id = urg.user_id
order by u.user_name
[Updated on: Fri, 26 May 2006 03:51] Report message to a moderator |
|
|
|
| Re: how to find query_only and standard functions (AOL) [message #174143 is a reply to message #174049] |
Fri, 26 May 2006 03:52 |
manwadkar
Messages: 104
Registered: September 2005
Location: Washington DC
|
Senior Member |
|
|
Another form of query
CLEAR SCR
PROMPT Script: user_functions.sql
PROMPT
PROMPT This script will list all functions associated with a given user.
PROMPT
ACCEPT user_name PROMPT "Enter User Name : "
SET VERIFY OFF
SET PAGESIZE 0
SET FEEDBACK 1
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
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
