Oracle Applications Knowledge Sharing- Ajay Atre

DECLARE
   -- This will prints the whole menu tree for a given responsibility, run for responsibility as a parameters into v_resp_name


   v_resp_name   VARCHAR2 (100) := '&RESPONSIBILITY_NAME';
   v_menu_id     NUMBER;
   v_main_menu   VARCHAR2 (100);


   PROCEDURE get_menu (p_menu_id IN VARCHAR2, p_level IN NUMBER)
   IS
      CURSOR cur_get_submenus
      IS
           SELECT e.entry_sequence,
                  m.menu_name,
                  m.user_menu_name,
                  e.sub_menu_id,
                  e.function_id,
                  f.function_name,
                  e.prompt,
                  f.web_html_call,
                  f.user_function_name,
                  e.grant_flag
             FROM fnd_menus_vl m,
                  fnd_menu_entries_vl e,
                  fnd_form_functions_vl f
            WHERE     e.sub_menu_id = m.menu_id(+)
                  AND e.function_id = f.function_id(+)
                  AND e.menu_id = p_menu_id
         ORDER BY 1;
      l_spaces   VARCHAR2 (30) := '';
      granted    VARCHAR2 (30);
   BEGIN
      -- for addting space
      FOR i IN 1 .. p_level
      LOOP
         l_spaces := l_spaces || '..';
      END LOOP;

      FOR c IN cur_get_submenus
      LOOP
         granted := '';
         IF c.grant_flag = 'Y'
         THEN
            granted := ' [granted]';
         END IF;
         IF c.sub_menu_id IS NULL
         THEN
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'FUNCTION- '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.function_name
               || ' ('
               || c.user_function_name
               || ')');
         -- dbms_output.put_line(l_spaces || '...........src=' || c.web_html_call);
         ELSE
            DBMS_OUTPUT.put_line (
                  l_spaces
               || 'MENU ('
               || p_level
               || ') '
               || NVL (c.prompt, '[hidden]')
               || granted
               || ' '
               || c.menu_name
               || ' ('
               || c.user_menu_name
               || ')');
         END IF;
         get_menu (c.sub_menu_id, p_level + 1);
      END LOOP;
   END;

BEGIN
   SELECT menu_id
     INTO v_menu_id
     FROM fnd_responsibility_vl
    WHERE responsibility_name = v_resp_name;
   SELECT menu_name
     INTO v_main_menu
     FROM fnd_menus
    WHERE menu_id = v_menu_id;
   DBMS_OUTPUT.put_line ('MAIN MENU- ' || v_main_menu);
   get_menu (v_menu_id, 1);
END;