Query to get responsibility name and menu name

Query to get responsibility name and menu name

In this post , We will be discuss about query to get responsibility name and menu name. We will be share the detail sql query , which will help to extract the oracle responsibility details with application menu and its sub menu details. We will be able to get the menu , submenu and menu submenu function details with responsibility name using this query in oracle apps. This is one of the useful sql query to extract oracle responsibility complete details with application menu name details. This sql query is also extracting other menu relevant informations but you can remove the not required sql query columns as per your requirement. Here below is the complete details about query to get responsibility name and menu name.

Query to get responsibility name and menu name

7 Important table used by Query to get responsibility name and menu name

These below are one of the important table which we need to use this sql query to extract the responsibility details with application menu name and menu relevant informations 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

Detail SQL Query to get responsibility name and menu name

This is the detail sql query extracting responsibility name and menu name details with other relevant details related to Application Form menu.

 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 ))