[Home] [Help]
PACKAGE BODY: APPS.AMS_CUST_SETUP_PVT
Source
1 PACKAGE BODY AMS_Cust_Setup_PVT AS
2 /* $Header: amsvcusb.pls 120.4 2006/03/08 00:51:22 vmodur ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_Cust_Setup_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_cust_setup
8 --
9 -- History
10 -- 11/29/1999 julou created
11 -------------------------------------------------------------------------------
12 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
13 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
14 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
15
16 PROCEDURE create_cust_setup
17 (
18 p_api_version IN NUMBER,
19 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
20 p_commit IN VARCHAR2 := FND_API.g_false,
21 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
22
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26
27 p_cust_setup_rec IN cust_setup_rec_type,
28 x_cust_setup_id OUT NOCOPY NUMBER
29 )
30 IS
31
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'create_cust_setup';
34 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
35 l_return_status VARCHAR2(1);
36 l_cust_setup_rec cust_setup_rec_type := p_cust_setup_rec;
37 l_cust_setup_count NUMBER;
38
39 CURSOR c_cust_setup_seq IS
40 SELECT AMS_CUSTOM_SETUPS_B_S.NEXTVAL
41 FROM DUAL;
42
43 CURSOR c_cust_setup_count(cust_setup_id IN NUMBER) IS
44 SELECT COUNT(*)
45 FROM AMS_CUSTOM_SETUPS_VL
46 WHERE custom_setup_id = cust_setup_id;
47
48 BEGIN
49 -- initialize
50 SAVEPOINT create_cust_setup;
51
52 IF FND_API.to_boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56 IF (AMS_DEBUG_HIGH_ON) THEN
57
58
59
60 AMS_Utility_PVT.debug_message(l_full_name || ': start');
61
62 END IF;
63
64 IF NOT FND_API.compatible_api_call
65 (
66 l_api_version,
67 p_api_version,
68 l_api_name,
69 g_pkg_name
70 )
71 THEN
72 RAISE FND_API.g_exc_unexpected_error;
73 END IF;
74
75 x_return_status := FND_API.g_ret_sts_success;
76
77 -- validate
78 IF (AMS_DEBUG_HIGH_ON) THEN
79
80 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
81 END IF;
82
83 validate_cust_setup
84 (
85 p_api_version => l_api_version,
86 p_init_msg_list => p_init_msg_list,
87 p_validation_level => p_validation_level,
88 x_return_status => l_return_status,
89 x_msg_count => x_msg_count,
90 x_msg_data => x_msg_data,
91 p_cust_setup_rec => l_cust_setup_rec
92 );
93
94 IF l_return_status = FND_API.g_ret_sts_error THEN
95 RAISE FND_API.g_exc_error;
96 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
97 RAISE FND_API.g_exc_unexpected_error;
98 END IF;
99
100 -- generate an unique ID if it is not parsed in
101 IF l_cust_setup_rec.custom_setup_id IS NULL THEN
102 LOOP
103 OPEN c_cust_setup_seq;
104 FETCH c_cust_setup_seq INTO l_cust_setup_rec.custom_setup_id;
105 CLOSE c_cust_setup_seq;
106
107 OPEN c_cust_setup_count(l_cust_setup_rec.custom_setup_id);
108 FETCH c_cust_setup_count INTO l_cust_setup_count;
109 CLOSE c_cust_setup_count;
110
111 EXIT WHEN l_cust_setup_count = 0;
112 END LOOP;
113 END IF;
114
115 -- insert
116 IF (AMS_DEBUG_HIGH_ON) THEN
117
118 AMS_Utility_PVT.debug_message(l_full_name || ': insert');
119 END IF;
120
121 INSERT INTO AMS_CUSTOM_SETUPS_B
122 (
123 custom_setup_id,
124 last_update_date,
125 last_updated_by,
126 creation_date,
127 created_by,
128 object_version_number,
129 last_update_login,
130 activity_type_code,
131 media_id,
132 enabled_flag,
133 allow_essential_grouping,
134 usage,
135 object_type,
136 source_code_suffix,
137 application_id
138 )
139 VALUES
140 (
141 l_cust_setup_rec.custom_setup_id,
142 SYSDATE,
143 FND_GLOBAL.user_id,
144 SYSDATE,
145 FND_GLOBAL.user_id,
146 1,
147 FND_GLOBAL.conc_login_id,
148 l_cust_setup_rec.activity_type_code,
149 l_cust_setup_rec.media_id,
150 NVL(l_cust_setup_rec.enabled_flag,'Y'),
151 NVL(l_cust_setup_rec.allow_essential_grouping,'N'),
152 l_cust_setup_rec.usage,
153 l_cust_setup_rec.object_type,
154 l_cust_setup_rec.source_code_suffix,
155 l_cust_setup_rec.application_id
156 );
157
158 INSERT INTO AMS_CUSTOM_SETUPS_TL
159 (
160 custom_setup_id,
161 language,
162 last_update_date,
163 last_updated_by,
164 creation_date,
165 created_by,
166 last_update_login,
167 source_lang,
168 setup_name,
169 description
170 )
171 SELECT
172 l_cust_setup_rec.custom_setup_id,
173 l.language_code,
174 SYSDATE,
175 FND_GLOBAL.user_id,
176 SYSDATE,
177 FND_GLOBAL.user_id,
178 FND_GLOBAL.conc_login_id,
179 USERENV('LANG'),
180 l_cust_setup_rec.setup_name,
181 l_cust_setup_rec.description
182 FROM fnd_languages l
183 WHERE l.installed_flag in ('I', 'B')
184 AND NOT EXISTS
185 (
186 SELECT NULL
187 FROM AMS_CUSTOM_SETUPS_TL t
188 WHERE t.custom_setup_id = l_cust_setup_rec.custom_setup_id
189 AND t.language = l.language_code
190 );
191
192 -- finish
193 x_cust_setup_id := l_cust_setup_rec.custom_setup_id;
194
195 IF l_cust_setup_rec.usage IS NULL THEN
196 INSERT INTO AMS_CUSTOM_SETUP_ATTR
197 (
198 setup_attribute_id,
199 custom_setup_id,
200 last_update_date,
201 last_updated_by,
202 creation_date,
203 created_by,
204 object_version_number,
205 last_update_login,
206 display_sequence_no,
207 object_attribute,
208 attr_mandatory_flag,
209 attr_available_flag,
210 function_name,
211 parent_function_name,
212 parent_setup_attribute,
213 parent_display_sequence,
214 show_in_report,
215 show_in_cue_card,
216 copy_allowed_flag,
217 related_ak_attribute,
218 essential_seq_num
219 )
220 select ams_custom_setup_attr_s.nextval,
221 x_cust_setup_id,
222 SYSDATE,
223 FND_GLOBAL.user_id,
224 SYSDATE,
225 FND_GLOBAL.user_id,
226 1,
227 FND_GLOBAL.conc_login_id,
228 stp.display_sequence_no,
229 stp.setup_attribute,
230 stp.mandatory_flag,
231 'Y',
232 stp.function_name,
233 stp.parent_function_name,
234 stp.parent_setup_attribute,
235 stp.parent_display_sequence,
236 nvl(stp.show_in_report,'Y'),
237 nvl(stp.show_in_cue_card,'Y'),
238 nvl(stp.copy_allowed_flag,'N'),
239 stp.related_ak_attribute,
240 stp.essential_seq_num
241
242 FROM ams_setup_types stp
243 WHERE stp.object_type = l_cust_setup_rec.object_type
244 AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
245 AND (stp.usage ='ALL' OR stp.usage is null)
246 AND stp.application_id = l_cust_setup_rec.application_id;
247 IF SQL%NOTFOUND THEN
248 INSERT INTO AMS_CUSTOM_SETUP_ATTR
249 (
250 setup_attribute_id,
251 custom_setup_id,
252 last_update_date,
253 last_updated_by,
254 creation_date,
255 created_by,
256 object_version_number,
257 last_update_login,
258 display_sequence_no,
259 object_attribute,
260 attr_mandatory_flag,
261 attr_available_flag,
262 function_name,
263 parent_function_name,
264 parent_setup_attribute,
265 parent_display_sequence,
266 show_in_report,
267 show_in_cue_card,
268 copy_allowed_flag,
269 related_ak_attribute,
270 essential_seq_num
271 )
272 select ams_custom_setup_attr_s.nextval,
273 x_cust_setup_id,
274 SYSDATE,
275 FND_GLOBAL.user_id,
276 SYSDATE,
277 FND_GLOBAL.user_id,
278 1,
279 FND_GLOBAL.conc_login_id,
280 stp.display_sequence_no,
281 stp.setup_attribute,
282 stp.mandatory_flag,
283 'Y',
284 stp.function_name,
285 stp.parent_function_name,
286 stp.parent_setup_attribute,
287 stp.parent_display_sequence,
288 nvl(stp.show_in_report,'Y'),
289 nvl(stp.show_in_cue_card,'Y'),
290 nvl(stp.copy_allowed_flag,'N'),
291 stp.related_ak_attribute,
292 stp.essential_seq_num
293 FROM ams_setup_types stp
294 WHERE stp.object_type = l_cust_setup_rec.object_type
295 AND stp.activity_type_code is null
296 AND (stp.usage ='ALL' OR stp.usage is null)
297 AND stp.application_id = l_cust_setup_rec.application_id;
298 END IF;
299
300 ELSE
301 --anchaudh adding the IF condition for bug#3718563
302 IF (l_cust_setup_rec.activity_type_code = 'DIRECT_MARKETING' AND l_cust_setup_rec.media_id = 460) THEN
303 INSERT INTO AMS_CUSTOM_SETUP_ATTR
304 (
305 setup_attribute_id,
306 custom_setup_id,
307 last_update_date,
308 last_updated_by,
309 creation_date,
310 created_by,
311 object_version_number,
312 last_update_login,
313 display_sequence_no,
314 object_attribute,
315 attr_mandatory_flag,
316 attr_available_flag,
317 function_name,
318 parent_function_name,
319 parent_setup_attribute,
320 parent_display_sequence,
321 show_in_report,
322 show_in_cue_card,
323 copy_allowed_flag,
324 related_ak_attribute,
325 essential_seq_num
326 )
327 select ams_custom_setup_attr_s.nextval,
328 x_cust_setup_id,
329 SYSDATE,
330 FND_GLOBAL.user_id,
331 SYSDATE,
332 FND_GLOBAL.user_id,
333 1,
334 FND_GLOBAL.conc_login_id,
335 stp.display_sequence_no,
336 stp.setup_attribute,
337 stp.mandatory_flag,
338 --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
339 --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
340 decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
341 stp.function_name,
342 stp.parent_function_name,
343 stp.parent_setup_attribute,
344 stp.parent_display_sequence,
345 nvl(stp.show_in_report,'Y'),
346 nvl(stp.show_in_cue_card,'Y'),
347 nvl(stp.copy_allowed_flag,'N'),
348 stp.related_ak_attribute,
349 stp.essential_seq_num
350
351 FROM ams_setup_types stp
352 WHERE stp.object_type = l_cust_setup_rec.object_type
353 AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
354 AND stp.usage in ('LITE','ALL')
355 AND stp.application_id = l_cust_setup_rec.application_id
356 AND stp.setup_attribute not in ('COLT'); --ANCHAUDH
357
358 IF SQL%NOTFOUND THEN
359 INSERT INTO AMS_CUSTOM_SETUP_ATTR
360 (
361 setup_attribute_id,
362 custom_setup_id,
363 last_update_date,
364 last_updated_by,
365 creation_date,
366 created_by,
367 object_version_number,
368 last_update_login,
369 display_sequence_no,
370 object_attribute,
371 attr_mandatory_flag,
372 attr_available_flag,
373 function_name,
374 parent_function_name,
375 parent_setup_attribute,
376 parent_display_sequence,
377 show_in_report,
378 show_in_cue_card,
379 copy_allowed_flag,
380 related_ak_attribute,
381 essential_seq_num
382 )
383 select ams_custom_setup_attr_s.nextval,
384 x_cust_setup_id,
385 SYSDATE,
386 FND_GLOBAL.user_id,
387 SYSDATE,
388 FND_GLOBAL.user_id,
389 1,
390 FND_GLOBAL.conc_login_id,
391 stp.display_sequence_no,
392 stp.setup_attribute,
393 stp.mandatory_flag,
394 --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
395 --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
396 decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
397 stp.function_name,
398 stp.parent_function_name,
399 stp.parent_setup_attribute,
400 stp.parent_display_sequence,
401 nvl(stp.show_in_report,'Y'),
402 nvl(stp.show_in_cue_card,'Y'),
403 nvl(stp.copy_allowed_flag,'N'),
404 stp.related_ak_attribute,
405 stp.essential_seq_num
406 FROM ams_setup_types stp
407 WHERE stp.object_type = l_cust_setup_rec.object_type
408 AND stp.activity_type_code is null
412 END IF;
409 AND stp.usage in ('LITE','ALL')
410 AND stp.application_id = l_cust_setup_rec.application_id
411 AND stp.setup_attribute not in ('COLT'); --ANCHAUDH
413
414 ELSE --anchaudh adding the ELSE condition for bug#3718563
415
416 INSERT INTO AMS_CUSTOM_SETUP_ATTR
417 (
418 setup_attribute_id,
419 custom_setup_id,
420 last_update_date,
421 last_updated_by,
422 creation_date,
423 created_by,
424 object_version_number,
425 last_update_login,
426 display_sequence_no,
427 object_attribute,
428 attr_mandatory_flag,
429 attr_available_flag,
430 function_name,
431 parent_function_name,
432 parent_setup_attribute,
433 parent_display_sequence,
434 show_in_report,
435 show_in_cue_card,
436 copy_allowed_flag,
437 related_ak_attribute,
438 essential_seq_num
439 )
440 select ams_custom_setup_attr_s.nextval,
441 x_cust_setup_id,
442 SYSDATE,
443 FND_GLOBAL.user_id,
444 SYSDATE,
445 FND_GLOBAL.user_id,
446 1,
447 FND_GLOBAL.conc_login_id,
448 stp.display_sequence_no,
449 stp.setup_attribute,
450 stp.mandatory_flag,
451 --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
452 --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
453 decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
454 stp.function_name,
455 stp.parent_function_name,
456 stp.parent_setup_attribute,
457 stp.parent_display_sequence,
458 nvl(stp.show_in_report,'Y'),
459 nvl(stp.show_in_cue_card,'Y'),
460 nvl(stp.copy_allowed_flag,'N'),
461 stp.related_ak_attribute,
462 stp.essential_seq_num
463
464 FROM ams_setup_types stp
465 WHERE stp.object_type = l_cust_setup_rec.object_type
466 AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
467 AND stp.usage in ('LITE','ALL')
468 AND stp.application_id = l_cust_setup_rec.application_id;
469
470 IF SQL%NOTFOUND THEN
471 INSERT INTO AMS_CUSTOM_SETUP_ATTR
472 (
473 setup_attribute_id,
474 custom_setup_id,
475 last_update_date,
476 last_updated_by,
477 creation_date,
478 created_by,
479 object_version_number,
480 last_update_login,
481 display_sequence_no,
482 object_attribute,
483 attr_mandatory_flag,
484 attr_available_flag,
485 function_name,
486 parent_function_name,
487 parent_setup_attribute,
488 parent_display_sequence,
489 show_in_report,
490 show_in_cue_card,
491 copy_allowed_flag,
492 related_ak_attribute,
493 essential_seq_num
494 )
495 select ams_custom_setup_attr_s.nextval,
496 x_cust_setup_id,
497 SYSDATE,
498 FND_GLOBAL.user_id,
499 SYSDATE,
500 FND_GLOBAL.user_id,
501 1,
502 FND_GLOBAL.conc_login_id,
503 stp.display_sequence_no,
504 stp.setup_attribute,
505 stp.mandatory_flag,
506 --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
507 --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
508 decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
509 stp.function_name,
510 stp.parent_function_name,
511 stp.parent_setup_attribute,
512 stp.parent_display_sequence,
513 nvl(stp.show_in_report,'Y'),
514 nvl(stp.show_in_cue_card,'Y'),
515 nvl(stp.copy_allowed_flag,'N'),
516 stp.related_ak_attribute,
517 stp.essential_seq_num
518 FROM ams_setup_types stp
519 WHERE stp.object_type = l_cust_setup_rec.object_type
520 AND stp.activity_type_code is null
521 AND stp.usage in ('LITE','ALL')
522 AND stp.application_id = l_cust_setup_rec.application_id;
523 END IF;
524
525 END IF; --anchaudh ending the IF condition for bug#3718563
526
527 END IF;
528
529
530
531
532 IF FND_API.to_boolean(p_commit) THEN
533 COMMIT;
534 END IF;
535
536 FND_MSG_PUB.count_and_get
537 (
538 p_encoded => FND_API.g_false,
539 p_count => x_msg_count,
540 p_data => x_msg_data
541 );
542
543 IF (AMS_DEBUG_HIGH_ON) THEN
544
545
546
547 AMS_Utility_PVT.debug_message(l_full_name||': end');
548
549 END IF;
550
551 EXCEPTION
552
553 WHEN FND_API.g_exc_error THEN
554 ROLLBACK TO create_cust_setup;
555 x_return_status := FND_API.g_ret_sts_error;
559 p_count => x_msg_count,
556 FND_MSG_PUB.count_and_get
557 (
558 p_encoded => FND_API.g_false,
560 p_data => x_msg_data
561 );
562
563 WHEN FND_API.g_exc_unexpected_error THEN
564 ROLLBACK TO create_cust_setup;
565 x_return_status := FND_API.g_ret_sts_unexp_error;
566 FND_MSG_PUB.count_and_get
567 (
568 p_encoded => FND_API.g_false,
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572
573 WHEN OTHERS THEN
574 ROLLBACK TO create_cust_setup;
575 x_return_status :=FND_API.g_ret_sts_unexp_error;
576 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
577 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
578 END IF;
579 FND_MSG_PUB.count_and_get
580 (
581 p_encoded => FND_API.g_false,
582 p_count => x_msg_count,
583 p_data => x_msg_data
584 );
585
586 END create_cust_setup;
587
588
589 /*****************************************************************************/
590 -- Procedure: update_cust_setup
591 --
592 -- History
593 -- 11/29/1999 julou created
594 -------------------------------------------------------------------------------
595 PROCEDURE update_cust_setup
596 (
597 p_api_version IN NUMBER,
598 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
599 p_commit IN VARCHAR2 := FND_API.g_false,
600 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
601 x_return_status OUT NOCOPY VARCHAR2,
602 x_msg_count OUT NOCOPY NUMBER,
603 x_msg_data OUT NOCOPY VARCHAR2,
604
605 p_cust_setup_rec IN cust_setup_rec_type
606 )
607 IS
608
609 l_api_version CONSTANT NUMBER := 1.0;
610 l_api_name CONSTANT VARCHAR2(30) := 'update_cust_setup';
611 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
612 l_return_status VARCHAR2(1);
613 l_cust_setup_rec cust_setup_rec_type := p_cust_setup_rec;
614
615 BEGIN
616
617 -- initialize
618 SAVEPOINT update_cust_setup;
619
620 IF FND_API.to_boolean(p_init_msg_list) THEN
621 FND_MSG_PUB.initialize;
622 END IF;
623
624 IF (AMS_DEBUG_HIGH_ON) THEN
625
626
627
628 AMS_Utility_PVT.debug_message(l_full_name || ': start');
629
630 END IF;
631
632 IF NOT FND_API.compatible_api_call
633 (
634 l_api_version,
635 p_api_version,
636 l_api_name,
637 g_pkg_name
638 )
639 THEN
640 RAISE FND_API.g_exc_unexpected_error;
641 END IF;
642
643 x_return_status := FND_API.g_ret_sts_success;
644
645 -- check custom_setup_id, dont update setups if id < 10000 as they are seed data
646 /**
647 IF l_cust_setup_rec.custom_setup_id < 10000 THEN
648 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
649 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_SEED_DATA');
650 FND_MSG_PUB.add;
651 END IF;
652 RAISE FND_API.g_exc_error;
653 END IF;
654 **/
655 -- complete the record
656 complete_cust_setup_rec
657 (
658 p_cust_setup_rec,
659 l_cust_setup_rec
660 );
661
662 -- validate
663 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
664 IF (AMS_DEBUG_HIGH_ON) THEN
665
666 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
667 END IF;
668 IF (AMS_DEBUG_HIGH_ON) THEN
669
670 AMS_Utility_PVT.debug_message(l_full_name || ': check items');
671 END IF;
672 check_items
673 (
674 p_validation_mode => JTF_PLSQL_API.g_update,
675 x_return_status => l_return_status,
676 p_cust_setup_rec => l_cust_setup_rec
677 );
678
679 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
680 RAISE FND_API.g_exc_unexpected_error;
681 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
682 RAISE FND_API.g_exc_error;
683 END IF;
684 END IF;
685
686
687 -- update
688 IF (AMS_DEBUG_HIGH_ON) THEN
689
690 AMS_Utility_PVT.debug_message(l_full_name||': update');
691 END IF;
692
693 UPDATE AMS_CUSTOM_SETUPS_B SET
694 last_update_date = SYSDATE,
695 last_updated_by = FND_GLOBAL.user_id,
696 object_version_number = l_cust_setup_rec.object_version_number + 1,
697 last_update_login = FND_GLOBAL.conc_login_id,
698 activity_type_code = l_cust_setup_rec.activity_type_code,
699 media_id = l_cust_setup_rec.media_id,
700 enabled_flag = l_cust_setup_rec.enabled_flag,
701 object_type = l_cust_setup_rec.object_type,
702 source_code_suffix = l_cust_setup_rec.source_code_suffix,
703 allow_essential_grouping = l_cust_setup_rec.allow_essential_grouping,
704 usage = l_cust_setup_rec.usage
705 WHERE custom_setup_id = l_cust_setup_rec.custom_setup_id
706 AND object_version_number = l_cust_setup_rec.object_version_number;
707
711 FND_MSG_PUB.add;
708 IF (SQL%NOTFOUND) THEN
709 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
710 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
712 END IF;
713 RAISE FND_API.g_exc_error;
714 END IF;
715
716 UPDATE AMS_CUSTOM_SETUPS_TL SET
717 last_update_date = SYSDATE,
718 last_updated_by = FND_GLOBAL.user_id,
719 last_update_login = FND_GLOBAL.conc_login_id,
720 source_lang = USERENV('LANG'),
721 setup_name = l_cust_setup_rec.setup_name,
722 description = l_cust_setup_rec.description
723 WHERE custom_setup_id = l_cust_setup_rec.custom_setup_id
724 AND USERENV('LANG') IN (language, source_lang);
725
726 IF (SQL%NOTFOUND) THEN
727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
728 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
729 FND_MSG_PUB.add;
730 END IF;
731 RAISE FND_API.g_exc_error;
732 END IF;
733
734 -- finish
735 IF FND_API.to_boolean(p_commit) THEN
736 COMMIT;
737 END IF;
738
739 FND_MSG_PUB.count_and_get
740 (
741 P_ENCODED => FND_API.g_false,
742 p_count => x_msg_count,
743 p_data => x_msg_data
744 );
745
746 IF (AMS_DEBUG_HIGH_ON) THEN
747
748
749
750 AMS_Utility_PVT.debug_message(l_full_name || ': end');
751
752 END IF;
753
754 EXCEPTION
755
756 WHEN FND_API.g_exc_error THEN
757 ROLLBACK TO update_cust_setup;
758 x_return_status := FND_API.g_ret_sts_error;
759 FND_MSG_PUB.count_and_get
760 (
761 p_encoded => FND_API.g_false,
762 p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765
766 WHEN FND_API.g_exc_unexpected_error THEN
767 ROLLBACK TO update_cust_setup;
768 x_return_status := FND_API.g_ret_sts_unexp_error;
769 FND_MSG_PUB.count_and_get
770 (
771 p_encoded => FND_API.g_false,
772 p_count => x_msg_count,
773 p_data => x_msg_data
774 );
775
776 WHEN OTHERS THEN
777 ROLLBACK TO update_cust_setup;
778 x_return_status :=FND_API.g_ret_sts_unexp_error;
779 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
780 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
781 END IF;
782 FND_MSG_PUB.count_and_get
783 (
784 p_encoded => FND_API.g_false,
785 p_count => x_msg_count,
786 p_data => x_msg_data
787 );
788
789 END update_cust_setup;
790
791
792 /*****************************************************************************/
793 -- Procedure: delete_cust_setup
794 --
795 -- History
796 -- 11/29/1999 julou created
797 -------------------------------------------------------------------------------
798 PROCEDURE delete_cust_setup
799 (
800 p_api_version IN NUMBER,
801 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
802 p_commit IN VARCHAR2 := FND_API.g_false,
803
804 x_return_status OUT NOCOPY VARCHAR2,
805 x_msg_count OUT NOCOPY NUMBER,
806 x_msg_data OUT NOCOPY VARCHAR2,
807
808 p_cust_setup_id IN NUMBER,
809 p_object_version IN NUMBER
810 )
811 IS
812
813 l_api_version CONSTANT NUMBER := 1.0;
814 l_api_name CONSTANT VARCHAR2(30) := 'delete_cust_setup';
815 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
816 l_is_used_count NUMBER;
817 l_object_version NUMBER;
818
819 CURSOR c_is_used_count(cust_setup_id IN NUMBER) IS
820 SELECT COUNT(*)
821 FROM AMS_OBJECT_ATTRIBUTES
822 WHERE custom_setup_id = cust_setup_id;
823
824 CURSOR c_object_version(cust_setup_id IN NUMBER) IS
825 SELECT object_version_number
826 FROM AMS_CUSTOM_SETUPS_B
827 WHERE custom_setup_id = cust_setup_id;
828
829 BEGIN
830 -- initialize
831 SAVEPOINT delete_cust_setup;
832
833 IF FND_API.to_boolean(p_init_msg_list) THEN
834 FND_MSG_PUB.initialize;
835 END IF;
836
837 IF (AMS_DEBUG_HIGH_ON) THEN
838
839
840
841 AMS_Utility_PVT.debug_message(l_full_name || ': start');
842
843 END IF;
844
845 IF NOT FND_API.compatible_api_call
846 (
847 l_api_version,
848 p_api_version,
849 l_api_name,
850 g_pkg_name
851 )
852 THEN
853 RAISE FND_API.g_exc_unexpected_error;
854 END IF;
855
856 x_return_status := FND_API.g_ret_sts_success;
857
858 -- check custom_setup_id, dont delete setups if id < 10000 as they are seed data
859 IF p_cust_setup_id < 10000 THEN
860 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
861 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_SEED_DATA');
862 FND_MSG_PUB.add;
863 END IF;
864 RAISE FND_API.g_exc_error;
865 END IF;
866
867 -- delete
868 IF (AMS_DEBUG_HIGH_ON) THEN
869
873 OPEN c_is_used_count(p_cust_setup_id);
870 AMS_Utility_PVT.debug_message(l_full_name || ': delete');
871 END IF;
872
874 FETCH c_is_used_count INTO l_is_used_count;
875 CLOSE c_is_used_count;
876
877 OPEN c_object_version(p_cust_setup_id);
878 FETCH c_object_version INTO l_object_version;
879 CLOSE c_object_version;
880
881 IF l_is_used_count = 0 THEN -- IS NOT USED
882 IF l_object_version = p_object_version THEN -- VERSIONS MATCH
883 DELETE FROM AMS_CUSTOM_SETUP_ATTR
884 WHERE custom_setup_id = p_cust_setup_id;
885
886 DELETE FROM AMS_CUSTOM_SETUPS_TL
887 WHERE custom_setup_id = p_cust_setup_id;
888
889 IF (SQL%NOTFOUND) THEN
890 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
891 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
892 FND_MSG_PUB.add;
893 END IF;
894 RAISE FND_API.g_exc_error;
895 END IF;
896
897 DELETE FROM AMS_CUSTOM_SETUPS_B
898 WHERE custom_setup_id = p_cust_setup_id
899 AND object_version_number = p_object_version;
900
901 IF (SQL%NOTFOUND) THEN
902 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
903 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
904 FND_MSG_PUB.add;
905 END IF;
906 RAISE FND_API.g_exc_error;
907 END IF;
908 ELSE
909 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
910 FND_MESSAGE.set_name('AMS', 'AMS_API_VERS_DONT_MATCH');
911 FND_MSG_PUB.add;
912 END IF;
913 RAISE FND_API.g_exc_error;
914 END IF;
915 ELSE
916 UPDATE AMS_CUSTOM_SETUPS_B SET -- IS USED
917 object_version_number = l_object_version +1,
918 enabled_flag = 'N'
919 WHERE custom_setup_id = p_cust_setup_id
920 AND object_version_number = p_object_version;
921 /*
922 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
923 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_IN_USE');
924 FND_MSG_PUB.add;
925 END IF;
926 RAISE FND_API.g_exc_error;
927 */
928 END IF;
929
930 -- finish
931 IF FND_API.to_boolean(p_commit) THEN
932 COMMIT;
933 END IF;
934
935 FND_MSG_PUB.count_and_get
936 (
937 P_ENCODED => FND_API.g_false,
938 p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941
942 IF (AMS_DEBUG_HIGH_ON) THEN
943
944
945
946 AMS_Utility_PVT.debug_message(l_full_name || ': end');
947
948 END IF;
949
950 EXCEPTION
951
952 WHEN FND_API.g_exc_error THEN
953 ROLLBACK TO delete_cust_setup;
954 x_return_status := FND_API.g_ret_sts_error;
955 FND_MSG_PUB.count_and_get
956 (
957 p_encoded => FND_API.g_false,
958 p_count => x_msg_count,
959 p_data => x_msg_data
960 );
961
962 WHEN FND_API.g_exc_unexpected_error THEN
963 ROLLBACK TO delete_cust_setup;
964 x_return_status := FND_API.g_ret_sts_unexp_error;
965 FND_MSG_PUB.count_and_get
966 (
967 p_encoded => FND_API.g_false,
968 p_count => x_msg_count,
969 p_data => x_msg_data
970 );
971
972 WHEN OTHERS THEN
973 ROLLBACK TO delete_cust_setup;
974 x_return_status :=FND_API.g_ret_sts_unexp_error;
975 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
976 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
977 END IF;
978 FND_MSG_PUB.count_and_get
979 (
980 p_encoded => FND_API.g_false,
981 p_count => x_msg_count,
982 p_data => x_msg_data
983 );
984
985 END delete_cust_setup;
986
987
988 /*****************************************************************************/
989 -- Procedure: lock_cust_setup
990 --
991 -- History
992 -- 11/29/1999 julou created
993 -------------------------------------------------------------------------------
994 PROCEDURE lock_cust_setup
995 (
996 p_api_version IN NUMBER,
997 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
998
999 x_return_status OUT NOCOPY VARCHAR2,
1000 x_msg_count OUT NOCOPY NUMBER,
1001 x_msg_data OUT NOCOPY VARCHAR2,
1002
1003 p_cust_setup_id IN NUMBER,
1004 p_object_version IN NUMBER
1005 )
1006 IS
1007
1008 l_api_version CONSTANT NUMBER := 1.0;
1009 l_api_name CONSTANT VARCHAR2(30) := 'lock_cust_setup';
1010 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1011 l_cust_setup_id NUMBER;
1012
1013 CURSOR c_cust_setup_b IS
1014 SELECT custom_setup_id
1015 FROM AMS_CUSTOM_SETUPS_B
1016 WHERE custom_setup_id = p_cust_setup_id
1017 AND object_version_number = p_object_version
1018 FOR UPDATE OF custom_setup_id NOWAIT;
1019
1020 CURSOR c_cust_setup_tl IS
1024 AND USERENV('LANG') IN (language, source_lang)
1021 SELECT custom_setup_id
1022 FROM AMS_CUSTOM_SETUPS_TL
1023 WHERE custom_setup_id = p_cust_setup_id
1025 FOR UPDATE OF custom_setup_id NOWAIT;
1026
1027 BEGIN
1028 -- initialize
1029 IF (AMS_DEBUG_HIGH_ON) THEN
1030
1031 AMS_Utility_PVT.debug_message(l_full_name || ': start');
1032 END IF;
1033
1034 IF FND_API.to_boolean(p_init_msg_list) THEN
1035 FND_MSG_PUB.initialize;
1036 END IF;
1037
1038 IF NOT FND_API.compatible_api_call
1039 (
1040 l_api_version,
1041 p_api_version,
1042 l_api_name,
1043 g_pkg_name
1044 )
1045 THEN
1046 RAISE FND_API.g_exc_unexpected_error;
1047 END IF;
1048
1049 x_return_status := FND_API.g_ret_sts_success;
1050
1051 -- lock
1052 IF (AMS_DEBUG_HIGH_ON) THEN
1053
1054 AMS_Utility_PVT.debug_message(l_full_name || ': lock');
1055 END IF;
1056
1057 OPEN c_cust_setup_b;
1058 FETCH c_cust_setup_b INTO l_cust_setup_id;
1059 IF (c_cust_setup_b%NOTFOUND) THEN
1060 CLOSE c_cust_setup_b;
1061 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1062 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1063 FND_MSG_PUB.add;
1064 END IF;
1065 RAISE FND_API.g_exc_error;
1066 END IF;
1067 CLOSE c_cust_setup_b;
1068
1069 OPEN c_cust_setup_tl;
1070 FETCH c_cust_setup_tl INTO l_cust_setup_id;
1071 IF (c_cust_setup_tl%NOTFOUND) THEN
1072 CLOSE c_cust_setup_tl;
1073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1074 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1075 FND_MSG_PUB.add;
1076 END IF;
1077 RAISE FND_API.g_exc_error;
1078 END IF;
1079 CLOSE c_cust_setup_tl;
1080
1081 -- finish
1082 FND_MSG_PUB.count_and_get
1083 (
1084 p_encoded => FND_API.g_false,
1085 p_count => x_msg_count,
1086 p_data => x_msg_data
1087 );
1088
1089 IF (AMS_DEBUG_HIGH_ON) THEN
1090
1091
1092
1093 AMS_Utility_PVT.debug_message(l_full_name || ': end');
1094
1095 END IF;
1096
1097 EXCEPTION
1098
1099 WHEN AMS_Utility_PVT.resource_locked THEN
1100 x_return_status := FND_API.g_ret_sts_error;
1101 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1102 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
1103 FND_MSG_PUB.add;
1104 END IF;
1105 FND_MSG_PUB.count_and_get
1106 (
1107 p_encoded => FND_API.g_false,
1108 p_count => x_msg_count,
1109 p_data => x_msg_data
1110 );
1111
1112 WHEN FND_API.g_exc_error THEN
1113 x_return_status := FND_API.g_ret_sts_error;
1114 FND_MSG_PUB.count_and_get
1115 (
1116 p_encoded => FND_API.g_false,
1117 p_count => x_msg_count,
1118 p_data => x_msg_data
1119 );
1120
1121 WHEN FND_API.g_exc_unexpected_error THEN
1122 x_return_status := FND_API.g_ret_sts_unexp_error;
1123 FND_MSG_PUB.count_and_get
1124 (
1125 p_encoded => FND_API.g_false,
1126 p_count => x_msg_count,
1127 p_data => x_msg_data
1128 );
1129
1130 WHEN OTHERS THEN
1131 x_return_status :=FND_API.g_ret_sts_unexp_error;
1132 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1133 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1134 END IF;
1135 FND_MSG_PUB.count_and_get
1136 (
1137 p_encoded => FND_API.g_false,
1138 p_count => x_msg_count,
1139 p_data => x_msg_data
1140 );
1141
1142 END lock_cust_setup;
1143
1144
1145 /*****************************************************************************/
1146 -- PROCEDURE
1147 -- validate_cust_setup
1148 --
1149 -- HISTORY
1150 -- 11/29/99 julou Created.
1151 --------------------------------------------------------------------
1152 PROCEDURE validate_cust_setup
1153 (
1154 p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1156 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1157
1158 x_return_status OUT NOCOPY VARCHAR2,
1159 x_msg_count OUT NOCOPY NUMBER,
1160 x_msg_data OUT NOCOPY VARCHAR2,
1161
1162 p_cust_setup_rec IN cust_setup_rec_type
1163 )
1164 IS
1165
1166 l_api_version CONSTANT NUMBER := 1.0;
1167 l_api_name CONSTANT VARCHAR2(30) := 'validate_cust_setup';
1168 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1169
1170 l_return_status VARCHAR2(1);
1171
1172 BEGIN
1173
1174 ----------------------- initialize --------------------
1175 IF (AMS_DEBUG_HIGH_ON) THEN
1176
1177 AMS_Utility_PVT.debug_message(l_full_name||': start');
1178 END IF;
1179
1180 -- IF FND_API.to_boolean(p_init_msg_list) THEN
1181 -- FND_MSG_PUB.initialize;
1182 -- END IF;
1183
1184 IF NOT FND_API.compatible_api_call
1185 (
1189 g_pkg_name
1186 l_api_version,
1187 p_api_version,
1188 l_api_name,
1190 )
1191 THEN
1192 RAISE FND_API.g_exc_unexpected_error;
1193 END IF;
1194
1195 x_return_status := FND_API.g_ret_sts_success;
1196
1197 ---------------------- validate ------------------------
1198 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1199 IF (AMS_DEBUG_HIGH_ON) THEN
1200
1201 AMS_Utility_PVT.debug_message(l_full_name||': check items');
1202 END IF;
1203 check_items
1204 (
1205 p_validation_mode => JTF_PLSQL_API.g_create,
1206 x_return_status => l_return_status,
1207 p_cust_setup_rec => p_cust_setup_rec
1208 );
1209
1210 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1211 RAISE FND_API.g_exc_unexpected_error;
1212 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1213 RAISE FND_API.g_exc_error;
1214 END IF;
1215 END IF;
1216
1217 -------------------- finish --------------------------
1218 FND_MSG_PUB.count_and_get
1219 (
1220 p_encoded => FND_API.g_false,
1221 p_count => x_msg_count,
1222 p_data => x_msg_data
1223 );
1224
1225 IF (AMS_DEBUG_HIGH_ON) THEN
1226
1227
1228
1229 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1230
1231 END IF;
1232
1233 EXCEPTION
1234 WHEN FND_API.g_exc_error THEN
1235 x_return_status := FND_API.g_ret_sts_error;
1236 FND_MSG_PUB.count_and_get
1237 (
1238 p_encoded => FND_API.g_false,
1239 p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242
1243 WHEN FND_API.g_exc_unexpected_error THEN
1244 x_return_status := FND_API.g_ret_sts_unexp_error ;
1245 FND_MSG_PUB.count_and_get
1246 (
1247 p_encoded => FND_API.g_false,
1248 p_count => x_msg_count,
1249 p_data => x_msg_data
1250 );
1251
1252 WHEN OTHERS THEN
1253 x_return_status := FND_API.g_ret_sts_unexp_error;
1254 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1255 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1256 END IF;
1257
1258 FND_MSG_PUB.count_and_get
1259 (
1260 p_encoded => FND_API.g_false,
1261 p_count => x_msg_count,
1262 p_data => x_msg_data
1263 );
1264
1265 END validate_cust_setup;
1266
1267
1268 /*****************************************************************************/
1269 -- Procedure: check_items
1270 --
1271 -- History
1272 -- 12/19/1999 julou created
1273 -------------------------------------------------------------------------------
1274 PROCEDURE check_items
1275 (
1276 p_validation_mode IN VARCHAR2,
1277 x_return_status OUT NOCOPY VARCHAR2,
1278 p_cust_setup_rec IN cust_setup_rec_type
1279 )
1280 IS
1281
1282 l_api_version CONSTANT NUMBER := 1.0;
1283 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
1284 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1285
1286 BEGIN
1287 -- initialize
1288 IF (AMS_DEBUG_HIGH_ON) THEN
1289
1290 AMS_Utility_PVT.debug_message(l_full_name || ': start');
1291 END IF;
1292
1293 x_return_status := FND_API.g_ret_sts_success;
1294
1295 -- check required items
1296 IF (AMS_DEBUG_HIGH_ON) THEN
1297
1298 AMS_Utility_PVT.debug_message(l_full_name || ': check required items');
1299 END IF;
1300 check_cust_setup_req_items
1301 (
1302 p_validation_mode => p_validation_mode,
1303 p_cust_setup_rec => p_cust_setup_rec,
1304 x_return_status => x_return_status
1305 );
1306
1307 IF x_return_status <> FND_API.g_ret_sts_success THEN
1308 RETURN;
1309 END IF;
1310
1311 -- check unique key items
1312 IF (AMS_DEBUG_HIGH_ON) THEN
1313
1314 AMS_Utility_PVT.debug_message(l_full_name || ': check uk items');
1315 END IF;
1316 check_cust_setup_uk_items
1317 (
1318 p_validation_mode => p_validation_mode,
1319 p_cust_setup_rec => p_cust_setup_rec,
1320 x_return_status => x_return_status
1321 );
1322
1323 IF x_return_status <> FND_API.g_ret_sts_success THEN
1324 RETURN;
1325 END IF;
1326
1327 -- check foreign key items
1328 IF (AMS_DEBUG_HIGH_ON) THEN
1329
1330 AMS_Utility_PVT.debug_message(l_full_name || ': check fk items');
1331 END IF;
1332 check_cust_setup_fk_items
1333 (
1334 p_cust_setup_rec => p_cust_setup_rec,
1335 x_return_status => x_return_status
1336 );
1337
1338 IF x_return_status <> FND_API.g_ret_sts_success THEN
1339 RETURN;
1340 END IF;
1341
1342 -- check flags
1343 IF (AMS_DEBUG_HIGH_ON) THEN
1344
1345 AMS_Utility_PVT.debug_message(l_full_name || ': check flag items');
1346 END IF;
1347 check_cust_setup_flag_items
1348 (
1352
1349 p_cust_setup_rec => p_cust_setup_rec,
1350 x_return_status => x_return_status
1351 );
1353 IF x_return_status <> FND_API.g_ret_sts_success THEN
1354 RETURN;
1355 END IF;
1356
1357 END check_items;
1358
1359
1360 /*****************************************************************************/
1361 -- Procedure: check_cust_setup_req_items
1362 --
1363 -- History
1364 -- 11/29/1999 julou created
1365 -- 03-Jan-2000 ptendulk Modified the Activity type check for Rollup campaigns
1366 -- as it can be null for rollup campaigns.
1367 -------------------------------------------------------------------------------
1368 PROCEDURE check_cust_setup_req_items
1369 (
1370 p_validation_mode IN VARCHAR2,
1371 p_cust_setup_rec IN cust_setup_rec_type,
1372 x_return_status OUT NOCOPY VARCHAR2
1373 )
1374 IS
1375
1376 BEGIN
1377
1378 x_return_status := FND_API.g_ret_sts_success;
1379
1380 -- check custom_setup_id
1381 IF p_cust_setup_rec.custom_setup_id IS NULL
1382 AND p_validation_mode = JTF_PLSQL_API.g_update
1383 THEN
1384 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1385 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_NO_CUS_SETUP_ID');
1386 FND_MSG_PUB.add;
1387 END IF;
1388
1389 x_return_status := FND_API.g_ret_sts_error;
1390 RETURN;
1391 END IF;
1392
1393 -- check object_version_number
1394 IF p_cust_setup_rec.object_version_number IS NULL
1395 AND p_validation_mode = JTF_PLSQL_API.g_update
1396 THEN
1397 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1398 FND_MESSAGE.set_name('AMS', 'AMS_API_NO_OBJ_VER_NUM');
1399 FND_MSG_PUB.add;
1400 END IF;
1401
1402 x_return_status := FND_API.g_ret_sts_error;
1403 RETURN;
1404 END IF;
1405
1406 -- check object_type
1407 /* IF p_cust_setup_rec.object_type NOT IN ('CAMP','ECAM','RCAM','EVEH','EVEO') THEN
1408 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1409 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_BAD_OBJ_TYPE');
1410 FND_MSG_PUB.add;
1411 END IF;
1412
1413 x_return_status := FND_API.g_ret_sts_error;
1414 RETURN;
1415 END IF;
1416
1417 -- check activity_type_code
1418 -- Following Line is modified by ptendulk on 03-Jan-2000 as for rollup campaigns,
1419 -- Activity type is not mandatory now.
1420 -- IF p_cust_setup_rec.object_type IN ('CAMP','ECAM','RCAM')
1421 IF p_cust_setup_rec.object_type IN ('CAMP','ECAM')
1422 AND p_cust_setup_rec.activity_type_code IS NULL
1423 THEN
1424 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1425 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_NO_ACT_TP_CODE');
1426 FND_MSG_PUB.add;
1427 END IF;
1428
1429 x_return_status := FND_API.g_ret_sts_error;
1430 RETURN;
1431 END IF;
1432
1433 IF p_cust_setup_rec.object_type = 'EVEH'
1434 OR p_cust_setup_rec.object_type = 'EVEO'
1435 THEN
1436 IF p_cust_setup_rec.activity_type_code IS NOT NULL
1437 OR p_cust_setup_rec.media_id IS NOT NULL
1438 THEN
1439 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1440 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_BAD_ACT_TP_CODE');
1441 FND_MSG_PUB.add;
1442 END IF;
1443
1444 x_return_status := FND_API.g_ret_sts_error;
1445 RETURN;
1446 END IF;
1447 END IF; */
1448
1449 -- check setup_name
1450 IF p_cust_setup_rec.setup_name IS NULL THEN
1451 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1452 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_NO_SETUP_NAME');
1453 FND_MSG_PUB.add;
1454 END IF;
1455
1456 x_return_status := FND_API.g_ret_sts_error;
1457 RETURN;
1458 END IF;
1459
1460
1461 END check_cust_setup_req_items;
1462
1463
1464 /*****************************************************************************/
1465 -- Procedure: check_cust_setup_uk_items
1466 --
1467 -- History
1468 -- 11/29/1999 julou created
1469 -------------------------------------------------------------------------------
1470 PROCEDURE check_cust_setup_uk_items
1471 (
1472 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1473 p_cust_setup_rec IN cust_setup_rec_type,
1474 x_return_status OUT NOCOPY VARCHAR2
1475 )
1476 IS
1477
1478 l_uk_flag VARCHAR2(1);
1479
1480 CURSOR c_name_unique_cr (p_setup_name IN VARCHAR2) IS
1481 SELECT ''
1482 FROM AMS_CUSTOM_SETUPS_TL
1483 WHERE UPPER(setup_name) = UPPER(p_setup_name)
1484 AND language = USERENV('LANG') ;
1485
1486 CURSOR c_name_unique_up (p_setup_name IN VARCHAR2, p_setup_id IN NUMBER) IS
1487 SELECT ''
1488 FROM AMS_CUSTOM_SETUPS_TL
1489 WHERE UPPER(setup_name) = UPPER(p_setup_name)
1490 AND custom_setup_id <> p_setup_id
1491 AND language = USERENV('LANG');
1492
1493 l_flag VARCHAR2(1);
1494 l_dummy VARCHAR2(1);
1495
1496 BEGIN
1497
1498 x_return_status := FND_API.g_ret_sts_success;
1499
1500 -- check PK, if custom_setup_id is passed in, must check if it is duplicATE
1501 IF p_validation_mode = JTF_PLSQL_API.g_create
1502 AND p_cust_setup_rec.custom_setup_id IS NOT NULL
1503 THEN
1504 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1505 (
1506 'AMS_CUSTOM_SETUPS_VL',
1507 'custom_setup_id = ' || p_cust_setup_rec.custom_setup_id
1508 );
1509 END IF;
1510
1511 IF l_uk_flag = FND_API.g_false THEN
1512 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1513 FND_MESSAGE.set_name('AMS', 'AMS_CUSTOM_SETUP_DUPLICATE_ID');
1514 FND_MSG_PUB.add;
1515 END IF;
1516
1517 x_return_status := FND_API.g_ret_sts_error;
1518 RETURN;
1519 END IF;
1520
1521 -- check custom_setup_id
1522 /*************** commented by abhola
1523 IF p_cust_setup_rec.custom_setup_id IS NOT NULL THEN -- UPDATE RECORD
1524 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1525 (
1526 'AMS_CUSTOM_SETUPS_TL',
1527 'custom_setup_id <> ' || p_cust_setup_rec.custom_setup_id
1528 || ' AND setup_name = ''' || p_cust_setup_rec.setup_name
1529 || ''' AND language = ''' || USERENV('LANG') ||''''
1530 );
1531 ELSE -- NEW RECORD
1532 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1533 (
1534 'AMS_CUSTOM_SETUPS_TL',
1535 'setup_name = ''' || p_cust_setup_rec.setup_name
1536 || ''' AND language = ''' || USERENV('LANG') ||''''
1537 );
1538 END IF;
1539
1540 IF l_uk_flag = FND_API.g_false THEN
1541 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1542 FND_MESSAGE.set_name('AMS', 'AMS_CUST_SETUP_DUP_NAME_LANG');
1543 FND_MSG_PUB.add;
1544 END IF;
1545
1546 x_return_status := FND_API.g_ret_sts_error;
1547 RETURN;
1548 END IF;
1549 ************* end abhola **************/
1550 --
1551 -- start abhola
1552 --
1553 l_flag := 'N';
1554
1555 IF p_cust_setup_rec.custom_setup_id IS NULL THEN -- UPDATE RECORD
1556
1557 OPEN c_name_unique_cr (p_cust_setup_rec.setup_name);
1558 FETCH c_name_unique_cr INTO l_dummy;
1559 if (c_name_unique_cr%FOUND) then
1560 l_flag := 'Y';
1561 end if;
1562 CLOSE c_name_unique_cr;
1563
1564 ELSE
1565
1566 OPEN c_name_unique_up (p_cust_setup_rec.setup_name,p_cust_setup_rec.custom_setup_id);
1567 FETCH c_name_unique_up INTO l_dummy;
1568 if (c_name_unique_up%FOUND) then
1569 l_flag := 'Y';
1570 end if;
1571
1572 END IF;
1573
1574 IF (l_flag = 'Y') THEN
1575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1579
1576 FND_MESSAGE.set_name('AMS', 'AMS_CUST_SETUP_DUP_NAME_LANG');
1577 FND_MSG_PUB.add;
1578 END IF;
1580 x_return_status := FND_API.g_ret_sts_error;
1581 RETURN;
1582 END IF;
1583 -- end abhola
1584
1585 END check_cust_setup_uk_items;
1586
1587
1588 /*****************************************************************************/
1589 -- Procedure: check_cust_setup_fk_items
1590 --
1591 -- History
1592 -- 11/29/1999 julou created
1593 -------------------------------------------------------------------------------
1594 PROCEDURE check_cust_setup_fk_items
1595 (
1596 p_cust_setup_rec IN cust_setup_rec_type,
1597 x_return_status OUT NOCOPY VARCHAR2
1598 )
1599 IS
1600
1601 l_fk_flag VARCHAR2(1);
1602
1603 BEGIN
1604
1605 x_return_status := FND_API.g_ret_sts_success;
1606 IF p_cust_setup_rec.media_id IS NOT NULL THEN
1607 -- DELV condition added by rrajesh on 07/21/01
1608 -- And moved ams_media_b check in else condition
1609 IF p_cust_setup_rec.object_type = 'DELV' THEN
1610 l_fk_flag := AMS_Utility_PVT.check_fk_exists
1611 (
1612 'AMS_CATEGORIES_B',
1613 'category_id',
1614 p_cust_setup_rec.media_id
1615 );
1616 ELSE
1617 l_fk_flag := AMS_Utility_PVT.check_fk_exists
1618 (
1619 'AMS_MEDIA_B',
1620 'media_id',
1621 p_cust_setup_rec.media_id
1622 );
1623 END IF;
1624 -- end change. 07/21/01
1625 IF l_fk_flag = FND_API.g_false THEN
1626 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1627 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_BAD_MEDIA_ID');
1628 FND_MSG_PUB.add;
1629 END IF;
1630
1631 x_return_status := FND_API.g_ret_sts_error;
1632 RETURN;
1633 END IF;
1634 END IF;
1635
1636 END check_cust_setup_fk_items;
1637
1638 /*****************************************************************************/
1639 -- Procedure: check_cust_setup_flag_items
1640 --
1641 -- History
1642 -- 11/29/1999 julou created
1643 -------------------------------------------------------------------------------
1644 PROCEDURE check_cust_setup_flag_items
1645 (
1646 p_cust_setup_rec IN cust_setup_rec_type,
1647 x_return_status OUT NOCOPY VARCHAR2
1648 )
1649 IS
1650
1651 l_mand_flag VARCHAR2(1);
1652
1653 BEGIN
1654
1655 x_return_status := FND_API.g_ret_sts_success;
1656
1657 -- enabled_flag
1658 IF p_cust_setup_rec.enabled_flag NOT IN ('Y','N',FND_API.g_miss_char)
1659 AND p_cust_setup_rec.enabled_flag IS NOT NULL
1660 THEN
1661 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1662 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_BAD_ENBL_FLAG');
1663 FND_MSG_PUB.add;
1664 END IF;
1665
1666 x_return_status := FND_API.g_ret_sts_error;
1667 RETURN;
1668 END IF;
1669
1670 END check_cust_setup_flag_items;
1671
1672
1673 /*****************************************************************************/
1674 -- Procedure: complete_cust_setup_rec
1675 --
1676 -- History
1677 -- 11/29/1999 julou created
1678 -------------------------------------------------------------------------------
1679 PROCEDURE complete_cust_setup_rec
1680 (
1681 p_cust_setup_rec IN cust_setup_rec_type,
1682 x_complete_rec OUT NOCOPY cust_setup_rec_type
1683 )
1684 IS
1685
1686 CURSOR c_cust_setup IS
1687 SELECT * FROM AMS_CUSTOM_SETUPS_VL
1688 WHERE custom_setup_id = p_cust_setup_rec.custom_setup_id;
1689
1690 l_cust_setup_rec c_cust_setup%ROWTYPE;
1691
1692 BEGIN
1693
1694 x_complete_rec := p_cust_setup_rec;
1695
1696 OPEN c_cust_setup;
1697 FETCH c_cust_setup INTO l_cust_setup_rec;
1698 IF (c_cust_setup%NOTFOUND) THEN
1699 CLOSE c_cust_setup;
1700 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1701 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1702 FND_MSG_PUB.add;
1703 END IF;
1704 RAISE FND_API.g_exc_error;
1705 END IF;
1706 CLOSE c_cust_setup;
1707
1708 IF p_cust_setup_rec.object_type = FND_API.g_miss_char THEN
1709 x_complete_rec.object_type := l_cust_setup_rec.object_type;
1710 END IF;
1711
1712 IF p_cust_setup_rec.enabled_flag = FND_API.g_miss_char THEN
1713 x_complete_rec.enabled_flag := l_cust_setup_rec.enabled_flag;
1714 END IF;
1715
1716 IF p_cust_setup_rec.allow_essential_grouping = FND_API.g_miss_char THEN
1717 x_complete_rec.allow_essential_grouping := l_cust_setup_rec.allow_essential_grouping;
1718 END IF;
1719
1720 IF p_cust_setup_rec.usage = FND_API.g_miss_char THEN
1721 x_complete_rec.usage := l_cust_setup_rec.usage;
1722 END IF;
1723
1724 -- IF p_cust_setup_rec.object_type NOT IN ('CAMP','ECAM','RCAM') THEN
1725 -- x_complete_rec.activity_type_code := NULL;
1726 -- x_complete_rec.media_id := NULL;
1727 -- ELSE
1728 IF p_cust_setup_rec.activity_type_code = FND_API.g_miss_char THEN
1729 x_complete_rec.activity_type_code := l_cust_setup_rec.activity_type_code;
1730 END IF;
1731
1732 IF p_cust_setup_rec.source_code_suffix = FND_API.g_miss_char THEN
1733 x_complete_rec.source_code_suffix := l_cust_setup_rec.source_code_suffix;
1734 END IF;
1735
1736 IF p_cust_setup_rec.media_id = FND_API.g_miss_num THEN
1737 x_complete_rec.media_id := l_cust_setup_rec.media_id;
1738 END IF;
1739 -- END IF;
1740
1741 IF p_cust_setup_rec.setup_name = FND_API.g_miss_char THEN
1742 x_complete_rec.setup_name := l_cust_setup_rec.setup_name;
1743 END IF;
1744
1745 IF p_cust_setup_rec.description = FND_API.g_miss_char THEN
1746 x_complete_rec.description := l_cust_setup_rec.description;
1747 END IF;
1748
1749 END complete_cust_setup_rec;
1750
1751
1752 /****************************************************************************/
1753 -- Procedure
1754 -- init_rec
1755 --
1756 -- HISTORY
1757 -- 12/19/1999 julou Created.
1758 ------------------------------------------------------------------------------
1759 PROCEDURE init_rec
1760 (
1761 x_cust_setup_rec OUT NOCOPY cust_setup_rec_type
1762 )
1763 IS
1764
1765 BEGIN
1766
1767 x_cust_setup_rec.custom_setup_id := FND_API.g_miss_num;
1768 x_cust_setup_rec.last_update_date := FND_API.g_miss_date;
1769 x_cust_setup_rec.last_updated_by := FND_API.g_miss_num;
1770 x_cust_setup_rec.creation_date := FND_API.g_miss_date;
1771 x_cust_setup_rec.created_by := FND_API.g_miss_num;
1772 x_cust_setup_rec.last_update_login := FND_API.g_miss_num;
1773 x_cust_setup_rec.object_version_number := FND_API.g_miss_num;
1774 x_cust_setup_rec.activity_type_code := FND_API.g_miss_char;
1775 x_cust_setup_rec.media_id := FND_API.g_miss_num;
1776 x_cust_setup_rec.enabled_flag := FND_API.g_miss_char;
1777 x_cust_setup_rec.object_type := FND_API.g_miss_char;
1778 x_cust_setup_rec.source_code_suffix := FND_API.g_miss_char;
1779 x_cust_setup_rec.setup_name := FND_API.g_miss_char;
1780 x_cust_setup_rec.description := FND_API.g_miss_char;
1781 x_cust_setup_rec.allow_essential_grouping := FND_API.g_miss_char;
1782 x_cust_setup_rec.usage := FND_API.g_miss_char;
1783
1784 END init_rec;
1785
1786 END AMS_Cust_Setup_PVT;