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

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

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

[sql]  view plain copy

1. DECLARE  
2.    -- script to add user responsibility for R12.0/10gR2  
3.   
4.    TYPE tab_user_list IS TABLE OF VARCHAR2 (100);  
5.    TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100);  
6.   
7.    -- user to be changed  
8.    l_tab_user_list tab_user_list  
9.                      := tab_user_list ('LIAO'  
10.                                       ,'KARL'  
11.                                       ,'xx');  
12.   
13.    -- responsibility to be added  
14.    l_arr_resp_list arr_resp_list  
15.                      := arr_resp_list ('system Administrator'  
16.                                       ,'Purchasing Super User'  
17.                                       ,'Application Administrator');  
18.   
19.    CURSOR cur_user (  
20.       pc_username IN VARCHAR2)  
21.    IS  
22.       SELECT fu.user_id  
23.             ,fu.user_name  
24.       FROM   fnd_user fu  
25.       WHERE  fu.user_name = pc_username  
26.              AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE))  
27.                                      AND TRUNC (NVL (fu.end_date, SYSDATE));  
28.    TYPE tab_user IS TABLE OF cur_user%ROWTYPE;  
29.    l_tab_user     tab_user;  
30.   
31.    CURSOR cur_resp (  
32.       pc_resp IN VARCHAR2)  
33.    IS  
34.       SELECT fa.application_id  
35.             ,fa.application_short_name  
36.             ,fr.responsibility_id  
37.             ,fr.responsibility_name  
38.             ,fr.responsibility_key  
39.             ,fsg.security_group_key  
40.       FROM   fnd_application fa  
41.             ,fnd_responsibility_vl fr  
42.             ,fnd_security_groups fsg  
43.       WHERE      LOWER (fr.responsibility_name) = LOWER (pc_resp)  
44.              AND fa.application_id = fr.application_id  
45.              AND fr.data_group_id = fsg.security_group_id;  
46.    TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE;  
47.    l_tab_resp     tab_resp;  
48.   
49.    expt_no_user   EXCEPTION;  
50.    expt_no_resp   EXCEPTION;  
51.    l_expt_msg     VARCHAR2 (2000);  
52. BEGIN  
53.    --l_tab_user_list := tab_user_list ();  
54.    IF (l_tab_user_list.COUNT = 0)  
55.    THEN  
56.       l_expt_msg  := 'no user to change';  
57.       RAISE expt_no_user;  
58.    END IF;  
59.   
60.    --l_arr_resp_list     := arr_resp_list ();  
61.    IF (l_arr_resp_list.COUNT = 0)  
62.    THEN  
63.       l_expt_msg  := 'no resp to add';  
64.       RAISE expt_no_resp;  
65.    END IF;  
66.   
67.   -- loop user  
68.   <<loop_tab_user_list>>  
69.    FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST  
70.    LOOP  
71.       DBMS_OUTPUT.put_line (  
72.             '>>> '  
73.          || idx_tab_user_list  
74.          || ' , working for user ('  
75.          || l_tab_user_list (idx_tab_user_list)  
76.          || ') <<< ');  
77.   
78.       -- check if user exist or active  
79.       OPEN cur_user (l_tab_user_list (idx_tab_user_list));  
80.       FETCH cur_user  
81.       BULK   COLLECT INTO l_tab_user;  
82.       CLOSE cur_user;  
83.       IF (l_tab_user.COUNT = 0)  
84.       THEN  
85.          DBMS_OUTPUT.put_line (  
86.                'user ('  
87.             || l_tab_user_list (idx_tab_user_list)  
88.             || ') is not exist or disabled');  
89.          --CONTINUE loop_tab_user_list;  
90.          goto  goto_tab_user_list;  
91.       END IF;  
92.   
93.      -- loop responsibility  
94.      <<loop_arr_resp_list>>  
95.       FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST  
96.       LOOP  
97.          -- check if responsibility active  
98.          OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list));  
99.          FETCH cur_resp  
100.          BULK   COLLECT INTO l_tab_resp;  
101.          CLOSE cur_resp;  
102.          IF (l_tab_resp.COUNT = 0)  
103.          THEN  
104.             DBMS_OUTPUT.put_line (  
105.                   'resp ('  
106.                || l_arr_resp_list (idx_arr_resp_list)  
107.                || ') is not exist or disabled');  
108.             --CONTINUE loop_arr_resp_list;  
109.             goto  goto_arr_resp_list;  
110.          END IF;  
111.   
112.          -- add resp for user  
113.          DBMS_OUTPUT.put_line (  
114.                'Adding resp ('  
115.             || l_arr_resp_list (idx_arr_resp_list)  
116.             || ') for user ('  
117.             || l_tab_user_list (idx_tab_user_list)  
118.             || ')');  
119.          fnd_user_pkg.addresp (  
120.             username    => l_tab_user_list (idx_tab_user_list)  
121.            ,resp_app    => l_tab_resp (1).application_short_name  
122.            ,resp_key    => l_tab_resp (1).responsibility_key  
123.            ,security_group => l_tab_resp (1).security_group_key  
124.            ,description => NULL  
125.            ,start_date  => TRUNC (SYSDATE)  
126.            ,end_date    => NULL);  
127.            <<goto_arr_resp_list>>  null;  
128.       END LOOP loop_arr_resp_list;  
129.       <<goto_tab_user_list>> null;  
130.    END LOOP loop_tab_user_list;  
131.    COMMIT;  
132. EXCEPTION  
133.    WHEN expt_no_user  
134.    THEN  
135.       DBMS_OUTPUT.put_line (l_expt_msg);  
136.       ROLLBACK;  
137.    WHEN expt_no_resp  
138.    THEN  
139.       DBMS_OUTPUT.put_line (l_expt_msg);  
140.       ROLLBACK;  
141. END;  
142.   
143. /*  
144. >>> 1 , working for user (LIAO) <<<   
145. Adding resp (system Administrator) for user (LIAO)  
146. Adding resp (Purchasing Super User) for user (LIAO)  
147. resp (Application Administrator) is not exist or disabled  
148. >>> 2 , working for user (KARL) <<<   
149. Adding resp (system Administrator) for user (KARL)  
150. Adding resp (Purchasing Super User) for user (KARL)  
151. resp (Application Administrator) is not exist or disabled  
152. >>> 3 , working for user (xx) <<<   
153. user (xx) is not exist or disabled  
154. */  
155.   
156. /*  
157. 1.  The Oracle PL/SQL GOTO Statement  
158. http://psoug.org/definition/GOTO.htm  
159. */  

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

