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

/* Formatted on 6/24/2014 10:57:14 AM (QP5 v5.115.810.9015) */
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 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;