namitak22

 Profile Answers by namitak22 

• Mar 15th, 2007

 

select responsibility_name from FND_responsibility_vl fr, FND_MENUS fm, FND_COMPILED_MENU_FUNCTIONS fmf

, fnd_Form_functions_vl ff

where fr.menu_id= fm.menu_id

AND ff.function_id= fmf.function_id

AND fmf.menu_id= fm.menu_id

and ff.USER_FUNCTION_NAME=:function_name

rahul.gatade

 Profile Answers by rahul.gatade Questions by rahul.gatade

• Apr 12th, 2008

 

 hi, I don't have much idea about this , but while reading this query I thought of making it clear so that others can get it easily...infact the above query is correct..

select responsibility_name
from   FND_responsibility_vl fr
        , FND_MENUS fm
        , FND_COMPILED_MENU_FUNCTIONS fmf

       ,fnd_Form_functions_vl ff
WHERE ff.USER_FUNCTION_NAME = :function_name   --1
AND     ff.function_id                 = fmf.function_id    --2
AND     fmf.menu_id                  = fm.menu_id         --3
AND     fm.menu_id                   = fr.menu_id          --4  

here, ff table is our driving table
from this table we are getting the function_id of our function name(step 1)
then we are joining this table with fmf to get the menu_ids to which our form is linked(step2)
then,we joining this with fnd_menus we are getting menu_ids of those menus(step3)
Finally joining the result with fr table we are getting all the responsibilities to which these menu_ids are attached(step4)
In this way we are getting all the responsibilities to which our user form function is attached...

fouz

• Sep 13th, 2011

 

but this query will list just the menu linked directely to responsibilities, not their sub menus also

fouzia

• Sep 13th, 2011

 

Here is the query that give you all responsibilities having a specific function in menu or sub menus:

Code
REPLACE yourFUNCTION_NAME BY your function_name


SELECT r.RESPONSIBILITY_NAME 


FROM 


apps.fnd_responsibility_vl r, 


apps.fnd_form_functions f 


WHERE f.function_name = yourFUNCTION_NAME


AND r.menu_id IN (SELECT me.menu_id 


FROM apps.fnd_menu_entries me 


START WITH me.function_id = f.function_id 


CONNECT BY PRIOR me.menu_id = me.sub_menu_id) 


AND r.menu_id NOT IN (SELECT frf.action_id 


FROM apps.fnd_resp_functions frf 


WHERE frf.action_id=r.menu_id 


AND frf.rule_type='M') 


AND f.function_id NOT IN (SELECT frf.action_id 


FROM apps.fnd_resp_functions frf 


WHERE frf.action_id=f.function_id 


AND frf.rule_type='F') 


 

hope that will be useful for you !

Paras

• Feb 26th, 2018

 

Fouzias answer is correct if you add following where clause in last two inner queries
and frf.responsibility_id = r.responsibility_id

Mozen

• May 25th, 2018

 

the compiled_menu_functions table also takes into account submenus.. the table contains the result of the loop you have used in your query. I cant seem to find the correct query that takes into account exclusions