[sql]  view plain copy

1. DECLARE  
2.    -- script to add user responsibility for R12.1/11gR2  
3.   
4.    TYPE tab_user_list IS TABLE OF VARCHAR2 (100);  
5.    TYPE arr_resp_list IS VARRAY (100) OF VARCHAR2 (100);  
6.   
7.    -- user to be changed  
8.    l_tab_user_list tab_user_list  
9.                      := tab_user_list ('LIAO'  
10.                                       ,'KARL'  
11.                                       ,'xx');  
12.   
13.    -- responsibility to be added  
14.    l_arr_resp_list arr_resp_list  
15.                      := arr_resp_list ('system Administrator'  
16.                                       ,'Purchasing Super User'  
17.                                       ,'Application Administrator');  
18.   
19.    CURSOR cur_user (  
20.       pc_username IN VARCHAR2)  
21.    IS  
22.       SELECT fu.user_id  
23.             ,fu.user_name  
24.       FROM   fnd_user fu  
25.       WHERE  fu.user_name = pc_username  
26.              AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (fu.start_date, SYSDATE))  
27.                                      AND TRUNC (NVL (fu.end_date, SYSDATE));  
28.    TYPE tab_user IS TABLE OF cur_user%ROWTYPE;  
29.    l_tab_user     tab_user;  
30.   
31.    CURSOR cur_resp (  
32.       pc_resp IN VARCHAR2)  
33.    IS  
34.       SELECT fa.application_id  
35.             ,fa.application_short_name  
36.             ,fr.responsibility_id  
37.             ,fr.responsibility_name  
38.             ,fr.responsibility_key  
39.             ,fsg.security_group_key  
40.       FROM   fnd_application fa  
41.             ,fnd_responsibility_vl fr  
42.             ,fnd_security_groups fsg  
43.       WHERE      LOWER (fr.responsibility_name) = LOWER (pc_resp)  
44.              AND fa.application_id = fr.application_id  
45.              AND fr.data_group_id = fsg.security_group_id;  
46.    TYPE tab_resp IS TABLE OF cur_resp%ROWTYPE;  
47.    l_tab_resp     tab_resp;  
48.   
49.    expt_no_user   EXCEPTION;  
50.    expt_no_resp   EXCEPTION;  
51.    l_expt_msg     VARCHAR2 (2000);  
52. BEGIN  
53.    --l_tab_user_list := tab_user_list ();  
54.    IF (l_tab_user_list.COUNT = 0)  
55.    THEN  
56.       l_expt_msg  := 'no user to change';  
57.       RAISE expt_no_user;  
58.    END IF;  
59.   
60.    --l_arr_resp_list     := arr_resp_list ();  
61.    IF (l_arr_resp_list.COUNT = 0)  
62.    THEN  
63.       l_expt_msg  := 'no resp to add';  
64.       RAISE expt_no_resp;  
65.    END IF;  
66.   
67.   -- loop user  
68.   <<loop_tab_user_list>>  
69.    FOR idx_tab_user_list IN l_tab_user_list.FIRST .. l_tab_user_list.LAST  
70.    LOOP  
71.       DBMS_OUTPUT.put_line (  
72.             '>>> '  
73.          || idx_tab_user_list  
74.          || ' , working for user ('  
75.          || l_tab_user_list (idx_tab_user_list)  
76.          || ') <<< ');  
77.   
78.       -- check if user exist or active  
79.       OPEN cur_user (l_tab_user_list (idx_tab_user_list));  
80.       FETCH cur_user  
81.       BULK   COLLECT INTO l_tab_user;  
82.       CLOSE cur_user;  
83.       IF (l_tab_user.COUNT = 0)  
84.       THEN  
85.          DBMS_OUTPUT.put_line (  
86.                'user ('  
87.             || l_tab_user_list (idx_tab_user_list)  
88.             || ') is not exist or disabled');  
89.          CONTINUE loop_tab_user_list;  
90.       END IF;  
91.   
92.      -- loop responsibility  
93.      <<loop_arr_resp_list>>  
94.       FOR idx_arr_resp_list IN l_arr_resp_list.FIRST .. l_arr_resp_list.LAST  
95.       LOOP  
96.          -- check if responsibility active  
97.          OPEN cur_resp (l_arr_resp_list (idx_arr_resp_list));  
98.          FETCH cur_resp  
99.          BULK   COLLECT INTO l_tab_resp;  
100.          CLOSE cur_resp;  
101.          IF (l_tab_resp.COUNT = 0)  
102.          THEN  
103.             DBMS_OUTPUT.put_line (  
104.                   'resp ('  
105.                || l_arr_resp_list (idx_arr_resp_list)  
106.                || ') is not exist or disabled');  
107.             CONTINUE loop_arr_resp_list;  
108.          END IF;  
109.   
110.          -- add resp for user  
111.          DBMS_OUTPUT.put_line (  
112.                'Adding resp ('  
113.             || l_arr_resp_list (idx_arr_resp_list)  
114.             || ') for user ('  
115.             || l_tab_user_list (idx_tab_user_list)  
116.             || ')');  
117.          fnd_user_pkg.addresp (  
118.             username    => l_tab_user_list (idx_tab_user_list)  
119.            ,resp_app    => l_tab_resp (1).application_short_name  
120.            ,resp_key    => l_tab_resp (1).responsibility_key  
121.            ,security_group => l_tab_resp (1).security_group_key  
122.            ,description => NULL  
123.            ,start_date  => TRUNC (SYSDATE)  
124.            ,end_date    => NULL);  
125.       END LOOP loop_arr_resp_list;  
126.    END LOOP loop_tab_user_list;  
127.    COMMIT;  
128. EXCEPTION  
129.    WHEN expt_no_user  
130.    THEN  
131.       DBMS_OUTPUT.put_line (l_expt_msg);  
132.       ROLLBACK;  
133.    WHEN expt_no_resp  
134.    THEN  
135.       DBMS_OUTPUT.put_line (l_expt_msg);  
136.       ROLLBACK;  
137. END;  
138.   
139. /*  
140. >>> 1 , working for user (LIAO) <<<   
141. Adding resp (system Administrator) for user (LIAO)  
142. Adding resp (Purchasing Super User) for user (LIAO)  
143. resp (Application Administrator) is not exist or disabled  
144. >>> 2 , working for user (KARL) <<<   
145. Adding resp (system Administrator) for user (KARL)  
146. Adding resp (Purchasing Super User) for user (KARL)  
147. resp (Application Administrator) is not exist or disabled  
148. >>> 3 , working for user (xx) <<<   
149. user (xx) is not exist or disabled  
150. */