[Home] [Help]
PACKAGE BODY: APPS.AMS_MEDIA_PVT
Source
1 PACKAGE BODY AMS_Media_PVT AS
2 /* $Header: amsvmedb.pls 115.35 2004/05/14 10:50:29 vmodur ship $ */
3
4 ------------------------------------------------------------------------------------------
5 -- PACKAGE
6 -- AMS_Media_PVT
7 --
8 -- PROCEDURES
9 -- AMS_MEDIA_VL:
10 -- Check_Media_Req_Items
11 -- Check_Media_UK_Items
12 -- Check_Media_FK_Items
13 -- Check_Media_Lookup_Items
14 -- Check_Media_Flag_Items
15 --
16 -- AMS_MEDIA_CHANNELS:
17 -- Check_MediaChannel_Req_Items
18 -- Check_MediaChannel_UK_Items
19 -- Check_MediaChannel_FK_Items
20 -- Check_MediaChl_Lookup_Items
21 -- Check_MediaChannel_Flag_Items
22 -- Check_MediaChannel_InterEntity
23 --
24 -- NOTES
25 --
26 --
27 -- HISTORY
28 -- 03-Nov-1999 choang Created.
29 -- 19-Nov-1999 choang Added Inter-Entity validation.
30 -- 10-Dec-1999 ptendulk Modified Create Media API as
31 -- Media with media_type_code = EVENTS can not be created
32 -- 15-dec-1999 ptendulk Modified Create Media Channel API
33 -- 31-Dec-1999 ptendulk Modified (Check Object Version Number before
34 -- updating /deleting)
35 -- 07-Nov-2000 rrajesh Modified the uniqueness checking of media_name, by replacing
36 -- the AMS_Utility_PVT call with c_name_unique_cr,
37 -- c_name_unique_up cursors.
38 -- 06-Dec-2000 julou Commented out procedure check_media_flag_items
39 -- 07-Nov-2000 rrajesh Bug fix. 2005131. Moved dependancy checking of media from
40 -- campaign level to schedule level.
41 -- 31-OCT-2001 rrajesh Bug fix:2089112
42 -- 31-Dec-2002 dbiswas updated cursor c_check_schedule_association
43 -- 12-Feb-2003 vmodur Bug 2766207 Fix in Update_MediaChannel
44 -- 14-May-2004 vmodur SQL Perf Fixes
45 ------------------------------------------------------------------------------------------
46
47 --
48 -- Global CONSTANTS
49 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Media_PVT';
50
51 -- Check_Media_Req_Items
52 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
53 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
54 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
55
56 PROCEDURE Check_Media_Req_Items (
57 p_media_rec IN Media_Rec_Type,
58 x_return_status OUT NOCOPY VARCHAR2
59 );
60
61 -- Check_Media_UK_Items
62 PROCEDURE Check_Media_UK_Items (
63 p_media_rec IN Media_Rec_Type,
64 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
65 x_return_status OUT NOCOPY VARCHAR2
66 );
67
68 -- Check_Media_FK_Items
69 PROCEDURE Check_Media_FK_Items (
70 p_media_rec IN Media_Rec_Type,
71 x_return_status OUT NOCOPY VARCHAR2
72 );
73
74 -- Check_Media_Lookup_Items
75 PROCEDURE Check_Media_Lookup_Items (
76 p_media_rec IN Media_Rec_Type,
77 x_return_status OUT NOCOPY VARCHAR2
78 );
79
80 -- Check_Media_Flag_Items
81 /*
82 PROCEDURE Check_Media_Flag_Items (
83 p_media_rec IN Media_Rec_Type,
84 x_return_status OUT NOCOPY VARCHAR2
85 );
86 */
87
88 -- Check_MediaChannel_Req_Items
89 PROCEDURE Check_MediaChannel_Req_Items (
90 p_mediachl_rec IN MediaChannel_Rec_Type,
91 x_return_status OUT NOCOPY VARCHAR2
92 );
93
94 -- Check_MediaChannel_UK_Items
95 PROCEDURE Check_MediaChannel_UK_Items (
96 p_mediachl_rec IN MediaChannel_Rec_Type,
97 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
98 x_return_status OUT NOCOPY VARCHAR2
99 );
100
101 -- Check_MediaChannel_FK_Items
102 PROCEDURE Check_MediaChannel_FK_Items (
103 p_mediachl_rec IN MediaChannel_Rec_Type,
104 x_return_status OUT NOCOPY VARCHAR2
105 );
106
107 -- Check_MediaChl_Lookup_Items
108 PROCEDURE Check_MediaChl_Lookup_Items (
109 p_mediachl_rec IN MediaChannel_Rec_Type,
110 x_return_status OUT NOCOPY VARCHAR2
111 );
112
113 -- Check_MediaChannel_Flag_Items
114 PROCEDURE Check_MediaChannel_Flag_Items (
115 p_mediachl_rec IN MediaChannel_Rec_Type,
116 x_return_status OUT NOCOPY VARCHAR2
117 );
118
119 --
120 -- PROCEDURE
121 -- Check_MediaChannel_InterEntity
122 PROCEDURE Check_MediaChannel_InterEntity (
123 p_mediachl_rec IN MediaChannel_Rec_Type,
124 p_complete_rec IN MediaChannel_Rec_Type := NULL,
125 x_return_status OUT NOCOPY VARCHAR2
126 );
127
128 -------------------------------------
129 ----- MEDIA -----
130 -------------------------------------
131
132 --
133 -- NAME
134 -- IsSeeded
135 --
136 -- PURPOSE
137 -- Returns whether the given ID is that of a seeded record.
138 --
139 -- NOTES
140 -- As of creation of the function, a seeded record has an ID
141 -- less than 10,000.
142 --
143 -- HISTORY
144 -- 01/05/2000 ptendulk Created.
145 --
146 FUNCTION IsSeeded (
147 p_id IN NUMBER
148 )
149 RETURN BOOLEAN
150 IS
151 BEGIN
152 IF p_id < 10000 THEN
153 RETURN TRUE;
154 END IF;
155
156 RETURN FALSE;
157 END IsSeeded;
158
159
160 --------------------------------------------------------------------
161 -- PROCEDURE
162 -- Create_Media
163 --
164 --------------------------------------------------------------------
165 PROCEDURE Create_Media (
166 p_api_version IN NUMBER,
167 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
168 p_commit IN VARCHAR2 := FND_API.g_false,
169 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
170
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174
175 p_media_rec IN Media_Rec_Type,
176 x_media_id OUT NOCOPY NUMBER
177 )
178 IS
179 L_API_VERSION CONSTANT NUMBER := 1.0;
180 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Media';
181 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
182
183 l_media_rec Media_Rec_Type := p_media_rec;
184 l_dummy NUMBER;
185 l_return_status VARCHAR2(1);
186
187 CURSOR c_seq IS
188 SELECT ams_media_b_s.NEXTVAL
189 FROM dual;
190
191 CURSOR c_id_exists (x_id IN NUMBER) IS
192 SELECT 1
193 FROM dual
194 WHERE EXISTS (SELECT 1
195 FROM ams_media_b
196 WHERE media_id = x_id);
197
198 BEGIN
199 --------------------- initialize -----------------------
200 SAVEPOINT Create_Media;
201
202 IF (AMS_DEBUG_HIGH_ON) THEN
203
204
205
206 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
207
208 END IF;
209
210 IF FND_API.to_boolean (p_init_msg_list) THEN
211 FND_MSG_PUB.initialize;
212 END IF;
213
214 IF NOT FND_API.compatible_api_call (
215 L_API_VERSION,
216 p_api_version,
217 L_API_NAME,
218 G_PKG_NAME
219 ) THEN
220 RAISE FND_API.g_exc_unexpected_error;
221 END IF;
222
223 x_return_status := FND_API.g_ret_sts_success;
224
225 ----------------------- validate -----------------------
226 IF (AMS_DEBUG_HIGH_ON) THEN
227
228 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
229 END IF;
230
231 -- Start of Comments by ptendulk
232 -- Following lines are commented on 10-Dec-1999 as
233 -- Media with Media_type_code = 'EVENTS' can not not be created and
234 -- can be created with media_type_code = 'DIRECT_MARKETING'
235 -- End of Comments by ptendulk
236
237 -- IF p_media_rec.media_type_code = 'DIRECT_MARKETING' THEN
238 -- IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
239 -- FND_MESSAGE.set_name ('AMS', 'AMS_MED_CANT_CREATE_DM_MEDIA');
240 -- FND_MSG_PUB.add;
241 -- END IF;
242 -- RAISE FND_API.g_exc_error;
243 -- END IF;
244
245
246 IF p_media_rec.media_type_code = 'EVENTS' THEN
247 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
248 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CANT_CREATE_EVE_MEDIA');
249 FND_MSG_PUB.add;
250 END IF;
251 RAISE FND_API.g_exc_error;
252 END IF;
253
254 IF p_media_rec.media_type_code = 'INTERNET' THEN
255 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
256 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CANT_CR_INTERNET_MEDIA');
257 FND_MSG_PUB.add;
258 END IF;
259 RAISE FND_API.g_exc_error;
260 END IF;
261
262 Validate_Media (
263 p_api_version => l_api_version,
264 p_init_msg_list => p_init_msg_list,
265 p_validation_level => p_validation_level,
266 x_return_status => l_return_status,
267 x_msg_count => x_msg_count,
268 x_msg_data => x_msg_data,
269 p_media_rec => l_media_rec
270 );
271
272 IF l_return_status = FND_API.g_ret_sts_error THEN
273 RAISE FND_API.g_exc_error;
274 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
275 RAISE FND_API.g_exc_unexpected_error;
276 END IF;
277
278 --
279 -- Check for the ID.
280 --
281 IF l_media_rec.media_id IS NULL THEN
282 LOOP
283 --
284 -- If the ID is not passed into the API, then
285 -- grab a value from the sequence.
286 OPEN c_seq;
287 FETCH c_seq INTO l_media_rec.media_id;
288 CLOSE c_seq;
289
290 --
291 -- Check to be sure that the sequence does not exist.
292 OPEN c_id_exists (l_media_rec.media_id);
293 FETCH c_id_exists INTO l_dummy;
294 CLOSE c_id_exists;
295
296 --
297 -- If the value for the ID already exists, then
298 -- l_dummy would be populated with '1', otherwise,
299 -- it receives NULL.
300 EXIT WHEN l_dummy IS NULL;
301 END LOOP;
302 END IF;
303
304 -------------------------- insert --------------------------
305 IF (AMS_DEBUG_HIGH_ON) THEN
306
307 AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
308 END IF;
309
310 --
311 -- Insert into mutli-language supported table.
312 --
313 INSERT INTO ams_media_b (
314 media_id,
315
316 -- standard who columns
317 last_update_date,
318 last_updated_by,
319 creation_date,
320 created_by,
321 last_update_login,
322
323 object_version_number,
324 media_type_code,
325 inbound_flag,
326 enabled_flag,
327
328 attribute_category,
329 attribute1,
330 attribute2,
331 attribute3,
332 attribute4,
333 attribute5,
334 attribute6,
335 attribute7,
336 attribute8,
337 attribute9,
338 attribute10,
339 attribute11,
340 attribute12,
341 attribute13,
342 attribute14,
343 attribute15
344 )
345 VALUES (
346 l_media_rec.media_id,
347
348 -- standard who columns
349 SYSDATE,
350 FND_GLOBAL.User_Id,
351 SYSDATE,
352 FND_GLOBAL.User_Id,
353 FND_GLOBAL.Conc_Login_Id,
354
355 1, -- object_version_number
356 l_media_rec.media_type_code,
357 NVL (l_media_rec.inbound_flag, 'Y'), -- Default is 'Y'. changed from 'N' to 'Y' by julou, 12/06/2000
358 NVL (l_media_rec.enabled_flag, 'Y'), -- Default is 'Y'
359
360 l_media_rec.attribute_category,
361 l_media_rec.attribute1,
362 l_media_rec.attribute2,
363 l_media_rec.attribute3,
364 l_media_rec.attribute4,
365 l_media_rec.attribute5,
366 l_media_rec.attribute6,
367 l_media_rec.attribute7,
368 l_media_rec.attribute8,
369 l_media_rec.attribute9,
370 l_media_rec.attribute10,
371 l_media_rec.attribute11,
372 l_media_rec.attribute12,
373 l_media_rec.attribute13,
374 l_media_rec.attribute14,
375 l_media_rec.attribute15
376 );
377
378 INSERT INTO ams_media_tl (
379 media_id,
380 language,
381 last_update_date,
382 last_updated_by,
383 creation_date,
384 created_by,
385 last_update_login,
386 source_lang,
387 media_name,
388 description
389 )
390 SELECT l_media_rec.media_id,
391 l.language_code,
392 -- standard who columns
393 SYSDATE,
394 FND_GLOBAL.User_Id,
395 SYSDATE,
396 FND_GLOBAL.User_Id,
397 FND_GLOBAL.Conc_Login_Id,
398 USERENV('LANG'),
399 l_media_rec.media_name,
400 l_media_rec.description
401 FROM fnd_languages l
402 WHERE l.installed_flag IN ('I', 'B')
403 AND NOT EXISTS (SELECT NULL
404 FROM ams_media_tl t
405 WHERE t.media_id = l_media_rec.media_id
406 AND t.language = l.language_code);
407
408 ------------------------- finish -------------------------------
409 -- set OUT value
410 x_media_id := l_media_rec.media_id;
411
412 --
413 -- END of API body.
414 --
415
416 -- Standard check of p_commit.
417 IF FND_API.To_Boolean ( p_commit ) THEN
418 COMMIT WORK;
419 END IF;
420
421 FND_MSG_PUB.count_and_get(
422 p_encoded => FND_API.g_false,
423 p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426
427 IF (AMS_DEBUG_HIGH_ON) THEN
428
429
430
431 AMS_Utility_PVT.debug_message (l_full_name || ': End');
432
433 END IF;
434
435 EXCEPTION
436 WHEN FND_API.g_exc_error THEN
437 ROLLBACK TO Create_Media;
438 x_return_status := FND_API.g_ret_sts_error;
439 FND_MSG_PUB.count_and_get(
440 p_encoded => FND_API.g_false,
441 p_count => x_msg_count,
442 p_data => x_msg_data
443 );
444 WHEN FND_API.g_exc_unexpected_error THEN
445 ROLLBACK TO Create_Media;
446 x_return_status := FND_API.g_ret_sts_unexp_error ;
447 FND_MSG_PUB.count_and_get (
448 p_encoded => FND_API.g_false,
449 p_count => x_msg_count,
450 p_data => x_msg_data
451 );
452 WHEN OTHERS THEN
453 ROLLBACK TO Create_Media;
454 x_return_status := FND_API.g_ret_sts_unexp_error ;
455
456 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
457 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
458 END IF;
459
460 FND_MSG_PUB.count_and_get (
461 p_encoded => FND_API.g_false,
462 p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465 END Create_Media;
466
467
468 --------------------------------------------------------------------
469 -- PROCEDURE
470 -- Update_Media
471 --
472 --------------------------------------------------------------------
473 PROCEDURE Update_Media (
474 p_api_version IN NUMBER,
475 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
476 p_commit IN VARCHAR2 := FND_API.g_false,
477 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
478
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2,
482
483 p_media_rec IN Media_Rec_Type
484 )
485 IS
486 L_API_VERSION CONSTANT NUMBER := 1.0;
487 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Media';
488 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
489
490 l_media_rec Media_Rec_Type := p_media_rec;
491 l_dummy NUMBER;
492 l_return_status VARCHAR2(1);
493
494 BEGIN
495 --------------------- initialize -----------------------
496 SAVEPOINT Update_Media;
497
498 IF (AMS_DEBUG_HIGH_ON) THEN
499
500
501
502 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
503
504 END IF;
505
506 IF FND_API.to_boolean (p_init_msg_list) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 IF NOT FND_API.compatible_api_call(
511 l_api_version,
512 p_api_version,
513 l_api_name,
514 g_pkg_name
515 ) THEN
516 RAISE FND_API.g_exc_unexpected_error;
517 END IF;
518
519 x_return_status := FND_API.g_ret_sts_success;
520
521 ----------------------- validate ----------------------
522 IF (AMS_DEBUG_HIGH_ON) THEN
523
524 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
525 END IF;
526
527 --
528 -- Check if record is seeded.
529 --
530 -- Seed Data can be disabled
531 /***
532 IF IsSeeded (p_media_rec.media_id) THEN
533 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
534 THEN
535 FND_MESSAGE.set_name('AMS', 'AMS_API_SEED_DATA');
536 FND_MSG_PUB.add;
537 END IF;
538
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541 ***/
542
543 -- replace g_miss_char/num/date with current column values
544 Complete_Media_Rec (p_media_rec, l_media_rec);
545
546 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
547 Check_Media_Items (
548 p_media_rec => p_media_rec,
549 p_validation_mode => JTF_PLSQL_API.g_update,
550 x_return_status => l_return_status
551 );
552
553 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
554 RAISE FND_API.g_exc_unexpected_error;
555 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
556 RAISE FND_API.g_exc_error;
557 END IF;
558 END IF;
559
560 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
561 Check_Media_Record (
562 p_media_rec => p_media_rec,
563 p_complete_rec => l_media_rec,
564 x_return_status => l_return_status
565 );
566
567 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
568 RAISE FND_API.g_exc_unexpected_error;
569 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
570 RAISE FND_API.g_exc_error;
571 END IF;
572 END IF;
573
574 -------------------------- update --------------------
575 IF (AMS_DEBUG_HIGH_ON) THEN
576
577 AMS_Utility_PVT.debug_message (l_full_name || ': Update');
578 END IF;
579
580 -- Modified by ptendulk on 30Dec1999
581 -- Check Obj Version Number before Updating
582 UPDATE ams_media_b
583 SET
584 last_update_date = SYSDATE,
585 last_updated_by = FND_GLOBAL.User_Id,
586 last_update_login = FND_GLOBAL.Conc_Login_Id,
587
588 object_version_number = object_version_number + 1,
589 media_type_code = l_media_rec.media_type_code,
590 inbound_flag = NVL (l_media_rec.inbound_flag, 'Y'), -- changed default value to 'Y', julou 12/06/2000
591 enabled_flag = NVL (l_media_rec.enabled_flag, 'Y'),
592
593 attribute_category = l_media_rec.attribute_category,
594 attribute1 = l_media_rec.attribute1,
595 attribute2 = l_media_rec.attribute2,
596 attribute3 = l_media_rec.attribute3,
597 attribute4 = l_media_rec.attribute4,
598 attribute5 = l_media_rec.attribute5,
599 attribute6 = l_media_rec.attribute6,
600 attribute7 = l_media_rec.attribute7,
601 attribute8 = l_media_rec.attribute8,
602 attribute9 = l_media_rec.attribute9,
603 attribute10 = l_media_rec.attribute10,
604 attribute11 = l_media_rec.attribute11,
605 attribute12 = l_media_rec.attribute12,
606 attribute13 = l_media_rec.attribute13,
607 attribute14 = l_media_rec.attribute14,
608 attribute15 = l_media_rec.attribute15
609 WHERE media_id = l_media_rec.media_id
610 AND object_version_number = l_media_rec.object_version_number ;
611
612 IF (SQL%NOTFOUND) THEN
613 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
614 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
615 FND_MSG_PUB.add;
616 END IF;
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618 END IF;
619
620 UPDATE ams_media_tl
621 SET
622 last_update_date = SYSDATE,
623 last_updated_by = FND_GLOBAL.User_Id,
624 last_update_login = FND_GLOBAL.Conc_Login_Id,
625
626 source_lang = USERENV('LANG'),
627 media_name = l_media_rec.media_name,
628 description = l_media_rec.description
629 WHERE media_id = l_media_rec.media_id
630 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
631
632 IF (SQL%NOTFOUND) THEN
633 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
634 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
635 FND_MSG_PUB.add;
636 END IF;
637 RAISE FND_API.g_exc_error;
638 END IF;
639
640 -------------------- finish --------------------------
641 IF FND_API.to_boolean (p_commit) THEN
642 COMMIT;
643 END IF;
644
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 IF (AMS_DEBUG_HIGH_ON) THEN
652
653
654
655 AMS_Utility_PVT.debug_message (l_full_name || ': End');
656
657 END IF;
658
659 EXCEPTION
660 WHEN FND_API.g_exc_error THEN
661 ROLLBACK TO Update_Media;
662 x_return_status := FND_API.g_ret_sts_error;
663 FND_MSG_PUB.count_and_get (
664 p_encoded => FND_API.g_false,
665 p_count => x_msg_count,
666 p_data => x_msg_data
667 );
668 WHEN FND_API.g_exc_unexpected_error THEN
669 ROLLBACK TO Update_Media;
670 x_return_status := FND_API.g_ret_sts_unexp_error ;
671 FND_MSG_PUB.count_and_get (
672 p_encoded => FND_API.g_false,
673 p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676 WHEN OTHERS THEN
677 ROLLBACK TO Update_Media;
678 x_return_status := FND_API.g_ret_sts_unexp_error ;
679
680 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
681 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
682 END IF;
683
684 FND_MSG_PUB.count_and_get (
685 p_encoded => FND_API.g_false,
686 p_count => x_msg_count,
687 p_data => x_msg_data
688 );
689 END Update_Media;
690
691
692 --------------------------------------------------------------------
693 -- PROCEDURE
694 -- Delete_Media
695 --------------------------------------------------------------------
696 PROCEDURE Delete_Media (
697 p_api_version IN NUMBER,
698 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
699 p_commit IN VARCHAR2 := FND_API.g_false,
700 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
701
702 x_return_status OUT NOCOPY VARCHAR2,
703 x_msg_count OUT NOCOPY NUMBER,
704 x_msg_data OUT NOCOPY VARCHAR2,
705
706 p_media_id IN NUMBER,
707 p_object_version IN NUMBER
708 )
709 IS
710
711 l_api_version CONSTANT NUMBER := 1.0;
712 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Media';
713 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
714
715 -- added by abhola
716
717 -- Following code is modidifed by rrajesh on 09/20/01
718 -- bug fix. 2005131
719 -- media should be attached at the schedule level instead of campaigns
720
721 /*CURSOR c_check_campaign(l_media_id in NUMBER) IS
722 SELECT 'Y'
723 FROM ams_campaigns_all_b
724 WHERE media_id = l_media_id;*/
725 CURSOR c_check_campaign(l_media_id in NUMBER) IS
726 SELECT 'Y'
727 FROM ams_campaign_schedules_b
728 WHERE activity_id = l_media_id;
729 -- end bug fix. 2005131
730
731 CURSOR c_check_customsetup(l_media_id IN NUMBER) IS
732 SELECT 'Y'
733 FROM ams_custom_setups_b
734 WHERE media_id = l_media_id;
735 -- end abhola
736
737 l_is_campaign VARCHAR2(1);
738 l_is_setup VARCHAR2(1);
739
740 BEGIN
741 --------------------- initialize -----------------------
742 SAVEPOINT Delete_Media;
743
744 IF (AMS_DEBUG_HIGH_ON) THEN
745
746
747
748 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
749
750 END IF;
751
752 IF FND_API.to_boolean (p_init_msg_list) THEN
753 FND_MSG_PUB.initialize;
754 END IF;
755
756 IF NOT FND_API.compatible_api_call (
757 l_api_version,
758 p_api_version,
759 l_api_name,
760 g_pkg_name
761 ) THEN
762 RAISE FND_API.g_exc_unexpected_error;
763 END IF;
764
765 x_return_status := FND_API.g_ret_sts_success;
766
767 ------------------------ delete ------------------------
768 IF (AMS_DEBUG_HIGH_ON) THEN
769
770 AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
771 END IF;
772
773 --
774 -- Check if record is seeded.
775 IF IsSeeded (p_media_id) THEN
776 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
777 THEN
778 FND_MESSAGE.set_name('AMS', 'AMS_API_SEED_DATA');
779 FND_MSG_PUB.add;
780 END IF;
781
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784
785 -- added by ABHOLA
786
787 OPEN c_check_campaign(p_media_id);
788 OPEN c_check_customsetup(p_media_id);
789
790 FETCH c_check_campaign INTO l_is_campaign;
791 FETCH c_check_customsetup INTO l_is_setup;
792
793 CLOSE c_check_campaign;
794 CLOSE c_check_customsetup;
795
796 IF ( l_is_campaign = 'Y' ) OR ( l_is_setup = 'Y') THEN
797 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
798 THEN
799 FND_MESSAGE.set_name('AMS', 'AMS_MEDIA_IS_USED');
800 FND_MSG_PUB.add;
801 END IF;
802
803 RAISE FND_API.G_EXC_ERROR;
804
805 END IF;
806
807 -- end abhola
808
809 -- Delete TL data
810 DELETE FROM ams_media_tl
811 WHERE media_id = p_media_id ;
812
813
814 IF (SQL%NOTFOUND) THEN
815 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
816 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
817 FND_MSG_PUB.add;
818 END IF;
819 RAISE FND_API.g_exc_error;
820 END IF;
821
822 DELETE FROM ams_media_b
823 WHERE media_id = p_media_id
824 AND object_version_number = p_object_version;
825
826 -------------------- finish --------------------------
827 IF FND_API.to_boolean (p_commit) THEN
828 COMMIT;
829 END IF;
830
831 FND_MSG_PUB.count_and_get (
832 p_encoded => FND_API.g_false,
833 p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836
837 IF (AMS_DEBUG_HIGH_ON) THEN
838
839
840
841 AMS_Utility_PVT.debug_message (l_full_name || ': End');
842
843 END IF;
844
845 EXCEPTION
846 WHEN FND_API.g_exc_error THEN
847 ROLLBACK TO Delete_Media;
848 x_return_status := FND_API.g_ret_sts_error;
849 FND_MSG_PUB.count_and_get (
850 p_encoded => FND_API.g_false,
851 p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 WHEN FND_API.g_exc_unexpected_error THEN
855 ROLLBACK TO Delete_Media;
856 x_return_status := FND_API.g_ret_sts_unexp_error ;
857 FND_MSG_PUB.count_and_get (
858 p_encoded => FND_API.g_false,
859 p_count => x_msg_count,
860 p_data => x_msg_data
861 );
862 WHEN OTHERS THEN
863 ROLLBACK TO Delete_Media;
864 x_return_status := FND_API.g_ret_sts_unexp_error ;
865
866 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
867 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
868 END IF;
869
870 FND_MSG_PUB.count_and_get (
871 p_encoded => FND_API.g_false,
872 p_count => x_msg_count,
873 p_data => x_msg_data
874 );
875 END Delete_Media;
876
877
878 --------------------------------------------------------------------
879 -- PROCEDURE
880 -- Lock_Media
881 --
882 --------------------------------------------------------------------
883 PROCEDURE Lock_Media (
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
886 p_commit IN VARCHAR2 := FND_API.g_false,
887 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
888
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892
893 p_media_id IN NUMBER,
894 p_object_version IN NUMBER
895 )
896 IS
897 l_api_version CONSTANT NUMBER := 1.0;
898 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Media';
899 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
900
901 l_dummy NUMBER; -- Used by the lock cursor.
902
903 --
904 -- NOTE: Not necessary to distinguish between a record
905 -- which does not exist and one which has been updated
906 -- by another user. To get that distinction, remove
907 -- the object_version condition from the SQL statement
908 -- and perform comparison in the body and raise the
909 -- exception there.
910 CURSOR c_lock IS
911 SELECT object_version_number
912 FROM ams_media_b
913 WHERE media_id = p_media_id
914 AND object_version_number = p_object_version
915 FOR UPDATE NOWAIT;
916 BEGIN
917 --------------------- initialize -----------------------
918 IF (AMS_DEBUG_HIGH_ON) THEN
919
920 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
921 END IF;
922
923 IF FND_API.to_boolean (p_init_msg_list) THEN
924 FND_MSG_PUB.initialize;
925 END IF;
926
927 IF NOT FND_API.compatible_api_call (
928 l_api_version,
929 p_api_version,
930 l_api_name,
931 g_pkg_name
932 ) THEN
933 RAISE FND_API.g_exc_unexpected_error;
934 END IF;
935
936 x_return_status := FND_API.g_ret_sts_success;
937
938 ------------------------ lock -------------------------
939 IF (AMS_DEBUG_HIGH_ON) THEN
940
941 AMS_Utility_PVT.debug_message (l_full_name || ': Lock');
942 END IF;
943
944 OPEN c_lock;
945 FETCH c_lock INTO l_dummy;
946 IF (c_lock%NOTFOUND) THEN
947 CLOSE c_lock;
948 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
949 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
950 FND_MSG_PUB.add;
951 END IF;
952 RAISE FND_API.g_exc_error;
953 END IF;
954 CLOSE c_lock;
955
956 -------------------- finish --------------------------
957 FND_MSG_PUB.count_and_get (
958 p_encoded => FND_API.g_false,
959 p_count => x_msg_count,
960 p_data => x_msg_data
961 );
962
963 IF (AMS_DEBUG_HIGH_ON) THEN
964
965
966
967 AMS_Utility_PVT.debug_message (l_full_name || ': End');
968
969 END IF;
970
971 EXCEPTION
972 WHEN AMS_Utility_PVT.resource_locked THEN
973 x_return_status := FND_API.g_ret_sts_error;
974 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
975 FND_MESSAGE.set_name ('AMS', 'AMS_API_RESOURCE_LOCKED');
976 FND_MSG_PUB.add;
977 END IF;
978
979 FND_MSG_PUB.count_and_get (
980 p_encoded => FND_API.g_false,
981 p_count => x_msg_count,
982 p_data => x_msg_data
983 );
984 WHEN FND_API.g_exc_error THEN
985 x_return_status := FND_API.g_ret_sts_error;
986 FND_MSG_PUB.count_and_get (
987 p_encoded => FND_API.g_false,
988 p_count => x_msg_count,
989 p_data => x_msg_data
990 );
991 WHEN FND_API.g_exc_unexpected_error THEN
992 x_return_status := FND_API.g_ret_sts_unexp_error ;
993 FND_MSG_PUB.count_and_get (
994 p_encoded => FND_API.g_false,
995 p_count => x_msg_count,
996 p_data => x_msg_data
997 );
998 WHEN OTHERS THEN
999 x_return_status := FND_API.g_ret_sts_unexp_error ;
1000 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1001 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1002 END IF;
1003
1004 FND_MSG_PUB.count_and_get (
1005 p_encoded => FND_API.g_false,
1006 p_count => x_msg_count,
1007 p_data => x_msg_data
1008 );
1009 END Lock_Media;
1010
1011
1012 --------------------------------------------------------------------
1013 -- PROCEDURE
1014 -- Validate_Media
1015 --
1016 --------------------------------------------------------------------
1017 PROCEDURE Validate_Media (
1018 p_api_version IN NUMBER,
1019 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1020 p_commit IN VARCHAR2 := FND_API.g_false,
1021 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1022
1023 x_return_status OUT NOCOPY VARCHAR2,
1024 x_msg_count OUT NOCOPY NUMBER,
1025 x_msg_data OUT NOCOPY VARCHAR2,
1026
1027 p_media_rec IN Media_Rec_Type
1028 )
1029 IS
1030 L_API_VERSION CONSTANT NUMBER := 1.0;
1031 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Media';
1032 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1033
1034 l_return_status VARCHAR2(1);
1035 BEGIN
1036 --------------------- initialize -----------------------
1037 IF (AMS_DEBUG_HIGH_ON) THEN
1038
1039 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1040 END IF;
1041
1042 IF FND_API.to_boolean (p_init_msg_list) THEN
1043 FND_MSG_PUB.initialize;
1044 END IF;
1045
1046 IF NOT FND_API.compatible_api_call (
1047 l_api_version,
1048 p_api_version,
1049 l_api_name,
1050 g_pkg_name
1051 ) THEN
1052 RAISE FND_API.g_exc_unexpected_error;
1053 END IF;
1054
1055 x_return_status := FND_API.g_ret_sts_success;
1056
1057 ---------------------- validate ------------------------
1058 IF (AMS_DEBUG_HIGH_ON) THEN
1059
1060 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
1061 END IF;
1062
1063 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1064 Check_Media_Items (
1065 p_media_rec => p_media_rec,
1066 p_validation_mode => JTF_PLSQL_API.g_create,
1067 x_return_status => l_return_status
1068 );
1069
1070 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1071 RAISE FND_API.g_exc_unexpected_error;
1072 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1073 RAISE FND_API.g_exc_error;
1074 END IF;
1075 END IF;
1076
1077 IF (AMS_DEBUG_HIGH_ON) THEN
1078
1079
1080
1081 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
1082
1083 END IF;
1084
1085 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1086 Check_Media_Record (
1087 p_media_rec => p_media_rec,
1088 p_complete_rec => NULL,
1089 x_return_status => l_return_status
1090 );
1091
1092 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1093 RAISE FND_API.g_exc_unexpected_error;
1094 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1095 RAISE FND_API.g_exc_error;
1096 END IF;
1097 END IF;
1098
1099 -------------------- finish --------------------------
1100 FND_MSG_PUB.count_and_get (
1101 p_encoded => FND_API.g_false,
1102 p_count => x_msg_count,
1103 p_data => x_msg_data
1104 );
1105
1106 IF (AMS_DEBUG_HIGH_ON) THEN
1107
1108
1109
1110 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1111
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN FND_API.g_exc_error THEN
1116 x_return_status := FND_API.g_ret_sts_error;
1117 FND_MSG_PUB.count_and_get (
1118 p_encoded => FND_API.g_false,
1119 p_count => x_msg_count,
1120 p_data => x_msg_data
1121 );
1122 WHEN FND_API.g_exc_unexpected_error THEN
1123 x_return_status := FND_API.g_ret_sts_unexp_error ;
1124 FND_MSG_PUB.count_and_get (
1125 p_encoded => FND_API.g_false,
1126 p_count => x_msg_count,
1127 p_data => x_msg_data
1128 );
1129 WHEN OTHERS THEN
1130 x_return_status := FND_API.g_ret_sts_unexp_error;
1131 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1132 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1133 END IF;
1134
1135 FND_MSG_PUB.count_and_get (
1136 p_encoded => FND_API.g_false,
1137 p_count => x_msg_count,
1138 p_data => x_msg_data
1139 );
1140 END Validate_Media;
1141
1142 ---------------------------------------------------------------------
1143 -- PROCEDURE
1144 -- Check_Media_Items
1145 --
1146 ---------------------------------------------------------------------
1147 PROCEDURE Check_Media_Items (
1148 p_media_rec IN Media_Rec_Type,
1149 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1150 x_return_status OUT NOCOPY VARCHAR2
1151 )
1152 IS
1153 BEGIN
1154 --
1155 -- Validate required items.
1156 Check_Media_Req_Items (
1157 p_media_rec => p_media_rec,
1158 x_return_status => x_return_status
1159 );
1160
1161 IF x_return_status <> FND_API.g_ret_sts_success THEN
1162 RETURN;
1163 END IF;
1164
1165 --
1166 -- Validate uniqueness.
1167 Check_Media_UK_Items (
1168 p_media_rec => p_media_rec,
1169 p_validation_mode => p_validation_mode,
1170 x_return_status => x_return_status
1171 );
1172
1173 IF x_return_status <> FND_API.g_ret_sts_success THEN
1174 RETURN;
1175 END IF;
1176
1177 Check_Media_FK_Items(
1178 p_media_rec => p_media_rec,
1179 x_return_status => x_return_status
1180 );
1181
1182 IF x_return_status <> FND_API.g_ret_sts_success THEN
1183 RETURN;
1184 END IF;
1185
1186 Check_Media_Lookup_Items (
1187 p_media_rec => p_media_rec,
1188 x_return_status => x_return_status
1189 );
1190
1191 IF x_return_status <> FND_API.g_ret_sts_success THEN
1192 RETURN;
1193 END IF;
1194 /*
1195 Check_Media_Flag_Items(
1196 p_media_rec => p_media_rec,
1197 x_return_status => x_return_status
1198 );
1199
1200 IF x_return_status <> FND_API.g_ret_sts_success THEN
1201 RETURN;
1202 END IF;
1203 */
1204 END Check_Media_Items;
1205
1206 ---------------------------------------------------------------------
1207 -- PROCEDURE
1208 -- Check_Media_Record
1209 --
1210 -- PURPOSE
1211 -- Check the record level business rules.
1212 --
1213 -- PARAMETERS
1214 -- p_media_rec: the record to be validated; may contain attributes
1215 -- as FND_API.g_miss_char/num/date
1216 -- p_complete_rec: the complete record after all "g_miss" items
1217 -- have been replaced by current database values
1218 ---------------------------------------------------------------------
1219 PROCEDURE Check_Media_Record (
1220 p_media_rec IN Media_Rec_Type,
1221 p_complete_rec IN Media_Rec_Type := NULL,
1222 x_return_status OUT NOCOPY VARCHAR2
1223 )
1224 IS
1225 BEGIN
1226 --
1227 -- Currently, no business rule for record
1228 -- level validation.
1229 x_return_status := FND_API.g_ret_sts_success;
1230 END Check_Media_Record;
1231
1232 ---------------------------------------------------------------------
1233 -- PROCEDURE
1234 -- Init_Media_Rec
1235 --
1236 ---------------------------------------------------------------------
1237 PROCEDURE Init_Media_Rec (
1238 x_media_rec OUT NOCOPY Media_Rec_Type
1239 )
1240 IS
1241 BEGIN
1242 x_media_rec.media_id := FND_API.g_miss_num;
1243 x_media_rec.last_update_date := FND_API.g_miss_date;
1244 x_media_rec.last_updated_by := FND_API.g_miss_num;
1245 x_media_rec.creation_date := FND_API.g_miss_date;
1246 x_media_rec.created_by := FND_API.g_miss_num;
1247 x_media_rec.last_update_login := FND_API.g_miss_num;
1248 x_media_rec.object_version_number := FND_API.g_miss_num;
1249 x_media_rec.media_type_code := FND_API.g_miss_char;
1250 x_media_rec.inbound_flag := FND_API.g_miss_char;
1251 x_media_rec.enabled_flag := FND_API.g_miss_char;
1252 x_media_rec.attribute_category := FND_API.g_miss_char;
1253 x_media_rec.attribute1 := FND_API.g_miss_char;
1254 x_media_rec.attribute2 := FND_API.g_miss_char;
1255 x_media_rec.attribute3 := FND_API.g_miss_char;
1256 x_media_rec.attribute4 := FND_API.g_miss_char;
1257 x_media_rec.attribute5 := FND_API.g_miss_char;
1258 x_media_rec.attribute6 := FND_API.g_miss_char;
1259 x_media_rec.attribute7 := FND_API.g_miss_char;
1260 x_media_rec.attribute8 := FND_API.g_miss_char;
1261 x_media_rec.attribute9 := FND_API.g_miss_char;
1262 x_media_rec.attribute10 := FND_API.g_miss_char;
1263 x_media_rec.attribute11 := FND_API.g_miss_char;
1264 x_media_rec.attribute12 := FND_API.g_miss_char;
1265 x_media_rec.attribute13 := FND_API.g_miss_char;
1266 x_media_rec.attribute14 := FND_API.g_miss_char;
1267 x_media_rec.attribute15 := FND_API.g_miss_char;
1268 x_media_rec.media_name := FND_API.g_miss_char;
1269 x_media_rec.description := FND_API.g_miss_char;
1270 END Init_Media_Rec;
1271
1272
1273 ---------------------------------------------------------------------
1274 -- PROCEDURE
1275 -- Complete_Media_Rec
1276 --
1277 ---------------------------------------------------------------------
1278 PROCEDURE Complete_Media_Rec (
1279 p_media_rec IN Media_Rec_Type,
1280 x_complete_rec OUT NOCOPY Media_Rec_Type
1281 )
1282 IS
1283 CURSOR c_media IS
1284 SELECT *
1285 FROM ams_media_vl
1286 WHERE media_id = p_media_rec.media_id;
1287 --
1288 -- This is the only exception for using %ROWTYPE.
1289 -- We are selecting from the VL view, which may
1290 -- have some denormalized columns as compared to
1291 -- the base tables.
1292 l_media_rec c_media%ROWTYPE;
1293 BEGIN
1294 x_complete_rec := p_media_rec;
1295
1296 OPEN c_media;
1297 FETCH c_media INTO l_media_rec;
1298 IF c_media%NOTFOUND THEN
1299 CLOSE c_media;
1300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1301 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1302 FND_MSG_PUB.add;
1303 END IF;
1304 RAISE FND_API.g_exc_error;
1305 END IF;
1306 CLOSE c_media;
1307
1308 --
1309 -- MEDIA_TYPE_CODE
1310 IF p_media_rec.media_type_code = FND_API.g_miss_char THEN
1311 x_complete_rec.media_type_code := l_media_rec.media_type_code;
1312 END IF;
1313
1314 --
1315 -- INBOUND_FLAG
1316 IF p_media_rec.inbound_flag = FND_API.g_miss_char THEN
1317 x_complete_rec.inbound_flag := l_media_rec.inbound_flag;
1318 END IF;
1319
1320 --
1321 -- ENABLED_FLAG
1322 IF p_media_rec.enabled_flag = FND_API.g_miss_char THEN
1323 x_complete_rec.enabled_flag := l_media_rec.enabled_flag;
1324 END IF;
1325
1326 --
1327 -- ATTRIBUTE_CATEGORY
1328 IF p_media_rec.attribute_category = FND_API.g_miss_char THEN
1329 x_complete_rec.attribute_category := l_media_rec.attribute_category;
1330 END IF;
1331
1332 --
1333 -- ATTRIBUTE1
1334 IF p_media_rec.attribute1 = FND_API.g_miss_char THEN
1335 x_complete_rec.attribute1 := l_media_rec.attribute1;
1336 END IF;
1337
1338 --
1339 -- ATTRIBUTE2
1340 IF p_media_rec.attribute2 = FND_API.g_miss_char THEN
1341 x_complete_rec.attribute2 := l_media_rec.attribute2;
1342 END IF;
1343
1344 --
1345 -- ATTRIBUTE3
1346 IF p_media_rec.attribute3 = FND_API.g_miss_char THEN
1347 x_complete_rec.attribute3 := l_media_rec.attribute3;
1348 END IF;
1349
1350 --
1351 -- ATTRIBUTE4
1352 IF p_media_rec.attribute4 = FND_API.g_miss_char THEN
1353 x_complete_rec.attribute4 := l_media_rec.attribute4;
1354 END IF;
1355
1356 --
1357 -- ATTRIBUTE5
1358 IF p_media_rec.attribute5 = FND_API.g_miss_char THEN
1359 x_complete_rec.attribute5 := l_media_rec.attribute5;
1360 END IF;
1361
1362 --
1363 -- ATTRIBUTE6
1364 IF p_media_rec.attribute6 = FND_API.g_miss_char THEN
1365 x_complete_rec.attribute6 := l_media_rec.attribute6;
1366 END IF;
1367
1368 --
1369 -- ATTRIBUTE7
1370 IF p_media_rec.attribute7 = FND_API.g_miss_char THEN
1371 x_complete_rec.attribute7 := l_media_rec.attribute7;
1372 END IF;
1373
1374 --
1375 -- ATTRIBUTE8
1376 IF p_media_rec.attribute8 = FND_API.g_miss_char THEN
1377 x_complete_rec.attribute8 := l_media_rec.attribute8;
1378 END IF;
1379
1380 --
1381 -- ATTRIBUTE9
1382 IF p_media_rec.attribute9 = FND_API.g_miss_char THEN
1383 x_complete_rec.attribute9 := l_media_rec.attribute9;
1384 END IF;
1385
1386 --
1387 -- ATTRIBUTE10
1388 IF p_media_rec.attribute10 = FND_API.g_miss_char THEN
1389 x_complete_rec.attribute10 := l_media_rec.attribute10;
1390 END IF;
1391
1392 --
1393 -- ATTRIBUTE11
1394 IF p_media_rec.attribute11 = FND_API.g_miss_char THEN
1395 x_complete_rec.attribute11 := l_media_rec.attribute11;
1396 END IF;
1397
1398 --
1399 -- ATTRIBUTE12
1400 IF p_media_rec.attribute12 = FND_API.g_miss_char THEN
1401 x_complete_rec.attribute12 := l_media_rec.attribute12;
1402 END IF;
1403
1404 --
1405 -- ATTRIBUTE13
1406 IF p_media_rec.attribute13 = FND_API.g_miss_char THEN
1407 x_complete_rec.attribute13 := l_media_rec.attribute13;
1408 END IF;
1409
1410 --
1411 -- ATTRIBUTE14
1412 IF p_media_rec.attribute14 = FND_API.g_miss_char THEN
1413 x_complete_rec.attribute14 := l_media_rec.attribute14;
1414 END IF;
1415
1416 --
1417 -- ATTRIBUTE15
1418 IF p_media_rec.attribute15 = FND_API.g_miss_char THEN
1419 x_complete_rec.attribute15 := l_media_rec.attribute15;
1420 END IF;
1421
1422 --
1423 -- MEDIA_NAME
1424 IF p_media_rec.media_name = FND_API.g_miss_char THEN
1425 x_complete_rec.media_name := l_media_rec.media_name;
1426 END IF;
1427
1428 --
1429 -- DESCRIPTION
1430 IF p_media_rec.description = FND_API.g_miss_char THEN
1431 x_complete_rec.description := l_media_rec.description;
1432 END IF;
1433 END Complete_Media_Rec;
1434
1435
1436 -- Check_Media_Req_Items
1437 PROCEDURE Check_Media_Req_Items (
1438 p_media_rec IN Media_Rec_Type,
1439 x_return_status OUT NOCOPY VARCHAR2
1440 )
1441 IS
1442 BEGIN
1443 -- MEDIA_TYPE_CODE
1444 IF p_media_rec.media_type_code IS NULL THEN
1445 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1446 FND_MESSAGE.set_name ('AMS', 'AMS_MED_NO_MEDIA_TYPE_CODE');
1447 FND_MSG_PUB.add;
1448 END IF;
1449
1450 x_return_status := FND_API.g_ret_sts_error;
1451 RETURN;
1452 END IF;
1453
1454 -- MEDIA_NAME
1455 IF p_media_rec.media_name IS NULL THEN
1456 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1457 FND_MESSAGE.set_name ('AMS', 'AMS_MED_NO_MEDIA_NAME');
1458 FND_MSG_PUB.add;
1459 END IF;
1460
1461 x_return_status := FND_API.g_ret_sts_error;
1462 RETURN;
1463 END IF;
1464
1465 END Check_Media_Req_Items;
1466
1467 -- Check_Media_UK_Items
1468 PROCEDURE Check_Media_UK_Items (
1469 p_media_rec IN Media_Rec_Type,
1470 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1471 x_return_status OUT NOCOPY VARCHAR2
1472 )
1473 IS
1474 l_valid_flag VARCHAR2(1);
1475 -- rrajesh 11/07/00 start
1476 l_count NUMBER ;
1477
1478 CURSOR c_name_unique_cr (p_media_name IN VARCHAR2) IS
1479 SELECT COUNT(1)
1480 FROM ams_media_vl
1481 WHERE UPPER(media_name) = UPPER(p_media_name) ;
1482
1483 CURSOR c_name_unique_up (p_media_name IN VARCHAR2, p_media_id IN NUMBER) IS
1484 SELECT COUNT(1)
1485 FROM ams_media_vl
1486 WHERE UPPER(media_name) = UPPER(p_media_name)
1487 AND media_id <> p_media_id ;
1488 -- end 11/07
1489
1490 BEGIN
1491 x_return_status := FND_API.g_ret_sts_success;
1492 --IF (AMS_DEBUG_HIGH_ON) THENAMS_Utility_PVT.debug_message('Check the uniqueness');END IF;
1493
1494 -- MEDIA_ID
1495 -- For Create_Media, when ID is passed in, we need to
1496 -- check if this ID is unique.
1497 IF p_validation_mode = JTF_PLSQL_API.g_create
1498 AND p_media_rec.media_id IS NOT NULL
1499 THEN
1500 IF AMS_Utility_PVT.check_uniqueness(
1501 'ams_media_b',
1502 'media_id = ' || p_media_rec.media_id
1503 ) = FND_API.g_false
1504 THEN
1505 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1506 FND_MESSAGE.set_name ('AMS', 'AMS_MED_DUP_MEDIA_ID');
1507 FND_MSG_PUB.add;
1508 END IF;
1509 x_return_status := FND_API.g_ret_sts_error;
1510 RETURN;
1511 END IF;
1512 END IF;
1513
1514 -- MEDIA_NAME
1515 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1516
1517 -- rrajesh start 11/07. Replaced the AMS_Utility_PVT call
1518 -- with the new cursor.
1519 OPEN c_name_unique_cr(p_media_rec.media_name) ;
1520 FETCH c_name_unique_cr INTO l_count ;
1521 CLOSE c_name_unique_cr ;
1522
1523 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1524 -- 'ams_media_vl',
1525 -- 'media_name = ''' || p_media_rec.media_name || ''''
1526 -- );
1527 ELSE
1528 -- l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1529 -- 'ams_media_vl',
1530 -- 'media_name = ''' || p_media_rec.media_name ||
1531 -- ''' AND media_id <> ' || p_media_rec.media_id
1532 -- );
1533 OPEN c_name_unique_up(p_media_rec.media_name,p_media_rec.media_id) ;
1534 FETCH c_name_unique_up INTO l_count ;
1535 CLOSE c_name_unique_up ;
1536 -- rrajesh end 11/07
1537
1538 END IF;
1539 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_Utility_PVT.debug_message('Check the uniqueness Cpount '|| l_count ); END IF;
1540 -- rrajesh start 11/07. Checking the uniqueness against the new cursor,
1541 -- c_name_unique_up
1542 IF l_count > 0 THEN
1543 -- rrajesh end 11/07
1544 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1545 FND_MESSAGE.set_name ('AMS', 'AMS_MED_DUP_NAME');
1546 FND_MSG_PUB.add;
1547 END IF;
1548 x_return_status := FND_API.g_ret_sts_error;
1549 RETURN;
1550 END IF;
1551
1552 END Check_Media_UK_Items;
1553
1554 -- Check_Media_FK_Items
1555 PROCEDURE Check_Media_FK_Items (
1556 p_media_rec IN Media_Rec_Type,
1557 x_return_status OUT NOCOPY VARCHAR2
1558 )
1559 IS
1560 --
1561 -- choang - 19-Nov-1999
1562 -- No foreign key validation required. Only
1563 -- FK is between B and TL tables.
1564 BEGIN
1565 x_return_status := FND_API.g_ret_sts_success;
1566 END Check_Media_FK_Items;
1567
1568 -- Check_Media_Lookup_Items
1569 PROCEDURE Check_Media_Lookup_Items (
1570 p_media_rec IN Media_Rec_Type,
1571 x_return_status OUT NOCOPY VARCHAR2
1572 )
1573 IS
1574 L_MEDIA_TYPE_CODE CONSTANT VARCHAR2(30) := 'AMS_MEDIA_TYPE';
1575 BEGIN
1576 x_return_status := FND_API.g_ret_sts_success;
1577
1578 --
1579 -- MEDIA_TYPE_CODE
1580 IF p_media_rec.media_type_code <> FND_API.g_miss_char THEN
1581 IF AMS_Utility_PVT.check_lookup_exists (
1582 p_lookup_type => L_MEDIA_TYPE_CODE,
1583 p_lookup_code => p_media_rec.media_type_code
1584 ) = FND_API.g_false
1585 THEN
1586 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1587 FND_MESSAGE.set_name ('AMS', 'AMS_MED_BAD_MEDIA_TYPE_CODE');
1588 FND_MSG_PUB.add;
1589 END IF;
1590
1591 x_return_status := FND_API.g_ret_sts_error;
1592 RETURN;
1593 END IF;
1594 END IF;
1595 END Check_Media_Lookup_Items;
1596
1597 -- This procedure is commented out by JULOU 12/06/2000
1598 -- These flags will be defaulted to "Y".
1599 -- The validation is not required.
1600 -- Check_Media_Flag_Items
1601 /*
1602 PROCEDURE Check_Media_Flag_Items (
1603 p_media_rec IN Media_Rec_Type,
1604 x_return_status OUT NOCOPY VARCHAR2
1605 )
1606 IS
1607 BEGIN
1608 x_return_status := FND_API.g_ret_sts_success;
1609
1610 -- INBOUND_FLAG
1611 IF p_media_rec.inbound_flag <> FND_API.g_miss_char AND p_media_rec.inbound_flag IS NOT NULL THEN
1612 IF AMS_Utility_PVT.is_Y_or_N (p_media_rec.inbound_flag) = FND_API.g_false THEN
1613 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1614 FND_MESSAGE.set_name ('AMS', 'AMS_MED_BAD_INBOUND_FLAG');
1615 FND_MSG_PUB.add;
1616 END IF;
1617
1618 x_return_status := FND_API.g_ret_sts_error;
1619 RETURN;
1620 END IF;
1621 END IF;
1622
1623 -- ENABLED_FLAG
1624 IF p_media_rec.enabled_flag <> FND_API.g_miss_char AND p_media_rec.enabled_flag IS NOT NULL THEN
1625 IF AMS_Utility_PVT.is_Y_or_N (p_media_rec.enabled_flag) = FND_API.g_false THEN
1626 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1627 FND_MESSAGE.set_name ('AMS', 'AMS_MED_BAD_ENABLED_FLAG');
1628 FND_MSG_PUB.add;
1629 END IF;
1630
1631 x_return_status := FND_API.g_ret_sts_error;
1632 RETURN;
1633 END IF;
1634 END IF;
1635
1636 END Check_Media_Flag_Items;
1637 */
1638
1639 -------------------------------------
1640 ------- MEDIA CHANNEL ------
1641 -------------------------------------
1642 ---------------------------------------------------------------------
1643 -- PROCEDURE
1644 -- Create_MediaChannel
1645 ---------------------------------------------------------------------
1646 PROCEDURE Create_MediaChannel (
1647 p_api_version IN NUMBER,
1648 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1649 p_commit IN VARCHAR2 := FND_API.g_false,
1650 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1651
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_msg_count OUT NOCOPY NUMBER,
1654 x_msg_data OUT NOCOPY VARCHAR2,
1655
1656 p_mediachl_rec IN MediaChannel_Rec_Type,
1657 x_mediachl_id OUT NOCOPY NUMBER
1658 )
1659 IS
1660 L_API_VERSION CONSTANT NUMBER := 1.0;
1661 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_MediaChannel';
1662 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1663
1664 -- Following Line is Commented by ptendulk as l_mediachl_rec
1665 -- should be initialized with the input record type
1666 -- l_mediachl_rec MediaChannel_Rec_Type;
1667
1668 l_mediachl_rec MediaChannel_Rec_Type := p_mediachl_rec;
1669 l_dummy NUMBER;
1670 l_return_status VARCHAR2(1);
1671
1672 CURSOR c_seq IS
1673 SELECT ams_media_channels_s.NEXTVAL
1674 FROM dual;
1675
1676 CURSOR c_id_exists (x_id IN NUMBER) IS
1677 SELECT 1
1678 FROM dual
1679 WHERE EXISTS (SELECT 1
1680 FROM ams_media_channels
1681 WHERE media_channel_id = x_id);
1682
1683 BEGIN
1684 --------------------- initialize -----------------------
1685 SAVEPOINT Create_MediaChannel;
1686
1687 IF (AMS_DEBUG_HIGH_ON) THEN
1688
1689
1690
1691 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1692
1693 END IF;
1694
1695 IF FND_API.to_boolean (p_init_msg_list) THEN
1696 FND_MSG_PUB.initialize;
1697 END IF;
1698
1699 IF NOT FND_API.compatible_api_call (
1700 L_API_VERSION,
1701 p_api_version,
1702 L_API_NAME,
1703 G_PKG_NAME
1704 ) THEN
1705 RAISE FND_API.g_exc_unexpected_error;
1706 END IF;
1707
1708 x_return_status := FND_API.g_ret_sts_success;
1709
1710 ----------------------- validate -----------------------
1711 IF (AMS_DEBUG_HIGH_ON) THEN
1712
1713 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
1714 END IF;
1715
1716 Validate_MediaChannel (
1717 p_api_version => l_api_version,
1718 p_init_msg_list => p_init_msg_list,
1719 p_validation_level => p_validation_level,
1720 x_return_status => l_return_status,
1721 x_msg_count => x_msg_count,
1722 x_msg_data => x_msg_data,
1723 p_mediachl_rec => l_mediachl_rec
1724 );
1725
1726 IF l_return_status = FND_API.g_ret_sts_error THEN
1727 RAISE FND_API.g_exc_error;
1728 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1729 RAISE FND_API.g_exc_unexpected_error;
1730 END IF;
1731
1732 --
1733 -- Check for the ID.
1734 --
1735 IF l_mediachl_rec.media_channel_id IS NULL THEN
1736 LOOP
1737 --
1738 -- If the ID is not passed into the API, then
1739 -- grab a value from the sequence.
1740 OPEN c_seq;
1741 FETCH c_seq INTO l_mediachl_rec.media_channel_id;
1742 CLOSE c_seq;
1743
1744 --
1745 -- Check to be sure that the sequence does not exist.
1746 OPEN c_id_exists (l_mediachl_rec.media_channel_id);
1747 FETCH c_id_exists INTO l_dummy;
1748 CLOSE c_id_exists;
1749
1750 --
1751 -- If the value for the ID already exists, then
1752 -- l_dummy would be populated with '1', otherwise,
1753 -- it receives NULL.
1754 EXIT WHEN l_dummy IS NULL;
1755 END LOOP;
1756 END IF;
1757
1758 -------------------------- insert --------------------------
1759 IF (AMS_DEBUG_HIGH_ON) THEN
1760
1761 AMS_Utility_PVT.debug_message (l_full_name || ': insert');
1762 END IF;
1763 IF (AMS_DEBUG_HIGH_ON) THEN
1764
1765 AMS_Utility_PVT.debug_message (l_full_name || ': '||l_mediachl_rec.media_channel_id);
1766 END IF;
1767 IF (AMS_DEBUG_HIGH_ON) THEN
1768
1769 AMS_Utility_PVT.debug_message (l_full_name || ': '||l_mediachl_rec.media_id);
1770 END IF;
1771 IF (AMS_DEBUG_HIGH_ON) THEN
1772
1773 AMS_Utility_PVT.debug_message (l_full_name || ': '||l_mediachl_rec.channel_id);
1774 END IF;
1775 IF (AMS_DEBUG_HIGH_ON) THEN
1776
1777 AMS_Utility_PVT.debug_message (l_full_name || ': '||l_mediachl_rec.active_from_date);
1778 END IF;
1779 IF (AMS_DEBUG_HIGH_ON) THEN
1780
1781 AMS_Utility_PVT.debug_message (l_full_name || ': '||l_mediachl_rec.active_to_date);
1782 END IF;
1783
1784 INSERT INTO ams_media_channels (
1785 media_channel_id,
1786 last_update_date,
1787 last_updated_by,
1788 creation_date,
1789 created_by,
1790 last_update_login,
1791 object_version_number,
1792 media_id,
1793 channel_id,
1794 active_from_date,
1795 active_to_date
1796 )
1797 VALUES (
1798 l_mediachl_rec.media_channel_id,
1799 SYSDATE, -- last_update_date
1800 FND_GLOBAL.user_id, -- last_updated_by
1801 SYSDATE, -- creation_date
1802 FND_GLOBAL.user_id, -- created_by
1803 FND_GLOBAl.conc_login_id, -- last_update_login
1804 1, -- object_version_number
1805 l_mediachl_rec.media_id,
1806 l_mediachl_rec.channel_id,
1807 l_mediachl_rec.active_from_date,
1808 l_mediachl_rec.active_to_date
1809 );
1810
1811 ------------------------- finish -------------------------------
1812 -- set OUT value
1813 x_mediachl_id := l_mediachl_rec.media_channel_id;
1814
1815 --
1816 -- END of API body.
1817 --
1818
1819 -- Standard check of p_commit.
1820 IF FND_API.To_Boolean ( p_commit ) THEN
1821 COMMIT WORK;
1822 END IF;
1823
1824 FND_MSG_PUB.count_and_get (
1825 p_encoded => FND_API.g_false,
1826 p_count => x_msg_count,
1827 p_data => x_msg_data
1828 );
1829
1830 IF (AMS_DEBUG_HIGH_ON) THEN
1831
1832
1833
1834 AMS_Utility_PVT.debug_message (l_full_name || ': End');
1835
1836 END IF;
1837
1838 EXCEPTION
1839 WHEN FND_API.g_exc_error THEN
1840 ROLLBACK TO Create_MediaChannel;
1841 x_return_status := FND_API.g_ret_sts_error;
1842 FND_MSG_PUB.count_and_get (
1843 p_encoded => FND_API.g_false,
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847 WHEN FND_API.g_exc_unexpected_error THEN
1848 ROLLBACK TO Create_MediaChannel;
1849 x_return_status := FND_API.g_ret_sts_unexp_error ;
1850 FND_MSG_PUB.count_and_get (
1851 p_encoded => FND_API.g_false,
1852 p_count => x_msg_count,
1853 p_data => x_msg_data
1854 );
1855 WHEN OTHERS THEN
1856 ROLLBACK TO Create_MediaChannel;
1857 x_return_status := FND_API.g_ret_sts_unexp_error ;
1858
1859 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1860 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
1861 END IF;
1862
1863 FND_MSG_PUB.count_and_get (
1864 p_encoded => FND_API.g_false,
1865 p_count => x_msg_count,
1866 p_data => x_msg_data
1867 );
1868 END Create_MediaChannel;
1869
1870
1871 ---------------------------------------------------------------------
1872 -- PROCEDURE
1873 -- Update_MediaChannel
1874 ---------------------------------------------------------------------
1875 PROCEDURE Update_MediaChannel (
1876 p_api_version IN NUMBER,
1877 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1878 p_commit IN VARCHAR2 := FND_API.g_false,
1879 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1880
1881 x_return_status OUT NOCOPY VARCHAR2,
1882 x_msg_count OUT NOCOPY NUMBER,
1883 x_msg_data OUT NOCOPY VARCHAR2,
1884
1885 p_mediachl_rec IN MediaChannel_Rec_Type
1886 )
1887 IS
1888 L_API_VERSION CONSTANT NUMBER := 1.0;
1889 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_MediaChannel';
1890 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1891
1892 l_mediachl_rec MediaChannel_Rec_Type;
1893 l_dummy NUMBER;
1894 l_return_status VARCHAR2(1);
1895
1896 BEGIN
1897 --------------------- initialize -----------------------
1898 SAVEPOINT Update_MediaChannel;
1899
1900 IF (AMS_DEBUG_HIGH_ON) THEN
1901
1902
1903
1904 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
1905
1906 END IF;
1907
1908 IF FND_API.to_boolean (p_init_msg_list) THEN
1909 FND_MSG_PUB.initialize;
1910 END IF;
1911
1912 IF NOT FND_API.compatible_api_call(
1913 l_api_version,
1914 p_api_version,
1915 l_api_name,
1916 g_pkg_name
1917 ) THEN
1918 RAISE FND_API.g_exc_unexpected_error;
1919 END IF;
1920
1921 x_return_status := FND_API.g_ret_sts_success;
1922
1923 ----------------------- validate ----------------------
1924 IF (AMS_DEBUG_HIGH_ON) THEN
1925
1926 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
1927 END IF;
1928
1929 -- replace g_miss_char/num/date with current column values
1930 Complete_MediaChannel_Rec (p_mediachl_rec, l_mediachl_rec);
1931
1932 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1933 Check_MediaChannel_Items (
1934 p_mediachl_rec => l_mediachl_rec, -- change from p_mediachl_rec vmodur
1935 p_validation_mode => JTF_PLSQL_API.g_update,
1936 x_return_status => l_return_status
1937 );
1938
1939 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1940 RAISE FND_API.g_exc_unexpected_error;
1941 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1942 RAISE FND_API.g_exc_error;
1943 END IF;
1944 END IF;
1945
1946 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1947 Check_MediaChannel_Record (
1948 p_mediachl_rec => l_mediachl_rec, -- change from p_mediachl_rec vmodur
1949 p_complete_rec => l_mediachl_rec,
1950 x_return_status => l_return_status
1951 );
1952
1953 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1954 RAISE FND_API.g_exc_unexpected_error;
1955 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1956 RAISE FND_API.g_exc_error;
1957 END IF;
1958 END IF;
1959
1960 -- Following lines are commented by ptendulk on 16dec1999
1961 -- as we are not validating Interentity
1962 -- IF p_validation_level >= JTF_PLSQL_API.g_valid_level_inter_entity THEN
1963 -- Check_MediaChannel_InterEntity (
1964 -- p_mediachl_rec => p_mediachl_rec,
1965 -- p_complete_rec => l_mediachl_rec,
1966 -- x_return_status => l_return_status
1967 -- );
1968 --
1969 -- IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1970 -- RAISE FND_API.g_exc_unexpected_error;
1971 -- ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1972 -- RAISE FND_API.g_exc_error;
1973 -- END IF;
1974 -- END IF;
1975
1976 -------------------------- update --------------------
1977 IF (AMS_DEBUG_HIGH_ON) THEN
1978
1979 AMS_Utility_PVT.debug_message(l_full_name ||': update');
1980 END IF;
1981
1982 -- Modified by ptendulk on 30Dec1999
1983 -- Check Obj Version Number before Updating
1984 UPDATE ams_media_channels
1985 SET
1986 last_update_date = SYSDATE,
1987 last_updated_by = FND_GLOBAL.user_id,
1988 last_update_login = FND_GLOBAL.conc_login_id,
1989 object_version_number = object_version_number + 1,
1990 media_id = l_mediachl_rec.media_id,
1991 channel_id = l_mediachl_rec.channel_id,
1992 active_from_date = l_mediachl_rec.active_from_date,
1993 active_to_date = l_mediachl_rec.active_to_date
1994 WHERE media_channel_id = l_mediachl_rec.media_channel_id
1995 AND object_version_number = l_mediachl_rec.object_version_number ;
1996
1997 IF (SQL%NOTFOUND) THEN
1998 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1999 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2000 FND_MSG_PUB.add;
2001 END IF;
2002 RAISE FND_API.g_exc_error;
2003 END IF;
2004
2005 -------------------- finish --------------------------
2006 IF FND_API.to_boolean (p_commit) THEN
2007 COMMIT;
2008 END IF;
2009
2010 FND_MSG_PUB.count_and_get (
2011 p_encoded => FND_API.g_false,
2012 p_count => x_msg_count,
2013 p_data => x_msg_data
2014 );
2015
2016 IF (AMS_DEBUG_HIGH_ON) THEN
2017
2018
2019
2020 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2021
2022 END IF;
2023
2024 EXCEPTION
2025 WHEN FND_API.g_exc_error THEN
2026 ROLLBACK TO Update_MediaChannel;
2027 x_return_status := FND_API.g_ret_sts_error;
2028 FND_MSG_PUB.count_and_get (
2029 p_encoded => FND_API.g_false,
2030 p_count => x_msg_count,
2031 p_data => x_msg_data
2032 );
2033 WHEN FND_API.g_exc_unexpected_error THEN
2034 ROLLBACK TO Update_MediaChannel;
2035 x_return_status := FND_API.g_ret_sts_unexp_error ;
2036 FND_MSG_PUB.count_and_get (
2037 p_encoded => FND_API.g_false,
2038 p_count => x_msg_count,
2039 p_data => x_msg_data
2040 );
2041 WHEN OTHERS THEN
2042 ROLLBACK TO Update_MediaChannel;
2043 x_return_status := FND_API.g_ret_sts_unexp_error ;
2044
2045 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2046 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2047 END IF;
2048
2049 FND_MSG_PUB.count_and_get (
2050 p_encoded => FND_API.g_false,
2051 p_count => x_msg_count,
2052 p_data => x_msg_data
2053 );
2054 END Update_MediaChannel;
2055
2056
2057 ---------------------------------------------------------------------
2058 -- PROCEDURE
2059 -- Delete_MediaChannel
2060 -- HISTORY
2061 -- 11-JUL-2000 holiu Cannot delete if used by campaigns.
2062 ---------------------------------------------------------------------
2063 PROCEDURE Delete_MediaChannel (
2064 p_api_version IN NUMBER,
2065 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2066 p_commit IN VARCHAR2 := FND_API.g_false,
2067 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2068
2069 x_return_status OUT NOCOPY VARCHAR2,
2070 x_msg_count OUT NOCOPY NUMBER,
2071 x_msg_data OUT NOCOPY VARCHAR2,
2072
2073 p_mediachl_id IN NUMBER,
2074 p_object_version IN NUMBER
2075 )
2076 IS
2077
2078 l_api_version CONSTANT NUMBER := 1.0;
2079 l_api_name CONSTANT VARCHAR2(30) := 'Delete_MediaChannel';
2080 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2081 l_in_use NUMBER;
2082
2083 CURSOR c_in_use IS
2084 SELECT 1
2085 FROM DUAL
2086 WHERE EXISTS(
2087 SELECT A.campaign_id
2088 FROM ams_campaigns_all_b A, ams_media_channels B -- Perf fix use all_b
2089 WHERE A.active_flag = 'Y'
2090 AND A.arc_channel_from = 'CHLS'
2091 AND A.media_id = B.media_id
2092 AND A.channel_id = B.channel_id
2093 ANd B.media_channel_id = p_mediachl_id);
2094
2095 -- Bug fix:2089112. Added by rrajesh on 10/31/01
2096 CURSOR c_check_schedule_association IS
2097 SELECT marketing_medium_id
2098 FROM ams_campaign_schedules_b a, ams_media_channels b
2099 WHERE a.marketing_medium_id = b.channel_id
2100 -- Added by dbiswas 12/31/02 to allow removal of medium from activity
2101 AND a.activity_id = b.media_id
2102 -- end change 12/31/02
2103 AND b.media_channel_id = p_mediachl_id;
2104 l_mktg_medium_id NUMBER;
2105 -- End change 10/31/01
2106 BEGIN
2107 --------------------- initialize -----------------------
2108 SAVEPOINT Delete_MediaChannel;
2109
2110 IF (AMS_DEBUG_HIGH_ON) THEN
2111
2112
2113
2114 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2115
2116 END IF;
2117
2118 IF FND_API.to_boolean (p_init_msg_list) THEN
2119 FND_MSG_PUB.initialize;
2120 END IF;
2121
2122 IF NOT FND_API.compatible_api_call (
2123 l_api_version,
2124 p_api_version,
2125 l_api_name,
2126 g_pkg_name
2127 ) THEN
2128 RAISE FND_API.g_exc_unexpected_error;
2129 END IF;
2130
2131 x_return_status := FND_API.g_ret_sts_success;
2132
2133 -- holiu: add the following checking for bug 1350477
2134 IF (AMS_DEBUG_HIGH_ON) THEN
2135
2136 AMS_Utility_PVT.debug_message (l_full_name || ': check before delete');
2137 END IF;
2138
2139 OPEN c_in_use;
2140 FETCH c_in_use INTO l_in_use;
2141 CLOSE c_in_use;
2142
2143 IF l_in_use IS NOT NULL THEN
2144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2145 THEN
2146 FND_MESSAGE.set_name('AMS', 'AMS_MED_CANNOT_DELETE_CHAN');
2147 FND_MSG_PUB.add;
2148 END IF;
2149 RAISE FND_API.g_exc_error;
2150 END IF;
2151
2152 -- Bug fix:2089112. Added by rrajesh on 10/31/01
2153 OPEN c_check_schedule_association;
2154 FETCH c_check_schedule_association INTO l_mktg_medium_id;
2155 CLOSE c_check_schedule_association;
2156
2157 IF l_mktg_medium_id IS NOT NULL THEN
2158 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2159 THEN
2160 FND_MESSAGE.set_name('AMS', 'AMS_MKTG_MEDIA_ACT_IN_USE');
2161 FND_MSG_PUB.add;
2162 END IF;
2163 RAISE FND_API.g_exc_error;
2164 END IF;
2165 -- Bug fix:2089112. 10/31/01
2166
2167 ------------------------ delete ------------------------
2168 IF (AMS_DEBUG_HIGH_ON) THEN
2169
2170 AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
2171 END IF;
2172
2173 DELETE FROM ams_media_channels
2174 WHERE media_channel_id = p_mediachl_id;
2175
2176 IF (SQL%NOTFOUND) THEN
2177 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2178 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
2179 FND_MSG_PUB.add;
2180 END IF;
2181 RAISE FND_API.g_exc_error;
2182 END IF;
2183
2184 -------------------- finish --------------------------
2185 IF FND_API.to_boolean (p_commit) THEN
2186 COMMIT;
2187 END IF;
2188
2189 FND_MSG_PUB.count_and_get (
2190 p_encoded => FND_API.g_false,
2191 p_count => x_msg_count,
2192 p_data => x_msg_data
2193 );
2194
2195 IF (AMS_DEBUG_HIGH_ON) THEN
2196
2197
2198
2199 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2200
2201 END IF;
2202
2203 EXCEPTION
2204 WHEN FND_API.g_exc_error THEN
2205 ROLLBACK TO Delete_MediaChannel;
2206 x_return_status := FND_API.g_ret_sts_error;
2207 FND_MSG_PUB.count_and_get (
2208 p_encoded => FND_API.g_false,
2209 p_count => x_msg_count,
2210 p_data => x_msg_data
2211 );
2212 WHEN FND_API.g_exc_unexpected_error THEN
2213 ROLLBACK TO Delete_MediaChannel;
2214 x_return_status := FND_API.g_ret_sts_unexp_error ;
2215 FND_MSG_PUB.count_and_get (
2216 p_encoded => FND_API.g_false,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data
2219 );
2220 WHEN OTHERS THEN
2221 ROLLBACK TO Delete_MediaChannel;
2222 x_return_status := FND_API.g_ret_sts_unexp_error ;
2223
2224 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2225 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2226 END IF;
2227
2228 FND_MSG_PUB.count_and_get (
2229 p_encoded => FND_API.g_false,
2230 p_count => x_msg_count,
2231 p_data => x_msg_data
2232 );
2233 END Delete_MediaChannel;
2234
2235
2236 ---------------------------------------------------------------------
2237 -- PROCEDURE
2238 -- Lock_MediaChannel
2239 ---------------------------------------------------------------------
2240 PROCEDURE Lock_MediaChannel (
2241 p_api_version IN NUMBER,
2242 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2243 p_commit IN VARCHAR2 := FND_API.g_false,
2244 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2245
2246 x_return_status OUT NOCOPY VARCHAR2,
2247 x_msg_count OUT NOCOPY NUMBER,
2248 x_msg_data OUT NOCOPY VARCHAR2,
2249
2250 p_mediachl_id IN NUMBER,
2251 p_object_version IN NUMBER
2252 )
2253 IS
2254 l_api_version CONSTANT NUMBER := 1.0;
2255 l_api_name CONSTANT VARCHAR2(30) := 'Lock_MediaChannel';
2256 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2257
2258 l_dummy NUMBER; -- Used by the lock cursor.
2259
2260 --
2261 -- NOTE: Not necessary to distinguish between a record
2262 -- which does not exist and one which has been updated
2263 -- by another user. To get that distinction, remove
2264 -- the object_version condition from the SQL statement
2265 -- and perform comparison in the body and raise the
2266 -- exception there.
2267 CURSOR c_lock IS
2268 SELECT object_version_number
2269 FROM ams_media_channels
2270 WHERE media_channel_id = p_mediachl_id
2271 AND object_version_number = p_object_version
2272 FOR UPDATE NOWAIT;
2273 BEGIN
2274 --------------------- initialize -----------------------
2275 IF (AMS_DEBUG_HIGH_ON) THEN
2276
2277 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2278 END IF;
2279
2280 IF FND_API.to_boolean (p_init_msg_list) THEN
2281 FND_MSG_PUB.initialize;
2282 END IF;
2283
2284 IF NOT FND_API.compatible_api_call (
2285 l_api_version,
2286 p_api_version,
2287 l_api_name,
2288 g_pkg_name
2289 ) THEN
2290 RAISE FND_API.g_exc_unexpected_error;
2291 END IF;
2292
2293 x_return_status := FND_API.g_ret_sts_success;
2294
2295 ------------------------ lock -------------------------
2296 IF (AMS_DEBUG_HIGH_ON) THEN
2297
2298 AMS_Utility_PVT.debug_message (l_full_name || ': Lock');
2299 END IF;
2300
2301 OPEN c_lock;
2302 FETCH c_lock INTO l_dummy;
2303 IF (c_lock%NOTFOUND) THEN
2304 CLOSE c_lock;
2305 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2306 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2307 FND_MSG_PUB.add;
2308 END IF;
2309 RAISE FND_API.g_exc_error;
2310 END IF;
2311 CLOSE c_lock;
2312
2313 -------------------- finish --------------------------
2314 FND_MSG_PUB.count_and_get (
2315 p_encoded => FND_API.g_false,
2316 p_count => x_msg_count,
2317 p_data => x_msg_data
2318 );
2319
2320 IF (AMS_DEBUG_HIGH_ON) THEN
2321
2322
2323
2324 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2325
2326 END IF;
2327
2328 EXCEPTION
2329 WHEN AMS_Utility_PVT.resource_locked THEN
2330 x_return_status := FND_API.g_ret_sts_error;
2331 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2332 FND_MESSAGE.set_name ('AMS', 'AMS_API_RESOURCE_LOCKED');
2333 FND_MSG_PUB.add;
2334 END IF;
2335
2336 FND_MSG_PUB.count_and_get (
2337 p_encoded => FND_API.g_false,
2338 p_count => x_msg_count,
2339 p_data => x_msg_data
2340 );
2341 WHEN FND_API.g_exc_error THEN
2342 x_return_status := FND_API.g_ret_sts_error;
2343 FND_MSG_PUB.count_and_get (
2344 p_encoded => FND_API.g_false,
2345 p_count => x_msg_count,
2346 p_data => x_msg_data
2347 );
2348 WHEN FND_API.g_exc_unexpected_error THEN
2349 x_return_status := FND_API.g_ret_sts_unexp_error ;
2350 FND_MSG_PUB.count_and_get (
2351 p_encoded => FND_API.g_false,
2352 p_count => x_msg_count,
2353 p_data => x_msg_data
2354 );
2355 WHEN OTHERS THEN
2356 x_return_status := FND_API.g_ret_sts_unexp_error ;
2357 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2358 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2359 END IF;
2360
2361 FND_MSG_PUB.count_and_get (
2362 p_encoded => FND_API.g_false,
2363 p_count => x_msg_count,
2364 p_data => x_msg_data
2365 );
2366 END Lock_MediaChannel;
2367
2368
2369 ---------------------------------------------------------------------
2370 -- PROCEDURE
2371 -- Validate_MediaChannel
2372 ---------------------------------------------------------------------
2373 PROCEDURE Validate_MediaChannel (
2374 p_api_version IN NUMBER,
2375 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2376 p_commit IN VARCHAR2 := FND_API.g_false,
2377 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2378
2379 x_return_status OUT NOCOPY VARCHAR2,
2380 x_msg_count OUT NOCOPY NUMBER,
2381 x_msg_data OUT NOCOPY VARCHAR2,
2382
2383 p_mediachl_rec IN MediaChannel_Rec_Type
2384 )
2385 IS
2386 L_API_VERSION CONSTANT NUMBER := 1.0;
2387 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_MediaChannel';
2388 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2389
2390 l_return_status VARCHAR2(1);
2391 BEGIN
2392 --------------------- initialize -----------------------
2393 IF (AMS_DEBUG_HIGH_ON) THEN
2394
2395 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
2396 END IF;
2397
2398 IF FND_API.to_boolean (p_init_msg_list) THEN
2399 FND_MSG_PUB.initialize;
2400 END IF;
2401
2402 IF NOT FND_API.compatible_api_call (
2403 l_api_version,
2404 p_api_version,
2405 l_api_name,
2406 g_pkg_name
2407 ) THEN
2408 RAISE FND_API.g_exc_unexpected_error;
2409 END IF;
2410
2411 x_return_status := FND_API.g_ret_sts_success;
2412
2413 ---------------------- validate ------------------------
2414 IF (AMS_DEBUG_HIGH_ON) THEN
2415
2416 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
2417 END IF;
2418
2419 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2420 Check_MediaChannel_Items (
2421 p_mediachl_rec => p_mediachl_rec,
2422 p_validation_mode => JTF_PLSQL_API.g_create,
2423 x_return_status => l_return_status
2424 );
2425
2426 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2427 RAISE FND_API.g_exc_unexpected_error;
2428 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2429 RAISE FND_API.g_exc_error;
2430 END IF;
2431 END IF;
2432
2433 IF (AMS_DEBUG_HIGH_ON) THEN
2434
2435
2436
2437 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
2438
2439 END IF;
2440
2441 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
2442 Check_MediaChannel_Record (
2443 p_mediachl_rec => p_mediachl_rec,
2444 p_complete_rec => NULL,
2445 x_return_status => l_return_status
2446 );
2447
2448 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2449 RAISE FND_API.g_exc_unexpected_error;
2450 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
2451 RAISE FND_API.g_exc_error;
2452 END IF;
2453 END IF;
2454
2455 -------------------- finish --------------------------
2456 FND_MSG_PUB.count_and_get (
2457 p_encoded => FND_API.g_false,
2458 p_count => x_msg_count,
2459 p_data => x_msg_data
2460 );
2461
2462 IF (AMS_DEBUG_HIGH_ON) THEN
2463
2464
2465
2466 AMS_Utility_PVT.debug_message (l_full_name || ': End');
2467
2468 END IF;
2469
2470 EXCEPTION
2471 WHEN FND_API.g_exc_error THEN
2472 x_return_status := FND_API.g_ret_sts_error;
2473 FND_MSG_PUB.count_and_get (
2474 p_encoded => FND_API.g_false,
2475 p_count => x_msg_count,
2476 p_data => x_msg_data
2477 );
2478 WHEN FND_API.g_exc_unexpected_error THEN
2479 x_return_status := FND_API.g_ret_sts_unexp_error ;
2480 FND_MSG_PUB.count_and_get (
2481 p_encoded => FND_API.g_false,
2482 p_count => x_msg_count,
2483 p_data => x_msg_data
2484 );
2485 WHEN OTHERS THEN
2486 x_return_status := FND_API.g_ret_sts_unexp_error;
2487 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2488 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
2489 END IF;
2490
2491 FND_MSG_PUB.count_and_get (
2492 p_encoded => FND_API.g_false,
2493 p_count => x_msg_count,
2494 p_data => x_msg_data
2495 );
2496 END Validate_MediaChannel;
2497
2498
2499 ---------------------------------------------------------------------
2500 -- PROCEDURE
2501 -- Check_MediaChannel_Items
2502 ---------------------------------------------------------------------
2503 PROCEDURE Check_MediaChannel_Items (
2504 p_mediachl_rec IN MediaChannel_Rec_Type,
2505 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2506 x_return_status OUT NOCOPY VARCHAR2
2507 )
2508 IS
2509 BEGIN
2510 --
2511 -- Validate required items.
2512 Check_MediaChannel_Req_Items (
2513 p_mediachl_rec => p_mediachl_rec,
2514 x_return_status => x_return_status
2515 );
2516
2517 IF x_return_status <> FND_API.g_ret_sts_success THEN
2518 RETURN;
2519 END IF;
2520
2521 --
2522 -- Validate uniqueness.
2523 Check_MediaChannel_UK_Items (
2524 p_mediachl_rec => p_mediachl_rec,
2525 p_validation_mode => p_validation_mode,
2526 x_return_status => x_return_status
2527 );
2528
2529 IF x_return_status <> FND_API.g_ret_sts_success THEN
2530 RETURN;
2531 END IF;
2532
2533 Check_MediaChannel_FK_Items(
2534 p_mediachl_rec => p_mediachl_rec,
2535 x_return_status => x_return_status
2536 );
2537
2538 IF x_return_status <> FND_API.g_ret_sts_success THEN
2539 RETURN;
2540 END IF;
2541
2542 Check_MediaChl_Lookup_Items (
2543 p_mediachl_rec => p_mediachl_rec,
2544 x_return_status => x_return_status
2545 );
2546
2547 IF x_return_status <> FND_API.g_ret_sts_success THEN
2548 RETURN;
2549 END IF;
2550
2551 Check_MediaChannel_Flag_Items(
2552 p_mediachl_rec => p_mediachl_rec,
2553 x_return_status => x_return_status
2554 );
2555
2556 IF x_return_status <> FND_API.g_ret_sts_success THEN
2557 RETURN;
2558 END IF;
2559 END Check_MediaChannel_Items;
2560
2561
2562 ---------------------------------------------------------------------
2563 -- PROCEDURE
2564 -- Check_MediaChannel_Record
2565 ---------------------------------------------------------------------
2566 PROCEDURE Check_MediaChannel_Record (
2567 p_mediachl_rec IN MediaChannel_Rec_Type,
2568 p_complete_rec IN MediaChannel_Rec_Type := NULL,
2569 x_return_status OUT NOCOPY VARCHAR2
2570 )
2571 IS
2572 l_active_from_date DATE;
2573 l_active_to_date DATE;
2574 l_channel_id NUMBER;
2575 l_chan_from_date DATE;
2576 l_chan_to_date DATE;
2577
2578 CURSOR c_check_date(p_chan_id IN NUMBER) IS
2579 SELECT active_from_date, active_to_date
2580 FROM AMS_CHANNELS_VL
2581 WHERE channel_id = p_chan_id;
2582
2583 BEGIN
2584 --
2585 -- Use local vars to reduce amount of typing.
2586 l_active_from_date := p_mediachl_rec.active_from_date;
2587 l_active_to_date := p_mediachl_rec.active_to_date;
2588
2589
2590 l_channel_id := p_mediachl_rec.channel_id;
2591
2592 x_return_status := FND_API.G_RET_STS_SUCCESS;
2593 --
2594 -- Validate the active dates.
2595 --
2596 IF l_active_from_date <> FND_API.g_miss_date AND l_active_to_date <> FND_API.g_miss_date THEN
2597 IF l_active_from_date IS NULL THEN
2598 l_active_from_date := p_complete_rec.active_from_date;
2599 END IF;
2600
2601 IF l_active_to_date IS NULL THEN
2602 l_active_to_date := p_complete_rec.active_to_date;
2603 END IF;
2604
2605 IF l_active_from_date > l_active_to_date THEN
2606 FND_MESSAGE.set_name ('AMS', 'AMS_MED_FROMDT_GTR_TODT');
2607 FND_MSG_PUB.add;
2608 x_return_status := FND_API.g_ret_sts_error;
2609 RETURN;
2610 END IF;
2611 END IF;
2612
2613 --
2614 -- this code added by abhola
2615 --
2616
2617 -- Validate that media chaneel dates entered should not be greater
2618 -- than channel dates
2619 --
2620 IF l_active_from_date <> FND_API.g_miss_date OR l_active_to_date <> FND_API.g_miss_date THEN
2621 IF l_active_from_date IS NULL THEN
2622 l_active_from_date := p_complete_rec.active_from_date;
2623 END IF;
2624
2625 IF l_active_to_date IS NULL THEN
2626 l_active_to_date := p_complete_rec.active_to_date;
2627 END IF;
2628
2629 OPEN c_check_date(l_channel_id);
2630 FETCH c_check_date INTO l_chan_from_date, l_chan_to_date;
2631 CLOSE c_check_date;
2632
2633 IF ((l_chan_from_date IS NOT NULL) AND (l_chan_to_date IS NOT NULL))
2634
2635 THEN
2636
2637 IF ( (l_active_from_date < l_chan_from_date)
2638 OR (l_active_from_date > l_chan_to_date )
2639 )
2640 THEN
2641
2642 FND_MESSAGE.set_name ('AMS', 'AMS_MEDCHAN_FROMDT_GTR');
2643 FND_MSG_PUB.add;
2644 x_return_status := FND_API.g_ret_sts_error;
2645 RETURN;
2646
2647 END IF;
2648
2649
2650 IF (
2651 (l_active_to_date > l_chan_to_date )
2652 OR (l_active_to_date < l_chan_from_date)
2653 )
2654 THEN
2655
2656 FND_MESSAGE.set_name ('AMS', 'AMS_MEDCHAN_TODT_GTR');
2657 FND_MSG_PUB.add;
2658 x_return_status := FND_API.g_ret_sts_error;
2659 RETURN;
2660
2661 END IF;
2662 END IF;
2663 END IF;
2664
2665 -- vmodur added
2666 IF (l_chan_from_date IS NOT NULL AND l_chan_to_date IS NULL) THEN
2667
2668 IF l_active_from_date < l_chan_from_date THEN
2669
2670 FND_MESSAGE.set_name ('AMS', 'AMS_MEDCHAN_FROMDT_GTR');
2671 FND_MSG_PUB.add;
2672 x_return_status := FND_API.g_ret_sts_error;
2673 RETURN;
2674
2675 END IF;
2676
2677 END IF;
2678
2679 END Check_MediaChannel_Record;
2680
2681
2682 ---------------------------------------------------------------------
2683 -- PROCEDURE
2684 -- Init_MediaChannel_Rec
2685 ---------------------------------------------------------------------
2686 PROCEDURE Init_MediaChannel_Rec (
2687 x_mediachl_rec OUT NOCOPY MediaChannel_Rec_Type
2688 )
2689 IS
2690 BEGIN
2691 x_mediachl_rec.media_channel_id := FND_API.g_miss_num;
2692 x_mediachl_rec.last_update_date := FND_API.g_miss_date;
2693 x_mediachl_rec.last_updated_by := FND_API.g_miss_num;
2694 x_mediachl_rec.creation_date := FND_API.g_miss_date;
2695 x_mediachl_rec.created_by := FND_API.g_miss_num;
2696 x_mediachl_rec.last_update_login := FND_API.g_miss_num;
2697 x_mediachl_rec.object_version_number := FND_API.g_miss_num;
2698 x_mediachl_rec.media_id := FND_API.g_miss_num;
2699 x_mediachl_rec.channel_id := FND_API.g_miss_num;
2700 x_mediachl_rec.active_from_date := FND_API.g_miss_date;
2701 x_mediachl_rec.active_to_date := FND_API.g_miss_date;
2702 END Init_MediaChannel_Rec;
2703
2704
2705 ---------------------------------------------------------------------
2706 -- PROCEDURE
2707 -- Complete_MediaChannel_Rec
2708 ---------------------------------------------------------------------
2709 PROCEDURE Complete_MediaChannel_Rec (
2710 p_mediachl_rec IN MediaChannel_Rec_Type,
2711 x_complete_rec OUT NOCOPY MediaChannel_Rec_Type
2712 )
2713 IS
2714 CURSOR c_mediachl IS
2715 SELECT *
2716 FROM ams_media_channels
2717 WHERE media_channel_id = p_mediachl_rec.media_channel_id;
2718
2719 l_mediachl_rec c_mediachl%ROWTYPE;
2720 BEGIN
2721 x_complete_rec := p_mediachl_rec;
2722
2723 OPEN c_mediachl;
2724 FETCH c_mediachl INTO l_mediachl_rec;
2725 IF c_mediachl%NOTFOUND THEN
2726 CLOSE c_mediachl;
2727 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2728 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2729 FND_MSG_PUB.add;
2730 END IF;
2731 RAISE FND_API.g_exc_error;
2732 END IF;
2733 CLOSE c_mediachl;
2734
2735 -- MEDIA_ID
2736 IF p_mediachl_rec.media_id = FND_API.g_miss_num THEN
2737 x_complete_rec.media_id := l_mediachl_rec.media_id;
2738 END IF;
2739 -- CHANNEL_ID
2740 IF p_mediachl_rec.channel_id = FND_API.g_miss_num THEN
2741 x_complete_rec.channel_id := l_mediachl_rec.channel_id;
2742 END IF;
2743 -- ACTIVE_FROM_DATE
2744 IF p_mediachl_rec.active_from_date = FND_API.g_miss_date THEN
2745 x_complete_rec.active_from_date := l_mediachl_rec.active_from_date;
2746 END IF;
2747 -- ACTIVE_TO_DATE
2748 IF p_mediachl_rec.active_to_date = FND_API.g_miss_date THEN
2749 x_complete_rec.active_to_date := l_mediachl_rec.active_to_date;
2750 END IF;
2751
2752
2753 END Complete_MediaChannel_Rec;
2754
2755
2756 ---------------------------------------------------------------------
2757 -- PROCEDURE
2758 -- Check_MediaChannel_Req_Items
2759 --
2760 ---------------------------------------------------------------------
2761 PROCEDURE Check_MediaChannel_Req_Items (
2762 p_mediachl_rec IN MediaChannel_Rec_Type,
2763 x_return_status OUT NOCOPY VARCHAR2
2764 )
2765 IS
2766 BEGIN
2767 x_return_status := FND_API.g_ret_sts_success;
2768
2769 --
2770 -- MEDIA_ID
2771 IF p_mediachl_rec.media_id IS NULL THEN
2772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2773 FND_MESSAGE.set_name('AMS', 'AMS_MED_NO_MEDIA_ID');
2774 FND_MSG_PUB.add;
2775 END IF;
2776
2777 x_return_status := FND_API.g_ret_sts_error;
2778 RETURN;
2779 END IF;
2780
2781 --
2782 -- CHANNEL_ID
2783 IF p_mediachl_rec.channel_id IS NULL THEN
2784 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2785 THEN
2786 FND_MESSAGE.set_name('AMS', 'AMS_MED_NO_CHANNEL_ID');
2787 FND_MSG_PUB.add;
2788 END IF;
2789
2790 x_return_status := FND_API.g_ret_sts_error;
2791 RETURN;
2792 END IF;
2793
2794 --
2795 -- ACTIVE_FROM_DATE
2796 IF p_mediachl_rec.active_from_date IS NULL THEN
2797 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2798 FND_MESSAGE.set_name('AMS', 'AMS_MED_NO_ACTIVE_FROM_DATE');
2799 FND_MSG_PUB.add;
2800 END IF;
2801
2802 x_return_status := FND_API.g_ret_sts_error;
2803 RETURN;
2804 END IF;
2805
2806 END Check_MediaChannel_Req_Items;
2807
2808
2809 ---------------------------------------------------------------------
2810 -- PROCEDURE
2811 -- Check_MediaChannel_UK_Items
2812 --
2813 ---------------------------------------------------------------------
2814 PROCEDURE Check_MediaChannel_UK_Items (
2815 p_mediachl_rec IN MediaChannel_Rec_Type,
2816 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
2817 x_return_status OUT NOCOPY VARCHAR2
2818 )
2819 IS
2820 -- soagrawa 15-jan-2002
2821 -- added the following two cursors
2822 CURSOR c_check_uniqueness_create(p_media_id IN NUMBER, p_channel_id IN NUMBER) IS
2823 SELECT count(*)
2824 FROM ams_media_channels
2825 WHERE media_id = p_media_id
2826 AND channel_id = p_channel_id
2827 AND active_from_date <= sysdate
2828 AND (active_to_date IS NULL OR active_to_date > SYSDATE) ;
2829
2830 CURSOR c_check_uniqueness_update(p_media_id IN NUMBER, p_channel_id IN NUMBER, p_media_channel_id IN NUMBER) IS
2831 SELECT count(*)
2832 FROM ams_media_channels
2833 WHERE media_id = p_media_id
2834 AND channel_id = p_channel_id
2835 AND media_channel_id <> p_media_channel_id
2836 AND active_from_date <= sysdate
2837 AND (active_to_date IS NULL OR active_to_date > SYSDATE) ;
2838
2839
2840 l_valid_flag VARCHAR2(1);
2841 l_count NUMBER;
2842 BEGIN
2843 x_return_status := FND_API.g_ret_sts_success;
2844 IF (AMS_DEBUG_HIGH_ON) THEN
2845
2846 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items start');
2847 END IF;
2848 -- The combination of media and channel should
2849 -- be unique.
2850 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2851 IF (AMS_DEBUG_HIGH_ON) THEN
2852
2853 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items CREATE');
2854 END IF;
2855 IF (AMS_DEBUG_HIGH_ON) THEN
2856
2857 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items >'||p_mediachl_rec.media_id);
2858 END IF;
2859 IF (AMS_DEBUG_HIGH_ON) THEN
2860
2861 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items >'||p_mediachl_rec.channel_id);
2862 END IF;
2863 /*
2864 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
2865 'ams_media_channels',
2866 'media_id = ' || p_mediachl_rec.media_id
2867 || ' AND channel_id = ' || p_mediachl_rec.channel_id
2868 -- following two where clauses added by soagrawa on 15-jan-2002
2869 -- to keep it consistent with the query in AmsGetChannel.java checkMedChan()
2870 || ' AND active_from_date <= SYSDATE '
2871 || ' AND ( active_to_date is NULL OR active_to_date > SYSDATE)'
2872 );*/
2873 -- soagrawa 15-jan-2002
2874 -- check uniqueness replaced by cursor
2875 OPEN c_check_uniqueness_create(p_mediachl_rec.media_id, p_mediachl_rec.channel_id);
2876 FETCH c_check_uniqueness_create INTO l_count;
2877 CLOSE c_check_uniqueness_create;
2878
2879 IF (AMS_DEBUG_HIGH_ON) THEN
2880
2881
2882
2883 AMS_Utility_PVT.debug_message('l_count is >'||l_count);
2884
2885 END IF;
2886 IF l_count = 0
2887 THEN
2888 l_valid_flag := FND_API.g_true;
2889 ELSE
2890 l_valid_flag := FND_API.g_false;
2891 END IF;
2892
2893 ELSE
2894 IF (AMS_DEBUG_HIGH_ON) THEN
2895
2896 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items UPDATE');
2897 END IF;
2898 IF (AMS_DEBUG_HIGH_ON) THEN
2899
2900 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items >'||p_mediachl_rec.media_id);
2901 END IF;
2902 IF (AMS_DEBUG_HIGH_ON) THEN
2903
2904 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items >'||p_mediachl_rec.channel_id);
2905 END IF;
2906 IF (AMS_DEBUG_HIGH_ON) THEN
2907
2908 AMS_Utility_PVT.debug_message('Check_MediaChannel_UK_Items >'||p_mediachl_rec.media_channel_id);
2909 END IF;
2910 --
2911 -- For UPDATE operations, make sure the
2912 -- uniqueness check excludes the current
2913 -- record.
2914 /*
2915 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
2916 'ams_media_channels',
2917 'media_id = ' || p_mediachl_rec.media_id
2918 || ' AND channel_id = ' || p_mediachl_rec.channel_id
2919 || ' AND media_channel_id <> ' || p_mediachl_rec.media_channel_id
2920 -- following two where clauses added by soagrawa on 15-jan-2002
2921 -- to keep it consistent with the query in AmsGetChannel.java checkMedChan()
2922 || ' AND active_from_date <= SYSDATE '
2923 || ' AND ( active_to_date is NULL OR active_to_date > SYSDATE)'
2924 );
2925 */
2926 -- soagrawa 15-jan-2002
2927 -- check uniqueness replaced by cursor
2928 OPEN c_check_uniqueness_update(p_mediachl_rec.media_id,p_mediachl_rec.channel_id,p_mediachl_rec.media_channel_id);
2929 FETCH c_check_uniqueness_update INTO l_count;
2930 CLOSE c_check_uniqueness_update;
2931
2932 IF l_count = 0
2933 THEN
2934 l_valid_flag := FND_API.g_true;
2935 ELSE
2936 l_valid_flag := FND_API.g_false;
2937 END IF;
2938
2939 END IF;
2940 IF l_valid_flag = FND_API.g_false THEN
2941 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2942 FND_MESSAGE.set_name('AMS', 'AMS_MED_DUP_MEDIA_CHANNEL');
2943 FND_MSG_PUB.add;
2944 END IF;
2945 x_return_status := FND_API.g_ret_sts_error;
2946 RETURN;
2947 END IF;
2948 END Check_MediaChannel_UK_Items;
2949
2950
2951 ---------------------------------------------------------------------
2952 -- PROCEDURE
2953 -- Check_MediaChannel_FK_Items
2954 --
2955 ---------------------------------------------------------------------
2956 PROCEDURE Check_MediaChannel_FK_Items (
2957 p_mediachl_rec IN MediaChannel_Rec_Type,
2958 x_return_status OUT NOCOPY VARCHAR2
2959 )
2960 IS
2961 BEGIN
2962 x_return_status := FND_API.g_ret_sts_success;
2963
2964 --
2965 -- MEDIA_ID
2966 IF p_mediachl_rec.media_id <> FND_API.g_miss_num THEN
2967 IF AMS_Utility_PVT.check_fk_exists (
2968 'ams_media_b',
2969 'media_id',
2970 p_mediachl_rec.media_id
2971 ) = FND_API.g_false
2972 THEN
2973 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2974 FND_MESSAGE.set_name ('AMS', 'AMS_MED_BAD_MEDIA_ID');
2975 FND_MSG_PUB.add;
2976 END IF;
2977
2978 x_return_status := FND_API.g_ret_sts_error;
2979 RETURN;
2980 END IF;
2981 END IF;
2982
2983 --
2984 -- CHANNEL_ID
2985 IF p_mediachl_rec.channel_id <> FND_API.g_miss_num THEN
2986 IF AMS_Utility_PVT.check_fk_exists (
2987 'ams_channels_b',
2988 'channel_id',
2989 p_mediachl_rec.channel_id
2990 ) = FND_API.g_false
2991 THEN
2992 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
2993 FND_MESSAGE.set_name ('AMS', 'AMS_MED_BAD_CHANNEL_ID');
2994 FND_MSG_PUB.add;
2995 END IF;
2996
2997 x_return_status := FND_API.g_ret_sts_error;
2998 RETURN;
2999 END IF;
3000 END IF;
3001
3002
3003
3004 END Check_MediaChannel_FK_Items;
3005
3006
3007 ---------------------------------------------------------------------
3008 -- PROCEDURE
3009 -- Check_MediaChl_Lookup_Items
3010 --
3011 ---------------------------------------------------------------------
3012 PROCEDURE Check_MediaChl_Lookup_Items (
3013 p_mediachl_rec IN MediaChannel_Rec_Type,
3014 x_return_status OUT NOCOPY VARCHAR2
3015 )
3016 IS
3017 --
3018 -- No AMS_LOOKUPS references as of 04-Nov-1999.
3019 --
3020 BEGIN
3021 x_return_status := FND_API.g_ret_sts_success;
3022 END Check_MediaChl_Lookup_Items;
3023
3024
3025 ---------------------------------------------------------------------
3026 -- PROCEDURE
3027 -- Check_MediaChannel_Flag_Items
3028 --
3029 ---------------------------------------------------------------------
3030 PROCEDURE Check_MediaChannel_Flag_Items (
3031 p_mediachl_rec IN MediaChannel_Rec_Type,
3032 x_return_status OUT NOCOPY VARCHAR2
3033 )
3034 IS
3035 --
3036 -- No flags to validate as of 04-Nov-1999.
3037 --
3038 BEGIN
3039 x_return_status := FND_API.g_ret_sts_success;
3040 END Check_MediaChannel_Flag_Items;
3041
3042
3043 --
3044 -- PROCEDURE
3045 -- Check_MediaChannel_InterEntity
3046 PROCEDURE Check_MediaChannel_InterEntity (
3047 p_mediachl_rec IN MediaChannel_Rec_Type,
3048 p_complete_rec IN MediaChannel_Rec_Type := NULL,
3049 x_return_status OUT NOCOPY VARCHAR2
3050 )
3051 IS
3052 l_active_from_date DATE;
3053 l_active_to_date DATE;
3054
3055 CURSOR c_channel IS
3056 SELECT active_from_date,
3057 active_to_date
3058 FROM ams_channels_b
3059 WHERE channel_id = p_mediachl_rec.channel_id
3060 ;
3061 l_channel_rec c_channel%ROWTYPE;
3062 BEGIN
3063 x_return_status := FND_API.G_RET_STS_SUCCESS;
3064
3065 --
3066 -- Initialize media channel dates
3067 l_active_from_date := p_mediachl_rec.active_from_date;
3068 l_active_to_date := p_mediachl_rec.active_to_date;
3069
3070 OPEN c_channel;
3071 --
3072 -- Check_MediaChannel_FK_Items should have
3073 -- already taken care of existence of this
3074 -- channel, so check not needed.
3075 FETCH c_channel INTO l_channel_rec;
3076 CLOSE c_channel;
3077
3078 --
3079 -- Channel vs. Media Channel Validation
3080 -- Validate the active dates.
3081 -- Note: stack error messages into message buffer
3082 -- so all error messages returned in one time.
3083 IF l_active_from_date <> FND_API.g_miss_date OR l_active_to_date <> FND_API.g_miss_date THEN
3084 IF l_active_from_date IS NULL THEN
3085 l_active_from_date := p_complete_rec.active_from_date;
3086 END IF;
3087
3088 IF l_active_to_date IS NULL THEN
3089 l_active_to_date := p_complete_rec.active_to_date;
3090 END IF;
3091
3092 --
3093 -- Channel dates should not be NULL if
3094 -- media channel dates have a value.
3095 /***
3096 ISSUE: Do we need to check this even though
3097 from date is NOT NULL on channels table?
3098 ***/
3099 IF l_channel_rec.active_from_date IS NULL AND l_active_from_date IS NOT NULL THEN
3100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3101 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CFD_IS_NULL'); -- Channel From Date is NULL.
3102 FND_MSG_PUB.Add;
3103 END IF;
3104
3105 x_return_status := FND_API.g_ret_sts_error;
3106 END IF;
3107
3108 IF l_channel_rec.active_to_date IS NULL AND l_active_from_date IS NOT NULL THEN
3109 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3110 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CTD_IS_NULL'); -- Channel To Date is NULL.
3111 FND_MSG_PUB.Add;
3112 END IF;
3113
3114 x_return_status := FND_API.g_ret_sts_error;
3115 END IF;
3116
3117 --
3118 -- media channel's active from date should not
3119 -- be before channel's active from date.
3120 IF l_active_from_date < l_channel_rec.active_from_date THEN
3121 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3122 FND_MESSAGE.set_name ('AMS', 'AMS_MED_MFD_LT_CFD');
3123 FND_MSG_PUB.Add;
3124 END IF;
3125
3126 x_return_status := FND_API.g_ret_sts_error;
3127 END IF;
3128
3129 --
3130 -- media channel's active from date should not
3131 -- be after channel's active to date.
3132 IF l_active_from_date > l_channel_rec.active_to_date THEN
3133 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3134 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CFD_GT_MTD');
3135 FND_MSG_PUB.Add;
3136 END IF;
3137
3138 x_return_status := FND_API.g_ret_sts_error;
3139 END IF;
3140
3141 --
3142 -- media channel's active to date should not
3143 -- be after channel's active to date.
3144 IF l_active_to_date > l_channel_rec.active_to_date THEN
3145 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3146 FND_MESSAGE.set_name ('AMS', 'AMS_MED_CTD_GT_MTD');
3147 FND_MSG_PUB.Add;
3148 END IF;
3149
3150 x_return_status := FND_API.g_ret_sts_error;
3151 END IF;
3152
3153 END IF;
3154
3155 END Check_MediaChannel_InterEntity;
3156
3157
3158 END AMS_Media_PVT;