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