[Home] [Help]
PACKAGE BODY: APPS.WSH_RU_ACTIONS
Source
1 PACKAGE BODY WSH_RU_ACTIONS AS
2 /* $Header: WSHRUACB.pls 120.1 2005/06/09 17:04:43 appldev $ */
3
4
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_RU_ACTIONS';
7 g_custom_message_tbl custom_message_cache_tbl;
8 --
9 PROCEDURE Create_Role_Definition(
10 p_role_def_record IN Role_Definition_Type,
11 x_rowid OUT NOCOPY VARCHAR2,
12 x_role_id OUT NOCOPY NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2) IS
14 l_rs VARCHAR2(1);
15 l_role WSH_RU_ROLES_PVT.Role_Type;
16 l_privilege WSH_RU_ROLE_PRIVILEGES_PVT.Role_Privilege_Type;
17 l_role_id NUMBER(15);
18 i NUMBER;
19 l_dummy_rowid VARCHAR2(18);
20 l_dummy_id NUMBER(15);
21 --
22 l_debug_on BOOLEAN;
23 --
24 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_ROLE_DEFINITION';
25 --
26 BEGIN
27
28 --
29 -- Debug Statements
30 --
31 --
32 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
33 --
34 IF l_debug_on IS NULL
35 THEN
36 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
37 END IF;
38 --
39 IF l_debug_on THEN
40 WSH_DEBUG_SV.push(l_module_name);
41 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.ROLE_ID',p_role_def_record.ROLE_ID);
42 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.NAME',p_role_def_record.NAME);
43 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.DESCRIPTION',p_role_def_record.DESCRIPTION);
44 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.CREATED_BY',p_role_def_record.CREATED_BY);
45 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.CREATION_DATE',p_role_def_record.CREATION_DATE);
46 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.LAST_UPDATED_BY',p_role_def_record.LAST_UPDATED_BY);
47 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.LAST_UPDATE_DATE',p_role_def_record.LAST_UPDATE_DATE);
48 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.LAST_UPDATE_LOGIN',p_role_def_record.LAST_UPDATE_LOGIN);
49 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.PRIVILEGES_COUNT',p_role_def_record.PRIVILEGES.COUNT);
50 END IF;
51 --
52 savepoint before_role_definition;
53
54 l_role.ROLE_ID := NULL;
55 l_role.NAME := p_role_def_record.NAME;
56 l_role.DESCRIPTION := p_role_def_record.DESCRIPTION;
57 l_role.CREATED_BY := p_role_def_record.CREATED_BY;
58 l_role.CREATION_DATE := p_role_def_record.CREATION_DATE;
59 l_role.LAST_UPDATED_BY := p_role_def_record.LAST_UPDATED_BY;
60 l_role.LAST_UPDATE_DATE := p_role_def_record.LAST_UPDATE_DATE;
61 l_role.LAST_UPDATE_LOGIN := p_role_def_record.LAST_UPDATE_LOGIN;
62
63 --
64 -- Debug Statements
65 --
66 IF l_debug_on THEN
67 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RU_ROLES_PVT.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
68 END IF;
69 --
70 WSH_RU_ROLES_PVT.Insert_Row(
71 p_role_record => l_role,
72 x_rowid => x_rowid,
73 x_role_id => l_role_id,
74 x_return_status => l_rs);
75
76 IF l_rs <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
77 x_return_status := l_rs;
78 rollback to before_role_definition;
79 --
80 -- Debug Statements
81 --
82 IF l_debug_on THEN
83 WSH_DEBUG_SV.pop(l_module_name);
84 END IF;
85 --
86 return;
87 END IF;
88
89 x_role_id := l_role_id;
90 -- Debug Statements
91 --
92 IF l_debug_on THEN
93 WSH_DEBUG_SV.log(l_module_name,'X_ROLE_ID',x_role_id);
94 END IF;
95 --
96
97 l_privilege.ROLE_PRIVILEGE_ID := NULL;
98 l_privilege.ROLE_ID := l_role_id;
99 l_privilege.PRIVILEGE_CODE := NULL; -- this will be updated
100 l_privilege.CREATED_BY := p_role_def_record.CREATED_BY;
101 l_privilege.CREATION_DATE := p_role_def_record.CREATION_DATE;
102 l_privilege.LAST_UPDATED_BY := p_role_def_record.LAST_UPDATED_BY;
103 l_privilege.LAST_UPDATE_DATE := p_role_def_record.LAST_UPDATE_DATE;
104 l_privilege.LAST_UPDATE_LOGIN := p_role_def_record.LAST_UPDATE_LOGIN;
105
106 FOR i IN 1..p_role_def_record.privileges.count LOOP
107
108 l_privilege.PRIVILEGE_CODE := p_role_def_record.privileges(i);
109
110 --
111 -- Debug Statements
112 --
113 IF l_debug_on THEN
114 WSH_DEBUG_SV.log(l_module_name,'PRIVILEGE_CODE',l_privilege.PRIVILEGE_CODE);
115 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RU_ROLE_PRIVILEGES_PVT.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
116 END IF;
117 --
118 WSH_RU_ROLE_PRIVILEGES_PVT.Insert_Row(
119 p_role_privilege_record => l_privilege,
120 x_rowid => l_dummy_rowid,
121 x_role_privilege_id => l_dummy_id,
122 x_return_status => l_rs);
123
124 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
125 x_return_status := l_rs;
126 rollback to before_role_definition;
127 --
128 -- Debug Statements
129 --
130 IF l_debug_on THEN
131 WSH_DEBUG_SV.pop(l_module_name);
132 END IF;
133 --
134 return;
135 END IF;
136
137 END LOOP;
138
139 x_return_status := l_rs;
140
141 --
142 -- Debug Statements
143 --
144 IF l_debug_on THEN
145 WSH_DEBUG_SV.pop(l_module_name);
146 END IF;
147 --
148 END Create_Role_Definition;
149
150
151 PROCEDURE Lock_Role_Definition(
152 p_role_def_record IN Role_Definition_Type,
153 p_row_id IN VARCHAR2) IS
154 --
155 l_role WSH_RU_ROLES_PVT.Role_Type;
156 l_debug_on BOOLEAN;
157 --
158 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ROLE_DEFINITION';
159 --
160 BEGIN
161 --
162 -- Debug Statements
163 --
164 --
165 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
166 --
167 IF l_debug_on IS NULL
168 THEN
169 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
170 END IF;
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.push(l_module_name);
174 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.ROLE_ID',p_role_def_record.ROLE_ID);
175 END IF;
176
177 l_role.ROLE_ID := p_role_def_record.ROLE_ID;
178 l_role.NAME := p_role_def_record.NAME;
179 l_role.DESCRIPTION := p_role_def_record.DESCRIPTION;
180 l_role.CREATED_BY := p_role_def_record.CREATED_BY;
181 l_role.CREATION_DATE := p_role_def_record.CREATION_DATE;
182 l_role.LAST_UPDATED_BY := p_role_def_record.LAST_UPDATED_BY;
183 l_role.LAST_UPDATE_DATE := p_role_def_record.LAST_UPDATE_DATE;
184 l_role.LAST_UPDATE_LOGIN := p_role_def_record.LAST_UPDATE_LOGIN;
185
186 --
187 wsh_ru_roles_pvt.lock_row(
188 p_rowid => p_row_id,
189 p_role_record => l_role
190 );
191
192 --
193 -- Debug Statements
194 --
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.pop(l_module_name);
197 END IF;
198 --
199 END Lock_Role_Definition;
200
201
202 PROCEDURE Update_Role_Definition(
203 p_role_def_record IN OUT NOCOPY Role_Definition_Type,
204 x_return_status OUT NOCOPY VARCHAR2) IS
205 --
206 l_rs VARCHAR2(1);
207 l_role WSH_RU_ROLES_PVT.Role_Type;
208 l_privilege WSH_RU_ROLE_PRIVILEGES_PVT.Role_Privilege_Type;
209 l_role_id NUMBER(15);
210 i NUMBER;
211 l_dummy_rowid VARCHAR2(18);
212 l_dummy_id NUMBER(15);
213 l_debug_on BOOLEAN;
214 --
215 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ROLE_DEFINITION';
216 --
217 BEGIN
218
219 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
220 --
221 -- Debug Statements
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 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.ROLE_ID',p_role_def_record.ROLE_ID);
234 END IF;
235
236 savepoint before_role_definition;
237
238 l_role.ROLE_ID := p_role_def_record.ROLE_ID;
239 l_role.NAME := p_role_def_record.NAME;
240 l_role.DESCRIPTION := p_role_def_record.DESCRIPTION;
241 l_role.CREATED_BY := p_role_def_record.CREATED_BY;
242 l_role.CREATION_DATE := p_role_def_record.CREATION_DATE;
243 l_role.LAST_UPDATED_BY := p_role_def_record.LAST_UPDATED_BY;
244 l_role.LAST_UPDATE_DATE := SYSDATE;
245 l_role.LAST_UPDATE_LOGIN := p_role_def_record.LAST_UPDATE_LOGIN;
246
247 --
248 -- Debug Statements
249 --
250 IF l_debug_on THEN
251 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RU_ROLES_PVT.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
252 END IF;
253 --
254 WSH_RU_ROLES_PVT.Update_Row(
255 p_role_record => l_role,
256 p_rowid => NULL,
257 x_return_status => l_rs);
258
259 IF l_rs <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
260 x_return_status := l_rs;
261 rollback to before_role_definition;
262 --
263 -- Debug Statements
264 --
265 IF l_debug_on THEN
266 WSH_DEBUG_SV.pop(l_module_name);
267 END IF;
268 --
269 return;
270 END IF;
271
272 WSH_RU_ROLE_PRIVILEGES_PVT.Delete_Role_Privileges(
273 p_role_id => p_role_def_record.ROLE_ID,
274 x_return_status => l_rs);
275
276 IF l_rs <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
277 x_return_status := l_rs;
278 rollback to before_role_definition;
279 --
280 -- Debug Statements
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.pop(l_module_name);
284 END IF;
285 --
286 return;
287 END IF;
288
289 l_privilege.ROLE_PRIVILEGE_ID := NULL;
290 l_privilege.ROLE_ID := p_role_def_record.ROLE_ID;
291 l_privilege.PRIVILEGE_CODE := NULL; -- this will be updated
292 l_privilege.CREATED_BY := p_role_def_record.CREATED_BY;
293 l_privilege.CREATION_DATE := p_role_def_record.CREATION_DATE;
294 l_privilege.LAST_UPDATED_BY := p_role_def_record.LAST_UPDATED_BY;
295 l_privilege.LAST_UPDATE_DATE := p_role_def_record.LAST_UPDATE_DATE;
296 l_privilege.LAST_UPDATE_LOGIN := p_role_def_record.LAST_UPDATE_LOGIN;
297
298 FOR i IN 1..p_role_def_record.privileges.count LOOP
299
300 l_privilege.PRIVILEGE_CODE := p_role_def_record.privileges(i);
301
302 --
303 -- Debug Statements
304 --
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.log(l_module_name,'PRIVILEGE_CODE',l_privilege.PRIVILEGE_CODE);
307 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RU_ROLE_PRIVILEGES_PVT.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
308 END IF;
309 --
310 WSH_RU_ROLE_PRIVILEGES_PVT.Insert_Row(
311 p_role_privilege_record => l_privilege,
312 x_rowid => l_dummy_rowid,
313 x_role_privilege_id => l_dummy_id,
314 x_return_status => l_rs);
315
316 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
317 x_return_status := l_rs;
318 rollback to before_role_definition;
319 --
320 -- Debug Statements
321 --
322 IF l_debug_on THEN
323 WSH_DEBUG_SV.pop(l_module_name);
324 END IF;
325 --
326 return;
327 END IF;
328
329 END LOOP;
330 --
331 -- Debug Statements
332 --
333 p_role_def_record.LAST_UPDATE_DATE := l_role.LAST_UPDATE_DATE;
334
335 IF l_debug_on THEN
336 WSH_DEBUG_SV.pop(l_module_name);
337 END IF;
338 --
339 END Update_Role_Definition;
340
341
342 PROCEDURE Delete_Role_Definition(
343 p_role_def_record IN Role_Definition_Type,
344 x_return_status OUT NOCOPY VARCHAR2) IS
345 --
346 l_debug_on BOOLEAN;
347 --
348 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_ROLE_DEFINITION';
349 --
350 BEGIN
351 --
352 -- Debug Statements
353 --
354 --
355 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
356 --
357 IF l_debug_on IS NULL
358 THEN
359 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
360 END IF;
361 --
362 IF l_debug_on THEN
363 WSH_DEBUG_SV.push(l_module_name);
364 WSH_DEBUG_SV.log(l_module_name,'P_ROLE_DEF_RECORD.ROLE_ID',p_role_def_record.ROLE_ID);
365 END IF;
366 --
367 FND_MESSAGE.SET_NAME('WSH', 'NOT_IMPLEMENTED');
368 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
369 --
370 -- Debug Statements
371 --
372 IF l_debug_on THEN
373 WSH_DEBUG_SV.pop(l_module_name);
374 END IF;
375 --
376 END Delete_Role_Definition;
377
378
379 PROCEDURE Get_Organization_Privileges(
380 p_organization_id IN NUMBER,
381 x_privileges OUT NOCOPY Privileges_Type,
382 x_return_status OUT NOCOPY VARCHAR2) IS
383
384 CURSOR c_privileges(x_org_id IN NUMBER, x_user_id IN NUMBER) IS
385 SELECT DISTINCT rp.privilege_code
386 FROM wsh_grants g,
387 wsh_role_privileges rp
388 WHERE g.user_id = x_user_id
389 AND sysdate BETWEEN g.start_date AND NVL(g.end_date, sysdate)
390 AND NVL(g.organization_id, NVL(x_org_id, -1))
391 = NVL(x_org_id, NVL(g.organization_id, -1))
392 AND rp.role_id = g.role_id
393 ORDER BY privilege_code;
394
395 i NUMBER := 0;
396 --
397 l_debug_on BOOLEAN;
398 --
399 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ORGANIZATION_PRIVILEGES';
400 --
401 BEGIN
402
403 --
404 -- Debug Statements
405 --
406 --
407 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
408 --
409 IF l_debug_on IS NULL
410 THEN
411 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
412 END IF;
413 --
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.push(l_module_name);
416 --
417 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
418 END IF;
419 --
420 FOR p IN c_privileges(p_organization_id, fnd_profile.value('USER_ID')) LOOP
421 i:=i+1; x_privileges(i) := p.privilege_code;
422 END LOOP;
423 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
424
425 --
426 -- Debug Statements
427 --
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.log(l_module_name,'X_PRIVILEGES.COUNT',X_PRIVILEGES.COUNT);
430 WSH_DEBUG_SV.pop(l_module_name);
431 END IF;
432 --
433 EXCEPTION
434 WHEN others THEN
435 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
436 wsh_util_core.default_handler('WSH_RU_ACTIONS.GET_ORGANIZATION_PRIVILEGES');
437
438 --
439 -- Debug Statements
440 --
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
443 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
444 END IF;
445 --
446 END Get_Organization_Privileges;
447
448
449 PROCEDURE Entity_Access_In_Organization(
450 p_entity_type IN VARCHAR2,
451 p_organization_id IN NUMBER,
452 x_access_type OUT NOCOPY VARCHAR2,
453 x_return_status OUT NOCOPY VARCHAR2) IS
454
455 CURSOR c_access(x_org_id IN NUMBER, x_priv IN VARCHAR2,
456 x_user_id IN NUMBER) IS
457 SELECT 'Y'
458 FROM wsh_grants g,
459 wsh_role_privileges rp
460 WHERE g.user_id = x_user_id
461 AND sysdate BETWEEN g.start_date AND NVL(g.end_date, sysdate)
462 AND rp.role_id = g.role_id
463 AND NVL(g.organization_id, NVL(x_org_id, -1))
464 = NVL(x_org_id, NVL(g.organization_id, -1))
465 AND rp.privilege_code = x_priv
466 ORDER BY privilege_code;
467
468 user_id NUMBER := fnd_profile.value('USER_ID');
469 edit_privilege VARCHAR2(30);
470 view_privilege VARCHAR2(30);
471 flag VARCHAR2(1) := 'N';
472
473 --
474 l_debug_on BOOLEAN;
475 --
476 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ENTITY_ACCESS_IN_ORGANIZATION';
477 --
478 BEGIN
479
480 --
481 -- Debug Statements
482 --
483 --
484 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
485 --
486 IF l_debug_on IS NULL
487 THEN
488 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
489 END IF;
490 --
491 IF l_debug_on THEN
492 WSH_DEBUG_SV.push(l_module_name);
493 --
494 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',P_ENTITY_TYPE);
495 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
496 END IF;
497 --
498 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
499
500 IF (p_entity_type = 'TRIP') THEN
501 edit_privilege := 'TRIP_EDIT';
502 view_privilege := 'TRIP_VIEW';
503
504 ELSIF (p_entity_type IN ('STOP', 'TRIP STOP')) THEN
505 edit_privilege := 'STOP_EDIT';
506 view_privilege := 'STOP_VIEW';
507
508 ELSIF (p_entity_type IN ('DLVY',
509 'DELIVERY',
510 'BILL OF LADING',
511 'DELIVERY LEG',
512 'PACK SLIP')) THEN
513 edit_privilege := 'DLVY_EDIT';
514 view_privilege := 'DLVY_VIEW';
515
516 ELSIF (p_entity_type IN ('DLVB', 'DELIVERY DETAIL')) THEN
517 edit_privilege := 'DLVB_EDIT';
518 view_privilege := 'DLVB_VIEW';
519
520 END IF;
521
522 OPEN c_access(p_organization_id, edit_privilege, user_id);
523 FETCH c_access INTO flag;
524 IF c_access%NOTFOUND THEN
525 flag := 'N';
526 END IF;
527 CLOSE c_access;
528
529 IF flag = 'Y' THEN
530 x_access_type := 'EDIT';
531 --
532 -- Debug Statements
533 --
534 IF l_debug_on THEN
535 WSH_DEBUG_SV.log(l_module_name,'X_ACCESS_TYPE',X_ACCESS_TYPE);
536 WSH_DEBUG_SV.pop(l_module_name);
537 END IF;
538 --
539 return;
540 END IF;
541
542 OPEN c_access(p_organization_id, view_privilege, user_id);
543 FETCH c_access INTO flag;
544 IF c_access%NOTFOUND THEN
545 flag := 'N';
546 END IF;
547 CLOSE c_access;
548
549 IF flag = 'Y' THEN
550 x_access_type := 'VIEW';
551 --
552 -- Debug Statements
553 --
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.log(l_module_name,'X_ACCESS_TYPE',X_ACCESS_TYPE);
556 WSH_DEBUG_SV.pop(l_module_name);
557 END IF;
558 --
559 return;
560 END IF;
561
562 x_access_type := 'NONE';
563
564 --
565 -- Debug Statements
566 --
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.log(l_module_name,'X_ACCESS_TYPE',X_ACCESS_TYPE);
569 WSH_DEBUG_SV.pop(l_module_name);
570 END IF;
571 --
572 EXCEPTION
573 WHEN others THEN
574 x_access_type := 'NONE';
575 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
576 wsh_util_core.default_handler('WSH_RU_ACTIONS.ENTITY_ACCESS_IN_ORGANIZATION');
577
578 --
579 -- Debug Statements
580 --
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
583 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
584 END IF;
585 --
586 END Entity_Access_In_Organization;
587
588
589
590
591
592 /**************************************************************************************
593 * Table Handler to insert records into wsh_customized_activity_msgs table
594 * This API is designed to be called from ROLE_DEFINITIONS form only.
595 ****************************************************************************************/
596 Procedure insert_customized_msgs (
597 p_custom_message_rec IN OUT NOCOPY custom_message_rec
598 ,x_error_message OUT NOCOPY VARCHAR2
599 ,x_return_status OUT NOCOPY VARCHAR2 ) is
600
601 l_user_id number := fnd_global.user_id;
602 l_login_id number := fnd_global.login_id;
603 l_sysdate date := sysdate;
604 l_id number;
605 l_debug_on boolean;
606 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_CUSTOMIZED_MSGS';
607
608 Begin
609
610 l_debug_on := wsh_debug_interface.g_debug;
611 IF l_debug_on IS NULL THEN
612 l_debug_on := wsh_debug_sv.is_debug_enabled;
613 END IF;
614
615
616 IF l_debug_on THEN
617 wsh_debug_sv.push(l_module_name);
618 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.customized_activity_mesg_id',p_custom_message_rec.customized_activity_mesg_id);
619 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.role_id',p_custom_message_rec.role_id);
620 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.activity_code',p_custom_message_rec.activity_code);
621 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.validation_code',p_custom_message_rec.validation_code);
622 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.return_status',p_custom_message_rec.return_status);
623 wsh_debug_sv.log(l_module_name,'l_id',l_id);
624 wsh_debug_sv.log(l_module_name,'l_user_id',l_user_id);
625 wsh_debug_sv.log(l_module_name,'l_login_id',l_login_id);
626 wsh_debug_sv.log(l_module_name,'l_sysdate',l_sysdate);
627 END IF;
628
629 x_return_status := wsh_util_core.g_ret_sts_success;
630
631 insert into wsh_customized_activity_msgs
632 (customized_activity_mesg_id
633 ,role_id
634 ,activity_code
635 ,validation_code
636 ,return_status
637 ,creation_date
638 ,created_by
639 ,last_update_date
640 ,last_updated_by
641 ,last_update_login)
642 values
643 (wsh_customized_activity_msgs_s.nextval
644 ,p_custom_message_rec.role_id
645 ,p_custom_message_rec.activity_code
646 ,p_custom_message_rec.validation_code
647 ,p_custom_message_rec.return_status
648 ,l_sysdate
649 ,l_user_id
650 ,l_sysdate
651 ,l_user_id
652 ,l_login_id)
653 returning customized_activity_mesg_id into l_id;
654
655 p_custom_message_rec.customized_activity_mesg_id := l_id;
656 --p_custom_message_rec.last_update_date := l_sysdate;
657
658 IF l_debug_on THEN
659 wsh_debug_sv.log(l_module_name,'x_return_status',x_return_status);
660 wsh_debug_sv.pop(l_module_name);
661 END IF;
662
663 Exception
664 When others then
665 x_error_message := SQLERRM;
666 x_return_status := wsh_util_core.g_ret_sts_error;
667 IF l_debug_on THEN
668 wsh_debug_sv.log(l_module_name,'x_return_status',x_return_status);
669 wsh_debug_sv.pop(l_module_name);
670 END IF;
671
672 End insert_customized_msgs ;
673
674
675 /**************************************************************************************
676 * Table Handler to update records into wsh_customized_activity_msgs table
677 * This API is designed to be called from ROLE_DEFINITIONS form only.
678 * ***************************************************************************************/
679 Procedure update_customized_msgs (
680 p_custom_message_rec IN OUT NOCOPY custom_message_rec
681 ,x_error_message OUT NOCOPY VARCHAR2
682 ,x_return_status OUT NOCOPY VARCHAR2 ) is
683
684 l_user_id number := fnd_global.user_id;
685 l_login_id number := fnd_global.login_id;
686 l_sysdate date := sysdate;
687 l_debug_on boolean ;
688 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CUSTOMIZED_MSGS';
689
690 Begin
691
692 l_debug_on := wsh_debug_interface.g_debug;
693 IF l_debug_on IS NULL THEN
694 l_debug_on := wsh_debug_sv.is_debug_enabled;
695 END IF;
696
697 IF l_debug_on THEN
698 wsh_debug_sv.push(l_module_name);
699 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.customized_activity_mesg_id',p_custom_message_rec.customized_activity_mesg_id);
700 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.role_id',p_custom_message_rec.role_id);
701 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.activity_code',p_custom_message_rec.activity_code);
702 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.validation_code',p_custom_message_rec.validation_code);
703 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.return_status',p_custom_message_rec.return_status);
704 wsh_debug_sv.log(l_module_name,'l_user_id',l_user_id);
705 wsh_debug_sv.log(l_module_name,'l_login_id',l_login_id);
706 wsh_debug_sv.log(l_module_name,'l_sysdate',l_sysdate);
707 END IF;
708
709 x_return_status := wsh_util_core.g_ret_sts_success;
710
711 update wsh_customized_activity_msgs msg
712 set return_status = nvl(p_custom_message_rec.return_status,'W')
713 ,creation_date = l_sysdate
714 ,created_by = l_user_id
715 ,last_update_date = l_sysdate
716 ,last_updated_by = l_user_id
717 ,last_update_login = l_login_id
718 where msg.customized_activity_mesg_id = p_custom_message_rec.customized_activity_mesg_id
719 and msg.role_id = p_custom_message_rec.role_id
720 and msg.activity_code = p_custom_message_rec.activity_code
721 and msg.validation_code = p_custom_message_rec.validation_code;
722
723 --p_custom_message_rec.last_update_date := l_sysdate;
724
725 IF l_debug_on THEN
726 wsh_debug_sv.log(l_module_name,'x_return_status',x_return_status);
727 wsh_debug_sv.pop(l_module_name);
728 END IF;
729
730 Exception
731 When others then
732 x_error_message := SQLERRM;
733 x_return_status := wsh_util_core.g_ret_sts_error;
734 IF l_debug_on THEN
735 wsh_debug_sv.log(l_module_name,'x_return_status',x_return_status);
736 wsh_debug_sv.pop(l_module_name);
737 END IF;
738
739 End update_customized_msgs ;
740
741
742 /**************************************************************************************
743 * Table Handler to delete records into wsh_customized_activity_msgs table
744 * This API is designed to be called from ROLE_DEFINITIONS form only.
745 ****************************************************************************************/
746 Procedure delete_customized_msgs (
747 p_custom_message_rec IN OUT NOCOPY custom_message_rec
748 ,x_error_message OUT NOCOPY VARCHAR2
749 ,x_return_status OUT NOCOPY VARCHAR2 ) is
750
751 l_debug_on boolean ;
752 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_CUSTOMIZED_MSGS';
753
754 Begin
755
756 l_debug_on := wsh_debug_interface.g_debug;
757 IF l_debug_on IS NULL THEN
758 l_debug_on := wsh_debug_sv.is_debug_enabled;
759 END IF;
760
761 IF l_debug_on THEN
762 wsh_debug_sv.push(l_module_name);
763 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.customized_activity_mesg_id',p_custom_message_rec.customized_activity_mesg_id);
764 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.role_id',p_custom_message_rec.role_id);
765 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.activity_code',p_custom_message_rec.activity_code);
766 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.validation_code',p_custom_message_rec.validation_code);
767 wsh_debug_sv.log(l_module_name,'p_custom_message_rec.return_status',p_custom_message_rec.return_status);
768 END IF;
769
770 x_return_status := wsh_util_core.g_ret_sts_success;
771
772 delete from wsh_customized_activity_msgs
773 where customized_activity_mesg_id = p_custom_message_rec.customized_activity_mesg_id;
774
775 IF l_debug_on THEN
776 wsh_debug_sv.log(l_module_name,'l_return_status',x_return_status);
777 wsh_debug_sv.pop(l_module_name);
778 END IF;
779
780
781 Exception
782 When others then
783 x_error_message := SQLERRM;
784 x_return_status := wsh_util_core.g_ret_sts_error;
785 IF l_debug_on THEN
786 wsh_debug_sv.log(l_module_name,'l_return_status',x_return_status);
787 wsh_debug_sv.pop(l_module_name);
788 END IF;
789 End delete_customized_msgs ;
790
791 Function get_message_severity (
792 p_activity_code in varchar2
793 ,p_validation_code in varchar2 ) return varchar2 is
794
795 Cursor l_get_message_severity_csr( p_user_id in number
796 ,p_activity_code in VARCHAR2
797 ,p_validation_code in VARCHAR2
798 ,p_lookup_type in VARCHAR2) is
799 select msgs.activity_code , msgs.validation_code , msgs.return_status
800 from wsh_customized_activity_msgs msgs
801 ,wsh_grants grants
802 ,wsh_lookups activity
803 ,wsh_lookups message
804 where grants.user_id = p_user_id
805 and sysdate between nvl(grants.start_date,sysdate) and nvl(grants.end_date,sysdate )
806 and msgs.activity_code = p_activity_code
807 and msgs.validation_code = p_validation_code
808 and grants.role_id = msgs.role_id
809 and msgs.activity_code = activity.lookup_code
810 and sysdate between nvl(activity.start_date_active,sysdate) and nvl(activity.end_date_active,sysdate)
811 and activity.lookup_type = p_lookup_type
812 and msgs.validation_code = message.lookup_code
813 and sysdate between nvl(message.start_date_active,sysdate) and nvl(message.end_date_active,sysdate)
814 and message.lookup_type = activity.lookup_code
815 and msgs.return_status = 'E';
816
817 l_get_message_severity_rec l_get_message_severity_csr%ROWTYPE;
818
819 l_lookup_type CONSTANT varchar2(200) := 'WSH_CUSTOMIZED_ACTIVITY';
820 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_MESSAGE_SEVERITY';
821 l_user_id number := fnd_global.user_id;
822 l_index number ;
823 l_debug_on boolean;
824
825
826 Begin
827
828 l_debug_on := wsh_debug_interface.g_debug;
829 IF l_debug_on IS NULL THEN
830 l_debug_on := wsh_debug_sv.is_debug_enabled;
831 END IF;
832
833 IF l_debug_on THEN
834 wsh_debug_sv.push(l_module_name);
835 wsh_debug_sv.log(l_module_name,'l_user_id',l_user_id);
836 wsh_debug_sv.log(l_module_name,'p_activity_code',p_activity_code);
837 wsh_debug_sv.log(l_module_name,'p_validation_code',p_validation_code);
838 wsh_debug_sv.log(l_module_name,'g_custom_message_tbl.count',g_custom_message_tbl.count);
839 End If;
840
841 If g_custom_message_tbl.count > 0 then
842 For i in g_custom_message_tbl.first..g_custom_message_tbl.last
843 Loop
844 IF l_debug_on THEN
845 wsh_debug_sv.log(l_module_name,'g_custom_message_tbl('||i||').user_id',g_custom_message_tbl(i).user_id);
846 wsh_debug_sv.log(l_module_name,'g_custom_message_tbl('||i||').activity_code',g_custom_message_tbl(i).activity_code);
847 wsh_debug_sv.log(l_module_name,'g_custom_message_tbl('||i||').validation_code',g_custom_message_tbl(i).validation_code);
848 End If;
849 If g_custom_message_tbl(i).user_id = l_user_id And
850 g_custom_message_tbl(i).activity_code = p_activity_code And
851 g_custom_message_tbl(i).validation_code = p_validation_code Then
852 IF l_debug_on THEN
853 wsh_debug_sv.log(l_module_name,'Message Severity from cache ', g_custom_message_tbl(i).return_status);
854 wsh_debug_sv.pop(l_module_name);
855 End If;
856 return(g_custom_message_tbl(i).return_status);
857 End If;
858 End Loop;
859 End If;
860
861 Open l_get_message_severity_csr ( l_user_id
862 , p_activity_code
863 , p_validation_code
864 , l_lookup_type );
865 Fetch l_get_message_severity_csr into l_get_message_severity_rec;
866 IF l_debug_on THEN
867 wsh_debug_sv.log(l_module_name,'l_get_message_severity_rec.return_status:',l_get_message_severity_rec.return_status);
868 End If;
869 Close l_get_message_severity_csr;
870
871 l_index := g_custom_message_tbl.count + 1 ;
872 g_custom_message_tbl(l_index).user_id := l_user_id;
873 g_custom_message_tbl(l_index).activity_code := p_activity_code;
874 g_custom_message_tbl(l_index).validation_code := p_validation_code;
875 g_custom_message_tbl(l_index).return_status := nvl(l_get_message_severity_rec.return_status,'W');
876
877 IF l_debug_on THEN
878 wsh_debug_sv.log(l_module_name,'Message Severity ',nvl(l_get_message_severity_rec.return_status, 'W'));
879 wsh_debug_sv.pop(l_module_name);
880 End If;
881 Return(nvl(l_get_message_severity_rec.return_status,'W'));
882 Exception
883 When others then
884 IF l_debug_on THEN
885 wsh_debug_sv.log(l_module_name,'IN EXCEPTION ',SQLERRM);
886 wsh_debug_sv.pop(l_module_name);
887 End If;
888 Raise;
889
890 End get_message_severity;
891
892
893
894 END WSH_RU_ACTIONS;