[Home] [Help]
PACKAGE BODY: APPS.XLA_CMP_CREATE_PKG
Source
1 PACKAGE BODY xla_cmp_create_pkg AS
2 /* $Header: xlacpdbo.pkb 120.18 2006/12/08 22:07:01 weshen ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_cmp_create_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a XLA private package, which contains all the APIs required |
13 | for creation packages (spec and body) in the database |
14 | |
15 | |
16 | HISTORY |
17 | 25-JUN-2002 K.Boussema Created |
18 | 22-APR-2003 K.Boussema Included error messages |
19 | 22-JAN-2004 K.Boussema Changed to call the standards APPS_ARRAY_DDL |
20 | and APPS_DDL to create PL/SQL packages |
21 | 23-FEB-2004 K.Boussema Made changes for the FND_LOG. |
22 | 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
23 | and the procedure. |
24 | 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
25 | trace() procedure |
26 | 02-JUN-2004 A.Quaglia Added push_database_object, execute_dml |
27 | ,execute_ddl |
28 | 17-JUN-2004 A.Quaglia push_database_object: removed alter package |
29 | 21-JUN-2004 A.Quaglia push_database_object: reintroduced package |
30 | compilation but only when the package body is|
31 | created. |
32 | 28-JUL-04 A.Quaglia Changed message tokens |
33 | 08-JUN-05 W.Chan Updated dump_package to loop the package from|
34 | user_source only if the log level is stmt |
35 | |
36 +===========================================================================*/
37
38 --
39 --+==========================================================================+
40 --| |
41 --| Private global constants |
42 --| |
43 --+==========================================================================+
44 --
45 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
46 C_CREATED CONSTANT BOOLEAN := TRUE;
47 --
48 g_Max_line CONSTANT NUMBER := 225;
49 g_chr_quote CONSTANT VARCHAR2(10):='''';
50 g_chr_newline CONSTANT VARCHAR2(10):= xla_environment_pkg.g_chr_newline;
51
52 --
53 --+==========================================================================+
54 --| |
55 --| |
56 --| |
57 --| |
58 --| |
59 --| |
60 --| |
61 --| FND_LOG trace |
62 --| |
63 --| |
64 --| |
65 --| |
66 --| |
67 --| |
68 --| |
69 --| |
70 --| |
71 --| |
72 --| |
73 --| |
74 --| |
75 --| |
76 --| |
77 --| |
78 --| |
79 --| |
80 --| |
81 --| |
82 --| |
83 --| |
84 --| |
85 --+==========================================================================+
86
87 --=============================================================================
88 -- *********** Local Trace Routine **********
89 --=============================================================================
90
91 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
92 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
93 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
94 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
95 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
96 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
97
98 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
99 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_cmp_create_pkg';
100
101 g_log_level NUMBER;
102 g_log_enabled BOOLEAN;
103
104 PROCEDURE trace
105 (p_msg IN VARCHAR2
106 ,p_level IN NUMBER
107 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE)
108 IS
109 BEGIN
110
111 ----------------------------------------------------------------------------
112 -- Following is for FND log.
113 ----------------------------------------------------------------------------
114 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
115 fnd_log.message(p_level, p_module);
116 ELSIF p_level >= g_log_level THEN
117 fnd_log.string(p_level, p_module, p_msg);
118 END IF;
119
120 EXCEPTION
121 WHEN xla_exceptions_pkg.application_exception THEN
122 RAISE;
123 WHEN OTHERS THEN
124 xla_exceptions_pkg.raise_message
125 (p_location => 'xla_cmp_create_pkg.trace');
126 END trace;
127 --
128 --+==========================================================================+
129 --| |
130 --| OVERVIEW of private procedures/functions |
131 --| |
132 --+==========================================================================+
133 --
134 --
135 --+==========================================================================+
136 --| PRIVATE procedures and functions |
137 --| |
138 --| |
139 --| |
140 --| |
141 --| |
142 --| |
143 --| |
144 --| |
145 --| |
146 --| |
147 --| |
148 --| |
149 --| |
150 --| |
151 --| |
152 --| |
153 --| |
154 --| |
155 --| |
156 --| |
157 --| |
158 --| |
159 --| |
160 --| |
161 --| |
162 --| |
163 --| |
164 --| |
165 --| |
166 --| |
167 --+==========================================================================+
168
169
170 --
171 --+==========================================================================+
172 --| PRIVATE procedures and functions |
173 --| |
174 --| |
175 --| |
176 --| |
177 --| |
178 --+==========================================================================+
179 --
180 PROCEDURE dump_package( p_package_name IN VARCHAR2
181 , p_package_type IN VARCHAR2)
182 IS
183 --
184 CURSOR text_cur ( p_package_name VARCHAR2
188 , us.line
185 ,p_package_type VARCHAR2)
186 IS
187 SELECT us.text
189 FROM user_source us
190 WHERE us.name = UPPER(p_package_name)
191 AND us.type = UPPER(p_package_type)
192 ORDER BY line
193 ;
194 --
195 l_first BOOLEAN:= TRUE;
196 l_log_module VARCHAR2(240);
197 --
198 BEGIN
199 --
200 IF g_log_enabled THEN
201 l_log_module := C_DEFAULT_MODULE||'.dump_package';
202 END IF;
203 --
204 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
205
206 trace
207 (p_msg => 'BEGIN of dump_package'
208 ,p_level => C_LEVEL_PROCEDURE
209 ,p_module => l_log_module);
210
211 END IF;
212 --
213 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
214 FOR text_rec IN text_cur(p_package_name, p_package_type) LOOP
215 --
216 IF l_first THEN
217 --
218 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
219
220 trace
221 (p_msg => '>>> DUMP '|| p_package_type ||' = '||p_package_name
222 ,p_level => C_LEVEL_STATEMENT
223 ,p_module => l_log_module);
224
225 END IF;
226
227 l_first := FALSE;
228
229 END IF;
230
231 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
232
233 trace
234 (p_msg => RPAD(text_rec.line ,10,' ') ||' '||text_rec.text
235 ,p_level => C_LEVEL_STATEMENT
236 ,p_module => l_log_module);
237
238 END IF;
239 --
240 END LOOP;
241 END IF;
242 --
243 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
244
245 trace
246 (p_msg => 'END of dump_package'
247 ,p_level => C_LEVEL_PROCEDURE
248 ,p_module => l_log_module);
249
250 END IF;
251 --
252 --
253 EXCEPTION
254 WHEN xla_exceptions_pkg.application_exception THEN
255 IF text_cur%ISOPEN THEN CLOSE text_cur; END IF;
256 RAISE;
257 WHEN OTHERS THEN
258 xla_exceptions_pkg.raise_message
259 (p_location => 'xla_cmp_create_pkg.dump_package');
260 END dump_package;
261
262 --
263 --+==========================================================================+
264 --| PRIVATE procedures and functions |
265 --| |
266 --| |
267 --| |
268 --| |
269 --| |
270 --+==========================================================================+
271 --
272 PROCEDURE get_pkg_errors( p_product_rule_name IN VARCHAR2
273 , p_package_name IN VARCHAR2
274 , p_package_type IN VARCHAR2)
275 IS
276 --
277 CURSOR error_cur ( p_package_name VARCHAR2
278 ,p_package_type VARCHAR2)
279 IS
280 SELECT SUBSTR(ue.text,1,2000) error
281 , ue.line
282 FROM user_errors ue
283 WHERE ue.name = UPPER(p_package_name)
284 AND ue.type = UPPER(p_package_type)
285 ORDER BY line
286 ;
287 --
288 l_first BOOLEAN:= TRUE;
289 l_log_module VARCHAR2(240);
290 --
291 BEGIN
292 --
293 IF g_log_enabled THEN
294 l_log_module := C_DEFAULT_MODULE||'.get_pkg_errors';
295 END IF;
296 --
297 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
298
299 trace
300 (p_msg => 'BEGIN of get_pkg_errors'
301 ,p_level => C_LEVEL_PROCEDURE
302 ,p_module => l_log_module);
303
304 END IF;
305 --
306 FOR error_rec IN error_cur(p_package_name, p_package_type) LOOP
307 --
308 IF l_first THEN
309
310 IF (C_LEVEL_ERROR >= g_log_level) THEN
311
312 trace
313 (p_msg => '----- COMPILATION FAILS ------'
314 ,p_level => C_LEVEL_ERROR
315 ,p_module => l_log_module);
316
317 trace
318 (p_msg => 'Additional information = '
319 ,p_level => C_LEVEL_ERROR
320 ,p_module => l_log_module);
321
322 trace
323 (p_msg => '------------------------------'
324 ,p_level => C_LEVEL_ERROR
325 ,p_module => l_log_module);
326
327 trace
328 (p_msg => 'Package name = '||p_package_name
329 ,p_level => C_LEVEL_ERROR
330 ,p_module => l_log_module);
331
332 trace
333 (p_msg => 'Application Accounting Definition = '||p_product_rule_name
334 ,p_level => C_LEVEL_ERROR
335 ,p_module => l_log_module);
336
337 trace
338 (p_msg => RPAD('LINE',10,' ') ||'|' ||' ERROR '
339 ,p_level => C_LEVEL_ERROR
340 ,p_module => l_log_module);
341
342 trace
343 (p_msg => LPAD('-',10,'-') ||'|' ||LPAD('-',50,'-')
344 ,p_level => C_LEVEL_ERROR
345 ,p_module => l_log_module);
346
347 END IF;
348 --
349 l_first := FALSE;
350
351 END IF;
352
353 IF (C_LEVEL_ERROR >= g_log_level) THEN
354
355 trace
359
356 (p_msg => RPAD(error_rec.line,10,' ') ||'| '||error_rec.error
357 ,p_level => C_LEVEL_ERROR
358 ,p_module => l_log_module);
360 END IF;
361 --
362 END LOOP;
363 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
364
365 trace
366 (p_msg => 'END of get_pkg_errors'
367 ,p_level => C_LEVEL_PROCEDURE
368 ,p_module => l_log_module);
369
370 END IF;
371 --
372 --
373 EXCEPTION
374 WHEN xla_exceptions_pkg.application_exception THEN
375 IF error_cur%ISOPEN THEN CLOSE error_cur; END IF;
376 RAISE;
377 WHEN OTHERS THEN
378 xla_exceptions_pkg.raise_message
379 (p_location => 'xla_cmp_create_pkg.get_pkg_errors');
380 END get_pkg_errors;
381 --
382 --
383 --+==========================================================================+
384 --| PRIVATE procedures and functions |
385 --| |
386 --| |
387 --| |
388 --| |
389 --| |
390 --+==========================================================================+
391 --
392 FUNCTION GetPackageStatus( p_package_name IN VARCHAR2
393 , p_package_type IN VARCHAR2)
394 RETURN BOOLEAN
395 IS
396 --
397 l_IsValid BOOLEAN;
398 l_status VARCHAR2(10);
399 l_log_module VARCHAR2(240);
400 --
401 BEGIN
402 --
403 IF g_log_enabled THEN
404 l_log_module := C_DEFAULT_MODULE||'.GetPackageStatus';
405 END IF;
406 --
407 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
408
409 trace
410 (p_msg => 'BEGIN of GetPackageStatus'
411 ,p_level => C_LEVEL_PROCEDURE
412 ,p_module => l_log_module);
413
414 END IF;
415 --
416 BEGIN
417 SELECT uo.status
418 INTO l_status
419 FROM user_objects uo
420 WHERE uo.object_name = UPPER(p_package_name)
421 AND uo.object_type = UPPER(p_package_type)
422 ;
423 --
424 l_IsValid := (l_status = 'VALID');
425 --
426 EXCEPTION
427 WHEN NO_DATA_FOUND THEN
428 l_IsValid := FALSE;
429 END ;
430 --
431 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
432
433 trace
434 (p_msg => 'END of GetPackageStatus'
435 ,p_level => C_LEVEL_PROCEDURE
436 ,p_module => l_log_module);
437
438 END IF;
439 RETURN l_IsValid;
440 EXCEPTION
441 WHEN OTHERS THEN
442 RETURN FALSE;
443 END GetPackageStatus;
444
445
446 --
447 --+==========================================================================+
448 --| PRIVATE procedures and functions |
449 --| |
450 --| |
451 --| |
452 --| |
453 --| |
454 --+==========================================================================+
455 FUNCTION CreateBodyPackage ( p_product_rule_name IN VARCHAR2
456 , p_package_name IN VARCHAR2
457 , p_package_type IN VARCHAR2
458 , p_package_text IN DBMS_SQL.VARCHAR2S
459 )
460 RETURN BOOLEAN
461 IS
462 --
463 --
464
465 l_lb INTEGER ;
466 l_ub INTEGER ;
467 --
468 compilation_fails EXCEPTION;
469 package_locked EXCEPTION;
470 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
471 PRAGMA EXCEPTION_INIT(package_locked ,-04021);
472 --
473 l_log_module VARCHAR2(240);
474 l_change_optimize_flag BOOLEAN;
475 l_original_optimize_level NUMBER;
476 BEGIN
477 --
478 IF g_log_enabled THEN
479 l_log_module := C_DEFAULT_MODULE||'.CreateBodyPackage';
480 END IF;
481 --
482 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
483
484 trace
485 (p_msg => 'BEGIN of CreateBodyPackage'
486 ,p_level => C_LEVEL_PROCEDURE
487 ,p_module => l_log_module);
488
489 END IF;
490 --
491 -- Init
492 --
493 l_lb := NVL(p_package_text.FIRST,0);
494 l_ub := NVL(p_package_text.LAST ,0);
495 APPS_ARRAY_DDL.glprogtext := p_package_text;
496 l_change_optimize_flag := FALSE;
497 --
498
499 IF (p_package_text.COUNT > 130000) THEN
500
501 l_original_optimize_level := $$PLSQL_OPTIMIZE_LEVEL;
502 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
503 trace
504 (p_msg => '-> Package count > 130000 and original optimize level: '
505 ||TO_CHAR(l_original_optimize_level)
506 ,p_level => C_LEVEL_STATEMENT
507 ,p_module => l_log_module);
508 END IF;
509
510 IF (l_original_optimize_level > 1) THEN
511 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
512 trace
513 (p_msg => '-> change optimize level'
514 ,p_level => C_LEVEL_STATEMENT
518 EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1';
515 ,p_module => l_log_module);
516 END IF;
517 l_change_optimize_flag := TRUE;
519 END IF;
520 END IF;
521
522
523
524
525 --
526 -- call apps_array_ddl API to create body package
527 --
528 --
529 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
530 trace
531 (p_msg => '-> CALL APPS_ARRAY_DDL.apps_array_ddl API'
532 ,p_level => C_LEVEL_PROCEDURE
533 ,p_module => l_log_module);
534 END IF;
535 APPS_ARRAY_DDL.apps_array_ddl(
536 lb => l_lb,
537 ub => l_ub
538 );
539 --
540 IF (l_change_optimize_flag) THEN
541 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
542 trace
543 (p_msg => '-> Change optimize level back '
544 ,p_level => C_LEVEL_STATEMENT
545 ,p_module => l_log_module);
546 END IF;
547 EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = '||TO_CHAR(l_original_optimize_level);
548 END IF;
549 --
550 --
551 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
552
553 trace
554 (p_msg => 'END of CreateBodyPackage'
555 ,p_level => C_LEVEL_PROCEDURE
556 ,p_module => l_log_module);
557
558 END IF;
559 --
560 RETURN TRUE;
561 --
562 EXCEPTION
563 WHEN package_locked THEN
564
565 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
566 trace
567 (p_msg => 'ERROR: XLA_CMP_PACKAGE_LOCKED'
568 ,p_level => C_LEVEL_EXCEPTION
569 ,p_module => l_log_module);
570 END IF;
571 xla_exceptions_pkg.raise_message
572 ('XLA'
573 ,'XLA_CMP_PACKAGE_LOCKED'
574 ,'PACKAGE_NAME'
575 , p_package_name
576 );
577
578 RETURN FALSE;
579 WHEN compilation_fails THEN
580
581 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
582 trace
583 (p_msg => 'ERROR: XLA_CMP_COMPILATION_FAILED'
584 ,p_level => C_LEVEL_EXCEPTION
585 ,p_module => l_log_module);
586 END IF;
587
588 xla_exceptions_pkg.raise_message
589 ('XLA'
590 ,'XLA_CMP_COMPILATION_FAILED'
591 ,'PACKAGE_NAME'
592 , p_package_name
593 );
594
595 RETURN FALSE;
596
597 WHEN xla_exceptions_pkg.application_exception THEN
598
599 RETURN FALSE;
600
601 WHEN OTHERS THEN
602 xla_exceptions_pkg.raise_message
603 (p_location => 'xla_cmp_create_pkg.CreateBodyPackage');
604 END CreateBodyPackage;
605
606 --
607 --+==========================================================================+
608 --| PRIVATE procedures and functions |
609 --| |
610 --| |
611 --| |
612 --| |
613 --| |
614 --+==========================================================================+
615 --
616 FUNCTION CreateSpecPackage ( p_product_rule_name IN VARCHAR2
617 , p_package_name IN VARCHAR2
618 , p_package_type IN VARCHAR2
619 , p_package_text IN VARCHAR2
620 )
621 RETURN BOOLEAN
622 IS
623 compilation_fails EXCEPTION;
624 package_locked EXCEPTION;
625 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
626 PRAGMA EXCEPTION_INIT(package_locked ,-04021);
627 l_log_module VARCHAR2(240);
628 BEGIN
629 --
630 IF g_log_enabled THEN
631 l_log_module := C_DEFAULT_MODULE||'.CreateSpecPackage';
632 END IF;
633 --
634 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
635
636 trace
637 (p_msg => 'BEGIN of CreateSpecPackage - '||p_package_name
638 ,p_level => C_LEVEL_PROCEDURE
639 ,p_module => l_log_module);
640
641 END IF;
642 --
643 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
644
645 trace
646 (p_msg => 'p_packgae_text = '||p_package_text
647 ,p_level => C_LEVEL_STATEMENT
648 ,p_module => l_log_module);
649
650 END IF;
651 --
652 --
653 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
654
655 trace
656 (p_msg => '-> CALL APPS_DDL.apps_ddl API'
657 ,p_level => C_LEVEL_PROCEDURE
658 ,p_module => l_log_module);
659
660 END IF;
661 --
662 APPS_DDL.apps_ddl(ddl_text => p_package_text);
663
664 --
665 /*
666 dump_package( p_package_name => p_package_name
667 , p_package_type => p_package_type
668 );
669 */
670
671 --
672 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
673
674 trace
675 (p_msg => 'END of CreateSpecPackage'
676 ,p_level => C_LEVEL_PROCEDURE
680 --
677 ,p_module => l_log_module);
678
679 END IF;
681 RETURN TRUE;
682 EXCEPTION
683 WHEN package_locked THEN
684 --
685 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
686 trace
687 (p_msg => 'ERROR: XLA_CMP_PACKAGE_LOCKED'
688 ,p_level => C_LEVEL_EXCEPTION
689 ,p_module => l_log_module);
690 END IF;
691
692 xla_exceptions_pkg.raise_message
693 ('XLA'
694 ,'XLA_CMP_PACKAGE_LOCKED'
695 ,'PACKAGE_NAME'
696 , p_package_name
697 );
698
699
700 RETURN FALSE;
701
702 WHEN compilation_fails THEN
703
704 get_pkg_errors( p_product_rule_name => p_product_rule_name
705 , p_package_name => p_package_name
706 , p_package_type => p_package_type)
707 ;
708 --
709 /*
710 dump_package( p_package_name => p_package_name
711 , p_package_type => p_package_type
712 );
713 */
714
715 --
716 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
717 trace
718 (p_msg => 'ERROR: XLA_CMP_COMPILATION_FAILED'
719 ,p_level => C_LEVEL_EXCEPTION
720 ,p_module => l_log_module);
721 END IF;
722
723 xla_exceptions_pkg.raise_message
724 ('XLA'
725 ,'XLA_CMP_COMPILATION_FAILED'
726 ,'PACKAGE_NAME'
727 , p_package_name
728 );
729
730 RETURN FALSE;
731
732
733 WHEN xla_exceptions_pkg.application_exception THEN
734 --
735 get_pkg_errors( p_product_rule_name => p_product_rule_name
736 , p_package_name => p_package_name
737 , p_package_type => p_package_type)
738 ;
739 --
740 /*
741 dump_package( p_package_name => p_package_name
742 , p_package_type => p_package_type
743 );
744 */
745 --
746 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
747 trace
748 (p_msg => 'ERROR: XLA_CMP_COMPILER_ERROR'
749 ,p_level => C_LEVEL_EXCEPTION
750 ,p_module => l_log_module);
751 END IF;
752 --
753 xla_exceptions_pkg.raise_message ('XLA'
754 ,'XLA_CMP_COMPILER_ERROR'
755 ,'PROCEDURE'
756 ,'xla_cmp_create_pkg.CompileProductRule'
757 ,'ERROR'
758 , sqlerrm
759 );
760 RETURN FALSE;
761 WHEN OTHERS THEN
762 xla_exceptions_pkg.raise_message
763 (p_location => 'xla_cmp_create_pkg.CreateSpecPackage');
764 END CreateSpecPackage;
765
766 --+==========================================================================+
767 --| PUBLIC procedures and functions |
768 --| |
769 --| |
770 --| |
771 --| |
772 --| |
773 --| |
774 --+==========================================================================+
775 --+==========================================================================+
776 --| PUBLIC FUNCTION |
777 --| create_package |
778 --| |
779 --| DESCRIPTION |
780 --| function called with package_text not null |
781 --+==========================================================================+
782
783 FUNCTION CreateSpecPackage( p_product_rule_name IN VARCHAR2
784 , p_package_name IN VARCHAR2
785 , p_package_text IN VARCHAR2
786 )
787 RETURN BOOLEAN
788 IS
789 --
790 l_package_text VARCHAR2(32000);
791 l_created_flag BOOLEAN := FALSE;
792 l_log_module VARCHAR2(240);
793 --
794 BEGIN
795 --
796 IF g_log_enabled THEN
797 l_log_module := C_DEFAULT_MODULE||'.CreateSpecPackage';
798 END IF;
799 --
800 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
801
802 trace
803 (p_msg => 'BEGIN of CreateSpecPackage'
804 ,p_level => C_LEVEL_PROCEDURE
805 ,p_module => l_log_module);
806
807 trace
808 (p_msg => 'p_product_rule_name = '||p_product_rule_name
809 ,p_level => C_LEVEL_PROCEDURE
810 ,p_module => l_log_module);
811
812 trace
813 (p_msg => 'p_package_name = '||p_package_name
814 ,p_level => C_LEVEL_PROCEDURE
815 ,p_module => l_log_module);
816
817 END IF;
818 --
819 l_package_text := p_package_text;
823 l_created_flag := CreateSpecPackage(
820 --
821 xla_cmp_string_pkg.truncate_lines( l_package_text);
822 --
824 p_product_rule_name => p_product_rule_name
825 , p_package_name => p_package_name
826 , p_package_type => C_SPECIFICATION
827 , p_package_text => l_package_text
828 );
829 --
830 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
831
832 trace
833 (p_msg => 'END of CreateSpecPackage'
834 ,p_level => C_LEVEL_PROCEDURE
835 ,p_module => l_log_module);
836
837 END IF;
838 --
839 RETURN l_created_flag;
840 EXCEPTION
841 WHEN xla_exceptions_pkg.application_exception THEN
842 RETURN FALSE;
843 WHEN OTHERS THEN
844 xla_exceptions_pkg.raise_message
845 (p_location => 'xla_cmp_create_pkg.CreateSpecPackage');
846 END CreateSpecPackage;
847 --
848 --+==========================================================================+
849 --| PUBLIC FUNCTION |
850 --| create_package |
851 --| |
852 --| DESCRIPTION |
853 --| function called with package_text not null |
854 --+==========================================================================+
855
856 FUNCTION CreateBodyPackage( p_product_rule_name IN VARCHAR2
857 , p_package_name IN VARCHAR2
858 , p_package_text IN DBMS_SQL.VARCHAR2S
859 )
860 RETURN BOOLEAN
861 IS
862 --
863 l_package_text DBMS_SQL.VARCHAR2S;
864 l_created_flag BOOLEAN := FALSE;
865 l_IsValid BOOLEAN := TRUE;
866 l_log_module VARCHAR2(240);
867 --
868 BEGIN
869 --
870 IF g_log_enabled THEN
871 l_log_module := C_DEFAULT_MODULE||'.CreateBodyPackage';
872 END IF;
873 --
874 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
875
876 trace
877 (p_msg => 'BEGIN of CreateBodyPackage'
878 ,p_level => C_LEVEL_PROCEDURE
879 ,p_module => l_log_module);
880
881 trace
882 (p_msg => 'p_product_rule_name = '||p_product_rule_name
883 ,p_level => C_LEVEL_PROCEDURE
884 ,p_module => l_log_module);
885
886 trace
887 (p_msg => 'p_package_name = '||p_package_name
888 ,p_level => C_LEVEL_PROCEDURE
889 ,p_module => l_log_module);
890
891 END IF;
892 --
893 --
894 l_created_flag := CreateBodyPackage( p_product_rule_name => p_product_rule_name
895 , p_package_name => p_package_name
896 , p_package_type => C_BODY
897 , p_package_text => p_package_text
898 );
899
900 --
901 l_IsValid := GetPackageStatus( p_package_name => p_package_name
902 , p_package_type => C_BODY
903 );
904 --
905 /*
906 dump_package(p_package_name => p_package_name
907 , p_package_type => C_BODY
908 );
909 */
910 --
911 IF NOT l_IsValid THEN
912 --
913 get_pkg_errors( p_product_rule_name => p_product_rule_name
914 , p_package_name => p_package_name
915 , p_package_type => C_BODY)
916 ;
917 END IF;
918 --
919 l_created_flag := l_created_flag AND l_IsValid;
920 --
921 --
922 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
923 IF l_created_flag THEN
924 trace
925 (p_msg => 'return value. = TRUE'
926 ,p_level => C_LEVEL_PROCEDURE
927 ,p_module => l_log_module);
928
929 ELSE
930 trace
931 (p_msg => 'return value. = FALSE'
932 ,p_level => C_LEVEL_PROCEDURE
933 ,p_module => l_log_module);
934
935 END IF;
936
937 trace
938 (p_msg => 'END of CreateBodyPackage'
939 ,p_level => C_LEVEL_PROCEDURE
940 ,p_module => l_log_module);
941
942 END IF;
943 --
944 RETURN l_created_flag;
945 --
946 EXCEPTION
947 WHEN xla_exceptions_pkg.application_exception THEN
948 RETURN FALSE;
949 WHEN OTHERS THEN
950 xla_exceptions_pkg.raise_message
951 (p_location => 'xla_cmp_create_pkg.CreateBodyPackage');
952 END CreateBodyPackage;
953
954 --Additions for the Transaction Account Builder
955
956 FUNCTION execute_ddl
957 (
958 p_ddl_text IN VARCHAR2
959 ,p_object_owner IN VARCHAR2
960 )
961 RETURN BOOLEAN
962 IS
963 compilation_fails EXCEPTION;
964 package_locked EXCEPTION;
965 l_sql_stmt VARCHAR2(2000);
966 l_log_module VARCHAR2(2000);
967 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
968 PRAGMA EXCEPTION_INIT(package_locked , -04021);
969 BEGIN
970 IF g_log_enabled THEN
971 l_log_module := C_DEFAULT_MODULE||'.execute_ddl';
972 END IF;
973
974 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
978 ,p_level => C_LEVEL_PROCEDURE);
975 trace
976 (p_module => l_log_module
977 ,p_msg => 'BEGIN ' || l_log_module
979 END IF;
980
981 IF p_object_owner IS NULL
982 THEN
983 APPS_DDL.apps_ddl( ddl_text => p_ddl_text );
984 ELSE
985
986 l_sql_stmt := 'BEGIN ' || p_object_owner || '.APPS_DDL.apps_ddl( ddl_text => ''' ||
987 p_ddl_text || ''' );' || ' END;';
988
989 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
990 trace
991 (p_module => l_log_module
992 ,p_msg => l_sql_stmt
993 ,p_level => C_LEVEL_STATEMENT);
994 END IF;
995
996 EXECUTE IMMEDIATE l_sql_stmt;
997
998 END IF;
999
1000 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1001 trace
1002 (p_module => l_log_module
1003 ,p_msg => 'END ' || l_log_module
1004 ,p_level => C_LEVEL_PROCEDURE);
1005 END IF;
1006
1007 RETURN TRUE;
1008
1009 EXCEPTION
1010 WHEN xla_exceptions_pkg.application_exception THEN
1011 RAISE;
1012 WHEN OTHERS THEN
1013 xla_exceptions_pkg.raise_message
1014 (p_location => 'xla_cmp_create_pkg.execute_ddl');
1015
1016 END execute_ddl
1017 ;
1018
1019
1020 FUNCTION execute_ddl
1021 (
1022 p_ddl_text IN DBMS_SQL.VARCHAR2S
1023 )
1024 RETURN BOOLEAN
1025 IS
1026 l_lb INTEGER ;
1027 l_ub INTEGER ;
1028 l_return_value BOOLEAN;
1029 compilation_fails EXCEPTION;
1030 package_locked EXCEPTION;
1031 l_log_module VARCHAR2(2000);
1032 l_fatal_error_message_text VARCHAR2(100);
1033 PRAGMA EXCEPTION_INIT (package_locked ,-04021);
1034
1035 BEGIN
1036 IF g_log_enabled THEN
1037 l_log_module := C_DEFAULT_MODULE||'.execute_ddl';
1038 END IF;
1039
1040 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1041 trace
1042 (p_module => l_log_module
1043 ,p_msg => 'BEGIN ' || l_log_module
1044 ,p_level => C_LEVEL_PROCEDURE);
1045 END IF;
1046
1047 l_return_value := TRUE;
1048
1049 l_lb := NVL(p_ddl_text.FIRST,0);
1050 l_ub := NVL(p_ddl_text.LAST ,0);
1051
1052
1053 APPS_ARRAY_DDL.glprogtext := p_ddl_text;
1054 APPS_ARRAY_DDL.apps_array_ddl
1055 (
1056 lb => l_lb
1057 ,ub => l_ub
1058 );
1059
1060 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1061 trace
1062 (p_module => l_log_module
1063 ,p_msg => 'END ' || l_log_module
1064 ,p_level => C_LEVEL_PROCEDURE);
1065 END IF;
1066
1067 RETURN l_return_value;
1068
1069 EXCEPTION
1070 WHEN xla_exceptions_pkg.application_exception THEN
1071 RAISE;
1072 WHEN compilation_fails
1073 THEN
1074 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1075 trace
1076 (p_module => l_log_module
1077 ,p_msg => 'EXCEPTION:'
1078 ,p_level => C_LEVEL_EXCEPTION);
1079 trace
1080 (p_msg => 'Compilation failed'
1081 ,p_level => C_LEVEL_EXCEPTION);
1082 END IF;
1083 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1084 trace
1085 (p_module => l_log_module
1086 ,p_msg => 'END ' || l_log_module
1087 ,p_level => C_LEVEL_PROCEDURE);
1088 END IF;
1089 RETURN FALSE;
1090 WHEN package_locked
1091 THEN
1092 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1093 trace
1094 (p_module => l_log_module
1095 ,p_msg => 'EXCEPTION:'
1096 ,p_level => C_LEVEL_EXCEPTION);
1097 trace
1098 (p_module => l_log_module
1099 ,p_msg => 'Package is locked'
1100 ,p_level => C_LEVEL_EXCEPTION);
1101 END IF;
1102 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1103 trace
1104 (p_module => l_log_module
1105 ,p_msg => 'END ' || l_log_module
1106 ,p_level => C_LEVEL_PROCEDURE);
1107 END IF;
1108 RETURN FALSE;
1109 WHEN OTHERS THEN
1110 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1111 trace
1112 (p_module => l_log_module
1113 ,p_msg => 'EXCEPTION:'
1114 ,p_level => C_LEVEL_EXCEPTION);
1115 trace
1116 (p_module => l_log_module
1117 ,p_msg => SQLERRM
1118 ,p_level => C_LEVEL_EXCEPTION);
1119 END IF;
1120 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1121 xla_cmp_common_pkg.dump_text
1122 (
1123 p_text => p_ddl_text
1124 );
1125
1126 END IF;
1127 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1128 trace
1129 (p_module => l_log_module
1130 ,p_msg => 'END ' || l_log_module
1131 ,p_level => C_LEVEL_PROCEDURE);
1132 END IF;
1133 RETURN FALSE;
1134 END execute_ddl
1135 ;
1136
1137 FUNCTION execute_dml
1138 (
1139 p_dml_text IN CLOB
1140 ,p_msg_mode IN VARCHAR2
1141 )
1142 RETURN BOOLEAN
1143 IS
1144
1145 l_lb INTEGER ;
1149 package_locked EXCEPTION;
1146 l_ub INTEGER ;
1147 l_return_value BOOLEAN;
1148 compilation_fails EXCEPTION;
1150 l_dml_text DBMS_SQL.VARCHAR2S;
1151 l_log_module VARCHAR2(2000);
1152 l_fatal_error_message_text VARCHAR2(100);
1153 l_msg_mode VARCHAR2(1);
1154
1155 BEGIN
1156 IF g_log_enabled THEN
1157 l_log_module := C_DEFAULT_MODULE||'.execute_ddl';
1158 END IF;
1159
1160 IF p_msg_mode IS NULL
1161 THEN
1162 l_msg_mode := G_STANDARD_MESSAGE;
1163 ELSE
1164 l_msg_mode := p_msg_mode;
1165 END IF;
1166
1167 l_return_value := TRUE;
1168
1169 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1170 trace
1171 (p_module => l_log_module
1172 ,p_msg => 'BEGIN ' || l_log_module
1173 ,p_level => C_LEVEL_PROCEDURE);
1174 END IF;
1175
1176 --Move the CLOB into a VARCHAR2S
1177 xla_cmp_common_pkg.clob_to_varchar2s
1178 (
1179 p_clob => p_dml_text
1180 ,p_varchar2s => l_dml_text
1181 );
1182
1183
1184 l_lb := NVL(l_dml_text.FIRST,0);
1185 l_ub := NVL(l_dml_text.LAST ,0);
1186
1187
1188 APPS_ARRAY_DDL.glprogtext := l_dml_text;
1189 APPS_ARRAY_DDL.apps_array_ddl
1190 (
1191 lb => l_lb
1192 ,ub => l_ub
1193 );
1194
1195 RETURN TRUE;
1196
1197 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1198 trace
1199 (p_module => l_log_module
1200 ,p_msg => 'END ' || l_log_module
1201 ,p_level => C_LEVEL_PROCEDURE);
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN OTHERS
1206 THEN
1207 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1208 trace
1209 (p_msg => 'EXCEPTION:'
1210 ,p_level => C_LEVEL_EXCEPTION);
1211 trace
1212 (p_msg => SQLERRM
1213 ,p_level => C_LEVEL_EXCEPTION);
1214 END IF;
1215 xla_exceptions_pkg.raise_message
1216 ( p_location => 'xla_cmp_create_pkg.execute_dml'
1217 ,p_msg_mode => l_msg_mode
1218 );
1219 END execute_dml
1220 ;
1221
1222 FUNCTION push_database_object
1223 (
1224 p_object_name IN VARCHAR2
1225 ,p_object_type IN VARCHAR2
1226 ,p_object_owner IN VARCHAR2
1227 ,p_apps_account IN VARCHAR2
1228 ,p_msg_mode IN VARCHAR2
1229 ,p_ddl_text IN CLOB
1230 )
1231 RETURN BOOLEAN
1232 IS
1233 l_return_value BOOLEAN;
1234 l_cur_position INTEGER;
1235 l_next_cr_position INTEGER;
1236 l_text_length INTEGER;
1237 l_additional_sql_stmt VARCHAR2(2000);
1238 l_ddl_text DBMS_SQL.VARCHAR2S;
1239 l_log_module VARCHAR2 (2000);
1240
1241 le_table_not_exists EXCEPTION;
1242 le_compilation_fails EXCEPTION;
1243 PRAGMA EXCEPTION_INIT (le_table_not_exists ,-00942);
1244 PRAGMA EXCEPTION_INIT (le_compilation_fails , -24344);
1245 BEGIN
1246 IF g_log_enabled THEN
1247 l_log_module := C_DEFAULT_MODULE||'.push_database_object';
1248 END IF;
1249
1250 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1251 trace
1252 (p_module => l_log_module
1253 ,p_msg => 'BEGIN ' || C_DEFAULT_MODULE||'.push_database_object'
1254 ,p_level => C_LEVEL_PROCEDURE);
1255 END IF;
1256
1257 l_return_value := TRUE;
1258
1259 IF (C_LEVEL_EVENT >= g_log_level) THEN
1260 trace
1261 (p_msg => 'DDL text size: ' || LENGTH(p_ddl_text)
1262 ,p_level => C_LEVEL_EVENT);
1263 END IF;
1264
1265 --If the object to push is a table try to drop in case it exists
1266 IF p_object_type = 'TABLE'
1267 THEN
1268 --drop the already existing one (if it exists)
1269 l_additional_sql_stmt :=
1270 'DROP TABLE ' || p_object_owner || '.' || p_object_name;
1271 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1272 trace
1273 (p_msg => l_additional_sql_stmt
1274 ,p_level => C_LEVEL_STATEMENT);
1275 END IF;
1276
1277 BEGIN
1278 EXECUTE IMMEDIATE l_additional_sql_stmt;
1279 EXCEPTION
1280 WHEN le_table_not_exists
1281 THEN
1282 NULL;
1283 WHEN OTHERS
1284 THEN
1285 RAISE;
1286 END;
1287 END IF;
1288
1289 --Move the CLOB into a VARCHAR2S
1290 xla_cmp_common_pkg.clob_to_varchar2s
1291 (
1292 p_clob => p_ddl_text
1293 ,p_varchar2s => l_ddl_text
1294 );
1295
1296 l_return_value := execute_ddl
1297 (
1298 p_ddl_text => l_ddl_text
1299 );
1300
1301
1302 --Post creation activities
1303
1304 --If the object to push is a table create synonym and grants
1305 IF p_object_type = 'TABLE'
1306 THEN
1307 --create the synonym
1308 l_additional_sql_stmt :=
1309 'CREATE OR REPLACE SYNONYM ' ||
1310 p_apps_account || '.' || p_object_name ||
1314 trace
1311 ' FOR ' || p_object_owner || '.' || p_object_name;
1312
1313 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1315 (p_msg => l_additional_sql_stmt
1316 ,p_level => C_LEVEL_STATEMENT);
1317 END IF;
1318
1319 EXECUTE IMMEDIATE l_additional_sql_stmt;
1320
1321 --grant all to the apps account
1322 l_return_value := execute_ddl
1323 (
1324 p_ddl_text => 'GRANT ALL ON ' || p_object_owner || '.' ||
1325 p_object_name || ' TO ' ||
1326 p_apps_account || ' WITH GRANT OPTION'
1327 ,p_object_owner => p_object_owner
1328 );
1329
1330 ELSIF p_object_type = 'PACKAGE BODY'
1331 THEN
1332 l_additional_sql_stmt :=
1333 'ALTER PACKAGE ' || p_object_name ||
1334 ' COMPILE
1335 ';
1336
1337 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1338 trace
1339 (p_msg => l_additional_sql_stmt
1340 ,p_level => C_LEVEL_STATEMENT);
1341 END IF;
1342
1343 EXECUTE IMMEDIATE l_additional_sql_stmt;
1344 END IF;
1345
1346 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1347 trace
1348 (p_module => l_log_module
1349 ,p_msg => 'END ' || C_DEFAULT_MODULE||'.push_database_object'
1350 ,p_level => C_LEVEL_PROCEDURE);
1351 END IF;
1352
1353 RETURN l_return_value;
1354
1355 EXCEPTION
1356 WHEN xla_exceptions_pkg.application_exception THEN
1357 RAISE;
1358 WHEN le_compilation_fails
1359 THEN
1360 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1361 trace
1362 (p_module => l_log_module
1363 ,p_msg => 'EXCEPTION:'
1364 ,p_level => C_LEVEL_EXCEPTION);
1365 trace
1366 (p_module => l_log_module
1367 ,p_msg => 'Compilation failed'
1368 ,p_level => C_LEVEL_EXCEPTION);
1369 trace
1370 (p_module => l_log_module
1371 ,p_msg => SQLERRM
1372 ,p_level => C_LEVEL_EXCEPTION);
1373 END IF;
1377 p_text => p_ddl_text
1374 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1375 xla_cmp_common_pkg.dump_text
1376 (
1378 );
1379 END IF;
1380
1381 xla_exceptions_pkg.raise_message
1382 ( p_appli_s_name => 'XLA'
1383 ,p_msg_name => 'XLA_TAB_CMP_ALTER_PKG_COMPILE'
1384 ,p_token_1 => 'PACKAGE_NAME'
1385 ,p_value_1 => p_object_name
1386 ,p_token_2 => 'ERROR_MSG'
1387 ,p_value_2 => SQLERRM
1388 ,p_msg_mode => p_msg_mode
1389 );
1390
1391 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1392 trace
1393 (p_module => l_log_module
1394 ,p_msg => 'END ' || l_log_module
1395 ,p_level => C_LEVEL_PROCEDURE);
1396 END IF;
1397 RETURN FALSE;
1398 WHEN OTHERS THEN
1399 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1400 trace
1401 (p_msg => 'EXCEPTION:'
1402 ,p_level => C_LEVEL_EXCEPTION);
1403 trace
1404 (p_msg => SQLERRM
1405 ,p_level => C_LEVEL_EXCEPTION);
1406 END IF;
1407 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1408 xla_cmp_common_pkg.dump_text
1409 (
1410 p_text => p_ddl_text
1411 );
1412 END IF;
1413
1414 xla_exceptions_pkg.raise_message
1415 ( p_location => 'xla_cmp_create_pkg.push_database_object'
1416 ,p_msg_mode => p_msg_mode
1417 );
1418
1419 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1420 trace
1421 (p_module => l_log_module
1422 ,p_msg => 'END ' || l_log_module
1423 ,p_level => C_LEVEL_PROCEDURE);
1424 END IF;
1425 RETURN FALSE;
1426 END push_database_object;
1427
1428
1429
1430 --
1431 --=============================================================================
1432 --
1433 --
1434 --
1435 --
1436 --
1437 --
1438 --
1439 --
1440 --
1441 --
1442 --
1443 --
1444 --
1445 --
1446 --
1447 --
1448 --
1449 --
1450 --
1451 --
1452 --
1453 --
1454 --=============================================================================
1455 --=============================================================================
1456 -- *********** Initialization routine **********
1457 --=============================================================================
1458
1459 BEGIN
1460
1461 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1462 g_log_enabled := fnd_log.test
1463 (log_level => g_log_level
1464 ,module => C_DEFAULT_MODULE);
1465
1466 IF NOT g_log_enabled THEN
1467 g_log_level := C_LEVEL_LOG_DISABLED;
1468 END IF;
1469
1470
1471 END xla_cmp_create_pkg; -- end of package body