[Home] [Help]
PACKAGE BODY: APPS.XLA_CMP_TAD_PKG
Source
1 PACKAGE BODY xla_cmp_tad_pkg AS
2 /* $Header: xlacptad.pkb 120.25.12010000.3 2009/06/26 09:29:37 vkasina ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_cmp_tad_pkg |
10 | |
11 | DESCRIPTION |
12 | Transaction Account Builder Engine Compiler |
13 | |
14 | HISTORY |
15 | 26-JAN-04 A.Quaglia Created |
16 | 04-JUN-04 A.Quaglia Changed hash_id to NUMBER |
17 | ,row_id to base_rowid |
18 | ,ccid, target_ccid to NUMBER(15) |
19 | Removed amb_context param from |
20 | compile_application_tads_srs |
21 | compile_application_tads |
22 | 07-JUN-04 A.Quaglia Fixed GSCC warnings |
23 | Corrected typo with |
24 | C_TMPL_WHERE_SEGMENTS_EQUALS in upd stmts.|
25 | 14-JUN-04 A.Quaglia Completed compile_application_tads. |
26 | 16-JUN-04 A.Quaglia compile_tad: added check on missing tats. |
27 | 17-JUN-04 A.Quaglia compile_tad: added reset of msg stack |
28 | get_tad_package_name: added TO_CHAR |
29 | in trace statements. |
30 | build_code_combinations_dyn: fixed bug |
31 | for multiple TATs |
32 | Some params were still IN OUT instd of OUT|
33 | 21-JUN-04 A.Quaglia Change to return the concatenated segments|
34 | g_tab_api_package_name: |
35 | removed |
36 | get_flex_concat_segments (generated): |
37 | removed |
38 | build_static_ccid_prc_stmts: |
39 | modified to generate the concatenated |
40 | segments for all rows. |
41 | build_code_combinations_dyn (generated): |
42 | modified to generate the concatenated |
43 | segments for all rows. |
44 | C_TMPL_BATCH_BUILD_CCID_STMTS: |
45 | modified to generate the concatenated |
46 | segments for all rows. |
47 | pop_interface_data (generated): |
48 | modified to retrieve the concatenated |
49 | segments. |
50 | 23-JUN-04 A.Quaglia compile_tad: |
51 | all enabled tats for the application |
52 | must be compiled not only those |
53 | associated to the tad being compiled. |
54 | 28-JUL-04 A.Quaglia trans_account_def_online: |
55 | delete from global temp tables so that |
56 | successive calls from OAF are allowed |
57 | in case of errors and no rollback is |
58 | issued in-between. |
59 | C_TMPL_PUSH_INTERF_DATA_STMT: |
60 | introduced deletion of interface |
61 | global temp table as per explanation |
62 | above. |
63 | 28-JUL-04 A.Quaglia compile_tad: |
64 | moved call to init_global_variables |
65 | before messages are raised. |
66 | log_ccid_disabled_error (generated): |
67 | log_null_segments_error (generated): |
68 | log_ccid_not_found_error (generated): |
69 | added param p_account_type_code |
70 | and logic to derive the acc.type name |
71 | changed calls to these routines |
72 | changed various message tokens |
73 | 28-JUL-04 A.Quaglia C_TMPL_BATCH_BUILD_CCID_STMTS: |
74 | gt.processed_flag must be gtint. |
75 | in the statements that creates the new |
76 | ccids. |
77 | 05-JAN-05 K.Boussema Split up C_TMPL_TAD_PACKAGE_BODY_PART_1 |
78 | and C_TMPL_TAD_PACKAGE_BODY_PART_2. |
79 | 18-MAI-05 K.Boussema added the column dummy_rowid in GT tables |
80 | to fix bug 4344773 |
81 | 08-AUG-2006 Jorge Larre Bug 5368196 |
82 | Use FIRST instead of 0 and 1 in the index of the array in the |
83 | statements C_TMPL_PUSH_INTERF_DATA_STMT and |
84 | C_TMPL_POP_INTERF_DATA_STMT. |
85 | 23-AUG-2006 Jorge Larre Bug 5411930 |
86 | When calling FND_FLEX_EXT.get_ccid we need to convert the date |
87 | into the format accepted by the routine : 'YYYY/MM/DD HH24:MI:SS' |
88 +======================================================================*/
89
90 --
91 -- Private exceptions
92 --
93
94 ge_fatal_error EXCEPTION;
95
96 TYPE gt_rec_tad_details IS RECORD
97 (
98 application_id NUMBER
99 ,amb_context_code VARCHAR2(30)
100 ,account_type_code VARCHAR2(30)
101 ,flexfield_segment_code VARCHAR2(30)
102 ,segment_rule_type_code VARCHAR2(1)
103 ,segment_rule_code VARCHAR2(30)
104 ,object_name_affix VARCHAR2(10)
105 ,tat_compile_status_code VARCHAR2(1)
106 ,rule_assignment_code VARCHAR2(30)
107 ,adr_id NUMBER
108 );
109
110 TYPE gt_table_of_tad_details IS TABLE OF gt_rec_tad_details
111 INDEX BY BINARY_INTEGER;
112
113
114 --
115 -- Private constants
116 --
117
118 g_chr_newline CONSTANT VARCHAR2(1):= xla_environment_pkg.g_chr_newline;
119
120 G_STANDARD_MESSAGE CONSTANT VARCHAR2(1) := xla_exceptions_pkg.C_STANDARD_MESSAGE;
121
122
123
124 --+==========================================================================+
125 --| package specification template |
126 --+==========================================================================+
127
128 --
129 C_TMPL_TAD_PACKAGE_SPEC CONSTANT CLOB :=
130 'CREATE OR REPLACE PACKAGE $TAD_PACKAGE_NAME_1$ AS' ||
131 g_chr_newline||
132 '/'||'* $Header: generated on site by the Subledger Accounting Compiler*/' ||
133 g_chr_newline||
134 '/'|| '*======================================================================+
135 | Copyright (c) 2004 Oracle Corporation |
136 | Redwood Shores, CA, USA |
137 | All rights reserved. |
138 +=======================================================================+
139 | PACKAGE NAME |
140 | $TAD_PACKAGE_NAME_2$
141 | |
142 | DESCRIPTION |
143 | |
144 | Transaction Account Builder Engine Component |
145 | |
146 | Package generated by Oracle Subledger Accounting for |
147 | |
148 | $APPLICATION_NAME$
149 | (application_id: $APPLICATION_ID$
150 | |
151 | Corresponds to the following Transaction Account Definition |
152 | Code : $TAD_CODE$
153 | Type Code : $TAD_TYPE_CODE$
154 | AMB Context Code: $AMB_CONTEXT_CODE$
155 | |
156 | |
157 | |
158 | ATTENTION: |
159 | This package has been automatically generated by the |
160 | Oracle Subledger Accounting Compiler. You should not modify its |
161 | content manually. |
162 | This package has been generated according to the current setup |
163 | for the aforementioned Transaction Account Definition. |
164 | |
165 | In case of issues independent of the setup |
166 | please log a bug against Oracle Subledger Accounting. |
167 | |
168 | |
169 | HISTORY |
170 | $HISTORY$
171 | |
172 +=======================================================================*'
173 ||'/'
174 ||
175 '
176
177
178 --Public procedures
179
180 PROCEDURE trans_account_def_online
181 (
182 p_transaction_coa_id IN NUMBER
183 ,p_accounting_coa_id IN NUMBER
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER
186 ,x_msg_data OUT NOCOPY VARCHAR2
187 );
188
189 PROCEDURE trans_account_def_batch
190 (
191 p_transaction_coa_id IN NUMBER
192 ,p_accounting_coa_id IN NUMBER
193 ,x_return_status OUT NOCOPY VARCHAR2
194 ,x_msg_count OUT NOCOPY NUMBER
195 ,x_msg_data OUT NOCOPY VARCHAR2
196 );
197
198 FUNCTION log_error (
199 p_mode IN VARCHAR2
200 ,p_rowid IN UROWID
201 ,p_line_index IN NUMBER
202 ,p_encoded_message IN VARCHAR2
203 )
204 RETURN VARCHAR2;
205
206 FUNCTION log_ccid_not_found_error (
207 p_mode IN VARCHAR2
208 ,p_rowid IN UROWID
209 ,p_line_index IN NUMBER
210 ,p_chart_of_accounts_name IN VARCHAR2
211 ,p_ccid IN NUMBER
212 ,p_calling_function_name IN VARCHAR2
213 ,p_account_type_code IN VARCHAR2
214 )
215 RETURN VARCHAR2;
216
217 FUNCTION log_ccid_disabled_error (
218 p_mode IN VARCHAR2
219 ,p_rowid IN UROWID
220 ,p_line_index IN NUMBER
221 ,p_chart_of_accounts_name IN VARCHAR2
222 ,p_ccid IN NUMBER
223 ,p_calling_function_name IN VARCHAR2
224 ,p_account_type_code IN VARCHAR2
225 )
226 RETURN VARCHAR2;
227
228 FUNCTION log_null_segments_error (
229 p_mode IN VARCHAR2
230 ,p_rowid IN UROWID
231 ,p_line_index IN NUMBER
232 ,p_chart_of_accounts_name IN VARCHAR2
233 ,p_ccid IN NUMBER
234 ,p_calling_function_name IN VARCHAR2
235 ,p_account_type_code IN VARCHAR2
236 )
237 RETURN VARCHAR2;
238
239 FUNCTION create_ccid (
240 p_mode IN VARCHAR2
241 ,p_rowid IN UROWID
242 ,p_line_index IN NUMBER
243 ,p_chart_of_accounts_id IN NUMBER
244 ,p_chart_of_accounts_name IN VARCHAR2
245 ,p_calling_function_name IN VARCHAR2
246 ,p_validation_date IN DATE
247 ,p_concatenated_segments IN VARCHAR2
248 )
249 RETURN NUMBER;
250
251 FUNCTION get_coa_info
252 (
253 p_chart_of_accounts_id IN NUMBER
254 ,p_chart_of_accounts_name OUT NOCOPY VARCHAR2
255 ,p_flex_delimiter OUT NOCOPY VARCHAR2
256 ,p_concat_segments_template OUT NOCOPY VARCHAR2
257 ,p_gl_balancing_segment_name OUT NOCOPY VARCHAR2
258 ,p_gl_account_segment_name OUT NOCOPY VARCHAR2
259 ,p_gl_intercompany_segment_name OUT NOCOPY VARCHAR2
260 ,p_gl_management_segment_name OUT NOCOPY VARCHAR2
261 ,p_fa_cost_ctr_segment_name OUT NOCOPY VARCHAR2
262 ,p_table_segment_qualifiers OUT NOCOPY xla_cmp_tad_pkg.gt_table_V30_V30
263 ,p_table_segment_column_names OUT NOCOPY xla_cmp_tad_pkg.gt_table_V30
264 )
265 RETURN BOOLEAN;
266
267
268 $TAD_ADR_FUNCT_SPECS$
269
270
271
272 END $TAD_PACKAGE_NAME_1$;
273 ';
274
275 --+==========================================================================+
276 --| end of package specification template |
277 --+==========================================================================+
278
279
280 C_TMPL_BATCH_BLD_CCID_DYN_STMS CONSTANT CLOB :=
281 '
282 IF NOT build_code_combinations_dyn
283 (
284 p_chart_of_accounts_id => p_chart_of_accounts_id
285 ,p_chart_of_accounts_name => p_chart_of_accounts_name
286 ,p_flex_delimiter => p_flex_delimiter
287 ,p_concat_segments_template => p_concat_segments_template
288 ,p_table_segment_qualifiers => p_table_segment_qualifiers
289 ,p_table_segment_column_names => p_table_segment_column_names
290 ,p_gl_balancing_segment_name => p_gl_balancing_segment_name
291 ,p_gl_account_segment_name => p_gl_account_segment_name
292 ,p_gl_intercompany_segment_name => p_gl_intercompany_segment_name
293 ,p_gl_management_segment_name => p_gl_management_segment_name
294 ,p_fa_cost_ctr_segment_name => p_fa_cost_ctr_segment_name
295 )
296 THEN
297 l_fatal_error_message := ''build_code_combinations_dyn failed'';
298 RAISE le_fatal_error;
299 END IF;
300 ';
301
302
303
304
305 --+==========================================================================+
306 --| package body template |
307 --+==========================================================================+
308
309 --
310
311 C_TMPL_TAD_PACKAGE_BODY_PART_1 CONSTANT CLOB :=
312 'CREATE OR REPLACE PACKAGE BODY $TAD_PACKAGE_NAME_1$ AS' ||
313 g_chr_newline||
314 '/'||'* $Header: generated on site by the Subledger Accounting Compiler*/' ||
315 g_chr_newline||
316 '/'|| '*======================================================================+
317 | Copyright (c) 2004 Oracle Corporation |
318 | Redwood Shores, CA, USA |
319 | All rights reserved. |
320 +=======================================================================+
321 | PACKAGE NAME |
322 | $TAD_PACKAGE_NAME_2$
323 | |
324 | DESCRIPTION |
325 | |
326 | Transaction Account Builder Engine Component |
327 | |
328 | Package generated by Oracle Subledger Accounting for |
329 | |
330 | $APPLICATION_NAME$
331 | (application_id: $APPLICATION_ID$
332 | |
333 | Corresponds to the following Transaction Account Definition |
334 | Code : $TAD_CODE$
335 | Type Code : $TAD_TYPE_CODE$
336 | AMB Context Code: $AMB_CONTEXT_CODE$
337 | |
338 | |
339 | |
340 | ATTENTION: |
341 | This package has been automatically generated by the |
342 | Oracle Subledger Accounting Compiler. You should not modify its |
343 | content manually. |
344 | This package has been generated according to the current setup |
345 | for the aforementioned Transaction Account Definition. |
346 | |
347 | In case of issues independent of the setup |
348 | please log a bug against Oracle Subledger Accounting. |
349 | |
350 | |
351 | HISTORY |
352 | $HISTORY$
353 | |
354 +=======================================================================*'
355 ||'/'
356 ||
357 '
358
359 FUNCTION g_default_module RETURN VARCHAR2
360 IS
361 BEGIN
362 return ''xla.plsql.$TAD_PACKAGE_NAME_3$'';
363 END g_default_module;
364
365 FUNCTION g_batch_build_ccid_stmts RETURN CLOB
366 IS
367 BEGIN
368 return REPLACE(xla_cmp_tad_pkg.C_TMPL_BATCH_BUILD_CCID_STMTS, '''''''', '''''''''''');
369 END g_batch_build_ccid_stmts;
370
371 PROCEDURE trace
372 ( p_module IN VARCHAR2
373 ,p_msg IN VARCHAR2
374 ,p_level IN NUMBER
375 ) IS
376 BEGIN
377 ----------------------------------------------------------------------------
378 -- Following is for FND log.
379 ----------------------------------------------------------------------------
380 IF (p_msg IS NULL AND p_level >= xla_cmp_tad_pkg.g_log_level) THEN
381 fnd_log.message(p_level, p_module);
382 ELSIF p_level >= xla_cmp_tad_pkg.g_log_level THEN
383 fnd_log.string(p_level, p_module, p_msg);
384 END IF;
385
386 EXCEPTION
387 WHEN app_exceptions.application_exception THEN
388 RAISE;
389 WHEN OTHERS THEN
390 fnd_message.set_name(''XLA'', ''XLA_TAB_UNHANDLED_EXCEPTION'');
391 fnd_message.set_token( ''PROCEDURE''
392 ,''$TAD_PACKAGE_NAME_3$.trace'');
393 RAISE;
394 END trace;
395
396
397 PROCEDURE log_error (
398 p_mode IN VARCHAR2
399 ,p_rowid IN UROWID
400 ,p_line_index IN NUMBER
401 ,p_encoded_message IN VARCHAR2
402 )
403 IS
404 l_encoded_message VARCHAR2(2000);
405 l_log_module VARCHAR2(2000);
406 BEGIN
407
408 IF xla_cmp_tad_pkg.g_log_enabled THEN
409 l_log_module := g_default_module||''.log_error'';
410 END IF;
411
412 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
413 trace
414 ( p_module => l_log_module
415 ,p_msg => ''BEGIN '' || l_log_module
416 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
417 END IF;
418
419 IF p_encoded_message IS NULL
420 THEN
421 --Retrieve the encoded message
422 l_encoded_message := FND_MESSAGE.GET_ENCODED();
423 ELSE
424 l_encoded_message := p_encoded_message;
425 END IF;
426
427 --Write a record in the appropriate table depending on the mode
428 IF p_mode = ''BATCH''
429 THEN
430 INSERT INTO XLA_TAB_ERRORS_GT
431 (
432 base_rowid
433 ,msg_data
434 )
435 (
436 SELECT p_rowid
437 ,l_encoded_message
438 FROM dual
439 WHERE 0 = (SELECT COUNT(*)
440 FROM XLA_TAB_ERRORS_GT xte
441 WHERE xte.base_rowid = p_rowid
442 AND xte.msg_data = l_encoded_message
443 )
444 AND ROWNUM = 1
445 );
446
447 ELSIF p_mode = ''ONLINE''
448 THEN
449 NULL;
450 END IF;
451
452 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
453 trace
454 ( p_module => l_log_module
455 ,p_msg => ''END '' || l_log_module
456 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
457 END IF;
458
459 END log_error;
460
461 FUNCTION log_error (
462 p_mode IN VARCHAR2
463 ,p_rowid IN UROWID
464 ,p_line_index IN NUMBER
465 ,p_encoded_message IN VARCHAR2
466 )
467 RETURN VARCHAR2
468 IS
469 l_encoded_message VARCHAR2(2000);
470 l_log_module VARCHAR2(2000);
471 BEGIN
472
473 IF xla_cmp_tad_pkg.g_log_enabled THEN
474 l_log_module := g_default_module||''.log_error'';
475 END IF;
476
477 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
478 trace
479 ( p_module => l_log_module
480 ,p_msg => ''BEGIN '' || l_log_module
481 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
482 END IF;
483
484 log_error (
485 p_mode => p_mode
486 ,p_rowid => p_rowid
487 ,p_line_index => p_line_index
488 ,p_encoded_message => p_encoded_message
489 );
490
491 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
492 trace
493 ( p_module => l_log_module
494 ,p_msg => ''END '' || l_log_module
495 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
496 END IF;
497
498 RETURN NULL;
499
500 END log_error;
501
502
503 PROCEDURE log_error (
504 p_mode IN VARCHAR2
505 ,p_rowid IN UROWID
506 ,p_line_index IN NUMBER
507 ,p_msg_name IN VARCHAR2
508 ,p_token_name_1 IN VARCHAR2 DEFAULT NULL
509 ,p_token_value_1 IN VARCHAR2 DEFAULT NULL
510 ,p_token_name_2 IN VARCHAR2 DEFAULT NULL
511 ,p_token_value_2 IN VARCHAR2 DEFAULT NULL
512 ,p_token_name_3 IN VARCHAR2 DEFAULT NULL
513 ,p_token_value_3 IN VARCHAR2 DEFAULT NULL
514 ,p_token_name_4 IN VARCHAR2 DEFAULT NULL
515 ,p_token_value_4 IN VARCHAR2 DEFAULT NULL
516 ,p_token_name_5 IN VARCHAR2 DEFAULT NULL
517 ,p_token_value_5 IN VARCHAR2 DEFAULT NULL
518 )
519 IS
520 l_encoded_message VARCHAR2(2000);
521 l_log_module VARCHAR2(2000);
522 BEGIN
523
524 IF xla_cmp_tad_pkg.g_log_enabled THEN
525 l_log_module := g_default_module||''.log_error'';
526 END IF;
527
528 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
529 trace
530 ( p_module => l_log_module
531 ,p_msg => ''BEGIN '' || l_log_module
532 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
533 END IF;
534
535 --Set the message onto the message stack
536 FND_MESSAGE.SET_NAME
537 (
538 application => ''XLA''
539 ,name => p_msg_name
540 );
541
542 --Set token 1
543 IF p_token_name_1 IS NOT NULL
544 THEN
545 FND_MESSAGE.SET_TOKEN (p_token_name_1, p_token_value_1);
546 END IF;
547
548 --Set token 2
549 IF p_token_name_2 IS NOT NULL
550 THEN
551 FND_MESSAGE.SET_TOKEN (p_token_name_2, p_token_value_2);
552 END IF;
553
554 --Set token 3
555 IF p_token_name_3 IS NOT NULL
556 THEN
557 FND_MESSAGE.SET_TOKEN (p_token_name_3, p_token_value_3);
558 END IF;
559
560 --Set token 4
561 IF p_token_name_4 IS NOT NULL
562 THEN
563 FND_MESSAGE.SET_TOKEN (p_token_name_4, p_token_value_4);
564 END IF;
565
566 --Set token 5
567 IF p_token_name_5 IS NOT NULL
568 THEN
569 FND_MESSAGE.SET_TOKEN (p_token_name_5, p_token_value_5);
570 END IF;
571
572 log_error
573 (
574 p_mode => p_mode
575 ,p_rowid => p_rowid
576 ,p_line_index => p_line_index
577 ,p_encoded_message => NULL
578 );
579
580 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
581 trace
582 ( p_module => l_log_module
583 ,p_msg => ''END '' || l_log_module
584 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
585 END IF;
586
587 END log_error;
588
589
590 FUNCTION log_null_segments_error (
591 p_mode IN VARCHAR2
592 ,p_rowid IN UROWID
593 ,p_line_index IN NUMBER
594 ,p_chart_of_accounts_name IN VARCHAR2
595 ,p_ccid IN NUMBER
596 ,p_calling_function_name IN VARCHAR2
597 ,p_account_type_code IN VARCHAR2
598 )
599 RETURN VARCHAR2
600 IS
601 l_account_type_name VARCHAR2(80);
602 l_log_module VARCHAR2(2000);
603 BEGIN
604
605 IF xla_cmp_tad_pkg.g_log_enabled THEN
606 l_log_module := g_default_module||''.log_null_segments_error'';
607 END IF;
608
609 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
610 trace
611 ( p_module => l_log_module
612 ,p_msg => ''BEGIN '' || l_log_module
613 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
614 END IF;
615
616 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level)
617 THEN
618 trace
619 ( p_module => l_log_module
620 ,p_msg => ''p_mode: '' || p_mode
621 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
622 trace
623 ( p_module => l_log_module
624 ,p_msg => ''p_rowid: '' || p_rowid
625 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
626 trace
627 ( p_module => l_log_module
628 ,p_msg => ''p_ccid: '' || p_ccid
629 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
630 trace
631 ( p_module => l_log_module
632 ,p_msg => ''p_chart_of_accounts_name: ''||p_chart_of_accounts_name
633 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
634 END IF;
635
636 --Try to get the account type name
637 BEGIN
638 SELECT xtat.name
639 INTO l_account_type_name
640 FROM xla_tab_acct_types_tl xtat
641 WHERE xtat.application_id = $APPLICATION_ID_2$
642 AND xtat.account_type_code = p_account_type_code;
643
644 EXCEPTION
645 --If unable to get the name use the account type code
646 WHEN OTHERS
647 THEN
648 l_account_type_name := p_account_type_code;
649 END;
650
651 --If no ccid was given it
652 IF p_ccid IS NULL
653 THEN
654 log_error (
655 p_mode => p_mode
656 ,p_rowid => p_rowid
657 ,p_line_index => p_line_index
658 ,p_msg_name => ''XLA_TAB_CCID_NULL''
659 ,p_token_name_1 => ''TRX_ACCT_TYPE''
660 ,p_token_value_1 => l_account_type_name
661
662 );
663 ELSE
664 log_error (
665 p_mode => p_mode
666 ,p_rowid => p_rowid
667 ,p_line_index => p_line_index
668 ,p_msg_name => ''XLA_TAB_CCID_NOT_FOUND''
669 ,p_token_name_1 => ''TRX_ACCT_TYPE''
670 ,p_token_value_1 => l_account_type_name
671 ,p_token_name_2 => ''ACCOUNT_VALUE''
672 ,p_token_value_2 => p_ccid
673 ,p_token_name_3 => ''STRUCTURE_NAME''
674 ,p_token_value_3 => p_chart_of_accounts_name
675 );
676 END IF;
677
678 RETURN NULL;
679
680 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
681 trace
682 ( p_module => l_log_module
683 ,p_msg => ''END '' || l_log_module
684 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
685 END IF;
686
687 END log_null_segments_error;
688
689 FUNCTION log_ccid_disabled_error (
690 p_mode IN VARCHAR2
691 ,p_rowid IN UROWID
692 ,p_line_index IN NUMBER
693 ,p_chart_of_accounts_name IN VARCHAR2
694 ,p_ccid IN NUMBER
695 ,p_calling_function_name IN VARCHAR2
696 ,p_account_type_code IN VARCHAR2
697 )
698 RETURN VARCHAR2
699 IS
700 l_account_type_name VARCHAR2(80);
701 l_log_module VARCHAR2(2000);
702 BEGIN
703
704 IF xla_cmp_tad_pkg.g_log_enabled THEN
705 l_log_module := g_default_module||''.log_ccid_disabled_error'';
706 END IF;
707
708 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
709 trace
710 ( p_module => l_log_module
711 ,p_msg => ''BEGIN '' || l_log_module
712 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
713 END IF;
714
715 --Try to get the account type name
716 BEGIN
717 SELECT xtat.name
718 INTO l_account_type_name
719 FROM xla_tab_acct_types_tl xtat
720 WHERE xtat.application_id = $APPLICATION_ID_2$
721 AND xtat.account_type_code = p_account_type_code;
722
723 EXCEPTION
724 --If unable to get the name use the account type code
725 WHEN OTHERS
726 THEN
727 l_account_type_name := p_account_type_code;
728 END;
729
730 log_error (
731 p_mode => p_mode
732 ,p_rowid => p_rowid
733 ,p_line_index => p_line_index
734 ,p_msg_name => ''XLA_TAB_CCID_DISABLED''
735 ,p_token_name_1 => ''ACCOUNT_VALUE''
736 ,p_token_value_1 => p_ccid
737 ,p_token_name_2 => ''STRUCTURE_NAME''
738 ,p_token_value_2 => p_chart_of_accounts_name
739 ,p_token_name_3 => ''TRX_ACCT_TYPE''
740 ,p_token_value_3 => l_account_type_name
741 );
742
743 RETURN NULL;
744
745 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
746 trace
747 ( p_module => l_log_module
748 ,p_msg => ''END '' || l_log_module
749 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
750 END IF;
751
752 END log_ccid_disabled_error;
753
754 FUNCTION log_ccid_not_found_error (
755 p_mode IN VARCHAR2
756 ,p_rowid IN UROWID
757 ,p_line_index IN NUMBER
758 ,p_chart_of_accounts_name IN VARCHAR2
759 ,p_ccid IN NUMBER
760 ,p_calling_function_name IN VARCHAR2
761 ,p_account_type_code IN VARCHAR2
762 )
763 RETURN VARCHAR2
764 IS
765 l_account_type_name VARCHAR2(80);
766 l_log_module VARCHAR2(2000);
767 BEGIN
768
769 IF xla_cmp_tad_pkg.g_log_enabled THEN
770 l_log_module := g_default_module||''.log_ccid_not_found_error'';
771 END IF;
772
773 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
774 trace
775 ( p_module => l_log_module
776 ,p_msg => ''BEGIN '' || l_log_module
777 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
778 END IF;
779
780 --Try to get the account type name
781 BEGIN
782 SELECT xtat.name
783 INTO l_account_type_name
784 FROM xla_tab_acct_types_tl xtat
785 WHERE xtat.application_id = $APPLICATION_ID_2$
786 AND xtat.account_type_code = p_account_type_code;
787
788 EXCEPTION
789 --If unable to get the name use the account type code
790 WHEN OTHERS
791 THEN
792 l_account_type_name := p_account_type_code;
793 END;
794
795 log_error (
796 p_mode => p_mode
797 ,p_rowid => p_rowid
798 ,p_line_index => p_line_index
799 ,p_msg_name => ''XLA_TAB_CCID_NOT_FOUND''
800 ,p_token_name_1 => ''TRX_ACCT_TYPE''
801 ,p_token_value_1 => l_account_type_name
802 ,p_token_name_2 => ''ACCOUNT_VALUE''
803 ,p_token_value_2 => p_ccid
804 ,p_token_name_3 => ''STRUCTURE_NAME''
805 ,p_token_value_3 => p_chart_of_accounts_name
806 );
807
808 RETURN NULL;
809
810 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
811 trace
812 ( p_module => l_log_module
813 ,p_msg => ''END '' || l_log_module
814 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
815 END IF;
816
817 END log_ccid_not_found_error;
818 '
819 ;
820
821 C_TMPL_TAD_PACKAGE_BODY_PART_2 CONSTANT CLOB :=
822 '
823 FUNCTION create_ccid (
824 p_mode IN VARCHAR2
825 ,p_rowid IN UROWID
826 ,p_line_index IN NUMBER
827 ,p_chart_of_accounts_id IN NUMBER
828 ,p_chart_of_accounts_name IN VARCHAR2
829 ,p_calling_function_name IN VARCHAR2
830 ,p_validation_date IN DATE
831 ,p_concatenated_segments IN VARCHAR2
832 )
833 RETURN NUMBER
834 IS
835 l_code_combination_id NUMBER;
836
837 l_log_module VARCHAR2(2000);
838 BEGIN
839
840 IF xla_cmp_tad_pkg.g_log_enabled THEN
841 l_log_module := g_default_module||''.create_ccid'';
842 END IF;
843
844 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
845 trace
846 ( p_module => l_log_module
847 ,p_msg => ''BEGIN '' || l_log_module
848 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
849 END IF;
850
851 l_code_combination_id := fnd_flex_ext.get_ccid
852 (
853 application_short_name => ''SQLGL''
854 ,key_flex_code => ''GL#''
855 ,structure_number => p_chart_of_accounts_id
856 ,validation_date => TO_CHAR(p_validation_date, ''YYYY/MM/DD HH24:MI:SS'')
857 ,concatenated_segments => p_concatenated_segments
858 );
859
860 IF l_code_combination_id > 0
861 THEN
862 INSERT
863 INTO XLA_TAB_NEW_CCIDS_GT
864 ( code_combination_id
865 ,base_rowid
866 ,concatenated_segments
867 ,msg_data
868 )
869 VALUES
870 ( l_code_combination_id
871 ,p_rowid
872 ,p_concatenated_segments
873 ,NULL
874 );
875 ELSE
876 --If 0 is returned there must be a message on the stack
877 DECLARE
878 l_message_text VARCHAR2(2000);
879 l_encoded_message VARCHAR2(2000);
880 BEGIN
881 l_code_combination_id := NULL;
882 --Get the flex message text
883 l_message_text := FND_MESSAGE.GET();
884
885 --Set the TAB message onto the message stack
886 FND_MESSAGE.SET_NAME
887 (
888 application => ''XLA''
889 ,name => ''XLA_TAB_CCID_COULD_NOT_CREATE''
890 );
891 --Replace the token for the flex message retrieved above
892 FND_MESSAGE.SET_TOKEN ( token => ''ERROR''
893 ,value => l_message_text
894 );
895 --Replace the function name token
896 FND_MESSAGE.SET_TOKEN ( token => ''FUNCTION_NAME''
897 ,value => p_calling_function_name
898 );
899 --Get the resulting encoded message
900 l_encoded_message := FND_MESSAGE.GET_ENCODED();
901
902 --Log the error for the current line
903 log_error (
904 p_mode => p_mode
905 ,p_rowid => p_rowid
906 ,p_line_index => p_line_index
907 ,p_encoded_message => l_encoded_message
908 );
909
910 --Create a record in the new ccid temp table
911 INSERT
912 INTO XLA_TAB_NEW_CCIDS_GT
913 ( code_combination_id
914 ,base_rowid
915 ,concatenated_segments
916 ,msg_data
917 )
918 VALUES
919 ( l_code_combination_id
920 ,p_rowid
921 ,p_concatenated_segments
922 ,l_encoded_message
923 );
924 END;
925 END IF;
926
927 --Assign return values
928 RETURN l_code_combination_id;
929
930 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
931 trace
932 ( p_module => l_log_module
933 ,p_msg => ''END '' || l_log_module
934 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
935 END IF;
936
937 END create_ccid;
938
939
940 FUNCTION get_coa_info
941 (
942 p_chart_of_accounts_id IN NUMBER
943 ,p_chart_of_accounts_name OUT NOCOPY VARCHAR2
944 ,p_flex_delimiter OUT NOCOPY VARCHAR2
945 ,p_concat_segments_template OUT NOCOPY VARCHAR2
946 ,p_gl_balancing_segment_name OUT NOCOPY VARCHAR2
947 ,p_gl_account_segment_name OUT NOCOPY VARCHAR2
948 ,p_gl_intercompany_segment_name OUT NOCOPY VARCHAR2
949 ,p_gl_management_segment_name OUT NOCOPY VARCHAR2
950 ,p_fa_cost_ctr_segment_name OUT NOCOPY VARCHAR2
951 ,p_table_segment_qualifiers OUT NOCOPY xla_cmp_tad_pkg.gt_table_V30_V30
952 ,p_table_segment_column_names OUT NOCOPY xla_cmp_tad_pkg.gt_table_V30
953 )
954 RETURN BOOLEAN
955 IS
956 le_fatal_error EXCEPTION;
957
958 TYPE lt_table_v30 IS TABLE OF VARCHAR2(30);
959
960 --Local vars for OUT params
961 l_chart_of_accounts_name VARCHAR2(80);
962 l_flex_delimiter VARCHAR2(1);
963 l_concat_segments_template VARCHAR2(2000);
964 l_gl_balancing_segment_name VARCHAR2(30);
965 l_gl_account_segment_name VARCHAR2(30);
966 l_gl_intercompany_segment_name VARCHAR2(30);
967 l_gl_management_segment_name VARCHAR2(30);
968 l_fa_cost_ctr_segment_name VARCHAR2(30);
969 l_table_segment_qualifiers xla_cmp_tad_pkg.gt_table_V30_V30;
970 l_table_segment_column_names xla_cmp_tad_pkg.gt_table_v30;
971
972 --Ancillary local vars
973 l_table_qualifier_names lt_table_v30;
974 l_account_segment_column VARCHAR2(30);
975 l_return_value BOOLEAN;
976 l_fatal_error_message VARCHAR2(2000);
977 l_log_module VARCHAR2(2000);
978 BEGIN
979 IF xla_cmp_tad_pkg.g_log_enabled THEN
980 l_log_module := g_default_module||''.get_coa_info'';
981 END IF;
982
983 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
984 trace
985 ( p_module => l_log_module
986 ,p_msg => ''BEGIN '' || l_log_module
987 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
988 END IF;
989
990 l_return_value := TRUE;
991
992 --Get the Chart Of Accounts name
993 SELECT id_flex_structure_name
994 INTO l_chart_of_accounts_name
995 FROM fnd_id_flex_structures_vl ffsvl
996 WHERE ffsvl.application_id = 101
997 AND ffsvl.id_flex_code = ''GL#''
998 AND ffsvl.id_flex_num = p_chart_of_accounts_id;
999
1000 --Initialize the segment qualifier name list
1001 l_table_qualifier_names := lt_table_v30
1002 ( ''GL_BALANCING''
1003 ,''GL_ACCOUNT''
1004 ,''GL_INTERCOMPANY''
1005 ,''GL_MANAGEMENT''
1006 ,''FA_COST_CTR''
1007 );
1008
1009 --For each qualifier (if assigned) we want the segment column name
1010 FOR i IN l_table_qualifier_names.FIRST .. l_table_qualifier_names.LAST
1011 LOOP
1012 IF FND_FLEX_APIS.get_segment_column( 101
1013 ,''GL#''
1014 ,p_chart_of_accounts_id
1015 ,l_table_qualifier_names(i)
1016 ,l_account_segment_column
1017 )
1018 THEN
1019 l_table_segment_qualifiers(l_table_qualifier_names(i)) := l_account_segment_column;
1020 END IF;
1021 END LOOP;
1022
1023 --There are explicit out params for each segment qualifiers
1024
1025 --Retrieve the balancing segment qualifier column name
1026 BEGIN
1027 l_gl_balancing_segment_name := l_table_segment_qualifiers(''GL_BALANCING'');
1028 EXCEPTION
1029 WHEN NO_DATA_FOUND
1030 THEN
1031 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
1032 THEN
1033 trace
1034 ( p_module => l_log_module
1035 ,p_msg => ''No balancing segment qualifier for COA:''
1036 || p_chart_of_accounts_id
1037 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1038 END IF;
1039 fnd_message.set_name
1040 (
1041 application => ''XLA''
1042 ,name => ''XLA_TAB_CANNOT_FIND_QUALIFIER''
1043 );
1044 fnd_message.set_token
1045 (
1046 token => ''QUALIFIER_NAME''
1047 ,value => ''GL_BALANCING''
1048 );
1049 fnd_message.set_token
1050 (
1051 token => ''STRUCTURE_NAME''
1052 ,value => l_chart_of_accounts_name
1053 );
1054 fnd_msg_pub.add;
1055 RAISE le_fatal_error;
1056 END;
1057
1058 --Retrieve the natural account segment qualifier column name
1059 BEGIN
1060 l_gl_account_segment_name := l_table_segment_qualifiers(''GL_ACCOUNT'');
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND
1063 THEN
1064 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
1065 THEN
1066 trace
1067 ( p_module => l_log_module
1068 ,p_msg => ''No account segment qualifier for COA:''
1069 || p_chart_of_accounts_id
1070 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1071 END IF;
1072 fnd_message.set_name
1073 (
1074 application => ''XLA''
1075 ,name => ''XLA_TAB_CANNOT_FIND_QUALIFIER''
1076 );
1077 fnd_message.set_token
1078 (
1079 token => ''QUALIFIER_NAME''
1080 ,value => ''GL_ACCOUNT''
1081 );
1082 fnd_message.set_token
1083 (
1084 token => ''STRUCTURE_NAME''
1085 ,value => l_chart_of_accounts_name
1086 );
1087 fnd_msg_pub.add;
1088 RAISE le_fatal_error;
1089 END;
1090
1091 --Retrieve the intercompany segment qualifier column name
1092 BEGIN
1093 l_gl_intercompany_segment_name := l_table_segment_qualifiers(''GL_INTERCOMPANY'');
1094 EXCEPTION
1095 WHEN NO_DATA_FOUND
1096 THEN
1097 NULL;
1098 END;
1099
1100
1101 --Retrieve the manegement segment qualifier column name
1102 BEGIN
1103 l_gl_management_segment_name := l_table_segment_qualifiers(''GL_MANAGEMENT'');
1104 EXCEPTION
1105 WHEN NO_DATA_FOUND
1106 THEN
1107 NULL;
1108 END;
1109
1110 --Retrieve the cost center segment qualifier column name
1111 BEGIN
1112 l_fa_cost_ctr_segment_name := l_table_segment_qualifiers(''FA_COST_CTR'');
1113 EXCEPTION
1114 WHEN NO_DATA_FOUND
1115 THEN
1116 NULL;
1117 END;
1118
1119 l_flex_delimiter := FND_FLEX_EXT.get_delimiter
1120 (
1121 application_short_name => ''SQLGL''
1122 ,key_flex_code => ''GL#''
1123 ,structure_number => p_chart_of_accounts_id
1124 );
1125 IF l_flex_delimiter IS NULL
1126 THEN
1127 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level) THEN
1128 trace
1129 ( p_module => l_log_module
1130 ,p_msg => ''EXCEPTION:''
1131 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1132 trace
1133 ( p_module => l_log_module
1134 ,p_msg => ''get_delimiter failed''
1135 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1136 END IF;
1137 --The error has been placed on the stack
1138 RAISE le_fatal_error;
1139 END IF;
1140
1141
1142 SELECT UPPER(fifs.application_column_name)
1143 BULK COLLECT
1144 INTO l_table_segment_column_names
1145 FROM fnd_id_flex_segments fifs
1146 WHERE fifs.application_id = 101
1147 AND fifs.id_flex_code = ''GL#''
1148 AND fifs.id_flex_num = p_chart_of_accounts_id
1149 AND fifs.enabled_flag = ''Y''
1150 ORDER BY fifs.segment_num;
1151
1152 l_concat_segments_template := NULL;
1153
1154 IF l_table_segment_column_names.COUNT > 0
1155 THEN
1156 FOR i IN 1..l_table_segment_column_names.COUNT
1157 LOOP
1158 l_concat_segments_template := l_concat_segments_template
1159 || CASE i
1160 WHEN 1 THEN NULL
1161 ELSE l_flex_delimiter
1162 END
1163 || l_table_segment_column_names(i);
1164 END LOOP;
1165 END IF;
1166
1167 --Assign out parameters
1168 p_chart_of_accounts_name := l_chart_of_accounts_name;
1169 p_flex_delimiter := l_flex_delimiter;
1170 p_concat_segments_template := l_concat_segments_template;
1171 p_gl_balancing_segment_name := l_gl_balancing_segment_name;
1172 p_gl_account_segment_name := l_gl_account_segment_name;
1173 p_gl_intercompany_segment_name := l_gl_intercompany_segment_name;
1174 p_gl_management_segment_name := l_gl_management_segment_name;
1175 p_fa_cost_ctr_segment_name := l_fa_cost_ctr_segment_name;
1176 p_table_segment_qualifiers := l_table_segment_qualifiers;
1177 p_table_segment_column_names := l_table_segment_column_names;
1178
1179 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1180 trace
1181 ( p_module => l_log_module
1182 ,p_msg => ''END '' || l_log_module
1183 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1184 END IF;
1185
1186 RETURN l_return_value;
1187
1188 EXCEPTION
1189 WHEN le_fatal_error
1190 THEN
1191 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level) THEN
1192 trace
1193 ( p_module => l_log_module
1194 ,p_msg => ''EXCEPTION:''
1195 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1196 trace
1197 ( p_module => l_log_module
1198 ,p_msg => ''Fatal error: '' || l_fatal_error_message
1199 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1200 END IF;
1201 RETURN FALSE;
1202 WHEN OTHERS
1203 THEN
1204 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level) THEN
1205 trace
1206 ( p_module => l_log_module
1207 ,p_msg => ''EXCEPTION:''
1208 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1209 trace
1210 ( p_module => l_log_module
1211 ,p_msg => ''Error: '' || SQLERRM
1212 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1213 END IF;
1214 RETURN FALSE;
1215 END get_coa_info;
1216
1217
1218 FUNCTION get_flexfield_segment
1219 (
1220 p_mode IN VARCHAR2
1221 ,p_rowid IN UROWID
1222 ,p_line_index IN NUMBER
1223 ,p_chart_of_accounts_id IN NUMBER
1224 ,p_chart_of_accounts_name IN VARCHAR2
1225 ,p_ccid IN NUMBER
1226 ,p_source_code IN VARCHAR2
1227 ,p_source_type_code IN VARCHAR2
1228 ,p_source_application_id IN NUMBER
1229 ,p_segment_name IN VARCHAR2
1230 ,p_gl_balancing_segment_name IN VARCHAR2
1231 ,p_gl_account_segment_name IN VARCHAR2
1232 ,p_gl_intercompany_segment_name IN VARCHAR2
1233 ,p_gl_management_segment_name IN VARCHAR2
1234 ,p_fa_cost_ctr_segment_name IN VARCHAR2
1235 ,p_adr_name IN VARCHAR2
1236 )
1237 RETURN VARCHAR2
1238 IS
1239 le_fatal_error EXCEPTION;
1240
1241 l_segment_name VARCHAR2(30);
1242 l_value_segment1 VARCHAR2(25);
1243 l_value_segment2 VARCHAR2(25);
1244 l_value_segment3 VARCHAR2(25);
1245 l_value_segment4 VARCHAR2(25);
1246 l_value_segment5 VARCHAR2(25);
1247 l_value_segment6 VARCHAR2(25);
1248 l_value_segment7 VARCHAR2(25);
1249 l_value_segment8 VARCHAR2(25);
1250 l_value_segment9 VARCHAR2(25);
1251 l_value_segment10 VARCHAR2(25);
1252 l_value_segment11 VARCHAR2(25);
1253 l_value_segment12 VARCHAR2(25);
1254 l_value_segment13 VARCHAR2(25);
1255 l_value_segment14 VARCHAR2(25);
1256 l_value_segment15 VARCHAR2(25);
1257 l_value_segment16 VARCHAR2(25);
1258 l_value_segment17 VARCHAR2(25);
1259 l_value_segment18 VARCHAR2(25);
1260 l_value_segment19 VARCHAR2(25);
1261 l_value_segment20 VARCHAR2(25);
1262 l_value_segment21 VARCHAR2(25);
1263 l_value_segment22 VARCHAR2(25);
1264 l_value_segment23 VARCHAR2(25);
1265 l_value_segment24 VARCHAR2(25);
1266 l_value_segment25 VARCHAR2(25);
1267 l_value_segment26 VARCHAR2(25);
1268 l_value_segment27 VARCHAR2(25);
1269 l_value_segment28 VARCHAR2(25);
1270 l_value_segment29 VARCHAR2(25);
1271 l_value_segment30 VARCHAR2(25);
1272 l_return_value VARCHAR2(25);
1273
1274 l_source_name VARCHAR2(80);
1275 l_fatal_error_message VARCHAR2(2000);
1276 l_log_module VARCHAR2(2000);
1277 BEGIN
1278 IF xla_cmp_tad_pkg.g_log_enabled THEN
1279 l_log_module := g_default_module||''.get_flexfield_segment'';
1280 END IF;
1281
1282 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1283 trace
1284 ( p_module => l_log_module
1285 ,p_msg => ''BEGIN '' || l_log_module
1286 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1287 END IF;
1288
1289 IF p_ccid IS NULL
1290 THEN
1291 SELECT name
1292 INTO l_source_name
1293 FROM xla_sources_vl xsv
1294 WHERE xsv.source_code = p_source_code
1295 AND xsv.source_type_code = p_source_type_code
1296 AND xsv.application_id = p_source_application_id;
1297
1298 log_error (
1299 p_mode => ''BATCH''
1300 ,p_rowid => p_rowid
1301 ,p_line_index => NULL
1302 ,p_msg_name => ''XLA_TAB_GET_SEG_CCID_IS_NULL''
1303 ,p_token_name_1 => ''SEGMENT_NAME''
1304 ,p_token_value_1 => p_segment_name
1305 ,p_token_name_2 => ''SOURCE_NAME''
1306 ,p_token_value_2 => l_source_name
1307 ,p_token_name_3 => ''SEGMENT_RULE_NAME''
1308 ,p_token_value_3 => p_adr_name
1309 );
1310
1311 RAISE le_fatal_error;
1312 END IF;
1313
1314 BEGIN
1315 SELECT
1316 gcc.segment1
1317 , gcc.segment2
1318 , gcc.segment3
1319 , gcc.segment4
1320 , gcc.segment5
1321 , gcc.segment6
1322 , gcc.segment7
1323 , gcc.segment8
1324 , gcc.segment9
1325 , gcc.segment10
1326 , gcc.segment11
1327 , gcc.segment12
1328 , gcc.segment13
1329 , gcc.segment14
1330 , gcc.segment15
1331 , gcc.segment16
1332 , gcc.segment17
1333 , gcc.segment18
1334 , gcc.segment19
1335 , gcc.segment20
1336 , gcc.segment21
1337 , gcc.segment22
1338 , gcc.segment23
1339 , gcc.segment24
1340 , gcc.segment25
1341 , gcc.segment26
1342 , gcc.segment27
1343 , gcc.segment28
1344 , gcc.segment29
1345 , gcc.segment30
1346 INTO
1347 l_value_segment1
1348 ,l_value_segment2
1349 ,l_value_segment3
1350 ,l_value_segment4
1351 ,l_value_segment5
1352 ,l_value_segment6
1353 ,l_value_segment7
1354 ,l_value_segment8
1355 ,l_value_segment9
1356 ,l_value_segment10
1357 ,l_value_segment11
1358 ,l_value_segment12
1359 ,l_value_segment13
1360 ,l_value_segment14
1361 ,l_value_segment15
1362 ,l_value_segment16
1363 ,l_value_segment17
1364 ,l_value_segment18
1365 ,l_value_segment19
1366 ,l_value_segment20
1367 ,l_value_segment21
1368 ,l_value_segment22
1369 ,l_value_segment23
1370 ,l_value_segment24
1371 ,l_value_segment25
1372 ,l_value_segment26
1373 ,l_value_segment27
1374 ,l_value_segment28
1375 ,l_value_segment29
1376 ,l_value_segment30
1377 FROM gl_code_combinations gcc
1378 WHERE gcc.code_combination_id = p_ccid
1379 AND gcc.chart_of_accounts_id = p_chart_of_accounts_id
1380 AND gcc.template_id IS NULL
1381 AND gcc.enabled_flag = ''Y'';
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND
1384 THEN
1385 SELECT name
1386 INTO l_source_name
1387 FROM xla_sources_vl xsv
1388 WHERE xsv.source_code = p_source_code
1389 AND xsv.source_type_code = p_source_type_code
1390 AND xsv.application_id = p_source_application_id;
1391
1392 log_error (
1393 p_mode => ''BATCH''
1394 ,p_rowid => p_rowid
1395 ,p_line_index => NULL
1396 ,p_msg_name => ''XLA_TAB_GET_SEG_CCID_NOT_FOUND''
1397 ,p_token_name_1 => ''ACCOUNT_VALUE''
1398 ,p_token_value_1 => p_ccid
1399 ,p_token_name_2 => ''SOURCE_NAME''
1400 ,p_token_value_2 => l_source_name
1401 ,p_token_name_3 => ''STRUCTURE_NAME''
1402 ,p_token_value_3 => p_chart_of_accounts_name
1403 ,p_token_name_4 => ''SEGMENT_RULE_NAME''
1404 ,p_token_value_4 => p_adr_name
1405 );
1406 RAISE le_fatal_error;
1407 END;
1408
1409 --The segment name might be a segment qualifier name
1410 CASE p_segment_name
1411 WHEN ''GL_BALANCING''
1412 THEN l_segment_name := p_gl_balancing_segment_name;
1413 WHEN ''GL_ACCOUNT''
1414 THEN l_segment_name := p_gl_account_segment_name;
1415 WHEN ''GL_INTERCOMPANY''
1416 THEN l_segment_name := p_gl_intercompany_segment_name;
1417 WHEN ''GL_MANAGEMENT''
1418 THEN l_segment_name := p_gl_management_segment_name;
1419 WHEN ''FA_COST_CTR''
1420 THEN l_segment_name := p_fa_cost_ctr_segment_name;
1421 ELSE
1422 l_segment_name := p_segment_name;
1423 END CASE;
1424
1425 CASE l_segment_name
1426 WHEN ''SEGMENT1''
1427 THEN l_return_value := l_value_segment1;
1428 WHEN ''SEGMENT2''
1429 THEN l_return_value := l_value_segment2;
1430 WHEN ''SEGMENT3''
1431 THEN l_return_value := l_value_segment3;
1432 WHEN ''SEGMENT4''
1433 THEN l_return_value := l_value_segment4;
1434 WHEN ''SEGMENT5''
1435 THEN l_return_value := l_value_segment5;
1436 WHEN ''SEGMENT6''
1437 THEN l_return_value := l_value_segment6;
1438 WHEN ''SEGMENT7''
1439 THEN l_return_value := l_value_segment7;
1440 WHEN ''SEGMENT8''
1441 THEN l_return_value := l_value_segment8;
1442 WHEN ''SEGMENT9''
1443 THEN l_return_value := l_value_segment9;
1444 WHEN ''SEGMENT10''
1445 THEN l_return_value := l_value_segment10;
1446 WHEN ''SEGMENT11''
1447 THEN l_return_value := l_value_segment11;
1448 WHEN ''SEGMENT12''
1449 THEN l_return_value := l_value_segment12;
1450 WHEN ''SEGMENT13''
1451 THEN l_return_value := l_value_segment13;
1452 WHEN ''SEGMENT14''
1453 THEN l_return_value := l_value_segment14;
1454 WHEN ''SEGMENT15''
1455 THEN l_return_value := l_value_segment15;
1456 WHEN ''SEGMENT16''
1457 THEN l_return_value := l_value_segment16;
1458 WHEN ''SEGMENT17''
1459 THEN l_return_value := l_value_segment17;
1460 WHEN ''SEGMENT18''
1461 THEN l_return_value := l_value_segment18;
1462 WHEN ''SEGMENT19''
1463 THEN l_return_value := l_value_segment19;
1464 WHEN ''SEGMENT20''
1465 THEN l_return_value := l_value_segment20;
1466 WHEN ''SEGMENT21''
1467 THEN l_return_value := l_value_segment21;
1468 WHEN ''SEGMENT22''
1469 THEN l_return_value := l_value_segment22;
1470 WHEN ''SEGMENT23''
1471 THEN l_return_value := l_value_segment23;
1472 WHEN ''SEGMENT24''
1473 THEN l_return_value := l_value_segment24;
1474 WHEN ''SEGMENT25''
1475 THEN l_return_value := l_value_segment25;
1476 WHEN ''SEGMENT26''
1477 THEN l_return_value := l_value_segment26;
1478 WHEN ''SEGMENT27''
1479 THEN l_return_value := l_value_segment27;
1480 WHEN ''SEGMENT28''
1481 THEN l_return_value := l_value_segment28;
1482 WHEN ''SEGMENT29''
1483 THEN l_return_value := l_value_segment29;
1484 WHEN ''SEGMENT30''
1485 THEN l_return_value := l_value_segment30;
1486 ELSE
1487 --The segment name is invalid
1488 log_error (
1489 p_mode => ''BATCH''
1490 ,p_rowid => p_rowid
1491 ,p_line_index => NULL
1492 ,p_msg_name => ''XLA_TAB_GET_SEG_INVLD_SEG_NAME''
1493 ,p_token_name_1 => ''FUNCTION_NAME''
1494 ,p_token_value_1 => ''$TAD_PACKAGE_NAME_3$.get_flexfield_segment''
1495 ,p_token_name_2 => ''SEGMENT_NAME''
1496 ,p_token_value_2 => p_segment_name
1497 ,p_token_name_3 => ''STRUCTURE_NAME''
1498 ,p_token_value_3 => p_chart_of_accounts_name
1499 ,p_token_name_4 => ''SEGMENT_RULE_NAME''
1500 ,p_token_value_4 => p_adr_name
1501 );
1502
1503 RAISE le_fatal_error;
1504
1505 END CASE;
1506
1507 RETURN l_return_value;
1508
1509 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1510 trace
1511 ( p_module => l_log_module
1512 ,p_msg => ''END '' || l_log_module
1513 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1514 END IF;
1515
1516 EXCEPTION
1517 WHEN le_fatal_error
1518 THEN
1519 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level) THEN
1520 trace
1521 ( p_module => l_log_module
1522 ,p_msg => ''EXCEPTION:''
1523 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1524 trace
1525 ( p_module => l_log_module
1526 ,p_msg => ''Fatal error: '' || l_fatal_error_message
1527 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1528 END IF;
1529 RAISE;
1530 WHEN OTHERS THEN
1531 log_error (
1532 p_mode => ''BATCH''
1533 ,p_rowid => p_rowid
1534 ,p_line_index => NULL
1535 ,p_msg_name => ''XLA_TAB_ADR_GENERIC_EXCEPTION''
1536 ,p_token_name_1 => ''FUNCTION_NAME''
1537 ,p_token_value_1 => ''$TAD_PACKAGE_NAME_3$.get_flexfield_segment''
1538 ,p_token_name_2 => ''ERROR''
1539 ,p_token_value_2 => SQLERRM
1540 ,p_token_name_3 => ''SEGMENT_RULE_NAME''
1541 ,p_token_value_3 => p_adr_name
1542 );
1543 RAISE;
1544 END get_flexfield_segment;
1545
1546
1547 $TAD_ADR_FUNCT_BODIES$
1548 ';
1549
1550 C_TMPL_TAD_PACKAGE_BODY_PART_3 CONSTANT CLOB :=
1551 '
1552 FUNCTION apply_adr_rules
1553 (
1554 p_chart_of_accounts_id IN NUMBER
1555 ,p_chart_of_accounts_name IN VARCHAR2
1556 ,p_flex_delimiter IN VARCHAR2
1557 ,p_concat_segments_template IN VARCHAR2
1558 ,p_table_segment_qualifiers IN xla_cmp_tad_pkg.gt_table_V30_V30
1559 ,p_table_segment_column_names IN xla_cmp_tad_pkg.gt_table_V30
1560 ,p_gl_balancing_segment_name IN VARCHAR2
1561 ,p_gl_account_segment_name IN VARCHAR2
1562 ,p_gl_intercompany_segment_name IN VARCHAR2
1563 ,p_gl_management_segment_name IN VARCHAR2
1564 ,p_fa_cost_ctr_segment_name IN VARCHAR2
1565 )
1566 RETURN BOOLEAN
1567 IS
1568 le_fatal_error EXCEPTION;
1569
1570 l_current_date DATE := TRUNC(SYSDATE);
1571
1572 l_return_value BOOLEAN;
1573 l_fatal_error_message VARCHAR2(2000);
1574 l_log_module VARCHAR2(2000);
1575 BEGIN
1576 IF xla_cmp_tad_pkg.g_log_enabled THEN
1577 l_log_module := g_default_module||''.apply_adr_rules'';
1578 END IF;
1579
1580 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1581 trace
1582 ( p_module => l_log_module
1583 ,p_msg => ''BEGIN '' || l_log_module
1584 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1585 END IF;
1586
1587 $C_TMPL_BATCH_CCID_SEG_UPD_STMTS$
1588
1589 l_return_value := TRUE;
1590
1591 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1592 trace
1593 ( p_module => l_log_module
1594 ,p_msg => ''END '' || l_log_module
1595 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1596 END IF;
1597
1598 RETURN l_return_value;
1599
1600 EXCEPTION
1601 WHEN OTHERS
1602 THEN
1603 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
1604 THEN
1605 trace
1606 ( p_module => l_log_module
1607 ,p_msg => ''apply_adr_rules failed with the error:''
1608 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1609 trace
1610 ( p_module => l_log_module
1611 ,p_msg => SQLERRM
1612 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
1613 END IF;
1614 fnd_message.set_name
1615 (
1616 application => ''XLA''
1617 ,name => ''XLA_TAB_CANNOT_APPLY_ADR_RULES''
1618 );
1619 fnd_message.set_token
1620 (
1621 token => ''FUNCTION_NAME''
1622 ,value => ''$TAD_PACKAGE_NAME_3$.apply_adr_rules''
1623 );
1624 fnd_message.set_token
1625 (
1626 token => ''ERROR''
1627 ,value => SQLERRM
1628 );
1629 fnd_msg_pub.add;
1630 RETURN FALSE;
1631 END apply_adr_rules;
1632
1633 FUNCTION build_code_combinations_dyn
1634 (
1635 p_chart_of_accounts_id IN NUMBER
1636 ,p_chart_of_accounts_name IN VARCHAR2
1637 ,p_flex_delimiter IN VARCHAR2
1638 ,p_concat_segments_template IN VARCHAR2
1639 ,p_table_segment_qualifiers IN xla_cmp_tad_pkg.gt_table_V30_V30
1640 ,p_table_segment_column_names IN xla_cmp_tad_pkg.gt_table_V30
1641 ,p_gl_balancing_segment_name IN VARCHAR2
1642 ,p_gl_account_segment_name IN VARCHAR2
1643 ,p_gl_intercompany_segment_name IN VARCHAR2
1644 ,p_gl_management_segment_name IN VARCHAR2
1645 ,p_fa_cost_ctr_segment_name IN VARCHAR2
1646 )
1647 RETURN BOOLEAN
1648 IS
1649 le_fatal_error EXCEPTION;
1650 TYPE lt_rec_tad_details IS RECORD
1651 (
1652 application_id NUMBER
1653 ,amb_context_code VARCHAR2(30)
1654 ,account_type_code VARCHAR2(30)
1655 ,flexfield_segment_code VARCHAR2(30)
1656 ,segment_rule_type_code VARCHAR2(1)
1657 ,segment_rule_code VARCHAR2(30)
1658 ,object_name_affix VARCHAR2(10)
1659 ,tat_compile_status_code VARCHAR2(1)
1660 ,rule_assignment_code VARCHAR2(30)
1661 ,adr_id NUMBER
1662 );
1663
1664 TYPE lt_table_of_tad_details IS TABLE OF lt_rec_tad_details
1665 INDEX BY BINARY_INTEGER;
1666
1667 l_table_of_tad_details lt_table_of_tad_details;
1668
1669 l_update_statement_text CLOB;
1670 l_update_statements_text CLOB;
1671
1672 l_tmpl_where_segment_null_and CLOB;
1673 l_tmpl_where_segment_null_ands CLOB;
1674 l_tmpl_where_segment_null_or CLOB;
1675 l_tmpl_where_segment_null_ors CLOB;
1676 l_tmpl_upd_set_segment_comma CLOB;
1677 l_tmpl_upd_set_segment_commas CLOB;
1678 l_tmpl_sel_nvl_segment_comma CLOB;
1679 l_tmpl_sel_nvl_segment_commas CLOB;
1680 l_tmpl_where_segments_equal CLOB;
1681 l_tmpl_where_segments_equals CLOB;
1682 l_tmpl_concat_segments CLOB;
1683
1684 l_update_stmts_wrapper_text CLOB;
1685
1686 l_current_object_name_affix VARCHAR2(10);
1687 l_current_temp_table_name VARCHAR2(30);
1688 l_dummy VARCHAR2(30);
1689
1690 l_current_date DATE := TRUNC(SYSDATE);
1691
1692 l_return_value BOOLEAN;
1693 l_fatal_error_message VARCHAR2(2000);
1694 l_log_module VARCHAR2(2000);
1695 BEGIN
1696 IF xla_cmp_tad_pkg.g_log_enabled THEN
1697 l_log_module := g_default_module||''.build_code_combinations_dyn'';
1698 END IF;
1699
1700 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
1701 trace
1702 ( p_module => l_log_module
1703 ,p_msg => ''BEGIN '' || l_log_module
1704 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
1705 END IF;
1706
1707 IF p_chart_of_accounts_id IS NULL
1708 THEN
1709 l_fatal_error_message
1710 := ''build_code_combinations_dyn: p_chart_of_accounts_id cannot be null.'';
1711 RAISE le_fatal_error;
1712 END IF;
1713
1714 --Build the common token values
1715 FOR i IN p_table_segment_column_names.FIRST..p_table_segment_column_names.LAST
1716 LOOP
1717 --C_TMPL_WHERE_SEGMENT_NULL_ANDS
1718 l_tmpl_where_segment_null_and := xla_cmp_string_pkg.replace_token
1719 ( ''
1720 AND gt.$SEGMENT_COLUMN_NAME$ IS NULL ''
1721 ,''$SEGMENT_COLUMN_NAME$''
1722 ,p_table_segment_column_names(i)
1723 );
1724 l_tmpl_where_segment_null_ands := l_tmpl_where_segment_null_ands
1725 || l_tmpl_where_segment_null_and;
1726
1727
1728 --C_TMPL_WHERE_SEGMENT_NULL_ORS
1729
1730 l_tmpl_where_segment_null_or := CASE i
1731 WHEN 1 THEN '' ''
1732 ELSE '' OR ''
1733 END
1734 ||
1735 xla_cmp_string_pkg.replace_token
1736 ( '' gt.$SEGMENT_COLUMN_NAME$ IS NULL
1737 ''
1738 ,''$SEGMENT_COLUMN_NAME$''
1739 ,p_table_segment_column_names(i)
1740 );
1741
1742 l_tmpl_where_segment_null_ors := l_tmpl_where_segment_null_ors
1743 || l_tmpl_where_segment_null_or;
1744
1745 --C_TMPL_UPD_SET_SEGMENT_COMMA
1746 l_tmpl_upd_set_segment_comma := CASE i
1747 WHEN 1 THEN '' ''
1748 ELSE '' ,''
1749 END
1750 ||
1751 xla_cmp_string_pkg.replace_token
1752 ( '' gt.$SEGMENT_COLUMN_NAME$
1753 ''
1754 ,''$SEGMENT_COLUMN_NAME$''
1755 ,p_table_segment_column_names(i)
1756 );
1757
1758 l_tmpl_upd_set_segment_commas := l_tmpl_upd_set_segment_commas
1759 || l_tmpl_upd_set_segment_comma;
1760
1761 --C_TMPL_SEL_NVL_SEGMENT_COMMA
1762 l_tmpl_sel_nvl_segment_comma := CASE i
1763 WHEN 1 THEN '' ''
1764 ELSE '' ,''
1765 END
1766 ||
1767 xla_cmp_string_pkg.replace_token
1768 ( '' NVL(gt.$SEGMENT_COLUMN_NAME$, gcc.$SEGMENT_COLUMN_NAME$)
1769 ''
1770 ,''$SEGMENT_COLUMN_NAME$''
1771 ,p_table_segment_column_names(i)
1772 );
1773
1774 l_tmpl_sel_nvl_segment_commas := l_tmpl_sel_nvl_segment_commas
1775 || l_tmpl_sel_nvl_segment_comma;
1776
1777 --C_TMPL_WHERE_SEGMENTS_EQUAL
1778 l_tmpl_where_segments_equal := xla_cmp_string_pkg.replace_token
1779 ( '' AND gcc.$SEGMENT_COLUMN_NAME$ = gt.$SEGMENT_COLUMN_NAME$
1780 ''
1781 ,''$SEGMENT_COLUMN_NAME$''
1782 ,p_table_segment_column_names(i)
1783 );
1784 l_tmpl_where_segments_equals := l_tmpl_where_segments_equals
1785 || l_tmpl_where_segments_equal;
1786
1787 IF i = 1
1788 THEN
1789 l_tmpl_concat_segments := '' ''
1790 || p_table_segment_column_names(i);
1791 ELSE
1792 l_tmpl_concat_segments := l_tmpl_concat_segments
1793 || ''
1794 ''
1795 || '' || '''''' || p_flex_delimiter || '''''' || ''
1796 || p_table_segment_column_names(i);
1797 END IF;
1798
1799 END LOOP;
1800
1801 --Read the TAD details and corresponding TAT affix
1802 SELECT xtdd.application_id
1803 ,xtdd.amb_context_code
1804 ,xtdd.account_type_code
1805 ,xtdd.flexfield_segment_code
1806 ,xtdd.segment_rule_type_code
1807 ,xtdd.segment_rule_code
1808 ,xtta.object_name_affix
1809 ,xtta.compile_status_code
1810 ,xtta.rule_assignment_code
1811 ,NULL
1812 BULK COLLECT
1813 INTO l_table_of_tad_details
1814 FROM xla_tab_acct_def_details xtdd
1815 ,xla_tab_acct_types_b xtta
1816 WHERE xtdd.application_id = $APPLICATION_ID_2$
1817 AND xtdd.account_definition_code = ''$TAD_CODE_2$''
1818 AND xtdd.account_definition_type_code = ''$TAD_TYPE_CODE_2$''
1819 AND xtdd.amb_context_code = ''$AMB_CONTEXT_CODE_2$''
1820 AND xtta.application_id = xtdd.application_id
1821 AND xtta.account_type_code = xtdd.account_type_code
1822 ORDER BY xtta.object_name_affix
1823 ,xtdd.flexfield_segment_code
1824 ,xtdd.account_type_code;
1825
1826 l_current_object_name_affix := NULL;
1827 FOR i IN l_table_of_tad_details.FIRST .. l_table_of_tad_details.LAST
1828 LOOP
1829 --If it is the first detail
1830 --or the affix changes
1831 --we need a new update statement
1832 IF (i = l_table_of_tad_details.FIRST)
1833 OR ( NVL(l_current_object_name_affix, ''a'')
1834 <> NVL(l_table_of_tad_details(i).object_name_affix, ''a'')
1835 )
1836 THEN
1837 --Concatenate the current upd statement to the existing ones
1838 l_update_statements_text := l_update_statements_text || l_update_statement_text;
1839
1840 --Null out the partial elements that have been consumed now
1841 l_update_statement_text := NULL;
1842
1843 --Retrieve the affix of the TAT associated to the current detail
1844 l_current_object_name_affix := l_table_of_tad_details(i).object_name_affix;
1845
1846 --Get the global temporary table name for the affix
1847 IF NOT xla_cmp_tab_pkg.get_interface_object_names
1848 (
1849 p_application_id => $APPLICATION_ID_2$
1850 ,p_object_name_affix => l_current_object_name_affix
1851 ,x_global_table_name => l_current_temp_table_name
1852 ,x_plsql_table_name => l_dummy
1853 )
1854 THEN
1855 l_fatal_error_message := ''get_interface_object_names failed'';
1856 RAISE le_fatal_error;
1857 END IF;
1858
1859 --Get the update statement template
1860 l_update_statement_text := g_batch_build_ccid_stmts;
1861
1862 --Replace the table name token
1863 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1864 l_update_statement_text
1865 ,''$TABLE_NAME$''
1866 ,NVL(l_current_temp_table_name, '' '')
1867 );
1868
1869 --Replace the common tokens
1870 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1871 l_update_statement_text
1872 ,''$C_TMPL_WHERE_SEGMENT_NULL_ANDS$''
1873 ,NVL(l_tmpl_where_segment_null_ands, '' '')
1874 );
1875
1876 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1877 l_update_statement_text
1878 ,''$C_TMPL_WHERE_SEGMENT_NULL_ORS$''
1879 ,NVL(l_tmpl_where_segment_null_ors, '' '')
1880 );
1881
1882 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1883 l_update_statement_text
1884 ,''$C_TMPL_UPD_SET_SEGMENT_COMMAS$''
1885 ,NVL(l_tmpl_upd_set_segment_commas, '' '')
1886 );
1887
1888 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1889 l_update_statement_text
1890 ,''$C_TMPL_SEL_NVL_SEGMENT_COMMAS$''
1891 ,NVL(l_tmpl_sel_nvl_segment_commas, '' '')
1892 );
1893
1894 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1895 l_update_statement_text
1896 ,''$C_TMPL_WHERE_SEGMENTS_EQUALS$''
1897 ,NVL(l_tmpl_where_segments_equals, '' '')
1898 );
1899
1900 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
1901 l_update_statement_text
1902 ,''$C_TMPL_CONCAT_SEGMENTS$''
1903 ,NVL(l_tmpl_concat_segments, '' '')
1904 );
1905
1906 ELSE
1907 --No action required
1908 NULL;
1909 END IF; --new update statement
1910 END LOOP;
1911
1912 --Concatenate the last processed statement
1913 l_update_statements_text := l_update_statements_text || l_update_statement_text;
1914
1915 l_update_stmts_wrapper_text := ''
1916 DECLARE
1917 l_chart_of_accounts_id NUMBER
1918 := $TRANSACTION_COA_ID$;
1919 l_chart_of_accounts_name VARCHAR2(80)
1920 := ''''$TRANSACTION_COA_NAME$'''';
1921 l_concat_segments_template VARCHAR2(2000)
1922 := ''''$CONCAT_SEGMENTS_TEMPLATE$'''';
1923
1924 PROCEDURE build_ccids_on_the_fly
1925 (
1926 p_chart_of_accounts_id IN NUMBER
1927 ,p_chart_of_accounts_name IN VARCHAR2
1928 ,p_concat_segments_template IN VARCHAR2
1929 )
1930 IS
1931 le_fatal_error EXCEPTION;
1932
1933 l_current_date DATE := TRUNC(SYSDATE);
1934
1935 l_return_value BOOLEAN;
1936 l_fatal_error_message VARCHAR2(2000);
1937
1938 BEGIN
1939 $C_TMPL_BATCH_BUILD_CCID_SQL$
1940 END build_ccids_on_the_fly;
1941 BEGIN
1942 build_ccids_on_the_fly
1943 (
1944 p_chart_of_accounts_id => l_chart_of_accounts_id
1945 ,p_chart_of_accounts_name => l_chart_of_accounts_name
1946 ,p_concat_segments_template => l_concat_segments_template
1947 );
1948
1949 END;
1950
1951 '';
1952
1953 --Replace the p_chart_of_accounts_id with :1
1954 l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
1955 l_update_stmts_wrapper_text
1956 ,''$TRANSACTION_COA_ID$''
1957 ,TO_CHAR(p_chart_of_accounts_id)
1958 );
1959 l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
1960 l_update_stmts_wrapper_text
1961 ,''$TRANSACTION_COA_NAME$''
1962 ,NVL(p_chart_of_accounts_name, '' '')
1963 );
1964 l_update_stmts_wrapper_text:= xla_cmp_string_pkg.replace_token(
1965 l_update_stmts_wrapper_text
1966 ,''$CONCAT_SEGMENTS_TEMPLATE$''
1967 ,NVL(p_concat_segments_template, '' '')
1968 );
1969
1970 --Replace the update statements token
1971 l_update_stmts_wrapper_text := xla_cmp_string_pkg.replace_token
1972 ( l_update_stmts_wrapper_text
1973 ,''$C_TMPL_BATCH_BUILD_CCID_SQL$''
1974 ,l_update_statements_text
1975 );
1976
1977
1978
1979 --Dump the dynamic statement
1980 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level)
1981 THEN
1982 xla_cmp_common_pkg.dump_text
1983 (
1984 p_text => l_update_stmts_wrapper_text
1985 );
1986 END IF;
1987
1988 --Execute the dynamic statement
1989 IF NOT xla_cmp_create_pkg.execute_dml
1990 (
1991 p_dml_text => l_update_stmts_wrapper_text
1992 ,p_msg_mode => xla_cmp_tad_pkg.G_OA_MESSAGE
1993 )
1994 THEN
1995 l_fatal_error_message := ''xla_cmp_create_pkg.execute_dml failed'';
1996 RAISE le_fatal_error;
1997 END IF;
1998
1999 l_return_value := TRUE;
2000
2001 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2002 trace
2003 ( p_module => l_log_module
2004 ,p_msg => ''END '' || l_log_module
2005 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2006 END IF;
2007
2008 RETURN l_return_value;
2009
2010 EXCEPTION
2011 WHEN le_fatal_error
2012 THEN
2013 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2014 THEN
2015 trace
2016 ( p_module => l_log_module
2017 ,p_msg => ''build_code_combinations_dyn failed with the error:''
2018 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2019 trace
2020 ( p_module => l_log_module
2021 ,p_msg => SQLERRM
2022 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2023 END IF;
2024 fnd_message.set_name
2025 (
2026 application => ''XLA''
2027 ,name => ''XLA_TAB_FUN_GENERIC_EXCEPTION''
2028 );
2029 fnd_message.set_token
2030 (
2031 token => ''FUNCTION_NAME''
2032 ,value => ''$TAD_PACKAGE_NAME_3$.build_code_combinations_dyn''
2033 );
2034 fnd_message.set_token
2035 (
2036 token => ''ERROR''
2037 ,value => SQLERRM
2038 );
2039 fnd_msg_pub.add;
2040 RETURN FALSE;
2041 WHEN OTHERS
2042 THEN
2043 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2044 THEN
2045 trace
2046 ( p_module => l_log_module
2047 ,p_msg => ''build_code_combinations_dyn failed with the error:''
2048 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2049 trace
2050 ( p_module => l_log_module
2051 ,p_msg => SQLERRM
2052 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2053 END IF;
2054 fnd_message.set_name
2055 (
2056 application => ''XLA''
2057 ,name => ''XLA_TAB_FUN_GENERIC_EXCEPTION''
2058 );
2059 fnd_message.set_token
2060 (
2061 token => ''FUNCTION_NAME''
2062 ,value => ''$TAD_PACKAGE_NAME_3$.build_code_combinations_dyn''
2063 );
2064 fnd_message.set_token
2065 (
2066 token => ''ERROR''
2067 ,value => SQLERRM
2068 );
2069 fnd_msg_pub.add;
2070 RETURN FALSE;
2071 END build_code_combinations_dyn;
2072 ';
2073
2074 C_TMPL_TAD_PACKAGE_BODY_PART_4 CONSTANT CLOB :=
2075 '
2076 FUNCTION build_code_combinations
2077 (
2078 p_chart_of_accounts_id IN NUMBER
2079 ,p_chart_of_accounts_name IN VARCHAR2
2080 ,p_flex_delimiter IN VARCHAR2
2081 ,p_concat_segments_template IN VARCHAR2
2082 ,p_table_segment_qualifiers IN xla_cmp_tad_pkg.gt_table_V30_V30
2083 ,p_table_segment_column_names IN xla_cmp_tad_pkg.gt_table_V30
2084 ,p_gl_balancing_segment_name IN VARCHAR2
2085 ,p_gl_account_segment_name IN VARCHAR2
2086 ,p_gl_intercompany_segment_name IN VARCHAR2
2087 ,p_gl_management_segment_name IN VARCHAR2
2088 ,p_fa_cost_ctr_segment_name IN VARCHAR2
2089 )
2090 RETURN BOOLEAN
2091 IS
2092 le_fatal_error EXCEPTION;
2093
2094 l_current_date DATE := TRUNC(SYSDATE);
2095
2096 l_return_value BOOLEAN;
2097 l_fatal_error_message VARCHAR2(2000);
2098 l_log_module VARCHAR2(2000);
2099 BEGIN
2100 IF xla_cmp_tad_pkg.g_log_enabled THEN
2101 l_log_module := g_default_module||''.build_code_combinations'';
2102 END IF;
2103
2104 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2105 trace
2106 ( p_module => l_log_module
2107 ,p_msg => ''BEGIN '' || l_log_module
2108 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2109 END IF;
2110
2111 $C_TMPL_BATCH_BUILD_CCID_STMTS$
2112
2113 l_return_value := TRUE;
2114
2115 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2116 trace
2117 ( p_module => l_log_module
2118 ,p_msg => ''END '' || l_log_module
2119 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2120 END IF;
2121
2122 RETURN l_return_value;
2123
2124 EXCEPTION
2125 WHEN le_fatal_error
2126 THEN
2127 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level) THEN
2128 trace
2129 ( p_module => l_log_module
2130 ,p_msg => ''EXCEPTION:''
2131 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2132 trace
2133 ( p_module => l_log_module
2134 ,p_msg => ''Fatal error: '' || l_fatal_error_message
2135 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2136 END IF;
2137 RETURN FALSE;
2138 WHEN OTHERS
2139 THEN
2140 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2141 THEN
2142 trace
2143 ( p_module => l_log_module
2144 ,p_msg => ''build_code_combinations failed with the error:''
2145 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2146 trace
2147 ( p_module => l_log_module
2148 ,p_msg => SQLERRM
2149 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2150 END IF;
2151 fnd_message.set_name
2152 (
2153 application => ''XLA''
2154 ,name => ''XLA_TAB_FUN_GENERIC_EXCEPTION''
2155 );
2156 fnd_message.set_token
2157 (
2158 token => ''FUNCTION_NAME''
2159 ,value => ''$TAD_PACKAGE_NAME_3$.build_code_combinations''
2160 );
2161 fnd_message.set_token
2162 (
2163 token => ''ERROR''
2164 ,value => SQLERRM
2165 );
2166 fnd_msg_pub.add;
2167 RETURN FALSE;
2168 END build_code_combinations;
2169
2170
2171 FUNCTION push_interface_data ( x_total_rows_moved OUT NOCOPY NUMBER)
2172 RETURN BOOLEAN
2173 IS
2174 l_total_rows_moved NUMBER;
2175 le_fatal_error EXCEPTION;
2176 l_return_value BOOLEAN;
2177 l_fatal_error_message VARCHAR2(2000);
2178 l_log_module VARCHAR2(2000);
2179 BEGIN
2180 IF xla_cmp_tad_pkg.g_log_enabled THEN
2181 l_log_module := g_default_module||''.push_interface_data'';
2182 END IF;
2183
2184 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2185 trace
2186 ( p_module => l_log_module
2187 ,p_msg => ''BEGIN '' || l_log_module
2188 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2189 END IF;
2190
2191 l_total_rows_moved := 0;
2192
2193 $C_TMPL_PUSH_INTERF_DATA_STMTS$
2194
2195 l_return_value := TRUE;
2196
2197 --Assign out parameters
2198 x_total_rows_moved := l_total_rows_moved;
2199
2200 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2201 trace
2202 ( p_module => l_log_module
2203 ,p_msg => ''END '' || l_log_module
2204 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2205 END IF;
2206
2207 RETURN l_return_value;
2208
2209 EXCEPTION
2210 WHEN OTHERS
2211 THEN
2212 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2213 THEN
2214 trace
2215 ( p_module => l_log_module
2216 ,p_msg => ''push_interface_data failed with the error:''
2217 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2218 trace
2219 ( p_module => l_log_module
2220 ,p_msg => SQLERRM
2221 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2222 END IF;
2223 fnd_message.set_name
2224 (
2225 application => ''XLA''
2226 ,name => ''XLA_TAB_FUN_GENERIC_EXCEPTION''
2227 );
2228 fnd_message.set_token
2229 (
2230 token => ''FUNCTION_NAME''
2231 ,value => ''$TAD_PACKAGE_NAME_3$.push_interface_data''
2232 );
2233 fnd_message.set_token
2234 (
2235 token => ''ERROR''
2236 ,value => SQLERRM
2237 );
2238 fnd_msg_pub.add;
2239 RETURN FALSE;
2240 END push_interface_data;
2241
2242 FUNCTION pop_interface_data ( x_total_rows_moved OUT NOCOPY NUMBER)
2243 RETURN BOOLEAN
2244 IS
2245 l_total_rows_moved NUMBER;
2246 le_fatal_error EXCEPTION;
2247 l_return_value BOOLEAN;
2248 l_fatal_error_message VARCHAR2(2000);
2249 l_log_module VARCHAR2(2000);
2250 BEGIN
2251 IF xla_cmp_tad_pkg.g_log_enabled THEN
2252 l_log_module := g_default_module||''.pop_interface_data'';
2253 END IF;
2254
2255 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2256 trace
2257 ( p_module => l_log_module
2258 ,p_msg => ''BEGIN '' || l_log_module
2259 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2260 END IF;
2261
2262 l_total_rows_moved := 0;
2263
2264 $C_TMPL_POP_INTERF_DATA_STMTS$
2265
2266 l_return_value := TRUE;
2267
2268 --Assign out parameters
2269 x_total_rows_moved := l_total_rows_moved;
2270
2271 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2272 trace
2273 ( p_module => l_log_module
2274 ,p_msg => ''END '' || l_log_module
2275 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2276 END IF;
2277
2278 RETURN l_return_value;
2279
2280 EXCEPTION
2281 WHEN OTHERS
2282 THEN
2283 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2284 THEN
2285 trace
2286 ( p_module => l_log_module
2287 ,p_msg => ''pop_interface_data failed with the error:''
2288 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2289 trace
2290 ( p_module => l_log_module
2291 ,p_msg => SQLERRM
2292 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2293 END IF;
2294 fnd_message.set_name
2295 (
2296 application => ''XLA''
2297 ,name => ''XLA_TAB_FUN_GENERIC_EXCEPTION''
2298 );
2299 fnd_message.set_token
2300 (
2301 token => ''FUNCTION_NAME''
2302 ,value => ''$TAD_PACKAGE_NAME_3$.pop_interface_data''
2303 );
2304 fnd_message.set_token
2305 (
2306 token => ''ERROR''
2307 ,value => SQLERRM
2308 );
2309 fnd_msg_pub.add;
2310 RETURN FALSE;
2311 END pop_interface_data;
2312
2313
2314 --Public procedures
2315
2316 PROCEDURE trans_account_def_online
2317 (
2318 p_transaction_coa_id IN NUMBER
2319 ,p_accounting_coa_id IN NUMBER
2320 ,x_return_status OUT NOCOPY VARCHAR2
2321 ,x_msg_count OUT NOCOPY NUMBER
2322 ,x_msg_data OUT NOCOPY VARCHAR2
2323 )
2324 IS
2325 le_fatal_error EXCEPTION;
2326 l_return_msg_name VARCHAR2(30);
2327
2328 l_table_segment_qualifiers xla_cmp_tad_pkg.gt_table_V30_V30;
2329 l_table_segment_column_names xla_cmp_tad_pkg.gt_table_V30;
2330 l_flex_delimiter VARCHAR2(1);
2331 l_concat_segments_template VARCHAR2(1000);
2332
2333 l_gl_balancing_segment_name VARCHAR2(30);
2334 l_gl_account_segment_name VARCHAR2(30);
2335 l_gl_intercompany_segment_name VARCHAR2(30);
2336 l_gl_management_segment_name VARCHAR2(30);
2337 l_fa_cost_ctr_segment_name VARCHAR2(30);
2338
2339 l_chart_of_accounts_name VARCHAR2(80);
2340
2341 l_current_date DATE := TRUNC(SYSDATE);
2342
2343 l_total_rows_pushed NUMBER;
2344 l_total_rows_popped NUMBER;
2345
2346 l_fatal_error_message VARCHAR2(2000);
2347 l_log_module VARCHAR2(2000);
2348
2349 BEGIN
2350 IF xla_cmp_tad_pkg.g_log_enabled THEN
2351 l_log_module := g_default_module||''.trans_account_def_online'';
2352 END IF;
2353
2354 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2355 trace
2356 ( p_module => l_log_module
2357 ,p_msg => ''BEGIN '' || l_log_module
2358 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2359 END IF;
2360
2361 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
2362
2363 trace( p_module => l_log_module
2364 ,p_msg => ''p_transaction_coa_id : '' || p_transaction_coa_id
2365 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2366
2367 trace( p_module => l_log_module
2368 ,p_msg => ''p_accounting_coa_id : '' || p_accounting_coa_id
2369 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2370
2371 END IF;
2372
2373
2374 --Retrieve the Chart Of Accounts information
2375 IF NOT get_coa_info
2376 (
2377 p_chart_of_accounts_id => p_transaction_coa_id
2378 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2379 ,p_flex_delimiter => l_flex_delimiter
2380 ,p_concat_segments_template => l_concat_segments_template
2381 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2382 ,p_gl_account_segment_name => l_gl_account_segment_name
2383 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2384 ,p_gl_management_segment_name => l_gl_management_segment_name
2385 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2386 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2387 ,p_table_segment_column_names => l_table_segment_column_names
2388 )
2389 THEN
2390 l_fatal_error_message := ''get_coa_info failed'';
2391 RAISE le_fatal_error;
2392 END IF;
2393
2394 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
2395
2396 trace( p_module => l_log_module
2397 ,p_msg => ''l_gl_balancing_segment_name: '' || l_gl_balancing_segment_name
2398 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2399
2400 trace( p_module => l_log_module
2401 ,p_msg => ''l_gl_account_segment_name: '' || l_gl_account_segment_name
2402 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2403
2404 END IF;
2405
2406
2407 --Move the data from the plsql tables into the global temporary tables
2408 IF NOT push_interface_data (x_total_rows_moved => l_total_rows_pushed)
2409 THEN
2410 fnd_message.set_name
2411 (
2412 application => ''XLA''
2413 ,name => ''XLA_TAB_FATAL_ERROR''
2414 );
2415 fnd_message.set_token
2416 (
2417 token => ''FUNCTION_NAME''
2418 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2419 );
2420 fnd_msg_pub.add;
2421
2422 l_fatal_error_message := ''push_interface_data failed'';
2423 RAISE le_fatal_error;
2424 END IF;
2425
2426 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level)
2427 THEN
2428 trace
2429 ( p_module => l_log_module
2430 ,p_msg => ''Number of rows pushed: '' || l_total_rows_pushed
2431 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2432 END IF;
2433
2434 --If no rows could be pushed then raise an error
2435 IF l_total_rows_pushed = 0
2436 THEN
2437 fnd_message.set_name
2438 (
2439 application => ''XLA''
2440 ,name => ''XLA_TAB_NO_ROWS_ONLINE_INTERF''
2441 );
2442 fnd_message.set_token
2443 (
2444 token => ''FUNCTION_NAME''
2445 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2446 );
2447 fnd_msg_pub.add;
2448
2449 --Document the exception
2450 l_fatal_error_message :=
2451 ''No rows present in the inline interface, aborting...'';
2452 RAISE le_fatal_error;
2453 END IF;
2454
2455 --Ensure that global temp tables are empty so that
2456 --successive calls from OAF are allowed in case of errors
2457 --and no rollback in-between.
2458 DELETE
2459 FROM xla_tab_errors_gt;
2460
2461 DELETE
2462 FROM xla_tab_new_ccids_gt;
2463
2464 --Apply flex and segment ADR Rules on each interface object
2465 IF NOT apply_adr_rules
2466 (
2467 p_chart_of_accounts_id => p_transaction_coa_id
2468 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2469 ,p_flex_delimiter => l_flex_delimiter
2470 ,p_concat_segments_template => l_concat_segments_template
2471 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2472 ,p_table_segment_column_names => l_table_segment_column_names
2473 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2474 ,p_gl_account_segment_name => l_gl_account_segment_name
2475 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2476 ,p_gl_management_segment_name => l_gl_management_segment_name
2477 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2478 )
2479 THEN
2480 fnd_message.set_name
2481 (
2482 application => ''XLA''
2483 ,name => ''XLA_TAB_FATAL_ERROR''
2484 );
2485 fnd_message.set_token
2486 (
2487 token => ''FUNCTION_NAME''
2488 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2489 );
2490 fnd_msg_pub.add;
2491
2492 l_fatal_error_message := ''apply_adr_rules failed'';
2493 RAISE le_fatal_error;
2494 END IF;
2495
2496 --Build the code combinations
2497 IF NOT build_code_combinations
2498 (
2499 p_chart_of_accounts_id => p_transaction_coa_id
2500 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2501 ,p_flex_delimiter => l_flex_delimiter
2502 ,p_concat_segments_template => l_concat_segments_template
2503 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2504 ,p_table_segment_column_names => l_table_segment_column_names
2505 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2506 ,p_gl_account_segment_name => l_gl_account_segment_name
2507 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2508 ,p_gl_management_segment_name => l_gl_management_segment_name
2509 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2510 )
2511 THEN
2512 fnd_message.set_name
2513 (
2514 application => ''XLA''
2515 ,name => ''XLA_TAB_FATAL_ERROR''
2516 );
2517 fnd_message.set_token
2518 (
2519 token => ''FUNCTION_NAME''
2520 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2521 );
2522 fnd_msg_pub.add;
2523
2524 l_fatal_error_message := ''build_code_combinations failed'';
2525 RAISE le_fatal_error;
2526 END IF;
2527
2528 --Move the data from the the global temporary tables back
2529 --into the global temp tables
2530 IF NOT pop_interface_data(x_total_rows_moved => l_total_rows_popped)
2531 THEN
2532 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2533 THEN
2534 trace
2535 ( p_module => l_log_module
2536 ,p_msg => ''push_interface_data failed''
2537 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2538 END IF;
2539 fnd_message.set_name
2540 (
2541 application => ''XLA''
2542 ,name => ''XLA_TAB_FATAL_ERROR''
2543 );
2544 fnd_message.set_token
2545 (
2546 token => ''FUNCTION_NAME''
2547 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2548 );
2549 fnd_msg_pub.add;
2550 l_fatal_error_message := ''pop_interface_data failed'';
2551 RAISE le_fatal_error;
2552 END IF;
2553
2554 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level)
2555 THEN
2556 trace
2557 ( p_module => l_log_module
2558 ,p_msg => ''Number of rows retrieved: '' || l_total_rows_popped
2559 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2560 END IF;
2561
2562 IF l_total_rows_pushed <> l_total_rows_popped
2563 THEN
2564 fnd_message.set_name
2565 (
2566 application => ''XLA''
2567 ,name => ''XLA_TAB_ROWS_MIS_ONLINE_INTERF''
2568 );
2569 fnd_message.set_token
2570 (
2571 token => ''TAB_ROWS_PUSHED''
2572 ,value => l_total_rows_pushed
2573 );
2574 fnd_message.set_token
2575 (
2576 token => ''TAB_ROWS_POPPED''
2577 ,value => l_total_rows_popped
2578 );
2579 fnd_message.set_token
2580 (
2581 token => ''FUNCTION_NAME''
2582 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_online''
2583 );
2584 fnd_msg_pub.add;
2585
2586 l_fatal_error_message := ''Rows pushed('' || l_total_rows_pushed
2587 || '') <> Rows popped(''
2588 || l_total_rows_popped || '')'';
2589 RAISE le_fatal_error;
2590
2591 END IF;
2592
2593 --Assign return status
2594 x_return_status := xla_cmp_tad_pkg.C_RET_STS_SUCCESS;
2595
2596 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2597 trace
2598 ( p_module => l_log_module
2599 ,p_msg => ''END '' || l_log_module
2600 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2601 END IF;
2602
2603 EXCEPTION
2604 WHEN le_fatal_error
2605 THEN
2606 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2607 THEN
2608 trace
2609 ( p_module => l_log_module
2610 ,p_msg => ''EXCEPTION:''
2611 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2612 trace
2613 ( p_module => l_log_module
2614 ,p_msg => ''Fatal error: '' || l_fatal_error_message
2615 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2616 END IF;
2617 IF x_return_status IS NULL
2618 THEN
2619 x_return_status := xla_cmp_tad_pkg.C_RET_STS_UNEXP_ERROR;
2620 END IF;
2621 IF l_return_msg_name IS NOT NULL
2622 THEN
2623 --There is a detailed message to push
2624 fnd_message.set_name
2625 (
2626 application => ''XLA''
2627 ,name => l_return_msg_name
2628 );
2629 fnd_msg_pub.add;
2630 END IF;
2631 fnd_msg_pub.Count_And_Get
2632 (
2633 p_count => x_msg_count
2634 ,p_data => x_msg_data
2635 );
2636 --for Forms callers
2637 fnd_message.set_encoded
2638 (
2639 encoded_message => x_msg_data
2640 );
2641 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2642 trace
2643 (p_module => l_log_module
2644 ,p_msg => ''END '' || l_log_module
2645 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2646 END IF;
2647 WHEN OTHERS
2648 THEN
2649 fnd_message.set_name(''XLA'', ''XLA_TAB_UNHANDLED_EXCEPTION'');
2650 fnd_message.set_token( ''PROCEDURE''
2651 ,''$TAD_PACKAGE_NAME_3$.trans_account_def_online'');
2652 RAISE;
2653 END trans_account_def_online;
2654
2655
2656 PROCEDURE trans_account_def_batch
2657 (
2658 p_transaction_coa_id IN NUMBER
2659 ,p_accounting_coa_id IN NUMBER
2660 ,x_return_status OUT NOCOPY VARCHAR2
2661 ,x_msg_count OUT NOCOPY NUMBER
2662 ,x_msg_data OUT NOCOPY VARCHAR2
2663 )
2664 IS
2665 le_fatal_error EXCEPTION;
2666 l_return_msg_name VARCHAR2(30);
2667
2668 l_table_segment_qualifiers xla_cmp_tad_pkg.gt_table_V30_V30;
2669 l_table_segment_column_names xla_cmp_tad_pkg.gt_table_V30;
2670 l_flex_delimiter VARCHAR2(1);
2671 l_concat_segments_template VARCHAR2(1000);
2672
2673 l_gl_balancing_segment_name VARCHAR2(30);
2674 l_gl_account_segment_name VARCHAR2(30);
2675 l_gl_intercompany_segment_name VARCHAR2(30);
2676 l_gl_management_segment_name VARCHAR2(30);
2677 l_fa_cost_ctr_segment_name VARCHAR2(30);
2678
2679 l_chart_of_accounts_name VARCHAR2(80);
2680
2681 l_current_date DATE := TRUNC(SYSDATE);
2682
2683 l_fatal_error_message VARCHAR2(2000);
2684 l_log_module VARCHAR2(2000);
2685
2686 BEGIN
2687 IF xla_cmp_tad_pkg.g_log_enabled THEN
2688 l_log_module := g_default_module||''.trans_account_def_batch'';
2689 END IF;
2690
2691 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2692 trace
2693 ( p_module => l_log_module
2694 ,p_msg => ''BEGIN '' || l_log_module
2695 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2696 END IF;
2697
2698 --Retrieve the Chart Of Accounts information
2699 IF NOT get_coa_info
2700 (
2701 p_chart_of_accounts_id => p_transaction_coa_id
2702 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2703 ,p_flex_delimiter => l_flex_delimiter
2704 ,p_concat_segments_template => l_concat_segments_template
2705 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2706 ,p_gl_account_segment_name => l_gl_account_segment_name
2707 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2708 ,p_gl_management_segment_name => l_gl_management_segment_name
2709 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2710 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2711 ,p_table_segment_column_names => l_table_segment_column_names
2712 )
2713 THEN
2714 l_fatal_error_message := ''Unable to get Chart Of Account Info'';
2715 RAISE le_fatal_error;
2716 END IF;
2717
2718 --Apply flex and segment ADR Rules on each interface object
2719 IF NOT apply_adr_rules
2720 (
2721 p_chart_of_accounts_id => p_transaction_coa_id
2722 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2723 ,p_flex_delimiter => l_flex_delimiter
2724 ,p_concat_segments_template => l_concat_segments_template
2725 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2726 ,p_table_segment_column_names => l_table_segment_column_names
2727 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2728 ,p_gl_account_segment_name => l_gl_account_segment_name
2729 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2730 ,p_gl_management_segment_name => l_gl_management_segment_name
2731 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2732 )
2733 THEN
2734 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2735 THEN
2736 trace
2737 ( p_module => l_log_module
2738 ,p_msg => ''apply_adr_rules failed''
2739 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2740 END IF;
2741 fnd_message.set_name
2742 (
2743 application => ''XLA''
2744 ,name => ''XLA_TAB_FATAL_ERROR''
2745 );
2746 fnd_message.set_token
2747 (
2748 token => ''FUNCTION_NAME''
2749 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_batch''
2750 );
2751 fnd_msg_pub.add;
2752 RAISE le_fatal_error;
2753 END IF;
2754
2755 --Build the code combinations
2756 IF NOT build_code_combinations
2757 (
2758 p_chart_of_accounts_id => p_transaction_coa_id
2759 ,p_chart_of_accounts_name => l_chart_of_accounts_name
2760 ,p_flex_delimiter => l_flex_delimiter
2761 ,p_concat_segments_template => l_concat_segments_template
2762 ,p_table_segment_qualifiers => l_table_segment_qualifiers
2763 ,p_table_segment_column_names => l_table_segment_column_names
2764 ,p_gl_balancing_segment_name => l_gl_balancing_segment_name
2765 ,p_gl_account_segment_name => l_gl_account_segment_name
2766 ,p_gl_intercompany_segment_name => l_gl_intercompany_segment_name
2767 ,p_gl_management_segment_name => l_gl_management_segment_name
2768 ,p_fa_cost_ctr_segment_name => l_fa_cost_ctr_segment_name
2769 )
2770 THEN
2771 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2772 THEN
2773 trace
2774 ( p_module => l_log_module
2775 ,p_msg => ''build_code_combinations failed''
2776 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2777 END IF;
2778 fnd_message.set_name
2779 (
2780 application => ''XLA''
2781 ,name => ''XLA_TAB_FATAL_ERROR''
2782 );
2783 fnd_message.set_token
2784 (
2785 token => ''FUNCTION_NAME''
2786 ,value => ''$TAD_PACKAGE_NAME_3$.trans_account_def_batch''
2787 );
2788 fnd_msg_pub.add;
2789 RAISE le_fatal_error;
2790 END IF;
2791
2792 --Assign return status
2793 x_return_status := xla_cmp_tad_pkg.C_RET_STS_SUCCESS;
2794
2795 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2796 trace
2797 ( p_module => l_log_module
2798 ,p_msg => ''END '' || l_log_module
2799 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2800 END IF;
2801
2802 EXCEPTION
2803 WHEN le_fatal_error
2804 THEN
2805 IF (xla_cmp_tad_pkg.C_LEVEL_EXCEPTION >= xla_cmp_tad_pkg.g_log_level)
2806 THEN
2807 trace
2808 ( p_module => l_log_module
2809 ,p_msg => ''EXCEPTION:''
2810 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2811 trace
2812 ( p_module => l_log_module
2813 ,p_msg => ''Fatal error: '' || l_fatal_error_message
2814 ,p_level => xla_cmp_tad_pkg.C_LEVEL_EXCEPTION);
2815 END IF;
2816 IF x_return_status IS NULL
2817 THEN
2818 x_return_status := xla_cmp_tad_pkg.C_RET_STS_UNEXP_ERROR;
2819 END IF;
2820 IF l_return_msg_name IS NOT NULL
2821 THEN
2822 --There is a detailed message to push
2823 fnd_message.set_name
2824 (
2825 application => ''XLA''
2826 ,name => l_return_msg_name
2827 );
2828 fnd_msg_pub.add;
2829 END IF;
2830 fnd_msg_pub.Count_And_Get
2831 (
2832 p_count => x_msg_count
2833 ,p_data => x_msg_data
2834 );
2835 --for Forms callers
2836 fnd_message.set_encoded
2837 (
2838 encoded_message => x_msg_data
2839 );
2840 IF (xla_cmp_tad_pkg.C_LEVEL_PROCEDURE >= xla_cmp_tad_pkg.g_log_level) THEN
2841 trace
2842 (p_module => l_log_module
2843 ,p_msg => ''END '' || l_log_module
2844 ,p_level => xla_cmp_tad_pkg.C_LEVEL_PROCEDURE);
2845 END IF;
2846 WHEN OTHERS
2847 THEN
2848 fnd_message.set_name(''XLA'', ''XLA_TAB_UNHANDLED_EXCEPTION'');
2849 fnd_message.set_token( ''PROCEDURE''
2850 ,''$TAD_PACKAGE_NAME_3$.trans_account_def_batch'');
2851 RAISE;
2852 END trans_account_def_batch;
2853
2854
2855
2856 --Trace initialization
2857 BEGIN
2858 xla_cmp_tad_pkg.g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2859 xla_cmp_tad_pkg.g_log_enabled := fnd_log.test
2860 (log_level => xla_cmp_tad_pkg.g_log_level
2861 ,module => g_default_module);
2862
2863 IF NOT xla_cmp_tad_pkg.g_log_enabled THEN
2864 xla_cmp_tad_pkg.g_log_level := xla_cmp_tad_pkg.C_LEVEL_LOG_DISABLED;
2865 END IF;
2866
2867
2868 END $TAD_PACKAGE_NAME_1$;
2869 ';
2870
2871
2872 C_TMPL_TAD_PACKAGE_BODY CONSTANT CLOB := C_TMPL_TAD_PACKAGE_BODY_PART_1
2873 || C_TMPL_TAD_PACKAGE_BODY_PART_2
2874 || C_TMPL_TAD_PACKAGE_BODY_PART_3
2875 || C_TMPL_TAD_PACKAGE_BODY_PART_4;
2876
2877 --+==========================================================================+
2878 --| end of package body template |
2879 --+==========================================================================+
2880
2881
2882 C_TMPL_PUSH_INTERF_DATA_STMT CONSTANT CLOB :=
2883 '
2884 IF $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$ IS NOT NULL
2885 AND $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.count > 0
2886 THEN
2887 --Dump the content of the PL/SQL table
2888 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
2889
2890 trace
2891 ( p_module => l_log_module
2892 ,p_msg =>
2893 ''Dumping content of PL/SQL table $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$''
2894 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2895
2896 trace
2897 ( p_module => l_log_module
2898 ,p_msg =>
2899 ''src_distr1, src_distr2, src_distr3, src_distr4, src_distr5, account_type_code, target_ccid''
2900 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2901
2902
2903 FOR i IN $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.FIRST..$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.LAST
2904 LOOP
2905
2906 trace
2907 ( p_module => l_log_module
2908 ,p_msg => ''Line '' || i || '': ''
2909 ||$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_1
2910 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_2
2911 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_3
2912 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_4
2913 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_5
2914 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).account_type_code
2915 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).target_ccid
2916 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2917
2918 END LOOP;
2919 END IF; --Trace statement
2920
2921
2922 --Ensure the global temporary table is empty
2923 DELETE
2924 FROM $TABLE_NAME$;
2925
2926 --A named binding would be preferable but either
2927 --we would encounter the error
2928 --PLS-00436 implementation restriction: cannot reference fields of BULK In-BIND
2929 --table of records
2930 --or we give up bulk binding
2931 --
2932 --Added the column dummy_rowid to fix bug4344773
2933 FORALL i IN $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.FIRST..$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.LAST
2934 INSERT
2935 INTO
2936 ( SELECT gt.DUMMY_ROWID
2937 ,gt.SOURCE_DISTRIBUTION_ID_NUM_1
2938 ,gt.SOURCE_DISTRIBUTION_ID_NUM_2
2939 ,gt.SOURCE_DISTRIBUTION_ID_NUM_3
2940 ,gt.SOURCE_DISTRIBUTION_ID_NUM_4
2941 ,gt.SOURCE_DISTRIBUTION_ID_NUM_5
2942 ,gt.ACCOUNT_TYPE_CODE
2943 --START of source list$C_TMPL_TAB_PUSH_INTERF_SOURCES$
2944 --END of source list
2945 ,gt.TARGET_CCID
2946 ,gt.CONCATENATED_SEGMENTS
2947 ,gt.MSG_COUNT
2948 ,gt.MSG_DATA
2949 FROM $TABLE_NAME$ gt
2950 )
2951 VALUES
2952 $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i);
2953
2954 l_total_rows_moved := l_total_rows_moved + SQL%ROWCOUNT;
2955
2956 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
2957 trace
2958 ( p_module => l_log_module
2959 ,p_msg => SQL%ROWCOUNT
2960 || '' row(s) inserted into $TABLE_NAME$''
2961 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
2962 END IF;
2963
2964 --Delete all the elements of the PLSQL table
2965 $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.DELETE;
2966
2967 END IF;
2968 ';
2969
2970 C_TMPL_POP_INTERF_DATA_STMT CONSTANT CLOB :=
2971 '
2972 IF $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.COUNT = 0
2973 THEN
2974 --A named binding would be preferable but either
2975 --we would encounter the error
2976 --PLS-00436 implementation restriction: cannot reference fields of BULK In-BIND
2977 --table of records
2978 --or we give up bulk binding
2979 SELECT ROWID
2980 ,SOURCE_DISTRIBUTION_ID_NUM_1
2981 ,SOURCE_DISTRIBUTION_ID_NUM_2
2982 ,SOURCE_DISTRIBUTION_ID_NUM_3
2983 ,SOURCE_DISTRIBUTION_ID_NUM_4
2984 ,SOURCE_DISTRIBUTION_ID_NUM_5
2985 ,ACCOUNT_TYPE_CODE
2986 --START of source list$C_TMPL_TAB_POP_INTERF_SOURCES$
2987 --END of source list
2988 ,TARGET_CCID
2989 ,CONCATENATED_SEGMENTS
2990 ,MSG_COUNT
2991 ,MSG_DATA
2992 BULK COLLECT
2993 INTO $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$
2994 FROM $TABLE_NAME$ gt;
2995
2996 l_total_rows_moved := l_total_rows_moved + SQL%ROWCOUNT;
2997
2998 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
2999 trace
3000 (p_module => l_log_module
3001 ,p_msg => SQL%ROWCOUNT
3002 || '' row(s) read from $TABLE_NAME$''
3003 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3004 END IF;
3005
3006 --Dump the content of the PL/SQL table
3007 IF (xla_cmp_tad_pkg.C_LEVEL_STATEMENT >= xla_cmp_tad_pkg.g_log_level) THEN
3008
3009 trace
3010 ( p_module => l_log_module
3011 ,p_msg =>
3012 ''Dumping content of PL/SQL table $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$''
3013 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3014
3015 trace
3016 ( p_module => l_log_module
3017 ,p_msg =>
3018 ''src_distr1, src_distr2, src_distr3, src_distr4, ''
3019 ||''src_distr5, account_type_code, target_ccid, msg_count, msg_data, ''
3020 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3021
3022 trace
3023 ( p_module => l_log_module
3024 ,p_msg => ''concatenated segments''
3025 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3026
3027 FOR i IN $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.FIRST..$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$.LAST
3028 LOOP
3029
3030 trace
3031 ( p_module => l_log_module
3032 ,p_msg => ''Line '' || i || '': ''
3033 ||$TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_1
3034 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_2
3035 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_3
3036 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_4
3037 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).source_distribution_id_num_5
3038 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).account_type_code
3039 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).target_ccid
3040 ||'','' || $TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).msg_count
3041 ||'','' || SUBSTR($TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).msg_data, 1, 20)
3042 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3043 trace
3044 ( p_module => l_log_module
3045 ,p_msg =>
3046 SUBSTR($TAB_API_PACKAGE_NAME$.$PLSQL_TABLE_NAME$(i).concatenated_segments, 1, 25)
3047 ,p_level => xla_cmp_tad_pkg.C_LEVEL_STATEMENT);
3048
3049 END LOOP;
3050 END IF; --Trace statement
3051
3052 END IF;
3053 ';
3054
3055
3056 C_TMPL_TAB_PUSH_INTERF_SOURCE CONSTANT CLOB :=
3057 '
3058 ,$SOURCE_CODE$';
3059 C_TMPL_TAB_POP_INTERF_SOURCE CONSTANT CLOB :=
3060 '
3061 ,$SOURCE_CODE$';
3062
3063
3064 --$TAD_BATCH_CCID_SEG_UPD_STMTS$
3065 C_TMPL_BATCH_CCID_SEG_UPD_STMT CONSTANT CLOB :=
3066 '
3067 UPDATE $table_name$ gt
3068 SET
3069 $C_TMPL_SET_CLAUSES$;
3070 ';
3071
3072 C_TMPL_SET_CCID CONSTANT CLOB :=
3073 'gt.target_ccid
3074 = CASE gt.account_type_code
3075 $CASE_BRANCHES$
3076 END
3077 ';
3078
3079 C_TMPL_SET_SEGMENT CONSTANT CLOB :=
3080 'gt.$SEGMENT_COLUMN_NAME$
3081 = CASE gt.account_type_code
3082 $CASE_BRANCHES$
3083 END
3084 ';
3085
3086
3087 C_TMPL_CASE_BRANCH CONSTANT CLOB :=
3088 ' WHEN ''$ACCOUNT_TYPE$''
3089 THEN $TAD_PACKAGE_NAME_1$.$ADR_FUNCT_NAME$
3090 (
3091 ''BATCH'' --p_mode
3092 ,ROWID --p_rowid
3093 ,NULL --p_line_index
3094 ,p_chart_of_accounts_id --p_chart_of_accounts_id
3095 ,p_chart_of_accounts_name --p_chart_of_accounts_name
3096 ,p_gl_balancing_segment_name --p_gl_balancing_segment_name
3097 ,p_gl_account_segment_name --p_gl_account_segment_name
3098 ,p_gl_intercompany_segment_name --p_gl_intercompany_segment_name
3099 ,p_gl_management_segment_name --p_gl_management_segment_name
3100 ,p_fa_cost_ctr_segment_name --p_fa_cost_ctr_segment_name
3101 ,l_current_date --p_validation_date
3102 $C_TMPL_ADR_FUNCT_PARAMS$
3103 )
3104 ';
3105
3106
3107
3108
3109 --
3110 -- Global variables
3111 --
3112 g_user_id CONSTANT INTEGER
3113 := xla_environment_pkg.g_usr_id;
3114 g_login_id CONSTANT INTEGER
3115 := xla_environment_pkg.g_login_id;
3116 g_prog_appl_id CONSTANT INTEGER
3117 := xla_environment_pkg.g_prog_appl_id;
3118 g_prog_id CONSTANT INTEGER
3119 := xla_environment_pkg.g_prog_id;
3120 g_req_id CONSTANT INTEGER
3121 := NVL(xla_environment_pkg.g_req_id, -1);
3122
3123 --Set the message mode to use the message stack instead of raising an exception
3124 g_msg_mode CONSTANT VARCHAR2(1) := G_OA_MESSAGE;
3125
3126 g_application_info xla_cmp_common_pkg.lt_application_info;
3127 g_user_name VARCHAR2(2000); --100 in the table
3128
3129 --
3130
3131 -- Cursor declarations
3132 --
3133
3134
3135
3136 --=============================================================================
3137 -- *********** Local Trace Routine **********
3138 --=============================================================================
3139 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_cmp_tad_pkg';
3140
3141 PROCEDURE trace
3142 ( p_module IN VARCHAR2
3143 ,p_msg IN VARCHAR2
3144 ,p_level IN NUMBER
3145 ) IS
3146 BEGIN
3147 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
3148 fnd_log.message(p_level, p_module);
3149 ELSIF p_level >= g_log_level THEN
3150 fnd_log.string(p_level, p_module, p_msg);
3151 END IF;
3152
3153 EXCEPTION
3154 WHEN xla_exceptions_pkg.application_exception THEN
3155 RAISE;
3156 WHEN OTHERS THEN
3157 xla_exceptions_pkg.raise_message
3158 ( p_location => 'xla_cmp_tad_pkg.trace'
3159 ,p_msg_mode => g_msg_mode
3160 );
3161 END trace;
3162
3163
3164 --Forward declarations of private functions
3165 FUNCTION init_global_variables
3166 ( p_application_id IN NUMBER
3167 )
3168 RETURN BOOLEAN;
3169
3170 FUNCTION get_tad_package_name
3171 (
3172 p_account_definition_code IN VARCHAR2
3173 ,p_account_definition_type_code IN VARCHAR2
3174 ,p_amb_context_code IN VARCHAR2
3175 ,p_tad_package_name OUT NOCOPY VARCHAR2
3176 ,p_chart_of_accounts_id OUT NOCOPY NUMBER
3177 )
3178 RETURN BOOLEAN;
3179
3180 FUNCTION create_package_spec
3181 (
3182 p_account_definition_code IN VARCHAR2
3183 ,p_account_definition_type_code IN VARCHAR2
3184 ,p_amb_context_code IN VARCHAR2
3185 ,p_package_name IN VARCHAR2
3186 ,p_table_of_tad_details IN gt_table_of_tad_details
3187 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
3188 ,p_adr_specs_text IN CLOB
3189 )
3190 RETURN BOOLEAN;
3191
3192 FUNCTION create_package_body
3193 (
3194 p_account_definition_code IN VARCHAR2
3195 ,p_account_definition_type_code IN VARCHAR2
3196 ,p_amb_context_code IN VARCHAR2
3197 ,p_package_name IN VARCHAR2
3198 ,p_tad_coa_id IN NUMBER
3199 ,p_table_of_tad_details IN gt_table_of_tad_details
3200 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
3201 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
3202 ,p_adr_bodies_text IN CLOB
3203 )
3204 RETURN BOOLEAN;
3205
3206 FUNCTION build_package_spec
3207 (
3208 p_account_definition_code IN VARCHAR2
3209 ,p_account_definition_type_code IN VARCHAR2
3210 ,p_amb_context_code IN VARCHAR2
3211 ,p_package_name IN VARCHAR2
3212 ,p_table_of_tad_details IN gt_table_of_tad_details
3213 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
3214 ,p_adr_specs_text IN CLOB
3215 ,p_package_spec_text OUT NOCOPY CLOB
3216 )
3217 RETURN BOOLEAN;
3218
3219 FUNCTION build_package_body
3220 (
3221 p_account_definition_code IN VARCHAR2
3222 ,p_account_definition_type_code IN VARCHAR2
3223 ,p_amb_context_code IN VARCHAR2
3224 ,p_package_name IN VARCHAR2
3225 ,p_tad_coa_id IN NUMBER
3226 ,p_table_of_tad_details IN gt_table_of_tad_details
3227 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
3228 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
3229 ,p_adr_bodies_text IN CLOB
3230 ,p_package_body_text OUT NOCOPY CLOB
3231 )
3232 RETURN BOOLEAN;
3233
3234 FUNCTION build_package_history
3235 (
3236 p_package_history OUT NOCOPY CLOB
3237 )
3238 RETURN BOOLEAN;
3239
3240 FUNCTION build_batch_update_statements
3241 (
3242 p_table_of_tad_details IN gt_table_of_tad_details
3243 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
3244 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
3245 ,p_update_statements_text OUT NOCOPY CLOB
3246 )
3247 RETURN BOOLEAN;
3248
3249 FUNCTION build_static_ccid_prc_stmts
3250 (
3251 p_table_of_tad_details IN gt_table_of_tad_details
3252 ,p_tad_coa_id IN NUMBER
3253 ,p_update_statements_text OUT NOCOPY CLOB
3254 )
3255 RETURN BOOLEAN;
3256
3257 FUNCTION build_move_interf_data_stmts
3258 (
3259 p_table_of_tad_details IN gt_table_of_tad_details
3260 ,p_tad_coa_id IN NUMBER
3261 ,x_push_interf_statements_text OUT NOCOPY CLOB
3262 ,x_pop_interf_statements_text OUT NOCOPY CLOB
3263 )
3264 RETURN BOOLEAN;
3265
3266
3267 FUNCTION get_coa_info
3268 (
3269 p_chart_of_accounts_id IN NUMBER
3270 ,p_chart_of_accounts_name OUT NOCOPY VARCHAR2
3271 ,p_flex_delimiter OUT NOCOPY VARCHAR2
3272 ,p_concat_segments_template OUT NOCOPY VARCHAR2
3273 ,p_table_segment_qualifiers OUT NOCOPY gt_table_V30_V30
3274 ,p_table_segment_column_names OUT NOCOPY gt_table_V30
3275 )
3276 RETURN BOOLEAN;
3277
3278 --End of forward declarations
3279
3280
3281 /*======================================================================+
3282 | |
3283 | Private Function |
3284 | |
3285 | init_global_variables |
3286 | |
3287 | This program initializes the global variables required by the |
3288 | package. It retrieves the user name. |
3289 | |
3290 | |
3291 +======================================================================*/
3292 FUNCTION init_global_variables
3293 ( p_application_id IN NUMBER
3294 )
3295 RETURN BOOLEAN
3296 IS
3297 l_return_value BOOLEAN;
3298 l_log_module VARCHAR2 (2000);
3299 BEGIN
3300 IF g_log_enabled THEN
3301 l_log_module := C_DEFAULT_MODULE||'.init_global_variables';
3302 END IF;
3303
3304 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3305 trace
3306 (p_module => l_log_module
3307 ,p_msg => 'BEGIN ' || l_log_module
3308 ,p_level => C_LEVEL_PROCEDURE);
3309 END IF;
3310
3311 l_return_value := TRUE;
3312 /*
3313 --Retrieve and set the User Name (xla_cmp_common_pkg.get_user_name)
3314 --Set the application id
3315 --Retrieve and set the Application Information (xla_cmp_common_pkg.get_application_info)
3316 --Build and set the Transaction Account Builder package name (get_tab_api_package_name)
3317 --Retrieve and set the object name affixes (get_distinct_affixes)
3318 */
3319 --Retrieve current user name
3320 IF NOT xla_cmp_common_pkg.get_user_name
3321 (
3322 p_user_id => g_user_id
3323 ,p_user_name => g_user_name
3324 )
3325 THEN
3326 IF (C_LEVEL_ERROR >= g_log_level) THEN
3327 trace
3328 (p_module => l_log_module
3329 ,p_msg => 'ERROR:' ||
3330 ' Cannot determine user name.'
3331 ,p_level => C_LEVEL_ERROR);
3332 END IF;
3333 END IF;
3334
3335 --Retrieve and set the application info
3336 IF NOT xla_cmp_common_pkg.get_application_info
3337 (
3338 p_application_id => p_application_id
3339 ,p_application_info => g_application_info
3340 )
3341 THEN
3342 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3343 trace
3344 (p_module => l_log_module
3345 ,p_msg => 'EXCEPTION:' ||
3346 ' Cannot read application info, aborting...'
3347 ,p_level => C_LEVEL_EXCEPTION);
3348 END IF;
3349 RAISE ge_fatal_error;
3350 END IF;
3351
3352 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3353 trace
3354 (p_module => l_log_module
3355 ,p_msg => 'value returned= '
3356 || CASE l_return_value
3357 WHEN TRUE THEN 'TRUE'
3358 WHEN FALSE THEN 'FALSE'
3359 ELSE 'NULL'
3360 END
3361 ,p_level => C_LEVEL_STATEMENT );
3362 END IF;
3363
3364 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3365 trace
3366 (p_module => l_log_module
3367 ,p_msg => 'END ' || l_log_module
3368 ,p_level => C_LEVEL_PROCEDURE);
3369 END IF;
3370
3371 RETURN l_return_value;
3372
3373 EXCEPTION
3374 WHEN ge_fatal_error
3375 THEN
3376 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3377 trace
3378 (p_module => l_log_module
3379 ,p_msg => 'END ' || l_log_module
3380 ,p_level => C_LEVEL_PROCEDURE);
3381 END IF;
3382 RETURN FALSE;
3383 WHEN xla_exceptions_pkg.application_exception
3384 THEN
3385 RAISE;
3386 WHEN OTHERS
3387 THEN
3388 xla_exceptions_pkg.raise_message
3389 ( p_location => 'xla_cmp_tad_pkg.init_global_variables'
3390 ,p_msg_mode => g_msg_mode
3391 );
3392 RETURN FALSE;
3393 END init_global_variables;
3394
3395
3396 /*======================================================================+
3397 | |
3398 | Private Function |
3399 | |
3400 | update_tad_compilation_status |
3401 | |
3402 | This program initializes the global variables required by the |
3403 | package. It retrieves the user name. |
3404 | |
3405 | |
3406 +======================================================================*/
3407 FUNCTION update_tad_compilation_status
3408 ( p_compilation_status_code IN VARCHAR2
3409 ,p_application_id IN NUMBER
3410 ,p_account_definition_code IN VARCHAR2
3411 ,p_account_definition_type_code IN VARCHAR2
3412 ,p_amb_context_code IN VARCHAR2
3413 )
3414 RETURN BOOLEAN
3415 IS
3416 l_return_value BOOLEAN;
3417 l_log_module VARCHAR2 (2000);
3418 BEGIN
3419 IF g_log_enabled THEN
3420 l_log_module := C_DEFAULT_MODULE||'.update_tad_compilation_status';
3421 END IF;
3422
3423 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3424 trace
3425 (p_module => l_log_module
3426 ,p_msg => 'BEGIN ' || l_log_module
3427 ,p_level => C_LEVEL_PROCEDURE);
3428 END IF;
3429
3430 UPDATE xla_tab_acct_defs_b xtad
3431 SET xtad.compile_status_code = p_compilation_status_code
3432 WHERE xtad.application_id = p_application_id
3433 AND xtad.account_definition_code = p_account_definition_code
3434 AND xtad.account_definition_type_code = p_account_definition_type_code
3435 AND xtad.amb_context_code = p_amb_context_code;
3436
3437 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3438 trace
3439 (p_module => l_log_module
3440 ,p_msg => SQL%ROWCOUNT
3441 || ' row(s) updated in xla_tab_acct_defs_b'
3442 ,p_level => C_LEVEL_STATEMENT);
3443 END IF;
3444
3445 l_return_value := TRUE;
3446
3447 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3448 trace
3449 (p_module => l_log_module
3450 ,p_msg => 'END ' || l_log_module
3451 ,p_level => C_LEVEL_PROCEDURE);
3452 END IF;
3453
3454 RETURN l_return_value;
3455
3456 EXCEPTION
3457 WHEN xla_exceptions_pkg.application_exception
3458 THEN
3459 xla_exceptions_pkg.raise_message
3460 ( p_location => 'xla_cmp_tad_pkg.update_tad_compilation_status'
3461 );
3462 WHEN OTHERS THEN
3463 xla_exceptions_pkg.raise_message
3464 ( p_location => 'xla_cmp_tad_pkg.update_tad_compilation_status'
3465 ,p_msg_mode => g_msg_mode
3466 );
3467 RETURN FALSE;
3468 END update_tad_compilation_status;
3469
3470
3471 PROCEDURE compile_application_tads_srs
3472 ( p_errbuf OUT NOCOPY VARCHAR2
3473 ,p_retcode OUT NOCOPY NUMBER
3474 ,p_application_id IN NUMBER
3475 )
3476 IS
3477 l_log_module VARCHAR2 (2000);
3478 BEGIN
3479 IF g_log_enabled THEN
3480 l_log_module := C_DEFAULT_MODULE || '.compile_application_tads_srs';
3481 END IF;
3482
3483 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3484 trace
3485 (p_module => l_log_module
3486 ,p_msg => 'BEGIN ' || l_log_module
3487 ,p_level => C_LEVEL_PROCEDURE);
3488 END IF;
3489
3490 IF xla_cmp_tad_pkg.compile_application_tads
3491 (
3492 p_application_id => p_application_id
3493 )
3494 THEN
3495 IF (C_LEVEL_EVENT >= g_log_level) THEN
3496 trace
3497 (p_module => l_log_module
3498 ,p_msg => 'TAB Accounting Engine built successfully'
3499 ,p_level => C_LEVEL_EVENT);
3500 END IF;
3501 p_retcode := 0;
3502 ELSE
3503 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3504 trace
3505 ( p_module => l_log_module
3506 ,p_msg => 'Unable to build TAB Accounting Engine.'
3507 ,p_level => C_LEVEL_EXCEPTION);
3508 END IF;
3509 p_retcode := 2;
3510 END IF;
3511
3512 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3513 trace
3514 (p_module => l_log_module
3515 ,p_msg => 'p_retcode = ' || p_retcode
3516 ,p_level => C_LEVEL_STATEMENT);
3517 END IF;
3518
3519 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3520 trace
3521 (p_module => l_log_module
3522 ,p_msg => 'RETURN ' || C_DEFAULT_MODULE || l_log_module
3523 ,p_level => C_LEVEL_PROCEDURE);
3524 END IF;
3525
3526 EXCEPTION
3527 WHEN xla_exceptions_pkg.application_exception
3528 THEN
3529 RAISE;
3530 WHEN OTHERS
3531 THEN
3532 xla_exceptions_pkg.raise_message
3533 (p_location => 'xla_cmp_tad_pkg.compile_application_tads_srs'
3534 );
3535
3536 END compile_application_tads_srs;
3537
3538
3539 FUNCTION compile_application_tads
3540 ( p_application_id IN NUMBER
3541 )
3542 RETURN BOOLEAN
3543 IS
3544 l_return_value BOOLEAN;
3545
3546 l_user_name VARCHAR2(30);
3547 lr_application_info xla_cmp_common_pkg.lt_application_info;
3548 l_amb_context_code VARCHAR2(30);
3549 i NUMBER;
3550 l_msg_count NUMBER;
3551 l_msg_data VARCHAR2(2000);
3552 l_message_text VARCHAR2(32000);
3553 l_log_module VARCHAR2(2000);
3554
3555 BEGIN
3556 IF g_log_enabled THEN
3557 l_log_module := C_DEFAULT_MODULE||'.compile_application_tads';
3558 END IF;
3559
3560 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3561 trace
3562 (p_module => l_log_module
3563 ,p_msg => 'BEGIN ' || l_log_module
3564 ,p_level => C_LEVEL_PROCEDURE);
3565 END IF;
3566
3567 --Set the return value to TRUE
3568 l_return_value := TRUE;
3569
3570 --Retrieve the AMB context code
3571 l_amb_context_code := NVL( fnd_profile.value('XLA_AMB_CONTEXT')
3572 ,'DEFAULT'
3573 );
3574
3575 --Open cursor on all enabled TADs for the application and AMB context
3576 FOR cur_tad IN
3577 (SELECT xtdv.application_id
3578 ,xtdv.account_definition_code
3579 ,xtdv.account_definition_type_code
3580 ,xtdv.amb_context_code
3581 ,xtdv.name
3582 FROM xla_tab_acct_defs_vl xtdv
3583 WHERE xtdv.application_id = p_application_id
3584 AND xtdv.amb_context_code = l_amb_context_code
3585 AND xtdv.enabled_flag = 'Y'
3586 )
3587 LOOP
3588 --If tad_compilation successful
3589 IF compile_tad
3590 (
3591 p_application_id => cur_tad.application_id
3592 ,p_account_definition_code => cur_tad.account_definition_code
3593 ,p_account_definition_type_code => cur_tad.account_definition_type_code
3594 ,p_amb_context_code => cur_tad.amb_context_code
3595 )
3596 THEN
3597 --Report the "successfully compiled" message in the output
3598 fnd_file.put_line
3599 (
3600 fnd_file.output
3601 ,xla_messages_pkg.get_message
3602 (
3603 'XLA'
3604 ,'XLA_TAB_CMP_TAD_SUCCEEDED'
3605 ,'TRX_ACCT_DEF', cur_tad.name
3606 )
3607 );
3608 --Else (compilation unsuccessful)
3609 ELSE
3610 --Set return value to FALSE
3611 l_return_value := FALSE;
3612 --Report the "unsuccessfully compiled" message in the output
3613 fnd_file.put_line
3614 (
3615 fnd_file.output
3616 ,xla_messages_pkg.get_message
3617 (
3618 'XLA'
3619 ,'XLA_TAB_CMP_TAD_FAILED'
3620 ,'TRX_ACCT_DEF', cur_tad.name
3621 )
3622 );
3623 --Report the errors
3624 fnd_msg_pub.Count_And_Get
3625 (
3626 p_count => l_msg_count
3627 ,p_data => l_msg_data
3628 );
3629 --If msg_count 0 it might be the message is on the old stack
3630 IF l_msg_count = 0
3631 THEN
3632 fnd_file.put_line
3633 (
3634 fnd_file.log
3635 ,fnd_message.get()
3636 );
3637 ELSIF l_msg_count = 1
3638 THEN
3639 fnd_message.set_encoded
3640 (
3641 encoded_message => l_msg_data
3642 );
3643 fnd_file.put_line
3644 (
3645 fnd_file.log
3646 ,fnd_message.get()
3647 );
3648 ELSIF l_msg_count > 1
3649 THEN
3650 FOR i IN 1..l_msg_count
3651 LOOP
3652 fnd_file.put_line
3653 (
3654 fnd_file.log
3655 ,fnd_msg_pub.get(p_encoded => 'F')
3656 );
3657 END LOOP;
3658 END IF;
3659 END IF;
3660 END LOOP;
3661
3662 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3663 trace
3664 (p_module => l_log_module
3665 ,p_msg => 'END ' || C_DEFAULT_MODULE || l_log_module
3666 ,p_level => C_LEVEL_PROCEDURE);
3667 END IF;
3668
3669 --If success in all phases return TRUE Else FALSE
3670 RETURN l_return_value;
3671
3672 EXCEPTION
3673 WHEN ge_fatal_error
3674 THEN
3675 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3676 trace
3677 ( p_module => l_log_module
3678 ,p_msg => 'EXCEPTION:' ||
3679 ' Cannot initialize global variables, aborting...'
3680 ,p_level => C_LEVEL_EXCEPTION);
3681 END IF;
3682 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3683 trace
3684 (p_module => l_log_module
3685 ,p_msg => 'END ' || l_log_module
3686 ,p_level => C_LEVEL_PROCEDURE);
3687 END IF;
3688 RETURN FALSE;
3689 WHEN xla_exceptions_pkg.application_exception
3690 THEN
3691 RAISE;
3692 WHEN OTHERS
3693 THEN
3694 xla_exceptions_pkg.raise_message
3695 (p_location => 'xla_cmp_tad_pkg.compile_application_tads'
3696 ,p_msg_mode => g_msg_mode
3697 );
3698 RETURN FALSE;
3699 END compile_application_tads;
3700
3701
3702 FUNCTION compile_tad
3703 ( p_application_id IN NUMBER
3704 ,p_account_definition_code IN VARCHAR2
3705 ,p_account_definition_type_code IN VARCHAR2
3706 ,p_amb_context_code IN VARCHAR2
3707 )
3708 RETURN BOOLEAN
3709 IS
3710 l_return_value BOOLEAN;
3711
3712 l_user_name VARCHAR2(30);
3713 lr_application_info xla_cmp_common_pkg.lt_application_info;
3714 l_tad_coa_id NUMBER;
3715 l_count_missing_required_tat NUMBER;
3716 l_uncomp_tat_name VARCHAR2(80);
3717 l_tad_package_name VARCHAR2(30);
3718 l_tad_name VARCHAR2(80);
3719 l_tad_enabled_flag VARCHAR2(1);
3720 l_table_of_tad_details gt_table_of_tad_details;
3721 l_table_of_adrs xla_cmp_adr_pkg.gt_table_of_adrs_in;
3722 l_table_of_adrs_ext xla_cmp_adr_pkg.gt_table_of_adrs_out;
3723 l_adr_specs CLOB;
3724 l_adr_bodies CLOB;
3725 l_final_compile_status_code VARCHAR2(1);
3726
3727 l_log_module VARCHAR2 (2000);
3728 BEGIN
3729 IF g_log_enabled THEN
3730 l_log_module := C_DEFAULT_MODULE || '.compile_tad';
3731 END IF;
3732
3733 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3734 trace
3735 (p_module => l_log_module
3736 ,p_msg => 'BEGIN ' || l_log_module
3737 ,p_level => C_LEVEL_PROCEDURE);
3738 END IF;
3739
3740 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3741 trace
3742 (p_module => l_log_module
3743 ,p_msg => 'Compiling TAD:'
3744 ,p_level => C_LEVEL_STATEMENT);
3745 trace
3746 (p_module => l_log_module
3747 ,p_msg => 'p_application_id:' || p_application_id
3748 ,p_level => C_LEVEL_STATEMENT);
3749 trace
3750 (p_module => l_log_module
3751 ,p_msg => 'p_account_definition_code:'
3752 || p_account_definition_code
3753 ,p_level => C_LEVEL_STATEMENT);
3754 trace
3755 (p_module => l_log_module
3756 ,p_msg => 'p_account_definition_type_code:'
3757 || p_account_definition_type_code
3758 ,p_level => C_LEVEL_STATEMENT);
3759 trace
3760 (p_module => l_log_module
3761 ,p_msg => 'p_amb_context_code:'
3762 || p_amb_context_code
3763 ,p_level => C_LEVEL_STATEMENT);
3764 END IF;
3765
3766 l_return_value := TRUE;
3767
3768 --Initialize the global message table
3769 FND_MSG_PUB.Initialize;
3770
3771 --Initialize global variables
3772 IF NOT init_global_variables
3773 (
3774 p_application_id => p_application_id
3775 )
3776 THEN
3777 --If global vars cannot be set we cannot continue
3778 IF (C_LEVEL_ERROR >= g_log_level) THEN
3779 trace
3780 (p_module => l_log_module
3781 ,p_msg => 'init_global_variables failed'
3782 ,p_level => C_LEVEL_ERROR);
3783 END IF;
3784 l_return_value := FALSE;
3785 RAISE ge_fatal_error;
3786 END IF;
3787
3788 --Get the name of the TAD in case we need to log messages
3789 BEGIN
3790 SELECT name
3791 ,enabled_flag
3792 INTO l_tad_name
3793 ,l_tad_enabled_flag
3794 FROM xla_tab_acct_defs_vl
3795 WHERE application_id = p_application_id
3796 AND account_definition_code = p_account_definition_code
3797 AND account_definition_type_code = p_account_definition_type_code
3798 AND amb_context_code = p_amb_context_code;
3799 EXCEPTION
3800 WHEN NO_DATA_FOUND
3801 THEN
3802 DECLARE
3803 l_amb_context_meaning VARCHAR2(80);
3804 l_owner_meaning VARCHAR2(80);
3805 BEGIN
3806 --Try to get the meaning of the amb context code
3807 BEGIN
3808 l_amb_context_meaning := xla_lookups_pkg.get_meaning
3809 (
3810 p_lookup_type => 'XLA_AMB_CONTEXT_TYPE'
3811 ,p_lookup_code => p_amb_context_code
3812 );
3813 EXCEPTION
3814 --If not possible use the amb context code
3815 WHEN OTHERS
3816 THEN
3817 l_amb_context_meaning := p_amb_context_code;
3818 END;
3819 --Try to get the meaning of the owner
3820 BEGIN
3821
3822 l_owner_meaning := xla_lookups_pkg.get_meaning
3823 (
3824 p_lookup_type => 'XLA_OWNER_TYPE'
3825 ,p_lookup_code => p_account_definition_type_code
3826 );
3827 EXCEPTION
3828 --If not possible use the the type_code
3829 WHEN OTHERS
3830 THEN
3831 l_owner_meaning := p_account_definition_type_code;
3832 END;
3833
3834 --Push a message in the message stack
3835 --without raising an exception
3836 xla_exceptions_pkg.raise_message
3837 ( p_appli_s_name => 'XLA'
3838 ,p_msg_name => 'XLA_TAB_CANT_FIND_TAD'
3839 ,p_token_1 => 'AMB_CONTEXT'
3840 ,p_value_1 => l_amb_context_meaning
3841 ,p_token_2 => 'OWNER'
3842 ,p_value_2 => l_owner_meaning
3843 ,p_token_3 => 'TRX_ACCT_DEF_CODE'
3844 ,p_value_3 => p_account_definition_code
3845 ,p_msg_mode => g_msg_mode
3846 );
3847 l_return_value := FALSE;
3848
3849 RAISE ge_fatal_error;
3850
3851 END;
3852 END;
3853
3854 --If the TAD is disabled abort the execution
3855 IF l_tad_enabled_flag = 'N'
3856 THEN
3857 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3858 trace
3859 (p_module => l_log_module
3860 ,p_msg => 'EXCEPTION:' ||
3861 'This TAD is disabled, aborting...'
3862 ,p_level => C_LEVEL_EXCEPTION);
3863 END IF;
3864 --Raise a user oriented message
3865 xla_exceptions_pkg.raise_message
3866 ( p_appli_s_name => 'XLA'
3867 ,p_msg_name => 'XLA_TAB_CMP_TAD_DISABLED'
3868 ,p_token_1 => 'TRX_ACCT_DEF'
3869 ,p_value_1 => l_tad_name
3870 ,p_msg_mode => g_msg_mode
3871 );
3872 RAISE ge_fatal_error;
3873 END IF;
3874
3875 BEGIN
3876 --Retrieve (if it exists) the first uncompiled TAT for this application
3877 SELECT xtat.name
3878 INTO l_uncomp_tat_name
3879 FROM xla_tab_acct_types_vl xtat
3880 WHERE xtat.application_id = p_application_id
3881 AND xtat.enabled_flag = 'Y'
3882 AND ( xtat.compile_status_code IS NULL
3883 OR xtat.compile_status_code
3884 <> xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_COMPILED
3885 )
3886 AND ROWNUM = 1;
3887 EXCEPTION
3888 WHEN NO_DATA_FOUND
3889 THEN
3890 NULL;
3891 END;
3892
3893 --If at least not
3894 IF l_uncomp_tat_name IS NOT NULL
3895 THEN
3896 --Raise a user oriented message
3897 xla_exceptions_pkg.raise_message
3898 ( p_appli_s_name => 'XLA'
3899 ,p_msg_name => 'XLA_TAB_CMP_INVALIDATED_TAT'
3900 ,p_token_1 => 'TRX_ACCT_TYPE'
3901 ,p_value_1 => l_uncomp_tat_name
3902 ,p_token_2 => 'APPLICATION_NAME'
3903 ,p_value_2 => g_application_info.application_name
3904 ,p_msg_mode => g_msg_mode
3905 );
3906 RAISE ge_fatal_error;
3907 END IF;
3908
3909 /*
3910 --Lock TAD setup data
3911 IF NOT xla_cmp_lock_pkg.lock_tad
3912 (
3913 p_application_id => p_application_id
3914 ,p_account_definition_code => p_account_definition_code
3915 ,p_account_definition_type_code => p_account_definition_type_code
3916 ,p_amb_context_code => p_amb_context_code
3917 )
3918 THEN
3919 l_return_value := FALSE;
3920 END IF;
3921 */
3922
3923 --Get the package name of the tad
3924 IF NOT get_tad_package_name
3925 (
3926 p_account_definition_code => p_account_definition_code
3927 ,p_account_definition_type_code => p_account_definition_type_code
3928 ,p_amb_context_code => p_amb_context_code
3929 ,p_tad_package_name => l_tad_package_name
3930 ,p_chart_of_accounts_id => l_tad_coa_id
3931 )
3932 THEN
3933 IF (C_LEVEL_ERROR >= g_log_level) THEN
3934 trace
3935 (p_module => l_log_module
3936 ,p_msg => 'get_tad_package_name failed'
3937 ,p_level => C_LEVEL_ERROR);
3938 END IF;
3939 l_return_value := FALSE;
3940 RAISE ge_fatal_error;
3941 END IF;
3942
3943 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3944 trace
3945 (p_module => l_log_module
3946 ,p_msg => 'TAD package name: ' || l_tad_package_name
3947 ,p_level => C_LEVEL_STATEMENT);
3948 END IF;
3949
3950 --Read the TAD details and corresponding TAT affix
3951 SELECT xtdd.application_id
3952 ,xtdd.amb_context_code
3953 ,xtdd.account_type_code
3954 ,xtdd.flexfield_segment_code
3955 ,xtdd.segment_rule_type_code
3956 ,xtdd.segment_rule_code
3957 ,xtta.object_name_affix
3958 ,xtta.compile_status_code
3959 ,xtta.rule_assignment_code
3960 ,NULL
3961 BULK COLLECT
3962 INTO l_table_of_tad_details
3963 FROM xla_tab_acct_def_details xtdd
3964 ,xla_tab_acct_types_b xtta
3965 WHERE xtdd.application_id = p_application_id
3966 AND xtdd.account_definition_code = p_account_definition_code
3967 AND xtdd.account_definition_type_code = p_account_definition_type_code
3968 AND xtdd.amb_context_code = p_amb_context_code
3969 AND xtta.application_id = xtdd.application_id
3970 AND xtta.account_type_code = xtdd.account_type_code
3971 ORDER BY xtta.object_name_affix
3972 ,xtdd.flexfield_segment_code
3973 ,xtdd.account_type_code;
3974
3975 --If the TAD has no details
3976 IF l_table_of_tad_details.COUNT = 0
3977 THEN
3978 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3979 trace
3980 (p_module => l_log_module
3981 ,p_msg => 'ERROR:'
3982 || 'This TAD has no details'
3983 ,p_level => C_LEVEL_EXCEPTION);
3984 END IF;
3985
3986 --Raise a user oriented message
3987 xla_exceptions_pkg.raise_message
3988 ( p_appli_s_name => 'XLA'
3989 ,p_msg_name => 'XLA_TAB_CMP_TAD_NO_DETAILS'
3990 ,p_token_1 => 'TRX_ACCT_DEF'
3991 ,p_value_1 => l_tad_name
3992 ,p_msg_mode => g_msg_mode
3993 );
3994 RAISE ge_fatal_error;
3995 END IF;
3996
3997 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3998 trace
3999 (p_module => l_log_module
4000 ,p_msg => 'List of TAD details: '
4001 ,p_level => C_LEVEL_STATEMENT);
4002 END IF;
4003
4004 --Read the distinct Account Derivation Rules assigned to the TAD
4005 SELECT application_id --application_id
4006 ,segment_rule_type_code --segment_rule_type_code
4007 ,segment_rule_code --segment_rule_code
4008 ,amb_context_code --amb_context_code
4009 BULK COLLECT
4010 INTO l_table_of_adrs
4011 FROM
4012 (
4013 SELECT DISTINCT
4014 xtdd.application_id
4015 ,xtdd.segment_rule_type_code
4016 ,xtdd.segment_rule_code
4017 ,xtdd.amb_context_code
4018 FROM xla_tab_acct_def_details xtdd
4019 ,xla_tab_acct_types_b xtta
4020 WHERE xtdd.application_id = p_application_id
4021 AND xtdd.account_definition_code = p_account_definition_code
4022 AND xtdd.account_definition_type_code = p_account_definition_type_code
4023 AND xtdd.amb_context_code = p_amb_context_code
4024 AND xtta.application_id = xtdd.application_id
4025 AND xtta.account_type_code = xtdd.account_type_code
4026 );
4027
4028 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4029 trace
4030 (p_module => l_log_module
4031 ,p_msg => 'ADR rules: '
4032 ,p_level => C_LEVEL_STATEMENT);
4033 IF l_table_of_adrs.FIRST IS NOT NULL
4034 THEN
4035 FOR i IN l_table_of_adrs.FIRST..l_table_of_adrs.LAST
4036 LOOP
4037 trace
4038 ( p_module => l_log_module
4039 ,p_msg => 'ADR ' || i || ' segment_rule_code: '
4040 || l_table_of_adrs(i).segment_rule_code
4041 ,p_level => C_LEVEL_STATEMENT);
4042 END LOOP;
4043 END IF;
4044 END IF;
4045
4046 --Build the specifications and the bodies of the adrs
4047 IF NOT xla_cmp_adr_pkg.build_adrs_for_tab
4048 (
4049 p_table_of_adrs_in => l_table_of_adrs
4050 ,x_table_of_adrs_out => l_table_of_adrs_ext
4051 ,x_adr_specs_text => l_adr_specs
4052 ,x_adr_bodies_text => l_adr_bodies
4053 )
4054 THEN
4055 IF (C_LEVEL_ERROR >= g_log_level) THEN
4056 trace
4057 (p_module => l_log_module
4058 ,p_msg => 'xla_cmp_adr_pkg.build_adrs_for_tad failed'
4059 ,p_level => C_LEVEL_ERROR);
4060 END IF;
4061 l_return_value := FALSE;
4062 RAISE ge_fatal_error;
4063 END IF;
4064
4065 --Dump the compiled ADRs hash ids and their sources
4066 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4067 FOR i IN l_table_of_adrs_ext.FIRST..l_table_of_adrs_ext.LAST
4068 LOOP
4069 trace
4070 (p_module => l_log_module
4071 ,p_msg => 'Hash id: ' || l_table_of_adrs_ext(i).adr_hash_id
4072 ,p_level => C_LEVEL_STATEMENT);
4073
4074 DECLARE
4075 n VARCHAR2(30);
4076 BEGIN
4077 n := l_table_of_adrs_ext(i).table_of_sources.FIRST;
4078 WHILE n IS NOT NULL
4079 LOOP
4080 trace
4081 (p_module => l_log_module
4082 ,p_msg => 'Source: ' || n || ':' || l_table_of_adrs_ext(i).table_of_sources(n)
4083 ,p_level => C_LEVEL_STATEMENT);
4084
4085 n := l_table_of_adrs_ext(i).table_of_sources.NEXT(n); -- get subscript of next element
4086 END LOOP;
4087 END;
4088 END LOOP;
4089
4090 END IF;
4091
4092
4093 --Create Package Specification
4094 IF NOT create_package_spec
4095 (
4096 p_account_definition_code => p_account_definition_code
4097 ,p_account_definition_type_code => p_account_definition_type_code
4098 ,p_amb_context_code => p_amb_context_code
4099 ,p_package_name => l_tad_package_name
4100 ,p_table_of_tad_details => l_table_of_tad_details
4101 ,p_table_of_adrs => l_table_of_adrs
4102 ,p_adr_specs_text => l_adr_specs
4103 )
4104 THEN
4105 IF (C_LEVEL_ERROR >= g_log_level) THEN
4106 trace
4107 (p_module => l_log_module
4108 ,p_msg => 'create_package_spec failed'
4109 ,p_level => C_LEVEL_ERROR);
4110 END IF;
4111 l_return_value := FALSE;
4112 END IF;
4113
4114
4115 l_adr_bodies := xla_cmp_string_pkg.replace_token
4116 ( l_adr_bodies
4117 ,'G_LOG_ENABLED'
4118 ,'xla_cmp_tad_pkg.G_LOG_ENABLED'
4119 );
4120
4121 l_adr_bodies := xla_cmp_string_pkg.replace_token
4122 ( l_adr_bodies
4123 ,'g_log_enabled'
4124 ,'xla_cmp_tad_pkg.G_LOG_ENABLED'
4125 );
4126 l_adr_bodies := xla_cmp_string_pkg.replace_token
4127 ( l_adr_bodies
4128 ,'G_LOG_LEVEL'
4129 ,'xla_cmp_tad_pkg.G_LOG_LEVEL'
4130 );
4131 l_adr_bodies := xla_cmp_string_pkg.replace_token
4132 ( l_adr_bodies
4133 ,'g_log_level'
4134 ,'xla_cmp_tad_pkg.G_LOG_LEVEL'
4135 );
4136
4137 l_adr_bodies := xla_cmp_string_pkg.replace_token
4138 ( l_adr_bodies
4139 ,'C_DEFAULT_MODULE'
4140 ,'g_default_module'
4141 );
4142 l_adr_bodies := xla_cmp_string_pkg.replace_token
4143 ( l_adr_bodies
4144 ,'C_LEVEL_PROCEDURE'
4145 ,'xla_cmp_tad_pkg.C_LEVEL_PROCEDURE'
4146 );
4147
4148 --Create Package Body
4149 IF NOT create_package_body
4150 (
4151 p_account_definition_code => p_account_definition_code
4152 ,p_account_definition_type_code => p_account_definition_type_code
4153 ,p_amb_context_code => p_amb_context_code
4154 ,p_package_name => l_tad_package_name
4155 ,p_tad_coa_id => l_tad_coa_id
4156 ,p_table_of_tad_details => l_table_of_tad_details
4157 ,p_table_of_adrs => l_table_of_adrs
4158 ,p_table_of_adrs_ext => l_table_of_adrs_ext
4159 ,p_adr_bodies_text => l_adr_bodies
4160 )
4161 THEN
4162 --If fails we cannot continue
4163 IF (C_LEVEL_ERROR >= g_log_level) THEN
4164 trace
4165 (p_module => l_log_module
4166 ,p_msg => 'create_package_body failed'
4167 ,p_level => C_LEVEL_ERROR);
4168 END IF;
4169 l_return_value := FALSE;
4170 END IF;
4171
4172 --If compilation status is false log raise a local exception
4173 IF NOT l_return_value
4174 THEN
4175 IF (C_LEVEL_ERROR >= g_log_level) THEN
4176 trace
4177 (p_module => l_log_module
4178 ,p_msg => 'Overall compilation status is failure'
4179 ,p_level => C_LEVEL_ERROR);
4180 END IF;
4181 RAISE ge_fatal_error;
4182 END IF;
4183
4184 --Check is some TAT requiring compilation has been left out
4185 SELECT count(*)
4186 INTO l_count_missing_required_tat
4187 FROM xla_tab_acct_types_b xtat
4188 ,xla_tab_acct_def_details xtad
4189 WHERE xtat.application_id = p_application_id
4190 AND xtat.enabled_flag = 'Y'
4191 AND xtat.rule_assignment_code = 'REQUIRED'
4192 AND xtad.application_id (+)= xtat.application_id
4193 AND xtad.account_type_code (+)= xtat.account_type_code
4194 AND xtad.account_definition_code (+)= p_account_definition_code
4195 AND xtad.account_definition_type_code (+)= p_account_definition_type_code
4196 AND xtad.amb_context_code (+)= p_amb_context_code
4197 AND xtad.flexfield_segment_code IS NULL;
4198
4199 IF l_count_missing_required_tat > 0
4200 THEN
4201 --Raise a user oriented message
4202 xla_exceptions_pkg.raise_message
4203 ( p_appli_s_name => 'XLA'
4204 ,p_msg_name => 'XLA_TAB_CMP_TAD_MISS_REQ_TAT'
4205 ,p_token_1 => 'TRX_ACCT_DEF'
4206 ,p_value_1 => l_tad_name
4207 ,p_msg_mode => g_msg_mode
4208 );
4209 RAISE ge_fatal_error;
4210 END IF;
4211
4212 --Update the compilation status of the TAD
4213 IF NOT update_tad_compilation_status
4214 ( p_compilation_status_code =>
4215 xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_COMPILED
4216 ,p_application_id => p_application_id
4217 ,p_account_definition_code => p_account_definition_code
4218 ,p_account_definition_type_code => p_account_definition_type_code
4219 ,p_amb_context_code => p_amb_context_code
4220 )
4221 THEN
4222 IF (C_LEVEL_ERROR >= g_log_level) THEN
4223 trace
4224 (p_module => l_log_module
4225 ,p_msg => 'Could not update the TAD compilation status'
4226 ,p_level => C_LEVEL_ERROR);
4227 END IF;
4228 END IF;
4229
4230 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4231 trace
4232 (p_module => l_log_module
4233 ,p_msg => 'END ' || l_log_module
4234 ,p_level => C_LEVEL_PROCEDURE);
4235 END IF;
4236
4237 --If success in all phases return TRUE Else FALSE
4238 RETURN l_return_value;
4239
4240 EXCEPTION
4241 WHEN ge_fatal_error
4242 THEN
4243 --If the TAD name is not null the TAD exists
4244 IF l_tad_name IS NOT NULL
4245 THEN
4246 --update the compilation status to error
4247 IF NOT update_tad_compilation_status
4248 ( p_compilation_status_code =>
4249 xla_cmp_common_pkg.G_COMPILE_STATUS_CODE_ERROR
4250 ,p_application_id => p_application_id
4251 ,p_account_definition_code => p_account_definition_code
4252 ,p_account_definition_type_code => p_account_definition_type_code
4253 ,p_amb_context_code => p_amb_context_code
4254 )
4255 THEN
4256 IF (C_LEVEL_ERROR >= g_log_level) THEN
4257 trace
4258 (p_module => l_log_module
4259 ,p_msg => 'Could not update the TAD compilation status'
4260 ,p_level => C_LEVEL_ERROR);
4261 END IF;
4262 END IF;
4263
4264 --Push a message in the message stack
4265 --without raising an exception
4266 xla_exceptions_pkg.raise_message
4267 ( p_appli_s_name => 'XLA'
4268 ,p_msg_name => 'XLA_TAB_CMP_TAD_FAILED'
4269 ,p_token_1 => 'TRX_ACCT_DEF'
4270 ,p_value_1 => l_tad_name
4271 ,p_msg_mode => g_msg_mode
4272 );
4273 END IF;
4274
4275 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4276 trace
4277 ( p_module => l_log_module
4278 ,p_msg => 'EXCEPTION:' ||
4279 ' Cannot compile TAD, aborting...'
4280 ,p_level => C_LEVEL_EXCEPTION);
4281 END IF;
4282 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4283 trace
4284 (p_module => l_log_module
4285 ,p_msg => 'END ' || l_log_module
4286 ,p_level => C_LEVEL_PROCEDURE);
4287 END IF;
4288 RETURN FALSE;
4289 WHEN xla_exceptions_pkg.application_exception
4290 THEN
4291 xla_exceptions_pkg.raise_message
4292 ( p_location => 'xla_cmp_tad_pkg.compile_tad'
4293 );
4294 WHEN OTHERS THEN
4295 xla_exceptions_pkg.raise_message
4296 ( p_location => 'xla_cmp_tad_pkg.compile_tad'
4297 ,p_msg_mode => g_msg_mode
4298 );
4299 RETURN FALSE;
4300 END compile_tad;
4301
4302
4303 FUNCTION compile_tad_AUTONOMOUS
4304 ( p_application_id IN NUMBER
4305 ,p_account_definition_code IN VARCHAR2
4306 ,p_account_definition_type_code IN VARCHAR2
4307 ,p_amb_context_code IN VARCHAR2
4308 )
4309 RETURN BOOLEAN
4310 IS
4311 PRAGMA AUTONOMOUS_TRANSACTION;
4312
4313 l_return_value BOOLEAN;
4314 l_log_module VARCHAR2 (2000);
4315 BEGIN
4316 IF g_log_enabled THEN
4317 l_log_module := C_DEFAULT_MODULE || '.compile_tad_AUTONOMOUS';
4318 END IF;
4319
4320 l_return_value := compile_tad
4321 ( p_application_id => p_application_id
4322 ,p_account_definition_code => p_account_definition_code
4323 ,p_account_definition_type_code => p_account_definition_type_code
4324 ,p_amb_context_code => p_amb_context_code
4325 );
4326
4327 COMMIT;
4328
4329 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4330 trace
4331 (p_module => l_log_module
4332 ,p_msg => 'END ' || l_log_module
4333 ,p_level => C_LEVEL_PROCEDURE);
4334 END IF;
4335
4336 RETURN l_return_value;
4337
4338 EXCEPTION
4339 WHEN ge_fatal_error
4340 THEN
4341 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4342 trace
4343 ( p_module => l_log_module
4344 ,p_msg => 'EXCEPTION:' ||
4345 ' Cannot compile TAD, aborting...'
4346 ,p_level => C_LEVEL_EXCEPTION);
4347 END IF;
4348 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4349 trace
4350 (p_module => l_log_module
4351 ,p_msg => 'END ' || l_log_module
4352 ,p_level => C_LEVEL_PROCEDURE);
4353 END IF;
4354 RETURN FALSE;
4355 WHEN xla_exceptions_pkg.application_exception
4356 THEN
4357 RAISE;
4358 WHEN OTHERS
4359 THEN
4360 xla_exceptions_pkg.raise_message
4361 (p_location => 'xla_cmp_tad_pkg.compile_tad_AUTONOMOUS'
4362 ,p_msg_mode => g_msg_mode
4363 );
4364 RETURN FALSE;
4365 END compile_tad_AUTONOMOUS;
4366
4367
4368 /*======================================================================+
4369 | |
4370 | Private Function |
4371 | |
4372 | create_package_spec |
4373 | |
4374 | |
4375 | |
4376 | |
4377 +======================================================================*/
4378 FUNCTION create_package_spec
4379 (
4380 p_account_definition_code IN VARCHAR2
4381 ,p_account_definition_type_code IN VARCHAR2
4382 ,p_amb_context_code IN VARCHAR2
4383 ,p_package_name IN VARCHAR2
4384 ,p_table_of_tad_details IN gt_table_of_tad_details
4385 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
4386 ,p_adr_specs_text IN CLOB
4387 )
4388 RETURN BOOLEAN
4389 IS
4390 l_return_value BOOLEAN;
4391 l_package_spec_text CLOB;
4392 l_log_module VARCHAR2 (2000);
4393
4394 BEGIN
4395 IF g_log_enabled THEN
4396 l_log_module := C_DEFAULT_MODULE||'.create_package_spec';
4397 END IF;
4398
4399 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4400 trace
4401 (p_module => l_log_module
4402 ,p_msg => 'BEGIN ' || l_log_module
4403 ,p_level => C_LEVEL_PROCEDURE);
4404 END IF;
4405
4406 l_return_value := TRUE;
4407
4408 --build the package specification
4409 IF NOT build_package_spec
4410 (
4411 p_account_definition_code => p_account_definition_code
4412 ,p_account_definition_type_code => p_account_definition_type_code
4413 ,p_amb_context_code => p_amb_context_code
4414 ,p_package_name => p_package_name
4415 ,p_table_of_tad_details => p_table_of_tad_details
4416 ,p_table_of_adrs => p_table_of_adrs
4417 ,p_adr_specs_text => p_adr_specs_text
4418 ,p_package_spec_text => l_package_spec_text
4419 )
4420 THEN
4421 l_return_value := FALSE;
4422 IF (C_LEVEL_ERROR >= g_log_level) THEN
4423 trace
4424 (p_module => l_log_module
4425 ,p_msg => 'ERROR: build_package_spec failed'
4426 ,p_level => C_LEVEL_ERROR);
4427 END IF;
4428 --Useless to push
4429 RAISE ge_fatal_error;
4430 END IF;
4431
4432 IF NOT xla_cmp_create_pkg.push_database_object
4433 (
4434 p_object_name => p_package_name
4435 ,p_object_type => 'PACKAGE'
4436 ,p_object_owner => NULL --current user
4437 ,p_apps_account => g_application_info.apps_account
4438 ,p_msg_mode => G_OA_MESSAGE
4439 ,p_ddl_text => l_package_spec_text
4440 )
4441 THEN
4442 l_return_value := FALSE;
4443 IF (C_LEVEL_ERROR >= g_log_level) THEN
4444 trace
4445 (p_module => l_log_module
4446 ,p_msg => 'push_database_object failed'
4447 ,p_level => C_LEVEL_ERROR);
4448 END IF;
4449 END IF;
4450
4451 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4452 trace
4453 ( p_module => l_log_module
4454 ,p_msg => 'END ' || l_log_module
4455 ,p_level => C_LEVEL_PROCEDURE
4456 );
4457 END IF;
4458
4459 RETURN l_return_value;
4460
4461 EXCEPTION
4462 WHEN ge_fatal_error
4463 THEN
4464 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4465 trace
4466 (p_module => l_log_module
4467 ,p_msg => 'EXCEPTION:' ||
4468 ' Fatal error, aborting...'
4469 ,p_level => C_LEVEL_EXCEPTION);
4470 END IF;
4471 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4472 trace
4473 (p_module => l_log_module
4474 ,p_msg => 'END ' || C_DEFAULT_MODULE || l_log_module
4475 ,p_level => C_LEVEL_PROCEDURE);
4476 END IF;
4477 RETURN FALSE;
4478 WHEN xla_exceptions_pkg.application_exception
4479 THEN
4480 RAISE;
4481 WHEN OTHERS
4482 THEN
4483 xla_exceptions_pkg.raise_message
4484 (p_location => 'xla_cmp_tad_pkg.create_package_spec'
4485 ,p_msg_mode => g_msg_mode
4486 );
4487 RETURN FALSE;
4488
4489 END create_package_spec;
4490
4491
4492
4493
4494 /*======================================================================+
4495 | |
4496 | Private Function |
4497 | |
4498 | build_package_spec |
4499 | |
4500 | |
4501 | |
4502 | |
4503 +======================================================================*/
4504 FUNCTION build_package_spec
4505 (
4506 p_account_definition_code IN VARCHAR2
4507 ,p_account_definition_type_code IN VARCHAR2
4508 ,p_amb_context_code IN VARCHAR2
4509 ,p_package_name IN VARCHAR2
4510 ,p_table_of_tad_details IN gt_table_of_tad_details
4511 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
4512 ,p_adr_specs_text IN CLOB
4513 ,p_package_spec_text OUT NOCOPY CLOB
4514 )
4515 RETURN BOOLEAN
4516 IS
4517
4518 l_package_spec_text CLOB;
4519 l_return_value BOOLEAN;
4520 l_log_module VARCHAR2 (2000);
4521 BEGIN
4522 IF g_log_enabled THEN
4523 l_log_module := C_DEFAULT_MODULE||'.build_package_spec';
4524 END IF;
4525
4526 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4527 trace
4528 (p_module => l_log_module
4529 ,p_msg => 'BEGIN ' || l_log_module
4530 ,p_level => C_LEVEL_PROCEDURE);
4531 END IF;
4532
4533 --take the package specification template
4534 l_package_spec_text := C_TMPL_TAD_PACKAGE_SPEC;
4535
4536 /*
4537 --build the package history
4538 IF NOT build_package_history (l_history )
4539 THEN
4540 --not a fatal error
4541 IF (C_LEVEL_ERROR >= g_log_level) THEN
4542 trace
4543 (p_module => l_log_module
4544 ,p_msg => 'cannot build package history'
4545 ,p_level => C_LEVEL_ERROR);
4546 END IF;
4547 l_return_value := FALSE;
4548 END IF;
4549
4550
4551 --replace the history token
4552 p_package_spec_text := REPLACE
4553 (
4554 p_package_spec_text
4555 ,'$HISTORY$'
4556 ,RPAD( l_history
4557 , 66
4558 , ' '
4559 ) || '|'
4560 );
4561 */
4562
4563 --replace the ADR functions declarations token
4564 l_package_spec_text:= xla_cmp_common_pkg.replace_token
4565 (
4566 p_original_text => l_package_spec_text
4567 ,p_token => '$TAD_ADR_FUNCT_SPECS$'
4568 ,p_replacement_text => p_adr_specs_text
4569 );
4570
4571 --replace the package name tokens
4572 l_package_spec_text := xla_cmp_string_pkg.replace_token
4573 (
4574 l_package_spec_text
4575 ,'$TAD_PACKAGE_NAME_1$'
4576 ,p_package_name
4577 );
4578
4579 l_package_spec_text := xla_cmp_string_pkg.replace_token
4580 (
4581 l_package_spec_text
4582 ,'$TAD_PACKAGE_NAME_2$'
4583 ,RPAD( p_package_name
4584 , 66
4585 , ' '
4586 )
4587 || '|'
4588 );
4589
4590 l_package_spec_text := xla_cmp_string_pkg.replace_token
4591 (
4592 l_package_spec_text
4593 ,'$TAD_PACKAGE_NAME_3$'
4594 ,UPPER(p_package_name)
4595 );
4596
4597 --replace the application name token
4598 l_package_spec_text := xla_cmp_string_pkg.replace_token
4599 (
4600 l_package_spec_text
4601 ,'$APPLICATION_NAME$'
4602 ,RPAD( g_application_info.application_name
4603 , 66
4604 , ' '
4605 ) || '|'
4606 );
4607
4608 --replace the application id token
4609 l_package_spec_text := xla_cmp_string_pkg.replace_token
4610 (
4611 l_package_spec_text
4612 ,'$APPLICATION_ID$'
4613 ,RPAD( TO_CHAR(g_application_info.application_id) || ')'
4614 , 49
4615 , ' '
4616 ) || '|'
4617 );
4618
4619
4620 --replace the TAD info tokens
4621 l_package_spec_text := xla_cmp_string_pkg.replace_token
4622 (
4623 l_package_spec_text
4624 ,'$TAD_CODE$'
4625 ,RPAD( p_account_definition_code
4626 , 48
4627 , ' '
4628 )
4629 || '|'
4630 );
4631 l_package_spec_text := xla_cmp_string_pkg.replace_token
4632 (
4633 l_package_spec_text
4634 ,'$TAD_TYPE_CODE$'
4635 ,RPAD( p_account_definition_type_code
4636 , 48
4637 , ' '
4638 )
4639 || '|'
4640 );
4641 l_package_spec_text := xla_cmp_string_pkg.replace_token
4642 (
4643 l_package_spec_text
4644 ,'$AMB_CONTEXT_CODE$'
4645 ,RPAD( p_amb_context_code
4646 , 48
4647 , ' '
4648 )
4649 || '|'
4650 );
4651
4652 p_package_spec_text := l_package_spec_text;
4653
4654 l_return_value := TRUE;
4655
4656 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4657 trace
4658 (p_module => l_log_module
4659 ,p_msg => 'END ' || l_log_module
4660 ,p_level => C_LEVEL_PROCEDURE);
4661 END IF;
4662
4663 RETURN l_return_value;
4664
4665 EXCEPTION
4666 WHEN xla_exceptions_pkg.application_exception
4667 THEN
4668 RAISE;
4669 WHEN OTHERS
4670 THEN
4671 xla_exceptions_pkg.raise_message
4672 (p_location => 'xla_cmp_tad_pkg.build_package_spec'
4673 ,p_msg_mode => g_msg_mode
4674 );
4675 RETURN FALSE;
4676
4677 END build_package_spec;
4678
4679
4680
4681 /*======================================================================+
4682 | |
4683 | Private Function |
4684 | |
4685 | build_package_history |
4686 | |
4687 | |
4688 | |
4689 | |
4690 +======================================================================*/
4691 FUNCTION build_package_history (p_package_history OUT NOCOPY CLOB)
4692 RETURN BOOLEAN
4693 IS
4694 l_return_value BOOLEAN;
4695 l_log_module VARCHAR2 (2000);
4696 BEGIN
4697 IF g_log_enabled THEN
4698 l_log_module := C_DEFAULT_MODULE||'.build_package_history';
4699 END IF;
4700
4701 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4702 trace
4703 (p_module => l_log_module
4704 ,p_msg => 'BEGIN ' || l_log_module
4705 ,p_level => C_LEVEL_PROCEDURE);
4706 END IF;
4707
4708 p_package_history := TO_CHAR(SYSDATE, 'DD-MON-RR')
4709 || ' XLA '
4710 || 'Generated by Oracle Subledger Accounting Compiler';
4711
4712 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4713 trace
4714 (p_module => l_log_module
4715 ,p_msg => 'END ' || l_log_module
4716 ,p_level => C_LEVEL_PROCEDURE);
4717 END IF;
4718
4719 RETURN l_return_value;
4720 EXCEPTION
4721 WHEN xla_exceptions_pkg.application_exception
4722 THEN
4723 RAISE;
4724 WHEN OTHERS
4725 THEN
4726 xla_exceptions_pkg.raise_message
4727 (p_location => 'xla_cmp_tad_pkg.build_package_history'
4728 ,p_msg_mode => g_msg_mode
4729 );
4730 RETURN FALSE;
4731 END build_package_history;
4732
4733
4734
4735 /*======================================================================+
4736 | |
4737 | Private Function |
4738 | |
4739 | create_package_body |
4740 | |
4741 | |
4742 | |
4743 | |
4744 +======================================================================*/
4745 FUNCTION create_package_body
4746 (
4747 p_account_definition_code IN VARCHAR2
4748 ,p_account_definition_type_code IN VARCHAR2
4749 ,p_amb_context_code IN VARCHAR2
4750 ,p_package_name IN VARCHAR2
4751 ,p_tad_coa_id IN NUMBER
4752 ,p_table_of_tad_details IN gt_table_of_tad_details
4753 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
4754 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
4755 ,p_adr_bodies_text IN CLOB
4756 )
4757 RETURN BOOLEAN
4758 IS
4759 l_return_value BOOLEAN;
4760 l_package_body_text CLOB;
4761 l_tab_api_package_name VARCHAR2(30);
4762 l_fatal_message_text VARCHAR2(2000);
4763 l_log_module VARCHAR2 (2000);
4764
4765 BEGIN
4766 IF g_log_enabled THEN
4767 l_log_module := C_DEFAULT_MODULE||'.create_package_body';
4768 END IF;
4769
4770 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4771 trace
4772 (p_module => l_log_module
4773 ,p_msg => 'BEGIN ' || l_log_module
4774 ,p_level => C_LEVEL_PROCEDURE);
4775 END IF;
4776
4777 --build the package body
4778 IF NOT build_package_body
4779 (
4780 p_account_definition_code => p_account_definition_code
4781 ,p_account_definition_type_code => p_account_definition_type_code
4782 ,p_amb_context_code => p_amb_context_code
4783 ,p_package_name => p_package_name
4784 ,p_tad_coa_id => p_tad_coa_id
4785 ,p_table_of_tad_details => p_table_of_tad_details
4786 ,p_table_of_adrs => p_table_of_adrs
4787 ,p_table_of_adrs_ext => p_table_of_adrs_ext
4788 ,p_package_body_text => l_package_body_text
4789 ,p_adr_bodies_text => p_adr_bodies_text
4790 )
4791 THEN
4792 l_return_value := FALSE;
4793 IF (C_LEVEL_ERROR >= g_log_level) THEN
4794 trace
4795 (p_module => l_log_module
4796 ,p_msg => 'build_package_body failed'
4797 ,p_level => C_LEVEL_ERROR);
4798 END IF;
4799 --Useless to push
4800 RAISE ge_fatal_error;
4801 END IF;
4802
4803 IF NOT xla_cmp_create_pkg.push_database_object
4804 (
4805 p_object_name => p_package_name
4806 ,p_object_type => 'PACKAGE BODY'
4807 ,p_object_owner => NULL --current user
4808 ,p_apps_account => g_application_info.apps_account
4809 ,p_msg_mode => G_OA_MESSAGE
4810 ,p_ddl_text => l_package_body_text
4811 )
4812 THEN
4813 l_return_value := FALSE;
4814 IF (C_LEVEL_ERROR >= g_log_level) THEN
4815 trace
4816 (p_module => l_log_module
4817 ,p_msg => 'push_database_object failed'
4818 ,p_level => C_LEVEL_ERROR);
4819 END IF;
4820 END IF;
4821
4822 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4823 trace
4824 (p_module => l_log_module
4825 ,p_msg => 'END ' || l_log_module
4826 ,p_level => C_LEVEL_PROCEDURE);
4827 END IF;
4828
4829 RETURN l_return_value;
4830 EXCEPTION
4831 WHEN ge_fatal_error
4832 THEN
4833 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4834 trace
4835 (p_module => l_log_module
4836 ,p_msg => 'EXCEPTION:' ||
4837 ' Fatal error, aborting...'
4838 ,p_level => C_LEVEL_EXCEPTION);
4839 END IF;
4840 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4841 trace
4842 (p_module => l_log_module
4843 ,p_msg => l_fatal_message_text
4844 ,p_level => C_LEVEL_EXCEPTION);
4845 END IF;
4846 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4847 trace
4848 (p_module => l_log_module
4849 ,p_msg => 'END ' || l_log_module
4850 ,p_level => C_LEVEL_PROCEDURE);
4851 END IF;
4852 RETURN FALSE;
4853 WHEN xla_exceptions_pkg.application_exception
4854 THEN
4855 xla_exceptions_pkg.raise_message
4856 (p_location => 'xla_cmp_tad_pkg.create_package_body'
4857 ,p_msg_mode => 'NON_STANDARD'
4858 );
4859 RETURN FALSE;
4860 WHEN OTHERS
4861 THEN
4862 xla_exceptions_pkg.raise_message
4863 (p_location => 'xla_cmp_tad_pkg.create_package_body'
4864 ,p_msg_mode => g_msg_mode
4865 );
4866 RETURN FALSE;
4867 END create_package_body;
4868
4869
4870 /*======================================================================+
4871 | |
4872 | Private Function |
4873 | |
4874 | build_package_body |
4875 | |
4876 | |
4877 | |
4878 | |
4879 +======================================================================*/
4880 FUNCTION build_package_body
4881 (
4882 p_account_definition_code IN VARCHAR2
4883 ,p_account_definition_type_code IN VARCHAR2
4884 ,p_amb_context_code IN VARCHAR2
4885 ,p_package_name IN VARCHAR2
4886 ,p_tad_coa_id IN NUMBER
4887 ,p_table_of_tad_details IN gt_table_of_tad_details
4888 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
4889 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
4890 ,p_adr_bodies_text IN CLOB
4891 ,p_package_body_text OUT NOCOPY CLOB
4892 )
4893 RETURN BOOLEAN
4894 IS
4895 l_history CLOB;
4896
4897 l_push_interf_statements_text CLOB;
4898 l_pop_interf_statements_text CLOB;
4899
4900 l_update_statements_text CLOB;
4901
4902 l_batch_ccid_proc_stmts_text CLOB;
4903 l_package_body_text CLOB;
4904
4905 l_return_value BOOLEAN;
4906 l_log_module VARCHAR2 (2000);
4907 BEGIN
4908 IF g_log_enabled THEN
4909 l_log_module := C_DEFAULT_MODULE||'.build_package_body';
4910 END IF;
4911 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4912 trace
4913 (p_module => l_log_module
4914 ,p_msg => 'BEGIN ' || l_log_module
4915 ,p_level => C_LEVEL_PROCEDURE);
4916 END IF;
4917
4918 l_return_value := TRUE;
4919
4920 --Build the statements to move data from/to the plsql tab
4921 --and the global temp table
4922 IF NOT build_move_interf_data_stmts
4923 (
4924 p_table_of_tad_details => p_table_of_tad_details
4925 ,p_tad_coa_id => p_tad_coa_id
4926 ,x_push_interf_statements_text => l_push_interf_statements_text
4927 ,x_pop_interf_statements_text => l_pop_interf_statements_text
4928 )
4929 THEN
4930 l_return_value := FALSE;
4931 IF (C_LEVEL_ERROR >= g_log_level) THEN
4932 trace
4933 (p_module => l_log_module
4934 ,p_msg => 'ERROR:' ||
4935 ' build_move_interf_data_stmts failed'
4936 ,p_level => C_LEVEL_ERROR);
4937 END IF;
4938 END IF;
4939
4940 --Build the statements process data into the global temp table
4941 IF NOT build_batch_update_statements
4942 (
4943 p_table_of_tad_details => p_table_of_tad_details
4944 ,p_table_of_adrs => p_table_of_adrs
4945 ,p_table_of_adrs_ext => p_table_of_adrs_ext
4946 ,p_update_statements_text => l_update_statements_text
4947 )
4948 THEN
4949 l_return_value := FALSE;
4950 IF (C_LEVEL_ERROR >= g_log_level) THEN
4951 trace
4952 (p_module => l_log_module
4953 ,p_msg => 'ERROR:' ||
4954 ' build_batch_update_statements failed'
4955 ,p_level => C_LEVEL_ERROR);
4956 END IF;
4957 END IF;
4958
4959 --Build the ccid processing statements
4960 IF NOT build_static_ccid_prc_stmts
4961 (
4962 p_table_of_tad_details => p_table_of_tad_details
4963 ,p_tad_coa_id => p_tad_coa_id
4964 ,p_update_statements_text => l_batch_ccid_proc_stmts_text
4965 )
4966 THEN
4967 l_return_value := FALSE;
4968 IF (C_LEVEL_ERROR >= g_log_level) THEN
4969 trace
4970 (p_module => l_log_module
4971 ,p_msg => 'ERROR:' ||
4972 ' build_static_ccid_prc_stmts failed'
4973 ,p_level => C_LEVEL_ERROR);
4974 END IF;
4975 END IF;
4976
4977 --take the package body template
4978 l_package_body_text := C_TMPL_TAD_PACKAGE_BODY;
4979
4980 --replace the ADR functions bodies token
4981 l_package_body_text:= xla_cmp_common_pkg.replace_token
4982 (
4983 p_original_text => l_package_body_text
4984 ,p_token => '$TAD_ADR_FUNCT_BODIES$'
4985 ,p_replacement_text => p_adr_bodies_text
4986 );
4987
4988 --replace the push interface data update statements token
4989 l_package_body_text:= xla_cmp_common_pkg.replace_token
4990 (
4991 p_original_text => l_package_body_text
4992 ,p_token => '$C_TMPL_PUSH_INTERF_DATA_STMTS$'
4993 ,p_replacement_text => NVL(l_push_interf_statements_text, ' ')
4994 );
4995
4996 --replace the pop interface data update statements token
4997 l_package_body_text:= xla_cmp_common_pkg.replace_token
4998 (
4999 p_original_text => l_package_body_text
5000 ,p_token => '$C_TMPL_POP_INTERF_DATA_STMTS$'
5001 ,p_replacement_text => NVL(l_pop_interf_statements_text, ' ')
5002 );
5003
5004 --replace the update statements token
5005 l_package_body_text:= xla_cmp_common_pkg.replace_token
5006 (
5007 p_original_text => l_package_body_text
5008 ,p_token => '$C_TMPL_BATCH_CCID_SEG_UPD_STMTS$'
5009 ,p_replacement_text => NVL(l_update_statements_text, ' ')
5010 );
5011
5012 --replace the ccid processing statements token
5013 l_package_body_text:= xla_cmp_common_pkg.replace_token
5014 (
5015 p_original_text => l_package_body_text
5016 ,p_token => '$C_TMPL_BATCH_BUILD_CCID_STMTS$'
5017 ,p_replacement_text => NVL(l_batch_ccid_proc_stmts_text, ' ')
5018 );
5019
5020 --replace the package name tokens
5021 /* Commented due to the bug 6354106
5022 l_package_body_text := REPLACE
5023 (
5024 l_package_body_text
5025 ,'$TAD_PACKAGE_NAME_1$'
5026 ,p_package_name
5027 );
5028 Introducing the following due to the bug 6354106
5029 */
5030
5031 l_package_body_text:= xla_cmp_common_pkg.replace_token
5032 (
5033 p_original_text => l_package_body_text
5034 ,p_token => '$TAD_PACKAGE_NAME_1$'
5035 ,p_replacement_text => p_package_name
5036 );
5037
5038
5039 l_package_body_text := xla_cmp_string_pkg.replace_token
5040 (
5041 l_package_body_text
5042 ,'$TAD_PACKAGE_NAME_2$'
5043 ,RPAD( p_package_name
5044 , 66
5045 , ' '
5046 )
5047 || '|'
5048 );
5049
5050 l_package_body_text := xla_cmp_string_pkg.replace_token
5051 (
5052 l_package_body_text
5053 ,'$TAD_PACKAGE_NAME_3$'
5054 ,LOWER(p_package_name)
5055 );
5056
5057
5058 --replace the application name token
5059 l_package_body_text := xla_cmp_string_pkg.replace_token
5060 (
5061 l_package_body_text
5062 ,'$APPLICATION_NAME$'
5063 ,RPAD( g_application_info.application_name
5064 , 66
5065 , ' '
5066 ) || '|'
5067 );
5068
5069 --replace the application id token
5070 l_package_body_text := xla_cmp_string_pkg.replace_token
5071 (
5072 l_package_body_text
5073 ,'$APPLICATION_ID$'
5074 ,RPAD( TO_CHAR(g_application_info.application_id) || ')'
5075 , 49
5076 , ' '
5077 ) || '|'
5078 );
5079 l_package_body_text := xla_cmp_string_pkg.replace_token
5080 (
5081 l_package_body_text
5082 ,'$APPLICATION_ID_2$'
5083 ,TO_CHAR(g_application_info.application_id)
5084 );
5085
5086 --replace the TAD info tokens
5087 l_package_body_text := xla_cmp_string_pkg.replace_token
5088 (
5089 l_package_body_text
5090 ,'$TAD_CODE$'
5091 ,RPAD( p_account_definition_code
5092 , 48
5093 , ' '
5094 )
5095 || '|'
5096 );
5097 l_package_body_text := xla_cmp_string_pkg.replace_token
5098 (
5099 l_package_body_text
5100 ,'$TAD_CODE_2$'
5101 ,p_account_definition_code
5102 );
5103
5104 l_package_body_text := xla_cmp_string_pkg.replace_token
5105 (
5106 l_package_body_text
5107 ,'$TAD_TYPE_CODE$'
5108 ,RPAD( p_account_definition_type_code
5109 , 48
5110 , ' '
5111 )
5112 || '|'
5113 );
5114 l_package_body_text := xla_cmp_string_pkg.replace_token
5115 (
5116 l_package_body_text
5117 ,'$TAD_TYPE_CODE_2$'
5118 ,p_account_definition_type_code
5119 );
5120
5121 l_package_body_text := xla_cmp_string_pkg.replace_token
5122 (
5123 l_package_body_text
5124 ,'$AMB_CONTEXT_CODE$'
5125 ,RPAD( p_amb_context_code
5126 , 48
5127 , ' '
5128 )
5129 || '|'
5130 );
5131 l_package_body_text := xla_cmp_string_pkg.replace_token
5132 (
5133 l_package_body_text
5134 ,'$AMB_CONTEXT_CODE_2$'
5135 ,p_amb_context_code
5136 );
5137
5138 --Assign the OUT params
5139 p_package_body_text := l_package_body_text;
5140
5141 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5142 trace
5143 (p_module => l_log_module
5144 ,p_msg => 'END ' || l_log_module
5145 ,p_level => C_LEVEL_PROCEDURE);
5146 END IF;
5147
5148 RETURN l_return_value;
5149
5150 EXCEPTION
5151 WHEN ge_fatal_error
5152 THEN
5153 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5154 trace
5155 (p_module => l_log_module
5156 ,p_msg => 'EXCEPTION:' ||
5157 ' Fatal error, aborting...'
5158 ,p_level => C_LEVEL_EXCEPTION);
5159 END IF;
5160 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5161 trace
5162 (p_module => l_log_module
5163 ,p_msg => 'END ' || l_log_module
5164 ,p_level => C_LEVEL_PROCEDURE);
5165 END IF;
5166 RETURN FALSE;
5167 WHEN xla_exceptions_pkg.application_exception
5168 THEN
5169 RAISE;
5170 WHEN OTHERS
5171 THEN
5172 xla_exceptions_pkg.raise_message
5173 (p_location => 'xla_cmp_tad_pkg.build_package_body'
5174 ,p_msg_mode => g_msg_mode
5175 );
5176 RETURN FALSE;
5177 END build_package_body;
5178
5179
5180
5181 /*======================================================================+
5182 | |
5183 | Private Function |
5184 | |
5185 | build_batch_update_statements |
5186 | |
5187 | |
5188 | |
5189 | |
5190 +======================================================================*/
5191 FUNCTION build_batch_update_statements
5192 (
5193 p_table_of_tad_details IN gt_table_of_tad_details
5194 ,p_table_of_adrs IN xla_cmp_adr_pkg.gt_table_of_adrs_in
5195 ,p_table_of_adrs_ext IN xla_cmp_adr_pkg.gt_table_of_adrs_out
5196 ,p_update_statements_text OUT NOCOPY CLOB
5197 )
5198 RETURN BOOLEAN
5199 IS
5200 l_update_statement_text CLOB;
5201 l_update_statements_text CLOB;
5202 l_set_clause_text CLOB;
5203 l_set_clauses_text CLOB;
5204 l_case_branch_text CLOB;
5205 l_case_branches_text CLOB;
5206 l_adr_funct_params CLOB;
5207 l_current_object_name_affix VARCHAR2(10);
5208 l_current_flex_segment_code VARCHAR2(30);
5209 l_current_account_type_code VARCHAR2(30);
5210 l_current_temp_table_name VARCHAR2(30);
5211 l_dummy VARCHAR2(30);
5212 l_adr_function_name VARCHAR2(30);
5213 l_table_of_adr_sources xla_cmp_adr_pkg.gt_table_of_adr_sources;
5214 l_fatal_message_text VARCHAR2(2000);
5215 l_return_value BOOLEAN;
5216 l_log_module VARCHAR2 (2000);
5217 BEGIN
5218 IF g_log_enabled THEN
5219 l_log_module := C_DEFAULT_MODULE||'.build_batch_update_statements';
5220 END IF;
5221
5222 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5223 trace
5224 (p_module => l_log_module
5225 ,p_msg => 'BEGIN ' || l_log_module
5226 ,p_level => C_LEVEL_PROCEDURE);
5227 END IF;
5228
5229 l_return_value := TRUE;
5230
5231 l_current_object_name_affix := NULL;
5232 l_current_flex_segment_code := NULL;
5233 l_current_account_type_code := NULL;
5234 FOR i IN p_table_of_tad_details.FIRST .. p_table_of_tad_details.LAST
5235 LOOP
5236 --If it is the first detail
5237 --or the affix changes
5238 --we need a new update statement
5239 IF (i = p_table_of_tad_details.FIRST)
5240 OR ( NVL(l_current_object_name_affix, 'a')
5241 <> NVL(p_table_of_tad_details(i).object_name_affix, 'a')
5242 )
5243 THEN
5244 --If not the first detail there is a SQL statement in the pipeline
5245 IF i <> p_table_of_tad_details.FIRST
5246 THEN
5247 --Chain the current case branch with the existing ones
5248 l_case_branches_text := l_case_branches_text || l_case_branch_text;
5249
5250 --Replace the current case branches to the current set clause
5251 l_set_clause_text:= xla_cmp_common_pkg.replace_token
5252 (
5253 p_original_text => l_set_clause_text
5254 ,p_token => '$CASE_BRANCHES$'
5255 ,p_replacement_text => NVL(l_case_branches_text, ' ')
5256 );
5257
5258 --Concatenate the current set clause to the already existing ones
5259 l_set_clauses_text := l_set_clauses_text || l_set_clause_text;
5260
5261 --Replace the accumulated update set clauses
5262 --in the current upd statement
5263 l_update_statement_text:= xla_cmp_common_pkg.replace_token
5264 (
5265 p_original_text => l_update_statement_text
5266 ,p_token => '$C_TMPL_SET_CLAUSES$'
5267 ,p_replacement_text => NVL(l_set_clauses_text, ' ')
5268 );
5269
5270 --Concatenate the current upd statement to the existing ones
5271 l_update_statements_text := l_update_statements_text || l_update_statement_text;
5272
5273 --Null out the partial elements that have been consumed now
5274 l_case_branch_text := NULL;
5275 l_case_branches_text := NULL;
5276 l_set_clause_text := NULL;
5277 l_set_clauses_text := NULL;
5278 l_update_statement_text := NULL;
5279
5280 --The new update statement has no dependency on the previous ones
5281 l_current_account_type_code := NULL;
5282 l_current_flex_segment_code := NULL;
5283 END IF;
5284
5285 --Retrieve the affix of the TAT associated to the current detail
5286 l_current_object_name_affix := p_table_of_tad_details(i).object_name_affix;
5287
5288 --Get the global temporary table name for the affix
5289 IF NOT xla_cmp_tab_pkg.get_interface_object_names
5290 (
5291 p_application_id => g_application_info.application_id
5292 ,p_object_name_affix => l_current_object_name_affix
5293 ,x_global_table_name => l_current_temp_table_name
5294 ,x_plsql_table_name => l_dummy
5295 )
5296 THEN
5297 l_fatal_message_text := 'get_interface_object_names failed';
5298 END IF;
5299
5300 --Get the update statement template
5301 l_update_statement_text := C_TMPL_BATCH_CCID_SEG_UPD_STMT;
5302
5303 --Replace the table name token
5304 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5305 l_update_statement_text
5306 ,'$table_name$'
5307 ,NVL(l_current_temp_table_name, ' ')
5308 );
5309 END IF; --new update statement
5310
5311 --If the segment code has changed or is the first one we process
5312 --initialize a new set clause
5313 IF ( l_current_flex_segment_code
5314 <> p_table_of_tad_details(i).flexfield_segment_code
5315 )
5316 OR l_current_flex_segment_code IS NULL
5317 THEN
5318 l_current_flex_segment_code
5319 := p_table_of_tad_details(i).flexfield_segment_code;
5320 --If there is a pending set clause
5321 IF l_set_clause_text IS NOT NULL
5322 THEN
5323 --Chain the current case branch with the existing ones
5324 l_case_branches_text := l_case_branches_text || l_case_branch_text;
5325 --Replace the current case branches in the current set clause
5326 l_set_clause_text:= xla_cmp_common_pkg.replace_token
5327 (
5328 p_original_text => l_set_clause_text
5329 ,p_token => '$CASE_BRANCHES$'
5330 ,p_replacement_text => NVL(l_case_branches_text, ' ')
5331 );
5332
5333 --Chain it to the other clauses
5334 l_set_clauses_text := l_set_clauses_text || l_set_clause_text;
5335
5336 --Null out the partial elements that have been consumed now
5337 l_case_branch_text := NULL;
5338 l_case_branches_text := NULL;
5339 l_set_clause_text := NULL;
5340
5341 --The new set clause has no dependency on the previous one
5342 l_current_account_type_code := NULL;
5343 END IF;
5344
5345 --If there is already some set clause we need to add a comma
5346 --at the beginning of the clause
5347 IF l_set_clauses_text IS NOT NULL
5348 THEN
5349 l_set_clause_text := ' ,';
5350 ELSE
5351 l_set_clause_text := ' ';
5352 END IF;
5353
5354 --If the flex segment code is ALL
5355 IF p_table_of_tad_details(i).flexfield_segment_code = 'ALL'
5356 THEN
5357 --Get the template for the ccid SET clause
5358 l_set_clause_text := l_set_clause_text || C_TMPL_SET_CCID;
5359 ELSE
5360 l_set_clause_text := l_set_clause_text || C_TMPL_SET_SEGMENT;
5361 l_set_clause_text :=
5362 xla_cmp_string_pkg.replace_token(
5363 l_set_clause_text
5364 ,'$SEGMENT_COLUMN_NAME$'
5365 ,p_table_of_tad_details(i).flexfield_segment_code
5366 );
5367 END IF;
5368 END IF;
5369
5370 --If the TAT code has changed or is NULL we need a new case branch
5371 IF ( l_current_account_type_code
5372 <> p_table_of_tad_details(i).account_type_code
5373 )
5374 OR l_current_account_type_code IS NULL
5375 THEN
5376 --Chain the current case branch with the existing ones
5377 l_case_branches_text := l_case_branches_text || l_case_branch_text;
5378 l_case_branch_text := C_TMPL_CASE_BRANCH;
5379
5380 --Get the TAT code of the current detail
5381 l_current_account_type_code := p_table_of_tad_details(i).account_type_code;
5382
5383 --Replace the account type token in the new branch
5384 l_case_branch_text:= xla_cmp_common_pkg.replace_token
5385 (
5386 p_original_text => l_case_branch_text
5387 ,p_token => '$ACCOUNT_TYPE$'
5388 ,p_replacement_text => NVL(l_current_account_type_code, ' ')
5389 );
5390
5391 END IF; --new TAT code
5392
5393 --Get the ADR function name associated to the detail
5394 l_adr_function_name := NULL;
5395 IF p_table_of_adrs.FIRST IS NOT NULL
5396 THEN
5397 FOR adr_index IN p_table_of_adrs.FIRST..p_table_of_adrs.LAST
5398 LOOP
5399 IF p_table_of_adrs(adr_index).application_id
5400 = p_table_of_tad_details(i).application_id
5401 AND p_table_of_adrs(adr_index).segment_rule_type_code
5402 = p_table_of_tad_details(i).segment_rule_type_code
5403 AND p_table_of_adrs(adr_index).segment_rule_code
5404 = p_table_of_tad_details(i).segment_rule_code
5405 AND p_table_of_adrs(adr_index).amb_context_code
5406 = p_table_of_tad_details(i).amb_context_code
5407 THEN
5408 --Get the ADR function name
5409 l_adr_function_name
5410 := p_table_of_adrs_ext(adr_index).adr_function_name;
5411
5412 --Get the source parameter list for the function
5413 l_table_of_adr_sources
5414 := p_table_of_adrs_ext(adr_index).table_of_sources;
5415 EXIT;
5416 END IF;
5417 END LOOP;
5418 END IF;
5419
5420 --Replace the function name token
5421 l_case_branch_text:= xla_cmp_string_pkg.replace_token(
5422 l_case_branch_text
5423 ,'$ADR_FUNCT_NAME$'
5424 ,l_adr_function_name
5425 );
5426
5427 --Concatenate the function source params
5428 l_adr_funct_params := NULL;
5429 DECLARE
5430 -- n VARCHAR2(30);
5431 n NUMBER;
5432 BEGIN
5433 n := l_table_of_adr_sources.FIRST;
5434 WHILE n IS NOT NULL
5435 LOOP
5436 --Add the current parameter
5437 l_adr_funct_params := l_adr_funct_params
5438 || ' ,'
5439 || RPAD(l_table_of_adr_sources(n), 30, ' ')
5440 || ' --p_source_'
5441 || n;
5442 --Add a linefeed
5443 l_adr_funct_params := l_adr_funct_params ||
5444 '
5445 ';
5446 -- get the subscript of next element
5447 n := l_table_of_adr_sources.NEXT(n);
5448 END LOOP;
5449 END;
5450
5451 --Replace the function source params token
5452 l_case_branch_text:= xla_cmp_string_pkg.replace_token(
5453 l_case_branch_text
5454 ,'$C_TMPL_ADR_FUNCT_PARAMS$'
5455 ,NVL(l_adr_funct_params, ' ')
5456 );
5457 END LOOP;
5458
5459 --Add the case branch text to the existing ones
5460 l_case_branches_text := l_case_branches_text || l_case_branch_text;
5461
5462 --Replace the current case branches in the current set clause
5463 l_set_clause_text:= xla_cmp_common_pkg.replace_token
5464 (
5465 p_original_text => l_set_clause_text
5466 ,p_token => '$CASE_BRANCHES$'
5467 ,p_replacement_text => NVL(l_case_branches_text, ' ')
5468 );
5469
5470 --Concatenate the current set clause to the already existing ones
5471 l_set_clauses_text := l_set_clauses_text || l_set_clause_text;
5472
5473 --Replace the accumulated update set clauses in the current upd statement
5474 l_update_statement_text:= xla_cmp_common_pkg.replace_token
5475 (
5476 p_original_text => l_update_statement_text
5477 ,p_token => '$C_TMPL_SET_CLAUSES$'
5478 ,p_replacement_text => NVL(l_set_clauses_text, ' ')
5479 );
5480
5481 --Concatenate the last processed statement
5482 l_update_statements_text := l_update_statements_text || l_update_statement_text;
5483
5484 --Assing the out parameter
5485 p_update_statements_text := l_update_statements_text;
5486
5487 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5488 trace
5489 (p_module => l_log_module
5490 ,p_msg => 'END ' || l_log_module
5491 ,p_level => C_LEVEL_PROCEDURE);
5492 END IF;
5493
5494 RETURN l_return_value;
5495
5496 EXCEPTION
5497 WHEN ge_fatal_error
5498 THEN
5499 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5500 trace
5501 ( p_module => l_log_module
5502 ,p_msg => 'EXCEPTION:' ||
5503 ' Fatal error, aborting...'
5504 ,p_level => C_LEVEL_EXCEPTION);
5505 trace
5506 ( p_module => l_log_module
5507 ,p_msg => l_fatal_message_text
5508 ,p_level => C_LEVEL_EXCEPTION);
5509 END IF;
5510 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5511 trace
5512 (p_module => l_log_module
5513 ,p_msg => 'END ' || l_log_module
5514 ,p_level => C_LEVEL_PROCEDURE);
5515 END IF;
5516 RETURN FALSE;
5517 WHEN xla_exceptions_pkg.application_exception
5518 THEN
5519 RAISE;
5520 WHEN OTHERS
5521 THEN
5522 xla_exceptions_pkg.raise_message
5523 (p_location => 'xla_cmp_tad_pkg.build_batch_update_statements'
5524 ,p_msg_mode => g_msg_mode
5525 );
5526 RETURN FALSE;
5527 END build_batch_update_statements;
5528
5529 /*======================================================================+
5530 | |
5531 | Private Function |
5532 | |
5533 | build_static_ccid_prc_stmts |
5534 | |
5535 | |
5536 | |
5537 | |
5538 +======================================================================*/
5539 FUNCTION build_static_ccid_prc_stmts
5540 (
5541 p_table_of_tad_details IN gt_table_of_tad_details
5542 ,p_tad_coa_id IN NUMBER
5543 ,p_update_statements_text OUT NOCOPY CLOB
5544 )
5545 RETURN BOOLEAN
5546 IS
5547 l_update_statement_text CLOB;
5548 l_update_statements_text CLOB;
5549
5550 l_tmpl_where_segment_null_and CLOB;
5551 l_tmpl_where_segment_null_ands CLOB;
5552 l_tmpl_where_segment_null_or CLOB;
5553 l_tmpl_where_segment_null_ors CLOB;
5554 l_tmpl_upd_set_segment_comma CLOB;
5555 l_tmpl_upd_set_segment_commas CLOB;
5556 l_tmpl_sel_nvl_segment_comma CLOB;
5557 l_tmpl_sel_nvl_segment_commas CLOB;
5558 l_tmpl_where_segments_equal CLOB;
5559 l_tmpl_where_segments_equals CLOB;
5560 l_tmpl_concat_segments CLOB;
5561
5562 l_current_object_name_affix VARCHAR2(10);
5563 l_current_temp_table_name VARCHAR2(30);
5564 l_dummy VARCHAR2(30);
5565
5566 l_table_segment_qualifiers gt_table_V30_V30;
5567 l_table_segment_column_names gt_table_V30;
5568 l_flex_delimiter VARCHAR2(1);
5569 l_concat_segments_template VARCHAR2(1000);
5570 l_chart_of_accounts_name VARCHAR2(80);
5571
5572 l_fatal_message_text VARCHAR2(2000);
5573 l_return_value BOOLEAN;
5574 l_log_module VARCHAR2 (2000);
5575 BEGIN
5576 IF g_log_enabled THEN
5577 l_log_module := C_DEFAULT_MODULE||'.build_static_ccid_prc_stmts';
5578 END IF;
5579
5580 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5581 trace
5582 (p_module => l_log_module
5583 ,p_msg => 'BEGIN ' || l_log_module
5584 ,p_level => C_LEVEL_PROCEDURE);
5585 END IF;
5586
5587 l_return_value := TRUE;
5588
5589 IF p_tad_coa_id IS NULL
5590 THEN
5591 l_update_statements_text := C_TMPL_BATCH_BLD_CCID_DYN_STMS;
5592 ELSE
5593 --retrieve the active flex segments
5594 IF NOT get_coa_info
5595 (
5596 p_chart_of_accounts_id => p_tad_coa_id
5597 ,p_chart_of_accounts_name => l_chart_of_accounts_name
5598 ,p_flex_delimiter => l_flex_delimiter
5599 ,p_concat_segments_template => l_concat_segments_template
5600 ,p_table_segment_qualifiers => l_table_segment_qualifiers
5601 ,p_table_segment_column_names => l_table_segment_column_names
5602 )
5603 THEN
5604 l_return_value := FALSE;
5605 IF (C_LEVEL_ERROR >= g_log_level) THEN
5606 trace
5607 (p_module => l_log_module
5608 ,p_msg => 'ERROR:' ||
5609 ' get_coa_info failed'
5610 ,p_level => C_LEVEL_ERROR);
5611 END IF;
5612 END IF;
5613
5614 --Build the common token values
5615 FOR i IN l_table_segment_column_names.FIRST..l_table_segment_column_names.LAST
5616 LOOP
5617 --C_TMPL_WHERE_SEGMENT_NULL_ANDS
5618 l_tmpl_where_segment_null_and := xla_cmp_string_pkg.replace_token
5619 ( '
5620 AND gt.$SEGMENT_COLUMN_NAME$ IS NULL '
5621 ,'$SEGMENT_COLUMN_NAME$'
5622 ,l_table_segment_column_names(i)
5623 );
5624 l_tmpl_where_segment_null_ands := l_tmpl_where_segment_null_ands
5625 || l_tmpl_where_segment_null_and;
5626
5627
5628 --C_TMPL_WHERE_SEGMENT_NULL_ORS
5629
5630 l_tmpl_where_segment_null_or := CASE i
5631 WHEN 1 THEN ' '
5632 ELSE ' OR '
5633 END
5634 ||
5635 xla_cmp_string_pkg.replace_token
5636 ( ' gt.$SEGMENT_COLUMN_NAME$ IS NULL
5637 '
5638 ,'$SEGMENT_COLUMN_NAME$'
5639 ,l_table_segment_column_names(i)
5640 );
5641
5642 l_tmpl_where_segment_null_ors := l_tmpl_where_segment_null_ors
5643 || l_tmpl_where_segment_null_or;
5644
5645 --C_TMPL_UPD_SET_SEGMENT_COMMA
5646 l_tmpl_upd_set_segment_comma := CASE i
5647 WHEN 1 THEN ' '
5648 ELSE ' ,'
5649 END
5650 ||
5651 xla_cmp_string_pkg.replace_token
5652 ( ' gt.$SEGMENT_COLUMN_NAME$
5653 '
5654 ,'$SEGMENT_COLUMN_NAME$'
5655 ,l_table_segment_column_names(i)
5656 );
5657
5658 l_tmpl_upd_set_segment_commas := l_tmpl_upd_set_segment_commas
5659 || l_tmpl_upd_set_segment_comma;
5660
5661 --C_TMPL_SEL_NVL_SEGMENT_COMMA
5662 l_tmpl_sel_nvl_segment_comma := CASE i
5663 WHEN 1 THEN ' '
5664 ELSE ' ,'
5665 END
5666 ||
5667 xla_cmp_string_pkg.replace_token
5668 ( ' NVL(gt.$SEGMENT_COLUMN_NAME$, gcc.$SEGMENT_COLUMN_NAME$)
5669 '
5670 ,'$SEGMENT_COLUMN_NAME$'
5671 ,l_table_segment_column_names(i)
5672 );
5673
5674 l_tmpl_sel_nvl_segment_commas := l_tmpl_sel_nvl_segment_commas
5675 || l_tmpl_sel_nvl_segment_comma;
5676
5677 --C_TMPL_WHERE_SEGMENTS_EQUAL
5678 l_tmpl_where_segments_equal := xla_cmp_string_pkg.replace_token
5679 ( ' AND gcc.$SEGMENT_COLUMN_NAME$ = gt.$SEGMENT_COLUMN_NAME$
5680 '
5681 ,'$SEGMENT_COLUMN_NAME$'
5682 ,l_table_segment_column_names(i)
5683 );
5684 l_tmpl_where_segments_equals := l_tmpl_where_segments_equals
5685 || l_tmpl_where_segments_equal;
5686
5687 IF i = 1
5688 THEN
5689 l_tmpl_concat_segments := ' '
5690 || l_table_segment_column_names(i);
5691 ELSE
5692 l_tmpl_concat_segments := l_tmpl_concat_segments
5693 || '
5694 '
5695 || ' || ''' || l_flex_delimiter || ''' || '
5696 || l_table_segment_column_names(i);
5697 END IF;
5698
5699 END LOOP;
5700
5701 l_current_object_name_affix := NULL;
5702
5703 FOR i IN p_table_of_tad_details.FIRST .. p_table_of_tad_details.LAST
5704 LOOP
5705 --If it is the first detail
5706 --or the affix changes
5707 --we need a new update statement
5708 IF (i = p_table_of_tad_details.FIRST)
5709 OR ( NVL(l_current_object_name_affix, 'a')
5710 <> NVL(p_table_of_tad_details(i).object_name_affix, 'a')
5711 )
5712 THEN
5713 --Concatenate the current upd statement to the existing ones
5714 l_update_statements_text := l_update_statements_text || l_update_statement_text;
5715
5716 --Null out the partial elements that have been consumed now
5717 l_update_statement_text := NULL;
5718
5719 --Retrieve the affix of the TAT associated to the current detail
5720 l_current_object_name_affix := p_table_of_tad_details(i).object_name_affix;
5721
5722 --Get the global temporary table name for the affix
5723 IF NOT xla_cmp_tab_pkg.get_interface_object_names
5724 (
5725 p_application_id => g_application_info.application_id
5726 ,p_object_name_affix => l_current_object_name_affix
5727 ,x_global_table_name => l_current_temp_table_name
5728 ,x_plsql_table_name => l_dummy
5729 )
5730 THEN
5731 l_fatal_message_text := 'get_interface_object_names failed';
5732 END IF;
5733
5734 --Get the update statement template
5735 l_update_statement_text := C_TMPL_BATCH_BUILD_CCID_STMTS;
5736
5737 --Replace the table name token
5738 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5739 l_update_statement_text
5740 ,'$TABLE_NAME$'
5741 ,NVL(l_current_temp_table_name, ' ')
5742 );
5743
5744 --Replace the common tokens
5745 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5746 l_update_statement_text
5747 ,'$C_TMPL_WHERE_SEGMENT_NULL_ANDS$'
5748 ,NVL(l_tmpl_where_segment_null_ands, ' ')
5749 );
5750
5751 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5752 l_update_statement_text
5753 ,'$C_TMPL_WHERE_SEGMENT_NULL_ORS$'
5754 ,NVL(l_tmpl_where_segment_null_ors, ' ')
5755 );
5756
5757 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5758 l_update_statement_text
5759 ,'$C_TMPL_UPD_SET_SEGMENT_COMMAS$'
5760 ,NVL(l_tmpl_upd_set_segment_commas, ' ')
5761 );
5762
5763 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5764 l_update_statement_text
5765 ,'$C_TMPL_SEL_NVL_SEGMENT_COMMAS$'
5766 ,NVL(l_tmpl_sel_nvl_segment_commas, ' ')
5767 );
5768
5769 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5770 l_update_statement_text
5771 ,'$C_TMPL_WHERE_SEGMENTS_EQUALS$'
5772 ,NVL(l_tmpl_where_segments_equals, ' ')
5773 );
5774
5775 l_update_statement_text:= xla_cmp_string_pkg.replace_token(
5776 l_update_statement_text
5777 ,'$C_TMPL_CONCAT_SEGMENTS$'
5778 ,NVL(l_tmpl_concat_segments, ' ')
5779 );
5780
5781 ELSE
5782 --No action required
5783 NULL;
5784 END IF; --new update statement
5785 END LOOP;
5786
5787 --Concatenate the last processed statement
5788 l_update_statements_text := l_update_statements_text || l_update_statement_text;
5789
5790 END IF;
5791
5792 --Assing the out parameter
5793 p_update_statements_text := l_update_statements_text;
5794
5795 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5796 trace
5797 (p_module => l_log_module
5798 ,p_msg => 'END ' || l_log_module
5799 ,p_level => C_LEVEL_PROCEDURE);
5800 END IF;
5801
5802 RETURN l_return_value;
5803
5804 EXCEPTION
5805 WHEN ge_fatal_error
5806 THEN
5807 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5808 trace
5809 ( p_module => l_log_module
5810 ,p_msg => 'EXCEPTION:' ||
5811 ' Fatal error, aborting...'
5812 ,p_level => C_LEVEL_EXCEPTION);
5813 trace
5814 ( p_module => l_log_module
5815 ,p_msg => l_fatal_message_text
5816 ,p_level => C_LEVEL_EXCEPTION);
5817 END IF;
5818 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5819 trace
5820 (p_module => l_log_module
5821 ,p_msg => 'END ' || l_log_module
5822 ,p_level => C_LEVEL_PROCEDURE);
5823 END IF;
5824 RETURN FALSE;
5825 WHEN xla_exceptions_pkg.application_exception
5826 THEN
5827 RAISE;
5828 WHEN OTHERS
5829 THEN
5830 xla_exceptions_pkg.raise_message
5831 (p_location => 'xla_cmp_tad_pkg.build_static_ccid_prc_stmts'
5832 ,p_msg_mode => g_msg_mode
5833 );
5834 RETURN FALSE;
5835 END build_static_ccid_prc_stmts;
5836
5837
5838
5839 /*======================================================================+
5840 | |
5841 | Private Function |
5842 | |
5843 | build_move_interf_data_stmts |
5844 | |
5845 | |
5846 | |
5847 | |
5848 +======================================================================*/
5849 FUNCTION build_move_interf_data_stmts
5850 (
5851 p_table_of_tad_details IN gt_table_of_tad_details
5852 ,p_tad_coa_id IN NUMBER
5853 ,x_push_interf_statements_text OUT NOCOPY CLOB
5854 ,x_pop_interf_statements_text OUT NOCOPY CLOB
5855 )
5856 RETURN BOOLEAN
5857 IS
5858 l_push_interf_statement_text CLOB;
5859 l_push_interf_statements_text CLOB;
5860
5861 l_pop_interf_statement_text CLOB;
5862 l_pop_interf_statements_text CLOB;
5863
5864 l_tmpl_tab_push_interf_sources CLOB;
5865 l_tmpl_tab_pop_interf_sources CLOB;
5866
5867 l_tab_api_package_name VARCHAR2(30);
5868
5869 l_current_object_name_affix VARCHAR2(10);
5870 l_current_temp_table_name VARCHAR2(30);
5871 l_current_plsql_table_name VARCHAR2(30);
5872
5873 l_table_of_sources xla_cmp_tab_pkg.gt_table_of_varchar2_30;
5874 l_table_of_source_datatypes xla_cmp_tab_pkg.gt_table_of_varchar2_1;
5875
5876 l_fatal_message_text VARCHAR2(2000);
5877 l_return_value BOOLEAN;
5878 l_log_module VARCHAR2 (2000);
5879
5880 BEGIN
5881 IF g_log_enabled THEN
5882 l_log_module := C_DEFAULT_MODULE||'.build_move_interf_data_stmts';
5883 END IF;
5884
5885 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5886 trace
5887 (p_module => l_log_module
5888 ,p_msg => 'BEGIN ' || l_log_module
5889 ,p_level => C_LEVEL_PROCEDURE);
5890 END IF;
5891
5892 l_return_value := TRUE;
5893
5894 --Get the TAB API package for the application
5895 IF NOT xla_cmp_tab_pkg.get_tab_api_package_name
5896 (
5897 p_application_id => g_application_info.application_id
5898 ,x_tab_api_package_name => l_tab_api_package_name
5899 )
5900 THEN
5901 l_fatal_message_text := 'get_tab_api_package_name failed';
5902 RAISE ge_fatal_error;
5903 END IF;
5904
5905 l_current_object_name_affix := NULL;
5906
5907 FOR i IN p_table_of_tad_details.FIRST .. p_table_of_tad_details.LAST
5908 LOOP
5909 --If it is the first detail
5910 --or the affix changes
5911 --we need a new update statement
5912 IF (i = p_table_of_tad_details.FIRST)
5913 OR ( NVL(l_current_object_name_affix, 'a')
5914 <> NVL(p_table_of_tad_details(i).object_name_affix, 'a')
5915 )
5916 THEN
5917 --Concatenate the current push and pop stmts to the existing ones
5918 l_push_interf_statements_text := l_push_interf_statements_text
5919 || l_push_interf_statement_text;
5920
5921 l_pop_interf_statements_text := l_pop_interf_statements_text
5922 || l_pop_interf_statement_text;
5923
5924 --Null out the partial elements that have been consumed now
5925 l_push_interf_statement_text := NULL;
5926 l_pop_interf_statement_text := NULL;
5927
5928 --Retrieve the affix of the TAT associated to the current detail
5929 l_current_object_name_affix := p_table_of_tad_details(i).object_name_affix;
5930
5931 --Get the global temporary table name for the affix
5932 IF NOT xla_cmp_tab_pkg.get_interface_object_names
5933 (
5934 p_application_id => g_application_info.application_id
5935 ,p_object_name_affix => l_current_object_name_affix
5936 ,x_global_table_name => l_current_temp_table_name
5937 ,x_plsql_table_name => l_current_plsql_table_name
5938 )
5939 THEN
5940 l_fatal_message_text := 'get_interface_object_names failed';
5941 RAISE ge_fatal_error;
5942 END IF;
5943
5944 --Get the source list for the affix
5945 IF NOT xla_cmp_tab_pkg.get_interface_sources
5946 (
5947 p_application_id => g_application_info.application_id
5948 ,p_object_name_affix => l_current_object_name_affix
5949 ,x_table_of_sources => l_table_of_sources
5950 ,x_table_of_source_datatypes => l_table_of_source_datatypes
5951
5952 )
5953 THEN
5954 l_fatal_message_text := 'get_source_list failed';
5955 RAISE ge_fatal_error;
5956 END IF;
5957
5958 l_tmpl_tab_push_interf_sources := NULL;
5959 l_tmpl_tab_pop_interf_sources := NULL;
5960
5961 FOR source_index IN l_table_of_sources.FIRST..l_table_of_sources.LAST
5962 LOOP
5963 l_tmpl_tab_push_interf_sources := l_tmpl_tab_push_interf_sources
5964 || xla_cmp_string_pkg.replace_token
5965 (
5966 C_TMPL_TAB_PUSH_INTERF_SOURCE
5967 ,'$SOURCE_CODE$'
5968 ,NVL( l_table_of_sources(source_index)
5969 , ' ')
5970 );
5971 l_tmpl_tab_pop_interf_sources := l_tmpl_tab_pop_interf_sources
5972 || xla_cmp_string_pkg.replace_token
5973 (
5974 C_TMPL_TAB_POP_INTERF_SOURCE
5975 ,'$SOURCE_CODE$'
5976 ,NVL( l_table_of_sources(source_index)
5977 , ' ')
5978 );
5979 END LOOP;
5980
5981 --Get the push/pop statement template
5982 l_push_interf_statement_text := C_TMPL_PUSH_INTERF_DATA_STMT;
5983 l_pop_interf_statement_text := C_TMPL_POP_INTERF_DATA_STMT;
5984
5985 --Replace the table name token
5986 l_push_interf_statement_text:= xla_cmp_string_pkg.replace_token
5987 (
5988 l_push_interf_statement_text
5989 ,'$TABLE_NAME$'
5990 ,NVL(l_current_temp_table_name, ' ')
5991 );
5992 l_pop_interf_statement_text:= xla_cmp_string_pkg.replace_token
5993 (
5994 l_pop_interf_statement_text
5995 ,'$TABLE_NAME$'
5996 ,NVL(l_current_temp_table_name, ' ')
5997 );
5998
5999 --Replace the plsql table name token
6000 l_push_interf_statement_text:= xla_cmp_string_pkg.replace_token
6001 (
6002 l_push_interf_statement_text
6003 ,'$PLSQL_TABLE_NAME$'
6004 ,NVL(l_current_plsql_table_name, ' ')
6005 );
6006 l_pop_interf_statement_text:= xla_cmp_string_pkg.replace_token
6007 (
6008 l_pop_interf_statement_text
6009 ,'$PLSQL_TABLE_NAME$'
6010 ,NVL(l_current_plsql_table_name, ' ')
6011 );
6012
6013 --Replace the TAB API package name token
6014 l_push_interf_statement_text:= xla_cmp_string_pkg.replace_token
6015 (
6016 l_push_interf_statement_text
6017 ,'$TAB_API_PACKAGE_NAME$'
6018 ,NVL(l_tab_api_package_name, ' ')
6019 );
6020 l_pop_interf_statement_text:= xla_cmp_string_pkg.replace_token
6021 (
6022 l_pop_interf_statement_text
6023 ,'$TAB_API_PACKAGE_NAME$'
6024 ,NVL(l_tab_api_package_name, ' ')
6025 );
6026
6027 --Replace the source list token
6028 l_push_interf_statement_text:= xla_cmp_string_pkg.replace_token
6029 (
6030 l_push_interf_statement_text
6031 ,'$C_TMPL_TAB_PUSH_INTERF_SOURCES$'
6032 ,NVL(l_tmpl_tab_push_interf_sources, ' ')
6033 );
6034 l_pop_interf_statement_text:= xla_cmp_string_pkg.replace_token
6035 (
6036 l_pop_interf_statement_text
6037 ,'$C_TMPL_TAB_POP_INTERF_SOURCES$'
6038 ,NVL(l_tmpl_tab_pop_interf_sources, ' ')
6039 );
6040
6041 ELSE
6042 --If same affix as previous record, no action is required
6043 NULL;
6044 END IF; --new update statement
6045 END LOOP;
6046
6047 --Concatenate the last processed statement
6048 l_push_interf_statements_text := l_push_interf_statements_text
6049 || l_push_interf_statement_text;
6050 l_pop_interf_statements_text := l_pop_interf_statements_text
6051 || l_pop_interf_statement_text;
6052
6053 --Assing the out parameter
6054 x_push_interf_statements_text := l_push_interf_statements_text;
6055 x_pop_interf_statements_text := l_pop_interf_statements_text;
6056
6057 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6058 trace
6059 (p_module => l_log_module
6060 ,p_msg => 'END ' || l_log_module
6061 ,p_level => C_LEVEL_PROCEDURE);
6062 END IF;
6063
6064 RETURN l_return_value;
6065
6066 EXCEPTION
6067 WHEN ge_fatal_error
6068 THEN
6069 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6070 trace
6071 ( p_module => l_log_module
6072 ,p_msg => 'EXCEPTION:' ||
6073 ' Fatal error, aborting...'
6074 ,p_level => C_LEVEL_EXCEPTION);
6075 trace
6076 ( p_module => l_log_module
6077 ,p_msg => l_fatal_message_text
6078 ,p_level => C_LEVEL_EXCEPTION);
6079 END IF;
6080 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6081 trace
6082 (p_module => l_log_module
6083 ,p_msg => 'END ' || l_log_module
6084 ,p_level => C_LEVEL_PROCEDURE);
6085 END IF;
6086 RETURN FALSE;
6087 WHEN xla_exceptions_pkg.application_exception
6088 THEN
6089 xla_exceptions_pkg.raise_message
6090 ( p_location => 'xla_cmp_tad_pkg.build_move_interf_data_stmts'
6091 ,p_msg_mode => 'NON_STANDARD'
6092 );
6093 RETURN FALSE;
6094 WHEN OTHERS
6095 THEN
6096 xla_exceptions_pkg.raise_message
6097 ( p_location => 'xla_cmp_tad_pkg.build_move_interf_data_stmts'
6098 ,p_msg_mode => g_msg_mode
6099 );
6100 RETURN FALSE;
6101 END build_move_interf_data_stmts;
6102
6103
6104
6105
6106 FUNCTION get_tad_package_name
6107 (
6108 p_application_id IN NUMBER
6109 ,p_account_definition_code IN VARCHAR2
6110 ,p_account_definition_type_code IN VARCHAR2
6111 ,p_amb_context_code IN VARCHAR2
6112 ,p_tad_package_name OUT NOCOPY VARCHAR2
6113 )
6114 RETURN BOOLEAN
6115 IS
6116 l_return_value BOOLEAN;
6117 l_chart_of_accounts_id NUMBER;
6118 l_log_module VARCHAR2 (2000);
6119
6120 BEGIN
6121 IF g_log_enabled THEN
6122 l_log_module := C_DEFAULT_MODULE||'.get_tad_package_name';
6123 END IF;
6124
6125 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6126 trace
6127 (p_module => l_log_module
6128 ,p_msg => 'BEGIN ' || l_log_module
6129 ,p_level => C_LEVEL_PROCEDURE);
6130 END IF;
6131
6132 --Initialize global variables
6133 IF NOT init_global_variables
6134 (
6135 p_application_id => p_application_id
6136 )
6137 THEN
6138 --If global vars cannot be set we cannot continue
6139 IF (C_LEVEL_ERROR >= g_log_level) THEN
6140 trace
6141 (p_module => l_log_module
6142 ,p_msg => 'init_global_variables failed'
6143 ,p_level => C_LEVEL_ERROR);
6144 END IF;
6145 l_return_value := FALSE;
6146 RAISE ge_fatal_error;
6147 END IF;
6148
6149 IF NOT get_tad_package_name
6150 (
6151 p_account_definition_code => p_account_definition_code
6152 ,p_account_definition_type_code => p_account_definition_type_code
6153 ,p_amb_context_code => p_amb_context_code
6154 ,p_tad_package_name => p_tad_package_name
6155 ,p_chart_of_accounts_id => l_chart_of_accounts_id
6156 )
6157 THEN
6158 IF (C_LEVEL_ERROR >= g_log_level) THEN
6159 trace
6160 (p_module => l_log_module
6161 ,p_msg => 'get_tad_package_name failed'
6162 ,p_level => C_LEVEL_ERROR);
6163 END IF;
6164 l_return_value := FALSE;
6165 RAISE ge_fatal_error;
6166 END IF;
6167
6168 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6169 trace
6170 (p_module => l_log_module
6171 ,p_msg => 'p_tad_package_name: ' || p_tad_package_name
6172 ,p_level => C_LEVEL_STATEMENT);
6173 END IF;
6174
6175 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6176 trace
6177 (p_module => l_log_module
6178 ,p_msg => 'END ' || l_log_module
6179 ,p_level => C_LEVEL_PROCEDURE);
6180 END IF;
6181
6182 RETURN TRUE;
6183
6184 EXCEPTION
6185 WHEN ge_fatal_error
6186 THEN
6187 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6188 trace
6189 (p_module => l_log_module
6190 ,p_msg => 'EXCEPTION:' ||
6191 ' Fatal error, aborting...'
6192 ,p_level => C_LEVEL_EXCEPTION);
6193 END IF;
6194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6195 trace
6196 (p_module => l_log_module
6197 ,p_msg => 'END ' || l_log_module
6198 ,p_level => C_LEVEL_PROCEDURE);
6199 END IF;
6200 RETURN FALSE;
6201 WHEN xla_exceptions_pkg.application_exception
6202 THEN
6203 RAISE;
6204 WHEN OTHERS
6205 THEN
6206 xla_exceptions_pkg.raise_message
6207 (p_location => 'xla_cmp_tad_pkg.get_tad_package_name'
6208 ,p_msg_mode => g_msg_mode
6209 );
6210 RETURN FALSE;
6211 END get_tad_package_name;
6212
6213
6214 /*======================================================================+
6215 | |
6216 | Private Function |
6217 | |
6218 | It builds the TAB API package name |
6219 | <PROD_ABBR>_XLA_TAB_PKG |
6220 | |
6221 | |
6222 | |
6223 +======================================================================*/
6224 FUNCTION get_tad_package_name
6225 (
6226 p_account_definition_code IN VARCHAR2
6227 ,p_account_definition_type_code IN VARCHAR2
6228 ,p_amb_context_code IN VARCHAR2
6229 ,p_tad_package_name OUT NOCOPY VARCHAR2
6230 ,p_chart_of_accounts_id OUT NOCOPY NUMBER
6231 )
6232 RETURN BOOLEAN
6233 IS
6234 l_return_value BOOLEAN;
6235 C_TMPL_TAD_PKG_NAME CONSTANT VARCHAR2(100)
6236 := 'XLA_$APP_HASH_ID$_TAD_$TYPE_CODE$_$TAD_HASH_ID$_PKG';
6237 -- -3-_---5---------_-3-_----1------_---10--------_-3-
6238 l_tad_hash_id NUMBER;
6239 l_tad_package_name VARCHAR2(100);
6240 l_tad_enabled_flag VARCHAR2(1);
6241 l_chart_of_accounts_id NUMBER;
6242
6243 l_log_module VARCHAR2 (2000);
6244 BEGIN
6245 IF g_log_enabled THEN
6246 l_log_module := C_DEFAULT_MODULE||'.get_tad_package_name';
6247 END IF;
6248
6249 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6250 trace
6251 (p_module => l_log_module
6252 ,p_msg => 'BEGIN ' || l_log_module
6253 ,p_level => C_LEVEL_PROCEDURE);
6254 END IF;
6255
6256 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6257 trace
6258 (p_module => l_log_module
6259 ,p_msg => 'p_account_definition_code: '
6260 || NVL(p_account_definition_code, '<NULL>')
6261 ,p_level => C_LEVEL_STATEMENT);
6262 END IF;
6263 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6264 trace
6265 (p_module => l_log_module
6266 ,p_msg => 'p_account_definition_type_code: '
6267 || NVL(p_account_definition_type_code, '<NULL>')
6268 ,p_level => C_LEVEL_STATEMENT);
6269 END IF;
6270 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6271 trace
6272 (p_module => l_log_module
6273 ,p_msg => 'p_amb_context_code: '
6274 || NVL(p_amb_context_code, '<NULL>')
6275 ,p_level => C_LEVEL_STATEMENT);
6276 END IF;
6277
6278 --Get TAD Hash Id
6279 BEGIN
6280 SELECT xtd.hash_id
6281 ,xtd.enabled_flag
6282 ,xtd.chart_of_accounts_id
6283 INTO l_tad_hash_id
6284 ,l_tad_enabled_flag
6285 ,l_chart_of_accounts_id
6286 FROM xla_tab_acct_defs_b xtd
6287 WHERE xtd.application_id = g_application_info.application_id
6288 AND xtd.account_definition_code = p_account_definition_code
6289 AND xtd.account_definition_type_code = p_account_definition_type_code
6290 AND xtd.amb_context_code = p_amb_context_code;
6291 EXCEPTION
6292 WHEN NO_DATA_FOUND
6293 THEN
6294 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6295 trace
6296 (p_module => l_log_module
6297 ,p_msg => 'EXCEPTION:' ||
6298 ' Transaction Account Definition not found'
6299 ,p_level => C_LEVEL_EXCEPTION);
6300 END IF;
6301 RAISE ge_fatal_error;
6302 END;
6303
6304 --If the TAD is disabled abort the execution
6305 IF l_tad_enabled_flag = 'N'
6306 THEN
6307 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6308 trace
6309 (p_module => l_log_module
6310 ,p_msg => 'EXCEPTION:' ||
6311 'This TAD is disabled, aborting...'
6312 ,p_level => C_LEVEL_EXCEPTION);
6313 END IF;
6314 RAISE ge_fatal_error;
6315 END IF;
6316
6317 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6318 trace
6319 (p_module => l_log_module
6320 ,p_msg => 'Current Hash ID: ' || NVL(TO_CHAR(l_tad_hash_id), 'NULL')
6321 ,p_level => C_LEVEL_STATEMENT);
6322 END IF;
6323
6324 --If the hash_id is NULL
6325 IF l_tad_hash_id IS NULL
6326 THEN
6327 --Get a new one from the DB sequence
6328 SELECT xla_tab_acct_defs_b_s.NEXTVAL
6329 INTO l_tad_hash_id
6330 FROM DUAL;
6331
6332 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6333 trace
6334 (p_module => l_log_module
6335 ,p_msg => 'New Hash ID: ' || NVL(TO_CHAR(l_tad_hash_id), 'NULL')
6336 ,p_level => C_LEVEL_STATEMENT);
6337 END IF;
6338
6339 --Update the TAD record with the hash id
6340 UPDATE xla_tab_acct_defs_b xtd
6341 SET xtd.hash_id = l_tad_hash_id
6342 WHERE xtd.application_id = g_application_info.application_id
6343 AND xtd.account_definition_code = p_account_definition_code
6344 AND xtd.account_definition_type_code = p_account_definition_type_code
6345 AND xtd.amb_context_code = p_amb_context_code;
6346
6347 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6348 trace
6349 (p_module => l_log_module
6350 ,p_msg => SQL%ROWCOUNT
6351 || ' row(s) updated in xla_tab_acct_defs_b'
6352 ,p_level => C_LEVEL_STATEMENT);
6353 END IF;
6354 END IF;
6355
6356 --Get the template for the TAD package name and replace the tokens
6357 l_tad_package_name := C_TMPL_TAD_PKG_NAME;
6358
6359 l_tad_package_name := REPLACE( l_tad_package_name
6360 ,'$APP_HASH_ID$'
6361 ,g_application_info.application_hash_id
6362 );
6363
6364 l_tad_package_name := REPLACE( l_tad_package_name
6365 ,'$TYPE_CODE$'
6366 ,p_account_definition_type_code
6367 );
6368
6369 l_tad_package_name := REPLACE( l_tad_package_name
6370 ,'$TAD_HASH_ID$'
6371 ,LPAD ( TO_CHAR(l_tad_hash_id)
6372 ,10
6373 ,'0'
6374 )
6375 );
6376
6377 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
6378 trace
6379 (p_module => l_log_module
6380 ,p_msg => 'l_tad_package_name: ' || l_tad_package_name
6381 ,p_level => C_LEVEL_STATEMENT);
6382 END IF;
6383
6384 --Assign the out variables
6385 p_tad_package_name := l_tad_package_name;
6386 p_chart_of_accounts_id := l_chart_of_accounts_id;
6387
6388 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6389 trace
6390 (p_module => l_log_module
6391 ,p_msg => 'END ' || l_log_module
6392 ,p_level => C_LEVEL_PROCEDURE);
6393 END IF;
6394
6395 RETURN TRUE;
6396
6397 EXCEPTION
6398 WHEN ge_fatal_error
6399 THEN
6400 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6401 trace
6402 (p_module => l_log_module
6403 ,p_msg => 'EXCEPTION:' ||
6404 ' Fatal error, aborting...'
6405 ,p_level => C_LEVEL_EXCEPTION);
6406 END IF;
6407 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6408 trace
6409 (p_module => l_log_module
6410 ,p_msg => 'END ' || l_log_module
6411 ,p_level => C_LEVEL_PROCEDURE);
6412 END IF;
6413 RETURN FALSE;
6414 WHEN xla_exceptions_pkg.application_exception
6415 THEN
6416 RAISE;
6417 WHEN OTHERS
6418 THEN
6419 xla_exceptions_pkg.raise_message
6420 (p_location => 'xla_cmp_tad_pkg.get_tad_package_name'
6421 ,p_msg_mode => g_msg_mode
6422 );
6423 RETURN FALSE;
6424 END get_tad_package_name;
6425
6426
6427 FUNCTION get_coa_info
6428 (
6429 p_chart_of_accounts_id IN NUMBER
6430 ,p_chart_of_accounts_name OUT NOCOPY VARCHAR2
6431 ,p_flex_delimiter OUT NOCOPY VARCHAR2
6432 ,p_concat_segments_template OUT NOCOPY VARCHAR2
6433 ,p_table_segment_qualifiers OUT NOCOPY gt_table_V30_V30
6434 ,p_table_segment_column_names OUT NOCOPY gt_table_V30
6435 )
6436 RETURN BOOLEAN
6437 IS
6438 le_fatal_error EXCEPTION;
6439
6440 TYPE lt_table_v30 IS TABLE OF VARCHAR2(30);
6441
6442 l_table_qualifier_names lt_table_v30;
6443 l_account_segment_column VARCHAR2(30);
6444 l_flex_delimiter VARCHAR2(1);
6445
6446 l_table_segment_names gt_table_v30;
6447
6448 l_concat_segments_string VARCHAR2(1000);
6449 l_return_value BOOLEAN;
6450 l_fatal_error_message VARCHAR2(2000);
6451 l_log_module VARCHAR2(2000);
6452 BEGIN
6453 IF g_log_enabled THEN
6454 l_log_module := C_DEFAULT_MODULE||'.get_coa_info';
6455 END IF;
6456
6457 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6458 trace
6459 ( p_module => l_log_module
6460 ,p_msg => 'BEGIN ' || l_log_module
6461 ,p_level => C_LEVEL_PROCEDURE);
6462 END IF;
6463
6464 l_return_value := TRUE;
6465
6466 --Get the Chart Of Accounts name
6467 SELECT id_flex_structure_name
6468 INTO p_chart_of_accounts_name
6469 FROM fnd_id_flex_structures_vl ffsvl
6470 WHERE ffsvl.application_id = 101
6471 AND ffsvl.id_flex_code = 'GL#'
6472 AND ffsvl.id_flex_num = p_chart_of_accounts_id;
6473
6474 --Initialize the segment qualifier name list
6475 l_table_qualifier_names := lt_table_v30
6476 ( 'GL_BALANCING'
6477 ,'GL_ACCOUNT'
6478 ,'GL_INTERCOMPANY'
6479 ,'GL_MANAGEMENT'
6480 ,'FA_COST_CTR'
6481 );
6482
6483 --For each qualifier (if assigned) we want the segment column name
6484 FOR i IN l_table_qualifier_names.FIRST .. l_table_qualifier_names.LAST
6485 LOOP
6486 IF FND_FLEX_APIS.get_segment_column( 101
6487 ,'GL#'
6488 ,p_chart_of_accounts_id
6489 ,l_table_qualifier_names(i)
6490 ,l_account_segment_column
6491 )
6492 THEN
6493 p_table_segment_qualifiers(l_table_qualifier_names(i)) := l_account_segment_column;
6494 END IF;
6495 END LOOP;
6496
6497 l_flex_delimiter := FND_FLEX_EXT.get_delimiter
6498 (
6499 application_short_name => 'SQLGL'
6500 ,key_flex_code => 'GL#'
6501 ,structure_number => p_chart_of_accounts_id
6502 );
6503 IF l_flex_delimiter IS NULL
6504 THEN
6505 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6506 trace
6507 ( p_module => l_log_module
6508 ,p_msg => 'EXCEPTION:'
6509 ,p_level => C_LEVEL_EXCEPTION);
6510 trace
6511 ( p_module => l_log_module
6512 ,p_msg => 'get_delimiter failed'
6513 ,p_level => C_LEVEL_EXCEPTION);
6514 END IF;
6515 --The error has been placed on the stack
6516 RAISE le_fatal_error;
6517 END IF;
6518
6519
6520 SELECT UPPER(fifs.application_column_name)
6521 BULK COLLECT
6522 INTO l_table_segment_names
6523 FROM fnd_id_flex_segments fifs
6524 WHERE fifs.application_id = 101
6525 AND fifs.id_flex_code = 'GL#'
6526 AND fifs.id_flex_num = p_chart_of_accounts_id
6527 AND fifs.enabled_flag = 'Y'
6528 ORDER BY fifs.segment_num;
6529
6530 l_concat_segments_string := NULL;
6531
6532 IF l_table_segment_names.COUNT > 0
6533 THEN
6534 FOR i IN 1..l_table_segment_names.COUNT
6535 LOOP
6536 l_concat_segments_string := l_concat_segments_string
6537 || CASE i
6538 WHEN 1 THEN NULL
6539 ELSE l_flex_delimiter
6540 END
6541 || l_table_segment_names(i);
6542 END LOOP;
6543 END IF;
6544
6545 --Assign out param
6546 p_flex_delimiter := l_flex_delimiter;
6547 p_concat_segments_template := l_concat_segments_string;
6548 p_table_segment_column_names := l_table_segment_names;
6549
6550 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
6551 trace
6552 ( p_module => l_log_module
6553 ,p_msg => 'END ' || l_log_module
6554 ,p_level => C_LEVEL_PROCEDURE);
6555 END IF;
6556
6557 RETURN l_return_value;
6558 EXCEPTION
6559 WHEN le_fatal_error
6560 THEN
6561 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
6562 trace
6563 ( p_module => l_log_module
6564 ,p_msg => 'EXCEPTION:'
6565 ,p_level => C_LEVEL_EXCEPTION);
6566 trace
6567 ( p_module => l_log_module
6568 ,p_msg => 'Fatal error: ' || l_fatal_error_message
6569 ,p_level => C_LEVEL_EXCEPTION);
6570 END IF;
6571 RETURN FALSE;
6572 WHEN xla_exceptions_pkg.application_exception
6573 THEN
6574 RAISE;
6575 WHEN OTHERS
6576 THEN
6577 xla_exceptions_pkg.raise_message
6578 (p_location => 'xla_cmp_tad_pkg.get_coa_info'
6579 ,p_msg_mode => g_msg_mode
6580 );
6581 RETURN FALSE;
6582 END get_coa_info;
6583
6584
6585
6586
6587
6588
6589
6590 --Trace initialization
6591 BEGIN
6592 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6593 g_log_enabled := fnd_log.test
6594 (log_level => g_log_level
6595 ,module => C_DEFAULT_MODULE);
6596
6597 IF NOT g_log_enabled THEN
6598 g_log_level := C_LEVEL_LOG_DISABLED;
6599 END IF;
6600
6601 END xla_cmp_tad_pkg;