[Home] [Help]
PACKAGE BODY: APPS.XLA_AE_CODE_COMBINATION_PKG
Source
1 PACKAGE BODY xla_ae_code_combination_pkg AS
2 /* $Header: xlajecci.pkb 120.59.12010000.2 2008/08/06 21:10:29 sbhaskar ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_ae_code_combination_pkg |
10 | |
11 | DESCRIPTION |
12 | |
13 | |
14 | HISTORY |
15 | 20-NOV-2002 K.Boussema Created |
16 | 17-MAR-2003 K.Boussema Made changes for the new bulk approach of the|
17 | accounting engine |
18 | 11-APR-2003 K.Boussema Include Build Ccid Process |
19 | static : create_ccid procedure , |
20 | dynamic. create_ccidV2 procedure |
21 | This package calls the static procedure |
22 | 19-APR-2003 K.Boussema Included Error messages |
23 | 12-MAI-2003 K.Boussema Updated Message code XLA_AP_CODE_COMBINATION |
24 | 13-MAI-2003 K.Boussema Renamed temporary table xla_je_lines_gt by |
25 | xla_ae_lines_gt |
26 | 27-MAI-2003 K.Boussema Renamed code_combination_status by |
27 | code_combination_status_flag |
28 | 17-JUL-2003 K.Boussema Reviewd the code |
29 | 24-JUL-2003 K.Boussema Updated the error messages |
30 | 29-JUL-2003 K.Boussema Renamed XLA_AP_INVALID_CODE_COMBINATION |
31 | message code by XLA_AP_INV_CODE_COMBINATION |
32 | 30-JUL-2003 K.Boussema Reviewed the procedure create_ccid() |
33 | 28-AUG-2003 K.boussema Reviewed GetCCid to fix bug 3103575 |
34 | 01-SEP-2003 K.boussema Reviewed call to build_message, bug 3099988 |
35 | 27-SEP-2003 K.Boussema Added the error message XLA_AP_COA_INVALID |
36 | 13-NOV-2003 K.Boussema Changed to fix issue in bug3252058 |
37 | 26-NOV-2003 K.Boussema Added the cache of GL mapping information |
38 | 28-NOV-2003 K.Boussema Changed create_ccid call by create_ccidV2 |
39 | 12-DEC-2003 K.Boussema Renamed target_coa_id in xla_ae_lines_gt |
40 | by ccid_coa_id |
41 | 18-DEC-2003 K.Boussema Changed to fix bug 3042840,3307761,3268940 |
42 | 3310291 and 3320689 |
43 | 20-JAN-2004 K.Boussema Updated the message error XLA_AP_COA_INVALID |
44 | 03-FEB-2004 K.Boussema Reviewed get_flexfield_description in order |
45 | to retrieve segment value description instead|
46 | of the segment description |
47 | 16-FEB-2004 K.Boussema Made changes for the FND_LOG. |
48 | renamed create_ccidV2 by create_ccid |
49 | 03-MAR-2004 K.Boussema Changed to set GL_ACCOUNTS_MAP_GRP debug param.|
50 | 22-MAR-2004 K.Boussema Added a parameter p_module to the TRACE calls|
51 | and the procedure. |
52 | 25-MAR-2004 K.Boussema Changed MapCcid to insert the coa_mapping_id |
53 | into gl_accounts_map_interface_gt GT GL table |
54 | 11-MAY-2004 K.Boussema Removed the call to XLA trace routine from |
55 | trace() procedure |
56 | Revised update of journal entry status defined |
57 | in BuildCcids() function |
58 | 03-JUN-2004 K.Boussema Added the validaton of the CCIDs passed through|
59 | extract, refer to bug 3656297 |
60 | 23-JUN-2004 K.Boussema Removed the validation of CCIDs, changed error |
61 | message XLA_AP_INVALID_CCID |
62 | by XLA_AP_CCID_NOT_EXISTS |
63 | 23-Sep-2004 S.Singhania Minor changes due to bulk performance in calls |
64 | to xla_accrounting_err_pkg.build_message. |
65 | 28-Feb-2005 K.boussema Renamed GT table: gl_accounts_map_interface_gt |
66 | => gl_accts_map_int_gt |
67 | 03-MAR-2005 K.Boussema Reviewed MapCCid() function to fix bug 4197942 |
68 | 06-MAR-2005 W. Shen Ledger Currency Project. |
69 | maintain two ccids in line table |
70 | 14-Mar-2005 K.Boussema Changed for ADR-enhancements. |
71 | 11-APR-2005 K.Boussema Reviewed the code to don't process Dummy lines |
72 | 21-APR-2005 Shishir J. Renamed gl_accounts_map_bsv_int_gt to |
73 | gl_accts_map_bsv_gt |
74 | 19-MAI-2005 K.Boussema Reviewed cache_combination_id to fix bug4304098 |
75 | 23-MAY-2005 W.Chan Fix bug4388150 in create_ccid |
76 | 08-Aug-2005 W.Chan Fix bug4542460 in map_ccid |
77 | 19-Aug-2005 W.Chan Fix bug4564062 in map_ccid |
78 | 26-May-2006 M.Asada Merge updates in create_ccid and create_new_ccid|
79 +===========================================================================*/
80
81 /*-------------------------------------------------------------------+
82 | |
83 | PL/SQL constants |
84 | |
85 +-------------------------------------------------------------------*/
86
87 -- accounting CCID status
88 C_NOT_PROCESSED CONSTANT VARCHAR2(30) := 'NOT_PROCESSED';
89 C_PROCESSING CONSTANT VARCHAR2(30) := 'PROCESSING';
90 C_CREATED CONSTANT VARCHAR2(30) := 'CREATED';
91 C_INVALID CONSTANT VARCHAR2(30) := 'INVALID';
92
93 -- transaction account status
94 C_MAP_CCID CONSTANT VARCHAR2(30) := 'MAP_CCID';
95 C_MAP_QUALIFIER CONSTANT VARCHAR2(30) := 'MAP_QUALIFIER';
96 C_MAP_SEGMENT CONSTANT VARCHAR2(30) := 'MAP_SEGMENT';
97
98 C_JE_INVALID CONSTANT VARCHAR2(30) := 'I';
99
100 C_FINAL CONSTANT VARCHAR2(1) := 'P';
101 C_DRAFT CONSTANT VARCHAR2(1) := 'D';
102
103 C_CHAR CONSTANT VARCHAR2(1) := '#';
104 C_MAXCOUNT CONSTANT NUMBER := 1000;
105
106 C_NEW_LINE CONSTANT VARCHAR2(8) := fnd_global.newline;
107
108 /*-------------------------------------------------------------------+
109 | |
110 | PL/SQL structures/records/arrays |
111 | |
112 +-------------------------------------------------------------------*/
113 --
114 -- CCID structure and cache, indexed by
115 -- hash_code(flexfield_application_id,
116 -- id_flex_code,
117 -- id_flex_num,
118 -- combination_id)
119 --
120 TYPE t_rec_combination_id IS RECORD (
121 flexfield_application_id NUMBER
122 ,id_flex_code VARCHAR2(4)
123 ,id_flex_num NUMBER
124 ,combination_id NUMBER
125 ,segment1 VARCHAR2(30)
126 ,segment2 VARCHAR2(30)
127 ,segment3 VARCHAR2(30)
128 ,segment4 VARCHAR2(30)
129 ,segment5 VARCHAR2(30)
130 ,segment6 VARCHAR2(30)
131 ,segment7 VARCHAR2(30)
132 ,segment8 VARCHAR2(30)
133 ,segment9 VARCHAR2(30)
134 ,segment10 VARCHAR2(30)
135 ,segment11 VARCHAR2(30)
136 ,segment12 VARCHAR2(30)
137 ,segment13 VARCHAR2(30)
138 ,segment14 VARCHAR2(30)
139 ,segment15 VARCHAR2(30)
140 ,segment16 VARCHAR2(30)
141 ,segment17 VARCHAR2(30)
142 ,segment18 VARCHAR2(30)
143 ,segment19 VARCHAR2(30)
144 ,segment20 VARCHAR2(30)
145 ,segment21 VARCHAR2(30)
146 ,segment22 VARCHAR2(30)
147 ,segment23 VARCHAR2(30)
148 ,segment24 VARCHAR2(30)
149 ,segment25 VARCHAR2(30)
150 ,segment26 VARCHAR2(30)
151 ,segment27 VARCHAR2(30)
152 ,segment28 VARCHAR2(30)
153 ,segment29 VARCHAR2(30)
154 ,segment30 VARCHAR2(30)
155 ,combination_status VARCHAR2(1)
156 );
157
158
159 TYPE t_array_qualifier IS TABLE OF varchar2(30) INDEX BY varchar2(30);
160 TYPE t_array_segment IS TABLE OF varchar2(30) INDEX BY binary_integer;
161
162 TYPE t_rec_key_flexfield IS RECORD (
163 flexfield_application_id NUMBER
164 ,application_short_name VARCHAR2(50)
165 ,id_flex_code VARCHAR2(4)
166 ,id_flex_num NUMBER --coa_id
167 ,segment_qualifier t_array_qualifier
168 ,segment_num t_array_segment
169 );
170
171 TYPE t_array_key_flexfiled IS TABLE OF t_rec_key_flexfield INDEX BY binary_integer;
172 TYPE t_array_combination_id IS TABLE OF t_rec_combination_id INDEX BY binary_integer;
173 TYPE t_array_appl_short_name IS TABLE OF varchar2(50) INDEX BY binary_integer;
174
175 /*-------------------------------------------------------------------+
176 | |
177 | global variables/caches |
178 | |
179 +-------------------------------------------------------------------*/
180
181 g_error_exists BOOLEAN;
182 g_array_combination_id t_array_combination_id;
183 g_array_key_flexfield t_array_key_flexfiled;
184 g_array_appl_short_name t_array_appl_short_name;
185 g_array_cache_target_coa xla_ae_journal_entry_pkg.t_array_Num;
186 g_cache_coa_sla_mapping xla_ae_journal_entry_pkg.t_array_V33L;
187 g_cache_dynamic_inserts xla_ae_journal_entry_pkg.t_array_V1L;
188
189 --
190 --=============================================================================
191 -- *********** Local Trace Routine **********
192 --=============================================================================
193
194 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
195 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
196 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
197 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
198 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
199 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
200
201 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
202 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_ae_code_combination_pkg';
203
204 g_log_level NUMBER;
205 g_log_enabled BOOLEAN;
206
207 PROCEDURE trace
208 (p_msg IN VARCHAR2
209 ,p_level IN NUMBER
210 ,p_module IN VARCHAR2) IS
211 BEGIN
212 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
213 fnd_log.message(p_level, p_module);
214 ELSIF p_level >= g_log_level THEN
215 fnd_log.string(p_level, p_module, p_msg);
216 END IF;
217
218 EXCEPTION
219 WHEN xla_exceptions_pkg.application_exception THEN
220 RAISE;
221 WHEN OTHERS THEN
222 xla_exceptions_pkg.raise_message
223 (p_location => 'xla_ae_code_combination_pkg.trace');
224 END trace;
225
226 /*======================================================================+
227 | |
228 | Private Function |
229 | |
230 | Dump_Text |
231 | |
232 | Dump text into fnd_log_messages. |
233 | |
234 +======================================================================*/
235 PROCEDURE dump_text
236 (
237 p_text IN VARCHAR2
238 )
239 IS
240 l_cur_position INTEGER;
241 l_next_cr_position INTEGER;
242 l_text_length INTEGER;
243 l_log_module VARCHAR2 (2000);
244
245 BEGIN
246 IF g_log_enabled THEN
247 l_log_module := C_DEFAULT_MODULE||'.dump_text';
248 END IF;
249
250 --Dump the SQL command
251 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
252 l_cur_position := 1;
253 l_next_cr_position := 0;
254 l_text_length := LENGTH(p_text);
255
256 WHILE l_next_cr_position < l_text_length
257 LOOP
258 l_next_cr_position := INSTR( p_text
259 ,C_NEW_LINE
260 ,l_cur_position
261 );
262
263 IF l_next_cr_position = 0
264 THEN
265 l_next_cr_position := l_text_length;
266 END IF;
267
268 trace
269 (p_msg => SUBSTR( p_text
270 ,l_cur_position
271 ,l_next_cr_position
272 - l_cur_position
273 + 1
274 )
275 ,p_level => C_LEVEL_STATEMENT
276 ,p_module => l_log_module);
277
278 IF l_cur_position < l_text_length
279 THEN
280 l_cur_position := l_next_cr_position + 1;
281 END IF;
282 END LOOP;
283 END IF;
284
285 EXCEPTION
286 WHEN xla_exceptions_pkg.application_exception THEN
287 RAISE;
288 WHEN OTHERS THEN
289 xla_exceptions_pkg.raise_message
290 (p_location => 'xla_ae_code_combination_pkg.dump_text');
291 END dump_text;
292
293 --
294 --====================================================================
295 --
296 --
297 --
298 --
299 -- Forward declaration of local routines
300 --
301 --
302 --
303 --
304 --======================================================================
305 --
306 --
307 --
308 PROCEDURE refreshGLMappingCache
309 ;
310
311 PROCEDURE cache_flex_qualifier (
312 p_flex_application_id IN NUMBER,
313 p_id_flex_code IN VARCHAR2,
314 p_id_flex_num IN NUMBER,
315 p_position IN NUMBER
316 )
317 ;
318
319 PROCEDURE cache_flex_segment (
320 p_flex_application_id IN NUMBER,
321 p_id_flex_code IN VARCHAR2,
322 p_id_flex_num IN NUMBER,
323 p_position IN NUMBER
324 )
325 ;
326
327 FUNCTION init_SegmentArray(
328 p_segment1 IN VARCHAR2
329 , p_segment2 IN VARCHAR2
333 , p_segment6 IN VARCHAR2
330 , p_segment3 IN VARCHAR2
331 , p_segment4 IN VARCHAR2
332 , p_segment5 IN VARCHAR2
334 , p_segment7 IN VARCHAR2
335 , p_segment8 IN VARCHAR2
336 , p_segment9 IN VARCHAR2
337 , p_segment10 IN VARCHAR2
338 , p_segment11 IN VARCHAR2
339 , p_segment12 IN VARCHAR2
340 , p_segment13 IN VARCHAR2
341 , p_segment14 IN VARCHAR2
342 , p_segment15 IN VARCHAR2
343 , p_segment16 IN VARCHAR2
344 , p_segment17 IN VARCHAR2
345 , p_segment18 IN VARCHAR2
346 , p_segment19 IN VARCHAR2
347 , p_segment20 IN VARCHAR2
348 , p_segment21 IN VARCHAR2
349 , p_segment22 IN VARCHAR2
350 , p_segment23 IN VARCHAR2
351 , p_segment24 IN VARCHAR2
352 , p_segment25 IN VARCHAR2
353 , p_segment26 IN VARCHAR2
354 , p_segment27 IN VARCHAR2
355 , p_segment28 IN VARCHAR2
356 , p_segment29 IN VARCHAR2
357 , p_segment30 IN VARCHAR2
358 , p_flex_application_id IN NUMBER
359 , p_application_short_name IN VARCHAR2
360 , p_id_flex_code IN VARCHAR2
361 , p_id_flex_num IN NUMBER
362 )
363 RETURN FND_FLEX_EXT.SegmentArray
364 ;
365
366 PROCEDURE cache_combination_id(
367 p_combination_id IN NUMBER
368 , p_flex_application_id IN NUMBER
369 , p_application_short_name IN VARCHAR2
370 , p_id_flex_code IN VARCHAR2
371 , p_id_flex_num IN NUMBER
372 )
373 ;
374
375 FUNCTION get_flex_structure_name(
376 p_flex_application_id IN NUMBER,
377 p_id_flex_code IN VARCHAR2,
378 p_id_flex_num IN NUMBER
379 )
380 RETURN VARCHAR2
381 ;
382
383
384 FUNCTION get_account_value(
385 p_combination_id IN NUMBER
386 , p_flex_application_id IN NUMBER
387 , p_application_short_name IN VARCHAR2
388 , p_id_flex_code IN VARCHAR2
389 , p_id_flex_num IN NUMBER)
390 RETURN VARCHAR2
391 ;
392
393 FUNCTION get_application_name(
394 p_flex_application_id IN NUMBER
395 )
396 RETURN VARCHAR2
397 ;
398
399
400 PROCEDURE build_events_message(
401 p_appli_s_name IN VARCHAR2
402 , p_msg_name IN VARCHAR2
403 , p_token_1 IN VARCHAR2
404 , p_value_1 IN VARCHAR2
405 , p_token_2 IN VARCHAR2
406 , p_value_2 IN VARCHAR2
407 , p_segment1 IN VARCHAR2
408 , p_segment2 IN VARCHAR2
409 , p_segment3 IN VARCHAR2
410 , p_segment4 IN VARCHAR2
411 , p_segment5 IN VARCHAR2
412 , p_segment6 IN VARCHAR2
413 , p_segment7 IN VARCHAR2
414 , p_segment8 IN VARCHAR2
415 , p_segment9 IN VARCHAR2
416 , p_segment10 IN VARCHAR2
417 , p_segment11 IN VARCHAR2
418 , p_segment12 IN VARCHAR2
419 , p_segment13 IN VARCHAR2
420 , p_segment14 IN VARCHAR2
421 , p_segment15 IN VARCHAR2
422 , p_segment16 IN VARCHAR2
423 , p_segment17 IN VARCHAR2
424 , p_segment18 IN VARCHAR2
425 , p_segment19 IN VARCHAR2
426 , p_segment20 IN VARCHAR2
427 , p_segment21 IN VARCHAR2
428 , p_segment22 IN VARCHAR2
429 , p_segment23 IN VARCHAR2
430 , p_segment24 IN VARCHAR2
431 , p_segment25 IN VARCHAR2
432 , p_segment26 IN VARCHAR2
433 , p_segment27 IN VARCHAR2
434 , p_segment28 IN VARCHAR2
435 , p_segment29 IN VARCHAR2
436 , p_segment30 IN VARCHAR2
437 , p_chart_of_accounts_id IN NUMBER
438 )
439 ;
440 --
441 PROCEDURE get_ccid_errors
442 ;
443 --
444 FUNCTION validate_source_ccid
445 RETURN NUMBER
446 ;
447 --
448 FUNCTION override_ccid
449 RETURN NUMBER
450 ;
451 --
452 FUNCTION create_ccid
453 RETURN NUMBER
454 ;
455 --
456 FUNCTION create_new_ccid
457 RETURN NUMBER
458 ;
459 --
460 FUNCTION map_ccid(
461 p_gl_coa_mapping_name IN VARCHAR2
462 , p_gl_coa_mapping_id IN NUMBER
463 )
464 RETURN NUMBER
465 ;
466
467 FUNCTION map_segment_qualifier(
468 p_gl_coa_mapping_name IN VARCHAR2
469 , p_gl_coa_mapping_id IN NUMBER
470 )
471 RETURN NUMBER
472 ;
473
474 FUNCTION map_transaction_accounts
475 RETURN NUMBER
476 ;
477
478 --
479 --+==========================================================================+
480 --| |
481 --| |
485 --| |
482 --| |
483 --| |
484 --| |
486 --| |
487 --| |
488 --| |
489 --| |
490 --| |
491 --| |
492 --| |
493 --| |
494 --| |
495 --+==========================================================================+
496
497 /*--------------------------------------------------------+
498 | |
499 | Private function |
500 | |
501 | get_application_name |
502 | |
503 | retruns the application name for a given application id |
504 | |
505 +--------------------------------------------------------*/
506
507 FUNCTION get_application_name(
508 p_flex_application_id IN NUMBER
509 )
510 RETURN VARCHAR2
511 IS
512 l_name VARCHAR2(240);
513 BEGIN
514 SELECT application_name
515 INTO l_name
516 FROM fnd_application_vl fnd
517 WHERE fnd.application_id = p_flex_application_id
518 ;
519
520 RETURN l_name;
521 EXCEPTION
522 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
523 RETURN TO_CHAR(p_flex_application_id);
524 WHEN xla_exceptions_pkg.application_exception THEN
525 RETURN TO_CHAR(p_flex_application_id);
526 WHEN OTHERS THEN
527 xla_exceptions_pkg.raise_message
528 (p_location => 'xla_ae_code_combination_pkg.get_application_name');
529 END get_application_name;
530
531 /*---------------------------------------------------------------+
532 | |
533 | Private function |
534 | |
535 | get_flex_structure_name |
536 | |
537 | retruns key flexfield structure name for a given key flexfield |
538 | |
539 +---------------------------------------------------------------*/
540
541 FUNCTION get_flex_structure_name(
542 p_flex_application_id IN NUMBER,
543 p_id_flex_code IN VARCHAR2,
544 p_id_flex_num IN NUMBER
545 )
546 RETURN VARCHAR2
547 IS
548 l_id_flex_num NUMBER;
549 l_name VARCHAR2(30);
550 BEGIN
551 l_id_flex_num:= p_id_flex_num;
552 IF l_id_flex_num IS NULL THEN
553 l_id_flex_num:= xla_flex_pkg.get_flexfield_structure
554 (p_application_id =>p_flex_application_id
555 ,p_id_flex_code => p_id_flex_code);
556 END IF;
557
558 SELECT id_flex_structure_name
559 INTO l_name
560 FROM fnd_id_flex_structures_vl fnd
561 WHERE fnd.application_id = p_flex_application_id
562 AND fnd.id_flex_code = p_id_flex_code
563 AND fnd.id_flex_num = l_id_flex_num
564 ;
565 RETURN l_name;
566 EXCEPTION
567 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
568 RETURN NULL;
569 WHEN xla_exceptions_pkg.application_exception THEN
570 RETURN NULL;
571 WHEN OTHERS THEN
572 xla_exceptions_pkg.raise_message
573 (p_location => 'xla_ae_code_combination_pkg.get_flex_structure_name');
574 END get_flex_structure_name;
575
576 /*---------------------------------------------------------------+
577 | |
578 | Public procedure |
579 | |
580 | refreshCCID |
581 | |
582 | refresh key flexfield combination cache |
583 | |
584 +---------------------------------------------------------------*/
585
586 PROCEDURE refreshCCID
587 IS
588 l_null_combination_id t_array_combination_id;
589 BEGIN
590 g_array_combination_id := l_null_combination_id;
591
592 END refreshCCID;
593
594 /*---------------------------------------------------------------+
595 | |
596 | Private procedure |
597 | |
601 | |
598 | reset_flexfield_cache |
599 | |
600 | refresh key flexfield structure cache |
602 +---------------------------------------------------------------*/
603
604 PROCEDURE reset_flexfield_cache
605 IS
606 l_null_key_flexfiled t_array_key_flexfiled;
607 BEGIN
608 g_array_key_flexfield := l_null_key_flexfiled;
609 END reset_flexfield_cache;
610
611 /*---------------------------------------------------------------+
612 | |
613 | Private procedure |
614 | |
615 | refreshGLMappingCache |
616 | |
617 | refresh GL COA mapping cache |
618 | |
619 +---------------------------------------------------------------*/
620
621 PROCEDURE refreshGLMappingCache
622 IS
623 l_null_coa_sla_mapping xla_ae_journal_entry_pkg.t_array_V33L;
624 l_null_dynamic_inserts xla_ae_journal_entry_pkg.t_array_V1L;
625 BEGIN
626
627 g_cache_coa_sla_mapping := l_null_coa_sla_mapping;
628 g_cache_dynamic_inserts := l_null_dynamic_inserts;
629
630 END refreshGLMappingCache;
631
632 /*---------------------------------------------------------------+
633 | |
634 | Public procedure |
635 | |
636 | refreshCcidCache |
637 | |
638 | refresh the accounts cache |
639 | |
640 +---------------------------------------------------------------*/
641
642 PROCEDURE refreshCcidCache
643 IS
644 g_array_appl_short_null t_array_appl_short_name;
645 BEGIN
646 --
647 refreshCCID;
648 reset_flexfield_cache;
649 g_array_appl_short_name:= g_array_appl_short_null ;
650 --
651 EXCEPTION
652 WHEN xla_exceptions_pkg.application_exception THEN
653 RAISE;
654 WHEN OTHERS THEN
655 xla_exceptions_pkg.raise_message
656 (p_location => 'xla_ae_journal_entry_pkg.refreshCcidCache');
657 END refreshCcidCache;
658
659 /*------------------------------------------------------------------+
660 | |
661 | Private procedure |
662 | |
663 | cache_flex_qualifier |
664 | |
665 | caches the segment attributes for a given key flexfield structure |
666 | |
667 +------------------------------------------------------------------*/
668
669 PROCEDURE cache_flex_qualifier(
670 p_flex_application_id IN NUMBER,
671 p_id_flex_code IN VARCHAR2,
672 p_id_flex_num IN NUMBER,
673 p_position IN NUMBER
674 )
675 IS
676 l_log_module VARCHAR2(240);
677 BEGIN
678
679 IF g_log_enabled THEN
680 l_log_module := C_DEFAULT_MODULE||'.cache_flex_qualifier';
681 END IF;
682
683 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
684 trace
685 (p_msg => 'BEGIN of cache_flex_qualifier'
686 ,p_level => C_LEVEL_PROCEDURE
687 ,p_module => l_log_module);
688 trace
689 (p_msg => 'flexfield application id = '|| TO_CHAR(p_flex_application_id) ||
690 ' - p_id_flex_code = '|| TO_CHAR(p_id_flex_code)||
691 ' - p_id_flex_num = '|| TO_CHAR(p_id_flex_num)||
692 ' - p_position = '||p_position
693 ,p_level => C_LEVEL_PROCEDURE
694 ,p_module => l_log_module);
695
696 END IF;
697
698 FOR flex_cur IN ( SELECT fsav.segment_attribute_type segment_qualifier
699 , fsav.application_column_name segment_code
700 FROM fnd_segment_attribute_values fsav
701 WHERE fsav.application_id = p_flex_application_id
702 AND fsav.id_flex_code = p_id_flex_code
703 AND fsav.id_flex_num = p_id_flex_num
704 AND fsav.attribute_value = 'Y'
705 GROUP BY fsav.application_column_name, fsav.segment_attribute_type
706 )
707 LOOP
708
709 g_array_key_flexfield(p_position).segment_qualifier(flex_cur.segment_qualifier):= flex_cur.segment_code;
710
711 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
712 trace
713 (p_msg => 'segment_qualifier = '||flex_cur.segment_qualifier||
714 ' - segment_code ='||flex_cur.segment_code
718
715 ,p_level => C_LEVEL_STATEMENT
716 ,p_module => l_log_module);
717 END IF;
719 END LOOP;
720
721 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
722 trace
723 (p_msg => 'END of cache_flex_qualifier'
724 ,p_level => C_LEVEL_PROCEDURE
725 ,p_module => l_log_module);
726
727 END IF;
728 EXCEPTION
729 WHEN xla_exceptions_pkg.application_exception THEN
730 RAISE;
731 WHEN OTHERS THEN
732 xla_exceptions_pkg.raise_message
733 (p_location => 'xla_ae_code_combination_pkg.cache_flex_qualifier');
734 END cache_flex_qualifier;
735
736 /*------------------------------------------------------------------+
737 | |
738 | Private procedure |
739 | |
740 | cache_flex_segment |
741 | |
742 | caches the segment number for a given key flexfield structure |
743 | |
744 +------------------------------------------------------------------*/
745
746 PROCEDURE cache_flex_segment (
747 p_flex_application_id IN NUMBER,
748 p_id_flex_code IN VARCHAR2,
749 p_id_flex_num IN NUMBER,
750 p_position IN NUMBER
751 )
752 IS
753 l_log_module VARCHAR2(240);
754 BEGIN
755 IF g_log_enabled THEN
756 l_log_module := C_DEFAULT_MODULE||'.cache_flex_segment';
757 END IF;
758 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
759 trace
760 (p_msg => 'BEGIN of cache_flex_segment'
761 ,p_level => C_LEVEL_PROCEDURE
762 ,p_module => l_log_module);
763 trace
764 (p_msg => 'p_flex_application_id = '|| TO_CHAR(p_flex_application_id)||
765 ' - p_id_flex_code = '|| TO_CHAR(p_id_flex_code)||
766 ' - p_id_flex_num = '|| TO_CHAR(p_id_flex_num)||
767 ' - p_position = '||p_position
768 ,p_level => C_LEVEL_PROCEDURE
769 ,p_module => l_log_module);
770 END IF;
771
772 FOR flex_cur IN (
773 SELECT upper(fifs.application_column_name) segment_name
774 , fifs.segment_num segment_num
775 FROM fnd_id_flex_segments fifs
776 WHERE fifs.application_id = p_flex_application_id
777 AND fifs.id_flex_code = p_id_flex_code
778 AND fifs.id_flex_num = p_id_flex_num
779 AND fifs.enabled_flag = 'Y'
780 ORDER BY fifs.segment_num
781 )
782 LOOP
783
784 g_array_key_flexfield(p_position).segment_num(flex_cur.segment_num):= flex_cur.segment_name;
785
786 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
787 trace
788 (p_msg => 'segment_num = '||flex_cur.segment_num||
789 ' - segment_name ='||flex_cur.segment_name
790 ,p_level => C_LEVEL_STATEMENT
791 ,p_module => l_log_module);
792 END IF;
793
794 END LOOP;
795
796 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
797 trace
798 (p_msg => 'END of cache_flex_segment'
799 ,p_level => C_LEVEL_PROCEDURE
800 ,p_module => l_log_module);
801 END IF;
802 EXCEPTION
803 WHEN xla_exceptions_pkg.application_exception THEN
804 RAISE;
805 WHEN OTHERS THEN
806 xla_exceptions_pkg.raise_message
807 (p_location => 'xla_ae_code_combination_pkg.cache_flex_segment');
808 END cache_flex_segment;
809
810 /*------------------------------------------------------------------+
811 | |
812 | Private procedure |
813 | |
814 | cache_key_flexfield |
815 | |
816 | caches the key flexfield structure. It retruns the key flexfield |
817 | number, when the key flexfield is not an accounting flexfield |
818 | |
819 +------------------------------------------------------------------*/
820
821 PROCEDURE cache_key_flexfield(
822 p_flex_application_id IN NUMBER,
823 p_application_short_name IN VARCHAR2,
824 p_id_flex_code IN VARCHAR2,
825 p_id_flex_num IN OUT NOCOPY NUMBER
826 )
827 IS
828 l_id_flex_num NUMBER;
829 l_position NUMBER;
830 l_log_module VARCHAR2(240);
831 BEGIN
832 IF g_log_enabled THEN
833 l_log_module := C_DEFAULT_MODULE||'.cache_key_flexfield';
834 END IF;
835 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
836 trace
837 (p_msg => 'BEGIN of cache_key_flexfield'
838 ,p_level => C_LEVEL_PROCEDURE
839 ,p_module => l_log_module);
840 trace
841 (p_msg => 'p_flex_application_id = '|| TO_CHAR(p_flex_application_id)||
842 ' - p_id_flex_code = '|| TO_CHAR(p_id_flex_code)||
843 ' - p_id_flex_num = '|| TO_CHAR(p_id_flex_num)
847
844 ,p_level => C_LEVEL_PROCEDURE
845 ,p_module => l_log_module);
846 END IF;
848 l_position := DBMS_UTILITY.get_hash_value(
849 TO_CHAR(p_id_flex_num)||
850 TO_CHAR(p_flex_application_id)||
851 p_id_flex_code,1,1073741824);
852
853 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
854 trace
855 (p_msg => ' hash code of the key flexfield/position ='||l_position
856 ,p_level => C_LEVEL_PROCEDURE
857 ,p_module => l_log_module);
858
859 END IF;
860
861 IF NOT g_array_key_flexfield.EXISTS(l_position) THEN
862
863 l_id_flex_num := p_id_flex_num;
864
865 IF l_id_flex_num IS NULL THEN
866
867 l_id_flex_num:= xla_flex_pkg.get_flexfield_structure
868 (p_application_id =>p_flex_application_id
869 ,p_id_flex_code => p_id_flex_code);
870 END IF;
871
872 g_array_key_flexfield(l_position).flexfield_application_id := p_flex_application_id;
873 g_array_key_flexfield(l_position).application_short_name := p_application_short_name;
874 g_array_key_flexfield(l_position).id_flex_code := p_id_flex_code;
875 g_array_key_flexfield(l_position).id_flex_num := l_id_flex_num;
876
877 cache_flex_qualifier(
878 p_flex_application_id => p_flex_application_id,
879 p_id_flex_code => p_id_flex_code,
880 p_id_flex_num => l_id_flex_num,
881 p_position => l_position
882 );
883
884 cache_flex_segment (
885 p_flex_application_id => p_flex_application_id,
886 p_id_flex_code => p_id_flex_code,
887 p_id_flex_num => l_id_flex_num,
888 p_position => l_position
889 );
890
891 --cache application short name
892 g_array_appl_short_name(p_flex_application_id):= p_application_short_name;
893
894 ELSE
895 l_id_flex_num := g_array_key_flexfield(l_position).id_flex_num;
896 END IF;
897
898 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
899 trace
900 (p_msg => 'END of cache_key_flexfield'
901 ,p_level => C_LEVEL_PROCEDURE
902 ,p_module => l_log_module);
903 END IF;
904 p_id_flex_num := l_id_flex_num;
905 EXCEPTION
906 WHEN xla_exceptions_pkg.application_exception THEN
907 RAISE;
908 WHEN OTHERS THEN
909 xla_exceptions_pkg.raise_message
910 (p_location => 'xla_ae_code_combination_pkg.cache_key_flexfield');
911 END cache_key_flexfield;
912
913 /*---------------------------------------------------------+
914 | |
915 | Public procedure |
916 | |
917 | cache_coa |
918 | |
919 | caches the accounting flexfield structures, involved in |
920 | the accounting process |
921 +---------------------------------------------------------*/
922
923 PROCEDURE cache_coa(
924 p_coa_id IN NUMBER
925 )
926 IS
927 l_id_flex_num NUMBER;
928 l_log_module VARCHAR2(240);
929 BEGIN
930 IF g_log_enabled THEN
931 l_log_module := C_DEFAULT_MODULE||'.cache_coa';
932 END IF;
933 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
934 trace
935 (p_msg => 'BEGIN of cache_coa'
936 ,p_level => C_LEVEL_PROCEDURE
937 ,p_module => l_log_module);
938 trace
939 (p_msg => 'p_coa_id = '|| TO_CHAR(p_coa_id)
940 ,p_level => C_LEVEL_PROCEDURE
941 ,p_module => l_log_module);
942
943 END IF;
944
945 l_id_flex_num:= p_coa_id;
946
947 cache_key_flexfield(
948 p_flex_application_id => 101,
949 p_application_short_name => 'SQLGL',
950 p_id_flex_code => 'GL#',
951 p_id_flex_num => l_id_flex_num
952 );
953
954 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
955 trace
956 (p_msg => 'END of cache_coa'
957 ,p_level => C_LEVEL_PROCEDURE
958 ,p_module => l_log_module);
959
960 END IF;
961 EXCEPTION
962 WHEN xla_exceptions_pkg.application_exception THEN
963 RAISE;
964 WHEN OTHERS THEN
965 xla_exceptions_pkg.raise_message
966 (p_location => 'xla_ae_code_combination_pkg.cache_coa');
967 END cache_coa;
968
969 /*---------------------------------------------------------+
970 | |
971 | Public procedure |
972 | |
973 | cache_coa |
974 | |
975 | caches the accounting flexfield structures, involved in |
976 | the accounting process. It caches only the accounting |
977 | chart of accounts |
978 +---------------------------------------------------------*/
979
980 PROCEDURE cache_coa(
981 p_coa_id IN NUMBER
982 ,p_target_coa IN VARCHAR2
983 )
984 IS
985 l_log_module VARCHAR2(240);
986 BEGIN
987 IF g_log_enabled THEN
991 trace
988 l_log_module := C_DEFAULT_MODULE||'.cache_coa';
989 END IF;
990 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
992 (p_msg => 'BEGIN of cache_coa'
993 ,p_level => C_LEVEL_PROCEDURE
994 ,p_module => l_log_module);
995 trace
996 (p_msg => 'p_coa_id = '|| TO_CHAR(p_coa_id)||
997 ' - p_target_coa = '|| TO_CHAR(p_target_coa)
998 ,p_level => C_LEVEL_PROCEDURE
999 ,p_module => l_log_module);
1000 END IF;
1001 --
1002 IF NVL(p_target_coa,'N')='Y' THEN
1003
1004 cache_coa( p_coa_id => p_coa_id );
1005 g_array_cache_target_coa(p_coa_id) := p_coa_id;
1006
1007 END IF;
1008
1009 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1010 trace
1011 (p_msg => 'END of cache_coa_segment_num'
1012 ,p_level => C_LEVEL_PROCEDURE
1013 ,p_module => l_log_module);
1014 END IF;
1015 EXCEPTION
1016 WHEN xla_exceptions_pkg.application_exception THEN
1017 RAISE;
1018 WHEN OTHERS THEN
1019 xla_exceptions_pkg.raise_message
1020 (p_location => 'xla_ae_code_combination_pkg.cache_coa');
1021 END cache_coa;
1022
1023 /*---------------------------------------------------------+
1024 | |
1025 | Public procedure |
1026 | |
1027 | cacheGLMapping |
1028 | |
1029 | caches the GL chart of acounts mappings, i,volved in the |
1030 | accounting process. |
1031 | |
1032 +---------------------------------------------------------*/
1033
1034 PROCEDURE cacheGLMapping(
1035 p_sla_coa_mapping_name IN VARCHAR2
1036 , p_sla_coa_mapping_id IN NUMBER
1037 , p_dynamic_inserts_flag IN VARCHAR2
1038 )
1039 IS
1040 --
1041 Idx BINARY_INTEGER;
1042 l_exists BOOLEAN;
1043 l_log_module VARCHAR2(240);
1044 --
1045 BEGIN
1046 IF g_log_enabled THEN
1047 l_log_module := C_DEFAULT_MODULE||'.cacheGLMapping';
1048 END IF;
1049 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1050 trace
1051 (p_msg => 'BEGIN of cacheGLMapping'
1052 ,p_level => C_LEVEL_PROCEDURE
1053 ,p_module => l_log_module);
1054
1055 trace
1056 (p_msg => 'p_sla_coa_mapping_name = '|| p_sla_coa_mapping_name||
1057 ' - p_sla_coa_mapping_id = '|| p_sla_coa_mapping_id||
1058 ' - p_dynamic_inserts_flag = '|| p_dynamic_inserts_flag
1059 ,p_level => C_LEVEL_PROCEDURE
1060 ,p_module => l_log_module);
1061
1062 END IF;
1063
1064 g_cache_coa_sla_mapping(p_sla_coa_mapping_id) := SUBSTR(p_sla_coa_mapping_name,1,33);
1065 g_cache_dynamic_inserts(p_sla_coa_mapping_id) := SUBSTR(p_dynamic_inserts_flag,1,1);
1066
1067
1068 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1069 trace
1070 (p_msg => 'END of cacheGLMapping'
1071 ,p_level => C_LEVEL_PROCEDURE
1072 ,p_module => l_log_module);
1073
1074 END IF;
1075 END cacheGLMapping;
1076
1077 /*------------------------------------------------------------------+
1078 | |
1079 | Private procedure |
1080 | |
1081 | InitSegmentArray |
1082 | |
1083 | Set the FND_FLEX_EXT.SegmentArray varaibles. |
1084 | |
1085 +------------------------------------------------------------------*/
1086
1087 FUNCTION init_SegmentArray(
1088 p_segment1 IN VARCHAR2
1089 , p_segment2 IN VARCHAR2
1090 , p_segment3 IN VARCHAR2
1091 , p_segment4 IN VARCHAR2
1092 , p_segment5 IN VARCHAR2
1093 , p_segment6 IN VARCHAR2
1094 , p_segment7 IN VARCHAR2
1095 , p_segment8 IN VARCHAR2
1096 , p_segment9 IN VARCHAR2
1097 , p_segment10 IN VARCHAR2
1098 , p_segment11 IN VARCHAR2
1099 , p_segment12 IN VARCHAR2
1100 , p_segment13 IN VARCHAR2
1101 , p_segment14 IN VARCHAR2
1102 , p_segment15 IN VARCHAR2
1103 , p_segment16 IN VARCHAR2
1104 , p_segment17 IN VARCHAR2
1105 , p_segment18 IN VARCHAR2
1106 , p_segment19 IN VARCHAR2
1107 , p_segment20 IN VARCHAR2
1108 , p_segment21 IN VARCHAR2
1109 , p_segment22 IN VARCHAR2
1110 , p_segment23 IN VARCHAR2
1111 , p_segment24 IN VARCHAR2
1112 , p_segment25 IN VARCHAR2
1113 , p_segment26 IN VARCHAR2
1114 , p_segment27 IN VARCHAR2
1115 , p_segment28 IN VARCHAR2
1116 , p_segment29 IN VARCHAR2
1120 , p_id_flex_code IN VARCHAR2
1117 , p_segment30 IN VARCHAR2
1118 , p_flex_application_id IN NUMBER
1119 , p_application_short_name IN VARCHAR2
1121 , p_id_flex_num IN NUMBER
1122 )
1123 RETURN FND_FLEX_EXT.SegmentArray
1124 IS
1125 l_position NUMBER;
1126 l_SegmentArray FND_FLEX_EXT.SegmentArray;
1127 l_log_module VARCHAR2(240);
1128 l_id_flex_num NUMBER;
1129 l_id INTEGER; -- index for flexfield
1130 --
1131 BEGIN
1132 IF g_log_enabled THEN
1133 l_log_module := C_DEFAULT_MODULE||'.init_SegmentArray';
1134 END IF;
1135 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1136 trace
1137 (p_msg => 'BEGIN of init_SegmentArray'
1138 ,p_level => C_LEVEL_PROCEDURE
1139 ,p_module => l_log_module);
1140 trace
1141 (p_msg => 'p_flex_application_id = '|| TO_CHAR(p_flex_application_id)||
1142 ' - p_id_flex_code = '|| TO_CHAR(p_id_flex_code)||
1143 ' - p_id_flex_num = '|| TO_CHAR(p_id_flex_num)
1144 ,p_level => C_LEVEL_PROCEDURE
1145 ,p_module => l_log_module);
1146 END IF;
1147
1148 l_position := DBMS_UTILITY.get_hash_value(
1149 TO_CHAR(p_id_flex_num)||
1150 TO_CHAR(p_flex_application_id)||
1151 p_id_flex_code,1,1073741824);
1152
1153 IF NOT g_array_key_flexfield.EXISTS(l_position) THEN
1154
1155 l_id_flex_num := p_id_flex_num;
1156
1157 cache_key_flexfield(
1158 p_flex_application_id => p_flex_application_id,
1159 p_application_short_name => p_application_short_name,
1160 p_id_flex_code => p_id_flex_code,
1161 p_id_flex_num => l_id_flex_num
1162 );
1163
1164 END IF;
1165
1166 IF g_array_key_flexfield(l_position).segment_num.COUNT > 0 THEN
1167
1168 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1169 trace
1170 (p_msg => '# key flexfield segments = '||g_array_key_flexfield(l_position).segment_num.COUNT
1171 ,p_level => C_LEVEL_STATEMENT
1172 ,p_module => l_log_module);
1173 END IF;
1174
1175 l_id := 0;
1176 FOR Idx IN g_array_key_flexfield(l_position).segment_num.first ..
1177 g_array_key_flexfield(l_position).segment_num.last LOOP
1178
1179 IF g_array_key_flexfield(l_position).segment_num.EXISTS(Idx) THEN
1180
1181 l_id := l_id + 1;
1182
1183 CASE g_array_key_flexfield(l_position).segment_num(Idx)
1184
1185 WHEN 'SEGMENT1' THEN l_SegmentArray(l_id) := p_segment1;
1186 WHEN 'SEGMENT2' THEN l_SegmentArray(l_id) := p_segment2;
1187 WHEN 'SEGMENT3' THEN l_SegmentArray(l_id) := p_segment3;
1188 WHEN 'SEGMENT4' THEN l_SegmentArray(l_id) := p_segment4;
1189 WHEN 'SEGMENT5' THEN l_SegmentArray(l_id) := p_segment5;
1190 WHEN 'SEGMENT6' THEN l_SegmentArray(l_id) := p_segment6;
1191 WHEN 'SEGMENT7' THEN l_SegmentArray(l_id) := p_segment7;
1192 WHEN 'SEGMENT8' THEN l_SegmentArray(l_id) := p_segment8;
1193 WHEN 'SEGMENT9' THEN l_SegmentArray(l_id) := p_segment9;
1194 WHEN 'SEGMENT10' THEN l_SegmentArray(l_id) := p_segment10;
1195 WHEN 'SEGMENT11' THEN l_SegmentArray(l_id) := p_segment11;
1196 WHEN 'SEGMENT12' THEN l_SegmentArray(l_id) := p_segment12;
1197 WHEN 'SEGMENT13' THEN l_SegmentArray(l_id) := p_segment13;
1198 WHEN 'SEGMENT14' THEN l_SegmentArray(l_id) := p_segment14;
1199 WHEN 'SEGMENT15' THEN l_SegmentArray(l_id) := p_segment15;
1200 WHEN 'SEGMENT16' THEN l_SegmentArray(l_id) := p_segment16;
1201 WHEN 'SEGMENT17' THEN l_SegmentArray(l_id) := p_segment17;
1202 WHEN 'SEGMENT18' THEN l_SegmentArray(l_id) := p_segment18;
1203 WHEN 'SEGMENT19' THEN l_SegmentArray(l_id) := p_segment19;
1204 WHEN 'SEGMENT20' THEN l_SegmentArray(l_id) := p_segment20;
1205 WHEN 'SEGMENT21' THEN l_SegmentArray(l_id) := p_segment21;
1206 WHEN 'SEGMENT22' THEN l_SegmentArray(l_id) := p_segment22;
1207 WHEN 'SEGMENT23' THEN l_SegmentArray(l_id) := p_segment23;
1208 WHEN 'SEGMENT24' THEN l_SegmentArray(l_id) := p_segment24;
1209 WHEN 'SEGMENT25' THEN l_SegmentArray(l_id) := p_segment25;
1210 WHEN 'SEGMENT26' THEN l_SegmentArray(l_id) := p_segment26;
1211 WHEN 'SEGMENT27' THEN l_SegmentArray(l_id) := p_segment27;
1212 WHEN 'SEGMENT28' THEN l_SegmentArray(l_id) := p_segment28;
1213 WHEN 'SEGMENT29' THEN l_SegmentArray(l_id) := p_segment29;
1214 WHEN 'SEGMENT30' THEN l_SegmentArray(l_id) := p_segment30;
1215 ELSE null;
1216 END CASE;
1217
1218 /*
1219 WHEN 'SEGMENT1' THEN l_SegmentArray(Idx) := p_segment1;
1220 WHEN 'SEGMENT2' THEN l_SegmentArray(Idx) := p_segment2;
1221 WHEN 'SEGMENT3' THEN l_SegmentArray(Idx) := p_segment3;
1222 WHEN 'SEGMENT4' THEN l_SegmentArray(Idx) := p_segment4;
1223 WHEN 'SEGMENT5' THEN l_SegmentArray(Idx) := p_segment5;
1224 WHEN 'SEGMENT6' THEN l_SegmentArray(Idx) := p_segment6;
1225 WHEN 'SEGMENT7' THEN l_SegmentArray(Idx) := p_segment7;
1226 WHEN 'SEGMENT8' THEN l_SegmentArray(Idx) := p_segment8;
1227 WHEN 'SEGMENT9' THEN l_SegmentArray(Idx) := p_segment9;
1228 WHEN 'SEGMENT10' THEN l_SegmentArray(Idx) := p_segment10;
1232 WHEN 'SEGMENT14' THEN l_SegmentArray(Idx) := p_segment14;
1229 WHEN 'SEGMENT11' THEN l_SegmentArray(Idx) := p_segment11;
1230 WHEN 'SEGMENT12' THEN l_SegmentArray(Idx) := p_segment12;
1231 WHEN 'SEGMENT13' THEN l_SegmentArray(Idx) := p_segment13;
1233 WHEN 'SEGMENT15' THEN l_SegmentArray(Idx) := p_segment15;
1234 WHEN 'SEGMENT16' THEN l_SegmentArray(Idx) := p_segment16;
1235 WHEN 'SEGMENT17' THEN l_SegmentArray(Idx) := p_segment17;
1236 WHEN 'SEGMENT18' THEN l_SegmentArray(Idx) := p_segment18;
1237 WHEN 'SEGMENT19' THEN l_SegmentArray(Idx) := p_segment19;
1238 WHEN 'SEGMENT20' THEN l_SegmentArray(Idx) := p_segment20;
1239 WHEN 'SEGMENT21' THEN l_SegmentArray(Idx) := p_segment21;
1240 WHEN 'SEGMENT22' THEN l_SegmentArray(Idx) := p_segment22;
1241 WHEN 'SEGMENT23' THEN l_SegmentArray(Idx) := p_segment23;
1242 WHEN 'SEGMENT24' THEN l_SegmentArray(Idx) := p_segment24;
1243 WHEN 'SEGMENT25' THEN l_SegmentArray(Idx) := p_segment25;
1244 WHEN 'SEGMENT26' THEN l_SegmentArray(Idx) := p_segment26;
1245 WHEN 'SEGMENT27' THEN l_SegmentArray(Idx) := p_segment27;
1246 WHEN 'SEGMENT28' THEN l_SegmentArray(Idx) := p_segment28;
1247 WHEN 'SEGMENT29' THEN l_SegmentArray(Idx) := p_segment29;
1248 WHEN 'SEGMENT30' THEN l_SegmentArray(Idx) := p_segment30;
1249 ELSE null;
1250 END CASE;
1251 */
1252 END IF;
1253
1254 END LOOP;
1255
1256 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1257 FOR l_id IN 1 .. 30 LOOP
1258 IF l_SegmentArray.EXISTS(l_id) THEN
1259 trace
1260 (p_msg => 'l_SegmentArray('||l_id||') = '||l_SegmentArray(l_id)
1261 ,p_level => C_LEVEL_PROCEDURE
1262 ,p_module => l_log_module);
1263 END IF;
1264 END LOOP;
1265 END IF;
1266
1267 END IF;
1268 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1269 trace
1270 (p_msg => 'END of init_SegmentArray'
1271 ,p_level => C_LEVEL_PROCEDURE
1272 ,p_module => l_log_module);
1273
1274 END IF;
1275 RETURN l_SegmentArray;
1276 EXCEPTION
1277 WHEN xla_exceptions_pkg.application_exception THEN
1278 RAISE;
1279 WHEN OTHERS THEN
1280 xla_exceptions_pkg.raise_message
1281 (p_location => 'xla_ae_code_combination_pkg.init_SegmentArray');
1282 END init_SegmentArray;
1283
1284 /*---------------------------------------------------+
1285 | |
1286 | Private procedure |
1287 | |
1288 | cache_combination_id |
1289 | |
1290 | cache the key flexfield combination involved in |
1291 | the accounting process |
1292 +---------------------------------------------------*/
1293
1294 PROCEDURE cache_combination_id(
1295 p_combination_id IN NUMBER
1296 , p_flex_application_id IN NUMBER
1297 , p_application_short_name IN VARCHAR2
1298 , p_id_flex_code IN VARCHAR2
1299 , p_id_flex_num IN NUMBER
1300 )
1301 IS
1302 l_ConcatKey VARCHAR2(4000);
1303 l_SegmentArray FND_FLEX_EXT.SegmentArray;
1304 l_SegmentNumber PLS_INTEGER;
1305 l_message VARCHAR2(4000);
1306 l_log_module VARCHAR2(240);
1307 l_position NUMBER;
1308 l_coa_position NUMBER;
1309 l_id_flex_num NUMBER;
1310 invalid_key_combination_id EXCEPTION;
1311 --
1312 BEGIN
1313 IF g_log_enabled THEN
1314 l_log_module := C_DEFAULT_MODULE||'.cache_combination_id';
1315 END IF;
1316 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1317 trace
1318 (p_msg => 'BEGIN of cache_combination_id'
1319 ,p_level => C_LEVEL_PROCEDURE
1320 ,p_module => l_log_module);
1321 trace
1322 (p_msg => 'p_combination_id = '|| TO_CHAR(p_combination_id)||
1323 ' - p_flex_application_id = '|| TO_CHAR(p_flex_application_id)||
1324 ' - p_id_flex_code = '|| p_id_flex_code||
1325 ' - p_id_flex_num = '|| TO_CHAR(p_id_flex_num)
1326 ,p_level => C_LEVEL_PROCEDURE
1327 ,p_module => l_log_module);
1328 END IF;
1329
1330 l_position := DBMS_UTILITY.get_hash_value(
1331 TO_CHAR(p_combination_id)||
1332 TO_CHAR(p_id_flex_num)||
1333 TO_CHAR(p_flex_application_id)||
1334 p_id_flex_code,1,1073741824);
1335
1336
1337 l_coa_position:= DBMS_UTILITY.get_hash_value(
1338 TO_CHAR(p_id_flex_num)||
1339 TO_CHAR(p_flex_application_id)||
1340 p_id_flex_code,1,1073741824);
1341
1342
1343 IF NOT g_array_key_flexfield.EXISTS(l_coa_position) THEN
1344
1345 l_id_flex_num := p_id_flex_num;
1346
1347 cache_key_flexfield(
1348 p_flex_application_id => p_flex_application_id,
1349 p_application_short_name => p_application_short_name,
1350 p_id_flex_code => p_id_flex_code,
1351 p_id_flex_num => l_id_flex_num
1355 -- added to fix bug4304098
1352 );
1353
1354 ELSE
1356 l_id_flex_num := nvl(p_id_flex_num,g_array_key_flexfield(l_coa_position).id_flex_num);
1357
1358 END IF;
1359
1360 --accounting flexfield
1361 IF NOT g_array_combination_id.EXISTS(l_position) AND
1362 p_flex_application_id = 101 AND
1363 p_id_flex_code ='GL#' AND
1364 l_id_flex_num IS NOT NULL THEN
1365
1366 BEGIN
1367 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1368 trace
1369 (p_msg => 'SQL - Select from gl_code_combinations '
1370 ,p_level => C_LEVEL_STATEMENT
1371 ,p_module => l_log_module);
1372 END IF;
1373
1374 SELECT
1375 gcc.code_combination_id
1376 , p_flex_application_id
1377 , p_id_flex_code
1378 , gcc.chart_of_accounts_id
1379 , gcc.segment1
1380 , gcc.segment2
1381 , gcc.segment3
1382 , gcc.segment4
1383 , gcc.segment5
1384 , gcc.segment6
1385 , gcc.segment7
1386 , gcc.segment8
1387 , gcc.segment9
1388 , gcc.segment10
1389 , gcc.segment11
1390 , gcc.segment12
1391 , gcc.segment13
1392 , gcc.segment14
1393 , gcc.segment15
1394 , gcc.segment16
1395 , gcc.segment17
1396 , gcc.segment18
1397 , gcc.segment19
1398 , gcc.segment20
1399 , gcc.segment21
1400 , gcc.segment22
1401 , gcc.segment23
1402 , gcc.segment24
1403 , gcc.segment25
1404 , gcc.segment26
1405 , gcc.segment27
1406 , gcc.segment28
1407 , gcc.segment29
1408 , gcc.segment30
1409 , 'Y'
1410 INTO
1411 g_array_combination_id(l_position).combination_id
1412 , g_array_combination_id(l_position).flexfield_application_id
1413 , g_array_combination_id(l_position).id_flex_code
1414 , g_array_combination_id(l_position).id_flex_num
1415 , g_array_combination_id(l_position).segment1
1416 , g_array_combination_id(l_position).segment2
1417 , g_array_combination_id(l_position).segment3
1418 , g_array_combination_id(l_position).segment4
1419 , g_array_combination_id(l_position).segment5
1420 , g_array_combination_id(l_position).segment6
1421 , g_array_combination_id(l_position).segment7
1422 , g_array_combination_id(l_position).segment8
1423 , g_array_combination_id(l_position).segment9
1424 , g_array_combination_id(l_position).segment10
1425 , g_array_combination_id(l_position).segment11
1426 , g_array_combination_id(l_position).segment12
1427 , g_array_combination_id(l_position).segment13
1428 , g_array_combination_id(l_position).segment14
1429 , g_array_combination_id(l_position).segment15
1430 , g_array_combination_id(l_position).segment16
1431 , g_array_combination_id(l_position).segment17
1432 , g_array_combination_id(l_position).segment18
1433 , g_array_combination_id(l_position).segment19
1434 , g_array_combination_id(l_position).segment20
1435 , g_array_combination_id(l_position).segment21
1436 , g_array_combination_id(l_position).segment22
1437 , g_array_combination_id(l_position).segment23
1438 , g_array_combination_id(l_position).segment24
1439 , g_array_combination_id(l_position).segment25
1440 , g_array_combination_id(l_position).segment26
1441 , g_array_combination_id(l_position).segment27
1442 , g_array_combination_id(l_position).segment28
1443 , g_array_combination_id(l_position).segment29
1444 , g_array_combination_id(l_position).segment30
1445 , g_array_combination_id(l_position).combination_status
1446 FROM gl_code_combinations gcc
1447 WHERE gcc.code_combination_id = p_combination_id
1448 AND gcc.chart_of_accounts_id = l_id_flex_num
1449 AND gcc.template_id IS NULL
1450 ;
1451
1452 EXCEPTION
1453 WHEN NO_DATA_FOUND THEN
1454 RAISE invalid_key_combination_id;
1455 END;
1456
1457 ELSE
1458 --key flexfield
1459 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1460 trace
1461 (p_msg => '-> CALL FND_FLEX_EXT.get_segments API'
1462 ,p_level => C_LEVEL_STATEMENT
1463 ,p_module => l_log_module);
1464 END IF;
1465
1466 IF FND_FLEX_EXT.get_segments(application_short_name => p_application_short_name ,
1467 key_flex_code => p_id_flex_code ,
1468 structure_number => l_id_flex_num ,
1469 combination_id => p_combination_id ,
1470 n_segments => l_SegmentNumber ,
1471 segments => l_SegmentArray )
1472 THEN
1473 g_array_combination_id(l_position).combination_id := p_combination_id ;
1474 g_array_combination_id(l_position).combination_status := 'Y' ;
1475 g_array_combination_id(l_position).id_flex_num := l_id_flex_num ;
1476 g_array_combination_id(l_position).flexfield_application_id := p_flex_application_id ;
1477 g_array_combination_id(l_position).id_flex_code := p_id_flex_code;
1478
1479 IF l_SegmentNumber > 0 THEN
1480
1481 FOR Idx IN l_SegmentArray.FIRST .. l_SegmentArray.LAST LOOP
1482
1483 CASE g_array_key_flexfield(l_coa_position).segment_num(Idx)
1484
1488 WHEN 'SEGMENT4' THEN g_array_combination_id(l_position).segment4 := l_SegmentArray(Idx);
1485 WHEN 'SEGMENT1' THEN g_array_combination_id(l_position).segment1 := l_SegmentArray(Idx);
1486 WHEN 'SEGMENT2' THEN g_array_combination_id(l_position).segment2 := l_SegmentArray(Idx);
1487 WHEN 'SEGMENT3' THEN g_array_combination_id(l_position).segment3 := l_SegmentArray(Idx);
1489 WHEN 'SEGMENT5' THEN g_array_combination_id(l_position).segment5 := l_SegmentArray(Idx);
1490 WHEN 'SEGMENT6' THEN g_array_combination_id(l_position).segment6 := l_SegmentArray(Idx);
1491 WHEN 'SEGMENT7' THEN g_array_combination_id(l_position).segment7 := l_SegmentArray(Idx);
1492 WHEN 'SEGMENT8' THEN g_array_combination_id(l_position).segment8 := l_SegmentArray(Idx);
1493 WHEN 'SEGMENT9' THEN g_array_combination_id(l_position).segment9 := l_SegmentArray(Idx);
1494 WHEN 'SEGMENT10' THEN g_array_combination_id(l_position).segment10 := l_SegmentArray(Idx);
1495 WHEN 'SEGMENT11' THEN g_array_combination_id(l_position).segment11 := l_SegmentArray(Idx);
1496 WHEN 'SEGMENT12' THEN g_array_combination_id(l_position).segment12 := l_SegmentArray(Idx);
1497 WHEN 'SEGMENT13' THEN g_array_combination_id(l_position).segment13 := l_SegmentArray(Idx);
1498 WHEN 'SEGMENT14' THEN g_array_combination_id(l_position).segment14 := l_SegmentArray(Idx);
1499 WHEN 'SEGMENT15' THEN g_array_combination_id(l_position).segment15 := l_SegmentArray(Idx);
1500 WHEN 'SEGMENT16' THEN g_array_combination_id(l_position).segment16 := l_SegmentArray(Idx);
1501 WHEN 'SEGMENT17' THEN g_array_combination_id(l_position).segment17 := l_SegmentArray(Idx);
1502 WHEN 'SEGMENT18' THEN g_array_combination_id(l_position).segment18 := l_SegmentArray(Idx);
1503 WHEN 'SEGMENT19' THEN g_array_combination_id(l_position).segment19 := l_SegmentArray(Idx);
1504 WHEN 'SEGMENT20' THEN g_array_combination_id(l_position).segment20 := l_SegmentArray(Idx);
1505 WHEN 'SEGMENT21' THEN g_array_combination_id(l_position).segment21 := l_SegmentArray(Idx);
1506 WHEN 'SEGMENT22' THEN g_array_combination_id(l_position).segment22 := l_SegmentArray(Idx);
1507 WHEN 'SEGMENT23' THEN g_array_combination_id(l_position).segment23 := l_SegmentArray(Idx);
1508 WHEN 'SEGMENT24' THEN g_array_combination_id(l_position).segment24 := l_SegmentArray(Idx);
1509 WHEN 'SEGMENT25' THEN g_array_combination_id(l_position).segment25 := l_SegmentArray(Idx);
1510 WHEN 'SEGMENT26' THEN g_array_combination_id(l_position).segment26 := l_SegmentArray(Idx);
1511 WHEN 'SEGMENT27' THEN g_array_combination_id(l_position).segment27 := l_SegmentArray(Idx);
1512 WHEN 'SEGMENT28' THEN g_array_combination_id(l_position).segment28 := l_SegmentArray(Idx);
1513 WHEN 'SEGMENT29' THEN g_array_combination_id(l_position).segment29 := l_SegmentArray(Idx);
1514 WHEN 'SEGMENT30' THEN g_array_combination_id(l_position).segment30 := l_SegmentArray(Idx);
1515 ELSE null;
1516
1517 END CASE;
1518
1519 END LOOP;
1520
1521 END IF;
1522
1523 ELSE
1524 RAISE invalid_key_combination_id;
1525 END IF;
1526
1527 END IF;
1528
1529 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1530 trace
1531 (p_msg => 'END of cache_combination_id'
1532 ,p_level => C_LEVEL_PROCEDURE
1533 ,p_module => l_log_module);
1534 END IF;
1535 EXCEPTION
1536
1537 WHEN invalid_key_combination_id THEN
1538
1539 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
1540 g_array_combination_id(l_position).combination_id := p_combination_id ;
1541 g_array_combination_id(l_position).id_flex_num := nvl(p_id_flex_num,l_id_flex_num);
1542 g_array_combination_id(l_position).flexfield_application_id := p_flex_application_id ;
1543 g_array_combination_id(l_position).id_flex_code := p_id_flex_code ;
1544 g_array_combination_id(l_position).combination_status := 'N';
1545
1546 l_message := SUBSTR(FND_FLEX_EXT.get_message,1,4000);
1547
1548 l_ConcatKey := FND_FLEX_EXT.concatenate_segments(
1549 n_segments => l_SegmentNumber,
1550 segments => l_SegmentArray,
1551 delimiter => FND_FLEX_EXT.get_delimiter(
1552 application_short_name => p_application_short_name,
1553 key_flex_code => p_id_flex_code,
1554 structure_number => nvl(p_id_flex_num,l_id_flex_num)
1555 ));
1556
1557 xla_accounting_err_pkg.build_message
1558 (p_appli_s_name => 'XLA'
1559 ,p_msg_name => 'XLA_AP_INVALID_AOL_CCID'
1560 ,p_token_1 => 'ACCOUNT_VALUE'
1561 ,p_value_1 => l_ConcatKey
1562 ,p_token_2 => 'MESSAGE'
1563 ,p_value_2 => l_message
1564 ,p_entity_id => xla_ae_journal_entry_pkg.g_cache_event.entity_id
1565 ,p_event_id => xla_ae_journal_entry_pkg.g_cache_event.event_id
1566 ,p_ledger_id => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id);
1567
1568 IF (C_LEVEL_ERROR >= g_log_level) THEN
1569 trace
1570 (p_msg => 'ERROR: XLA_AP_INVALID_AOL_CCID'
1571 ,p_level => C_LEVEL_ERROR
1572 ,p_module => l_log_module);
1573 END IF;
1574 WHEN xla_exceptions_pkg.application_exception THEN
1575 RAISE;
1576 WHEN OTHERS THEN
1577 xla_exceptions_pkg.raise_message
1581 /*-----------------------------------------------------+
1578 (p_location => 'xla_ae_code_combination_pkg.cache_combination_id');
1579 END cache_combination_id;
1580
1582 | |
1583 | Private function |
1584 | |
1585 | get_account_value |
1586 | |
1587 | retruns the key flex concateneted value for a given |
1588 | key combination identifier. |
1589 | |
1590 +-----------------------------------------------------*/
1591
1592 FUNCTION get_account_value(
1593 p_combination_id IN NUMBER
1594 , p_flex_application_id IN NUMBER
1595 , p_application_short_name IN VARCHAR2
1596 , p_id_flex_code IN VARCHAR2
1597 , p_id_flex_num IN NUMBER)
1598 RETURN VARCHAR2
1599 IS
1600 l_ConcatKey VARCHAR2(4000); -- key flex concateneted value
1601 l_SegmentArray FND_FLEX_EXT.SegmentArray;
1602 l_SegmentNumber PLS_INTEGER;
1603 l_log_module VARCHAR2(240);
1604 l_position NUMBER;
1605 l_id_flex_num NUMBER;
1606 BEGIN
1607 --
1608 IF g_log_enabled THEN
1609 l_log_module := C_DEFAULT_MODULE||'.get_account_value';
1610 END IF;
1611 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1612 trace
1613 (p_msg => 'BEGIN of get_account_value'
1614 ,p_level => C_LEVEL_PROCEDURE
1615 ,p_module => l_log_module);
1616 trace
1617 (p_msg => 'p_combination_id = '|| TO_CHAR(p_combination_id)
1618 ,p_level => C_LEVEL_PROCEDURE
1619 ,p_module => l_log_module);
1620 trace
1621 (p_msg => 'p_id_flex_num = '|| TO_CHAR(p_id_flex_num)
1622 ,p_level => C_LEVEL_PROCEDURE
1623 ,p_module => l_log_module);
1624
1625 END IF;
1626
1627 l_id_flex_num := p_id_flex_num;
1628
1629 IF l_id_flex_num IS NULL THEN
1630
1631 l_position:= DBMS_UTILITY.get_hash_value(
1632 TO_CHAR(p_id_flex_num)||
1633 TO_CHAR(p_flex_application_id)||
1634 p_id_flex_code,1,1073741824);
1635
1636 IF NOT g_array_key_flexfield.EXISTS(l_position) THEN
1637
1638 cache_key_flexfield(
1639 p_flex_application_id => p_flex_application_id,
1640 p_application_short_name => p_application_short_name,
1641 p_id_flex_code => p_id_flex_code,
1642 p_id_flex_num => l_id_flex_num
1643 );
1644 END IF;
1645
1646 END IF;
1647
1648 IF FND_FLEX_EXT.get_segments(application_short_name => p_application_short_name ,
1649 key_flex_code => p_id_flex_code ,
1650 structure_number => l_id_flex_num ,
1651 combination_id => p_combination_id ,
1652 n_segments => l_SegmentNumber ,
1653 segments => l_SegmentArray )
1654 THEN
1655
1656 l_ConcatKey := FND_FLEX_EXT.concatenate_segments(
1657 n_segments => l_SegmentNumber,
1658 segments => l_SegmentArray,
1659 delimiter => FND_FLEX_EXT.get_delimiter(
1660 application_short_name => p_application_short_name,
1661 key_flex_code => p_id_flex_code,
1662 structure_number => l_id_flex_num
1663 )
1664 );
1665 ELSE
1666 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1667 trace
1668 (p_msg => 'Invalid ccid. = '||p_combination_id
1669 ,p_level => C_LEVEL_PROCEDURE
1670 ,p_module => l_log_module);
1671 END IF;
1672 l_ConcatKey := NULL;
1673 END IF;
1674 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1675 trace
1676 (p_msg => 'return value. = '||l_ConcatKey
1677 ,p_level => C_LEVEL_PROCEDURE
1678 ,p_module => l_log_module);
1679 trace
1680 (p_msg => 'END of get_account_value'
1681 ,p_level => C_LEVEL_PROCEDURE
1682 ,p_module => l_log_module);
1683 END IF;
1684 RETURN l_ConcatKey;
1685 EXCEPTION
1686 WHEN xla_exceptions_pkg.application_exception THEN
1687 RAISE;
1688 WHEN OTHERS THEN
1689 xla_exceptions_pkg.raise_message
1690 (p_location => 'xla_ae_code_combination_pkg.get_account_value');
1691 END get_account_value;
1692
1693 --======================================================================
1694 --
1695 --
1696 --
1697 -- routines to retrieve values from caches
1698 --
1699 --
1700 --
1701 --=======================================================================
1702
1703 /*-----------------------------------------------------+
1704 | |
1708 | |
1705 | Public function |
1706 | |
1707 | get_segment_code |
1709 | Returns the segment code for a given key flexfield |
1710 | attribute. |
1711 | |
1712 +-----------------------------------------------------*/
1713 -- replaces FUNCTION get_segment_qualifier()
1714 FUNCTION get_segment_code(
1715 p_flex_application_id IN NUMBER
1716 , p_application_short_name IN VARCHAR2
1717 , p_id_flex_code IN VARCHAR2
1718 , p_id_flex_num IN NUMBER
1719 , p_segment_qualifier IN VARCHAR2
1720 , p_component_type IN VARCHAR2
1721 , p_component_code IN VARCHAR2
1722 , p_component_type_code IN VARCHAR2
1723 , p_component_appl_id IN INTEGER
1724 , p_amb_context_code IN VARCHAR2
1725 , p_entity_code IN VARCHAR2
1726 , p_event_class_code IN VARCHAR2
1727 )
1728 RETURN VARCHAR2
1729 IS
1730 l_position NUMBER;
1731 l_id_flex_num NUMBER;
1732 l_segment_code VARCHAR2(30);
1733 l_segment_name VARCHAR2(240);
1734 l_key_flexfield_name VARCHAR2(240);
1735 l_product_name VARCHAR2(240);
1736 l_type VARCHAR2(240);
1737 l_name VARCHAR2(240);
1738 l_owner VARCHAR2(240);
1739 l_log_module VARCHAR2(240);
1740 invalid_segment_qualifier EXCEPTION;
1741 BEGIN
1742 IF g_log_enabled THEN
1743 l_log_module := C_DEFAULT_MODULE||'.get_segment_code';
1744 END IF;
1745 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1746 trace
1747 (p_msg => 'BEGIN of get_segment_code'
1748 ,p_level => C_LEVEL_PROCEDURE
1749 ,p_module => l_log_module);
1750 trace
1751 (p_msg => 'p_flex_application_id ='||p_flex_application_id||
1752 ' - p_id_flex_code =' ||p_id_flex_code||
1753 ' - p_id_flex_num =' ||TO_CHAR(p_id_flex_num) ||
1754 ' - p_segment_qualifier ='||p_segment_qualifier
1755 ,p_level => C_LEVEL_PROCEDURE
1756 ,p_module => l_log_module);
1757
1758 END IF;
1759
1760 IF p_segment_qualifier LIKE 'SEGMENT%' THEN
1761
1762 l_segment_code := p_segment_qualifier;
1763
1764 ELSE
1765 l_position := DBMS_UTILITY.get_hash_value(
1766 TO_CHAR(p_id_flex_num)||
1767 TO_CHAR(p_flex_application_id)||
1768 p_id_flex_code,1,1073741824);
1769
1770 IF NOT g_array_key_flexfield.EXISTS(l_position) THEN
1771
1772 l_id_flex_num := p_id_flex_num;
1773
1774 cache_key_flexfield(
1775 p_flex_application_id => p_flex_application_id,
1776 p_application_short_name => p_application_short_name,
1777 p_id_flex_code => p_id_flex_code,
1778 p_id_flex_num => l_id_flex_num
1779 );
1780
1781 END IF;
1782
1783 if g_array_key_flexfield(l_position).segment_qualifier.EXISTS(p_segment_qualifier) then -- 5276582
1784
1785 l_segment_code := g_array_key_flexfield(l_position).segment_qualifier(p_segment_qualifier);
1786
1787 else -- 5276582
1788
1789 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1790 trace
1791 (p_msg => 'Invalid qualifier'
1792 ,p_level => C_LEVEL_PROCEDURE
1793 ,p_module => l_log_module);
1794 END IF;
1795 l_segment_code := null;
1796
1797 end if;
1798
1799 IF l_segment_code IS NULL THEN
1800 RAISE invalid_segment_qualifier;
1801 END IF;
1802
1803 END IF;
1804
1805 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1806 trace
1807 (p_msg => 'return value. = '||l_segment_code
1808 ,p_level => C_LEVEL_PROCEDURE
1809 ,p_module => l_log_module);
1810 trace
1811 (p_msg => 'END of get_segment_code'
1812 ,p_level => C_LEVEL_PROCEDURE
1813 ,p_module => l_log_module);
1814 END IF;
1815 RETURN l_segment_code;
1816 EXCEPTION
1817 WHEN invalid_segment_qualifier THEN
1818 xla_ae_journal_entry_pkg.g_global_status:= xla_ae_journal_entry_pkg.C_INVALID;
1819
1820 /*
1821 l_segment_name := xla_lookups_pkg.get_meaning(
1822 'XLA_FLEXFIELD_SEGMENTS_QUAL'
1823 , p_segment_qualifier
1824 );
1825 */
1826 l_segment_name := xla_flex_pkg.get_qualifier_name -- 5276582
1827 (p_application_id => p_flex_application_id
1828 ,p_id_flex_code => 'GL#'
1829 ,p_qualifier_segment => p_segment_qualifier);
1830
1831 l_key_flexfield_name := get_flex_structure_name(
1832 p_flex_application_id,
1833 p_id_flex_code,
1834 l_id_flex_num
1835 );
1836
1840 'XLA_AMB_COMPONENT_TYPE'
1837 l_product_name := get_application_name(p_flex_application_id);
1838
1839 l_type := xla_lookups_pkg.get_meaning(
1841 , p_component_type
1842 );
1843
1844 l_name := xla_ae_sources_pkg.GetComponentName (
1845 p_component_type
1846 ,p_component_code
1847 ,p_component_type_code
1848 ,p_component_appl_id
1849 ,p_amb_context_code
1850 ,p_entity_code
1851 ,p_event_class_code
1852 );
1853
1854 l_owner := xla_lookups_pkg.get_meaning('XLA_OWNER_TYPE',p_component_type_code );
1855
1856 xla_accounting_err_pkg.build_message
1857 (p_appli_s_name => 'XLA'
1858 ,p_msg_name => 'XLA_AP_INVALID_QUALIFIER'
1859 ,p_token_1 => 'QUALIFIER_NAME'
1860 ,p_value_1 => l_segment_name
1861 ,p_token_2 => 'KEY_FLEXFIELD_NAME'
1862 ,p_value_2 => l_key_flexfield_name
1863 ,p_token_3 => 'FLEX_APPLICATION_NAME'
1864 ,p_value_3 => l_product_name
1865 ,p_token_4 => 'COMPONENT_TYPE'
1866 ,p_value_4 => l_type
1867 ,p_token_5 => 'COMPONENT_NAME'
1868 ,p_value_5 => l_name
1869 ,p_token_6 => 'OWNER'
1870 ,p_value_6 => l_owner
1871 ,p_entity_id => xla_ae_journal_entry_pkg.g_cache_event.entity_id
1872 ,p_event_id => xla_ae_journal_entry_pkg.g_cache_event.event_id
1873 ,p_ledger_id => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
1874 ,p_ae_header_id => NULL
1875 );
1876 IF (C_LEVEL_ERROR >= g_log_level) THEN
1877 trace
1878 (p_msg => 'ERROR: XLA_AP_INVALID_QUALIFIER'
1879 ,p_level => C_LEVEL_ERROR
1880 ,p_module => l_log_module);
1881 END IF;
1882 RETURN NULL;
1883 WHEN xla_exceptions_pkg.application_exception THEN
1884 RAISE;
1885 WHEN OTHERS THEN
1886 xla_exceptions_pkg.raise_message
1887 (p_location => 'xla_ae_code_combination_pkg.get_segment_code');
1888 END get_segment_code;
1889
1890
1891 /*-------------------------------------------------------------+
1892 | |
1893 | Public function |
1894 | |
1895 | get_flex_segment_value |
1896 | |
1897 | retrieves the segment value from key flexfield combination |
1898 | |
1899 +-------------------------------------------------------------*/
1900 -- replaces get_flexfield_segment()
1901 FUNCTION get_flex_segment_value(
1902 p_combination_id IN NUMBER
1903 ,p_segment_code IN VARCHAR2
1904 ,p_id_flex_code IN VARCHAR2
1905 ,p_flex_application_id IN NUMBER
1906 ,p_application_short_name IN VARCHAR2
1907 ,p_source_code IN VARCHAR2
1908 ,p_source_type_code IN VARCHAR2
1909 ,p_source_application_id IN NUMBER
1910 ,p_component_type IN VARCHAR2
1911 ,p_component_code IN VARCHAR2
1912 ,p_component_type_code IN VARCHAR2
1913 ,p_component_appl_id IN INTEGER
1914 ,p_amb_context_code IN VARCHAR2
1915 ,p_entity_code IN VARCHAR2
1916 ,p_event_class_code IN VARCHAR2
1917 ,p_ae_header_id IN NUMBER
1918 )
1919 RETURN VARCHAR2
1920 IS
1921 l_position NUMBER;
1922 l_segment_value VARCHAR2(25);
1923 l_id_flex_num NUMBER;
1924 l_segment_code VARCHAR2(30);
1925 l_component_name VARCHAR2(240);
1926 l_product_name VARCHAR2(240);
1927 l_type VARCHAR2(240);
1928 l_name VARCHAR2(240);
1929 l_owner VARCHAR2(240);
1930 l_source_name VARCHAR2(240);
1931 l_key_flexfield_name VARCHAR2(240);
1932 l_ConcatKey VARCHAR2(4000);
1933 null_key_combination_id EXCEPTION;
1934 invalid_key_combination_id EXCEPTION;
1935 invalid_segment EXCEPTION;
1936 l_log_module VARCHAR2(240);
1937 BEGIN
1938 IF g_log_enabled THEN
1939 l_log_module := C_DEFAULT_MODULE||'.get_flex_segment_value';
1940 END IF;
1941 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1942 trace
1943 (p_msg => 'BEGIN of get_flexfield_segment'
1944 ,p_level => C_LEVEL_PROCEDURE
1945 ,p_module => l_log_module);
1946 trace
1947 (p_msg => 'p_combination_id = '|| TO_CHAR(p_combination_id)
1948 ||' - p_segment_code = '||p_segment_code
1949 ,p_level => C_LEVEL_PROCEDURE
1953
1950 ,p_module => l_log_module);
1951
1952 END IF;
1954 IF p_flex_application_id = 101 and p_id_flex_code ='GL#' THEN
1955 l_id_flex_num := xla_ae_journal_entry_pkg.g_cache_ledgers_info.source_coa_id;
1956 ELSE
1957 l_id_flex_num := NULL;
1958 END IF;
1959
1960 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1961 trace
1962 (p_msg => 'p_flex_application_id = '||p_flex_application_id
1963 ||' - p_id_flex_code = '||p_id_flex_code
1964 ||' - l_id_flex_num = '||l_id_flex_num
1965 ,p_level => C_LEVEL_STATEMENT
1966 ,p_module => l_log_module);
1967 trace
1968 (p_msg => 'p_source_code = '||p_source_code
1969 ||' - p_source_type_code = '||p_source_type_code
1970 ||' - p_source_application_id = '||p_source_application_id
1971
1972 ,p_level => C_LEVEL_STATEMENT
1973 ,p_module => l_log_module);
1974 trace
1975 (p_msg => 'p_component_code = '||p_component_code
1976 ||' - p_component_type = '||p_component_type
1977 ||' - p_component_type_code = '||p_component_type_code
1978 ||' - p_amb_context_code = '||p_amb_context_code
1979 ||' - p_entity_code = '||p_entity_code
1980 ||' - p_event_class_code = '||p_event_class_code
1981 ,p_level => C_LEVEL_STATEMENT
1982 ,p_module => l_log_module);
1983
1984 END IF;
1985
1986 IF p_combination_id IS NULL THEN
1987 RAISE null_key_combination_id;
1988 END IF;
1989
1990 l_position := DBMS_UTILITY.get_hash_value(
1991 TO_CHAR(p_combination_id)||
1992 TO_CHAR(l_id_flex_num)||
1993 TO_CHAR(p_flex_application_id)||
1994 p_id_flex_code,1,1073741824);
1995
1996 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1997 trace
1998 (p_msg => 'position = '||l_position
1999 ,p_level => C_LEVEL_STATEMENT
2000 ,p_module => l_log_module);
2001 END IF;
2002
2003 IF NOT g_array_combination_id.EXISTS(l_position) THEN
2004
2005 cache_combination_id(
2006 p_combination_id => p_combination_id
2007 , p_flex_application_id => p_flex_application_id
2008 , p_application_short_name => p_application_short_name
2009 , p_id_flex_code => p_id_flex_code
2010 , p_id_flex_num => l_id_flex_num )
2011 ;
2012 END IF;
2013
2014 IF nvl(g_array_combination_id(l_position).combination_status,'N') <> 'Y' THEN
2015 RAISE invalid_key_combination_id;
2016 END IF;
2017
2018 l_segment_code:= p_segment_code;
2019
2020 IF l_segment_code NOT LIKE 'SEGMENT%' THEN
2021
2022 l_segment_code := get_segment_code(
2023 p_flex_application_id => p_flex_application_id
2024 ,p_application_short_name => p_application_short_name
2025 ,p_id_flex_code => p_id_flex_code
2026 ,p_id_flex_num => l_id_flex_num
2027 ,p_segment_qualifier => l_segment_code
2028 ,p_component_type => p_component_type
2029 ,p_component_code => p_component_code
2030 ,p_component_type_code => p_component_type_code
2031 ,p_component_appl_id => p_component_appl_id
2032 ,p_amb_context_code => p_amb_context_code
2033 ,p_entity_code => p_entity_code
2034 ,p_event_class_code => p_event_class_code
2035 );
2036
2037 END IF;
2038
2039 CASE l_segment_code
2040
2041 WHEN 'SEGMENT1' THEN l_segment_value := g_array_combination_id(l_position).segment1;
2042 WHEN 'SEGMENT2' THEN l_segment_value := g_array_combination_id(l_position).segment2;
2043 WHEN 'SEGMENT3' THEN l_segment_value := g_array_combination_id(l_position).segment3;
2044 WHEN 'SEGMENT4' THEN l_segment_value := g_array_combination_id(l_position).segment4;
2045 WHEN 'SEGMENT5' THEN l_segment_value := g_array_combination_id(l_position).segment5;
2046 WHEN 'SEGMENT6' THEN l_segment_value := g_array_combination_id(l_position).segment6;
2047 WHEN 'SEGMENT7' THEN l_segment_value := g_array_combination_id(l_position).segment7;
2048 WHEN 'SEGMENT8' THEN l_segment_value := g_array_combination_id(l_position).segment8;
2049 WHEN 'SEGMENT9' THEN l_segment_value := g_array_combination_id(l_position).segment9;
2050 WHEN 'SEGMENT10' THEN l_segment_value := g_array_combination_id(l_position).segment10;
2051 WHEN 'SEGMENT11' THEN l_segment_value := g_array_combination_id(l_position).segment11;
2052 WHEN 'SEGMENT12' THEN l_segment_value := g_array_combination_id(l_position).segment12;
2053 WHEN 'SEGMENT13' THEN l_segment_value := g_array_combination_id(l_position).segment13;
2054 WHEN 'SEGMENT14' THEN l_segment_value := g_array_combination_id(l_position).segment14;
2055 WHEN 'SEGMENT15' THEN l_segment_value := g_array_combination_id(l_position).segment15;
2056 WHEN 'SEGMENT16' THEN l_segment_value := g_array_combination_id(l_position).segment16;
2057 WHEN 'SEGMENT17' THEN l_segment_value := g_array_combination_id(l_position).segment17;
2058 WHEN 'SEGMENT18' THEN l_segment_value := g_array_combination_id(l_position).segment18;
2059 WHEN 'SEGMENT19' THEN l_segment_value := g_array_combination_id(l_position).segment19;
2063 WHEN 'SEGMENT23' THEN l_segment_value := g_array_combination_id(l_position).segment23;
2060 WHEN 'SEGMENT20' THEN l_segment_value := g_array_combination_id(l_position).segment20;
2061 WHEN 'SEGMENT21' THEN l_segment_value := g_array_combination_id(l_position).segment21;
2062 WHEN 'SEGMENT22' THEN l_segment_value := g_array_combination_id(l_position).segment22;
2064 WHEN 'SEGMENT24' THEN l_segment_value := g_array_combination_id(l_position).segment24;
2065 WHEN 'SEGMENT25' THEN l_segment_value := g_array_combination_id(l_position).segment25;
2066 WHEN 'SEGMENT26' THEN l_segment_value := g_array_combination_id(l_position).segment26;
2067 WHEN 'SEGMENT27' THEN l_segment_value := g_array_combination_id(l_position).segment27;
2068 WHEN 'SEGMENT28' THEN l_segment_value := g_array_combination_id(l_position).segment28;
2069 WHEN 'SEGMENT29' THEN l_segment_value := g_array_combination_id(l_position).segment29;
2070 WHEN 'SEGMENT30' THEN l_segment_value := g_array_combination_id(l_position).segment30;
2071
2072 ELSE
2073 RAISE invalid_segment;
2074 END CASE;
2075
2076 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2077 trace
2078 (p_msg => 'return value. = '||l_segment_value
2079 ,p_level => C_LEVEL_PROCEDURE
2080 ,p_module => l_log_module);
2081 trace
2082 (p_msg => 'END of get_flex_segment_value'
2083 ,p_level => C_LEVEL_PROCEDURE
2084 ,p_module => l_log_module);
2085
2086 END IF;
2087 RETURN l_segment_value;
2088 EXCEPTION
2089 WHEN invalid_segment THEN
2090 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
2091
2092 l_key_flexfield_name := get_flex_structure_name(
2093 p_flex_application_id,
2094 p_id_flex_code,
2095 l_id_flex_num
2096 );
2097
2098 l_product_name := get_application_name(p_flex_application_id);
2099
2100 l_type := xla_lookups_pkg.get_meaning(
2101 'XLA_AMB_COMPONENT_TYPE'
2102 , p_component_type
2103 );
2104
2105 l_name := xla_ae_sources_pkg.GetComponentName (
2106 p_component_type
2107 ,p_component_code
2108 ,p_component_type_code
2109 ,p_component_appl_id
2110 ,p_amb_context_code
2111 ,p_entity_code
2112 ,p_event_class_code
2113 );
2114
2115 l_owner := xla_lookups_pkg.get_meaning('XLA_OWNER_TYPE',p_component_type_code );
2116
2117 xla_accounting_err_pkg.build_message
2118 (p_appli_s_name => 'XLA'
2119 ,p_msg_name => 'XLA_AP_INVALID_QUALIFIER'
2120 ,p_token_1 => 'QUALIFIER_NAME'
2121 ,p_value_1 => p_segment_code
2122 ,p_token_2 => 'KEY_FLEXFIELD_NAME'
2123 ,p_value_2 => l_key_flexfield_name
2124 ,p_token_3 => 'FLEX_APPLICATION_NAME'
2125 ,p_value_3 => l_product_name
2126 ,p_token_4 => 'COMPONENT_TYPE'
2127 ,p_value_4 => l_type
2128 ,p_token_5 => 'COMPONENT_NAME'
2129 ,p_value_5 => l_name
2130 ,p_token_6 => 'OWNER'
2131 ,p_value_6 => l_owner
2132 ,p_entity_id => xla_ae_journal_entry_pkg.g_cache_event.entity_id
2133 ,p_event_id => xla_ae_journal_entry_pkg.g_cache_event.event_id
2134 ,p_ledger_id => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
2135 ,p_ae_header_id => NULL
2136 );
2137
2138 IF (C_LEVEL_ERROR >= g_log_level) THEN
2139 trace
2140 (p_msg => 'ERROR:XLA_AP_INVALID_QUALIFIER'
2141 ,p_level => C_LEVEL_ERROR
2142 ,p_module => l_log_module);
2143
2144 END IF;
2145 RETURN NULL;
2146 WHEN null_key_combination_id THEN
2147 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
2148
2149 l_type := xla_lookups_pkg.get_meaning(
2150 'XLA_AMB_COMPONENT_TYPE'
2151 , p_component_type
2152 );
2153
2154 l_name := xla_ae_sources_pkg.GetComponentName (
2155 p_component_type
2156 ,p_component_code
2157 ,p_component_type_code
2158 ,p_component_appl_id
2159 ,p_amb_context_code
2160 ,p_entity_code
2161 ,p_event_class_code
2162 );
2163
2164 l_owner := xla_lookups_pkg.get_meaning(
2165 'XLA_OWNER_TYPE'
2166 ,p_component_type_code
2167 );
2168
2169 IF p_source_code IS NOT NULL THEN
2170 l_source_name := xla_ae_sources_pkg.GetSourceName(
2171 p_source_code
2172 ,p_source_type_code
2173 ,p_source_application_id
2174 );
2175 END IF;
2176
2177 xla_accounting_err_pkg.build_message
2178 (p_appli_s_name => 'XLA'
2182 ,p_token_2 => 'COMPONENT_TYPE'
2179 ,p_msg_name => 'XLA_AP_NULL_CODE_COMBINATION'
2180 ,p_token_1 => 'SOURCE_NAME'
2181 ,p_value_1 => l_source_name
2183 ,p_value_2 => l_type
2184 ,p_token_3 => 'COMPONENT_NAME'
2185 ,p_value_3 => l_name
2186 ,p_token_4 => 'OWNER'
2187 ,p_value_4 => l_owner
2188 ,p_token_5 => 'PRODUCT_NAME'
2189 ,p_value_5 => xla_ae_journal_entry_pkg.g_cache_event.application_name
2190 ,p_entity_id => xla_ae_journal_entry_pkg.g_cache_event.entity_id
2191 ,p_event_id => xla_ae_journal_entry_pkg.g_cache_event.event_id
2192 ,p_ledger_id => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
2193 ,p_ae_header_id => NULL --p_ae_header_id
2194 );
2195
2196 IF (C_LEVEL_ERROR >= g_log_level) THEN
2197 trace
2198 (p_msg => 'ERROR: XLA_AP_NULL_CODE_COMBINATION'
2199 ,p_level => C_LEVEL_ERROR
2200 ,p_module => l_log_module);
2201 END IF;
2202 RETURN NULL;
2203 WHEN invalid_key_combination_id THEN
2204 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
2205 l_type := xla_lookups_pkg.get_meaning(
2206 'XLA_AMB_COMPONENT_TYPE'
2207 , p_component_type
2208 );
2209
2210 l_name := xla_ae_sources_pkg.GetComponentName (
2211 p_component_type
2212 ,p_component_code
2213 ,p_component_type_code
2214 ,p_component_appl_id
2215 ,p_amb_context_code
2216 ,p_entity_code
2217 ,p_event_class_code
2218 );
2219
2220 l_owner := xla_lookups_pkg.get_meaning(
2221 'XLA_OWNER_TYPE'
2222 ,p_component_type_code
2223 );
2224
2225 IF p_source_code IS NOT NULL THEN
2226 l_source_name := xla_ae_sources_pkg.GetSourceName(
2227 p_source_code
2228 ,p_source_type_code
2229 ,p_source_application_id
2230 );
2231 END IF;
2232
2233 l_ConcatKey := NVL(get_account_value(
2234 p_combination_id
2235 , p_flex_application_id
2236 , p_application_short_name
2237 , p_id_flex_code
2238 , l_id_flex_num),TO_CHAR(p_combination_id));
2239
2240 xla_accounting_err_pkg.build_message
2241 (p_appli_s_name => 'XLA'
2242 ,p_msg_name => 'XLA_AP_INV_CODE_COMBINATION'
2243 ,p_token_1 => 'CODE_COMBINATION_ID'
2244 ,p_value_1 => l_ConcatKey
2245 ,p_token_2 => 'SOURCE_NAME'
2246 ,p_value_2 => l_source_name
2247 ,p_token_3 => 'COMPONENT_TYPE'
2248 ,p_value_3 => l_type
2249 ,p_token_4 => 'COMPONENT_NAME'
2250 ,p_value_4 => l_name
2251 ,p_token_5 => 'OWNER'
2252 ,p_value_5 => l_owner
2253 ,p_entity_id => xla_ae_journal_entry_pkg.g_cache_event.entity_id
2254 ,p_event_id => xla_ae_journal_entry_pkg.g_cache_event.event_id
2255 ,p_ledger_id => xla_ae_journal_entry_pkg.g_cache_event.target_ledger_id
2256 ,p_ae_header_id => NULL --p_ae_header_id
2257 );
2258
2259 IF (C_LEVEL_ERROR >= g_log_level) THEN
2260 trace
2261 (p_msg => 'ERROR: XLA_AP_INV_CODE_COMBINATION'
2262 ,p_level => C_LEVEL_ERROR
2263 ,p_module => l_log_module);
2264 END IF;
2265 RETURN NULL;
2266 WHEN xla_exceptions_pkg.application_exception THEN
2267 RETURN NULL;
2268 WHEN OTHERS THEN
2269 xla_exceptions_pkg.raise_message
2270 (p_location => 'xla_ae_code_combination_pkg.get_flex_segment_value');
2271 END get_flex_segment_value;
2272
2273 /*-------------------------------------------------------------+
2274 | |
2275 | Public function |
2276 | |
2277 | get_flex_segment_desc |
2278 | |
2279 | retrieves the segment description for a given segment code |
2280 | |
2281 +--------------------------------------------------------------*/
2282
2283 --replaces FUNCTION get_flexfield_description()
2284 FUNCTION get_flex_segment_desc(
2285 p_combination_id IN NUMBER
2286 ,p_segment_code IN VARCHAR2
2287 ,p_id_flex_code IN VARCHAR2
2288 ,p_flex_application_id IN NUMBER
2289 ,p_application_short_name IN VARCHAR2
2290 ,p_source_code IN VARCHAR2
2291 ,p_source_type_code IN VARCHAR2
2292 ,p_source_application_id IN NUMBER
2293 ,p_component_type IN VARCHAR2
2294 ,p_component_code IN VARCHAR2
2295 ,p_component_type_code IN VARCHAR2
2299 )
2296 ,p_component_appl_id IN INTEGER
2297 ,p_amb_context_code IN VARCHAR2
2298 ,p_ae_header_id IN NUMBER
2300 RETURN VARCHAR2
2301 IS
2302 l_segment_value VARCHAR2(240);
2303 l_segment_description VARCHAR2(240);
2304 l_segment_code VARCHAR2(30) ;
2305 l_desc_language VARCHAR2(30);
2306 l_id_flex_num NUMBER;
2307 l_log_module VARCHAR2(240);
2308 BEGIN
2309 IF g_log_enabled THEN
2310 l_log_module := C_DEFAULT_MODULE||'.get_flex_segment_desc';
2311 END IF;
2312 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2313 trace
2314 (p_msg => 'BEGIN of get_flex_segment_desc'
2315 ,p_level => C_LEVEL_PROCEDURE
2316 ,p_module => l_log_module);
2317 trace
2318 (p_msg => 'p_combination_id = '|| TO_CHAR(p_combination_id)
2319 ||' - p_segment_code = '||p_segment_code
2320 ,p_level => C_LEVEL_PROCEDURE
2321 ,p_module => l_log_module);
2322
2323 END IF;
2324
2325 l_segment_description:= NULL;
2326 IF p_flex_application_id = 101 and p_id_flex_code ='GL#' THEN
2327 l_id_flex_num := xla_ae_journal_entry_pkg.g_cache_ledgers_info.source_coa_id;
2328 ELSE
2329 l_id_flex_num := NULL;
2330 END IF;
2331 l_desc_language := xla_ae_journal_entry_pkg.g_cache_ledgers_info.description_language;
2332
2333 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2334 trace
2335 (p_msg => 'p_flex_application_id = '||p_flex_application_id
2336 ||' - p_id_flex_code = '||p_id_flex_code
2337 ||' - l_id_flex_num = '||l_id_flex_num
2338 ||' - language = '||l_desc_language
2339 ,p_level => C_LEVEL_STATEMENT
2340 ,p_module => l_log_module);
2341
2342 trace
2343 (p_msg => 'p_source_code = '||p_source_code
2344 ||' - p_source_type_code = '||p_source_type_code
2345 ||' - p_source_application_id = '||p_source_application_id
2346
2347 ,p_level => C_LEVEL_STATEMENT
2348 ,p_module => l_log_module);
2349 trace
2350 (p_msg => 'p_component_code = '||p_component_code
2351 ||' - p_component_type = '||p_component_type
2352 ||' - p_component_type_code = '||p_component_type_code
2353 ||' - p_amb_context_code = '||p_amb_context_code
2354 ,p_level => C_LEVEL_STATEMENT
2355 ,p_module => l_log_module);
2356 END IF;
2357
2358 IF l_segment_code NOT LIKE 'SEGMENT%' THEN
2359
2360 l_segment_code := get_segment_code(
2361 p_flex_application_id => p_flex_application_id
2362 ,p_application_short_name => p_application_short_name
2363 ,p_id_flex_code => p_id_flex_code
2364 ,p_id_flex_num => l_id_flex_num
2365 ,p_segment_qualifier => p_segment_code
2366 ,p_component_type => p_component_type
2367 ,p_component_code => p_component_code
2368 ,p_component_type_code => p_component_type_code
2369 ,p_component_appl_id => p_component_appl_id
2370 ,p_amb_context_code => p_amb_context_code
2371 ,p_entity_code => NULL
2372 ,p_event_class_code => NULL
2373 );
2374
2375 END IF;
2376
2377 l_segment_value := get_flex_segment_value(
2378 p_combination_id => p_combination_id
2379 ,p_segment_code => p_segment_code
2380 ,p_id_flex_code => p_id_flex_code
2381 ,p_flex_application_id => p_flex_application_id
2382 ,p_application_short_name => p_application_short_name
2383 ,p_source_code => p_source_code
2384 ,p_source_type_code => p_source_type_code
2385 ,p_source_application_id => p_source_application_id
2386 ,p_component_type => p_component_type
2387 ,p_component_code => p_component_code
2388 ,p_component_type_code => p_component_type_code
2389 ,p_component_appl_id => p_component_appl_id
2390 ,p_amb_context_code => p_amb_context_code
2391 ,p_entity_code => NULL
2392 ,p_event_class_code => NULL
2393 ,p_ae_header_id => p_ae_header_id
2394 );
2395
2396 IF l_segment_value IS NOT NULL AND l_segment_code IS NOT NULL THEN
2397
2398 BEGIN
2399 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2400 trace
2401 (p_msg => 'SQL - Select from fnd_flex_values_tl'
2402 ,p_level => C_LEVEL_STATEMENT
2403 ,p_module => l_log_module);
2404 END IF;
2405
2406 SELECT ffvt.description
2407 INTO l_segment_description
2408 FROM fnd_flex_values_tl ffvt
2409 , fnd_flex_values ffv
2410 , fnd_id_flex_segments fifs
2411 WHERE ffvt.flex_value_meaning = ffv.flex_value
2412 AND ffvt.flex_value_id = ffv.flex_value_id
2413 AND ffvt.language = l_desc_language
2417 AND fifs.id_flex_code = p_id_flex_code
2414 AND ffv.flex_value = l_segment_value
2415 AND ffv.flex_value_set_id = fifs.flex_value_set_id
2416 AND fifs.application_id = p_flex_application_id
2418 AND fifs.id_flex_num = l_id_flex_num
2419 AND fifs.application_column_name = l_segment_code
2420 ;
2421 EXCEPTION
2422 WHEN NO_DATA_FOUND THEN
2423 NULL;
2424 WHEN OTHERS THEN
2425 NULL;
2426 END;
2427 END IF;
2428
2429 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2430 trace
2431 (p_msg => 'return value. = '||l_segment_description
2432 ,p_level => C_LEVEL_PROCEDURE
2433 ,p_module => l_log_module);
2434 trace
2435 (p_msg => 'END of get_flex_segment_desc'
2436 ,p_level => C_LEVEL_PROCEDURE
2437 ,p_module => l_log_module);
2438 END IF;
2439 RETURN l_segment_description;
2440 EXCEPTION
2441 WHEN xla_exceptions_pkg.application_exception THEN
2442 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2443 trace
2444 (p_msg => 'Error. = '||sqlerrm
2445 ,p_level => C_LEVEL_PROCEDURE
2446 ,p_module => l_log_module);
2447 END IF;
2448 RAISE;
2449 WHEN OTHERS THEN
2450 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2451 trace
2452 (p_msg => 'Error. = '||sqlerrm
2453 ,p_level => C_LEVEL_PROCEDURE
2454 ,p_module => l_log_module);
2455 END IF;
2456 xla_exceptions_pkg.raise_message
2457 (p_location => 'xla_ae_code_combination_pkg.get_flex_segment_desc');
2458 END get_flex_segment_desc;
2459
2460
2461 --======================================================================
2462 --
2463 --
2464 --
2465 -- routines to build the new accounting ccids
2466 --
2467 --
2468 --
2469 --=======================================================================
2470
2471
2472 /*-------------------------------------------------------------+
2473 | |
2474 | Private function |
2475 | |
2476 | build_events_message |
2477 | |
2478 | build the line error messages for invalid ccids |
2479 | |
2480 +-------------------------------------------------------------*/
2481
2482 PROCEDURE build_events_message(
2483 p_appli_s_name IN VARCHAR2
2484 , p_msg_name IN VARCHAR2
2485 , p_token_1 IN VARCHAR2
2486 , p_value_1 IN VARCHAR2
2487 , p_token_2 IN VARCHAR2
2488 , p_value_2 IN VARCHAR2
2489 , p_segment1 IN VARCHAR2
2490 , p_segment2 IN VARCHAR2
2491 , p_segment3 IN VARCHAR2
2492 , p_segment4 IN VARCHAR2
2493 , p_segment5 IN VARCHAR2
2494 , p_segment6 IN VARCHAR2
2495 , p_segment7 IN VARCHAR2
2496 , p_segment8 IN VARCHAR2
2497 , p_segment9 IN VARCHAR2
2498 , p_segment10 IN VARCHAR2
2499 , p_segment11 IN VARCHAR2
2500 , p_segment12 IN VARCHAR2
2501 , p_segment13 IN VARCHAR2
2502 , p_segment14 IN VARCHAR2
2503 , p_segment15 IN VARCHAR2
2504 , p_segment16 IN VARCHAR2
2505 , p_segment17 IN VARCHAR2
2506 , p_segment18 IN VARCHAR2
2507 , p_segment19 IN VARCHAR2
2508 , p_segment20 IN VARCHAR2
2509 , p_segment21 IN VARCHAR2
2510 , p_segment22 IN VARCHAR2
2511 , p_segment23 IN VARCHAR2
2512 , p_segment24 IN VARCHAR2
2513 , p_segment25 IN VARCHAR2
2514 , p_segment26 IN VARCHAR2
2515 , p_segment27 IN VARCHAR2
2516 , p_segment28 IN VARCHAR2
2517 , p_segment29 IN VARCHAR2
2518 , p_segment30 IN VARCHAR2
2519 , p_chart_of_accounts_id IN NUMBER
2520 )
2521 IS
2522 l_log_module VARCHAR2(240);
2523 BEGIN
2524 --
2525 IF g_log_enabled THEN
2526 l_log_module := C_DEFAULT_MODULE||'.build_events_message';
2527 END IF;
2528 --
2529 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2530
2531 trace
2532 (p_msg => 'BEGIN of build_events_message'
2533 ,p_level => C_LEVEL_PROCEDURE
2534 ,p_module => l_log_module);
2535
2536 END IF;
2537 --
2538
2539 FOR events_rec IN (
2540 SELECT DISTINCT
2541 hdr.event_id event_id,
2542 hdr.entity_id entity_id,
2543 hdr.ledger_id ledger_id,
2544 hdr.ae_header_id ae_header_id
2545 FROM xla_ae_lines_gt temp,
2546 xla_ae_headers_gt hdr
2550 AND nvl(temp.segment2 ,'#') = nvl(p_segment2,'#')
2547 WHERE temp.ae_header_id = hdr.ae_header_id
2548 AND temp.ccid_coa_id = p_chart_of_accounts_id
2549 AND nvl(temp.segment1 ,'#') = nvl(p_segment1,'#')
2551 AND nvl(temp.segment3 ,'#') = nvl(p_segment3,'#')
2552 AND nvl(temp.segment4 ,'#') = nvl(p_segment4,'#')
2553 AND nvl(temp.segment5 ,'#') = nvl(p_segment5,'#')
2554 AND nvl(temp.segment6 ,'#') = nvl(p_segment6,'#')
2555 AND nvl(temp.segment7 ,'#') = nvl(p_segment7,'#')
2556 AND nvl(temp.segment8 ,'#') = nvl(p_segment8,'#')
2557 AND nvl(temp.segment9 ,'#') = nvl(p_segment9,'#')
2558 AND nvl(temp.segment10,'#') = nvl(p_segment10,'#')
2559 AND nvl(temp.segment11,'#') = nvl(p_segment11,'#')
2560 AND nvl(temp.segment12,'#') = nvl(p_segment12,'#')
2561 AND nvl(temp.segment13,'#') = nvl(p_segment13,'#')
2562 AND nvl(temp.segment14,'#') = nvl(p_segment14,'#')
2563 AND nvl(temp.segment15,'#') = nvl(p_segment15,'#')
2564 AND nvl(temp.segment16,'#') = nvl(p_segment16,'#')
2565 AND nvl(temp.segment17,'#') = nvl(p_segment17,'#')
2566 AND nvl(temp.segment18,'#') = nvl(p_segment18,'#')
2567 AND nvl(temp.segment19,'#') = nvl(p_segment19,'#')
2568 AND nvl(temp.segment20,'#') = nvl(p_segment20,'#')
2569 AND nvl(temp.segment21,'#') = nvl(p_segment21,'#')
2570 AND nvl(temp.segment22,'#') = nvl(p_segment22,'#')
2571 AND nvl(temp.segment23,'#') = nvl(p_segment23,'#')
2572 AND nvl(temp.segment24,'#') = nvl(p_segment24,'#')
2573 AND nvl(temp.segment25,'#') = nvl(p_segment25,'#')
2574 AND nvl(temp.segment26,'#') = nvl(p_segment26,'#')
2575 AND nvl(temp.segment27,'#') = nvl(p_segment27,'#')
2576 AND nvl(temp.segment28,'#') = nvl(p_segment28,'#')
2577 AND nvl(temp.segment29,'#') = nvl(p_segment29,'#')
2578 AND nvl(temp.segment30,'#') = nvl(p_segment30,'#')
2579 AND temp.code_combination_id = -1
2580 AND temp.code_combination_status_code = C_CREATED
2581 AND temp.balance_type_code <> 'X'
2582 AND hdr.entity_id IS NOT NULL
2583 AND hdr.event_id IS NOT NULL
2584 AND hdr.ledger_id IS NOT NULL
2585 )
2586 LOOP
2587
2588 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
2589 xla_accounting_err_pkg.build_message
2590 (p_appli_s_name => p_appli_s_name
2591 ,p_msg_name => p_msg_name
2592 ,p_token_1 => p_token_1
2593 ,p_value_1 => p_value_1
2594 ,p_token_2 => p_token_2
2595 ,p_value_2 => p_value_2
2596 ,p_entity_id => events_rec.entity_id
2597 ,p_event_id => events_rec.event_id
2598 ,p_ledger_id => events_rec.ledger_id
2599 ,p_ae_header_id => events_rec.ae_header_id
2600 );
2601
2602 IF (C_LEVEL_ERROR >= g_log_level) THEN
2603 trace
2604 (p_msg => 'ERROR: '||p_msg_name
2605 ,p_level => C_LEVEL_ERROR
2606 ,p_module => l_log_module);
2607 END IF;
2608 END LOOP;
2609
2610 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2611 trace
2612 (p_msg => 'END of build_events_message'
2613 ,p_level => C_LEVEL_PROCEDURE
2614 ,p_module => l_log_module);
2615 END IF;
2616 --
2617 EXCEPTION
2618 WHEN xla_exceptions_pkg.application_exception THEN
2619 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2620 trace
2621 (p_msg => 'Error. = '||sqlerrm
2622 ,p_level => C_LEVEL_PROCEDURE
2623 ,p_module => l_log_module);
2624 END IF;
2625 RAISE;
2626 WHEN OTHERS THEN
2627 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2628 trace
2629 (p_msg => 'Error. = '||sqlerrm
2630 ,p_level => C_LEVEL_PROCEDURE
2631 ,p_module => l_log_module);
2632 END IF;
2633 xla_exceptions_pkg.raise_message
2634 (p_location => 'xla_ae_code_combination_pkg.build_events_message');
2635 END build_events_message;
2636
2637 /*-------------------------------------------------------------+
2638 | |
2639 | Private function |
2640 | |
2641 | get_ccid_errors |
2642 | |
2643 | get AOL error message for invalid ccids |
2644 | |
2645 +-------------------------------------------------------------*/
2646
2647 PROCEDURE get_ccid_errors
2648 IS
2649 l_ConcatKey VARCHAR2(2000); -- key flex concateneted value
2650 --
2651 l_SegmentArray FND_FLEX_EXT.SegmentArray;
2652 l_SegmentNumber PLS_INTEGER;
2653 l_message VARCHAR2(4000);
2654 l_Ccid NUMBER;
2658 IF g_log_enabled THEN
2655 l_log_module VARCHAR2(240);
2656 BEGIN
2657 --
2659 l_log_module := C_DEFAULT_MODULE||'.get_ccid_errors';
2660 END IF;
2661 --
2662 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2663
2664 trace
2665 (p_msg => 'BEGIN of get_ccid_errors'
2666 ,p_level => C_LEVEL_PROCEDURE
2667 ,p_module => l_log_module);
2668
2669 END IF;
2670 --
2671 FOR ccid_rec IN (
2672 SELECT DISTINCT
2673 temp.segment1 segment1,
2674 temp.segment2 segment2,
2675 temp.segment3 segment3,
2676 temp.segment4 segment4,
2677 temp.segment5 segment5,
2678 temp.segment6 segment6,
2679 temp.segment7 segment7,
2680 temp.segment8 segment8,
2681 temp.segment9 segment9,
2682 temp.segment10 segment10,
2683 temp.segment11 segment11,
2684 temp.segment12 segment12,
2685 temp.segment13 segment13,
2686 temp.segment14 segment14,
2687 temp.segment15 segment15,
2688 temp.segment16 segment16,
2689 temp.segment17 segment17,
2690 temp.segment18 segment18,
2691 temp.segment19 segment19,
2692 temp.segment20 segment20,
2693 temp.segment21 segment21,
2694 temp.segment22 segment22,
2695 temp.segment23 segment23,
2696 temp.segment24 segment24,
2697 temp.segment25 segment25,
2698 temp.segment26 segment26,
2699 temp.segment27 segment27,
2700 temp.segment28 segment28,
2701 temp.segment29 segment29,
2702 temp.segment30 segment30,
2703 temp.ccid_coa_id coa_id
2704 FROM xla_ae_lines_gt temp
2705 WHERE temp.code_combination_id = -1
2706 AND temp.code_combination_status_code = C_CREATED
2707 AND temp.balance_type_code <> 'X'
2708 )
2709 LOOP
2710
2711 l_SegmentArray := init_SegmentArray(
2712 p_segment1 => ccid_rec.segment1
2713 , p_segment2 => ccid_rec.segment2
2714 , p_segment3 => ccid_rec.segment3
2715 , p_segment4 => ccid_rec.segment4
2716 , p_segment5 => ccid_rec.segment5
2717 , p_segment6 => ccid_rec.segment6
2718 , p_segment7 => ccid_rec.segment7
2719 , p_segment8 => ccid_rec.segment8
2720 , p_segment9 => ccid_rec.segment9
2721 , p_segment10 => ccid_rec.segment10
2722 , p_segment11 => ccid_rec.segment11
2723 , p_segment12 => ccid_rec.segment12
2724 , p_segment13 => ccid_rec.segment13
2725 , p_segment14 => ccid_rec.segment14
2726 , p_segment15 => ccid_rec.segment15
2727 , p_segment16 => ccid_rec.segment16
2728 , p_segment17 => ccid_rec.segment17
2729 , p_segment18 => ccid_rec.segment18
2730 , p_segment19 => ccid_rec.segment19
2731 , p_segment20 => ccid_rec.segment20
2732 , p_segment21 => ccid_rec.segment21
2733 , p_segment22 => ccid_rec.segment22
2734 , p_segment23 => ccid_rec.segment23
2735 , p_segment24 => ccid_rec.segment24
2736 , p_segment25 => ccid_rec.segment25
2737 , p_segment26 => ccid_rec.segment26
2738 , p_segment27 => ccid_rec.segment27
2739 , p_segment28 => ccid_rec.segment28
2740 , p_segment29 => ccid_rec.segment29
2741 , p_segment30 => ccid_rec.segment30
2742 , p_flex_application_id => 101
2743 , p_application_short_name => 'SQLGL'
2744 , p_id_flex_code =>'GL#'
2745 , p_id_flex_num => ccid_rec.coa_id
2746 );
2747
2748 l_SegmentNumber := l_SegmentArray.COUNT;
2749
2750 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2751 trace
2752 (p_msg => '-> CALL FND_FLEX_EXT.get_combination_id API'
2753 ,p_level => C_LEVEL_STATEMENT
2754 ,p_module => l_log_module);
2755
2756 END IF;
2757
2758 IF FND_FLEX_EXT.get_combination_id(
2759 application_short_name => 'SQLGL',
2760 key_flex_code => 'GL#',
2761 structure_number => ccid_rec.coa_id,
2762 validation_date => sysdate,
2763 n_segments => l_SegmentNumber,
2764 segments => l_SegmentArray,
2768 --
2765 combination_id => l_Ccid) = FALSE
2766 THEN
2767
2769 -- get FND error message
2770 --
2771 l_message:= SUBSTR(FND_FLEX_EXT.get_message,1,2000);
2772 --
2773 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2774
2775 trace
2776 (p_msg => 'l_message = '||l_message
2777 ,p_level => C_LEVEL_STATEMENT
2778 ,p_module => l_log_module);
2779
2780 trace
2781 (p_msg => '-> CALL FND_FLEX_EXT.concatenate_segments API'
2782 ,p_level => C_LEVEL_STATEMENT
2783 ,p_module => l_log_module);
2784
2785 END IF;
2786 --
2787 -- Concatenates segments from segment array
2788 --
2789 l_ConcatKey := FND_FLEX_EXT.concatenate_segments(
2790 n_segments => l_SegmentNumber,
2791 segments => l_SegmentArray,
2792 delimiter => FND_FLEX_EXT.get_delimiter(
2793 application_short_name => 'SQLGL',
2794 key_flex_code => 'GL#',
2795 structure_number => ccid_rec.coa_id
2796 )
2797 );
2798 --
2799
2800 build_events_message(
2801 p_appli_s_name => 'XLA'
2802 , p_msg_name => 'XLA_AP_INVALID_AOL_CCID'
2803 , p_token_1 => 'ACCOUNT_VALUE'
2804 , p_value_1 => l_ConcatKey
2805 , p_token_2 => 'MESSAGE'
2806 , p_value_2 => l_message
2807 , p_segment1 => ccid_rec.segment1
2808 , p_segment2 => ccid_rec.segment2
2809 , p_segment3 => ccid_rec.segment3
2810 , p_segment4 => ccid_rec.segment4
2811 , p_segment5 => ccid_rec.segment5
2812 , p_segment6 => ccid_rec.segment6
2813 , p_segment7 => ccid_rec.segment7
2814 , p_segment8 => ccid_rec.segment8
2815 , p_segment9 => ccid_rec.segment9
2816 , p_segment10 => ccid_rec.segment10
2817 , p_segment11 => ccid_rec.segment11
2818 , p_segment12 => ccid_rec.segment12
2819 , p_segment13 => ccid_rec.segment13
2820 , p_segment14 => ccid_rec.segment14
2821 , p_segment15 => ccid_rec.segment15
2822 , p_segment16 => ccid_rec.segment16
2823 , p_segment17 => ccid_rec.segment17
2824 , p_segment18 => ccid_rec.segment18
2825 , p_segment19 => ccid_rec.segment19
2826 , p_segment20 => ccid_rec.segment20
2827 , p_segment21 => ccid_rec.segment21
2828 , p_segment22 => ccid_rec.segment22
2829 , p_segment23 => ccid_rec.segment23
2830 , p_segment24 => ccid_rec.segment24
2831 , p_segment25 => ccid_rec.segment25
2832 , p_segment26 => ccid_rec.segment26
2833 , p_segment27 => ccid_rec.segment27
2834 , p_segment28 => ccid_rec.segment28
2835 , p_segment29 => ccid_rec.segment29
2836 , p_segment30 => ccid_rec.segment30
2837 , p_chart_of_accounts_id => ccid_rec.coa_id
2838 );
2839
2840 END IF;
2841 END LOOP;
2842
2843 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2844 trace
2845 (p_msg => 'END of get_ccid_errors'
2846 ,p_level => C_LEVEL_PROCEDURE
2847 ,p_module => l_log_module);
2848 END IF;
2849 EXCEPTION
2850 WHEN xla_exceptions_pkg.application_exception THEN
2851 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2852 trace
2853 (p_msg => 'Error. = '||sqlerrm
2854 ,p_level => C_LEVEL_PROCEDURE
2855 ,p_module => l_log_module);
2856 END IF;
2857 RAISE;
2858 WHEN OTHERS THEN
2859 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2860 trace
2861 (p_msg => 'Error. = '||sqlerrm
2862 ,p_level => C_LEVEL_PROCEDURE
2863 ,p_module => l_log_module);
2864 END IF;
2865 xla_exceptions_pkg.raise_message
2866 (p_location => 'xla_ae_code_combination_pkg.get_ccid_errors');
2867 END get_ccid_errors;
2868
2869 /*-------------------------------------------------------------+
2870 | |
2871 | Public function |
2872 | |
2873 | GetCcid |
2874 | |
2875 | Call AOL routine to create the new ccid, when the ccid does |
2879 +-------------------------------------------------------------*/
2876 | not exist in the gl_code_combinations table. It calls the |
2877 | AOL API FND_FLEX_EXT.get_combination_id. |
2878 | |
2880
2881 FUNCTION GetCcid(
2882 p_segment1 IN VARCHAR2
2883 , p_segment2 IN VARCHAR2
2884 , p_segment3 IN VARCHAR2
2885 , p_segment4 IN VARCHAR2
2886 , p_segment5 IN VARCHAR2
2887 , p_segment6 IN VARCHAR2
2888 , p_segment7 IN VARCHAR2
2889 , p_segment8 IN VARCHAR2
2890 , p_segment9 IN VARCHAR2
2891 , p_segment10 IN VARCHAR2
2892 , p_segment11 IN VARCHAR2
2893 , p_segment12 IN VARCHAR2
2894 , p_segment13 IN VARCHAR2
2895 , p_segment14 IN VARCHAR2
2896 , p_segment15 IN VARCHAR2
2897 , p_segment16 IN VARCHAR2
2898 , p_segment17 IN VARCHAR2
2899 , p_segment18 IN VARCHAR2
2900 , p_segment19 IN VARCHAR2
2901 , p_segment20 IN VARCHAR2
2902 , p_segment21 IN VARCHAR2
2903 , p_segment22 IN VARCHAR2
2904 , p_segment23 IN VARCHAR2
2905 , p_segment24 IN VARCHAR2
2906 , p_segment25 IN VARCHAR2
2907 , p_segment26 IN VARCHAR2
2908 , p_segment27 IN VARCHAR2
2909 , p_segment28 IN VARCHAR2
2910 , p_segment29 IN VARCHAR2
2911 , p_segment30 IN VARCHAR2
2912 , p_chart_of_accounts_id IN NUMBER
2913 )
2914 RETURN NUMBER
2915 IS
2916 l_ConcatKey VARCHAR2(4000); -- key flex concateneted value
2917 --
2918 l_SegmentArray FND_FLEX_EXT.SegmentArray;
2919 l_SegmentNumber PLS_INTEGER;
2920 l_Ccid NUMBER;
2921 l_sql_stmt VARCHAR2(10000);
2922 l_message VARCHAR2(4000);
2923 l_log_module VARCHAR2(240);
2924 BEGIN
2925 --
2926 IF g_log_enabled THEN
2927 l_log_module := C_DEFAULT_MODULE||'.GetCcid';
2928 END IF;
2929 --
2930
2931 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2932
2933 trace
2934 (p_msg => 'BEGIN of GetCcid'
2935 ,p_level => C_LEVEL_PROCEDURE
2936 ,p_module => l_log_module);
2937
2938 END IF;
2939 --
2940 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2941
2942 trace
2943 (p_msg => 'p_chart_of_accounts_id = '||p_chart_of_accounts_id
2944 ,p_level => C_LEVEL_STATEMENT
2945 ,p_module => l_log_module);
2946 trace
2947 (p_msg => 'p_segment1 = '||p_segment1 ||
2948 '- p_segment2 = '||p_segment2 ||
2949 '- p_segment3 = '||p_segment3 ||
2950 '- p_segment4 = '||p_segment4 ||
2951 '- p_segment5 = '||p_segment5 ||
2952 '- p_segment6 = '||p_segment6 ||
2953 '- p_segment7 = '||p_segment7 ||
2954 '- p_segment8 = '||p_segment8 ||
2955 '- p_segment9 = '||p_segment9 ||
2956 '- p_segment10 = '||p_segment10
2957 ,p_level => C_LEVEL_STATEMENT
2958 ,p_module => l_log_module);
2959
2960 trace
2961 (p_msg => 'p_segment11 = '||p_segment11 ||
2962 '- p_segment12 = '||p_segment12 ||
2963 '- p_segment13 = '||p_segment13 ||
2964 '- p_segment14 = '||p_segment14 ||
2965 '- p_segment15 = '||p_segment15 ||
2966 '- p_segment16 = '||p_segment16 ||
2967 '- p_segment17 = '||p_segment17 ||
2968 '- p_segment18 = '||p_segment18 ||
2969 '- p_segment19 = '||p_segment19 ||
2970 '- p_segment20 = '||p_segment20
2971 ,p_level => C_LEVEL_STATEMENT
2972 ,p_module => l_log_module);
2973 trace
2974 (p_msg => 'p_segment21 = '||p_segment21 ||
2975 '- p_segment22 = '||p_segment22 ||
2976 '- p_segment23 = '||p_segment23 ||
2977 '- p_segment24 = '||p_segment24 ||
2978 '- p_segment25 = '||p_segment25 ||
2979 '- p_segment26 = '||p_segment26 ||
2980 '- p_segment27 = '||p_segment27 ||
2981 '- p_segment28 = '||p_segment28 ||
2982 '- p_segment29 = '||p_segment29 ||
2983 '- p_segment30 = '||p_segment30
2984 ,p_level => C_LEVEL_STATEMENT
2985 ,p_module => l_log_module);
2986
2987 END IF;
2988 l_SegmentArray := init_SegmentArray(
2989 p_segment1 => p_segment1
2990 , p_segment2 => p_segment2
2991 , p_segment3 => p_segment3
2992 , p_segment4 => p_segment4
2993 , p_segment5 => p_segment5
2994 , p_segment6 => p_segment6
2995 , p_segment7 => p_segment7
2999 , p_segment11 => p_segment11
2996 , p_segment8 => p_segment8
2997 , p_segment9 => p_segment9
2998 , p_segment10 => p_segment10
3000 , p_segment12 => p_segment12
3001 , p_segment13 => p_segment13
3002 , p_segment14 => p_segment14
3003 , p_segment15 => p_segment15
3004 , p_segment16 => p_segment16
3005 , p_segment17 => p_segment17
3006 , p_segment18 => p_segment18
3007 , p_segment19 => p_segment19
3008 , p_segment20 => p_segment20
3009 , p_segment21 => p_segment21
3010 , p_segment22 => p_segment22
3011 , p_segment23 => p_segment23
3012 , p_segment24 => p_segment24
3013 , p_segment25 => p_segment25
3014 , p_segment26 => p_segment26
3015 , p_segment27 => p_segment27
3016 , p_segment28 => p_segment28
3017 , p_segment29 => p_segment29
3018 , p_segment30 => p_segment30
3019 , p_flex_application_id => 101
3020 , p_application_short_name => 'SQLGL'
3021 , p_id_flex_code =>'GL#'
3022 , p_id_flex_num => p_chart_of_accounts_id
3023 );
3024
3025 l_SegmentNumber := l_SegmentArray.COUNT;
3026
3027
3028 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3029
3030 trace
3031 (p_msg => '-> CALL FND_FLEX_EXT.get_combination_id API'
3032 ,p_level => C_LEVEL_STATEMENT
3033 ,p_module => l_log_module);
3034
3035 END IF;
3036
3037 IF FND_FLEX_EXT.get_combination_id(
3038 application_short_name => 'SQLGL',
3039 key_flex_code => 'GL#',
3040 structure_number => p_chart_of_accounts_id,
3041 validation_date => sysdate,
3042 n_segments => l_SegmentNumber,
3043 segments => l_SegmentArray,
3044 combination_id => l_Ccid) = FALSE
3045 THEN
3046
3047 g_error_exists := TRUE;
3048 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
3049
3050 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3051
3052 trace
3053 (p_msg => 'return value. = -1'
3054 ,p_level => C_LEVEL_PROCEDURE
3055 ,p_module => l_log_module);
3056
3057 trace
3058 (p_msg => 'END of functionGetCcid'
3059 ,p_level => C_LEVEL_PROCEDURE
3060 ,p_module => l_log_module);
3061
3062 END IF;
3063 RETURN -1;
3064
3065 ELSE
3066
3067 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3068 trace
3069 (p_msg => 'return value. = '||l_Ccid
3070 ,p_level => C_LEVEL_PROCEDURE
3071 ,p_module => l_log_module);
3072 trace
3073 (p_msg => 'END of GetCcid'
3074 ,p_level => C_LEVEL_PROCEDURE
3075 ,p_module => l_log_module);
3076
3077 END IF;
3078
3079 RETURN l_Ccid;
3080 END IF;
3081 EXCEPTION
3082 WHEN xla_exceptions_pkg.application_exception THEN
3083 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3084 trace
3085 (p_msg => 'Error. = '||sqlerrm
3086 ,p_level => C_LEVEL_PROCEDURE
3087 ,p_module => l_log_module);
3088 END IF;
3089 RAISE;
3090 WHEN OTHERS THEN
3091 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3092 trace
3093 (p_msg => 'Error. = '||sqlerrm
3094 ,p_level => C_LEVEL_PROCEDURE
3095 ,p_module => l_log_module);
3096 END IF;
3097 xla_exceptions_pkg.raise_message
3098 (p_location => 'xla_ae_code_combination_pkg.GetCcid');
3099 END GetCcid;
3100
3101 /*------------------------------------------------------------------+
3102 | |
3103 | Private function |
3104 | |
3105 | validate_source_ccid |
3106 | |
3107 | This function validates the code combination identifiers |
3108 | passed to ccounting engine through the extract (by the product). |
3109 | It returns the number of rows updated |
3110 | |
3111 +------------------------------------------------------------------*/
3112
3113 FUNCTION validate_source_ccid
3114 RETURN NUMBER
3115 IS
3116 l_log_module VARCHAR2(240);
3117 l_rowcount NUMBER;
3118 l_count NUMBER;
3119 BEGIN
3120 IF g_log_enabled THEN
3124 trace
3121 l_log_module := C_DEFAULT_MODULE||'.validate_source_ccid';
3122 END IF;
3123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3125 (p_msg => 'BEGIN of validate_source_ccid'
3126 ,p_level => C_LEVEL_PROCEDURE
3127 ,p_module => l_log_module);
3128
3129 END IF;
3130
3131 l_count := 0;
3132 l_rowcount := 0;
3133
3134 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3135 trace
3136 (p_msg => 'Validate the accounting ccids: SQL - Update xla_ae_lines_gt '
3137 ,p_level => C_LEVEL_STATEMENT
3138 ,p_module => l_log_module);
3139 END IF;
3140
3141 UPDATE xla_ae_lines_gt temp
3142 SET code_combination_status_code =
3143 CASE
3144 WHEN temp.code_combination_id IS NOT NULL
3145 AND temp.code_combination_status_code = C_NOT_PROCESSED
3146 AND temp.code_combination_id <> -1
3147 AND temp.balance_type_code <> 'X'
3148 AND NOT EXISTS
3149 (SELECT 'x'
3150 FROM gl_code_combinations gl
3151 WHERE gl.code_combination_id = temp.code_combination_id
3152 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3153 AND gl.template_id IS NULL)
3154 THEN C_INVALID
3155 ELSE code_combination_status_code
3156 END
3157 ,alt_ccid_status_code =
3158 CASE
3159 WHEN temp.alt_code_combination_id IS NOT NULL
3160 AND temp.alt_ccid_status_code = C_NOT_PROCESSED
3161 AND temp.alt_code_combination_id <> -1
3162 AND temp.balance_type_code <> 'X'
3163 AND NOT EXISTS
3164 (SELECT 'x'
3165 FROM gl_code_combinations gl
3166 WHERE gl.code_combination_id = temp.alt_code_combination_id
3167 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3168 AND gl.template_id IS NULL)
3169 THEN C_INVALID
3170 ELSE alt_ccid_status_code
3171 END
3172 WHERE
3173 (temp.code_combination_id IS NOT NULL
3174 AND temp.code_combination_status_code = C_NOT_PROCESSED
3175 AND temp.code_combination_id <> -1
3176 AND temp.balance_type_code <> 'X'
3177 AND NOT EXISTS (SELECT 'x'
3178 FROM gl_code_combinations gl
3179 WHERE gl.code_combination_id = temp.code_combination_id
3180 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3181 AND gl.template_id IS NULL
3182 ))
3183 OR
3184 (temp.alt_code_combination_id IS NOT NULL
3185 AND temp.alt_ccid_status_code = C_NOT_PROCESSED
3186 AND temp.alt_code_combination_id <> -1
3187 AND temp.balance_type_code <> 'X'
3188 AND NOT EXISTS (SELECT 'x'
3189 FROM gl_code_combinations gl
3190 WHERE gl.code_combination_id = temp.alt_code_combination_id
3191 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3192 AND gl.template_id IS NULL
3193 ))
3194 ;
3195
3196 l_rowcount := SQL%ROWCOUNT;
3197 l_count:= l_count + l_rowcount;
3198
3199 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3200 trace
3201 (p_msg => '# rows Updated in xla_ae_lines_gt (ccid + ALT ccid) ='||l_rowcount
3202 ,p_level => C_LEVEL_STATEMENT
3203 ,p_module => l_log_module);
3204 END IF;
3205
3206
3207 --logging the errors
3208 IF ( l_count> 0 ) THEN
3209
3210 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
3211
3212 FOR error_rec IN (
3213 SELECT event_id
3214 ,entity_id
3215 ,ledger_id
3216 ,ae_header_id
3217 ,ccid
3218 FROM (
3219 --accounting ccid
3220 SELECT DISTINCT
3221 hdr.event_id event_id,
3222 hdr.entity_id entity_id,
3223 hdr.ledger_id ledger_id,
3224 hdr.ae_header_id ae_header_id,
3225 lns.code_combination_id ccid
3226 FROM xla_ae_lines_gt lns,
3227 xla_ae_headers_gt hdr
3228 WHERE lns.ae_header_id = hdr.ae_header_id
3229 AND lns.code_combination_id <> -1
3230 AND lns.code_combination_status_code = C_INVALID
3231 AND lns.balance_type_code <> 'X'
3232 AND hdr.entity_id IS NOT NULL
3233 AND hdr.event_id IS NOT NULL
3234 AND hdr.ledger_id IS NOT NULL
3235
3236 UNION
3237 --accounting ALT ccid
3238 SELECT DISTINCT
3239 hdr.event_id event_id,
3240 hdr.entity_id entity_id,
3241 hdr.ledger_id ledger_id,
3242 hdr.ae_header_id ae_header_id,
3243 lns.alt_code_combination_id ccid
3244 FROM xla_ae_lines_gt lns,
3245 xla_ae_headers_gt hdr
3246 WHERE lns.ae_header_id = hdr.ae_header_id
3250 AND hdr.entity_id IS NOT NULL
3247 AND lns.alt_code_combination_id <> -1
3248 AND lns.alt_ccid_status_code = C_INVALID
3249 AND lns.balance_type_code <> 'X'
3251 AND hdr.event_id IS NOT NULL
3252 AND hdr.ledger_id IS NOT NULL
3253 )
3254 )
3255 LOOP
3256 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
3257 xla_accounting_err_pkg.build_message
3258 (p_appli_s_name => 'XLA'
3259 ,p_msg_name => 'XLA_AP_CCID_NOT_EXISTS'
3260 ,p_token_1 => 'CODE_COMBINATION_ID'
3261 ,p_value_1 => error_rec.ccid
3262 ,p_entity_id => error_rec.entity_id
3263 ,p_event_id => error_rec.event_id
3264 ,p_ledger_id => error_rec.ledger_id
3265 ,p_ae_header_id => error_rec.ae_header_id
3266 );
3267
3268 IF (C_LEVEL_ERROR >= g_log_level) THEN
3269
3270 trace
3271 (p_msg => 'ERROR: XLA_AP_CCID_NOT_EXISTS = '||TO_CHAR(error_rec.ccid)
3272 ,p_level => C_LEVEL_ERROR
3273 ,p_module => l_log_module);
3274
3275 END IF;
3276
3277 END LOOP;
3278 END IF;
3279 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3280 trace
3281 (p_msg => 'END of validate_source_ccid'
3282 ,p_level => C_LEVEL_PROCEDURE
3283 ,p_module => l_log_module);
3284 END IF;
3285 RETURN l_count;
3286 EXCEPTION
3287 WHEN xla_exceptions_pkg.application_exception THEN
3288 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3289 trace
3290 (p_msg => 'Error. = '||sqlerrm
3291 ,p_level => C_LEVEL_PROCEDURE
3292 ,p_module => l_log_module);
3293 END IF;
3294 RAISE;
3295 WHEN OTHERS THEN
3296 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3297 trace
3298 (p_msg => 'Error. = '||sqlerrm
3299 ,p_level => C_LEVEL_PROCEDURE
3300 ,p_module => l_log_module);
3301 END IF;
3302 xla_exceptions_pkg.raise_message
3303 (p_location => 'xla_ae_code_combination_pkg.validate_source_ccid');
3304 END validate_source_ccid;
3305
3306 /*------------------------------------------------------------------+
3307 | |
3308 | Private function |
3309 | |
3310 | override_ccid |
3311 | |
3312 | Override accounting ccid segments. It returns the number of rows |
3313 | updated. |
3314 | |
3315 +------------------------------------------------------------------*/
3316
3317 FUNCTION override_ccid
3318 RETURN NUMBER
3319 IS
3320 l_log_module VARCHAR2(240);
3321 l_rowcount NUMBER;
3322 l_return NUMBER;
3323 BEGIN
3324 IF g_log_enabled THEN
3325 l_log_module := C_DEFAULT_MODULE||'.override_ccid';
3326 END IF;
3327 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3328 trace
3329 (p_msg => 'BEGIN of override_ccid'
3330 ,p_level => C_LEVEL_PROCEDURE
3331 ,p_module => l_log_module);
3332 END IF;
3333
3334 l_return := 0;
3335 l_rowcount := 0;
3336
3337 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3338 trace
3339 (p_msg => 'Override the accounting ccid: SQL - Update xla_ae_lines_gt '
3340 ,p_level => C_LEVEL_STATEMENT
3341 ,p_module => l_log_module);
3342 END IF;
3343
3344 UPDATE xla_ae_lines_gt temp
3345 SET
3346 ( segment1
3347 , segment2
3348 , segment3
3349 , segment4
3350 , segment5
3351 , segment6
3352 , segment7
3353 , segment8
3354 , segment9
3355 , segment10
3356 , segment11
3357 , segment12
3358 , segment13
3359 , segment14
3360 , segment15
3361 , segment16
3362 , segment17
3363 , segment18
3364 , segment19
3365 , segment20
3366 , segment21
3367 , segment22
3368 , segment23
3369 , segment24
3370 , segment25
3371 , segment26
3372 , segment27
3373 , segment28
3374 , segment29
3375 , segment30
3376 , code_combination_status_code
3377 )
3378 = (
3379 SELECT
3380 nvl(temp.segment1 , gl.segment1)
3381 , nvl(temp.segment2 , gl.segment2)
3382 , nvl(temp.segment3 , gl.segment3)
3383 , nvl(temp.segment4 , gl.segment4)
3384 , nvl(temp.segment5 , gl.segment5)
3388 , nvl(temp.segment9 , gl.segment9)
3385 , nvl(temp.segment6 , gl.segment6)
3386 , nvl(temp.segment7 , gl.segment7)
3387 , nvl(temp.segment8 , gl.segment8)
3389 , nvl(temp.segment10 , gl.segment10)
3390 , nvl(temp.segment11 , gl.segment11)
3391 , nvl(temp.segment12 , gl.segment12)
3392 , nvl(temp.segment13 , gl.segment13)
3393 , nvl(temp.segment14 , gl.segment14)
3394 , nvl(temp.segment15 , gl.segment15)
3395 , nvl(temp.segment16 , gl.segment16)
3396 , nvl(temp.segment17 , gl.segment17)
3397 , nvl(temp.segment18 , gl.segment18)
3398 , nvl(temp.segment19 , gl.segment19)
3399 , nvl(temp.segment20 , gl.segment20)
3400 , nvl(temp.segment21 , gl.segment21)
3401 , nvl(temp.segment22 , gl.segment22)
3402 , nvl(temp.segment23 , gl.segment23)
3403 , nvl(temp.segment24 , gl.segment24)
3404 , nvl(temp.segment25 , gl.segment25)
3405 , nvl(temp.segment26 , gl.segment26)
3406 , nvl(temp.segment27 , gl.segment27)
3407 , nvl(temp.segment28 , gl.segment28)
3408 , nvl(temp.segment29 , gl.segment29)
3409 , nvl(temp.segment30 , gl.segment30)
3410 , C_PROCESSING
3411 FROM gl_code_combinations gl
3412 WHERE gl.code_combination_id = temp.code_combination_id
3413 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3414 AND gl.template_id IS NULL
3415 )
3416 WHERE temp.code_combination_id IS NOT NULL
3417 AND temp.code_combination_status_code = C_NOT_PROCESSED
3418 AND temp.code_combination_id <> -1
3419 ;
3420
3421 l_rowcount := SQL%ROWCOUNT;
3422 l_return := l_return + l_rowcount;
3423
3424
3425 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3426 trace
3427 (p_msg => '# rows updates = '||TO_CHAR(l_rowcount)
3428 ,p_level => C_LEVEL_STATEMENT
3429 ,p_module => l_log_module);
3430
3431 trace
3432 (p_msg => 'Override the accounting ALT ccid: SQL - Update xla_ae_lines_gt '
3433 ,p_level => C_LEVEL_STATEMENT
3434 ,p_module => l_log_module);
3435
3436 END IF;
3437
3438
3439 UPDATE xla_ae_lines_gt temp
3440 SET
3441 ( alt_segment1
3442 , alt_segment2
3443 , alt_segment3
3444 , alt_segment4
3445 , alt_segment5
3446 , alt_segment6
3447 , alt_segment7
3448 , alt_segment8
3449 , alt_segment9
3450 , alt_segment10
3451 , alt_segment11
3452 , alt_segment12
3453 , alt_segment13
3454 , alt_segment14
3455 , alt_segment15
3456 , alt_segment16
3457 , alt_segment17
3458 , alt_segment18
3459 , alt_segment19
3460 , alt_segment20
3461 , alt_segment21
3462 , alt_segment22
3463 , alt_segment23
3464 , alt_segment24
3465 , alt_segment25
3466 , alt_segment26
3467 , alt_segment27
3468 , alt_segment28
3469 , alt_segment29
3470 , alt_segment30
3471 , alt_ccid_status_code
3472 )
3473 = (
3474 SELECT
3475 nvl(temp.alt_segment1 , gl.segment1)
3476 , nvl(temp.alt_segment2 , gl.segment2)
3477 , nvl(temp.alt_segment3 , gl.segment3)
3478 , nvl(temp.alt_segment4 , gl.segment4)
3479 , nvl(temp.alt_segment5 , gl.segment5)
3480 , nvl(temp.alt_segment6 , gl.segment6)
3481 , nvl(temp.alt_segment7 , gl.segment7)
3482 , nvl(temp.alt_segment8 , gl.segment8)
3483 , nvl(temp.alt_segment9 , gl.segment9)
3484 , nvl(temp.alt_segment10 , gl.segment10)
3485 , nvl(temp.alt_segment11 , gl.segment11)
3486 , nvl(temp.alt_segment12 , gl.segment12)
3487 , nvl(temp.alt_segment13 , gl.segment13)
3488 , nvl(temp.alt_segment14 , gl.segment14)
3489 , nvl(temp.alt_segment15 , gl.segment15)
3490 , nvl(temp.alt_segment16 , gl.segment16)
3491 , nvl(temp.alt_segment17 , gl.segment17)
3492 , nvl(temp.alt_segment18 , gl.segment18)
3493 , nvl(temp.alt_segment19 , gl.segment19)
3494 , nvl(temp.alt_segment20 , gl.segment20)
3495 , nvl(temp.alt_segment21 , gl.segment21)
3496 , nvl(temp.alt_segment22 , gl.segment22)
3497 , nvl(temp.alt_segment23 , gl.segment23)
3498 , nvl(temp.alt_segment24 , gl.segment24)
3499 , nvl(temp.alt_segment25 , gl.segment25)
3500 , nvl(temp.alt_segment26 , gl.segment26)
3501 , nvl(temp.alt_segment27 , gl.segment27)
3502 , nvl(temp.alt_segment28 , gl.segment28)
3503 , nvl(temp.alt_segment29 , gl.segment29)
3504 , nvl(temp.alt_segment30 , gl.segment30)
3505 , C_PROCESSING
3506 FROM gl_code_combinations gl
3507 WHERE gl.code_combination_id = temp.alt_code_combination_id
3508 AND gl.chart_of_accounts_id = temp.ccid_coa_id
3509 AND gl.template_id IS NULL
3510 )
3511 WHERE temp.alt_code_combination_id IS NOT NULL
3512 AND temp.alt_ccid_status_code = C_NOT_PROCESSED
3513 AND temp.alt_code_combination_id <> -1
3514 ;
3515
3516 l_rowcount := SQL%ROWCOUNT;
3520 trace
3517 l_return := l_return + l_rowcount;
3518
3519 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3521 (p_msg => '# rows updates = '||TO_CHAR(l_rowcount)||
3522 ' - return value. = '||to_char(l_return)
3523 ,p_level => C_LEVEL_STATEMENT
3524 ,p_module => l_log_module);
3525 trace
3526 (p_msg => 'END of override_ccid'
3527 ,p_level => C_LEVEL_PROCEDURE
3528 ,p_module => l_log_module);
3529 END IF;
3530 RETURN l_return;
3531 EXCEPTION
3532 WHEN xla_exceptions_pkg.application_exception THEN
3533 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3534 trace
3535 (p_msg => 'Error. = '||sqlerrm
3536 ,p_level => C_LEVEL_PROCEDURE
3537 ,p_module => l_log_module);
3538 END IF;
3539 RAISE;
3540 WHEN OTHERS THEN
3541 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3542 trace
3543 (p_msg => 'Error. = '||sqlerrm
3544 ,p_level => C_LEVEL_PROCEDURE
3545 ,p_module => l_log_module);
3546 END IF;
3547 xla_exceptions_pkg.raise_message
3548 (p_location => 'xla_ae_code_combination_pkg.override_ccid');
3549 END override_ccid;
3550
3551 /*-----------------------------------------------------------------------+
3552 | |
3553 | Private function |
3554 | |
3555 | create_ccid |
3556 | |
3557 | retrieves new accounting ccids and ALT ccids from gl_code_combinations |
3558 | gl_code_combinations table. It returns the number of rows updated |
3559 | |
3560 +-----------------------------------------------------------------------*/
3561
3562 FUNCTION create_ccid
3563 RETURN NUMBER
3564 IS
3565 l_upd_stmt VARCHAR2(20000);
3566 l_sql_stmt VARCHAR2(20000);
3567 l_alt_sql_stmt VARCHAR2(20000);
3568 l_position NUMBER;
3569 l_id_flex_num NUMBER;
3570 l_rowcount NUMBER;
3571 l_count NUMBER;
3572 l_log_module VARCHAR2(240);
3573
3574 BEGIN
3575 IF g_log_enabled THEN
3576 l_log_module := C_DEFAULT_MODULE||'.create_ccid';
3577 END IF;
3578 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3579 trace
3580 (p_msg => 'BEGIN of create_ccid'
3581 ,p_level => C_LEVEL_PROCEDURE
3582 ,p_module => l_log_module);
3583 END IF;
3584
3585 l_count := 0;
3586 l_rowcount := 0;
3587
3588 IF g_array_cache_target_coa.COUNT > 0 THEN
3589
3590 FOR Idx IN g_array_cache_target_coa.FIRST .. g_array_cache_target_coa.LAST LOOP
3591
3592 IF g_array_cache_target_coa.EXISTS(Idx) AND g_array_cache_target_coa(Idx) IS NOT NULL THEN
3593
3594 l_position:= DBMS_UTILITY.get_hash_value(
3595 TO_CHAR(g_array_cache_target_coa(Idx))||
3596 TO_CHAR(101)||
3597 'GL#',1,1073741824);
3598
3599 IF NOT g_array_key_flexfield.EXISTS(l_position) THEN
3600
3601 l_id_flex_num := g_array_cache_target_coa(Idx);
3602
3603 cache_key_flexfield(
3604 p_flex_application_id => 101,
3605 p_application_short_name => 'SQLGL',
3606 p_id_flex_code => 'GL#',
3607 p_id_flex_num => l_id_flex_num
3608 );
3609
3610 END IF;
3611
3612 --
3613 -- Initialize when coa id is switched.
3614 --
3615 l_sql_stmt := NULL;
3616 l_alt_sql_stmt := NULL;
3617
3618 FOR Jdx IN g_array_key_flexfield(l_position).segment_num.first ..
3619 g_array_key_flexfield(l_position).segment_num.last LOOP
3620
3621 IF g_array_key_flexfield(l_position).segment_num.EXISTS(Jdx) THEN
3622
3623 CASE g_array_key_flexfield(l_position).segment_num(Jdx)
3624
3625 WHEN 'SEGMENT1' THEN
3626 l_sql_stmt := l_sql_stmt || ' AND glc.segment1 = temp.segment1' ;
3627 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment1 = temp.alt_segment1' ;
3628 WHEN 'SEGMENT2' THEN
3629 l_sql_stmt := l_sql_stmt || ' AND glc.segment2 = temp.segment2 ' ;
3630 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment2 = temp.alt_segment2 ' ;
3631 WHEN 'SEGMENT3' THEN
3632 l_sql_stmt := l_sql_stmt || ' AND glc.segment3 = temp.segment3 ' ;
3633 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment3 = temp.alt_segment3 ' ;
3634 WHEN 'SEGMENT4' THEN
3635 l_sql_stmt := l_sql_stmt || ' AND glc.segment4 = temp.segment4 ' ;
3636 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment4 = temp.alt_segment4 ' ;
3637 WHEN 'SEGMENT5' THEN
3638 l_sql_stmt := l_sql_stmt || ' AND glc.segment5 = temp.segment5 ' ;
3639 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment5 = temp.alt_segment5 ' ;
3640 WHEN 'SEGMENT6' THEN
3644 l_sql_stmt := l_sql_stmt || ' AND glc.segment7 = temp.segment7 ' ;
3641 l_sql_stmt := l_sql_stmt || ' AND glc.segment6 = temp.segment6 ' ;
3642 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment6 = temp.alt_segment6 ' ;
3643 WHEN 'SEGMENT7' THEN
3645 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment7 = temp.alt_segment7 ' ;
3646 WHEN 'SEGMENT8' THEN
3647 l_sql_stmt := l_sql_stmt || ' AND glc.segment8 = temp.segment8 ' ;
3648 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment8 = temp.alt_segment8 ' ;
3649 WHEN 'SEGMENT9' THEN
3650 l_sql_stmt := l_sql_stmt || ' AND glc.segment9 = temp.segment9 ' ;
3651 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment9 = temp.alt_segment9 ' ;
3652 WHEN 'SEGMENT10' THEN
3653 l_sql_stmt := l_sql_stmt || ' AND glc.segment10 = temp.segment10 ' ;
3654 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment10 = temp.alt_segment10 ' ;
3655 WHEN 'SEGMENT11' THEN
3656 l_sql_stmt := l_sql_stmt || ' AND glc.segment11 = temp.segment11 ' ;
3657 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment11 = temp.alt_segment11 ' ;
3658 WHEN 'SEGMENT12' THEN
3659 l_sql_stmt := l_sql_stmt || ' AND glc.segment12 = temp.segment12 ' ;
3660 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment12 = temp.alt_segment12 ' ;
3661 WHEN 'SEGMENT13' THEN
3662 l_sql_stmt := l_sql_stmt || ' AND glc.segment13 = temp.segment13 ' ;
3663 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment13 = temp.alt_segment13 ' ;
3664 WHEN 'SEGMENT14' THEN
3665 l_sql_stmt := l_sql_stmt || ' AND glc.segment14 = temp.segment14 ' ;
3666 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment14 = temp.alt_segment14 ' ;
3667 WHEN 'SEGMENT15' THEN
3668 l_sql_stmt := l_sql_stmt || ' AND glc.segment15 = temp.segment15 ' ;
3669 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment15 = temp.alt_segment15 ' ;
3670 WHEN 'SEGMENT16' THEN
3671 l_sql_stmt := l_sql_stmt || ' AND glc.segment16 = temp.segment16 ' ;
3672 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment16 = temp.alt_segment16 ' ;
3673 WHEN 'SEGMENT17' THEN
3674 l_sql_stmt := l_sql_stmt || ' AND glc.segment17 = temp.segment17 ' ;
3675 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment17 = temp.alt_segment17 ' ;
3676 WHEN 'SEGMENT18' THEN
3677 l_sql_stmt := l_sql_stmt || ' AND glc.segment18 = temp.segment18 ' ;
3678 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment18 = temp.alt_segment18 ' ;
3679 WHEN 'SEGMENT19' THEN
3680 l_sql_stmt := l_sql_stmt || ' AND glc.segment19 = temp.segment19 ' ;
3681 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment19 = temp.alt_segment19 ' ;
3682 WHEN 'SEGMENT20' THEN
3683 l_sql_stmt := l_sql_stmt || ' AND glc.segment20 = temp.segment20 ' ;
3684 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment20 = temp.alt_segment20 ' ;
3685 WHEN 'SEGMENT21' THEN
3686 l_sql_stmt := l_sql_stmt || ' AND glc.segment21 = temp.segment21 ' ;
3687 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment21 = temp.alt_segment21 ' ;
3688 WHEN 'SEGMENT22' THEN
3689 l_sql_stmt := l_sql_stmt || ' AND glc.segment22 = temp.segment22 ' ;
3690 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment22 = temp.alt_segment22 ' ;
3691 WHEN 'SEGMENT23' THEN
3692 l_sql_stmt := l_sql_stmt || ' AND glc.segment23 = temp.segment23 ' ;
3693 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment23 = temp.alt_segment23 ' ;
3694 WHEN 'SEGMENT24' THEN
3695 l_sql_stmt := l_sql_stmt || ' AND glc.segment24 = temp.segment24 ' ;
3696 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment24 = temp.alt_segment24 ' ;
3697 WHEN 'SEGMENT25' THEN
3698 l_sql_stmt := l_sql_stmt || ' AND glc.segment25 = temp.segment25 ' ;
3699 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment25 = temp.alt_segment25 ' ;
3700 WHEN 'SEGMENT26' THEN
3701 l_sql_stmt := l_sql_stmt || ' AND glc.segment26 = temp.segment26 ' ;
3702 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment26 = temp.alt_segment26 ' ;
3703 WHEN 'SEGMENT27' THEN
3704 l_sql_stmt := l_sql_stmt || ' AND glc.segment27 = temp.segment27 ' ;
3705 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment27 = temp.alt_segment27 ' ;
3706 WHEN 'SEGMENT28' THEN
3707 l_sql_stmt := l_sql_stmt || ' AND glc.segment28 = temp.segment28 ' ;
3708 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment28 = temp.alt_segment28 ' ;
3709 WHEN 'SEGMENT29' THEN
3710 l_sql_stmt := l_sql_stmt || ' AND glc.segment29 = temp.segment29 ' ;
3711 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment29 = temp.alt_segment29 ' ;
3712 WHEN 'SEGMENT30' THEN
3713 l_sql_stmt := l_sql_stmt || ' AND glc.segment30 = temp.segment30 ' ;
3714 l_alt_sql_stmt := l_alt_sql_stmt || ' AND glc.segment30 = temp.alt_segment30 ' ;
3715
3719
3716 ELSE null;
3717
3718 END CASE;
3720 END IF;
3721 --
3722 END LOOP;
3723
3724 l_upd_stmt := 'UPDATE xla_ae_lines_gt temp
3725 SET code_combination_id =
3726 CASE
3727 WHEN temp.ccid_coa_id = :1
3728 AND temp.code_combination_status_code = :2
3729 AND temp.balance_type_code <> ''X''
3730 THEN
3731 (SELECT glc.code_combination_id
3732 FROM gl_code_combinations glc
3733 WHERE glc.chart_of_accounts_id = temp.ccid_coa_id
3734 AND temp.ccid_coa_id = :3
3735 AND glc.template_id IS NULL
3736 ' || l_sql_stmt || ' )
3737 ELSE
3738 code_combination_id
3739 END
3740 ,code_combination_status_code =
3741 CASE
3742 WHEN temp.ccid_coa_id = :4
3743 AND temp.code_combination_status_code = :5
3744 AND temp.balance_type_code <> ''X''
3745 THEN :6
3746 ELSE
3747 code_combination_status_code
3748 END
3749 ,alt_code_combination_id =
3750 CASE
3751 WHEN temp.ccid_coa_id = :7
3752 AND temp.alt_ccid_status_code = :8
3753 AND temp.balance_type_code <> ''X''
3754 THEN
3755 (SELECT glc.code_combination_id
3756 FROM gl_code_combinations glc
3757 WHERE glc.chart_of_accounts_id = temp.ccid_coa_id
3758 AND temp.ccid_coa_id = :9
3759 AND glc.template_id IS NULL
3760 ' || l_alt_sql_stmt || ' )
3761 ELSE
3762 alt_code_combination_id
3763 END
3764 ,alt_ccid_status_code =
3765 CASE
3766 WHEN temp.ccid_coa_id = :10
3767 AND temp.alt_ccid_status_code = :11
3768 AND temp.balance_type_code <> ''X''
3769 THEN :12
3770 ELSE
3771 alt_ccid_status_code
3772 END
3773 WHERE temp.ccid_coa_id = :13
3774 AND temp.balance_type_code <> ''X''
3775 AND
3776 (temp.code_combination_status_code = :14
3777 OR temp.alt_ccid_status_code = :15) ';
3778
3779 --
3780 --============================
3781 -- execute Dynamic SQL
3782 --============================
3783
3784 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3785
3786 /*
3787 * Fix bug 4388150 - the l_seq_stmt and l_alt_sql_stmt are too big for the
3788 * fnd_log
3789 trace
3790 (p_msg => '>> EXECUTE Dynamic SQL = '||l_sql_stmt
3791 ,p_level => C_LEVEL_STATEMENT
3792 ,p_module => l_log_module);
3793
3794 trace
3795 (p_msg => '>> EXECUTE Dynamic SQL = '||l_alt_sql_stmt
3796 ,p_level => C_LEVEL_STATEMENT
3797 ,p_module => l_log_module);
3798 */
3799
3800 trace
3801 (p_msg => 'target_coa = '||g_array_cache_target_coa(Idx)
3802 ,p_level => C_LEVEL_STATEMENT
3803 ,p_module => l_log_module);
3804
3805 dump_text(p_text => l_upd_stmt);
3806
3807 END IF;
3808
3809
3810 EXECUTE IMMEDIATE l_upd_stmt USING g_array_cache_target_coa(Idx) -- 1
3811 ,C_PROCESSING
3812 ,g_array_cache_target_coa(Idx)
3813 ,g_array_cache_target_coa(Idx)
3814 ,C_PROCESSING -- 5
3815 ,C_CREATED
3816 ,g_array_cache_target_coa(Idx)
3817 ,C_PROCESSING
3818 ,g_array_cache_target_coa(Idx)
3819 ,g_array_cache_target_coa(Idx) -- 10
3820 ,C_PROCESSING
3821 ,C_CREATED
3822 ,g_array_cache_target_coa(Idx)
3823 ,C_PROCESSING
3824 ,C_PROCESSING; -- 15
3825
3826 l_rowcount := SQL%ROWCOUNT;
3827 l_count := l_count + l_rowcount;
3828
3829 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3830
3831 trace
3832 (p_msg => '# rows updated (ccid + alt ccid) = '||l_rowcount
3833 ,p_level => C_LEVEL_STATEMENT
3837
3834 ,p_module => l_log_module);
3835 END IF;
3836
3838 END IF;
3839 END LOOP;
3840 END IF;
3841 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3842 trace
3843 (p_msg => 'return value. = '||l_count
3844 ,p_level => C_LEVEL_PROCEDURE
3845 ,p_module => l_log_module);
3846 trace
3847 (p_msg => 'END of create_ccid'
3848 ,p_level => C_LEVEL_PROCEDURE
3849 ,p_module => l_log_module);
3850 END IF;
3851 RETURN l_count;
3852 EXCEPTION
3853 WHEN xla_exceptions_pkg.application_exception THEN
3854 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3855 trace
3856 (p_msg => 'Error. = '||sqlerrm
3857 ,p_level => C_LEVEL_PROCEDURE
3858 ,p_module => l_log_module);
3859 END IF;
3860 RAISE;
3861 WHEN OTHERS THEN
3862 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3863 trace
3864 (p_msg => 'Error. = '||sqlerrm
3865 ,p_level => C_LEVEL_PROCEDURE
3866 ,p_module => l_log_module);
3867 END IF;
3868 xla_exceptions_pkg.raise_message
3869 (p_location => 'xla_ae_code_combination_pkg.create_ccid');
3870 END create_ccid;
3871
3872 /*---------------------------------------------------------------+
3873 | |
3874 | Private function |
3875 | |
3876 | create_new_ccid |
3877 | |
3878 | create new accounting ccids and new ALT ccids. It returns the |
3879 | number of rows updated |
3880 | |
3881 +---------------------------------------------------------------*/
3882
3883 FUNCTION create_new_ccid
3884 RETURN NUMBER
3885 IS
3886 l_log_module VARCHAR2(240);
3887 l_rowcount NUMBER;
3888 l_count NUMBER;
3889 BEGIN
3890 --
3891 IF g_log_enabled THEN
3892 l_log_module := C_DEFAULT_MODULE||'.create_new_ccid';
3893 END IF;
3894 --
3895 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3896
3897 trace
3898 (p_msg => 'BEGIN of create_new_ccid'
3899 ,p_level => C_LEVEL_PROCEDURE
3900 ,p_module => l_log_module);
3901
3902 END IF;
3903
3904 l_rowcount := 0;
3905 l_count := 0;
3906 g_error_exists := FALSE;
3907
3908 UPDATE xla_ae_lines_gt temp
3909 SET code_combination_id =
3910 CASE
3911 WHEN temp.code_combination_id IS NULL
3912 AND temp.balance_type_code <> 'X'
3913 THEN xla_ae_code_combination_pkg.GetCcid(
3914 temp.segment1
3915 ,temp.segment2
3916 ,temp.segment3
3917 ,temp.segment4
3918 ,temp.segment5
3919 ,temp.segment6
3920 ,temp.segment7
3921 ,temp.segment8
3922 ,temp.segment9
3923 ,temp.segment10
3924 ,temp.segment11
3925 ,temp.segment12
3926 ,temp.segment13
3927 ,temp.segment14
3928 ,temp.segment15
3929 ,temp.segment16
3930 ,temp.segment17
3931 ,temp.segment18
3932 ,temp.segment19
3933 ,temp.segment20
3934 ,temp.segment21
3935 ,temp.segment22
3936 ,temp.segment23
3937 ,temp.segment24
3938 ,temp.segment25
3939 ,temp.segment26
3940 ,temp.segment27
3941 ,temp.segment28
3942 ,temp.segment29
3943 ,temp.segment30
3944 ,temp.ccid_coa_id
3945 )
3946 ELSE code_combination_id
3947 END
3948
3949 ,code_combination_status_code =
3950 CASE
3951 WHEN temp.code_combination_id IS NULL
3952 AND temp.balance_type_code <> 'X'
3953 THEN C_CREATED
3954 ELSE code_combination_status_code
3955 END
3956
3957 ,alt_code_combination_id =
3958 CASE
3959 WHEN temp.alt_code_combination_id IS NULL
3960 AND temp.balance_type_code <> 'X'
3961 AND temp.gain_or_loss_flag = 'Y'
3962 THEN xla_ae_code_combination_pkg.GetCcid(
3963 temp.alt_segment1
3964 ,temp.alt_segment2
3965 ,temp.alt_segment3
3966 ,temp.alt_segment4
3967 ,temp.alt_segment5
3968 ,temp.alt_segment6
3969 ,temp.alt_segment7
3970 ,temp.alt_segment8
3971 ,temp.alt_segment9
3972 ,temp.alt_segment10
3973 ,temp.alt_segment11
3974 ,temp.alt_segment12
3975 ,temp.alt_segment13
3976 ,temp.alt_segment14
3977 ,temp.alt_segment15
3978 ,temp.alt_segment16
3982 ,temp.alt_segment20
3979 ,temp.alt_segment17
3980 ,temp.alt_segment18
3981 ,temp.alt_segment19
3983 ,temp.alt_segment21
3984 ,temp.alt_segment22
3985 ,temp.alt_segment23
3986 ,temp.alt_segment24
3987 ,temp.alt_segment25
3988 ,temp.alt_segment26
3989 ,temp.alt_segment27
3990 ,temp.alt_segment28
3991 ,temp.alt_segment29
3992 ,temp.alt_segment30
3993 ,temp.ccid_coa_id
3994 )
3995 ELSE alt_code_combination_id
3996 END
3997 ,alt_ccid_status_code =
3998 CASE
3999 WHEN temp.alt_code_combination_id IS NULL
4000 AND temp.balance_type_code <> 'X'
4001 AND temp.gain_or_loss_flag = 'Y'
4002 THEN C_CREATED
4003 ELSE alt_ccid_status_code
4004 END
4005 WHERE temp.balance_type_code <> 'X'
4006 AND (
4007 (temp.code_combination_id IS NULL)
4008 OR
4009 (temp.alt_code_combination_id IS NULL
4010 AND temp.gain_or_loss_flag = 'Y')
4011 );
4012
4013 l_rowcount := SQL%ROWCOUNT;
4014 l_count := l_count + l_rowcount;
4015
4016 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4017
4018 trace
4019 (p_msg => '# rows updated (ccid + ALT ccid)='||l_rowcount
4020 ,p_level => C_LEVEL_STATEMENT
4021 ,p_module => l_log_module);
4022
4023 END IF;
4024 --
4025 -- get event error messages
4026 --
4027 IF g_error_exists THEN get_ccid_errors; END IF;
4028
4029 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4030
4031 trace
4032 (p_msg => 'return value. = '||to_char(l_count)
4033 ,p_level => C_LEVEL_PROCEDURE
4034 ,p_module => l_log_module);
4035
4036 trace
4037 (p_msg => 'END of create_new_ccid'
4038 ,p_level => C_LEVEL_PROCEDURE
4039 ,p_module => l_log_module);
4040
4041 END IF;
4042 --
4043 RETURN l_count;
4044 EXCEPTION
4045 WHEN xla_exceptions_pkg.application_exception THEN
4046 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4047 trace
4048 (p_msg => 'Error. = '||sqlerrm
4049 ,p_level => C_LEVEL_PROCEDURE
4050 ,p_module => l_log_module);
4051 END IF;
4052 RAISE;
4053 WHEN OTHERS THEN
4054 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4055 trace
4056 (p_msg => 'Error. = '||sqlerrm
4057 ,p_level => C_LEVEL_PROCEDURE
4058 ,p_module => l_log_module);
4059 END IF;
4060 xla_exceptions_pkg.raise_message
4061 (p_location => 'xla_ae_code_combination_pkg.create_new_ccid');
4062 END create_new_ccid;
4063
4064 /*---------------------------------------------------------------+
4065 | |
4066 | Private function |
4067 | |
4068 | map_ccid |
4069 | |
4070 | converts the transaction CCIDs in accounting ledger's COA |
4071 | |
4072 +---------------------------------------------------------------*/
4073
4074 FUNCTION map_ccid(
4075 p_gl_coa_mapping_name IN VARCHAR2
4076 , p_gl_coa_mapping_id IN NUMBER
4077 )
4078 RETURN NUMBER
4079 IS
4080 --
4081 l_message VARCHAR2(2000);
4082 l_count NUMBER;
4083 l_rowcount NUMBER;
4084 l_log_module VARCHAR2(240);
4085 l_ConcatKey VARCHAR2(4000);
4086
4087 TYPE t_array_num15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
4088 TYPE t_array_vc30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4089
4090 l_array_header_id t_array_num15;
4091 l_array_temp_line_num t_array_num15;
4092 l_array_ledger_id t_array_num15;
4093 l_array_coa_mapping_id t_array_num15;
4094 l_array_gl_map_status t_array_vc30;
4095 l_array_processing_status_code t_array_vc30;
4096 l_array_to_segment_code t_array_vc30;
4097 l_array_ccid t_array_num15;
4098 l_array_segment_value t_array_vc30;
4099
4100 l_array_alt_header_id t_array_num15;
4101 l_array_alt_temp_line_num t_array_num15;
4102 l_array_alt_ledger_id t_array_num15;
4103 l_array_alt_coa_mapping_id t_array_num15;
4104 l_array_alt_gl_map_status t_array_vc30;
4105 l_array_alt_proc_status_code t_array_vc30;
4106 l_array_alt_to_segment_code t_array_vc30;
4107 l_array_alt_ccid t_array_num15;
4108 l_array_alt_segment_value t_array_vc30;
4109
4110 -- bug 6743896
4111 l_coa_mapping_id NUMBER;
4112 l_from_coa_id NUMBER;
4113 l_to_coa_id NUMBER;
4114 l_start_date_active DATE;
4115 l_end_date_active DATE;
4119 --
4116 GL_DISABLED_MAPPING Exception;
4117 -- end bug 6743896
4118
4120 BEGIN
4121 IF g_log_enabled THEN
4122 l_log_module := C_DEFAULT_MODULE||'.map_ccid';
4123 END IF;
4124 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4125 trace
4126 (p_msg => 'BEGIN of map_ccid'
4127 ,p_level => C_LEVEL_PROCEDURE
4128 ,p_module => l_log_module);
4129 END IF;
4130
4131 l_count := 0;
4132 l_rowcount := 0;
4133
4134 DELETE FROM gl_accts_map_int_gt; -- bug 4564062
4135
4136 INSERT INTO gl_accts_map_int_gt
4137 (
4138 from_ccid
4139 , coa_mapping_id
4140 )
4141 SELECT code_combination_id
4142 , sl_coa_mapping_id
4143 FROM xla_transaction_accts_gt
4144 WHERE code_combination_id IS NOT NULL
4145 AND processing_status_code IN (C_MAP_CCID , C_MAP_SEGMENT)
4146 AND sl_coa_mapping_id = p_gl_coa_mapping_id
4147 GROUP BY code_combination_id, sl_coa_mapping_id
4148 ;
4149
4150 l_rowcount:= SQL%ROWCOUNT;
4151 l_count := l_count + l_rowcount;
4152
4153 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4154 trace
4155 (p_msg => '# rows inserted into gl_accts_map_int_gt(ccid) = '||to_char(l_rowcount)
4156 ,p_level => C_LEVEL_STATEMENT
4157 ,p_module => l_log_module);
4158 END IF;
4159
4160 IF l_rowcount > 0 THEN
4161
4162 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4163 trace
4164 (p_msg => '-> CALL gl_accounts_map_grp API'
4165 ,p_level => C_LEVEL_STATEMENT
4166 ,p_module => l_log_module);
4167 END IF;
4168
4169 -- call GL_ACCOUNTS_MAP_GRP to map accounts
4170 GL_ACCOUNTS_MAP_GRP.MAP(
4171 mapping_name => p_gl_coa_mapping_name
4172 , create_ccid => ( NVL(g_cache_dynamic_inserts(p_gl_coa_mapping_id),'N') ='Y' )
4173 , debug => g_log_enabled
4174 );
4175
4176 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4177 trace
4178 (p_msg => 'SQL - convert the transaction ccids'
4179 ,p_level => C_LEVEL_STATEMENT
4180 ,p_module => l_log_module);
4181 END IF;
4182
4183 --
4184 -- get transaction ccid
4185 --
4186 UPDATE /*+ dynamic_sampling(1) */ xla_ae_lines_gt temp
4187 SET ( temp.code_combination_id
4188 ,temp.segment1
4189 ,temp.segment2
4190 ,temp.segment3
4191 ,temp.segment4
4192 ,temp.segment5
4193 ,temp.segment6
4194 ,temp.segment7
4195 ,temp.segment8
4196 ,temp.segment9
4197 ,temp.segment10
4198 ,temp.segment11
4199 ,temp.segment12
4200 ,temp.segment13
4201 ,temp.segment14
4202 ,temp.segment15
4203 ,temp.segment16
4204 ,temp.segment17
4205 ,temp.segment18
4206 ,temp.segment19
4207 ,temp.segment20
4208 ,temp.segment21
4209 ,temp.segment22
4210 ,temp.segment23
4211 ,temp.segment24
4212 ,temp.segment25
4213 ,temp.segment26
4214 ,temp.segment27
4215 ,temp.segment28
4216 ,temp.segment29
4217 ,temp.segment30
4218 ,temp.code_combination_status_code) =
4219 (
4220 SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
4221 DISTINCT
4222 DECODE(gami.error_code, NULL, gami.to_ccid, -1)
4223 , nvl(temp.segment1 , gami.to_segment1)
4224 , nvl(temp.segment2 , gami.to_segment2)
4225 , nvl(temp.segment3 , gami.to_segment3)
4226 , nvl(temp.segment4 , gami.to_segment4)
4227 , nvl(temp.segment5 , gami.to_segment5)
4228 , nvl(temp.segment6 , gami.to_segment6)
4229 , nvl(temp.segment7 , gami.to_segment7)
4230 , nvl(temp.segment8 , gami.to_segment8)
4231 , nvl(temp.segment9 , gami.to_segment9)
4232 , nvl(temp.segment10, gami.to_segment10)
4233 , nvl(temp.segment11, gami.to_segment11)
4234 , nvl(temp.segment12, gami.to_segment12)
4235 , nvl(temp.segment13, gami.to_segment13)
4236 , nvl(temp.segment14, gami.to_segment14)
4237 , nvl(temp.segment15, gami.to_segment15)
4238 , nvl(temp.segment16, gami.to_segment16)
4239 , nvl(temp.segment17, gami.to_segment17)
4240 , nvl(temp.segment18, gami.to_segment18)
4241 , nvl(temp.segment19, gami.to_segment19)
4242 , nvl(temp.segment20, gami.to_segment20)
4243 , nvl(temp.segment21, gami.to_segment21)
4244 , nvl(temp.segment22, gami.to_segment22)
4245 , nvl(temp.segment23, gami.to_segment23)
4246 , nvl(temp.segment24, gami.to_segment24)
4247 , nvl(temp.segment25, gami.to_segment25)
4248 , nvl(temp.segment26, gami.to_segment26)
4252 , nvl(temp.segment30, gami.to_segment30)
4249 , nvl(temp.segment27, gami.to_segment27)
4250 , nvl(temp.segment28, gami.to_segment28)
4251 , nvl(temp.segment29, gami.to_segment29)
4253 , CASE WHEN gami.error_code IS NULL
4254 THEN CASE temp.code_combination_status_code
4255 WHEN C_INVALID THEN C_CREATED
4256 WHEN C_PROCESSING THEN C_NOT_PROCESSED
4257 ELSE temp.code_combination_status_code
4258 END
4259 ELSE C_INVALID
4260 END
4261 FROM gl_accts_map_int_gt gami
4262 , xla_transaction_accts_gt xta
4263 WHERE xta.ae_header_id = temp.ae_header_id
4264 AND xta.temp_line_num = temp.temp_line_num
4265 AND xta.ledger_id = temp.ledger_id
4266 AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
4267 AND gami.from_ccid = xta.code_combination_id
4268 AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4269 AND xta.processing_status_code = 'MAP_CCID'
4270 AND xta.side_code IN ('ALL','CREDIT','NA')
4271 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
4272 AND temp.code_combination_id IS NULL
4273 )
4274 WHERE temp.code_combination_id IS NULL
4275 AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4276 AND temp.balance_type_code <> 'X'
4277 AND EXISTS (SELECT 'x'
4278 FROM xla_transaction_accts_gt t
4279 WHERE t.ae_header_id = temp.ae_header_id
4280 AND t.temp_line_num = temp.temp_line_num
4281 AND t.ledger_id = temp.ledger_id
4282 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4283 AND t.processing_status_code = 'MAP_CCID'
4284 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
4285 )
4286 ;
4287
4288 l_rowcount:= SQL%ROWCOUNT;
4289 l_count := l_count + l_rowcount;
4290
4291 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4292 trace
4293 (p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
4294 ,p_level => C_LEVEL_STATEMENT
4295 ,p_module => l_log_module);
4296
4297 END IF;
4298
4299
4300 UPDATE /*+ dynamic_sampling(1) */ xla_ae_lines_gt temp
4301 SET ( temp.alt_code_combination_id
4302 ,temp.alt_segment1
4303 ,temp.alt_segment2
4304 ,temp.alt_segment3
4305 ,temp.alt_segment4
4306 ,temp.alt_segment5
4307 ,temp.alt_segment6
4308 ,temp.alt_segment7
4309 ,temp.alt_segment8
4310 ,temp.alt_segment9
4311 ,temp.alt_segment10
4312 ,temp.alt_segment11
4313 ,temp.alt_segment12
4314 ,temp.alt_segment13
4315 ,temp.alt_segment14
4316 ,temp.alt_segment15
4317 ,temp.alt_segment16
4318 ,temp.alt_segment17
4319 ,temp.alt_segment18
4320 ,temp.alt_segment19
4321 ,temp.alt_segment20
4322 ,temp.alt_segment21
4323 ,temp.alt_segment22
4324 ,temp.alt_segment23
4325 ,temp.alt_segment24
4326 ,temp.alt_segment25
4327 ,temp.alt_segment26
4328 ,temp.alt_segment27
4329 ,temp.alt_segment28
4330 ,temp.alt_segment29
4331 ,temp.alt_segment30
4332 ,temp.alt_ccid_status_code) =
4333 (
4334 SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
4335 DISTINCT
4336 DECODE(gami.error_code, NULL, gami.to_ccid, -1)
4337 , nvl(temp.alt_segment1 , gami.to_segment1)
4338 , nvl(temp.alt_segment2 , gami.to_segment2)
4339 , nvl(temp.alt_segment3 , gami.to_segment3)
4340 , nvl(temp.alt_segment4 , gami.to_segment4)
4341 , nvl(temp.alt_segment5 , gami.to_segment5)
4342 , nvl(temp.alt_segment6 , gami.to_segment6)
4343 , nvl(temp.alt_segment7 , gami.to_segment7)
4344 , nvl(temp.alt_segment8 , gami.to_segment8)
4345 , nvl(temp.alt_segment9 , gami.to_segment9)
4346 , nvl(temp.alt_segment10, gami.to_segment10)
4347 , nvl(temp.alt_segment11, gami.to_segment11)
4348 , nvl(temp.alt_segment12, gami.to_segment12)
4349 , nvl(temp.alt_segment13, gami.to_segment13)
4350 , nvl(temp.alt_segment14, gami.to_segment14)
4351 , nvl(temp.alt_segment15, gami.to_segment15)
4352 , nvl(temp.alt_segment16, gami.to_segment16)
4353 , nvl(temp.alt_segment17, gami.to_segment17)
4354 , nvl(temp.alt_segment18, gami.to_segment18)
4355 , nvl(temp.alt_segment19, gami.to_segment19)
4359 , nvl(temp.alt_segment23, gami.to_segment23)
4356 , nvl(temp.alt_segment20, gami.to_segment20)
4357 , nvl(temp.alt_segment21, gami.to_segment21)
4358 , nvl(temp.alt_segment22, gami.to_segment22)
4360 , nvl(temp.alt_segment24, gami.to_segment24)
4361 , nvl(temp.alt_segment25, gami.to_segment25)
4362 , nvl(temp.alt_segment26, gami.to_segment26)
4363 , nvl(temp.alt_segment27, gami.to_segment27)
4364 , nvl(temp.alt_segment28, gami.to_segment28)
4365 , nvl(temp.alt_segment29, gami.to_segment29)
4366 , nvl(temp.alt_segment30, gami.to_segment30)
4367 , CASE WHEN gami.error_code IS NULL
4368 THEN CASE temp.alt_ccid_status_code
4369 WHEN C_INVALID THEN C_CREATED
4370 WHEN C_PROCESSING THEN C_NOT_PROCESSED
4371 ELSE temp.alt_ccid_status_code
4372 END
4373 ELSE C_INVALID
4374 END
4375 FROM gl_accts_map_int_gt gami
4376 , xla_transaction_accts_gt xta
4377 WHERE xta.ae_header_id = temp.ae_header_id
4378 AND xta.temp_line_num = temp.temp_line_num
4379 AND xta.ledger_id = temp.ledger_id
4380 AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
4381 AND gami.from_ccid = xta.code_combination_id
4382 AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4383 AND xta.processing_status_code = 'MAP_CCID'
4384 AND xta.side_code IN ('ALL','DEBIT')
4385 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
4386 AND temp.alt_code_combination_id IS NULL
4387 )
4388 WHERE temp.alt_code_combination_id IS NULL
4389 AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4390 AND temp.balance_type_code <> 'X'
4391 AND EXISTS (SELECT 'x'
4392 FROM xla_transaction_accts_gt t
4393 WHERE t.ae_header_id = temp.ae_header_id
4394 AND t.temp_line_num = temp.temp_line_num
4395 AND t.ledger_id = temp.ledger_id
4396 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4397 AND t.processing_status_code = 'MAP_CCID'
4398 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
4399 )
4400 ;
4401
4402 l_rowcount:= SQL%ROWCOUNT;
4403 l_count := l_count + l_rowcount;
4404
4405 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4406 trace
4407 (p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
4408 ,p_level => C_LEVEL_STATEMENT
4409 ,p_module => l_log_module);
4410
4411 END IF;
4412
4413 --
4414 -- get accounting segment value from converted ccid
4415 --
4416 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4417 trace
4418 (p_msg => 'SQL - override segments'
4419 ,p_level => C_LEVEL_STATEMENT
4420 ,p_module => l_log_module);
4421 END IF;
4422
4423 --
4424 -- Retrieve to_segment information
4425 --
4426 SELECT coa_mapping_id,
4427 from_coa_id,
4428 to_coa_id,
4429 start_date_active,
4430 end_date_active
4431
4432 INTO l_coa_mapping_id,
4433 l_from_coa_id,
4434 l_to_coa_id,
4435 l_start_date_active,
4436 l_end_date_active
4437 FROM gl_coa_mappings
4438 WHERE name = p_gl_coa_mapping_name
4439 AND coa_mapping_id=p_gl_coa_mapping_id;
4440
4441 IF (l_start_date_active IS NOT NULL AND l_start_date_active > SYSDATE) OR
4442 (l_end_date_active IS NOT NULL AND l_end_date_active < SYSDATE) THEN
4443 raise GL_DISABLED_MAPPING;
4444 END IF;
4445
4446
4447 SELECT DISTINCT
4448 xta.ae_header_id ae_header_id
4449 , xta.temp_line_num temp_line_num
4450 , xta.ledger_id ledger_id
4451 , xta.sl_coa_mapping_id sl_coa_mapping_id
4452 , DECODE (gami.code_combination_id,NULL,C_INVALID,C_CREATED) gl_map_status
4453 , xta.processing_status_code processing_status_code
4454 , xta.to_segment_code to_segment_code
4455 , NVL(gami.code_combination_id,-1) code_combination_id
4456 , CASE xta.from_segment_code
4457 WHEN 'SEGMENT1' THEN gami.segment1
4458 WHEN 'SEGMENT2' THEN gami.segment2
4459 WHEN 'SEGMENT3' THEN gami.segment3
4460 WHEN 'SEGMENT4' THEN gami.segment4
4461 WHEN 'SEGMENT5' THEN gami.segment5
4462 WHEN 'SEGMENT6' THEN gami.segment6
4463 WHEN 'SEGMENT7' THEN gami.segment7
4464 WHEN 'SEGMENT8' THEN gami.segment8
4465 WHEN 'SEGMENT9' THEN gami.segment9
4469 WHEN 'SEGMENT13' THEN gami.segment13
4466 WHEN 'SEGMENT10' THEN gami.segment10
4467 WHEN 'SEGMENT11' THEN gami.segment11
4468 WHEN 'SEGMENT12' THEN gami.segment12
4470 WHEN 'SEGMENT14' THEN gami.segment14
4471 WHEN 'SEGMENT15' THEN gami.segment15
4472 WHEN 'SEGMENT16' THEN gami.segment16
4473 WHEN 'SEGMENT17' THEN gami.segment17
4474 WHEN 'SEGMENT18' THEN gami.segment18
4475 WHEN 'SEGMENT19' THEN gami.segment19
4476 WHEN 'SEGMENT20' THEN gami.segment20
4477 WHEN 'SEGMENT21' THEN gami.segment21
4478 WHEN 'SEGMENT22' THEN gami.segment22
4479 WHEN 'SEGMENT23' THEN gami.segment23
4480 WHEN 'SEGMENT24' THEN gami.segment24
4481 WHEN 'SEGMENT25' THEN gami.segment25
4482 WHEN 'SEGMENT26' THEN gami.segment26
4483 WHEN 'SEGMENT27' THEN gami.segment27
4484 WHEN 'SEGMENT28' THEN gami.segment28
4485 WHEN 'SEGMENT29' THEN gami.segment29
4486 WHEN 'SEGMENT30' THEN gami.segment30
4487 END segment_value
4488 BULK COLLECT INTO
4489 l_array_header_id
4490 , l_array_temp_line_num
4491 , l_array_ledger_id
4492 , l_array_coa_mapping_id
4493 , l_array_gl_map_status
4494 , l_array_processing_status_code
4495 , l_array_to_segment_code
4496 , l_array_ccid
4497 , l_array_segment_value
4498 FROM gl_code_combinations gami
4499 , xla_transaction_accts_gt xta
4500 WHERE gami.code_combination_id = xta.code_combination_id
4501 AND gami.chart_of_accounts_id = l_from_coa_id
4502 AND xta.code_combination_id IS NOT NULL
4503 AND xta.from_segment_code IS NOT NULL
4504 AND xta.to_segment_code IS NOT NULL
4505 AND xta.processing_status_code = 'MAP_SEGMENT'
4506 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
4507 AND xta.side_code IN ('ALL','CREDIT','NA');
4508 -- end bug 6743896
4509
4510
4511 /*SELECT DISTINCT
4512 xta.ae_header_id ae_header_id
4513 , xta.temp_line_num temp_line_num
4514 , xta.ledger_id ledger_id
4515 , xta.sl_coa_mapping_id sl_coa_mapping_id
4516 , DECODE (gami.error_code ,NULL,C_CREATED,C_INVALID) gl_map_status
4517 , xta.processing_status_code processing_status_code
4518 , xta.to_segment_code to_segment_code
4519 , DECODE(gami.error_code ,NULL,gami.to_ccid,-1) code_combination_id
4520 , CASE xta.from_segment_code
4521 WHEN 'SEGMENT1' THEN gami.to_segment1
4522 WHEN 'SEGMENT2' THEN gami.to_segment2
4523 WHEN 'SEGMENT3' THEN gami.to_segment3
4524 WHEN 'SEGMENT4' THEN gami.to_segment4
4525 WHEN 'SEGMENT5' THEN gami.to_segment5
4526 WHEN 'SEGMENT6' THEN gami.to_segment6
4527 WHEN 'SEGMENT7' THEN gami.to_segment7
4528 WHEN 'SEGMENT8' THEN gami.to_segment8
4529 WHEN 'SEGMENT9' THEN gami.to_segment9
4530 WHEN 'SEGMENT10' THEN gami.to_segment10
4531 WHEN 'SEGMENT11' THEN gami.to_segment11
4532 WHEN 'SEGMENT12' THEN gami.to_segment12
4533 WHEN 'SEGMENT13' THEN gami.to_segment13
4534 WHEN 'SEGMENT14' THEN gami.to_segment14
4535 WHEN 'SEGMENT15' THEN gami.to_segment15
4536 WHEN 'SEGMENT16' THEN gami.to_segment16
4537 WHEN 'SEGMENT17' THEN gami.to_segment17
4538 WHEN 'SEGMENT18' THEN gami.to_segment18
4539 WHEN 'SEGMENT19' THEN gami.to_segment19
4540 WHEN 'SEGMENT20' THEN gami.to_segment20
4541 WHEN 'SEGMENT21' THEN gami.to_segment21
4542 WHEN 'SEGMENT22' THEN gami.to_segment22
4543 WHEN 'SEGMENT23' THEN gami.to_segment23
4544 WHEN 'SEGMENT24' THEN gami.to_segment24
4545 WHEN 'SEGMENT25' THEN gami.to_segment25
4546 WHEN 'SEGMENT26' THEN gami.to_segment26
4547 WHEN 'SEGMENT27' THEN gami.to_segment27
4548 WHEN 'SEGMENT28' THEN gami.to_segment28
4549 WHEN 'SEGMENT29' THEN gami.to_segment29
4550 WHEN 'SEGMENT30' THEN gami.to_segment30
4551 END segment_value
4552 BULK COLLECT INTO
4553 l_array_header_id
4554 , l_array_temp_line_num
4555 , l_array_ledger_id
4556 , l_array_coa_mapping_id
4557 , l_array_gl_map_status
4558 , l_array_processing_status_code
4559 , l_array_to_segment_code
4560 , l_array_ccid
4561 , l_array_segment_value
4562 FROM gl_accts_map_int_gt gami
4563 , xla_transaction_accts_gt xta
4564 WHERE gami.from_ccid = xta.code_combination_id
4565 AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4566 AND xta.code_combination_id IS NOT NULL
4567 AND xta.from_segment_code IS NOT NULL
4568 AND xta.to_segment_code IS NOT NULL
4569 AND xta.processing_status_code = 'MAP_SEGMENT'
4570 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
4571 AND xta.side_code IN ('ALL','CREDIT','NA'); */
4572
4573
4574 IF l_array_header_id.COUNT > 0 THEN
4575
4579 SET ( temp.code_combination_id
4576 FORALL i IN l_array_header_id.FIRST .. l_array_header_id.LAST
4577
4578 UPDATE xla_ae_lines_gt temp
4580 ,temp.segment1
4581 ,temp.segment2
4582 ,temp.segment3
4583 ,temp.segment4
4584 ,temp.segment5
4585 ,temp.segment6
4586 ,temp.segment7
4587 ,temp.segment8
4588 ,temp.segment9
4589 ,temp.segment10
4590 ,temp.segment11
4591 ,temp.segment12
4592 ,temp.segment13
4593 ,temp.segment14
4594 ,temp.segment15
4595 ,temp.segment16
4596 ,temp.segment17
4597 ,temp.segment18
4598 ,temp.segment19
4599 ,temp.segment20
4600 ,temp.segment21
4601 ,temp.segment22
4602 ,temp.segment23
4603 ,temp.segment24
4604 ,temp.segment25
4605 ,temp.segment26
4606 ,temp.segment27
4607 ,temp.segment28
4608 ,temp.segment29
4609 ,temp.segment30
4610 ,temp.code_combination_status_code) =
4611 (
4612 SELECT DISTINCT
4613 DECODE(l_array_gl_map_status(i), C_INVALID, -1, temp.code_combination_id)
4614 , DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_segment_value(i), temp.segment1)
4615 , DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_segment_value(i), temp.segment2)
4616 , DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_segment_value(i), temp.segment3)
4617 , DECODE(seg.to_segment_code,'SEGMENT4' ,l_array_segment_value(i), temp.segment4)
4618 , DECODE(seg.to_segment_code,'SEGMENT5' ,l_array_segment_value(i), temp.segment5)
4619 , DECODE(seg.to_segment_code,'SEGMENT6' ,l_array_segment_value(i), temp.segment6)
4620 , DECODE(seg.to_segment_code,'SEGMENT7' ,l_array_segment_value(i), temp.segment7)
4621 , DECODE(seg.to_segment_code,'SEGMENT8' ,l_array_segment_value(i), temp.segment8)
4622 , DECODE(seg.to_segment_code,'SEGMENT9' ,l_array_segment_value(i), temp.segment9)
4623 , DECODE(seg.to_segment_code,'SEGMENT10',l_array_segment_value(i), temp.segment10)
4624 , DECODE(seg.to_segment_code,'SEGMENT11',l_array_segment_value(i), temp.segment11)
4625 , DECODE(seg.to_segment_code,'SEGMENT12',l_array_segment_value(i), temp.segment12)
4626 , DECODE(seg.to_segment_code,'SEGMENT13',l_array_segment_value(i), temp.segment13)
4627 , DECODE(seg.to_segment_code,'SEGMENT14',l_array_segment_value(i), temp.segment14)
4628 , DECODE(seg.to_segment_code,'SEGMENT15',l_array_segment_value(i), temp.segment15)
4629 , DECODE(seg.to_segment_code,'SEGMENT16',l_array_segment_value(i), temp.segment16)
4630 , DECODE(seg.to_segment_code,'SEGMENT17',l_array_segment_value(i), temp.segment17)
4631 , DECODE(seg.to_segment_code,'SEGMENT18',l_array_segment_value(i), temp.segment18)
4632 , DECODE(seg.to_segment_code,'SEGMENT19',l_array_segment_value(i), temp.segment19)
4633 , DECODE(seg.to_segment_code,'SEGMENT20',l_array_segment_value(i), temp.segment20)
4634 , DECODE(seg.to_segment_code,'SEGMENT21',l_array_segment_value(i), temp.segment21)
4635 , DECODE(seg.to_segment_code,'SEGMENT22',l_array_segment_value(i), temp.segment22)
4636 , DECODE(seg.to_segment_code,'SEGMENT23',l_array_segment_value(i), temp.segment23)
4637 , DECODE(seg.to_segment_code,'SEGMENT24',l_array_segment_value(i), temp.segment24)
4638 , DECODE(seg.to_segment_code,'SEGMENT25',l_array_segment_value(i), temp.segment25)
4639 , DECODE(seg.to_segment_code,'SEGMENT26',l_array_segment_value(i), temp.segment26)
4640 , DECODE(seg.to_segment_code,'SEGMENT27',l_array_segment_value(i), temp.segment27)
4641 , DECODE(seg.to_segment_code,'SEGMENT28',l_array_segment_value(i), temp.segment28)
4642 , DECODE(seg.to_segment_code,'SEGMENT29',l_array_segment_value(i), temp.segment29)
4643 , DECODE(seg.to_segment_code,'SEGMENT30',l_array_segment_value(i), temp.segment30)
4644 , CASE l_array_gl_map_status(i)
4645 WHEN C_INVALID THEN C_INVALID
4646 ELSE CASE temp.code_combination_status_code
4647 WHEN C_INVALID THEN C_PROCESSING
4648 WHEN C_CREATED THEN C_NOT_PROCESSED
4649 ELSE temp.code_combination_status_code
4650 END
4651 END
4652 FROM xla_transaction_accts_gt seg
4653 WHERE seg.ae_header_id = temp.ae_header_id
4654 AND seg.temp_line_num = temp.temp_line_num
4655 AND seg.ledger_id = temp.ledger_id
4656 AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
4657 AND seg.ae_header_id = l_array_header_id(i)
4658 AND seg.temp_line_num = l_array_temp_line_num(i)
4659 AND seg.ledger_id = l_array_ledger_id(i)
4660 AND seg.sl_coa_mapping_id = l_array_coa_mapping_id(i)
4661 AND seg.to_segment_code = l_array_to_segment_code(i) --added 6660472 suggested by Kaouther
4662 AND seg.processing_status_code = l_array_processing_status_code(i)--added for bug6314762 to avoid single row subquery returns more than one row error
4663 )
4667 WHERE t.ae_header_id = temp.ae_header_id
4664 WHERE temp.balance_type_code <> 'X'
4665 AND EXISTS (SELECT 'x'
4666 FROM xla_transaction_accts_gt t
4668 AND t.temp_line_num = temp.temp_line_num
4669 AND t.ledger_id = temp.ledger_id
4670 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4671 AND t.processing_status_code = 'MAP_SEGMENT'
4672 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
4673 AND t.ae_header_id = l_array_header_id(i)
4674 AND t.temp_line_num = l_array_temp_line_num(i)
4675 AND t.ledger_id = l_array_ledger_id(i)
4676 AND t.to_segment_code = l_array_to_segment_code(i) --added 6660472 suggested by Kaouther
4677 AND t.sl_coa_mapping_id = l_array_coa_mapping_id(i))
4678
4679 ;
4680 END IF; -- l_array_header_id.COUNT > 0
4681
4682 l_rowcount:= SQL%ROWCOUNT;
4683 l_count := l_count + l_rowcount;
4684
4685 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4686 trace
4687 (p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
4688 ,p_level => C_LEVEL_STATEMENT
4689 ,p_module => l_log_module);
4690
4691 END IF;
4692
4693 --
4694 -- Retrieve to_segment information for alt ccid
4695 --
4696 SELECT DISTINCT
4697 xta.ae_header_id ae_header_id
4698 , xta.temp_line_num temp_line_num
4699 , xta.ledger_id ledger_id
4700 , xta.sl_coa_mapping_id sl_coa_mapping_id
4701 , DECODE (gami.error_code ,NULL,C_CREATED,C_INVALID) gl_map_status
4702 , xta.processing_status_code processing_status_code
4703 , xta.to_segment_code to_segment_code
4704 , DECODE(gami.error_code ,NULL,gami.to_ccid,-1) code_combination_id
4705 , CASE xta.from_segment_code
4706 WHEN 'SEGMENT1' THEN gami.to_segment1
4707 WHEN 'SEGMENT2' THEN gami.to_segment2
4708 WHEN 'SEGMENT3' THEN gami.to_segment3
4709 WHEN 'SEGMENT4' THEN gami.to_segment4
4710 WHEN 'SEGMENT5' THEN gami.to_segment5
4711 WHEN 'SEGMENT6' THEN gami.to_segment6
4712 WHEN 'SEGMENT7' THEN gami.to_segment7
4713 WHEN 'SEGMENT8' THEN gami.to_segment8
4714 WHEN 'SEGMENT9' THEN gami.to_segment9
4715 WHEN 'SEGMENT10' THEN gami.to_segment10
4716 WHEN 'SEGMENT11' THEN gami.to_segment11
4717 WHEN 'SEGMENT12' THEN gami.to_segment12
4718 WHEN 'SEGMENT13' THEN gami.to_segment13
4719 WHEN 'SEGMENT14' THEN gami.to_segment14
4720 WHEN 'SEGMENT15' THEN gami.to_segment15
4721 WHEN 'SEGMENT16' THEN gami.to_segment16
4722 WHEN 'SEGMENT17' THEN gami.to_segment17
4723 WHEN 'SEGMENT18' THEN gami.to_segment18
4724 WHEN 'SEGMENT19' THEN gami.to_segment19
4725 WHEN 'SEGMENT20' THEN gami.to_segment20
4726 WHEN 'SEGMENT21' THEN gami.to_segment21
4727 WHEN 'SEGMENT22' THEN gami.to_segment22
4728 WHEN 'SEGMENT23' THEN gami.to_segment23
4729 WHEN 'SEGMENT24' THEN gami.to_segment24
4730 WHEN 'SEGMENT25' THEN gami.to_segment25
4731 WHEN 'SEGMENT26' THEN gami.to_segment26
4732 WHEN 'SEGMENT27' THEN gami.to_segment27
4733 WHEN 'SEGMENT28' THEN gami.to_segment28
4734 WHEN 'SEGMENT29' THEN gami.to_segment29
4735 WHEN 'SEGMENT30' THEN gami.to_segment30
4736 END segment_value
4737 BULK COLLECT INTO
4738 l_array_alt_header_id
4739 , l_array_alt_temp_line_num
4740 , l_array_alt_ledger_id
4741 , l_array_alt_coa_mapping_id
4742 , l_array_alt_gl_map_status
4743 , l_array_alt_proc_status_code
4744 , l_array_alt_to_segment_code
4745 , l_array_alt_ccid
4746 , l_array_alt_segment_value
4747 FROM gl_accts_map_int_gt gami
4748 , xla_transaction_accts_gt xta
4749 WHERE gami.from_ccid = xta.code_combination_id
4750 AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4751 AND xta.code_combination_id IS NOT NULL
4752 AND xta.from_segment_code IS NOT NULL
4753 AND xta.to_segment_code IS NOT NULL
4754 AND xta.processing_status_code = 'MAP_SEGMENT'
4755 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
4756 AND xta.side_code IN ('ALL','DEBIT');
4757
4758 IF l_array_alt_header_id.COUNT > 0 THEN
4759
4760 FORALL i IN l_array_alt_header_id.FIRST .. l_array_alt_header_id.LAST
4761
4762 UPDATE xla_ae_lines_gt temp
4763 SET ( temp.alt_code_combination_id
4764 ,temp.alt_segment1
4765 ,temp.alt_segment2
4766 ,temp.alt_segment3
4767 ,temp.alt_segment4
4768 ,temp.alt_segment5
4769 ,temp.alt_segment6
4770 ,temp.alt_segment7
4771 ,temp.alt_segment8
4772 ,temp.alt_segment9
4776 ,temp.alt_segment13
4773 ,temp.alt_segment10
4774 ,temp.alt_segment11
4775 ,temp.alt_segment12
4777 ,temp.alt_segment14
4778 ,temp.alt_segment15
4779 ,temp.alt_segment16
4780 ,temp.alt_segment17
4781 ,temp.alt_segment18
4782 ,temp.alt_segment19
4783 ,temp.alt_segment20
4784 ,temp.alt_segment21
4785 ,temp.alt_segment22
4786 ,temp.alt_segment23
4787 ,temp.alt_segment24
4788 ,temp.alt_segment25
4789 ,temp.alt_segment26
4790 ,temp.alt_segment27
4791 ,temp.alt_segment28
4792 ,temp.alt_segment29
4793 ,temp.alt_segment30
4794 ,temp.alt_ccid_status_code) =
4795 (
4796 SELECT DISTINCT
4797 DECODE(l_array_alt_gl_map_status(i), C_INVALID, -1, temp.alt_code_combination_id)
4798 , DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_alt_segment_value(i), temp.alt_segment1)
4799 , DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_alt_segment_value(i), temp.alt_segment2)
4800 , DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_alt_segment_value(i), temp.alt_segment3)
4801 , DECODE(seg.to_segment_code,'SEGMENT4' ,l_array_alt_segment_value(i), temp.alt_segment4)
4802 , DECODE(seg.to_segment_code,'SEGMENT5' ,l_array_alt_segment_value(i), temp.alt_segment5)
4803 , DECODE(seg.to_segment_code,'SEGMENT6' ,l_array_alt_segment_value(i), temp.alt_segment6)
4804 , DECODE(seg.to_segment_code,'SEGMENT7' ,l_array_alt_segment_value(i), temp.alt_segment7)
4805 , DECODE(seg.to_segment_code,'SEGMENT8' ,l_array_alt_segment_value(i), temp.alt_segment8)
4806 , DECODE(seg.to_segment_code,'SEGMENT9' ,l_array_alt_segment_value(i), temp.alt_segment9)
4807 , DECODE(seg.to_segment_code,'SEGMENT10',l_array_alt_segment_value(i), temp.alt_segment10)
4808 , DECODE(seg.to_segment_code,'SEGMENT11',l_array_alt_segment_value(i), temp.alt_segment11)
4809 , DECODE(seg.to_segment_code,'SEGMENT12',l_array_alt_segment_value(i), temp.alt_segment12)
4810 , DECODE(seg.to_segment_code,'SEGMENT13',l_array_alt_segment_value(i), temp.alt_segment13)
4811 , DECODE(seg.to_segment_code,'SEGMENT14',l_array_alt_segment_value(i), temp.alt_segment14)
4812 , DECODE(seg.to_segment_code,'SEGMENT15',l_array_alt_segment_value(i), temp.alt_segment15)
4813 , DECODE(seg.to_segment_code,'SEGMENT16',l_array_alt_segment_value(i), temp.alt_segment16)
4814 , DECODE(seg.to_segment_code,'SEGMENT17',l_array_alt_segment_value(i), temp.alt_segment17)
4815 , DECODE(seg.to_segment_code,'SEGMENT18',l_array_alt_segment_value(i), temp.alt_segment18)
4816 , DECODE(seg.to_segment_code,'SEGMENT19',l_array_alt_segment_value(i), temp.alt_segment19)
4817 , DECODE(seg.to_segment_code,'SEGMENT20',l_array_alt_segment_value(i), temp.alt_segment20)
4818 , DECODE(seg.to_segment_code,'SEGMENT21',l_array_alt_segment_value(i), temp.alt_segment21)
4819 , DECODE(seg.to_segment_code,'SEGMENT22',l_array_alt_segment_value(i), temp.alt_segment22)
4820 , DECODE(seg.to_segment_code,'SEGMENT23',l_array_alt_segment_value(i), temp.alt_segment23)
4821 , DECODE(seg.to_segment_code,'SEGMENT24',l_array_alt_segment_value(i), temp.alt_segment24)
4822 , DECODE(seg.to_segment_code,'SEGMENT25',l_array_alt_segment_value(i), temp.alt_segment25)
4823 , DECODE(seg.to_segment_code,'SEGMENT26',l_array_alt_segment_value(i), temp.alt_segment26)
4824 , DECODE(seg.to_segment_code,'SEGMENT27',l_array_alt_segment_value(i), temp.alt_segment27)
4825 , DECODE(seg.to_segment_code,'SEGMENT28',l_array_alt_segment_value(i), temp.alt_segment28)
4826 , DECODE(seg.to_segment_code,'SEGMENT29',l_array_alt_segment_value(i), temp.alt_segment29)
4827 , DECODE(seg.to_segment_code,'SEGMENT30',l_array_alt_segment_value(i), temp.alt_segment30)
4828 , CASE l_array_alt_gl_map_status(i)
4829 WHEN C_INVALID THEN C_INVALID
4830 ELSE CASE temp.alt_ccid_status_code
4831 WHEN C_INVALID THEN C_PROCESSING
4832 WHEN C_CREATED THEN C_NOT_PROCESSED
4833 ELSE temp.alt_ccid_status_code
4834 END
4835 END
4836 FROM xla_transaction_accts_gt seg
4837 WHERE seg.ae_header_id = temp.ae_header_id
4838 AND seg.temp_line_num = temp.temp_line_num
4839 AND seg.ledger_id = temp.ledger_id
4840 AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
4841 AND seg.ae_header_id = l_array_alt_header_id(i)
4842 AND seg.temp_line_num = l_array_alt_temp_line_num(i)
4843 AND seg.ledger_id = l_array_alt_ledger_id(i)
4844 AND seg.sl_coa_mapping_id = l_array_alt_coa_mapping_id(i)
4848 WHERE temp.balance_type_code <> 'X'
4845 AND seg.to_segment_code = l_array_alt_to_segment_code(i) --added 6660472 suggested by Kaouther
4846 AND seg.processing_status_code = l_array_processing_status_code(i)--added by for bug6314762 to avoid single row subquery returns more than one row error
4847 )
4849 AND EXISTS (SELECT 'x'
4850 FROM xla_transaction_accts_gt t
4851 WHERE t.ae_header_id = temp.ae_header_id
4852 AND t.temp_line_num = temp.temp_line_num
4853 AND t.ledger_id = temp.ledger_id
4854 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4855 AND t.processing_status_code = 'MAP_SEGMENT'
4856 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
4857 AND t.ae_header_id = l_array_alt_header_id(i)
4858 AND t.temp_line_num = l_array_alt_temp_line_num(i)
4859 AND t.ledger_id = l_array_alt_ledger_id(i)
4860 AND t.to_segment_code = l_array_alt_to_segment_code(i) --added 6660472 suggested by Kaouther
4861 AND t.sl_coa_mapping_id = l_array_alt_coa_mapping_id(i) )
4862
4863 ;
4864 END IF; -- l_array_alt_header_id.COUNT > 0
4865
4866 l_rowcount:= SQL%ROWCOUNT;
4867 l_count := l_count + l_rowcount;
4868
4869 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4870 trace
4871 (p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
4872 ,p_level => C_LEVEL_STATEMENT
4873 ,p_module => l_log_module);
4874
4875 END IF;
4876
4877 END IF;
4878
4879 --
4880 -- get Mapping errors
4881 --
4882 FOR error_rec IN
4883 (
4884 SELECT error_code
4885 ,event_id
4886 ,ledger_id
4887 ,entity_id
4888 ,from_ccid
4889 ,ccid_coa_id
4890 FROM (SELECT DISTINCT
4891 gami.error_code error_code
4892 ,xahg.event_id event_id
4893 ,xahg.ledger_id ledger_id
4894 ,xahg.entity_id entity_id
4895 ,gami.from_ccid from_ccid
4896 ,xjlg.ccid_coa_id ccid_coa_id
4897 FROM gl_accts_map_int_gt gami
4898 ,xla_ae_lines_gt xjlg
4899 ,xla_ae_headers_gt xahg
4900 ,xla_transaction_accts_gt xtag
4901 WHERE xjlg.ae_header_id = xahg.ae_header_id
4902 AND xjlg.event_id = xahg.event_id
4903 AND xjlg.ae_header_id = xtag.ae_header_id
4904 AND xjlg.temp_line_num = xtag.temp_line_num
4905 AND xjlg.ledger_id = xtag.ledger_id
4906 AND xjlg.sl_coa_mapping_id = xtag.sl_coa_mapping_id
4907 AND gami.from_ccid = xtag.code_combination_id
4908 AND gami.coa_mapping_id = xtag.sl_coa_mapping_id
4909 AND gami.coa_mapping_id = p_gl_coa_mapping_id
4910 AND xtag.processing_status_code IN ('MAP_CCID','MAP_SEGMENT')
4911 AND xtag.side_code IN ('ALL','CREDIT','NA')
4912 AND gami.error_code IS NOT NULL
4913 AND xjlg.code_combination_id = -1
4914 AND xjlg.balance_type_code <> 'X'
4915 UNION
4916 SELECT DISTINCT
4917 gami.error_code error_code
4918 ,xahg.event_id event_id
4919 ,xahg.ledger_id ledger_id
4920 ,xahg.entity_id entity_id
4921 ,gami.from_ccid from_ccid
4922 , xjlg.ccid_coa_id coa_id
4923 FROM gl_accts_map_int_gt gami
4924 ,xla_ae_lines_gt xjlg
4925 ,xla_ae_headers_gt xahg
4926 ,xla_transaction_accts_gt xtag
4927 WHERE xjlg.ae_header_id = xahg.ae_header_id
4928 AND xjlg.event_id = xahg.event_id
4929 AND xjlg.ae_header_id = xtag.ae_header_id
4930 AND xjlg.temp_line_num = xtag.temp_line_num
4931 AND xjlg.ledger_id = xtag.ledger_id
4932 AND xjlg.sl_coa_mapping_id = xtag.sl_coa_mapping_id
4933 AND gami.from_ccid = xtag.code_combination_id
4934 AND gami.coa_mapping_id = xtag.sl_coa_mapping_id
4935 AND gami.coa_mapping_id = p_gl_coa_mapping_id
4939 AND xjlg.alt_code_combination_id = -1
4936 AND xtag.processing_status_code IN ('MAP_CCID','MAP_SEGMENT')
4937 AND xtag.side_code IN ('ALL','DEBIT')
4938 AND gami.error_code IS NOT NULL
4940 AND xjlg.balance_type_code <> 'X'
4941 )
4942 )
4943 LOOP
4944
4945 --added bug 6666983,account value should be displayed in error message even if ccid is invalid
4946 SELECT concatenated_segments
4947 INTO l_ConcatKey
4948 FROM gl_code_combinations_kfv
4949 WHERE code_combination_id = error_rec.from_ccid;
4950
4951
4952 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
4953 l_message := SUBSTR(error_rec.error_code,1,1000);
4954 xla_accounting_err_pkg.build_message
4955 (p_appli_s_name => 'XLA'
4956 ,p_msg_name => 'XLA_AP_GL_INVALID_COA_MAPPING'
4957 ,p_token_1 => 'GL_COA_MAPPING_NAME'
4958 ,p_value_1 => p_gl_coa_mapping_name
4959 ,p_token_2 => 'ACCOUNT_VALUE'
4960 ,p_value_2 => NVL(get_account_value(
4961 p_combination_id => error_rec.from_ccid
4962 ,p_flex_application_id => 101
4963 ,p_application_short_name => 'SQLGL'
4964 ,p_id_flex_code => 'GL#'
4965 ,p_id_flex_num => error_rec.ccid_coa_id),l_ConcatKey)
4966 ,p_token_3 => 'ERROR'
4967 ,p_value_3 => nvl(l_message, error_rec.error_code)
4968 ,p_entity_id => error_rec.entity_id
4969 ,p_event_id => error_rec.event_id
4970 ,p_ledger_id => error_rec.ledger_id
4971 );
4972
4973 IF (C_LEVEL_ERROR >= g_log_level) THEN
4974 trace
4975 (p_msg => 'ERROR: XLA_AP_GL_INVALID_COA_MAPPING'
4976 ,p_level => C_LEVEL_ERROR
4977 ,p_module => l_log_module);
4978 END IF;
4979 END LOOP;
4980
4981
4982 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4983 trace
4984 (p_msg => 'END of map_ccid = '||to_char(l_count)
4985 ,p_level => C_LEVEL_PROCEDURE
4986 ,p_module => l_log_module);
4987
4988 END IF;
4989 RETURN l_count;
4990 EXCEPTION
4991
4992 WHEN GL_DISABLED_MAPPING THEN
4993 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4994 trace
4995 (p_msg => 'Error. = '||sqlerrm
4996 ,p_level => C_LEVEL_PROCEDURE
4997 ,p_module => l_log_module);
4998 END IF;
4999 RAISE;
5000
5001 WHEN xla_exceptions_pkg.application_exception THEN
5002 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5003 trace
5004 (p_msg => 'Error. = '||sqlerrm
5005 ,p_level => C_LEVEL_PROCEDURE
5006 ,p_module => l_log_module);
5007 END IF;
5008 RAISE;
5009 WHEN OTHERS THEN
5010 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5011 trace
5012 (p_msg => 'Error. = '||sqlerrm
5013 ,p_level => C_LEVEL_PROCEDURE
5014 ,p_module => l_log_module);
5015 END IF;
5016 xla_exceptions_pkg.raise_message
5017 (p_location => 'xla_ae_code_combination_pkg.map_ccid');
5018 --
5019 END map_ccid;
5020
5021
5022 /*---------------------------------------------------------------+
5023 | |
5024 | Private function |
5025 | |
5026 | map_segment_qualifier |
5027 | |
5028 | converts the transaction CCIDs in accounting ledger's COA |
5029 | |
5030 +---------------------------------------------------------------*/
5031
5032 FUNCTION map_segment_qualifier(
5033 p_gl_coa_mapping_name IN VARCHAR2
5034 , p_gl_coa_mapping_id IN NUMBER
5035 )
5036 RETURN NUMBER
5037 IS
5038 l_message VARCHAR2(2000);
5039 l_count NUMBER;
5040 l_rowcount NUMBER;
5041 l_error BOOLEAN;
5042 l_gl_error_code VARCHAR2(1000);
5043 l_array_event_id xla_ae_journal_entry_pkg.t_array_Num;
5044 l_array_entity_id xla_ae_journal_entry_pkg.t_array_Num;
5045 l_array_ledger_id xla_ae_journal_entry_pkg.t_array_Num;
5046 l_log_module VARCHAR2(240);
5047 BEGIN
5048 IF g_log_enabled THEN
5049 l_log_module := C_DEFAULT_MODULE||'.map_segment_qualifier';
5050 END IF;
5051 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5052 trace
5053 (p_msg => 'BEGIN of map_segment_qualifier'
5054 ,p_level => C_LEVEL_PROCEDURE
5055 ,p_module => l_log_module);
5056 END IF;
5057
5058 l_count := 0;
5059
5063 AND xtag.processing_status_code = 'MAP_QUALIFIER'
5060 FOR qualifier_rec IN (SELECT xtag.from_segment_code qualifier
5061 FROM xla_transaction_accts_gt xtag
5062 WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
5064 GROUP BY xtag.from_segment_code )
5065 LOOP
5066 -- reset the GT table
5067
5068 DELETE from gl_accts_map_bsv_gt;
5069
5070 -- insert the segment value in the GT table
5071
5072 INSERT INTO gl_accts_map_bsv_gt
5073 ( SOURCE_BSV )
5074 SELECT segment
5075 FROM xla_transaction_accts_gt xtag
5076 WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
5077 AND xtag.processing_status_code = 'MAP_QUALIFIER'
5078 AND xtag.from_segment_code = qualifier_rec.qualifier
5079 GROUP BY segment
5080 ;
5081
5082 -- call the GL qualifier mapping
5083
5084 BEGIN
5085
5086 GL_ACCOUNTS_MAP_GRP.map_qualified_segment(
5087 p_mapping_name => p_gl_coa_mapping_name
5088 , p_qualifier => qualifier_rec.qualifier
5089 , p_debug => g_log_enabled
5090 );
5091
5092 EXCEPTION
5093
5094 WHEN GL_ACCOUNTS_MAP_GRP.GL_INVALID_MAPPING_NAME THEN
5095 l_gl_error_code:= 'GL_INVALID_MAPPING_NAME';
5096 l_error :=TRUE;
5097 WHEN GL_ACCOUNTS_MAP_GRP.GL_DISABLED_MAPPING THEN
5098 l_gl_error_code:='GL_DISABLED_MAPPING';
5099 l_error :=TRUE;
5100 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
5101 l_gl_error_code:='GL_BSV_MAP_NO_SOURCE_BAL_SEG';
5102 l_error :=TRUE;
5103 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
5104 l_gl_error_code:='GL_BSV_MAP_NO_TARGET_BAL_SEG';
5105 l_error :=TRUE;
5106 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NO_SEGMENT_MAP THEN
5107 l_gl_error_code:='GL_BSV_MAP_NO_SEGMENT_MAP';
5108 l_error :=TRUE;
5109 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NO_SINGLE_VALUE THEN
5110 l_gl_error_code:='GL_BSV_MAP_NO_SINGLE_VALUE';
5111 l_error :=TRUE;
5112 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NO_FROM_SEGMENT THEN
5113 l_gl_error_code:='GL_BSV_MAP_NO_FROM_SEGMENT';
5114 l_error :=TRUE;
5115 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_NOT_BSV_DERIVED THEN
5116 l_gl_error_code:='GL_BSV_MAP_NOT_BSV_DERIVED';
5117 l_error :=TRUE;
5118 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_SETUP_ERROR THEN
5119 l_gl_error_code:='GL_BSV_MAP_SETUP_ERROR';
5120 l_error :=TRUE;
5121 WHEN GL_ACCOUNTS_MAP_GRP.GL_BSV_MAP_MAPPING_ERROR THEN
5122 l_gl_error_code:='GL_BSV_MAP_MAPPING_ERROR';
5123 l_error :=TRUE;
5124 END;
5125
5126 -- update xla_ae_lines_gt
5127
5128 IF l_error THEN
5129
5130 UPDATE xla_ae_lines_gt temp
5131 SET temp.code_combination_id = -1
5132 ,temp.code_combination_status_code = C_INVALID
5133 WHERE temp.balance_type_code <> 'X'
5134 AND EXISTS (SELECT 'x'
5135 FROM xla_transaction_accts_gt t
5136 WHERE t.ae_header_id = temp.ae_header_id
5137 AND t.temp_line_num = temp.temp_line_num
5138 AND t.ledger_id = temp.ledger_id
5139 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5140 AND t.from_segment_code = qualifier_rec.qualifier
5141 AND t.processing_status_code = 'MAP_QUALIFIER'
5142 AND t.side_code IN ('ALL','CREDIT','NA')
5143 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
5144 )
5145 RETURNING entity_id, event_id, ledger_id BULK COLLECT
5146 INTO l_array_entity_id, l_array_event_id, l_array_ledger_id
5147 ;
5148
5149 l_rowcount:= SQL%ROWCOUNT;
5150
5151 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5152 trace
5153 (p_msg => '# of rows updated into xla_ae_lines_gt(error) = '||to_char(l_rowcount)
5154 ,p_level => C_LEVEL_STATEMENT
5155 ,p_module => l_log_module);
5156
5157 END IF;
5158
5159 l_count := l_count + l_rowcount;
5160
5161 IF (C_LEVEL_ERROR >= g_log_level) THEN
5162 trace
5163 (p_msg => 'ERROR: XLA_AP_GL_INV_QUAL_MAPPING'
5164 ,p_level => C_LEVEL_ERROR
5165 ,p_module => l_log_module);
5166 END IF;
5167
5168 FOR Idx IN l_array_event_id.FIRST .. l_array_event_id.LAST LOOP
5169
5170 IF l_array_event_id.EXISTS(Idx) THEN
5171 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
5172 xla_accounting_err_pkg.build_message
5173 (p_appli_s_name => 'XLA'
5174 ,p_msg_name => 'XLA_AP_GL_INV_QUAL_MAPPING'
5175 ,p_token_1 => 'GL_COA_MAPPING_NAME'
5176 ,p_value_1 => p_gl_coa_mapping_name
5177 ,p_token_2 => 'QUALIFIER_NAME'
5178 ,p_value_2 => xla_lookups_pkg.get_meaning(
5179 'XLA_FLEXFIELD_SEGMENTS_QUAL'
5180 , qualifier_rec.qualifier)
5181 ,p_token_3 => 'ERROR_MSG'
5182 ,p_value_3 => l_gl_error_code
5183 ,p_entity_id => l_array_entity_id(Idx)
5184 ,p_event_id => l_array_event_id(Idx)
5188
5185 ,p_ledger_id => l_array_ledger_id(Idx)
5186 );
5187 END IF;
5189 END LOOP;
5190
5191 l_array_entity_id.DELETE ;
5192 l_array_event_id.DELETE;
5193 l_array_ledger_id.DELETE;
5194
5195 UPDATE xla_ae_lines_gt temp
5196 SET temp.alt_code_combination_id = -1
5197 ,temp.alt_ccid_status_code = C_INVALID
5198 WHERE temp.balance_type_code <> 'X'
5199 AND EXISTS (SELECT 'x'
5200 FROM xla_transaction_accts_gt t
5201 WHERE t.ae_header_id = temp.ae_header_id
5202 AND t.temp_line_num = temp.temp_line_num
5203 AND t.ledger_id = temp.ledger_id
5204 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5205 AND t.from_segment_code = qualifier_rec.qualifier
5206 AND t.processing_status_code = 'MAP_QUALIFIER'
5207 AND t.side_code IN ('ALL','DEBIT')
5208 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id
5209 )
5210 RETURNING entity_id, event_id, ledger_id BULK COLLECT
5211 INTO l_array_entity_id, l_array_event_id, l_array_ledger_id
5212 ;
5213
5214 l_rowcount:= SQL%ROWCOUNT;
5215 l_count := l_count + l_rowcount;
5216
5217 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5218 trace
5219 (p_msg => '# of rows updated into xla_ae_lines_gt(error) = '||to_char(l_rowcount)
5220 ,p_level => C_LEVEL_STATEMENT
5221 ,p_module => l_log_module);
5222
5223 END IF;
5224
5225 FOR Idx IN l_array_event_id.FIRST .. l_array_event_id.LAST LOOP
5226
5227 IF l_array_event_id.EXISTS(Idx) THEN
5228 xla_ae_journal_entry_pkg.g_global_status := xla_ae_journal_entry_pkg.C_INVALID;
5229 xla_accounting_err_pkg.build_message
5230 (p_appli_s_name => 'XLA'
5231 ,p_msg_name => 'XLA_AP_GL_INV_QUAL_MAPPING'
5232 ,p_token_1 => 'GL_COA_MAPPING_NAME'
5233 ,p_value_1 => p_gl_coa_mapping_name
5234 ,p_token_2 => 'QUALIFIER_NAME'
5235 ,p_value_2 => xla_lookups_pkg.get_meaning(
5236 'XLA_FLEXFIELD_SEGMENTS_QUAL'
5237 , qualifier_rec.qualifier)
5238 ,p_token_3 => 'ERROR_MSG'
5239 ,p_value_3 => l_gl_error_code
5240 ,p_entity_id => l_array_entity_id(Idx)
5241 ,p_event_id => l_array_event_id(Idx)
5242 ,p_ledger_id => l_array_ledger_id(Idx)
5243 );
5244 END IF;
5245
5246 END LOOP;
5247
5248
5249 ELSE
5250 -- no error
5251 -- copy converted qualifier value in the xla_ae_lines_gt table
5252
5253 UPDATE xla_ae_lines_gt temp
5254 SET ( temp.segment1
5255 ,temp.segment2
5256 ,temp.segment3
5257 ,temp.segment4
5258 ,temp.segment5
5259 ,temp.segment6
5260 ,temp.segment7
5261 ,temp.segment8
5262 ,temp.segment9
5263 ,temp.segment10
5264 ,temp.segment11
5265 ,temp.segment12
5266 ,temp.segment13
5267 ,temp.segment14
5268 ,temp.segment15
5269 ,temp.segment16
5270 ,temp.segment17
5271 ,temp.segment18
5272 ,temp.segment19
5273 ,temp.segment20
5274 ,temp.segment21
5275 ,temp.segment22
5276 ,temp.segment23
5277 ,temp.segment24
5278 ,temp.segment25
5279 ,temp.segment26
5280 ,temp.segment27
5281 ,temp.segment28
5282 ,temp.segment29
5283 ,temp.segment30
5284 ,temp.code_combination_status_code) =
5285 (
5286 SELECT DISTINCT
5287 DECODE(seg.to_segment_code,'SEGMENT1' ,seg.target_value, temp.segment1)
5288 , DECODE(seg.to_segment_code,'SEGMENT2' ,seg.target_value, temp.segment2)
5289 , DECODE(seg.to_segment_code,'SEGMENT3' ,seg.target_value, temp.segment3)
5290 , DECODE(seg.to_segment_code,'SEGMENT4' ,seg.target_value, temp.segment4)
5291 , DECODE(seg.to_segment_code,'SEGMENT5' ,seg.target_value, temp.segment5)
5292 , DECODE(seg.to_segment_code,'SEGMENT6' ,seg.target_value, temp.segment6)
5293 , DECODE(seg.to_segment_code,'SEGMENT7' ,seg.target_value, temp.segment7)
5294 , DECODE(seg.to_segment_code,'SEGMENT8' ,seg.target_value, temp.segment8)
5295 , DECODE(seg.to_segment_code,'SEGMENT9' ,seg.target_value, temp.segment9)
5296 , DECODE(seg.to_segment_code,'SEGMENT10',seg.target_value, temp.segment10)
5297 , DECODE(seg.to_segment_code,'SEGMENT11',seg.target_value, temp.segment11)
5301 , DECODE(seg.to_segment_code,'SEGMENT15',seg.target_value, temp.segment15)
5298 , DECODE(seg.to_segment_code,'SEGMENT12',seg.target_value, temp.segment12)
5299 , DECODE(seg.to_segment_code,'SEGMENT13',seg.target_value, temp.segment13)
5300 , DECODE(seg.to_segment_code,'SEGMENT14',seg.target_value, temp.segment14)
5302 , DECODE(seg.to_segment_code,'SEGMENT16',seg.target_value, temp.segment16)
5303 , DECODE(seg.to_segment_code,'SEGMENT17',seg.target_value, temp.segment17)
5304 , DECODE(seg.to_segment_code,'SEGMENT18',seg.target_value, temp.segment18)
5305 , DECODE(seg.to_segment_code,'SEGMENT19',seg.target_value, temp.segment19)
5306 , DECODE(seg.to_segment_code,'SEGMENT20',seg.target_value, temp.segment20)
5307 , DECODE(seg.to_segment_code,'SEGMENT21',seg.target_value, temp.segment21)
5308 , DECODE(seg.to_segment_code,'SEGMENT22',seg.target_value, temp.segment22)
5309 , DECODE(seg.to_segment_code,'SEGMENT23',seg.target_value, temp.segment23)
5310 , DECODE(seg.to_segment_code,'SEGMENT24',seg.target_value, temp.segment24)
5311 , DECODE(seg.to_segment_code,'SEGMENT25',seg.target_value, temp.segment25)
5312 , DECODE(seg.to_segment_code,'SEGMENT26',seg.target_value, temp.segment26)
5313 , DECODE(seg.to_segment_code,'SEGMENT27',seg.target_value, temp.segment27)
5314 , DECODE(seg.to_segment_code,'SEGMENT28',seg.target_value, temp.segment28)
5315 , DECODE(seg.to_segment_code,'SEGMENT29',seg.target_value, temp.segment29)
5316 , DECODE(seg.to_segment_code,'SEGMENT30',seg.target_value, temp.segment30)
5317 , CASE temp.code_combination_status_code
5318 WHEN C_INVALID THEN C_PROCESSING
5319 WHEN C_CREATED THEN C_NOT_PROCESSED
5320 ELSE temp.code_combination_status_code
5321 END
5322 FROM (
5323 SELECT DISTINCT
5324 xta.ae_header_id ae_header_id
5325 , xta.temp_line_num temp_line_num
5326 , xta.ledger_id ledger_id
5327 , xta.sl_coa_mapping_id sl_coa_mapping_id
5328 , xta.to_segment_code to_segment_code
5329 , gami.target_bsv target_value
5330 FROM gl_accts_map_bsv_gt gami
5331 , xla_transaction_accts_gt xta
5332 WHERE gami.source_bsv = xta.segment
5333 AND xta.from_segment_code = qualifier_rec.qualifier
5334 AND xta.processing_status_code = 'MAP_QUALIFIER'
5335 AND xta.side_code IN ('ALL','CREDIT','NA')
5336 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
5337 ) seg
5338 WHERE seg.ae_header_id = temp.ae_header_id
5339 AND seg.temp_line_num = temp.temp_line_num
5340 AND seg.ledger_id = temp.ledger_id
5341 AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5342 )
5343 WHERE temp.balance_type_code <> 'X'
5344 AND EXISTS (SELECT 'x'
5345 FROM xla_transaction_accts_gt t
5346 WHERE t.ae_header_id = temp.ae_header_id
5347 AND t.temp_line_num = temp.temp_line_num
5348 AND t.ledger_id = temp.ledger_id
5349 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5350 AND t.from_segment_code = qualifier_rec.qualifier
5351 AND t.processing_status_code = 'MAP_QUALIFIER'
5352 AND t.side_code IN ('ALL','CREDIT','NA')
5353 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id)
5354 ;
5355
5356 l_rowcount:= SQL%ROWCOUNT;
5357 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5358 trace
5359 (p_msg => '# of rows updated into xla_ae_lines_gt(ccid) = '||to_char(l_rowcount)
5360 ,p_level => C_LEVEL_STATEMENT
5361 ,p_module => l_log_module);
5362 END IF;
5363 l_count := l_count + l_rowcount;
5364 --end insert into xla_ae_lines_gt(ccid)
5365
5366
5367 UPDATE xla_ae_lines_gt temp
5368 SET ( temp.alt_segment1
5369 ,temp.alt_segment2
5370 ,temp.alt_segment3
5371 ,temp.alt_segment4
5372 ,temp.alt_segment5
5373 ,temp.alt_segment6
5374 ,temp.alt_segment7
5375 ,temp.alt_segment8
5376 ,temp.alt_segment9
5377 ,temp.alt_segment10
5378 ,temp.alt_segment11
5379 ,temp.alt_segment12
5380 ,temp.alt_segment13
5381 ,temp.alt_segment14
5382 ,temp.alt_segment15
5383 ,temp.alt_segment16
5384 ,temp.alt_segment17
5385 ,temp.alt_segment18
5386 ,temp.alt_segment19
5387 ,temp.alt_segment20
5388 ,temp.alt_segment21
5389 ,temp.alt_segment22
5390 ,temp.alt_segment23
5391 ,temp.alt_segment24
5395 ,temp.alt_segment28
5392 ,temp.alt_segment25
5393 ,temp.alt_segment26
5394 ,temp.alt_segment27
5396 ,temp.alt_segment29
5397 ,temp.alt_segment30
5398 ,temp.alt_ccid_status_code) =
5399 (
5400 SELECT DISTINCT
5401 DECODE(seg.to_segment_code,'SEGMENT1' ,seg.target_value, temp.alt_segment1)
5402 , DECODE(seg.to_segment_code,'SEGMENT2' ,seg.target_value, temp.alt_segment2)
5403 , DECODE(seg.to_segment_code,'SEGMENT3' ,seg.target_value, temp.alt_segment3)
5404 , DECODE(seg.to_segment_code,'SEGMENT4' ,seg.target_value, temp.alt_segment4)
5405 , DECODE(seg.to_segment_code,'SEGMENT5' ,seg.target_value, temp.alt_segment5)
5406 , DECODE(seg.to_segment_code,'SEGMENT6' ,seg.target_value, temp.alt_segment6)
5407 , DECODE(seg.to_segment_code,'SEGMENT7' ,seg.target_value, temp.alt_segment7)
5408 , DECODE(seg.to_segment_code,'SEGMENT8' ,seg.target_value, temp.alt_segment8)
5409 , DECODE(seg.to_segment_code,'SEGMENT9' ,seg.target_value, temp.alt_segment9)
5410 , DECODE(seg.to_segment_code,'SEGMENT10',seg.target_value, temp.alt_segment10)
5411 , DECODE(seg.to_segment_code,'SEGMENT11',seg.target_value, temp.alt_segment11)
5412 , DECODE(seg.to_segment_code,'SEGMENT12',seg.target_value, temp.alt_segment12)
5413 , DECODE(seg.to_segment_code,'SEGMENT13',seg.target_value, temp.alt_segment13)
5414 , DECODE(seg.to_segment_code,'SEGMENT14',seg.target_value, temp.alt_segment14)
5415 , DECODE(seg.to_segment_code,'SEGMENT15',seg.target_value, temp.alt_segment15)
5416 , DECODE(seg.to_segment_code,'SEGMENT16',seg.target_value, temp.alt_segment16)
5417 , DECODE(seg.to_segment_code,'SEGMENT17',seg.target_value, temp.alt_segment17)
5418 , DECODE(seg.to_segment_code,'SEGMENT18',seg.target_value, temp.alt_segment18)
5419 , DECODE(seg.to_segment_code,'SEGMENT19',seg.target_value, temp.alt_segment19)
5420 , DECODE(seg.to_segment_code,'SEGMENT20',seg.target_value, temp.alt_segment20)
5421 , DECODE(seg.to_segment_code,'SEGMENT21',seg.target_value, temp.alt_segment21)
5422 , DECODE(seg.to_segment_code,'SEGMENT22',seg.target_value, temp.alt_segment22)
5423 , DECODE(seg.to_segment_code,'SEGMENT23',seg.target_value, temp.alt_segment23)
5424 , DECODE(seg.to_segment_code,'SEGMENT24',seg.target_value, temp.alt_segment24)
5425 , DECODE(seg.to_segment_code,'SEGMENT25',seg.target_value, temp.alt_segment25)
5426 , DECODE(seg.to_segment_code,'SEGMENT26',seg.target_value, temp.alt_segment26)
5427 , DECODE(seg.to_segment_code,'SEGMENT27',seg.target_value, temp.alt_segment27)
5428 , DECODE(seg.to_segment_code,'SEGMENT28',seg.target_value, temp.alt_segment28)
5429 , DECODE(seg.to_segment_code,'SEGMENT29',seg.target_value, temp.alt_segment29)
5430 , DECODE(seg.to_segment_code,'SEGMENT30',seg.target_value, temp.alt_segment30)
5431 , CASE temp.alt_ccid_status_code
5432 WHEN C_INVALID THEN C_PROCESSING
5433 WHEN C_CREATED THEN C_NOT_PROCESSED
5434 ELSE temp.alt_ccid_status_code
5435 END
5436 FROM (
5437 SELECT DISTINCT
5438 xta.ae_header_id ae_header_id
5439 , xta.temp_line_num temp_line_num
5440 , xta.ledger_id ledger_id
5441 , xta.sl_coa_mapping_id sl_coa_mapping_id
5442 , xta.to_segment_code to_segment_code
5443 , gami.target_bsv target_value
5444 FROM gl_accts_map_bsv_gt gami
5445 , xla_transaction_accts_gt xta
5446 WHERE gami.source_bsv = xta.segment
5447 AND xta.from_segment_code = qualifier_rec.qualifier
5448 AND xta.processing_status_code = 'MAP_QUALIFIER'
5449 AND xta.side_code IN ('ALL','DEBIT')
5450 AND xta.sl_coa_mapping_id = p_gl_coa_mapping_id
5451 ) seg
5452 WHERE seg.ae_header_id = temp.ae_header_id
5453 AND seg.temp_line_num = temp.temp_line_num
5454 AND seg.ledger_id = temp.ledger_id
5455 AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5456 )
5457 WHERE temp.balance_type_code <> 'X'
5458 AND EXISTS (SELECT 'x'
5459 FROM xla_transaction_accts_gt t
5460 WHERE t.ae_header_id = temp.ae_header_id
5461 AND t.temp_line_num = temp.temp_line_num
5462 AND t.ledger_id = temp.ledger_id
5463 AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5464 AND t.from_segment_code = qualifier_rec.qualifier
5465 AND t.processing_status_code = 'MAP_QUALIFIER'
5466 AND t.side_code IN ('ALL','DEBIT')
5467 AND t.sl_coa_mapping_id = p_gl_coa_mapping_id)
5468 ;
5469
5470 l_rowcount:= SQL%ROWCOUNT;
5471 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5475 ,p_module => l_log_module);
5472 trace
5473 (p_msg => '# of rows updated into xla_ae_lines_gt(ALT ccid) = '||to_char(l_rowcount)
5474 ,p_level => C_LEVEL_STATEMENT
5476 END IF;
5477 l_count := l_count + l_rowcount;
5478 --end insert into xla_ae_lines_gt(ccid)
5479
5480 END IF;
5481
5482 END LOOP; --end loop qualifiers
5483
5484 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5485 trace
5486 (p_msg => 'END of map_segment_qualifier ='||l_count
5487 ,p_level => C_LEVEL_PROCEDURE
5488 ,p_module => l_log_module);
5489 END IF;
5490 RETURN l_count;
5491 EXCEPTION
5492 WHEN xla_exceptions_pkg.application_exception THEN
5493 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5494 trace
5495 (p_msg => 'Error. = '||sqlerrm
5496 ,p_level => C_LEVEL_PROCEDURE
5497 ,p_module => l_log_module);
5498 END IF;
5499 RAISE;
5500 WHEN OTHERS THEN
5501 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5502 trace
5503 (p_msg => 'Error. = '||sqlerrm
5504 ,p_level => C_LEVEL_PROCEDURE
5505 ,p_module => l_log_module);
5506 END IF;
5507 xla_exceptions_pkg.raise_message
5508 (p_location => 'xla_ae_code_combination_pkg.map_segment_qualifier');
5509 END map_segment_qualifier;
5510
5511 /*-----------------------------------------------------------------------+
5512 | |
5513 | Private function |
5514 | |
5515 | map_transaction_accounts |
5516 | |
5517 | Drives the mapping of ccids and segment qualifiers |
5518 | |
5519 +-----------------------------------------------------------------------*/
5520
5521 FUNCTION map_transaction_accounts
5522 RETURN NUMBER
5523 IS
5524 l_message VARCHAR2(2000);
5525 l_count NUMBER;
5526 l_log_module VARCHAR2(240);
5527 BEGIN
5528 IF g_log_enabled THEN
5529 l_log_module := C_DEFAULT_MODULE||'.map_transaction_accounts';
5530 END IF;
5531 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5532 trace
5533 (p_msg => 'BEGIN of map_transaction_accounts'
5534 ,p_level => C_LEVEL_PROCEDURE
5535 ,p_module => l_log_module);
5536 END IF;
5537 l_count := 0;
5538
5539 IF g_cache_coa_sla_mapping.COUNT > 0 THEN
5540 FOR Idx IN g_cache_coa_sla_mapping.FIRST .. g_cache_coa_sla_mapping.LAST LOOP
5541 IF g_cache_coa_sla_mapping.EXISTS(Idx) THEN
5542
5543 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5544 trace
5545 (p_msg => 'coa_sla_mapping_name = '|| g_cache_coa_sla_mapping(Idx)||
5546 ', coa_sla_mapping_id = '|| Idx
5547 ,p_level => C_LEVEL_STATEMENT
5548 ,p_module => l_log_module);
5549 END IF;
5550
5551 l_count := l_count + map_ccid(
5552 p_gl_coa_mapping_name => g_cache_coa_sla_mapping(Idx),
5553 p_gl_coa_mapping_id => Idx);
5554
5555 l_count := l_count + map_segment_qualifier(
5556 p_gl_coa_mapping_name => g_cache_coa_sla_mapping(Idx),
5557 p_gl_coa_mapping_id => Idx);
5558
5559 END IF; -- end if exists
5560 END LOOP;
5561 END IF; -- end if count>0
5562 --
5563 refreshGLMappingCache;
5564 --
5565 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5566 trace
5567 (p_msg => 'END of map_transaction_accounts= '||to_char(l_count)
5568 ,p_level => C_LEVEL_PROCEDURE
5569 ,p_module => l_log_module);
5570 END IF;
5571 --
5572 RETURN l_count;
5573 EXCEPTION
5574 WHEN xla_exceptions_pkg.application_exception THEN
5575 RAISE;
5576 WHEN OTHERS THEN
5577 xla_exceptions_pkg.raise_message
5578 (p_location => 'xla_ae_code_combination_pkg.map_transaction_accounts');
5579 --
5580 END map_transaction_accounts;
5581
5582 /*-----------------------------------------------------------------------+
5583 | |
5584 | Public function |
5585 | |
5586 | BuildCcids |
5587 | |
5588 | builds the new accounting ccids. It returns the number of rows updated |
5589 | |
5590 +-----------------------------------------------------------------------*/
5591 FUNCTION BuildCcids
5592 RETURN NUMBER
5593 IS
5594 --
5595 --
5596 l_ccid_created NUMBER;
5597 l_array_je_ids xla_ae_journal_entry_pkg.t_array_Num;
5598 l_array_event_ids xla_ae_journal_entry_pkg.t_array_Num;
5599 l_array_event_status xla_ae_journal_entry_pkg.t_array_V1L;
5600 l_cache_array_target_coa xla_ae_journal_entry_pkg.t_array_Num;
5601 l_log_module VARCHAR2(240);
5602 --
5603 BEGIN
5604 --
5605 IF g_log_enabled THEN
5606 l_log_module := C_DEFAULT_MODULE||'.BuildCcids';
5607 END IF;
5608 --
5609 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5610
5611 trace
5612 (p_msg => 'BEGIN of BuildCcids'
5613 ,p_level => C_LEVEL_PROCEDURE
5614 ,p_module => l_log_module);
5615
5616 END IF;
5617 --
5618 l_ccid_created :=0;
5619 --
5620 l_ccid_created := map_transaction_accounts +
5621 validate_source_ccid +
5622 override_ccid +
5623 create_ccid +
5624 create_new_ccid ;
5625 --
5626 IF l_ccid_created > 0 THEN
5627
5628 UPDATE xla_ae_headers_gt xahg
5629 SET xahg.accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
5630 WHERE xahg.ae_header_id IN (SELECT xalg.ae_header_id
5631 FROM xla_ae_lines_gt xalg
5632 WHERE xalg.balance_type_code <> 'X'
5633 AND (xalg.code_combination_status_code <> C_CREATED
5634 OR xalg.code_combination_id = -1) and (nvl(xalg.gain_or_loss_flag,'N') = 'N' or nvl(xalg.calculate_g_l_amts_flag,'N') = 'N')
5635 /*
5636 OR ((xalg.alt_code_combination_id = -1
5637 OR xalg.alt_ccid_status_code <> C_CREATED)
5638 AND xalg.gain_or_loss_flag = 'Y' and xalg.calculate_g_l_amts_flag = 'Y'))
5639 */
5640 )
5641 AND xahg.accounting_entry_status_code <> xla_ae_journal_entry_pkg.C_INVALID
5642 ;
5643
5644 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
5645
5646 trace
5647 (p_msg => 'SQL - Update xla_ae_headers_gt '
5648 ,p_level => C_LEVEL_STATEMENT
5649 ,p_module => l_log_module);
5650 trace
5651 (p_msg => '# of rows updated into xla_ae_headers_gt = '||SQL%ROWCOUNT
5652 ,p_level => C_LEVEL_STATEMENT
5653 ,p_module => l_log_module);
5654
5655 END IF;
5656
5657
5658 END IF;
5659 --
5660 -- reset traget coa cache
5661 --
5662 g_array_cache_target_coa := l_cache_array_target_coa;
5663 --
5664 --
5665 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
5666
5667 trace
5668 (p_msg => 'return value. = 0'
5669 ,p_level => C_LEVEL_PROCEDURE
5670 ,p_module => l_log_module);
5671
5672 trace
5673 (p_msg => 'END of BuildCcids'
5674 ,p_level => C_LEVEL_PROCEDURE
5675 ,p_module => l_log_module);
5676
5677 END IF;
5678 --
5679 RETURN 0;
5680 EXCEPTION
5681 WHEN xla_exceptions_pkg.application_exception THEN
5682 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5683 trace
5684 (p_msg => 'Error. = '||sqlerrm
5685 ,p_level => C_LEVEL_PROCEDURE
5686 ,p_module => l_log_module);
5687 END IF;
5688 RAISE;
5689 WHEN OTHERS THEN
5690 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
5691 trace
5692 (p_msg => 'Error. = '||sqlerrm
5693 ,p_level => C_LEVEL_PROCEDURE
5694 ,p_module => l_log_module);
5695 END IF;
5696 xla_exceptions_pkg.raise_message
5697 (p_location => 'xla_ae_code_combination_pkg.BuildCcids');
5698 END BuildCcids;
5699 --
5700 --=============================================================================
5701 --
5702 --
5703 --
5704 --
5705 --
5706 --
5707 --
5708 --
5709 --
5710 --
5711 --
5712 --
5713 --
5714 --
5715 --
5716 --
5717 --
5718 --
5719 --
5720 --
5721 --
5722 --
5723 --=============================================================================
5724 --=============================================================================
5725 -- *********** Initialization routine **********
5726 --=============================================================================
5727
5728 BEGIN
5729 g_error_exists :=FALSE;
5730 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5731 g_log_enabled := fnd_log.test
5732 (log_level => g_log_level
5733 ,module => C_DEFAULT_MODULE);
5734
5735 IF NOT g_log_enabled THEN
5736 g_log_level := C_LEVEL_LOG_DISABLED;
5737 END IF;
5738 END xla_ae_code_combination_pkg; --