Oracle EBS R12 - 一段Oracle EBS中给指定用户增加指定职责的PLSQL脚本

最新推荐文章于 2023-07-13 10:18:55 发布

t0nsha

最新推荐文章于 2023-07-13 10:18:55 发布

阅读量3.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle - EBS/APP 文章标签： oracle user list application exception

本文链接：https://blog.youkuaiyun.com/t0nsha/article/details/8083103

Oracle - EBS/APP 专栏收录该内容

75 篇文章

订阅专栏

针对Oracle EBS R12系统，当需要为多个用户分配职责时，手动操作繁琐。这篇博客提供了一段PLSQL脚本，旨在简化DBA为指定用户批量添加职责的过程。脚本分别适用于Oracle 10g和11g，由于10g不支持continue语句，故进行了区分处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在一些比较注重权限的EBS项目中, 普通用户通常没有System Administrator权限, 只能由DBA给一个一个用户一个一个职责加, 纯手工的话很麻烦, 于是写了一段PLSQL脚本, 留着备用. 由于Oracle 10g尚不支持continue语句, 因此10g和11g分开写了.

1. Based on EBS R12.0.6/Oracle DB 10gR2

DECLARE
   -- script to add user responsibility for R12.0/10gR2

   TYPE tab_user_list IS TABLE OF VARCHAR2 (100);
   TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100);

   -- user to be changed
   l_tab_user_list tab_user_list
                     := tab_user_list ('LIAO'
                                      ,'KARL'
                                      ,'xx');

   -- responsibility to be added
   l_arr_resp_list arr_resp_list
                     := arr_resp_list ('system Administrator'
                                      ,'Purchasing Super User'
                                      ,'Application Administrator');

   CURSOR cur_user (
      pc_username IN VARCHAR2)
   IS
      SELECT fu.user_id
            ,fu.user_name
      FROM   fnd_user fu
      WHERE  fu.user_name = pc_username
             AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE))
                                     AND TRUNC (NVL (fu.end_date, SYSDATE));
   TYPE tab_user IS TABLE OF cur_user%ROWTYPE;
   l_tab_user     tab_user;

   CURSOR cur_resp (
      pc_resp IN VARCHAR2)
   IS
      SELECT fa.application_id
            ,fa.application_short_name
            ,fr.responsibility_id
            ,fr.responsibility_name
            ,fr.responsibility_key
            ,fsg.security_group_key
      FROM   fnd_application fa
            ,fnd_responsibility_vl fr
            ,fnd_security_groups fsg
      WHERE      LOWER (fr.responsibility_name) = LOWER (pc_resp)
             AND fa.application_id = fr.application_id
             AND fr.data_group_id = fsg.security_group_id;
   TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE;
   l_tab_resp     tab_resp;

   expt_no_user   EXCEPTION;
   expt_no_resp   EXCEPTION;
   l_expt_msg     VARCHAR2 (2000);
BEGIN
   --l_tab_user_list := tab_user_list ();
   IF (l_tab_user_list.COUNT = 0)
   THEN
      l_expt_msg  := 'no user to change';
      RAISE expt_no_user;
   END IF;

   --l_arr_resp_list     := arr_resp_list ();
   IF (l_arr_resp_list.COUNT = 0)
   THEN
      l_expt_msg  := 'no resp to add';
      RAISE expt_no_resp;
   END IF;

  -- loop user
  <<loop_tab_user_list>>
   FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            '>>> '
         || idx_tab_user_list
         || ' , working for user ('
         || l_tab_user_list (idx_tab_user_list)
         || ') <<< ');

      -- check if user exist or active
      OPEN cur_user (l_tab_user_list (idx_tab_user_list));
      FETCH cur_user
      BULK   COLLECT INTO l_tab_user;
      CLOSE cur_user;
      IF (l_tab_user.COUNT = 0)
      THEN
         DBMS_OUTPUT.put_line (
               'user ('
            || l_tab_user_list (idx_tab_user_list)
            || ') is not exist or disabled');
         --CONTINUE loop_tab_user_list;
         goto  goto_tab_user_list;
      END IF;

     -- loop responsibility
     <<loop_arr_resp_list>>
      FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST
      LOOP
         -- check if responsibility active
         OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list));
         FETCH cur_resp
         BULK   COLLECT INTO l_tab_resp;
         CLOSE cur_resp;
         IF (l_tab_resp.COUNT = 0)
         THEN
            DBMS_OUTPUT.put_line (
                  'resp ('
               || l_arr_resp_list (idx_arr_resp_list)
               || ') is not exist or disabled');
            --CONTINUE loop_arr_resp_list;
            goto  goto_arr_resp_list;
         END IF;

         -- add resp for user
         DBMS_OUTPUT.put_line (
               'Adding resp ('
            || l_arr_resp_list (idx_arr_resp_list)
            || ') for user ('
            || l_tab_user_list (idx_tab_user_list)
            || ')');
         fnd_user_pkg.addresp (
            username    => l_tab_user_list (idx_tab_user_list)
           ,resp_app    => l_tab_resp (1).application_short_name
           ,resp_key    => l_tab_resp (1).responsibility_key
           ,security_group => l_tab_resp (1).security_group_key
           ,description => NULL
           ,start_date  => TRUNC (SYSDATE)
           ,end_date    => NULL);
           <<goto_arr_resp_list>>  null;
      END LOOP loop_arr_resp_list;
      <<goto_tab_user_list>> null;
   END LOOP loop_tab_user_list;
   COMMIT;
EXCEPTION
   WHEN expt_no_user
   THEN
      DBMS_OUTPUT.put_line (l_expt_msg);
      ROLLBACK;
   WHEN expt_no_resp
   THEN
      DBMS_OUTPUT.put_line (l_expt_msg);
      ROLLBACK;
