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