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:-

Responsibilities not visible to user in oracle apps

We recently encountered this issue on a R12 instance fully functional instance.

 1) Login as sysadmin or any user who as privileges to create user/add responsibilities

2) Create a new user and add responsibilities or add additional seeded/custom responsibilities to existing user


3) New/existing user logs in but he/she cant see the new responsibility.

Strange isnt it. I had encountered a simillar situation post 11.5.x to 11.5.10.2 upgrade, but the underlying problem is different here.

To tackle this situation, we ran the concurrent program “Workflow Directory Services User/Role Validation” with below parameters:

p_BatchSize – 10000 (Default Value 10000)
p_Check_Dangling – Yes (Default value No)
Add missing user/role assignments – Yes (Default Value No)
Update WHO columns in WF tables – No (Default Value No)

So what does this concurrent program do. It syncs all the user and role related WF tables with latest information viz., WF_LOCAL_ROLES, WF_LOCAL_USER_ROLES, WF_USER_ROLE_ASSIGNMENTS etc.,

The time taken to complete this program depends on the number of users/roles to be synched. After completion of this request the affected user can log back in to see the missing responsibilities.

You can also schedule this request to run every 15 minutes or so, but it depends how frequently you create/assign new users/roles/responsibilities.

Query: Menus and Function Name for a Particular Responsibility in oracle apps

/* Formatted on 10/19/2015 11:03:28 AM (QP5 v5.240.12305.39446) */
  SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) =
                                   UPPER (:resp_name))
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence

Query to List all the responsibilities attached to a User In Oracle apps

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.


select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')

Finding Responsibility of a Form in oralce apps

Below is the Query, which helps you to find all the responsibilities of a Form, to which it has been attached.


SELECT   FORMS.form_name,
         FORMSTL.user_form_name,
         RESTL.responsibility_name,
         FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions FUNC,
         fnd_menu_entries MENU,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE       FORMSTL.user_form_name LIKE 'CUM Workbench%'
         AND FORMS.form_id = FORMSTL.form_id
         AND FUNC.form_id = FORMS.form_id
         AND MENU.function_id = FUNC.function_id
         AND RES.menu_id(+) = MENU.menu_id
         AND RES.responsibility_id = RESTL.responsibility_id(+)

Excluded menu and function in Responsibility Query in oracle apps

This query can give details of a Oracle Application Responsibility and list of menus and functions that are excluded from that responsibility.

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,'F',(select function_name || ',' || description
                                   from fnd_form_functions_vl fnc
                                  where fnc.function_id = exc.action_id
                                )
                           ,'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 ;

Query to find the menus, functions for a responsibility in oracle apps

SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM apps.fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM apps.fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) = ('responsibility name')
                                   )
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence

Responsibility Based Transaction in EBS master data

SELECT       fu.user_name,

             fd.responsibility_id,

             ftl.responsibility_name,

             fd.start_date,

             fd.end_date,

             aia.invoice_num,

             aia.invoice_date

      FROM fnd_user fu, fnd_user_resp_groups_direct fd,fnd_responsibility_tl ftl,ap_invoices_all aia

      WHERE     fu.user_id = fd.user_id

            AND fd.RESPONSIBILITY_ID = ftl.RESPONSIBILITY_ID

            AND fd.end_date IS NULL

            AND fu.user_id  =   :p_user_id

            AND (fu.user_id  =   aia.created_by

              OR fu.user_id  =   aia.last_updated_by) 

            AND  ftl.responsibility_name='Payables Super User (Process Operations)'

To identify the menu list in responsibility in Oracle Apps

/* Formatted on 7/17/2014 11:55:20 AM (QP5 v5.115.810.9015) */
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,
          'F', (SELECT function_name || ',' || description
                FROM fnd_form_functions_vl fnc
                WHERE fnc.function_id = exc.action_id),
          '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;

API Script to add the responsibilities to a user

/* Formatted on 6/24/2014 12:35:52 PM (QP5 v5.115.810.9015) */
DECLARE
   CURSOR c_resp
   IS
      SELECT fav.application_short_name,
             fav.application_name,
             frv.responsibility_key,
             frv.responsibility_name
      FROM fnd_application_vl fav, fnd_responsibility_vl frv
      WHERE frv.application_id = fav.application_id
            AND frv.responsibility_name IN ('Application Developer');

   l_user_name         VARCHAR2 (15) := '&USER_NAME';
   l_appl_short_name   fnd_application_vl.application_short_name%TYPE;
   l_resp_name         fnd_responsibility_vl.responsibility_name%TYPE;
   l_resp_key          fnd_responsibility_vl.responsibility_key%TYPE;
   l_description VARCHAR2 (100)
         := 'Adding Responsibility to the user using script' ;
