Oracle Responsibility and Menu Queries

1. Responsibilities Listing

SELECT (SELECT application_short_name

          FROM fnd_application fa

         WHERE fa.application_id = frt.application_id)

          application,

       frt.responsibility_id,

       frt.responsibility_name

  FROM apps.fnd_responsibility_tl frt;

2. Menus Listing

SELECT DISTINCT a.responsibility_name, c.user_menu_name

  FROM apps.fnd_responsibility_tl a,

       apps.fnd_responsibility b,

       apps.fnd_menus_tl c,

       apps.fnd_menus d,

       apps.fnd_application_tl e,

       apps.fnd_application f

WHERE     a.responsibility_id(+) = b.responsibility_id

       AND a.responsibility_id = ‘20538’

       AND b.menu_id = c.menu_id

       AND b.menu_id = d.menu_id

       AND e.application_id = f.application_id

       AND f.application_id = b.application_id

       AND a.LANGUAGE = 'US';

3. User And Assigned Responsibility Listing

  SELECT UNIQUE u.user_id,

                SUBSTR (u.user_name, 1, 30) user_name,

                SUBSTR (r.responsibility_name, 1, 60) responsiblity,

                SUBSTR (a.application_name, 1, 50) application

    FROM fnd_user u,

         fnd_user_resp_groups g,

         fnd_application_tl a,

         fnd_responsibility_tl r

   WHERE     g.user_id(+) = u.user_id

         AND g.responsibility_application_id = a.application_id

         AND a.application_id = r.application_id

         AND g.responsibility_id = r.responsibility_id

ORDER BY SUBSTR (user_name, 1, 30),

         SUBSTR (a.application_name, 1, 50),

         SUBSTR (r.responsibility_name, 1, 60);

4. Menu Tree Sturcture

           SELECT fmv.menu_name,

                  LPAD ('->', (LEVEL - 1) * 2, '-') || fmev.prompt prompt,

                  ffft.user_function_name,

                  LEVEL,

                  fmev.entry_sequence

             FROM apps.fnd_menus_vl fmv,

                  apps.fnd_form_functions_tl ffft,

                  apps.fnd_menu_entries_vl fmev

            WHERE     fmev.menu_id = fmv.menu_id

                  AND ffft.function_id(+) = fmev.function_id

                  AND fmev.prompt IS NOT NULL

                  AND ffft.LANGUAGE(+) = 'US'

       CONNECT BY fmev.menu_id = PRIOR fmev.sub_menu_id

       START WITH fmv.menu_name = 'CSD_MAIN_MENU'

ORDER SIBLINGS BY fmev.entry_sequence