[Home] [Help]
PACKAGE BODY: APPS.AMS_SETUP_ATTR_PVT
Source
1 PACKAGE BODY AMS_Setup_Attr_PVT AS
2 /* $Header: amsvattb.pls 115.30 2003/03/11 06:39:08 cgoyal ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_Setup_Attr_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_setup_attr
8 --
9 -- History
10 -- 12/1/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_setup_attr
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_setup_attr_rec IN setup_attr_rec_type,
28 x_setup_attr_id OUT NOCOPY NUMBER
29 )
30 IS
31
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'create_setup_attr';
34 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
35 l_return_status VARCHAR2(1);
36 l_setup_attr_rec setup_attr_rec_type := p_setup_attr_rec;
37 l_setup_attr_count NUMBER;
38
39 CURSOR c_setup_attr_seq IS
40 SELECT AMS_CUSTOM_SETUP_ATTR_S.NEXTVAL
41 FROM DUAL;
42
43 CURSOR c_setup_attr_count(setup_attr_id IN NUMBER) IS
44 SELECT COUNT(*)
45 FROM AMS_CUSTOM_SETUP_ATTR
46 WHERE setup_attribute_id = setup_attr_id;
47
48 BEGIN
49 -- initialize
50 SAVEPOINT create_setup_attr;
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
76 x_return_status := FND_API.g_ret_sts_success;
77
78 -- validate
79 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
80 IF (AMS_DEBUG_HIGH_ON) THEN
81
82 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
83 END IF;
84
85 validate_setup_attr
86 (
87 p_api_version => l_api_version,
88 p_init_msg_list => p_init_msg_list,
89 p_validation_level => p_validation_level,
90 x_return_status => l_return_status,
91 x_msg_count => x_msg_count,
92 x_msg_data => x_msg_data,
93 p_setup_attr_rec => l_setup_attr_rec
94 );
95
96 IF l_return_status = FND_API.g_ret_sts_error THEN
97 RAISE FND_API.g_exc_error;
98 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
99 RAISE FND_API.g_exc_unexpected_error;
100 END IF;
101 END IF;
102
103 -- insert
104 IF (AMS_DEBUG_HIGH_ON) THEN
105
106 AMS_Utility_PVT.debug_message(l_full_name || ': insert');
107 END IF;
108
109 IF l_setup_attr_rec.setup_attribute_id IS NULL THEN
110 LOOP
111 OPEN c_setup_attr_seq;
112 FETCH c_setup_attr_seq INTO l_setup_attr_rec.setup_attribute_id;
113 CLOSE c_setup_attr_seq;
114
115 OPEN c_setup_attr_count(l_setup_attr_rec.setup_attribute_id);
116 FETCH c_setup_attr_count INTO l_setup_attr_count;
117 CLOSE c_setup_attr_count;
118
119 EXIT WHEN l_setup_attr_count = 0;
120 END LOOP;
121 END IF;
122
123 INSERT INTO AMS_CUSTOM_SETUP_ATTR
124 (
125 setup_attribute_id,
126 custom_setup_id,
127 last_update_date,
128 last_updated_by,
129 creation_date,
130 created_by,
131 object_version_number,
132 last_update_login,
133 display_sequence_no,
134 object_attribute,
135 attr_mandatory_flag,
136 attr_available_flag,
137 function_name,
138 parent_function_name,
139 parent_setup_attribute,
140 parent_display_sequence,
141 show_in_report,
142 show_in_cue_card,
143 copy_allowed_flag,
144 related_ak_attribute,
145 essential_seq_num
146 )
147 VALUES
148 (
149 l_setup_attr_rec.setup_attribute_id,
150 l_setup_attr_rec.custom_setup_id,
151 SYSDATE,
152 FND_GLOBAL.user_id,
153 SYSDATE,
154 FND_GLOBAL.user_id,
155 1,
156 FND_GLOBAL.conc_login_id,
157 l_setup_attr_rec.display_sequence_no,
158 l_setup_attr_rec.object_attribute,
159 l_setup_attr_rec.attr_mandatory_flag,
160 NVL(l_setup_attr_rec.attr_available_flag,'Y'),
161 l_setup_attr_rec.function_name,
162 l_setup_attr_rec.parent_function_name,
163 l_setup_attr_rec.parent_setup_attribute,
164 l_setup_attr_rec.parent_display_sequence,
165 nvl(l_setup_attr_rec.show_in_report,'Y'),
166 nvl(l_setup_attr_rec.show_in_cue_card,'Y'),
167 nvl(l_setup_attr_rec.copy_allowed_flag,'N'),
168 l_setup_attr_rec.related_ak_attribute,
169 l_setup_attr_rec.essential_seq_num
170 );
171
172 -- finish
173 x_setup_attr_id := l_setup_attr_rec.setup_attribute_id;
174
175 IF FND_API.to_boolean(p_commit) THEN
176 COMMIT;
177 END IF;
178
179 FND_MSG_PUB.count_and_get
180 (
181 p_encoded => FND_API.g_false,
182 p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185
186 IF (AMS_DEBUG_HIGH_ON) THEN
187
188
189
190 AMS_Utility_PVT.debug_message(l_full_name||': end');
191
192 END IF;
193
194 EXCEPTION
195
196 WHEN FND_API.g_exc_error THEN
197 ROLLBACK TO create_setup_attr;
198 x_return_status := FND_API.g_ret_sts_error;
199 FND_MSG_PUB.count_and_get
200 (
201 p_encoded => FND_API.g_false,
202 p_count => x_msg_count,
203 p_data => x_msg_data
204 );
205
206 WHEN FND_API.g_exc_unexpected_error THEN
207 ROLLBACK TO create_setup_attr;
208 x_return_status := FND_API.g_ret_sts_unexp_error;
209 FND_MSG_PUB.count_and_get
210 (
211 p_encoded => FND_API.g_false,
212 p_count => x_msg_count,
213 p_data => x_msg_data
214 );
215
216 WHEN OTHERS THEN
217 ROLLBACK TO create_setup_attr;
218 x_return_status :=FND_API.g_ret_sts_unexp_error;
219 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
220 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
221 END IF;
222 FND_MSG_PUB.count_and_get
223 (
224 p_encoded => FND_API.g_false,
225 p_count => x_msg_count,
226 p_data => x_msg_data
227 );
228
229 END create_setup_attr;
230
231
232 /*****************************************************************************/
233 -- Procedure: update_setup_attr
234 --
235 -- History
236 -- 12/1/1999 julou created
237 -------------------------------------------------------------------------------
238 PROCEDURE update_setup_attr
239 (
240 p_api_version IN NUMBER,
241 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
242 p_commit IN VARCHAR2 := FND_API.g_false,
243 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
244
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248
249 p_setup_attr_rec IN setup_attr_rec_type
250 )
251 IS
252
253 l_api_version CONSTANT NUMBER := 1.0;
254 l_api_name CONSTANT VARCHAR2(30) := 'update_setup_attr';
255 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
256 l_return_status VARCHAR2(1);
257 l_setup_attr_rec setup_attr_rec_type := p_setup_attr_rec;
258
259 BEGIN
260
261 -- initialize
262 SAVEPOINT update_setup_attr;
263
264 IF FND_API.to_boolean(p_init_msg_list) THEN
265 FND_MSG_PUB.initialize;
266 END IF;
267
268 x_return_status := FND_API.g_ret_sts_success;
269
270 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': start of api'); END IF;
271
272 /***
273 IF NOT FND_API.compatible_api_call
274 (
275 l_api_version,
276 p_api_version,
277 l_api_name,
278 g_pkg_name
279 )
280 THEN
281 RAISE FND_API.g_exc_unexpected_error;
282 END IF;
283 **/
284
285
286 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': complete '); END IF;
287
288 -- complete record
289 complete_setup_attr_rec
290 (
291 p_setup_attr_rec,
292 l_setup_attr_rec
293 );
294
295 -- validate
296 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
297 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': validate'); END IF;
298
299 check_items
300 (
301 p_validation_mode => JTF_PLSQL_API.g_update,
302 x_return_status => l_return_status,
303 p_setup_attr_rec => l_setup_attr_rec
304 );
305
306 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
307 RAISE FND_API.g_exc_unexpected_error;
308 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
309 RAISE FND_API.g_exc_error;
310 END IF;
311 END IF;
312
313 -- update
314 IF (AMS_DEBUG_HIGH_ON) THEN
315
316 AMS_Utility_PVT.debug_message(l_full_name||': update');
317 END IF;
318
319 UPDATE AMS_CUSTOM_SETUP_ATTR SET
320 custom_setup_id = l_setup_attr_rec.custom_setup_id,
321 last_update_date = SYSDATE,
322 last_updated_by = FND_GLOBAL.user_id,
323 object_version_number = l_setup_attr_rec.object_version_number + 1,
324 last_update_login = FND_GLOBAL.conc_login_id,
325 display_sequence_no = l_setup_attr_rec.display_sequence_no,
326 object_attribute = l_setup_attr_rec.object_attribute,
327 attr_mandatory_flag = l_setup_attr_rec.attr_mandatory_flag,
328 attr_available_flag = l_setup_attr_rec.attr_available_flag,
329 function_name = l_setup_attr_rec.function_name,
330 parent_function_name = l_setup_attr_rec.parent_function_name,
331 parent_setup_attribute = l_setup_attr_rec.parent_setup_attribute,
332 parent_display_sequence = l_setup_attr_rec.parent_display_sequence,
333 show_in_report = nvl(l_setup_attr_rec.show_in_report,'Y'),
334 related_ak_attribute = l_setup_attr_rec.related_ak_attribute,
335 essential_seq_num = l_setup_attr_rec.essential_seq_num
336 WHERE setup_attribute_id = l_setup_attr_rec.setup_attribute_id
337 AND object_version_number = l_setup_attr_rec.object_version_number;
338
339 IF (SQL%NOTFOUND) THEN
340 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
341 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
342 FND_MSG_PUB.add;
343 END IF;
344 RAISE FND_API.g_exc_error;
345 END IF;
346
347 -- finish
348 IF FND_API.to_boolean(p_commit) THEN
349 COMMIT;
350 END IF;
351
352 FND_MSG_PUB.count_and_get
353 (
354 P_ENCODED => FND_API.g_false,
355 p_count => x_msg_count,
356 p_data => x_msg_data
357 );
358
359 IF (AMS_DEBUG_HIGH_ON) THEN
360
361
362
363 AMS_Utility_PVT.debug_message(l_full_name || ': end');
364
365 END IF;
366
367 EXCEPTION
368
369 WHEN FND_API.g_exc_error THEN
370 ROLLBACK TO update_setup_attr;
371 x_return_status := FND_API.g_ret_sts_error;
372 FND_MSG_PUB.count_and_get
373 (
374 p_encoded => FND_API.g_false,
375 p_count => x_msg_count,
376 p_data => x_msg_data
377 );
378
379 WHEN FND_API.g_exc_unexpected_error THEN
380 ROLLBACK TO update_setup_attr;
381 x_return_status := FND_API.g_ret_sts_unexp_error;
382 FND_MSG_PUB.count_and_get
383 (
384 p_encoded => FND_API.g_false,
385 p_count => x_msg_count,
386 p_data => x_msg_data
387 );
388
389 WHEN OTHERS THEN
390 ROLLBACK TO update_setup_attr;
391 x_return_status :=FND_API.g_ret_sts_unexp_error;
392 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
393 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
394 END IF;
395 FND_MSG_PUB.count_and_get
396 (
397 p_encoded => FND_API.g_false,
398 p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401
402 END update_setup_attr;
403
404
405 /*****************************************************************************/
406 -- PROCEDURE
407 -- validate_setup_attr
408 --
409 -- HISTORY
410 -- 11/29/99 julou Created.
411 --------------------------------------------------------------------
412 PROCEDURE validate_setup_attr
413 (
414 p_api_version IN NUMBER,
415 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
416 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
417
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2,
421
422 p_setup_attr_rec IN setup_attr_rec_type
423 )
424 IS
425
426 l_api_version CONSTANT NUMBER := 1.0;
427 l_api_name CONSTANT VARCHAR2(30) := 'validate_setup_attr';
428 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
429
430 l_return_status VARCHAR2(1);
431
432 BEGIN
433
434 ----------------------- initialize --------------------
435 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name||': start start '); END IF;
436
437 IF FND_API.to_boolean(p_init_msg_list) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440
441 /** IF NOT FND_API.compatible_api_call(
442 l_api_version,
443 p_api_version,
444 l_api_name,
445 g_pkg_name
446 ) THEN
447 RAISE FND_API.g_exc_unexpected_error;
448 END IF;
449 **/
450
451 x_return_status := FND_API.g_ret_sts_success;
452
453 ---------------------- validate ------------------------
454 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
455 IF (AMS_DEBUG_HIGH_ON) THEN
456
457 AMS_Utility_PVT.debug_message(l_full_name||': check items');
458 END IF;
459 check_items
460 (
461 p_validation_mode => JTF_PLSQL_API.g_create,
462 x_return_status => l_return_status,
463 p_setup_attr_rec => p_setup_attr_rec
464 );
465
466 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
467 RAISE FND_API.g_exc_unexpected_error;
468 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
469 RAISE FND_API.g_exc_error;
470 END IF;
471 END IF;
472
473 -------------------- finish --------------------------
474 FND_MSG_PUB.count_and_get(
475 p_encoded => FND_API.g_false,
476 p_count => x_msg_count,
477 p_data => x_msg_data
478 );
479
480 IF (AMS_DEBUG_HIGH_ON) THEN
481
482
483
484 AMS_Utility_PVT.debug_message(l_full_name ||': end');
485
486 END IF;
487
488 EXCEPTION
489 WHEN FND_API.g_exc_error THEN
490 x_return_status := FND_API.g_ret_sts_error;
491 FND_MSG_PUB.count_and_get
492 (
493 p_encoded => FND_API.g_false,
494 p_count => x_msg_count,
495 p_data => x_msg_data
496 );
497
498 WHEN FND_API.g_exc_unexpected_error THEN
499 x_return_status := FND_API.g_ret_sts_unexp_error ;
500 FND_MSG_PUB.count_and_get
501 (
502 p_encoded => FND_API.g_false,
503 p_count => x_msg_count,
504 p_data => x_msg_data
505 );
506
507 WHEN OTHERS THEN
508 x_return_status := FND_API.g_ret_sts_unexp_error;
509 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
510 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
511 END IF;
512
513 FND_MSG_PUB.count_and_get
514 (
515 p_encoded => FND_API.g_false,
516 p_count => x_msg_count,
517 p_data => x_msg_data
518 );
519
520 END validate_setup_attr;
521
522
523 /*****************************************************************************/
524 -- Procedure: check_items
525 --
526 -- History
527 -- 12/1/1999 julou created
528 -------------------------------------------------------------------------------
529 PROCEDURE check_items
530 (
531 p_validation_mode IN VARCHAR2,
532 x_return_status OUT NOCOPY VARCHAR2,
533 p_setup_attr_rec IN setup_attr_rec_type
534 )
535 IS
536
537 l_api_version CONSTANT NUMBER := 1.0;
538 l_api_name CONSTANT VARCHAR2(30) := 'check items';
539 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
540
541 BEGIN
542 -- initialize
543 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': start'); END IF;
544
545 x_return_status := FND_API.g_ret_sts_success;
546
547 -- check required items
548 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': check required items'); END IF;
549 check_setup_attr_req_items
550 (
551 p_validation_mode => p_validation_mode,
552 p_setup_attr_rec => p_setup_attr_rec,
553 x_return_status => x_return_status
554 );
555
556 IF x_return_status <> FND_API.g_ret_sts_success THEN
557 RETURN;
558 END IF;
559
560 -- check unique key items
561 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': check uk items'); END IF;
562 /*
563 check_setup_attr_uk_items
564 (
565 p_setup_attr_rec => p_setup_attr_rec,
566 x_return_status => x_return_status
567 );
568
569 IF x_return_status <> FND_API.g_ret_sts_success THEN
570 RETURN;
571 END IF;
572 */
573 -- check foreign key items
574 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': check fk items'); END IF;
575 check_setup_attr_fk_items
576 (
577 p_setup_attr_rec => p_setup_attr_rec,
578 x_return_status => x_return_status
579 );
580
581 IF x_return_status <> FND_API.g_ret_sts_success THEN
582 RETURN;
583 END IF;
584
585 -- check flag items
586 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(l_full_name || ': check flag items'); END IF;
587 check_setup_attr_flag_items
588 (
589 p_setup_attr_rec => p_setup_attr_rec,
590 x_return_status => x_return_status
591 );
592
593 IF x_return_status <> FND_API.g_ret_sts_success THEN
594 RETURN;
595 END IF;
596
597 END check_items;
598
599
600 /*****************************************************************************/
601 -- Procedure: check_setup_attr_req_items
602 --
603 -- History
604 -- 12/1/1999 julou created
605 -------------------------------------------------------------------------------
606 PROCEDURE check_setup_attr_req_items
607 (
608 p_validation_mode IN VARCHAR2,
609 p_setup_attr_rec IN setup_attr_rec_type,
610 x_return_status OUT NOCOPY VARCHAR2
611 )
612 IS
613
614 BEGIN
615
616 x_return_status := FND_API.g_ret_sts_success;
617
618 -- check setup_attribute_id
619 IF p_setup_attr_rec.setup_attribute_id IS NULL
620 AND p_validation_mode = JTF_PLSQL_API.g_update THEN
621 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
622 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_SETUP_ATT_ID');
623 FND_MSG_PUB.add;
624 END IF;
625
626 x_return_status := FND_API.g_ret_sts_error;
627 RETURN;
628 END IF;
629
630 -- check object_version_number
631 IF p_setup_attr_rec.object_version_number IS NULL
632 AND p_validation_mode = JTF_PLSQL_API.g_update
633 THEN
634 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
635 FND_MESSAGE.set_name('AMS', 'AMS_API_NO_OBJ_VER_NUM');
636 FND_MSG_PUB.add;
637 END IF;
638
639 x_return_status := FND_API.g_ret_sts_error;
640 RETURN;
641 END IF;
642
643 -- check custom_setup_id
644 IF p_setup_attr_rec.custom_setup_id IS NULL THEN
645 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
646 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_CUS_SETUP_ID');
647 FND_MSG_PUB.add;
648 END IF;
649
650 x_return_status := FND_API.g_ret_sts_error;
651 RETURN;
652 END IF;
653
654 -- check display_sequence_no
655 IF p_setup_attr_rec.display_sequence_no IS NULL THEN
656 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
657 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_DIS_SEQ_NO');
658 FND_MSG_PUB.add;
659 END IF;
660
661 x_return_status := FND_API.g_ret_sts_error;
662 RETURN;
663 END IF;
664
665 -- check object_attribute
666 IF p_setup_attr_rec.object_attribute IS NULL THEN
667 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
668 FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_NO_OBJ_ATTR');
669 FND_MSG_PUB.add;
670 END IF;
671
672 x_return_status := FND_API.g_ret_sts_error;
673 RETURN;
674 END IF;
675
676 -- check attr_mandatory_flag
677 IF p_setup_attr_rec.attr_mandatory_flag IS NULL THEN
678 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
679 -- Change message here from Mandatory to Essential
680 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_ATT_ESS_FLAG');
681 FND_MSG_PUB.add;
682 END IF;
683
684 x_return_status := FND_API.g_ret_sts_error;
685 RETURN;
686 END IF;
687
688 -- Put the check for the essential seq num here
689 IF (nvl(p_setup_attr_rec.attr_mandatory_flag, 'N') = 'Y' AND
690 p_setup_attr_rec.essential_seq_num IS NULL) THEN
691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
692 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_ESS_SEQ_NUM');
693 FND_MSG_PUB.add;
694 END IF;
695
696 x_return_status := FND_API.g_ret_sts_error;
697 RETURN;
698 END IF;
699
700 -- check attr_available_flag
701 IF p_setup_attr_rec.attr_available_flag IS NULL THEN
702 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
703 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATT_NO_ATT_AVAL_FLAG');
704 FND_MSG_PUB.add;
705 END IF;
706
707 x_return_status := FND_API.g_ret_sts_error;
708 RETURN;
709 END IF;
710
711 END check_setup_attr_req_items;
712
713
714 /*****************************************************************************/
715 -- Procedure: check_setup_attr_uk_items
716 --
717 -- History
718 -- 11/29/1999 julou created
719 -------------------------------------------------------------------------------
720 PROCEDURE check_setup_attr_uk_items
721 (
722 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
723 p_setup_attr_rec IN setup_attr_rec_type,
724 x_return_status OUT NOCOPY VARCHAR2
725 )
726 IS
727
728 l_uk_flag VARCHAR2(1);
729 l_dummy NUMBER;
730
731 CURSOR c_ess_seq_exists(seq_num_in IN NUMBER, id_in IN NUMBER, setup_id_in IN NUMBER) IS
732 SELECT 1 FROM dual
733 WHERE EXISTS (SELECT 1 FROM ams_custom_setup_attr
734 WHERE custom_setup_id = id_in
735 AND essential_seq_num = seq_num_in
736 AND setup_attribute_id <> setup_id_in
737 );
738 BEGIN
739
740 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message(' UK Check '); END IF;
741
742 x_return_status := FND_API.g_ret_sts_success;
743
744 -- check PK, if custom_setup_id is passed in, must check if it is duplicate
745
746 /***
747
748 IF p_validation_mode = JTF_PLSQL_API.g_create
749 AND p_setup_attr_rec.setup_attribute_id IS NOT NULL
750 THEN
751 l_uk_flag := AMS_Utility_PVT.check_uniqueness
752 (
753 'AMS_CUSTOM_SETUP_ATTR',
754 'setup_attribute_id = ' || p_setup_attr_rec.setup_attribute_id
755 );
756 END IF;
757
758 IF l_uk_flag = FND_API.g_false THEN
759 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
760 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_DUPLICATE_ID');
761 FND_MSG_PUB.add;
762 END IF;
763
764
765 x_return_status := FND_API.g_ret_sts_error;
766 RETURN;
767 END IF;
768 *****/
769
770 IF p_validation_mode = Jtf_Plsql_Api.g_create
771 THEN
772 OPEN c_ess_seq_exists (p_setup_attr_rec.essential_seq_num,
773 p_setup_attr_rec.custom_setup_id,
774 p_setup_attr_rec.setup_attribute_id);
775 FETCH c_ess_seq_exists INTO l_dummy;
776 CLOSE c_ess_seq_exists;
777
778 IF l_dummy = 1 THEN
779 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR)
780 THEN
781 Fnd_Message.set_name('AMS', 'AMS_ESSENTIAL_DUP_SEQ');
782 Fnd_Msg_Pub.ADD;
783 x_return_status := Fnd_Api.g_ret_sts_error;
784 RETURN;
785 END IF;
786 END IF;
787 END IF;
788
789 END check_setup_attr_uk_items;
790
791
792 /*****************************************************************************/
793 -- Procedure: check_setup_attr_fk_items
794 --
795 -- History
796 -- 12/1/1999 julou created
797 -------------------------------------------------------------------------------
798 PROCEDURE check_setup_attr_fk_items
799 (
800 p_setup_attr_rec IN setup_attr_rec_type,
801 x_return_status OUT NOCOPY VARCHAR2
802 )
803 IS
804
805 l_fk_flag VARCHAR2(1);
806
807 BEGIN
808
809 IF (AMS_DEBUG_HIGH_ON) THEN
810
811
812
813 AMS_Utility_PVT.debug_message(' FK Check ');
814
815 END IF;
816 x_return_status := FND_API.g_ret_sts_success;
817
818 l_fk_flag := AMS_Utility_PVT.check_fk_exists
819 (
820 'AMS_CUSTOM_SETUPS_B',
821 'custom_setup_id',
822 p_setup_attr_rec.custom_setup_id
823 );
824
825 IF l_fk_flag = FND_API.g_false THEN
826 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
827 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_BAD_CUS_SET_ID');
828 FND_MSG_PUB.add;
829 END IF;
830
831 x_return_status := FND_API.g_ret_sts_error;
832 RETURN;
833 END IF;
834
835 END check_setup_attr_fk_items;
836
837
838 /*****************************************************************************/
839 -- Procedure: check_setup_attr_flag_items
840 --
841 -- History
842 -- 12/1/1999 julou created
843 -- 29-Dec-2000 ptendulk 1.Added Additional cursor to get the object details
844 -- from ams_custom_setups_vl
845 -- 2.Check Mandatory flag for object type and activity
846 -- type
847 -- 19-OCT-2001 julou modified. fix for bug 2064453
848 -- added cursor to check mandatory flag for setup with
849 -- no activity type.
850 --
851 -- Note
852 -- Desc of the fix done by ptendulk on 29 Dec
853 -- Current code check for mandatory flag for attribute instead of checking for
854 -- object or attribute. Due to this when the custom setup is created for Events
855 -- cost and metric is not mandatory but when query selects for the attribute,
856 -- it selects for campaign, and it gives Mandatory flag as Y and creation
857 -- errors out for events (It copies attribute till cost)
858 -------------------------------------------------------------------------------
859 PROCEDURE check_setup_attr_flag_items
860 (
861 p_setup_attr_rec IN setup_attr_rec_type,
862 x_return_status OUT NOCOPY VARCHAR2
863 )
864 IS
865
866 CURSOR c_mand_flag1 (obj_attr IN VARCHAR2,l_obj_type IN VARCHAR2,
867 l_act_type IN VARCHAR2) IS
868 SELECT mandatory_flag FROM AMS_SETUP_TYPES
869 WHERE setup_attribute = obj_attr
870 -- Following lines are added by ptendulk on 29th Dec
871 AND object_type = l_obj_type
872 AND activity_type_code = l_act_type ;
873 -- cursor added by julou 19-OCT-2001
874 CURSOR c_mand_flag2 (obj_attr IN VARCHAR2,l_obj_type IN VARCHAR2) IS
875 SELECT mandatory_flag FROM AMS_SETUP_TYPES
876 WHERE setup_attribute = obj_attr
877 -- Following lines are added by ptendulk on 29th Dec
878 AND object_type = l_obj_type
879 AND activity_type_code IS NULL ;
880
881 -- Following code is added by ptendulk on 29 Dec
882 CURSOR c_cus_det IS
883 SELECT object_type,activity_type_code
884 FROM ams_custom_setups_vl
885 WHERE custom_setup_id = p_setup_attr_rec.custom_setup_id ;
886
887 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
888 CURSOR c_mand_setup_attr (obj_attr IN VARCHAR2) IS
889 SELECT MEANING FROM AMS_LOOKUPS WHERE LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' AND LOOKUP_CODE = obj_attr;
890 l_mand_setup_attr_rec c_mand_setup_attr%ROWTYPE;
891 -- End add by cgoyal
892
893 l_obj VARCHAR2(30) ;
894 l_act VARCHAR2(30) ;
895
896 l_mand_flag VARCHAR2(1);
897 l_mand_setup_attr AMS_LOOKUPS.meaning%type;
898
899 BEGIN
900 x_return_status := FND_API.g_ret_sts_success;
901 l_mand_setup_attr := NULL;
902 IF p_setup_attr_rec.attr_mandatory_flag NOT IN ('Y','N',FND_API.g_miss_char,NULL)
903 THEN
904 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
905 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
906 IF l_mand_setup_attr IS NULL THEN
907 OPEN c_mand_setup_attr (p_setup_attr_rec.object_attribute);
908 FETCH c_mand_setup_attr
909 INTO l_mand_setup_attr_rec;
910 IF (c_mand_setup_attr%NOTFOUND) THEN
911 CLOSE c_mand_setup_attr;
912 l_mand_setup_attr := NULL;
913 ELSE
914 l_mand_setup_attr := l_mand_setup_attr_rec.meaning;
915 CLOSE c_mand_setup_attr;
916 END IF;
917 END IF;
918 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_INVALID_ESS_FLG');
919 FND_MESSAGE.set_token('ESS_SETUP_ATTR', l_mand_setup_attr);
920 -- End Add by cgoyal
921 FND_MSG_PUB.add;
922 END IF;
923 x_return_status := FND_API.g_ret_sts_error;
924 RETURN;
925 END IF;
926
927 IF p_setup_attr_rec.attr_available_flag NOT IN ('Y','N',FND_API.g_miss_char,NULL)
928 THEN
929 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
930 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
931 IF l_mand_setup_attr IS NULL THEN
932 OPEN c_mand_setup_attr (p_setup_attr_rec.object_attribute);
933 FETCH c_mand_setup_attr
934 INTO l_mand_setup_attr_rec;
935 IF (c_mand_setup_attr%NOTFOUND) THEN
936 CLOSE c_mand_setup_attr;
937 l_mand_setup_attr := NULL;
938 ELSE
939 l_mand_setup_attr := l_mand_setup_attr_rec.meaning;
940 CLOSE c_mand_setup_attr;
941 END IF;
942 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_INVALID_AVL_FLG');
943 FND_MESSAGE.set_token('MAND_SETUP_ATTR', l_mand_setup_attr);
944 -- End Add by cgoyal
945 FND_MSG_PUB.add;
946 END IF;
947 END IF;
948 x_return_status := FND_API.g_ret_sts_error;
949 RETURN;
950 END IF;
951
952 -- start of code modified by ptendulk on 29th Dec
953 OPEN c_cus_det ;
954 FETCH c_cus_det INTO l_obj,l_act ;
955 CLOSE c_cus_det ;
956
957 IF l_act IS NOT NULL THEN
958 OPEN c_mand_flag1(p_setup_attr_rec.object_attribute,l_obj,l_act);
959 FETCH c_mand_flag1 INTO l_mand_flag;
960 CLOSE c_mand_flag1;
961 ELSE -- no activity_type_code, added by julou 19-OCT-2001
962 OPEN c_mand_flag2(p_setup_attr_rec.object_attribute,l_obj);
963 FETCH c_mand_flag2 INTO l_mand_flag;
964 CLOSE c_mand_flag2;
965 END IF;
966 -- End of code modified by ptendulk on 29th Dec.
967
968 IF (p_setup_attr_rec.OBJECT_ATTRIBUTE = 'DETL') AND (p_setup_attr_rec.attr_mandatory_flag <> 'Y') THEN
969 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
970 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
971 IF l_mand_setup_attr IS NULL THEN
972 OPEN c_mand_setup_attr (p_setup_attr_rec.object_attribute);
973 FETCH c_mand_setup_attr
974 INTO l_mand_setup_attr_rec;
975 IF (c_mand_setup_attr%NOTFOUND) THEN
976 CLOSE c_mand_setup_attr;
977 l_mand_setup_attr := NULL;
978 ELSE
979 l_mand_setup_attr := l_mand_setup_attr_rec.meaning;
980 CLOSE c_mand_setup_attr;
981 END IF;
982 END IF;
983 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_BAD_ESS_FLAG');
984 FND_MESSAGE.set_token('ESS_SETUP_ATTR', l_mand_setup_attr);
985 -- End Add by cgoyal
986 FND_MSG_PUB.add;
987 END IF;
988 x_return_status := FND_API.g_ret_sts_error;
989 RETURN;
990 END IF;
991
992 IF l_mand_flag = 'Y' THEN
993 IF p_setup_attr_rec.attr_available_flag <> 'Y' THEN
994 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
995 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
996 IF l_mand_setup_attr IS NULL THEN
997 OPEN c_mand_setup_attr (p_setup_attr_rec.object_attribute);
998 FETCH c_mand_setup_attr
999 INTO l_mand_setup_attr_rec;
1000 IF (c_mand_setup_attr%NOTFOUND) THEN
1001 CLOSE c_mand_setup_attr;
1002 l_mand_setup_attr := NULL;
1003 ELSE
1004 l_mand_setup_attr := l_mand_setup_attr_rec.meaning;
1005 CLOSE c_mand_setup_attr;
1006 END IF;
1007 END IF;
1008 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_BAD_AVAL_FLAG');
1009 FND_MESSAGE.set_token('MAND_SETUP_ATTR', l_mand_setup_attr);
1010 -- End Add by cgoyal
1011 FND_MSG_PUB.add;
1012 END IF;
1013 x_return_status := FND_API.g_ret_sts_error;
1014 RETURN;
1015 END IF;
1016 END IF;
1017
1018 IF p_setup_attr_rec.attr_mandatory_flag = 'Y'
1019 AND p_setup_attr_rec.attr_available_flag <> 'Y'
1020 THEN
1021 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1022 -- Added by cgoyal on 06/Feb/2002 for bugfix 2178381
1023 IF l_mand_setup_attr IS NULL THEN
1024 OPEN c_mand_setup_attr (p_setup_attr_rec.object_attribute);
1025 FETCH c_mand_setup_attr
1026 INTO l_mand_setup_attr_rec;
1027 IF (c_mand_setup_attr%NOTFOUND) THEN
1028 CLOSE c_mand_setup_attr;
1029 l_mand_setup_attr := NULL;
1030 ELSE
1031 l_mand_setup_attr := l_mand_setup_attr_rec.meaning;
1032 CLOSE c_mand_setup_attr;
1033 END IF;
1034 END IF;
1035 FND_MESSAGE.set_name('AMS', 'AMS_SETUP_ATTR_BAD_AVAL_FLAG');
1036 FND_MESSAGE.set_token('MAND_SETUP_ATTR', l_mand_setup_attr);
1037 -- End Add by cgoyal
1038 FND_MSG_PUB.add;
1039 END IF;
1040 x_return_status := FND_API.g_ret_sts_error;
1041 RETURN;
1042 END IF;
1043
1044 END check_setup_attr_flag_items;
1045
1046
1047 /*****************************************************************************/
1048 -- Procedure: complete_setup_attr_rec
1049 --
1050 -- History
1051 -- 12/1/1999 julou created
1052 -------------------------------------------------------------------------------
1053 PROCEDURE complete_setup_attr_rec
1054 (
1055 p_setup_attr_rec IN setup_attr_rec_type,
1056 x_complete_rec OUT NOCOPY setup_attr_rec_type
1057 )
1058 IS
1059
1060 CURSOR c_setup_attr IS
1061 SELECT * FROM AMS_CUSTOM_SETUP_ATTR
1062 WHERE setup_attribute_id = p_setup_attr_rec.setup_attribute_id;
1063
1064 l_setup_attr_rec c_setup_attr%ROWTYPE;
1065
1066 BEGIN
1067
1068 x_complete_rec := p_setup_attr_rec;
1069
1070 OPEN c_setup_attr;
1071 FETCH c_setup_attr INTO l_setup_attr_rec;
1072 IF (c_setup_attr%NOTFOUND) THEN
1073 CLOSE c_setup_attr;
1074 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1075 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1076 FND_MSG_PUB.add;
1077 END IF;
1078 RAISE FND_API.g_exc_error;
1079 END IF;
1080 CLOSE c_setup_attr;
1081
1082 IF p_setup_attr_rec.display_sequence_no = FND_API.g_miss_num THEN
1083 x_complete_rec.display_sequence_no := l_setup_attr_rec.display_sequence_no;
1084 END IF;
1085
1086 IF p_setup_attr_rec.parent_display_sequence = FND_API.g_miss_num THEN
1087 x_complete_rec.parent_display_sequence := l_setup_attr_rec.parent_display_sequence;
1088 END IF;
1089 IF p_setup_attr_rec.object_attribute = FND_API.g_miss_char THEN
1090 x_complete_rec.object_attribute := l_setup_attr_rec.object_attribute;
1091 END IF;
1092
1093 IF p_setup_attr_rec.attr_mandatory_flag = FND_API.g_miss_char THEN
1094 x_complete_rec.attr_available_flag := l_setup_attr_rec.attr_available_flag;
1095 END IF;
1096
1097 IF p_setup_attr_rec.attr_available_flag = FND_API.g_miss_char THEN
1098 x_complete_rec.attr_available_flag := l_setup_attr_rec.attr_available_flag;
1099 END IF;
1100
1101 IF p_setup_attr_rec.show_in_report = FND_API.g_miss_char THEN
1102 x_complete_rec.show_in_report := l_setup_attr_rec.show_in_report;
1103 END IF;
1104
1105 IF p_setup_attr_rec.copy_allowed_flag = FND_API.g_miss_char THEN
1106 x_complete_rec.copy_allowed_flag := l_setup_attr_rec.copy_allowed_flag;
1107 END IF;
1108
1109 IF p_setup_attr_rec.parent_setup_attribute= FND_API.g_miss_char THEN
1110 x_complete_rec.parent_setup_attribute := l_setup_attr_rec.parent_setup_attribute;
1111 END IF;
1112
1113 IF p_setup_attr_rec.related_ak_attribute = FND_API.g_miss_char THEN
1114 x_complete_rec.related_ak_attribute := l_setup_attr_rec.related_ak_attribute;
1115 END IF;
1116
1117 IF p_setup_attr_rec.parent_function_name = FND_API.g_miss_char THEN
1118 x_complete_rec.parent_function_name := l_setup_attr_rec.parent_function_name;
1119 END IF;
1120
1121 IF p_setup_attr_rec.function_name= FND_API.g_miss_char THEN
1122 x_complete_rec.function_name := l_setup_attr_rec.function_name;
1123 END IF;
1124
1125 IF p_setup_attr_rec.essential_seq_num = FND_API.g_miss_num THEN
1126 x_complete_rec.essential_seq_num := l_setup_attr_rec.essential_seq_num;
1127 END IF;
1128
1129 END complete_setup_attr_rec;
1130
1131
1132 /****************************************************************************/
1133 -- Procedure
1134 -- init_rec
1135 --
1136 -- HISTORY
1137 -- 12/19/1999 julou Created.
1138 ------------------------------------------------------------------------------
1139 PROCEDURE init_rec
1140 (
1141 x_setup_attr_rec OUT NOCOPY setup_attr_rec_type
1142 )
1143 IS
1144
1145 BEGIN
1146
1147 x_setup_attr_rec.setup_attribute_id := FND_API.g_miss_num;
1148 x_setup_attr_rec.custom_setup_id := FND_API.g_miss_num;
1149 x_setup_attr_rec.last_update_date := FND_API.g_miss_date;
1150 x_setup_attr_rec.last_updated_by := FND_API.g_miss_num;
1151 x_setup_attr_rec.creation_date := FND_API.g_miss_date;
1152 x_setup_attr_rec.created_by := FND_API.g_miss_num;
1153 x_setup_attr_rec.last_update_login := FND_API.g_miss_num;
1154 x_setup_attr_rec.object_version_number := FND_API.g_miss_num;
1155 x_setup_attr_rec.display_sequence_no := FND_API.g_miss_num;
1156 x_setup_attr_rec.object_attribute := FND_API.g_miss_char;
1157 x_setup_attr_rec.attr_mandatory_flag := FND_API.g_miss_char;
1158 x_setup_attr_rec.attr_available_flag := FND_API.g_miss_char;
1159 x_setup_attr_rec.function_name := FND_API.g_miss_char;
1160 x_setup_attr_rec.parent_function_name := FND_API.g_miss_char;
1161 x_setup_attr_rec.parent_setup_attribute := FND_API.g_miss_char;
1162 x_setup_attr_rec.parent_display_sequence := FND_API.g_miss_num;
1163 x_setup_attr_rec.show_in_report := FND_API.g_miss_char;
1164 x_setup_attr_rec.show_in_cue_card := FND_API.g_miss_char;
1165 x_setup_attr_rec.copy_allowed_flag := FND_API.g_miss_char;
1166 x_setup_attr_rec.related_ak_attribute := FND_API.g_miss_char;
1167 x_setup_attr_rec.essential_seq_num := FND_API.g_miss_num;
1168
1169 END init_rec;
1170
1171 END AMS_Setup_Attr_PVT;