[Home] [Help]
PACKAGE BODY: APPS.WSH_RU_ROLE_PRIVILEGES_PVT
Source
1 PACKAGE BODY WSH_RU_ROLE_PRIVILEGES_PVT AS
2 /* $Header: WSHRPTHB.pls 120.0 2005/05/29 14:11:46 appldev noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_RU_ROLE_PRIVILEGES_PVT';
6 --
7 PROCEDURE Insert_Row(
8 p_role_privilege_record IN Role_Privilege_Type,
9 x_rowid OUT NOCOPY VARCHAR2,
10 x_role_privilege_id OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2) IS
12
13 CURSOR c_new_role_privilege_id IS
14 SELECT wsh_role_privileges_s.nextval FROM DUAL;
15
16 CURSOR c_role_privilege_rowid(x_role_privilege_id IN NUMBER) IS
17 SELECT rowid FROM WSH_ROLE_PRIVILEGES WHERE role_privilege_id = x_role_privilege_id;
18
19 l_role_privilege_id NUMBER(15) := NULL;
20 l_rowid VARCHAR2(18) := '';
21
22 --
23 l_debug_on BOOLEAN;
24 --
25 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
26 --
27 BEGIN
28 --
29 --
30 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
31 --
32 IF l_debug_on IS NULL
33 THEN
34 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
35 END IF;
36 --
37 IF l_debug_on THEN
38 WSH_DEBUG_SV.push(l_module_name);
39 WSH_DEBUG_SV.log(l_module_name,'role_privilege_id',
40 p_role_privilege_record.role_privilege_id);
41 END IF;
42 --
43 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
44
45 l_role_privilege_id := p_role_privilege_record.role_privilege_id;
46
47 IF l_role_privilege_id IS NULL THEN
48 OPEN c_new_role_privilege_id;
49 FETCH c_new_role_privilege_id INTO l_role_privilege_id;
50 CLOSE c_new_role_privilege_id;
51 IF l_debug_on THEN
52 WSH_DEBUG_SV.log(l_module_name,'l_role_privilege_id',l_role_privilege_id)
53 ;
54 END IF;
55 END IF;
56
57 INSERT INTO WSH_ROLE_PRIVILEGES (
58 ROLE_PRIVILEGE_ID,
59 ROLE_ID,
60 PRIVILEGE_CODE,
61 CREATED_BY,
62 CREATION_DATE,
63 LAST_UPDATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATE_LOGIN
66 ) VALUES (
67 l_role_privilege_id,
68 p_role_privilege_record.ROLE_ID,
69 p_role_privilege_record.PRIVILEGE_CODE,
70 p_role_privilege_record.CREATED_BY,
71 p_role_privilege_record.CREATION_DATE,
72 p_role_privilege_record.LAST_UPDATED_BY,
73 p_role_privilege_record.LAST_UPDATE_DATE,
74 p_role_privilege_record.LAST_UPDATE_LOGIN
75 );
76
77 OPEN c_role_privilege_rowid(l_role_privilege_id);
78 FETCH c_role_privilege_rowid INTO l_rowid;
79 IF c_role_privilege_rowid%NOTFOUND THEN
80 CLOSE c_role_privilege_rowid;
81 RAISE NO_DATA_FOUND;
82 END IF;
83 CLOSE c_role_privilege_rowid;
84
85 x_rowid := l_rowid;
86 x_role_privilege_id := l_role_privilege_id;
87 --
88 IF l_debug_on THEN
89 WSH_DEBUG_SV.pop(l_module_name);
90 END IF;
91 --
92 EXCEPTION
93 WHEN others THEN
94 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
95 wsh_util_core.default_handler('WSH_RU_ROLE_PRIVILEGES_PVT.INSERT_ROW',l_module_name);
96 --
97 IF l_debug_on THEN
98 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
99 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
100 END IF;
101 --
102 END Insert_Row;
103
104
105 PROCEDURE Lock_Row(
106 p_rowid IN VARCHAR2,
107 p_role_privilege_record IN Role_Privilege_Type) IS
108
109 CURSOR c_lock_role_privilege IS
110 SELECT * FROM WSH_ROLE_PRIVILEGES
111 WHERE rowid = p_rowid
112 FOR UPDATE OF ROLE_PRIVILEGE_ID NOWAIT;
113
114 l_db_rec c_lock_role_privilege%ROWTYPE;
115
116 --
117 l_debug_on BOOLEAN;
118 --
119 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
120 --
121 BEGIN
122 --
123 --
124 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
125 --
126 IF l_debug_on IS NULL
127 THEN
128 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
129 END IF;
130 --
131 IF l_debug_on THEN
132 WSH_DEBUG_SV.push(l_module_name);
133 --
134 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
135 END IF;
136 --
137 OPEN c_lock_role_privilege;
138 FETCH c_lock_role_privilege INTO l_db_rec;
139 IF c_lock_role_privilege%NOTFOUND THEN
140 CLOSE c_lock_role_privilege;
141 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
142 IF l_debug_on THEN
143 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
144 END IF;
145 app_exception.raise_exception;
146 END IF;
147 CLOSE c_lock_role_privilege;
148
149 IF (l_db_rec.ROLE_ID = p_role_privilege_record.ROLE_ID)
150 AND (l_db_rec.PRIVILEGE_CODE = p_role_privilege_record.PRIVILEGE_CODE)
151 AND (l_db_rec.CREATED_BY = p_role_privilege_record.CREATED_BY)
152 AND (l_db_rec.CREATION_DATE = p_role_privilege_record.CREATION_DATE)
153 AND (l_db_rec.LAST_UPDATED_BY = p_role_privilege_record.LAST_UPDATED_BY)
154 AND (l_db_rec.LAST_UPDATE_DATE = p_role_privilege_record.LAST_UPDATE_DATE)
155 AND ((l_db_rec.LAST_UPDATE_LOGIN = p_role_privilege_record.LAST_UPDATE_LOGIN)
156 OR ( l_db_rec.LAST_UPDATE_LOGIN IS NULL
157 AND p_role_privilege_record.LAST_UPDATE_LOGIN IS NULL))
158 THEN
159 --
160 IF l_debug_on THEN
161 WSH_DEBUG_SV.pop(l_module_name,'Noting Changed');
162 END IF;
163 --
164 RETURN;
165 ELSE
166 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
167 IF l_debug_on THEN
168 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
169 END IF;
170 app_exception.raise_exception;
171 END IF;
172 --
173 IF l_debug_on THEN
174 WSH_DEBUG_SV.pop(l_module_name);
175 END IF;
176 --
177 EXCEPTION
178 WHEN app_exception.application_exception
179 or app_exception.record_lock_exception THEN
180
181 if (c_lock_role_privilege%ISOPEN) then
182 close c_lock_role_privilege;
183 end if;
184 --
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
187 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
188 END IF;
189 --
190 RAISE;
191
192 WHEN others THEN
193
194 if (c_lock_role_privilege%ISOPEN) then
195 close c_lock_role_privilege;
196 end if;
197
198 FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
199 FND_MESSAGE.Set_Token('PACKAGE', 'WSH_RU_ROLE_PRIVILEGES_PVT.LOCK_ROW');
200 FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
201 FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
202 --
203 IF l_debug_on THEN
204 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
205 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
206 END IF;
207 --
208 RAISE;
209 END Lock_Row;
210
211
212 PROCEDURE Update_Row(
213 p_rowid IN VARCHAR2,
214 p_role_privilege_record IN Role_Privilege_Type,
215 x_return_status OUT NOCOPY VARCHAR2) IS
216 --
217 l_debug_on BOOLEAN;
218 --
219 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
220 --
221 BEGIN
222 --
223 --
224 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
225 --
226 IF l_debug_on IS NULL
227 THEN
228 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
229 END IF;
230 --
231 IF l_debug_on THEN
232 WSH_DEBUG_SV.push(l_module_name);
233 --
234 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
235 END IF;
236 --
237 UPDATE WSH_ROLE_PRIVILEGES
238 SET
239 ROLE_ID = p_role_privilege_record.ROLE_ID,
240 PRIVILEGE_CODE = p_role_privilege_record.PRIVILEGE_CODE,
241 CREATED_BY = p_role_privilege_record.CREATED_BY,
242 CREATION_DATE = p_role_privilege_record.CREATION_DATE,
243 LAST_UPDATED_BY = p_role_privilege_record.LAST_UPDATED_BY,
244 LAST_UPDATE_DATE = p_role_privilege_record.LAST_UPDATE_DATE,
245 LAST_UPDATE_LOGIN = p_role_privilege_record.LAST_UPDATE_LOGIN
246 WHERE rowid = p_rowid;
247
248 IF l_debug_on THEN
249 WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
250 END IF;
251 IF (SQL%NOTFOUND) THEN
252 RAISE NO_DATA_FOUND;
253 ELSE
254 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
255 END IF;
256 --
257 IF l_debug_on THEN
258 WSH_DEBUG_SV.pop(l_module_name);
259 END IF;
260 --
261 EXCEPTION
262 WHEN others THEN
263 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
264 wsh_util_core.default_handler('WSH_RU_ROLE_PRIVILEGES_PVT.UPDATE_ROW',l_module_name);
265 --
266 IF l_debug_on THEN
267 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
268 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
269 END IF;
270 --
271 END Update_Row;
272
273
274 PROCEDURE Delete_Row(
275 p_rowid IN VARCHAR2,
276 x_return_status OUT NOCOPY VARCHAR2) IS
277 --
278 l_debug_on BOOLEAN;
279 --
280 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
281 --
282 BEGIN
283 --
284 --
285 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
286 --
287 IF l_debug_on IS NULL
288 THEN
289 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
290 END IF;
291 --
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.push(l_module_name);
294 --
295 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
296 END IF;
297 --
298 DELETE FROM WSH_ROLE_PRIVILEGES
299 WHERE rowid = p_rowid;
300 IF SQL%NOTFOUND THEN
301 RAISE NO_DATA_FOUND;
302 END IF;
303 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
304 --
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.log(l_module_name,'Rows Deleted',SQL%ROWCOUNT);
307 WSH_DEBUG_SV.pop(l_module_name);
308 END IF;
309 --
310 EXCEPTION
311 WHEN others THEN
312 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
313 wsh_util_core.default_handler('WSH_RU_ROLE_PRIVILEGES_PVT.DELETE_ROW',l_module_name);
314 --
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
317 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
318 END IF;
319 --
320 END Delete_Row;
321
322
323 -- This will delete all the data access privileges attached
324 -- to that role from wsh_role_privileges.
325 -- Following are the data access privileges.
326 -- TRIP_VIEW, TRIP_EDIT, STOP_VIEW, STOP_EDIT, DLVY_VIEW,
327 -- DLVY_EDIT, DLVB_VIEW, DLVB_EDIT', GENR_VIEW, GENR_EDIT
328
329 PROCEDURE Delete_Role_Privileges(p_role_id in NUMBER,
330 x_return_status OUT NOCOPY VARCHAR2)
331
332
333 IS
334
335 l_debug_on BOOLEAN;
336 --
337 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROLE';
338
339
340 BEGIN
341 --
342 --
343 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
344 --
345 IF l_debug_on IS NULL
346 THEN
347 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
348 END IF;
349 --
350 IF l_debug_on THEN
351 WSH_DEBUG_SV.push(l_module_name);
352 --
353 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_ID',P_ROLE_ID);
354 END IF;
355 --
356 DELETE FROM WSH_ROLE_PRIVILEGES
357 WHERE role_id = p_role_id
358 AND privilege_code
359 IN('TRIP_VIEW','TRIP_EDIT',
360 'STOP_VIEW','STOP_EDIT',
361 'DLVY_VIEW','DLVY_EDIT',
362 'DLVB_VIEW','DLVB_EDIT',
363 'GENR_VIEW','GENR_EDIT'); -- Added AND condition for R12
364 IF SQL%NOTFOUND THEN
365 RAISE NO_DATA_FOUND;
366 END IF;
367 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
368 --
369 IF l_debug_on THEN
370 WSH_DEBUG_SV.log(l_module_name,'Rows Deleted',SQL%ROWCOUNT);
371 WSH_DEBUG_SV.pop(l_module_name);
372 END IF;
373 --
374 EXCEPTION
375 WHEN others THEN
376 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
377 wsh_util_core.default_handler('WSH_RU_ROLE_PRIVILEGES_PVT.DELETE_ROLR',l_module_name);
378 --
379 IF l_debug_on THEN
380 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM, WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
382 END IF;
383 --
384 END Delete_Role_Privileges;
385
386
387
388 END WSH_RU_ROLE_PRIVILEGES_PVT;