Query to find functions attached to responsibility in Oracle apps

Query to find functions attached to responsibility in Oracle apps

In this post , We are going to discuss about Query to find functions attached to responsibility in Oracle apps. This SQL Query will help to extract, each Oracle Responsibility details and its attached Application menu and each application menu functions , sub-functions and sub menu details in oracle apps. We will be able to find out the each application menu form functions attached to each oracle responsibility in oracle apps. This is one of the important oracle apps form menu functions query. Here below is the complete detail about Query to find functions attached to responsibility in Oracle apps.

Query to find functions attached to responsibility in Oracle apps

8 Important Tables used by Query to find functions attached to responsibility in Oracle apps

These are one of the important tables used to developed the query to find functions attached to responsibility in Oracle apps.

1.fnd_menu_entries_tl
2.fnd_form_functions_vl
3.fnd_responsibility_tl
4.fnd_menu_entrie
5.fnd_form_functions_tl

6.fnd_application_tl
7.fnd_responsibility
8.fnd_menus_vl

Detail Query  to find functions attached to responsibility in Oracle apps

Here below is the detail SQL Query to extract the Oracle Responsibility menu functions in Oracle Apps.

 SELECT 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"

    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 find functions attached to responsibility in Oracle apps

Query to find functions attached to responsibility in Oracle apps