Need a query that can give me all the responsibilities that have a specific function.

The input param will be the "function name" and the output should be "all the responsibilities that use that function" irrespective of whether the function in under the menu or the sub-menu of that responsibility.

Showing Answers 1 - 4 of 4 Answers

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

  Was this answer useful?  Yes

 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...

  Was this answer useful?  Yes

fouz

  • Sep 13th, 2011
 

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

  Was this answer useful?  Yes

fouzia

  • Sep 13th, 2011
 

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

Code
  1. REPLACE yourFUNCTION_NAME BY your function_name

  2. SELECT r.RESPONSIBILITY_NAME

  3. FROM

  4. apps.fnd_responsibility_vl r,

  5. apps.fnd_form_functions f

  6. WHERE f.function_name = yourFUNCTION_NAME

  7. AND r.menu_id IN (SELECT me.menu_id

  8. FROM apps.fnd_menu_entries me

  9. START WITH me.function_id = f.function_id

  10. CONNECT BY PRIOR me.menu_id = me.sub_menu_id)

  11. AND r.menu_id NOT IN (SELECT frf.action_id

  12. FROM apps.fnd_resp_functions frf

  13. WHERE frf.action_id=r.menu_id

  14. AND frf.rule_type='M')

  15. AND f.function_id NOT IN (SELECT frf.action_id

  16. FROM apps.fnd_resp_functions frf

  17. WHERE frf.action_id=f.function_id

  18. AND frf.rule_type='F')

  19.  


hope that will be useful for you !

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions