Query to find function attached to menu in oracle apps

query to find function attached to menu in oracle apps

In this post , We will be discuss about query to find function attached to menu in oracle apps. We will be able to extract the list of menu functions/forms attached to a particular menu in oracle apps. We can find out 

the details of oracle form functions attached to menu in oracle apps using this sql query. This is one of the important sql query to get the details of menu function in oracle apps. Here below is the detail about query to find function attached to menu in oracle apps.

Important Table used by Query to find function attached to menu 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 ))