Query to get the Menu,Submenu and function details For Oracle Menus

Query to get the Menu,Submenu and function details For Oracle Menus

/* Formatted on 2017/10/13 16:26 (Formatter Plus v4.8.8) */
SELECT   SECOND.application_id "App ID", SECOND.application_name "App Name",
         SECOND.responsibility_id "Resp ID",
         SECOND.responsibility_name "Responsibility",
         SECOND.menu_id "Menu ID", SECOND.user_menu_name "Main Menu Name",
         SECOND.entry_sequence "Seq", SECOND.prompt "Prompt",
         SECOND.function_id "Function ID",
         SECOND.user_function_name "Function",
         SECOND.func_descrip "Function Descrip",
         SECOND.sub_menu_id "SubMenu ID", SECOND.sub_menu_name "SubMenu Name",
         SECOND.sub_seq "Sub Seq", SECOND.sub_prompt "SubPrompt",
         SECOND.sub_func_id "SubFunction ID", SECOND.sub_func "SubFunction",
         SECOND.sub_func_descrip "SubFunction Descrip",
         SECOND.sub_sub_menu_id "Sub-SubMenu ID",
         SECOND.grant_flag "Grant Flag", SECOND.resp_end_date "Resp End Date",
         DECODE (exc.rule_type,
                 'F', (SELECT 'Ex F: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.function_id = exc.action_id)
                ) excluded_function,
         DECODE (exc.rule_type,
                 'F', (SELECT 'Ex SF: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_func_id = exc.action_id)
                ) excluded_sub_function,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex M: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.menu_id = exc.action_id)
                ) excluded_menu,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex SM: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_menu_id = exc.action_id)
                ) excluded_sub_menu,
         DECODE (exc.rule_type,
                 'M', (SELECT 'Ex SSM: ' || exc.action_id
                         FROM fnd_form_functions_vl fnc
                        WHERE fnc.function_id = exc.action_id
                          AND SECOND.sub_sub_menu_id = exc.action_id)
                ) excluded_sub_sub_menu
    FROM (SELECT   FIRST.application_id, FIRST.application_name,
                   FIRST.responsibility_id, FIRST.responsibility_name,
                   FIRST.end_date AS resp_end_date, FIRST.menu_id,
                   FIRST.user_menu_name, FIRST.entry_sequence, FIRST.prompt,
                   FIRST.function_id, ffft.user_function_name,
                   ffft.description AS func_descrip, FIRST.sub_menu_id,
                   fmv2.user_menu_name AS sub_menu_name,
                   fme2.entry_sequence AS sub_seq, fmet2.prompt AS sub_prompt,
                   fme2.function_id AS sub_func_id,
                   ffft2.user_function_name AS sub_func,
                   ffft2.description AS sub_func_descrip,
                   fme2.sub_menu_id AS sub_sub_menu_id, FIRST.grant_flag
              FROM (SELECT   fat.application_id, fat.application_name,
                             fr.responsibility_id, frt.responsibility_name,
                             fr.end_date, fr.menu_id, fmv.user_menu_name,
                             fme.entry_sequence, fmet.prompt, fme.sub_menu_id,
                             fme.function_id, fme.grant_flag
                        FROM apps.fnd_application_tl fat,
                             apps.fnd_responsibility fr,
                             apps.fnd_menus_vl fmv,
                             apps.fnd_responsibility_tl frt,
                             apps.fnd_menu_entries fme,
                             apps.fnd_menu_entries_tl fmet
                       --joins and constant selection
                    WHERE    fat.application_id = fr.application_id(+)
                         AND fr.menu_id = fmv.menu_id(+)
                         AND fr.responsibility_id = frt.responsibility_id(+)
                         AND fr.menu_id = fme.menu_id(+)
                         AND fme.menu_id = fmet.menu_id(+)
                         AND fme.entry_sequence = fmet.entry_sequence(+)
                         AND fmet.LANGUAGE = 'US'
                         AND frt.responsibility_name = 'Receivables Manager'
--                     AND fat.application_id = &appid
                    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) FIRST
                         ---for application, responsibility and main menu info
                                                                         ,
                   apps.fnd_menus_vl fmv2                  ---for submenu info
                                         ,
                   apps.fnd_menu_entries fme2,
                   apps.fnd_menu_entries_tl fmet2,
                   apps.fnd_form_functions_tl ffft        ---for function info
                                                  ,
                   apps.fnd_form_functions_tl ffft2    ---for subfunction info
             --left outer joins keep original records and add any sub menu and function info
          WHERE    FIRST.function_id = ffft.function_id(+)
               AND FIRST.sub_menu_id = fmv2.menu_id(+)
               AND FIRST.sub_menu_id = fme2.menu_id(+)
               AND fme2.menu_id = fmet2.menu_id(+)
               AND fme2.entry_sequence = fmet2.entry_sequence(+)
               AND fme2.function_id = ffft2.function_id(+)
          ORDER BY 1,
                   2,
                   3,
                   4,
                   5,
                   6,
                   7,
                   8,
                   9,
                   10,
                   11,
                   12,
                   13,
                   14,
                   15,
                   16,
                   17,
                   18,
                   19,
                   20,
                   21) SECOND           -- adds any sub menu and function info
         LEFT OUTER JOIN
         apps.fnd_resp_functions exc                         ---for exclusions
         ON (    SECOND.application_id = exc.application_id
             AND SECOND.responsibility_id = exc.responsibility_id
             AND (   SECOND.function_id = exc.action_id
                  OR SECOND.sub_func_id = exc.action_id
                  OR SECOND.menu_id = exc.action_id
                  OR SECOND.sub_menu_id = exc.action_id
                  OR SECOND.sub_sub_menu_id = exc.action_id
                 )
            )
ORDER BY 1,
         2,
         3,
         4,
         5,
         6,
         7,
         8,
         9,
         10,
         11,
         12,
         13,
         14,
         15,
         16,
         17,
         18,
         19,
         20,
         21;