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;