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

No comments:

Post a Comment