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