[Home] [Help]
PACKAGE BODY: APPS.AMS_MESSAGES_PVT
Source
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_Messages_PVT';
1 PACKAGE BODY AMS_Messages_PVT AS
2 /* $Header: amsvmsgb.pls 115.22 2002/11/15 21:02:26 abhola ship $ */
3
5
6 /*****************************************************************************/
7 -- Procedure: create_msg
8 --
9 -- History
10 -- 01/04/2000 julou created
11 -- 11/29/2000 musman bug 1519059 fix.Changed the cursor in check_uk_items procedure.
12 -------------------------------------------------------------------------------
13 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
14 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
15 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
16
17 PROCEDURE create_msg
18 (
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
21 p_commit IN VARCHAR2 := FND_API.g_false,
22 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
23
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2,
27
28 p_msg_rec IN msg_rec_type,
29 x_msg_id OUT NOCOPY NUMBER
30 )
31 IS
32
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'create_msg';
35 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
36 l_return_status VARCHAR2(1);
37 l_msg_rec msg_rec_type := p_msg_rec;
38 l_msg_count NUMBER;
39
40 CURSOR c_msg_seq IS
41 SELECT AMS_MESSAGES_B_S.NEXTVAL
42 FROM DUAL;
43
44 CURSOR c_msg_count(msg_id IN NUMBER) IS
45 SELECT COUNT(*)
46 FROM AMS_MESSAGES_VL
47 WHERE message_id = msg_id;
48
49 BEGIN
50 -- initialize
51 SAVEPOINT create_msg;
52
53 IF FND_API.to_boolean(p_init_msg_list) THEN
54 FND_MSG_PUB.initialize;
55 END IF;
56
57
58 IF (AMS_DEBUG_HIGH_ON) THEN
59
60
61
62
63
64 AMS_Utility_PVT.debug_message(l_full_name || ': start');
65
66
67 END IF;
68
69 IF NOT FND_API.compatible_api_call
70 (
71 l_api_version,
72 p_api_version,
73 l_api_name,
74 g_pkg_name
75 )
76 THEN
77 RAISE FND_API.g_exc_unexpected_error;
78 END IF;
79
80 x_return_status := FND_API.g_ret_sts_success;
81
82 -- validate
83 IF (AMS_DEBUG_HIGH_ON) THEN
84
85 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
86 END IF;
87 validate_msg
88 (
89 p_api_version => l_api_version,
90 p_init_msg_list => p_init_msg_list,
91 p_validation_level => p_validation_level,
92 x_return_status => l_return_status,
93 x_msg_count => x_msg_count,
94 x_msg_data => x_msg_data,
95 p_msg_rec => l_msg_rec
96 );
97
98 IF l_return_status = FND_API.g_ret_sts_error THEN
99 RAISE FND_API.g_exc_error;
100 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
101 RAISE FND_API.g_exc_unexpected_error;
102 END IF;
103
104 -- insert
105 IF (AMS_DEBUG_HIGH_ON) THEN
106
107 AMS_Utility_PVT.debug_message(l_full_name || ': insert');
108 END IF;
109
110 IF l_msg_rec.message_id IS NULL THEN
111 LOOP
112 OPEN c_msg_seq;
113 FETCH c_msg_seq INTO l_msg_rec.message_id;
114 CLOSE c_msg_seq;
115
116 OPEN c_msg_count(l_msg_rec.message_id);
117 FETCH c_msg_count INTO l_msg_count;
118 CLOSE c_msg_count;
119
120 EXIT WHEN l_msg_count = 0;
121 END LOOP;
122 END IF;
123
124 /*
125 AMS_ObjectAttribute_PVT.create_object_attributes
126 (
127 p_api_version => p_api_version,
128 p_init_msg_list => p_init_msg_list,
129 p_validation_level => p_validation_level,
130 x_return_status => x_return_status,
131 x_msg_count => x_msg_count,
132 x_msg_data => x_msg_data,
133 p_object_type => 'MESG',
134 p_object_id => l_msg_rec.message_id,
135 p_setup_id => 1000
136 );
137 */
138
139 INSERT INTO AMS_MESSAGES_B
140 (
141 message_id,
142 last_update_date,
143 last_updated_by,
147 object_version_number,
144 creation_date,
145 created_by,
146 last_update_login,
148 date_effective_from,
149 date_effective_to,
150 active_flag,
151 message_type_code,
152 owner_user_id,
153 country_id,
154 custom_setup_id,
155 attribute_category,
156 attribute1,
157 attribute2,
158 attribute3,
159 attribute4,
160 attribute5,
161 attribute6,
162 attribute7,
163 attribute8,
164 attribute9,
165 attribute10,
166 attribute11,
167 attribute12,
168 attribute13,
169 attribute14,
170 attribute15
171 )
172 VALUES
173 (
174 l_msg_rec.message_id,
175 SYSDATE,
176 FND_GLOBAL.user_id,
177 SYSDATE,
178 FND_GLOBAL.user_id,
179 FND_GLOBAL.conc_login_id,
180 1,
181 l_msg_rec.date_effective_from,
182 l_msg_rec.date_effective_to,
183 l_msg_rec.active_flag,
184 l_msg_rec.message_type_code,
185 l_msg_rec.owner_user_id,
186 l_msg_rec.country_id,
187 1000,
188 l_msg_rec.attribute_category,
189 l_msg_rec.attribute1,
190 l_msg_rec.attribute2,
191 l_msg_rec.attribute3,
192 l_msg_rec.attribute4,
193 l_msg_rec.attribute5,
194 l_msg_rec.attribute6,
195 l_msg_rec.attribute7,
196 l_msg_rec.attribute8,
197 l_msg_rec.attribute9,
198 l_msg_rec.attribute10,
199 l_msg_rec.attribute11,
200 l_msg_rec.attribute12,
201 l_msg_rec.attribute13,
202 l_msg_rec.attribute14,
203 l_msg_rec.attribute15
204 );
205
206 INSERT INTO AMS_MESSAGES_TL
207 (
208 message_id,
209 language,
210 last_update_date,
211 last_updated_by,
212 creation_date,
213 created_by,
214 last_update_login,
215 source_lang,
216 message_name,
217 description
218 )
219 SELECT
220 l_msg_rec.message_id,
224 SYSDATE,
221 l.language_code,
222 SYSDATE,
223 FND_GLOBAL.user_id,
225 FND_GLOBAL.user_id,
226 FND_GLOBAL.conc_login_id,
227 USERENV('LANG'),
228 l_msg_rec.message_name,
229 l_msg_rec.description
230 FROM fnd_languages l
231 WHERE l.installed_flag in ('I', 'B')
232 AND NOT EXISTS
233 (
234 SELECT NULL
235 FROM AMS_MESSAGES_TL t
236 WHERE t.message_id = l_msg_rec.message_id
237 AND t.language = l.language_code
238 );
239
240 -- finish
241 x_msg_id := l_msg_rec.message_id;
242
243 IF FND_API.to_boolean(p_commit) THEN
244 COMMIT;
245 END IF;
246
247 FND_MSG_PUB.count_and_get
248 (
249 p_encoded => FND_API.g_false,
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 IF (AMS_DEBUG_HIGH_ON) THEN
255
256
257
258 AMS_Utility_PVT.debug_message(l_full_name||': end');
259
260 END IF;
261
262 EXCEPTION
263
264 WHEN FND_API.g_exc_error THEN
265 ROLLBACK TO create_msg;
266 x_return_status := FND_API.g_ret_sts_error;
267 FND_MSG_PUB.count_and_get
268 (
269 p_encoded => FND_API.g_false,
270 p_count => x_msg_count,
271 p_data => x_msg_data
272 );
273
274 WHEN FND_API.g_exc_unexpected_error THEN
275 ROLLBACK TO create_msg;
276 x_return_status := FND_API.g_ret_sts_unexp_error;
277 FND_MSG_PUB.count_and_get
278 (
279 p_encoded => FND_API.g_false,
280 p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283
284 WHEN OTHERS THEN
285 ROLLBACK TO create_msg;
286 x_return_status :=FND_API.g_ret_sts_unexp_error;
287 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
288 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
289 END IF;
290 FND_MSG_PUB.count_and_get
291 (
292 p_encoded => FND_API.g_false,
293 p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296
297 END create_msg;
298
299
300 /*****************************************************************************/
301 -- Procedure: update_msg
302 --
303 -- History
304 -- 01/04/2000 julou created
305 -------------------------------------------------------------------------------
306 PROCEDURE update_msg
307 (
308 p_api_version IN NUMBER,
312
309 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
310 p_commit IN VARCHAR2 := FND_API.g_false,
311 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_msg_count OUT NOCOPY NUMBER,
315 x_msg_data OUT NOCOPY VARCHAR2,
316
317 p_msg_rec IN msg_rec_type
318 )
319 IS
320
321 l_api_version CONSTANT NUMBER := 1.0;
322 l_api_name CONSTANT VARCHAR2(30) := 'update_msg';
323 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
324 l_return_status VARCHAR2(1);
325 l_msg_rec msg_rec_type := p_msg_rec;
326
327 BEGIN
328
329 -- initialize
330 SAVEPOINT update_msg;
331
332 IF FND_API.to_boolean(p_init_msg_list) THEN
333 FND_MSG_PUB.initialize;
334 END IF;
335
336 IF (AMS_DEBUG_HIGH_ON) THEN
337
338
339
340 AMS_Utility_PVT.debug_message(l_full_name || ': start');
341
342 END IF;
343
344 IF NOT FND_API.compatible_api_call
345 (
346 l_api_version,
347 p_api_version,
348 l_api_name,
349 g_pkg_name
350 )
351 THEN
352 RAISE FND_API.g_exc_unexpected_error;
353 END IF;
354
355 x_return_status := FND_API.g_ret_sts_success;
356
357 -- complete record
358 complete_msg_rec
359 (
360 p_msg_rec,
361 l_msg_rec
362 );
363
364 -- validate
365 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
366 IF (AMS_DEBUG_HIGH_ON) THEN
367
368 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
369 END IF;
370
371 check_items
372 (
373 p_validation_mode => JTF_PLSQL_API.g_update,
374 x_return_status => l_return_status,
375 p_msg_rec => l_msg_rec
376 );
377
378 IF l_return_status = FND_API.g_ret_sts_error THEN
379 RAISE FND_API.g_exc_error;
380 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
381 RAISE FND_API.g_exc_unexpected_error;
382 END IF;
383 END IF;
384 /*
385 -- complete record
386 complete_msg_rec
387 (
388 p_msg_rec,
389 l_msg_rec
390 );
391 */
392 -- record level
393 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
394 IF (AMS_DEBUG_HIGH_ON) THEN
395
396 AMS_Utility_PVT.debug_message(l_full_name||': check record');
397 END IF;
398 check_record
399 (
400 p_msg_rec => p_msg_rec,
401 p_complete_rec => l_msg_rec,
402 x_return_status => l_return_status
403 );
404
405 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
406 RAISE FND_API.g_exc_unexpected_error;
407 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
408 RAISE FND_API.g_exc_error;
409 END IF;
410 END IF;
411
412 -- update
413 IF (AMS_DEBUG_HIGH_ON) THEN
414
415 AMS_Utility_PVT.debug_message(l_full_name||': update');
416 END IF;
417
418 UPDATE AMS_MESSAGES_B SET
419 last_update_date = SYSDATE,
420 last_updated_by = FND_GLOBAL.user_id,
421 last_update_login = FND_GLOBAL.conc_login_id,
422 object_version_number = l_msg_rec.object_version_number + 1,
423 date_effective_from = l_msg_rec.date_effective_from,
424 date_effective_to = l_msg_rec.date_effective_to,
425 active_flag = l_msg_rec.active_flag,
426 message_type_code = l_msg_rec.message_type_code,
427 owner_user_id = l_msg_rec.owner_user_id,
428 attribute_category = l_msg_rec.attribute_category,
429 attribute1 = l_msg_rec.attribute1,
430 attribute2 = l_msg_rec.attribute2,
431 attribute3 = l_msg_rec.attribute3,
432 attribute4 = l_msg_rec.attribute4,
433 attribute5 = l_msg_rec.attribute5,
434 attribute6 = l_msg_rec.attribute6,
435 attribute7 = l_msg_rec.attribute7,
436 attribute8 = l_msg_rec.attribute8,
437 attribute9 = l_msg_rec.attribute9,
438 attribute10 = l_msg_rec.attribute10,
439 attribute11 = l_msg_rec.attribute11,
440 attribute12 = l_msg_rec.attribute12,
441 attribute13 = l_msg_rec.attribute13,
442 attribute14 = l_msg_rec.attribute14,
443 attribute15 = l_msg_rec.attribute15
444 WHERE message_id = l_msg_rec.message_id
445 AND object_version_number = l_msg_rec.object_version_number;
446
447 IF (SQL%NOTFOUND) THEN
448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
449 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
450 FND_MSG_PUB.add;
451 END IF;
452 RAISE FND_API.g_exc_error;
453 END IF;
454
455 UPDATE AMS_MESSAGES_TL SET
456 last_update_date = SYSDATE,
457 last_updated_by = FND_GLOBAL.user_id,
458 last_update_login = FND_GLOBAL.conc_login_id,
459 source_lang = USERENV('LANG'),
460 message_name = l_msg_rec.message_name,
461 description = l_msg_rec.description
462 WHERE message_id = l_msg_rec.message_id
463 AND USERENV('LANG') IN (language, source_lang);
464
465 IF (SQL%NOTFOUND) THEN
466 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
467 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
468 FND_MSG_PUB.add;
469 END IF;
470 RAISE FND_API.g_exc_error;
471 END IF;
472
473 -- finish
474 IF FND_API.to_boolean(p_commit) THEN
475 COMMIT;
479 (
476 END IF;
477
478 FND_MSG_PUB.count_and_get
480 P_ENCODED => FND_API.g_false,
481 p_count => x_msg_count,
482 p_data => x_msg_data
483 );
484
485 IF (AMS_DEBUG_HIGH_ON) THEN
486
487
488
489 AMS_Utility_PVT.debug_message(l_full_name || ': end');
490
491 END IF;
492
493 EXCEPTION
494
495 WHEN FND_API.g_exc_error THEN
496 ROLLBACK TO update_msg;
497 x_return_status := FND_API.g_ret_sts_error;
498 FND_MSG_PUB.count_and_get
499 (
500 p_encoded => FND_API.g_false,
501 p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504
505 WHEN FND_API.g_exc_unexpected_error THEN
506 ROLLBACK TO update_msg;
507 x_return_status := FND_API.g_ret_sts_unexp_error;
508 FND_MSG_PUB.count_and_get
509 (
510 p_encoded => FND_API.g_false,
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514
515 WHEN OTHERS THEN
516 ROLLBACK TO update_msg;
517 x_return_status :=FND_API.g_ret_sts_unexp_error;
518 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
519 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
520 END IF;
521 FND_MSG_PUB.count_and_get
522 (
523 p_encoded => FND_API.g_false,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527
528 END update_msg;
529
530
531 /*****************************************************************************/
532 -- Procedure: delete_msg
533 --
534 -- History
535 -- 01/04/2000 julou created
536 -------------------------------------------------------------------------------
537 PROCEDURE delete_msg
538 (
539 p_api_version IN NUMBER,
540 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
541 p_commit IN VARCHAR2 := FND_API.g_false,
542
543 x_return_status OUT NOCOPY VARCHAR2,
544 x_msg_count OUT NOCOPY NUMBER,
545 x_msg_data OUT NOCOPY VARCHAR2,
546
547 p_msg_id IN NUMBER,
548 p_object_version IN NUMBER
549 )
550 IS
551
552 l_api_version CONSTANT NUMBER := 1.0;
553 l_api_name CONSTANT VARCHAR2(30) := 'delete_msg';
554 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
555
556 BEGIN
557 -- initialize
558 SAVEPOINT delete_msg;
559
560 IF (AMS_DEBUG_HIGH_ON) THEN
561
562
563
564 AMS_Utility_PVT.debug_message(l_full_name || ': start');
565
566 END IF;
567
568 IF FND_API.to_boolean(p_init_msg_list) THEN
569 FND_MSG_PUB.initialize;
570 END IF;
571
572 IF NOT FND_API.compatible_api_call
573 (
574 l_api_version,
575 p_api_version,
576 l_api_name,
577 g_pkg_name
578 )
579 THEN
580 RAISE FND_API.g_exc_unexpected_error;
581 END IF;
582
583 x_return_status := FND_API.g_ret_sts_success;
584
585 -- delete
586 IF (AMS_DEBUG_HIGH_ON) THEN
587
588 AMS_Utility_PVT.debug_message(l_full_name || ': delete');
589 END IF;
590
591 DELETE FROM AMS_MESSAGES_TL
592 WHERE message_id = p_msg_id;
593
594 IF (SQL%NOTFOUND) THEN
595 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
596 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
597 FND_MSG_PUB.add;
598 END IF;
599 RAISE FND_API.g_exc_error;
600 END IF;
601
602 DELETE FROM AMS_MESSAGES_B
603 WHERE message_id = p_msg_id
604 AND object_version_number = p_object_version;
605
606 IF (SQL%NOTFOUND) THEN
607 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
608 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
609 FND_MSG_PUB.add;
610 END IF;
611 RAISE FND_API.g_exc_error;
612 END IF;
613
614 -- finish
615 IF FND_API.to_boolean(p_commit) THEN
616 COMMIT;
617 END IF;
618
619 FND_MSG_PUB.count_and_get
620 (
621 P_ENCODED => FND_API.g_false,
622 p_count => x_msg_count,
623 p_data => x_msg_data
624 );
625
626 IF (AMS_DEBUG_HIGH_ON) THEN
627
628
629
630 AMS_Utility_PVT.debug_message(l_full_name || ': end');
631
632 END IF;
633
634 EXCEPTION
635
636 WHEN FND_API.g_exc_error THEN
637 ROLLBACK TO delete_msg;
638 x_return_status := FND_API.g_ret_sts_error;
639 FND_MSG_PUB.count_and_get
640 (
641 p_encoded => FND_API.g_false,
642 p_count => x_msg_count,
643 p_data => x_msg_data
644 );
645
646 WHEN FND_API.g_exc_unexpected_error THEN
647 ROLLBACK TO delete_msg;
648 x_return_status := FND_API.g_ret_sts_unexp_error;
649 FND_MSG_PUB.count_and_get
650 (
651 p_encoded => FND_API.g_false,
657 ROLLBACK TO delete_msg;
652 p_count => x_msg_count,
653 p_data => x_msg_data
654 );
655
656 WHEN OTHERS THEN
658 x_return_status :=FND_API.g_ret_sts_unexp_error;
659 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
660 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
661 END IF;
662 FND_MSG_PUB.count_and_get
663 (
664 p_encoded => FND_API.g_false,
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668
669 END delete_msg;
670
671
672 /*****************************************************************************/
673 -- Procedure: lock_msg
674 --
675 -- History
676 -- 01/04/2000 julou created
677 -------------------------------------------------------------------------------
678 PROCEDURE lock_msg
679 (
680 p_api_version IN NUMBER,
681 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
682
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2,
686
687 p_msg_id IN NUMBER,
688 p_object_version IN NUMBER
689 )
690 IS
691
692 l_api_version CONSTANT NUMBER := 1.0;
693 l_api_name CONSTANT VARCHAR2(30) := 'lock_msg';
694 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
695 l_msg_id NUMBER;
696
697 CURSOR c_msg_b IS
698 SELECT message_id
699 FROM AMS_MESSAGES_B
700 WHERE message_id = p_msg_id
701 AND object_version_number = p_object_version
702 FOR UPDATE OF message_id NOWAIT;
703
704 CURSOR c_msg_tl IS
705 SELECT message_id
706 FROM AMS_MESSAGES_TL
707 WHERE message_id = p_msg_id
708 AND USERENV('LANG') IN (language, source_lang)
709 FOR UPDATE OF message_id NOWAIT;
710
711 BEGIN
712 -- initialize
713 IF (AMS_DEBUG_HIGH_ON) THEN
714
715 AMS_Utility_PVT.debug_message(l_full_name || ': start');
716 END IF;
717
718 IF FND_API.to_boolean(p_init_msg_list) THEN
719 FND_MSG_PUB.initialize;
720 END IF;
721
722 IF NOT FND_API.compatible_api_call
723 (
724 l_api_version,
725 p_api_version,
726 l_api_name,
727 g_pkg_name
728 )
729 THEN
730 RAISE FND_API.g_exc_unexpected_error;
731 END IF;
732
733 x_return_status := FND_API.g_ret_sts_success;
734
735 -- lock
736 IF (AMS_DEBUG_HIGH_ON) THEN
737
738 AMS_Utility_PVT.debug_message(l_full_name || ': lock');
739 END IF;
740
741 OPEN c_msg_b;
742 FETCH c_msg_b INTO l_msg_id;
743 IF (c_msg_b%NOTFOUND) THEN
744 CLOSE c_msg_b;
745 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
746 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
747 FND_MSG_PUB.add;
748 END IF;
749 RAISE FND_API.g_exc_error;
750 END IF;
751 CLOSE c_msg_b;
752
753 OPEN c_msg_tl;
754 FETCH c_msg_tl INTO l_msg_id;
755 IF (c_msg_tl%NOTFOUND) THEN
756 CLOSE c_msg_tl;
757 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
758 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
759 FND_MSG_PUB.add;
760 END IF;
761 RAISE FND_API.g_exc_error;
762 END IF;
763 CLOSE c_msg_tl;
764
765 -- finish
766 FND_MSG_PUB.count_and_get
767 (
768 p_encoded => FND_API.g_false,
769 p_count => x_msg_count,
770 p_data => x_msg_data
771 );
772
773 IF (AMS_DEBUG_HIGH_ON) THEN
774
775
776
777 AMS_Utility_PVT.debug_message(l_full_name || ': end');
778
779 END IF;
780
781 EXCEPTION
782
783 WHEN AMS_Utility_PVT.resource_locked THEN
784 x_return_status := FND_API.g_ret_sts_error;
785 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
786 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
787 FND_MSG_PUB.add;
788 END IF;
789 FND_MSG_PUB.count_and_get
790 (
791 p_encoded => FND_API.g_false,
792 p_count => x_msg_count,
793 p_data => x_msg_data
794 );
795
796 WHEN FND_API.g_exc_error THEN
800 p_encoded => FND_API.g_false,
797 x_return_status := FND_API.g_ret_sts_error;
798 FND_MSG_PUB.count_and_get
799 (
801 p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804
805 WHEN FND_API.g_exc_unexpected_error THEN
806 x_return_status := FND_API.g_ret_sts_unexp_error;
807 FND_MSG_PUB.count_and_get
808 (
809 p_encoded => FND_API.g_false,
810 p_count => x_msg_count,
811 p_data => x_msg_data
812 );
813
814 WHEN OTHERS THEN
815 x_return_status :=FND_API.g_ret_sts_unexp_error;
816 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
817 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
818 END IF;
819 FND_MSG_PUB.count_and_get
820 (
821 p_encoded => FND_API.g_false,
822 p_count => x_msg_count,
823 p_data => x_msg_data
824 );
825
826 END lock_msg;
827
828
829 /*****************************************************************************/
830 -- PROCEDURE
831 -- validate_msg
832 --
833 -- HISTORY
834 -- 01/04/2000 julou Created.
835 --------------------------------------------------------------------
836 PROCEDURE validate_msg
837 (
838 p_api_version IN NUMBER,
839 P_init_msg_list IN VARCHAR2 := FND_API.g_false,
840 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
841
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2,
845
846 p_msg_rec IN msg_rec_type
847 )
848 IS
849
850 l_api_version CONSTANT NUMBER := 1.0;
851 l_api_name CONSTANT VARCHAR2(30) := 'validate_msg';
852 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
853
854 l_return_status VARCHAR2(1);
855
856 BEGIN
857
858 ----------------------- initialize --------------------
859 IF (AMS_DEBUG_HIGH_ON) THEN
860
864 IF NOT FND_API.compatible_api_call
861 AMS_Utility_PVT.debug_message(l_full_name||': start');
862 END IF;
863
865 (
866 l_api_version,
867 p_api_version,
868 l_api_name,
869 g_pkg_name
870 )
871 THEN
872 RAISE FND_API.g_exc_unexpected_error;
873 END IF;
874
875 x_return_status := FND_API.g_ret_sts_success;
876
877 ---------------------- validate ------------------------
878 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
879 IF (AMS_DEBUG_HIGH_ON) THEN
880
881 AMS_Utility_PVT.debug_message(l_full_name||': check items');
882 END IF;
883 check_items
884 (
885 p_validation_mode => JTF_PLSQL_API.g_create,
886 x_return_status => l_return_status,
887 p_msg_rec => p_msg_rec
888 );
889
890 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
891 RAISE FND_API.g_exc_unexpected_error;
892 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
893 RAISE FND_API.g_exc_error;
894 END IF;
895 END IF;
896
897 -- record level
898 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
899 IF (AMS_DEBUG_HIGH_ON) THEN
900
901 AMS_Utility_PVT.debug_message(l_full_name||': check record');
902 END IF;
903 check_record
904 (
905 p_msg_rec => p_msg_rec,
906 p_complete_rec => p_msg_rec,
907 x_return_status => l_return_status
908 );
909
910 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
914 END IF;
911 RAISE FND_API.g_exc_unexpected_error;
912 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
913 RAISE FND_API.g_exc_error;
915 END IF;
916 -------------------- finish --------------------------
917 FND_MSG_PUB.count_and_get
918 (
919 p_encoded => FND_API.g_false,
920 p_count => x_msg_count,
921 p_data => x_msg_data
922 );
923
924 IF (AMS_DEBUG_HIGH_ON) THEN
925
926
927
928 AMS_Utility_PVT.debug_message(l_full_name ||': end');
929
930 END IF;
931
932 EXCEPTION
933 WHEN FND_API.g_exc_error THEN
934 x_return_status := FND_API.g_ret_sts_error;
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 WHEN FND_API.g_exc_unexpected_error THEN
943 x_return_status := FND_API.g_ret_sts_unexp_error ;
944 FND_MSG_PUB.count_and_get
945 (
946 p_encoded => FND_API.g_false,
947 p_count => x_msg_count,
948 p_data => x_msg_data
949 );
950
951 WHEN OTHERS THEN
952 x_return_status := FND_API.g_ret_sts_unexp_error;
953 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
954 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
955 END IF;
956
957 FND_MSG_PUB.count_and_get
958 (
959 p_encoded => FND_API.g_false,
960 p_count => x_msg_count,
961 p_data => x_msg_data
962 );
963
964 END validate_msg;
965
966 /*****************************************************************************/
967 -- Procedure: check_items
968 --
969 -- History
970 -- 01/04/2000 julou created
971 -------------------------------------------------------------------------------
972 PROCEDURE check_items
973 (
974 p_validation_mode IN VARCHAR2,
975 x_return_status OUT NOCOPY VARCHAR2,
976 p_msg_rec IN msg_rec_type
977 )
978 IS
979
980 l_api_version CONSTANT NUMBER := 1.0;
981 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
982 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
983
984 BEGIN
985 -- initialize
986 IF (AMS_DEBUG_HIGH_ON) THEN
987
988 AMS_Utility_PVT.debug_message(l_full_name || ': start');
989 END IF;
990
991 x_return_status := FND_API.g_ret_sts_success;
992
993 -- check required items
994 IF (AMS_DEBUG_HIGH_ON) THEN
995
996 AMS_Utility_PVT.debug_message(l_full_name || ': check required items');
997 END IF;
998 check_req_items
999 (
1000 p_validation_mode => p_validation_mode,
1001 p_msg_rec => p_msg_rec,
1002 x_return_status => x_return_status
1003 );
1004
1005 IF x_return_status <> FND_API.g_ret_sts_success THEN
1006 RETURN;
1007 END IF;
1008
1009 -- check unique key items
1010 IF (AMS_DEBUG_HIGH_ON) THEN
1011
1012 AMS_Utility_PVT.debug_message(l_full_name || ': check uk items');
1013 END IF;
1014 check_uk_items
1015 (
1016 p_validation_mode => p_validation_mode,
1017 p_msg_rec => p_msg_rec,
1018 x_return_status => x_return_status
1019 );
1020
1021 IF x_return_status <> FND_API.g_ret_sts_success THEN
1022 RETURN;
1023 END IF;
1024
1025 END check_items;
1026
1027
1028 /*****************************************************************************/
1029 -- Procedure: check_req_items
1030 --
1031 -- History
1032 -- 01/04/2000 julou created
1033 -------------------------------------------------------------------------------
1034 PROCEDURE check_req_items
1035 (
1036 p_validation_mode IN VARCHAR2,
1037 p_msg_rec IN msg_rec_type,
1038 x_return_status OUT NOCOPY VARCHAR2
1039 )
1040 IS
1041
1042 BEGIN
1043
1044 x_return_status := FND_API.g_ret_sts_success;
1045
1046 -- check message_id
1047 IF p_msg_rec.message_id IS NULL
1048 AND p_validation_mode = JTF_PLSQL_API.g_update THEN
1049 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1050 FND_MESSAGE.set_name('AMS', 'AMS_MSG_NO_MSG_ID');
1051 FND_MSG_PUB.add;
1052 END IF;
1053
1054 x_return_status := FND_API.g_ret_sts_error;
1055 RETURN;
1056 END IF;
1057
1058 -- check object_version_number
1059 IF p_msg_rec.object_version_number IS NULL
1060 AND p_validation_mode = JTF_PLSQL_API.g_update
1061 THEN
1062 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1063 FND_MESSAGE.set_name('AMS', 'AMS_NO_OBJ_VER_NUM');
1064 FND_MSG_PUB.add;
1065 END IF;
1066
1067 x_return_status := FND_API.g_ret_sts_error;
1068 RETURN;
1072 IF p_msg_rec.active_flag IS NULL THEN
1069 END IF;
1070
1071 -- check active_flag
1073 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1074 FND_MESSAGE.set_name('AMS', 'AMS_MSG_BAD_ACTIVE_FLAG');
1075 FND_MSG_PUB.add;
1076 END IF;
1077
1078 x_return_status := FND_API.g_ret_sts_error;
1079 RETURN;
1080 END IF;
1081
1082 IF p_msg_rec.active_flag <> 'Y'
1083 AND p_msg_rec.active_flag <> 'N'
1084 AND p_msg_rec.active_flag <> FND_API.g_miss_char
1085 THEN
1086 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1087 FND_MESSAGE.set_name('AMS', 'AMS_MSG_BAD_ACTIVE_FLAG');
1088 FND_MSG_PUB.add;
1089 END IF;
1090
1091 x_return_status := FND_API.g_ret_sts_error;
1092 RETURN;
1093 END IF;
1094
1095 -- check message_name
1096 IF p_msg_rec.message_name IS NULL THEN
1097 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1098 FND_MESSAGE.set_name('AMS', 'AMS_MSG_NO_MSG_NAME');
1099 FND_MSG_PUB.add;
1100 END IF;
1101
1102 x_return_status := FND_API.g_ret_sts_error;
1103 RETURN;
1104 END IF;
1105
1106 END check_req_items;
1107
1108
1109 /*****************************************************************************/
1110 -- Procedure: check_uk_items
1111 --
1112 -- History
1113 -- 01/04/2000 julou created
1114 -------------------------------------------------------------------------------
1115 PROCEDURE check_uk_items
1116 (
1117 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1118 p_msg_rec IN msg_rec_type,
1119 x_return_status OUT NOCOPY VARCHAR2
1120 )
1121 IS
1122
1123 l_uk_flag VARCHAR2(1);
1124
1125 cursor check_name_with_id(l_msg_id IN NUMBER, l_msg_name IN VARCHAR2) IS
1126 SELECT ''
1127 FROM AMS_MESSAGES_TL
1128 WHERE message_id <> l_msg_id
1129 AND message_name = l_msg_name
1130 AND language = USERENV('LANG');
1131
1132
1133 cursor check_name_without_id(l_msg_name IN VARCHAR2) IS
1134 SELECT ''
1135 FROM AMS_MESSAGES_TL
1136 WHERE message_name = l_msg_name
1137 AND language = USERENV('LANG');
1138 l_dummy VARCHAR2(1);
1139 l_flag VARCHAR2(1);
1140
1141 BEGIN
1142
1143 x_return_status := FND_API.g_ret_sts_success;
1144
1145 -- check PK, if message_id is passed in, must check if it is duplicate
1146 IF p_validation_mode = JTF_PLSQL_API.g_create
1147 AND p_msg_rec.message_id IS NOT NULL
1148 THEN
1149 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1150 (
1151 'AMS_MESSAGES_VL',
1152 'message_id = ' || p_msg_rec.message_id
1153 );
1154 END IF;
1155
1156 IF l_uk_flag = FND_API.g_false THEN
1157 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1158 FND_MESSAGE.set_name('AMS', 'AMS_MSG_DUPLICATE_MSG_ID');
1159 FND_MSG_PUB.add;
1160 END IF;
1161
1162 x_return_status := FND_API.g_ret_sts_error;
1163 RETURN;
1164 END IF;
1165
1166 -- check message_name, language
1167 /********
1168 IF p_msg_rec.message_id IS NOT NULL THEN
1169 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1170 (
1171 'AMS_MESSAGES_TL',
1172 'message_id<> ' || p_msg_rec.message_id
1173 || ' AND message_name = '''
1174 || p_msg_rec.message_name
1175 || ''' AND language = ''' || USERENV('LANG') || ''''
1176 );
1177 ELSE
1178 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1179 (
1180 'AMS_MESSAGES_TL',
1181 'message_name = ''' || p_msg_rec.message_name
1182 ||''' AND language = ''' || USERENV('LANG') || ''''
1183 );
1184 END IF;
1185
1186 IF l_uk_flag = FND_API.g_false THEN
1187 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1188 FND_MESSAGE.set_name('AMS', 'AMS_MSG_DUP_NAME_LANG');
1189 FND_MSG_PUB.add;
1190 END IF;
1191
1192 ***************/
1193 l_flag := 'N';
1194
1195 IF p_msg_rec.message_id IS NOT NULL THEN
1196 OPEN check_name_with_id(p_msg_rec.message_id,p_msg_rec.message_name);
1197 FETCH check_name_with_id INTO l_dummy;
1198 IF (check_name_with_id%FOUND) THEN
1199 l_flag := 'Y';
1200 END IF;
1201 CLOSE check_name_with_id;
1202 ELSE
1203 OPEN check_name_without_id(p_msg_rec.message_name);
1204 FETCH check_name_without_id INTO l_dummy;
1205 if (check_name_without_id%FOUND) then
1206 l_flag := 'Y';
1207 end if;
1208 CLOSE check_name_without_id;
1209
1210 END IF;
1211
1215 FND_MSG_PUB.add;
1212 IF ( l_flag = 'Y') THEN
1213 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1214 FND_MESSAGE.set_name('AMS', 'AMS_MSG_DUP_NAME_LANG');
1216 END IF;
1217 x_return_status := FND_API.g_ret_sts_error;
1218 RETURN;
1219 END IF;
1220
1221 END check_uk_items;
1222
1223
1224 /*****************************************************************************/
1225 -- PROCEDURE
1226 -- check_record
1227 --
1228 -- HISTORY
1229 -- 01/04/2000 julou Created.
1230 -------------------------------------------------------------------------------
1231 PROCEDURE check_record
1232 (
1233 p_msg_rec IN msg_rec_type,
1234 p_complete_rec IN msg_rec_type,
1235 x_return_status OUT NOCOPY VARCHAR2
1236 )
1237 IS
1238
1239 l_date_from DATE;
1240 l_date_to DATE;
1241
1242 BEGIN
1243
1244 x_return_status := FND_API.g_ret_sts_success;
1245
1246 -- check that date_effective_from <= date_effective_to
1247 IF p_complete_rec.date_effective_from <> FND_API.g_miss_date
1248 AND p_complete_rec.date_effective_from IS NOT NULL
1249 AND p_complete_rec.date_effective_to <> FND_API.g_miss_date
1250 AND p_complete_rec.date_effective_to IS NOT NULL
1251 THEN
1252 l_date_from := p_complete_rec.date_effective_from;
1253 l_date_to := p_complete_rec.date_effective_to;
1254 IF l_date_from > l_date_to THEN
1255 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1256 FND_MESSAGE.set_name('AMS', 'AMS_MESG_INVALID_DATES');
1257 FND_MSG_PUB.add;
1258 END IF;
1259
1260 x_return_status := FND_API.g_ret_sts_error;
1261 RETURN;
1262 END IF;
1263 END IF;
1264
1265 END check_record;
1266
1267
1271 -- History
1268 /*****************************************************************************/
1269 -- Procedure: complete_rec
1270 --
1272 -- 01/04/2000 julou created
1273 -------------------------------------------------------------------------------
1274 PROCEDURE complete_msg_rec
1275 (
1276 p_msg_rec IN msg_rec_type,
1277 x_complete_rec OUT NOCOPY msg_rec_type
1278 )
1279 IS
1280
1281 CURSOR c_msg IS
1282 SELECT * FROM AMS_MESSAGES_VL
1283 WHERE message_id = p_msg_rec.message_id;
1284
1285 l_msg_rec c_msg%ROWTYPE;
1286
1287 BEGIN
1288
1289 x_complete_rec := p_msg_rec;
1290
1291 OPEN c_msg;
1292 FETCH c_msg INTO l_msg_rec;
1293 IF (c_msg%NOTFOUND) THEN
1294 CLOSE c_msg;
1295 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1296 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1297 FND_MSG_PUB.add;
1298 END IF;
1299 RAISE FND_API.g_exc_error;
1300 END IF;
1301 CLOSE c_msg;
1302
1303 IF p_msg_rec.date_effective_from = FND_API.g_miss_date THEN
1304 x_complete_rec.date_effective_from := l_msg_rec.date_effective_from;
1305 END IF;
1306
1307 IF p_msg_rec.date_effective_to = FND_API.g_miss_date THEN
1308 x_complete_rec.date_effective_to := l_msg_rec.date_effective_to;
1309 END IF;
1310
1311 IF p_msg_rec.active_flag = FND_API.g_miss_char THEN
1312 x_complete_rec.active_flag := l_msg_rec.active_flag;
1313 END IF;
1314
1315 IF p_msg_rec.message_name = FND_API.g_miss_char THEN
1316 x_complete_rec.message_name := l_msg_rec.message_name;
1317 END IF;
1318
1319 IF p_msg_rec.description = FND_API.g_miss_char THEN
1320 x_complete_rec.description := l_msg_rec.description;
1321 END IF;
1322
1323 IF p_msg_rec.owner_user_id = FND_API.g_miss_num THEN
1324 x_complete_rec.owner_user_id := l_msg_rec.owner_user_id;
1325 END IF;
1326
1327 IF p_msg_rec.message_type_code = FND_API.g_miss_char THEN
1328 x_complete_rec.message_type_code := l_msg_rec.message_type_code;
1329 END IF;
1330
1331 END complete_msg_rec;
1332
1333
1334 /****************************************************************************/
1335 -- Procedure
1336 -- init_rec
1337 --
1338 -- HISTORY
1339 -- 01/04/2000 julou Created.
1340 ------------------------------------------------------------------------------
1341 PROCEDURE init_rec
1342 (
1343 x_msg_rec OUT NOCOPY msg_rec_type
1344 )
1345 IS
1346
1347 BEGIN
1348
1349 x_msg_rec.message_id := FND_API.g_miss_num;
1350 x_msg_rec.country_id := FND_API.g_miss_num;
1351 x_msg_rec.last_update_date := FND_API.g_miss_date;
1352 x_msg_rec.last_updated_by := FND_API.g_miss_num;
1353 x_msg_rec.creation_date := FND_API.g_miss_date;
1354 x_msg_rec.created_by := FND_API.g_miss_num;
1358 x_msg_rec.date_effective_to := FND_API.g_miss_date;
1355 x_msg_rec.last_update_login := FND_API.g_miss_num;
1356 x_msg_rec.object_version_number := FND_API.g_miss_num;
1357 x_msg_rec.date_effective_from := FND_API.g_miss_date;
1359 x_msg_rec.active_flag := 'Y';
1360 x_msg_rec.message_type_code := FND_API.g_miss_char;
1361 x_msg_rec.owner_user_id := FND_API.g_miss_num;
1362 x_msg_rec.message_name := FND_API.g_miss_char;
1363 x_msg_rec.description := FND_API.g_miss_char;
1364
1365 END init_rec;
1366
1367 END AMS_Messages_PVT;