3 Replies Latest reply: Oct 10, 2011 2:03 PM by Sandeep Gandhi, Consultant RSS

    Extract Menu Tree for All Active Menus


      I am trying to create an extract of the menu tree hierarchy for a group of "active" menus (i.e., active in that the menu is assigned to a responsibility which is assigned to a user). I have used the following query which returns the menu tree for a given MENU_ID (&MENU_ID):

      SELECT      LPAD(' ', 6*(LEVEL-1)) || menu_entry.entry_sequence sequence
           , LPAD(' ', 6*(LEVEL-1)) || menu.user_menu_name SubMenu_Descrition
           , LPAD(' ', 6*(LEVEL-1)) || func.user_function_name Function_Description
           , menu.menu_id
           , func.function_id
           , menu_entry.grant_flag Grant_Flag
           , DECODE(menu_entry.sub_menu_id, null, 'FUNCTION', DECODE(menu_entry.function_id, null, 'SUBMENU', 'BOTH')) Type
      FROM      apps.fnd_menu_entries menu_entry
           , apps.fnd_menus_tl menu
           , apps.fnd_form_functions_tl func
      WHERE      menu_entry.sub_menu_id = menu.menu_id(+)
           AND menu_entry.function_id = func.function_id(+)
           AND grant_flag = 'Y'
           START WITH menu_entry.menu_id =      (
                               SELECT      menu_id
                               FROM      apps.fnd_menus_tl menu2
                               WHERE      menu2.menu_id = '*&menu_id'*)
           CONNECT BY menu_entry.menu_id = PRIOR menu_entry.sub_menu_id
      ORDER SIBLINGS BY menu_entry.entry_sequence;

      I would like to be able to run the above query, and subsequently spool to file, for all of the MENU_IDs returned as a result of the following sub-query (i.e., replace the &menu_id value with the menu_ids returned from the following query):

      SELECT      FM.MENU_ID
      FROM      apps.FND_MENUS FM,
           apps.fnd_user_resp_groups furg,
           apps.fnd_responsibility fr
      WHERE     fm.menu_id = fr.menu_id
           AND furg.responsibility_id = fr.responsibility_id;

      Any help would be appreciated!