BEGIN
   FOR resp_rec IN c_resp
   LOOP
      l_appl_short_name   := resp_rec.application_short_name;
      l_resp_key          := resp_rec.responsibility_key;
      l_resp_name         := resp_rec.responsibility_name;

      BEGIN
         fnd_user_pkg.addresp (username => l_user_name,
                               resp_app => l_appl_short_name,
                               resp_key => l_resp_key,
                               security_group => 'STANDARD',
                               description => l_description,
                               start_date => SYSDATE,
                               end_date => NULL
         );
         COMMIT;
         DBMS_OUTPUT.put_line(   'The responsibility '
                              || l_resp_name
                              || ' is added to the user '
                              || l_user_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line(   'Responsibility '
                                 || l_resp_name
                                 || ' IS NOT added to the user '
                                 || l_user_name
                                 || ' due to '
                                 || SQLCODE
                                 || '; '
                                 || SUBSTR (SQLERRM, 1, 250));
            DBMS_OUTPUT.put_line ('');
            ROLLBACK;
      END;
   END LOOP;
END;

API script to Disable the enabled Responsibilities for a User

/* Formatted on 6/24/2014 12:34:59 PM (QP5 v5.115.810.9015) */
DECLARE
   --cursor to get all inactive users
   CURSOR cur_inactive_user
   IS
      SELECT fu.user_id,
             fd.responsibility_id,
             fd.responsibility_application_id,
             fd.security_group_id,
             fd.start_date,
             fd.end_date
      FROM fnd_user fu, fnd_user_resp_groups_direct fd
      WHERE     fu.user_id = fd.user_id
            AND (fu.end_date <= SYSDATE OR fu.end_date IS NOT NULL)
            AND fd.end_date IS NOT NULL
            AND fu.user_id = :p_user_id;
BEGIN
   FOR rec_inactive_user IN cur_inactive_user
   LOOP
      --checking if the responsibility is assigned to the user
      IF (fnd_user_resp_groups_api.assignment_exists (rec_inactive_user.user_id,
                                                      rec_inactive_user.responsibility_id,
                                                      rec_inactive_user.responsibility_application_id,
                                                      rec_inactive_user.security_group_id
          ))
      THEN
         -- Call API to End date the responsibility
         fnd_user_resp_groups_api.update_assignment (user_id => rec_inactive_user.user_id,
                                                     responsibility_id => rec_inactive_user.responsibility_id,
                                                     responsibility_application_id => rec_inactive_user.responsibility_application_id,
                                                     security_group_id => rec_inactive_user.security_group_id,
                                                     start_date => rec_inactive_user.start_date,
                                                     end_date => NULL,
                                                     description => NULL
         );
         COMMIT;
      END IF;
   END LOOP;
END;

API script to Enable the Disabled Responsibilities for a User

/* Formatted on 6/24/2014 12:33:31 PM (QP5 v5.115.810.9015) */
DECLARE
   --cursor to get all inactive users
   CURSOR cur_inactive_user
   IS
      SELECT fu.user_id,
             fd.responsibility_id,
             fd.responsibility_application_id,
             fd.security_group_id,
             fd.start_date,
             fd.end_date
      FROM fnd_user fu, fnd_user_resp_groups_direct fd
      WHERE fu.user_id = fd.user_id AND fu.user_id = :p_user_id;
BEGIN
   FOR rec_inactive_user IN cur_inactive_user
   LOOP
      fnd_user_resp_groups_api.update_assignment (user_id => rec_inactive_user.user_id,
                                                  responsibility_id => rec_inactive_user.responsibility_id,
                                                  responsibility_application_id => rec_inactive_user.responsibility_application_id,
                                                  security_group_id => rec_inactive_user.security_group_id,
                                                  start_date => rec_inactive_user.start_date,
                                                  end_date => NULL,
                                                  description => NULL
      );
      COMMIT;
   END LOOP;
END;

Create Menu and responsibility to attached the User in oracle apps

Create Menu Based on the oracle form :-

Inventory --> item cost --> oracle form based menu creation process

To Create Menu

System administrator --> Application -->   Menu   To Create Responsibility System administrator --> Security --> Responsibility --> Define   Create Responsibility and attach the Menu:   Create User:- System administrator --> Security --> User --> Define  create responsibility and attached the user   user created user and attached the  Responsibility Go to EBS