Form Functions attached to a Responsibility

Using the below Query we can get all the form functions that are attached to a Responsibility including Sub Menus and Excluding Menu Exclusions.

SELECT frtl.responsibility_name,

       fr.responsibility_key,

       fm.menu_id,

       fm.menu_name,

       menu.function_id,

       menu.prompt,

       fffv.user_function_name,

       fffv.function_name,

       fffv.TYPE

  FROM (SELECT connect_by_root fmet.menu_id top_menu_id,

               fmet.menu_id                 menu_id,

               fmet.sub_menu_id,

               fmet.function_id,

               fmet.prompt

          FROM fnd_menu_entries_vl fmet

        CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id

                         AND PRIOR fmet.prompt IS NOT NULL) menu,

       fnd_responsibility fr,

       fnd_responsibility_tl frtl,

       fnd_menus fm,

       fnd_form_functions_vl fffv

WHERE fr.menu_id = menu.top_menu_id

   AND fffv.function_id = menu.function_id

   AND fffv.TYPE <> 'SUBFUNCTION'

   AND menu.function_id IS NOT NULL

   AND menu.prompt IS NOT NULL

   AND fm.menu_id = menu.menu_id

   AND frtl.responsibility_id = fr.responsibility_id

   AND frtl.responsibility_name LIKE 'System Administrator'

   AND menu.function_id NOT IN (SELECT ffvl.function_id

                                  FROM apps.fnd_resp_functions frf,

                                       applsys.fnd_responsibility_tl frt,

                                       apps.fnd_form_functions_vl ffvl

                                 WHERE

       frf.responsibility_id = frt.responsibility_id

                                   AND frf.action_id = ffvl.function_id

                                   AND frf.rule_type = 'F'

                                   AND

           frt.responsibility_name = frtl.responsibility_name)

   AND menu.menu_id NOT IN (SELECT fmv.menu_id

                              FROM apps.fnd_resp_functions frf,

                                   applsys.fnd_responsibility_tl frt,

                                   apps.fnd_menus_vl fmv

                             WHERE

       frf.responsibility_id = frt.responsibility_id

                               AND frf.action_id = fmv.menu_id

                               AND frf.rule_type = 'M'

                               AND

       frt.responsibility_name = frtl.responsibility_name)

ORDER BY fffv.user_function_name;

References:

http://alloracleapps.com/scripts/anonmys_scripts/aol/form-functions-attached-to-a-responsibility/