[Home] [Help]
PACKAGE BODY: APPS.WSH_RU_GRANTS_PVT
Source
1 PACKAGE BODY WSH_RU_GRANTS_PVT AS
2 /* $Header: WSHGRTHB.pls 115.3 2002/11/12 01:35:27 nparikh ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_RU_GRANTS_PVT';
6 --
7 PROCEDURE Insert_Row(
8 p_grant_record IN Grant_Type,
9 x_rowid OUT NOCOPY VARCHAR2,
10 x_grant_id OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2) IS
12
13 WSH_GRANT_OVERLAP EXCEPTION;
14
15 CURSOR c_grant_overlap IS
16 SELECT grant_id
17 FROM wsh_grants
18 WHERE user_id = p_grant_record.user_id
19 AND NVL(organization_id, NVL(p_grant_record.organization_id, -1))
20 = NVL(p_grant_record.organization_id, NVL(organization_id, -1))
21 AND (
22 ( p_grant_record.end_date IS NULL
23 AND p_grant_record.start_date < start_date)
24 OR ( end_date IS NULL
25 AND p_grant_record.end_date > start_date)
26 OR ( p_grant_record.end_date IS NULL
27 AND end_date IS NULL)
28 OR (p_grant_record.start_date BETWEEN start_date AND end_date)
29 OR (p_grant_record.end_date BETWEEN start_date AND end_date)
30 OR (start_date BETWEEN p_grant_record.start_date AND p_grant_record.end_date)
31 OR (end_date BETWEEN p_grant_record.start_date AND p_grant_record.end_date)
32 )
33 AND rownum = 1;
34
35 CURSOR c_new_grant_id IS
36 SELECT wsh_grants_s.nextval FROM DUAL;
37
38 CURSOR c_grant_rowid(x_grant_id IN NUMBER) IS
39 SELECT rowid FROM WSH_GRANTS WHERE grant_id = x_grant_id;
40
41 l_grant_id NUMBER(15) := NULL;
42 l_overlap_id NUMBER(15) := NULL;
43 l_rowid VARCHAR2(18) := '';
44
45 --
46 l_debug_on BOOLEAN;
47 --
48 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_ROW';
49 --
50 BEGIN
51
52 --
53 -- Debug Statements
54 --
55 --
56 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
57 --
58 IF l_debug_on IS NULL
59 THEN
60 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
61 END IF;
62 --
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.push(l_module_name);
65 END IF;
66 --
67 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
68
69 -- FGS does not care whether grants overlap. Simply take the
70 -- union of their organizations
71 -- OPEN c_grant_overlap;
72 -- FETCH c_grant_overlap INTO l_overlap_id;
73 -- IF c_grant_overlap%NOTFOUND THEN
74 -- l_overlap_id := NULL;
75 -- END IF;
76 -- CLOSE c_grant_overlap;
77 --
78 -- IF l_overlap_id IS NOT NULL THEN
79 -- raise WSH_GRANT_OVERLAP;
80 -- END IF;
81
82 l_grant_id := p_grant_record.grant_id;
83
84 IF l_grant_id IS NULL THEN
85 OPEN c_new_grant_id;
86 FETCH c_new_grant_id INTO l_grant_id;
87 CLOSE c_new_grant_id;
88 END IF;
89
90 INSERT INTO WSH_GRANTS (
91 GRANT_ID,
92 USER_ID,
93 ROLE_ID,
94 ORGANIZATION_ID,
95 START_DATE,
96 END_DATE,
97 CREATED_BY,
98 CREATION_DATE,
99 LAST_UPDATED_BY,
100 LAST_UPDATE_DATE,
101 LAST_UPDATE_LOGIN
102 ) VALUES (
103 l_grant_id,
104 p_grant_record.USER_ID,
105 p_grant_record.ROLE_ID,
106 p_grant_record.ORGANIZATION_ID,
107 p_grant_record.START_DATE,
108 p_grant_record.END_DATE,
109 p_grant_record.CREATED_BY,
110 p_grant_record.CREATION_DATE,
111 p_grant_record.LAST_UPDATED_BY,
112 p_grant_record.LAST_UPDATE_DATE,
113 p_grant_record.LAST_UPDATE_LOGIN
114 );
115
116 OPEN c_grant_rowid(l_grant_id);
117 FETCH c_grant_rowid INTO l_rowid;
118 IF c_grant_rowid%NOTFOUND THEN
119 CLOSE c_grant_rowid;
120 RAISE NO_DATA_FOUND;
121 END IF;
122 CLOSE c_grant_rowid;
123
124 x_rowid := l_rowid;
125 x_grant_id := l_grant_id;
126
127 --
128 -- Debug Statements
129 --
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.pop(l_module_name);
132 END IF;
133 --
134 EXCEPTION
135 WHEN wsh_grant_overlap THEN
136 FND_MESSAGE.Set_Name('WSH', 'WSH_RU_GRANTS_OVERLAP');
137 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
138 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
139
140 --
141 -- Debug Statements
142 --
143 IF l_debug_on THEN
144 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_GRANT_OVERLAP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
145 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_GRANT_OVERLAP');
146 END IF;
147 --
148 WHEN others THEN
149 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
150 wsh_util_core.default_handler('WSH_RU_GRANTS_PVT.INSERT_ROW');
151
152 --
153 -- Debug Statements
154 --
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
157 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
158 END IF;
159 --
160 END Insert_Row;
161
162
163 PROCEDURE Lock_Row(
164 p_rowid IN VARCHAR2,
165 p_grant_record IN Grant_Type) IS
166
167 CURSOR c_lock_grant IS
168 SELECT * FROM WSH_GRANTS
169 WHERE rowid = p_rowid
170 FOR UPDATE OF GRANT_ID NOWAIT;
171
172 l_db_rec c_lock_grant%ROWTYPE;
173
174 --
175 l_debug_on BOOLEAN;
176 --
177 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROW';
178 --
179 BEGIN
180
181 --
182 -- Debug Statements
183 --
184 --
185 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
186 --
187 IF l_debug_on IS NULL
188 THEN
189 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
190 END IF;
191 --
192 IF l_debug_on THEN
193 WSH_DEBUG_SV.push(l_module_name);
194 --
195 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
196 END IF;
197 --
198 OPEN c_lock_grant;
199 FETCH c_lock_grant INTO l_db_rec;
200 IF c_lock_grant%NOTFOUND THEN
201 CLOSE c_lock_grant;
202 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
203 app_exception.raise_exception;
204 END IF;
205 CLOSE c_lock_grant;
206
207 IF (l_db_rec.USER_ID = p_grant_record.USER_ID)
208 AND (l_db_rec.ROLE_ID = p_grant_record.ROLE_ID)
209 AND ((l_db_rec.ORGANIZATION_ID = p_grant_record.ORGANIZATION_ID)
210 OR ( l_db_rec.ORGANIZATION_ID IS NULL
211 AND p_grant_record.ORGANIZATION_ID IS NULL))
212 AND (l_db_rec.START_DATE = p_grant_record.START_DATE)
213 AND ((l_db_rec.END_DATE = p_grant_record.END_DATE)
214 OR ( l_db_rec.END_DATE IS NULL
215 AND p_grant_record.END_DATE IS NULL))
216 AND (l_db_rec.CREATED_BY = p_grant_record.CREATED_BY)
217 AND (l_db_rec.CREATION_DATE = p_grant_record.CREATION_DATE)
218 AND (l_db_rec.LAST_UPDATED_BY = p_grant_record.LAST_UPDATED_BY)
219 AND (l_db_rec.LAST_UPDATE_DATE = p_grant_record.LAST_UPDATE_DATE)
220 AND ((l_db_rec.LAST_UPDATE_LOGIN = p_grant_record.LAST_UPDATE_LOGIN)
221 OR ( l_db_rec.LAST_UPDATE_LOGIN IS NULL
222 AND p_grant_record.LAST_UPDATE_LOGIN IS NULL))
223 THEN
224 --
225 -- Debug Statements
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.pop(l_module_name);
229 END IF;
230 --
231 RETURN;
232 ELSE
233 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
234 app_exception.raise_exception;
235 END IF;
236
237 --
238 -- Debug Statements
239 --
240 IF l_debug_on THEN
241 WSH_DEBUG_SV.pop(l_module_name);
242 END IF;
243 --
244 EXCEPTION
245 WHEN app_exception.application_exception
246 or app_exception.record_lock_exception THEN
247
248 if (c_lock_grant%ISOPEN) then
249 close c_lock_grant;
250 end if;
251
252 --
253 -- Debug Statements
254 --
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
257 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
258 END IF;
259 --
260 RAISE;
261
262 WHEN others THEN
263
264 if (c_lock_grant%ISOPEN) then
265 close c_lock_grant;
266 end if;
267
268 FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
269 FND_MESSAGE.Set_Token('PACKAGE', 'WSH_RU_GRANTS_PVT.LOCK_ROW');
270 FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
271 FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
272
273 --
274 -- Debug Statements
275 --
276 IF l_debug_on THEN
277 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
278 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
279 END IF;
280 --
281 RAISE;
282
283 --
284 -- Debug Statements
285 --
286 IF l_debug_on THEN
287 WSH_DEBUG_SV.pop(l_module_name);
288 END IF;
289 --
290 END Lock_Row;
291
292
293 PROCEDURE Update_Row(
294 p_rowid IN VARCHAR2,
295 p_grant_record IN Grant_Type,
296 x_return_status OUT NOCOPY VARCHAR2) IS
297
298 WSH_GRANT_OVERLAP EXCEPTION;
299
300 CURSOR c_grant_overlap IS
301 SELECT grant_id
302 FROM wsh_grants
303 WHERE user_id = p_grant_record.user_id
304 AND NVL(organization_id, NVL(p_grant_record.organization_id, -1))
305 = NVL(p_grant_record.organization_id, NVL(organization_id, -1))
306 AND (
307 ( p_grant_record.end_date IS NULL
308 AND p_grant_record.start_date < start_date)
309 OR ( end_date IS NULL
310 AND p_grant_record.end_date > start_date)
311 OR ( p_grant_record.end_date IS NULL
312 AND end_date IS NULL)
313 OR (p_grant_record.start_date BETWEEN start_date AND end_date)
314 OR (p_grant_record.end_date BETWEEN start_date AND end_date)
315 OR (start_date BETWEEN p_grant_record.start_date AND p_grant_record.end_date)
316 OR (end_date BETWEEN p_grant_record.start_date AND p_grant_record.end_date)
317 )
318 AND grant_id <> p_grant_record.grant_id
319 AND rownum = 1;
320
321 l_overlap_id NUMBER(15);
322
323 --
324 l_debug_on BOOLEAN;
325 --
326 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROW';
327 --
328 BEGIN
329
330 -- FGS does not care whether grants overlap. Simply take the
331 -- union of their organizations
332 -- OPEN c_grant_overlap;
333 -- FETCH c_grant_overlap INTO l_overlap_id;
334 -- IF c_grant_overlap%NOTFOUND THEN
335 -- l_overlap_id := NULL;
336 -- END IF;
337 -- CLOSE c_grant_overlap;
338 --
339 -- IF l_overlap_id IS NOT NULL THEN
340 -- raise WSH_GRANT_OVERLAP;
341 -- END IF;
342
343 --
344 -- Debug Statements
345 --
346 --
347 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
348 --
349 IF l_debug_on IS NULL
350 THEN
351 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
352 END IF;
353 --
354 IF l_debug_on THEN
355 WSH_DEBUG_SV.push(l_module_name);
356 --
357 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
358 END IF;
359 --
360 UPDATE WSH_GRANTS
361 SET
362 USER_ID = p_grant_record.USER_ID,
363 ROLE_ID = p_grant_record.ROLE_ID,
364 ORGANIZATION_ID = p_grant_record.ORGANIZATION_ID,
365 START_DATE = p_grant_record.START_DATE,
366 END_DATE = p_grant_record.END_DATE,
367 CREATED_BY = p_grant_record.CREATED_BY,
368 CREATION_DATE = p_grant_record.CREATION_DATE,
369 LAST_UPDATED_BY = p_grant_record.LAST_UPDATED_BY,
370 LAST_UPDATE_DATE = p_grant_record.LAST_UPDATE_DATE,
371 LAST_UPDATE_LOGIN = p_grant_record.LAST_UPDATE_LOGIN
372 WHERE GRANT_ID = p_grant_record.GRANT_ID;
373
374 IF (SQL%NOTFOUND) THEN
375 RAISE NO_DATA_FOUND;
376 ELSE
377 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
378 END IF;
379
380 --
381 -- Debug Statements
382 --
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.pop(l_module_name);
385 END IF;
386 --
387 EXCEPTION
388 WHEN wsh_grant_overlap THEN
389 FND_MESSAGE.Set_Name('WSH', 'WSH_RU_GRANTS_OVERLAP_UPDATE');
390 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
391 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
392
393 --
394 -- Debug Statements
395 --
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_GRANT_OVERLAP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
398 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_GRANT_OVERLAP');
399 END IF;
400 --
401 WHEN others THEN
402 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
403 wsh_util_core.default_handler('WSH_RU_GRANTS_PVT.UPDATE_ROW');
404
405 --
406 -- Debug Statements
407 --
408 IF l_debug_on THEN
409 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
410 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
411 END IF;
412 --
413 END Update_Row;
414
415
416 PROCEDURE Delete_Row(
417 p_rowid IN VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2) IS
419 --
420 l_debug_on BOOLEAN;
421 --
422 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROW';
423 --
424 BEGIN
425
426 --
427 -- Debug Statements
428 --
429 --
430 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
431 --
432 IF l_debug_on IS NULL
433 THEN
434 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
435 END IF;
436 --
437 IF l_debug_on THEN
438 WSH_DEBUG_SV.push(l_module_name);
439 --
440 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
441 END IF;
442 --
443 DELETE FROM WSH_GRANTS
444 WHERE rowid = p_rowid;
445 IF SQL%NOTFOUND THEN
446 RAISE NO_DATA_FOUND;
447 END IF;
448 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
449
450 --
451 -- Debug Statements
452 --
453 IF l_debug_on THEN
454 WSH_DEBUG_SV.pop(l_module_name);
455 END IF;
456 --
457 EXCEPTION
458 WHEN others THEN
459 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
460 wsh_util_core.default_handler('WSH_RU_GRANTS_PVT.DELETE_ROW');
461
462 --
463 -- Debug Statements
464 --
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
467 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
468 END IF;
469 --
470 END Delete_Row;
471
472
473 END WSH_RU_GRANTS_PVT;