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