END;

/*
>>> 1 , working for user (LIAO) <<< 
Adding resp (system Administrator) for user (LIAO)
Adding resp (Purchasing Super User) for user (LIAO)
resp (Application Administrator) is not exist or disabled
>>> 2 , working for user (KARL) <<< 
Adding resp (system Administrator) for user (KARL)
Adding resp (Purchasing Super User) for user (KARL)
resp (Application Administrator) is not exist or disabled
>>> 3 , working for user (xx) <<< 
user (xx) is not exist or disabled
*/

/*
1.  The Oracle PL/SQL GOTO Statement
http://psoug.org/definition/GOTO.htm
*/

2. Based on EBS R12.1/Oracle DB 11gR2

DECLARE
   -- script to add user responsibility for R12.1/11gR2

   TYPE tab_user_list IS TABLE OF VARCHAR2 (100);
   TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100);

   -- user to be changed
   l_tab_user_list tab_user_list
                     := tab_user_list ('LIAO'
                                      ,'KARL'
                                      ,'xx');

   -- responsibility to be added
   l_arr_resp_list arr_resp_list
                     := arr_resp_list ('system Administrator'
                                      ,'Purchasing Super User'
                                      ,'Application Administrator');

   CURSOR cur_user (
      pc_username IN VARCHAR2)
   IS
      SELECT fu.user_id
            ,fu.user_name
      FROM   fnd_user fu
      WHERE  fu.user_name = pc_username
             AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE))
                                     AND TRUNC (NVL (fu.end_date, SYSDATE));
   TYPE tab_user IS TABLE OF cur_user%ROWTYPE;
   l_tab_user     tab_user;

   CURSOR cur_resp (
      pc_resp IN VARCHAR2)
   IS
      SELECT fa.application_id
            ,fa.application_short_name
            ,fr.responsibility_id
            ,fr.responsibility_name
            ,fr.responsibility_key
            ,fsg.security_group_key
      FROM   fnd_application fa
            ,fnd_responsibility_vl fr
            ,fnd_security_groups fsg
      WHERE      LOWER (fr.responsibility_name) = LOWER (pc_resp)
             AND fa.application_id = fr.application_id
             AND fr.data_group_id = fsg.security_group_id;
   TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE;
   l_tab_resp     tab_resp;

   expt_no_user   EXCEPTION;
   expt_no_resp   EXCEPTION;
   l_expt_msg     VARCHAR2 (2000);
BEGIN
   --l_tab_user_list := tab_user_list ();
   IF (l_tab_user_list.COUNT = 0)
   THEN
      l_expt_msg  := 'no user to change';
      RAISE expt_no_user;
   END IF;

   --l_arr_resp_list     := arr_resp_list ();
   IF (l_arr_resp_list.COUNT = 0)
   THEN
      l_expt_msg  := 'no resp to add';
      RAISE expt_no_resp;
   END IF;

  -- loop user
  <<loop_tab_user_list>>
   FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (
            '>>> '
         || idx_tab_user_list
         || ' , working for user ('
         || l_tab_user_list (idx_tab_user_list)
         || ') <<< ');

      -- check if user exist or active
      OPEN cur_user (l_tab_user_list (idx_tab_user_list));
      FETCH cur_user
      BULK   COLLECT INTO l_tab_user;
      CLOSE cur_user;
      IF (l_tab_user.COUNT = 0)
      THEN
         DBMS_OUTPUT.put_line (
               'user ('
            || l_tab_user_list (idx_tab_user_list)
            || ') is not exist or disabled');
         CONTINUE loop_tab_user_list;
      END IF;

     -- loop responsibility
     <<loop_arr_resp_list>>
      FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST
      LOOP
         -- check if responsibility active
         OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list));
         FETCH cur_resp
         BULK   COLLECT INTO l_tab_resp;
         CLOSE cur_resp;
         IF (l_tab_resp.COUNT = 0)
         THEN
            DBMS_OUTPUT.put_line (
                  'resp ('
               || l_arr_resp_list (idx_arr_resp_list)
               || ') is not exist or disabled');
            CONTINUE loop_arr_resp_list;
         END IF;

         -- add resp for user
         DBMS_OUTPUT.put_line (
               'Adding resp ('
            || l_arr_resp_list (idx_arr_resp_list)
            || ') for user ('
            || l_tab_user_list (idx_tab_user_list)
            || ')');
         fnd_user_pkg.addresp (
            username    => l_tab_user_list (idx_tab_user_list)
           ,resp_app    => l_tab_resp (1).application_short_name
           ,resp_key    => l_tab_resp (1).responsibility_key
           ,security_group => l_tab_resp (1).security_group_key
           ,description => NULL
           ,start_date  => TRUNC (SYSDATE)
           ,end_date    => NULL);
      END LOOP loop_arr_resp_list;
   END LOOP loop_tab_user_list;
   COMMIT;
EXCEPTION
   WHEN expt_no_user
   THEN
      DBMS_OUTPUT.put_line (l_expt_msg);
      ROLLBACK;
   WHEN expt_no_resp
   THEN
      DBMS_OUTPUT.put_line (l_expt_msg);
      ROLLBACK;
END;

/*
>>> 1 , working for user (LIAO) <<< 
Adding resp (system Administrator) for user (LIAO)
Adding resp (Purchasing Super User) for user (LIAO)
resp (Application Administrator) is not exist or disabled
>>> 2 , working for user (KARL) <<< 
Adding resp (system Administrator) for user (KARL)
Adding resp (Purchasing Super User) for user (KARL)
resp (Application Administrator) is not exist or disabled
>>> 3 , working for user (xx) <<< 
user (xx) is not exist or disabled
*/