Query to get menu and submenu in oracle apps

Query to get menu and submenu in oracle apps

In this post , We will be discuss about query to get menu and submenu in oracle apps. This SQL query will help to extract the list of Menu and their submenu details in oracle apps. We will be able to get the detail of all main menu and submenu details attached to a given responsibility in oracle apps. This is one of the important sql query about menu and submenu in oracle apps. We can fetch informations about all responsibilities menu and submenu using this sql query. Here below is the detail about sql query to get menu and submenu in oracle apps.

Query to get menu and submenu in oracle apps

8 Important table used by sql Query to get menu and submenu in oracle apps

1.fnd_application_tl
2.fnd_responsibility
3.fnd_menus_vl
4.fnd_responsibility_tl
5.fnd_menu_entries
6.fnd_menu_entries_tl
7.fnd_form_functions_vl
8.fnd_form_functions_tl

Detail SQL Query to get menu and submenu in oracle apps

 SELECT main_query.application_name "App Name"
       , main_query.responsibility_name "Responsibility"
       , main_query.user_menu_name "Main Menu Name"
       , main_query.entry_sequence "Seq"
       , main_query.prompt "Prompt"
       , main_query.user_function_name "Function"
       , main_query.func_descrip "Function Descrip"
       , main_query.sub_menu_name "SubMenu Name"
       , main_query.sub_seq "Sub Seq"
       , main_query.sub_prompt "SubPrompt"
       , main_query.sub_func "SubFunction"
       , main_query.sub_func_descrip "SubFunction Descrip"
       , main_query.grant_flag "Grant Flag"
       , main_query.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 main_query.function_id = exc.action_id ) )
            exclude_func
       , 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 main_query.sub_func_id = exc.action_id ) )
            exclude_sub_func
       , 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 main_query.menu_id = exc.action_id ) )
            exclude_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 main_query.sub_menu_id = exc.action_id ) )
            exclude_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 main_query.sub_sub_menu_id = exc.action_id ) )
            exclude_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
                       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 = :P_APPL_ID) FIRST
                 , apps.fnd_menus_vl fmv2     
                 , apps.fnd_menu_entries fme2
                 , apps.fnd_menu_entries_tl fmet2
                 , apps.fnd_form_functions_tl ffft 
                 , apps.fnd_form_functions_tl ffft2 
             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(+)) main_query
         LEFT OUTER JOIN apps.fnd_resp_functions exc 
            ON ( main_query.application_id = exc.application_id
            AND main_query.responsibility_id = exc.responsibility_id
            AND ( main_query.function_id = exc.action_id
              OR main_query.sub_func_id = exc.action_id
              OR main_query.menu_id = exc.action_id
              OR main_query.sub_menu_id = exc.action_id
              OR main_query.sub_sub_menu_id = exc.action_id ))

Query to get menu and submenu in oracle apps