GeekInterview.com
Series: Subject: Topic:
Question: 142 of 208

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.

Asked by: Interview Candidate | Asked on: Jan 4th, 2007
Showing Answers 1 - 4 of 4 Answers
namitak22

Answered On : Mar 15th, 2007

View all answers by namitak22

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

  
Login to rate this answer.

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

  
Login to rate this answer.
fouz

Answered On : Sep 13th, 2011

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

  
Login to rate this answer.
fouzia

Answered On : 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 !

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Question Categories

Oracle Apps Forms FAQ

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.