[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_EXTRACT_PKG
Source
1 PACKAGE BODY fa_xla_cmp_extract_pkg AS
2 /* $Header: faxlaceb.pls 120.3 2009/10/29 12:45:30 bridgway ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | fa_xla_cmp_extract_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for XLA extract package generation |
14 | |
15 | |
16 | HISTORY |
17 | 25-FEB-2006 BRIDGWAY Created |
18 | |
19 +===========================================================================*/
20
21
22 --+==========================================================================+
23 --| |
24 --| |
25 --| Extract templates/Global constants |
26 --| |
27 --| |
28 --+==========================================================================+
29
30
31 C_COMMENT CONSTANT VARCHAR2(2000) :=
32 '/'||'*======================================================================+
33 | Copyright (c) 1997 Oracle Corporation |
34 | Redwood Shores, CA, USA |
35 | All rights reserved. |
36 +=======================================================================+
37 | Package Name |
38 | $name
39 | |
40 | DESCRIPTION |
41 | Package generated From FA AAD setups |
42 | |
43 | HISTORY |
44 | $history
45 +=======================================================================*'||'/'
46 ;
47
48
49
50 --+==========================================================================+
51 --| specification package template |
52 --+==========================================================================+
53
54
55 C_PACKAGE_SPEC CONSTANT VARCHAR2(32000) :=
56
57 'CREATE OR REPLACE PACKAGE $PACKAGE_NAME$ AS
58
59 $header$
60
61
62 PROCEDURE load_data ;
63
64
65 END $PACKAGE_NAME$;
66
67 ';
68
69
70 C_PACKAGE_BODY_CLOSE CONSTANT VARCHAR2(32000) := '
71
72 END $PACKAGE_NAME$;
73
74 ';
75
76
77 --+==========================================================================+
78 --| Template Body package associated to a Product Accounting definition |
79 --+==========================================================================+
80
81 C_PACKAGE_BODY_1 CONSTANT VARCHAR2(32000) := '
82
83 CREATE OR REPLACE PACKAGE BODY $PACKAGE_NAME$ AS
84
85 $header$
86
87
88 -- TYPES
89 -- globals / constants
90
91 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
92
93 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
94 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
95 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
96 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
97 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
98 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
99
100 G_MODULE_NAME CONSTANT VARCHAR2(50):= ''fa.plsql.fa_xla_extract_$EXTRACT_TYPE$_pkg.'';
101
102
103 --+============================================+
104 --| |
105 --| PRIVATE PROCEDURES/FUNCTIONS |
106 --| |
107 --+============================================+
108
109 ';
110
111 -- BMR removed these as they will be built in lower level "generate" routines called from below
112 -- i.e. load_header / lines / ccid gen, etc
113 --
114 -- also not removing package body two as this will be dynamic too
115
116
117 --+==========================================================================+
118 --| |
119 --| End of Constants for Dynamic packages |
120 --| |
121 --+==========================================================================+
122
123 --+==========================================================================+
124 --| |
125 --| Private global variable |
126 --| |
127 --+==========================================================================+
128
129 g_UserName VARCHAR2(100);
130 g_PackageName VARCHAR2(60);
131 g_ProductRuleName VARCHAR2(80);
132 g_ProductRuleVersion VARCHAR2(30);
133
134 --+==========================================================================+
135 --| |
136 --| Private global constant or variable declarations |
137 --| |
138 --+==========================================================================+
139
140 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
141
142 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
143
144 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
145 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
146 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
147 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
148 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
149 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
150
151 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_extract_pkg.';
152
153 --+==========================================================================+
154 --| |
155 --| OVERVIEW of private procedures and functions |
156 --| |
157 --+==========================================================================+
158
159 --+==========================================================================+
160 --| |
161 --| PRIVATE procedures and functions |
162 --| |
163 --+==========================================================================+
164
165 /*------------------------------------------------+
166 | |
167 | Private function |
168 | |
169 | return the user name |
170 | |
171 +------------------------------------------------*/
172
173 FUNCTION GetUserName
174 RETURN VARCHAR2 IS
175
176 l_user_name VARCHAR2(100);
177 l_procedure_name varchar2(80) := 'GetUserName';
178
179 BEGIN
180
181 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
182 fnd_log.string(G_LEVEL_PROCEDURE,
183 G_MODULE_NAME||l_procedure_name||'.begin',
184 'Beginning of procedure');
185 END IF;
186
187 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
188 fnd_log.string(G_LEVEL_STATEMENT,
189 G_MODULE_NAME||l_procedure_name,
190 'SQL - Select from fnd_user');
191 END IF;
192
193 SELECT nvl(fd.user_name, 'ANONYMOUS')
194 INTO l_user_name
195 FROM fnd_user fd
196 WHERE fd.user_id = fnd_global.user_id;
197
198 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
199 fnd_log.string(G_LEVEL_STATEMENT,
200 G_MODULE_NAME||l_procedure_name,
201 'User name = ' || l_user_name);
202 END IF;
203
204 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
205 fnd_log.string(G_LEVEL_PROCEDURE,
206 G_MODULE_NAME||l_procedure_name||'.end',
207 'End of procedure');
208 END IF;
209
210 RETURN l_user_name;
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 l_user_name := 'ANONYMOUS';
215 RETURN l_user_name;
216
217 WHEN OTHERS THEN
218 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
219 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
220 fnd_message.set_token('ORACLE_ERR',SQLERRM);
221 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
222 END IF;
223 raise;
224
225 END GetUserName;
226
227 /*--------------------------------------------------------------+
228 | |
229 | Private function |
230 | |
231 | Create the comment in the FA Extract packages |
232 | |
233 +--------------------------------------------------------------*/
234 FUNCTION InsertString( p_InputString IN VARCHAR2
235 , p_token IN VARCHAR2
236 , p_value IN VARCHAR2)
237 RETURN VARCHAR2 IS
238
239 l_OutputString VARCHAR2(2000);
240 l_procedure_name varchar2(80) := 'InsertString';
241
242 BEGIN
243
244 l_OutputString := REPLACE(p_InputString,p_token,p_value);
245 l_OutputString := SUBSTR(l_OutputString,1,66);
246 l_OutputString := l_Outputstring || LPAD('|', 67- LENGTH(l_OutputString));
247 return l_OutputString ;
248
249 END InsertString;
250
251 /*--------------------------------------------------------------+
252 | |
253 | Private function |
254 | |
255 | GeneratePkgComment |
256 | |
257 | Create the comment in the FA extract packages |
258 | |
259 +--------------------------------------------------------------*/
260
261 FUNCTION GeneratePkgComment ( p_user_name IN VARCHAR2
262 , p_package_name IN VARCHAR2
263 )
264 RETURN VARCHAR2 IS
265
266 l_header VARCHAR2(32000);
267 l_StringValue VARCHAR2(2000);
268 l_procedure_name varchar2(80) := 'GeneratePkgComment';
269
270 BEGIN
271
272 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
273 fnd_log.string(G_LEVEL_PROCEDURE,
274 G_MODULE_NAME||l_procedure_name||'.begin',
275 'Beginning of procedure');
276 END IF;
277
278 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
279 fnd_log.string(G_LEVEL_STATEMENT,
280 G_MODULE_NAME||l_procedure_name,
281 'p_user_name = '||p_user_name||
282 ' - p_package_name = '||p_package_name);
283 END IF;
284
285 l_header := C_COMMENT;
286
287 l_StringValue := InsertString( p_InputString => '$pkg_name'
288 ,p_token => '$pkg_name'
289 ,p_value => p_package_name
290 );
291
292 l_header := REPLACE(l_header,'$name',l_StringValue);
293
294 l_StringValue := REPLACE('Generated at $date by user $user ' ,'$date',
295 TO_CHAR(sysdate, 'DD-MM-YYYY "at" HH:MM:SS' ));
296
297 l_StringValue := InsertString(p_InputString => l_StringValue
298 ,p_token => '$user'
299 ,p_value => p_user_name
300 );
301
302 l_header := REPLACE(l_header,'$history',l_StringValue );
303
304 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
305 fnd_log.string(G_LEVEL_PROCEDURE,
306 G_MODULE_NAME||l_procedure_name||'.end',
307 'End of procedure');
308 END IF;
309
310 RETURN l_header;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
315 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
316 fnd_message.set_token('ORACLE_ERR',SQLERRM);
317 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
318 END IF;
319 raise;
320
321 END GeneratePkgComment;
322
323
324 /*--------------------------------------------------------------+
325 | |
326 | |
327 | |
328 | Generation of FA Extract specification packages |
329 | |
330 | |
331 | |
332 +--------------------------------------------------------------*/
333
334
335 /*--------------------------------------------------------------+
336 | |
337 | Private function |
338 | |
339 | BuildSpecPkg |
340 | |
341 | Creates the FA Extract package specifications |
342 | |
343 +--------------------------------------------------------------*/
344
345 FUNCTION BuildSpecPkg( p_user_name IN VARCHAR2
346 , p_package_name IN VARCHAR2
350 l_SpecPkg VARCHAR2(32000);
347 , p_extract_type IN VARCHAR2)
348 RETURN VARCHAR2 IS
349
351 l_procedure_name varchar2(80) := 'BuildSpecPkg';
352
353 BEGIN
354
355 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
356 fnd_log.string(G_LEVEL_PROCEDURE,
357 G_MODULE_NAME||l_procedure_name||'.begin',
358 'Beginning of procedure');
359 END IF;
360
361 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
362 fnd_log.string(G_LEVEL_STATEMENT,
363 G_MODULE_NAME||l_procedure_name,
364 'p_user_name = '||p_user_name||
365 ' - p_package_name = '||p_package_name||
366 ' - p_extract_type = '||p_extract_type);
367 END IF;
368
369 l_SpecPkg := C_PACKAGE_SPEC;
370
371 l_SpecPkg := REPLACE(l_SpecPkg,'$PACKAGE_NAME$',p_package_name);
372
373 l_SpecPkg := REPLACE(l_SpecPkg,'$header$',GeneratePkgComment (
374 p_user_name => p_user_name
375 , p_package_name => p_package_name
376 ) );
377
378 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
379 fnd_log.string(G_LEVEL_PROCEDURE,
380 G_MODULE_NAME||l_procedure_name||'.end',
381 'End of procedure' || length(l_SpecPkg));
382 END IF;
383
384 RETURN l_SpecPkg ;
385
386 EXCEPTION
387
388 WHEN OTHERS THEN
389 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
390 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
391 fnd_message.set_token('ORACLE_ERR',SQLERRM);
392 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
393 END IF;
394 raise;
395
396 END BuildSpecPkg;
397
398 /*--------------------------------------------------------------+
399 | |
400 | Private function |
401 | |
402 | GenerateSpecPackage |
403 | |
404 | Generates the FA Extract specifcation packages from AAD |
405 | Returns TRUE if the compiler succeeds to generate the spec. |
406 | package, FALSE otherwise. |
407 +--------------------------------------------------------------*/
408
409 FUNCTION GenerateSpecPackage(
410 p_extract_type IN VARCHAR2
411 , p_package OUT NOCOPY VARCHAR2
412 )
413 RETURN BOOLEAN IS
414
415 l_procedure_name varchar2(80) := 'GenerateSpecPackage';
416
417 BEGIN
418
419 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
420 fnd_log.string(G_LEVEL_PROCEDURE,
421 G_MODULE_NAME||l_procedure_name||'.begin',
422 'Beginning of procedure');
423 END IF;
424
425 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
426 fnd_log.string(G_LEVEL_STATEMENT,
427 G_MODULE_NAME||l_procedure_name,
428 ' p_extract_type = '||p_extract_type);
429 END IF;
430
431 p_package := BuildSpecPkg(
432 p_user_name => g_UserName
433 , p_package_name => g_PackageName
434 , p_extract_type => p_extract_type
435 );
436
437 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
438 fnd_log.string(G_LEVEL_PROCEDURE,
439 G_MODULE_NAME||l_procedure_name||'.end',
440 'End of procedure');
441 END IF;
442
443 RETURN (p_package IS NOT NULL);
444
445 EXCEPTION
446
447 WHEN OTHERS THEN
448 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
449 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
450 fnd_message.set_token('ORACLE_ERR',SQLERRM);
451 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
452 END IF;
453 return false;
454
455 END GenerateSpecPackage;
456
457 /*------------------------------------------------------------------+
458 | |
459 | Private function |
460 | |
461 | CreateSpecPackage |
462 | |
463 | Creates/compiler the FA package specifications in the DATABASE |
464 | It returns TRUE, if the package created is VALID, FALSE otherwise |
465 | |
466 +------------------------------------------------------------------*/
467
468 FUNCTION CreateSpecPackage ( p_extract_type IN VARCHAR2)
469
470 RETURN BOOLEAN IS
471
472 l_Package VARCHAR2(32000);
473 l_IsCompiled BOOLEAN;
474 l_procedure_name varchar2(80) := 'CreateSpecPackage';
475
476 BEGIN
477
478 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
479 fnd_log.string(G_LEVEL_PROCEDURE,
480 G_MODULE_NAME||l_procedure_name||'.begin',
481 'Beginning of procedure');
482 END IF;
483
484 l_IsCompiled := GenerateSpecPackage(
485 p_extract_type => p_extract_type
489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
486 , p_package => l_Package
487 );
488
490 fnd_log.string(G_LEVEL_STATEMENT,
491 G_MODULE_NAME||l_procedure_name,
492 ' Compile the specification package in the DATABASE'||
493 ' - length of the package = '||length(l_Package));
494 END IF;
495
496 l_IsCompiled := fa_xla_cmp_create_pkg.CreateSpecPackage(
497 p_package_name => g_PackageName
498 , p_package_text => l_Package
499 ) AND l_IsCompiled;
500
501 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
502 fnd_log.string(G_LEVEL_STATEMENT,
503 G_MODULE_NAME||l_procedure_name,
504 'END of CreateSpecPackage : return = '
505 ||CASE WHEN l_IsCompiled THEN 'TRUE' ELSE 'FALSE' END);
506 END IF;
507
508
509 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
510 fnd_log.string(G_LEVEL_PROCEDURE,
511 G_MODULE_NAME||l_procedure_name||'.end',
512 'End of procedure');
513 END IF;
514
515
516 RETURN l_IsCompiled;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
521 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
522 fnd_message.set_token('ORACLE_ERR',SQLERRM);
523 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
524 END IF;
525 return FALSE;
526
527 END CreateSpecPackage;
528
529 /*--------------------------------------------------------------+
530 | |
531 | |
532 | |
533 | Generation of FA Extract Body packages |
534 | |
535 | |
536 | |
537 +--------------------------------------------------------------*/
538
539 /*--------------------------------------------------------------+
540 | |
541 | Private function |
542 | |
543 | GeneratePrivateProcedures |
544 | |
545 | Generates private procedures and functions in FA packages |
546 | |
547 +--------------------------------------------------------------*/
548
549 FUNCTION GeneratePrivateProcedures
550 (p_package_name IN VARCHAR2
551 ,p_extract_type IN VARCHAR2
552 ,p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S)
553 RETURN BOOLEAN IS
554
555 l_IsCompiled BOOLEAN;
556 l_IsGenerated BOOLEAN;
557
558 l_array_body DBMS_SQL.VARCHAR2S;
559 l_array_string DBMS_SQL.VARCHAR2S;
560 l_procedure_name varchar2(80) := 'GeneratePrivateProcedures';
561
562 BEGIN
563
564 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
565 fnd_log.string(G_LEVEL_PROCEDURE,
566 G_MODULE_NAME||l_procedure_name||'.begin',
567 'Beginning of procedure');
568 END IF;
569
570 l_IsCompiled := TRUE;
571 l_IsGenerated := TRUE;
572
573 -- generate description functions and the call to those functions
574 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
575 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
576
577 --
578 -- Generate Header Section
579 --
580
581 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
582 fnd_log.string(G_LEVEL_STATEMENT,
583 G_MODULE_NAME||l_procedure_name,
584 '-> CALL FA_XLA_CMP_HEADER_PKG.GenerateHeaderExtract API');
585 END IF;
586
587 l_IsGenerated :=
588 FA_XLA_CMP_HEADER_PKG.GenerateHeaderExtract
589 (p_extract_type => p_extract_type,
590 p_package_body => l_array_string);
591
592 l_IsCompiled := l_IsCompiled AND l_IsGenerated ;
593
594 l_array_body :=
595 fa_cmp_string_pkg.ConcatTwoStrings
596 (p_array_string_1 => l_array_body
597 ,p_array_string_2 => l_array_string);
598
599 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
600
601
602 --
603 -- generate line sections
604 --
605 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
606 fnd_log.string(G_LEVEL_STATEMENT,
607 G_MODULE_NAME||l_procedure_name,
608 '-> CALL FA_XLA_CMP_LINES_PKG.GenerateLinesExtract API');
609 END IF;
610
611 l_IsGenerated :=
612 FA_XLA_CMP_LINE_PKG.GenerateLineExtract
613 (p_extract_type => p_extract_type,
614 p_package_body => l_array_string);
615
616
617 l_array_body :=
618 fa_cmp_string_pkg.ConcatTwoStrings
619 (p_array_string_1 => l_array_body
620 ,p_array_string_2 => l_array_string);
621
622 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
623
624 l_IsCompiled := l_IsCompiled AND l_IsGenerated;
625
626
630 --
627
628 --
629 -- generate mls section
631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
632 fnd_log.string(G_LEVEL_STATEMENT,
633 G_MODULE_NAME||l_procedure_name,
634 '-> CALL FA_XLA_CMP_LINES_PKG.GenerateMlsExtract API');
635 END IF;
636
637 l_IsGenerated :=
638 FA_XLA_CMP_MLS_PKG.GenerateMlsExtract
639 (p_extract_type => p_extract_type,
640 p_package_body => l_array_string);
641
642
643 l_array_body :=
644 fa_cmp_string_pkg.ConcatTwoStrings
645 (p_array_string_1 => l_array_body
646 ,p_array_string_2 => l_array_string);
647
648 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
649
650 l_IsCompiled := l_IsCompiled AND l_IsGenerated;
651
652
653
654 --
655 -- generate ccid sections - for legacy workflow support
656 --
657
658 l_IsGenerated :=
659 FA_XLA_CMP_CCID_PKG.GenerateCCIDExtract
660 (p_extract_type => p_extract_type,
661 p_package_body => l_array_string);
662
663
664 l_array_body :=
665 fa_cmp_string_pkg.ConcatTwoStrings
666 (p_array_string_1 => l_array_body
667 ,p_array_string_2 => l_array_string);
668
669 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
670
671 l_IsCompiled := l_IsCompiled AND l_IsGenerated ;
672
673 --
674 -- generate locking sections
675 --
676
677 l_IsGenerated :=
678 FA_XLA_CMP_LOCK_PKG.GenerateLockingExtract
679 (p_extract_type => p_extract_type,
680 p_package_body => l_array_string);
681
682 l_array_body :=
683 fa_cmp_string_pkg.ConcatTwoStrings
684 (p_array_string_1 => l_array_body
685 ,p_array_string_2 => l_array_string);
686
687 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
688
689 l_IsCompiled := l_IsCompiled AND l_IsGenerated ;
690
691
692 --
693 -- generate main procedure
694 --
695
696 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
697 fnd_log.string(G_LEVEL_STATEMENT,
698 G_MODULE_NAME||l_procedure_name,
699 '-> CALL FA_XLA_CMP_LOAD_PKG.GenerateLoadExtract API');
700 END IF;
701
702 l_IsGenerated :=
703 FA_XLA_CMP_LOAD_PKG.GenerateLoadExtract
704 (p_extract_type => p_extract_type,
705 p_package_body => l_array_string);
706
707 l_IsCompiled := l_IsCompiled AND l_IsGenerated ;
708
709 l_array_body :=
710 fa_cmp_string_pkg.ConcatTwoStrings
711 (p_array_string_1 => l_array_body
712 ,p_array_string_2 => l_array_string);
713
714
715 l_array_string := fa_cmp_string_pkg.g_null_varchar2s;
716
717 p_package_body := l_array_body;
718
719
720 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
721 fnd_log.string(G_LEVEL_STATEMENT,
722 G_MODULE_NAME||l_procedure_name,
723 'l_isCompiled = '||CASE WHEN l_IsCompiled
724 THEN 'TRUE'
725 ELSE 'FALSE' END);
726 END IF;
727
728 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
729 fnd_log.string(G_LEVEL_PROCEDURE,
730 G_MODULE_NAME||l_procedure_name||'.end',
731 'End of procedure');
732 END IF;
733
734 RETURN l_IsCompiled;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
739 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
740 fnd_message.set_token('ORACLE_ERR',SQLERRM);
741 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
742 END IF;
743 return false;
744
745 END GeneratePrivateProcedures;
746
747 /*--------------------------------------------------------------+
748 | |
749 | Private function |
750 | |
751 | GenerateBody |
752 | |
753 | Generates the procedures and functions in FA body packages |
754 | |
755 +--------------------------------------------------------------*/
756
757 FUNCTION GenerateBody
758 (p_package_name IN VARCHAR2
759 ,p_extract_type IN VARCHAR2
760 ,p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S)
761 RETURN BOOLEAN IS
762
763 l_IsCompiled BOOLEAN;
764 l_procedure_name varchar2(80) := 'GenerateBody';
765
766 BEGIN
767
768 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
769 fnd_log.string(G_LEVEL_PROCEDURE,
770 G_MODULE_NAME||l_procedure_name||'.begin',
771 'Beginning of procedure');
772 END IF;
773
774 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
775 fnd_log.string(G_LEVEL_STATEMENT,
776 G_MODULE_NAME||l_procedure_name,
777 'p_package_name = '||p_package_name||
778 ' - p_extract_type = '||p_extract_type);
779 END IF;
780
781 l_IsCompiled :=
785 ,p_package_body => p_package_body);
782 GeneratePrivateProcedures
783 (p_package_name => p_package_name
784 ,p_extract_type => p_extract_type
786
787
788 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
789 fnd_log.string(G_LEVEL_STATEMENT,
790 G_MODULE_NAME||l_procedure_name,
791 'l_isCompiled = '||CASE WHEN l_IsCompiled
792 THEN 'TRUE'
793 ELSE 'FALSE' END);
794 END IF;
795
796 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
797 fnd_log.string(G_LEVEL_PROCEDURE,
798 G_MODULE_NAME||l_procedure_name||'.end',
799 'End of procedure');
800 END IF;
801
802 RETURN l_IsCompiled;
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
807 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
808 fnd_message.set_token('ORACLE_ERR',SQLERRM);
809 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
810 END IF;
811 return false;
812
813 END GenerateBody;
814
815 /*--------------------------------------------------------------+
816 | |
817 | Private function |
818 | |
819 | GenerateBodyPackage |
820 | |
821 | Generates the FA body packages from AAD definitions |
822 | Returns TRUE if the compiler succeeds to generate the body |
823 | package, FALSE otherwise. |
824 +--------------------------------------------------------------*/
825
826 FUNCTION GenerateBodyPackage
827 (p_extract_type IN VARCHAR2
828 ,p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S)
829 RETURN BOOLEAN IS
830
831 l_array_pkg DBMS_SQL.VARCHAR2S;
832 l_BodyPkg VARCHAR2(32000);
833 l_array_body DBMS_SQL.VARCHAR2S;
834 l_IsCompiled BOOLEAN;
835 l_procedure_name varchar2(80) := 'GenerateBodyPackage';
836
837 BEGIN
838
839 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
840 fnd_log.string(G_LEVEL_PROCEDURE,
841 G_MODULE_NAME||l_procedure_name||'.begin',
842 'Beginning of procedure');
843 END IF;
844
845 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
846 fnd_log.string(G_LEVEL_STATEMENT,
847 G_MODULE_NAME||l_procedure_name,
848 'p_extract_type = '||p_extract_type);
849 END IF;
850
851 l_IsCompiled := TRUE;
852 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
853 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
854
855 l_BodyPkg := C_PACKAGE_BODY_1;
856 l_BodyPkg := REPLACE(l_BodyPkg,'$PACKAGE_NAME$' ,g_PackageName);
857 l_BodyPkg := REPLACE(l_BodyPkg,'$EXTRACT_TYPE$' ,lower(p_extract_type));
858
859 l_BodyPkg :=
860 REPLACE(l_BodyPkg,'$header$'
861 ,GeneratePkgComment
862 (p_user_name => g_UserName
863 ,p_package_name => g_PackageName
864 ));
865
866 l_BodyPkg := REPLACE(l_BodyPkg,'$PACKAGE_NAME$' ,g_PackageName);
867
868 fa_cmp_string_pkg.CreateString
869 (p_package_text => l_BodyPkg
870 ,p_array_string => l_array_pkg);
871
872 l_IsCompiled :=
873 GenerateBody
874 (p_package_name => g_PackageName
875 ,p_extract_type => p_extract_type
876 ,p_package_body => l_array_body);
877
878
879 l_array_pkg :=
880 fa_cmp_string_pkg.ConcatTwoStrings
881 (p_array_string_1 => l_array_pkg
882 ,p_array_string_2 => l_array_body);
883
884 fa_cmp_string_pkg.CreateString
885 (p_package_text => l_BodyPkg
886 ,p_array_string => l_array_body);
887
888 l_BodyPkg := C_PACKAGE_BODY_CLOSE ;
889 l_BodyPkg := REPLACE(l_BodyPkg,'$PACKAGE_NAME$' ,g_PackageName);
890
891 fa_cmp_string_pkg.CreateString
892 (p_package_text => l_BodyPkg
893 ,p_array_string => l_array_body);
894
895 l_array_pkg :=
896 fa_cmp_string_pkg.ConcatTwoStrings
897 (p_array_string_1 => l_array_pkg
898 ,p_array_string_2 => l_array_body);
899
900 p_package_body := l_array_pkg;
901
902 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
903 fnd_log.string(G_LEVEL_STATEMENT,
904 G_MODULE_NAME||l_procedure_name,
905 'return value (l_IsCompiled) = '||
906 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
907 END IF;
908
909 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
910 fnd_log.string(G_LEVEL_PROCEDURE,
911 G_MODULE_NAME||l_procedure_name||'.end',
912 'End of procedure');
913 END IF;
914
915 RETURN l_IsCompiled;
916
917 EXCEPTION
918 WHEN OTHERS THEN
919 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
920 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
921 fnd_message.set_token('ORACLE_ERR',SQLERRM);
922 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
923 END IF;
927
924 RETURN FALSE;
925
926 END GenerateBodyPackage;
928 /*--------------------------------------------------------------+
929 | |
930 | Private function |
931 | |
932 | CreateBodyPackage |
933 | |
934 | Compiles the FA body packages in the DATABASE |
935 | Returns TRUE if the package body is VALID, FALSE otherwise. |
936 | |
937 +--------------------------------------------------------------*/
938 FUNCTION CreateBodyPackage
939 (p_extract_type IN VARCHAR2)
940 RETURN BOOLEAN IS
941
942 l_Package DBMS_SQL.VARCHAR2S;
943 l_PackageName VARCHAR2(30);
944 l_ProductRuleName VARCHAR2(80);
945 l_ProductRuleVersion VARCHAR2(30);
946
947 l_IsCompiled BOOLEAN;
948 l_procedure_name varchar2(80) := 'CreateBodyPackage';
949
950 BEGIN
951
952 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
953 fnd_log.string(G_LEVEL_PROCEDURE,
954 G_MODULE_NAME||l_procedure_name||'.begin',
955 'Beginning of procedure');
956 END IF;
957
958
959
960 l_IsCompiled :=
961 GenerateBodyPackage
962 (p_extract_type => p_extract_type
963 ,p_package_body => l_Package);
964
965 l_IsCompiled :=
966 fa_xla_cmp_create_pkg.CreateBodyPackage
967 (p_package_name => g_PackageName
968 ,p_package_text => l_Package) AND l_IsCompiled;
969
970 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
971 fnd_log.string(G_LEVEL_STATEMENT,
972 G_MODULE_NAME||l_procedure_name,
973 'return value (l_IsCompiled) = '||
974 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
975 END IF;
976
977 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
978 fnd_log.string(G_LEVEL_PROCEDURE,
979 G_MODULE_NAME||l_procedure_name||'.end',
980 'End of procedure');
981 END IF;
982
983 RETURN l_IsCompiled;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
988 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
989 fnd_message.set_token('ORACLE_ERR',SQLERRM);
990 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
991 END IF;
992
993 return false;
994
995 END CreateBodyPackage;
996
997
998 --+==========================================================================+
999 --| PUBLIC function |
1000 --| Compile |
1001 --| |
1002 --| DESCRIPTION : generates the PL/SQL packages from the Product Accounting |
1003 --| definition. |
1004 --| |
1005 --| |
1006 --| RETURNS |
1007 --| 1. l_IsCompiled : BOOLEAN, TRUE if Product accounting definition has |
1008 --| been successfully created, FALSE otherwise. |
1009 --| |
1010 --| |
1011 --| |
1012 --+==========================================================================+
1013
1014 FUNCTION Compile RETURN BOOLEAN IS
1015
1016 l_IsCompiled BOOLEAN := TRUE;
1017 l_log_module VARCHAR2(240);
1018 l_procedure_name varchar2(80) := 'Compile';
1019
1020 l_extract_type varchar2(15);
1021
1022 BEGIN
1023
1024 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1025 fnd_log.string(G_LEVEL_PROCEDURE,
1026 G_MODULE_NAME||l_procedure_name||'.begin',
1027 'Beginning of procedure');
1028 END IF;
1029
1030 g_UserName := GetUserName;
1031
1032
1033 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1034 fnd_log.string(G_LEVEL_STATEMENT,
1035 G_MODULE_NAME||l_procedure_name,
1036 'entering loop');
1037 END IF;
1038
1039 -- Loop three times to process each package: deprn/trx/deferred
1040
1041 for i in 1..3 loop
1042
1043 -- no need for this anymore as we are not segregating by AAD
1044 -- g_PackageName :=
1045 -- fa_xla_cmp_hash_pkg.BuildPackageName
1046 -- (p_extract_type => l_extract_type);
1047
1048
1049 if (i=1) then
1050 l_extract_type := 'DEPRN';
1051 g_PackageName := 'FA_XLA_EXTRACT_DEPRN_PKG';
1052 elsif (i=2) then
1053 l_extract_type := 'TRX';
1054 g_PackageName := 'FA_XLA_EXTRACT_TRX_PKG';
1055 else
1056 l_extract_type := 'DEF';
1057 g_PackageName := 'FA_XLA_EXTRACT_DEF_PKG';
1058 end if;
1059
1060 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1061 fnd_log.string(G_LEVEL_STATEMENT,
1062 G_MODULE_NAME||l_procedure_name,
1063 'l_extract_type = '||l_extract_type ||
1064 ' - g_PackageName = '||g_PackageName);
1065 END IF;
1066
1067 l_IsCompiled := l_IsCompiled and
1068 CreateSpecPackage
1069 (p_extract_type => l_extract_type);
1070
1071 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1072 fnd_log.string(G_LEVEL_STATEMENT,
1073 G_MODULE_NAME||l_procedure_name,
1074 ' FA specification package created = '||
1075 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1076 END IF;
1077
1078 l_IsCompiled :=
1079 l_IsCompiled AND
1080 CreateBodyPackage
1081 (p_extract_type => l_extract_type);
1082
1083 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1084 fnd_log.string(G_LEVEL_STATEMENT,
1085 G_MODULE_NAME||l_procedure_name,
1086 ' AAD body package created = '||
1087 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1088 END IF;
1089
1090 -- BMR - need something here? was calling xla_amb_setup_err_pkg.stack_error
1091
1092
1093 END LOOP;
1094
1095 COMMIT;
1096
1097 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1098 fnd_log.string(G_LEVEL_STATEMENT,
1099 G_MODULE_NAME||l_procedure_name,
1100 'return value. = '||
1101 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1102 END IF;
1103
1104 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1105 fnd_log.string(G_LEVEL_PROCEDURE,
1106 G_MODULE_NAME||l_procedure_name||'.end',
1107 'End of procedure');
1108 END IF;
1109
1110 RETURN l_IsCompiled;
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1115 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1116 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1117 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1118 END IF;
1119 RETURN FALSE;
1120
1121 END Compile;
1122
1123 --=============================================================================
1124
1125 END fa_xla_cmp_extract_pkg; -- end of package spec