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