Sunday 29 December 2013

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;

1 comment: