Oracle Applications Knowledge Sharing- Ajay Atre

--Script to pull menus, submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT 
menu_id,
         (SELECT menu_name
            FROM FND_MENUS menu
           WHERE menu_id = menuent.menu_id)
            menu_name,
         (SELECT USER_MENU_NAME
            FROM FND_MENUS_vl menu
           WHERE menu_id = menuent.menu_id)
            USER_MENU_NAME,
         ENTRY_SEQUENCE,
         PROMPT,
         DESCRIPTION,
         GRANT_FLAG,
         SUB_MENU_ID,
         (SELECT MENU_NAME
            FROM FND_MENUS_VL sub
           WHERE sub.menu_id = menuent.SUB_MENU_ID)
            SUB_MENU_name,
         FUNCTION_ID,
         CASE
            WHEN Function_id IS NOT NULL
            THEN
               (SELECT FUNCTION_name
                  FROM fnd_form_functions fun
                 WHERE fun.function_id = menuent.FUNCTION_ID)
         END
            FUNCTION_name,
         LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATE_LOGIN
   FROM FND_MENU_ENTRIES_VL menuent
--WHERE (MENU_Id in (83718,83719))
ORDER BY 2, ENTRY_SEQUENCE;








--Script to pull responsibility,request_group, Menu submenu, functions
--Run the script in 11i and R12 seperately save the extract
SELECT responsibility_id,
       RESPONSIBILITY_NAME,
       (SELECT REQUEST_GROUP_NAME
          FROM fnd_request_groups rg
         WHERE rg.REQUEST_GROUP_ID = fr.REQUEST_GROUP_ID)
          REQUEST_GROUP_NAME,
       fr.LAST_UPDATE_DATE,
       fr.LAST_UPDATED_BY,
       fr.CREATED_BY,
       fr.CREATION_DATE,
       fr.LAST_UPDATE_LOGIN,
       a.*
  FROM apps.fnd_responsibility_vl fr,
       (  SELECT menu_id,
                 (SELECT menu_name
                    FROM FND_MENUS menu
                   WHERE menu_id = menuent.menu_id)
                    menu_name,
                 (SELECT USER_MENU_NAME
                    FROM FND_MENUS_vl menu
                   WHERE menu_id = menuent.menu_id)
                    USER_MENU_NAME,
                 ENTRY_SEQUENCE,
                 PROMPT,
                 DESCRIPTION,
                 GRANT_FLAG,
                 SUB_MENU_ID,
                 (SELECT MENU_NAME
                    FROM FND_MENUS_VL sub
                   WHERE sub.menu_id = menuent.SUB_MENU_ID)
                    SUB_MENU_name,
                 FUNCTION_ID,
                 CASE
                    WHEN Function_id IS NOT NULL
                    THEN
                       (SELECT FUNCTION_name
                          FROM fnd_form_functions fun
                         WHERE fun.function_id = menuent.FUNCTION_ID)
                 END
                    FUNCTION_name,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATED_BY,
                 CREATION_DATE,
                 LAST_UPDATE_LOGIN
            FROM FND_MENU_ENTRIES_VL menuent
        ORDER BY 2, ENTRY_SEQUENCE) a
 WHERE fr.menu_id = a.menu_id;

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;