[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.1 2006/03/08 22:46:23 bridgway noship $ */
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_trx_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
347 , p_extract_type IN VARCHAR2)
348 RETURN VARCHAR2 IS
349
350 l_SpecPkg VARCHAR2(32000);
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
486 , p_package => l_Package
487 );
488
489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
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
627
628 --
629 -- generate mls section
630 --
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 :=
782 GeneratePrivateProcedures
783 (p_package_name => p_package_name
784 ,p_extract_type => p_extract_type
785 ,p_package_body => p_package_body);
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
858 l_BodyPkg :=
859 REPLACE(l_BodyPkg,'$header$'
860 ,GeneratePkgComment
861 (p_user_name => g_UserName
862 ,p_package_name => g_PackageName
863 ));
864
865 l_BodyPkg := REPLACE(l_BodyPkg,'$PACKAGE_NAME$' ,g_PackageName);
866
867 fa_cmp_string_pkg.CreateString
868 (p_package_text => l_BodyPkg
869 ,p_array_string => l_array_pkg);
870
871 l_IsCompiled :=
872 GenerateBody
873 (p_package_name => g_PackageName
874 ,p_extract_type => p_extract_type
875 ,p_package_body => l_array_body);
876
877
878 l_array_pkg :=
879 fa_cmp_string_pkg.ConcatTwoStrings
880 (p_array_string_1 => l_array_pkg
881 ,p_array_string_2 => l_array_body);
882
883 fa_cmp_string_pkg.CreateString
884 (p_package_text => l_BodyPkg
885 ,p_array_string => l_array_body);
886
887 l_BodyPkg := C_PACKAGE_BODY_CLOSE ;
888 l_BodyPkg := REPLACE(l_BodyPkg,'$PACKAGE_NAME$' ,g_PackageName);
889
890 fa_cmp_string_pkg.CreateString
891 (p_package_text => l_BodyPkg
892 ,p_array_string => l_array_body);
893
894 l_array_pkg :=
895 fa_cmp_string_pkg.ConcatTwoStrings
896 (p_array_string_1 => l_array_pkg
897 ,p_array_string_2 => l_array_body);
898
899 p_package_body := l_array_pkg;
900
901 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
902 fnd_log.string(G_LEVEL_STATEMENT,
903 G_MODULE_NAME||l_procedure_name,
904 'return value (l_IsCompiled) = '||
905 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
906 END IF;
907
908 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
909 fnd_log.string(G_LEVEL_PROCEDURE,
910 G_MODULE_NAME||l_procedure_name||'.end',
911 'End of procedure');
912 END IF;
913
914 RETURN l_IsCompiled;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
919 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
920 fnd_message.set_token('ORACLE_ERR',SQLERRM);
921 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
922 END IF;
923 RETURN FALSE;
924
925 END GenerateBodyPackage;
926
927 /*--------------------------------------------------------------+
928 | |
929 | Private function |
930 | |
931 | CreateBodyPackage |
932 | |
933 | Compiles the FA body packages in the DATABASE |
934 | Returns TRUE if the package body is VALID, FALSE otherwise. |
935 | |
936 +--------------------------------------------------------------*/
937 FUNCTION CreateBodyPackage
938 (p_extract_type IN VARCHAR2)
939 RETURN BOOLEAN IS
940
941 l_Package DBMS_SQL.VARCHAR2S;
942 l_PackageName VARCHAR2(30);
943 l_ProductRuleName VARCHAR2(80);
944 l_ProductRuleVersion VARCHAR2(30);
945
946 l_IsCompiled BOOLEAN;
947 l_procedure_name varchar2(80) := 'CreateBodyPackage';
948
949 BEGIN
950
951 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
952 fnd_log.string(G_LEVEL_PROCEDURE,
953 G_MODULE_NAME||l_procedure_name||'.begin',
954 'Beginning of procedure');
955 END IF;
956
957
958
959 l_IsCompiled :=
960 GenerateBodyPackage
961 (p_extract_type => p_extract_type
962 ,p_package_body => l_Package);
963
964 l_IsCompiled :=
965 fa_xla_cmp_create_pkg.CreateBodyPackage
966 (p_package_name => g_PackageName
967 ,p_package_text => l_Package) AND l_IsCompiled;
968
969 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
970 fnd_log.string(G_LEVEL_STATEMENT,
971 G_MODULE_NAME||l_procedure_name,
972 'return value (l_IsCompiled) = '||
973 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
974 END IF;
975
976 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
977 fnd_log.string(G_LEVEL_PROCEDURE,
978 G_MODULE_NAME||l_procedure_name||'.end',
979 'End of procedure');
980 END IF;
981
982 RETURN l_IsCompiled;
983
984 EXCEPTION
985 WHEN OTHERS THEN
986 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
987 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
988 fnd_message.set_token('ORACLE_ERR',SQLERRM);
989 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
990 END IF;
991
992 return false;
993
994 END CreateBodyPackage;
995
996
997 --+==========================================================================+
998 --| PUBLIC function |
999 --| Compile |
1000 --| |
1001 --| DESCRIPTION : generates the PL/SQL packages from the Product Accounting |
1002 --| definition. |
1003 --| |
1004 --| |
1005 --| RETURNS |
1006 --| 1. l_IsCompiled : BOOLEAN, TRUE if Product accounting definition has |
1007 --| been successfully created, FALSE otherwise. |
1008 --| |
1009 --| |
1010 --| |
1011 --+==========================================================================+
1012
1013 FUNCTION Compile RETURN BOOLEAN IS
1014
1015 l_IsCompiled BOOLEAN := TRUE;
1016 l_log_module VARCHAR2(240);
1017 l_procedure_name varchar2(80) := 'Compile';
1018
1019 l_extract_type varchar2(15);
1020
1021 BEGIN
1022
1023 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1024 fnd_log.string(G_LEVEL_PROCEDURE,
1025 G_MODULE_NAME||l_procedure_name||'.begin',
1026 'Beginning of procedure');
1027 END IF;
1028
1029 g_UserName := GetUserName;
1030
1031
1032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1033 fnd_log.string(G_LEVEL_STATEMENT,
1034 G_MODULE_NAME||l_procedure_name,
1035 'entering loop');
1036 END IF;
1037
1038 -- Loop three times to process each package: deprn/trx/deferred
1039
1040 for i in 1..3 loop
1041
1042 -- no need for this anymore as we are not segregating by AAD
1043 -- g_PackageName :=
1044 -- fa_xla_cmp_hash_pkg.BuildPackageName
1045 -- (p_extract_type => l_extract_type);
1046
1047
1048 if (i=1) then
1049 l_extract_type := 'DEPRN';
1050 g_PackageName := 'FA_XLA_EXTRACT_DEPRN_PKG';
1051 elsif (i=2) then
1052 l_extract_type := 'TRX';
1053 g_PackageName := 'FA_XLA_EXTRACT_TRX_PKG';
1054 else
1055 l_extract_type := 'DEF';
1056 g_PackageName := 'FA_XLA_EXTRACT_DEF_PKG';
1057 end if;
1058
1059 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1060 fnd_log.string(G_LEVEL_STATEMENT,
1061 G_MODULE_NAME||l_procedure_name,
1062 'l_extract_type = '||l_extract_type ||
1063 ' - g_PackageName = '||g_PackageName);
1064 END IF;
1065
1066 l_IsCompiled := l_IsCompiled and
1067 CreateSpecPackage
1068 (p_extract_type => l_extract_type);
1069
1070 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1071 fnd_log.string(G_LEVEL_STATEMENT,
1072 G_MODULE_NAME||l_procedure_name,
1073 ' FA specification package created = '||
1074 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1075 END IF;
1076
1077 l_IsCompiled :=
1078 l_IsCompiled AND
1079 CreateBodyPackage
1080 (p_extract_type => l_extract_type);
1081
1082 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1083 fnd_log.string(G_LEVEL_STATEMENT,
1084 G_MODULE_NAME||l_procedure_name,
1085 ' AAD body package created = '||
1086 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1087 END IF;
1088
1089 -- BMR - need something here? was calling xla_amb_setup_err_pkg.stack_error
1090
1091
1092 END LOOP;
1093
1094 COMMIT;
1095
1096 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1097 fnd_log.string(G_LEVEL_STATEMENT,
1098 G_MODULE_NAME||l_procedure_name,
1099 'return value. = '||
1100 CASE l_IsCompiled WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END);
1101 END IF;
1102
1103 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1104 fnd_log.string(G_LEVEL_PROCEDURE,
1105 G_MODULE_NAME||l_procedure_name||'.end',
1106 'End of procedure');
1107 END IF;
1108
1109 RETURN l_IsCompiled;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
1114 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
1115 fnd_message.set_token('ORACLE_ERR',SQLERRM);
1116 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
1117 END IF;
1118 RETURN FALSE;
1119
1120 END Compile;
1121
1122 --=============================================================================
1123
1124 END fa_xla_cmp_extract_pkg; -- end of package spec