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