[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_WORD_FIELDS_PVT
Source
1 PACKAGE BODY AMS_List_Word_Fields_PVT AS
2 /* $Header: amsvwdfb.pls 115.8 2002/11/22 08:56:31 jieli ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_List_Word_Fields_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_list_word_field
8 --
9 -- History
10 -- 01/24/2000 julou created
11 -------------------------------------------------------------------------------
12 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
13 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
14 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
15
16 PROCEDURE create_list_word_field
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_wrd_fld_rec IN wrd_fld_rec_type,
28 x_wrd_fld_id OUT NOCOPY NUMBER
29 )
30 IS
31
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_api_name CONSTANT VARCHAR2(30) := 'create_list_word_field';
34 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
35 l_return_status VARCHAR2(1);
36 l_wrd_fld_rec wrd_fld_rec_type := p_wrd_fld_rec;
37 l_wrd_fld_count NUMBER;
38
39 CURSOR c_list_word_field_seq IS
40 SELECT AMS_LIST_WORD_FIELDS_S.NEXTVAL
41 FROM DUAL;
42
43 CURSOR c_list_word_field_count(wrd_fld_id IN NUMBER) IS
44 SELECT COUNT(*)
45 FROM AMS_LIST_WORD_FIELDS
46 WHERE list_word_field_id = wrd_fld_id;
47
48 BEGIN
49 -- initialize
50 SAVEPOINT create_list_word_field;
51
52 IF FND_API.to_boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55
56
57 IF (AMS_DEBUG_HIGH_ON) THEN
58
59
60
61
62
63 AMS_Utility_PVT.debug_message(l_full_name || ': start');
64
65
66 END IF;
67
68 IF NOT FND_API.compatible_api_call
69 (
70 l_api_version,
71 p_api_version,
72 l_api_name,
73 g_pkg_name
74 )
75 THEN
76 RAISE FND_API.g_exc_unexpected_error;
77 END IF;
78
79 x_return_status := FND_API.g_ret_sts_success;
80
81 -- validate
82 IF (AMS_DEBUG_HIGH_ON) THEN
83
84 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
85 END IF;
86 validate_list_word_field
87 (
88 p_api_version => l_api_version,
89 p_init_msg_list => p_init_msg_list,
90 p_validation_level => p_validation_level,
91 x_return_status => l_return_status,
92 x_msg_count => x_msg_count,
93 x_msg_data => x_msg_data,
94 p_wrd_fld_rec => l_wrd_fld_rec
95 );
96
97 IF l_return_status = FND_API.g_ret_sts_error THEN
98 RAISE FND_API.g_exc_error;
99 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
100 RAISE FND_API.g_exc_unexpected_error;
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_wrd_fld_rec.list_word_field_id IS NULL THEN
110 LOOP
111 OPEN c_list_word_field_seq;
112 FETCH c_list_word_field_seq INTO l_wrd_fld_rec.list_word_field_id;
113 CLOSE c_list_word_field_seq;
114
115 OPEN c_list_word_field_count(l_wrd_fld_rec.list_word_field_id);
116 FETCH c_list_word_field_count INTO l_wrd_fld_count;
117 CLOSE c_list_word_field_count;
118
119 EXIT WHEN l_wrd_fld_count = 0;
120 END LOOP;
121 END IF;
122
123 INSERT INTO AMS_LIST_WORD_FIELDS
124 (
125 list_word_field_id,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 object_version_number,
132 field_table_name,
133 field_column_name,
134 list_word_replaces_id,
135 original_word,
136 replacement_word,
137 enabled_flag,
138 description
139 )
140 VALUES
141 (
142 l_wrd_fld_rec.list_word_field_id,
143 SYSDATE,
144 FND_GLOBAL.user_id,
145 SYSDATE,
146 FND_GLOBAL.user_id,
147 FND_GLOBAL.conc_login_id,
148 1,
149 l_wrd_fld_rec.field_table_name,
150 l_wrd_fld_rec.field_column_name,
151 l_wrd_fld_rec.list_word_replaces_id,
152 l_wrd_fld_rec.original_word,
153 l_wrd_fld_rec.replacement_word,
154 l_wrd_fld_rec.enabled_flag,
155 l_wrd_fld_rec.description
156 );
157
158 -- finish
159 x_wrd_fld_id := l_wrd_fld_rec.list_word_field_id;
160
161 IF FND_API.to_boolean(p_commit) THEN
162 COMMIT;
163 END IF;
164
165 FND_MSG_PUB.count_and_get
166 (
167 p_encoded => FND_API.g_false,
168 p_count => x_msg_count,
169 p_data => x_msg_data
170 );
171
172 IF (AMS_DEBUG_HIGH_ON) THEN
173
174
175
176 AMS_Utility_PVT.debug_message(l_full_name||': end');
177
178 END IF;
179
180 EXCEPTION
181
182 WHEN FND_API.g_exc_error THEN
183 ROLLBACK TO create_list_word_field;
184 x_return_status := FND_API.g_ret_sts_error;
185 FND_MSG_PUB.count_and_get
186 (
187 p_encoded => FND_API.g_false,
188 p_count => x_msg_count,
189 p_data => x_msg_data
190 );
191
192 WHEN FND_API.g_exc_unexpected_error THEN
193 ROLLBACK TO create_list_word_field;
194 x_return_status := FND_API.g_ret_sts_unexp_error;
195 FND_MSG_PUB.count_and_get
196 (
197 p_encoded => FND_API.g_false,
198 p_count => x_msg_count,
199 p_data => x_msg_data
200 );
201
202 WHEN OTHERS THEN
203 ROLLBACK TO create_list_word_field;
204 x_return_status :=FND_API.g_ret_sts_unexp_error;
205 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
206 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
207 END IF;
208 FND_MSG_PUB.count_and_get
209 (
210 p_encoded => FND_API.g_false,
211 p_count => x_msg_count,
212 p_data => x_msg_data
213 );
214
215 END create_list_word_field;
216
217
218 /*****************************************************************************/
219 -- Procedure: update_list_word_field
220 --
221 -- History
222 -- 01/24/2000 julou created
223 -------------------------------------------------------------------------------
224 PROCEDURE update_list_word_field
225 (
226 p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
228 p_commit IN VARCHAR2 := FND_API.g_false,
229 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
230
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2,
234
235 p_wrd_fld_rec IN wrd_fld_rec_type
236 )
237 IS
238
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_api_name CONSTANT VARCHAR2(30) := 'update_list_word_field';
241 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
242 l_return_status VARCHAR2(1);
243 l_wrd_fld_rec wrd_fld_rec_type := p_wrd_fld_rec;
244
245 BEGIN
246
247 -- initialize
248 SAVEPOINT update_list_word_field;
249
250 IF FND_API.to_boolean(p_init_msg_list) THEN
251 FND_MSG_PUB.initialize;
252 END IF;
253
254 IF (AMS_DEBUG_HIGH_ON) THEN
255
256
257
258 AMS_Utility_PVT.debug_message(l_full_name || ': start');
259
260 END IF;
261
262 IF NOT FND_API.compatible_api_call
263 (
264 l_api_version,
265 p_api_version,
266 l_api_name,
267 g_pkg_name
268 )
269 THEN
270 RAISE FND_API.g_exc_unexpected_error;
271 END IF;
272
273 x_return_status := FND_API.g_ret_sts_success;
274
275 -- validate
276 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
277 IF (AMS_DEBUG_HIGH_ON) THEN
278
279 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
280 END IF;
281
282 check_items
283 (
284 p_validation_mode => JTF_PLSQL_API.g_update,
285 x_return_status => l_return_status,
286 p_wrd_fld_rec => l_wrd_fld_rec
287 );
288
289 IF l_return_status = FND_API.g_ret_sts_error THEN
290 RAISE FND_API.g_exc_error;
291 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
292 RAISE FND_API.g_exc_unexpected_error;
293 END IF;
294 END IF;
295
296 -- complete record
297 complete_rec
298 (
299 p_wrd_fld_rec,
300 l_wrd_fld_rec
301 );
302
303 -- record level
304
305 -- update
306 IF (AMS_DEBUG_HIGH_ON) THEN
307
308 AMS_Utility_PVT.debug_message(l_full_name||': update');
309 END IF;
310
311 UPDATE AMS_LIST_WORD_FIELDS SET
312 last_update_date = SYSDATE,
313 last_updated_by = FND_GLOBAL.user_id,
314 last_update_login = FND_GLOBAL.conc_login_id,
315 object_version_number = l_wrd_fld_rec.object_version_number + 1,
316 field_table_name = l_wrd_fld_rec.field_table_name,
317 field_column_name = l_wrd_fld_rec.field_column_name,
318 list_word_replaces_id = l_wrd_fld_rec.list_word_replaces_id,
319 original_word = l_wrd_fld_rec.original_word,
320 replacement_word = l_wrd_fld_rec.replacement_word,
321 enabled_flag = l_wrd_fld_rec.enabled_flag,
322 description = l_wrd_fld_rec.description
323 WHERE list_word_field_id = l_wrd_fld_rec.list_word_field_id
324 AND object_version_number = l_wrd_fld_rec.object_version_number;
325
326 IF (SQL%NOTFOUND) THEN
327 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
328 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
329 FND_MSG_PUB.add;
330 END IF;
331 RAISE FND_API.g_exc_error;
332 END IF;
333
334 -- finish
335 IF FND_API.to_boolean(p_commit) THEN
336 COMMIT;
337 END IF;
338
339 FND_MSG_PUB.count_and_get
340 (
341 P_ENCODED => FND_API.g_false,
342 p_count => x_msg_count,
343 p_data => x_msg_data
344 );
345
346 IF (AMS_DEBUG_HIGH_ON) THEN
347
348
349
350 AMS_Utility_PVT.debug_message(l_full_name || ': end');
351
352 END IF;
353
354 EXCEPTION
355
356 WHEN FND_API.g_exc_error THEN
357 ROLLBACK TO update_list_word_field;
358 x_return_status := FND_API.g_ret_sts_error;
359 FND_MSG_PUB.count_and_get
360 (
361 p_encoded => FND_API.g_false,
362 p_count => x_msg_count,
363 p_data => x_msg_data
364 );
365
366 WHEN FND_API.g_exc_unexpected_error THEN
367 ROLLBACK TO update_list_word_field;
368 x_return_status := FND_API.g_ret_sts_unexp_error;
369 FND_MSG_PUB.count_and_get
370 (
371 p_encoded => FND_API.g_false,
372 p_count => x_msg_count,
373 p_data => x_msg_data
374 );
375
376 WHEN OTHERS THEN
377 ROLLBACK TO update_list_word_field;
378 x_return_status :=FND_API.g_ret_sts_unexp_error;
379 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
380 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
381 END IF;
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 END update_list_word_field;
390
391
392 /*****************************************************************************/
393 -- Procedure: delete_list_word_field
394 --
395 -- History
396 -- 01/24/2000 julou created
397 -------------------------------------------------------------------------------
398 PROCEDURE delete_list_word_field
399 (
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
402 p_commit IN VARCHAR2 := FND_API.g_false,
403
404 x_return_status OUT NOCOPY VARCHAR2,
405 x_msg_count OUT NOCOPY NUMBER,
406 x_msg_data OUT NOCOPY VARCHAR2,
407
408 p_wrd_fld_id IN NUMBER,
409 p_object_version IN NUMBER
410 )
411 IS
412
413 l_api_version CONSTANT NUMBER := 1.0;
414 l_api_name CONSTANT VARCHAR2(30) := 'delete_list_word_field';
415 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
416
417 BEGIN
418 -- initialize
419 SAVEPOINT delete_list_word_field;
420
421 IF (AMS_DEBUG_HIGH_ON) THEN
422
423
424
425 AMS_Utility_PVT.debug_message(l_full_name || ': start');
426
427 END IF;
428
429 IF FND_API.to_boolean(p_init_msg_list) THEN
430 FND_MSG_PUB.initialize;
431 END IF;
432
433 IF NOT FND_API.compatible_api_call
434 (
435 l_api_version,
436 p_api_version,
437 l_api_name,
438 g_pkg_name
439 )
440 THEN
441 RAISE FND_API.g_exc_unexpected_error;
442 END IF;
443
444 x_return_status := FND_API.g_ret_sts_success;
445
446 -- delete
447 IF (AMS_DEBUG_HIGH_ON) THEN
448
449 AMS_Utility_PVT.debug_message(l_full_name || ': delete');
450 END IF;
451
452 DELETE FROM AMS_LIST_WORD_FIELDS
453 WHERE list_word_field_id = p_wrd_fld_id
454 AND object_version_number = p_object_version;
455
456 IF (SQL%NOTFOUND) THEN
457 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
458 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
459 FND_MSG_PUB.add;
460 END IF;
461 RAISE FND_API.g_exc_error;
462 END IF;
463
464 -- finish
465 IF FND_API.to_boolean(p_commit) THEN
466 COMMIT;
467 END IF;
468
469 FND_MSG_PUB.count_and_get
470 (
471 P_ENCODED => FND_API.g_false,
472 p_count => x_msg_count,
473 p_data => x_msg_data
474 );
475
476 IF (AMS_DEBUG_HIGH_ON) THEN
477
478
479
480 AMS_Utility_PVT.debug_message(l_full_name || ': end');
481
482 END IF;
483
484 EXCEPTION
485
486 WHEN FND_API.g_exc_error THEN
487 ROLLBACK TO delete_list_word_field;
488 x_return_status := FND_API.g_ret_sts_error;
489 FND_MSG_PUB.count_and_get
490 (
491 p_encoded => FND_API.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 WHEN FND_API.g_exc_unexpected_error THEN
497 ROLLBACK TO delete_list_word_field;
498 x_return_status := FND_API.g_ret_sts_unexp_error;
499 FND_MSG_PUB.count_and_get
500 (
501 p_encoded => FND_API.g_false,
502 p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 WHEN OTHERS THEN
507 ROLLBACK TO delete_list_word_field;
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 FND_MSG_PUB.count_and_get
513 (
514 p_encoded => FND_API.g_false,
515 p_count => x_msg_count,
516 p_data => x_msg_data
517 );
518
519 END delete_list_word_field;
520
521
522 /*****************************************************************************/
526 -- 01/24/2000 julou created
523 -- Procedure: lock_list_word_field
524 --
525 -- History
527 -------------------------------------------------------------------------------
528 PROCEDURE lock_list_word_field
529 (
530 p_api_version IN NUMBER,
531 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
532
533 x_return_status OUT NOCOPY VARCHAR2,
534 x_msg_count OUT NOCOPY NUMBER,
535 x_msg_data OUT NOCOPY VARCHAR2,
536
537 p_wrd_fld_id IN NUMBER,
538 p_object_version IN NUMBER
539 )
540 IS
541
542 l_api_version CONSTANT NUMBER := 1.0;
543 l_api_name CONSTANT VARCHAR2(30) := 'lock_list_word_field';
544 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
545 l_wrd_fld_id NUMBER;
546
547 CURSOR c_list_word_field IS
548 SELECT list_word_field_id
549 FROM AMS_LIST_WORD_FIELDS
550 WHERE list_word_field_id = p_wrd_fld_id
551 AND object_version_number = p_object_version
552 FOR UPDATE OF list_word_field_id NOWAIT;
553
554 BEGIN
555 -- initialize
556 IF (AMS_DEBUG_HIGH_ON) THEN
557
558 AMS_Utility_PVT.debug_message(l_full_name || ': start');
559 END IF;
560
561 IF FND_API.to_boolean(p_init_msg_list) THEN
562 FND_MSG_PUB.initialize;
563 END IF;
564
565 IF NOT FND_API.compatible_api_call
566 (
567 l_api_version,
568 p_api_version,
569 l_api_name,
570 g_pkg_name
571 )
572 THEN
573 RAISE FND_API.g_exc_unexpected_error;
574 END IF;
575
576 x_return_status := FND_API.g_ret_sts_success;
577
578 -- lock
579 IF (AMS_DEBUG_HIGH_ON) THEN
580
581 AMS_Utility_PVT.debug_message(l_full_name || ': lock');
582 END IF;
583
584 OPEN c_list_word_field;
585 FETCH c_list_word_field INTO l_wrd_fld_id;
586 IF (c_list_word_field%NOTFOUND) THEN
587 CLOSE c_list_word_field;
588 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
589 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
590 FND_MSG_PUB.add;
591 END IF;
592 RAISE FND_API.g_exc_error;
593 END IF;
594 CLOSE c_list_word_field;
595
596 -- finish
597 FND_MSG_PUB.count_and_get
598 (
599 p_encoded => FND_API.g_false,
600 p_count => x_msg_count,
601 p_data => x_msg_data
602 );
603
604 IF (AMS_DEBUG_HIGH_ON) THEN
605
606
607
608 AMS_Utility_PVT.debug_message(l_full_name || ': end');
609
610 END IF;
611
612 EXCEPTION
613
614 WHEN AMS_Utility_PVT.resource_locked THEN
615 x_return_status := FND_API.g_ret_sts_error;
616 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
617 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
618 FND_MSG_PUB.add;
619 END IF;
620 FND_MSG_PUB.count_and_get
621 (
622 p_encoded => FND_API.g_false,
623 p_count => x_msg_count,
624 p_data => x_msg_data
625 );
626
627 WHEN FND_API.g_exc_error THEN
628 x_return_status := FND_API.g_ret_sts_error;
629 FND_MSG_PUB.count_and_get
630 (
631 p_encoded => FND_API.g_false,
632 p_count => x_msg_count,
633 p_data => x_msg_data
634 );
635
636 WHEN FND_API.g_exc_unexpected_error THEN
637 x_return_status := FND_API.g_ret_sts_unexp_error;
638 FND_MSG_PUB.count_and_get
639 (
640 p_encoded => FND_API.g_false,
641 p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644
645 WHEN OTHERS THEN
646 x_return_status :=FND_API.g_ret_sts_unexp_error;
647 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
648 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
649 END IF;
650 FND_MSG_PUB.count_and_get
651 (
652 p_encoded => FND_API.g_false,
653 p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656
657 END lock_list_word_field;
658
659
660 /*****************************************************************************/
661 -- PROCEDURE
662 -- validate_list_word_field
663 --
664 -- HISTORY
665 -- 01/24/2000 julou Created.
666 --------------------------------------------------------------------
667 PROCEDURE validate_list_word_field
668 (
669 p_api_version IN NUMBER,
670 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
671 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
672
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676
677 p_wrd_fld_rec IN wrd_fld_rec_type
678 )
679 IS
680
681 l_api_version CONSTANT NUMBER := 1.0;
682 l_api_name CONSTANT VARCHAR2(30) := 'validate_list_word_field';
683 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
684
685 l_return_status VARCHAR2(1);
686
687 BEGIN
688
689 ----------------------- initialize --------------------
690 IF (AMS_DEBUG_HIGH_ON) THEN
691
692 AMS_Utility_PVT.debug_message(l_full_name||': start');
693 END IF;
694
695 IF NOT FND_API.compatible_api_call
696 (
697 l_api_version,
698 p_api_version,
699 l_api_name,
700 g_pkg_name
704 END IF;
701 )
702 THEN
703 RAISE FND_API.g_exc_unexpected_error;
705
706 x_return_status := FND_API.g_ret_sts_success;
707
708 ---------------------- validate ------------------------
709 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
710 IF (AMS_DEBUG_HIGH_ON) THEN
711
712 AMS_Utility_PVT.debug_message(l_full_name||': check items');
713 END IF;
714 check_items
715 (
716 p_validation_mode => JTF_PLSQL_API.g_create,
717 x_return_status => l_return_status,
718 p_wrd_fld_rec => p_wrd_fld_rec
719 );
720
721 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
722 RAISE FND_API.g_exc_unexpected_error;
723 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
724 RAISE FND_API.g_exc_error;
725 END IF;
726 END IF;
727
728 -- record level
729
730 -------------------- finish --------------------------
731 FND_MSG_PUB.count_and_get
732 (
733 p_encoded => FND_API.g_false,
734 p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737
738 IF (AMS_DEBUG_HIGH_ON) THEN
739
740
741
742 AMS_Utility_PVT.debug_message(l_full_name ||': end');
743
744 END IF;
745
746 EXCEPTION
747 WHEN FND_API.g_exc_error THEN
748 x_return_status := FND_API.g_ret_sts_error;
749 FND_MSG_PUB.count_and_get
750 (
751 p_encoded => FND_API.g_false,
752 p_count => x_msg_count,
753 p_data => x_msg_data
754 );
755
756 WHEN FND_API.g_exc_unexpected_error THEN
757 x_return_status := FND_API.g_ret_sts_unexp_error ;
758 FND_MSG_PUB.count_and_get
759 (
760 p_encoded => FND_API.g_false,
761 p_count => x_msg_count,
762 p_data => x_msg_data
763 );
764
765 WHEN OTHERS THEN
766 x_return_status := FND_API.g_ret_sts_unexp_error;
767 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
768 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
769 END IF;
770
771 FND_MSG_PUB.count_and_get
772 (
773 p_encoded => FND_API.g_false,
774 p_count => x_msg_count,
775 p_data => x_msg_data
776 );
777
778 END validate_list_word_field;
779
780 /*****************************************************************************/
781 -- Procedure: check_items
782 --
783 -- History
784 -- 01/24/2000 julou created
785 -------------------------------------------------------------------------------
786 PROCEDURE check_items
787 (
788 p_validation_mode IN VARCHAR2,
789 x_return_status OUT NOCOPY VARCHAR2,
790 p_wrd_fld_rec IN wrd_fld_rec_type
791 )
792 IS
793
794 l_api_version CONSTANT NUMBER := 1.0;
795 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
796 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
797
798 BEGIN
799 -- initialize
800 IF (AMS_DEBUG_HIGH_ON) THEN
801
802 AMS_Utility_PVT.debug_message(l_full_name || ': start');
803 END IF;
804
805 x_return_status := FND_API.g_ret_sts_success;
806
807 -- check required items
808 IF (AMS_DEBUG_HIGH_ON) THEN
809
810 AMS_Utility_PVT.debug_message(l_full_name || ': check required items');
811 END IF;
812 check_req_items
813 (
814 p_validation_mode => p_validation_mode,
815 p_wrd_fld_rec => p_wrd_fld_rec,
816 x_return_status => x_return_status
817 );
818
819 IF x_return_status <> FND_API.g_ret_sts_success THEN
820 RETURN;
821 END IF;
822
823 -- check foreign key items
824 IF (AMS_DEBUG_HIGH_ON) THEN
825
826 AMS_Utility_PVT.debug_message(l_full_name || ': check fk items');
827 END IF;
828 check_fk_items
829 (
830 p_wrd_fld_rec => p_wrd_fld_rec,
831 x_return_status => x_return_status
832 );
833
834 IF x_return_status <> FND_API.g_ret_sts_success THEN
835 RETURN;
836 END IF;
837
838 -- check unique key items
839 IF (AMS_DEBUG_HIGH_ON) THEN
840
841 AMS_Utility_PVT.debug_message(l_full_name || ': check uk items');
842 END IF;
843 check_uk_items
844 (
845 p_validation_mode => p_validation_mode,
846 p_wrd_fld_rec => p_wrd_fld_rec,
847 x_return_status => x_return_status
848 );
849
850 IF x_return_status <> FND_API.g_ret_sts_success THEN
851 RETURN;
852 END IF;
853
854 END check_items;
855
856
857 /*****************************************************************************/
858 -- Procedure: check_req_items
859 --
860 -- History
861 -- 01/24/2000 julou created
862 -------------------------------------------------------------------------------
863 PROCEDURE check_req_items
864 (
865 p_validation_mode IN VARCHAR2,
866 p_wrd_fld_rec IN wrd_fld_rec_type,
867 x_return_status OUT NOCOPY VARCHAR2
868 )
869 IS
870
871 BEGIN
872
873 x_return_status := FND_API.g_ret_sts_success;
874
875 -- check list_word_field_id
876 IF p_wrd_fld_rec.list_word_field_id IS NULL
877 AND p_validation_mode = JTF_PLSQL_API.g_update THEN
878 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
882
879 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_FIELDS_NO_ID');
880 FND_MSG_PUB.add;
881 END IF;
883 x_return_status := FND_API.g_ret_sts_error;
884 RETURN;
885 END IF;
886
887 -- check object_version_number
888 IF p_wrd_fld_rec.object_version_number IS NULL
889 AND p_validation_mode = JTF_PLSQL_API.g_update
890 THEN
891 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
892 FND_MESSAGE.set_name('AMS', 'AMS_NO_OBJ_VER_NUM');
893 FND_MSG_PUB.add;
894 END IF;
895
896 x_return_status := FND_API.g_ret_sts_error;
897 RETURN;
898 END IF;
899
900 -- check field_table_name
901 IF p_wrd_fld_rec.field_table_name IS NULL THEN
902 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
903 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_TBL_NAME');
904 FND_MSG_PUB.add;
905 END IF;
906
907 x_return_status := FND_API.g_ret_sts_error;
908 RETURN;
909 END IF;
910
911 -- check field_column_name
912 IF p_wrd_fld_rec.field_column_name IS NULL THEN
913 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
914 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_COL_NAME');
915 FND_MSG_PUB.add;
916 END IF;
917
918 x_return_status := FND_API.g_ret_sts_error;
919 RETURN;
920 END IF;
921
922 -- check list_word_replaces_id
923 IF p_wrd_fld_rec.list_word_replaces_id IS NULL THEN
924 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
925 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_RPL_ID');
926 FND_MSG_PUB.add;
927 END IF;
928
929 x_return_status := FND_API.g_ret_sts_error;
930 RETURN;
931 END IF;
932
933 -- check original_word
934 IF p_wrd_fld_rec.original_word IS NULL THEN
935 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
936 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_ORG_WRD');
937 FND_MSG_PUB.add;
938 END IF;
939
940 x_return_status := FND_API.g_ret_sts_error;
941 RETURN;
942 END IF;
943
944 -- check replacement_word
945 IF p_wrd_fld_rec.replacement_word IS NULL THEN
946 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
947 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_RPL_WRD');
948 FND_MSG_PUB.add;
949 END IF;
950
951 x_return_status := FND_API.g_ret_sts_error;
952 RETURN;
953 END IF;
954
955 -- check enabled_flag
956 IF p_wrd_fld_rec.enabled_flag IS NULL THEN
957 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
958 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_ENBL_FLAG');
959 FND_MSG_PUB.add;
960 END IF;
961
962 x_return_status := FND_API.g_ret_sts_error;
963 RETURN;
964 END IF;
965
966 IF p_wrd_fld_rec.enabled_flag <> 'Y'
967 AND p_wrd_fld_rec.enabled_flag <> 'N'
968 AND p_wrd_fld_rec.enabled_flag <> FND_API.g_miss_char
969 THEN
970 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
971 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_ENBL_FLAG');
972 FND_MSG_PUB.add;
973 END IF;
974
975 x_return_status := FND_API.g_ret_sts_error;
976 RETURN;
977 END IF;
978
979 END check_req_items;
980
981
982 /*****************************************************************************/
983 -- Procedure: check_fk_items
984 --
985 -- History
986 -- 01/24/2000 julou created
987 -------------------------------------------------------------------------------
988 PROCEDURE check_fk_items
989 (
990 p_wrd_fld_rec IN wrd_fld_rec_type,
991 x_return_status OUT NOCOPY VARCHAR2
992 )
993 IS
994
995 l_fk_flag VARCHAR2(1);
996
997 BEGIN
998
999 x_return_status := FND_API.g_ret_sts_success;
1000
1001 -- check field_table_name, field_column_name
1002 l_fk_flag := AMS_Utility_PVT.check_fk_exists
1003 (
1004 'AMS_LIST_FIELDS_B',
1005 'field_table_name',
1006 p_wrd_fld_rec.field_table_name,
1007 2, -- varchar2 type
1008 'field_column_name = ''' || p_wrd_fld_rec.field_column_name || ''''
1009 );
1010
1011 IF l_fk_flag = FND_API.g_false THEN
1012 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1013 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_FLD_BAD_TBL_COL');
1014 FND_MSG_PUB.add;
1015 END IF;
1016
1017 x_return_status := FND_API.g_ret_sts_error;
1018 RETURN;
1019 END IF;
1020
1021 -- check list_word_replaces_id
1022 IF p_wrd_fld_rec.list_word_replaces_id IS NOT NULL THEN
1023 l_fk_flag := AMS_Utility_PVT.check_fk_exists
1024 (
1025 'AMS_LIST_WORD_REPLACES',
1026 'list_word_replaces_id',
1027 p_wrd_fld_rec.list_word_replaces_id
1028 );
1029
1030 IF l_fk_flag = FND_API.g_false THEN
1031 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1032 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_FLD_BAD_RPL_ID');
1033 FND_MSG_PUB.add;
1034 END IF;
1035
1036 x_return_status := FND_API.g_ret_sts_error;
1037 RETURN;
1038 END IF;
1039 END IF;
1040
1041 END check_fk_items;
1042
1043
1044 /*****************************************************************************/
1045 -- Procedure: check_uk_items
1046 --
1050 PROCEDURE check_uk_items
1047 -- History
1048 -- 01/24/2000 julou created
1049 -------------------------------------------------------------------------------
1051 (
1052 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1053 p_wrd_fld_rec IN wrd_fld_rec_type,
1054 x_return_status OUT NOCOPY VARCHAR2
1055 )
1056 IS
1057
1058 l_uk_flag VARCHAR2(1);
1059
1060 BEGIN
1061
1062 x_return_status := FND_API.g_ret_sts_success;
1063
1064 -- check PK, if list_word_field_id is passed in, must check if it is duplicate
1065 IF p_validation_mode = JTF_PLSQL_API.g_create
1066 AND p_wrd_fld_rec.list_word_field_id IS NOT NULL
1067 THEN
1068 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1069 (
1070 'AMS_LIST_WORD_FIELDS',
1071 'list_word_field_id = ' || p_wrd_fld_rec.list_word_field_id
1072 );
1073 END IF;
1074
1075 IF l_uk_flag = FND_API.g_false THEN
1076 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1077 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_FIELDS_NO_ID');
1078 FND_MSG_PUB.add;
1079 END IF;
1080
1081 x_return_status := FND_API.g_ret_sts_error;
1082 RETURN;
1083 END IF;
1084
1085 -- check list_word_replaces_id, field_table_name, field_column_name
1086 IF p_wrd_fld_rec.list_word_field_id IS NOT NULL THEN
1087 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1088 (
1089 'AMS_LIST_WORD_FIELDS',
1090 'list_word_field_id <> ' || p_wrd_fld_rec.list_word_field_id
1091 || ' AND list_word_replaces_id = ' || p_wrd_fld_rec.list_word_replaces_id
1092 || ' AND field_table_name = ''' || p_wrd_fld_rec.field_table_name
1093 || ''' AND field_column_name = ''' || p_wrd_fld_rec.field_column_name || ''''
1094 );
1095 ELSE
1096 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1097 (
1098 'AMS_LIST_WORD_FIELDS',
1099 'list_word_replaces_id = ' || p_wrd_fld_rec.list_word_replaces_id
1100 || ' AND field_table_name = ''' || p_wrd_fld_rec.field_table_name
1101 || ''' AND field_column_name = ''' || p_wrd_fld_rec.field_column_name || ''''
1102 );
1103 END IF;
1104
1105 IF l_uk_flag = FND_API.g_false THEN
1106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1107 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_FLD_DUP_RP_TB_CO');
1108 FND_MSG_PUB.add;
1109 END IF;
1110
1111 x_return_status := FND_API.g_ret_sts_error;
1112 RETURN;
1113 END IF;
1114
1115 END check_uk_items;
1116
1117
1118 /*****************************************************************************/
1119 -- Procedure: complete_rec
1120 --
1121 -- History
1122 -- 01/24/2000 julou created
1123 -------------------------------------------------------------------------------
1124 PROCEDURE complete_rec
1125 (
1126 p_wrd_fld_rec IN wrd_fld_rec_type,
1127 x_complete_rec OUT NOCOPY wrd_fld_rec_type
1128 )
1129 IS
1130
1131 CURSOR c_list_word_field IS
1132 SELECT * FROM AMS_LIST_WORD_FIELDS
1133 WHERE list_word_field_id = p_wrd_fld_rec.list_word_field_id;
1134
1135 l_wrd_fld_rec c_list_word_field%ROWTYPE;
1136
1137 BEGIN
1138
1139 x_complete_rec := p_wrd_fld_rec;
1140
1141 OPEN c_list_word_field;
1142 FETCH c_list_word_field INTO l_wrd_fld_rec;
1143 IF (c_list_word_field%NOTFOUND) THEN
1144 CLOSE c_list_word_field;
1145 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1146 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1147 FND_MSG_PUB.add;
1148 END IF;
1149 RAISE FND_API.g_exc_error;
1150 END IF;
1151 CLOSE c_list_word_field;
1152
1153 IF p_wrd_fld_rec.field_table_name = FND_API.g_miss_char THEN
1154 x_complete_rec.field_table_name := l_wrd_fld_rec.field_table_name;
1155 END IF;
1156
1157 IF p_wrd_fld_rec.field_column_name = FND_API.g_miss_char THEN
1158 x_complete_rec.field_column_name := l_wrd_fld_rec.field_column_name;
1159 END IF;
1160
1161 IF p_wrd_fld_rec.list_word_replaces_id = FND_API.g_miss_num THEN
1162 x_complete_rec.list_word_replaces_id := l_wrd_fld_rec.list_word_replaces_id;
1163 END IF;
1164
1165 IF p_wrd_fld_rec.original_word = FND_API.g_miss_char THEN
1166 x_complete_rec.original_word := l_wrd_fld_rec.original_word;
1167 END IF;
1168
1169 IF p_wrd_fld_rec.replacement_word = FND_API.g_miss_char THEN
1170 x_complete_rec.replacement_word := l_wrd_fld_rec.replacement_word;
1171 END IF;
1172
1173 IF p_wrd_fld_rec.enabled_flag = FND_API.g_miss_char THEN
1174 x_complete_rec.enabled_flag := l_wrd_fld_rec.enabled_flag;
1175 END IF;
1176
1177 IF p_wrd_fld_rec.description = FND_API.g_miss_char THEN
1178 x_complete_rec.description := l_wrd_fld_rec.description;
1179 END IF;
1180
1181 END complete_rec;
1182
1183
1184 /****************************************************************************/
1185 -- Procedure
1186 -- init_rec
1187 --
1188 -- HISTORY
1189 -- 01/24/2000 julou Created.
1190 ------------------------------------------------------------------------------
1191 PROCEDURE init_rec
1192 (
1193 x_wrd_fld_rec OUT NOCOPY wrd_fld_rec_type
1194 )
1195 IS
1196
1197 BEGIN
1198
1199 x_wrd_fld_rec.list_word_field_id := FND_API.g_miss_num;
1200 x_wrd_fld_rec.last_update_date := FND_API.g_miss_date;
1201 x_wrd_fld_rec.last_updated_by := FND_API.g_miss_num;
1202 x_wrd_fld_rec.creation_date := FND_API.g_miss_date;
1206 x_wrd_fld_rec.list_word_replaces_id := FND_API.g_miss_num;
1203 x_wrd_fld_rec.created_by := FND_API.g_miss_num;
1204 x_wrd_fld_rec.last_update_login := FND_API.g_miss_num;
1205 x_wrd_fld_rec.object_version_number := FND_API.g_miss_num;
1207 x_wrd_fld_rec.field_table_name := FND_API.g_miss_char;
1208 x_wrd_fld_rec.field_column_name := FND_API.g_miss_char;
1209 x_wrd_fld_rec.original_word := FND_API.g_miss_char;
1210 x_wrd_fld_rec.replacement_word := FND_API.g_miss_char;
1211 x_wrd_fld_rec.enabled_flag := FND_API.g_miss_char;
1212 x_wrd_fld_rec.description := FND_API.g_miss_char;
1213
1214 END init_rec;
1215
1216 END AMS_List_Word_Fields_PVT;