[Home] [Help]
PACKAGE BODY: APPS.AMS_CHANNEL_PVT
Source
1 Package Body AMS_CHANNEL_PVT AS
2 /* $Header: amsvchab.pls 115.30 2004/06/18 08:15:28 kgupta ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='AMS_CHANNEL_PVT';
5
6 -- HISTORY
7 -- mpande Created
8 -- 07/13/2000 ptendulk Added procedure 'Check_Chan_Record Bug#1353602
9 -- 01/18/2001 rssharma Removed internal_attribute attribute(from the record) and relateed code
10 -- 31-May-2001 soagrawa In check_chan_fk_items: Changed table name while checking for country id
11 -- 28-sep-2001 soagrawa In check_chan_fk_items: fixed bug# 2021940
12 -- 31-OCT-2001 rrajesh Bug fix:2089112
13 -- 03-Dec-2002 ptendulk Added fix for bug 2615287, increased length of vendor
14 -- 13-FEB-2003 vmodur Fix for Bug 2791639
15
16
17 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
18 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
19 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
20
21 PROCEDURE Check_Chan_Record(
22 p_chan_rec IN chan_rec_type,
23 p_complete_rec IN chan_rec_type,
24 x_return_status OUT NOCOPY VARCHAR2
25 ) ;
26
27 ---------------------------------------------------------------------
28 -- PROCEDURE
29 -- create_CHANNEL
30 --
31 -- HISTORY
32 -- 11/23/99 mpande Created.
33 -- 1. active_from_date,active_to_date and order_sequence are all for upgrade purposes only
34 -- so no validation is done for these columns
35 -- 08-Nov-2000 rrajesh Modified the uniqueness checking of channel_name, by replacing
36 -- the AMS_Utility_PVT call with c_name_unique_cr,
37 -- c_name_unique_up cursors.
38 -- 13-Dec-2000 rrajesh Changes for R4
39 -- 22-Dec-2000 rrajesh Removed the comments of internal channel flag.
40 -- 01/18/2001 rssharma Removed internal_resource attribute from the record and related code
41 ---------------------------------------------------------------------
42 PROCEDURE create_channel(
43 p_api_version IN NUMBER,
44 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
45 p_commit IN VARCHAR2 := FND_API.g_false,
46 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2,
50
51 p_chan_rec IN chan_rec_type,
52 x_chan_id OUT NOCOPY NUMBER
53 )
54 IS
55
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_api_name CONSTANT VARCHAR2(30) := 'create_channel';
58 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
59
60 l_return_status VARCHAR2(1);
61 l_chan_rec chan_rec_type := p_chan_rec;
62 l_chan_count NUMBER;
63
64 CURSOR c_chan_seq IS
65 SELECT ams_channels_b_s.NEXTVAL
66 FROM DUAL;
67
68 CURSOR c_chan_count(chan_id IN NUMBER) IS
69 SELECT COUNT(*)
70 FROM ams_channels_vl
71 WHERE channel_id = chan_id;
72
73 BEGIN
74
75 --------------------- initialize -----------------------
76 SAVEPOINT create_channel;
77
78 IF (AMS_DEBUG_HIGH_ON) THEN
79
80
81
82 AMS_Utility_PVT.debug_message(l_full_name||': start');
83
84 END IF;
85
86 IF FND_API.to_boolean(p_init_msg_list) THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 IF NOT FND_API.compatible_api_call(
91 l_api_version,
92 p_api_version,
93 l_api_name,
94 g_pkg_name
95 ) THEN
96 RAISE FND_API.g_exc_unexpected_error;
97 END IF;
98
99 x_return_status := FND_API.g_ret_sts_success;
100
101 ----------------------- validate -----------------------
102 IF (AMS_DEBUG_HIGH_ON) THEN
103
104 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
105 END IF;
106
107 validate_channel(
108 p_api_version => l_api_version,
109 p_init_msg_list => p_init_msg_list,
110 p_validation_level => p_validation_level,
111 x_return_status => l_return_status,
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data,
114 p_chan_rec => l_chan_rec
115 );
116 IF l_return_status = FND_API.g_ret_sts_error THEN
117 RAISE FND_API.g_exc_error;
118 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
119 RAISE FND_API.g_exc_unexpected_error;
120 END IF;
121
122 -- try to generate a unique id from the sequence
123 IF l_chan_rec.channel_id IS NULL THEN
124 LOOP
125 OPEN c_chan_seq;
126 FETCH c_chan_seq INTO l_chan_rec.channel_id;
127 CLOSE c_chan_seq;
128
129 OPEN c_chan_count(l_chan_rec.channel_id);
130 FETCH c_chan_count INTO l_chan_count;
131 CLOSE c_chan_count;
132
133 EXIT WHEN l_chan_count = 0;
134 END LOOP;
135 END IF;
136
137 -------------------------- insert --------------------------
138 IF (AMS_DEBUG_HIGH_ON) THEN
139
140 AMS_Utility_PVT.debug_message(l_full_name ||': insert');
141 END IF;
142
143 -- Added by rrajesh on 12/07/00
144 --l_chan_rec.channel_type_code := 'INTERNAL'; --commented OUT NOCOPY by rrajesh on 12/22/00
145 l_chan_rec.outbound_flag := 'Y';
146 l_chan_rec.inbound_flag := 'Y';
147 -- end 12/07/00
148
149 INSERT INTO ams_channels_b(
150 channel_id,
151 last_update_date,
152 last_updated_by,
153 creation_date,
154 created_by,
155 last_update_login,
156 object_version_number,
157 channel_type_code,
158 order_sequence,
159 managed_by_person_id,
160 outbound_flag,
161 inbound_flag,
162 active_from_date,
163 active_to_date,
164 rating,
165 preferred_vendor_id,
166 party_id,
167 attribute_category,
168 attribute1,
169 attribute2,
170 attribute3,
171 attribute4,
172 attribute5,
173 attribute6,
174 attribute7,
175 attribute8,
176 attribute9,
177 attribute10,
178 attribute11,
179 attribute12,
180 attribute13,
181 attribute14,
182 attribute15,
183 --rrajesh added on 12/07/00
184 country_id
185 -- Rahul Sharma removed on 01/18/2001
186 --internal_resource
187 --end 12/07/00
188 )
189 VALUES(
190 l_chan_rec.channel_id,
191 SYSDATE,
192 FND_GLOBAL.user_id,
193 SYSDATE,
194 FND_GLOBAL.user_id,
195 FND_GLOBAL.conc_login_id,
196 1, -- object_version_number
197 l_chan_rec.channel_type_code,
198 l_chan_rec.order_sequence,
199 -- no validation for order.This column is for upgrade purposes only
200 l_chan_rec.managed_by_person_id,
201
202 -- added by rrajesh on 12/07/00
203 --NVL(l_chan_rec.outbound_flag,'Y'),
204 --NVL(l_chan_rec.inbound_flag,'N'),
205 NVL(l_chan_rec.outbound_flag,'Y'),
206 NVL(l_chan_rec.inbound_flag,'Y'),
207 --end 12/07/00
208
209 NVL(l_chan_rec.active_from_date,SYSDATE),
210 -- no validation for active_for_date .This column is for upgrade purposes only
211 l_chan_rec.active_to_date,
212 -- no validation for active_to_date .This column is for upgrade purposes only
213 l_chan_rec.rating,
214 l_chan_rec.preferred_vendor_id,
215 l_chan_rec.party_id,
216 l_chan_rec.attribute_category,
217 l_chan_rec.attribute1,
218 l_chan_rec.attribute2,
219 l_chan_rec.attribute3,
220 l_chan_rec.attribute4,
221 l_chan_rec.attribute5,
222 l_chan_rec.attribute6,
223 l_chan_rec.attribute7,
224 l_chan_rec.attribute8,
225 l_chan_rec.attribute9,
226 l_chan_rec.attribute10,
227 l_chan_rec.attribute11,
228 l_chan_rec.attribute12,
229 l_chan_rec.attribute13,
230 l_chan_rec.attribute14,
231 l_chan_rec.attribute15,
232 --added by rrajesh 12/07/00
233 l_chan_rec.country_id
234 -- Rahul Sharma removed on 01/18/2001
235 --l_chan_rec.internal_resource
236 --end 12/07/00
237 );
238
239 INSERT INTO ams_channels_tl(
240 channel_id,
241 language,
242 last_update_date,
243 last_updated_by,
244 creation_date,
245 created_by,
246 last_update_login,
247 source_lang,
248 channel_name,
249 description
250 )
251 SELECT
252 l_chan_rec.channel_id,
253 l.language_code,
254 SYSDATE,
255 FND_GLOBAL.user_id,
256 SYSDATE,
257 FND_GLOBAL.user_id,
258 FND_GLOBAL.conc_login_id,
259 USERENV('LANG'),
260 l_chan_rec.channel_name,
261 l_chan_rec.description
262 FROM fnd_languages l
263 WHERE l.installed_flag in ('I', 'B')
264 AND NOT EXISTS(
265 SELECT NULL
266 FROM ams_channels_tl t
267 WHERE t.channel_id = l_chan_rec.channel_id
268 AND t.language = l.language_code );
269
270
271 ------------------------- finish -------------------------------
272 x_chan_id := l_chan_rec.channel_id;
273
274 IF FND_API.to_boolean(p_commit) THEN
275 COMMIT;
276 END IF;
277
278 FND_MSG_PUB.count_and_get(
279 p_encoded => FND_API.g_false,
280 p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283
284 IF (AMS_DEBUG_HIGH_ON) THEN
285
286
287
288 AMS_Utility_PVT.debug_message(l_full_name ||': end');
289
290 END IF;
291
292 EXCEPTION
293
294 WHEN FND_API.g_exc_error THEN
295 ROLLBACK TO create_channel;
296 x_return_status := FND_API.g_ret_sts_error;
297 FND_MSG_PUB.count_and_get(
298 p_encoded => FND_API.g_false,
299 p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302
303 WHEN FND_API.g_exc_unexpected_error THEN
304 ROLLBACK TO create_channel;
305 x_return_status := FND_API.g_ret_sts_unexp_error ;
306 FND_MSG_PUB.count_and_get(
307 p_encoded => FND_API.g_false,
308 p_count => x_msg_count,
309 p_data => x_msg_data
310 );
311
312
313 WHEN OTHERS THEN
314 ROLLBACK TO create_channel;
315 x_return_status := FND_API.g_ret_sts_unexp_error ;
316
317 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
318 THEN
319 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
320 END IF;
321
322 FND_MSG_PUB.count_and_get(
323 p_encoded => FND_API.g_false,
324 p_count => x_msg_count,
325 p_data => x_msg_data
326 );
327
328 END create_channel;
329
330
331 -----------------------------------------------------------------
332 -- PROCEDURE
333 -- delete_channel
334 --
335 -- HISTORY
336 -- 11/23/99 mpande Created.
337 -- 07/11/00 holiu Cannot delete if associated to activities.
338 -----------------------------------------------------------------
339 PROCEDURE delete_channel(
340 p_api_version IN NUMBER,
341 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
342 p_commit IN VARCHAR2 := FND_API.g_false,
343
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2,
347
348 p_chan_id IN NUMBER,
349 p_object_version IN NUMBER
350 )
351 IS
352
353 l_api_version CONSTANT NUMBER := 1.0;
354 l_api_name CONSTANT VARCHAR2(30) := 'delete_channel';
355 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
356 l_media_id NUMBER;
357
358 CURSOR c_media_id IS
359 SELECT media_id
360 FROM ams_media_channels
361 WHERE channel_id = p_chan_id
362 AND (active_to_date > SYSDATE OR active_to_date IS NULL);
363
364 -- Bug fix:2089112. Added by rrajesh on 10/31/01
365 CURSOR c_check_schedule_association IS
366 SELECT marketing_medium_id
367 FROM ams_campaign_schedules_b
368 WHERE marketing_medium_id = p_chan_id;
369 l_mktg_medium_id NUMBER;
370 -- End change 10/31/01
371
372 BEGIN
373
374 --------------------- initialize -----------------------
375 SAVEPOINT delete_channel;
376
377 IF (AMS_DEBUG_HIGH_ON) THEN
378
379
380
381 AMS_Utility_PVT.debug_message(l_full_name||': start');
382
383 END IF;
384
385 IF FND_API.to_boolean(p_init_msg_list) THEN
386 FND_MSG_PUB.initialize;
387 END IF;
388
389 IF NOT FND_API.compatible_api_call(
390 l_api_version,
391 p_api_version,
392 l_api_name,
393 g_pkg_name
394 ) THEN
395 RAISE FND_API.g_exc_unexpected_error;
396 END IF;
397
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400 -- holiu: the following checking is added for bug 1350477
401 IF (AMS_DEBUG_HIGH_ON) THEN
402
403 AMS_Utility_PVT.debug_message(l_full_name ||': check before delete');
404 END IF;
405
406 OPEN c_media_id;
407 FETCH c_media_id INTO l_media_id;
408 CLOSE c_media_id;
409
410 IF l_media_id IS NOT NULL THEN
411 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
412 THEN
413 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_CANNOT_DELETE');
414 FND_MSG_PUB.add;
415 END IF;
416 RAISE FND_API.g_exc_error;
417 END IF;
418
419 -- Bug fix:2089112. Added by rrajesh on 10/31/01
420 OPEN c_check_schedule_association;
421 FETCH c_check_schedule_association INTO l_mktg_medium_id;
422 CLOSE c_check_schedule_association;
423
424 IF l_mktg_medium_id IS NOT NULL THEN
425 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
426 THEN
427 FND_MESSAGE.set_name('AMS', 'AMS_MKTG_MEDIA_IS_USED_BY_CSCH');
428 FND_MSG_PUB.add;
429 END IF;
430 RAISE FND_API.g_exc_error;
431 END IF;
432 -- Bug fix:2089112. 10/31/01
433 ------------------------ delete ------------------------
434 IF (AMS_DEBUG_HIGH_ON) THEN
435
436 AMS_Utility_PVT.debug_message(l_full_name ||': delete');
437 END IF;
438
439 DELETE FROM ams_channels_b
440 WHERE channel_id = p_chan_id
441 AND object_version_number = p_object_version;
442
443 IF (SQL%NOTFOUND) THEN
444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
445 THEN
446 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
447 FND_MSG_PUB.add;
448 END IF;
449 RAISE FND_API.g_exc_error;
450 END IF;
451
452 DELETE FROM ams_channels_tl
453 WHERE channel_id = p_chan_id;
454
455 IF (SQL%NOTFOUND) THEN
456 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
457 THEN
458 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
459 FND_MSG_PUB.add;
460 END IF;
461 RAISE FND_API.g_exc_error;
462 END IF;
463
464 -------------------- finish --------------------------
465 IF FND_API.to_boolean(p_commit) THEN
466 COMMIT;
467 END IF;
468
469 FND_MSG_PUB.count_and_get(
470 p_encoded => FND_API.g_false,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474
475 IF (AMS_DEBUG_HIGH_ON) THEN
476
477
478
479 AMS_Utility_PVT.debug_message(l_full_name ||': end');
480
481 END IF;
482
483 EXCEPTION
484
485 WHEN FND_API.g_exc_error THEN
486 ROLLBACK TO delete_channel;
487 x_return_status := FND_API.g_ret_sts_error;
488 FND_MSG_PUB.count_and_get(
489 p_encoded => FND_API.g_false,
490 p_count => x_msg_count,
491 p_data => x_msg_data
492 );
493
494 WHEN FND_API.g_exc_unexpected_error THEN
495 ROLLBACK TO delete_channel;
496 x_return_status := FND_API.g_ret_sts_unexp_error ;
497 FND_MSG_PUB.count_and_get(
498 p_encoded => FND_API.g_false,
499 p_count => x_msg_count,
500 p_data => x_msg_data
501 );
502
503 WHEN OTHERS THEN
504 ROLLBACK TO delete_channel;
505 x_return_status := FND_API.g_ret_sts_unexp_error ;
506
507 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
508 THEN
509 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
510 END IF;
511
512 FND_MSG_PUB.count_and_get(
513 p_encoded => FND_API.g_false,
514 p_count => x_msg_count,
515 p_data => x_msg_data
516 );
517
518 END delete_channel;
519
520
524 --
521 -------------------------------------------------------------------
522 -- PROCEDURE
523 -- lock_channel
525 -- HISTORY
526 -- 11/23/99 mpande Created.
527 --------------------------------------------------------------------
528 PROCEDURE lock_channel(
529 p_api_version IN NUMBER,
530 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
531
532 x_return_status OUT NOCOPY VARCHAR2,
533 x_msg_count OUT NOCOPY NUMBER,
534 x_msg_data OUT NOCOPY VARCHAR2,
535
536 p_chan_id IN NUMBER,
537 p_object_version IN NUMBER
538 )
539 IS
540
541 l_api_version CONSTANT NUMBER := 1.0;
542 l_api_name CONSTANT VARCHAR2(30) := 'lock_channel';
543 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
544
545 l_chan_id NUMBER;
546
547 CURSOR c_chan_b IS
548 SELECT channel_id
549 FROM ams_channels_b
550 WHERE channel_id = p_chan_id
551 AND object_version_number = p_object_version
552 FOR UPDATE NOWAIT;
553
554 CURSOR c_chan_tl IS
555 SELECT channel_id
556 FROM ams_channels_tl
557 WHERE channel_id = p_chan_id
558 AND USERENV('LANG') IN (language, source_lang)
559 FOR UPDATE NOWAIT;
560
561 BEGIN
562
563 -------------------- initialize ------------------------
564 IF (AMS_DEBUG_HIGH_ON) THEN
565
566 AMS_Utility_PVT.debug_message(l_full_name||': start');
567 END IF;
568
569 IF FND_API.to_boolean(p_init_msg_list) THEN
570 FND_MSG_PUB.initialize;
571 END IF;
572
573 IF NOT FND_API.compatible_api_call(
574 l_api_version,
575 p_api_version,
576 l_api_name,
577 g_pkg_name
578 ) THEN
579 RAISE FND_API.g_exc_unexpected_error;
580 END IF;
581
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583
584 ------------------------ lock -------------------------
585 IF (AMS_DEBUG_HIGH_ON) THEN
586
587 AMS_Utility_PVT.debug_message(l_full_name||': lock');
588 END IF;
589
590 OPEN c_chan_b;
591 FETCH c_chan_b INTO l_chan_id;
592 IF (c_chan_b%NOTFOUND) THEN
593 CLOSE c_chan_b;
594 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
595 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
596 FND_MSG_PUB.add;
597 END IF;
598 RAISE FND_API.g_exc_error;
599 END IF;
600 CLOSE c_chan_b;
601
602 OPEN c_chan_tl;
603 CLOSE c_chan_tl;
604
605 -------------------- finish --------------------------
606 FND_MSG_PUB.count_and_get(
607 p_encoded => FND_API.g_false,
608 p_count => x_msg_count,
609 p_data => x_msg_data
610 );
611
612 IF (AMS_DEBUG_HIGH_ON) THEN
613
614
615
616 AMS_Utility_PVT.debug_message(l_full_name ||': end');
617
618 END IF;
619
620 EXCEPTION
621
622 WHEN AMS_Utility_PVT.resource_locked THEN
623 x_return_status := FND_API.g_ret_sts_error;
624 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
625 FND_MESSAGE.set_name('AMS', 'AMS_API_RESOURCE_LOCKED');
626 FND_MSG_PUB.add;
627 END IF;
628
629 FND_MSG_PUB.count_and_get(
630 p_encoded => FND_API.g_false,
631 p_count => x_msg_count,
632 p_data => x_msg_data
633 );
634
635 WHEN FND_API.g_exc_error THEN
636 x_return_status := FND_API.g_ret_sts_error;
637 FND_MSG_PUB.count_and_get(
638 p_encoded => FND_API.g_false,
639 p_count => x_msg_count,
640 p_data => x_msg_data
641 );
642
643 WHEN FND_API.g_exc_unexpected_error THEN
644 x_return_status := FND_API.g_ret_sts_unexp_error ;
645 FND_MSG_PUB.count_and_get(
646 p_encoded => FND_API.g_false,
647 p_count => x_msg_count,
648 p_data => x_msg_data
649 );
650
651 WHEN OTHERS THEN
652 x_return_status := FND_API.g_ret_sts_unexp_error ;
653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
654 THEN
655 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
656 END IF;
657
658 FND_MSG_PUB.count_and_get(
659 p_encoded => FND_API.g_false,
660 p_count => x_msg_count,
661 p_data => x_msg_data
662 );
663
664 END lock_channel;
665
666
667 ---------------------------------------------------------------------
668 -- PROCEDURE
669 -- update_channel
670 --
671 -- HISTORY
672 -- 11/23/99 mpande Created.
673 -- 01/18/2001 rssharma removed internal_resource attribute
674 ----------------------------------------------------------------------
675 PROCEDURE update_channel(
676 p_api_version IN NUMBER,
677 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
678 p_commit IN VARCHAR2 := FND_API.g_false,
682 x_msg_count OUT NOCOPY NUMBER,
679 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
680
681 x_return_status OUT NOCOPY VARCHAR2,
683 x_msg_data OUT NOCOPY VARCHAR2,
684
685 p_chan_rec IN chan_rec_type
686 )
687 IS
688
689 l_api_version CONSTANT NUMBER := 1.0;
690 l_api_name CONSTANT VARCHAR2(30) := 'update_channel';
691 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
692
693 l_chan_rec chan_rec_type;
694 l_return_status VARCHAR2(1);
695
696 BEGIN
697
698 -------------------- initialize -------------------------
699 SAVEPOINT update_channel;
700
701 IF (AMS_DEBUG_HIGH_ON) THEN
702
703
704
705 AMS_Utility_PVT.debug_message(l_full_name||': start');
706
707 END IF;
708
709 IF FND_API.to_boolean(p_init_msg_list) THEN
710 FND_MSG_PUB.initialize;
711 END IF;
712
713 IF NOT FND_API.compatible_api_call(
714 l_api_version,
715 p_api_version,
716 l_api_name,
717 g_pkg_name
718 ) THEN
719 RAISE FND_API.g_exc_unexpected_error;
720 END IF;
721
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 ----------------------- validate ----------------------
725 IF (AMS_DEBUG_HIGH_ON) THEN
726
727 AMS_Utility_PVT.debug_message(l_full_name ||': validate');
728 END IF;
729
730 -- replace g_miss_char/num/date with current column values
731 complete_chan_rec(p_chan_rec, l_chan_rec);
732 -- item level
733 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
734 check_chan_items(
735 p_chan_rec => l_chan_rec, -- change from p_chan_rec vmodur
736 p_validation_mode => JTF_PLSQL_API.g_update,
737 x_return_status => l_return_status
738 );
739 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
740 RAISE FND_API.g_exc_unexpected_error;
741 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
742 RAISE FND_API.g_exc_error;
743 END IF;
744 END IF;
745
746 --=============================================================================
747 -- Following call is added by ptendulk on Jul 13th 2000 Ref: Bug#1353602
748 --=============================================================================
749
750 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
751 Check_Chan_Record(
752 p_chan_rec => l_chan_rec, -- change from p_chan_rec vmodur
753 p_complete_rec => l_chan_rec,
754 x_return_status => l_return_status
755 );
756
757
758 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
759 RAISE FND_API.g_exc_unexpected_error;
760 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
761 RAISE FND_API.g_exc_error;
762 END IF;
763 END IF;
764
765 -- record level
766 -------------------------- update --------------------
767 IF (AMS_DEBUG_HIGH_ON) THEN
768
769 AMS_Utility_PVT.debug_message(l_full_name ||': update');
770 END IF;
771
772 -- Added by rrajesh on 12/07/00
773 --l_chan_rec.channel_type_code := 'INTERNAL'; -- commented out by rrajesh on 12/22/00
774 l_chan_rec.outbound_flag := 'Y';
775 l_chan_rec.inbound_flag := 'Y';
776 -- end 12/07/00
777
778
779 UPDATE ams_channels_b SET
780 last_update_date = SYSDATE,
781 last_updated_by = FND_GLOBAL.user_id,
782 last_update_login = FND_GLOBAL.conc_login_id,
783 object_version_number = l_chan_rec.object_version_number + 1,
784 channel_type_code = l_chan_rec.channel_type_code,
785 order_sequence = l_chan_rec.order_sequence,
786 managed_by_person_id = l_chan_rec.managed_by_person_id,
787 --rrajesh added 12/07/00
788 --outbound_flag = NVL(l_chan_rec.outbound_flag,'Y'),
789 --inbound_flag = NVL(l_chan_rec.inbound_flag,'N'),
790 outbound_flag = NVL(l_chan_rec.outbound_flag,'Y'),
791 inbound_flag = NVL(l_chan_rec.inbound_flag,'Y'),
792 --end 12/07/00
793 active_from_date = l_chan_rec.active_from_date,
794 active_to_date = l_chan_rec.active_to_date,
795 rating = l_chan_rec.rating,
796 preferred_vendor_id = l_chan_rec.preferred_vendor_id,
797 party_id = l_chan_rec.party_id,
798 attribute_category = l_chan_rec.attribute_category,
799 attribute1 = l_chan_rec.attribute1,
800 attribute2 = l_chan_rec.attribute2,
801 attribute3 = l_chan_rec.attribute3,
802 attribute4 = l_chan_rec.attribute4,
803 attribute5 = l_chan_rec.attribute5,
804 attribute6 = l_chan_rec.attribute6,
805 attribute7 = l_chan_rec.attribute7,
806 attribute8 = l_chan_rec.attribute8,
807 attribute9 = l_chan_rec.attribute9,
808 attribute10 = l_chan_rec.attribute10,
809 attribute11 = l_chan_rec.attribute11,
810 attribute12 = l_chan_rec.attribute12,
811 attribute13 = l_chan_rec.attribute13,
812 attribute14 = l_chan_rec.attribute14,
813 attribute15 = l_chan_rec.attribute15,
814 --rrajesh added 12/07/00
815 country_id = l_chan_rec.country_id
819 --end 12/07/00
816 -- Rahul Sharma removed 01/18/2001
817 --internal_resource = l_chan_rec.internal_resource
818 -- end 01/18/2001
820 WHERE channel_id = l_chan_rec.channel_id
821 AND object_version_number = l_chan_rec.object_version_number;
822
823 IF (SQL%NOTFOUND) THEN
824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
825 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
826 FND_MSG_PUB.add;
827 END IF;
828 RAISE FND_API.g_exc_error;
829 END IF;
830
831 update ams_channels_tl set
832 channel_name = l_chan_rec.channel_name,
833 description = l_chan_rec.description,
834 last_update_date = SYSDATE,
835 last_updated_by = FND_GLOBAL.user_id,
836 last_update_login = FND_GLOBAL.conc_login_id,
837 source_lang = USERENV('LANG')
838 WHERE channel_id = l_chan_rec.channel_id
839 AND USERENV('LANG') IN (language, source_lang);
840
841 IF (SQL%NOTFOUND) THEN
842 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
843 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
844 FND_MSG_PUB.add;
845 END IF;
846 RAISE FND_API.g_exc_error;
847 END IF;
848
849 -------------------- finish --------------------------
850 IF FND_API.to_boolean(p_commit) THEN
851 COMMIT;
852 END IF;
853
854 FND_MSG_PUB.count_and_get(
855 p_encoded => FND_API.g_false,
856 p_count => x_msg_count,
857 p_data => x_msg_data
858 );
859
860 IF (AMS_DEBUG_HIGH_ON) THEN
861
862
863
864 AMS_Utility_PVT.debug_message(l_full_name ||': end');
865
866 END IF;
867
868 EXCEPTION
869
870 WHEN FND_API.g_exc_error THEN
871 ROLLBACK TO update_channel;
872 x_return_status := FND_API.g_ret_sts_error;
873 FND_MSG_PUB.count_and_get(
874 p_encoded => FND_API.g_false,
875 p_count => x_msg_count,
876 p_data => x_msg_data
877 );
878
879 WHEN FND_API.g_exc_unexpected_error THEN
880 ROLLBACK TO update_channel;
881 x_return_status := FND_API.g_ret_sts_unexp_error;
882 FND_MSG_PUB.count_and_get(
883 p_encoded => FND_API.g_false,
884 p_count => x_msg_count,
885 p_data => x_msg_data
886 );
887
888 WHEN OTHERS THEN
889 ROLLBACK TO update_channel;
890 x_return_status := FND_API.g_ret_sts_unexp_error;
891
892 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
893 THEN
894 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
895 END IF;
896
897 FND_MSG_PUB.count_and_get(
898 p_encoded => FND_API.g_false,
899 p_count => x_msg_count,
900 p_data => x_msg_data
901 );
902
903 END update_channel;
904
905
906 --------------------------------------------------------------------
907 -- PROCEDURE
908 -- validate_channel
909 --
910 -- HISTORY
911 -- 11/23/99 mpande Created.
912 -- 1. active_from_date,active_to_date and order_sequence are all for upgrade purposes only
913 -- so no validation is done for these columns
914 --------------------------------------------------------------------
915 PROCEDURE validate_channel(
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
918 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
919
920 x_return_status OUT NOCOPY VARCHAR2,
921 x_msg_count OUT NOCOPY NUMBER,
922 x_msg_data OUT NOCOPY VARCHAR2,
923
924 p_chan_rec IN chan_rec_type
925 )
926 IS
927
928 l_api_version CONSTANT NUMBER := 1.0;
929 l_api_name CONSTANT VARCHAR2(30) := 'validate_channel';
930 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
931
932 l_return_status VARCHAR2(1);
933
934 BEGIN
935
936 ----------------------- initialize --------------------
937 IF (AMS_DEBUG_HIGH_ON) THEN
938
939 AMS_Utility_PVT.debug_message(l_full_name||': start');
940 END IF;
941
942 IF FND_API.to_boolean(p_init_msg_list) THEN
943 FND_MSG_PUB.initialize;
944 END IF;
945
946 IF NOT FND_API.compatible_api_call(
947 l_api_version,
948 p_api_version,
949 l_api_name,
950 g_pkg_name
951 ) THEN
952 RAISE FND_API.g_exc_unexpected_error;
953 END IF;
954
955 x_return_status := FND_API.g_ret_sts_success;
956
957 ---------------------- validate ------------------------
958 IF (AMS_DEBUG_HIGH_ON) THEN
959
960 AMS_Utility_PVT.debug_message(l_full_name||': check items');
961 END IF;
962
963 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
964 check_chan_items(
965 p_chan_rec => p_chan_rec,
966 p_validation_mode => JTF_PLSQL_API.g_create,
967 x_return_status => l_return_status
968 );
972 RAISE FND_API.g_exc_error;
969 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
970 RAISE FND_API.g_exc_unexpected_error;
971 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
973 END IF;
974 END IF;
975
976 --=============================================================================
977 -- Following call is added by ptendulk on Jul 13th 2000 Ref: Bug#1353602
978 --=============================================================================
979 IF (AMS_DEBUG_HIGH_ON) THEN
980
981 AMS_Utility_PVT.debug_message(l_full_name||': check Records');
982 END IF;
983 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
984 Check_Chan_Record(
985 p_chan_rec => p_chan_rec,
986 p_complete_rec => null,
987 x_return_status => l_return_status
988 );
989
990 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
991 RAISE FND_API.g_exc_unexpected_error;
992 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
993 RAISE FND_API.g_exc_error;
994 END IF;
995 END IF;
996
997 -------------------- finish --------------------------
998 FND_MSG_PUB.count_and_get(
999 p_encoded => FND_API.g_false,
1000 p_count => x_msg_count,
1001 p_data => x_msg_data
1002 );
1003
1004 IF (AMS_DEBUG_HIGH_ON) THEN
1005
1006
1007
1008 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1009
1010 END IF;
1011
1012 EXCEPTION
1013
1014 WHEN FND_API.g_exc_error THEN
1015 x_return_status := FND_API.g_ret_sts_error;
1016 FND_MSG_PUB.count_and_get(
1017 p_encoded => FND_API.g_false,
1018 p_count => x_msg_count,
1019 p_data => x_msg_data
1020 );
1021
1022 WHEN FND_API.g_exc_unexpected_error THEN
1023 x_return_status := FND_API.g_ret_sts_unexp_error ;
1024 FND_MSG_PUB.count_and_get(
1025 p_encoded => FND_API.g_false,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data
1028 );
1029
1030 WHEN OTHERS THEN
1031 x_return_status := FND_API.g_ret_sts_unexp_error;
1032 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1033 THEN
1034 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1035 END IF;
1036
1037 FND_MSG_PUB.count_and_get(
1038 p_encoded => FND_API.g_false,
1039 p_count => x_msg_count,
1040 p_data => x_msg_data
1041 );
1042
1043 END validate_channel;
1044
1045
1046 ---------------------------------------------------------------------
1047 -- PROCEDURE
1048 -- check_camp_req_items
1049 --
1050 -- HISTORY
1051 -- 11/23/99 mpande Created.
1052 --
1053 -- NOTES
1054 -- 1. We don't check active_from_date and any flags.
1055 ---------------------------------------------------------------------
1056 PROCEDURE check_chan_req_items(
1057 p_chan_rec IN chan_rec_type,
1058 x_return_status OUT NOCOPY VARCHAR2
1059 )
1060 IS
1061 BEGIN
1062
1063 x_return_status := FND_API.g_ret_sts_success;
1064 ------------------------ channel_type --------------------------
1065 IF p_chan_rec.channel_type_code IS NULL THEN
1066 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1067 THEN
1068 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_INVALID_TYPE_CODE');
1069 FND_MSG_PUB.add;
1070 END IF;
1071 x_return_status := FND_API.g_ret_sts_error;
1072 RETURN;
1073 END IF;
1074
1075 ------------------------ channel_name --------------------------
1076 IF p_chan_rec.channel_name IS NULL THEN
1077 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1078 THEN
1079 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_NO_NAME');
1080 FND_MSG_PUB.add;
1081 END IF;
1082 x_return_status := FND_API.g_ret_sts_error;
1083 RETURN;
1084 END IF;
1085
1086 -- Added by rrajesh on 12/07/00
1087 ------------------------ country_id --------------------------
1088 IF p_chan_rec.country_id IS NULL THEN
1089 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1090 THEN
1091 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_INVALID_COUNTRY_ID');
1092 FND_MSG_PUB.add;
1093 END IF;
1094 x_return_status := FND_API.g_ret_sts_error;
1095 RETURN;
1096 END IF;
1097 -- end rrajesh 12/07/00
1098
1099 -- Added by vmodur on 02/13/2003
1100 ------------------------ Active From Date ---------------------
1101 IF p_chan_rec.active_from_date IS NULL THEN
1102 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1103 THEN
1104 FND_MESSAGE.set_name('AMS', 'AMS_MED_CFD_IS_NULL');
1105 FND_MSG_PUB.add;
1106 END IF;
1107 x_return_status := FND_API.g_ret_sts_error;
1108 RETURN;
1109 END IF;
1110 -- end vmodur
1111
1112
1113 END check_chan_req_items;
1114
1115
1116 ---------------------------------------------------------------------
1117 -- PROCEDURE
1121 -- 23/11/99 mpande Created.
1118 -- check_chan_uk_items
1119 --
1120 -- HISTORY
1122 ---------------------------------------------------------------------
1123 PROCEDURE check_chan_uk_items(
1124 p_chan_rec IN chan_rec_type,
1125 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1126 x_return_status OUT NOCOPY VARCHAR2
1127 )
1128 IS
1129 l_valid_flag VARCHAR2(1);
1130 -- rrajesh 11/08/00 start
1131 l_count NUMBER ;
1132
1133 -- Modified by rrajesh 12/07/00
1134 /*CURSOR c_name_unique_cr (p_channel_name IN VARCHAR2) IS
1135 SELECT COUNT(1)
1136 FROM ams_channels_vl
1137 WHERE UPPER(channel_name) = UPPER(p_channel_name) ;*/
1138 CURSOR c_name_unique_cr (p_channel_name IN VARCHAR2, p_country_id IN NUMBER) IS
1139 SELECT COUNT(1)
1140 FROM ams_channels_vl
1141 WHERE UPPER(channel_name) = UPPER(p_channel_name)
1142 AND country_id = p_country_id;
1143
1144 /*CURSOR c_name_unique_up (p_channel_name IN VARCHAR2, p_channel_id IN NUMBER) IS
1145 SELECT COUNT(1)
1146 FROM ams_channels_vl
1147 WHERE UPPER(channel_name) = UPPER(p_channel_name)
1148 AND channel_id <> p_channel_id ;*/
1149 CURSOR c_name_unique_up (p_channel_name IN VARCHAR2, p_channel_id IN NUMBER, p_country_id IN NUMBER) IS
1150 SELECT COUNT(1)
1151 FROM ams_channels_vl
1152 WHERE UPPER(channel_name) = UPPER(p_channel_name)
1153 AND country_id = p_country_id
1154 AND channel_id <> p_channel_id ;
1155 --end 12/07/00
1156 -- end rrajesh 11/08
1157
1158 BEGIN
1159 x_return_status := FND_API.g_ret_sts_success;
1160 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Check the uniqueness Count - begin'); END IF;
1161 -- For create_channel, when channel_id is passed in, we need to
1162 -- check if this channel_id is unique.
1163 IF p_validation_mode = JTF_PLSQL_API.g_create
1164 AND p_chan_rec.channel_id IS NOT NULL
1165 THEN
1166 IF AMS_Utility_PVT.check_uniqueness(
1167 'ams_channels_vl',
1168 'channel_id = ' || p_chan_rec.channel_id
1169 ) = FND_API.g_false
1170 THEN
1171 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1172 THEN
1173 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_DUPLICATE_ID');
1174 FND_MSG_PUB.add;
1175 END IF;
1176 x_return_status := FND_API.g_ret_sts_error;
1177 RETURN;
1178 END IF;
1179 END IF;
1180
1181
1182 -- Check if channel_name is unique. Need to handle create and
1183 -- update differently.
1184 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1185
1186 -- rrajesh start 11/08. Replaced the AMS_Utility_PVT call
1187 -- with the new cursor.
1188 --modified by rrajesh on 12/07/00
1189 --OPEN c_name_unique_cr(p_chan_rec.channel_name) ;
1190 OPEN c_name_unique_cr(p_chan_rec.channel_name, p_chan_rec.country_id) ;
1191 -- end 12/07/00
1192 FETCH c_name_unique_cr INTO l_count ;
1193 CLOSE c_name_unique_cr ;
1194
1195 --l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1196 -- 'ams_channels_vl',
1197 -- 'channel_name = ''' || p_chan_rec.channel_name ||''''
1198 --);
1199 ELSE
1200 --l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1201 -- 'ams_channels_vl',
1202 -- 'channel_name = ''' || p_chan_rec.channel_name ||
1203 -- ''' AND channel_id <> ' || p_chan_rec.channel_id
1204 --);
1205 -- modified by rrajesh on 12/07/00
1206 --OPEN c_name_unique_up(p_chan_rec.channel_name,p_chan_rec.channel_id) ;
1207 OPEN c_name_unique_up(p_chan_rec.channel_name,p_chan_rec.channel_id, p_chan_rec.country_id) ;
1208 -- end 12/07/00
1209 FETCH c_name_unique_up INTO l_count ;
1210 CLOSE c_name_unique_up ;
1211 -- rrajesh end 11/08
1212 END IF;
1213
1214 -- rrajesh start 11/08. Checking the uniqueness against the new cursor,
1215 -- c_name_unique_up
1216 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Check the uniqueness Count '|| l_count ); END IF;
1217 IF l_count > 0 THEN
1218 --IF l_valid_flag = FND_API.g_false THEN
1219 -- rrajesh end 11/08
1220 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1221 THEN
1222 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_DUPLICATE_NAME');
1223 FND_MSG_PUB.add;
1224 END IF;
1225 x_return_status := FND_API.g_ret_sts_error;
1226 RETURN;
1227 END IF;
1228
1229 END check_chan_uk_items;
1230
1231
1232 ---------------------------------------------------------------------
1233 -- PROCEDURE
1234 -- check_chan_fk_items
1235 --
1236 -- HISTORY
1237 -- 11/23/99 mpande Created.
1238 -- 31-May-2001 soagrawa Changed table name while checking for country id
1239 -- 28-sep-2001 soagrawa Modified additional where clause in check for country fk, bug# 2021940
1240 ---------------------------------------------------------------------
1241 PROCEDURE check_chan_fk_items(
1242 p_chan_rec IN chan_rec_type,
1243 x_return_status OUT NOCOPY VARCHAR2
1244 )
1245 IS
1246 BEGIN
1247
1248 x_return_status := FND_API.g_ret_sts_success;
1249
1250 ----------------------- managed_by_person_id ------------------------
1251 IF p_chan_rec.managed_by_person_id <> FND_API.g_miss_num THEN
1255 p_chan_rec.managed_by_person_id
1252 IF AMS_Utility_PVT.check_fk_exists(
1253 'ams_jtf_rs_emp_v',
1254 'resource_id',
1256 ) = FND_API.g_false
1257 THEN
1258 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1259 THEN
1260 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_BAD_MANAGED_BY_ID');
1261 FND_MSG_PUB.add;
1262 END IF;
1263 x_return_status := FND_API.g_ret_sts_error;
1264 RETURN;
1265 END IF;
1266 END IF;
1267
1268 --------------------- preferred_vendor_id ------------------------
1269 IF p_chan_rec.preferred_vendor_id <> FND_API.g_miss_num THEN
1270 IF AMS_Utility_PVT.check_fk_exists(
1271 'po_vendors',
1272 'vendor_id',
1273 p_chan_rec.preferred_vendor_id
1274 ) = FND_API.g_false
1275 THEN
1276 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1277 THEN
1278 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_VENDOR_ID');
1279 FND_MSG_PUB.add;
1280 END IF;
1281 x_return_status := FND_API.g_ret_sts_error;
1282 RETURN;
1283 END IF;
1284 END IF;
1285 --------------------- party_id ------------------------
1286 IF p_chan_rec.party_id <> FND_API.g_miss_num THEN
1287 IF AMS_Utility_PVT.check_fk_exists(
1288 'HZ_PARTIES',
1289 'party_id',
1290 p_chan_rec.party_id
1291 ) = FND_API.g_false
1292 THEN
1293 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1294 THEN
1295 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_PARTY_ID');
1296 FND_MSG_PUB.add;
1297 END IF;
1298 x_return_status := FND_API.g_ret_sts_error;
1299 RETURN;
1300 END IF;
1301 END IF;
1302
1303 --Added by rrajesh on 12/07/00
1304 --------------------- country_id ------------------------
1305 IF p_chan_rec.country_id <> FND_API.g_miss_num THEN
1306
1307 -- modified by soagrawa on 31-May-2001
1308 IF AMS_Utility_PVT.check_fk_exists(
1309 p_table_name => 'jtf_loc_hierarchies_vl', --'jtf_loc_areas_vl',
1310 p_pk_name => 'location_hierarchy_id', --'location_area_id',
1311 p_pk_value => p_chan_rec.country_id,
1312 p_pk_data_type => AMS_Utility_PVT.G_NUMBER,
1313 -- modified by soagrawa on 28-sep-2001, bug# 2021940
1314 p_additional_where_clause => ' location_type_code = ''COUNTRY'' ' --' location_type_name = ''Country'' '
1315 ) = FND_API.g_false
1316 THEN
1317 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1318 THEN
1319 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_COUNTRY_ID');
1320 FND_MSG_PUB.add;
1321 END IF;
1322 x_return_status := FND_API.g_ret_sts_error;
1323 RETURN;
1324 END IF;
1325 END IF;
1326
1327 --end 12/07/00
1328
1329 END check_chan_fk_items;
1330
1331
1332 ---------------------------------------------------------------------
1333 -- PROCEDURE
1334 -- check_chan_lookup_items
1335 --
1336 -- HISTORY
1337 -- 23/11/99 mpande Created.
1338 ---------------------------------------------------------------------
1339 PROCEDURE check_chan_lookup_items(
1340 p_chan_rec IN chan_rec_type,
1341 x_return_status OUT NOCOPY VARCHAR2
1342 )
1343 IS
1344 BEGIN
1345
1346 x_return_status := FND_API.g_ret_sts_success;
1347
1348 ----------------------- channel_type_code ------------------------
1349 IF p_chan_rec.channel_type_code <> FND_API.g_miss_char THEN
1350 IF AMS_Utility_PVT.check_lookup_exists(
1351 p_lookup_type => 'AMS_CHANNEL_TYPE',
1352 p_lookup_code => p_chan_rec.channel_type_code
1353 ) = FND_API.g_false
1354 THEN
1355 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1356 THEN
1357 FND_MESSAGE.set_name('AMS', 'AMS_CAMP_WRONG_CHANNEL_TYPE');
1358 FND_MSG_PUB.add;
1359 END IF;
1360 x_return_status := FND_API.g_ret_sts_error;
1361 RETURN;
1362 END IF;
1363 END IF;
1364
1365 ----------------------- rating ------------------------
1366 IF p_chan_rec.rating <> FND_API.g_miss_char
1367 AND p_chan_rec.rating IS NOT NULL
1368 THEN
1369 IF AMS_Utility_PVT.check_lookup_exists(
1370 p_lookup_type => 'AMS_CHANNEL_RATING',
1371 p_lookup_code => p_chan_rec.rating
1372 ) = FND_API.g_false
1373 THEN
1374 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1375 THEN
1376 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_RATING_VALUE');
1377 FND_MSG_PUB.add;
1378 END IF;
1379 x_return_status := FND_API.g_ret_sts_error;
1380 RETURN;
1381 END IF;
1382 END IF;
1383
1384
1385 END check_chan_lookup_items;
1386
1387 -- commented out by rrajesh on 12/07/00
1388 /*
1389
1390 ---------------------------------------------------------------------
1394 -- HISTORY
1391 -- PROCEDURE
1392 -- check_chan_flag_items
1393 --
1395 -- 23/11/99 mpande Created.
1396 ---------------------------------------------------------------------
1397 PROCEDURE check_chan_flag_items(
1398 p_chan_rec IN chan_rec_type,
1399 x_return_status OUT NOCOPY VARCHAR2
1400 )
1401 IS
1402 BEGIN
1403
1404 x_return_status := FND_API.g_ret_sts_success;
1405
1406 ----------------------- outbound_flag ------------------------
1407 IF p_chan_rec.outbound_flag <> FND_API.g_miss_char
1408 AND p_chan_rec.outbound_flag IS NOT NULL
1409 THEN
1410 IF AMS_Utility_PVT.is_Y_or_N(p_chan_rec.outbound_flag) = FND_API.g_false
1411 THEN
1412 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1413 THEN
1414 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_OUTBOUND_FLAG');
1415 FND_MSG_PUB.add;
1416 END IF;
1417 x_return_status := FND_API.g_ret_sts_error;
1418 RETURN;
1419 END IF;
1420 END IF;
1421
1422 ----------------------- inbound_flag ------------------------
1423 IF p_chan_rec.inbound_flag <> FND_API.g_miss_char
1424 AND p_chan_rec.inbound_flag IS NOT NULL
1425 THEN
1426 IF AMS_Utility_PVT.is_Y_or_N(p_chan_rec.inbound_flag) = FND_API.g_false
1427 THEN
1428 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1429 THEN
1430 FND_MESSAGE.set_name('AMS', 'AMS_CHAN_WRONG_INBOUND_FLAG');
1431 FND_MSG_PUB.add;
1432 END IF;
1433 x_return_status := FND_API.g_ret_sts_error;
1434 RETURN;
1435 END IF;
1436 END IF;
1437
1438 END check_chan_flag_items;
1439 */
1440 --end comment rrajesh 12/07/00
1441
1442
1443
1444
1445
1446 ---------------------------------------------------------------------
1447 -- PROCEDURE
1448 -- check_chan_items
1449 --
1450 -- HISTORY
1451 -- 11/23/99 mpande Created.
1452 ---------------------------------------------------------------------
1453 PROCEDURE check_chan_items(
1454 p_chan_rec IN chan_rec_type,
1455 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1456 x_return_status OUT NOCOPY VARCHAR2
1457 )
1458 IS
1459 BEGIN
1460
1461 check_chan_req_items(
1462 p_chan_rec => p_chan_rec,
1463 x_return_status => x_return_status
1464 );
1465
1466 IF x_return_status <> FND_API.g_ret_sts_success THEN
1467 RETURN;
1468 END IF;
1469
1470 check_chan_uk_items(
1471 p_chan_rec => p_chan_rec,
1472 p_validation_mode => p_validation_mode,
1473 x_return_status => x_return_status
1474 );
1475
1476 IF x_return_status <> FND_API.g_ret_sts_success THEN
1477 RETURN;
1478 END IF;
1479
1480 check_chan_fk_items(
1481 p_chan_rec => p_chan_rec,
1482 x_return_status => x_return_status
1483 );
1484
1485 IF x_return_status <> FND_API.g_ret_sts_success THEN
1486 RETURN;
1487 END IF;
1488
1489 check_chan_lookup_items(
1490 p_chan_rec => p_chan_rec,
1491 x_return_status => x_return_status
1492 );
1493
1494 IF x_return_status <> FND_API.g_ret_sts_success THEN
1495 RETURN;
1496 END IF;
1497
1498 --commented OUT NOCOPY by rrajesh on 12/07/00
1499 --check_chan_flag_items(
1500 -- p_chan_rec => p_chan_rec,
1501 -- x_return_status => x_return_status
1502 --);
1503 -- end 12/07/00
1504
1505 IF x_return_status <> FND_API.g_ret_sts_success THEN
1506 RETURN;
1507 END IF;
1508
1509 END check_chan_items;
1510
1511
1512 -- Start of Comments
1513 --
1514 -- NAME
1515 -- Validate_Chan_Record
1516 --
1517 -- PURPOSE
1518 -- This procedure is to validate the start and end date of the channel
1519 --
1520 -- NOTES
1521 --
1522 --
1523 -- HISTORY
1524 -- 07/13/2000 ptendulk created
1525 -- End of Comments
1526 PROCEDURE Check_Chan_Record(
1527 p_chan_rec IN chan_rec_type,
1528 p_complete_rec IN chan_rec_type,
1529 x_return_status OUT NOCOPY VARCHAR2
1530 )
1531 IS
1532
1533 l_start_date DATE;
1534 l_end_date DATE;
1535 BEGIN
1536 --
1537 -- Initialize the Out Variable
1538 --
1539 x_return_status := FND_API.g_ret_sts_success;
1540
1541 -- Check start date time
1542 IF (p_chan_rec.active_from_date IS NOT NULL AND
1543 p_chan_rec.active_from_date <> FND_API.G_MISS_DATE) OR
1544 (p_chan_rec.active_to_date IS NOT NULL AND
1545 p_chan_rec.active_to_date <> FND_API.G_MISS_DATE)
1546 THEN
1547 IF p_chan_rec.active_from_date = FND_API.G_MISS_DATE THEN
1548 l_start_date := p_complete_rec.active_from_date;
1549 ELSE
1550 l_start_date := p_chan_rec.active_from_date;
1551 END IF ;
1552
1553 IF p_chan_rec.active_to_date = FND_API.G_MISS_DATE THEN
1554 l_end_date := p_complete_rec.active_to_date;
1558
1555 ELSE
1556 l_end_date := p_chan_rec.active_to_date;
1557 END IF ;
1559
1560 IF l_end_date IS NOT NULL
1561 AND l_start_date IS NOT NULL THEN
1562 IF l_start_date > l_end_date THEN
1563 -- invalid item
1564 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1565 THEN -- MMSG
1566 -- DBMS_OUTPUT.Put_Line('Start Date time or End Date Time is invalid');
1567 FND_MESSAGE.Set_Name('AMS', 'AMS_MED_FROMDT_GTR_TODT');
1568 -- FND_MESSAGE.Set_Name('AMS', 'AMS_TRIG_INVALID_DT_RANGE');
1569 FND_MSG_PUB.Add;
1570 END IF;
1571 x_return_status := FND_API.G_RET_STS_ERROR;
1572 -- If any errors happen abort API/Procedure.
1573 RETURN;
1574 END IF;
1575
1576 END IF;
1577 END IF;
1578
1579 --Added by rrajesh on 12/07/00
1580 --Validation to be done against the fulfillment server and call center
1581 --end 12/07/00 rrajesh
1582 END Check_Chan_Record;
1583
1584
1585 ---------------------------------------------------------------------
1586 -- PROCEDURE
1587 -- init_chan_rec
1588 --
1589 -- HISTORY
1590 -- 11/23/99 mpande Created.
1591 -- 01/18/2001 rssharma Removed internal_resource attribute
1592 ---------------------------------------------------------------------
1593 PROCEDURE init_chan_rec(
1594 x_chan_rec OUT NOCOPY chan_rec_type
1595 )
1596 IS
1597 BEGIN
1598
1599 x_chan_rec.channel_id := FND_API.g_miss_num;
1600 x_chan_rec.last_update_date := FND_API.g_miss_date;
1601 x_chan_rec.last_updated_by := FND_API.g_miss_num;
1602 x_chan_rec.creation_date := FND_API.g_miss_date;
1603 x_chan_rec.created_by := FND_API.g_miss_num;
1604 x_chan_rec.last_update_login := FND_API.g_miss_num;
1605 x_chan_rec.object_version_number := FND_API.g_miss_num;
1606 x_chan_rec.managed_by_person_id := FND_API.g_miss_num;
1607 x_chan_rec.preferred_vendor_id := FND_API.g_miss_num;
1608 x_chan_rec.party_id := FND_API.g_miss_num;
1609 x_chan_rec.channel_type_code := FND_API.g_miss_char;
1610 x_chan_rec.active_from_date := FND_API.g_miss_date;
1611 x_chan_rec.active_to_date := FND_API.g_miss_date;
1612 x_chan_rec.order_sequence := FND_API.g_miss_num;
1613 x_chan_rec.outbound_flag := FND_API.g_miss_char;
1614 x_chan_rec.inbound_flag := FND_API.g_miss_char;
1615 x_chan_rec.rating := FND_API.g_miss_char;
1616 x_chan_rec.attribute_category := FND_API.g_miss_char;
1617 x_chan_rec.attribute1 := FND_API.g_miss_char;
1618 x_chan_rec.attribute2 := FND_API.g_miss_char;
1619 x_chan_rec.attribute3 := FND_API.g_miss_char;
1620 x_chan_rec.attribute4 := FND_API.g_miss_char;
1621 x_chan_rec.attribute5 := FND_API.g_miss_char;
1622 x_chan_rec.attribute6 := FND_API.g_miss_char;
1623 x_chan_rec.attribute7 := FND_API.g_miss_char;
1624 x_chan_rec.attribute8 := FND_API.g_miss_char;
1625 x_chan_rec.attribute9 := FND_API.g_miss_char;
1626 x_chan_rec.attribute10 := FND_API.g_miss_char;
1627 x_chan_rec.attribute11 := FND_API.g_miss_char;
1628 x_chan_rec.attribute12 := FND_API.g_miss_char;
1629 x_chan_rec.attribute13 := FND_API.g_miss_char;
1630 x_chan_rec.attribute14 := FND_API.g_miss_char;
1631 x_chan_rec.attribute15 := FND_API.g_miss_char;
1632 x_chan_rec.channel_name := FND_API.g_miss_char;
1633 x_chan_rec.description := FND_API.g_miss_char;
1634 --added by rrajesh on 12/07/00
1635 x_chan_rec.country_id := FND_API.g_miss_num;
1636 -- removed by Rahul Sharma on 01/18/2001
1637 --x_chan_rec.internal_resource := FND_API.g_miss_char;
1638 -- end 01/18/2001
1639 --end addition 12/07/00
1640 END init_chan_rec;
1641
1642
1643 ---------------------------------------------------------------------
1644 -- PROCEDURE
1645 -- complete_chan_rec
1646 --
1647 -- HISTORY
1648 -- 23/11/99 mpande Created.
1649 -- 01/18/2001 rssharma removed internal_resource attribute
1650 ---------------------------------------------------------------------
1651 PROCEDURE complete_chan_rec(
1652 p_chan_rec IN chan_rec_type,
1653 x_complete_rec OUT NOCOPY chan_rec_type
1654 )
1655 IS
1656
1657 CURSOR c_chan IS
1658 SELECT *
1659 FROM ams_channels_vl
1660 WHERE channel_id = p_chan_rec.channel_id;
1661
1662 l_chan_rec c_chan%ROWTYPE;
1663
1664 BEGIN
1665
1666 x_complete_rec := p_chan_rec;
1667
1668 OPEN c_chan;
1669 FETCH c_chan INTO l_chan_rec;
1670 IF c_chan%NOTFOUND THEN
1671 CLOSE c_chan;
1672 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1673 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1674 FND_MSG_PUB.add;
1675 END IF;
1676 RAISE FND_API.g_exc_error;
1677 END IF;
1678 CLOSE c_chan;
1679
1680 IF p_chan_rec.managed_by_person_id = FND_API.g_miss_num THEN
1681 x_complete_rec.managed_by_person_id := l_chan_rec.managed_by_person_id;
1682 END IF;
1683
1684 IF p_chan_rec.active_from_date = FND_API.g_miss_date
1685 THEN
1686 x_complete_rec.active_from_date := l_chan_rec.active_from_date;
1687 END IF;
1688
1689 IF p_chan_rec.active_to_date = FND_API.g_miss_date
1693
1690 THEN
1691 x_complete_rec.active_to_date := l_chan_rec.active_to_date;
1692 END IF;
1694 IF p_chan_rec.outbound_flag = FND_API.g_miss_char THEN
1695 x_complete_rec.outbound_flag := l_chan_rec.outbound_flag;
1696 END IF;
1697
1698 IF p_chan_rec.inbound_flag = FND_API.g_miss_char THEN
1699 x_complete_rec.inbound_flag := l_chan_rec.inbound_flag;
1700 END IF;
1701
1702 IF p_chan_rec.order_sequence = FND_API.g_miss_num THEN
1703 x_complete_rec.order_sequence := l_chan_rec.order_sequence;
1704 END IF;
1705
1706 IF p_chan_rec.channel_type_code = FND_API.g_miss_char THEN
1707 x_complete_rec.channel_type_code := l_chan_rec.channel_type_code;
1708 END IF;
1709
1710 IF p_chan_rec.rating = FND_API.g_miss_char THEN
1711 x_complete_rec.rating := l_chan_rec.rating;
1712 END IF;
1713
1714 IF p_chan_rec.preferred_vendor_id = FND_API.g_miss_num THEN
1715 x_complete_rec.preferred_vendor_id:= l_chan_rec.preferred_vendor_id;
1716 END IF;
1717
1718 IF p_chan_rec.party_id = FND_API.g_miss_num THEN
1719 x_complete_rec.party_id:= l_chan_rec.party_id;
1720 END IF;
1721
1722
1723 IF p_chan_rec.attribute_category = FND_API.g_miss_char THEN
1724 x_complete_rec.attribute_category := l_chan_rec.attribute_category;
1725 END IF;
1726
1727 IF p_chan_rec.attribute1 = FND_API.g_miss_char THEN
1728 x_complete_rec.attribute1 := l_chan_rec.attribute1;
1729 END IF;
1730
1731 IF p_chan_rec.attribute2 = FND_API.g_miss_char THEN
1732 x_complete_rec.attribute2 := l_chan_rec.attribute2;
1733 END IF;
1734
1735 IF p_chan_rec.attribute3 = FND_API.g_miss_char THEN
1736 x_complete_rec.attribute3 := l_chan_rec.attribute3;
1737 END IF;
1738
1739 IF p_chan_rec.attribute4 = FND_API.g_miss_char THEN
1740 x_complete_rec.attribute4 := l_chan_rec.attribute4;
1741 END IF;
1742
1743 IF p_chan_rec.attribute5 = FND_API.g_miss_char THEN
1744 x_complete_rec.attribute5 := l_chan_rec.attribute5;
1745 END IF;
1746
1747 IF p_chan_rec.attribute6 = FND_API.g_miss_char THEN
1748 x_complete_rec.attribute6 := l_chan_rec.attribute6;
1749 END IF;
1750
1751 IF p_chan_rec.attribute7 = FND_API.g_miss_char THEN
1752 x_complete_rec.attribute7 := l_chan_rec.attribute7;
1753 END IF;
1754
1755 IF p_chan_rec.attribute8 = FND_API.g_miss_char THEN
1756 x_complete_rec.attribute8 := l_chan_rec.attribute8;
1757 END IF;
1758
1759 IF p_chan_rec.attribute9 = FND_API.g_miss_char THEN
1760 x_complete_rec.attribute9 := l_chan_rec.attribute9;
1761 END IF;
1762
1763 IF p_chan_rec.attribute10 = FND_API.g_miss_char THEN
1764 x_complete_rec.attribute10 := l_chan_rec.attribute10;
1765 END IF;
1766
1767 IF p_chan_rec.attribute11 = FND_API.g_miss_char THEN
1768 x_complete_rec.attribute11 := l_chan_rec.attribute11;
1769 END IF;
1770
1771 IF p_chan_rec.attribute12 = FND_API.g_miss_char THEN
1772 x_complete_rec.attribute12 := l_chan_rec.attribute12;
1773 END IF;
1774
1775 IF p_chan_rec.attribute13 = FND_API.g_miss_char THEN
1776 x_complete_rec.attribute13 := l_chan_rec.attribute13;
1777 END IF;
1778
1779 IF p_chan_rec.attribute14 = FND_API.g_miss_char THEN
1780 x_complete_rec.attribute14 := l_chan_rec.attribute14;
1781 END IF;
1782
1783 IF p_chan_rec.attribute15 = FND_API.g_miss_char THEN
1784 x_complete_rec.attribute15 := l_chan_rec.attribute15;
1785 END IF;
1786
1787 IF p_chan_rec.channel_name = FND_API.g_miss_char THEN
1788 x_complete_rec.channel_name := l_chan_rec.channel_name;
1789 END IF;
1790
1791 IF p_chan_rec.description = FND_API.g_miss_char THEN
1792 x_complete_rec.description := l_chan_rec.description;
1793 END IF;
1794
1795 --Added by rrajesh on 12/07/00
1796 IF p_chan_rec.country_id = FND_API.g_miss_num THEN
1797 x_complete_rec.country_id := l_chan_rec.country_id;
1798 END IF;
1799
1800 -- removed by Rahul Sharma on 01/18/2001
1801 --IF p_chan_rec.internal_resource = FND_API.g_miss_char THEN
1802 --x_complete_rec.internal_resource := l_chan_rec.internal_resource;
1803 --END IF;
1804 --end 01/18/2001
1805 --end 12/07/00
1806 END complete_chan_rec;
1807
1808 --
1809 -- Code added by abhola to this API ( code written by GJOBY )
1810 --
1811
1812 ---------------------------------------------------------------------
1813 -- FUNCTION
1814 -- get_party_name
1815 -- USAGE
1816 -- Example:
1817 -- SELECT AMS_CHANNEL_PVT.get_party_name (party_id)
1818 -- FROM AMS_CHANNELS_VL
1819 -- HISTORY
1820 -- 25-MAY-2000 gjoby Created.
1821 ---------------------------------------------------------------------
1822 FUNCTION get_party_name (
1823 p_party_id IN NUMBER
1824 )
1825 RETURN VARCHAR2
1826 IS
1827 l_party_name VARCHAR2(255);
1828
1829 CURSOR c_party_name IS
1830 SELECT party_name
1831 FROM hz_parties
1832 WHERE party_id = p_party_id;
1833 BEGIN
1834 IF p_party_id IS NULL THEN
1835 RETURN NULL;
1836 END IF;
1840 CLOSE c_party_name;
1837
1838 OPEN c_party_name;
1839 FETCH c_party_name INTO l_party_name;
1841
1842 RETURN l_party_name;
1843 END get_party_name;
1844
1845
1846
1847 ---------------------------------------------------------------------
1848 -- FUNCTION
1849 -- get_party_number
1850 -- USAGE
1851 -- Example:
1852 -- SELECT AMS_CHANNEL_PVT.get_party_number(party_id)
1853 -- FROM AMS_CHANNELS_VL
1854 -- HISTORY
1855 -- 25-MAY-2000 gjoby Created.
1856 ---------------------------------------------------------------------
1857 FUNCTION get_party_number (
1858 p_party_id IN NUMBER
1859 )
1860 RETURN VARCHAR2
1861 IS
1862 l_party_number VARCHAR2(30);
1863
1864 CURSOR c_party_number IS
1865 SELECT party_number
1866 FROM hz_parties
1867 WHERE party_id = p_party_id;
1868 BEGIN
1869 IF p_party_id IS NULL THEN
1870 RETURN NULL;
1871 END IF;
1872
1873 OPEN c_party_number;
1874 FETCH c_party_number INTO l_party_number;
1875 CLOSE c_party_number;
1876
1877 RETURN l_party_number;
1878 END get_party_number;
1879
1880
1881 ---------------------------------------------------------------------
1882 -- FUNCTION
1883 -- get_party_type
1884 -- USAGE
1885 -- Example:
1886 -- SELECT AMS_CHANNEL_PVT.get_party_type(party_id)
1887 -- FROM AMS_CHANNELS_VL
1888 -- HISTORY
1889 -- 25-MAY-2000 gjoby Created.
1890 ---------------------------------------------------------------------
1891 FUNCTION get_party_type (
1892 p_party_id IN NUMBER
1893 )
1894 RETURN VARCHAR2
1895 IS
1896 l_party_type VARCHAR2(30);
1897
1898 CURSOR c_party_type IS
1899 SELECT party_type
1900 FROM hz_parties
1901 WHERE party_id = p_party_id;
1902 BEGIN
1903 IF p_party_id IS NULL THEN
1904 RETURN NULL;
1905 END IF;
1906
1907 OPEN c_party_type;
1908 FETCH c_party_type INTO l_party_type;
1909 CLOSE c_party_type;
1910
1911 RETURN l_party_type;
1912 END get_party_type;
1913
1914 ---------------------------------------------------------------------
1915 -- FUNCTION
1916 -- get_vendor_name
1917 -- USAGE
1918 -- Example:
1919 -- SELECT AMS_CHANNEL_PVT.get_vendor_name (PREFERRED_vendor_id)
1920 -- FROM AMS_CHANNELS_VL
1921 -- HISTORY
1922 -- 25-MAY-2000 gjoby Created.
1923 ---------------------------------------------------------------------
1924 FUNCTION get_vendor_name (
1925 p_vendor_id IN NUMBER
1926 )
1927 RETURN VARCHAR2
1928 IS
1929 l_vendor_name VARCHAR2(300);
1930
1931 CURSOR c_vendor_name IS
1932 SELECT vendor_name
1933 FROM po_vendors
1934 WHERE vendor_id = p_vendor_id;
1935 BEGIN
1936 IF p_vendor_id IS NULL THEN
1937 RETURN NULL;
1938 END IF;
1939
1940 OPEN c_vendor_name;
1941 FETCH c_vendor_name INTO l_vendor_name;
1942 CLOSE c_vendor_name;
1943
1944 RETURN l_vendor_name;
1945 END get_vendor_name;
1946
1947 ---------------------------------------------------------------------
1948 -- FUNCTION
1949 -- get_country_name
1950 -- USAGE
1951 -- Example:
1952 -- SELECT AMS_CHANNEL_PVT.get_country_name (country_id)
1953 -- FROM jtf_loc_areas_vl
1954 -- HISTORY
1955 -- 13-DEC-2000 rrajesh Created.
1956 ---------------------------------------------------------------------
1957 FUNCTION get_country_name (
1958 p_country_id IN NUMBER
1959 )
1960 RETURN VARCHAR2
1961 IS
1962 l_country_name VARCHAR2(80);
1963
1964 CURSOR c_country_name IS
1965 SELECT location_area_name
1966 FROM jtf_loc_areas_vl
1967 WHERE location_area_id = p_country_id
1968 AND location_type_code = 'COUNTRY';
1969
1970 BEGIN
1971 IF (AMS_DEBUG_HIGH_ON) THEN
1972
1973 AMS_Utility_PVT.debug_message('arg in get_country_name() :' || p_country_id);
1974 END IF;
1975
1976 IF p_country_id IS NULL THEN
1977 RETURN NULL;
1978 END IF;
1979
1980 OPEN c_country_name;
1981 FETCH c_country_name INTO l_country_name;
1982 CLOSE c_country_name;
1983
1984 RETURN l_country_name;
1985 END get_country_name;
1986
1987
1988 END AMS_CHANNEL_PVT;