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