Tuesday, 22 August 2017

Oracle Responsibility: Menu, Function exclusion query

/* Formatted on 8/22/2017 11:51:57 AM (QP5 v5.114.809.3010) */
SELECT   res.responsibility_name,
         app.application_name,
         res.responsibility_key,
         res.description,
         res.start_date,
         res.end_date,
         dat.data_group_name,
         apd.application_name,
         mnu.menu_name,
         req.request_group_name,
         apr.application_name,
         DECODE (exc.rule_type, 'F', 'Function', 'M', 'Menu', rule_type)
            Exclusion_Type,
         DECODE (exc.rule_type,
                 'Ex  F', (SELECT   function_name || ',' || description
                         FROM   fnd_form_functions_vl fnc
                        WHERE   fnc.function_id = exc.action_id),
                 'Ex  M', (SELECT   menu_name || ',' || description
                         FROM   fnd_menus_vl imn
                        WHERE   imn.menu_id = exc.action_id),
                 TO_CHAR (exc.action_id))
            Excluded_Menu_Or_func
  FROM   apps.fnd_responsibility_vl res,
         apps.fnd_application_vl app,
         apps.fnd_data_groups dat,
         apps.fnd_menus_vl mnu,
         apps.fnd_request_groups req,
         apps.fnd_application_vl apd,
         apps.fnd_application_vl apr,
         apps.fnd_resp_functions exc
 WHERE       res.application_id = app.application_id
         AND res.data_group_id = dat.data_group_id
         AND res.data_group_application_id = apd.application_id
         AND res.menu_id = mnu.menu_id
         AND req.request_group_id = res.request_group_id
         AND req.application_id = res.group_application_id
         AND apr.application_id = req.application_id
         AND exc.application_id = res.application_id
         AND exc.responsibility_id = res.responsibility_id
         AND res.RESPONSIBILITY_NAME like 'Cash Management';

Output:-