[Home] [Help]
PACKAGE BODY: APPS.XLA_TB_BALANCE_PKG
Source
1 PACKAGE BODY XLA_TB_BALANCE_PKG AS
2 /* $Header: xlatbbal.pkb 120.8.12010000.5 2009/03/05 13:42:47 ssawhney ship $ */
3 /*======================================================================+
4 | Copyright (c) 2000-2001 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_tb_balance_pkg |
10 | |
11 | DESCRIPTION |
12 | Description |
13 | |
14 | HISTORY |
15 | 01-Dec-05 Mizuru Asada Created |
16 | |
17 | 23-Sep-2008 rajose bug#7364921 Upgraded invoices not appearing|
18 | in the TB report for a given date range. |
19 | 5-Mar-2009 ssawhney BUG 8222265 mainline perf changes |
20 +======================================================================*/
21
22
23 C_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'xla_tb_balance_pkg';
24 -- Object Version Number (OVN)
25 C_OVN CONSTANT NUMBER(15) := 1;
26 C_OWNER_ORACLE CONSTANT VARCHAR2(30) := 'S';
27 C_CREATE_MODE CONSTANT VARCHAR2(30) := 'CREATE';
28 C_UPDATE_MODE CONSTANT VARCHAR2(30) := 'UPDATE';
29
30 g_mode VARCHAR2(30); -- C_CREATE_MODE / C_UPDATE_MODE
31
32
33 TYPE r_definition IS RECORD
34 (definition_code xla_tb_definitions_b.definition_code%TYPE
35 ,name xla_tb_definitions_tl.name%TYPE
36 ,ledger_id xla_tb_definitions_b.ledger_id%TYPE
37 ,description xla_tb_definitions_tl.description%TYPE
38 ,balance_side_code xla_tb_definitions_b.balance_side_code%TYPE);
39
40
41 --
42
43 --=============================================================================
44 -- *********** Local Trace Routine **********
45 --=============================================================================
46 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
47 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
48 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
49 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
50 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
51 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
52
53 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
54 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
55 := 'xla.plsql.xla_tb_balance_pkg';
56
57 g_log_level NUMBER;
58 g_log_enabled BOOLEAN;
59
60 PROCEDURE trace
61 (p_msg IN VARCHAR2
62 ,p_level IN NUMBER
63 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
64 BEGIN
65 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
66 fnd_log.message(p_level, p_module);
67 ELSIF p_level >= g_log_level THEN
68 fnd_log.string(p_level, p_module, p_msg);
69 END IF;
70 EXCEPTION
71 WHEN xla_exceptions_pkg.application_exception THEN
72 RAISE;
73 WHEN OTHERS THEN
74 xla_exceptions_pkg.raise_message
75 (p_location => 'xla_tb_balance_pkg.trace');
76 END trace;
77
78 /*======================================================================+
79 | |
80 | Private Procedure |
81 | |
82 | Submit_Data_Manager |
83 | |
84 | Submit Data Manager. Called in Update mode. |
85 | |
86 +======================================================================*/
87 PROCEDURE submit_data_manager
88 (p_definition_rec IN r_definition
89 ,p_je_source_name IN VARCHAR2
90 ,p_gl_date_from IN DATE
91 ,p_gl_date_to IN DATE
92 ,p_process_mode IN VARCHAR2)
93 IS
94
95
96 --
97 -- WHO column information
98 --
99 l_last_update_date DATE;
100 l_last_updated_by NUMBER(15);
101 l_last_update_login NUMBER(15);
102
103 l_log_module VARCHAR2(240);
104 l_req_id fnd_concurrent_requests.request_id%TYPE;
105
106 BEGIN
107
108 IF g_log_enabled THEN
109 l_log_module := C_DEFAULT_MODULE||'.submit_data_manager';
110 END IF;
111 --
112 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
113
114 trace
115 (p_msg => 'BEGIN of submit_data_manager'
116 ,p_level => C_LEVEL_PROCEDURE
117 ,p_module => l_log_module);
118
119 END IF;
120
121
122 l_last_update_date := sysdate;
123 l_last_updated_by := xla_environment_pkg.g_usr_id;
124 l_last_update_login := xla_environment_pkg.g_login_id;
125
126
127 --
128 -- UPDATE - p_process_mode = NULL
129 -- In this case, data in
130 --
131 l_req_id := fnd_request.submit_request
132 (application => 'XLA'
133 ,program => 'XLATBDMG'
134 ,description => NULL
135 ,start_time => SYSDATE
136 ,sub_request => NULL
137 ,argument1 => p_definition_rec.ledger_id
138 ,argument2 => NULL -- Group_Id
139 ,argument3 => p_definition_rec.definition_code
140 ,argument4 => p_process_mode --Request_Mode
141 ,argument5 => p_je_source_name
142 ,argument6 => NULL --upg_batch_id
143 ,argument7 => fnd_date.date_to_canonical(p_gl_date_from)
144 ,argument8 => fnd_date.date_to_canonical(p_gl_date_to)
145 );
146
147 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
148 trace
149 (p_msg => 'END of submit_data_manager'
150 ,p_level => C_LEVEL_PROCEDURE
151 ,p_module => l_log_module);
152 END IF;
153
154 EXCEPTION
155 WHEN xla_exceptions_pkg.application_exception THEN
156 RAISE;
157 WHEN OTHERS THEN
158 xla_exceptions_pkg.raise_message
159 (p_location => 'xla_tb_balance_pkg.submit_data_manager');
160 END submit_data_manager;
161 /*======================================================================+
162 | |
163 | Private Procedure |
164 | |
165 | Validate_Definition |
166 | |
167 | Validate Report Definition |
168 | |
169 +======================================================================*/
170 FUNCTION validate_definition
171 (p_definition_rec IN r_definition)
172 RETURN BOOLEAN IS
173
174 l_log_module VARCHAR2(240);
175
176 l_db_cnt PLS_INTEGER;
177
178 BEGIN
179
180 IF g_log_enabled THEN
181 l_log_module := C_DEFAULT_MODULE||'.validate_definition';
182 END IF;
183 --
184 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
185
186 trace
187 (p_msg => 'BEGIN of validate_definition'
188 ,p_level => C_LEVEL_PROCEDURE
189 ,p_module => l_log_module);
190
191 END IF;
192
193 --
194 -- Check if Definition Code exists in db
195 --
196 SELECT COUNT(1)
197 INTO l_db_cnt
198 FROM xla_tb_definitions_b
199 WHERE definition_code = p_definition_rec.definition_code;
200
201 IF l_db_cnt = 0 THEN
202
203 IF g_mode = C_CREATE_MODE THEN
204
205 RETURN TRUE;
206
207 ELSIF g_mode = C_UPDATE_MODE THEN
208
209 fnd_message.set_name('XLA','XLA_TB_INVALID_DEF_CODE');
210 fnd_message.set_token('DEFINITION_CODE'
211 ,p_definition_rec.definition_code);
212
213 fnd_msg_pub.add;
214 RETURN FALSE;
215
216 END IF;
217
218 ELSIF l_db_cnt = 1 THEN
219
220 IF g_mode = C_CREATE_MODE THEN
221
222 fnd_message.set_name('XLA','XLA_TB_PARAM_DUP_DEF_CODE');
223 fnd_msg_pub.add;
224 RETURN FALSE;
225
226 ELSIF g_mode = C_UPDATE_MODE THEN
227
228 RETURN TRUE;
229
230 END IF;
231
232 ELSE
233 --
234 -- Data Corruption
235 --
236 RETURN FALSE;
237
238 END IF;
239
240 EXCEPTION
241 WHEN xla_exceptions_pkg.application_exception THEN
242 RAISE;
243 WHEN OTHERS THEN
244 xla_exceptions_pkg.raise_message
245 (p_location => 'xla_tb_balance_pkg.validate_je_sources');
246 END validate_definition;
247
248 /*======================================================================+
249 | |
250 | Private Procedure |
251 | |
252 | Validate_Ledgers |
253 | |
254 | Validate ledgers |
255 | |
256 +======================================================================*/
257 FUNCTION validate_ledger
258 (p_ledger_id IN NUMBER)
259 RETURN BOOLEAN
260 IS
261
262 l_log_module VARCHAR2(240);
263 l_db_cnt PLS_INTEGER;
264
265 BEGIN
266
267 IF g_log_enabled THEN
268 l_log_module := C_DEFAULT_MODULE||'.validate_ledger';
269 END IF;
270 --
271 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
272
273 trace
274 (p_msg => 'BEGIN of validate_ledger'
275 ,p_level => C_LEVEL_PROCEDURE
276 ,p_module => l_log_module);
277
278 END IF;
279
280 --
281 -- Check for null ledger ids is already done in check_required_params
282 --
283
284 SELECT COUNT(1)
285 INTO l_db_cnt
286 FROM gl_ledgers gl
287 WHERE gl.ledger_id = p_ledger_id;
288
289 IF l_db_cnt = 1 THEN
290
291 --
292 -- if p_ledger_id is valid, then return true.
293 --
294 RETURN TRUE;
295
296 ELSIF l_db_cnt = 0 THEN
297
298 --
299 -- if p_ledger_id is invalid, then return false.
300 --
301 fnd_message.set_name('XLA','XLA_COMMON_INVALID_PARAM2');
302 fnd_message.set_token('PARAMETER_VALUE',p_ledger_id);
303 fnd_message.set_token('PARAMETER','p_ledger_id');
304
305 fnd_msg_pub.add;
306 RETURN FALSE;
307
308 END IF;
309
310 EXCEPTION
311 WHEN xla_exceptions_pkg.application_exception THEN
312 RAISE;
313 WHEN OTHERS THEN
314 xla_exceptions_pkg.raise_message
315 (p_location => 'xla_tb_balance_pkg.validate_ledger');
316 END validate_ledger;
317
318 /*======================================================================+
319 | |
320 | Private Procedure |
321 | |
322 | validate_je_sources |
323 | |
324 | <Description of the procedure> |
325 | |
326 +======================================================================*/
327 FUNCTION validate_je_source
328 (p_je_source_name IN VARCHAR2)
329 RETURN BOOLEAN IS
330
331 l_log_module VARCHAR2(240);
332
333 l_db_cnt PLS_INTEGER;
334
335 BEGIN
336
337 IF g_log_enabled THEN
338 l_log_module := C_DEFAULT_MODULE||'.validate_je_source';
339 END IF;
340 --
341 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
342
343 trace
344 (p_msg => 'BEGIN of validate_je_source'
345 ,p_level => C_LEVEL_PROCEDURE
346 ,p_module => l_log_module);
347
348 END IF;
349
350 --
351 -- Check if Journal Source exists in db
352 -- This procedure does not validate journal source against
353 -- xla_tb_defn_je_sources.
354 --
355 SELECT COUNT(1)
356 INTO l_db_cnt
357 FROM xla_subledgers xs
358 ,gl_je_sources gs
359 WHERE xs.je_source_name = gs.je_source_name
360 AND xs.je_source_name = p_je_source_name;
361
362 IF l_db_cnt = 1 THEN
363
364 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
365 trace
369 END IF;
366 (p_msg => 'END of validate_je_source - db count = 1'
367 ,p_level => C_LEVEL_PROCEDURE
368 ,p_module => l_log_module);
370
371 --
372 -- If Journal source is valid, then return true.
373 --
374 RETURN TRUE;
375
376 ELSE
377
378 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
379 trace
380 (p_msg => 'END of validate_je_source - db count = 0'
381 ,p_level => C_LEVEL_PROCEDURE
382 ,p_module => l_log_module);
383 END IF;
384
385 RETURN FALSE;
386
387 END IF;
388
389 EXCEPTION
390 WHEN xla_exceptions_pkg.application_exception THEN
391 RAISE;
392 WHEN OTHERS THEN
393 xla_exceptions_pkg.raise_message
394 (p_location => 'xla_tb_balance_pkg.validate_je_source');
395 END validate_je_source;
396
397 /*======================================================================+
398 | |
399 | Private Procedure |
400 | |
401 | Validate_Ccids |
402 | |
403 | Validate code combination ids |
404 | |
405 +======================================================================*/
406 FUNCTION validate_ccids
407 (p_definition_rec IN r_definition)
408 RETURN BOOLEAN IS
409
410 l_log_module VARCHAR2(240);
411
412 l_param_cnt PLS_INTEGER;
413 l_db_cnt PLS_INTEGER;
414
415 t_array_ccid fnd_table_of_number;
416
417 BEGIN
418
419 IF g_log_enabled THEN
420 l_log_module := C_DEFAULT_MODULE||'.validate_ccids';
421 END IF;
422 --
423 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
424
425 trace
426 (p_msg => 'BEGIN of validate_ccids'
427 ,p_level => C_LEVEL_PROCEDURE
428 ,p_module => l_log_module);
429
430 END IF;
431
432 SELECT COUNT(1)
433 INTO l_db_cnt
434 FROM xla_tb_balances_gt
435 WHERE definition_code = p_definition_rec.definition_code;
436
437 IF l_db_cnt = 0 THEN
438
439
440 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
441 trace
442 (p_msg => 'END of validate_ccids - No ccid'
443 ,p_level => C_LEVEL_PROCEDURE
444 ,p_module => l_log_module);
445 END IF;
446
447 --
448 -- No ccid in the GT table
449 --
450 IF g_mode = C_CREATE_MODE THEN
451
452 fnd_message.set_name('XLA','XLA_TB_NO_CCID_IN_GT');
453 fnd_msg_pub.add;
454
455 RETURN FALSE;
456
457 ELSE
458
459 RETURN TRUE;
460
461 END IF;
462
463 ELSE
464
465 --
466 -- Select invalid ccids
467 --
468 SELECT code_combination_id
469 BULK COLLECT
470 INTO t_array_ccid
471 FROM xla_tb_balances_gt
472 WHERE code_combination_id NOT IN
473 (SELECT code_combination_id
474 FROM gl_code_combinations gcc
475 ,gl_ledgers gld
476 WHERE gcc.chart_of_accounts_id = gld.chart_of_accounts_id
477 AND gld.ledger_id = p_definition_rec.ledger_id);
478
479 IF t_array_ccid.COUNT = 0 THEN
480
481 --
482 -- No invalid ccids. Return TRUE.
483 --
484
485 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
486 trace
487 (p_msg => 'END of validate_ccids - Ccids are valid'
488 ,p_level => C_LEVEL_PROCEDURE
489 ,p_module => l_log_module);
490 END IF;
491
492 RETURN TRUE;
493
494 ELSE
495
496 FOR i IN t_array_ccid.FIRST .. t_array_ccid.LAST LOOP
497
498 fnd_message.set_name('XLA','XLA_TB_INVALID_CCID');
499 fnd_message.set_token('CCID',t_array_ccid(i));
500 fnd_msg_pub.add;
501
502 END LOOP;
503
504 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
505 trace
506 (p_msg => 'END of validate_ccids - Invalid ccids'
507 ,p_level => C_LEVEL_PROCEDURE
508 ,p_module => l_log_module);
509 END IF;
510
511 RETURN FALSE;
512
513 END IF;
514
515 END IF;
516
517 EXCEPTION
518 WHEN xla_exceptions_pkg.application_exception THEN
519 RAISE;
520 WHEN OTHERS THEN
521 xla_exceptions_pkg.raise_message
522 (p_location => 'xla_tb_balance_pkg.validate_ccids');
523 END validate_ccids;
524
525 /*======================================================================+
529 | Check_Required_Params |
526 | |
527 | Private Procedure |
528 | |
530 | |
531 | Check if required parameters are passed in |
532 | - p_definition_rec.definition_code |
533 | - p_definition_rec.name |
534 | - p_definition_rec.ledger_id |
535 | - p_definition_rec.balance_side_code |
536 | - p_je_source_name |
537 | - p_mode |
538 +======================================================================*/
539 FUNCTION validate_required_params
540 (p_definition_rec IN r_definition
541 ,p_je_source_name IN VARCHAR2
542 ,p_gl_date_from IN DATE
543 ,p_gl_date_to IN DATE
544 ,p_mode IN VARCHAR2)
545 RETURN BOOLEAN IS
546
547 l_err_found BOOLEAN := FALSE;
548
549 l_log_module VARCHAR2(240);
550
551 BEGIN
552
553 IF g_log_enabled THEN
554 l_log_module := C_DEFAULT_MODULE||'.validate_required_params';
555 END IF;
556 --
557 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
558
559 trace
560 (p_msg => 'BEGIN of validate_required_params'
561 ,p_level => C_LEVEL_PROCEDURE
562 ,p_module => l_log_module);
563
564 END IF;
565
566 --
567 -- Mandatory parameters check
568 --
569 IF p_definition_rec.definition_code IS NULL THEN
570
571 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
572 fnd_message.set_token('PARAMETER','p_definition_code');
573
574 fnd_msg_pub.add;
575 l_err_found := TRUE;
576
577 END IF;
578
579 IF p_definition_rec.name IS NULL THEN
580
581 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
582 fnd_message.set_token('PARAMETER','p_definition_name');
583
584 fnd_msg_pub.add;
585 l_err_found := TRUE;
586
587 END IF;
588
589 IF p_definition_rec.ledger_id IS NULL THEN
590
591 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
592 fnd_message.set_token('PARAMETER','p_ledger_id');
593
594 fnd_msg_pub.add;
595 l_err_found := TRUE;
596
597 END IF;
598
599 IF p_je_source_name IS NULL THEN
600
601 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
602 fnd_message.set_token('PARAMETER','p_je_source_name');
603
604 fnd_msg_pub.add;
605 l_err_found := TRUE;
606
607 END IF;
608
609 IF g_mode = C_UPDATE_MODE THEN
610
611 IF p_gl_date_from IS NULL THEN
612
613 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
614 fnd_message.set_token('PARAMETER','p_gl_date_from');
615
616 fnd_msg_pub.add;
617 l_err_found := TRUE;
618
619 END IF;
620
621 IF p_gl_date_to IS NULL THEN
622
623 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
624 fnd_message.set_token('PARAMETER','p_gl_date_to');
625
626 fnd_msg_pub.add;
627 l_err_found := TRUE;
628
629 END IF;
630
631 END IF;
632
633 IF p_mode IS NULL THEN
634
635 fnd_message.set_name('XLA','XLA_COMMON_NULL_PARAM');
636 fnd_message.set_token('PARAMETER','p_mode');
637
638 fnd_msg_pub.add;
639 l_err_found := TRUE;
640
641 ELSIF p_mode NOT IN (C_CREATE_MODE, C_UPDATE_MODE) THEN
642
643 fnd_message.set_name('XLA','XLA_COMMON_INVALID_PARAM2');
644 fnd_message.set_token('PARAMETER_VALUE',p_mode);
645 fnd_message.set_token('PARAMETER','p_mode');
646
647 fnd_msg_pub.add;
648 l_err_found := TRUE;
649
650 END IF;
651
652 IF l_err_found = FALSE THEN
653
654 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
655 trace
656 (p_msg => 'END of validate_required_params - no error found'
657 ,p_level => C_LEVEL_PROCEDURE
658 ,p_module => l_log_module);
659 END IF;
660
661 RETURN TRUE;
662
663 ELSE
664
665 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
666 trace
667 (p_msg => 'END of validate_required_params - Error found'
668 ,p_level => C_LEVEL_PROCEDURE
669 ,p_module => l_log_module);
670 END IF;
671
672 RETURN FALSE;
673
674 END IF;
675
676 EXCEPTION
677 WHEN xla_exceptions_pkg.application_exception THEN
678 RAISE;
679 WHEN OTHERS THEN
680 xla_exceptions_pkg.raise_message
681 (p_location => 'xla_tb_balance_pkg.validate_required_params');
682
683 END validate_required_params;
684
685 /*======================================================================+
689 | Validate_Parameters |
686 | |
687 | Private Procedure |
688 | |
690 | |
691 | Validate input parameters |
692 | |
693 +======================================================================*/
694 FUNCTION validate_parameters
695 (p_definition_rec IN r_definition
696 ,p_je_source_name IN VARCHAR2
697 ,p_gl_date_from IN DATE
698 ,p_gl_date_to IN DATE
699 ,p_mode IN VARCHAR2)
700 RETURN BOOLEAN IS
701
702 l_val_defn BOOLEAN := TRUE;
703 l_val_ledger BOOLEAN := TRUE;
704 l_val_je_sources BOOLEAN := TRUE;
705 l_val_ccids BOOLEAN := TRUE;
706
707 l_error_param VARCHAR2(30);
708
709 l_log_module VARCHAR2(240);
710
711 BEGIN
712
713 IF g_log_enabled THEN
714 l_log_module := C_DEFAULT_MODULE||'.validate_parameters';
715 END IF;
716 --
717 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
718
719 trace
720 (p_msg => 'BEGIN of validate_parameters'
721 ,p_level => C_LEVEL_PROCEDURE
722 ,p_module => l_log_module);
723
724 END IF;
725
726 --
727 -- Check if required parameters are passed in
728 --
729 IF NOT validate_required_params
730 (p_definition_rec => p_definition_rec
731 ,p_je_source_name => p_je_source_name
732 ,p_gl_date_from => p_gl_date_from
733 ,p_gl_date_to => p_gl_date_to
734 ,p_mode => p_mode)
735 THEN
736
737 RETURN FALSE;
738
739 END IF;
740
741 --
742 -- Check definition exists in db
743 --
744 IF NOT validate_definition
745 (p_definition_rec => p_definition_rec) THEN
746
747 l_val_defn := FALSE;
748
749 END IF;
750
751 --
752 -- Check ledger exists in db
753 --
754 IF NOT validate_ledger
755 (p_ledger_id => p_definition_rec.ledger_id) THEN
756
757 l_val_ledger := FALSE;
758
759 END IF;
760
761 --
762 -- Check je source exists in db
763 --
764 IF NOT validate_je_source(p_je_source_name => p_je_source_name) THEN
765
766 l_val_je_sources := FALSE;
767
768 END IF;
769
770 --
771 -- Return results
772 --
773 IF NOT l_val_defn OR NOT l_val_ledger
774 OR NOT l_val_je_sources OR NOT l_val_ccids
775 THEN
776
777 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
778 trace
779 (p_msg => 'END of validate_parameters - Error found'
780 ,p_level => C_LEVEL_PROCEDURE
781 ,p_module => l_log_module);
782 END IF;
783
784 RETURN FALSE;
785
786 ELSE
787
788 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
789 trace
790 (p_msg => 'END of validate_parameters - No error found'
791 ,p_level => C_LEVEL_PROCEDURE
792 ,p_module => l_log_module);
793 END IF;
794
795 RETURN TRUE;
796
797 END IF;
798
799 EXCEPTION
800 WHEN xla_exceptions_pkg.application_exception THEN
801 RAISE;
802 WHEN OTHERS THEN
803 xla_exceptions_pkg.raise_message
804 (p_location => 'xla_tb_balance_pkg.validate_parameters');
805
806 END validate_parameters;
807
808
809 /*======================================================================+
810 | |
811 | Private Procedure |
812 | |
813 | <procedure name> |
814 | |
815 | <Description of the procedure> |
816 | |
817 +======================================================================*/
818 PROCEDURE create_definition
819 (p_definition_rec IN r_definition)
820 IS
821
822 C_ENABLED_FLAG CONSTANT VARCHAR2(1) := 'Y';
823 C_DEFINED_BY_CODE CONSTANT VARCHAR2(30) := 'FLEXFIELD';
824 C_DEFN_STATUS_CODE CONSTANT VARCHAR2(30) := 'NEW';
825 C_DEFN_OWNER_CODE CONSTANT VARCHAR2(30) := 'S';
826
827 l_rowid ROWID;
828
829 l_creation_date DATE;
830 l_last_update_date DATE;
831 l_created_by NUMBER(15);
832 l_last_updated_by NUMBER(15);
833 l_last_update_login NUMBER(15);
834
835 l_log_module VARCHAR2(240);
836
837 BEGIN
838
839 IF g_log_enabled THEN
840 l_log_module := C_DEFAULT_MODULE||'.create_definition';
841 END IF;
842 --
843 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
844
845 trace
849
846 (p_msg => 'BEGIN of create_definition'
847 ,p_level => C_LEVEL_PROCEDURE
848 ,p_module => l_log_module);
850 END IF;
851
852 l_creation_date := sysdate;
853 l_last_update_date := sysdate;
854 l_created_by := xla_environment_pkg.g_usr_id;
855 l_last_updated_by := xla_environment_pkg.g_usr_id;
856 l_last_update_login := xla_environment_pkg.g_login_id;
857
858 xla_tb_definition_pvt.insert_row
859 (p_rowid => l_rowid
860 ,p_definition_code => p_definition_rec.definition_code
861 ,p_object_version_number => C_OVN
862 ,p_ledger_id => p_definition_rec.ledger_id
863 ,p_enabled_flag => C_ENABLED_FLAG
864 ,p_balance_side_code => NVL(p_definition_rec.balance_side_code,'C')
865 ,p_defined_by_code => C_DEFINED_BY_CODE
866 ,p_definition_status_code => C_DEFN_STATUS_CODE
867 ,p_name => SUBSTRB(p_definition_rec.NAME,1,80)
868 ,p_description => p_definition_rec.description
869 ,p_defn_owner_code => C_DEFN_OWNER_CODE
870 ,p_creation_date => l_creation_date
871 ,p_created_by => l_created_by
872 ,p_last_update_date => l_last_update_date
873 ,p_last_updated_by => l_last_updated_by
874 ,p_last_update_login => l_last_update_login);
875
876 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
877 trace
878 (p_msg => 'END of create_definition'
879 ,p_level => C_LEVEL_PROCEDURE
880 ,p_module => l_log_module);
881 END IF;
882
883 EXCEPTION
884 WHEN xla_exceptions_pkg.application_exception THEN
885 RAISE;
886 WHEN OTHERS THEN
887 xla_exceptions_pkg.raise_message
888 (p_location => 'xla_tb_balance_pkg.create_definition');
889 END create_definition;
890
891 /*======================================================================+
892 | |
893 | Private Procedure |
894 | |
895 | Create_Je_Source |
896 | |
897 | Create Journal Source |
898 | |
899 +======================================================================*/
900 PROCEDURE create_je_source
901 (p_definition_code IN VARCHAR2
902 ,p_je_source_name IN VARCHAR2)
903 IS
904
905 l_log_module VARCHAR2(240);
906
907 l_creation_date DATE;
908 l_last_update_date DATE;
909 l_created_by NUMBER(15);
910 l_last_updated_by NUMBER(15);
911 l_last_update_login NUMBER(15);
912
913 BEGIN
914
915 IF g_log_enabled THEN
916 l_log_module := C_DEFAULT_MODULE||'.create_je_source';
917 END IF;
918 --
919 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
920
921 trace
922 (p_msg => 'BEGIN of create_je_source'
923 ,p_level => C_LEVEL_PROCEDURE
924 ,p_module => l_log_module);
925
926 END IF;
927
928 l_creation_date := sysdate;
929 l_last_update_date := sysdate;
930 l_created_by := xla_environment_pkg.g_usr_id;
931 l_last_updated_by := xla_environment_pkg.g_usr_id;
932 l_last_update_login := xla_environment_pkg.g_login_id;
933
934 INSERT INTO xla_tb_defn_je_sources
935 (definition_code
936 ,je_source_name
937 ,object_version_number
938 ,owner_code
939 ,creation_date
940 ,created_by
941 ,last_update_date
942 ,last_updated_by
943 ,last_update_login)
944 SELECT
945 p_definition_code
946 ,p_je_source_name
947 ,C_OVN
948 ,C_OWNER_ORACLE
949 ,l_creation_date
950 ,l_created_by
951 ,l_last_update_date
952 ,l_last_updated_by
953 ,l_last_update_login
954 FROM dual
955 WHERE NOT EXISTS (
956 SELECT 1
957 FROM xla_tb_defn_je_sources
958 WHERE definition_code = p_definition_code
959 AND je_source_name = p_je_source_name);
960
961 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
962 trace
963 (p_msg => 'END of create_je_source'
964 ,p_level => C_LEVEL_PROCEDURE
965 ,p_module => l_log_module);
966 END IF;
967
968 EXCEPTION
969 WHEN xla_exceptions_pkg.application_exception THEN
970 RAISE;
971 WHEN OTHERS THEN
972 xla_exceptions_pkg.raise_message
973 (p_location => 'xla_tb_balance_pkg.create_je_source');
974 END create_je_source;
975
976 /*======================================================================+
977 | |
978 | Private Procedure |
979 | |
980 | Create_Defn_Details |
984 +======================================================================*/
981 | |
982 | Create report definition details |
983 | |
985 PROCEDURE create_defn_details
986 (p_definition_code IN VARCHAR2)
987 IS
988
989 l_log_module VARCHAR2(240);
990
991 l_creation_date DATE;
992 l_last_update_date DATE;
993 l_created_by NUMBER(15);
994 l_last_updated_by NUMBER(15);
995 l_last_update_login NUMBER(15);
996
997 BEGIN
998
999 IF g_log_enabled THEN
1000 l_log_module := C_DEFAULT_MODULE||'.create_defn_details';
1001 END IF;
1002 --
1003 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1004
1005 trace
1006 (p_msg => 'BEGIN of create_defn_details'
1007 ,p_level => C_LEVEL_PROCEDURE
1008 ,p_module => l_log_module);
1009
1010 END IF;
1011
1012 l_creation_date := sysdate;
1013 l_last_update_date := sysdate;
1014 l_created_by := xla_environment_pkg.g_usr_id;
1015 l_last_updated_by := xla_environment_pkg.g_usr_id;
1016 l_last_update_login := xla_environment_pkg.g_login_id;
1017
1018 --
1019 -- As record elements (e.g. p_balance_tbl(i).balance_date) is not allowed
1020 -- in forall statements, reassgin parameters to local variables
1021 --
1022 INSERT INTO xla_tb_defn_details
1023 (definition_detail_id
1024 ,object_version_number
1025 ,definition_code
1026 ,flexfield_segment_code
1027 ,segment_value_from
1028 ,segment_value_to
1029 ,code_combination_id
1030 ,owner_code
1031 ,balance_date
1032 ,balance_amount
1033 ,creation_date
1034 ,created_by
1035 ,last_update_date
1036 ,last_updated_by
1037 ,last_update_login)
1038 SELECT
1039 xla_tb_defn_details_s.NEXTVAL
1040 ,C_OVN
1041 ,p_definition_code
1042 ,NULL -- flexfield segment code
1043 ,NULL -- segment value from
1044 ,NULL -- segment value to
1045 ,code_combination_id
1046 ,C_OWNER_ORACLE
1047 ,balance_date
1048 ,balance_amount
1049 ,l_creation_date
1050 ,l_created_by
1051 ,l_last_update_date
1052 ,l_last_updated_by
1053 ,l_last_update_login
1054 FROM xla_tb_balances_gt
1055 WHERE definition_code = p_definition_code;
1056
1057 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1058 trace
1059 (p_msg => 'END of create_defn_details'
1060 ,p_level => C_LEVEL_PROCEDURE
1061 ,p_module => l_log_module);
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN xla_exceptions_pkg.application_exception THEN
1066 RAISE;
1067 WHEN OTHERS THEN
1068 xla_exceptions_pkg.raise_message
1069 (p_location => 'xla_tb_balance_pkg.create_defn_details');
1070 END create_defn_details;
1071
1072 PROCEDURE update_definition
1073 (p_definition_rec IN r_definition)
1074 IS
1075
1076 C_ENABLED_FLAG CONSTANT VARCHAR2(1) := 'Y';
1077 C_DEFINED_BY_CODE CONSTANT VARCHAR2(30) := 'FLEXFIELD';
1078 C_DEFN_STATUS_CODE CONSTANT VARCHAR2(30) := 'NEW';
1079 C_DEFN_OWNER_CODE CONSTANT VARCHAR2(30) := 'S';
1080
1081 l_ovn NUMBER;
1082 l_ledger_id xla_tb_definitions_b.ledger_id%TYPE;
1083 l_enabled_flag xla_tb_definitions_b.enabled_flag%TYPE;
1084 l_balance_side_code xla_tb_definitions_b.balance_side_code%TYPE;
1085 l_defined_by_code xla_tb_definitions_b.defined_by_code%TYPE;
1086 l_definition_status_code xla_tb_definitions_b.definition_status_code%TYPE;
1087 l_owner_code xla_tb_definitions_b.owner_code%TYPE;
1088 l_name xla_tb_definitions_vl.NAME%TYPE;
1089 l_description xla_tb_definitions_vl.description%TYPE;
1090
1091 l_creation_date DATE;
1092 l_last_update_date DATE;
1093 l_created_by NUMBER(15);
1094 l_last_updated_by NUMBER(15);
1095 l_last_update_login NUMBER(15);
1096
1097 l_log_module VARCHAR2(240);
1098
1099 BEGIN
1100
1101 IF g_log_enabled THEN
1102 l_log_module := C_DEFAULT_MODULE||'.update_definition';
1103 END IF;
1104 --
1105 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1106
1107 trace
1108 (p_msg => 'BEGIN of update_definition'
1109 ,p_level => C_LEVEL_PROCEDURE
1110 ,p_module => l_log_module);
1111
1112 END IF;
1113
1114 l_creation_date := sysdate;
1115 l_last_update_date := sysdate;
1116 l_created_by := xla_environment_pkg.g_usr_id;
1117 l_last_updated_by := xla_environment_pkg.g_usr_id;
1118 l_last_update_login := xla_environment_pkg.g_login_id;
1119
1120 SELECT object_version_number
1121 ,NVL(p_definition_rec.ledger_id,ledger_id)
1122 ,enabled_flag
1123 ,NVL(p_definition_rec.balance_side_code,balance_side_code)
1124 ,defined_by_code
1125 ,definition_status_code
1126 ,owner_code
1127 ,NVL(p_definition_rec.NAME,NAME)
1128 ,NVL(p_definition_rec.description,description)
1129 INTO l_ovn
1130 ,l_ledger_id
1131 ,l_enabled_flag
1132 ,l_balance_side_code
1133 ,l_defined_by_code
1134 ,l_definition_status_code
1135 ,l_owner_code
1136 ,l_name
1137 ,l_description
1138 FROM xla_tb_definitions_vl
1139 WHERE definition_code = p_definition_rec.definition_code
1140 AND defined_by_code = C_DEFINED_BY_CODE
1141 AND owner_code = C_DEFN_OWNER_CODE
1142 FOR UPDATE;
1143
1144 xla_tb_definition_pvt.update_row
1145 (p_definition_code => p_definition_rec.definition_code
1146 ,p_object_version_number => l_ovn
1147 ,p_ledger_id => p_definition_rec.ledger_id
1148 ,p_enabled_flag => C_ENABLED_FLAG
1149 ,p_balance_side_code => NVL(p_definition_rec.balance_side_code,'C')
1150 ,p_defined_by_code => C_DEFINED_BY_CODE
1151 ,p_definition_status_code => C_DEFN_STATUS_CODE
1152 ,p_name => p_definition_rec.name
1153 ,p_description => p_definition_rec.description
1154 ,p_defn_owner_code => C_DEFN_OWNER_CODE
1155 ,p_last_update_date => l_last_update_date
1156 ,p_last_updated_by => l_last_updated_by
1157 ,p_last_update_login => l_last_update_login);
1158
1159 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1160 trace
1161 (p_msg => 'END of update_definition'
1162 ,p_level => C_LEVEL_PROCEDURE
1163 ,p_module => l_log_module);
1164 END IF;
1165
1166 EXCEPTION
1167 WHEN NO_DATA_FOUND THEN
1168 NULL;
1169 WHEN xla_exceptions_pkg.application_exception THEN
1170 RAISE;
1171 WHEN OTHERS THEN
1172 xla_exceptions_pkg.raise_message
1173 (p_location => 'xla_tb_balance_pkg.create_definition');
1174 END update_definition;
1175
1176 /*======================================================================+
1177 | |
1178 | Private Procedure |
1179 | |
1180 | Update_Je_Source |
1181 | |
1182 | Update Journal Source |
1183 | |
1184 +======================================================================*/
1185 PROCEDURE update_je_source
1186 (p_definition_code IN VARCHAR2
1187 ,p_je_source_name IN VARCHAR2)
1188 IS
1189
1190
1191 l_db_cnt PLS_INTEGER;
1192
1193 --
1194 -- WHO column information
1195 --
1196 l_last_update_date DATE;
1197 l_last_updated_by NUMBER(15);
1198 l_last_update_login NUMBER(15);
1199
1200 l_log_module VARCHAR2(240);
1201
1202 BEGIN
1203
1204 IF g_log_enabled THEN
1205 l_log_module := C_DEFAULT_MODULE||'.update_je_source';
1206 END IF;
1207 --
1208 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1209
1210 trace
1211 (p_msg => 'BEGIN of update_je_source'
1212 ,p_level => C_LEVEL_PROCEDURE
1213 ,p_module => l_log_module);
1214
1215 END IF;
1216
1217
1218 l_last_update_date := sysdate;
1219 l_last_updated_by := xla_environment_pkg.g_usr_id;
1220 l_last_update_login := xla_environment_pkg.g_login_id;
1221
1222 SELECT COUNT(1)
1223 INTO l_db_cnt
1224 FROM xla_subledgers xs
1225 ,gl_je_sources gs
1226 WHERE xs.je_source_name = gs.je_source_name
1230
1227 AND xs.je_source_name = p_je_source_name;
1228
1229 IF l_db_cnt = 0 THEN
1231 create_je_source
1232 (p_definition_code => p_definition_code
1233 ,p_je_source_name => p_je_source_name);
1234
1235 END IF;
1236
1237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1238 trace
1239 (p_msg => 'END of update_je_source'
1240 ,p_level => C_LEVEL_PROCEDURE
1241 ,p_module => l_log_module);
1242 END IF;
1243
1244 EXCEPTION
1245 WHEN xla_exceptions_pkg.application_exception THEN
1246 RAISE;
1247 WHEN OTHERS THEN
1248 xla_exceptions_pkg.raise_message
1249 (p_location => 'xla_tb_balance_pkg.update_je_sources');
1250 END update_je_source;
1251
1252 /*======================================================================+
1253 | |
1254 | Private Procedure |
1255 | |
1256 | Update_Defn_Details |
1257 | |
1258 | Update Report Definition Details |
1259 | |
1260 +======================================================================*/
1261 PROCEDURE update_defn_details
1262 (p_definition_code IN VARCHAR2)
1263 IS
1264
1265 --
1266 -- Variables for WHO column information
1267 --
1268 l_last_update_date DATE;
1269 l_last_updated_by NUMBER(15);
1270 l_last_update_login NUMBER(15);
1271
1272 l_log_module VARCHAR2(240);
1273
1274 BEGIN
1275
1276 IF g_log_enabled THEN
1277 l_log_module := C_DEFAULT_MODULE||'.update_defn_details';
1278 END IF;
1279 --
1280 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1281
1282 trace
1283 (p_msg => 'BEGIN of update_defn_details'
1284 ,p_level => C_LEVEL_PROCEDURE
1285 ,p_module => l_log_module);
1286
1287 END IF;
1288
1289 l_last_update_date := sysdate;
1290 l_last_updated_by := xla_environment_pkg.g_usr_id;
1291 l_last_update_login := xla_environment_pkg.g_login_id;
1292
1293 MERGE INTO xla_tb_defn_details dt
1294 USING (SELECT code_combination_id
1295 ,balance_date
1296 ,balance_amount
1297 FROM xla_tb_balances_gt
1298 WHERE definition_code = p_definition_code) gt
1299
1300 ON (dt.code_combination_id = gt.code_combination_id)
1301
1302 WHEN MATCHED THEN
1303 UPDATE SET dt.object_version_number = dt.object_version_number + 1
1304 ,dt.balance_date = gt.balance_date
1305 ,dt.balance_amount = gt.balance_amount
1306 WHERE dt.balance_date <> gt.balance_date
1307 OR dt.balance_amount <> gt.balance_amount
1308
1309 WHEN NOT MATCHED THEN
1310 INSERT (definition_detail_id
1311 ,object_version_number
1312 ,definition_code
1313 ,flexfield_segment_code
1314 ,segment_value_from
1315 ,segment_value_to
1316 ,code_combination_id
1317 ,owner_code
1318 ,balance_date
1319 ,balance_amount
1320 ,creation_date
1321 ,created_by
1322 ,last_update_date
1323 ,last_updated_by
1324 ,last_update_login)
1325 VALUES (xla_tb_defn_details_s.NEXTVAL
1326 ,C_OVN
1327 ,p_definition_code
1328 ,NULL
1329 ,NULL
1330 ,NULL
1331 ,gt.code_combination_id
1332 ,C_OWNER_ORACLE
1333 ,gt.balance_date
1334 ,gt.balance_amount
1335 ,l_last_update_date
1336 ,l_last_updated_by
1337 ,l_last_update_date
1338 ,l_last_updated_by
1339 ,l_last_update_login);
1340
1341
1342 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1343 trace
1344 (p_msg => 'END of update_defn_details'
1345 ,p_level => C_LEVEL_PROCEDURE
1346 ,p_module => l_log_module);
1347 END IF;
1348
1349 END update_defn_details;
1350
1351 --============================================================================
1352 --
1353 -- Private Procedures
1354 -- The procedure inserts a row into the xla_gl_ledgers table
1355 -- to store default values for ledger attributes related to the Trial Balance.
1356 --============================================================================
1357
1358 PROCEDURE create_ledger
1359 ( p_ledger_id IN NUMBER
1360 ) IS
1361
1362 l_log_module VARCHAR2(240);
1363 l_api_name CONSTANT VARCHAR2(30) := 'create_ledger';
1364 l_ledger_id gl_ledgers.ledger_id%TYPE;
1365 BEGIN
1366
1367 IF g_log_enabled THEN
1371 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1368 l_log_module := C_DEFAULT_MODULE||'.create_ledger';
1369 END IF;
1370 --
1372
1373 trace
1374 (p_msg => 'BEGIN of create_ledger'
1375 ,p_level => C_LEVEL_PROCEDURE
1376 ,p_module => l_log_module);
1377
1378 END IF;
1379
1380 BEGIN
1381 SELECT ledger_id
1382 INTO l_ledger_id
1383 FROM xla_gl_ledgers
1384 WHERE ledger_id = p_ledger_id;
1385 EXCEPTION
1386 WHEN NO_DATA_FOUND THEN
1387 INSERT INTO xla_gl_ledgers
1388 ( LEDGER_ID
1389 ,OBJECT_VERSION_NUMBER
1390 ,WORK_UNIT
1391 ,NUM_OF_WORKERS
1392 ,CREATION_DATE
1393 ,CREATED_BY
1394 ,LAST_UPDATE_DATE
1395 ,LAST_UPDATED_BY
1396 ,LAST_UPDATE_LOGIN
1397 )
1398 VALUES
1399 ( p_ledger_id
1400 ,1
1401 ,5000
1402 ,1
1403 ,SYSDATE
1404 ,xla_environment_pkg.g_usr_id
1405 ,SYSDATE
1406 ,xla_environment_pkg.g_usr_id
1407 ,xla_environment_pkg.g_login_id
1408 );
1409 WHEN OTHERS THEN
1410 RAISE;
1411
1412 END;
1413
1414 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1415
1416 trace
1417 (p_msg => 'END of create_ledger'
1418 ,p_level => C_LEVEL_PROCEDURE
1419 ,p_module => l_log_module);
1420
1421 END IF;
1422 EXCEPTION
1423 WHEN xla_exceptions_pkg.application_exception THEN
1424 RAISE FND_API.G_EXC_ERROR;
1425 WHEN OTHERS THEN
1426 FND_MSG_PUB.Add_Exc_Msg
1427 (p_pkg_name => C_PACKAGE_NAME
1428 ,p_procedure_name => l_api_name);
1429
1430 xla_exceptions_pkg.raise_message
1431 (p_location => 'xla_tb_balance_pkg.create_ledger');
1432
1433 END create_ledger;
1434 --============================================================================
1435 --
1436 -- Public Procedures
1437 --
1438 --============================================================================
1439 PROCEDURE create_balances
1440 (p_api_version IN NUMBER
1441 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1442 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1443 ,x_return_status OUT NOCOPY VARCHAR2
1444 ,x_msg_count OUT NOCOPY NUMBER
1445 ,x_msg_data OUT NOCOPY VARCHAR2
1446 ,p_definition_rec IN r_definition
1447 ,p_je_source_name IN VARCHAR2)
1448 IS
1449
1450 l_log_module VARCHAR2(240);
1451 l_api_name CONSTANT VARCHAR2(30) := 'create_balances';
1452
1453
1454 BEGIN
1455
1456 IF g_log_enabled THEN
1457 l_log_module := C_DEFAULT_MODULE||'.create_balances';
1458 END IF;
1459 --
1460 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1461
1462 trace
1463 (p_msg => 'BEGIN of create_balances'
1464 ,p_level => C_LEVEL_PROCEDURE
1465 ,p_module => l_log_module);
1466
1467 END IF;
1468
1469 create_definition
1470 (p_definition_rec => p_definition_rec);
1471
1472 create_ledger
1473 (p_ledger_id => p_definition_rec.ledger_id);
1474
1475 create_je_source
1476 (p_definition_code => p_definition_rec.definition_code
1477 ,p_je_source_name => p_je_source_name);
1478
1479 create_defn_details
1480 (p_definition_code => p_definition_rec.definition_code);
1481
1482 IF FND_API.To_Boolean( p_commit ) THEN
1483
1484 COMMIT WORK;
1485
1486 END IF;
1487
1488 xla_tb_data_manager_pvt.add_partition
1489 (p_definition_code => p_definition_rec.definition_code);
1490
1491 --
1492 -- Pass in process mode 'CHANGED' as data manager needs to
1493 -- create segment ranges in xla_tb_data_manager_pvt.upload.
1494 --
1495 submit_data_manager
1496 (p_definition_rec => p_definition_rec
1497 ,p_je_source_name => p_je_source_name
1498 ,p_gl_date_from => NULL
1499 ,p_gl_date_to => NULL
1500 ,p_process_mode => 'CHANGED');
1501
1502
1503 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1504 trace
1505 (p_msg => 'END of create_balance'
1506 ,p_level => C_LEVEL_PROCEDURE
1507 ,p_module => l_log_module);
1508 END IF;
1509
1510 x_return_status := FND_API.G_RET_STS_SUCCESS;
1511
1512 EXCEPTION
1513 WHEN xla_exceptions_pkg.application_exception THEN
1514
1515 RAISE FND_API.G_EXC_ERROR;
1516
1517 WHEN OTHERS THEN
1518
1519 FND_MSG_PUB.Add_Exc_Msg
1520 (p_pkg_name => C_PACKAGE_NAME
1521 ,p_procedure_name => l_api_name);
1522
1523 xla_exceptions_pkg.raise_message
1524 (p_location => 'xla_tb_balance_pkg.create_balances');
1525
1526 END create_balances;
1527
1528 PROCEDURE update_balances
1529 (p_api_version IN NUMBER
1533 ,x_return_status OUT NOCOPY VARCHAR2
1530 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1531 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1532 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1534 ,x_msg_count OUT NOCOPY NUMBER
1535 ,x_msg_data OUT NOCOPY VARCHAR2
1536 ,p_definition_rec IN r_definition
1537 ,p_je_source_name IN VARCHAR2
1538 ,p_gl_date_from IN DATE
1539 ,p_gl_date_to IN DATE)
1540 IS
1541
1542 l_log_module VARCHAR2(240);
1543 l_api_name CONSTANT VARCHAR2(30) := 'update_balances';
1544
1545
1546 BEGIN
1547
1548 IF g_log_enabled THEN
1549 l_log_module := C_DEFAULT_MODULE||'.update_balances';
1550 END IF;
1551 --
1552 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1553
1554 trace
1555 (p_msg => 'BEGIN of update_balances'
1556 ,p_level => C_LEVEL_PROCEDURE
1557 ,p_module => l_log_module);
1558
1559 END IF;
1560
1561 update_definition
1562 (p_definition_rec => p_definition_rec);
1563
1564 update_je_source
1565 (p_definition_code => p_definition_rec.definition_code
1566 ,p_je_source_name => p_je_source_name);
1567
1568 update_defn_details
1569 (p_definition_code => p_definition_rec.definition_code);
1570
1571 submit_data_manager
1572 (p_definition_rec => p_definition_rec
1573 ,p_je_source_name => p_je_source_name
1574 ,p_gl_date_from => p_gl_date_from
1575 ,p_gl_date_to => p_gl_date_to
1576 ,p_process_mode => NULL);
1577
1578 IF FND_API.To_Boolean( p_commit ) THEN
1579
1580 COMMIT WORK;
1581
1582 END IF;
1583
1584 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1585 trace
1586 (p_msg => 'END of update_balances'
1587 ,p_level => C_LEVEL_PROCEDURE
1588 ,p_module => l_log_module);
1589 END IF;
1590
1591 x_return_status := FND_API.G_RET_STS_SUCCESS;
1592
1593 EXCEPTION
1594 WHEN xla_exceptions_pkg.application_exception THEN
1595
1596 RAISE FND_API.G_EXC_ERROR;
1597
1598 WHEN OTHERS THEN
1599 xla_exceptions_pkg.raise_message
1600 (p_location => 'xla_tb_balance_pkg.update_balances');
1601 END update_balances;
1602
1603 PROCEDURE upload_balances
1604 (p_api_version IN NUMBER
1605 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1606 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1607 ,x_return_status OUT NOCOPY VARCHAR2
1608 ,x_msg_count OUT NOCOPY NUMBER
1609 ,x_msg_data OUT NOCOPY VARCHAR2
1610 ,p_definition_code IN VARCHAR2
1611 ,p_definition_name IN VARCHAR2
1612 ,p_definition_desc IN VARCHAR2
1613 ,p_ledger_id IN NUMBER
1614 ,p_balance_side_code IN VARCHAR2
1615 ,p_je_source_name IN VARCHAR2
1616 ,p_gl_date_from IN DATE
1617 ,p_gl_date_to IN DATE
1618 ,p_mode IN VARCHAR2
1619 )
1620 IS
1621
1622 l_log_module VARCHAR2(240);
1623 l_api_name CONSTANT VARCHAR2(30) := 'upload_balances';
1624
1625 l_definition_rec r_definition;
1626
1627 BEGIN
1628
1629 IF g_log_enabled THEN
1630 l_log_module := C_DEFAULT_MODULE||'.upload_balances';
1631 END IF;
1632 --
1633 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1634
1635 trace
1636 (p_msg => 'BEGIN of upload_balances'
1637 ,p_level => C_LEVEL_PROCEDURE
1638 ,p_module => l_log_module);
1639
1640 trace
1641 (p_msg => 'p_definition_code = '||p_definition_code
1642 ,p_level => C_LEVEL_PROCEDURE
1643 ,p_module => l_log_module);
1644
1645 trace
1646 (p_msg => 'p_definition_name = '||p_definition_name
1647 ,p_level => C_LEVEL_PROCEDURE
1648 ,p_module => l_log_module);
1649
1650 trace
1651 (p_msg => 'p_definition_desc = '||p_definition_desc
1652 ,p_level => C_LEVEL_PROCEDURE
1653 ,p_module => l_log_module);
1654
1655 trace
1656 (p_msg => 'p_ledger_id = '||p_ledger_id
1657 ,p_level => C_LEVEL_PROCEDURE
1658 ,p_module => l_log_module);
1659
1660 trace
1661 (p_msg => 'p_balance_side_code = '||p_balance_side_code
1662 ,p_level => C_LEVEL_PROCEDURE
1663 ,p_module => l_log_module);
1664
1665 trace
1666 (p_msg => 'p_je_source_name = '||p_je_source_name
1667 ,p_level => C_LEVEL_PROCEDURE
1668 ,p_module => l_log_module);
1669
1670 trace
1671 (p_msg => 'p_gl_date_from = '||p_gl_date_from
1672 ,p_level => C_LEVEL_PROCEDURE
1673 ,p_module => l_log_module);
1674
1675 trace
1676 (p_msg => 'p_gl_date_to = '||p_gl_date_to
1677 ,p_level => C_LEVEL_PROCEDURE
1678 ,p_module => l_log_module);
1679
1680 trace
1681 (p_msg => 'p_mode = '||p_mode
1685 END IF;
1682 ,p_level => C_LEVEL_PROCEDURE
1683 ,p_module => l_log_module);
1684
1686
1687
1688
1689 xla_environment_pkg.refresh;
1690
1691 g_mode := NVL(p_mode,'C_CREATE_MODE');
1692
1693 l_definition_rec.definition_code := p_definition_code;
1694 l_definition_rec.name := p_definition_name;
1695 l_definition_rec.description := p_definition_desc;
1696 l_definition_rec.ledger_id := p_ledger_id;
1697 l_definition_rec.balance_side_code := p_balance_side_code;
1698
1699 IF NOT validate_parameters
1700 (p_definition_rec => l_definition_rec
1701 ,p_je_source_name => p_je_source_name
1702 ,p_gl_date_from => p_gl_date_from
1703 ,p_gl_date_to => p_gl_date_to
1704 ,p_mode => p_mode)
1705 THEN
1706
1707 x_return_status := FND_API.G_RET_STS_ERROR;
1708
1709 RETURN;
1710
1711 END IF;
1712
1713 IF NOT validate_ccids
1714 (p_definition_rec => l_definition_rec)
1715 THEN
1716
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718
1719 RETURN;
1720
1721 END IF;
1722
1723 IF g_mode = C_CREATE_MODE THEN
1724
1725 create_balances
1726 (p_api_version => p_api_version
1727 ,p_init_msg_list => p_init_msg_list
1728 ,p_commit => p_commit
1729 ,x_return_status => x_return_status
1730 ,x_msg_count => x_msg_count
1731 ,x_msg_data => x_msg_data
1732 ,p_definition_rec => l_definition_rec
1733 ,p_je_source_name => p_je_source_name);
1734
1735 ELSIF g_mode = C_UPDATE_MODE THEN
1736
1737 update_balances
1738 (p_api_version => p_api_version
1739 ,p_init_msg_list => p_init_msg_list
1740 ,p_commit => p_commit
1741 ,x_return_status => x_return_status
1742 ,x_msg_count => x_msg_count
1743 ,x_msg_data => x_msg_data
1744 ,p_definition_rec => l_definition_rec
1745 ,p_je_source_name => p_je_source_name
1746 ,p_gl_date_from => p_gl_date_from
1747 ,p_gl_date_to => p_gl_date_to);
1748
1749 END IF;
1750
1751 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1752 trace
1753 (p_msg => 'END of upload_balances'
1754 ,p_level => C_LEVEL_PROCEDURE
1755 ,p_module => l_log_module);
1756 END IF;
1757
1758 EXCEPTION
1759 WHEN xla_exceptions_pkg.application_exception THEN
1760
1761 RAISE;
1762
1763 WHEN OTHERS THEN
1764
1765 xla_exceptions_pkg.raise_message
1766 (p_location => 'xla_tb_balance_pkg.upload_balances');
1767
1768 END upload_balances;
1769
1770 PROCEDURE populate_user_trans_view
1771 IS
1772
1773 CURSOR c_event_class IS
1774 SELECT DISTINCT
1775 xut.application_id
1776 ,xec.entity_code
1777 ,xut.event_class_code
1778 ,xut.reporting_view_name
1779 FROM xla_tb_user_trans_views xut
1780 ,xla_event_classes_b xec
1781 WHERE xut.application_id = xec.application_id
1782 AND xut.event_class_code = xec.event_class_code
1783 AND xut.select_string = '###'
1784 ;
1785
1786 l_application_id NUMBER(15);
1787 l_entity_code VARCHAR2(30);
1788 l_event_class_code VARCHAR2(30);
1789 l_reporting_view_name VARCHAR2(30);
1790 l_select_string VARCHAR2(4000);
1791 l_from_string VARCHAR2(4000);
1792 l_where_string VARCHAR2(4000);
1793
1794 l_log_module VARCHAR2(240);
1795
1796 BEGIN
1797
1798 IF g_log_enabled THEN
1799 l_log_module := C_DEFAULT_MODULE||'.populate_user_trans_view';
1800 END IF;
1801 --
1802 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1803
1804 trace
1805 (p_msg => 'BEGIN of populate_user_trans_view'
1806 ,p_level => C_LEVEL_PROCEDURE
1807 ,p_module => l_log_module);
1808
1809 END IF;
1810
1811 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1812 trace('Inserting user transaction views'
1813 ,C_LEVEL_STATEMENT
1814 ,l_Log_module);
1815 END IF;
1816
1817 --add parallel hint per bug 8222265
1818
1819 INSERT INTO xla_tb_user_trans_views
1820 (definition_code
1821 ,application_id
1822 ,event_class_code
1823 ,reporting_view_name
1824 ,select_string
1825 ,from_string
1826 ,where_string
1827 ,creation_date
1828 ,created_by
1829 ,last_update_date
1830 ,last_updated_by
1831 ,last_update_login
1832 ,request_id
1833 ,program_application_id
1834 ,program_id
1835 ,program_update_date
1836 )
1837 SELECT /*+ leading(XTB,XECA,XTD) use_hash(XECA,XTD) swap_join_inputs(XECA) swap_join_inputs(XTD) parallel(XTB) */
1838 DISTINCT
1839 xtb.definition_code
1843 ,'###'
1840 ,source_application_id
1841 ,xeca.event_class_code
1842 ,xeca.reporting_view_name
1844 ,'###'
1845 ,'###'
1846 ,SYSDATE
1847 ,xla_environment_pkg.g_Usr_Id
1848 ,SYSDATE
1849 ,xla_environment_pkg.g_Usr_Id
1850 ,xla_environment_pkg.g_Login_Id
1851 ,xla_environment_pkg.g_req_Id
1852 ,xla_environment_pkg.g_Prog_Appl_Id
1853 ,xla_environment_pkg.g_Prog_Id
1854 ,SYSDATE
1855 FROM xla_trial_balances xtb
1856 ,xla_tb_definitions_b xtd
1857 ,xla_event_class_attrs xeca
1858 WHERE xeca.event_class_code <> 'MANUAL'
1859 AND xtb.event_class_code = xeca.event_class_code
1860 AND xtb.source_application_id = xeca.application_id
1861 AND xtb.definition_code = xtd.definition_code
1862 AND NOT EXISTS
1863 (SELECT 'x'
1864 FROM xla_tb_user_trans_views xut
1865 WHERE xut.definition_code = xtb.definition_code
1866 AND xut.application_id = xtb.source_application_id
1867 AND xut.event_class_code = xtb.event_class_code
1868 );
1869
1870 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1871 trace('# of rows inserted = ' || SQL%ROWCOUNT
1872 ,C_LEVEL_STATEMENT
1873 ,l_Log_module);
1874 END IF;
1875
1876 OPEN c_event_class;
1877 LOOP
1878 FETCH c_event_class
1879 INTO l_application_id
1880 ,l_entity_code
1881 ,l_event_class_code
1882 ,l_reporting_view_name;
1883
1884 EXIT WHEN c_event_class%NOTFOUND;
1885
1886 IF l_event_class_code <> 'MANUAL' THEN
1887
1888 xla_report_utility_pkg.get_transaction_id
1889 (p_application_id => l_application_id
1890 ,p_entity_code => l_entity_code
1891 ,p_event_class_code => l_event_class_code
1892 ,p_reporting_view_name => l_reporting_view_name
1893 ,p_select_str => l_select_string
1894 ,p_from_str => l_from_string
1895 ,p_where_str => l_where_string);
1896
1897 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1898
1899 trace
1900 (p_msg => 'l_select_string = ' || l_select_string
1901 ,p_level => C_LEVEL_PROCEDURE
1902 ,p_module => l_log_module);
1903 trace
1904 (p_msg => 'l_from_string = ' || l_from_string
1905 ,p_level => C_LEVEL_PROCEDURE
1906 ,p_module => l_log_module);
1907 trace
1908 (p_msg => 'l_where_string = ' || l_where_string
1909 ,p_level => C_LEVEL_PROCEDURE
1910 ,p_module => l_log_module);
1911
1912 trace('Updating user transaction view...'
1913 ,C_LEVEL_STATEMENT
1914 ,l_Log_module);
1915
1916 END IF;
1917
1918 UPDATE xla_tb_user_trans_views
1919 SET select_string = l_select_string
1920 ,from_string = l_from_string
1921 ,where_string = l_where_string
1922 WHERE application_id = l_application_id
1923 AND event_class_code = l_event_class_code
1924 ;
1925
1926 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1927 trace('# of rows updated = ' || SQL%ROWCOUNT
1928 ,C_LEVEL_STATEMENT
1929 ,l_Log_module);
1930 END IF;
1931
1932 END IF;
1933 END LOOP;
1934 CLOSE c_event_class;
1935
1936 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1937 trace
1938 (p_msg => 'END of populate_user_trans_view'
1939 ,p_level => C_LEVEL_PROCEDURE
1940 ,p_module => l_log_module);
1941 END IF;
1942
1943 EXCEPTION
1944 WHEN xla_exceptions_pkg.application_exception THEN
1945 RAISE;
1946 WHEN OTHERS THEN
1947 xla_exceptions_pkg.raise_message
1948 (p_location => 'xla_tb_data_manager_pvt.populate_user_trans_view');
1949 END populate_user_trans_view;
1950
1951 PROCEDURE create_ap_balances
1952 (p_api_version IN NUMBER
1953 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1954 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1955 ,x_return_status OUT NOCOPY VARCHAR2
1956 ,x_msg_count OUT NOCOPY NUMBER
1957 ,x_msg_data OUT NOCOPY VARCHAR2
1958 ,p_balance_side_code IN VARCHAR2
1959 ,p_je_source_name IN VARCHAR2
1960 )
1961 IS
1962
1963 l_log_module VARCHAR2(240);
1964 l_api_name CONSTANT VARCHAR2(30) := 'create_ap_balances';
1965
1966 l_status VARCHAR2(30);
1967 l_industry VARCHAR2(30);
1968 l_schema VARCHAR2(30);
1969
1970 l_count NUMBER;
1971
1972 l_usr_id NUMBER := xla_environment_pkg.g_Usr_Id;
1973 l_login_id NUMBER := xla_environment_pkg.g_Login_Id;
1974 l_req_id NUMBER := xla_environment_pkg.g_req_Id;
1978 BEGIN
1975 l_prog_appl_id NUMBER := xla_environment_pkg.g_Prog_Appl_Id;
1976 l_prog_id NUMBER := xla_environment_pkg.g_Prog_Id;
1977
1979 IF g_log_enabled THEN
1980 l_log_module := C_DEFAULT_MODULE||'.create_ap_balances';
1981 END IF;
1982 --
1983 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1984 trace
1985 (p_msg => 'BEGIN of create_ap_balances'
1986 ,p_level => C_LEVEL_PROCEDURE
1987 ,p_module => l_log_module);
1988 trace
1989 (p_msg => 'p_balance_side_code = '||p_balance_side_code
1990 ,p_level => C_LEVEL_PROCEDURE
1991 ,p_module => l_log_module);
1992 trace
1993 (p_msg => 'p_je_source_name = '||p_je_source_name
1994 ,p_level => C_LEVEL_PROCEDURE
1995 ,p_module => l_log_module);
1996 END IF;
1997
1998 IF(NOT(ad_event_registry_pkg.is_event_done(
1999 p_owner => 'XLA',
2000 p_event_name => 'XLA_AP_TRIAL_UPG_AP_BAL'))) THEN
2001 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2002 trace
2003 (p_msg => 'Inserting for ap entries'
2004 ,p_level => C_LEVEL_PROCEDURE
2005 ,p_module => l_log_module);
2006 END IF;
2007
2008 --for bug#7364921 did a trunc of xah.accounting_date in the query below
2009 --Reason gl_date is populated with time component for upgraded data and the trial balance report
2010 --query does not fetch data for a date including time stamp example report query date range is
2011 --'01-MAY-2008' to '31-MAY-2008' and if for a invoice in trial balance table the gl_date is
2012 --'31-MAY-2008 09:13:00 AM' this invoice will not fall in the above date range. It will fall in the date
2013 -- range for the next day ie '01-MAY-2008' to '01-JUN-2008'
2014
2015 --BUG 8222265 added a swap_join_inputs(gcc) hint and flipped first 2 tables in FROM clause
2016
2017 INSERT /*+ parallel(xtb) append */ INTO xla_trial_balances xtb(
2018 record_type_code
2019 ,source_entity_id
2020 ,event_class_code
2021 ,source_application_id
2022 ,applied_to_entity_id
2023 ,applied_to_application_id
2024 ,gl_date
2025 ,trx_currency_code
2026 ,entered_rounded_dr
2027 ,entered_rounded_cr
2028 ,entered_unrounded_dr
2029 ,entered_unrounded_cr
2030 ,acctd_rounded_dr
2031 ,acctd_rounded_cr
2032 ,acctd_unrounded_dr
2033 ,acctd_unrounded_cr
2034 ,code_combination_id
2035 ,balancing_segment_value
2036 ,natural_account_segment_value
2037 ,cost_center_segment_value
2038 ,intercompany_segment_value
2039 ,management_segment_value
2040 ,ledger_id
2041 ,definition_code
2042 ,party_id
2043 ,party_site_id
2044 ,party_type_code
2045 ,ae_header_id
2046 ,generated_by_code
2047 ,creation_date
2048 ,created_by
2049 ,last_update_date
2050 ,last_updated_by
2051 ,last_update_login
2052 ,request_id
2053 ,program_application_id
2054 ,program_id
2055 ,program_update_date)
2056 SELECT /*+ ORDERED NO_EXPAND use_hash(xtd,xdd,xjs,xsu,xal,xah,gcc,xet,xteu,fsav)
2057 parallel(alb) parallel(xal) parallel(xah) parallel(gcc) parallel(xteu)
2058 parallel(xtd) parallel(xdd) parallel(xjs) parallel(xsu)
2059 pq_distribute(xal,hash,hash) pq_distribute(fsav,none,broadcast)
2060 pq_distribute(gcc,hash,hash) pq_distribute(xteu,hash,hash)
2061 pq_distribute(xjs,none,broadcast) pq_distribute(xsu,none,broadcast)
2062 swap_join_inputs(fsav) swap_join_inputs(xtd) swap_join_inputs(xdd)
2063 swap_join_inputs(xjs) swap_join_inputs(xsu) swap_join_inputs(gcc) */
2064 DECODE(xet.event_class_code,'PREPAYMENT APPLICATIONS','APPLIED',DECODE(xteu.entity_id,xah.entity_id,'SOURCE','APPLIED')) record_type_code --bug6373682
2065 ,xah.entity_id source_entity_id
2066 ,xet.event_class_code event_class_code
2067 ,xah.application_id source_application_id
2068 ,DECODE(xet.event_class_code,'PREPAYMENT APPLICATIONS',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id)) applied_to_entity_id --bug6373682
2069 ,200 applied_to_application_id
2070 ,trunc(xah.accounting_date) gl_date --bug#7364921
2071 ,xal.currency_code trx_currency_code
2072 ,SUM(NVL(xal.entered_dr,0)) entered_rounded_dr
2073 ,SUM(NVL(xal.entered_cr,0)) entered_rounded_cr
2074 ,SUM(NVL(xal.entered_dr,0)) entered_unrounded_dr
2075 ,SUM(NVL(xal.entered_cr,0)) entered_unrounded_cr
2076 ,SUM(NVL(alb.accounted_dr, 0)) acctd_rounded_dr
2077 ,SUM(NVL(alb.accounted_cr, 0)) acctd_rounded_cr
2078 ,SUM(NVL(alb.accounted_dr,0)) acctd_unrounded_dr
2079 ,SUM(NVL(alb.accounted_cr,0)) acctd_unrounded_cr
2080 ,xal.code_combination_id code_combination_id
2081 ,DECODE(fsav.balancing_segment,
2082 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2086 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2083 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2084 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2085 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2087 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2088 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2089 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2090 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2091 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2092 null)
2093 balancing_segment_value
2094 ,DECODE(fsav.account_segment,
2095 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2096 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2097 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2098 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2099 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2100 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2101 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2102 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2103 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2104 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2105 null)
2106 natural_account_segment_value
2107 ,DECODE(fsav.cost_crt_segment,
2108 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2109 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2110 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2111 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2112 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2113 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2114 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2115 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2116 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2117 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2118 null)
2119 cost_center_segment_value
2120 ,DECODE(fsav.intercompany_segment,
2121 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2122 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2123 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2124 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2125 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2126 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2127 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2128 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2129 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2130 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2131 null)
2132 intercompany_segment_value
2133 ,DECODE(fsav.management_segment,
2134 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2135 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2136 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2137 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2138 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2139 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2140 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2141 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2142 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2143 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2144 null)
2145 management_segment_value
2146 ,xah.ledger_id ledger_id
2147 ,xtd.definition_code DEFINITION_code
2148 ,xal.party_id party_id
2149 ,xal.party_site_id party_site_id
2150 ,xal.party_type_code party_type_code
2154 ,l_Usr_Id created_by
2151 ,xah.ae_header_id ae_header_id
2152 ,'SYSTEM' generated_by_code
2153 ,SYSDATE creation_date
2155 ,SYSDATE last_update_date
2156 ,l_Usr_Id last_updated_by
2157 ,l_Login_Id last_update_login
2158 ,l_req_Id request_id
2159 ,l_Prog_Appl_Id program_application_id
2160 ,l_Prog_Id program_id
2161 ,SYSDATE program_update_date
2162 FROM
2163 xla_ae_headers PARTITION (AP) xah
2164 ,ap_liability_balance alb
2165 ,xla_event_types_b xet
2166 ,xla_tb_defn_details xdd
2167 ,xla_tb_definitions_b xtd
2168 ,xla_tb_defn_je_sources xjs
2169 ,xla_subledgers xsu
2170 ,xla_transaction_entities_upg PARTITION (AP) xteu
2171 ,xla_ae_lines PARTITION (AP) xal
2172 ,gl_code_combinations gcc
2173 ,( SELECT /*+ NO_MERGE PARALLEL(fsav1) */ id_flex_num
2174 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_BALANCING', application_column_name, NULL)) balancing_segment
2175 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_ACCOUNT', application_column_name, NULL)) account_segment
2176 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'FA_COST_CTR', application_column_name, NULL)) cost_crt_segment
2177 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_INTERCOMPANY', application_column_name, NULL)) intercompany_segment
2178 ,MAX(DECODE(SEGMENT_ATTRIBUTE_TYPE, 'GL_MANAGEMENT', application_column_name, NULL)) management_segment
2179 FROM fnd_segment_attribute_values fsav1 -- Need alias here also.
2180 WHERE application_id = 101
2181 AND id_flex_code = 'GL#'
2182 AND attribute_value = 'Y'
2183 GROUP BY id_flex_num) fsav
2184 WHERE xtd.definition_code = xdd.definition_code
2185 AND xtd.definition_code = xjs.definition_code
2186 AND xtd.enabled_flag = 'Y'
2187 AND xjs.je_source_name = xsu.je_source_name
2188 AND xsu.application_id = 200
2189 AND xtd.ledger_id = alb.set_of_books_id
2190 AND alb.code_combination_id = xdd.code_combination_id
2191 --
2192 -- AND alb.ae_header_id is NOT NULL -- now considering both cases in one shot
2193 --
2194 AND NVL(alb.ae_header_id, alb.sle_header_id) = xah.completion_acct_seq_value
2195 AND NVL2(alb.ae_header_id,200, alb.journal_sequence_id) = xah.completion_acct_seq_version_id
2196 AND NVL2(alb.ae_header_id, alb.ae_line_id,alb.sle_line_num) = xal.ae_line_num
2197 AND (
2198 (alb.ae_header_id IS NOT NULL AND xah.upg_source_application_id = 200)
2199 OR
2200 (alb.ae_header_id IS NULL AND xah.upg_source_application_id = 600 AND xah.upg_batch_id = -5672)
2201 )
2202 AND alb.code_combination_id = xal.code_combination_id
2203 AND xal.application_id = 200
2204 AND xah.gl_transfer_status_code IN ('Y','NT')
2205 AND xah.application_id = xal.application_id
2206 AND xah.ae_header_id = xal.ae_header_id
2207 AND xal.code_combination_id = gcc.code_combination_id
2208 AND xah.application_id = xet.application_id
2209 AND xah.event_type_code = xet.event_type_code
2210 AND xteu.application_id = 200
2211 AND xteu.entity_code = 'AP_INVOICES'
2212 AND xteu.source_id_int_1 = alb.invoice_id
2213 AND gcc.chart_of_accounts_id = fsav.id_flex_num
2214 GROUP BY
2215 DECODE(xet.event_class_code,'PREPAYMENT APPLICATIONS','APPLIED',DECODE(xteu.entity_id,xah.entity_id,'SOURCE','APPLIED'))
2216 ,xah.entity_id
2217 ,xet.event_class_code
2218 ,xah.application_id
2219 ,DECODE(xet.event_class_code,'PREPAYMENT APPLICATIONS',xteu.entity_id,DECODE(xteu.entity_id, xah.entity_id,NULL,xteu.entity_id))
2220 ,xah.accounting_date
2221 ,xal.currency_code
2222 ,xal.code_combination_id
2223 ,DECODE(fsav.balancing_segment,
2224 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2225 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2226 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2227 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2228 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2229 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2230 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2231 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2232 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2233 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2234 null)
2238 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2235 ,DECODE(fsav.account_segment,
2236 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2237 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2239 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2240 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2241 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2242 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2243 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2244 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2245 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2246 null)
2247 ,DECODE(fsav.cost_crt_segment,
2248 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2249 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2250 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2251 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2252 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2253 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2254 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2255 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2256 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2257 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2258 null)
2259 ,DECODE(fsav.intercompany_segment,
2260 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2261 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2262 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2263 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2264 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2265 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2266 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2267 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2268 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2269 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2270 null)
2271 ,DECODE(fsav.management_segment,
2272 'SEGMENT1', gcc.segment1, 'SEGMENT2', gcc.segment2, 'SEGMENT3', gcc.segment3,
2273 'SEGMENT4', gcc.segment4, 'SEGMENT5', gcc.segment5, 'SEGMENT6', gcc.segment6,
2274 'SEGMENT7', gcc.segment7, 'SEGMENT8', gcc.segment8, 'SEGMENT9', gcc.segment9,
2275 'SEGMENT10', gcc.segment10, 'SEGMENT11', gcc.segment11, 'SEGMENT12', gcc.segment12,
2276 'SEGMENT13', gcc.segment13, 'SEGMENT14', gcc.segment14, 'SEGMENT15', gcc.segment15,
2277 'SEGMENT16', gcc.segment16, 'SEGMENT17', gcc.segment17, 'SEGMENT18', gcc.segment18,
2278 'SEGMENT19', gcc.segment19, 'SEGMENT20', gcc.segment20, 'SEGMENT21', gcc.segment21,
2279 'SEGMENT22', gcc.segment22, 'SEGMENT23', gcc.segment23, 'SEGMENT24', gcc.segment24,
2280 'SEGMENT25', gcc.segment25, 'SEGMENT26', gcc.segment26, 'SEGMENT27', gcc.segment27,
2281 'SEGMENT28', gcc.segment28, 'SEGMENT29', gcc.segment29, 'SEGMENT30', gcc.segment30,
2282 null)
2283 ,xah.ledger_id
2284 ,xtd.definition_code
2285 ,xal.party_id
2286 ,xal.party_site_id
2287 ,xal.party_type_code
2288 ,xah.ae_header_id
2289 ;
2290
2291 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2292 trace('# of rows inserted = ' || SQL%ROWCOUNT
2293 ,C_LEVEL_STATEMENT
2294 ,l_Log_module);
2295 END IF;
2296 ad_event_registry_pkg.set_event_as_done('XLA', 'XLA_AP_TRIAL_UPG_AP_BAL', 'xla_tb_balance_pkg');
2297 COMMIT;
2298 END IF;
2299
2300 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2301 trace
2302 (p_msg => 'END of create_ap_balances'
2303 ,p_level => C_LEVEL_PROCEDURE
2304 ,p_module => l_log_module);
2305 END IF;
2306 END create_ap_balances;
2307
2308 PROCEDURE create_defns_in_batch
2309 (p_balance_side_code IN VARCHAR2
2310 ,p_je_source_name IN VARCHAR2)
2311 IS
2312
2313 TYPE t_array_vc30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
2314 TYPE t_array_num15 IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2315
2316 l_array_defn_code t_array_vc30;
2317 l_array_ledger_id t_array_num15;
2318
2319 l_status VARCHAR2(30);
2320 l_industry VARCHAR2(30);
2321 l_schema VARCHAR2(30);
2322
2323 l_log_module VARCHAR2(240);
2327 IF g_log_enabled THEN
2324 l_api_name CONSTANT VARCHAR2(30) := 'create_defns_in_batch';
2325 BEGIN
2326
2328 l_log_module := C_DEFAULT_MODULE||'.create_defns_in_batch';
2329 END IF;
2330 --
2331 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2332 trace
2333 (p_msg => 'BEGIN of create_defns_in_batch'
2334 ,p_level => C_LEVEL_PROCEDURE
2335 ,p_module => l_log_module);
2336 trace
2337 (p_msg => 'p_balance_side_code = '||p_balance_side_code
2338 ,p_level => C_LEVEL_PROCEDURE
2339 ,p_module => l_log_module);
2340 trace
2341 (p_msg => 'p_je_source_name = '||p_je_source_name
2342 ,p_level => C_LEVEL_PROCEDURE
2343 ,p_module => l_log_module);
2344 END IF;
2345
2346 IF(NOT(ad_event_registry_pkg.is_event_done(
2347 p_owner => 'XLA',
2348 p_event_name => 'XLA_AP_TRIAL_UPG_DEFN'))) THEN
2349 SELECT DISTINCT definition_code, ledger_id
2350 BULK COLLECT INTO l_array_defn_code, l_array_ledger_id
2351 FROM xla_tb_balances_gt tb
2352 WHERE definition_code NOT IN
2353 (
2354 SELECT definition_code
2355 FROM xla_tb_definitions_b
2356 );
2357
2358 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2359 trace
2360 (p_msg => 'Fetched definitions into the array'
2361 ,p_level => C_LEVEL_STATEMENT
2362 ,p_module => l_log_module);
2363 END IF;
2364
2365 FORALL i IN l_array_defn_code.first .. l_array_defn_code.last
2366 INSERT INTO xla_tb_definitions_b
2367 (definition_code
2368 ,object_version_number
2369 ,ledger_id
2370 ,enabled_flag
2371 ,balance_side_code
2372 ,defined_by_code
2373 ,definition_status_code
2374 ,creation_date
2375 ,created_by
2376 ,last_update_date
2377 ,last_updated_by
2378 ,last_update_login
2379 ,program_application_id
2380 ,program_id
2381 ,program_update_date
2382 ,owner_code)
2383 VALUES (l_array_defn_code(i)
2384 ,1
2385 ,l_array_ledger_id(i)
2386 ,'Y'
2387 ,p_balance_side_code
2388 ,'FLEXFIELD'
2389 ,'NEW'
2390 ,sysdate
2391 ,xla_environment_pkg.g_Usr_Id
2392 ,sysdate
2393 ,xla_environment_pkg.g_Usr_Id
2394 ,xla_environment_pkg.g_login_Id
2395 ,xla_environment_pkg.g_Prog_Appl_Id
2396 ,xla_environment_pkg.g_Usr_Id
2397 ,sysdate
2398 ,'S');
2399
2400 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2401 trace
2402 (p_msg => 'inserted definition into the xla_tb_definitions_b:'|| SQL%ROWCOUNT
2403 ,p_level => C_LEVEL_STATEMENT
2404 ,p_module => l_log_module);
2405 END IF;
2406
2407 FORALL i IN l_array_defn_code.first .. l_array_defn_code.last
2408 INSERT INTO xla_tb_defn_je_sources
2409 (definition_code
2410 ,je_source_name
2411 ,object_version_number
2412 ,creation_date
2413 ,created_by
2414 ,last_update_date
2415 ,last_updated_by
2416 ,last_update_login
2417 ,owner_code)
2418 VALUES (l_array_defn_code(i)
2419 ,p_je_source_name
2420 ,1
2421 ,sysdate
2422 ,xla_environment_pkg.g_Usr_Id
2423 ,sysdate
2424 ,xla_environment_pkg.g_Usr_Id
2425 ,xla_environment_pkg.g_login_Id
2426 ,'S');
2427
2428 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2429 trace
2430 (p_msg => 'inserted definition into the xla_tb_defn_je_sources:'|| SQL%ROWCOUNT
2431 ,p_level => C_LEVEL_STATEMENT
2432 ,p_module => l_log_module);
2433 END IF;
2434
2435 INSERT INTO xla_gl_ledgers
2436 ( LEDGER_ID
2437 ,OBJECT_VERSION_NUMBER
2438 ,WORK_UNIT
2439 ,NUM_OF_WORKERS
2440 ,CREATION_DATE
2441 ,CREATED_BY
2442 ,LAST_UPDATE_DATE
2443 ,LAST_UPDATED_BY
2444 ,LAST_UPDATE_LOGIN
2445 )
2446 SELECT DISTINCT
2447 xtb.ledger_id
2448 ,1
2449 ,5000
2450 ,1
2451 ,SYSDATE
2452 ,xla_environment_pkg.g_usr_id
2453 ,SYSDATE
2454 ,xla_environment_pkg.g_usr_id
2455 ,xla_environment_pkg.g_login_id
2456 FROM xla_tb_balances_gt xtb
2457 WHERE NOT EXISTS
2458 (SELECT 1
2459 FROM XLA_GL_LEDGERS
2460 WHERE ledger_id = xtb.ledger_id);
2461
2462 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2463 trace
2464 (p_msg => 'inserted ledger info into the xla_gl_ledgers:'|| SQL%ROWCOUNT
2465 ,p_level => C_LEVEL_STATEMENT
2466 ,p_module => l_log_module);
2467 END IF;
2468
2469
2470 INSERT INTO xla_tb_definitions_tl
2471 (
2472 definition_code
2473 ,name
2474 ,description
2475 ,created_by
2476 ,creation_date
2477 ,last_updated_by
2478 ,last_update_date
2479 ,last_update_login
2480 ,language
2481 ,source_lang
2482 )
2483 SELECT DISTINCT
2484 definition_code
2485 ,definition_name
2486 ,definition_desc
2487 ,xla_environment_pkg.g_Usr_Id
2488 ,sysdate
2489 ,xla_environment_pkg.g_Usr_Id
2490 ,sysdate
2491 ,xla_environment_pkg.g_login_Id
2492 ,l.language_code
2493 ,userenv('LANG')
2494 FROM fnd_languages l
2495 ,xla_tb_balances_gt tb
2496 WHERE l.installed_flag in ('I', 'B')
2497 AND NOT EXISTS
2498 (SELECT 1
2499 FROM xla_tb_definitions_tl t
2500 WHERE t.definition_code = tb.definition_code
2501 AND t.language = l.language_code);
2502
2503 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2504 trace
2505 (p_msg => 'inserted definition into the xla_tb_definitions_tl:'|| SQL%ROWCOUNT
2506 ,p_level => C_LEVEL_STATEMENT
2507 ,p_module => l_log_module);
2508 END IF;
2509
2510 --
2511 -- Insert rows into xla_tb_defn_details
2512 --
2513
2514 INSERT ALL INTO xla_tb_defn_details
2515 (definition_detail_id
2516 ,object_version_number
2517 ,definition_code
2518 ,flexfield_segment_code
2519 ,segment_value_from
2520 ,segment_value_to
2521 ,code_combination_id
2522 ,owner_code
2523 ,balance_date
2524 ,balance_amount
2525 ,creation_date
2526 ,created_by
2527 ,last_update_date
2528 ,last_updated_by
2529 ,last_update_login)
2530 VALUES (xla_tb_defn_details_s.NEXTVAL
2531 ,1
2532 ,definition_code
2533 ,NULL -- flexfield segment code
2534 ,NULL -- segment value from
2535 ,NULL -- segment value to
2536 ,code_combination_id
2537 ,owner_code
2538 ,NULL -- balance_date
2539 ,NULL -- balance_amount
2540 ,sysdate
2541 ,xla_environment_pkg.g_Usr_Id
2542 ,sysdate
2543 ,xla_environment_pkg.g_Usr_Id
2544 ,xla_environment_pkg.g_login_Id)
2545 INTO xla_tb_def_seg_ranges
2546 (definition_code
2547 ,line_num
2548 ,balance_date
2549 ,owner_code
2550 ,segment1_from
2551 ,segment1_to
2552 ,segment2_from
2553 ,segment2_to
2554 ,segment3_from
2555 ,segment3_to
2556 ,segment4_from
2557 ,segment4_to
2558 ,segment5_from
2559 ,segment5_to
2560 ,segment6_from
2561 ,segment6_to
2562 ,segment7_from
2563 ,segment7_to
2564 ,segment8_from
2565 ,segment8_to
2566 ,segment9_from
2567 ,segment9_to
2568 ,segment10_from
2569 ,segment10_to
2570 ,segment11_from
2571 ,segment11_to
2572 ,segment12_from
2573 ,segment12_to
2574 ,segment13_from
2575 ,segment13_to
2576 ,segment14_from
2577 ,segment14_to
2578 ,segment15_from
2579 ,segment15_to
2580 ,segment16_from
2581 ,segment16_to
2582 ,segment17_from
2583 ,segment17_to
2584 ,segment18_from
2585 ,segment18_to
2586 ,segment19_from
2587 ,segment19_to
2588 ,segment20_from
2589 ,segment20_to
2590 ,segment21_from
2591 ,segment21_to
2592 ,segment22_from
2593 ,segment22_to
2594 ,segment23_from
2595 ,segment23_to
2596 ,segment24_from
2597 ,segment24_to
2598 ,segment25_from
2599 ,segment25_to
2600 ,segment26_from
2601 ,segment26_to
2602 ,segment27_from
2603 ,segment27_to
2604 ,segment28_from
2605 ,segment28_to
2606 ,segment29_from
2610 VALUES (definition_code
2607 ,segment29_to
2608 ,segment30_from
2609 ,segment30_to)
2611 ,line_num
2612 ,NULL -- balance_date
2613 ,owner_code
2614 ,segment1
2615 ,segment1
2616 ,segment2
2617 ,segment2
2618 ,segment3
2619 ,segment3
2620 ,segment4
2621 ,segment4
2622 ,segment5
2623 ,segment5
2624 ,segment6
2625 ,segment6
2626 ,segment7
2627 ,segment7
2628 ,segment8
2629 ,segment8
2630 ,segment9
2631 ,segment9
2632 ,segment10
2633 ,segment10
2634 ,segment11
2635 ,segment11
2636 ,segment12
2637 ,segment12
2638 ,segment13
2639 ,segment13
2640 ,segment14
2641 ,segment14
2642 ,segment15
2643 ,segment15
2644 ,segment16
2645 ,segment16
2646 ,segment17
2647 ,segment17
2648 ,segment18
2649 ,segment18
2650 ,segment19
2651 ,segment19
2652 ,segment20
2653 ,segment20
2654 ,segment21
2655 ,segment21
2656 ,segment22
2657 ,segment22
2658 ,segment23
2659 ,segment23
2660 ,segment24
2661 ,segment24
2662 ,segment25
2663 ,segment25
2664 ,segment26
2665 ,segment26
2666 ,segment27
2667 ,segment27
2668 ,segment28
2669 ,segment28
2670 ,segment29
2671 ,segment29
2672 ,segment30
2673 ,segment30)
2674 SELECT tdd.definition_code definition_code
2675 ,ROWNUM line_num
2676 ,tdd.code_combination_id
2677 ,'S' owner_code
2678 ,balance_date
2679 ,balance_amount
2680 ,gcc.segment1
2681 ,gcc.segment2
2682 ,gcc.segment3
2683 ,gcc.segment4
2684 ,gcc.segment5
2685 ,gcc.segment6
2686 ,gcc.segment7
2687 ,gcc.segment8
2688 ,gcc.segment9
2689 ,gcc.segment10
2690 ,gcc.segment11
2691 ,gcc.segment12
2692 ,gcc.segment13
2693 ,gcc.segment14
2694 ,gcc.segment15
2695 ,gcc.segment16
2696 ,gcc.segment17
2697 ,gcc.segment18
2698 ,gcc.segment19
2699 ,gcc.segment20
2700 ,gcc.segment21
2701 ,gcc.segment22
2702 ,gcc.segment23
2703 ,gcc.segment24
2704 ,gcc.segment25
2705 ,gcc.segment26
2706 ,gcc.segment27
2707 ,gcc.segment28
2708 ,gcc.segment29
2709 ,gcc.segment30
2710 FROM xla_tb_balances_gt tdd
2711 ,gl_code_combinations gcc
2712 WHERE gcc.code_combination_id = tdd.code_combination_id;
2713
2714
2715 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2716 trace
2717 (p_msg => 'multi inserted definition into the details and seg ranges:'|| SQL%ROWCOUNT
2718 ,p_level => C_LEVEL_STATEMENT
2719 ,p_module => l_log_module);
2720 END IF;
2721
2722 --
2723 -- Commit before creating partitions
2724 --
2725 COMMIT ;
2726
2727 IF (NOT FND_INSTALLATION.get_app_info
2728 (application_short_name => 'XLA'
2729 ,status => l_status
2730 ,industry => l_industry
2731 ,oracle_schema => l_schema)) THEN
2732 l_schema := NULL;
2733 END IF;
2734
2735 --
2736 -- Add partitions to xla_trial_balances
2737 --
2738 IF (l_array_defn_code.COUNT > 0 ) THEN
2739 FOR i in l_array_defn_code.FIRST .. l_array_defn_code.LAST LOOP
2740 BEGIN
2741 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2742 trace
2743 (p_msg => 'ALTER TABLE '||l_schema||'.xla_trial_balances'
2744 ||' ADD PARTITION '||l_array_defn_code(i)
2745 || ' VALUES ('''||l_array_defn_code(i)||''' ) executing'
2746 ,p_level => C_LEVEL_STATEMENT
2747 ,p_module => l_log_module);
2748 trace
2749 (p_msg => 'l_array_defn_code(i)'||l_array_defn_code(i)
2750 || ' l_ledger(i)'|| l_array_ledger_id(i)
2751 ,p_level => C_LEVEL_STATEMENT
2752 ,p_module => l_log_module);
2753 END IF;
2754
2755 EXECUTE IMMEDIATE
2756 'ALTER TABLE '||l_schema||'.xla_trial_balances'||' ADD PARTITION '||l_array_defn_code(i)||
2757 ' VALUES ('''||l_array_defn_code(i)||''' )';
2758 EXCEPTION
2759 WHEN OTHERS THEN
2760 IF(SQLCODE = -14312) THEN
2761 -- partition already exist
2762 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2763 trace
2764 (p_msg => 'partition already exists:' || l_array_defn_code(i)
2765 ,p_level => C_LEVEL_STATEMENT
2766 ,p_module => l_log_module);
2767 END IF;
2768 ELSE
2769 raise;
2770 END IF;
2771 END;
2772
2773 END LOOP;
2774 END IF;
2775 ad_event_registry_pkg.set_event_as_done('XLA', 'XLA_AP_TRIAL_UPG_DEFN', 'xla_tb_balance_pkg');
2776
2777 COMMIT;
2778 END IF;
2779
2780 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2781 trace
2782 (p_msg => 'END of Upgrade_AP_Balances'
2783 ,p_level => C_LEVEL_PROCEDURE
2784 ,p_module => l_log_module);
2785 END IF;
2786 END create_defns_in_batch;
2787
2788 PROCEDURE Upgrade_AP_Balances
2789 (p_api_version IN NUMBER
2790 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2791 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2792 ,x_return_status OUT NOCOPY VARCHAR2
2793 ,x_msg_count OUT NOCOPY NUMBER
2794 ,x_msg_data OUT NOCOPY VARCHAR2
2795 ,p_balance_side_code IN VARCHAR2
2796 ,p_je_source_name IN VARCHAR2
2797 )
2798 IS
2799
2800 l_log_module VARCHAR2(240);
2801 l_api_name CONSTANT VARCHAR2(30) := 'Upgrade_AP_Balances';
2802
2803 l_definition_rec r_definition;
2804
2805 BEGIN
2806
2807 IF g_log_enabled THEN
2808 l_log_module := C_DEFAULT_MODULE||'.Upgrade_AP_Balances';
2809 END IF;
2810 --
2811 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2812 trace
2813 (p_msg => 'BEGIN of Upgrade_AP_Balances'
2814 ,p_level => C_LEVEL_PROCEDURE
2815 ,p_module => l_log_module);
2816 trace
2817 (p_msg => 'p_balance_side_code = '||p_balance_side_code
2818 ,p_level => C_LEVEL_PROCEDURE
2819 ,p_module => l_log_module);
2820 trace
2821 (p_msg => 'p_je_source_name = '||p_je_source_name
2822 ,p_level => C_LEVEL_PROCEDURE
2823 ,p_module => l_log_module);
2824 END IF;
2825
2826 xla_environment_pkg.refresh;
2827
2828 IF NOT validate_je_source
2829 (p_je_source_name => p_je_source_name)
2830 THEN
2831 x_return_status := FND_API.G_RET_STS_ERROR;
2832 RETURN;
2833 END IF;
2834
2835 create_defns_in_batch
2836 (p_balance_side_code=> p_balance_side_code
2837 ,p_je_source_name => p_je_source_name);
2838
2839 create_ap_balances
2840 (p_api_version => p_api_version
2841 ,p_init_msg_list => p_init_msg_list
2842 ,p_commit => p_commit
2843 ,x_return_status => x_return_status
2844 ,x_msg_count => x_msg_count
2845 ,x_msg_data => x_msg_data
2846 ,p_balance_side_code=> p_balance_side_code
2847 ,p_je_source_name => p_je_source_name);
2848
2849 populate_user_trans_view;
2850
2851 x_return_status := FND_API.G_RET_STS_SUCCESS;
2852
2853 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2854 trace
2855 (p_msg => 'END of Upgrade_AP_Balances'
2856 ,p_level => C_LEVEL_PROCEDURE
2857 ,p_module => l_log_module);
2858 END IF;
2859
2860 EXCEPTION
2861 WHEN xla_exceptions_pkg.application_exception THEN
2862
2863 RAISE;
2864
2865 WHEN OTHERS THEN
2866
2867 xla_exceptions_pkg.raise_message
2868 (p_location => 'xla_tb_balance_pkg.Upgrade_AP_Balances');
2869
2870 END Upgrade_AP_Balances;
2871
2872 BEGIN
2873 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2874 g_log_enabled := fnd_log.test
2875 (log_level => g_log_level
2876 ,module => C_DEFAULT_MODULE);
2877
2878 IF NOT g_log_enabled THEN
2879 g_log_level := C_LEVEL_LOG_DISABLED;
2880 END IF;
2881
2882 END xla_tb_balance_pkg;