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