[Home] [Help]
PACKAGE BODY: APPS.VEA_TPA_UTIL_PVT
Source
1 PACKAGE BODY VEA_TPA_UTIL_PVT as
2 /* $Header: VEATUTLB.pls 115.18 2004/07/27 00:08:33 rvishnuv ship $ */
3 --{
4 /*======================== vea_tpa_util_pvt ===========================*/
5 /*========================================================================
6 PURPOSE: TPA Utilities Package
7
8 NOTES: To run the script:
9
10 sql> start VEATUTLB.pls
11
12 HISTORY
13 Created N PARIKH 09/09/99 10:00 AM
14
15 =========================================================================*/
16
17 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'VEA_TPA_UTIL_PVT';
18 --
19 --
20 G_LAYER_MERGE BOOLEAN := FALSE;
21 --
22 --
23 /*========================================================================
24
25 PROCEDURE NAME: set_message
26
27 PURPOSE: Sets an error message on the message stack
28
29 ========================================================================*/
30 PROCEDURE set_message
31 (
32 p_error_name IN VARCHAR2,
33 p_token1 IN VARCHAR2 DEFAULT NULL,
34 p_value1 IN VARCHAR2 DEFAULT NULL,
35 p_token2 IN VARCHAR2 DEFAULT NULL,
36 p_value2 IN VARCHAR2 DEFAULT NULL,
37 p_token3 IN VARCHAR2 DEFAULT NULL,
38 p_value3 IN VARCHAR2 DEFAULT NULL,
39 p_token4 IN VARCHAR2 DEFAULT NULL,
40 p_value4 IN VARCHAR2 DEFAULT NULL,
41 p_token5 IN VARCHAR2 DEFAULT NULL,
42 p_value5 IN VARCHAR2 DEFAULT NULL,
43 p_token6 IN VARCHAR2 DEFAULT NULL,
44 p_value6 IN VARCHAR2 DEFAULT NULL,
45 p_token7 IN VARCHAR2 DEFAULT NULL,
46 p_value7 IN VARCHAR2 DEFAULT NULL,
47 p_token8 IN VARCHAR2 DEFAULT NULL,
48 p_value8 IN VARCHAR2 DEFAULT NULL
49 )
50 IS
51 --{
52 l_api_name CONSTANT VARCHAR2(30) := 'set_message';
53 l_location VARCHAR2(32767);
54 --}
55 BEGIN
56 --{
57 l_location := '0010';
58 --
59 IF p_error_name IS NOT NULL
60 THEN
61 --{
62 l_location := '0020';
63 --
64 FND_MESSAGE.SET_NAME('VEA',p_error_name);
65 --
66 --
67 l_location := '0030';
68 --
69 IF p_token1 IS NOT NULL
70 AND p_value1 IS NOT NULL
71 THEN
72 --{
73 l_location := '0040';
74 --
75 FND_MESSAGE.SET_TOKEN(p_token1, p_value1);
76 --}
77 END IF;
78 --
79 --
80 l_location := '0050';
81 --
82 IF p_token2 IS NOT NULL
83 AND p_value2 IS NOT NULL
84 THEN
85 --{
86 l_location := '0060';
87 --
88 FND_MESSAGE.SET_TOKEN(p_token2, p_value2);
89 --}
90 END IF;
91 --
92 --
93 l_location := '0070';
94 --
95 IF p_token3 IS NOT NULL
96 AND p_value3 IS NOT NULL
97 THEN
98 --{
99 l_location := '0080';
100 --
101 FND_MESSAGE.SET_TOKEN(p_token3, p_value3);
102 --}
103 END IF;
104 --
105 --
106 l_location := '0090';
107 --
108 IF p_token4 IS NOT NULL
109 AND p_value4 IS NOT NULL
110 THEN
111 --{
112 l_location := '0100';
113 --
114 FND_MESSAGE.SET_TOKEN(p_token4, p_value4);
115 --}
116 END IF;
117 --
118 --
119 l_location := '0110';
120 --
121 IF p_token5 IS NOT NULL
122 AND p_value5 IS NOT NULL
123 THEN
124 --{
125 l_location := '0120';
126 --
127 FND_MESSAGE.SET_TOKEN(p_token5, p_value5);
128 --}
129 END IF;
130 --
131 --
132 l_location := '0130';
133 --
134 IF p_token6 IS NOT NULL
135 AND p_value6 IS NOT NULL
136 THEN
137 --{
138 l_location := '0140';
139 --
140 FND_MESSAGE.SET_TOKEN(p_token6, p_value6);
141 --}
142 END IF;
143 --
144 --
145 l_location := '0150';
146 --
147 IF p_token7 IS NOT NULL
148 AND p_value7 IS NOT NULL
149 THEN
150 --{
151 l_location := '0160';
152 --
153 FND_MESSAGE.SET_TOKEN(p_token7, p_value7);
154 --}
155 END IF;
156 --
157 --
158 l_location := '0170';
159 --
160 IF p_token8 IS NOT NULL
161 AND p_value8 IS NOT NULL
162 THEN
163 --{
164 l_location := '0180';
165 --
166 FND_MESSAGE.SET_TOKEN(p_token8, p_value8);
167 --}
168 END IF;
169 --
170 --
171 /*
172 APP_EXCEPTION.RAISE_EXCEPTION
173 (
174 exception_code => 'POPS',
175 exception_text => FND_MESSAGE.GET
176 );
177 */
178 --}
179 END IF;
180 --}
181 EXCEPTION
182 --{
183 WHEN OTHERS THEN
184 RAISE;
185 --}
186 END set_message;
187 --
188 --
189 /*========================================================================
190
191 PROCEDURE NAME: display_message
192
193 PURPOSE: Displays the token-substituted error message in the concurrent
194 program log file.
195
196 ========================================================================*/
197 PROCEDURE display_message
198 (
199 p_error_name IN VARCHAR2,
200 p_token1 IN VARCHAR2 ,
201 p_value1 IN VARCHAR2 ,
202 p_token2 IN VARCHAR2 ,
203 p_value2 IN VARCHAR2 ,
204 p_token3 IN VARCHAR2 ,
205 p_value3 IN VARCHAR2 ,
206 p_token4 IN VARCHAR2 ,
207 p_value4 IN VARCHAR2 ,
208 p_token5 IN VARCHAR2 ,
209 p_value5 IN VARCHAR2 ,
210 p_token6 IN VARCHAR2 ,
211 p_value6 IN VARCHAR2 ,
212 p_token7 IN VARCHAR2 ,
213 p_value7 IN VARCHAR2 ,
214 p_token8 IN VARCHAR2 ,
215 p_value8 IN VARCHAR2
216 )
217 IS
218 --{
219 l_api_name CONSTANT VARCHAR2(30) := 'display_message';
220 l_location VARCHAR2(32767);
221 --}
222 BEGIN
223 --{
224 l_location := '0010';
225 --
226 IF p_error_name IS NOT NULL
227 THEN
228 --{
229 l_location := '0020';
230 --
231 vea_tpa_util_pvt.set_message
232 (
233 p_error_name => p_error_name,
234 p_token1 => p_token1,
235 p_value1 => p_value1,
236 p_token2 => p_token2,
237 p_value2 => p_value2,
238 p_token3 => p_token3,
239 p_value3 => p_value3,
240 p_token4 => p_token4,
241 p_value4 => p_value4,
242 p_token5 => p_token5,
243 p_value5 => p_value5,
244 p_token6 => p_token6,
245 p_value6 => p_value6,
246 p_token7 => p_token7,
247 p_value7 => p_value7,
248 p_token8 => p_token8,
249 p_value8 => p_value8
250 );
251 --
252 --
253 l_location := '0030';
254 --
255 FND_FILE.PUT_LINE
256 (
257 FND_FILE.LOG,
258 FND_MESSAGE.GET
259 );
260 --}
261 END IF;
262 --}
263 EXCEPTION
264 --{
265 WHEN OTHERS THEN
266 RAISE;
267 --}
268 END display_message;
269 --
270 --
271 /*========================================================================
272
273 PROCEDURE NAME: add_message
274
275 PURPOSE: Adds a message to API message stack.
276
277 ========================================================================*/
278 PROCEDURE add_message
279 (
280 p_error_name IN VARCHAR2,
281 p_token1 IN VARCHAR2 ,
282 p_value1 IN VARCHAR2 ,
283 p_token2 IN VARCHAR2 ,
284 p_value2 IN VARCHAR2 ,
285 p_token3 IN VARCHAR2 ,
286 p_value3 IN VARCHAR2 ,
287 p_token4 IN VARCHAR2 ,
288 p_value4 IN VARCHAR2 ,
289 p_token5 IN VARCHAR2 ,
290 p_value5 IN VARCHAR2 ,
291 p_token6 IN VARCHAR2 ,
292 p_value6 IN VARCHAR2 ,
293 p_token7 IN VARCHAR2 ,
294 p_value7 IN VARCHAR2 ,
295 p_token8 IN VARCHAR2 ,
296 p_value8 IN VARCHAR2
297 )
298 IS
299 --{
300 l_api_name CONSTANT VARCHAR2(30) := 'add_message';
301 l_location VARCHAR2(32767);
302 --}
303 BEGIN
304 --{
305 l_location := '0010';
306 --
307 IF p_error_name IS NOT NULL
308 THEN
309 --{
310 l_location := '0020';
311 --
312 vea_tpa_util_pvt.set_message
313 (
314 p_error_name => p_error_name,
315 p_token1 => p_token1,
316 p_value1 => p_value1,
317 p_token2 => p_token2,
318 p_value2 => p_value2,
319 p_token3 => p_token3,
320 p_value3 => p_value3,
321 p_token4 => p_token4,
322 p_value4 => p_value4,
323 p_token5 => p_token5,
324 p_value5 => p_value5,
325 p_token6 => p_token6,
326 p_value6 => p_value6,
327 p_token7 => p_token7,
328 p_value7 => p_value7,
329 p_token8 => p_token8,
330 p_value8 => p_value8
331 );
332 --
333 --
334 l_location := '0030';
335 --
336 FND_MSG_PUB.Add;
337 --}
338 END IF;
339 --}
340 EXCEPTION
341 --{
342 WHEN OTHERS THEN
343 RAISE;
344 --}
345 END add_message;
346 --
347 --
348 /*========================================================================
349
350 PROCEDURE NAME: add_message_and_raise
351
352 PURPOSE: Adds a message to API message stack.
353
354 ========================================================================*/
355 PROCEDURE add_message_and_raise
356 (
357 p_error_name IN VARCHAR2,
358 p_token1 IN VARCHAR2 ,
359 p_value1 IN VARCHAR2 ,
360 p_token2 IN VARCHAR2 ,
361 p_value2 IN VARCHAR2 ,
362 p_token3 IN VARCHAR2 ,
363 p_value3 IN VARCHAR2 ,
364 p_token4 IN VARCHAR2 ,
365 p_value4 IN VARCHAR2 ,
366 p_token5 IN VARCHAR2 ,
367 p_value5 IN VARCHAR2 ,
368 p_token6 IN VARCHAR2 ,
369 p_value6 IN VARCHAR2 ,
370 p_token7 IN VARCHAR2 ,
371 p_value7 IN VARCHAR2 ,
372 p_token8 IN VARCHAR2 ,
373 p_value8 IN VARCHAR2
374 )
375 IS
376 --{
377 l_api_name CONSTANT VARCHAR2(30) := 'add_message_and_raise';
378 l_location VARCHAR2(32767);
379 --}
380 BEGIN
381 --{
382 l_location := '0010';
383 --
384 IF p_error_name IS NOT NULL
385 THEN
386 --{
387 l_location := '0020';
388 --
389 vea_tpa_util_pvt.add_message
390 (
391 p_error_name => p_error_name,
392 p_token1 => p_token1,
393 p_value1 => p_value1,
394 p_token2 => p_token2,
395 p_value2 => p_value2,
396 p_token3 => p_token3,
397 p_value3 => p_value3,
398 p_token4 => p_token4,
399 p_value4 => p_value4,
400 p_token5 => p_token5,
401 p_value5 => p_value5,
402 p_token6 => p_token6,
403 p_value6 => p_value6,
404 p_token7 => p_token7,
405 p_value7 => p_value7,
406 p_token8 => p_token8,
407 p_value8 => p_value8
408 );
409 --
410 --
411 l_location := '0030';
412 --
413 RAISE FND_API.G_EXC_ERROR;
414 --}
415 END IF;
416 --}
417 EXCEPTION
418 --{
419 WHEN OTHERS THEN
420 RAISE;
421 --}
422 END add_message_and_raise;
423 --
424 --
425 /*========================================================================
426
427 PROCEDURE NAME: add_exc_message
428
429 PURPOSE: Adds a message for unexpected errors, to API message stack.
430
431 ========================================================================*/
432 PROCEDURE add_exc_message
433 (
434 p_package_name IN VARCHAR2,
435 p_api_name IN VARCHAR2,
436 p_location IN VARCHAR2
437 )
438 IS
439 --{
440 l_api_name CONSTANT VARCHAR2(30) := 'add_exc_message';
441 l_location VARCHAR2(32767);
442 --}
443 BEGIN
444 --{
445 l_location := '0010';
446 --
447 vea_tpa_util_pvt.add_message
448 (
449 p_error_name => 'VEA_PLSQL_UNEXPECTED_ERROR',
450 p_token1 => 'PACKAGE_NAME',
451 p_value1 => p_package_name,
452 p_token2 => 'API_NAME',
453 p_value2 => p_api_name,
454 p_token3 => 'LOCATION',
455 p_value3 => p_location,
456 p_token4 => 'ORACLE_ERROR_TEXT',
457 p_value4 => sqlerrm
458 );
459 --}
460 EXCEPTION
461 --{
462 WHEN OTHERS THEN
463 RAISE;
464 --}
465 END add_exc_message;
466 --
467 --
468 /*========================================================================
469
470 PROCEDURE NAME: add_exc_message
471
472 PURPOSE: Adds a message for unexpected errors, to API message stack
473 and raises an exception.
474
475 ========================================================================*/
476 PROCEDURE add_exc_message_and_raise
477 (
478 p_package_name IN VARCHAR2,
479 p_api_name IN VARCHAR2,
480 p_location IN VARCHAR2
481 )
482 IS
483 --{
484 l_api_name CONSTANT VARCHAR2(30) := 'add_exc_message_and_raise';
485 l_location VARCHAR2(32767);
486 --}
487 BEGIN
488 --{
489 l_location := '0010';
490 --
491 --
492 vea_tpa_util_pvt.add_exc_message
493 (
494 p_package_name => p_package_name,
495 p_api_name => p_api_name,
496 p_location => p_location
497 );
498 --
499 --
500 l_location := '0020';
501 --
502 RAISE vea_tpa_util_pvt.e_unexpected_error;
503 --}
504 EXCEPTION
505 --{
506 WHEN OTHERS THEN
507 RAISE;
508 --}
509 END add_exc_message_and_raise;
510 --
511 --
512 /*========================================================================
513
514 PROCEDURE NAME: handle_error
515
516 PURPOSE: Standard error handler as per Business object API standards
517
518 ========================================================================*/
519 PROCEDURE
520 handle_error
521 (
522 p_error_type IN VARCHAR2,
523 p_savepoint_name IN VARCHAR2,
524 p_package_name IN VARCHAR2,
525 p_api_name IN VARCHAR2,
526 p_location IN VARCHAR2,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 x_api_return_status OUT NOCOPY VARCHAR2
530 )
531 IS
532 --{
533 l_api_name CONSTANT VARCHAR2(30) := 'handle_error';
534 l_location VARCHAR2(32767);
535 l_statement VARCHAR2(32767);
536 --}
537 BEGIN
538 --{
539 l_location := '0010';
540 --
541 l_statement := 'ROLLBACK TO ' || p_savepoint_name;
542 --
543 --
544 l_location := '0011';
545 --
546 EXECUTE IMMEDIATE l_statement;
547 --
548 --
549 l_location := '0020';
550 --
551 IF p_error_type = G_ERROR
552 THEN
553 --{
554 l_location := '0030';
555 --
556 x_api_return_status := FND_API.G_RET_STS_ERROR;
557 --}
558 ELSIF p_error_type = G_UNEXPECTED_ERROR
559 THEN
560 --{
561 l_location := '0040';
562 --
563 x_api_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 --}
565 ELSE
566 --{
567 l_location := '0050';
568 --
569 x_api_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 --
571 --
572 l_location := '0060';
573 --
574 --IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
575 --THEN
576 --{
577 l_location := '0070';
578 --
579 vea_tpa_util_pvt.add_exc_message
580 (
581 p_package_name => p_package_name,
582 p_api_name => p_api_name,
583 p_location => p_location
584 );
585 /*
586 FND_MSG_PUB.Add_Exc_Msg
587 (
588 p_package_name,
589 p_api_name
590 );
591 */
592 --}
593 --END IF;
594 --}
595 END IF;
596 --
597 --
598 l_location := '0080';
599 --
600 FND_MSG_PUB.Count_and_Get
601 (
602 p_count => x_msg_count,
603 p_data => x_msg_data,
604 p_encoded => FND_API.G_FALSE
605 );
606 --
607 --
608 --}
609 EXCEPTION
610 --{
611 WHEN OTHERS
612 THEN
613 --{
614 add_exc_message_and_raise
615 (
616 p_package_name => G_PACKAGE_NAME,
617 p_api_name => l_api_name,
618 p_location => l_location
619 );
620 --}
621 --}
622 END handle_error;
623 --
624 --
625 /*========================================================================
626
627 PROCEDURE NAME: api_post_call
628
629 PURPOSE: Implements standard API post call process, as per business
630 object API standards
631
632 ========================================================================*/
633 PROCEDURE
634 api_post_call
635 (
636 p_msg_count IN NUMBER,
637 p_msg_data IN VARCHAR2,
638 p_api_return_status IN VARCHAR2
639 )
640 IS
641 --{
642 l_api_name CONSTANT VARCHAR2(30) := 'api_post_call';
643 l_location VARCHAR2(32767);
644 --
645 --
646 l_count NUMBER;
647 --}
648 BEGIN
649 --{
650 l_location := '0010';
651 --
652 IF p_msg_count = 1
653 THEN
654 --{
655 l_location := '0020';
656 --
657 FND_FILE.PUT_LINE
658 (
659 FND_FILE.LOG,
660 p_msg_data
661 );
662 --}
663 ELSE
664 --{
665 l_location := '0030';
666 --
667 FOR l_count IN 1..NVL(p_msg_count,0)
668 LOOP
669 --{
670 l_location := '0040';
671 --
672 FND_FILE.PUT_LINE
673 (
674 FND_FILE.LOG,
675 FND_MSG_PUB.GET( p_encoded => FND_API.G_FALSE )
676 );
677 --}
678 END LOOP;
679 --}
680 END IF;
681 --
682 --
683 l_location := '0050';
684 --
685 IF nvl(p_api_return_status, FND_API.G_RET_STS_SUCCESS ) = FND_API.G_RET_STS_SUCCESS
686 THEN
687 --{
688 l_location := '0060';
689 --
690 NULL;
691 --}
692 ELSE
693 --{
694 l_location := '0070';
695 --
696 ROLLBACK WORK;
697 --
698 --
699 l_location := '0080';
700 --
701 --APP_EXCEPTION.RAISE_EXCEPTION;
702 RAISE FND_API.G_EXC_ERROR;
703 --}
704 END IF;
705 --}
706 EXCEPTION
707 --{
708 WHEN OTHERS
709 THEN
710 --{
711 RAISE;
712 --}
713 --}
714 END api_post_call;
715 --
716 --
717 /*========================================================================
718
719 PROCEDURE NAME: api_header
720
721 PURPOSE: Standard API header as per Business object API standards
722
723 ========================================================================*/
724 PROCEDURE
725 api_header
726 (
727 p_package_name IN VARCHAR2,
728 p_api_name IN VARCHAR2,
729 p_api_type IN VARCHAR2,
730 p_api_current_version IN NUMBER,
731 p_api_caller_version IN NUMBER,
732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
733 x_savepoint_name OUT NOCOPY VARCHAR2,
734 x_api_return_status OUT NOCOPY VARCHAR2
735 )
736 IS
737 --{
738 l_api_name CONSTANT VARCHAR2(30) := 'api_header';
739 l_location VARCHAR2(32767);
740 l_savepoint_name VARCHAR2(30);
741 l_statement VARCHAR2(32767);
742 --}
743 BEGIN
744 --{
745 -- Standard Start of API Savepoint
746 --
747 l_location := '0010';
748 --
749 l_savepoint_name := SUBSTR(p_package_name,1,18)
750 || '_'
751 || SUBSTR(p_api_name,1,7)
752 || '_'
753 || p_api_type;
754 --
755 --
756 l_location := '0020';
757 --
758 x_savepoint_name := l_savepoint_name;
759 --
760 --
761 l_location := '0030';
762 --
763 l_statement := 'SAVEPOINT ' || l_savepoint_name;
764 --
765 --
766 l_location := '0031';
767 --
768 EXECUTE IMMEDIATE l_statement;
769 --
770 --
771 -- Standard call to check for call compatibility
772 --
773 l_location := '0040';
774 --
775 IF NOT FND_API.Compatible_API_Call
776 (
777 p_api_current_version,
778 p_api_caller_version,
779 p_api_name,
780 p_package_name
781 )
782 THEN
783 --{
784 l_location := '0050';
785 --
786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
787 --}
788 END IF;
789 --
790 --
791 -- Initialize message list if p_init_msg_list is set to TRUE
792 --
793 l_location := '0060';
794 --
795 IF FND_API.to_Boolean( p_init_msg_list )
796 THEN
797 --{
798 l_location := '0070';
799 --
800 FND_MSG_PUB.initialize;
801 --}
802 END IF;
803 --
804 --
805 -- Initialize API return status to Success
806 --
807 l_location := '0080';
808 --
809 x_api_return_status := FND_API.G_RET_STS_SUCCESS;
810 --}
811 EXCEPTION
812 --{
813 WHEN OTHERS
814 THEN
815 --{
816 add_exc_message_and_raise
817 (
818 p_package_name => G_PACKAGE_NAME,
819 p_api_name => l_api_name,
820 p_location => l_location
821 );
822 --}
823 --}
824 END api_header;
825 --
826 --
827 /*========================================================================
828
829 PROCEDURE NAME: api_footer
830
831 PURPOSE: Standard API footer as per Business object API standards
832
833 ========================================================================*/
834 PROCEDURE
835 api_footer
836 (
837 p_commit IN VARCHAR2 := FND_API.G_FALSE,
838 x_msg_count OUT NOCOPY NUMBER,
839 x_msg_data OUT NOCOPY VARCHAR2
840 )
841 IS
842 --{
843 l_api_name CONSTANT VARCHAR2(30) := 'api_footer';
844 l_location VARCHAR2(32767);
845 --}
846 BEGIN
847 --{
848 --
849 --
850 -- Standard check of p_commit
851 --
852 l_location := '0010';
853 --
854 IF FND_API.to_Boolean( p_commit )
855 THEN
856 --{
857 l_location := '0020';
858 --
859 commit work;
860 --}
861 END IF;
862 --
863 --
864 -- Standard call to get message count
865 -- and if count is 1, get message info
866 --
867 l_location := '0030';
868 --
869 FND_MSG_PUB.Count_and_Get
870 (
871 p_count => x_msg_count,
872 p_data => x_msg_data,
873 p_encoded => FND_API.G_FALSE
874 );
875 --}
876 EXCEPTION
877 --{
878 WHEN OTHERS
879 THEN
880 --{
881 add_exc_message_and_raise
882 (
883 p_package_name => G_PACKAGE_NAME,
884 p_api_name => l_api_name,
885 p_location => l_location
886 );
887 --}
888 --}
889 END api_footer;
890 --
891 --
892 /*========================================================================
893
894 PROCEDURE NAME: get_profile_value
895
896 PURPOSE: Obtains profile value - Cover API for FND API
897
898 ========================================================================*/
899 FUNCTION
900 get_profile_value
901 (
902 p_profile_name IN VARCHAR2
903 )
904 RETURN VARCHAR2
905 IS
906 --{
907 l_api_name CONSTANT VARCHAR2(30) := 'get_profile_value';
908 l_location VARCHAR2(32767);
909 --}
910 BEGIN
911 --{
912 l_location := '0010';
913
914 --
915 RETURN( FND_PROFILE.VALUE(p_profile_name) );
916 --}
917 EXCEPTION
918 --{
919 WHEN OTHERS
920 THEN
921 --{
922 add_exc_message_and_raise
923 (
924 p_package_name => G_PACKAGE_NAME,
925 p_api_name => l_api_name,
926 p_location => l_location
927 );
928 --}
929 --}
930 END get_profile_value;
931 --
932 --
933 /*========================================================================
934
935 PROCEDURE NAME: get_curr_layer_provider_code
936
937 PURPOSE: Convenience method to obtain current layer provider code
938 profile value
939
940 ========================================================================*/
941 FUNCTION
942 get_curr_layer_provider_code
943 RETURN VARCHAR2
944 IS
945 --{
946 l_api_name CONSTANT VARCHAR2(30) := 'get_curr_layer_provider_code';
947 l_location VARCHAR2(32767);
948 --}
949 BEGIN
950 --{
951 l_location := '0010';
952 --
953 RETURN( get_profile_value('VEA_LAYER_PROVIDER') );
954 --}
955 EXCEPTION
956 --{
957 WHEN OTHERS
958 THEN
959 --{
960 add_exc_message_and_raise
961 (
962 p_package_name => G_PACKAGE_NAME,
963 p_api_name => l_api_name,
964 p_location => l_location
965 );
966 --}
967 --}
968 END get_curr_layer_provider_code;
969 --
970 --
971 /*========================================================================
972
973 PROCEDURE NAME: get_curr_customer_name
974
975 PURPOSE: Convenience method to obtain current customer name
976 profile value
977
978 ========================================================================*/
979 FUNCTION
980 get_curr_customer_name
981 RETURN VARCHAR2
982 IS
983 --{
984 l_api_name CONSTANT VARCHAR2(30) := 'get_curr_customer_name';
985 l_location VARCHAR2(32767);
986 --}
987 BEGIN
988 --{
989 l_location := '0010';
990 --
991 RETURN( get_profile_value('VEA_CUSTOMER') );
992 --}
993 EXCEPTION
994 --{
995 WHEN OTHERS
996 THEN
997 --{
998 add_exc_message_and_raise
999 (
1000 p_package_name => G_PACKAGE_NAME,
1001 p_api_name => l_api_name,
1002 p_location => l_location
1003 );
1004 --}
1005 --}
1006 END get_curr_customer_name;
1007 --
1008 --
1009 /*========================================================================
1010
1011 PROCEDURE NAME: get_user_id
1012
1013 PURPOSE: Returns current user ID - Cover API for FND API
1014
1015 ========================================================================*/
1016 FUNCTION
1017 get_user_id
1018 RETURN NUMBER
1019 IS
1020 --{
1021 l_api_name CONSTANT VARCHAR2(30) := 'get_user_id';
1022 l_location VARCHAR2(32767);
1023 --}
1024 BEGIN
1025 --{
1026 l_location := '0010';
1027 --
1028 RETURN( FND_GLOBAL.USER_ID );
1029 --}
1030 EXCEPTION
1031 --{
1032 WHEN OTHERS
1033 THEN
1034 --{
1035 add_exc_message_and_raise
1036 (
1037 p_package_name => G_PACKAGE_NAME,
1038 p_api_name => l_api_name,
1039 p_location => l_location
1040 );
1041 --}
1042 --}
1043 END get_user_id;
1044 --
1045 --
1046 /*========================================================================
1047
1048 PROCEDURE NAME: get_login_id
1049
1050 PURPOSE: Returns current login ID - Cover API for FND API
1051
1052 ========================================================================*/
1053 FUNCTION
1054 get_login_id
1055 RETURN NUMBER
1056 IS
1057 --{
1058 l_api_name CONSTANT VARCHAR2(30) := 'get_login_id';
1059 l_location VARCHAR2(32767);
1060 --}
1061 BEGIN
1062 --{
1063 l_location := '0010';
1064 --
1065 RETURN( FND_GLOBAL.LOGIN_ID );
1066 --}
1067 EXCEPTION
1068 --{
1069 WHEN OTHERS
1070 THEN
1071 --{
1072 add_exc_message_and_raise
1073 (
1074 p_package_name => G_PACKAGE_NAME,
1075 p_api_name => l_api_name,
1076 p_location => l_location
1077 );
1078 --}
1079 --}
1080 END get_login_id;
1081 --
1082 --
1083 /*========================================================================
1084
1085 PROCEDURE NAME: get_constants
1086
1087 PURPOSE: Obtains values of various constants required by java clients
1088 - Cover API to access package level global constants
1089
1090 ========================================================================*/
1091 PROCEDURE
1092 get_constants
1093 (
1094 p_api_version IN NUMBER,
1095 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1096 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1097 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1098 x_return_status OUT NOCOPY VARCHAR2,
1099 x_msg_count OUT NOCOPY NUMBER,
1100 x_msg_data OUT NOCOPY VARCHAR2,
1101 x_true OUT NOCOPY VARCHAR2,
1102 x_false OUT NOCOPY VARCHAR2,
1103 x_valid_level_full OUT NOCOPY NUMBER,
1104 x_valid_level_none OUT NOCOPY NUMBER,
1105 x_success OUT NOCOPY VARCHAR2,
1106 x_error OUT NOCOPY VARCHAR2,
1107 x_unexpected_error OUT NOCOPY VARCHAR2,
1108 x_next OUT NOCOPY NUMBER
1109 )
1110 IS
1111 --{
1112 l_api_name CONSTANT VARCHAR2(30) := 'get_constants';
1113 l_api_version CONSTANT NUMBER := 1.0;
1114 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1115 --
1116 --
1117 l_location VARCHAR2(32767);
1118 l_savepoint_name VARCHAR2(30);
1119 --}
1120 BEGIN
1121 --{
1122 --
1123 -- Standard API Header
1124 --
1125 l_location := '0010';
1126 --
1127 vea_tpa_util_pvt.api_header
1128 (
1129 p_package_name => G_PACKAGE_NAME,
1130 p_api_name => l_api_name,
1131 p_api_type => l_api_type,
1132 p_api_current_version => l_api_version,
1133 p_api_caller_version => p_api_version,
1134 p_init_msg_list => p_init_msg_list,
1135 x_savepoint_name => l_savepoint_name,
1136 x_api_return_status => x_return_status
1137 );
1138 --
1139 --
1140 --{ API Body
1141 --
1142 --
1143 l_location := '0010';
1144 --
1145 x_true := FND_API.G_TRUE;
1146 x_false := FND_API.G_FALSE;
1147 --
1148 l_location := '0020';
1149 --
1150 x_valid_level_full := FND_API.G_VALID_LEVEL_FULL;
1151 x_valid_level_none := FND_API.G_VALID_LEVEL_NONE;
1152 --
1153 l_location := '0030';
1154 --
1155 x_success := FND_API.G_RET_STS_SUCCESS;
1156 x_error := FND_API.G_RET_STS_ERROR;
1157 x_unexpected_error := FND_API.G_RET_STS_UNEXP_ERROR;
1158 --
1159 --
1160 l_location := '0040';
1161 --
1162 x_next := FND_MSG_PUB.G_NEXT;
1163 --
1164 --
1165 --} API Body
1166 --
1167 --
1168 -- Standard API Footer
1169 --
1170 l_location := '0040';
1171 --
1172 vea_tpa_util_pvt.api_footer
1173 (
1174 p_commit => p_commit,
1175 x_msg_count => x_msg_count,
1176 x_msg_data => x_msg_data
1177 );
1178 --}
1179 EXCEPTION
1180 --{
1181 WHEN FND_API.G_EXC_ERROR
1182 THEN
1183 --{
1184 --RAISE;
1185 vea_tpa_util_pvt.handle_error
1186 (
1187 p_error_type => vea_tpa_util_pvt.G_ERROR,
1188 p_savepoint_name => l_savepoint_name,
1189 p_package_name => G_PACKAGE_NAME,
1190 p_api_name => l_api_name,
1191 p_location => l_location,
1192 x_msg_count => x_msg_count,
1193 x_msg_data => x_msg_data,
1194 x_api_return_status => x_return_status
1195 );
1196 --}
1197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1198 THEN
1199 --{
1200 --RAISE;
1201 vea_tpa_util_pvt.handle_error
1202 (
1203 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1204 p_savepoint_name => l_savepoint_name,
1205 p_package_name => G_PACKAGE_NAME,
1206 p_api_name => l_api_name,
1207 p_location => l_location,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 x_api_return_status => x_return_status
1211 );
1212 --}
1213 WHEN OTHERS
1214 THEN
1215 --{
1216 --RAISE;
1217 vea_tpa_util_pvt.handle_error
1218 (
1219 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1220 p_savepoint_name => l_savepoint_name,
1221 p_package_name => G_PACKAGE_NAME,
1222 p_api_name => l_api_name,
1223 p_location => l_location,
1224 x_msg_count => x_msg_count,
1225 x_msg_data => x_msg_data,
1226 x_api_return_status => x_return_status
1227 );
1228 --}
1229 --}
1230 END get_constants;
1231 --
1232 --
1233 /*========================================================================
1234
1235 PROCEDURE NAME: isVEAInstalled
1236
1237 PURPOSE: Reports installation status of VEA product.
1238
1239 ========================================================================*/
1240 PROCEDURE
1241 isVEAInstalled
1242 (
1243 p_api_version IN NUMBER,
1244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1247 x_return_status OUT NOCOPY VARCHAR2,
1248 x_msg_count OUT NOCOPY NUMBER,
1249 x_msg_data OUT NOCOPY VARCHAR2,
1250 x_vea_install_status OUT NOCOPY VARCHAR2
1251 )
1252 IS
1253 --{
1254 l_api_name CONSTANT VARCHAR2(30) := 'isVEAInstalled';
1255 l_api_version CONSTANT NUMBER := 1.0;
1256 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1257 --
1258 --
1259 l_location VARCHAR2(32767);
1260 l_savepoint_name VARCHAR2(30);
1261 l_install BOOLEAN;
1262 l_org VARCHAR2(32767);
1263 --}
1264 BEGIN
1265 --{
1266 --
1267 -- Standard API Header
1268 --
1269 l_location := '0010';
1270 --
1271 vea_tpa_util_pvt.api_header
1272 (
1273 p_package_name => G_PACKAGE_NAME,
1274 p_api_name => l_api_name,
1275 p_api_type => l_api_type,
1276 p_api_current_version => l_api_version,
1277 p_api_caller_version => p_api_version,
1278 p_init_msg_list => p_init_msg_list,
1279 x_savepoint_name => l_savepoint_name,
1280 x_api_return_status => x_return_status
1281 );
1282 --
1283 --
1284 --{ API Body
1285 --
1286 --
1287 l_location := '0020';
1288 --
1289 l_install := fnd_installation.get(663,663,x_vea_install_status,l_org);
1290 --
1291 --
1292 --} API Body
1293 --
1294 --
1295 -- Standard API Footer
1296 --
1297 l_location := '0030';
1298 --
1299 vea_tpa_util_pvt.api_footer
1300 (
1301 p_commit => p_commit,
1302 x_msg_count => x_msg_count,
1303 x_msg_data => x_msg_data
1304 );
1305 --}
1306 EXCEPTION
1307 --{
1308 WHEN FND_API.G_EXC_ERROR
1309 THEN
1310 --{
1311 --RAISE;
1312 vea_tpa_util_pvt.handle_error
1313 (
1314 p_error_type => vea_tpa_util_pvt.G_ERROR,
1315 p_savepoint_name => l_savepoint_name,
1316 p_package_name => G_PACKAGE_NAME,
1317 p_api_name => l_api_name,
1318 p_location => l_location,
1319 x_msg_count => x_msg_count,
1320 x_msg_data => x_msg_data,
1321 x_api_return_status => x_return_status
1322 );
1323 --}
1324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1325 THEN
1326 --{
1327 --RAISE;
1328 vea_tpa_util_pvt.handle_error
1329 (
1330 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1331 p_savepoint_name => l_savepoint_name,
1332 p_package_name => G_PACKAGE_NAME,
1333 p_api_name => l_api_name,
1334 p_location => l_location,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data,
1337 x_api_return_status => x_return_status
1338 );
1339 --}
1340 WHEN OTHERS
1341 THEN
1342 --{
1343 --RAISE;
1344 vea_tpa_util_pvt.handle_error
1345 (
1346 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1347 p_savepoint_name => l_savepoint_name,
1348 p_package_name => G_PACKAGE_NAME,
1349 p_api_name => l_api_name,
1350 p_location => l_location,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data,
1353 x_api_return_status => x_return_status
1354 );
1355 --}
1356 --}
1357 END isVEAInstalled;
1358 --
1359 --
1360 /*========================================================================
1361
1362 PROCEDURE NAME: isVEAInstalled
1363
1364 PURPOSE: Returns True, if VEA product is installed.
1365
1366 ========================================================================*/
1367 FUNCTION
1368 is_vea_installed
1369 RETURN BOOLEAN
1370 IS
1371 --{
1372 l_api_name CONSTANT VARCHAR2(30) := 'is_vea_installed';
1373 l_location VARCHAR2(32767);
1374 l_install BOOLEAN;
1375 l_vea_status VARCHAR2(32767);
1376 l_org VARCHAR2(32767);
1377 --}
1378 BEGIN
1379 --{
1380 l_location := '0010';
1381 --
1382 l_install := fnd_installation.get(663,663,l_vea_status,l_org);
1383 --
1384 --
1385 l_location := '0020';
1386 --
1387 IF l_vea_status = 'I'
1388 THEN
1389 RETURN(TRUE);
1390 ELSE
1391 RETURN(FALSE);
1392 END IF;
1393 --}
1394 EXCEPTION
1395 --{
1396 WHEN OTHERS
1397 THEN
1398 --{
1399 add_exc_message_and_raise
1400 (
1401 p_package_name => G_PACKAGE_NAME,
1402 p_api_name => l_api_name,
1403 p_location => l_location
1404 );
1405 --}
1406 --}
1407 END is_vea_installed;
1408 --
1409 --
1410 /*========================================================================
1411
1412 PROCEDURE NAME: preProcess
1413
1414 PURPOSE: Handles processing at the beginning of layer merge. It deletes
1415 all information in the repository pertaining to the specified
1416 TP layer developed by the specified layer provider for the
1417 specified TP application. It also sets a variable to indicate
1418 that layer merge process has begun.
1419 ========================================================================*/
1420 PROCEDURE
1421 preProcess
1422 (
1423 p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1427 x_return_status OUT NOCOPY VARCHAR2,
1428 x_msg_count OUT NOCOPY NUMBER,
1429 x_msg_data OUT NOCOPY VARCHAR2,
1430 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1431 p_application_short_name IN vea_packages.application_short_name%TYPE,
1432 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
1433 p_tp_layer_name IN vea_tp_layers.name%TYPE
1434 )
1435 IS
1436 --{
1437 l_api_name CONSTANT VARCHAR2(30) := 'preProcess';
1438 l_api_version CONSTANT NUMBER := 1.0;
1439 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1440 --
1441 --
1442 l_location VARCHAR2(32767);
1443 l_savepoint_name VARCHAR2(30);
1444 --
1445 --
1446 l_tp_layer_id NUMBER;
1447 --
1448 --
1449 --}
1450 BEGIN
1451 --{
1452 l_location := '0010';
1453 --
1454 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
1455 THEN
1456 RETURN;
1457 END IF;
1458 --
1459 --
1460 -- Standard API Header
1461 --
1462 l_location := '0020';
1463 --
1464 vea_tpa_util_pvt.api_header
1465 (
1466 p_package_name => G_PACKAGE_NAME,
1467 p_api_name => l_api_name,
1468 p_api_type => l_api_type,
1469 p_api_current_version => l_api_version,
1470 p_api_caller_version => p_api_version,
1471 p_init_msg_list => p_init_msg_list,
1472 x_savepoint_name => l_savepoint_name,
1473 x_api_return_status => x_return_status
1474 );
1475 --
1476 --
1477 --{ API Body
1478 --
1479 --
1480 --
1481 l_location := '0030';
1482 --
1483 G_LAYER_MERGE := TRUE;
1484 --
1485 --
1486 g_current_layer_provider_code := get_curr_layer_provider_code;
1487 --
1488 g_tpLyr_fileId_dbId_tbl.delete;
1489 g_tpLyr_fileId_dbId_ext_tbl.delete;
1490 g_PU_fileId_dbId_tbl.delete;
1491 g_PU_fileId_dbId_ext_tbl.delete;
1492 g_pend_puId_tpaPUId_tbl.delete;
1493 g_pend_puId_tpaPUId_ext_tbl.delete;
1494 --
1495 --
1496 l_location := '0040';
1497 --
1498 vea_layers_sv.populateLayerActiveTable
1499 (
1500 p_layer_provider_code => p_layer_provider_code
1501 );
1502 --
1503 --
1504 l_location := '0050';
1505 --
1506 l_tp_layer_id :=
1507 vea_tp_layers_sv.getId
1508 (
1509 p_layer_provider_code => p_layer_provider_code,
1510 p_tp_layer_name => p_tp_layer_name
1511 );
1512 --
1513 --
1514 l_location := '0060';
1515 --
1516 vea_tp_layers_sv.delete_rows
1517 (
1518 p_layer_provider_code => p_layer_provider_code,
1519 p_tp_layer_id => l_tp_layer_id,
1520 p_tp_layer_name => p_tp_layer_name,
1521 p_application_short_name => p_application_short_name
1522 );
1523 --
1524 --
1525 --} API Body
1526 --
1527 --
1528 -- Standard API Footer
1529 --
1530 l_location := '0070';
1531 --
1532 vea_tpa_util_pvt.api_footer
1533 (
1534 p_commit => p_commit,
1535 x_msg_count => x_msg_count,
1536 x_msg_data => x_msg_data
1537 );
1538 --}
1539 EXCEPTION
1540 --{
1541 WHEN FND_API.G_EXC_ERROR
1542 THEN
1543 --{
1544 --RAISE;
1545 vea_tpa_util_pvt.handle_error
1546 (
1547 p_error_type => vea_tpa_util_pvt.G_ERROR,
1548 p_savepoint_name => l_savepoint_name,
1549 p_package_name => G_PACKAGE_NAME,
1550 p_api_name => l_api_name,
1551 p_location => l_location,
1552 x_msg_count => x_msg_count,
1553 x_msg_data => x_msg_data,
1554 x_api_return_status => x_return_status
1555 );
1556 --}
1557 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1558 THEN
1559 --{
1560 --RAISE;
1561 vea_tpa_util_pvt.handle_error
1562 (
1563 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1564 p_savepoint_name => l_savepoint_name,
1565 p_package_name => G_PACKAGE_NAME,
1566 p_api_name => l_api_name,
1567 p_location => l_location,
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data,
1570 x_api_return_status => x_return_status
1571 );
1572 --}
1573 WHEN OTHERS
1574 THEN
1575 --{
1576 --RAISE;
1577 vea_tpa_util_pvt.handle_error
1578 (
1579 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1580 p_savepoint_name => l_savepoint_name,
1581 p_package_name => G_PACKAGE_NAME,
1582 p_api_name => l_api_name,
1583 p_location => l_location,
1584 x_msg_count => x_msg_count,
1585 x_msg_data => x_msg_data,
1586 x_api_return_status => x_return_status
1587 );
1588 --}
1589 --}
1590 END preProcess;
1591 --
1592 --
1593 /*========================================================================
1594
1595 PROCEDURE NAME: postProcess
1596
1597 PURPOSE: Handles processing at the end of layer merge. It deletes any
1598 TP layers not licensed to current customer. It also sets a
1599 variable to indicate that layer merge process has ended.
1600
1601
1602 MODIFIED: This procedure has been modified by Ravi (rvishnuv) on 09/29/2000.
1603 An additional parameter "layer_provider_name" has been added to
1604 the existing parameters list for the procedure.
1605
1606 ========================================================================*/
1607 PROCEDURE
1608 postProcess
1609 (
1610 p_api_version IN NUMBER,
1611 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1612 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1613 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1614 x_return_status OUT NOCOPY VARCHAR2,
1615 x_msg_count OUT NOCOPY NUMBER,
1616 x_msg_data OUT NOCOPY VARCHAR2,
1617 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
1618 p_application_short_name IN vea_packages.application_short_name%TYPE,
1619 p_tp_layer_id IN vea_tp_layers.tp_layer_id%TYPE,
1620 p_tp_layer_name IN vea_tp_layers.name%TYPE
1621 )
1622 IS
1623 --{
1624 l_api_name CONSTANT VARCHAR2(30) := 'postProcess';
1625 l_api_version CONSTANT NUMBER := 1.0;
1626 l_api_type CONSTANT VARCHAR2(3) := vea_tpa_util_pvt.G_PUBLIC_API;
1627 --
1628 --
1629 l_location VARCHAR2(32767);
1630 l_savepoint_name VARCHAR2(30);
1631 l_tp_layer_id NUMBER;
1632 l_index NUMBER;
1633 l_program_unit_id NUMBER;
1634 l_tpa_program_unit_id NUMBER;
1635 --
1636 --
1637 --}
1638 BEGIN
1639 --{
1640 l_location := '0010';
1641 --
1642 IF NOT( vea_tpa_util_pvt.is_vea_installed() )
1643 THEN
1644 RETURN;
1645 END IF;
1646 --
1647 --
1648 -- Standard API Header
1649 --
1650 l_location := '0020';
1651
1652
1653 --
1654 vea_tpa_util_pvt.api_header
1655 (
1656 p_package_name => G_PACKAGE_NAME,
1657 p_api_name => l_api_name,
1658 p_api_type => l_api_type,
1659 p_api_current_version => l_api_version,
1660 p_api_caller_version => p_api_version,
1661 p_init_msg_list => p_init_msg_list,
1662 x_savepoint_name => l_savepoint_name,
1663 x_api_return_status => x_return_status
1664 );
1665 --
1666 --
1667 --{ API Body
1668 --
1669 --
1670 l_location := '0030';
1671 --
1672 --
1673 l_index := g_pend_puId_tpaPUId_tbl.FIRST;
1674 --
1675 WHILE l_index IS NOT NULL
1676 LOOP
1677 --{
1678 l_location := '0040';
1679 --
1680 l_program_unit_id := g_pend_puId_tpaPUId_tbl(l_index).key;
1681 --
1682 --
1683 l_location := '0050';
1684 --
1685 vea_tpa_util_pvt.get
1686 (
1687 p_key => g_pend_puId_tpaPUId_tbl(l_index).value,
1688 p_cache_tbl => g_PU_fileId_dbId_tbl,
1689 p_cache_ext_tbl => g_PU_fileId_dbId_ext_tbl,
1690 x_value => l_tpa_program_unit_id
1691 );
1692 --
1693 --
1694 l_location := '0060';
1695 --
1696 VEA_PROGRAM_UNITS_SV.deleteUnreferencedProgramUnits
1697 (
1698 p_layer_provider_code => p_layer_provider_code,
1699 p_program_unit_id => l_program_unit_id,
1700 p_tpa_program_unit_id => l_tpa_program_unit_id
1701 );
1702 --
1703 --
1704 l_location := '0070';
1705 --
1706 UPDATE vea_program_units
1707 SET tpa_program_unit_id = l_tpa_program_unit_id
1708 WHERE layer_provider_code = p_layer_provider_code
1709 AND program_unit_id = l_program_unit_id;
1710 --
1711 --
1712 l_index := g_pend_puId_tpaPUId_tbl.NEXT(l_index);
1713 --}
1714 END LOOP;
1715 --
1716 --
1717 --
1718 l_location := '0080';
1719 --
1720 --
1721 l_index := g_pend_puId_tpaPUId_ext_tbl.FIRST;
1722 --
1723 WHILE l_index IS NOT NULL
1724 LOOP
1725 --{
1726 l_location := '0090';
1727 --
1728 l_program_unit_id := g_pend_puId_tpaPUId_ext_tbl(l_index).key;
1729 --
1730 --
1731 l_location := '0100';
1732 --
1733 vea_tpa_util_pvt.get
1734 (
1735 p_key => g_pend_puId_tpaPUId_ext_tbl(l_index).value,
1736 p_cache_tbl => g_PU_fileId_dbId_tbl,
1737 p_cache_ext_tbl => g_PU_fileId_dbId_ext_tbl,
1738 x_value => l_tpa_program_unit_id
1739 );
1740 --
1741 --
1742 l_location := '0110';
1743 --
1744 VEA_PROGRAM_UNITS_SV.deleteUnreferencedProgramUnits
1745 (
1746 p_layer_provider_code => p_layer_provider_code,
1747 p_program_unit_id => l_program_unit_id,
1748 p_tpa_program_unit_id => l_tpa_program_unit_id
1749 );
1750 --
1751 --
1752 l_location := '0120';
1753 --
1754 UPDATE vea_program_units
1755 SET tpa_program_unit_id = l_tpa_program_unit_id
1756 WHERE layer_provider_code = p_layer_provider_code
1757 AND program_unit_id = l_program_unit_id;
1758 --
1759 --
1760 l_index := g_pend_puId_tpaPUId_ext_tbl.NEXT(l_index);
1761 --}
1762 END LOOP;
1763 --
1764 --
1765 l_location := '0130';
1766 --
1767 vea_tp_layers_sv.deleteUnlicensedLayers
1768 (
1769 p_layer_provider_code => p_layer_provider_code
1770 );
1771 --
1772 --
1773 l_location := '0140';
1774 --
1775 --
1776 -- Check if a Layer Provider has an Layers, if not then delete the Layer Provider Code. Bug No: 1387766 - rvishnuv
1777
1778 vea_layerproviders_sv.delete_rows
1779 (
1780 p_layer_provider_code => p_layer_provider_code
1781 );
1782 --
1783 --
1784 l_location :='0150';
1785 --
1786 --
1787 vea_packages_sv.deleteUnreferencedPackages;
1788 --
1789 l_location :='0155';
1790 --
1791 l_tp_layer_id :=
1792 vea_tp_layers_sv.getId
1793 (
1794 p_layer_provider_code => p_layer_provider_code,
1795 p_tp_layer_name => p_tp_layer_name
1796 );
1797 --
1798 --
1799 l_location := '0160';
1800 --
1801 -- Check if customer site only then process conflicting layers
1802 --IF (( get_curr_layer_provider_code = 'CUST') AND (get_curr_customer_name != 'CUST')) THEN (This will not process layers belonging to a customer named "CUST")
1803 IF ( get_curr_layer_provider_code = 'CUST') THEN
1804 --
1805 --
1806 --vea_layers_sv.processConflictingLayers;
1807 vea_layers_sv.processConflictingLayers
1808 (
1809 p_tp_layer_id => l_tp_layer_id, --p_tp_layer_id,
1810 p_layer_provider_code => p_layer_provider_code
1811 );
1812 --
1813 --
1814 END IF;
1815 --
1816 --
1817 l_location := '0170';
1818 --
1819 G_LAYER_MERGE := FALSE;
1820 --
1821 --
1822 --} API Body
1823 --
1824 --
1825 -- Standard API Footer
1826 --
1827 l_location := '0180';
1828 --
1829 vea_tpa_util_pvt.api_footer
1830 (
1831 p_commit => p_commit,
1832 x_msg_count => x_msg_count,
1833 x_msg_data => x_msg_data
1834 );
1835 --}
1836 EXCEPTION
1837 --{
1838 WHEN FND_API.G_EXC_ERROR
1839 THEN
1840 --{
1841 --RAISE;
1842 vea_tpa_util_pvt.handle_error
1843 (
1844 p_error_type => vea_tpa_util_pvt.G_ERROR,
1845 p_savepoint_name => l_savepoint_name,
1846 p_package_name => G_PACKAGE_NAME,
1847 p_api_name => l_api_name,
1848 p_location => l_location,
1849 x_msg_count => x_msg_count,
1850 x_msg_data => x_msg_data,
1851 x_api_return_status => x_return_status
1852 );
1853 --}
1854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1855 THEN
1856 --{
1857 --RAISE;
1858 vea_tpa_util_pvt.handle_error
1859 (
1860 p_error_type => vea_tpa_util_pvt.G_UNEXPECTED_ERROR,
1861 p_savepoint_name => l_savepoint_name,
1862 p_package_name => G_PACKAGE_NAME,
1863 p_api_name => l_api_name,
1864 p_location => l_location,
1865 x_msg_count => x_msg_count,
1866 x_msg_data => x_msg_data,
1867 x_api_return_status => x_return_status
1868 );
1869 --}
1870 WHEN OTHERS
1871 THEN
1872 --{
1873 --RAISE;
1874 vea_tpa_util_pvt.handle_error
1875 (
1876 p_error_type => vea_tpa_util_pvt.G_OTHER_ERROR,
1877 p_savepoint_name => l_savepoint_name,
1878 p_package_name => G_PACKAGE_NAME,
1879 p_api_name => l_api_name,
1880 p_location => l_location,
1881 x_msg_count => x_msg_count,
1882 x_msg_data => x_msg_data,
1883 x_api_return_status => x_return_status
1884 );
1885 --}
1886 --}
1887 END postProcess;
1888 --
1889 --
1890 /*========================================================================
1891
1892 PROCEDURE NAME: isLayerMergeOn
1893
1894 PURPOSE: Returns true, if layer merge is going on.
1895
1896 ========================================================================*/
1897 FUNCTION isLayerMergeOn
1898 RETURN BOOLEAN
1899 IS
1900 --{
1901 l_api_name CONSTANT VARCHAR2(30) := 'isLayerMergeOn';
1902 l_location VARCHAR2(32767);
1903 --
1904 --
1905 --}
1906 BEGIN
1907 --{
1908 l_location := '0010';
1909 --
1910 RETURN (G_LAYER_MERGE);
1911 --}
1912 EXCEPTION
1913 --{
1914 WHEN OTHERS
1915 THEN
1916 --{
1917 add_exc_message_and_raise
1918 (
1919 p_package_name => G_PACKAGE_NAME,
1920 p_api_name => l_api_name,
1921 p_location => l_location
1922 );
1923 --}
1924 --}
1925 END isLayerMergeOn;
1926 --
1927 --
1928 /*========================================================================
1929
1930 PROCEDURE NAME: populateLayerActiveTable
1931
1932 PURPOSE: This procedure queries the TPA repository for the specified
1933 customizable program unit and stores active/inactive status
1934 of each layer within the customizable program unit in a
1935 PL/SQL table (layer cache). This procedure is called
1936 at the beginning of each TPA program unit. Layer cache is
1937 later used to check if layer is active/inactive.
1938
1939 ========================================================================*/
1940 PROCEDURE
1941 populateLayerActiveTable
1942 (
1943 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
1944 p_program_unit_id IN vea_program_units.program_unit_id%TYPE,
1945 x_layer_table IN OUT NOCOPY g_layer_tbl_type
1946 )
1947 IS
1948 --{
1949 l_layer_rec g_layer_rec_type;
1950 l_queryRequired BOOLEAN := FALSE;
1951 l_index NUMBER;
1952 l_layerTblCount NUMBER;
1953 l_startPosition NUMBER;
1954 --
1955 --
1956 CURSOR layer_cur
1957 IS
1958 SELECT LA.layer_provider_code,
1959 LA.layer_id,
1960 DECODE(TLA.ACTIVE_FLAG, 'N', 'N', LA.ACTIVE_FLAG) IS_LAYER_ACTIVE
1961 FROM vea_layers LA,
1962 vea_program_units PU,
1963 vea_layer_headers LH,
1964 vea_program_units APU,
1965 vea_packages PK,
1966 vea_tp_layers TLA
1967 WHERE PU.program_unit_id = p_program_unit_id
1968 AND PU.layer_provider_code = p_layer_provider_code
1969 AND LH.program_unit_id = PU.program_unit_id
1970 AND LH.program_unit_lp_code = PU.layer_provider_code
1971 AND LA.layer_provider_code = LH.layer_provider_code
1972 AND LA.layer_header_id = LH.layer_header_id
1973 AND APU.layer_provider_code = LA.program_unit_lp_code
1974 AND APU.program_unit_id = LA.new_program_unit_id
1975 AND PK.layer_provider_code = APU.layer_provider_code
1976 AND PK.package_id = APU.package_id
1977 AND TLA.layer_provider_code = PK.layer_provider_code
1978 AND TLA.tp_layer_id = PK.tp_layer_id;
1979 --}
1980 BEGIN
1981 --{
1982 l_queryRequired := TRUE;
1983 --
1984 --
1985 IF p_program_unit_id < C_INDEX_LIMIT
1986 THEN
1987 --{
1988 IF g_programUnit_Tbl.EXISTS(p_program_unit_id)
1989 THEN
1990 l_queryRequired := FALSE;
1991 END IF;
1992 --}
1993 ELSE
1994 --{
1995 l_index := g_programUnitExt_Tbl.FIRST;
1996 --
1997 WHILE l_index IS NOT NULL
1998 LOOP
1999 --{
2000 IF g_programUnitExt_Tbl(l_index).program_unit_id = p_program_unit_id
2001 THEN
2002 l_queryRequired := FALSE;
2003 EXIT;
2004 END IF;
2005 --
2006 --
2007 l_index := g_programUnitExt_Tbl.NEXT(l_index);
2008 --}
2009 END LOOP;
2010 --}
2011 END IF;
2012 --
2013 --
2014 IF l_queryRequired
2015 THEN
2016 --{
2017 --dbms_output.put_line('Query Required');
2018 l_layerTblCount := g_layer_Tbl.COUNT;
2019 l_startPosition := l_layerTblCount;
2020 --
2021 FOR layer_rec IN layer_cur
2022 LOOP
2023 --{
2024 l_layer_rec.layer_id := layer_rec.layer_id;
2025 l_layer_rec.layer_provider_code := layer_rec.layer_provider_code;
2026 --
2027 --
2028 IF layer_rec.is_layer_active = 'Y'
2029 THEN
2030 --{
2031 l_layer_rec.is_layer_active := TRUE;
2032 --}
2033 ELSE
2034 --{
2035 l_layer_rec.is_layer_active := FALSE;
2036 --}
2037 END IF ;
2038 --
2039 --
2040 IF l_layerTblCount = l_startPosition
2041 THEN
2042 --{
2043 --dbms_output.put_line('PU Table PUT');
2044 IF p_program_unit_id < C_INDEX_LIMIT
2045 THEN
2046 --{
2047 g_programUnit_Tbl(p_program_unit_id).program_unit_id := p_program_unit_id;
2048 g_programUnit_Tbl(p_program_unit_id).start_position := l_startPosition;
2049 --}
2050 ELSE
2051 --{
2052 g_programUnitExt_Tbl(g_programUnitExt_Tbl.COUNT).program_unit_id := p_program_unit_id;
2053 g_programUnitExt_Tbl(g_programUnitExt_Tbl.COUNT-1).start_position := l_startPosition;
2054 --}
2055 END IF;
2056 --}
2057 END IF;
2058 --
2059 --
2060 --x_layer_table(x_layer_table.COUNT+1) := l_layer_rec;
2061 g_layer_Tbl(l_layerTblCount) := l_layer_rec;
2062 --
2063 l_layerTblCount := l_layerTblCount + 1;
2064 --}
2065 END LOOP;
2066 --}
2067 END IF;
2068 --
2069 --
2070 /* DEbug info.
2071 dbms_output.put_line('g_programUnit_Tbl.Count='||g_programUnit_Tbl.COUNT);
2072 dbms_output.put_line('g_programUnitExt_Tbl.Count='||g_programUnitExt_Tbl.COUNT);
2073 dbms_output.put_line('g_layer_Tbl.Count='||g_layer_Tbl.COUNT);
2074 --
2075 --
2076 IF g_programUnitExt_Tbl.COUNT > 0
2077 THEN
2078 dbms_output.put_line('g_programUnitExt_Tbl Contents');
2079 l_index := g_programUnitExt_Tbl.FIRST;
2080 --
2081 WHILE l_index IS NOT NULL
2082 LOOP
2083 --{
2084 dbms_output.put_line('At Index ' || l_index || '-->'
2085 || g_programUnitExt_Tbl(l_index).program_unit_id
2086 || ','
2087 || g_programUnitExt_Tbl(l_index).start_position);
2088 --
2089 --
2090 l_index := g_programUnitExt_Tbl.NEXT(l_index);
2091 --}
2092 END LOOP;
2093 END IF;
2094 --
2095 --
2096 IF g_programUnit_Tbl.COUNT > 0
2097 THEN
2098 dbms_output.put_line('g_programUnit_Tbl Contents');
2099 l_index := g_programUnit_Tbl.FIRST;
2100 --
2101 WHILE l_index IS NOT NULL
2102 LOOP
2103 --{
2104 dbms_output.put_line('At Index ' || l_index || '-->'
2105 || g_programUnit_Tbl(l_index).program_unit_id
2106 || ','
2107 || g_programUnit_Tbl(l_index).start_position);
2108 --
2109 --
2110 l_index := g_programUnit_Tbl.NEXT(l_index);
2111 --}
2112 END LOOP;
2113 END IF;
2114 --
2115 --
2116 IF g_layer_Tbl.COUNT > 0
2117 THEN
2118 dbms_output.put_line('g_layer_Tbl Contents');
2119 l_index := g_layer_Tbl.FIRST;
2120 --
2121 WHILE l_index IS NOT NULL
2122 LOOP
2123 --{
2124 --
2125 IF g_layer_tbl(l_index).is_layer_active
2126 THEN
2127 dbms_output.put_line('At Index ' || l_index || '-->'
2128 || g_layer_Tbl(l_index).layer_id
2129 || ','
2130 || g_layer_Tbl(l_index).layer_provider_code
2131 || ',layer is active' );
2132 ELSE
2133 dbms_output.put_line('At Index ' || l_index || '-->'
2134 || g_layer_Tbl(l_index).layer_id
2135 || ','
2136 || g_layer_Tbl(l_index).layer_provider_code
2137 || ',layer is not active' );
2138 END IF;
2139 --
2140 l_index := g_layer_Tbl.NEXT(l_index);
2141 --}
2142 END LOOP;
2143 END IF;
2144 */
2145 --}
2146 EXCEPTION
2147 --{
2148 WHEN OTHERS
2149 THEN
2150 --{
2151 RAISE;
2152 --}
2153 --}
2154 END populateLayerActiveTable;
2155 --
2156 --
2157 /*========================================================================
2158
2159 PROCEDURE NAME: isLayerActive
2160
2161 PURPOSE: Returns true, if layer is active.
2162 It searches for the specified layer in the layer cache
2163 and returns active/inactive status as in the layer cache.
2164 It returns TRUE, if layer is active and FALSE otherwise.
2165
2166 If specifed layer is not found, it returns FALSE, meaning
2167 that layer is inactive.
2168
2169 ========================================================================*/
2170 FUNCTION isLayerActive
2171 (
2172 p_layer_table IN g_layer_tbl_type,
2173 p_layer_id IN vea_layers.layer_id%TYPE,
2174 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE
2175 )
2176 RETURN BOOLEAN
2177 IS
2178 --{
2179 l_index NUMBER;
2180 --}
2181 BEGIN
2182 --{
2183 l_index := g_layer_Tbl.FIRST;
2184 --
2185 --
2186 WHILE l_index IS NOT NULL
2187 LOOP
2188 --{
2189 IF g_layer_Tbl(l_index).layer_id = p_layer_id
2190 AND g_layer_Tbl(l_index).layer_provider_code = p_layer_provider_code
2191 THEN
2192 --{
2193 RETURN (g_layer_Tbl(l_index).is_layer_active);
2194 --}
2195 END IF ;
2196 --
2197 --
2198 l_index := g_layer_Tbl.NEXT(l_index);
2199 --}
2200 END LOOP;
2201 --
2202 --
2203 RETURN (FALSE);
2204 --}
2205 EXCEPTION
2206 --{
2207 WHEN OTHERS THEN
2208 RAISE;
2209 --NULL;
2210 --}
2211 END isLayerActive;
2212 --
2213 --
2214 /*========================================================================
2215
2216 PROCEDURE NAME: insert_ece_xref_category
2217
2218 PURPOSE: Inserts a code conversion category into EDI table
2219 ECE_XREF_CATEGORIES.
2220
2221 ========================================================================*/
2222 PROCEDURE insert_ece_xref_category
2223 (
2224 p_category_id IN ece_xref_categories.xref_category_id%TYPE,
2225 p_category_code IN ece_xref_categories.xref_category_code%TYPE,
2226 p_description IN ece_xref_categories.description%TYPE
2227 )
2228 IS
2229 --{
2230 l_api_name CONSTANT VARCHAR2(30) := 'insert_ece_xref_category';
2231 l_location VARCHAR2(32767);
2232 --
2233 --
2234 --}
2235 BEGIN
2236 --{
2237 l_location := '0010';
2238 --
2239 INSERT INTO ece_xref_categories
2240 (
2241 xref_category_id,
2242 xref_category_code,
2243 description,
2244 key1_used_flag,
2245 key2_used_flag,
2246 key3_used_flag,
2247 key4_used_flag,
2248 key5_used_flag,
2249 created_by,
2250 creation_date,
2251 last_update_date,
2252 last_updated_by,
2253 last_update_login
2254 )
2255 VALUES
2256 (
2257 p_category_id,
2258 p_category_code,
2259 p_description,
2260 'Y',
2261 'N',
2262 'N',
2263 'N',
2264 'N',
2265 vea_tpa_util_pvt.get_user_id,
2266 SYSDATE,
2267 SYSDATE,
2268 vea_tpa_util_pvt.get_user_id,
2269 vea_tpa_util_pvt.get_login_id
2270 );
2271 --}
2272 EXCEPTION
2273 --{
2274 WHEN OTHERS
2275 THEN
2276 --{
2277 add_exc_message_and_raise
2278 (
2279 p_package_name => G_PACKAGE_NAME,
2280 p_api_name => l_api_name,
2281 p_location => l_location
2282 );
2283 --}
2284 --}
2285 END insert_ece_xref_category;
2286 --
2287 --
2288 /*========================================================================
2289
2290 PROCEDURE NAME: process_ece_xref_category
2291
2292 PURPOSE: Inserts a code conversion category into EDI table
2293 ECE_XREF_CATEGORIES, if not existing already.
2294
2295 ========================================================================*/
2296 FUNCTION process_ece_xref_category
2297 (
2298 p_category_code IN ece_xref_categories.xref_category_code%TYPE,
2299 p_description IN ece_xref_categories.description%TYPE DEFAULT NULL
2300 )
2301 RETURN ece_xref_categories.xref_category_id%TYPE
2302 IS
2303 --{
2304 l_api_name CONSTANT VARCHAR2(30) := 'process_ece_xref_category';
2305 l_location VARCHAR2(32767);
2306 --
2307 --
2308 l_xref_category_id ece_xref_categories.xref_category_id%TYPE;
2309 --
2310 --
2311 CURSOR xref_category_cur
2312 (
2313 p_category_code IN ece_xref_categories.xref_category_code%TYPE
2314 )
2315 IS
2316 SELECT xref_category_id
2317 FROM ece_xref_categories
2318 WHERE xref_category_code = p_category_code;
2319 --}
2320 BEGIN
2321 --{
2322 l_location := '0010';
2323 --
2324 l_xref_category_id := NULL;
2325 --
2326 --
2327 l_location := '0020';
2328 --
2329 FOR xref_category_rec IN xref_category_cur
2330 (
2331 p_category_code => p_category_code
2332 )
2333 LOOP
2334 --{
2335 l_location := '0030';
2336 --
2337 l_xref_category_id := xref_category_rec.xref_category_id;
2338 --}
2339 END LOOP;
2340 --
2341 --
2342 l_location := '0040';
2343 --
2344 IF l_xref_category_id IS NULL
2345 THEN
2346 --{
2347 l_location := '0050';
2348 --
2349 SELECT ECE_XREF_CATEGORIES_S1.nextval
2350 INTO l_xref_category_id
2351 FROM DUAL;
2352 --
2353 --
2354 l_location := '0060';
2355 --
2356 vea_tpa_util_pvt.insert_ece_xref_category
2357 (
2358 p_category_id => l_xref_category_id,
2359 p_category_code => p_category_code,
2360 p_description => p_description
2361 );
2362 --}
2363 END IF;
2364 --
2365 --
2366 l_location := '0070';
2367 --
2368 RETURN(l_xref_category_id);
2369 --}
2370 EXCEPTION
2371 --{
2372 WHEN OTHERS
2373 THEN
2374 --{
2375 add_exc_message_and_raise
2376 (
2377 p_package_name => G_PACKAGE_NAME,
2378 p_api_name => l_api_name,
2379 p_location => l_location
2380 );
2381 --}
2382 --}
2383 END process_ece_xref_category;
2384 --
2385 --
2386 /*========================================================================
2387
2388 PROCEDURE NAME: insert_ece_xref_data
2389
2390 PURPOSE: Inserts a code conversion value into EDI table
2391 ECE_XREF_DATA.
2392
2393 ========================================================================*/
2394 PROCEDURE insert_ece_xref_data
2395 (
2396 p_data_id IN ece_xref_data.xref_data_id%TYPE,
2397 p_category_id IN ece_xref_data.xref_category_id%TYPE,
2398 p_category_code IN ece_xref_data.xref_category_code%TYPE,
2399 p_ext_value IN ece_xref_data.xref_ext_value1%TYPE,
2400 p_key1 IN ece_xref_data.xref_key1%TYPE,
2401 p_description IN ece_xref_data.description%TYPE
2402 )
2403 IS
2404 --{
2405 l_api_name CONSTANT VARCHAR2(30) := 'insert_ece_xref_data';
2406 l_location VARCHAR2(32767);
2407 --
2408 --
2409 --}
2410 BEGIN
2411 --{
2412 l_location := '0010';
2413 --
2414 INSERT INTO ece_xref_data
2415 (
2416 xref_data_id,
2417 xref_category_id,
2418 xref_category_code,
2419 description,
2420 xref_key1,
2421 xref_int_value,
2422 xref_ext_value1,
2423 direction,
2424 created_by,
2425 creation_date,
2426 last_update_date,
2427 last_updated_by,
2428 last_update_login
2429 )
2430 VALUES
2431 (
2432 p_data_id,
2433 p_category_id,
2434 p_category_code,
2435 p_description,
2436 p_key1,
2437 p_ext_value,
2438 p_ext_value,
2439 'BOTH',
2440 vea_tpa_util_pvt.get_user_id,
2441 SYSDATE,
2442 SYSDATE,
2443 vea_tpa_util_pvt.get_user_id,
2444 vea_tpa_util_pvt.get_login_id
2445 );
2446 --}
2447 EXCEPTION
2448 --{
2449 WHEN OTHERS
2450 THEN
2451 --{
2452 add_exc_message_and_raise
2453 (
2454 p_package_name => G_PACKAGE_NAME,
2455 p_api_name => l_api_name,
2456 p_location => l_location
2457 );
2458 --}
2459 --}
2460 END insert_ece_xref_data;
2461 --
2462 --
2463 /*========================================================================
2464
2465 PROCEDURE NAME: process_ece_xref_data
2466
2467 PURPOSE: Inserts a code conversion value into EDI table ECE_XREF_DATA,
2468 if record does not existing for the correspoding category,
2469 layer provider code(key1) and external value(branching
2470 condition value). IF a record is inserted then appends a
2471 message to log file informing user to specify code conversion
2472 for the inserted record.
2473
2474 ========================================================================*/
2475 PROCEDURE process_ece_xref_data
2476 (
2477 p_category_id IN ece_xref_data.xref_category_id%TYPE,
2478 p_category_code IN ece_xref_data.xref_category_code%TYPE,
2479 p_ext_value IN ece_xref_data.xref_ext_value1%TYPE,
2480 p_key1 IN ece_xref_data.xref_key1%TYPE,
2481 p_description IN ece_xref_data.description%TYPE DEFAULT NULL
2482 )
2483 IS
2484 --{
2485 l_api_name CONSTANT VARCHAR2(30) := 'process_ece_xref_data';
2486 l_location VARCHAR2(32767);
2487 --
2488 --
2489 l_xref_data_id ece_xref_data.xref_data_id%TYPE;
2490 --
2491 --
2492 CURSOR xref_data_cur
2493 (
2494 p_category_id IN ece_xref_data.xref_category_id%TYPE,
2495 p_ext_value IN ece_xref_data.xref_ext_value1%TYPE,
2496 p_key1 IN ece_xref_data.xref_key1%TYPE
2497 )
2498 IS
2499 SELECT xref_data_id
2500 FROM ece_xref_data
2501 WHERE xref_category_id = p_category_id
2502 AND xref_ext_value1 = p_ext_value
2503 AND xref_key1 = p_key1;
2504 --}
2505 BEGIN
2506 --{
2507 l_location := '0010';
2508 --
2509 l_xref_data_id := NULL;
2510 --
2511 --
2512 l_location := '0020';
2513 --
2514 FOR xref_data_rec IN xref_data_cur
2515 (
2516 p_category_id => p_category_id,
2517 p_ext_value => p_ext_value,
2518 p_key1 => p_key1
2519 )
2520 LOOP
2521 --{
2522 l_location := '0030';
2523 --
2524 l_xref_data_id := xref_data_rec.xref_data_id;
2525 --}
2526 END LOOP;
2527 --
2528 --
2529 l_location := '0040';
2530 --
2531 IF l_xref_data_id IS NULL
2532 THEN
2533 --{
2534 l_location := '0050';
2535 --
2536 SELECT ECE_XREF_DATA_S1.nextval
2537 INTO l_xref_data_id
2538 FROM DUAL;
2539 --
2540 --
2541 l_location := '0060';
2542 --
2543 vea_tpa_util_pvt.insert_ece_xref_data
2544 (
2545 p_data_id => l_xref_data_id,
2546 p_category_id => p_category_id,
2547 p_category_code => p_category_code,
2548 p_ext_value => p_ext_value,
2549 p_key1 => p_key1,
2550 p_description => p_description
2551 );
2552 --
2553 --
2554 l_location := '0070';
2555 --
2556 vea_tpa_util_pvt.add_message
2557 (
2558 p_error_name => 'VEA_LM_NEW_ECE_XREF_DATA',
2559 p_token1 => 'ECE_XREF_CATEGORY_CODE',
2560 p_value1 => p_category_code,
2561 p_token2 => 'EXT_VALUE',
2562 p_value2 => p_ext_value,
2563 p_token3 => 'KEY1',
2564 p_value3 => p_key1
2565 );
2566 --}
2567 END IF;
2568 --}
2569 EXCEPTION
2570 --{
2571 WHEN OTHERS
2572 THEN
2573 --{
2574 add_exc_message_and_raise
2575 (
2576 p_package_name => G_PACKAGE_NAME,
2577 p_api_name => l_api_name,
2578 p_location => l_location
2579 );
2580 --}
2581 --}
2582 END process_ece_xref_data;
2583 --
2584 --
2585 /*========================================================================
2586
2587 PROCEDURE NAME: get_ece_xref_category_code
2588
2589 PURPOSE: Maps TPS parameter name to EDI code conversion category
2590
2591 ========================================================================*/
2592 FUNCTION
2593 get_ece_xref_category_code
2594 (
2595 p_tps_parameter_name IN vea_parameters.name%TYPE
2596 )
2597 RETURN VARCHAR2
2598 IS
2599 --{
2600 l_api_name CONSTANT VARCHAR2(30) := 'get_ece_xref_category_code';
2601 l_location VARCHAR2(32767);
2602 --
2603 --
2604 --}
2605 BEGIN
2606 --{
2607 l_location := '0010';
2608 --
2609 IF LOWER(p_tps_parameter_name) = 'x_customer_number'
2610 THEN
2611 --{
2612 l_location := '0020';
2613 --
2614 RETURN('VEA_CUSTOMER_NUMBER');
2615 --}
2616 ELSIF LOWER(p_tps_parameter_name) = 'x_tp_group_code'
2617 THEN
2618 --{
2619 l_location := '0030';
2620 --
2621 RETURN('VEA_TP_GROUP_CODE');
2622 --}
2623 ELSIF LOWER(p_tps_parameter_name) = 'x_ship_to_ece_locn_code'
2624 OR LOWER(p_tps_parameter_name) = 'x_bill_to_ece_locn_code'
2625 OR LOWER(p_tps_parameter_name) = 'x_inter_ship_to_ece_locn_code'
2626 THEN
2627 --{
2628 l_location := '0040';
2629 --
2630 RETURN('VEA_ECE_TP_LOCATION_CODE');
2631 --}
2632 ELSE
2633 --{
2634 RETURN(NULL);
2635 --}
2636 END IF;
2637 --}
2638 EXCEPTION
2639 --{
2640 WHEN OTHERS
2641 THEN
2642 --{
2643 RAISE;
2644 /*
2645 add_exc_message_and_raise
2646 (
2647 p_package_name => G_PACKAGE_NAME,
2648 p_api_name => l_api_name,
2649 p_location => l_location
2650 );
2651 */
2652 --}
2653 --}
2654 END get_ece_xref_category_code;
2655 --
2656 --
2657 /*========================================================================
2658
2659 PROCEDURE NAME: get_ece_xref_category_code
2660
2661 PURPOSE: Maps TPS parameter to EDI code conversion category
2662
2663 ========================================================================*/
2664 FUNCTION
2665 get_ece_xref_category_code
2666 (
2667 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2668 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2669 p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE
2670 )
2671 RETURN VARCHAR2
2672 IS
2673 --{
2674 l_api_name CONSTANT VARCHAR2(30) := 'get_ece_xref_category_code';
2675 l_location VARCHAR2(32767);
2676 --
2677 --
2678 CURSOR parameter_cur
2679 (
2680 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2681 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2682 p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE
2683 )
2684 IS
2685 SELECT PA.name
2686 FROM vea_parameters PA,
2687 vea_program_units PU,
2688 vea_layer_headers LH
2689 WHERE PA.parameter_id = p_tps_parameter_id
2690 AND LH.layer_provider_code = p_layer_provider_code
2691 AND LH.layer_header_id = p_layer_header_id
2692 AND PU.program_unit_id = LH.tps_program_unit_id
2693 AND PU.layer_provider_code = LH.tps_program_unit_lp_code
2694 AND PA.program_unit_id = PU.program_unit_id
2695 AND PA.layer_provider_code = PU.layer_provider_code;
2696 --}
2697 BEGIN
2698 --{
2699 l_location := '0010';
2700 --
2701 FOR parameter_rec IN parameter_cur
2702 (
2703 p_layer_provider_code => p_layer_provider_code,
2704 p_layer_header_id => p_layer_header_id,
2705 p_tps_parameter_id => p_tps_parameter_id
2706 )
2707 LOOP
2708 --{
2709 l_location := '0010';
2710 --
2711 --
2712 RETURN
2713 (
2714 vea_tpa_util_pvt.get_ece_xref_category_code
2715 (
2716 p_tps_parameter_name => parameter_rec.name
2717 )
2718 );
2719 --}
2720 END LOOP;
2721 --}
2722 EXCEPTION
2723 --{
2724 WHEN OTHERS
2725 THEN
2726 --{
2727 add_exc_message_and_raise
2728 (
2729 p_package_name => G_PACKAGE_NAME,
2730 p_api_name => l_api_name,
2731 p_location => l_location
2732 );
2733 --}
2734 --}
2735 END get_ece_xref_category_code;
2736 --
2737 --
2738 /*========================================================================
2739
2740 PROCEDURE NAME: process_code_conversion
2741
2742 PURPOSE: Processes each branch criteria codition to check if it
2743 requires EDI code conversion.
2744 - Maps TPS parameter name to EDI code category.
2745 - Inserts EDI code category, if not existing already.
2746 - Inserts EDI code conversion value, if not existing already.
2747
2748 This procedure is called during layer merge.
2749
2750 ========================================================================*/
2751 PROCEDURE
2752 process_code_conversion
2753 (
2754 p_layer_provider_code IN vea_layers.layer_provider_code%TYPE,
2755 p_layer_header_id IN vea_layers.layer_header_id%TYPE,
2756 p_tps_parameter_id IN vea_layers.tps_parameter1_id%TYPE,
2757 p_tps_parameter_value IN vea_layers.tps_parameter1_value%TYPE
2758 )
2759 IS
2760 --{
2761 l_api_name CONSTANT VARCHAR2(30) := 'process_code_conversion';
2762 l_location VARCHAR2(32767);
2763 --
2764 --
2765 l_ece_xref_category_code ece_xref_categories.xref_category_code%TYPE;
2766 l_xref_category_id ece_xref_categories.xref_category_id%TYPE;
2767 --}
2768 BEGIN
2769 --{
2770 l_location := '0010';
2771 --
2772 IF p_tps_parameter_id IS NOT NULL
2773 AND p_tps_parameter_value IS NOT NULL
2774 THEN
2775 --{
2776 l_location := '0020';
2777 --
2778 l_ece_xref_category_code
2779 :=
2780 vea_tpa_util_pvt.get_ece_xref_category_code
2781 (
2782 p_layer_provider_code => p_layer_provider_code,
2783 p_layer_header_id => p_layer_header_id,
2784 p_tps_parameter_id => p_tps_parameter_id
2785 );
2786 --}
2787 END IF;
2788 --
2789 --
2790 l_location := '0030';
2791 --
2792 IF l_ece_xref_category_code IS NOT NULL
2793 THEN
2794 --{
2795 l_location := '0040';
2796 --
2797 l_xref_category_id
2798 :=
2799 vea_tpa_util_pvt.process_ece_xref_category
2800 (
2801 p_category_code => l_ece_xref_category_code
2802 );
2803 --
2804 --
2805 l_location := '0050';
2806 --
2807 vea_tpa_util_pvt.process_ece_xref_data
2808 (
2809 p_category_id => l_xref_category_id,
2810 p_category_code => l_ece_xref_category_code,
2811 p_key1 => p_layer_provider_code,
2812 p_ext_value => p_tps_parameter_value
2813 );
2814 --}
2815 END IF;
2816 --}
2817 EXCEPTION
2818 --{
2819 WHEN OTHERS
2820 THEN
2821 --{
2822 add_exc_message_and_raise
2823 (
2824 p_package_name => G_PACKAGE_NAME,
2825 p_api_name => l_api_name,
2826 p_location => l_location
2827 );
2828 --}
2829 --}
2830 END process_code_conversion;
2831 --
2832 --
2833 /*========================================================================
2834
2835 PROCEDURE NAME: convertBranchCriteria
2836
2837 PURPOSE: Converts branch condition values to internal values.
2838
2839 This procedure is called during execution of TPA generated
2840 code.
2841
2842 ========================================================================*/
2843 FUNCTION convertBranchCriteria
2844 (
2845 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
2846 p_parameter_name IN vea_parameters.name%TYPE,
2847 p_external_value IN ece_xref_data.xref_ext_value1%TYPE,
2848 x_code_conversion_tbl IN OUT NOCOPY vea_tpa_util_pvt.g_code_conversion_tbl_type
2849 )
2850 RETURN ece_xref_data.xref_int_value%TYPE
2851 IS
2852 --{
2853 l_index NUMBER;
2854 --
2855 --
2856 l_parameter_name vea_parameters.name%TYPE;
2857 l_ece_xref_category_code ece_xref_categories.xref_category_code%TYPE;
2858 l_code_conversion_rec vea_tpa_util_pvt.g_code_conversion_rec_type;
2859 l_internal_value ece_xref_data.xref_int_value%TYPE;
2860 --
2861 --
2862 l_return_status VARCHAR2(32767);
2863 l_msg_count NUMBER;
2864 l_msg_data VARCHAR2(32767);
2865 l_msg_index NUMBER;
2866 l_message VARCHAR2(32767);
2867 --}
2868 BEGIN
2869 --{
2870 l_index := x_code_conversion_tbl.FIRST;
2871 --
2872 --
2873 l_parameter_name := LOWER(p_parameter_name);
2874 --
2875 --
2876 WHILE l_index IS NOT NULL
2877 LOOP
2878 --{
2879 l_code_conversion_rec := x_code_conversion_tbl(l_index);
2880 --
2881 --
2882 IF l_code_conversion_rec.parameter_name = l_parameter_name
2883 AND l_code_conversion_rec.layer_provider_code = p_layer_provider_code
2884 AND l_code_conversion_rec.external_value = p_external_value
2885 THEN
2886 --{
2887 RETURN (l_code_conversion_rec.internal_value);
2888 --}
2889 END IF;
2890 --
2891 --
2892 l_index := x_code_conversion_tbl.NEXT(l_index);
2893 --}
2894 END LOOP;
2895 --
2896 --
2897 l_ece_xref_category_code
2898 :=
2899 vea_tpa_util_pvt.get_ece_xref_category_code
2900 (
2901 p_tps_parameter_name => l_parameter_name
2902 );
2903 --
2904 --
2905 IF l_ece_xref_category_code IS NULL
2906 THEN
2907 --{
2908 RETURN(p_external_value);
2909 --}
2910 END IF;
2911 --
2912 --
2913 EC_Code_Conversion_PVT.Convert_from_ext_to_int
2914 (
2915 p_api_version_number => 1.0,
2916 p_return_status => l_return_status,
2917 p_msg_count => l_msg_count,
2918 p_msg_data => l_msg_data,
2919 p_Category => l_ece_xref_category_code,
2920 p_Key1 => p_layer_provider_code,
2921 p_Int_val => l_internal_value,
2922 p_Ext_val1 => p_external_value
2923 );
2924 --
2925 --
2926 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2927 THEN
2928 --{
2929 l_code_conversion_rec.parameter_name := l_parameter_name;
2930 l_code_conversion_rec.layer_provider_code := p_layer_provider_code;
2931 l_code_conversion_rec.external_value := p_external_value;
2932 l_code_conversion_rec.internal_value := l_internal_value;
2933 --
2934 --
2935 x_code_conversion_tbl(x_code_conversion_tbl.COUNT+1)
2936 := l_code_conversion_rec;
2937 --
2938 --
2939 RETURN(l_internal_value);
2940 --}
2941 ELSE
2942 --{
2943 IF l_msg_count = 1
2944 THEN
2945 --{
2946 l_message := l_msg_data;
2947 --}
2948 ELSE
2949 --{
2950 FOR l_msg_index IN 1..l_msg_count
2951 LOOP
2952 --{
2953 l_message := SUBSTRB
2954 (
2955 l_message
2956 || ' '
2957 || FND_MSG_PUB.GET
2958 (
2959 p_encoded => FND_API.G_FALSE
2960 ),
2961 1,
2962 32767
2963 );
2964 --}
2965 END LOOP;
2966 --}
2967 END IF;
2968 --
2969 --
2970 set_message
2971 (
2972 p_error_name => 'VEA_CODE_CONVERSION_ERROR',
2973 p_token1 => 'ECE_XREF_CATEGORY_CODE',
2974 p_value1 => l_ece_xref_category_code,
2975 p_token2 => 'EXT_VALUE',
2976 p_value2 => p_external_value,
2977 p_token3 => 'KEY1',
2978 p_value3 => p_layer_provider_code,
2979 p_token4 => 'ERROR_TEXT',
2980 p_value4 => l_message
2981 );
2982 --
2983 --
2984 APP_EXCEPTION.RAISE_EXCEPTION;
2985 --}
2986 END IF;
2987 --}
2988 EXCEPTION
2989 --{
2990 WHEN OTHERS THEN
2991 RAISE;
2992 --NULL;
2993 --}
2994 END convertBranchCriteria;
2995 --
2996 --
2997 /*========================================================================
2998
2999 PROCEDURE NAME: Convert_from_ext_to_int
3000
3001 PURPOSE: Cover API for ECE code conversion API
3002
3003 ========================================================================*/
3004 FUNCTION Convert_from_ext_to_int
3005 (
3006 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
3007 p_parameter_name IN vea_parameters.name%TYPE,
3008 p_external_value IN ece_xref_data.xref_ext_value1%TYPE
3009 )
3010 RETURN ece_xref_data.xref_int_value%TYPE
3011 IS
3012 --{
3013 l_parameter_name vea_parameters.name%TYPE;
3014 l_ece_xref_category_code ece_xref_categories.xref_category_code%TYPE;
3015 l_external_value ece_xref_data.xref_ext_value1%TYPE;
3016 l_external_value2 ece_xref_data.xref_ext_value2%TYPE;
3017 l_external_value3 ece_xref_data.xref_ext_value3%TYPE;
3018 l_external_value4 ece_xref_data.xref_ext_value4%TYPE;
3019 l_external_value5 ece_xref_data.xref_ext_value5%TYPE;
3020 l_internal_value ece_xref_data.xref_int_value%TYPE;
3021 --
3022 --
3023 l_return_status VARCHAR2(32767);
3024 l_msg_count NUMBER;
3025 l_msg_data VARCHAR2(32767);
3026 l_msg_index NUMBER;
3027 l_message VARCHAR2(32767);
3028 --}
3029 BEGIN
3030 --{
3031 l_parameter_name := LOWER(p_parameter_name);
3032 --
3033 --
3034 l_ece_xref_category_code
3035 :=
3036 vea_tpa_util_pvt.get_ece_xref_category_code
3037 (
3038 p_tps_parameter_name => l_parameter_name
3039 );
3040 --
3041 --
3042 IF l_ece_xref_category_code IS NULL
3043 THEN
3044 --{
3045 RETURN(p_external_value);
3046 --}
3047 END IF;
3048 --
3049 --
3050 EC_Code_Conversion_PVT.Convert_from_ext_to_int
3051 (
3052 p_api_version_number => 1.0,
3053 p_return_status => l_return_status,
3054 p_msg_count => l_msg_count,
3055 p_msg_data => l_msg_data,
3056 p_Category => l_ece_xref_category_code,
3057 p_Key1 => p_layer_provider_code,
3058 p_Int_val => l_internal_value,
3059 p_Ext_val1 => p_external_value
3060 );
3061 --
3062 --
3063 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3064 THEN
3065 --{
3066 RETURN(l_internal_value);
3067 --}
3068 ELSE
3069 --{
3070 IF l_msg_count = 1
3071 THEN
3072 --{
3073 l_message := l_msg_data;
3074 --}
3075 ELSE
3076 --{
3077 FOR l_msg_index IN 1..l_msg_count
3078 LOOP
3079 --{
3080 l_message := SUBSTRB
3081 (
3082 l_message
3083 || ' '
3084 || FND_MSG_PUB.GET
3085 (
3086 p_encoded => FND_API.G_FALSE
3087 ),
3088 1,
3089 32767
3090 );
3091 --}
3092 END LOOP;
3093 --}
3094 END IF;
3095 --
3096 --
3097 set_message
3098 (
3099 p_error_name => 'VEA_CODE_CONVERSION_ERROR',
3100 p_token1 => 'ECE_XREF_CATEGORY_CODE',
3101 p_value1 => l_ece_xref_category_code,
3102 p_token2 => 'EXT_VALUE',
3103 p_value2 => p_external_value,
3104 p_token3 => 'KEY1',
3105 p_value3 => p_layer_provider_code,
3106 p_token4 => 'ERROR_TEXT',
3107 p_value4 => l_message
3108 );
3109 --
3110 --
3111 APP_EXCEPTION.RAISE_EXCEPTION;
3112 --}
3113 END IF;
3114 --}
3115 EXCEPTION
3116 --{
3117 WHEN OTHERS THEN
3118 RAISE;
3119 --NULL;
3120 --}
3121 END Convert_from_ext_to_int;
3122 --
3123 --
3124 /*========================================================================
3125
3126 PROCEDURE NAME: Convert_from_int_to_ext
3127
3128 PURPOSE: Cover API for ECE code conversion API
3129
3130 ========================================================================*/
3131 FUNCTION Convert_from_int_to_ext
3132 (
3133 p_layer_provider_code IN vea_program_units.layer_provider_code%TYPE,
3134 p_parameter_name IN vea_parameters.name%TYPE,
3135 p_internal_value IN ece_xref_data.xref_int_value%TYPE
3136 )
3137 RETURN ece_xref_data.xref_ext_value1%TYPE
3138 IS
3139 --{
3140 l_parameter_name vea_parameters.name%TYPE;
3141 l_ece_xref_category_code ece_xref_categories.xref_category_code%TYPE;
3142 l_external_value ece_xref_data.xref_ext_value1%TYPE;
3143 l_external_value2 ece_xref_data.xref_ext_value2%TYPE;
3144 l_external_value3 ece_xref_data.xref_ext_value3%TYPE;
3145 l_external_value4 ece_xref_data.xref_ext_value4%TYPE;
3146 l_external_value5 ece_xref_data.xref_ext_value5%TYPE;
3147 --
3148 --
3149 l_return_status VARCHAR2(32767);
3150 l_msg_count NUMBER;
3151 l_msg_data VARCHAR2(32767);
3152 l_msg_index NUMBER;
3153 l_message VARCHAR2(32767);
3154 --}
3155 BEGIN
3156 --{
3157 l_parameter_name := LOWER(p_parameter_name);
3158 --
3159 --
3160 l_ece_xref_category_code
3161 :=
3162 vea_tpa_util_pvt.get_ece_xref_category_code
3163 (
3164 p_tps_parameter_name => l_parameter_name
3165 );
3166 --
3167 --
3168 IF l_ece_xref_category_code IS NULL
3169 THEN
3170 --{
3171 RETURN(p_internal_value);
3172 --}
3173 END IF;
3174 --
3175 --
3176 EC_Code_Conversion_PVT.Convert_from_int_to_ext
3177 (
3178 p_api_version_number => 1.0,
3179 p_return_status => l_return_status,
3180 p_msg_count => l_msg_count,
3181 p_msg_data => l_msg_data,
3182 p_Category => l_ece_xref_category_code,
3183 p_Key1 => p_layer_provider_code,
3184 p_Int_val => p_internal_value,
3185 p_Ext_val1 => l_external_value,
3186 p_Ext_val2 => l_external_value2,
3187 p_Ext_val3 => l_external_value3,
3188 p_Ext_val4 => l_external_value4,
3189 p_Ext_val5 => l_external_value5
3190 );
3191 --
3192 --
3193 IF l_return_status = FND_API.G_RET_STS_SUCCESS
3194 THEN
3195 --{
3196 RETURN(l_external_value);
3197 --}
3198 ELSE
3199 --{
3200 IF l_msg_count = 1
3201 THEN
3202 --{
3203 l_message := l_msg_data;
3204 --}
3205 ELSE
3206 --{
3207 FOR l_msg_index IN 1..l_msg_count
3208 LOOP
3209 --{
3210 l_message := SUBSTRB
3211 (
3212 l_message
3213 || ' '
3214 || FND_MSG_PUB.GET
3215 (
3216 p_encoded => FND_API.G_FALSE
3217 ),
3218 1,
3219 32767
3220 );
3221 --}
3222 END LOOP;
3223 --}
3224 END IF;
3225 --
3226 --
3227 set_message
3228 (
3229 p_error_name => 'VEA_CODE_CONVERSION_ERROR',
3230 p_token1 => 'ECE_XREF_CATEGORY_CODE',
3231 p_value1 => l_ece_xref_category_code,
3232 p_token2 => 'INT_VALUE',
3233 p_value2 => p_internal_value,
3234 p_token3 => 'KEY1',
3235 p_value3 => p_layer_provider_code,
3236 p_token4 => 'ERROR_TEXT',
3237 p_value4 => l_message
3238 );
3239 --
3240 --
3241 APP_EXCEPTION.RAISE_EXCEPTION;
3242 --}
3243 END IF;
3244 --}
3245 EXCEPTION
3246 --{
3247 WHEN OTHERS THEN
3248 RAISE;
3249 --NULL;
3250 --}
3251 END Convert_from_int_to_ext;
3252 --
3253 --
3254 PROCEDURE debug
3255 (
3256 p_string IN VARCHAR2
3257 )
3258 IS
3259 --{
3260 --}
3261 BEGIN
3262 --{
3263 FND_FILE.PUT_LINE(FND_FILE.LOG,p_string);
3264 --}
3265 EXCEPTION
3266 --{
3267 WHEN OTHERS THEN
3268 RAISE;
3269 --NULL;
3270 --}
3271 END debug;
3272 --
3273 --
3274 FUNCTION validate
3275 RETURN BOOLEAN
3276 IS
3277 --{
3278 --}
3279 BEGIN
3280 --{
3281 RETURN (TRUE);
3282 --}
3283 EXCEPTION
3284 --{
3285 WHEN OTHERS THEN
3286 RAISE;
3287 --NULL;
3288 --}
3289 END validate;
3290 --
3291 --
3292 /*========================================================================
3293
3294 PROCEDURE NAME: get_message_text
3295
3296 PURPOSE: Returns a message to the Application
3297
3298 Added by V RAVIKIRAN 10/12/2000
3299 ========================================================================*/
3300
3301 --
3302 --
3303 FUNCTION get_message_text
3304 (
3305 p_error_name IN VARCHAR2,
3306 p_token1 IN VARCHAR2 ,
3307 p_value1 IN VARCHAR2 ,
3308 p_token2 IN VARCHAR2 ,
3309 p_value2 IN VARCHAR2 ,
3310 p_token3 IN VARCHAR2 ,
3311 p_value3 IN VARCHAR2 ,
3312 p_token4 IN VARCHAR2 ,
3313 p_value4 IN VARCHAR2 ,
3314 p_token5 IN VARCHAR2 ,
3315 p_value5 IN VARCHAR2 ,
3316 p_token6 IN VARCHAR2 ,
3317 p_value6 IN VARCHAR2 ,
3318 p_token7 IN VARCHAR2 ,
3319 p_value7 IN VARCHAR2 ,
3320 p_token8 IN VARCHAR2 ,
3321 p_value8 IN VARCHAR2
3322
3323 )
3324 RETURN VARCHAR2
3325 IS
3326 --{
3327 l_api_name CONSTANT VARCHAR2(30) := 'get_message_text';
3328 l_location VARCHAR2(32767);
3329 --}
3330 BEGIN
3331 --{
3332 l_location := '0010';
3333 --
3334 --
3335 vea_tpa_util_pvt.set_message
3336 (
3337 p_error_name => p_error_name,
3338 p_token1 => p_token1,
3339 p_value1 => p_value1,
3340 p_token2 => p_token2,
3341 p_value2 => p_value2,
3342 p_token3 => p_token3,
3343 p_value3 => p_value3,
3344 p_token4 => p_token4,
3345 p_value4 => p_value4,
3346 p_token5 => p_token5,
3347 p_value5 => p_value5,
3348 p_token6 => p_token6,
3349 p_value6 => p_value6,
3350 p_token7 => p_token7,
3351 p_value7 => p_value7,
3352 p_token8 => p_token8,
3353 p_value8 => p_value8
3354 );
3355 --
3356 --
3357 l_location := '0020';
3358 --
3359 --
3360 RETURN(FND_MESSAGE.GET);
3361 --
3362 --
3363 EXCEPTION
3364 --{
3365 WHEN OTHERS
3366 THEN
3367 RAISE;
3368 --}
3369 END get_message_text;
3370 --
3371 --
3372 PROCEDURE update_lookup_values
3373 (
3374 p_lookup_type IN fnd_lookup_values.lookup_type%TYPE,
3375 p_new_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
3376 p_current_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
3377 p_meaning IN fnd_lookup_values.meaning%TYPE,
3378 p_description IN fnd_lookup_values.description%TYPE
3379 )
3380 IS
3381 --{
3382 l_api_name CONSTANT VARCHAR2(30) := 'update_lookup_values';
3383 l_location VARCHAR2(32767);
3384 l_row_id VARCHAR2(55);
3385 --}
3386 BEGIN
3387 --{
3388 l_location := '0010';
3389 --
3390 --
3391 UPDATE fnd_lookup_values
3392 SET lookup_code = p_new_lookup_code,
3393 meaning = p_meaning,
3394 description = p_description
3395 WHERE lookup_type = p_lookup_type
3396 AND lookup_code = p_current_lookup_code;
3397 --
3398 --
3399 EXCEPTION
3400 --{
3401 WHEN OTHERS THEN
3402 RAISE;
3403 --}
3404 END update_lookup_values;
3405 --
3406 --
3407 PROCEDURE insert_lookup_values
3408 (
3409 p_lookup_type IN fnd_lookup_values.lookup_type%TYPE,
3410 p_lookup_code IN fnd_lookup_values.lookup_code%TYPE,
3411 p_meaning IN fnd_lookup_values.meaning%TYPE,
3412 p_description IN fnd_lookup_values.description%TYPE
3413 )
3414 IS
3415 --{
3416 l_api_name CONSTANT VARCHAR2(30) := 'insert_lookup_values';
3417 l_location VARCHAR2(32767);
3418 l_row_id VARCHAR2(55);
3419 --}
3420 BEGIN
3421 --{
3422 l_location := '0010';
3423 --
3424 --
3425 FND_LOOKUP_VALUES_PKG.INSERT_ROW
3426 (
3427 x_rowid => l_row_id,
3428 x_lookup_type => p_lookup_type,
3429 x_security_group_id => FND_GLOBAL.SECURITY_GROUP_ID,
3430 x_view_application_id => 0,
3431 x_lookup_code => p_lookup_code,
3432 x_tag => NULL,
3433 x_attribute_category => NULL,
3434 x_attribute1 => NULL,
3435 x_attribute2 => NULL,
3436 x_attribute3 => NULL,
3437 x_attribute4 => NULL,
3438 x_enabled_flag => 'Y',
3439 x_start_date_active => SYSDATE,
3440 x_end_date_active => NULL,
3441 x_territory_code => NULL,
3442 x_attribute5 => NULL,
3443 x_attribute6 => NULL,
3444 x_attribute7 => NULL,
3445 x_attribute8 => NULL,
3446 x_attribute9 => NULL,
3447 x_attribute10 => NULL,
3448 x_attribute11 => NULL,
3449 x_attribute12 => NULL,
3450 x_attribute13 => NULL,
3451 x_attribute14 => NULL,
3452 x_attribute15 => NULL,
3453 x_meaning => p_meaning,
3454 x_description => p_description,
3455 x_creation_date => SYSDATE,
3456 x_created_by => FND_GLOBAL.USER_ID,
3457 x_last_update_date => SYSDATE,
3458 x_last_updated_by => FND_GLOBAL.USER_ID,
3459 x_last_update_login => FND_GLOBAL.LOGIN_ID
3460 );
3461 --
3462 --
3463 EXCEPTION
3464 --{
3465 WHEN OTHERS THEN
3466 RAISE;
3467 --}
3468 END insert_lookup_values;
3469
3470
3471 --
3472 --
3473 /*========================================================================
3474
3475 PROCEDURE NAME: clearLayerActiveTable
3476
3477 ========================================================================*/
3478 PROCEDURE
3479 clearLayerActiveTable
3480 IS
3481 --{
3482 --}
3483 BEGIN
3484 --{
3485 g_programUnit_Tbl.DELETE;
3486 g_programUnitExt_Tbl.DELETE;
3487 g_layer_Tbl.DELETE;
3488 --}
3489 EXCEPTION
3490 --{
3491 WHEN OTHERS
3492 THEN
3493 --{
3494 RAISE;
3495 --}
3496 --}
3497 END clearLayerActiveTable;
3498 --
3499 --
3500
3501 --
3502 --
3503 /*========================================================================
3504
3505 PROCEDURE NAME: put
3506
3507 ========================================================================*/
3508 PROCEDURE
3509 put
3510 (
3511 p_key IN NUMBER,
3512 p_value IN NUMBER,
3513 x_cache_tbl IN OUT NOCOPY g_cache_tbl_type,
3514 x_cache_ext_tbl IN OUT NOCOPY g_cache_tbl_type
3515 )
3516 IS
3517 --{
3518 l_index NUMBER;
3519 l_found BOOLEAN := FALSE;
3520 --}
3521 BEGIN
3522 --{
3523 IF p_key IS NULL
3524 THEN
3525 RAISE FND_API.G_EXC_ERROR;
3526 END IF;
3527 --
3528 --
3529 IF p_key < C_INDEX_LIMIT
3530 THEN
3531 x_cache_tbl(p_key).key := p_key;
3532 x_cache_tbl(p_key).value := p_value;
3533 ELSE
3534 l_index := x_cache_ext_tbl.FIRST;
3535 --
3536 WHILE l_index IS NOT NULL
3537 LOOP
3538 IF x_cache_ext_tbl(l_index).key = p_key
3539 THEN
3540 x_cache_ext_tbl(l_index).value := p_value;
3541 l_found := TRUE;
3542 EXIT;
3543 END IF;
3544 --
3545 l_index := x_cache_ext_tbl.NEXT(l_index);
3546 END LOOP;
3547 --
3548 IF NOT(l_found)
3549 THEN
3550 x_cache_ext_tbl(x_cache_ext_tbl.COUNT + 1).key := p_key;
3551 x_cache_ext_tbl(x_cache_ext_tbl.COUNT).value := p_value;
3552 END IF;
3553 END IF;
3554 --}
3555 EXCEPTION
3556 --{
3557 WHEN OTHERS
3558 THEN
3559 --{
3560 RAISE;
3561 --}
3562 --}
3563 END put;
3564 --
3565 --
3566 --
3567 /*========================================================================
3568
3569 PROCEDURE NAME: get
3570
3571 ========================================================================*/
3572 PROCEDURE
3573 get
3574 (
3575 p_key IN NUMBER,
3576 p_cache_tbl IN OUT NOCOPY g_cache_tbl_type,
3577 p_cache_ext_tbl IN OUT NOCOPY g_cache_tbl_type,
3578 x_value OUT NOCOPY NUMBER
3579 )
3580 IS
3581 --{
3582 l_index NUMBER;
3583 l_found BOOLEAN := FALSE;
3584 --}
3585 BEGIN
3586 --{
3587 IF p_key IS NULL
3588 THEN
3589 RAISE FND_API.G_EXC_ERROR;
3590 END IF;
3591 --
3592 --
3593 IF p_key < C_INDEX_LIMIT
3594 THEN
3595 IF p_cache_tbl.EXISTS(p_key)
3596 THEN
3597 x_value := p_cache_tbl(p_key).value;
3598 ELSE
3599 RAISE FND_API.G_EXC_ERROR;
3600 END IF;
3601 ELSE
3602 l_index := p_cache_ext_tbl.FIRST;
3603 --
3604 WHILE l_index IS NOT NULL
3605 LOOP
3606 IF p_cache_ext_tbl(l_index).key = p_key
3607 THEN
3608 x_value := p_cache_ext_tbl(l_index).value ;
3609 l_found := TRUE;
3610 EXIT;
3611 END IF;
3612 --
3613 l_index := p_cache_ext_tbl.NEXT(l_index);
3614 END LOOP;
3615 --
3616 IF NOT(l_found)
3617 THEN
3618 RAISE FND_API.G_EXC_ERROR;
3619 END IF;
3620 END IF;
3621 --}
3622 EXCEPTION
3623 --{
3624 WHEN OTHERS
3625 THEN
3626 --{
3627 RAISE;
3628 --}
3629 --}
3630 END get;
3631 --
3632 --
3633
3634 --}
3635 END VEA_TPA_UTIL_PVT;