EXCLUSIONS in Oracle Apps

SIMPLE EXCLUSIONS LIST

SELECT frt.responsibility_name      , frf.creation_date      , frf.created_by      , DECODE (frf.rule_type,  'M', 'Menu',  'F', 'Function') type_      , CASE           WHEN frf.rule_type = 'M'           THEN              (SELECT fmv.user_menu_name                 FROM apps.fnd_menus_vl fmv                WHERE frf.action_id = fmv.menu_id                  AND frf.rule_type = 'M')           WHEN frf.rule_type = 'F'           THEN              (SELECT ffvl.user_function_name                 FROM apps.fnd_form_functions_vl ffvl                WHERE frf.action_id = ffvl.function_id                  AND frf.rule_type = 'F')        END           detail      , CASE           WHEN frf.rule_type = 'M'           THEN              (SELECT fmv.menu_name                 FROM apps.fnd_menus fmv                WHERE frf.action_id = fmv.menu_id                  AND frf.rule_type = 'M')           WHEN frf.rule_type = 'F'           THEN              (SELECT ffvl.function_name                 FROM apps.fnd_form_functions ffvl                WHERE frf.action_id = ffvl.function_id                  AND frf.rule_type = 'F')        END           detail2   FROM apps.fnd_resp_functions frf      , applsys.fnd_responsibility_tl frt      , applsys.fnd_user fu  WHERE frf.responsibility_id = frt.responsibility_id    AND frf.created_by = fu.user_id    AND frt.responsibility_name LIKE '%General%Ledger%';

ALL EXCLUSIONS ON RESPONSIBILITIES, ANOTHER WAY TO GET INFO USING A UNION

SELECT frt.responsibility_name      , fmv.menu_name      , fmv.user_menu_name menu      , fmv.description menu_desc      , '' fcn_name      , '' fcn      , '' fcn_desc   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 = 'AP Inquiry' UNION SELECT frt.responsibility_name      , ffvl.function_name      , '' menu      , '' menu_desc      , ffvl.function_name fcn_name      , ffvl.user_function_name fcn      , ffvl.description fcn_desc   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 = 'AP Inquiry';

ALL EXCLUSIONS ON RESPONSIBILITIES WITH RESP ASSIGNED STATS

SELECT frt.responsibility_name      , (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct      , fmv.menu_name      , fmv.user_menu_name menu      , fmv.description menu_desc      , '' fcn_name      , '' fcn      , '' fcn_desc   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 = 'AP Inquiry'    AND (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0 UNION SELECT frt.responsibility_name      , (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id     AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) user_ct      , ffvl.function_name      , '' menu      , '' menu_desc      , ffvl.function_name fcn_name      , ffvl.user_function_name fcn      , ffvl.description fcn_desc   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 = 'AP Inquiry'    AND (SELECT DISTINCT COUNT (*)     FROM applsys.fnd_user fu, apps.fnd_user_resp_groups_direct furg     WHERE furg.user_id = fu.user_id AND frt.responsibility_id = furg.responsibility_id     AND NVL (furg.end_date, SYSDATE + 1) > SYSDATE     AND NVL (fu.end_date, SYSDATE + 1) > SYSDATE) > 0;

MENU EXCLUSIONS ON RESPONSIBILITIES

SELECT frt.responsibility_name      , fmv.menu_name      , fmv.user_menu_name ex_name      , fmv.description menu_description   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 = 'Inventory';

FUNCTION EXCLUSIONS ON RESPONSIBILITIES

SELECT frt.responsibility_name      , ffvl.function_name      , ffvl.user_function_name      , ffvl.description function_description   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 ffvl.function_name = 'INV_INVTTMTX_MISC' --   AND frt.responsibility_name = 'Inventory'    AND 1 = 1;