Showing posts with label sub query. Show all posts
Showing posts with label sub query. Show all posts

Tuesday, March 12, 2013

Using CONNECT BY PRIOR to get all functions, even in submenu

So you need to find all the functions under a menu, includes the functions under all the sub menus, how to do that? Here it is:

Script 1 - Menu Functions from the User Menu name.

One input parameter:- &UserMenuName (Example: Menu1 - the name has to be precise but is Case Insensitive)



SELECT DISTINCT fft.user_function_name,
       ff.function_name,
       ff.function_id,
       mut.user_menu_name,
       menus.level_num
  FROM fnd_form_functions_tl fft,
       fnd_form_functions ff,
       fnd_menu_entries me,
       fnd_menus_tl mut,
     (SELECT DISTINCT level level_num,
            me.menu_id
       FROM fnd_menu_entries me
      START WITH menu_id IN
      (SELECT menu_id
        FROM fnd_menus_tl mut
       WHERE  UPPER(mut.user_menu_name) = UPPER('&UserMenuName'))
      CONNECT BY PRIOR me.sub_menu_id  = me.menu_id) Menus
 WHERE fft.function_id = ff.function_id
   AND ff.function_id = me.function_id
   AND mut.menu_id = menus.menu_id
   AND me.menu_id = mut.menu_id
 ORDER BY level_num,
       mut.user_menu_name

2 - Menu Functions based on the Responsibility name.

One input parameter:- &ResponsibilityName (Example: Resonsibility1 - the name has to be precise but is Case Insensitive)

SELECT DISTINCT fft.user_function_name,
       ff.function_name,
       ff.function_id,
       mut.user_menu_name,
       menus.level_num
  FROM fnd_form_functions_tl fft,
       fnd_form_functions ff,
       fnd_menu_entries me,
       fnd_menus_tl mut,
     (SELECT DISTINCT level level_num,
            me.menu_id
       FROM fnd_menu_entries me
      START WITH menu_id IN
      (SELECT menu_id
        FROM fnd_responsibility fr,
       fnd_responsibility_tl frt
       WHERE fr.responsibility_id = frt.responsibility_id
         AND UPPER(frt.RESPONSIBILITY_NAME) = UPPER('&ResponsibilityName'))
      CONNECT BY PRIOR me.sub_menu_id  = me.menu_id) Menus
 WHERE fft.function_id = ff.function_id
   AND ff.function_id = me.function_id
   AND mut.menu_id = menus.menu_id
   AND me.menu_id = mut.menu_id
 ORDER BY level_num,
       mut.user_menu_name