[Home] [Help]
PACKAGE BODY: APPS.AMS_ACCESS_PVT
Source
1 PACKAGE BODY AMS_access_PVT AS
2 /* $Header: amsvaccb.pls 120.0 2005/05/31 23:58:05 appldev noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_access_PVT';
5
6 --==================================================================
7 -- Following code is added by ptendulk on 18-Jul-2000
8 --==================================================================
9 TYPE t_grp is TABLE OF NUMBER
10 INDEX BY BINARY_INTEGER;
11
12 --==================================================================
13 -- End of code added by ptendulk on 18-Jul-2000
14 --==================================================================
15
16 --------------------------------------------------------------
17 -- PROCEDURE
18 -- create_access
19 --
20 -- HISTORY
21 -- 10/12/99 abhola Create.
22 ---------------------------------------------------------------------
23 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
24 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
25 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26
27 PROCEDURE create_access(
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
30 p_commit IN VARCHAR2 := FND_API.g_false,
31 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 p_access_rec IN access_rec_type,
36 x_access_id OUT NOCOPY NUMBER
37 )
38 IS
39
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_api_name CONSTANT VARCHAR2(30) := 'create_access';
42 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
43
44 l_return_status VARCHAR2(1);
45 l_access_rec access_rec_type := p_access_rec;
46 l_access_count NUMBER;
47
48 CURSOR c_access_seq IS
49 SELECT ams_act_access_s.NEXTVAL
50 FROM DUAL;
51
52 CURSOR c_access_count(l_access_id IN NUMBER) IS
53 SELECT COUNT(*)
54 FROM ams_act_access
55 WHERE activity_access_id = l_access_id;
56
57 /* Following code is added by rrajesh on 09/12/01 */
58 CURSOR c_access_camp_schedules(l_camp_id IN NUMBER) IS
59 SELECT *
60 FROM ams_campaign_schedules_vl
61 WHERE campaign_id = l_camp_id;
62
63 l_schedule_rec c_access_camp_schedules%ROWTYPE;
64 /* end change 09/12/01 */
65
66 /* added by sunkumar 03-12-2002 bug id.. 2216520 */
67 /* check for the uniqueness of entries ams_act_access table if p_access_rec.arc_user_or_role_type = 'GROUP' */
68 /* if the entry exists ams_act_access table then a message is popped up to run the concurrent program */
69
70 CURSOR c_group_exists IS
71 SELECT 1
72 FROM DUAL
73 WHERE NOT EXISTS(SELECT 1
74 FROM ams_act_access
75 WHERE ams_act_access.act_access_to_object_id = p_access_rec.act_access_to_object_id
76 AND ams_act_access.arc_act_access_to_object = p_access_rec.arc_act_access_to_object
77 AND ams_act_access.user_or_role_id = p_access_rec.user_or_role_id
78 AND ams_act_access.delete_flag='Y');
79
80 l_group_exists NUMBER;
81 BEGIN
82
83 --------------------- initialize -----------------------
84 SAVEPOINT create_access;
85
86 IF (AMS_DEBUG_HIGH_ON) THEN
87
88
89
90 AMS_Utility_PVT.debug_message(l_full_name||': start');
91
92 END IF;
93
94 IF FND_API.to_boolean(p_init_msg_list) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97
98 IF NOT FND_API.compatible_api_call(
99 l_api_version,
100 p_api_version,
101 l_api_name,
102 g_pkg_name
103 ) THEN
104 RAISE FND_API.g_exc_unexpected_error;
105 END IF;
106
107 x_return_status := FND_API.g_ret_sts_success;
108
109 ----------------------- validate -----------------------
110 IF (AMS_DEBUG_HIGH_ON) THEN
111
112 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
113 END IF;
114
115 validate_access(
116 p_api_version => l_api_version,
117 p_init_msg_list => p_init_msg_list,
118 p_validation_level => p_validation_level,
119 x_return_status => l_return_status,
120 x_msg_count => x_msg_count,
121 x_msg_data => x_msg_data,
122 p_access_rec => l_access_rec
123 );
124
125 IF l_return_status = FND_API.g_ret_sts_error THEN
126 RAISE FND_API.g_exc_error;
127 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
128 RAISE FND_API.g_exc_unexpected_error;
129 END IF;
130
131
132 -------------------------- insert --------------------------
133 IF (AMS_DEBUG_HIGH_ON) THEN
134
135 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
136 END IF;
137
138
139 /* added by sunkumar 03-12-2002 bug id.. 2216520 */
140 /* check for the uniqueness of entries ams_act_access table if p_access_rec.arc_user_or_role_type = 'GROUP' */
141 /* if the entry exists in the ams_act_access table then a message is popped up to run the concurrent program */
142
143 OPEN c_group_exists;
144 FETCH c_group_exists INTO l_group_exists;
145 IF p_access_rec.arc_user_or_role_type = 'GROUP' AND c_group_exists%NOTFOUND THEN
146 FND_MESSAGE.set_name('AMS', 'AMS_RUN_ACCESS_REFRESH_PROGRAM');
147 FND_MSG_PUB.add;
148 RAISE FND_API.g_exc_error;
149 END IF;
150 CLOSE c_group_exists;
151 IF l_access_rec.activity_access_id IS NULL THEN
152 LOOP
153 OPEN c_access_seq;
154 FETCH c_access_seq INTO l_access_rec.activity_access_id;
155 CLOSE c_access_seq;
156
157 OPEN c_access_count(l_access_rec.activity_access_id);
158 FETCH c_access_count INTO l_access_count;
159 CLOSE c_access_count;
160
161 EXIT WHEN l_access_count = 0;
162 END LOOP;
163 END IF;
164
165 INSERT INTO ams_act_access(
166 activity_access_id,
167 last_update_date,
168 last_updated_by,
169 creation_date,
170 created_by,
171 last_update_login,
172 object_version_number,
173 act_access_to_object_id,
174 arc_act_access_to_object,
175 user_or_role_id,
176 arc_user_or_role_type,
177 active_from_date,
178 active_to_date,
179 admin_flag,
180 approver_flag,
181 owner_flag,
182 delete_flag )
183 VALUES(
184 l_access_rec.activity_access_id,
185 SYSDATE,
186 FND_GLOBAL.user_id,
187 SYSDATE,
188 FND_GLOBAL.user_id,
189 FND_GLOBAL.conc_login_id,
190 1, -- object_version_number
191 l_access_rec.act_access_to_object_id,
192 l_access_rec.arc_act_access_to_object,
193 l_access_rec.user_or_role_id,
194 l_access_rec.arc_user_or_role_type,
195 l_access_rec.active_from_date,
196 l_access_rec.active_to_date,
197 decode(l_access_rec.owner_flag,'Y','Y',nvl(l_access_rec.admin_flag,'N') ),
198 l_access_rec.approver_flag,
199 l_access_rec.owner_flag,
200 'N' );
201
202 ------------------------- finish -------------------------------
203 x_access_id := l_access_rec.activity_access_id;
204 IF l_access_rec.owner_flag = 'Y' THEN
205 l_access_rec.admin_flag := 'Y';
206 END IF;
207 IF l_access_rec.arc_user_or_role_type = 'USER' THEN
208
209 ams_access_denorm_pvt.insert_resource (
210 p_resource_id => l_access_rec.user_or_role_id,
211 p_object_id => l_access_rec.act_access_to_object_id,
212 p_object_type => l_access_rec.arc_act_access_to_object,
213 p_edit_metrics => l_access_rec.admin_flag,
214 x_return_status => l_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data
217 );
218
219 IF l_return_status = FND_API.g_ret_sts_error THEN
220 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
221 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
222 FND_MSG_PUB.add;
223 RAISE FND_API.g_exc_error;
224 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
225 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
226 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
227 FND_MSG_PUB.add;
228 RAISE FND_API.g_exc_unexpected_error;
229 END IF;
230
231 END IF;
232 -- if (l_access_rec.arc_act_access_to_object <> 'FUND' ) then
233
234 /************* Modify Attribute ******************************/
235 /***
236 AMS_ObjectAttribute_PVT.modify_object_attribute(
237 p_api_version => l_api_version,
238 p_init_msg_list => FND_API.g_false,
239 p_commit => FND_API.g_false,
240 p_validation_level => FND_API.g_valid_level_full,
241
242 x_return_status => l_return_status,
243 x_msg_count => x_msg_count,
244 x_msg_data => x_msg_data,
245
246 p_object_type => l_access_rec.arc_act_access_to_object,
247 p_object_id => l_access_rec.act_access_to_object_id ,
248 p_attr => 'TEAM',
249 p_attr_defined_flag => 'Y'
250 );
251 ***/
252
253 -- end if;
254 -- Added by rrajesh on 09/12/01. to add access to schedules.
255 IF l_access_rec.arc_act_access_to_object = 'CAMP' THEN
256 IF l_access_rec.act_access_to_object_id IS NOT NULL THEN
257 OPEN c_access_camp_schedules(l_access_rec.act_access_to_object_id);
258 LOOP
259 LOOP
260 OPEN c_access_seq;
261 FETCH c_access_seq INTO l_access_rec.activity_access_id;
262 CLOSE c_access_seq;
263
264 OPEN c_access_count(l_access_rec.activity_access_id);
265 FETCH c_access_count INTO l_access_count;
266 CLOSE c_access_count;
267
268 EXIT WHEN l_access_count = 0;
269 END LOOP;
270 FETCH c_access_camp_schedules INTO l_schedule_rec;
271 EXIT WHEN c_access_camp_schedules%NOTFOUND;
272
273 INSERT INTO ams_act_access(
274 activity_access_id,
275 last_update_date,
276 last_updated_by,
277 creation_date,
278 created_by,
279 last_update_login,
280 object_version_number,
281 act_access_to_object_id,
282 arc_act_access_to_object,
283 user_or_role_id,
284 arc_user_or_role_type,
285 active_from_date,
286 active_to_date,
287 admin_flag,
288 approver_flag,
289 owner_flag,
290 delete_flag )
291 SELECT
292 l_access_rec.activity_access_id,
293 SYSDATE,
294 FND_GLOBAL.user_id,
295 SYSDATE,
296 FND_GLOBAL.user_id,
297 FND_GLOBAL.conc_login_id,
298 1, -- object_version_number
299 l_schedule_rec.schedule_id,
300 'CSCH',
301 l_access_rec.user_or_role_id,
302 l_access_rec.arc_user_or_role_type,
303 l_access_rec.active_from_date,
304 l_access_rec.active_to_date,
305 decode(l_access_rec.owner_flag,'Y','Y',nvl(l_access_rec.admin_flag,'N') ),
306 l_access_rec.approver_flag,
307 l_access_rec.owner_flag,
308 'N'
309 FROM DUAL
310 WHERE NOT EXISTS ( SELECT 1 FROM AMS_ACT_ACCESS WHERE act_access_to_object_id = l_schedule_rec.schedule_id
311 and arc_act_access_to_object = 'CSCH'
312 and user_or_role_id = l_access_rec.user_or_role_id
313 and arc_user_or_role_type = 'USER');
314
315 IF l_access_rec.arc_user_or_role_type = 'USER' THEN
316
317 ams_access_denorm_pvt.insert_resource (
318 p_resource_id => l_access_rec.user_or_role_id,
319 p_object_id => l_schedule_rec.schedule_id,
320 p_object_type => 'CSCH',
321 p_edit_metrics => nvl(l_access_rec.admin_flag,'N'),
322 x_return_status => l_return_status,
323 x_msg_count => x_msg_count,
324 x_msg_data => x_msg_data
325 );
326
327 IF l_return_status = FND_API.g_ret_sts_error THEN
328 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
329 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
330 FND_MSG_PUB.add;
331 RAISE FND_API.g_exc_error;
332 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
333 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
334 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
335 FND_MSG_PUB.add;
336 RAISE FND_API.g_exc_unexpected_error;
337 END IF;
338
339 END IF;
340
341 END LOOP;
342 CLOSE c_access_camp_schedules;
343 END IF; -- obj_id
344 END IF ;-- obj_type
345 -- end change for schedule level access
346
347 /*****************************************************************/
348 IF l_return_status = FND_API.g_ret_sts_error THEN
349 RAISE FND_API.g_exc_error;
350 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
351 RAISE FND_API.g_exc_unexpected_error;
352 END IF;
353 -----------------------------------------------------------------
354
355 IF FND_API.to_boolean(p_commit) THEN
356 COMMIT;
357 END IF;
358
359 FND_MSG_PUB.count_and_get(
360 p_encoded => FND_API.g_false,
361 p_count => x_msg_count,
362 p_data => x_msg_data
363 );
364
365 IF (AMS_DEBUG_HIGH_ON) THEN
366
367
368
369 AMS_Utility_PVT.debug_message(l_full_name ||': end');
370
371 END IF;
372
373 EXCEPTION
374
375 WHEN FND_API.g_exc_error THEN
376 ROLLBACK TO create_access;
377 x_return_status := FND_API.g_ret_sts_error;
378 FND_MSG_PUB.count_and_get(
379 p_encoded => FND_API.g_false,
380 p_count => x_msg_count,
381 p_data => x_msg_data
382 );
383
384 WHEN FND_API.g_exc_unexpected_error THEN
385 ROLLBACK TO create_access;
386 x_return_status := FND_API.g_ret_sts_unexp_error ;
387 FND_MSG_PUB.count_and_get(
388 p_encoded => FND_API.g_false,
389 p_count => x_msg_count,
390 p_data => x_msg_data
391 );
392
393
394 WHEN OTHERS THEN
395 ROLLBACK TO create_access;
396 x_return_status := FND_API.g_ret_sts_unexp_error ;
397
398 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
399 THEN
400 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
401 END IF;
402
403 FND_MSG_PUB.count_and_get(
404 p_encoded => FND_API.g_false,
405 p_count => x_msg_count,
406 p_data => x_msg_data
407 );
408
409 END create_access;
410 ---------------------------------------------------------------
411 -- PROCEDURE
412 -- delete_access
413 --
414 -- HISTORY
415 -- 10/12/99 abhola Create.
416 ---------------------------------------------------------------
417 PROCEDURE delete_access(
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
420 p_commit IN VARCHAR2 := FND_API.g_false,
421 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 p_access_id IN NUMBER,
426 p_object_version IN NUMBER
427 )
428 IS
429
430 l_api_version CONSTANT NUMBER := 1.0;
431 l_api_name CONSTANT VARCHAR2(30) := 'delete_access';
432 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
433
434 CURSOR c_getobj_type_id(l_access_id IN NUMBER) IS
435 SELECT arc_act_access_to_object,
436 act_access_to_object_id,
437 arc_user_or_role_type,
438 user_or_role_id,
439 admin_flag
440 FROM ams_act_access
441 WHERE activity_access_id = l_access_id;
442
443 CURSOR c_getobj_attr ( p_obj_id in NUMBER, p_obj_type IN VARCHAR2) IS
444 SELECT 'x'
445 FROM ams_act_access
446 WHERE arc_act_access_to_object = p_obj_type
447 AND act_access_to_object_id = p_obj_id;
448
449 l_object_id NUMBER;
450 l_object_type VARCHAR2(100);
451 l_role_id NUMBER;
452 l_role_type VARCHAR2(100);
453 l_admin_flag VARCHAR2(1);
454 l_dummy VARCHAR2(1);
455
456 l_return_status VARCHAr2(1);
457
458 /* Following code is added by rrajesh on 09/12/01 */
459 CURSOR c_access_camp_schedules(l_camp_id IN NUMBER) IS
460 SELECT *
461 FROM ams_campaign_schedules_vl
462 WHERE campaign_id = l_camp_id;
463
464 /* commented by skarumur, using c_getobj_type_id cursor
465 CURSOR c_get_camp_id(l_activity_access_id IN NUMBER) IS
466 SELECT act_access_to_object_id
467 FROM ams_act_access
468 WHERE activity_access_id = l_activity_access_id;
469
470 CURSOR c_get_user_or_role_type(l_activity_access_id IN NUMBER) IS
471 SELECT arc_user_or_role_type
472 FROM ams_act_access
473 WHERE activity_access_id = l_activity_access_id;
474
475 CURSOR c_get_user_or_role_id(l_activity_access_id IN NUMBER) IS
476 SELECT user_or_role_id
477 FROM ams_act_access
478 WHERE activity_access_id = l_activity_access_id; */
479
480 l_schedule_rec c_access_camp_schedules%ROWTYPE;
481 /* end change 09/12/01 */
482
483 BEGIN
484
485 --------------------- initialize -----------------------
486 SAVEPOINT delete_access;
487
488 IF (AMS_DEBUG_HIGH_ON) THEN
489
490
491
492 AMS_Utility_PVT.debug_message(l_full_name||': start');
493
494 END IF;
495
496 IF FND_API.to_boolean(p_init_msg_list) THEN
497 FND_MSG_PUB.initialize;
498 END IF;
499
500 IF NOT FND_API.compatible_api_call(
501 l_api_version,
502 p_api_version,
503 l_api_name,
504 g_pkg_name
505 ) THEN
506 RAISE FND_API.g_exc_unexpected_error;
507 END IF;
508
509
510 ----- Get the object type and obj id for this access id ----
511
512 OPEN c_getobj_type_id(p_access_id);
513 FETCH c_getobj_type_id into l_object_type, l_object_id,l_role_type,l_role_id,l_admin_flag;
514 CLOSE c_getobj_type_id;
515
516 --------------------------------------------------------------
517
518 x_return_status := FND_API.G_RET_STS_SUCCESS;
519
520 ------------------------ delete ------------------------
521 IF (AMS_DEBUG_HIGH_ON) THEN
522
523 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
524 END IF;
525
526 IF l_role_type = 'USER' THEN
527
528 DELETE FROM ams_act_access
529 WHERE activity_access_id = p_access_id
530 AND object_version_number = p_object_version;
531
532 IF (SQL%NOTFOUND) THEN
533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
534 THEN
535 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
536 FND_MSG_PUB.add;
537 END IF;
538 RAISE FND_API.g_exc_error;
539 END IF;
540
541
542 -- Added by rrajesh on 09/12/01 - for campaign schedules
543
544 /*OPEN c_get_camp_id(p_access_id);
545 FETCH c_get_camp_id INTO l_camp_id;
546 CLOSE c_get_camp_id;
547
548 OPEN c_get_user_or_role_type(p_access_id);
549 FETCH c_get_user_or_role_type INTO l_user_or_role_type;
550 CLOSE c_get_user_or_role_type;
551
552 OPEN c_get_user_or_role_id(p_access_id);
553 FETCH c_get_user_or_role_id INTO l_user_or_role_id;
554 CLOSE c_get_user_or_role_type; */
555
556 IF l_object_type = 'CAMP' THEN
557 OPEN c_access_camp_schedules(l_object_id);
558 LOOP
559 FETCH c_access_camp_schedules INTO l_schedule_rec;
560 EXIT WHEN c_access_camp_schedules%NOTFOUND ;
561 DELETE FROM ams_act_access
562 WHERE act_access_to_object_id = l_schedule_rec.schedule_id
563 AND arc_act_access_to_object = 'CSCH'
564 AND user_or_role_id = l_role_id
565 AND arc_user_or_role_type = l_role_type;
566
567 IF (SQL%NOTFOUND) THEN
568 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
569 THEN
570 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
571 FND_MSG_PUB.add;
572 END IF;
573 RAISE FND_API.g_exc_error;
574 END IF;
575
576 ams_access_denorm_pvt.delete_resource (
577 p_resource_id => l_role_id,
578 p_object_id => l_schedule_rec.schedule_id,
579 p_object_type => 'CSCH',
580 p_edit_metrics => l_admin_flag,
581 x_return_status => l_return_status,
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data
584 );
585
586 IF l_return_status = FND_API.g_ret_sts_error THEN
587 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
588 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
589 FND_MSG_PUB.add;
590 RAISE FND_API.g_exc_error;
591 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
592 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
593 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
594 FND_MSG_PUB.add;
595 RAISE FND_API.g_exc_unexpected_error;
596 END IF;
597 END LOOP;
598 CLOSE c_access_camp_schedules;
599 END IF;
600 -- end change. for schedules. 09/12/01
601
602 ams_access_denorm_pvt.delete_resource (
603 p_resource_id => l_role_id,
604 p_object_id => l_object_id,
605 p_object_type => l_object_type,
606 p_edit_metrics => l_admin_flag,
607 x_return_status => l_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data
610 );
611
612 IF l_return_status = FND_API.g_ret_sts_error THEN
613 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
614 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
615 FND_MSG_PUB.add;
616 RAISE FND_API.g_exc_error;
617 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
618 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
619 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
620 FND_MSG_PUB.add;
621 RAISE FND_API.g_exc_unexpected_error;
622 END IF;
623
624 ELSIF l_role_type = 'GROUP' THEN
625
626 UPDATE ams_act_access
627 SET delete_flag = 'Y',
628 last_update_date = sysdate,
629 last_update_login = fnd_global.conc_login_id,
630 last_updated_by = fnd_global.user_id
631 WHERE activity_access_id = p_access_id
632 AND object_version_number = p_object_version;
633
634 IF (SQL%NOTFOUND) THEN
635 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
636 THEN
637 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
638 FND_MSG_PUB.add;
639 END IF;
640 RAISE FND_API.g_exc_error;
641 END IF;
642
643 -- Added by rrajesh on 09/12/01 - for campaign schedules
644
645 /* OPEN c_get_camp_id(p_access_id);
646 FETCH c_get_camp_id INTO l_camp_id;
647 CLOSE c_get_camp_id;
648
649 OPEN c_get_user_or_role_id(p_access_id);
650 FETCH c_get_user_or_role_id INTO l_user_or_role_id;
651 CLOSE c_get_user_or_role_id;
652
653 OPEN c_get_user_or_role_type(p_access_id);
654 FETCH c_get_user_or_role_type INTO l_user_or_role_type;
655 CLOSE c_get_user_or_role_type; */
656
657 IF l_object_type = 'CAMP' THEN
658 OPEN c_access_camp_schedules(l_object_id);
659 LOOP
660 FETCH c_access_camp_schedules INTO l_schedule_rec;
661 EXIT WHEN c_access_camp_schedules%NOTFOUND ;
662 UPDATE ams_act_access
663 SET delete_flag = 'Y',
664 last_update_date = sysdate,
665 last_update_login = fnd_global.conc_login_id,
666 last_updated_by = fnd_global.user_id
667 WHERE act_access_to_object_id = l_schedule_rec.schedule_id
668 AND arc_act_access_to_object = 'CSCH'
669 AND user_or_role_id = l_role_id
670 AND arc_user_or_role_type = l_role_type;
671
672 IF (SQL%NOTFOUND) THEN
673 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
674 THEN
675 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
676 FND_MSG_PUB.add;
677 END IF;
678 RAISE FND_API.g_exc_error;
679 END IF;
680 END LOOP;
681 CLOSE c_access_camp_schedules;
682 END IF;
683 /* end change. for schedules. 09/12/01 */
684 END IF;
685
686 -- if (l_object_type <> 'FUND' ) then
687
688 /***
689 ----- Modify Object Attribute ---------------
690
691 OPEN c_getobj_attr( l_object_id, l_object_type);
692 FETCH c_getobj_attr into l_dummy;
693
694 if (c_getobj_attr%NOTFOUND) then
695
696 AMS_ObjectAttribute_PVT.modify_object_attribute(
697 p_api_version => l_api_version,
698 p_init_msg_list => FND_API.g_false,
699 p_commit => FND_API.g_false,
700 p_validation_level => FND_API.g_valid_level_full,
701 x_return_status => l_return_status,
702 x_msg_count => x_msg_count,
703 x_msg_data => x_msg_data,
704
705 p_object_type => l_object_type,
706 p_object_id => l_object_id ,
707 p_attr => 'TEAM',
708 p_attr_defined_flag => 'N'
709 );
710 IF l_return_status = FND_API.g_ret_sts_error THEN
711 RAISE FND_API.g_exc_error;
712 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
713 RAISE FND_API.g_exc_unexpected_error;
714 END IF;
715
716 end if;
717 -- end if;
718 ***/
719 ------------------------------------------------------
720
721
722 -------------------- finish --------------------------
723 IF FND_API.to_boolean(p_commit) THEN
724 COMMIT;
725 END IF;
726
727 FND_MSG_PUB.count_and_get(
728 p_encoded => FND_API.g_false,
729 p_count => x_msg_count,
730 p_data => x_msg_data
731 );
732
733 IF (AMS_DEBUG_HIGH_ON) THEN
734
735
736
737 AMS_Utility_PVT.debug_message(l_full_name ||': end');
738
739 END IF;
740
741 EXCEPTION
742
743 WHEN FND_API.g_exc_error THEN
744 ROLLBACK TO delete_access;
745 x_return_status := FND_API.g_ret_sts_error;
746 FND_MSG_PUB.count_and_get(
747 p_encoded => FND_API.g_false,
748 p_count => x_msg_count,
749 p_data => x_msg_data
750 );
751
752 WHEN FND_API.g_exc_unexpected_error THEN
753 ROLLBACK TO delete_access;
754 x_return_status := FND_API.g_ret_sts_unexp_error ;
755 FND_MSG_PUB.count_and_get(
756 p_encoded => FND_API.g_false,
757 p_count => x_msg_count,
758 p_data => x_msg_data
759 );
760
761 WHEN OTHERS THEN
762 ROLLBACK TO delete_access;
763 x_return_status := FND_API.g_ret_sts_unexp_error ;
764
765 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
766 THEN
767 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
768 END IF;
769
770 FND_MSG_PUB.count_and_get(
771 p_encoded => FND_API.g_false,
772 p_count => x_msg_count,
773 p_data => x_msg_data
774 );
775
776 END delete_access;
777
778 -------------------------------------------------------------------
779 -- PROCEDURE
780 -- lock_access
781 --
782 -- HISTORY
783 -- 10/12/99 abhola Create.
784 --------------------------------------------------------------------
785 PROCEDURE lock_access(
786 p_api_version IN NUMBER,
787 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
788 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
789
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2,
793
794 p_access_id IN NUMBER,
795 p_object_version IN NUMBER
796 )
797 IS
798
799 l_api_version CONSTANT NUMBER := 1.0;
800 l_api_name CONSTANT VARCHAR2(30) := 'lock_access';
801 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
802
803 l_access_id NUMBER;
804
805 CURSOR c_access_b IS
806 SELECT activity_access_id
807 FROM ams_act_access
808 WHERE activity_access_id = p_access_id
809 AND object_version_number = p_object_version
810 FOR UPDATE OF activity_access_id NOWAIT;
811
812
813 BEGIN
814
815 -------------------- initialize ------------------------
816 IF (AMS_DEBUG_HIGH_ON) THEN
817
818 AMS_Utility_PVT.debug_message(l_full_name||': start');
819 END IF;
820
821 IF FND_API.to_boolean(p_init_msg_list) THEN
822 FND_MSG_PUB.initialize;
823 END IF;
824
825 IF NOT FND_API.compatible_api_call(
826 l_api_version,
827 p_api_version,
828 l_api_name,
829 g_pkg_name
830 ) THEN
831 RAISE FND_API.g_exc_unexpected_error;
832 END IF;
833
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835
836 ------------------------ lock -------------------------
837 IF (AMS_DEBUG_HIGH_ON) THEN
838
839 AMS_Utility_PVT.debug_message(l_full_name||': lock');
840 END IF;
841
842 OPEN c_access_b;
843 FETCH c_access_b INTO l_access_id;
844 IF (c_access_b%NOTFOUND) THEN
845 CLOSE c_access_b;
846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
847 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
848 FND_MSG_PUB.add;
849 END IF;
850 RAISE FND_API.g_exc_error;
851 END IF;
852 CLOSE c_access_b;
853 -------------------- finish --------------------------
854 FND_MSG_PUB.count_and_get(
855 p_encoded => FND_API.g_false,
856 p_count => x_msg_count,
857 p_data => x_msg_data
858 );
859
860 IF (AMS_DEBUG_HIGH_ON) THEN
861
862
863
864 AMS_Utility_PVT.debug_message(l_full_name ||': end');
865
866 END IF;
867
868 EXCEPTION
869
870 WHEN AMS_Utility_PVT.resource_locked THEN
871 x_return_status := FND_API.g_ret_sts_error;
872 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
873 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
874 FND_MSG_PUB.add;
875 END IF;
876
877 FND_MSG_PUB.count_and_get(
878 p_encoded => FND_API.g_false,
879 p_count => x_msg_count,
880 p_data => x_msg_data
881 );
882
883 WHEN FND_API.g_exc_error THEN
884 x_return_status := FND_API.g_ret_sts_error;
885 FND_MSG_PUB.count_and_get(
886 p_encoded => FND_API.g_false,
887 p_count => x_msg_count,
888 p_data => x_msg_data
889 );
890
891 WHEN FND_API.g_exc_unexpected_error THEN
892 x_return_status := FND_API.g_ret_sts_unexp_error ;
893 FND_MSG_PUB.count_and_get(
894 p_encoded => FND_API.g_false,
895 p_count => x_msg_count,
896 p_data => x_msg_data
897 );
898
899 WHEN OTHERS THEN
900 x_return_status := FND_API.g_ret_sts_unexp_error ;
901 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
902 THEN
903 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
904 END IF;
905
906 FND_MSG_PUB.count_and_get(
907 p_encoded => FND_API.g_false,
908 p_count => x_msg_count,
909 p_data => x_msg_data
910 );
911
912 END lock_access;
913
914
915 ---------------------------------------------------------------------
916 -- PROCEDURE
917 -- update_access
918 --
919 -- HISTORY
920 -- 10/12/99 abhola Create.
921 ----------------------------------------------------------------------
922 PROCEDURE update_access(
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
925 p_commit IN VARCHAR2 := FND_API.g_false,
926 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
927 x_return_status OUT NOCOPY VARCHAR2,
928 x_msg_count OUT NOCOPY NUMBER,
929 x_msg_data OUT NOCOPY VARCHAR2,
930 p_access_rec IN access_rec_type
931 )
932 IS
933
934 l_api_version CONSTANT NUMBER := 1.0;
935 l_api_name CONSTANT VARCHAR2(30) := 'update_access';
936 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
937
938 l_access_rec access_rec_type := p_access_rec;
939 l_return_status VARCHAR2(1);
940
941 /* Following code is added by rrajesh on 09/12/01 */
942
943 CURSOR c_access_camp_schedules(l_camp_id IN NUMBER) IS
944 SELECT *
945 FROM ams_campaign_schedules_vl
946 WHERE campaign_id = l_camp_id;
947
948 CURSOR c_get_user_or_role_type(l_activity_access_id IN NUMBER) IS
949 SELECT arc_user_or_role_type
950 FROM ams_act_access
951 WHERE activity_access_id = l_activity_access_id;
952
953 CURSOR c_get_user_or_role_id(l_activity_access_id IN NUMBER) IS
954 SELECT user_or_role_id
955 FROM ams_act_access
956 WHERE activity_access_id = l_activity_access_id;
957
958 CURSOR c_get_key_id(p_sched_id IN NUMBER, p_user_or_role_type IN VARCHAR2, p_user_or_role_id NUMBER) IS
959 SELECT activity_access_id
960 FROM ams_act_access
961 WHERE act_access_to_object_id = p_sched_id
962 AND arc_act_access_to_object = 'CSCH'
963 AND user_or_role_id = p_user_or_role_id
964 AND arc_user_or_role_type = p_user_or_role_type;
965
966
967 l_schedule_rec c_access_camp_schedules%ROWTYPE;
968 l_camp_id NUMBER;
969 l_user_or_role_type VARCHAR2(30);
970 l_user_or_role_id NUMBER;
971 l_key_id NUMBER;
972 /* end change 09/12/01 */
973
974 BEGIN
975
976 -------------------- initialize -------------------------
977 SAVEPOINT update_access;
978
979 IF (AMS_DEBUG_HIGH_ON) THEN
980
981
982
983 AMS_Utility_PVT.debug_message(l_full_name||': start');
984
985 END IF;
986
987 IF FND_API.to_boolean(p_init_msg_list) THEN
988 FND_MSG_PUB.initialize;
989 END IF;
990
991 IF NOT FND_API.compatible_api_call(
992 l_api_version,
993 p_api_version,
994 l_api_name,
995 g_pkg_name
996 ) THEN
997 RAISE FND_API.g_exc_unexpected_error;
998 END IF;
999
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001
1002 ----------------------- validate ----------------------
1003 IF (AMS_DEBUG_HIGH_ON) THEN
1004
1005 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1006 END IF;
1007
1008 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1009 check_access_items(
1010 p_access_rec => p_access_rec,
1011 p_validation_mode => JTF_PLSQL_API.g_update,
1012 x_return_status => l_return_status
1013 );
1014
1015 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1016 RAISE FND_API.g_exc_unexpected_error;
1017 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1018 RAISE FND_API.g_exc_error;
1019 END IF;
1020 END IF;
1021
1022 -- replace g_miss_char/num/date with current column values
1023 complete_access_rec(p_access_rec, l_access_rec);
1024
1025 -------------------------- update --------------------
1026 IF (AMS_DEBUG_HIGH_ON) THEN
1027
1028 AMS_Utility_PVT.debug_message(l_full_name ||': update');
1029 END IF;
1030
1031 UPDATE ams_act_access SET
1032 last_update_date = SYSDATE,
1033 last_updated_by = FND_GLOBAL.user_id,
1034 last_update_login = FND_GLOBAL.conc_login_id,
1035 object_version_number = l_access_rec.object_version_number + 1,
1036 act_access_to_object_id = l_access_rec.act_access_to_object_id,
1037 arc_act_access_to_object = l_access_rec.arc_act_access_to_object,
1038 user_or_role_id = l_access_rec.user_or_role_id,
1039 arc_user_or_role_type = l_access_rec.arc_user_or_role_type,
1040 active_from_date = l_access_rec.active_from_date,
1041 active_to_date = l_access_rec.active_to_date,
1042 admin_flag = l_access_rec.admin_flag,
1043 approver_flag = l_access_rec.approver_flag
1044 WHERE activity_access_id = l_access_rec.activity_access_id
1045 AND object_version_number = l_access_rec.object_version_number;
1046
1047 IF (SQL%NOTFOUND) THEN
1048 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1049 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1050 FND_MSG_PUB.add;
1051 END IF;
1052 RAISE FND_API.g_exc_error;
1053 END IF;
1054
1055 /* Added by rrajesh on 09/12/01. to add access to schedules. */
1056 IF l_access_rec.arc_act_access_to_object = 'CAMP' THEN
1057 IF l_access_rec.act_access_to_object_id IS NOT NULL THEN
1058 OPEN c_access_camp_schedules(l_access_rec.act_access_to_object_id);
1059 LOOP
1060 FETCH c_access_camp_schedules INTO l_schedule_rec;
1061 EXIT WHEN c_access_camp_schedules%NOTFOUND ;
1062
1063 OPEN c_get_key_id(l_schedule_rec.schedule_id, l_access_rec.arc_user_or_role_type, l_access_rec.user_or_role_id);
1064 FETCH c_get_key_id INTO l_key_id;
1065 CLOSE c_get_key_id;
1066
1067 UPDATE ams_act_access SET
1068 last_update_date = SYSDATE,
1069 last_updated_by = FND_GLOBAL.user_id,
1070 last_update_login = FND_GLOBAL.conc_login_id,
1071 object_version_number = object_version_number + 1,
1072 act_access_to_object_id = l_schedule_rec.schedule_id,
1073 arc_act_access_to_object = 'CSCH',
1074 user_or_role_id = l_access_rec.user_or_role_id,
1075 arc_user_or_role_type = l_access_rec.arc_user_or_role_type,
1076 active_from_date = l_access_rec.active_from_date,
1077 active_to_date = l_access_rec.active_to_date,
1078 admin_flag = l_access_rec.admin_flag,
1079 approver_flag = l_access_rec.approver_flag
1080 WHERE activity_access_id = l_key_id;
1081
1082 IF (SQL%NOTFOUND) THEN
1083 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1084 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1085 FND_MSG_PUB.add;
1086 END IF;
1087 RAISE FND_API.g_exc_error;
1088 END IF;
1089
1090 ams_access_denorm_pvt.update_resource (
1091 p_resource_id => l_access_rec.user_or_role_id,
1092 p_object_id => l_schedule_rec.schedule_id,
1093 p_object_type => 'CSCH',
1094 p_edit_metrics => l_access_rec.admin_flag,
1095 x_return_status => l_return_status,
1096 x_msg_count => x_msg_count,
1097 x_msg_data => x_msg_data
1098 );
1099
1100 IF l_return_status = FND_API.g_ret_sts_error THEN
1101 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1102 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1103 FND_MSG_PUB.add;
1104 RAISE FND_API.g_exc_error;
1105 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1106 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
1107 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1108 FND_MSG_PUB.add;
1109 RAISE FND_API.g_exc_unexpected_error;
1110 END IF;
1111
1112 END LOOP;
1113 CLOSE c_access_camp_schedules;
1114 END IF; -- obj_id
1115 END IF; -- obj_type
1116 /* end change for schedule level access */
1117
1118 IF l_access_rec.arc_user_or_role_type = 'USER' THEN
1119 IF l_access_rec.owner_flag <> 'Y' OR l_access_rec.owner_flag IS NULL THEN
1120 ams_access_denorm_pvt.update_resource (
1121 p_resource_id => l_access_rec.user_or_role_id,
1122 p_object_id => l_access_rec.act_access_to_object_id,
1123 p_object_type => l_access_rec.arc_act_access_to_object,
1124 p_edit_metrics => l_access_rec.admin_flag,
1125 x_return_status => l_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data
1128 );
1129
1130 IF l_return_status = FND_API.g_ret_sts_error THEN
1131 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1132 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1133 FND_MSG_PUB.add;
1134 RAISE FND_API.g_exc_error;
1135 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1136 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
1137 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1138 FND_MSG_PUB.add;
1139 RAISE FND_API.g_exc_unexpected_error;
1140 END IF;
1141
1142 ELSIF l_access_rec.owner_flag = 'Y' THEN
1143
1144 ams_access_denorm_pvt.delete_resource (
1145 p_resource_id => l_access_rec.user_or_role_id,
1146 p_object_id => l_access_rec.act_access_to_object_id,
1147 p_object_type => l_access_rec.arc_act_access_to_object,
1148 p_edit_metrics => l_access_rec.admin_flag,
1149 x_return_status => l_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data
1152 );
1153
1154 IF l_return_status = FND_API.g_ret_sts_error THEN
1155 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1156 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1157 FND_MSG_PUB.add;
1158 RAISE FND_API.g_exc_error;
1159 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1160 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
1161 -- FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1162 FND_MSG_PUB.add;
1163 RAISE FND_API.g_exc_unexpected_error;
1164 END IF;
1165
1166 ams_access_denorm_pvt.insert_resource (
1167 p_resource_id => l_access_rec.user_or_role_id,
1168 p_object_id => l_access_rec.act_access_to_object_id,
1169 p_object_type => l_access_rec.arc_act_access_to_object,
1170 p_edit_metrics => l_access_rec.admin_flag,
1171 x_return_status => l_return_status,
1172 x_msg_count => x_msg_count,
1173 x_msg_data => x_msg_data
1174 );
1175
1176 IF l_return_status = FND_API.g_ret_sts_error THEN
1177 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1178 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1179 FND_MSG_PUB.add;
1180 RAISE FND_API.g_exc_error;
1181 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1182 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
1183 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1184 FND_MSG_PUB.add;
1185 RAISE FND_API.g_exc_unexpected_error;
1186 END IF;
1187 END IF;
1188 END IF;
1189 -------------------- finish --------------------------
1190 IF FND_API.to_boolean(p_commit) THEN
1191 COMMIT;
1192 END IF;
1193
1194 FND_MSG_PUB.count_and_get(
1195 p_encoded => FND_API.g_false,
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199
1200 IF (AMS_DEBUG_HIGH_ON) THEN
1201
1202
1203
1204 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1205
1206 END IF;
1207
1208 EXCEPTION
1209
1210 WHEN FND_API.g_exc_error THEN
1211 ROLLBACK TO update_access;
1212 x_return_status := FND_API.g_ret_sts_error;
1213 FND_MSG_PUB.count_and_get(
1214 p_encoded => FND_API.g_false,
1215 p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218
1219 WHEN FND_API.g_exc_unexpected_error THEN
1220 ROLLBACK TO update_access;
1221 x_return_status := FND_API.g_ret_sts_unexp_error ;
1222 FND_MSG_PUB.count_and_get(
1223 p_encoded => FND_API.g_false,
1224 p_count => x_msg_count,
1225 p_data => x_msg_data
1226 );
1227
1228 WHEN OTHERS THEN
1229 ROLLBACK TO update_access;
1230 x_return_status := FND_API.g_ret_sts_unexp_error ;
1231
1232 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1233 THEN
1234 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1235 END IF;
1236
1237 FND_MSG_PUB.count_and_get(
1238 p_encoded => FND_API.g_false,
1239 p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242
1243 END update_access;
1244 ---------------------------------------------------------------------
1245 -- PROCEDURE
1246 -- update_object_owner
1247 --
1248 -- HISTORY
1249 -- 02/12/2001 skarumur Create.
1250 ----------------------------------------------------------------------
1251 PROCEDURE update_object_owner(
1252 p_api_version IN NUMBER,
1253 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1254 p_commit IN VARCHAR2 := FND_API.g_false,
1255 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1256 x_return_status OUT NOCOPY VARCHAR2,
1257 x_msg_count OUT NOCOPY NUMBER,
1258 x_msg_data OUT NOCOPY VARCHAR2,
1259 p_object_type IN VARCHAR2,
1260 p_object_id IN NUMBER,
1261 p_resource_id IN NUMBER,
1262 p_old_resource_id IN NUMBER
1263 )
1264 IS
1265 l_api_version CONSTANT NUMBER := 1.0;
1266 l_api_name CONSTANT VARCHAR2(30) := 'update_owner';
1267 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1268
1269 l_return_status VARCHAR2(1);
1270 -- added by julou 14-dec-2001 check for the existence of new owner
1271 l_access_id NUMBER;
1272 l_obj_ver_no NUMBER;
1273
1274 CURSOR c_user_exists IS
1275 SELECT activity_access_id, object_version_number
1276 FROM ams_act_access
1277 WHERE act_access_to_object_id = p_object_id
1278 AND arc_act_access_to_object = p_object_type
1279 AND user_or_role_id = p_resource_id
1280 AND arc_user_or_role_type = 'USER';
1281 BEGIN
1282
1283 -------------------- initialize -------------------------
1284 SAVEPOINT update_owner;
1285
1286 IF (AMS_DEBUG_HIGH_ON) THEN
1287
1288
1289
1290 AMS_Utility_PVT.debug_message(l_full_name||': start');
1291
1292 END IF;
1293
1294 IF FND_API.to_boolean(p_init_msg_list) THEN
1295 FND_MSG_PUB.initialize;
1296 END IF;
1297
1298 IF NOT FND_API.compatible_api_call(
1299 l_api_version,
1300 p_api_version,
1301 l_api_name,
1302 g_pkg_name
1303 ) THEN
1304 RAISE FND_API.g_exc_unexpected_error;
1305 END IF;
1306
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 ----------------------- validate ----------------------
1310 IF (AMS_DEBUG_HIGH_ON) THEN
1311
1312 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
1313 END IF;
1314
1315 -- added by julou 14-DEC-2001 check existence of new owner
1316 OPEN c_user_exists;
1317 FETCH c_user_exists INTO l_access_id, l_obj_ver_no;
1318 CLOSE c_user_exists;
1319
1320 IF l_access_id IS NOT NULL THEN -- the user already in access list
1321 delete_access( -- remove it first to avoid unique constraint violation
1322 p_api_version => p_api_version,
1323 p_init_msg_list => p_init_msg_list,
1324 p_commit => p_commit,
1325 p_validation_level => p_validation_level,
1326 x_return_status => x_return_status,
1327 x_msg_count => x_msg_count,
1328 x_msg_data => x_msg_data,
1329 p_access_id => l_access_id,
1330 p_object_version => l_obj_ver_no
1331 );
1332 -- end of code added by julou
1333 END IF; -- added by sveerave on 06-jun-2002
1334 -- ELSE -- update owner to new resource
1335 UPDATE ams_act_access SET
1336 last_update_date = SYSDATE,
1337 last_updated_by = FND_GLOBAL.user_id,
1338 last_update_login = FND_GLOBAL.conc_login_id,
1339 object_version_number = object_version_number + 1,
1340 act_access_to_object_id = p_object_id,
1341 arc_act_access_to_object = p_object_type,
1342 user_or_role_id = p_resource_id
1343 WHERE act_access_to_object_id = p_object_id
1344 AND arc_act_access_to_object = p_object_type
1345 -- needs user_or_role_id to be specified Bug 3578905
1346 -- child budgets have 2 owners. Parent Budget owner is also a owner
1347 -- will result in unique constraint violation
1348 AND user_or_role_id = p_old_resource_id
1349 AND owner_flag = 'Y';
1350
1351 IF (SQL%NOTFOUND) THEN
1352 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1353 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1354 FND_MSG_PUB.add;
1355 END IF;
1356 RAISE FND_API.g_exc_error;
1357 END IF;
1358 -- END IF;
1359
1360 ams_access_denorm_pvt.delete_resource (
1361 p_resource_id => p_old_resource_id,
1362 p_object_id => p_object_id,
1363 p_object_type => p_object_type,
1364 p_edit_metrics => 'Y',
1365 x_return_status => l_return_status,
1366 x_msg_count => x_msg_count,
1367 x_msg_data => x_msg_data
1368 );
1369
1370 IF l_return_status = FND_API.g_ret_sts_error THEN
1371 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1372 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1373 FND_MSG_PUB.add;
1374 RAISE FND_API.g_exc_error;
1375 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1376 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_PROCESS_FAILED');
1377 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1378 FND_MSG_PUB.add;
1379 RAISE FND_API.g_exc_unexpected_error;
1380 END IF;
1381
1382 ams_access_denorm_pvt.insert_resource (
1383 p_resource_id => p_resource_id,
1384 p_object_id => p_object_id,
1385 p_object_type => p_object_type,
1386 p_edit_metrics => 'Y',
1387 x_return_status => l_return_status,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data
1390 );
1391
1392 IF l_return_status = FND_API.g_ret_sts_error THEN
1393 FND_MESSAGE.set_name('AMS', 'AMS_DENORM_PROCESS_FAILED');
1394 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1395 FND_MSG_PUB.add;
1396 RAISE FND_API.g_exc_error;
1397 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1398 FND_MESSAGE.set_name('AMS', 'AMS_INSERT_DENORM_FAILED');
1399 --FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1400 FND_MSG_PUB.add;
1401 RAISE FND_API.g_exc_unexpected_error;
1402 END IF;
1403
1404 -------------------- finish --------------------------
1405 IF FND_API.to_boolean(p_commit) THEN
1406 COMMIT;
1407 END IF;
1408
1409 FND_MSG_PUB.count_and_get(
1410 p_encoded => FND_API.g_false,
1411 p_count => x_msg_count,
1412 p_data => x_msg_data
1413 );
1414
1415 IF (AMS_DEBUG_HIGH_ON) THEN
1416
1417
1418
1419 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1420
1421 END IF;
1422
1423 EXCEPTION
1424
1425 WHEN FND_API.g_exc_error THEN
1426 ROLLBACK TO update_owner;
1427 x_return_status := FND_API.g_ret_sts_error;
1428 FND_MSG_PUB.count_and_get(
1429 p_encoded => FND_API.g_false,
1430 p_count => x_msg_count,
1431 p_data => x_msg_data
1432 );
1433
1434 WHEN FND_API.g_exc_unexpected_error THEN
1435 ROLLBACK TO update_owner;
1436 x_return_status := FND_API.g_ret_sts_unexp_error ;
1437 FND_MSG_PUB.count_and_get(
1438 p_encoded => FND_API.g_false,
1439 p_count => x_msg_count,
1440 p_data => x_msg_data
1441 );
1442
1443 WHEN OTHERS THEN
1444 ROLLBACK TO update_owner;
1445 x_return_status := FND_API.g_ret_sts_unexp_error ;
1446
1447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1448 THEN
1449 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1450 END IF;
1451
1452 FND_MSG_PUB.count_and_get(
1453 p_encoded => FND_API.g_false,
1454 p_count => x_msg_count,
1455 p_data => x_msg_data
1456 );
1457
1458 END update_object_owner;
1459 --------------------------------------------------------------------
1460 -- PROCEDURE
1461 -- validate_access
1462 --
1463 -- HISTORY
1464 -- 10/12/99 abhola Create.
1465 --------------------------------------------------------------------
1466 PROCEDURE validate_access(
1467 p_api_version IN NUMBER,
1468 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1469 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1470
1471 x_return_status OUT NOCOPY VARCHAR2,
1472 x_msg_count OUT NOCOPY NUMBER,
1473 x_msg_data OUT NOCOPY VARCHAR2,
1474
1475 p_access_rec IN access_rec_type
1476 )
1477 IS
1478
1479 l_api_version CONSTANT NUMBER := 1.0;
1480 l_api_name CONSTANT VARCHAR2(30) := 'validate_access';
1481 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1482 l_return_status VARCHAR2(1);
1483
1484 CURSOR c_check_dup (l_obj_id IN NUMBER,
1485 l_obj_type IN VARCHAR2,
1486 l_user_id IN NUMBER,
1487 l_user_type IN VARCHAR2)
1488 IS
1489 SELECT 'x'
1490 FROM ams_act_access
1491 WHERE act_access_to_object_id = l_obj_id
1492 AND arc_act_access_to_object = l_obj_type
1493 AND user_or_role_id = l_user_id
1494 AND arc_user_or_role_type = l_user_type
1495 AND delete_flag='N';
1496
1497 l_local_x varchar2(2);
1498
1499 BEGIN
1500
1501 ----------------------- initialize --------------------
1502 IF (AMS_DEBUG_HIGH_ON) THEN
1503
1504 AMS_Utility_PVT.debug_message(l_full_name||': start validate');
1505 END IF;
1506
1507 IF FND_API.to_boolean(p_init_msg_list) THEN
1508 FND_MSG_PUB.initialize;
1509 END IF;
1510
1511 IF NOT FND_API.compatible_api_call(
1512 l_api_version,
1513 p_api_version,
1514 l_api_name,
1515 g_pkg_name
1516 ) THEN
1517 RAISE FND_API.g_exc_unexpected_error;
1518 END IF;
1519
1520 x_return_status := FND_API.g_ret_sts_success;
1521
1522 ---------------------- validate ------------------------
1523 IF (AMS_DEBUG_HIGH_ON) THEN
1524
1525 AMS_Utility_PVT.debug_message(l_full_name||': check items');
1526 END IF;
1527
1528 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1529 check_access_items(
1530 p_access_rec => p_access_rec,
1531 p_validation_mode => JTF_PLSQL_API.g_create,
1532 x_return_status => l_return_status
1533 );
1534
1535 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1536 RAISE FND_API.g_exc_unexpected_error;
1537 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1538 RAISE FND_API.g_exc_error;
1539 END IF;
1540 END IF;
1541
1542 IF (AMS_DEBUG_HIGH_ON) THEN
1543
1544
1545
1546 AMS_Utility_PVT.debug_message(l_full_name||': check record');
1547
1548 END IF;
1549
1550 -- Check for Dupliacate Records -----
1551
1552 OPEN c_check_dup( p_access_rec.act_access_to_object_id,
1553 p_access_rec.arc_act_access_to_object,
1554 p_access_rec.user_or_role_id,
1555 p_access_rec.arc_user_or_role_type);
1556
1557 FETCH c_check_dup into l_local_x;
1558
1559 IF (c_check_dup%FOUND) THEN
1560 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1561 THEN
1562 IF (p_access_rec.arc_user_or_role_type = 'USER')
1563 THEN
1564 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_DUP_USER');
1565 ELSE
1566 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_DUP_GRP');
1567 END IF;
1568 FND_MSG_PUB.add;
1569 END IF;
1570 CLOSE c_check_dup;
1571 RAISE FND_API.g_exc_error;
1572 END IF;
1573 CLOSE c_check_dup;
1574 -------------------- finish --------------------------
1575 IF (AMS_DEBUG_HIGH_ON) THEN
1576
1577 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1578 END IF;
1579
1580 EXCEPTION
1581
1582 WHEN FND_API.g_exc_error THEN
1583 x_return_status := FND_API.g_ret_sts_error;
1584 FND_MSG_PUB.count_and_get(
1585 p_encoded => FND_API.g_false,
1586 p_count => x_msg_count,
1587 p_data => x_msg_data
1588 );
1589
1590 WHEN FND_API.g_exc_unexpected_error THEN
1591 x_return_status := FND_API.g_ret_sts_unexp_error ;
1592 FND_MSG_PUB.count_and_get(
1593 p_encoded => FND_API.g_false,
1594 p_count => x_msg_count,
1595 p_data => x_msg_data
1596 );
1597
1598 WHEN OTHERS THEN
1599 x_return_status := FND_API.g_ret_sts_unexp_error;
1600 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1601 THEN
1602 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1603 END IF;
1604
1605 FND_MSG_PUB.count_and_get(
1606 p_encoded => FND_API.g_false,
1607 p_count => x_msg_count,
1608 p_data => x_msg_data
1609 );
1610
1611 END validate_access;
1612
1613 ---------------------------------------------------------------------
1614 -- FUNCTION
1615 -- check_owner
1616 -- check whether the input user is the owner of the activity.
1617 -- HISTORY
1618 -- 7/13/00 abhola Create.
1619 -- 7/26/00 holiu Expose as needed in amscampv.jsp .
1620 ---------------------------------------------------------------------
1621 FUNCTION check_owner(
1622 p_object_id IN NUMBER,
1623 p_object_type IN VARCHAR2,
1624 p_user_or_role_id IN NUMBER,
1625 p_user_or_role_type IN VARCHAR2
1626 )
1627 RETURN VARCHAR2
1628 IS
1629 CURSOR cur_is_owner IS
1630 SELECT 'Y'
1631 FROM ams_act_access
1632 WHERE user_or_role_id = p_user_or_role_id
1633 AND arc_user_or_role_type = 'USER'
1634 AND arc_act_access_to_object = p_object_type
1635 AND act_access_to_object_id = p_object_id
1636 AND owner_flag = 'Y';
1637
1638 l_is_owner VARCHAR2(1) := 'N';
1639
1640 BEGIN
1641
1642 OPEN cur_is_owner;
1643 FETCH cur_is_owner INTO l_is_owner;
1644 CLOSE cur_is_owner;
1645
1646 return l_is_owner;
1647
1648 END check_owner;
1649
1650 --=============================================================================================
1651 -- NAME
1652 -- Find_Groups
1653 --
1654 -- PURPOSE
1655 -- This Procedure will Find the group the user belongs to .
1656 -- It will also fing all the parent groups of the user' group.
1657 -- It will return all the groups in pl sql table.
1658 -- NOTES
1659 --
1660 -- HISTORY
1661 -- 18-Jul-1999 ptendulk Created
1662 -- 03-Feb-2001 ptendulk Modified cursor to get all groups , use the denorm table.
1663 -- Refer bug # 1626705
1664 -- 02-12-2001 skarumur This method is depricated. Leaving to support prior releases
1665 --=============================================================================================
1666 PROCEDURE Find_Groups (p_user_type IN VARCHAR2,
1667 p_user_id IN NUMBER,
1668 x_grp_tab OUT NOCOPY t_grp)
1669 IS
1670 --
1671 -- Cursor to Find all the group the user is in
1672 --
1673 CURSOR c_all_grp IS
1674 SELECT group_id
1675 FROM jtf_rs_group_members
1676 WHERE resource_id = p_user_id
1677 AND delete_flag = 'N' ;
1678
1679 --
1680 -- Find the all the parents of the group the user is in
1681 --
1682 -- Following code is commented by ptendulk on 03-Feb-2001
1683 -- Use the denorm table to get all parent groups.
1684 -- CURSOR c_parent_grp(c_grp_no NUMBER) IS
1685 -- SELECT related_group_id
1686 -- FROM jtf_rs_grp_relations
1687 -- WHERE delete_flag = 'N'
1688 -- START WITH group_id = c_grp_no
1689 -- CONNECT BY PRIOR related_group_id = group_id
1690 -- AND delete_flag = 'N' ;
1691 -- End of code is commented by ptendulk on 03-Feb-2001
1692
1693 CURSOR c_parent_grp(c_grp_no NUMBER) IS
1694 SELECT parent_group_id
1695 FROM jtf_rs_groups_denorm
1696 WHERE group_id = c_grp_no
1697 AND NVL(start_date_active,SYSDATE) = SYSDATE
1698 AND NVL(end_date_active,SYSDATE) = SYSDATE ;
1699
1700 l_temp NUMBER := 0 ;
1701 l_temp_grp NUMBER;
1702
1703 l_grp_tab t_grp ;
1704 BEGIN
1705
1706 IF p_user_type <> 'GROUP' THEN
1707
1708 FOR c_all_grp_rec IN c_all_grp
1709 LOOP
1710 l_grp_tab(l_temp) := c_all_grp_rec.group_id ;
1711 l_temp := l_temp + 1 ;
1712 OPEN c_parent_grp(c_all_grp_rec.group_id);
1713 LOOP
1714 FETCH c_parent_grp INTO l_temp_grp ;
1715 EXIT WHEN c_parent_grp%NOTFOUND ;
1716 IF l_temp_grp IS NOT NULL THEN
1717 l_grp_tab(l_temp) := l_temp_grp ;
1718 l_temp := l_temp + 1 ;
1719 END IF ;
1720 END LOOP;
1721 CLOSE c_parent_grp ;
1722 -- 09/13/00 holiu: remove the following line
1723 -- l_temp := l_temp + 1 ;
1724 END LOOP ;
1725
1726 x_grp_tab := l_grp_tab ;
1727 ELSE
1728 OPEN c_parent_grp(p_user_id);
1729 LOOP
1730 FETCH c_parent_grp INTO l_temp_grp ;
1731 EXIT WHEN c_parent_grp%NOTFOUND ;
1732 l_grp_tab(l_temp) := l_temp_grp ;
1733 l_temp := l_temp + 1 ;
1734 END LOOP;
1735 CLOSE c_parent_grp ;
1736 END IF;
1737 x_grp_tab := l_grp_tab ;
1738
1739 END ;
1740
1741 ---------------------------------------------------------------------
1742 -- FUNCTION
1743 -- check_update_Access
1744 --
1745 -- HISTORY
1746 -- 10/12/99 abhola Create.
1747 ---------------------------------------------------------------------
1748 FUNCTION check_update_access(
1749 p_object_id IN NUMBER,
1750 p_object_type IN VARCHAR2,
1751 p_user_or_role_id IN NUMBER,
1752 p_user_or_role_type IN VARCHAR2
1753 )
1754 RETURN VARCHAR2
1755 IS
1756
1757 x_access VARCHAR2(1) := 'N';
1758 x_return_full_priv VARCHAR2(1) := 'F';
1759
1760 CURSOR cur_check_access IS
1761 SELECT decode(edit_metrics_yn,'Y','F','R')
1762 FROM ams_act_access_denorm
1763 WHERE resource_id = p_user_or_role_id
1764 AND object_id = p_object_id
1765 AND object_type = p_object_type;
1766
1767 BEGIN
1768
1769 IF Check_Admin_Access(p_user_or_role_id) or p_object_type = 'OFFR' THEN
1770 RETURN x_return_full_priv ;
1771 END IF ;
1772
1773 open cur_check_access;
1774 fetch cur_check_access into x_access;
1775 close cur_check_access;
1776
1777 RETURN x_access;
1778
1779 END check_update_access;
1780
1781 FUNCTION get_source_code(
1782 p_object_type IN VARCHAR2,
1783 p_object_id IN NUMBER
1784 )
1785 RETURN VARCHAR2
1786 IS
1787 l_source_code varchar2(30);
1788
1789 CURSOR cur_get_source_code IS
1790 SELECT source_code
1791 FROM ams_source_codes
1792 WHERE source_code_for_id = p_object_id
1793 AND arc_source_code_for = p_object_type;
1794
1795 BEGIN
1796
1797 OPEN cur_get_source_code;
1798 FETCH cur_get_source_code INTO l_source_code;
1799 CLOSE cur_get_source_code;
1800
1801 RETURN l_source_code;
1802
1803 END;
1804
1805
1806 ---------------------------------------------------------------------
1807 -- PROCEDURE
1808 -- check_view_Access
1809 --
1810 -- HISTORY
1811 -- 10/12/99 abhola Create.
1812 ---------------------------------------------------------------------
1813 FUNCTION check_view_access(
1814 p_object_id IN NUMBER,
1815 p_object_type IN VARCHAR2,
1816 p_user_or_role_id IN NUMBER,
1817 p_user_or_role_type IN VARCHAR2
1818 )
1819
1820 RETURN VARCHAR2
1821 IS
1822
1823 x_access VARCHAR2(1);
1824
1825 cursor c_access_camp (
1826 c_act_access_to_object_id IN ams_act_access.act_access_to_object_id%TYPE)
1827 IS
1828 SELECT private_flag
1829 FROM ams_campaigns_all_b
1830 --FROM ams_campaigns_vl
1831 -- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
1832 WHERE campaign_id = c_act_access_to_object_id;
1833
1834 cursor c_access_eveo (
1835 c_act_access_to_object_id IN ams_act_access.act_access_to_object_id%TYPE)
1836 IS
1837 SELECT private_flag
1838 -- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
1839 --FROM ams_event_offers_vl
1840 FROM ams_event_offers_all_b
1841 WHERE event_offer_id = c_act_access_to_object_id;
1842
1843 cursor c_access_eveh (
1844 c_act_access_to_object_id IN ams_act_access.act_access_to_object_id%TYPE)
1845 IS
1846 SELECT private_flag
1847 -- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
1848 -- FROM ams_event_headers_vl
1849 FROM ams_event_headers_all_b
1850 WHERE event_header_id = c_act_access_to_object_id;
1851
1852 cursor c_access_delv (
1853 c_act_access_to_object_id IN ams_act_access.act_access_to_object_id%TYPE)
1854 IS
1855 SELECT private_flag
1856 FROM ams_deliverables_vl
1857 WHERE deliverable_id = c_act_access_to_object_id;
1858
1859 cursor c_access (
1860 c_act_access_to_object_id IN ams_act_access.act_access_to_object_id%TYPE,
1861 c_arc_act_access_to_object IN ams_act_access.arc_act_access_to_object%TYPE,
1862 c_user_or_role_id IN ams_act_access.user_or_role_id%TYPE,
1863 c_arc_user_or_role_type IN ams_act_access.arc_user_or_role_type%TYPE )
1864 IS
1865 SELECT admin_flag
1866 FROM ams_act_access
1867 WHERE act_access_to_object_id = c_act_access_to_object_id
1868 AND arc_act_access_to_object = c_arc_act_access_to_object
1869 AND user_or_role_id = c_user_or_role_id
1870 AND arc_user_or_role_type = c_arc_user_or_role_type;
1871
1872 -- 07/16/00 holiu: check group access for user
1873 --=============================================================================
1874 -- Following code is commented by ptendulk on 18Jul2000
1875 --=============================================================================
1876 -- CURSOR c_group_access IS
1877 -- SELECT 1
1878 -- FROM DUAL
1879 -- WHERE EXISTS(
1880 -- SELECT 1
1881 -- FROM ams_act_access A, jtf_rs_group_members_vl B
1882 -- WHERE A.arc_act_access_to_object = p_object_type
1883 -- AND A.act_access_to_object_id = p_object_id
1884 -- AND A.arc_user_or_role_type = 'GROUP'
1885 -- AND (A.active_from_date IS NULL OR A.active_from_date <= SYSDATE)
1886 -- AND (A.active_to_date IS NULL OR A.active_to_date >= SYSDATE)
1887 -- AND A.user_or_role_id = B.group_id
1888 -- AND B.delete_flag = 'N'
1889 -- AND B.resource_id = p_user_or_role_id);
1890
1891 CURSOR c_group_access(c_grp_id NUMBER) IS
1892 SELECT 1
1893 FROM DUAL
1894 WHERE EXISTS(
1895 SELECT 1
1896 FROM ams_act_access
1897 WHERE arc_act_access_to_object = p_object_type
1898 AND act_access_to_object_id = p_object_id
1899 AND arc_user_or_role_type = 'GROUP'
1900 AND (active_from_date IS NULL OR active_from_date <= SYSDATE)
1901 AND (active_to_date IS NULL OR active_to_date >= SYSDATE)
1902 AND user_or_role_id = c_grp_id) ;
1903
1904 l_private_flag varchar2(1);
1905 l_admin_flag varchar2(1);
1906 l_is_owner varchar2(1);
1907 l_dummy NUMBER;
1908 l_grp_tab t_grp ;
1909 BEGIN
1910 --===================================================================
1911 -- Following code is added by ptendulk on 01-Sep-2000
1912 -- Give the full permission for the admin user
1913 --===================================================================
1914 IF Check_Admin_Access(p_user_or_role_id) THEN
1915 RETURN 'Y' ;
1916 END IF ;
1917
1918 ----------- check wheteher the input user is the owner -----
1919
1920
1921 l_is_owner := check_owner(p_object_id ,
1922 p_object_type ,
1923 p_user_or_role_id ,
1924 p_user_or_role_type);
1925
1926 if ( l_is_owner = 'Y') then
1927 return l_is_owner;
1928 else
1929
1930 x_access := 'N';
1931
1932 if (p_object_type = 'CAMP') then
1933 open c_access_camp(p_object_id);
1934 fetch c_access_camp into l_private_flag;
1935
1936 if (c_access_camp%NOTFOUND) then
1937 close c_access_camp;
1938
1939 return x_access;
1940 end if;
1941
1942 close c_access_camp;
1943
1944 elsif (p_object_type = 'EVEO' ) then
1945 open c_access_eveo(p_object_id);
1946 fetch c_access_eveo into l_private_flag;
1947
1948 if (c_access_eveo%NOTFOUND) then
1949 close c_access_eveo;
1950
1951 return x_access;
1952 end if;
1953
1954 close c_access_eveo;
1955
1956 elsif (p_object_type = 'EVEH' ) then
1957 open c_access_eveh(p_object_id);
1958 fetch c_access_eveh into l_private_flag;
1959
1960 if (c_access_eveh%NOTFOUND) then
1961 close c_access_eveh;
1962
1963 return x_access;
1964 end if;
1965
1966 close c_access_eveh;
1967
1968 elsif (p_object_type = 'DELV' ) then
1969 open c_access_delv(p_object_id);
1970 fetch c_access_delv into l_private_flag;
1971
1972 if (c_access_delv%NOTFOUND) then
1973 close c_access_delv;
1974
1975 return x_access;
1976 end if;
1977
1978 close c_access_delv;
1979
1980 elsif (p_object_type = 'FUND' ) then
1981
1982 l_private_flag := 'Y';
1983
1984 end if;
1985
1986 if (l_private_flag = 'N') then
1987 x_access := 'Y';
1988 else
1989 open c_access( p_object_id ,
1990 p_object_type ,
1991 p_user_or_role_id ,
1992 p_user_or_role_type );
1993
1994 fetch c_access into l_admin_flag;
1995 if (c_access%NOTFOUND) then
1996 x_access := 'N';
1997 else
1998 x_access := 'Y';
1999 end if;
2000 close c_access;
2001 end if;
2002
2003 end if;
2004
2005 -- 07/16/00 holiu: check group access for user
2006 IF x_access = 'Y' OR p_user_or_role_type = 'GROUP' THEN
2007 RETURN x_access;
2008 ELSE
2009
2010 Find_Groups (p_user_type => 'USER',
2011 p_user_id => p_user_or_role_id ,
2012 x_grp_tab => l_grp_tab) ;
2013
2014 IF l_grp_tab.first IS NOT null THEN
2015 FOR i IN l_grp_tab.first..l_grp_tab.last
2016 LOOP
2017
2018 OPEN c_group_access(l_grp_tab(i));
2019 FETCH c_group_access INTO l_dummy ;
2020 CLOSE c_group_access;
2021 IF l_dummy = 1 THEN
2022 RETURN 'Y';
2023 END IF;
2024 END LOOP;
2025 END IF;
2026
2027 RETURN 'N' ;
2028
2029 END IF;
2030 END check_view_access;
2031
2032
2033 ---------------------------------------------------------------------
2034 -- PROCEDURE
2035 -- check_Access_req_items
2036 --
2037 -- HISTORY
2038 -- 10/12/99 abhola Create.
2039 ---------------------------------------------------------------------
2040 PROCEDURE check_Access_req_items(
2041 p_Access_rec IN Access_rec_type,
2042 x_return_status OUT NOCOPY VARCHAR2
2043 )
2044 IS
2045
2046 CURSOR c1(p_inp_user_id IN NUMBER,
2047 p_inp_user_type IN VARCHAR2,
2048 p_inp_obj_id IN NUMBER,
2049 p_inp_obj_type IN VARCHAR2
2050 ) is SELECT 'x'
2051 FROM ams_act_access
2052 WHERE user_or_role_id = p_inp_user_id
2053 AND arc_user_or_role_type = p_inp_user_type
2054 AND act_access_to_object_id = p_inp_obj_id
2055 AND arc_act_access_to_object= p_inp_obj_type
2056 AND delete_flag = 'N' ;
2057
2058 l_var1 VARCHAR2(3);
2059 BEGIN
2060
2061 IF (AMS_DEBUG_HIGH_ON) THEN
2062
2063
2064
2065 AMS_Utility_PVT.debug_message(' start req item');
2066
2067 END IF;
2068
2069 x_return_status := FND_API.g_ret_sts_success;
2070
2071 ------------------------ user_status_id --------------------------
2072 IF p_Access_rec.act_access_to_object_id IS NULL THEN
2073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2074 THEN
2075 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_NO_OBJECT_ID');
2076 FND_MSG_PUB.add;
2077 END IF;
2078
2079 x_return_status := FND_API.g_ret_sts_error;
2080 RETURN;
2081 END IF;
2082
2083 IF p_Access_rec.arc_act_access_to_object IS NULL THEN
2084 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2085 THEN
2086 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_NO_OBJECT');
2087 FND_MSG_PUB.add;
2088 END IF;
2089
2090 x_return_status := FND_API.g_ret_sts_error;
2091 RETURN;
2092 END IF;
2093
2094 IF p_Access_rec.user_or_role_id IS NULL THEN
2095 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2096 THEN
2097 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_NO_UR_ID');
2098 FND_MSG_PUB.add;
2099 END IF;
2100
2101 x_return_status := FND_API.g_ret_sts_error;
2102 RETURN;
2103 END IF;
2104
2105 IF p_Access_rec.arc_user_or_role_type IS NULL THEN
2106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2107 THEN
2108 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_NO_UR_TYPE');
2109 FND_MSG_PUB.add;
2110 END IF;
2111
2112 x_return_status := FND_API.g_ret_sts_error;
2113 RETURN;
2114 END IF;
2115
2116 IF (AMS_DEBUG_HIGH_ON) THEN
2117
2118
2119
2120 AMS_Utility_PVT.debug_message(': check uniquness of record');
2121
2122 END IF;
2123
2124 IF p_Access_rec.user_or_role_id IS NOT NULL AND p_access_rec.object_version_number IS NULL THEN
2125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2126 THEN
2127 OPEN c1( p_Access_rec.user_or_role_id
2128 , p_Access_rec.arc_user_or_role_type
2129 , p_Access_rec.act_access_to_object_id
2130 , p_Access_rec.arc_act_access_to_object);
2131 FETCH c1 into l_var1;
2132
2133 if (c1%FOUND) then
2134 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2135 THEN
2136 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_DUP_USER');
2137 FND_MSG_PUB.add;
2138 END IF;
2139 x_return_status := FND_API.g_ret_sts_error;
2140 RETURN;
2141 end if;
2142
2143 END IF;
2144
2145 END IF;
2146 IF (AMS_DEBUG_HIGH_ON) THEN
2147
2148 AMS_Utility_PVT.debug_message(': check uniquness of record end ');
2149 END IF;
2150
2151 END check_Access_req_items;
2152 ---------------------------------------------------------------------
2153 -- PROCEDURE
2154 -- check_Access_uk_items
2155 --
2156 -- HISTORY
2157 -- 10/12/99 abhola Create.
2158 ---------------------------------------------------------------------
2159 PROCEDURE check_Access_uk_items(
2160 p_Access_rec IN Access_rec_type,
2161 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2162 x_return_status OUT NOCOPY VARCHAR2
2163 )
2164 IS
2165 l_valid_flag VARCHAR2(1);
2166 BEGIN
2167
2168 x_return_status := FND_API.g_ret_sts_success;
2169
2170 -- For create_Access, when Access_id is passed in, we need to
2171 -- check if this Access_id is unique.
2172 --
2173 IF p_validation_mode = JTF_PLSQL_API.g_create
2174 AND p_Access_rec.activity_access_id IS NOT NULL
2175 THEN
2176 IF AMS_Utility_PVT.check_uniqueness(
2177 'ams_act_access',
2178 'activity_access_id = ' || p_Access_rec.activity_access_id
2179 ) = FND_API.g_false
2180 THEN
2181 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2182 THEN
2183 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_DUPLICATE_ID');
2184 FND_MSG_PUB.add;
2185 END IF;
2186 x_return_status := FND_API.g_ret_sts_error;
2187 RETURN;
2188 END IF;
2189 END IF;
2190
2191 END check_Access_uk_items;
2192
2193
2194 ---------------------------------------------------------------------
2195 -- PROCEDURE
2196 -- check_Access_fk_items
2197 --
2198 -- HISTORY
2199 -- 10/12/99 abhola Create.
2200 ---------------------------------------------------------------------
2201 PROCEDURE check_Access_fk_items(
2202 p_Access_rec IN Access_rec_type,
2203 x_return_status OUT NOCOPY VARCHAR2
2204 )
2205 IS
2206 BEGIN
2207
2208 x_return_status := FND_API.g_ret_sts_success;
2209
2210 ------------------user or role id ---------------------------------------
2211 IF p_Access_rec.user_or_role_id <> FND_API.g_miss_num THEN
2212
2213 if UPPER(p_Access_rec.arc_user_or_role_type) = 'USER' THEN
2214
2215 IF AMS_Utility_PVT.check_fk_exists(
2216 'ams_jtf_rs_emp_v',
2217 'RESOURCE_ID',
2218 p_Access_rec.user_or_role_id
2219 ) = FND_API.g_false
2220 THEN
2221 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2222 THEN
2223 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_BAD_USER_ID');
2224 FND_MSG_PUB.add;
2225 END IF;
2226
2227 x_return_status := FND_API.g_ret_sts_error;
2228 RETURN;
2229 END IF;
2230 END IF;
2231
2232
2233 if UPPER(p_Access_rec.arc_user_or_role_type) = 'GROUP' THEN
2234
2235 IF AMS_Utility_PVT.check_fk_exists(
2236 -- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
2237 --'JTF_RS_GROUPS_VL',
2238 'JTF_RS_GROUPS_B',
2239 'GROUP_ID',
2240 p_Access_rec.user_or_role_id
2241 ) = FND_API.g_false
2242 THEN
2243 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2244 THEN
2245 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_BAD_GROUP_ID');
2246 FND_MSG_PUB.add;
2247 END IF;
2248
2249 x_return_status := FND_API.g_ret_sts_error;
2250 RETURN;
2251 END IF;
2252 END IF;
2253
2254
2255
2256
2257
2258
2259 END IF;
2260
2261 END check_Access_fk_items;
2262
2263 ---------------------------------------------------------------------
2264 -- PROCEDURE
2265 -- check_Access_lookup_items
2266 --
2267 -- HISTORY
2268 -- 10/12/99 abhola Create.
2269 ---------------------------------------------------------------------
2270 PROCEDURE check_Access_lookup_items(
2271 p_Access_rec IN Access_rec_type,
2272 x_return_status OUT NOCOPY VARCHAR2
2273 )
2274 IS
2275 BEGIN
2276
2277 x_return_status := FND_API.g_ret_sts_success;
2278
2279 ----------------------- access to object ------------------------
2280 IF p_Access_rec.arc_act_access_to_object <> FND_API.g_miss_char THEN
2281
2282 /*
2283 IF AMS_Utility_PVT.check_lookup_exists(
2284 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
2285 p_lookup_code => p_Access_rec.arc_act_access_to_object
2286 ) = FND_API.g_false
2287 THEN
2288 */
2289 /*
2290 IF AMS_Utility_PVT.check_lookup_exists(
2291 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
2292 p_lookup_code => p_Access_rec.arc_user_or_role_type,
2293 p_view_application_id => fnd_global.resp_appl_id
2294 ) = FND_API.g_false
2295 THEN
2296 */
2297 -- Correcting the p_lookup_code value getting passed for bug# 2419540
2298 -- Team concept is being used by mulitple applications. In some cases
2299 -- we are relying that object exists in owning application, for e.g.
2300 -- MDF in PV relies on assumption that Campaing is defined in AMS,
2301 -- and in other cases, we assume that responsibility's application owns this
2302 -- object, i.e. in case of programs, it should in in PV.
2303 -- So, changing code to check in 530 if not found in appl_id. -- bug#2421583
2304 IF AMS_Utility_PVT.check_lookup_exists(
2305 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
2306 p_lookup_code => p_Access_rec.arc_act_access_to_object,
2307 p_view_application_id => fnd_global.resp_appl_id
2308 ) = FND_API.g_false
2309 THEN
2310 IF AMS_Utility_PVT.check_lookup_exists(
2311 p_lookup_type => 'AMS_SYS_ARC_QUALIFIER',
2312 p_lookup_code => p_Access_rec.arc_act_access_to_object,
2313 p_view_application_id => 530
2314 ) = FND_API.g_false
2315 THEN
2316 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2317 THEN
2318 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_BAD_SYS_ARC');
2319 FND_MSG_PUB.add;
2320 END IF;
2321 x_return_status := FND_API.g_ret_sts_error;
2322 RETURN;
2323 END IF;
2324 END IF;
2325 END IF;
2326
2327 ------------------user or role type ---------------------------------------
2328 IF p_Access_rec.arc_user_or_role_type <> FND_API.g_miss_char THEN
2329 /*
2330 IF AMS_Utility_PVT.check_lookup_exists(
2331 p_lookup_type => 'AMS_ACCESS_TYPE',
2332 p_lookup_code => p_Access_rec.arc_user_or_role_type
2333 ) = FND_API.g_false
2334 THEN
2335 */
2336 -- User and Group lookups should exist only in AMS, and hence using 530
2337 IF AMS_Utility_PVT.check_lookup_exists(
2338 p_lookup_type => 'AMS_ACCESS_TYPE',
2339 p_lookup_code => p_Access_rec.arc_user_or_role_type,
2340 p_view_application_id => 530
2341 ) = FND_API.g_false
2342 THEN
2343 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2344 THEN
2345 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_BAD_USER_TYPE');
2346 FND_MSG_PUB.add;
2347 END IF;
2348
2349 x_return_status := FND_API.g_ret_sts_error;
2350 RETURN;
2351 END IF;
2352 END IF;
2353
2354 END check_Access_lookup_items;
2355
2356
2357 ---------------------------------------------------------------------
2358 -- PROCEDURE
2359 -- check_Access_flag_items
2360 --
2361 -- HISTORY
2362 -- 10/12/99 abhola Create.
2363 ---------------------------------------------------------------------
2364 PROCEDURE check_Access_flag_items(
2365 p_Access_rec IN Access_rec_type,
2366 x_return_status OUT NOCOPY VARCHAR2
2367 )
2368 IS
2369 BEGIN
2370
2371 x_return_status := FND_API.g_ret_sts_success;
2372
2373 ----------------------- admin_flag ------------------------
2374 IF p_Access_rec.admin_flag <> FND_API.g_miss_char
2375 AND p_Access_rec.admin_flag IS NOT NULL
2376 THEN
2377 IF AMS_Utility_PVT.is_Y_or_N(p_access_rec.admin_flag) = FND_API.g_false
2378 THEN
2379 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2380 THEN
2381 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_BAD_ADMIN_FLAG');
2382 FND_MSG_PUB.add;
2383 END IF;
2384
2385 x_return_status := FND_API.g_ret_sts_error;
2386 RETURN;
2387 END IF;
2388 END IF;
2389
2390 -- check other flags
2391
2392 END check_Access_flag_items;
2393 ---------------------------------------------------------------------
2394 -- PROCEDURE
2395 -- check_Access_items
2396 --
2397 -- HISTORY
2398 -- 10/12/99 abhola Create.
2399 ---------------------------------------------------------------------
2400 PROCEDURE check_Access_items(
2401 p_access_rec IN Access_rec_type,
2402 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2403 x_return_status OUT NOCOPY VARCHAR2
2404 )
2405 IS
2406 -- cursor added by julou 29-nov-2001 ref. bug 2117645
2407 /*
2408 CURSOR c_user_exist IS
2409 SELECT 1
2410 FROM DUAL
2411 WHERE EXISTS(SELECT 1
2412 FROM ams_act_access
2413 WHERE act_access_to_object_id = p_access_rec.act_access_to_object_id
2414 AND arc_act_access_to_object = p_access_rec.arc_act_access_to_object
2415 AND user_or_role_id = p_access_rec.user_or_role_id
2416 AND arc_user_or_role_type = p_access_rec.arc_user_or_role_type
2417 AND activity_access_id <> p_access_rec.activity_access_id);
2418
2419 l_user_exist NUMBER;
2420 */
2421 BEGIN
2422 x_return_status := FND_API.g_ret_sts_success;
2423
2424 IF (AMS_DEBUG_HIGH_ON) THEN
2425
2426
2427
2428 AMS_Utility_PVT.debug_message(' req item check start');
2429
2430 END IF;
2431 check_Access_req_items(
2432 p_Access_rec => p_Access_rec,
2433 x_return_status => x_return_status
2434 );
2435
2436 IF x_return_status <> FND_API.g_ret_sts_success THEN
2437 RETURN;
2438 END IF;
2439
2440 IF (AMS_DEBUG_HIGH_ON) THEN
2441
2442
2443
2444 AMS_Utility_PVT.debug_message(' req item check success');
2445
2446 END IF;
2447
2448 /* added by sunkumar 03-12-2002 bug id.. 2216520 */
2449 /* check for the uniqueness of entries ams_act_access table if p_access_rec.arc_user_or_role_type='USER' */
2450 /* then only check for the unique constraint on the ams_act_access table */
2451
2452 IF p_access_rec.arc_user_or_role_type='USER' THEN
2453 BEGIN
2454 check_Access_uk_items(
2455 p_Access_rec => p_Access_rec,
2456 p_validation_mode => p_validation_mode,
2457 x_return_status => x_return_status
2458 );
2459
2460
2461 IF (AMS_DEBUG_HIGH_ON) THEN
2462
2463
2464
2465
2466
2467 AMS_Utility_PVT.debug_message(' UK check items success');
2468
2469
2470 END IF;
2471
2472 IF x_return_status <> FND_API.g_ret_sts_success THEN
2473 RETURN;
2474 END IF;
2475 END;
2476 END IF;
2477 check_Access_fk_items(
2478 p_Access_rec => p_Access_rec,
2479 x_return_status => x_return_status
2480 );
2481
2482 IF (AMS_DEBUG_HIGH_ON) THEN
2483
2484
2485
2486 AMS_Utility_PVT.debug_message('FK check items success ');
2487
2488 END IF;
2489
2490 IF x_return_status <> FND_API.g_ret_sts_success THEN
2491 RETURN;
2492 END IF;
2493
2494 check_Access_lookup_items(
2495 p_Access_rec => p_Access_rec,
2496 x_return_status => x_return_status
2497 );
2498
2499 IF x_return_status <> FND_API.g_ret_sts_success THEN
2500 RETURN;
2501 END IF;
2502
2503 check_Access_flag_items(
2504 p_Access_rec => p_Access_rec,
2505 x_return_status => x_return_status
2506 );
2507
2508 IF (AMS_DEBUG_HIGH_ON) THEN
2509
2510
2511
2512 AMS_Utility_PVT.debug_message(' Flag check items success ');
2513
2514 END IF;
2515
2516 IF x_return_status <> FND_API.g_ret_sts_success THEN
2517 RETURN;
2518 END IF;
2519 -- addee by julou 29-nov-2001 ref. bug 2117645
2520 /*
2521 IF p_validation_mode = JTF_PLSQL_API.G_UPDATE THEN
2522 OPEN c_user_exist;
2523 FETCH c_user_exist INTO l_user_exist;
2524 CLOSE c_user_exist;
2525
2526 IF l_user_exist = 1 THEN
2527 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2528 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_USER_EXIST');
2529 FND_MSG_PUB.add;
2530 END IF;
2531 x_return_status := FND_API.g_ret_sts_error;
2532 END IF;
2533 END IF;
2534 */
2535 END check_Access_items;
2536 ---------------------------------------------------------------------
2537 -- PROCEDURE
2538 -- check_Access_record
2539 --
2540 -- HISTORY
2541 -- 10/12/99 abhola Create.
2542 ---------------------------------------------------------------------
2543 PROCEDURE check_Access_record(
2544 p_Access_rec IN Access_rec_type,
2545 p_complete_rec IN Access_rec_type,
2546 x_return_status OUT NOCOPY VARCHAR2
2547 )
2548 IS
2549
2550 l_start_date DATE;
2551 l_end_date DATE;
2552
2553 BEGIN
2554
2555 x_return_status := FND_API.g_ret_sts_success;
2556
2557 IF p_Access_rec.active_from_date <> FND_API.g_miss_date
2558 OR p_Access_rec.active_to_date <> FND_API.g_miss_date
2559 THEN
2560 IF p_Access_rec.active_from_date = FND_API.g_miss_date THEN
2561 l_start_date := p_complete_rec.active_from_date;
2562 ELSE
2563 l_start_date := p_Access_rec.active_from_date;
2564 END IF;
2565
2566 IF p_Access_rec.active_to_date = FND_API.g_miss_date THEN
2567 l_end_date := p_complete_rec.active_to_date;
2568 ELSE
2569 l_end_date := p_Access_rec.active_to_date;
2570 END IF;
2571
2572 IF l_start_date > l_end_date THEN
2573 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2574 THEN
2575 FND_MESSAGE.set_name('AMS', 'AMS_ACCESS_INV_DATES');
2576 FND_MSG_PUB.add;
2577 END IF;
2578 x_return_status := FND_API.g_ret_sts_error;
2579 END IF;
2580 END IF;
2581
2582 -- do other record level checkings
2583
2584 END check_Access_record;
2585
2586
2587 ---------------------------------------------------------------------
2588 -- PROCEDURE
2589 -- init_Access_rec
2590 --
2591 -- HISTORY
2592 -- 10/12/99 abhola Create.
2593 ---------------------------------------------------------------------
2594 PROCEDURE init_Access_rec(
2595 x_Access_rec OUT NOCOPY Access_rec_type
2596 )
2597 IS
2598 BEGIN
2599
2600 x_Access_rec.activity_access_id := FND_API.g_miss_num;
2601 x_Access_rec.last_update_date := FND_API.g_miss_date;
2602 x_Access_rec.last_updated_by := FND_API.g_miss_num;
2603 x_Access_rec.creation_date := FND_API.g_miss_date;
2604 x_Access_rec.created_by := FND_API.g_miss_num;
2605 x_Access_rec.last_update_login := FND_API.g_miss_num;
2606 x_Access_rec.object_version_number := FND_API.g_miss_num;
2607 x_Access_rec.act_access_to_object_id := FND_API.g_miss_num;
2608 x_Access_rec.arc_act_access_to_object := FND_API.g_miss_char;
2609 x_Access_rec.user_or_role_id := FND_API.g_miss_num;
2610 x_Access_rec.arc_user_or_role_type := FND_API.g_miss_char;
2611
2612 x_Access_rec.active_from_date := FND_API.g_miss_date;
2613 x_Access_rec.active_to_date := FND_API.g_miss_date;
2614
2615 x_Access_rec.admin_flag := FND_API.g_miss_char;
2616 x_Access_rec.owner_flag := FND_API.g_miss_char;
2617 x_Access_rec.delete_flag := FND_API.g_miss_char;
2618
2619
2620 END init_Access_rec;
2621
2622
2623 ---------------------------------------------------------------------
2624 -- PROCEDURE
2625 -- complete_Access_rec
2626 --
2627 -- HISTORY
2628 -- 10/12/99 abhola Create.
2629 ---------------------------------------------------------------------
2630 PROCEDURE complete_Access_rec(
2631 p_Access_rec IN Access_rec_type,
2632 x_complete_rec OUT NOCOPY Access_rec_type
2633 )
2634 IS
2635
2636 CURSOR c_Access IS
2637 SELECT *
2638 FROM ams_act_access
2639 WHERE activity_access_id = p_Access_rec.activity_access_id;
2640
2641 l_Access_rec c_Access%ROWTYPE;
2642
2643 BEGIN
2644
2645 x_complete_rec := p_Access_rec;
2646
2647 OPEN c_Access;
2648 FETCH c_Access INTO l_Access_rec;
2649 IF c_Access%NOTFOUND THEN
2650 CLOSE c_Access;
2651 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2652 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2653 FND_MSG_PUB.add;
2654 END IF;
2655 RAISE FND_API.g_exc_error;
2656 END IF;
2657 CLOSE c_access;
2658
2659
2660 IF p_Access_rec.act_access_to_object_id = FND_API.g_miss_num THEN
2661 x_complete_rec.act_access_to_object_id := l_Access_rec.act_access_to_object_id;
2662 END IF;
2663
2664 IF p_Access_rec.arc_act_access_to_object = FND_API.g_miss_char THEN
2665 x_complete_rec.arc_act_access_to_object := l_Access_rec.arc_act_access_to_object;
2666 END IF;
2667
2668 IF p_Access_rec.user_or_role_id = FND_API.g_miss_num THEN
2669 x_complete_rec.user_or_role_id := l_Access_rec.user_or_role_id;
2670 END IF;
2671
2672 IF p_Access_rec.arc_user_or_role_type = FND_API.g_miss_char THEN
2673 x_complete_rec.arc_user_or_role_type := l_Access_rec.arc_user_or_role_type;
2674 END IF;
2675
2676 IF p_Access_rec.active_from_date = FND_API.g_miss_date THEN
2677 x_complete_rec.active_from_date := l_Access_rec.active_from_date;
2678 END IF;
2679
2680 IF p_Access_rec.active_to_date = FND_API.g_miss_date THEN
2681 x_complete_rec.active_to_date := l_Access_rec.active_to_date;
2682 END IF;
2683
2684 IF p_Access_rec.admin_flag = FND_API.g_miss_char THEN
2685 x_complete_rec.admin_flag := l_Access_rec.admin_flag ;
2686 END IF;
2687
2688 IF p_Access_rec.owner_flag = FND_API.g_miss_char THEN
2689 x_complete_rec.owner_flag := l_Access_rec.owner_flag ;
2690 END IF;
2691
2692 IF p_Access_rec.delete_flag = FND_API.g_miss_char THEN
2693 x_complete_rec.delete_flag := l_Access_rec.delete_flag ;
2694 END IF;
2695
2696 END complete_Access_rec;
2697
2698 --=========================================================================
2699 -- PROCEDURE
2700 -- Check_Admin_access
2701 -- PURPOSE
2702 -- To give the Admin user full previledges for the security
2703 -- PARAMETER
2704 -- p_resource_id ID of the person loggin in
2705 -- output TRUE if the resource has the admin previledges
2706 -- FALSE if the resource doesn't have the admin previledges
2707 -- HISTORY
2708 -- 09-Sep-2000 PTENDULK Created
2709 -- 16-Sep-2000 PTENDULK Added code to check if the profile option is null
2710 -- 20-Nov-2000 PTENDULK Changed the where clause for delete flag. Bug#1503997
2711 --=========================================================================
2712 FUNCTION Check_Admin_Access(
2713 p_resource_id IN NUMBER )
2714 RETURN BOOLEAN
2715 IS
2716 L_ADMIN_GROUP CONSTANT VARCHAR2(30) := 'AMS_ADMIN_GROUP';
2717 l_admin NUMBER ;
2718
2719 CURSOR c_members(p_group_id NUMBER) IS
2720 SELECT resource_id
2721 FROM jtf_rs_group_members
2722 -- commented by ptendulk on 22-Jan-2001 Ref Bug #1607548
2723 --FROM jtf_rs_group_members_vl
2724 WHERE group_id = p_group_id
2725 AND resource_id = p_resource_id
2726 AND delete_flag = 'N' ;
2727
2728 l_access BOOLEAN := FALSE ;
2729 l_res_id NUMBER ;
2730 BEGIN
2731 l_admin := FND_PROFILE.Value(L_ADMIN_GROUP);
2732
2733 --============================================================
2734 -- Following code is added by ptendulk on Sep16 to check if
2735 -- the profile option is not defined.
2736 --============================================================
2737 IF l_admin IS NULL
2738 THEN
2739 RETURN FALSE;
2740 END IF;
2741
2742 OPEN c_members(l_admin);
2743 FETCH c_members INTO l_res_id ;
2744 IF c_members%FOUND THEN
2745 CLOSE c_members;
2746 RETURN TRUE ;
2747 ELSE
2748 CLOSE c_members;
2749 RETURN FALSE ;
2750 END IF ;
2751
2752 END Check_Admin_access;
2753
2754 FUNCTION check_function_security( p_function_name IN VARCHAR2 ) RETURN NUMBER
2755 IS
2756 BEGIN
2757
2758 IF fnd_function.test(p_function_name) THEN
2759 return (1);
2760 ELSE
2761 return(0);
2762 END IF;
2763
2764 END;
2765
2766
2767 END AMS_access_PVT;