1 PACKAGE BODY Ibc_Validate_Pvt AS
2 /* $Header: ibcvvldb.pls 120.2 2005/08/24 12:21:17 appldev ship $ */
3
4 /****************************************************
5 -------------PACKAGE VARIABLES -------------------------------------------------------------------------
6 ****************************************************/
7
8
9
10
11 /****************************************************
12 -------------FUNCTIONS--------------------------------------------------------------------------
13 ****************************************************/
14 -- --------------------------------------------------------------
15 -- IS APPROVED
16 --
17 -- Checks to see if content item version is approved
18 --
19 -- --------------------------------------------------------------
20 FUNCTION isApproved(
21 f_citem_ver_id IN NUMBER
22 )
23 RETURN VARCHAR2
24 IS
25 CURSOR c_app IS
26 SELECT
27 citem_version_id
28 FROM
29 ibc_citem_versions_b
30 WHERE
31 citem_version_id = f_citem_ver_id
32 AND
33 citem_version_status = IBC_UTILITIES_PUB.G_STV_APPROVED;
34
35 temp NUMBER;
36 BEGIN
37 open c_app;
38 fetch c_app into temp;
39
40 if (c_app%NOTFOUND) then
41 close c_app;
42 RETURN FND_API.g_false;
43 else
44 close c_app;
45 RETURN FND_API.g_true;
46 end if;
47 END;
48
49
50
51
52 -- --------------------------------------------------------------
53 -- IS APPROVED ITEM
54 --
55 -- Checks to see if content item version is approved
56 --
57 -- --------------------------------------------------------------
58 FUNCTION isApprovedItem(
59 f_citem_id IN NUMBER
60 )
61 RETURN VARCHAR2
62 IS
63 CURSOR c_appi IS
64 SELECT
65 content_item_id
66 FROM
67 ibc_content_items
68 WHERE
69 content_item_id = f_citem_id
70 AND
71 content_item_status = IBC_UTILITIES_PUB.G_STI_APPROVED;
72
73 temp NUMBER;
74 BEGIN
75 open c_appi;
76 fetch c_appi into temp;
77
78 if (c_appi%NOTFOUND) then
79 close c_appi;
80 RETURN FND_API.g_false;
81 else
82 close c_appi;
83 RETURN FND_API.g_true;
84 end if;
85 END;
86
87
88 -- --------------------------------------------------------------
89 -- IS BOOLEAN
90 --
91 -- Used to check if item is boolean
92 --
93 -- --------------------------------------------------------------
94 FUNCTION isBoolean(
95 f_boolean IN VARCHAR2
96 )
97 RETURN VARCHAR2
98 IS
99 BEGIN
100 IF ( (f_boolean = 'F') OR (f_boolean = 'T') ) THEN
101 RETURN FND_API.g_true;
102 ELSE
103 RETURN FND_API.g_false;
104 END IF;
105 END isBoolean;
106
107
108
109
110
111
112
113 -- --------------------------------------------------------------
114 -- IS DATE
115 --
116 -- Used to check if a string is actually a date
117 --
118 -- --------------------------------------------------------------
119 FUNCTION isDate(
120 f_date IN VARCHAR2
121 )
122 RETURN VARCHAR2
123 IS
124 temp DATE;
125 BEGIN
126
127
128 IF (f_date IS NOT NULL) THEN
129 BEGIN
130 temp := TO_DATE(f_date, FND_DATE.user_mask);
131 EXCEPTION
132 WHEN OTHERS THEN
133 BEGIN
134 temp := TO_DATE(f_date, Fnd_Profile.value('ICX_DATE_FORMAT_MASK'));
135 EXCEPTION
136 WHEN OTHERS THEN
137 temp := TO_DATE(f_date,'RRRR-mm-dd');
138 END;
139
140 END;
141 RETURN Fnd_Api.g_true;
142 ELSE
143 RETURN Fnd_Api.g_false;
144 END IF;
145 EXCEPTION
146 WHEN OTHERS THEN
147 RETURN Fnd_Api.g_false;
148 END isDate;
149
150
151
152
153
154 -- --------------------------------------------------------------
155 -- IS NUMBER
156 --
157 -- Used to check if a string is actually a number
158 --
159 -- --------------------------------------------------------------
160 FUNCTION isNumber(
161 f_number IN VARCHAR2
162 )
163 RETURN VARCHAR2
164 IS
165 temp NUMBER;
166 BEGIN
167 if (f_number is not null) then
168 temp := TO_NUMBER(f_number);
169 RETURN FND_API.g_true;
170 else
171 RETURN FND_API.g_false;
172 end if;
173 EXCEPTION
174 WHEN OTHERS THEN
175 RETURN FND_API.g_false;
176 END isNumber;
177
178
179
180
181 -- --------------------------------------------------------------
182 -- IS VALID ASSOCIATION
183 --
184 -- Used to check if the association exists
185 --
186 -- --------------------------------------------------------------
187 FUNCTION isValidAssoc(
188 f_assoc_id IN VARCHAR2
189 )
190 RETURN VARCHAR2
191 IS
192 CURSOR c_assoc IS
193 SELECT
194 association_id
195 FROM
196 ibc_associations
197 WHERE
198 association_id = f_assoc_id;
199
200 temp NUMBER;
201 BEGIN
202 IF (f_assoc_id is not null) THEN
203 OPEN c_assoc;
204 FETCH c_assoc INTO temp;
205
206 IF (c_assoc%NOTFOUND) THEN
207 CLOSE c_assoc;
208 RETURN FND_API.g_false;
209 ELSE
210 CLOSE c_assoc;
211 RETURN FND_API.g_false;
212 END IF;
213 ELSE
214 RETURN FND_API.g_false;
215 END IF;
216 END isValidAssoc;
217
218
219
220
221 -- --------------------------------------------------------------
222 -- IS VALID ATTACHMENT
223 --
224 -- Used to check if the attachment exists
225 --
226 -- --------------------------------------------------------------
227 FUNCTION isValidAttachment(
228 f_attach_id IN VARCHAR2
229 )
230 RETURN VARCHAR2
231 IS
232 CURSOR c_attach IS
233 SELECT
234 file_id
235 FROM
236 fnd_lobs
237 WHERE
238 file_id = f_attach_id;
239
240 temp NUMBER;
241 BEGIN
242 IF (f_attach_id is not null) then
243 OPEN c_attach;
244 FETCH c_attach INTO temp;
245
246 IF (c_attach%NOTFOUND) THEN
247 CLOSE c_attach;
248 RETURN FND_API.g_false;
249 ELSE
250 CLOSE c_attach;
251 RETURN FND_API.g_true;
252 END IF;
253 ELSE
254 RETURN FND_API.g_false;
255 END IF;
256 END isValidAttachment;
257
258
259
260 -- --------------------------------------------------------------
261 -- IS VALID ASSOCIATION TYPE CODE
262 --
263 -- Used to check if the association type code exists
264 --
265 -- --------------------------------------------------------------
266 FUNCTION isValidAssocType(
267 f_assoc_code IN VARCHAR2
268 )
269 RETURN VARCHAR2
270 IS
271 CURSOR c_assoc IS
272 SELECT
273 association_type_code
274 FROM
275 ibc_association_types_b
276 WHERE
277 association_type_code = f_assoc_code;
278
279 temp IBC_ASSOCIATION_TYPES_B.association_type_code%TYPE;
280 BEGIN
281 IF (f_assoc_code IS NOT NULL) THEN
282 open c_assoc;
283 fetch c_assoc into temp;
284
285 if (c_assoc%NOTFOUND) then
286 close c_assoc;
287 RETURN FND_API.g_false;
288 else
289 close c_assoc;
290 RETURN FND_API.g_true;
291 end if;
292 ELSE
293 RETURN FND_API.g_false;
294 END IF;
295 END isValidAssocType;
296
297
298
299
300 -- --------------------------------------------------------------
301 -- IS VALID ATTRIBUTE CODE
302 --
303 -- Used to check if the directory node exists
304 --
305 -- --------------------------------------------------------------
306 FUNCTION isValidAttrCode(
307 f_attr_type_code IN VARCHAR2
308 ,f_ctype_code IN VARCHAR2
309 )
310 RETURN VARCHAR2
311 IS
312 CURSOR c_acode IS
313 SELECT
314 attribute_type_code
315 FROM
316 ibc_attribute_types_b
317 WHERE
318 attribute_type_code = f_attr_type_code
319 AND
320 content_type_code = f_ctype_code;
321
322 temp IBC_ATTRIBUTE_TYPES_B.attribute_type_code%TYPE;
323 BEGIN
324 IF (f_attr_type_code IS NOT NULL) THEN
325 open c_acode;
326 fetch c_acode into temp;
327
328 if (c_acode%NOTFOUND) then
329 CLOSE c_acode;
330 RETURN FND_API.g_false;
331 else
332 CLOSE c_acode;
333 RETURN FND_API.g_true;
334 end if;
335 ELSE
336 RETURN FND_API.g_false;
337 END IF;
338 END isValidAttrCode;
339
340
341
342
343 -- --------------------------------------------------------------
344 -- IS VALID CITEM VERSION
345 --
346 -- Used to check if the content item version exists
347 --
348 -- --------------------------------------------------------------
349 FUNCTION isValidCitemVer(
350 f_citem_ver_id IN VARCHAR2
351 )
352 RETURN VARCHAR2
353 IS
354 CURSOR c_verid IS
355 SELECT
356 citem_version_id
357 FROM
358 ibc_citem_versions_b
359 WHERE
360 citem_version_id = f_citem_ver_id;
361
362 temp NUMBER;
363 BEGIN
364 IF (f_citem_ver_id IS NOT NULL) THEN
365 open c_verid;
366 fetch c_verid into temp;
367
368 if (c_verid%NOTFOUND) then
369 close c_verid;
370 RETURN FND_API.g_false;
371 else
372 close c_verid;
373 RETURN FND_API.g_true;
374 end if;
375 ELSE
376 RETURN FND_API.g_false;
377 END IF;
378 END isValidCitemVer;
379
380
381
382 -- --------------------------------------------------------------
383 -- IS VALID CITEM VERSION FOR CITEM
384 --
385 -- Used to check if the citem version id is valid and belongs to
386 -- a particular content item.
387 --
388 -- --------------------------------------------------------------
389 FUNCTION isValidCitemVerForCitem (
390 f_citem_id IN VARCHAR2
391 ,f_citem_ver_id IN VARCHAR2
392 )
393 RETURN VARCHAR2
394 IS
395 CURSOR c_verid IS
396 SELECT citem_version_id
397 FROM ibc_citem_versions_b
398 WHERE content_item_id = f_citem_id
399 AND citem_version_id = f_citem_ver_id;
400 temp NUMBER;
401 BEGIN
402 IF (f_citem_ver_id IS NOT NULL) THEN
403 open c_verid;
404 fetch c_verid into temp;
405
406 if (c_verid%NOTFOUND) then
407 close c_verid;
408 RETURN FND_API.g_false;
409 else
410 close c_verid;
411 RETURN FND_API.g_true;
412 end if;
413 ELSE
414 RETURN FND_API.g_false;
415 END IF;
416 END isValidCitemVerForCitem;
417
418
419
420 -- --------------------------------------------------------------
421 -- IS VALID CITEM
422 --
423 -- Used to check if the content item exists
424 --
425 -- --------------------------------------------------------------
426 FUNCTION isValidCitem(
427 f_citem_id IN VARCHAR2
428 )
429 RETURN VARCHAR2
430 IS
431 CURSOR c_citem IS
432 SELECT
433 content_item_id
434 FROM
435 ibc_content_items
436 WHERE
437 content_item_id = f_citem_id;
438
439 temp NUMBER;
440 BEGIN
441 IF (f_citem_id IS NOT NULL) THEN
442 open c_citem;
443 fetch c_citem into temp;
444
445 IF (c_citem%NOTFOUND) THEN
446 close c_citem;
447 RETURN FND_API.g_false;
448 ELSE
449 close c_citem;
450 RETURN FND_API.g_true;
451 END IF;
452 ELSE
453 RETURN FND_API.g_false;
454 END IF;
455 END isValidCitem;
456
457
458 -- --------------------------------------------------------------
459 -- IS VALID LANGUAGE
460 --
461 -- Used to check if specified language exists in FND_LANGUAGES
462 --
463 -- --------------------------------------------------------------
464 FUNCTION isValidLanguage(
465 p_language IN VARCHAR2
466 )
467 RETURN VARCHAR2
468 IS
469 CURSOR c_language IS
470 SELECT 'X'
471 FROM fnd_languages
472 WHERE language_code = p_language;
473
474 l_dummy VARCHAR2(1);
475 BEGIN
476 IF (p_language IS NOT NULL) THEN
477 open c_language;
478 fetch c_language into l_dummy;
479
480 IF (c_language%NOTFOUND) THEN
481 close c_language;
482 RETURN FND_API.g_false;
483 ELSE
484 close c_language;
485 RETURN FND_API.g_true;
486 END IF;
487 ELSE
488 RETURN FND_API.g_true;
489 END IF;
490 END isValidLanguage;
491
492
493 -- --------------------------------------------------------------
494 -- IS VALID CTYPE
495 --
496 -- Used to check if the content type code exists
497 --
498 -- --------------------------------------------------------------
499 FUNCTION isValidCType(
500 f_ctype_code IN VARCHAR2
501 )
502 RETURN VARCHAR2
503 IS
504 CURSOR c_ctype IS
505 SELECT
506 content_type_code
507 FROM
508 ibc_content_types_b
509 WHERE
510 content_type_code = f_ctype_code;
511
512 temp IBC_CONTENT_TYPES_B.content_type_code%TYPE;
513 BEGIN
514 IF (f_ctype_code IS NOT NULL) THEN
515 open c_ctype;
516 fetch c_ctype into temp;
517
518 IF(c_ctype%NOTFOUND) THEN
519 CLOSE c_ctype;
520 RETURN FND_API.g_false;
521 ELSE
522 CLOSE c_ctype;
523 RETURN FND_API.g_true;
524 END IF;
525 ELSE
526 RETURN FND_API.g_false;
527 END IF;
528 END isValidCType;
529
530
531
532 -- --------------------------------------------------------------
533 -- IS VALID DIRECTORY NODE
534 --
535 -- Used to check if the directory node exists
536 --
537 -- --------------------------------------------------------------
538 FUNCTION isValidDirNode(
539 f_node_id IN NUMBER
540 )
541 RETURN VARCHAR2
542 IS
543 CURSOR c_dnode IS
544 SELECT
545 directory_node_id
546 FROM
547 ibc_directory_nodes_b
548 WHERE
549 directory_node_id = f_node_id;
550
551 temp NUMBER;
552 BEGIN
553 IF (f_node_id IS NOT NULL) THEN
554 OPEN c_dnode;
555 fetch c_dnode into temp;
556
557 IF(c_dnode%NOTFOUND) THEN
558 CLOSE c_dnode;
559 RETURN FND_API.g_false;
560 ELSE
561 CLOSE c_dnode;
562 RETURN FND_API.g_true;
563 END IF;
564 ELSE
565 RETURN FND_API.g_false;
566 END IF;
567 END isValidDirNode;
568
569
570
571
572
573 -- --------------------------------------------------------------
574 -- IS VALID LOB
575 --
576 -- Used to check if the lob exists in fnd_lobs
577 --
578 -- --------------------------------------------------------------
579 FUNCTION isValidLob(
580 f_lob_id IN NUMBER
581 )
582 RETURN VARCHAR2
583 IS
584 CURSOR c_lob IS
585 SELECT
586 file_id
587 FROM
588 fnd_lobs
589 WHERE
590 file_id = f_lob_id;
591
592 temp NUMBER;
593 BEGIN
594 IF (f_lob_id IS NOT NULL) THEN
595 open c_lob;
596 fetch c_lob into temp;
597
598 IF(c_lob%NOTFOUND) THEN
599 CLOSE c_lob;
600 RETURN FND_API.g_false;
601 ELSE
602 CLOSE c_lob;
603 RETURN FND_API.g_true;
604 END IF;
605 ELSE
606 RETURN FND_API.g_false;
607 END IF;
608 END isValidLob;
609
610
611 -- --------------------------------------------------------------
612 -- IS VALID RESOURCE
613 --
614 -- Used to check if the resource exists
615 --
616 -- --------------------------------------------------------------
617 FUNCTION isValidResource(
618 f_resource_id IN NUMBER
619 ,f_resource_type IN VARCHAR2
620 )
621 RETURN VARCHAR2
622 IS
623 CURSOR c_resource IS
624 SELECT resource_id
625 FROM jtf_rs_all_resources_vl
626 WHERE resource_id = f_resource_id
627 AND resource_type = f_resource_type;
628
629 temp number;
630
631 CURSOR c_user IS
632 SELECT user_id
633 FROM FND_USER
634 WHERE user_id = f_resource_id;
635
636 BEGIN
637
638 if (f_resource_id IS NULL or f_resource_type IS NULL) then
639 RETURN FND_API.g_false;
640 end if;
641
642 if (UPPER(f_resource_type) = 'USER') then
643 open c_user;
644 fetch c_user into temp;
645
646 if (c_user%NOTFOUND) then
647 close c_user;
648 RETURN FND_API.g_false;
649 else
650 close c_user;
651 RETURN FND_API.g_true;
652 end if;
653 else
654 open c_resource;
655 fetch c_resource into temp;
656
657 if (c_resource%NOTFOUND) then
658 close c_resource;
659 RETURN FND_API.g_false;
660 else
661 close c_resource;
662 RETURN FND_API.g_true;
663 end if;
664 end if;
665 END isValidResource;
666
667
668 -- --------------------------------------------------------------
669 -- IS VALID USER
670 --
671 -- Used to check if the user exists in FND_USER
672 --
673 -- --------------------------------------------------------------
674 FUNCTION isValidUser(
675 f_user_id IN NUMBER
676 )
677 RETURN VARCHAR2
678 IS
679 CURSOR c_user IS
680 SELECT user_id
681 FROM FND_USER
682 WHERE user_id = f_user_id;
683 temp number;
684 BEGIN
685 if (f_user_id is not null) then
686 open c_user;
687 fetch c_user into temp;
688
689 if (c_user%NOTFOUND) then
690 close c_user;
691 RETURN FND_API.g_false;
692 else
693 close c_user;
694 RETURN FND_API.g_true;
695 end if;
696 else
697 RETURN FND_API.g_false;
698 end if;
699 END isValidUser;
700
701
702
703 -- --------------------------------------------------------------
704 -- IS VALID STATUS
705 --
706 -- Used to check if the status
707 --
708 -- --------------------------------------------------------------
709 FUNCTION isValidStatus(
710 f_status IN VARCHAR2
711 )
712 RETURN VARCHAR2
713 IS
714 temp VARCHAR2(30);
715 BEGIN
716
717 if ( (f_status = IBC_UTILITIES_PUB.G_STV_SUBMIT_FOR_APPROVAL) or
718 (f_status = IBC_UTILITIES_PUB.G_STV_APPROVED) or
719 (f_status = IBC_UTILITIES_PUB.G_STV_ARCHIVED) or
720 (f_status = IBC_UTILITIES_PUB.G_STV_WORK_IN_PROGRESS) or
721 (f_status = IBC_UTILITIES_PUB.G_STV_REJECTED) ) then
722
723 RETURN FND_API.g_true;
724 else
725 RETURN FND_API.g_false;
726 end if;
727 END isValidStatus;
728
729
730
731 -- --------------------------------------------------------------
732 -- IS VALID LABEL
733 --
734 -- Used to check if the label exists
735 --
736 -- --------------------------------------------------------------
737 FUNCTION isValidLabel(
738 f_label IN VARCHAR2
739 )
740 RETURN VARCHAR2
741 IS
742 CURSOR c_label IS
743 SELECT
744 LABEL_CODE
745 FROM
746 IBC_LABELS_B
747 WHERE
748 LABEL_CODE = f_label;
749
750 temp IBC_LABELS_B.label_code%TYPE;
751 BEGIN
752 IF (f_label IS NOT NULL) THEN
753 open c_label;
754 fetch c_label into temp;
755
756 IF(c_label%NOTFOUND) THEN
757 CLOSE c_label;
758 RETURN FND_API.g_false;
759 ELSE
760 CLOSE c_label;
761 RETURN FND_API.g_true;
762 END IF;
763 ELSE
764 RETURN FND_API.g_false;
765 END IF;
766 END isValidLabel;
767
768
769
770 /****************************************************
771 -------------PROCEDURES--------------------------------------------------------------------------
772 ****************************************************/
773
774
775 PROCEDURE Validate_NotNULL_NUMBER (
776 p_init_msg_list IN VARCHAR2,
777 p_column_name IN VARCHAR2,
778 p_notnull_column IN NUMBER,
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_msg_count OUT NOCOPY NUMBER,
781 x_msg_data OUT NOCOPY VARCHAR2)
782 IS
783 BEGIN
784 -- Initialize message list if p_init_msg_list is set to TRUE.
785 IF FND_API.to_Boolean( p_init_msg_list ) THEN
786 FND_MSG_PUB.initialize;
787 END IF;
788
789 -- Initialize API return status to success
790 x_return_status := FND_API.G_RET_STS_SUCCESS;
791
792 IF (p_notnull_column IS NULL OR p_notnull_column = FND_API.G_MISS_NUM) THEN
793 x_return_status := FND_API.G_RET_STS_ERROR;
794 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
795 FND_MESSAGE.Set_Name('IBC', 'API_MISSING_COLUMN');
796 FND_MESSAGE.Set_Token('COLUMN', p_column_name, FALSE);
797 FND_MSG_PUB.ADD;
798 END IF;
799 END IF;
800
801 FND_MSG_PUB.Count_And_Get(
802 p_count => x_msg_count,
803 p_data => x_msg_data);
804
805 END Validate_NotNULL_NUMBER;
806
807
808 PROCEDURE Validate_NotNULL_VARCHAR2 (
809 p_init_msg_list IN VARCHAR2,
810 p_column_name IN VARCHAR2,
811 p_notnull_column IN VARCHAR2,
812 x_return_status OUT NOCOPY VARCHAR2,
813 x_msg_count OUT NOCOPY NUMBER,
814 x_msg_data OUT NOCOPY VARCHAR2)
815 IS
816 BEGIN
817 -- Initialize message list if p_init_msg_list is set to TRUE.
818 IF FND_API.to_Boolean( p_init_msg_list ) THEN
819 FND_MSG_PUB.initialize;
820 END IF;
821
822 -- Initialize API return status to success
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825 IF (p_notnull_column IS NULL OR p_notnull_column = FND_API.G_MISS_CHAR) THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
828 FND_MESSAGE.Set_Name('IBC', 'API_MISSING_COLUMN');
829 FND_MESSAGE.Set_Token('COLUMN', p_column_name, FALSE);
830 FND_MSG_PUB.ADD;
831 END IF;
832 END IF;
833
834 FND_MSG_PUB.Count_And_Get(
835 p_count => x_msg_count,
836 p_data => x_msg_data);
837
838 END Validate_NotNULL_VARCHAR2;
839
840
841 PROCEDURE Validate_Content_Type_Status (
842 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
843 p_Content_Type_Status IN VARCHAR2,
844 X_Return_Status OUT NOCOPY VARCHAR2,
845 X_Msg_Count OUT NOCOPY NUMBER,
846 X_Msg_Data OUT NOCOPY VARCHAR2)
847 IS
848
849 CURSOR C_Content_Type_Status IS
850 SELECT lookup_code
851 FROM ibc_lookups lk
852 WHERE lookup_type = 'IBC_CTYPE_STATUS'
853 AND lookup_code = p_Content_Type_Status;
854
855 l_Content_Type_Status VARCHAR2(30);
856
857 BEGIN
858
859 -- Initialize message list if p_init_msg_list is set to TRUE.
860 IF FND_API.to_Boolean( p_init_msg_list ) THEN
861 FND_MSG_PUB.initialize;
862 END IF;
863
864 -- Initialize API return status to success
865 x_return_status := FND_API.G_RET_STS_SUCCESS;
866
867 IF (p_Content_Type_Status IS NOT NULL AND p_Content_Type_Status <> FND_API.G_MISS_CHAR) THEN
868 OPEN C_Content_Type_Status;
869 FETCH C_Content_Type_Status INTO l_Content_Type_Status;
870 IF (C_Content_Type_Status%NOTFOUND) THEN
871 CLOSE C_Content_Type_Status;
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
874 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
875 FND_MESSAGE.Set_Token('COLUMN', 'CONTENT_TYPE_STATUS', FALSE);
876 FND_MSG_PUB.ADD;
877 END IF;
878 ELSE
879 CLOSE C_Content_Type_Status;
880 END IF;
881 END IF;
882
883 END Validate_Content_Type_Status;
884
885 PROCEDURE Validate_appl_short_name (
886 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
887 p_appl_short_name IN VARCHAR2,
888 x_application_id OUT NOCOPY NUMBER,
889 X_Return_Status OUT NOCOPY VARCHAR2,
890 X_Msg_Count OUT NOCOPY NUMBER,
891 X_Msg_Data OUT NOCOPY VARCHAR2)
892 IS
893 CURSOR C_Get_Appl_Id (x_short_name VARCHAR2) IS
894 SELECT application_id
895 FROM fnd_application_vl
896 WHERE application_short_name = x_short_name;
897
898 BEGIN
899
900
901 -- Initialize message list if p_init_msg_list is set to TRUE.
902 IF FND_API.to_Boolean( p_init_msg_list ) THEN
903 FND_MSG_PUB.initialize;
904 END IF;
905
906 -- Initialize API return status to success
907 x_return_status := FND_API.G_RET_STS_SUCCESS;
908
909 IF (p_appl_short_name IS NOT NULL AND p_appl_short_name <> FND_API.G_MISS_CHAR) THEN
910 OPEN C_Get_Appl_Id (p_appl_short_name);
911 FETCH C_Get_Appl_Id INTO x_application_id;
912 IF (C_Get_Appl_Id%NOTFOUND) THEN
913 CLOSE C_Get_Appl_Id;
914 x_return_status := FND_API.G_RET_STS_ERROR;
915 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
916 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
917 FND_MESSAGE.Set_Token('COLUMN', 'APPLICATION SHORT NAME', FALSE);
918 FND_MSG_PUB.ADD;
919 END IF;
920 ELSE
921 CLOSE C_Get_Appl_Id;
922 END IF;
923 END IF;
924
925 END Validate_appl_short_name;
926
927
928 PROCEDURE Validate_application_id (
929 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
930 p_application_id IN NUMBER,
931 X_Return_Status OUT NOCOPY VARCHAR2,
932 X_Msg_Count OUT NOCOPY NUMBER,
933 X_Msg_Data OUT NOCOPY VARCHAR2)
934 IS
935 CURSOR C_Get_Appl_Id IS
936 SELECT application_id
937 FROM fnd_application_vl
938 WHERE application_id = p_application_id;
939
940 l_temp NUMBER;
941
942 BEGIN
943
944
945 -- Initialize message list if p_init_msg_list is set to TRUE.
946 IF FND_API.to_Boolean( p_init_msg_list ) THEN
947 FND_MSG_PUB.initialize;
948 END IF;
949
950 -- Initialize API return status to success
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952
953 IF (p_application_id IS NOT NULL AND p_application_id <> FND_API.G_MISS_NUM) THEN
954 OPEN C_Get_Appl_Id;
955 FETCH C_Get_Appl_Id INTO l_temp;
956 IF (C_Get_Appl_Id%NOTFOUND) THEN
957 CLOSE C_Get_Appl_Id;
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
960 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
961 FND_MESSAGE.Set_Token('COLUMN', 'APPLICATION ID', FALSE);
962 FND_MSG_PUB.ADD;
963 END IF;
964 ELSE
965 CLOSE C_Get_Appl_Id;
966 END IF;
967 END IF;
968
969 END Validate_application_id;
970
971
972 PROCEDURE Validate_Content_Type_Code (
973 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
974 p_Content_type_Code IN VARCHAR2,
975 X_Return_Status OUT NOCOPY VARCHAR2,
976 X_Msg_Count OUT NOCOPY NUMBER,
977 X_Msg_Data OUT NOCOPY VARCHAR2)
978 IS
979 CURSOR C_CType_Code IS
980 SELECT '1'
981 FROM ibc_content_types_vl
982 WHERE content_type_code = p_content_type_code;
983
984 l_temp CHAR(1);
985
986 BEGIN
987
988 -- Initialize message list if p_init_msg_list is set to TRUE.
989 IF FND_API.to_Boolean( p_init_msg_list ) THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 -- Initialize API return status to success
994 x_return_status := FND_API.G_RET_STS_SUCCESS;
995
996 IF (p_Content_type_Code IS NOT NULL AND p_Content_type_Code <> FND_API.G_MISS_CHAR) THEN
997 OPEN C_CType_Code;
998 FETCH C_CType_Code INTO l_temp;
999 IF (C_CType_Code%NOTFOUND) THEN
1000 CLOSE C_CType_Code;
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1003 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1004 FND_MESSAGE.Set_Token('COLUMN', 'CONTENT TYPE CODE', FALSE);
1005 FND_MSG_PUB.ADD;
1006 END IF;
1007 ELSE
1008 CLOSE C_CType_Code;
1009 END IF;
1010 END IF;
1011
1012 END Validate_Content_Type_Code;
1013
1014
1015 PROCEDURE Validate_Data_Type_Code (
1016 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1017 p_Data_type_Code IN VARCHAR2,
1018 X_Return_Status OUT NOCOPY VARCHAR2,
1019 X_Msg_Count OUT NOCOPY NUMBER,
1020 X_Msg_Data OUT NOCOPY VARCHAR2)
1021 IS
1022 CURSOR C_Data_type_Code IS
1023 SELECT lookup_code
1024 FROM ibc_lookups lk
1025 WHERE lookup_type = 'IBC_ATTRIBUTE_DATA_TYPE'
1026 AND lookup_code = p_Data_type_Code;
1027
1028 l_Data_type_Code VARCHAR2(30);
1029
1030 BEGIN
1031
1032 -- Initialize message list if p_init_msg_list is set to TRUE.
1033 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1034 FND_MSG_PUB.initialize;
1035 END IF;
1036
1037 -- Initialize API return status to success
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 -- Data Type Code cannot be NULL
1041 IF (p_Data_type_Code IS NOT NULL AND p_Data_type_Code <> FND_API.G_MISS_CHAR) THEN
1042 OPEN C_Data_type_Code;
1043 FETCH C_Data_type_Code INTO l_Data_type_Code;
1044 IF (C_Data_type_Code%NOTFOUND) THEN
1045 CLOSE C_Data_type_Code;
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1048 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1049 FND_MESSAGE.Set_Token('COLUMN', 'DATA_TYPE_CODE', FALSE);
1050 FND_MSG_PUB.ADD;
1051 END IF;
1052 ELSE
1053 CLOSE C_Data_type_Code;
1054 END IF;
1055 END IF;
1056
1057 END Validate_Data_Type_Code;
1058
1059
1060 PROCEDURE Validate_Default_value (
1061 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1062 p_data_type_code IN VARCHAR2,
1063 p_Default_value IN VARCHAR2,
1064 X_Return_Status OUT NOCOPY VARCHAR2,
1065 X_Msg_Count OUT NOCOPY NUMBER,
1066 X_Msg_Data OUT NOCOPY VARCHAR2)
1067 IS
1068
1069 BEGIN
1070
1071
1072 -- Initialize message list if p_init_msg_list is set to TRUE.
1073 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1074 FND_MSG_PUB.initialize;
1075 END IF;
1076
1077 -- Initialize API return status to success
1078 x_return_status := FND_API.G_RET_STS_SUCCESS;
1079
1080
1081 IF (p_Default_value IS NULL OR p_Default_value = FND_API.G_MISS_CHAR) THEN
1082 RETURN;
1083 END IF;
1084
1085 -- Check for valid Default_value is NOT NULL
1086 IF p_data_type_code = 'string' THEN
1087 NULL;
1088
1089 ELSIF p_data_type_code = 'html' THEN
1090 NULL;
1091
1092 ELSIF p_data_type_code = 'decimal' THEN
1093 NULL;
1094
1095 ELSIF p_data_type_code = 'dateTime' THEN
1096 NULL;
1097
1098 ELSIF p_data_type_code = 'url' THEN
1099 NULL;
1100
1101 ELSIF p_data_type_code = 'boolean' THEN
1102 NULL;
1103
1104 ELSIF p_data_type_code = 'component' THEN
1105 DECLARE
1106 CURSOR C_content_item_id IS
1107 SELECT content_item_id
1108 FROM ibc_content_items
1109 WHERE content_item_id = TO_NUMBER(p_default_value);
1110
1111 l_content_item_id NUMBER;
1112 BEGIN
1113 OPEN C_content_item_id;
1114 FETCH C_content_item_id INTO l_content_item_id;
1115 IF (C_content_item_id%NOTFOUND) THEN
1116 CLOSE C_content_item_id;
1117 x_return_status := FND_API.G_RET_STS_ERROR;
1118 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1119 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1120 FND_MESSAGE.Set_Token('COLUMN', 'DEFAULT VALUE', FALSE);
1121 FND_MSG_PUB.ADD;
1122 END IF;
1123 ELSE
1124 CLOSE C_content_item_id;
1125 END IF;
1126
1127 END;
1128
1129 ELSIF p_data_type_code = 'attachment' THEN
1130 DECLARE
1131 CURSOR C_file_id IS
1132 SELECT file_id
1133 FROM fnd_lobs
1134 WHERE file_id = p_default_value;
1135
1136 l_file_id NUMBER;
1137 BEGIN
1138 OPEN C_file_id;
1139 FETCH C_file_id INTO l_file_id;
1140 IF (C_file_id%NOTFOUND) THEN
1141 CLOSE C_file_id;
1142 x_return_status := FND_API.G_RET_STS_ERROR;
1143 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1144 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1145 FND_MESSAGE.Set_Token('COLUMN', 'DEFAULT VALUE', FALSE);
1146 FND_MSG_PUB.ADD;
1147 END IF;
1148 ELSE
1149 CLOSE C_file_id;
1150 END IF;
1151
1152 END;
1153
1154 END IF;
1155
1156 END Validate_Default_value;
1157
1158 PROCEDURE Validate_Reference_Code (
1159 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1160 p_data_type_code IN VARCHAR2,
1161 p_Reference_Code IN VARCHAR2,
1162 X_Return_Status OUT NOCOPY VARCHAR2,
1163 X_Msg_Count OUT NOCOPY NUMBER,
1164 X_Msg_Data OUT NOCOPY VARCHAR2)
1165 IS
1166 BEGIN
1167 -- Initialize message list if p_init_msg_list is set to TRUE.
1168 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1169 FND_MSG_PUB.initialize;
1170 END IF;
1171
1172 -- Initialize API return status to success
1173 x_return_status := FND_API.G_RET_STS_SUCCESS;
1174
1175 IF p_data_type_code = 'component' THEN
1176
1177 -- For CITEM the reference code must refer to a valid Content Type Code
1178 IF (p_reference_code IS NULL OR p_reference_code = FND_API.G_MISS_CHAR) THEN
1179 x_return_status := FND_API.G_RET_STS_ERROR;
1180 ELSE
1181
1182 Validate_Content_Type_Code (
1183 p_init_msg_list => p_init_msg_list,
1184 p_Content_type_Code => p_reference_code,
1185 X_Return_Status => X_Return_Status,
1186 X_Msg_Count => X_Msg_Count,
1187 X_Msg_Data => X_Msg_Data);
1188 END IF;
1189
1190 ELSE
1191 IF (p_reference_code IS NOT NULL AND p_reference_code <> FND_API.G_MISS_CHAR) THEN
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 END IF;
1194
1195 END IF;
1196
1197 IF X_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
1198 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1199 FND_MESSAGE.Set_Token('COLUMN', 'Reference Code', FALSE);
1200 FND_MSG_PUB.ADD;
1201 END IF;
1202
1203 END Validate_Reference_Code;
1204
1205 PROCEDURE Validate_Min_Max_Instances (
1206 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1207 p_Max_Instances IN NUMBER,
1208 p_Min_Instances IN NUMBER,
1209 X_Return_Status OUT NOCOPY VARCHAR2,
1210 X_Msg_Count OUT NOCOPY NUMBER,
1211 X_Msg_Data OUT NOCOPY VARCHAR2)
1212 IS
1213 BEGIN
1214
1215 -- Initialize message list if p_init_msg_list is set to TRUE.
1216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1217 FND_MSG_PUB.initialize;
1218 END IF;
1219
1220 -- Initialize API return status to success
1221 x_return_status := FND_API.G_RET_STS_SUCCESS;
1222
1223 -- Max Instance cannot be greater than Min Instances
1224 IF (p_MAX_Instances < 1 OR p_min_instances < 0 OR NVL(p_Max_Instances,p_Min_Instances+1) < p_Min_Instances ) THEN
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1227 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1228 FND_MESSAGE.Set_Token('COLUMN','MAX Instances', FALSE);
1229 FND_MSG_PUB.ADD;
1230 END IF;
1231 END IF;
1232
1233 END Validate_Min_Max_Instances;
1234
1235 PROCEDURE Validate_Resource (
1236 p_init_msg_list IN VARCHAR2,
1237 p_resource_id IN NUMBER,
1238 p_resource_type IN VARCHAR2,
1239 x_return_status OUT NOCOPY VARCHAR2,
1240 x_msg_count OUT NOCOPY NUMBER,
1241 x_msg_data OUT NOCOPY VARCHAR2)
1242 IS
1243
1244 -- For performance issues assuming only GROUPS to be valid
1245 -- and not using jtf_rs_all_resources_vl but jtf_rs_groups_vl
1246 CURSOR C_jtf_resources IS
1247 SELECT '1'
1248 FROM JTF_RS_GROUPS_VL
1249 WHERE group_id = p_resource_id;
1250
1251 l_tmp CHAR(1);
1252
1253 BEGIN
1254
1255 -- Initialize message list if p_init_msg_list is set to TRUE.
1256 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1257 FND_MSG_PUB.initialize;
1258 END IF;
1259
1260 -- Initialize API return status to success
1261 x_return_status := FND_API.G_RET_STS_SUCCESS;
1262
1263 IF (p_resource_id IS NOT NULL AND p_resource_id <> FND_API.G_MISS_NUM) THEN
1264 OPEN C_jtf_resources;
1265 FETCH C_jtf_resources INTO l_tmp;
1266 IF (C_jtf_resources%NOTFOUND) THEN
1267 CLOSE C_jtf_resources;
1268 x_return_status := FND_API.G_RET_STS_ERROR;
1269 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1270 FND_MESSAGE.Set_Name('IBC', 'API_INVALID_ID');
1271 FND_MESSAGE.Set_Token('COLUMN', 'RESOURCE_ID', FALSE);
1272 FND_MSG_PUB.ADD;
1273 END IF;
1274 ELSE
1275 CLOSE C_jtf_resources;
1276 END IF;
1277 END IF;
1278
1279 END Validate_Resource;
1280
1281 /****************************************************
1282 -------------FUNCTIONS--------------------------------------------------------------------------
1283 ****************************************************/
1284 -- --------------------------------------------------------------
1285 -- IBC_VALIDATE_PVT.isTranslationApproved
1286 --
1287 -- Checks to see if content item version translation is approved
1288 --
1289 -- --------------------------------------------------------------
1290 FUNCTION isTranslationApproved(f_citem_ver_id IN NUMBER
1291 ,f_language IN VARCHAR2
1292 )
1293 RETURN VARCHAR2
1294 IS
1295 CURSOR c_app IS
1296 SELECT
1297 citem_version_id
1298 FROM
1299 ibc_citem_versions_tl
1300 WHERE
1301 citem_version_id = f_citem_ver_id
1302 AND language = f_language
1303 AND citem_translation_status = IBC_UTILITIES_PUB.G_STV_APPROVED;
1304
1305 temp NUMBER;
1306 BEGIN
1307 open c_app;
1308 fetch c_app into temp;
1309
1310 if (c_app%NOTFOUND) then
1311 close c_app;
1312 RETURN FND_API.g_false;
1313 else
1314 close c_app;
1315 RETURN FND_API.g_true;
1316 end if;
1317 END isTranslationApproved;
1318
1319
1320 -- --------------------------------------------------------------
1321 -- IBC_VALIDATE_PVT.getItemBaseLanguage
1322 --
1323 -- Get the base language for the content item
1324 --
1325 -- --------------------------------------------------------------
1326 FUNCTION getItemBaseLanguage(f_content_item_id IN NUMBER)
1327 RETURN VARCHAR2
1328 IS
1329 CURSOR c_app IS
1330 SELECT
1331 base_language
1332 FROM
1333 ibc_content_items
1334 WHERE
1335 content_item_id = f_content_item_id;
1336
1337 temp VARCHAR2(35);
1338 BEGIN
1339 open c_app;
1340 fetch c_app into temp;
1341
1342 if (c_app%NOTFOUND) then
1343 close c_app;
1344 RETURN 'XXX';
1345 else
1346 close c_app;
1347 RETURN temp;
1348 end if;
1349 END getItemBaseLanguage;
1350
1351
1352 END Ibc_Validate_Pvt;
[Home] [Help]