[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_CREATE_PKG
Source
1 PACKAGE BODY fa_xla_cmp_create_pkg AS
2 /* $Header: faxlaccb.pls 120.0 2006/03/06 22:14:18 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_create_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for creation packages (spec and body) in the database |
14 | |
15 | |
16 | HISTORY |
17 | 25-FEB-2006 BRIDGWAY Created |
18 | |
19 +===========================================================================*/
20
21
22 --+==========================================================================+
23 --| |
24 --| Private global constants |
25 --| |
26 --+==========================================================================+
27
28 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
29 C_CREATED CONSTANT BOOLEAN := TRUE;
30
31 g_Max_line CONSTANT NUMBER := 225;
32 g_chr_quote CONSTANT VARCHAR2(10):='''';
33 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
34
35 g_log_level_rec fa_api_types.log_level_rec_type;
36
37 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
38
39 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
40 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
41 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
42 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
43 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
44 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
45
46 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_create_pkg.';
47
48
49 --+==========================================================================+
50 --| |
51 --| PRIVATE procedures and functions |
52 --| |
53 --+==========================================================================+
54
55 PROCEDURE dump_package( p_package_name IN VARCHAR2,
56 p_package_type IN VARCHAR2)
57 IS
58
59 CURSOR text_cur ( p_package_name VARCHAR2
60 ,p_package_type VARCHAR2) IS
61 SELECT us.text
62 , us.line
63 FROM user_source us
64 WHERE us.name = UPPER(p_package_name)
65 AND us.type = UPPER(p_package_type)
66 ORDER BY line;
67
68 l_first BOOLEAN:= TRUE;
69 l_procedure_name varchar2(80) := 'dump_package';
70
71 BEGIN
72
73 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
74 fnd_log.string(G_LEVEL_PROCEDURE,
75 G_MODULE_NAME||l_procedure_name||'.begin',
76 'Beginning of procedure');
77 END IF;
78
79 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
80
81 FOR text_rec IN text_cur(p_package_name, p_package_type) LOOP
82
83 IF l_first THEN
84
85 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
86 fnd_log.string(G_LEVEL_STATEMENT,
87 G_MODULE_NAME||l_procedure_name,
88 '>>> DUMP '|| p_package_type ||' = '||p_package_name);
89 END IF;
90
91 l_first := FALSE;
92
93 END IF;
94
95 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
96 fnd_log.string(G_LEVEL_STATEMENT,
97 G_MODULE_NAME||l_procedure_name,
98 RPAD(text_rec.line ,10,' ') ||' '||text_rec.text);
99 END IF;
100
101 END LOOP;
102 END IF;
103
104 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
105 fnd_log.string(G_LEVEL_PROCEDURE,
106 G_MODULE_NAME||l_procedure_name||'.end',
107 'End of procedure');
108 END IF;
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
113 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
114 fnd_message.set_token('ORACLE_ERR',SQLERRM);
115 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
116 END IF;
117 raise;
118
119 END dump_package;
120
121
122 --+==========================================================================+
123 --| |
124 --| PRIVATE procedures and functions |
125 --| |
126 --+==========================================================================+
127
128 PROCEDURE get_pkg_errors(p_package_name IN VARCHAR2,
129 p_package_type IN VARCHAR2) IS
130
131 CURSOR error_cur ( p_package_name VARCHAR2
132 ,p_package_type VARCHAR2) IS
133 SELECT SUBSTR(ue.text,1,2000) error
134 , ue.line
135 FROM user_errors ue
136 WHERE ue.name = UPPER(p_package_name)
137 AND ue.type = UPPER(p_package_type)
138 ORDER BY line;
139
140 l_first BOOLEAN:= TRUE;
141 l_procedure_name varchar2(80) := 'get_pkg_errors';
142
143 BEGIN
144
145 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
146 fnd_log.string(G_LEVEL_PROCEDURE,
147 G_MODULE_NAME||l_procedure_name||'.begin',
148 'Beginning of procedure');
149 END IF;
150
151
152 FOR error_rec IN error_cur(p_package_name, p_package_type) LOOP
153
154 IF l_first THEN
155
156 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
157
158 fnd_log.string(G_LEVEL_STATEMENT,
159 G_MODULE_NAME||l_procedure_name,
160 '----- COMPILATION FAILS ------');
161
162 fnd_log.string(G_LEVEL_STATEMENT,
163 G_MODULE_NAME||l_procedure_name,
164 'Additional information = ');
165
166 fnd_log.string(G_LEVEL_STATEMENT,
167 G_MODULE_NAME||l_procedure_name,
168 '------------------------------');
169
170 fnd_log.string(G_LEVEL_STATEMENT,
171 G_MODULE_NAME||l_procedure_name,
172 'Package name = '||p_package_name);
173
174 fnd_log.string(G_LEVEL_STATEMENT,
175 G_MODULE_NAME||l_procedure_name,
176 RPAD('LINE',10,' ') ||'|' ||' ERROR ');
177
178 fnd_log.string(G_LEVEL_STATEMENT,
179 G_MODULE_NAME||l_procedure_name,
180 LPAD('-',10,'-') ||'|' ||LPAD('-',50,'-'));
181
182 END IF;
183
184 l_first := FALSE;
185
186 END IF;
187
188
189 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
190 fnd_log.string(G_LEVEL_STATEMENT,
191 G_MODULE_NAME||l_procedure_name,
192 RPAD(error_rec.line,10,' ') ||'| '||error_rec.error);
193 END IF;
194
195 END LOOP;
196
197 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
198 fnd_log.string(G_LEVEL_PROCEDURE,
199 G_MODULE_NAME||l_procedure_name||'.end',
200 'End of procedure');
201 END IF;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
206 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
207 fnd_message.set_token('ORACLE_ERR',SQLERRM);
208 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
209 END IF;
210 raise;
211
212 END get_pkg_errors;
213
214
215 --+==========================================================================+
216 --| |
217 --| PRIVATE procedures and functions |
218 --| |
219 --+==========================================================================+
220
221 FUNCTION GetPackageStatus( p_package_name IN VARCHAR2,
222 p_package_type IN VARCHAR2)
223 RETURN BOOLEAN IS
224
225 l_IsValid BOOLEAN;
226 l_status VARCHAR2(10);
227 l_procedure_name varchar2(80) := 'GetPackageStatus';
228
229 BEGIN
230
231 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
232 fnd_log.string(G_LEVEL_PROCEDURE,
233 G_MODULE_NAME||l_procedure_name||'.begin',
234 'Beginning of procedure');
235 END IF;
236
237
238 BEGIN
239 SELECT uo.status
240 INTO l_status
241 FROM user_objects uo
242 WHERE uo.object_name = UPPER(p_package_name)
243 AND uo.object_type = UPPER(p_package_type);
244
245 l_IsValid := (l_status = 'VALID');
246
247 EXCEPTION
248 WHEN NO_DATA_FOUND THEN
249 l_IsValid := FALSE;
250 END;
251
252 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
253 fnd_log.string(G_LEVEL_PROCEDURE,
254 G_MODULE_NAME||l_procedure_name||'.end',
255 'End of procedure');
256 END IF;
257
258 RETURN l_IsValid;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
263 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
264 fnd_message.set_token('ORACLE_ERR',SQLERRM);
265 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
266 END IF;
267 RETURN FALSE;
268
269 END GetPackageStatus;
270
271 --+==========================================================================+
272 --| |
273 --| PRIVATE procedures and functions |
274 --| |
275 --+==========================================================================+
276
277 FUNCTION CreateBodyPackage (p_package_name IN VARCHAR2,
278 p_package_type IN VARCHAR2,
279 p_package_text IN DBMS_SQL.VARCHAR2S
280 )
281 RETURN BOOLEAN IS
282
283 l_lb INTEGER ;
284 l_ub INTEGER ;
285 compilation_fails EXCEPTION;
286 package_locked EXCEPTION;
287 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
288 PRAGMA EXCEPTION_INIT (package_locked ,-04021);
289
290 l_procedure_name varchar2(80) := 'CreateBodyPackage';
291
292 BEGIN
293
294 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
295 fnd_log.string(G_LEVEL_PROCEDURE,
296 G_MODULE_NAME||l_procedure_name||'.begin',
297 'Beginning of procedure');
298 END IF;
299
300 -- Init
301 l_lb := NVL(p_package_text.FIRST,0);
302 l_ub := NVL(p_package_text.LAST ,0);
303 APPS_ARRAY_DDL.glprogtext := p_package_text;
304
305 --
306 -- call apps_array_ddl API to create body package
307 --
308
309
310 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
311 fnd_log.string(G_LEVEL_STATEMENT,
312 G_MODULE_NAME||l_procedure_name,
313 '-> CALL APPS_ARRAY_DDL.apps_array_ddl API');
314 END IF;
315
316 APPS_ARRAY_DDL.apps_array_ddl(
317 lb => l_lb,
318 ub => l_ub
319 );
320
321 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
322 fnd_log.string(G_LEVEL_PROCEDURE,
323 G_MODULE_NAME||l_procedure_name||'.end',
324 'End of procedure');
325 END IF;
326
327 RETURN TRUE;
328
329 EXCEPTION
330 WHEN package_locked THEN
331
332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
333 fnd_log.string(G_LEVEL_STATEMENT,
334 G_MODULE_NAME||l_procedure_name,
335 'ERROR: Package Locked');
336 END IF;
337
338 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL ) THEN
339 fnd_message.set_name('XLA','XLA_CMP_PACKAGE_LOCKED');
340 fnd_message.set_token('PACKAGE_NAME',p_package_name);
341 FND_LOG.MESSAGE (G_LEVEL_ERROR,G_MODULE_NAME||l_procedure_name,TRUE);
342 END IF;
343
344 RETURN FALSE;
345
346 WHEN compilation_fails THEN
347
348 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
349 fnd_log.string(G_LEVEL_STATEMENT,
350 G_MODULE_NAME||l_procedure_name,
351 'ERROR: Package Compilation Failed');
352 END IF;
353
354
355 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL ) THEN
356 fnd_message.set_name('XLA','XLA_CMP_COMPILATION_FAILED');
357 fnd_message.set_token('PACKAGE_NAME',p_package_name);
358 FND_LOG.MESSAGE (G_LEVEL_ERROR,G_MODULE_NAME||l_procedure_name,TRUE);
359 END IF;
360
361 RETURN FALSE;
362
363 WHEN OTHERS THEN
364 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
365 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
366 fnd_message.set_token('ORACLE_ERR',SQLERRM);
367 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
368 END IF;
369
370 RETURN FALSE;
371
372 END CreateBodyPackage;
373
374
375 --+==========================================================================+
376 --| |
377 --| PRIVATE procedures and functions |
378 --| |
379 --+==========================================================================+
380
381 FUNCTION CreateSpecPackage (p_package_name IN VARCHAR2,
382 p_package_type IN VARCHAR2,
383 p_package_text IN VARCHAR2
384 )
385 RETURN BOOLEAN IS
386 compilation_fails EXCEPTION;
387 package_locked EXCEPTION;
388 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
389 PRAGMA EXCEPTION_INIT(package_locked ,-04021);
390 l_procedure_name varchar2(80) := 'CreateSpecPackage';
391
392 BEGIN
393
394 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
395 fnd_log.string(G_LEVEL_PROCEDURE,
396 G_MODULE_NAME||l_procedure_name||'.begin',
397 'Beginning of procedure');
398 END IF;
399
400
401 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
402 fnd_log.string(G_LEVEL_STATEMENT,
403 G_MODULE_NAME||l_procedure_name,
404 'p_packgae_text = '||p_package_text);
405 END IF;
406
407 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
408 fnd_log.string(G_LEVEL_STATEMENT,
409 G_MODULE_NAME||l_procedure_name,
410 '-> CALL APPS_DDL.apps_ddl API');
411 END IF;
412
413 APPS_DDL.apps_ddl(ddl_text => p_package_text);
414
415 /* this is already handled above and is easier to read:
416
417 dump_package( p_package_name => p_package_name
418 , p_package_type => p_package_type);
419
420 */
421
422 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
423 fnd_log.string(G_LEVEL_PROCEDURE,
424 G_MODULE_NAME||l_procedure_name||'.end',
425 'End of procedure');
426 END IF;
427
428 RETURN TRUE;
429
430 EXCEPTION
431
432 WHEN package_locked THEN
433 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
434 fnd_log.string(G_LEVEL_STATEMENT,
435 G_MODULE_NAME||l_procedure_name,
436 'ERROR: XLA_CMP_PACKAGE_LOCKED');
437 END IF;
438
439 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL ) THEN
440 fnd_message.set_name('XLA','XLA_CMP_PACKAGE_LOCKED');
441 fnd_message.set_token('PACKAGE_NAME',p_package_name);
442 FND_LOG.MESSAGE (G_LEVEL_ERROR,G_MODULE_NAME||l_procedure_name,TRUE);
443 END IF;
444
445 RETURN FALSE;
446
447 WHEN compilation_fails THEN
448
449 get_pkg_errors( p_package_name => p_package_name
450 , p_package_type => p_package_type);
451 dump_package( p_package_name => p_package_name
452 , p_package_type => p_package_type
453 );
454
455 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
456 fnd_log.string(G_LEVEL_STATEMENT,
457 G_MODULE_NAME||l_procedure_name,
458 'ERROR: XLA_CMP_COMPILATION_FAILED');
459 END IF;
460
461 IF (G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL ) THEN
462 fnd_message.set_name('XLA','XLA_CMP_COMPILATION_FAILED');
463 fnd_message.set_token('PACKAGE_NAME',p_package_name);
464 FND_LOG.MESSAGE (G_LEVEL_ERROR,G_MODULE_NAME||l_procedure_name,TRUE);
465 END IF;
466
467 RETURN FALSE;
468
469
470 WHEN OTHERS THEN
471 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
472 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
473 fnd_message.set_token('ORACLE_ERR',SQLERRM);
474 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
475 END IF;
476
477 return false;
478
479 END CreateSpecPackage;
480
481 --+==========================================================================+
482 --| |
483 --| PUBLIC procedures and functions |
484 --| |
485 --+==========================================================================+
486
487 --+==========================================================================+
488 --| PUBLIC FUNCTION |
489 --| create_package |
490 --| |
491 --| DESCRIPTION |
492 --| function called with package_text not null |
493 --+==========================================================================+
494
495 FUNCTION CreateSpecPackage( p_package_name IN VARCHAR2,
496 p_package_text IN VARCHAR2
497 )
498 RETURN BOOLEAN IS
499
500 l_package_text VARCHAR2(32000);
501 l_created_flag BOOLEAN := FALSE;
502 l_procedure_name varchar2(80) := 'CreateSpecPackage';
503
504 BEGIN
505
506 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
507 fnd_log.string(G_LEVEL_PROCEDURE,
508 G_MODULE_NAME||l_procedure_name||'.begin',
509 'Beginning of procedure');
510 END IF;
511
512
513 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
514 fnd_log.string(G_LEVEL_STATEMENT,
515 G_MODULE_NAME||l_procedure_name,
516 'p_package_name = '||p_package_name);
517
518 END IF;
519
520 l_package_text := p_package_text;
521
522 fa_cmp_string_pkg.truncate_lines( l_package_text);
523
524 l_created_flag := CreateSpecPackage(
525 p_package_name => p_package_name
526 , p_package_type => C_SPECIFICATION
527 , p_package_text => l_package_text
528 );
529
530 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
531 fnd_log.string(G_LEVEL_PROCEDURE,
532 G_MODULE_NAME||l_procedure_name||'.end',
533 'End of procedure');
534 END IF;
535
536 RETURN l_created_flag;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
541 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
542 fnd_message.set_token('ORACLE_ERR',SQLERRM);
543 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
544 END IF;
545
546 return false;
547
548 END CreateSpecPackage;
549
550 --+==========================================================================+
551 --| PUBLIC FUNCTION |
552 --| create_package |
553 --| |
554 --| DESCRIPTION |
555 --| function called with package_text not null |
556 --+==========================================================================+
557
558 FUNCTION CreateBodyPackage( p_package_name IN VARCHAR2,
559 p_package_text IN DBMS_SQL.VARCHAR2S
560 )
561 RETURN BOOLEAN IS
562
563 l_package_text DBMS_SQL.VARCHAR2S;
564 l_created_flag BOOLEAN := FALSE;
565 l_IsValid BOOLEAN := TRUE;
566 l_procedure_name varchar2(80) := 'CreateBodyPackage';
567
568 BEGIN
569
570 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
571 fnd_log.string(G_LEVEL_PROCEDURE,
572 G_MODULE_NAME||l_procedure_name||'.begin',
573 'Beginning of procedure');
574 END IF;
575
576 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
577
578 fnd_log.string(G_LEVEL_STATEMENT,
579 G_MODULE_NAME||l_procedure_name,
580 'p_package_name = '||p_package_name);
581
582 END IF;
583
584 l_created_flag := CreateBodyPackage(p_package_name => p_package_name
585 , p_package_type => C_BODY
586 , p_package_text => p_package_text);
587
588 l_IsValid := GetPackageStatus( p_package_name => p_package_name
589 , p_package_type => C_BODY);
590
591
592 /* this is already handled above and is easier to read:
593
594 dump_package(p_package_name => p_package_name
595 , p_package_type => C_BODY
596 );
597 */
598
599 IF NOT l_IsValid THEN
600 get_pkg_errors( p_package_name => p_package_name
601 , p_package_type => C_BODY);
602 END IF;
603
604 l_created_flag := l_created_flag AND l_IsValid;
605
606 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
607 IF l_created_flag THEN
608 fnd_log.string(G_LEVEL_STATEMENT,
609 G_MODULE_NAME||l_procedure_name,
610 'return value. = TRUE');
611 ELSE
612 fnd_log.string(G_LEVEL_STATEMENT,
613 G_MODULE_NAME||l_procedure_name,
614 'return value. = FALSE');
615 END IF;
616 END IF;
617
618 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
619 fnd_log.string(G_LEVEL_PROCEDURE,
620 G_MODULE_NAME||l_procedure_name||'.end',
621 'End of procedure');
622 END IF;
623
624 RETURN l_created_flag;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
629 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
630 fnd_message.set_token('ORACLE_ERR',SQLERRM);
631 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
632 END IF;
633
634 return false;
635
636 END CreateBodyPackage;
637
638 --Additions for the Transaction Account Builder
639
640 FUNCTION execute_ddl
641 (
642 p_ddl_text IN VARCHAR2,
643 p_object_owner IN VARCHAR2
644 )
645 RETURN BOOLEAN IS
646 compilation_fails EXCEPTION;
647 package_locked EXCEPTION;
648 l_sql_stmt VARCHAR2(2000);
649 l_procedure_name varchar2(80) := 'execute_ddl';
650
651 PRAGMA EXCEPTION_INIT (compilation_fails , -24344);
652 PRAGMA EXCEPTION_INIT(package_locked , -04021);
653
654 BEGIN
655
656 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
657 fnd_log.string(G_LEVEL_PROCEDURE,
658 G_MODULE_NAME||l_procedure_name||'.begin',
659 'Beginning of procedure');
660 END IF;
661
662 IF p_object_owner IS NULL THEN
663 APPS_DDL.apps_ddl( ddl_text => p_ddl_text );
664 ELSE
665 l_sql_stmt := 'BEGIN ' || p_object_owner || '.APPS_DDL.apps_ddl( ddl_text => ''' ||
666 p_ddl_text || ''' );' || ' END;';
667
668 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
669 fnd_log.string(G_LEVEL_STATEMENT,
670 G_MODULE_NAME||l_procedure_name,
671 l_sql_stmt);
672 END IF;
673
674 EXECUTE IMMEDIATE l_sql_stmt;
675
676 END IF;
677
678 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
679 fnd_log.string(G_LEVEL_PROCEDURE,
680 G_MODULE_NAME||l_procedure_name||'.end',
681 'End of procedure');
682 END IF;
683
684 RETURN TRUE;
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
689 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
690 fnd_message.set_token('ORACLE_ERR',SQLERRM);
691 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
692 END IF;
693
694 return false;
695
696 END execute_ddl;
697
698
699 FUNCTION execute_ddl
700 (
701 p_ddl_text IN DBMS_SQL.VARCHAR2S
702 )
703 RETURN BOOLEAN IS
704
705 l_lb INTEGER ;
706 l_ub INTEGER ;
707 l_return_value BOOLEAN;
708 compilation_fails EXCEPTION;
709 package_locked EXCEPTION;
710 l_procedure_name varchar2(80) := 'execute_ddl';
711 l_fatal_error_message_text VARCHAR2(100);
712 PRAGMA EXCEPTION_INIT (package_locked ,-04021);
713
714 BEGIN
715
716 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
717 fnd_log.string(G_LEVEL_PROCEDURE,
718 G_MODULE_NAME||l_procedure_name||'.begin',
719 'Beginning of procedure');
720 END IF;
721
722 l_return_value := TRUE;
723
724 l_lb := NVL(p_ddl_text.FIRST,0);
725 l_ub := NVL(p_ddl_text.LAST ,0);
726
727
728 APPS_ARRAY_DDL.glprogtext := p_ddl_text;
729 APPS_ARRAY_DDL.apps_array_ddl
730 (
731 lb => l_lb
732 ,ub => l_ub
733 );
734
735 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
736 fnd_log.string(G_LEVEL_PROCEDURE,
737 G_MODULE_NAME||l_procedure_name||'.end',
738 'End of procedure');
739 END IF;
740
741 RETURN l_return_value;
742
743 EXCEPTION
744
745 WHEN compilation_fails THEN
746
747 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
748 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
749 fnd_message.set_token('ORACLE_ERR',SQLERRM);
750 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
751 END IF;
752
753 RETURN FALSE;
754
755 WHEN package_locked THEN
756
757 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
758 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
759 fnd_message.set_token('ORACLE_ERR',SQLERRM);
760 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
761 END IF;
762
763 RETURN FALSE;
764
765 WHEN OTHERS THEN
766
767 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
768 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
769 fnd_message.set_token('ORACLE_ERR',SQLERRM);
770 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
771 END IF;
772
773 RETURN FALSE;
774
775 --BMR : do we need routine like xla_cmp_common_pkg.dump_text here????
776
777 END execute_ddl;
778
779 FUNCTION execute_dml
780 (
781 p_dml_text IN CLOB
782 )
783 RETURN BOOLEAN IS
784
785 l_lb INTEGER ;
786 l_ub INTEGER ;
787 l_return_value BOOLEAN;
788 compilation_fails EXCEPTION;
789 package_locked EXCEPTION;
790 l_dml_text DBMS_SQL.VARCHAR2S;
791 l_procedure_name varchar2(80) := 'execute_dml';
792 l_fatal_error_message_text VARCHAR2(100);
793
794 BEGIN
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||'.begin',
799 'Beginning of procedure');
800 END IF;
801
802 l_return_value := TRUE;
803
804 --Move the CLOB into a VARCHAR2S
805 fa_cmp_string_pkg.clob_to_varchar2s
806 (
807 p_clob => p_dml_text
808 ,p_varchar2s => l_dml_text
809 );
810
811
812 l_lb := NVL(l_dml_text.FIRST,0);
813 l_ub := NVL(l_dml_text.LAST ,0);
814
815
816 APPS_ARRAY_DDL.glprogtext := l_dml_text;
817 APPS_ARRAY_DDL.apps_array_ddl
818 (
819 lb => l_lb
820 ,ub => l_ub
821 );
822
823 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
824 fnd_log.string(G_LEVEL_PROCEDURE,
825 G_MODULE_NAME||l_procedure_name||'.end',
826 'End of procedure');
827 END IF;
828
829 RETURN TRUE;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
834 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
835 fnd_message.set_token('ORACLE_ERR',SQLERRM);
836 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
837 END IF;
838
839 return false;
840
841
842 END execute_dml;
843
844 FUNCTION push_database_object
845 (
846 p_object_name IN VARCHAR2,
847 p_object_type IN VARCHAR2,
848 p_object_owner IN VARCHAR2,
849 p_apps_account IN VARCHAR2,
850 p_ddl_text IN CLOB
851 )
852 RETURN BOOLEAN IS
853
854 l_return_value BOOLEAN;
855 l_cur_position INTEGER;
856 l_next_cr_position INTEGER;
857 l_text_length INTEGER;
858 l_additional_sql_stmt VARCHAR2(2000);
859 l_ddl_text DBMS_SQL.VARCHAR2S;
860 l_procedure_name varchar2(80) := 'push_database_object';
861
862 le_table_not_exists EXCEPTION;
863 le_compilation_fails EXCEPTION;
864 PRAGMA EXCEPTION_INIT (le_table_not_exists ,-00942);
865 PRAGMA EXCEPTION_INIT (le_compilation_fails , -24344);
866
867 BEGIN
868
869 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
870 fnd_log.string(G_LEVEL_PROCEDURE,
871 G_MODULE_NAME||l_procedure_name||'.begin',
872 'Beginning of procedure');
873 END IF;
874
875 l_return_value := TRUE;
876
877
878 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
879 fnd_log.string(G_LEVEL_STATEMENT,
880 G_MODULE_NAME||l_procedure_name,
881 'DDL text size: ' || LENGTH(p_ddl_text));
882 END IF;
883
884 --If the object to push is a table try to drop in case it exists
885 IF p_object_type = 'TABLE' THEN
886
887 --drop the already existing one (if it exists)
888 l_additional_sql_stmt :=
889 'DROP TABLE ' || p_object_owner || '.' || p_object_name;
890 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
891 fnd_log.string(G_LEVEL_STATEMENT,
892 G_MODULE_NAME||l_procedure_name,
893 l_additional_sql_stmt);
894 END IF;
895
896 BEGIN
897 EXECUTE IMMEDIATE l_additional_sql_stmt;
898 EXCEPTION
899 WHEN le_table_not_exists THEN
900 NULL;
901 WHEN OTHERS THEN
902 RAISE;
903 END;
904
905 END IF;
906
907 --Move the CLOB into a VARCHAR2S
908 fa_cmp_string_pkg.clob_to_varchar2s
909 (
910 p_clob => p_ddl_text
911 ,p_varchar2s => l_ddl_text
912 );
913
914 l_return_value := execute_ddl
915 (
916 p_ddl_text => l_ddl_text
917 );
918
919
920 --Post creation activities
921
922 --If the object to push is a table create synonym and grants
923 IF p_object_type = 'TABLE' THEN
924
925 --create the synonym
926 l_additional_sql_stmt :=
927 'CREATE OR REPLACE SYNONYM ' ||
928 p_apps_account || '.' || p_object_name ||
929 ' FOR ' || p_object_owner || '.' || p_object_name;
930
931 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
932 fnd_log.string(G_LEVEL_STATEMENT,
933 G_MODULE_NAME||l_procedure_name,
934 l_additional_sql_stmt);
935 END IF;
936
937 EXECUTE IMMEDIATE l_additional_sql_stmt;
938
939 --grant all to the apps account
940 l_return_value := execute_ddl
941 (
942 p_ddl_text => 'GRANT ALL ON ' || p_object_owner || '.' ||
943 p_object_name || ' TO ' ||
944 p_apps_account || ' WITH GRANT OPTION'
945 ,p_object_owner => p_object_owner
946 );
947
948 ELSIF p_object_type = 'PACKAGE BODY' THEN
949 l_additional_sql_stmt :=
950 'ALTER PACKAGE ' || p_object_name || ' COMPILE ';
951
952 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
953 fnd_log.string(G_LEVEL_STATEMENT,
954 G_MODULE_NAME||l_procedure_name,
955 l_additional_sql_stmt);
956 END IF;
957
958 EXECUTE IMMEDIATE l_additional_sql_stmt;
959 END IF;
960
961 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
962 fnd_log.string(G_LEVEL_PROCEDURE,
963 G_MODULE_NAME||l_procedure_name||'.end',
964 'End of procedure');
965 END IF;
966
967 RETURN l_return_value;
968
969 EXCEPTION
970 WHEN le_compilation_fails THEN
971 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
972 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
973 fnd_message.set_token('ORACLE_ERR',SQLERRM);
974 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
975 END IF;
976
977 -- BMR: need utility like xla_cmp_common_pkg.dump_text here
978
979 RETURN FALSE;
980
981 WHEN OTHERS THEN
982 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
983 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
984 fnd_message.set_token('ORACLE_ERR',SQLERRM);
985 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
986 END IF;
987
988 RETURN FALSE;
989
990 END push_database_object;
991
992 --=============================================================================
993
994 END fa_xla_cmp_create_pkg; -- end of package body