[Home] [Help]
PACKAGE BODY: APPS.AMS_LIST_WORD_REPLACES_PVT
Source
1 PACKAGE BODY AMS_List_Word_Replaces_PVT AS
2 /* $Header: amsvwdrb.pls 115.10 2002/11/22 08:56:35 jieli ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'AMS_List_Word_Replaces_PVT';
5
6 /*****************************************************************************/
7 -- Procedure: create_list_word_replace
8 --
9 -- History
10 -- 01/24/2000 julou created
11 -- 01/31/2000 mpande modified delete procedure for cascade delete
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_list_word_replace
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_wrd_rpl_rec IN wrd_rpl_rec_type,
29 x_wrd_rpl_id OUT NOCOPY NUMBER
30 )
31 IS
32
33 l_api_version CONSTANT NUMBER := 1.0;
34 l_api_name CONSTANT VARCHAR2(30) := 'create_list_word_replace';
35 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
36 l_return_status VARCHAR2(1);
37 l_wrd_rpl_rec wrd_rpl_rec_type := p_wrd_rpl_rec;
38 l_wrd_rpl_count NUMBER;
39
40 CURSOR c_list_word_replace_seq IS
41 SELECT AMS_LIST_WORD_REPLACES_S.NEXTVAL
42 FROM DUAL;
43
44 CURSOR c_list_word_replace_count(wrd_rpl_id IN NUMBER) IS
45 SELECT COUNT(*)
46 FROM AMS_LIST_WORD_REPLACES
47 WHERE list_word_replaces_id = wrd_rpl_id;
48
49 BEGIN
50 -- initialize
51 SAVEPOINT create_list_word_replace;
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_list_word_replace
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_wrd_rpl_rec => l_wrd_rpl_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_wrd_rpl_rec.list_word_replaces_id IS NULL THEN
111 LOOP
112 OPEN c_list_word_replace_seq;
113 FETCH c_list_word_replace_seq INTO l_wrd_rpl_rec.list_word_replaces_id;
114 CLOSE c_list_word_replace_seq;
115
116 OPEN c_list_word_replace_count(l_wrd_rpl_rec.list_word_replaces_id);
117 FETCH c_list_word_replace_count INTO l_wrd_rpl_count;
118 CLOSE c_list_word_replace_count;
119
120 EXIT WHEN l_wrd_rpl_count = 0;
121 END LOOP;
122 END IF;
123
124 INSERT INTO AMS_LIST_WORD_REPLACES
125 (
126 list_word_replaces_id,
127 last_update_date,
128 last_updated_by,
129 creation_date,
130 created_by,
131 last_update_login,
132 object_version_number,
133 original_word,
134 replacement_word,
135 enabled_flag,
136 description
137 )
138 VALUES
139 (
140 l_wrd_rpl_rec.list_word_replaces_id,
141 SYSDATE,
142 FND_GLOBAL.user_id,
143 SYSDATE,
144 FND_GLOBAL.user_id,
145 FND_GLOBAL.conc_login_id,
146 1,
147 l_wrd_rpl_rec.original_word,
148 l_wrd_rpl_rec.replacement_word,
149 l_wrd_rpl_rec.enabled_flag,
150 l_wrd_rpl_rec.description
151 );
152
153 -- finish
154 x_wrd_rpl_id := l_wrd_rpl_rec.list_word_replaces_id;
155
156 IF FND_API.to_boolean(p_commit) THEN
157 COMMIT;
158 END IF;
159
160 FND_MSG_PUB.count_and_get
161 (
162 p_encoded => FND_API.g_false,
163 p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166
167 IF (AMS_DEBUG_HIGH_ON) THEN
168
169
170
171 AMS_Utility_PVT.debug_message(l_full_name||': end');
172
173 END IF;
174
175 EXCEPTION
176
177 WHEN FND_API.g_exc_error THEN
178 ROLLBACK TO create_list_word_replace;
179 x_return_status := FND_API.g_ret_sts_error;
180 FND_MSG_PUB.count_and_get
181 (
182 p_encoded => FND_API.g_false,
183 p_count => x_msg_count,
184 p_data => x_msg_data
185 );
186
187 WHEN FND_API.g_exc_unexpected_error THEN
188 ROLLBACK TO create_list_word_replace;
189 x_return_status := FND_API.g_ret_sts_unexp_error;
190 FND_MSG_PUB.count_and_get
191 (
192 p_encoded => FND_API.g_false,
193 p_count => x_msg_count,
194 p_data => x_msg_data
195 );
196
197 WHEN OTHERS THEN
198 ROLLBACK TO create_list_word_replace;
199 x_return_status :=FND_API.g_ret_sts_unexp_error;
200 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
201 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
202 END IF;
203 FND_MSG_PUB.count_and_get
204 (
205 p_encoded => FND_API.g_false,
206 p_count => x_msg_count,
207 p_data => x_msg_data
208 );
209
210 END create_list_word_replace;
211
212
213 /*****************************************************************************/
214 -- Procedure: update_list_word_replace
215 --
216 -- History
217 -- 01/24/2000 julou created
218 -------------------------------------------------------------------------------
219 PROCEDURE update_list_word_replace
220 (
221 p_api_version IN NUMBER,
222 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
223 p_commit IN VARCHAR2 := FND_API.g_false,
224 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
225
226 x_return_status OUT NOCOPY VARCHAR2,
227 x_msg_count OUT NOCOPY NUMBER,
228 x_msg_data OUT NOCOPY VARCHAR2,
229
230 p_wrd_rpl_rec IN wrd_rpl_rec_type
231 )
232 IS
233
234 l_api_version CONSTANT NUMBER := 1.0;
235 l_api_name CONSTANT VARCHAR2(30) := 'update_list_word_replace';
236 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
237 l_return_status VARCHAR2(1);
238 l_wrd_rpl_rec wrd_rpl_rec_type := p_wrd_rpl_rec;
239
240 BEGIN
241
242 -- initialize
243 SAVEPOINT update_list_word_replace;
244
245 IF FND_API.to_boolean(p_init_msg_list) THEN
246 FND_MSG_PUB.initialize;
247 END IF;
248
249 IF (AMS_DEBUG_HIGH_ON) THEN
250
251
252
253 AMS_Utility_PVT.debug_message(l_full_name || ': start');
254
255 END IF;
256
257 IF NOT FND_API.compatible_api_call
258 (
259 l_api_version,
260 p_api_version,
261 l_api_name,
262 g_pkg_name
263 )
264 THEN
265 RAISE FND_API.g_exc_unexpected_error;
266 END IF;
267
268 x_return_status := FND_API.g_ret_sts_success;
269
270 -- validate
271 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
272 IF (AMS_DEBUG_HIGH_ON) THEN
273
274 AMS_Utility_PVT.debug_message(l_full_name || ': validate');
275 END IF;
276
277 check_items
278 (
279 p_validation_mode => JTF_PLSQL_API.g_update,
280 x_return_status => l_return_status,
281 p_wrd_rpl_rec => l_wrd_rpl_rec
282 );
283
284 IF l_return_status = FND_API.g_ret_sts_error THEN
285 RAISE FND_API.g_exc_error;
286 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
287 RAISE FND_API.g_exc_unexpected_error;
288 END IF;
289 END IF;
290
291 -- complete record
292 complete_rec
293 (
294 p_wrd_rpl_rec,
295 l_wrd_rpl_rec
296 );
297
298 -- record level
299
300 -- update
301 IF (AMS_DEBUG_HIGH_ON) THEN
302
303 AMS_Utility_PVT.debug_message(l_full_name||': update');
304 END IF;
305
306 UPDATE AMS_LIST_WORD_REPLACES SET
307 last_update_date = SYSDATE,
308 last_updated_by = FND_GLOBAL.user_id,
309 last_update_login = FND_GLOBAL.conc_login_id,
310 object_version_number = l_wrd_rpl_rec.object_version_number + 1,
311 original_word = l_wrd_rpl_rec.original_word,
312 replacement_word = l_wrd_rpl_rec.replacement_word,
313 enabled_flag = l_wrd_rpl_rec.enabled_flag,
314 description = l_wrd_rpl_rec.description
315 WHERE list_word_replaces_id = l_wrd_rpl_rec.list_word_replaces_id
316 AND object_version_number = l_wrd_rpl_rec.object_version_number;
317
318 IF (SQL%NOTFOUND) THEN
319 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
320 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
321 FND_MSG_PUB.add;
322 END IF;
323 RAISE FND_API.g_exc_error;
324 END IF;
325
326 -- finish
327 IF FND_API.to_boolean(p_commit) THEN
328 COMMIT;
329 END IF;
330
331 FND_MSG_PUB.count_and_get
332 (
333 P_ENCODED => FND_API.g_false,
334 p_count => x_msg_count,
335 p_data => x_msg_data
336 );
337
338 IF (AMS_DEBUG_HIGH_ON) THEN
339
340
341
342 AMS_Utility_PVT.debug_message(l_full_name || ': end');
343
344 END IF;
345
346 EXCEPTION
347
348 WHEN FND_API.g_exc_error THEN
349 ROLLBACK TO update_list_word_replace;
350 x_return_status := FND_API.g_ret_sts_error;
351 FND_MSG_PUB.count_and_get
352 (
353 p_encoded => FND_API.g_false,
354 p_count => x_msg_count,
355 p_data => x_msg_data
356 );
357
358 WHEN FND_API.g_exc_unexpected_error THEN
359 ROLLBACK TO update_list_word_replace;
360 x_return_status := FND_API.g_ret_sts_unexp_error;
361 FND_MSG_PUB.count_and_get
362 (
363 p_encoded => FND_API.g_false,
364 p_count => x_msg_count,
365 p_data => x_msg_data
366 );
367
368 WHEN OTHERS THEN
369 ROLLBACK TO update_list_word_replace;
370 x_return_status :=FND_API.g_ret_sts_unexp_error;
371 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
372 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
373 END IF;
374 FND_MSG_PUB.count_and_get
375 (
376 p_encoded => FND_API.g_false,
377 p_count => x_msg_count,
378 p_data => x_msg_data
379 );
380
381 END update_list_word_replace;
382
383
384 /*****************************************************************************/
385 -- Procedure: delete_list_word_replace
386 --
387 -- History
388 -- 01/24/2000 julou created
389 -- 01/31/2000 mpande modified for cascade delete
390 -------------------------------------------------------------------------------
391 PROCEDURE delete_list_word_replace
392 (
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
395 p_commit IN VARCHAR2 := FND_API.g_false,
396
397 x_return_status OUT NOCOPY VARCHAR2,
398 x_msg_count OUT NOCOPY NUMBER,
399 x_msg_data OUT NOCOPY VARCHAR2,
400
401 p_wrd_rpl_id IN NUMBER,
402 p_object_version IN NUMBER
403 )
404 IS
405
406 l_api_version CONSTANT NUMBER := 1.0;
407 l_api_name CONSTANT VARCHAR2(30) := 'delete_list_word_replace';
408 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
409
410 CURSOR c_list_word_fields IS
411 SELECT * FROM AMS_LIST_WORD_FIELDS
412 WHERE LIST_WORD_REPLACES_ID = p_wrd_rpl_id;
413 l_return_status VARCHAR2(80);
414 l_msg_count NUMBER;
415 l_msg_data VARCHAR2(4000);
416
417 BEGIN
418 -- initialize
419 SAVEPOINT delete_list_word_replace;
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_REPLACES
453 WHERE list_word_replaces_id = p_wrd_rpl_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 IF (AMS_DEBUG_HIGH_ON) THEN
464
465 AMS_Utility_PVT.debug_message(l_full_name || ': list word fields delete');
466 END IF;
467
468 FOR l_word_fields_rec IN c_list_word_fields
469 LOOP
470 AMS_LIST_word_FIELDS_PVT.delete_list_word_field(
471 p_api_version => l_api_version,
472 p_init_msg_list => FND_API.g_true,
473 x_return_status => l_return_status,
474 x_msg_count => l_msg_count,
475 x_msg_data => l_msg_data,
476 p_wrd_fld_id => l_word_fields_rec.list_word_field_id,
477 p_object_version => l_word_fields_rec.object_version_number
478 );
479 END LOOP;
480
481 IF l_return_status = FND_API.g_ret_sts_error THEN
482 RAISE FND_API.g_exc_error;
483 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
484 RAISE FND_API.g_exc_unexpected_error;
485 END IF ;
486
487
488
489 -- finish
490 IF FND_API.to_boolean(p_commit) THEN
491 COMMIT;
492 END IF;
493
494 FND_MSG_PUB.count_and_get
495 (
496 P_ENCODED => FND_API.g_false,
497 p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500
501 IF (AMS_DEBUG_HIGH_ON) THEN
502
503
504
505 AMS_Utility_PVT.debug_message(l_full_name || ': end');
506
507 END IF;
508
509 EXCEPTION
510
511 WHEN FND_API.g_exc_error THEN
512 ROLLBACK TO delete_list_word_replace;
513 x_return_status := FND_API.g_ret_sts_error;
514 FND_MSG_PUB.count_and_get
515 (
516 p_encoded => FND_API.g_false,
517 p_count => x_msg_count,
518 p_data => x_msg_data
519 );
520
521 WHEN FND_API.g_exc_unexpected_error THEN
522 ROLLBACK TO delete_list_word_replace;
523 x_return_status := FND_API.g_ret_sts_unexp_error;
524 FND_MSG_PUB.count_and_get
525 (
526 p_encoded => FND_API.g_false,
527 p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530
531 WHEN OTHERS THEN
532 ROLLBACK TO delete_list_word_replace;
533 x_return_status :=FND_API.g_ret_sts_unexp_error;
534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
535 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
536 END IF;
537 FND_MSG_PUB.count_and_get
538 (
539 p_encoded => FND_API.g_false,
540 p_count => x_msg_count,
541 p_data => x_msg_data
542 );
543
544 END delete_list_word_replace;
545
546
547 /*****************************************************************************/
548 -- Procedure: lock_list_word_replace
549 --
550 -- History
551 -- 01/24/2000 julou created
552 -------------------------------------------------------------------------------
553 PROCEDURE lock_list_word_replace
554 (
555 p_api_version IN NUMBER,
556 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
557
558 x_return_status OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_msg_data OUT NOCOPY VARCHAR2,
561
562 p_wrd_rpl_id IN NUMBER,
563 p_object_version IN NUMBER
564 )
565 IS
566
567 l_api_version CONSTANT NUMBER := 1.0;
568 l_api_name CONSTANT VARCHAR2(30) := 'lock_list_word_replace';
569 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
570 l_wrd_rpl_id NUMBER;
571
572 CURSOR c_list_word_replace IS
573 SELECT list_word_replaces_id
574 FROM AMS_LIST_WORD_REPLACES
575 WHERE list_word_replaces_id = p_wrd_rpl_id
576 AND object_version_number = p_object_version
577 FOR UPDATE OF list_word_replaces_id NOWAIT;
578
579 BEGIN
580 -- initialize
581 IF (AMS_DEBUG_HIGH_ON) THEN
582
583 AMS_Utility_PVT.debug_message(l_full_name || ': start');
584 END IF;
585
586 IF FND_API.to_boolean(p_init_msg_list) THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590 IF NOT FND_API.compatible_api_call
591 (
592 l_api_version,
593 p_api_version,
594 l_api_name,
595 g_pkg_name
596 )
597 THEN
598 RAISE FND_API.g_exc_unexpected_error;
599 END IF;
600
601 x_return_status := FND_API.g_ret_sts_success;
602
603 -- lock
604 IF (AMS_DEBUG_HIGH_ON) THEN
605
606 AMS_Utility_PVT.debug_message(l_full_name || ': lock');
607 END IF;
608
609 OPEN c_list_word_replace;
610 FETCH c_list_word_replace INTO l_wrd_rpl_id;
611 IF (c_list_word_replace%NOTFOUND) THEN
612 CLOSE c_list_word_replace;
613 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
614 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
615 FND_MSG_PUB.add;
616 END IF;
617 RAISE FND_API.g_exc_error;
618 END IF;
619 CLOSE c_list_word_replace;
620
621 -- finish
622 FND_MSG_PUB.count_and_get
623 (
624 p_encoded => FND_API.g_false,
625 p_count => x_msg_count,
626 p_data => x_msg_data
627 );
628
629 IF (AMS_DEBUG_HIGH_ON) THEN
630
631
632
633 AMS_Utility_PVT.debug_message(l_full_name || ': end');
634
635 END IF;
636
637 EXCEPTION
638
639 WHEN AMS_Utility_PVT.resource_locked THEN
640 x_return_status := FND_API.g_ret_sts_error;
641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
642 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
643 FND_MSG_PUB.add;
644 END IF;
645 FND_MSG_PUB.count_and_get
646 (
647 p_encoded => FND_API.g_false,
648 p_count => x_msg_count,
649 p_data => x_msg_data
650 );
651
652 WHEN FND_API.g_exc_error THEN
653 x_return_status := FND_API.g_ret_sts_error;
654 FND_MSG_PUB.count_and_get
655 (
656 p_encoded => FND_API.g_false,
657 p_count => x_msg_count,
658 p_data => x_msg_data
659 );
660
661 WHEN FND_API.g_exc_unexpected_error THEN
662 x_return_status := FND_API.g_ret_sts_unexp_error;
663 FND_MSG_PUB.count_and_get
664 (
665 p_encoded => FND_API.g_false,
666 p_count => x_msg_count,
667 p_data => x_msg_data
668 );
669
670 WHEN OTHERS THEN
671 x_return_status :=FND_API.g_ret_sts_unexp_error;
672 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
673 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
674 END IF;
675 FND_MSG_PUB.count_and_get
676 (
677 p_encoded => FND_API.g_false,
678 p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681
682 END lock_list_word_replace;
683
684
685 /*****************************************************************************/
686 -- PROCEDURE
687 -- validate_list_word_replace
688 --
689 -- HISTORY
690 -- 01/24/2000 julou Created.
691 --------------------------------------------------------------------
692 PROCEDURE validate_list_word_replace
693 (
694 p_api_version IN NUMBER,
695 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
696 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
697
698 x_return_status OUT NOCOPY VARCHAR2,
699 x_msg_count OUT NOCOPY NUMBER,
700 x_msg_data OUT NOCOPY VARCHAR2,
701
702 p_wrd_rpl_rec IN wrd_rpl_rec_type
703 )
704 IS
705
706 l_api_version CONSTANT NUMBER := 1.0;
707 l_api_name CONSTANT VARCHAR2(30) := 'validate_list_word_replace';
708 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
709
710 l_return_status VARCHAR2(1);
711
712 BEGIN
713
714 ----------------------- initialize --------------------
715 IF (AMS_DEBUG_HIGH_ON) THEN
716
717 AMS_Utility_PVT.debug_message(l_full_name||': start');
718 END IF;
719
720 IF NOT FND_API.compatible_api_call
721 (
722 l_api_version,
723 p_api_version,
724 l_api_name,
725 g_pkg_name
726 )
727 THEN
728 RAISE FND_API.g_exc_unexpected_error;
729 END IF;
730
731 x_return_status := FND_API.g_ret_sts_success;
732
733 ---------------------- validate ------------------------
734 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
735 IF (AMS_DEBUG_HIGH_ON) THEN
736
737 AMS_Utility_PVT.debug_message(l_full_name||': check items');
738 END IF;
739 check_items
740 (
741 p_validation_mode => JTF_PLSQL_API.g_create,
742 x_return_status => l_return_status,
743 p_wrd_rpl_rec => p_wrd_rpl_rec
744 );
745
746 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
747 RAISE FND_API.g_exc_unexpected_error;
748 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
749 RAISE FND_API.g_exc_error;
750 END IF;
751 END IF;
752
753 -- record level
754
755 -------------------- finish --------------------------
756 FND_MSG_PUB.count_and_get
757 (
758 p_encoded => FND_API.g_false,
759 p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 IF (AMS_DEBUG_HIGH_ON) THEN
764
765
766
767 AMS_Utility_PVT.debug_message(l_full_name ||': end');
768
769 END IF;
770
771 EXCEPTION
772 WHEN FND_API.g_exc_error THEN
773 x_return_status := FND_API.g_ret_sts_error;
774 FND_MSG_PUB.count_and_get
775 (
776 p_encoded => FND_API.g_false,
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780
781 WHEN FND_API.g_exc_unexpected_error THEN
782 x_return_status := FND_API.g_ret_sts_unexp_error ;
783 FND_MSG_PUB.count_and_get
784 (
785 p_encoded => FND_API.g_false,
786 p_count => x_msg_count,
787 p_data => x_msg_data
788 );
789
790 WHEN OTHERS THEN
791 x_return_status := FND_API.g_ret_sts_unexp_error;
792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
793 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
794 END IF;
795
796 FND_MSG_PUB.count_and_get
797 (
798 p_encoded => FND_API.g_false,
799 p_count => x_msg_count,
800 p_data => x_msg_data
801 );
802
803 END validate_list_word_replace;
804
805 /*****************************************************************************/
806 -- Procedure: check_items
807 --
808 -- History
809 -- 01/24/2000 julou created
810 -------------------------------------------------------------------------------
811 PROCEDURE check_items
812 (
813 p_validation_mode IN VARCHAR2,
814 x_return_status OUT NOCOPY VARCHAR2,
815 p_wrd_rpl_rec IN wrd_rpl_rec_type
816 )
817 IS
818
819 l_api_version CONSTANT NUMBER := 1.0;
820 l_api_name CONSTANT VARCHAR2(30) := 'check_items';
821 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
822
823 BEGIN
824 -- initialize
825 IF (AMS_DEBUG_HIGH_ON) THEN
826
827 AMS_Utility_PVT.debug_message(l_full_name || ': start');
828 END IF;
829
830 x_return_status := FND_API.g_ret_sts_success;
831
832 -- check required items
833 IF (AMS_DEBUG_HIGH_ON) THEN
834
835 AMS_Utility_PVT.debug_message(l_full_name || ': check required items');
836 END IF;
837 check_req_items
838 (
839 p_validation_mode => p_validation_mode,
840 p_wrd_rpl_rec => p_wrd_rpl_rec,
841 x_return_status => x_return_status
842 );
843
844 IF x_return_status <> FND_API.g_ret_sts_success THEN
845 RETURN;
846 END IF;
847
848 -- check unique key items
849 IF (AMS_DEBUG_HIGH_ON) THEN
850
851 AMS_Utility_PVT.debug_message(l_full_name || ': check uk items');
852 END IF;
853 check_uk_items
854 (
855 p_validation_mode => p_validation_mode,
856 p_wrd_rpl_rec => p_wrd_rpl_rec,
857 x_return_status => x_return_status
858 );
859
860 IF x_return_status <> FND_API.g_ret_sts_success THEN
861 RETURN;
862 END IF;
863
864 END check_items;
865
866
867 /*****************************************************************************/
868 -- Procedure: check_req_items
869 --
870 -- History
871 -- 01/24/2000 julou created
872 -------------------------------------------------------------------------------
873 PROCEDURE check_req_items
874 (
875 p_validation_mode IN VARCHAR2,
876 p_wrd_rpl_rec IN wrd_rpl_rec_type,
877 x_return_status OUT NOCOPY VARCHAR2
878 )
879 IS
880
881 BEGIN
882
883 x_return_status := FND_API.g_ret_sts_success;
884
885 -- check list_word_replaces_id
886 IF p_wrd_rpl_rec.list_word_replaces_id IS NULL
887 AND p_validation_mode = JTF_PLSQL_API.g_update THEN
888 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
889 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_REPLACES_NO_ID');
890 FND_MSG_PUB.add;
891 END IF;
892
893 x_return_status := FND_API.g_ret_sts_error;
894 RETURN;
895 END IF;
896
897 -- check object_version_number
898 IF p_wrd_rpl_rec.object_version_number IS NULL
899 AND p_validation_mode = JTF_PLSQL_API.g_update
900 THEN
901 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
902 FND_MESSAGE.set_name('AMS', 'AMS_NO_OBJ_VER_NUM');
903 FND_MSG_PUB.add;
904 END IF;
905
906 x_return_status := FND_API.g_ret_sts_error;
907 RETURN;
908 END IF;
909
910 -- check original_word
911 IF p_wrd_rpl_rec.original_word IS NULL THEN
912 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
913 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_ORG_WRD');
914 FND_MSG_PUB.add;
915 END IF;
916
917 x_return_status := FND_API.g_ret_sts_error;
918 RETURN;
919 END IF;
920
921 -- check replacement_word
922 IF p_wrd_rpl_rec.replacement_word IS NULL THEN
923 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
924 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_RPL_WRD');
925 FND_MSG_PUB.add;
926 END IF;
927
928 x_return_status := FND_API.g_ret_sts_error;
929 RETURN;
930 END IF;
931
932 -- check enabled_flag
933 IF p_wrd_rpl_rec.enabled_flag IS NULL THEN
934 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
935 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WRD_FLD_NO_ENBL_FLAG');
936 FND_MSG_PUB.add;
937 END IF;
938
939 x_return_status := FND_API.g_ret_sts_error;
940 RETURN;
941 END IF;
942
943 IF p_wrd_rpl_rec.enabled_flag <> 'Y'
944 AND p_wrd_rpl_rec.enabled_flag <> 'N'
945 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_ENBL_FLAG');
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 END check_req_items;
956
957
958 /*****************************************************************************/
959 -- Procedure: check_uk_items
960 --
961 -- History
962 -- 01/24/2000 julou created
963 -------------------------------------------------------------------------------
964 PROCEDURE check_uk_items
965 (
966 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
967 p_wrd_rpl_rec IN wrd_rpl_rec_type,
968 x_return_status OUT NOCOPY VARCHAR2
969 )
970 IS
971
972 l_uk_flag VARCHAR2(1);
973
974 BEGIN
975
976 x_return_status := FND_API.g_ret_sts_success;
977
978 -- check PK, if list_word_replaces_id is passed in, must check if it is duplicate
979 IF p_validation_mode = JTF_PLSQL_API.g_create
980 AND p_wrd_rpl_rec.list_word_replaces_id IS NOT NULL
981 THEN
982 l_uk_flag := AMS_Utility_PVT.check_uniqueness
983 (
984 'AMS_LIST_WORD_REPLACES',
985 'list_word_replaces_id = ' || p_wrd_rpl_rec.list_word_replaces_id
986 );
987 END IF;
988
989 IF l_uk_flag = FND_API.g_false THEN
990 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
991 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_REPLACES_NO_ID');
992 FND_MSG_PUB.add;
993 END IF;
994
995 x_return_status := FND_API.g_ret_sts_error;
996 RETURN;
997 END IF;
998
999 -- check original_word, replacement_word
1000 IF p_wrd_rpl_rec.list_word_replaces_id IS NOT NULL THEN
1001 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1002 (
1003 'AMS_LIST_WORD_REPLACES',
1004 'list_word_replaces_id <> ' || p_wrd_rpl_rec.list_word_replaces_id
1005 || ' AND original_word = ''' || p_wrd_rpl_rec.original_word
1006 || ''' AND replacement_word = ''' || p_wrd_rpl_rec.replacement_word || ''''
1007 );
1008 ELSE
1009 l_uk_flag := AMS_Utility_PVT.check_uniqueness
1010 (
1011 'AMS_LIST_WORD_REPLACES',
1012 'original_word = ''' || p_wrd_rpl_rec.original_word
1013 ||''' AND replacement_word = ''' || p_wrd_rpl_rec.replacement_word || ''''
1014 );
1015 END IF;
1016
1017 IF l_uk_flag = FND_API.g_false THEN
1018 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1019 FND_MESSAGE.set_name('AMS', 'AMS_LIST_WORD_RPL_DUP_ORG_RPL');
1020 FND_MSG_PUB.add;
1021 END IF;
1022
1023 x_return_status := FND_API.g_ret_sts_error;
1024 RETURN;
1025 END IF;
1026
1027 END check_uk_items;
1028
1029
1030 /*****************************************************************************/
1031 -- Procedure: complete_rec
1032 --
1033 -- History
1034 -- 01/24/2000 julou created
1035 -------------------------------------------------------------------------------
1036 PROCEDURE complete_rec
1037 (
1038 p_wrd_rpl_rec IN wrd_rpl_rec_type,
1039 x_complete_rec OUT NOCOPY wrd_rpl_rec_type
1040 )
1041 IS
1042
1043 CURSOR c_list_word_replace IS
1044 SELECT * FROM AMS_LIST_WORD_REPLACES
1045 WHERE list_word_replaces_id = p_wrd_rpl_rec.list_word_replaces_id;
1046
1047 l_wrd_rpl_rec c_list_word_replace%ROWTYPE;
1048
1049 BEGIN
1050
1051 x_complete_rec := p_wrd_rpl_rec;
1052
1053 OPEN c_list_word_replace;
1054 FETCH c_list_word_replace INTO l_wrd_rpl_rec;
1055 IF (c_list_word_replace%NOTFOUND) THEN
1056 CLOSE c_list_word_replace;
1057 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1058 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1059 FND_MSG_PUB.add;
1060 END IF;
1061 RAISE FND_API.g_exc_error;
1062 END IF;
1063 CLOSE c_list_word_replace;
1064
1065 IF p_wrd_rpl_rec.original_word = FND_API.g_miss_char THEN
1066 x_complete_rec.original_word := l_wrd_rpl_rec.original_word;
1067 END IF;
1068
1069 IF p_wrd_rpl_rec.replacement_word = FND_API.g_miss_char THEN
1070 x_complete_rec.replacement_word := l_wrd_rpl_rec.replacement_word;
1071 END IF;
1072
1073 IF p_wrd_rpl_rec.enabled_flag = FND_API.g_miss_char THEN
1074 x_complete_rec.enabled_flag := l_wrd_rpl_rec.enabled_flag;
1075 END IF;
1076
1077 IF p_wrd_rpl_rec.description = FND_API.g_miss_char THEN
1078 x_complete_rec.description := l_wrd_rpl_rec.description;
1079 END IF;
1080
1081 END complete_rec;
1082
1083
1084 /****************************************************************************/
1085 -- Procedure
1086 -- init_rec
1087 --
1088 -- HISTORY
1089 -- 01/24/2000 julou Created.
1090 ------------------------------------------------------------------------------
1091 PROCEDURE init_rec
1092 (
1093 x_wrd_rpl_rec OUT NOCOPY wrd_rpl_rec_type
1094 )
1095 IS
1096
1097 BEGIN
1098
1099 x_wrd_rpl_rec.list_word_replaces_id := FND_API.g_miss_num;
1100 x_wrd_rpl_rec.last_update_date := FND_API.g_miss_date;
1101 x_wrd_rpl_rec.last_updated_by := FND_API.g_miss_num;
1102 x_wrd_rpl_rec.creation_date := FND_API.g_miss_date;
1103 x_wrd_rpl_rec.created_by := FND_API.g_miss_num;
1104 x_wrd_rpl_rec.last_update_login := FND_API.g_miss_num;
1105 x_wrd_rpl_rec.object_version_number := FND_API.g_miss_num;
1106 x_wrd_rpl_rec.original_word := FND_API.g_miss_char;
1107 x_wrd_rpl_rec.replacement_word := FND_API.g_miss_char;
1108 x_wrd_rpl_rec.enabled_flag := FND_API.g_miss_char;
1109 x_wrd_rpl_rec.description := FND_API.g_miss_char;
1110
1111 END init_rec;
1112 END AMS_List_Word_Replaces_PVT;