1 PACKAGE BODY IBE_DSPMGRVALIDATION_GRP AS
2 /* $Header: IBEGDVDB.pls 120.0 2005/05/30 03:13:59 appldev noship $ */
3
4 -----------------------------------------------------------------
5 -- NOTES
6 -- 1. Returns true if the attachment id exists
7 -- 2. Object version number is used if it is not FND_API.G_MISS_NUM
8 -- 3. Return false, if the attachment id does not exist,
9 -- IBE_DSP_ATH_NOT_EXISTS is pushed on the stack
10 ---------------------------------------------------------------------
11 FUNCTION check_attachment_exists(
12 p_attachment_id IN NUMBER,
13 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM)
14 RETURN BOOLEAN
15 IS
16
17 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_exists';
18
19 CURSOR attachment_cur( p_attachment_id IN NUMBER ) IS
20 -- SELECT attachment_used_by_id
21 SELECT 1
22 FROM JTF_AMV_ATTACHMENTS
23 WHERE attachment_id = p_attachment_id;
24
25 CURSOR attachment_version_cur( p_attachment_id IN NUMBER ,
26 p_object_version_number IN NUMBER) IS
27 -- SELECT attachment_used_by_id
28 SELECT 1
29 FROM JTF_AMV_ATTACHMENTS
30 WHERE attachment_id = p_attachment_id
31 AND object_version_number=p_object_version_number;
32
33 l_exists NUMBER;
34 l_return_status BOOLEAN := FALSE;
35 l_ath_not_exists_exception EXCEPTION;
36
37 BEGIN
38
39 IF (p_attachment_id IS NULL) OR (p_object_version_number IS NULL) THEN
40 RAISE l_ath_not_exists_exception;
41
42 ELSIF p_object_version_number = FND_API.G_MISS_NUM THEN
43 OPEN attachment_cur(p_attachment_id );
44 FETCH attachment_cur INTO l_exists;
45 IF attachment_cur%NOTFOUND THEN
46 CLOSE attachment_cur;
47 RAISE l_ath_not_exists_exception;
48 END IF;
49 CLOSE attachment_cur;
50 l_return_status := true;
51
52 ELSE
53 OPEN attachment_version_cur(p_attachment_id ,p_object_version_number);
54 FETCH attachment_version_cur INTO l_exists;
55 IF attachment_version_cur%NOTFOUND THEN
56 CLOSE attachment_version_cur;
57 RAISE l_ath_not_exists_exception;
58 END IF;
59 CLOSE attachment_version_cur;
60 l_return_status := true;
61
62 END IF;
63
64 RETURN l_return_status;
65
66 EXCEPTION
67
68 WHEN l_ath_not_exists_exception THEN
69 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
70 FND_MESSAGE.set_name('IBE','IBE_DSP_ATH_NOT_EXISTS');
71 FND_MESSAGE.set_token('ID', TO_CHAR(p_attachment_id));
72 FND_MSG_PUB.ADD;
73 END IF;
74 RETURN l_return_status;
75
76 WHEN OTHERS THEN
77 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
78 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
79 END IF;
80 RETURN l_return_status;
81
82 END check_attachment_exists;
83 ----------------------------------------------------------------------------------------
84 -----------------------------------------------------------------
85 -- NOTES
86 -- 1. Returns deliverable id for a attachment
87 -- 2. If deliverable id or attachment id not exists , return null
88 -- 3. Message IBE_DSP_ATH_NOT_EXISTS is pushed on the stack
89 ---------------------------------------------------------------------
90
91 FUNCTION check_attachment_deliverable(p_attachment_id IN NUMBER)
92 RETURN NUMBER
93 IS
94 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_deliverable';
95
96 CURSOR attachment_cur( p_attachment_id IN NUMBER ) IS
97 SELECT attachment_used_by_id FROM JTF_AMV_ATTACHMENTS
98 WHERE attachment_id = p_attachment_id;
99
100 l_deliverable_id NUMBER;
101 l_ath_not_exists_exception EXCEPTION;
102
103 BEGIN
104
105 IF p_attachment_id IS NULL THEN
106 RAISE l_ath_not_exists_exception;
107
108 ELSE
109 OPEN attachment_cur(p_attachment_id );
110 FETCH attachment_cur INTO l_deliverable_id;
111 IF attachment_cur%NOTFOUND THEN
112 CLOSE attachment_cur;
113 RAISE l_ath_not_exists_exception;
114 END IF;
115 CLOSE attachment_cur;
116
117 END IF;
118
119 RETURN l_deliverable_id;
120
121 EXCEPTION
122
123 WHEN l_ath_not_exists_exception THEN
124 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
125 FND_MESSAGE.set_name('IBE','IBE_DSP_ATH_NOT_EXISTS');
126 FND_MESSAGE.set_token('ID', TO_CHAR(p_attachment_id));
127 FND_MSG_PUB.ADD;
128 END IF;
129 RETURN NULL;
130
131 WHEN OTHERS THEN
132 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
133 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
134 END IF;
135 RETURN NULL;
136
137 END check_attachment_deliverable;
138
139 ----------------------------------------------------------------------------------------
140
141 -----------------------------------------------------------------
142 -- NOTES
143 -- 1. Returns the context type code for a given context id
144 -- 2. If the context_id is passed does not exist, null is returned
145 -- , and IBE_DSP_CONTEXT_NOT_EXISTS is pushed on the
146 -- message stack
147 ---------------------------------------------------------------------
148 FUNCTION check_context_type_code(p_context_id IN NUMBER)
149 RETURN VARCHAR2
150 IS
151 l_api_name CONSTANT VARCHAR2(40) := 'check_context_type_code';
152
153 CURSOR context_type_cur (p_context_id IN NUMBER) IS
154 SELECT context_type_code FROM IBE_DSP_CONTEXT_B WHERE
155 context_id = p_context_id ;
156
157 l_type_code VARCHAR2(40) := null;
158
159 BEGIN
160
161 OPEN context_type_cur(p_context_id );
162 FETCH context_type_cur INTO l_type_code;
163
164 IF context_type_cur%NOTFOUND
165 THEN
166 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_NOT_EXISTS');
167 FND_MESSAGE.set_token('ID', p_context_id);
168 FND_MSG_PUB.ADD;
169 END IF;
170 CLOSE context_type_cur;
171
172 return l_type_code;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
177 THEN
178 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
179 END IF;
180 return l_type_code;
181 END check_context_type_code;
182
183 ----------------------------------------------------------------------------------------
184
185 -----------------------------------------------------------------
186 -- NOTES
187 -- 1. Returns true if the context id with the context type exists
188 -- 2. Object version number is used if it is not FND_API.G_MISS_NUM
189 -- 3. If the context_id is passed does not exist, an exception is
190 -- raised , and IBE_DSP_CONTEXT_NOT_EXISTS is pushed on the
191 -- message stack
192 ---------------------------------------------------------------------
193 FUNCTION check_context_exists(p_context_id IN NUMBER,
194 p_context_type IN VARCHAR2,
195 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM)
196 RETURN boolean
197 IS
198 l_api_name CONSTANT VARCHAR2(40) := 'check_context_exists';
199
200 CURSOR context_cur( p_context_id IN NUMBER ,p_context_type IN VARCHAR2 ) IS
201 -- SELECT context_id
202 SELECT 1
203 FROM IBE_DSP_CONTEXT_B WHERE
204 context_id = p_context_id and context_type_code = p_context_type;
205
206 CURSOR context_version_cur( p_context_id IN NUMBER,p_context_type IN VARCHAR2 ,
207 p_object_version_number IN NUMBER) IS
208 -- SELECT context_id
209 SELECT 1
210 FROM IBE_DSP_CONTEXT_B WHERE
211 context_id = p_context_id and context_type_code = p_context_type
212 and object_version_number = p_object_version_number;
213
214 l_exists NUMBER;
215 l_return_status boolean := false;
216 l_context_type VARCHAR2(30);
217 BEGIN
218
219 l_context_type := trim(p_context_type);
220
221 if p_object_version_number = FND_API.G_MISS_NUM then
222 OPEN context_cur(p_context_id ,l_context_type);
223 FETCH context_cur INTO l_exists;
224 IF context_cur%NOTFOUND
225 THEN
226 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_NOT_EXISTS');
227 FND_MESSAGE.set_token('ID', p_context_id);
228 FND_MSG_PUB.ADD;
229 ELSE
230 l_return_status := true;
231 END IF;
232 CLOSE context_cur;
233 else
234 OPEN context_version_cur(p_context_id ,l_context_type, p_object_version_number);
235 FETCH context_version_cur INTO l_exists;
236 IF context_version_cur%NOTFOUND
237 THEN
238 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_NOT_EXISTS');
239 FND_MESSAGE.set_token('ID', p_context_id);
240 FND_MSG_PUB.ADD;
241 ELSE
242 l_return_status := true;
243 END IF;
244 CLOSE context_version_cur;
245 end if;
246 return l_return_status;
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
251 THEN
252 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
253 END IF;
254 return l_return_status;
255 END check_context_exists;
256 ----------------------------------------------------------------------------------------
257
258 -----------------------------------------------------------------
259 -- NOTES
260 -- 1. Returns true if the context type is valid (TEMPLATE/MEDIA)
261 -- 2. FND_LOOKUP used for context type is JTF_AMV_DELV_TYPE_CODE
262 -- 3. If the context type passed is not valid, an exception is
263 -- raised , and IBE_DSP_CONTEXT_TYPE_INVALID is pushed on the
264 -- message stack
265 ---------------------------------------------------------------------
266 FUNCTION check_valid_context_type(p_context_type in VARCHAR2)
267 RETURN boolean
268 IS
269 CURSOR valid_context_cur (p_context_type IN VARCHAR2) IS
270 select count(*) from FND_LOOKUP_VALUES_VL WHERE
271 lookup_type = 'JTF_AMV_DELV_TYPE_CODE' and
272 lookup_code = p_context_type and
273 enabled_flag = 'Y';
274
275 l_return_status boolean := false;
276 l_exists NUMBER := 0;
277 l_api_name CONSTANT VARCHAR2(40) := 'check_valid_context_type';
278 l_context_type VARCHAR2(30);
279 BEGIN
280 l_context_type := trim(p_context_type);
281 OPEN valid_context_cur(l_context_type);
282 FETCH valid_context_cur into l_exists;
283
284 IF valid_context_cur%NOTFOUND then
285 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_TYPE_INVLD');
286 FND_MESSAGE.set_token('TYPE', l_context_type);
287 FND_MESSAGE.set_token('ID', to_char(null));
288 FND_MSG_PUB.ADD;
289 l_return_status := false;
290 ELSE
291 l_return_status := true;
292 end if;
293
294 CLOSE valid_context_cur;
295 return l_return_status;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
300 THEN
301 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
302 END IF;
303 return l_return_status;
304 END check_valid_context_type;
305
306 ----------------------------------------------------------------------------------------
307
308 -----------------------------------------------------------------
309 -- NOTES
310 -- 1. Returns true if the object type is valid (S/I/C)
311 -- 2. FND_LOOKUP used for object type is
312 -- 3. If the object type passed is not valid, an exception is
313 -- raised , and IBE_DSP_OBJECT_TYPE_INVALID is pushed on the
314 -- message stack
315 ---------------------------------------------------------------------
316 FUNCTION check_valid_object_type(p_object_type_code in VARCHAR2)
317 RETURN boolean
318 IS
319
320 CURSOR valid_object_cur (p_object_type IN VARCHAR2) IS
321 -- select lookup_code
322 select 1
323 from FND_LOOKUP_VALUES_VL WHERE
324 lookup_type = 'IBE_RELATIONSHIP_RULE_OBJ_TYPE' and
325 lookup_code = p_object_type and
326 lookup_code <> 'N' and
327 enabled_flag = 'Y';
328
329 l_return_status boolean := false;
330 l_exists NUMBER := 0;
331 l_api_name CONSTANT VARCHAR2(40) := 'check_valid_object_type';
332 l_object_type_code VARCHAR2(30);
333 BEGIN
334 l_object_type_code := trim(p_object_type_code);
335
336 /*
337 if l_object_type_code = 'SECTION' or
338 l_object_type_code = 'ITEM' or
339 l_object_type_code = 'CATEGORY'
340 then
341 return true;
342 else
343 return false;
344 end if;
345 */
346
347 OPEN valid_object_cur(l_object_type_code);
348 FETCH valid_object_cur into l_exists;
349 IF valid_object_cur%NOTFOUND then
350 FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_OBJTYPE_INVLD');
351 FND_MESSAGE.set_token('TYPE', l_object_type_code);
352 FND_MSG_PUB.ADD;
353 l_return_status := false;
354 ELSE
355 l_return_status := true;
356 end if;
357 close valid_object_cur;
358 return l_return_status;
359
360 EXCEPTION
361 WHEN OTHERS THEN
362 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
363 THEN
364 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
365 END IF;
366 return l_return_status;
367 END check_valid_object_type;
368 ----------------------------------------------------------------------------------------
369
370 -----------------------------------------------------------------
371 -- NOTES
372 -- 1. Returns false if the context accessname is not being used
373 -- 2. If the context_id is passed, then the access name being
374 -- used is checked against access names other than the context
375 -- id passed
376 -- 3. If the context access name is being used, then it returns false
377 -- and IBE_DSP_CONTEXT_ACCNAME_EXISTS is pushed on the
378 -- message stack
379 ---------------------------------------------------------------------
380 FUNCTION check_context_accessname(p_context_accessname IN VARCHAR2,
381 p_context_type IN VARCHAR2,
382 p_context_id IN NUMBER := FND_API.G_MISS_NUM)
383 RETURN boolean
384 IS
385 l_api_name CONSTANT VARCHAR2(40) := 'check_context_accessname';
386 l_context_accessname VARCHAR2(40);
387 l_context_type VARCHAR2(30);
388 CURSOR context_accessname_cur( p_context_accessname IN VARCHAR2 ,
389 p_context_type IN VARCHAR2)
390 IS
391 -- SELECT access_name
392 SELECT 1
393 FROM IBE_DSP_CONTEXT_B WHERE
394 access_name = p_context_accessname ;
395
396 CURSOR context_accessname_id_cur( p_context_accessname IN VARCHAR2 ,
397 p_context_type IN VARCHAR2,
398 p_context_id IN VARCHAR2)
399 IS
400 -- SELECT access_name
401 SELECT 1
402 FROM IBE_DSP_CONTEXT_B WHERE
403 access_name = p_context_accessname and
404 context_id <> p_context_id ;
405 l_exists NUMBER;
406 l_return_status boolean := true;
407
408 BEGIN
409
410 l_context_accessname := trim(p_context_accessname);
411 l_context_type := trim(p_context_type);
412
413 if p_context_id = FND_API.G_MISS_NUM or p_context_id is null then
414 OPEN context_accessname_cur(l_context_accessname ,l_context_type);
415 FETCH context_accessname_cur INTO l_exists;
416 IF context_accessname_cur%FOUND
417 THEN
418 l_return_status := FALSE;
419 END IF;
420 CLOSE context_accessname_cur;
421 else
422 OPEN context_accessname_id_cur(l_context_accessname ,l_context_type,p_context_id);
423 FETCH context_accessname_id_cur INTO l_exists;
424 IF context_accessname_id_cur%FOUND
425 THEN
426 l_return_status := false;
427 END IF;
428 CLOSE context_accessname_id_cur;
429 end if;
430
431 if not l_return_status then
432 FND_MESSAGE.set_name('IBE','IBE_DSP_CONTEXT_ACCNAME_EXISTS');
433 FND_MESSAGE.set_token('ACC_NAME', l_context_accessname);
434 FND_MSG_PUB.ADD;
435 end if;
436
437 return l_return_status;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
442 THEN
443 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
444 END IF;
445 return l_return_status;
446 END check_context_accessname;
447 ----------------------------------------------------------------------------------------
448
449 -----------------------------------------------------------------
450 -- NOTES
451 -- 1. Returns false if there is no association for a
452 -- deliverable id/ category id in IBE_DSP_TPL_CTG
453 -- 2. No message is pushed on the stack
454 ---------------------------------------------------------------------
455 FUNCTION check_ctg_tpl_relation_exists(p_category_id IN NUMBER,
456 p_template_id IN NUMBER)
457 RETURN boolean
458 IS
459 l_api_name CONSTANT VARCHAR2(40) := 'check_ctg_tpl_relation_exists';
460
461 CURSOR ctg_tpl_relation_cur( p_category_id IN NUMBER,p_template_id IN NUMBER ) IS
462 -- select tpl_ctg_id
463 select 1
464 from ibe_dsp_tpl_ctg
465 where category_id = p_category_id and item_id = p_template_id;
466
467 l_exists NUMBER;
468 l_return_status boolean := false;
469
470 BEGIN
471 OPEN ctg_tpl_relation_cur(p_category_id,p_template_id );
472 FETCH ctg_tpl_relation_cur INTO l_exists;
473 IF ctg_tpl_relation_cur%FOUND
474 THEN
475 l_return_status := true;
476 END IF;
477 CLOSE ctg_tpl_relation_cur;
478
479 return l_return_status;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
484 THEN
485 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
486 END IF;
487 return l_return_status;
488 END check_ctg_tpl_relation_exists;
489
490 ----------------------------------------------------------------------------------------
491
492 -----------------------------------------------------------------
493 -- NOTES
494 -- 1. Returns true if the category id does exist
495 -- 2. Return false, if the category id does not exist,
496 -- IBE_DSP_CATEGORY_NOT_EXISTS is pushed on the stack
497 ---------------------------------------------------------------------
498 FUNCTION check_category_exists(p_category_id IN NUMBER)
499 RETURN boolean
500 IS
501 l_api_name CONSTANT VARCHAR2(40) := 'check_category_exists';
502
503 CURSOR category_cur( p_category_id IN NUMBER ) IS
504 -- select category_id
505 select 1
506 from mtl_categories
507 where category_id=p_category_id;
508
509 l_exists NUMBER;
510 l_return_status boolean := false;
511
512 BEGIN
513
514 OPEN category_cur(p_category_id );
515 FETCH category_cur INTO l_exists;
516 IF category_cur%NOTFOUND
517 THEN
518 FND_MESSAGE.set_name('IBE','IBE_DSP_CATEGORY_NOT_EXISTS');
519 FND_MESSAGE.set_token('ID',p_category_id);
520 FND_MSG_PUB.ADD;
521 ELSE
522 l_return_status := true;
523 END IF;
524 CLOSE category_cur;
525
526 return l_return_status;
527
528 EXCEPTION
529 WHEN OTHERS THEN
530 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
531 THEN
532 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
533 END IF;
534 return l_return_status;
535 END check_category_exists;
536
537 ----------------------------------------------------------------------------------------
538
539 -----------------------------------------------------------------
540 -- NOTES
541 -- 1. Returns true if the item id does exist
542 -- 2. Return false, if the item id does not exist,
543 -- IBE_DSP_ITEM_NOT_EXISTS is pushed on the stack
544 ---------------------------------------------------------------------
545 FUNCTION check_item_exists(p_item_id IN NUMBER)
546 RETURN boolean
547 IS
548 l_api_name CONSTANT VARCHAR2(40) := 'check_item_exists';
549
550 CURSOR item_cur( p_item_id IN NUMBER ) IS
551 -- select distinct inventory_item_id
552 select 1
553 from mtl_system_items
554 where inventory_item_id = p_item_id;
555
556 l_exists NUMBER;
557 l_return_status boolean := false;
558
559 BEGIN
560
561 OPEN item_cur(p_item_id );
562 FETCH item_cur INTO l_exists;
563 IF item_cur%NOTFOUND
564 THEN
565 FND_MESSAGE.set_name('IBE','IBE_DSP_ITEM_NOT_EXISTS');
566 FND_MESSAGE.set_token('ID',p_item_id);
567 FND_MSG_PUB.ADD;
568 ELSE
569 l_return_status := true;
570 END IF;
571 CLOSE item_cur;
572
573 return l_return_status;
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
578 THEN
579 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
580 END IF;
581 return l_return_status;
582 END check_item_exists;
583
584 ----------------------------------------------------------------------------------------
585
586 -----------------------------------------------------------------
587 -- NOTES
588 -- 1. Returns true if the section id does exist
589 -- 2. Return false, if the section id does not exist,
590 -- IBE_DSP_SECTION_NOT_EXISTS is pushed on the stack
591 ---------------------------------------------------------------------
592 FUNCTION check_section_exists(p_section_id IN NUMBER)
593 RETURN boolean
594 IS
595 l_api_name CONSTANT VARCHAR2(40) := 'check_section_exists';
596
597 CURSOR section_cur( p_section_id IN NUMBER ) IS
598 -- select section_id
599 select 1
600 from ibe_dsp_sections_b where
601 section_id = p_section_id;
602 l_exists NUMBER;
603 l_return_status boolean := false;
604
605 BEGIN
606
607 OPEN section_cur(p_section_id );
608 FETCH section_cur INTO l_exists;
609 IF section_cur%NOTFOUND
610 THEN
611 FND_MESSAGE.set_name('IBE','IBE_DSP_SECTION_NOT_EXISTS');
612 FND_MESSAGE.set_token('ID',p_section_id);
613
614 FND_MSG_PUB.ADD;
615 ELSE
616 l_return_status := true;
617 END IF;
618 CLOSE section_cur;
619
620 return l_return_status;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
625 THEN
626 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
627 END IF;
628 ---dbms_output.put_line('returning false status error');
629 return l_return_status;
630 END check_section_exists;
631
632 -----------------------------------------------------------------
633 -- NOTES
634 -- 1. Returns true if the section id does exist
635 -- 2. Return false, if the section id does not exist,
636 -- IBE_MSITE_RSECID_INVLD is pushed on the stack
637 ---------------------------------------------------------------------
638 FUNCTION check_root_section_exists(p_root_section_id IN NUMBER)
639 RETURN boolean
640 IS
641 l_api_name CONSTANT VARCHAR2(40) := 'check_root_section_exists';
642
643 CURSOR section_cur( p_section_id IN NUMBER ) IS
644 -- select section_id
645 select 1
646 from ibe_dsp_sections_b where
647 section_id = p_section_id ;
648 l_exists NUMBER;
649 l_return_status boolean := false;
650 BEGIN
651 OPEN section_cur(p_root_section_id );
652 FETCH section_cur INTO l_exists;
653 IF section_cur%NOTFOUND
654 THEN
655 FND_MESSAGE.set_name('IBE','IBE_MSITE_RSECID_INVLD');
656 FND_MESSAGE.set_token('ID',p_root_section_id);
657 FND_MSG_PUB.ADD;
658 ELSE
659 l_return_status := true;
660 END IF;
661 CLOSE section_cur;
662
663 return l_return_status;
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
668 THEN
669 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
670 END IF;
671 ---dbms_output.put_line('returning false status error');
672 return l_return_status;
673 END check_root_section_exists;
674
675
676 ----------------------------------------------------------------------------------------
677
678 -----------------------------------------------------------------
679 -- NOTES
680 -- 1. Returns true if the object id with the right type does exist
681 ---------------------------------------------------------------------
682 FUNCTION check_lgl_object_exists(p_object_type IN VARCHAR2,
683 p_object_id IN NUMBER )
684 RETURN boolean
685 IS
686 l_api_name CONSTANT VARCHAR2(40) := 'check_lgl_object_exists';
687
688 l_exists NUMBER;
689 l_return_status boolean := false;
690 l_object_type VARCHAR2(30);
691 BEGIN
692
693 l_object_type := trim(p_object_type);
694 l_return_status := ibe_dspmgrvalidation_grp.check_valid_object_type(l_object_type);
695
696 if l_return_status = false then
697 return l_return_status;
698 end if;
699
700 l_return_status := false;
701
702 if l_object_type = 'I'
703 then
704 l_return_status := ibe_dspmgrvalidation_grp.check_item_exists(p_object_id);
705 elsif l_object_type = 'C'
706 then
707 l_return_status := ibe_dspmgrvalidation_grp.check_category_exists(p_object_id);
708 elsif l_object_type = 'S'
709 then
710 l_return_status := ibe_dspmgrvalidation_grp.check_section_exists(p_object_id);
711 end if;
712
713 return l_return_status;
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
718 THEN
719 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
720 END IF;
721 return l_return_status;
722 END check_lgl_object_exists;
723
724
725 ----------------------------------------------------------------------------------------
726 -----------------------------------------------------------------
727 -- NOTES
728 -- 1. Returns true if the deliverable with type matches
729 -- valid types are TEMPLATE/MEDIA
730 --- 2. applicable to ,is used if passed
731 -- 3. If not found ,returns false and message IBE_DSP_DLV_TYPE_NOT_EXISTS
732 -- is pushed onto the stack
733 -- 4. Modified for bug 2454428 to include 'GENERIC'
734 -- 5. Modified by YAXU on 12/19/2002, do not check the applicable_to for 'MEDIA'
735 ---------------------------------------------------------------------
736 FUNCTION check_deliverable_type_exists(
737 p_deliverable_id IN NUMBER,
738 p_item_type IN VARCHAR2,
739 p_applicable_to IN VARCHAR2 := FND_API.g_miss_char)
740 RETURN BOOLEAN
741 IS
742 l_api_name CONSTANT VARCHAR2(40) := 'check_deliverable_type_exists';
743 l_exists NUMBER;
744 l_item_type VARCHAR2(40);
745 CURSOR deliverable_type_cur( p_deliverable_id IN NUMBER,
746 p_item_type IN VARCHAR2 ) IS
747 -- SELECT item_id
748 SELECT 1
749 FROM JTF_AMV_ITEMS_B
750 WHERE item_id = p_deliverable_id and
751 deliverable_type_code = p_item_type ;
752
753 CURSOR deliverable_type_app_cur( p_deliverable_id IN NUMBER,
754 p_item_type IN VARCHAR2, p_applicable_to IN VARCHAR2 ) IS
755 -- SELECT item_id
756 SELECT 1
757 FROM JTF_AMV_ITEMS_B
758 WHERE item_id = p_deliverable_id and
759 deliverable_type_code = p_item_type and
760 (applicable_to_code = p_applicable_to OR
761 applicable_to_code = 'GENERIC');
762
763 l_return_status BOOLEAN := false;
764
765 BEGIN
766
767 l_item_type := trim(p_item_type);
768
769 --Modified by YAXU on 12/19/2002, do not check the applicable_to for 'MEDIA'
770 if p_applicable_to is null or p_applicable_to = FND_API.g_miss_char or p_item_type = 'MEDIA'
771 then
772 OPEN deliverable_type_cur(p_deliverable_id,l_item_type);
773 FETCH deliverable_type_cur INTO l_exists;
774 IF deliverable_type_cur%FOUND THEN
775 l_return_status := true;
776 end if;
777 CLOSE deliverable_type_cur;
778 else
779 OPEN deliverable_type_app_cur(p_deliverable_id,l_item_type,p_applicable_to);
780 FETCH deliverable_type_app_cur INTO l_exists;
781 IF deliverable_type_app_cur%FOUND THEN
782 l_return_status := true;
783 end if;
784
785 CLOSE deliverable_type_app_cur;
786 end if;
787
788 if l_return_status = false then
789 FND_MESSAGE.set_name('IBE','IBE_DSP_DLV_TYPE_NOT_EXISTS');
790 FND_MESSAGE.set_token('ID', p_deliverable_id);
791 FND_MESSAGE.set_token('TYPE', p_item_type);
792 FND_MSG_PUB.add;
793 end if;
794
795 return l_return_status;
796
797 EXCEPTION
798
799 WHEN OTHERS THEN
800 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
801 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
802 END IF;
803 return l_return_status;
804
805 END check_deliverable_type_exists;
806
807 ----------------------------------------------------------------------------------------
808 -----------------------------------------------------------------
809 -- NOTES
810 -- 1. Returns true if the deliverable id exists
811 -- 2. If object version number is passed, then the deliverable id
812 -- with object version number is checked for existence
813 -- 3. If deliverable id in both cases if not found
814 -- message IBE_DSP_DLV_NOT_EXISTS is pushed on the stack
815 ---------------------------------------------------------------------
816
817 FUNCTION check_deliverable_exists(
818 p_deliverable_id IN NUMBER,
819 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM)
820 RETURN boolean
821 IS
822 l_api_name CONSTANT VARCHAR2(40) := 'check_deliverable_exists';
823
824 CURSOR deliverable_cur( p_deliverable_id IN NUMBER ) IS
825 -- SELECT item_id
826 SELECT 1
827 FROM JTF_AMV_ITEMS_B
828 WHERE item_id = p_deliverable_id;
829
830 CURSOR deliverable_version_cur( p_deliverable_id IN NUMBER,
831 p_object_version_number IN NUMBER ) IS
832 -- SELECT item_id
833 SELECT 1
834 FROM JTF_AMV_ITEMS_B
835 WHERE item_id = p_deliverable_id and
836 object_version_number = p_object_version_number;
837
838 l_exists NUMBER;
839 l_return_status boolean := false;
840 l_dlv_not_exists_exception EXCEPTION;
841
842 BEGIN
843
844 IF (p_deliverable_id IS NULL) OR (p_object_version_number IS NULL) THEN
845 RAISE l_dlv_not_exists_exception;
846
847 ELSIF p_object_version_number = FND_API.G_MISS_NUM then
848 OPEN deliverable_cur(p_deliverable_id );
849 FETCH deliverable_cur INTO l_exists;
850 IF deliverable_cur%NOTFOUND THEN
851 CLOSE deliverable_cur;
852 RAISE l_dlv_not_exists_exception;
853 END IF;
854 CLOSE deliverable_cur;
855 l_return_status := true;
856
857 ELSE
858 OPEN deliverable_version_cur(p_deliverable_id,
859 p_object_version_number);
860 FETCH deliverable_version_cur INTO l_exists;
861 IF deliverable_version_cur%NOTFOUND THEN
862 CLOSE deliverable_version_cur;
863 RAISE l_dlv_not_exists_exception;
864 END IF;
865 CLOSE deliverable_version_cur;
866 l_return_status := true;
867
868 END IF;
869
870 return l_return_status;
871
872 EXCEPTION
873
874 WHEN l_dlv_not_exists_exception THEN
875 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
876 FND_MESSAGE.set_name('IBE','IBE_DSP_DLV_NOT_EXISTS');
877 FND_MESSAGE.set_token('ID', TO_CHAR(p_deliverable_id));
878 FND_MSG_PUB.add;
879 END IF;
880 RETURN l_return_status;
881
882 WHEN OTHERS THEN
883 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
884 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
885 END IF;
886 return l_return_status;
887
888 END check_deliverable_exists;
889
890 ----------------------------------------------------------------------------------------
891 -----------------------------------------------------------------
892 -- NOTES
893 -- 1. Returns true if the logical content id exists
894 -- 2. If object version number is passed, then the logical content id
895 -- with object version number is checked for existence
896 -- 3. If logical content id in both cases is not found
897 -- message IBE_DSP_LGL_CTNT_ID_NOT_EXISTS is pushed on the stack
898 ---------------------------------------------------------------------
899
900 FUNCTION check_lgl_ctnt_id_exists(p_lgl_ctnt_id IN NUMBER,
901 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM)
902 RETURN boolean
903 IS
904 l_api_name CONSTANT VARCHAR2(40) := 'check_lgl_ctnt_id_exists';
905
906 CURSOR lgl_ctnt_cur( p_lgl_ctnt_id IN NUMBER ) IS
907 -- SELECT obj_lgl_ctnt_id
908 SELECT 1
909 FROM IBE_DSP_OBJ_LGL_CTNT
910 WHERE obj_lgl_ctnt_id = p_lgl_ctnt_id;
911
912 CURSOR lgl_ctnt_version_cur( p_lgl_ctnt_id IN NUMBER,
913 p_object_version_number IN NUMBER ) IS
914 -- select obj_lgl_ctnt_id
915 select 1
916 FROM IBE_DSP_OBJ_LGL_CTNT
917 WHERE obj_lgl_ctnt_id = p_lgl_ctnt_id and
918 object_version_number = p_object_version_number;
919
920 l_exists NUMBER;
921 l_return_status boolean := false;
922
923 BEGIN
924 if p_object_version_number = FND_API.G_MISS_NUM then
925 OPEN lgl_ctnt_cur(p_lgl_ctnt_id );
926 FETCH lgl_ctnt_cur INTO l_exists;
927 IF lgl_ctnt_cur%NOTFOUND
928 THEN
929 FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_ID_NOT_EXISTS');
930 FND_MESSAGE.set_token('ID',p_lgl_ctnt_id);
931 FND_MSG_PUB.ADD;
932 ELSE
933 l_return_status := true;
934 END IF;
935 CLOSE lgl_ctnt_cur;
936 else
937 OPEN lgl_ctnt_version_cur(p_lgl_ctnt_id ,p_object_version_number);
938 FETCH lgl_ctnt_version_cur INTO l_exists;
939 IF lgl_ctnt_version_cur%NOTFOUND
940 THEN
941 FND_MESSAGE.set_name('IBE','IBE_DSP_LGLCTNT_ID_NOT_EXISTS');
942 FND_MESSAGE.set_token('ID',p_lgl_ctnt_id);
943 FND_MSG_PUB.ADD;
944 ELSE
945 l_return_status := true;
946 END IF;
947 CLOSE lgl_ctnt_version_cur;
948
949 end if;
950 return l_return_status;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
955 THEN
956 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
957 END IF;
958 return l_return_status;
959 END check_lgl_ctnt_id_exists;
960
961 ----------------------------------------------------------------------------------------
962 -----------------------------------------------------------------
963 -- NOTES
964 -- 1. Returns true if the filename associated with the attachment
965 -- is not null and unique
966 -- 2. If file name is null or missing message IBE_DSP_ATH_FILENAME_REQ
967 -- is pushed on the stack
968 -- 3. If file name already exists , message IBE_DSP_ATH_FILENAME_EXISTS
969 -- is pushed on the stack
970 ---------------------------------------------------------------------
971
972 FUNCTION check_attachment_filename(
973 p_attachment_id IN NUMBER,
974 p_file_name IN varchar2)
975 RETURN BOOLEAN
976 IS
977 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_filename';
978 l_return_status BOOLEAN := FALSE;
979 l_exists NUMBER;
980
981 CURSOR filename_cur( p_file_name IN VARCHAR2 ) IS
982 -- SELECT file_name
983 SELECT 1
984 FROM JTF_AMV_ATTACHMENTS
985 WHERE file_name = p_file_name;
986
987 CURSOR attachment_filename_cur(p_attachment_id IN NUMBER,
988 p_file_name IN VARCHAR2) IS
989 -- SELECT file_name
990 SELECT 1
991 FROM JTF_AMV_ATTACHMENTS
992 WHERE file_name = p_file_name and attachment_id <> p_attachment_id;
993
994 l_dup_file_exception EXCEPTION;
995
996 BEGIN
997
998 IF TRIM(p_file_name) IS NULL THEN
999 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1000 FND_MESSAGE.set_name('IBE', 'IBE_DSP_ATH_FILENAME_REQ');
1001 FND_MSG_PUB.add;
1002 END IF;
1003
1004 ELSE
1005 IF p_attachment_id IS NOT NULL THEN
1006 OPEN attachment_filename_cur(p_attachment_id, p_file_name);
1007 FETCH attachment_filename_cur INTO l_exists;
1008 IF attachment_filename_cur%FOUND THEN
1009 CLOSE attachment_filename_cur;
1010 RAISE l_dup_file_exception;
1011 END IF;
1012 CLOSE attachment_filename_cur;
1013 ELSE
1014 OPEN filename_cur(p_file_name);
1015 FETCH filename_cur INTO l_exists;
1016 IF filename_cur%FOUND THEN
1017 CLOSE filename_cur;
1018 RAISE l_dup_file_exception;
1019 END IF;
1020 CLOSE filename_cur;
1021 END IF;
1022
1023 l_return_status := TRUE;
1024
1025 END IF;
1026
1027 RETURN l_return_status;
1028
1029 EXCEPTION
1030
1031 WHEN l_dup_file_exception THEN
1032 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1033 FND_MESSAGE.set_name('IBE','IBE_DSP_ATH_FILENAME_EXISTS');
1034 FND_MESSAGE.set_token('FILE_NAME', p_file_name);
1035 FND_MSG_PUB.ADD;
1036 END IF;
1037 RETURN l_return_status;
1038
1039 WHEN OTHERS THEN
1040 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1041 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1042 END IF;
1043 RETURN l_return_status;
1044
1045 END check_attachment_filename;
1046
1047 ----------------------------------------------------------------------------------------
1048 -----------------------------------------------------------------
1049 -- NOTES
1050 -- 1. Returns true if the minisite exists
1051 -- 2. If not, message IBE_MSITE_NOT_EXISTS is pushed on the stack
1052 ---------------------------------------------------------------------
1053
1054 FUNCTION check_msite_exists(
1055 p_msite_id IN NUMBER,
1056 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM)
1057 RETURN boolean
1058 IS
1059 l_api_name CONSTANT VARCHAR2(40) := 'check_msite_exists';
1060 l_return_status boolean := false;
1061 l_exists NUMBER;
1062
1063 CURSOR msite_cur( p_msite_id IN NUMBER ) IS
1064 -- SELECT msite_id
1065 SELECT 1
1066 FROM IBE_MSITES_B where msite_id = p_msite_id and site_type = 'I';
1067 -- and
1068 -- end_date_active is null);
1069 --- sysdate between start_date_active and nvl(end_date_active,sysdate));
1070
1071 CURSOR msite_version_cur( p_msite_id IN NUMBER,
1072 p_object_version_number IN NUMBER ) IS
1073 -- SELECT msite_id
1074 SELECT 1
1075 FROM IBE_MSITES_B
1076 WHERE msite_id = p_msite_id
1077 and object_version_number = p_object_version_number and site_type = 'I';
1078 -- AND
1079 -- end_date_active is null);
1080 ---- sysdate between start_date_active and nvl(end_date_active,sysdate));
1081
1082
1083 l_msite_not_exists_exception EXCEPTION;
1084
1085 BEGIN
1086
1087 if (p_msite_id IS NULL) OR (p_object_version_number IS NULL) THEN
1088 RAISE l_msite_not_exists_exception;
1089
1090 elsif p_object_version_number = FND_API.G_MISS_NUM then
1091 OPEN msite_cur(p_msite_id );
1092 FETCH msite_cur INTO l_exists;
1093 IF msite_cur%NOTFOUND THEN
1094 CLOSE msite_cur;
1095 RAISE l_msite_not_exists_exception;
1096 END IF;
1097 close msite_cur;
1098 l_return_status := true;
1099
1100 else
1101 OPEN msite_version_cur(p_msite_id ,p_object_version_number);
1102 FETCH msite_version_cur INTO l_exists;
1103 IF msite_version_cur%NOTFOUND THEN
1104 CLOSE msite_version_cur;
1105 RAISE l_msite_not_exists_exception;
1106 END IF;
1107 CLOSE msite_version_cur;
1108 l_return_status := true;
1109
1110 END IF;
1111
1112 return l_return_status;
1113
1114 EXCEPTION
1115
1116 WHEN l_msite_not_exists_exception THEN
1117 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1118 FND_MESSAGE.set_name('IBE', 'IBE_MSITE_NOT_EXISTS');
1119 FND_MESSAGE.set_token('ID', TO_CHAR(p_msite_id));
1120 FND_MSG_PUB.add;
1121 END IF;
1122 RETURN l_return_status;
1123
1124 WHEN OTHERS THEN
1125 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1126 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1127 END IF;
1128 return l_return_status;
1129
1130 END check_msite_exists;
1131
1132 ----------------------------------------------------------------------------------------
1133 -----------------------------------------------------------------
1134 -- NOTES
1135 -- 1. Returns true if lgl_phys_map_id (IBE_DSP_LGL_PHYS_MAP) exists
1136 -- 2. If not , message IBE_DSP_PHYSMAP_NOT_EXISTS is pushed on the stack
1137 ---------------------------------------------------------------------
1138 FUNCTION check_physicalmap_exists(p_lgl_phys_map_id IN NUMBER)
1139 RETURN boolean
1140 IS
1141 l_api_name CONSTANT VARCHAR2(40) := 'check_physicalmap_exists';
1142 l_return_status boolean := false;
1143 l_exists NUMBER;
1144
1145 CURSOR lgl_phys_map_cur( p_lgl_phys_map_id IN NUMBER ) IS
1146 -- SELECT lgl_phys_map_id
1147 SELECT 1
1148 FROM IBE_DSP_LGL_PHYS_MAP
1149 where lgl_phys_map_id = p_lgl_phys_map_id;
1150
1151 l_map_not_exists_exception EXCEPTION;
1152
1153 BEGIN
1154
1155 IF p_lgl_phys_map_id IS NULL THEN
1156 RAISE l_map_not_exists_exception;
1157
1158 ELSE
1159 OPEN lgl_phys_map_cur(p_lgl_phys_map_id );
1160 FETCH lgl_phys_map_cur INTO l_exists;
1161 IF lgl_phys_map_cur%NOTFOUND THEN
1162 CLOSE lgl_phys_map_cur;
1163 RAISE l_map_not_exists_exception;
1164 END IF;
1165 CLOSE lgl_phys_map_cur;
1166 l_return_status := true;
1167
1168 END IF;
1169
1170 return l_return_status;
1171
1172 EXCEPTION
1173
1174 WHEN l_map_not_exists_exception THEN
1175 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1176 FND_MESSAGE.set_name('IBE', 'IBE_DSP_PHYSMAP_NOT_EXISTS');
1177 FND_MESSAGE.set_token('ID', TO_CHAR(p_lgl_phys_map_id));
1178 FND_MSG_PUB.add;
1179 END IF;
1180 RETURN l_return_status;
1181
1182 WHEN OTHERS THEN
1183 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1184 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1185 END IF;
1186 return l_return_status;
1187
1188 END check_physicalmap_exists;
1189
1190 ----------------------------------------------------------------------------------------
1191 -----------------------------------------------------------------
1192 -- NOTES
1193 -- 1. Returns true if the deliverable with accessname exists and
1194 -- is not null and unique
1195 -- 2. If access name is null, message IBE_DSP_DLV_ACCNAME_REQ
1196 -- is pushed on the stack
1197 -- 3. If access name already exists , message IBE_DSP_DLV_ACCNAME_EXISTS
1198 -- is pushed on the stack
1199 ---------------------------------------------------------------------
1200
1201 FUNCTION check_deliverable_accessname(
1202 p_deliverable_id IN NUMBER,
1203 p_access_name IN varchar2)
1204 RETURN BOOLEAN
1205 IS
1206 l_api_name CONSTANT VARCHAR2(40) := 'check_deliverable_accessname';
1207 l_return_status boolean := false;
1208 l_exists NUMBER;
1209
1210 CURSOR accessname_cur(p_access_name IN VARCHAR2 ) IS
1211 -- SELECT access_name
1212 SELECT 1
1213 FROM JTF_AMV_ITEMS_B
1214 WHERE access_name = p_access_name;
1215
1216 CURSOR deliverable_accessname_cur(p_item_id IN NUMBER,
1217 p_access_name IN VARCHAR2 ) IS
1218 -- SELECT access_name
1219 SELECT 1
1220 FROM JTF_AMV_ITEMS_B
1221 WHERE access_name = p_access_name AND item_id <> p_item_id;
1222
1223 l_dup_access_exception EXCEPTION;
1224
1225 BEGIN
1226
1227 IF TRIM(p_access_name) IS NULL THEN
1228 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1229 FND_MESSAGE.set_name('IBE', 'IBE_DSP_DLV_ACCNAME_REQ');
1230 FND_MSG_PUB.add;
1231 END IF;
1232
1233 ELSE
1234 IF p_deliverable_id IS NOT NULL THEN
1235 -- update
1236 OPEN deliverable_accessname_cur(p_deliverable_id, p_access_name);
1237 FETCH deliverable_accessname_cur INTO l_exists;
1238 IF deliverable_accessname_cur%FOUND THEN
1239 CLOSE deliverable_accessname_cur;
1240 RAISE l_dup_access_exception;
1241 END IF;
1242 CLOSE deliverable_accessname_cur;
1243 ELSE
1244 OPEN accessname_cur(p_access_name);
1245 FETCH accessname_cur INTO l_exists;
1246 IF accessname_cur%FOUND THEN
1247 CLOSE accessname_cur;
1248 RAISE l_dup_access_exception;
1249 END IF;
1250 CLOSE accessname_cur;
1251 END IF;
1252
1253 l_return_status := TRUE;
1254
1255 END IF;
1256
1257 RETURN l_return_status;
1258
1259 EXCEPTION
1260
1261 WHEN l_dup_access_exception THEN
1262 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1263 FND_MESSAGE.set_name('IBE', 'IBE_DSP_DLV_ACCNAME_EXISTS');
1264 FND_MESSAGE.set_token('ACC_NAME', p_access_name);
1265 FND_MSG_PUB.add;
1266 END IF;
1267 RETURN l_return_status;
1268
1269 WHEN OTHERS THEN
1270 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1271 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1272 END IF;
1273 RETURN l_return_status;
1274
1275 END check_deliverable_accessname;
1276
1277
1278 ----------------------------------------------------------------------------------------
1279 -----------------------------------------------------------------
1280 -- NOTES
1281 -- 1. Returns true if the language is supported by the minisite
1282 -- 2. If not, message IBE_MSITE_LANG_NOT_SUPPORTED
1283 -- is pushed on the stack
1284 ---------------------------------------------------------------------
1285
1286 FUNCTION check_language_supported(
1287 p_msite_id IN NUMBER,
1288 p_language_code in varchar2 )
1289 RETURN boolean
1290 IS
1291 l_api_name CONSTANT VARCHAR2(40) := 'check_language_supported';
1292 l_return_status boolean := false;
1293 l_exists NUMBER;
1294
1295 CURSOR msite_lang_cur( p_msite_id IN NUMBER, p_language_code IN VARCHAR2 ) IS
1296 -- SELECT b.language_code
1297 SELECT 1 FROM
1298 IBE_MSITES_B A, IBE_MSITE_LANGUAGES B where
1299 A.msite_id = p_msite_id and
1300 B.msite_id = A.msite_id and
1301 A.site_type = 'I' and
1302 B.language_code = p_language_code ;
1303
1304 l_lang_not_supp_exception EXCEPTION;
1305
1306 BEGIN
1307
1308 IF (p_msite_id IS NULL) OR (p_language_code IS NULL) THEN
1309 RAISE l_lang_not_supp_exception;
1310 ELSE
1311 OPEN msite_lang_cur(p_msite_id,p_language_code );
1312 FETCH msite_lang_cur INTO l_exists;
1313 IF msite_lang_cur%NOTFOUND THEN
1314 CLOSE msite_lang_cur;
1315 RAISE l_lang_not_supp_exception;
1316 END IF;
1317 CLOSE msite_lang_cur;
1318 l_return_status := true;
1319 END IF;
1320
1321 return l_return_status;
1322
1323 EXCEPTION
1324
1325 WHEN l_lang_not_supp_exception THEN
1326 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1327 FND_MESSAGE.set_name('IBE', 'IBE_MSITE_LANG_NOT_SUPP' );
1328 FND_MESSAGE.set_token('ID', TO_CHAR(p_msite_id));
1329 FND_MESSAGE.set_token('LANG', p_language_code);
1330 FND_MSG_PUB.add;
1331 END IF;
1332 return l_return_status;
1333
1334 WHEN OTHERS THEN
1335 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1336 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1337 END IF;
1338 return l_return_status;
1339
1340 END check_language_supported;
1341
1342 FUNCTION check_item_deliverable(p_item_id IN NUMBER,
1343 p_deliverable_id IN NUMBER)
1344 RETURN boolean
1345 IS
1346 l_api_name CONSTANT VARCHAR2(40) := 'check_item_deliverable';
1347
1348 CURSOR item_dlv_cur(p_item_id in NUMBER,
1349 p_deliverable_id IN NUMBER ) IS
1350 -- SELECT A.ITEM_ID
1351 SELECT 1
1352 from IBE_DSP_TPL_CTG A, MTL_ITEM_CATEGORIES B where
1353 B.INVENTORY_ITEM_ID = p_item_id and
1354 B.CATEGORY_ID = A.CATEGORY_ID AND
1355 A.ITEM_ID= p_deliverable_id ;
1356
1357 l_return_status boolean := false;
1358 l_exists NUMBER := 0;
1359 BEGIN
1360
1361 return true;
1362 /*
1363 if p_deliverable_id is not null and p_item_id is not null then
1364
1365 open item_dlv_cur(p_item_id,p_deliverable_id);
1366 FETCH item_dlv_cur INTO l_exists;
1367 IF item_dlv_cur%FOUND THEN
1368 l_return_status := true;
1369 END IF;
1370
1371 CLOSE item_dlv_cur;
1372
1373 if l_return_status = false then
1374 FND_MESSAGE.set_name('IBE', 'IBE_DSP_ITEM_DLV_INVLD');
1375 FND_MESSAGE.set_token('ITEM_ID', p_item_id);
1376 FND_MESSAGE.set_token('ID', p_deliverable_id);
1377 FND_MSG_PUB.add;
1378 END IF;
1379 else
1380 FND_MESSAGE.set_name('IBE', 'IBE_DSP_ITEM_DLV_REQ');
1381 FND_MSG_PUB.add;
1382 END IF;
1383
1384
1385 return l_return_status;
1386
1387 EXCEPTION
1388 WHEN OTHERS THEN
1389 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1390 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1391 END IF;
1392 return false;
1393 */
1394 end check_item_deliverable;
1395
1396
1397 -----------------------------------------------------------------
1398 -- NOTES
1399 -- 1. Returns true if category has the deliverable association
1400 -- 2. If not, message IBE_DSP_CATEGORY_DLV_INVALID
1401 -- is pushed on the stack
1402 ---------------------------------------------------------------------
1403 FUNCTION check_category_deliverable(p_category_id IN NUMBER,
1404 p_deliverable_id IN NUMBER)
1405 RETURN boolean
1406 IS
1407 l_api_name CONSTANT VARCHAR2(40) := 'check_category_deliverable';
1408
1409 CURSOR category_dlv_cur(p_category_id in NUMBER,
1410 p_deliverable_id IN NUMBER ) IS
1411 -- SELECT ITEM_ID
1412 SELECT 1
1413 from IBE_DSP_TPL_CTG where category_id = p_category_id and
1414 ITEM_ID= p_deliverable_id ;
1415
1416 l_return_status boolean := false;
1417 l_exists NUMBER := 0;
1418 BEGIN
1419
1420 if p_deliverable_id is not null and p_category_id is not null then
1421
1422 open category_dlv_cur(p_category_id,p_deliverable_id);
1423 FETCH category_dlv_cur INTO l_exists;
1424 IF category_dlv_cur%NOTFOUND THEN
1425 CLOSE category_dlv_cur;
1426 FND_MESSAGE.set_name('IBE', 'IBE_DSP_CATEGORY_DLV_INVLD');
1427 FND_MESSAGE.set_token('CATEGORY_ID', p_category_id);
1428 FND_MESSAGE.set_token('ID', p_deliverable_id);
1429 FND_MSG_PUB.add;
1430 l_return_status := false;
1431 else
1432 CLOSE category_dlv_cur;
1433 l_return_status := true;
1434 END IF;
1435 else
1436 FND_MESSAGE.set_name('IBE', 'IBE_DSP_CATEGORY_DLV_REQ');
1437 FND_MSG_PUB.add;
1438 END IF;
1439
1440 return l_return_status;
1441
1442 EXCEPTION
1443 WHEN OTHERS THEN
1444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1445 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1446 END IF;
1447 return false;
1448
1449 end check_category_deliverable;
1450
1451 -----------------------------------------------------------------
1452 -- NOTES
1453 -- 1. Returns true if the minisite exists
1454 -- 2. If not, message IBE_MSITE_NOT_EXISTS is pushed on the stack
1455 ---------------------------------------------------------------------
1456
1457 FUNCTION check_master_msite_exists
1458 RETURN NUMBER
1459 IS
1460 l_api_name CONSTANT VARCHAR2(40) := 'check_master_msite_exists';
1461 l_msite_id NUMBER := null;
1462
1463 CURSOR msite_cur IS
1464 SELECT msite_id FROM IBE_MSITES_B where master_msite_flag='Y' and site_type = 'I';
1465
1466 BEGIN
1467
1468 OPEN msite_cur;
1469 FETCH msite_cur INTO l_msite_id;
1470 close msite_cur;
1471
1472 if l_msite_id is null then
1473 FND_MESSAGE.set_name('IBE', 'IBE_MSITE_MASTER_NOT_EXISTS');
1474 FND_MSG_PUB.add;
1475 END IF;
1476
1477 return l_msite_id;
1478
1479 EXCEPTION
1480
1481 WHEN OTHERS THEN
1482 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1483 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1484 END IF;
1485 return l_msite_id;
1486
1487 END check_master_msite_exists;
1488
1489 FUNCTION check_attachment_exists(p_file_name IN VARCHAR2)
1490 RETURN NUMBER
1491 IS
1492 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_exists';
1493
1494 CURSOR attachment_cur(p_file_name IN VARCHAR2) IS
1495 SELECT attachment_id FROM JTF_AMV_ATTACHMENTS
1496 WHERE file_name = p_file_name;
1497
1498 l_attachment_id NUMBER := NULL;
1499
1500 BEGIN
1501
1502 IF TRIM(p_file_name) IS NOT NULL THEN
1503 OPEN attachment_cur(TRIM(p_file_name));
1504 FETCH attachment_cur INTO l_attachment_id;
1505 CLOSE attachment_cur;
1506 END IF;
1507
1508 RETURN l_attachment_id;
1509
1510 EXCEPTION
1511
1512 WHEN OTHERS THEN
1513 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1514 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1515 END IF;
1516 RETURN NULL;
1517
1518 END check_attachment_exists;
1519
1520 -- Added by G. Zhang 05/23/01 10:57AM
1521 FUNCTION check_attachment_exists(
1522 p_deliverable_id IN NUMBER,
1523 p_file_id IN NUMBER,
1524 p_file_name IN VARCHAR2)
1525 RETURN NUMBER
1526 IS
1527 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_exists';
1528
1529 /*bug 2665027
1530 CURSOR attachment_cur(p_deliverable_id IN NUMBER,p_file_id IN NUMBER,p_file_name IN VARCHAR2) IS
1531 SELECT attachment_id FROM JTF_AMV_ATTACHMENTS
1532 WHERE file_name = p_file_name AND
1533 file_id = p_file_id AND
1534 attachment_used_by_id = p_deliverable_id;
1535
1536 CURSOR attachment_cur2(p_deliverable_id IN NUMBER,p_file_name IN VARCHAR2) IS
1537 SELECT attachment_id FROM JTF_AMV_ATTACHMENTS
1538 WHERE file_name = p_file_name AND
1539 file_id is null AND
1540 attachment_used_by_id = p_deliverable_id;
1541 */
1542 --bug 2665027
1543 CURSOR attachment_cur(p_deliverable_id IN NUMBER,p_file_id IN NUMBER,p_file_name IN VARCHAR2) IS
1544 SELECT jta.attachment_id
1545 FROM JTF_AMV_ATTACHMENTS jta,
1546 JTF_AMV_ITEMS_B jtai,
1547 IBE_DSP_LGL_PHYS_MAP idlpm
1548 WHERE jta.file_name = p_file_name AND
1549 jta.file_id = p_file_id AND
1550 jta.attachment_id = idlpm.attachment_id AND
1551 idlpm.item_id = jtai.item_id AND
1552 rownum=1;
1553
1554 CURSOR attachment_cur2(p_deliverable_id IN NUMBER,p_file_name IN VARCHAR2) IS
1555 SELECT jta.attachment_id
1556 FROM JTF_AMV_ATTACHMENTS jta,
1557 JTF_AMV_ITEMS_B jtai,
1558 IBE_DSP_LGL_PHYS_MAP idlpm
1559 WHERE jta.file_name = p_file_name AND
1560 jta.file_id is null AND
1561 jta.attachment_id = idlpm.attachment_id AND
1562 idlpm.item_id = jtai.item_id AND
1563 rownum = 1;
1564
1565 l_attachment_id NUMBER := NULL;
1566
1567 BEGIN
1568
1569 IF TRIM(p_file_name) IS NOT NULL THEN
1570 IF p_file_id is null THEN
1571 OPEN attachment_cur2(p_deliverable_id,TRIM(p_file_name));
1572 FETCH attachment_cur2 INTO l_attachment_id;
1573 CLOSE attachment_cur2;
1574 ELSE
1575 OPEN attachment_cur(p_deliverable_id,p_file_id,TRIM(p_file_name));
1576 FETCH attachment_cur INTO l_attachment_id;
1577 CLOSE attachment_cur;
1578 END IF;
1579 END IF;
1580
1581 RETURN l_attachment_id;
1582
1583 EXCEPTION
1584
1585 WHEN OTHERS THEN
1586 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1587 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1588 END IF;
1589 RETURN NULL;
1590
1591 END check_attachment_exists;
1592
1593 FUNCTION check_attachment_deliverable(
1594 p_attachment_id IN NUMBER,
1595 p_deliverable_id IN NUMBER)
1596 RETURN BOOLEAN
1597 IS
1598
1599 l_api_name CONSTANT VARCHAR2(40) := 'check_attachment_deliverable';
1600
1601 CURSOR attachment_cur(p_attachment_id IN NUMBER,
1602 p_deliverable_id IN NUMBER) IS
1603 -- SELECT attachment_used_by_id
1604 SELECT 1
1605 FROM JTF_AMV_ATTACHMENTS
1606 WHERE attachment_id = p_attachment_id
1607 AND attachment_used_by_id = p_deliverable_id;
1608
1609 l_exists NUMBER;
1610 l_return_status BOOLEAN := FALSE;
1611
1612 BEGIN
1613
1614 IF (p_attachment_id IS NOT NULL)
1615 AND (p_attachment_id <> FND_API.G_MISS_NUM)
1616 AND (p_deliverable_id IS NOT NULL)
1617 AND (p_deliverable_id <> FND_API.G_MISS_NUM) THEN
1618 OPEN attachment_cur(p_attachment_id, p_deliverable_id);
1619
1620 FETCH attachment_cur INTO l_exists;
1621 IF attachment_cur%FOUND THEN
1622 l_return_status := TRUE;
1623 END IF;
1624 CLOSE attachment_cur;
1625 END IF;
1626
1627 IF NOT l_return_status THEN
1628 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1629 FND_MESSAGE.set_name('IBE','IBE_DSP_DLV_ATH_INVLD');
1630 FND_MESSAGE.set_token('ID', TO_CHAR(p_deliverable_id));
1631 FND_MESSAGE.set_token('ATH_ID', TO_CHAR(p_attachment_id));
1632 FND_MSG_PUB.ADD;
1633 END IF;
1634 END IF;
1635
1636 RETURN l_return_status;
1637
1638 EXCEPTION
1639
1640 WHEN OTHERS THEN
1641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1642 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1643 END IF;
1644 RETURN l_return_status;
1645
1646 END check_attachment_deliverable;
1647
1648 FUNCTION check_default_attachment(p_attachment_id IN NUMBER)
1649 RETURN BOOLEAN IS
1650
1651 l_api_name CONSTANT VARCHAR2(40) := 'check_default_attachment';
1652
1653 CURSOR physmap_cur(p_attachment_id IN NUMBER) IS
1654 -- SELECT attachment_id
1655 SELECT 1
1656 FROM IBE_DSP_LGL_PHYS_MAP
1657 WHERE attachment_id = p_attachment_id
1658 AND default_site = 'Y'
1659 AND default_language = 'Y';
1660
1661 l_exists NUMBER;
1662 l_return_status BOOLEAN := FALSE;
1663
1664 BEGIN
1665
1666 IF p_attachment_id IS NOT NULL THEN
1667 OPEN physmap_cur(p_attachment_id);
1668 FETCH physmap_cur INTO l_exists;
1669 IF physmap_cur%FOUND THEN
1670 l_return_status := TRUE;
1671 END IF;
1672 CLOSE physmap_cur;
1673 END IF;
1674
1675 RETURN l_return_status;
1676
1677 EXCEPTION
1678
1679 WHEN OTHERS THEN
1680 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1681 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1682 END IF;
1683 RETURN FALSE;
1684
1685 END check_default_attachment;
1686
1687 ---------------------------------------------------------------------
1688 -- NOTES
1689 -- 1. Returns TRUE if the access_name for a mini site is Unique.
1690 -- 2. If Access Name already exists, message IBE_MSITE_DUP_ACCNAME is
1691 -- pushed on stack.
1692 ---------------------------------------------------------------------
1693 FUNCTION Check_Msite_Accessname(p_access_name IN VARCHAR2)
1694 RETURN BOOLEAN
1695 AS
1696 Cursor C_Exists(p_access_name Varchar2) Is
1697 Select 'x'
1698 From ibe_msites_b
1699 Where access_name = p_access_name and site_type = 'I';
1700 l_exists Varchar2(1);
1701 l_status Boolean := TRUE;
1702 Begin
1703 Open C_Exists(p_access_name);
1704 Fetch C_Exists INTO l_exists;
1705 If C_Exists%FOUND Then
1706 l_status := FALSE ;
1707 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1708 FND_MESSAGE.set_name('IBE','IBE_MSITE_DUP_ACCNAME');
1709 FND_MESSAGE.set_token('ACCNAME',p_access_name);
1710 FND_MSG_PUB.ADD;
1711 END IF;
1712 Else
1713 l_status := TRUE ;
1714 End If;
1715 Close C_Exists ;
1716 Return l_status;
1717
1718 End Check_Msite_Accessname ;
1719
1720 FUNCTION check_lookup_exists(
1721 p_lookup_type IN VARCHAR2,
1722 p_lookup_code IN VARCHAR2
1723 ) Return VARCHAR2 AS
1724 l_count NUMBER;
1725
1726 Cursor c_check_lookup(c_lookup_type VARCHAR2, c_lookup_code VARCHAR2) IS
1727 select COUNT(*) FROM FND_LOOKUP_VALUES WHERE lookup_type = c_lookup_type
1728 And lookup_code = c_lookup_code And enabled_flag = 'Y';
1729
1730 BEGIN
1731
1732 open c_check_lookup(p_lookup_type, p_lookup_code);
1733 fetch c_check_lookup into l_count;
1734 CLOSE c_check_lookup;
1735
1736 IF l_count = 0 THEN
1737 RETURN FND_API.g_false;
1738 ELSE
1739 RETURN FND_API.g_true;
1740 END IF;
1741 END check_lookup_exists;
1742
1743 FUNCTION Check_Media_Object(p_operation IN VARCHAR2,
1744 p_access_name IN VARCHAR2,
1745 p_deliverable_type_code IN VARCHAR2,
1746 p_applicable_to_code IN VARCHAR2)
1747 RETURN NUMBER
1748 IS
1749 BEGIN
1750 IF (p_operation = 'CREATE') THEN
1751 IF (p_access_name is NULL)
1752 OR (p_access_name = FND_API.G_MISS_CHAR) THEN
1753 -- Access name cannot be null
1754 RETURN -1;
1755 END IF;
1756 IF (p_deliverable_type_code is NULL)
1757 OR (p_deliverable_type_code = FND_API.G_MISS_CHAR) THEN
1758 -- Deliverable type code cannot be null
1759 RETURN -2;
1760 ELSE
1761 -- Check content type code for OCM integration
1762 NULL;
1763 END IF;
1764 IF (p_applicable_to_code is NULL)
1765 OR (p_applicable_to_code = FND_API.G_MISS_CHAR) THEN
1766 -- Applicable to code cannot be null
1767 RETURN -3;
1768 ELSE
1769 -- Check the lookup code
1770 IF check_lookup_exists(p_lookup_type => 'JTF_AMV_APPLI_TO_CODE',
1771 p_lookup_code => p_applicable_to_code)
1772 = FND_API.G_FALSE THEN
1773 IF p_deliverable_type_code = 'TEMPLATE' THEN
1774 IF check_lookup_exists(p_lookup_type => 'IBE_M_TEMPLATE_APPLI_TO',
1775 p_lookup_code => p_applicable_to_code)
1776 = FND_API.G_FALSE THEN
1777 RETURN -5;
1778 END IF;
1779 ELSIF p_deliverable_type_code = 'MEDIA' THEN
1780 IF check_lookup_exists(p_lookup_type => 'IBE_M_MEDIA_OBJECT_APPLI_TO',
1781 p_lookup_code => p_applicable_to_code)
1782 = FND_API.G_FALSE THEN
1783 RETURN -6;
1784 END IF;
1785 END IF;
1786 END IF;
1787 END IF;
1788 ELSIF (p_operation = 'UPDATE') THEN
1789 IF (p_access_name is NULL) THEN
1790 -- Access name cannot be null
1791 RETURN -1;
1792 END IF;
1793 IF (p_deliverable_type_code is NULL) THEN
1794 -- Deliverable type code cannot be null
1795 RETURN -2;
1796 ELSE
1797 -- Check content type code for OCM integration
1798 NULL;
1799 END IF;
1800 IF (p_applicable_to_code is NULL) THEN
1801 -- Applicable to code cannot be null
1802 RETURN -3;
1803 ELSE
1804 IF (p_applicable_to_code <> FND_API.G_MISS_CHAR) THEN
1805 -- Check the lookup code
1806 IF check_lookup_exists(p_lookup_type => 'JTF_AMV_APPLI_TO_CODE',
1807 p_lookup_code => p_applicable_to_code)
1808 = FND_API.G_FALSE THEN
1809 IF p_deliverable_type_code = 'TEMPLATE' THEN
1810 IF check_lookup_exists(p_lookup_type => 'IBE_M_TEMPLATE_APPLI_TO',
1811 p_lookup_code => p_applicable_to_code)
1812 = FND_API.G_FALSE THEN
1813 RETURN -5;
1814 END IF;
1815 ELSIF p_deliverable_type_code = 'MEDIA' THEN
1816 IF check_lookup_exists(p_lookup_type => 'IBE_M_MEDIA_OBJECT_APPLI_TO',
1817 p_lookup_code => p_applicable_to_code)
1818 = FND_API.G_FALSE THEN
1819 RETURN -6;
1820 END IF;
1821 END IF;
1822 END IF;
1823 END IF; -- FND_API.G_MISS_CHAR checking
1824 END IF;
1825 END IF;
1826 RETURN 0;
1827 END Check_Media_Object;
1828
1829 END IBE_DSPMGRVALIDATION_GRP;