[Home] [Help]
PACKAGE BODY: APPS.CZ_ADMIN
Source
1 PACKAGE BODY CZ_ADMIN as
2 /* $Header: czcadmnb.pls 115.18 2003/10/31 19:49:48 qmao ship $ */
3 PROCEDURE VALIDATE_END_USERS IS
4 CURSOR C_GET_USER IS
5 SELECT LOGIN_NAME FROM CZ_END_USERS;
6 sLogin_name CZ_END_USERS.LOGIN_NAME%TYPE;
7
8 BEGIN
9 OPEN C_GET_USER;
10
11 LOOP
12 FETCH C_GET_USER INTO sLogin_name;
13 EXIT WHEN C_GET_USER%NOTFOUND;
14 IF(sLogin_name IS NULL)THEN
15 -- xERROR:=CZ_UTILS.REPORT(CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_LOGIN_NAME'),1,'CZ_ADMIN.VALIDATE_END_USER',11276);
16 cz_utils.log_report('CZ_ADMIN', 'VALIDATE_END_USERS', null,
17 CZ_UTILS.GET_TEXT('CZ_IMP_INVALID_LOGIN_NAME'),
18 fnd_log.LEVEL_ERROR);
19 END IF;
20 END LOOP;
21 CLOSE C_GET_USER;
22
23 EXCEPTION
24 WHEN OTHERS THEN
25 CLOSE C_GET_USER;
26 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.VALIDATE_END_USER',11276);
27 cz_utils.log_report('CZ_ADMIN', 'VALIDATE_END_USERS', null,
28 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
29 END VALIDATE_END_USERS;
30 ------------------------------------------------------------------------------------------
31 PROCEDURE ENABLE_END_USERS IS
32 CURSOR C_CHECK_USER(sUser_name VARCHAR2) IS
33 SELECT USER_ID FROM ALL_USERS WHERE USERNAME=sUser_name;
34 CURSOR C_GET_USER IS
35 SELECT UPPER(LOGIN_NAME) FROM CZ_END_USERS;
36 CURSOR C_GET_ROLE IS
37 SELECT VALUE FROM CZ_DB_SETTINGS
38 WHERE SECTION_NAME='DB_USER_ROLES' AND SETTING_ID='0';
39 CURSOR C_GET_DEFAULT IS
40 SELECT VALUE FROM CZ_DB_SETTINGS
41 WHERE SECTION_NAME='SCHEMA' AND SETTING_ID='SpxDefaultTablespace';
42 CURSOR C_GET_TEMPORY IS
43 SELECT VALUE FROM CZ_DB_SETTINGS
44 WHERE SECTION_NAME='SCHEMA' AND SETTING_ID='SpxTemporaryTablespace';
45 DC_CURSOR INTEGER;
46 sLogin_name CZ_END_USERS.LOGIN_NAME%TYPE;
47 sDefault_role CZ_DB_SETTINGS.VALUE%TYPE := 'SPX_USER';
48 sDefault_tspc CZ_DB_SETTINGS.VALUE%TYPE := NULL;
49 sTempory_tspc CZ_DB_SETTINGS.VALUE%TYPE := NULL;
50 sCommand VARCHAR2(255);
51 nUserID NUMBER;
52 RESULT INTEGER;
53
54 BEGIN
55 OPEN C_GET_ROLE;
56 FETCH C_GET_ROLE INTO sDefault_role;
57 CLOSE C_GET_ROLE;
58 OPEN C_GET_DEFAULT;
59 FETCH C_GET_DEFAULT INTO sDefault_tspc;
60 CLOSE C_GET_DEFAULT;
61 OPEN C_GET_TEMPORY;
62 FETCH C_GET_TEMPORY INTO sTempory_tspc;
63 CLOSE C_GET_TEMPORY;
64
65 OPEN C_GET_USER;
66
67 LOOP
68 FETCH C_GET_USER INTO sLogin_name;
69 EXIT WHEN C_GET_USER%NOTFOUND;
70
71 IF(sLogin_name IS NULL)THEN
72 -- xERROR:=CZ_UTILS.REPORT('Invalid login name',1,'CZ_ADMIN.ENABLE_END_USER',11276);
73 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
74 'Invalid login name', fnd_log.LEVEL_ERROR);
75 ELSE
76 BEGIN
77
78 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
79 sCommand:='CREATE USER '||sLogin_name||' IDENTIFIED BY '||sLogin_name;
80 IF(sDefault_tspc IS NOT NULL)THEN
81 sCommand:=sCommand||' DEFAULT TABLESPACE '||sDefault_tspc;
82 END IF;
83 IF(sTempory_tspc IS NOT NULL)THEN
84 sCommand:=sCommand||' TEMPORARY TABLESPACE '||sTempory_tspc;
85 END IF;
86 DBMS_SQL.PARSE(DC_CURSOR,sCommand,DBMS_SQL.NATIVE);
87 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
88 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
93 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.ENABLE_END_USER',11276);
94 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
95 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
96 END;
97
98 BEGIN
99
100 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
101 DBMS_SQL.PARSE(DC_CURSOR,'GRANT "CONNECT" TO '||sLogin_name,DBMS_SQL.NATIVE);
102 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
103 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
108 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.ENABLE_END_USER',11276);
109 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
110 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
111 END;
112
113 BEGIN
114
115 DC_CURSOR:=DBMS_SQL.OPEN_CURSOR;
116 DBMS_SQL.PARSE(DC_CURSOR,'GRANT '||sDefault_role||' TO '||sLogin_name,DBMS_SQL.NATIVE);
117 RESULT:=DBMS_SQL.EXECUTE(DC_CURSOR);
118 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 DBMS_SQL.CLOSE_CURSOR(DC_CURSOR);
123 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.ENABLE_END_USER',11276);
124 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
125 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
126 END;
127
128 nUserID:=NULL;
129 OPEN C_CHECK_USER(sLogin_name);
130 FETCH C_CHECK_USER INTO nUserID;
131 CLOSE C_CHECK_USER;
132
133 BEGIN
134 UPDATE CZ_END_USERS SET DBMS_ID=nUserID WHERE UPPER(LOGIN_NAME)=sLogin_name;
135 EXCEPTION
136 WHEN OTHERS THEN
137 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.ENABLE_END_USER',11276);
138 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
139 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
140 END;
141 END IF;
142 END LOOP;
143 CLOSE C_GET_USER;
144 COMMIT;
145
146 EXCEPTION
147 WHEN OTHERS THEN
148 CLOSE C_GET_USER;
149 -- xERROR:=CZ_UTILS.REPORT(SQLERRM,1,'CZ_ADMIN.ENABLE_END_USER',11276);
150 cz_utils.log_report('CZ_ADMIN', 'ENABLE_END_USER', null,
151 SQLERRM, fnd_log.LEVEL_UNEXPECTED);
152 END ENABLE_END_USERS;
153 ------------------------------------------------------------------------------------------
154 PROCEDURE SPX_WAIT(nSeconds IN NUMBER DEFAULT 0) IS
155 BEGIN
156 IF(nSeconds IS NOT NULL AND nSeconds > 0)THEN
157 DBMS_LOCK.SLEEP(nSeconds);
158 END IF;
159 END;
160 ------------------------------------------------------------------------------------------
161 FUNCTION SPX_IMPORTSESSIONS RETURN INTEGER IS
162 nRet INTEGER;
163 BEGIN
164 BEGIN
165 SELECT 1 INTO nRet FROM V$SESSION WHERE MODULE='CZIMPORT';
166 EXCEPTION
167 WHEN NO_DATA_FOUND THEN
168 nRet:=0;
169 END;
170 RETURN nRet;
171 END;
172 ------------------------------------------------------------------------------------------
173 PROCEDURE SPX_SYNC_IMPORTSESSIONS IS
174 nSeconds NUMBER;
175 nCounter PLS_INTEGER;
176 BEGIN
177 BEGIN
178 SELECT NVL(to_number(VALUE),0) INTO nSeconds FROM CZ_DB_SETTINGS
179 WHERE SETTING_ID='MULTISESSION' AND SECTION_NAME='IMPORT';
180 EXCEPTION
181 WHEN OTHERS THEN
182 nSeconds:=0;
183 END;
184 IF(nSeconds < 0 OR SPX_IMPORTSESSIONS = 0)THEN RETURN; END IF;
185
186 nCounter:=0;
187 WHILE nCounter < nSeconds LOOP
188 SPX_WAIT(1);
189 IF(SPX_IMPORTSESSIONS = 0)THEN RETURN; END IF;
190 nCounter:=nCounter+1;
191 END LOOP;
192
193 RAISE IMP_ACTIVE_SESSION_EXISTS;
194 END;
195 ------------------------------------------------------------------------------------------
196 FUNCTION SPX_PUBLISHSESSIONS RETURN INTEGER IS
197 nRet INTEGER;
198 BEGIN
199 BEGIN
200 SELECT 1 INTO nRet FROM V$SESSION WHERE MODULE like 'CZ_SYNC%';
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 nRet:=0;
204 END;
205 RETURN nRet;
206 END;
207 ------------------------------------------------------------------------------------------
208 PROCEDURE SPX_SYNC_PUBLISHSESSIONS IS
209 nSeconds NUMBER;
210 nCounter PLS_INTEGER;
211 BEGIN
212 BEGIN
213 SELECT NVL(to_number(VALUE),0) INTO nSeconds FROM CZ_DB_SETTINGS
214 WHERE SETTING_ID='MULTISESSION' AND SECTION_NAME='CZPUBLISH';
215 EXCEPTION
216 WHEN OTHERS THEN
217 nSeconds:=0;
218 END;
219 IF(nSeconds < 0 OR SPX_PUBLISHSESSIONS = 0)THEN RETURN; END IF;
220
221 nCounter:=0;
222 WHILE nCounter < nSeconds LOOP
223 SPX_WAIT(1);
224 IF(SPX_PUBLISHSESSIONS = 0)THEN RETURN; END IF;
225 nCounter:=nCounter+1;
226 END LOOP;
227
228 RAISE IMP_ACTIVE_SESSION_EXISTS;
229 END;
230 ------------------------------------------------------------------------------------------
231
232 END CZ_ADMIN;