Wednesday, October 15, 2014

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;
  1. 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';
  1. 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);
  1. 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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect