[Home] [Help]
PACKAGE BODY: APPS.XLA_BALANCES_CALC_PKG
Source
1 PACKAGE BODY xla_balances_calc_pkg as
2 /* $Header: xlabacalc.pkb 120.26.12020000.4 2012/08/15 00:11:37 vdamerla ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_balances_calc_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Balances Calculation Package |
13 | |
14 | HISTORY |
15 | |
16 | 06-04-2012 VenkatDamerla |
17 | 1. Fix Locking code to make the locking more granular and also fix |
18 | other issues in the locking code. |
19 | 2. Fix the analytical balance calculation to consider the |
20 | year_end_carry_forward_code. |
21 | 3. For ledgers created after the new balances fix the code to |
22 | update the effective_period_num in balances table in addition to |
23 | updating the effective_period_num in xla_ledger_options. |
24 | |
25 | |
26 +======================================================================*/
27
28 -- Private exceptions
29 --
30 le_resource_busy EXCEPTION;
31 le_deadlock_detected EXCEPTION; -- bug 13614923
32 le_wait_expire EXCEPTION; -- bug 13614923
33
34 PRAGMA EXCEPTION_INIT(le_resource_busy , -00054);
35 PRAGMA EXCEPTION_INIT(le_deadlock_detected, -00060); -- bug 13614923
36 PRAGMA EXCEPTION_INIT(le_wait_expire , -30006); -- bug 13614923
37
38
39 g_ledger_array XLA_ARRAY_NUMBER_TYPE ; -- bug 13614923
40 g_application_array XLA_ARRAY_NUMBER_TYPE ; -- bug 13614923
41 g_accounting_batch_array XLA_ARRAY_NUMBER_TYPE ; -- bug 13614923
42
43
44 --
45 -- Private types
46 --
47 TYPE table_of_pls_integer IS TABLE OF PLS_INTEGER
48 INDEX BY PLS_INTEGER;
49
50 --
51 --
52 g_user_id INTEGER;
53 g_login_id INTEGER;
54 g_date DATE;
55 g_prog_appl_id INTEGER;
56 g_prog_id INTEGER;
57 g_req_id INTEGER;
58 g_cached_ledgers table_of_pls_integer;
59 g_cached_single_period BOOLEAN;
60 g_lock_flag VARCHAR2 (1) DEFAULT 'N';
61 g_preupdate_flag VARCHAR2 (1);
62 g_postupdate_flag VARCHAR2 (1);
63
64 --
65 -- Cursor declarations
66 --
70 c_level_statement CONSTANT NUMBER := fnd_log.level_statement;
67 --=============================================================================
68 -- *********** Local Trace Routine **********
69 --=============================================================================
71 c_level_procedure CONSTANT NUMBER := fnd_log.level_procedure;
72 c_level_event CONSTANT NUMBER := fnd_log.level_event;
73 c_level_exception CONSTANT NUMBER := fnd_log.level_exception;
74 c_level_error CONSTANT NUMBER := fnd_log.level_error;
75 c_level_unexpected CONSTANT NUMBER := fnd_log.level_unexpected;
76 c_level_log_disabled CONSTANT NUMBER := 99;
77 c_default_module CONSTANT VARCHAR2 (240)
78 := 'xla.plsql.xla_balances_calc_pkg';
79 g_log_level NUMBER;
80 g_log_enabled BOOLEAN;
81
82 PROCEDURE TRACE (p_module IN VARCHAR2, p_msg IN VARCHAR2, p_level IN NUMBER)
83 IS
84 BEGIN
85 IF (p_msg IS NULL AND p_level >= g_log_level)
86 THEN
87 fnd_log.MESSAGE (p_level, p_module);
88 ELSIF p_level >= g_log_level
89 THEN
90 fnd_log.STRING (p_level, p_module, p_msg);
91 END IF;
92 EXCEPTION
93 WHEN xla_exceptions_pkg.application_exception
94 THEN
95 RAISE;
96 WHEN OTHERS
97 THEN
98 xla_exceptions_pkg.raise_message(p_location => 'xla_balances_calc_pkg.trace');
99 END TRACE;
100
101 /*===============================================+
102 | |
103 | Private Function |
104 |------------------ |
105 | lock records in xla_bal_concurrency_control |
106 | |
107 | |
108 +===============================================*/
109 FUNCTION lock_bal_concurrency_control (
110 p_application_id IN INTEGER
111 , p_ledger_id IN INTEGER
112 , p_entity_id IN INTEGER
113 , p_event_id IN INTEGER
114 , p_ae_header_id IN INTEGER
115 , p_ae_line_num IN INTEGER
116 , p_request_id IN INTEGER
117 , p_accounting_batch_id IN INTEGER
118 , p_execution_mode IN VARCHAR2
119 , p_concurrency_class IN VARCHAR2
120 )
121 RETURN BOOLEAN
122 IS
123 l_log_module VARCHAR2 (2000);
124 PRAGMA AUTONOMOUS_TRANSACTION;
125 l_insert_sql VARCHAR2(2000);
126 BEGIN
127 IF g_log_enabled
128 THEN
129 l_log_module := c_default_module || '.lock_bal_concurrency_control';
130 END IF;
131
132 IF (c_level_procedure >= g_log_level)
133 THEN
134 TRACE (p_module => l_log_module
135 , p_msg => 'BEGIN ' || l_log_module
136 , p_level => c_level_procedure
137 );
138 END IF;
139
140 IF (c_level_procedure >= g_log_level)
141 THEN
142 TRACE (p_module => l_log_module
143 , p_msg => 'p_application_id ' || p_application_id
144 , p_level => c_level_procedure
145 );
146 END IF;
147 IF (c_level_procedure >= g_log_level)
148 THEN
149 TRACE (p_module => l_log_module
150 , p_msg => 'p_ledger_id ' || p_ledger_id
151 , p_level => c_level_procedure
152 );
153 END IF;
154 IF (c_level_procedure >= g_log_level)
155 THEN
156 TRACE (p_module => l_log_module
157 , p_msg => 'p_entity_id ' || p_entity_id
158 , p_level => c_level_procedure
159 );
160 END IF;
161 IF (c_level_procedure >= g_log_level)
162 THEN
163 TRACE (p_module => l_log_module
164 , p_msg => 'p_event_id ' || p_event_id
165 , p_level => c_level_procedure
166 );
167 END IF;
168 IF (c_level_procedure >= g_log_level)
169 THEN
170 TRACE (p_module => l_log_module
171 , p_msg => 'p_ae_header_id ' || p_ae_header_id
172 , p_level => c_level_procedure
173 );
174 END IF;
175 IF (c_level_procedure >= g_log_level)
176 THEN
177 TRACE (p_module => l_log_module
178 , p_msg => 'p_ae_line_num ' || p_ae_line_num
179 , p_level => c_level_procedure
180 );
181 END IF;
182 IF (c_level_procedure >= g_log_level)
183 THEN
184 TRACE (p_module => l_log_module
185 , p_msg => 'p_request_id ' || p_request_id
186 , p_level => c_level_procedure
187 );
188 END IF;
189 IF (c_level_procedure >= g_log_level)
190 THEN
191 TRACE (p_module => l_log_module
192 , p_msg => 'p_accounting_batch_id ' || p_accounting_batch_id
193 , p_level => c_level_procedure
194 );
195 END IF;
196 IF (c_level_procedure >= g_log_level)
197 THEN
198 TRACE (p_module => l_log_module
199 , p_msg => 'p_execution_mode ' || p_execution_mode
200 , p_level => c_level_procedure
201 );
202 END IF;
203 IF (c_level_procedure >= g_log_level)
204 THEN
205 TRACE (p_module => l_log_module
206 , p_msg => 'p_concurrency_class ' || p_concurrency_class
210
207 , p_level => c_level_procedure
208 );
209 END IF;
211 IF p_ledger_id IS NOT NULL
212 AND p_concurrency_class = 'BALANCES_CALCULATION'
213 AND p_execution_mode = 'C'
214 AND p_entity_id IS NULL
215 AND p_event_id IS NULL
216 AND p_ae_header_id IS NULL
217 AND p_ae_line_num IS NULL
218 AND p_accounting_batch_id IS NULL
219 THEN -- For sandalone balance process
220 SELECT xah.ledger_id
221 , xah.application_id
222 , xah.accounting_batch_id
223 BULK COLLECT INTO
224 g_ledger_array
225 , g_application_array
226 , g_accounting_batch_array
227 FROM xla_ae_headers xah
228 , xla_ae_lines xal
229 , gl_period_statuses gps
230 , xla_ledger_relationships_v xlr
231 WHERE xah.application_id = p_application_id
232 AND xah.ledger_id = p_ledger_id
233 AND xah.ae_header_id = xal.ae_header_id
234 AND xah.application_id = xal.application_id
235 AND xah.accounting_batch_id IS NOT NULL
236 -- to handle undo case. accounting_batch_id will be null if the entries were created by undo
237 AND ( xal.analytical_balance_flag = 'P'
238 OR xal.control_balance_flag = 'P'
239 )
240 AND xah.accounting_entry_status_code ='F'
241 AND xah.ledger_id = xlr.ledger_id
242 AND gps.period_name = xah.period_name
243 AND gps.ledger_id = DECODE(xlr.ledger_category_code, 'ALC' , xlr.primary_ledger_id, xlr.ledger_id)
244 AND gps.application_id=101
245 AND gps.closing_status in ('O','C','P')
246 AND gps.adjustment_period_flag = 'N'
247 GROUP BY xah.application_id
248 , xah.ledger_id
249 , xah.accounting_batch_id;
250
251
252 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
253 INSERT INTO xla_bal_concurrency_control
254 (ledger_id
255 , application_id
256 , concurrency_class
257 , accounting_batch_id
258 , execution_mode
259 , request_id
260 ) VALUES
261 ( g_ledger_array(i)
262 , g_application_array(i)
263 , p_concurrency_class
264 , g_accounting_batch_array(i)
265 , p_execution_mode
266 , p_request_id ) ;
267
268 ELSIF p_concurrency_class = 'BALANCES_CALCULATION'
269 THEN
270 -- begin bug 13614923
271 if p_entity_id is not null then
272 select distinct
273 ledger_id
274 ,application_id
275 ,accounting_batch_id
276 BULK COLLECT INTO
277 g_ledger_array
278 , g_application_array
279 , g_accounting_batch_array
280 from xla_ae_headers
281 where application_id = p_application_id
282 and entity_id = p_entity_id;
283
284
285 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
286 INSERT INTO xla_bal_concurrency_control
287 (ledger_id
288 , application_id
289 , concurrency_class
290 , accounting_batch_id
291 , execution_mode
292 , request_id
293 ) VALUES
294 ( g_ledger_array(i)
295 , g_application_array(i)
296 , p_concurrency_class
297 , g_accounting_batch_array(i)
298 , p_execution_mode
299 , -1*p_entity_id ) ;
300
301 elsif p_ae_header_id is not null then
302 select distinct
303 ledger_id
304 ,application_id
305 ,accounting_batch_id
306 BULK COLLECT INTO
307 g_ledger_array
308 , g_application_array
309 , g_accounting_batch_array
310 from xla_ae_headers
311 where application_id = p_application_id
312 and ae_header_id = p_ae_header_id;
313
314 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
315 INSERT INTO xla_bal_concurrency_control
316 (ledger_id
317 , application_id
318 , concurrency_class
319 , accounting_batch_id
320 , execution_mode
321 , request_id
322 ) VALUES
323 ( g_ledger_array(i)
324 , g_application_array(i)
325 , p_concurrency_class
326 , g_accounting_batch_array(i)
327 , p_execution_mode
328 , -1*p_ae_header_id ) ;
329
330
331 elsif p_accounting_batch_id is not null then
332 select distinct
333 ledger_id
334 ,application_id
335 ,p_accounting_batch_id
336 BULK COLLECT INTO
337 g_ledger_array
338 , g_application_array
339 , g_accounting_batch_array
340 from xla_ae_headers
341 where accounting_batch_id = p_accounting_batch_id
342 and application_id = p_application_id;
343
344 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
345 INSERT INTO xla_bal_concurrency_control
346 (ledger_id
350 , execution_mode
347 , application_id
348 , concurrency_class
349 , accounting_batch_id
351 , request_id
352 ) VALUES
353 ( g_ledger_array(i)
354 , g_application_array(i)
355 , p_concurrency_class
356 , g_accounting_batch_array(i)
357 , p_execution_mode
358 , p_request_id ) ;
359
360 else
361 -- end bug 13614923
362 fnd_file.put_line (fnd_file.LOG ,'inside not header,entity,accounting_batch');
363 select
364 p_ledger_id
365 ,p_application_id
366 ,p_accounting_batch_id
367 BULK COLLECT INTO
368 g_ledger_array
369 , g_application_array
370 , g_accounting_batch_array
371 FROM dual;
372
373 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
374 INSERT INTO xla_bal_concurrency_control
375 (ledger_id
376 , application_id
377 , concurrency_class
378 , accounting_batch_id
379 , execution_mode
380 , request_id
381 ) VALUES
382 ( g_ledger_array(i)
383 , g_application_array(i)
384 , p_concurrency_class
385 , g_accounting_batch_array(i)
386 , p_execution_mode
387 , p_request_id ) ;
388
389
390 end if;
391 ELSIF p_concurrency_class <> 'BALANCES_CALCULATION'
392 THEN -- open period balances program
393 select
394 p_ledger_id
395 BULK COLLECT INTO
396 g_ledger_array
397 FROM dual;
398
399 FORALL i in g_ledger_array.FIRST..g_ledger_array.LAST
400 INSERT INTO xla_bal_concurrency_control
401 (ledger_id
402 , concurrency_class
403 , request_id
404 ) VALUES
405 ( g_ledger_array(i)
406 , p_concurrency_class
407 , p_request_id ) ;
408
409 END IF;
410
411 IF (c_level_procedure >= g_log_level)
412 THEN
413 TRACE (p_module => l_log_module
414 , p_msg => '# rows inserted into xla_bal_concurrency_control ' || SQL%ROWCOUNT
415 , p_level => c_level_procedure
416 );
417 END IF;
418
419 IF (c_level_procedure >= g_log_level)
420 THEN
421 TRACE (p_module => l_log_module
422 , p_msg => 'END ' || l_log_module
423 , p_level => c_level_procedure
424 );
425 END IF;
426
427 COMMIT;
428 RETURN TRUE;
429
430 EXCEPTION
431 WHEN le_resource_busy or le_wait_expire or le_deadlock_detected
432 THEN
433 IF (c_level_error >= g_log_level)
434 THEN
435 TRACE (p_module => l_log_module
436 , p_msg => 'Cannot lock XLA_BAL_CONCURRENCY_CONTROL'
437 , p_level => c_level_error
438 );
439 END IF;
440
441 IF (c_level_procedure >= g_log_level)
442 THEN
443 TRACE (p_module => l_log_module
444 , p_msg => 'END ' || l_log_module
445 , p_level => c_level_procedure
446 );
447 END IF;
448
449 RAISE;
450
451 WHEN xla_exceptions_pkg.application_exception
452 THEN
453 RAISE;
454 WHEN OTHERS
455 THEN
456 xla_exceptions_pkg.raise_message
457 (p_location => 'xla_balances_calc_pkg.lock_bal_concurrency_control');
458 RAISE; -- Bug 13614923
459 END lock_bal_concurrency_control;
460
461 /*===============================================+
462 | |
463 | Private Function |
464 +------------------------------------------------+
465 | Calculate Analaytical Balances |
466 | |
467 +===============================================*/
468 FUNCTION calculate_analytical_balances ( p_application_id IN INTEGER
469 , p_ledger_id IN INTEGER
470 , p_entity_id IN INTEGER
471 , p_event_id IN INTEGER
472 , p_ae_header_id IN INTEGER
473 , p_ae_line_num IN INTEGER
474 , p_request_id IN INTEGER
475 , p_accounting_batch_id IN INTEGER
476 , p_operation_code IN VARCHAR2
477 , p_execution_mode IN VARCHAR2
478 )
479 RETURN BOOLEAN
480 IS
481 l_log_module VARCHAR2 (240);
482 l_insert1_count NUMBER;
483 l_merge_count NUMBER;
484 l_update1_count NUMBER;
485 l_insert2_count NUMBER;
486 l_update2_count NUMBER;
487 l_update_bal VARCHAR2(6000);
488 l_insert_bal VARCHAR2(6000);
489 l_update_processed VARCHAR2(5000);
490 l_summary_bind_array t_array_varchar;
494 --
491 l_summary_bind_count INTEGER :=1 ;
492 l_processed_bind_array t_array_varchar;
493 l_processed_bind_count INTEGER :=1 ;
495 l_summary_stmt VARCHAR2(7000):= 'INSERT INTO xla_ac_bal_interim_gt
496 ( application_id
497 , ledger_id
498 , code_combination_id
499 , analytical_criterion_code
500 , analytical_criterion_type_code
501 , amb_context_code
502 , ac1
503 , ac2
504 , ac3
505 , ac4
506 , ac5
507 , period_name
508 , effective_period_num
509 , period_balance_dr
510 , period_balance_cr
511 , period_year
512 )
513 SELECT /*+ $parallel$ use_nl(aeh) use_nl(acs) use_nl(ael) */
514 ael.application_id
515 , ael.ledger_id
516 , ael.code_combination_id
517 , acs.analytical_criterion_code
518 , acs.analytical_criterion_type_code
519 , acs.amb_context_code
520 , acs.ac1
521 , acs.ac2
522 , acs.ac3
523 , acs.ac4
524 , acs.ac5
525 , aeh.period_name
526 , gps.effective_period_num
527 , $period_balance_dr$
528 , $period_balance_cr$
529 , SUBSTR (gps.effective_period_num, 1, 4) period_year
530 FROM xla_ae_headers aeh
531 , xla_ae_lines ael
532 , xla_ae_line_acs acs
533 , xla_analytical_hdrs_b xbh
534 , gl_period_statuses gps
535 , xla_ledger_options xlo
536 , xla_ledger_relationships_v xlr
537 $bal_concurrency$
538 WHERE aeh.application_id = :'||l_summary_bind_count||'
539 AND aeh.accounting_entry_status_code = ''F''
540 AND aeh.balance_type_code = ''A''
541 AND ael.application_id = aeh.application_id
542 AND ael.ae_header_id = aeh.ae_header_id
543 AND ael.analytical_balance_flag = '''||g_preupdate_flag||'''
544 AND ael.ledger_id = aeh.ledger_id
545 AND acs.ae_header_id = ael.ae_header_id
546 AND acs.ae_line_num = ael.ae_line_num
547 AND xlr.ledger_id = aeh.ledger_id
548 AND xlo.application_id = aeh.application_id
549 AND xlo.ledger_id = DECODE (xlr.ledger_category_code , ''ALC''
550 , xlr.primary_ledger_id , xlr.ledger_id )
551 AND gps.ledger_id = xlo.ledger_id
552 AND gps.application_id = 101
553 AND gps.closing_status IN (''O'', ''C'', ''P'')
554 AND gps.effective_period_num <= xlo.effective_period_num
555 AND gps.adjustment_period_flag = ''N''
556 AND gps.period_name = aeh.period_name
557 AND xbh.analytical_criterion_code = acs.analytical_criterion_code
558 AND xbh.analytical_criterion_type_code = acs.analytical_criterion_type_code
559 AND xbh.amb_context_code = acs.amb_context_code
560 AND xbh.balancing_flag <> ''N''';
561 l_group_by_stmt VARCHAR2(1000):= ' GROUP BY ael.application_id
562 , ael.ledger_id
563 , ael.code_combination_id
564 , acs.analytical_criterion_code
565 , acs.analytical_criterion_type_code
566 , acs.amb_context_code
567 , acs.ac1
568 , acs.ac2
569 , acs.ac3
570 , acs.ac4
571 , acs.ac5
572 , aeh.period_name
576 THEN
573 , gps.effective_period_num';
574 BEGIN
575 IF g_log_enabled
577 l_log_module := c_default_module || '.calculate_analytical_balances';
578 END IF;
579 IF (c_level_procedure >= g_log_level)
580 THEN
581 TRACE (p_module => l_log_module
582 , p_msg => 'BEGIN ' || l_log_module
583 , p_level => c_level_procedure
584 );
585 END IF;
586 IF (c_level_exception >= g_log_level)
587 THEN
588 TRACE (p_module => l_log_module
589 , p_msg => 'p_application_id : ' || p_application_id
590 , p_level => c_level_exception
591 );
592 END IF;
593 IF (c_level_exception >= g_log_level)
594 THEN
595 TRACE (p_module => l_log_module
596 , p_msg => 'p_ledger_id : ' || p_ledger_id
597 , p_level => c_level_exception
598 );
599 END IF;
600 IF (c_level_exception >= g_log_level)
601 THEN
602 TRACE (p_module => l_log_module
603 , p_msg => 'p_accounting_batch_id : '
604 || p_accounting_batch_id
605 , p_level => c_level_exception
606 );
607 END IF;
608 IF (c_level_exception >= g_log_level)
609 THEN
610 TRACE (p_module => l_log_module
611 , p_msg => 'p_execution_mode : ' || p_execution_mode
612 , p_level => c_level_exception
613 );
614 END IF;
615 IF (c_level_exception >= g_log_level)
616 THEN
617 TRACE (p_module => l_log_module
618 , p_msg => 'request_id : ' || g_req_id
619 , p_level => c_level_exception
620 );
621 END IF;
622 IF (c_level_exception >= g_log_level)
623 THEN
624 TRACE (p_module => l_log_module
625 , p_msg => 'p_operation_code : ' || p_operation_code
626 , p_level => c_level_exception
627 );
628 END IF;
629
630 l_summary_bind_array(l_summary_bind_count) := to_char(p_application_id);
631 l_summary_bind_count := l_summary_bind_count+1;
632
633 -- add dynamic conditions
634 IF p_request_id IS NOT NULL AND p_request_id <> -1
635 THEN
636 l_summary_stmt := REPLACE (l_summary_stmt, '$bal_concurrency$', ',xla_bal_concurrency_control bcc');
637 l_summary_stmt := l_summary_stmt || '
638 AND bcc.request_id = :'||l_summary_bind_count||'
639 AND bcc.accounting_batch_id = aeh.accounting_batch_id
640 AND bcc.application_id = aeh.application_id
641 AND bcc.ledger_id = aeh.ledger_id'; -- Bug 14255667';
642
643 l_summary_bind_array(l_summary_bind_count) := to_char(p_request_id);
644 l_summary_bind_count := l_summary_bind_count+1;
645 ELSE
646 l_summary_stmt := REPLACE(l_summary_stmt,'$bal_concurrency$','');
647 END IF;
648
649
650 IF p_accounting_batch_id IS NOT NULL
651 THEN
652 l_summary_stmt := l_summary_stmt || '
653 AND aeh.accounting_batch_id = :'||l_summary_bind_count;
654
655 l_summary_bind_array(l_summary_bind_count) := to_char(p_accounting_batch_id);
656 l_summary_bind_count := l_summary_bind_count+1;
657 END IF;
658
659 IF p_ledger_id IS NOT NULL
660 AND p_accounting_batch_id IS NULL
661 AND p_event_id IS NULL
662 AND p_entity_id IS NULL
663 AND p_ae_header_id IS NULL
664 AND p_ae_line_num IS NULL
665 THEN
666 l_summary_stmt := l_summary_stmt || '
667 AND aeh.ledger_id = :'||l_summary_bind_count;
668
669 l_summary_bind_array(l_summary_bind_count) := to_char(p_ledger_id);
670 l_summary_bind_count := l_summary_bind_count+1;
671 END IF;
672
673 IF p_entity_id IS NOT NULL
674 THEN
675 l_summary_stmt := l_summary_stmt || '
676 AND aeh.entity_id = :'||l_summary_bind_count;
677
678 l_summary_bind_array(l_summary_bind_count) := to_char(p_entity_id);
679 l_summary_bind_count := l_summary_bind_count+1;
680 END IF;
681
682 IF p_event_id IS NOT NULL
683 THEN
684 l_summary_stmt := l_summary_stmt || '
685 AND aeh.event_id = :'||l_summary_bind_count;
686
687 l_summary_bind_array(l_summary_bind_count) := to_char(p_event_id);
688 l_summary_bind_count := l_summary_bind_count+1;
689 END IF;
690
691 IF p_ae_header_id IS NOT NULL
692 THEN
693 l_summary_stmt := l_summary_stmt || '
694 AND aeh.ae_header_id = :'||l_summary_bind_count;
695
696 l_summary_bind_array(l_summary_bind_count) := to_char(p_ae_header_id);
697 l_summary_bind_count := l_summary_bind_count+1;
698 END IF;
699
700 IF p_ae_line_num IS NOT NULL
701 THEN
702 l_summary_stmt := l_summary_stmt || '
703 AND ael.ae_line_num = :'||l_summary_bind_count;
704
705 l_summary_bind_array(l_summary_bind_count) := to_char(p_ae_line_num);
706 l_summary_bind_count := l_summary_bind_count+1;
707 END IF;
708
709 l_summary_bind_count := l_summary_bind_count-1;
710
711 -- Replace perf. hint dynamically
712 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
713 l_summary_stmt := REPLACE(l_summary_stmt,'$parallel$','parallel(aeh)');
714 ELSE
715 l_summary_stmt := REPLACE(l_summary_stmt,'$parallel$','');
716 END IF;
717
718 IF p_operation_code = 'A' --Add
719 THEN
723 THEN
720 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_dr$','SUM (NVL (ael.accounted_dr, 0)) period_balance_dr');
721 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_cr$','SUM (NVL (ael.accounted_cr, 0)) period_balance_cr');
722 ELSIF p_operation_code = 'R' -- Remove
724 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_dr$','SUM (NVL (ael.accounted_dr, 0)) * -1 period_balance_dr');
725 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_cr$','SUM (NVL (ael.accounted_cr, 0)) * -1 period_balance_cr');
726 END IF;
727
728 l_summary_stmt := l_summary_stmt || l_group_by_stmt;
729
730 IF (c_level_procedure >= g_log_level)
731 THEN
732 trace
733 (p_msg => 'AC l_summary_stmt_1:'||substr(l_summary_stmt, 1, 1000)
734 ,p_level => C_LEVEL_STATEMENT
735 ,p_module => l_log_module);
736 trace
737 (p_msg => 'AC l_summary_stmt_2:'||substr(l_summary_stmt, 1001, 1000)
738 ,p_level => C_LEVEL_STATEMENT
739 ,p_module => l_log_module);
740 trace
741 (p_msg => 'AC l_summary_stmt_3:'||substr(l_summary_stmt, 2001, 1000)
742 ,p_level => C_LEVEL_STATEMENT
743 ,p_module => l_log_module);
744 trace
745 (p_msg => 'AC l_summary_stmt_4:'||substr(l_summary_stmt, 3001, 1000)
746 ,p_level => C_LEVEL_STATEMENT
747 ,p_module => l_log_module);
748 trace
749 (p_msg => 'AC l_summary_stmt_5:'||substr(l_summary_stmt, 4001, 1000)
750 ,p_level => C_LEVEL_STATEMENT
751 ,p_module => l_log_module);
752 trace
753 (p_msg => 'AC l_summary_stmt_6:'||substr(l_summary_stmt, 5001, 1000)
754 ,p_level => C_LEVEL_STATEMENT
755 ,p_module => l_log_module);
756 trace
757 (p_msg => 'AC l_summary_stmt_7:'||substr(l_summary_stmt, 6001, 999)
758 ,p_level => C_LEVEL_STATEMENT
759 ,p_module => l_log_module);
760 trace
761 (p_msg => 'l_summary_bind_count : '||l_summary_bind_count
762 ,p_level => C_LEVEL_STATEMENT
763 ,p_module => l_log_module);
764
765 END IF;
766
767 IF l_summary_bind_count = 1
768 THEN
769 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1);
770 ELSIF l_summary_bind_count = 2
771 THEN
772 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2);
773 ELSIF l_summary_bind_count = 3
774 THEN
775 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3);
776 ELSIF l_summary_bind_count = 4
777 THEN
778 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
779 ,l_summary_bind_array(4);
780 ELSIF l_summary_bind_count = 5
781 THEN
782 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
783 ,l_summary_bind_array(4), l_summary_bind_array(5);
784 ELSIF l_summary_bind_count = 6
785 THEN
786 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
787 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6);
788 ELSIF l_summary_bind_count = 7
789 THEN
790 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
791 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6)
792 , l_summary_bind_array(7);
793 ELSIF l_summary_bind_count = 8
794 THEN
795 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
796 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6)
797 , l_summary_bind_array(7), l_summary_bind_array(8);
798 END IF;
799
800 l_insert1_count := SQL%ROWCOUNT;
801
802 IF (c_level_procedure >= g_log_level)
803 THEN
804 TRACE (p_module => l_log_module
805 , p_msg => '# rows inserted in XLA_AC_BAL_INTERIM_GT : '
806 || l_insert1_count
807 , p_level => c_level_procedure
808 );
809 END IF;
810
811 IF l_insert1_count = 0
812 THEN
813 IF (c_level_procedure >= g_log_level)
814 THEN
815 TRACE (p_module => l_log_module
816 , p_msg => 'No Records to process ' || l_insert1_count
817 , p_level => c_level_procedure
818 );
819 END IF;
820
821 RETURN TRUE; --No records to process
822 END IF;
823
824 --
825 -- Calculate the bgin balance and insert records into summary table for future periods
826 --
827 MERGE INTO xla_ac_bal_interim_gt stmp
828 USING (SELECT period_balance_dr
829 , period_balance_cr
830 -- Begin Bug 13498442
831 , decode (year_end_carry_forward_code,'Y',
832 SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
833 , analytical_criterion_code, analytical_criterion_type_code, amb_context_code
834 , ac1, ac2, ac3, ac4, ac5 ORDER BY application_id, ledger_id
835 , code_combination_id
836 , analytical_criterion_code
837 , analytical_criterion_type_code
838 , amb_context_code
839 , ac1
840 , ac2
841 , ac3
842 , ac4
846 , analytical_criterion_code, analytical_criterion_type_code, amb_context_code
843 , ac5
844 , effective_period_num)
845 , SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
847 , ac1, ac2, ac3, ac4, ac5, period_year ORDER BY application_id, ledger_id
848 , code_combination_id
849 , analytical_criterion_code
850 , analytical_criterion_type_code
851 , amb_context_code
852 , ac1
853 , ac2
854 , ac3
855 , ac4
856 , ac5
857 , effective_period_num) ) xal_beginning_balance_dr
858 , decode (year_end_carry_forward_code,'Y',
859 SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
860 , analytical_criterion_code, analytical_criterion_type_code, amb_context_code
861 , ac1, ac2, ac3, ac4, ac5 ORDER BY application_id, ledger_id
862 , code_combination_id
863 , analytical_criterion_code
864 , analytical_criterion_type_code
865 , amb_context_code
866 , ac1
867 , ac2
868 , ac3
869 , ac4
870 , ac5
871 , effective_period_num)
872 , SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
873 , analytical_criterion_code, analytical_criterion_type_code, amb_context_code
874 , ac1, ac2, ac3, ac4, ac5, period_year ORDER BY application_id, ledger_id
875 , code_combination_id
876 , analytical_criterion_code
877 , analytical_criterion_type_code
878 , amb_context_code
879 , ac1
880 , ac2
881 , ac3
882 , ac4
883 , ac5
884 , effective_period_num) ) xal_beginning_balance_cr
885 -- End Bug 13498442
886 , application_id
887 , ledger_id
888 , code_combination_id
889 , analytical_criterion_code
890 , analytical_criterion_type_code
891 , amb_context_code
892 , ac1
893 , ac2
894 , ac3
895 , ac4
896 , ac5
897 , period_name
898 , effective_period_num
899 , period_year
900 FROM (SELECT /*+ leading(xag,xal_bal) */
901 xal_bal.application_id
902 , xal_bal.ledger_id
903 , xal_bal.code_combination_id
904 code_combination_id
905 , xal_bal.analytical_criterion_code
906 , xal_bal.analytical_criterion_type_code
907 , xal_bal.amb_context_code
908 , xal_bal.ac1
909 , xal_bal.ac2
910 , xal_bal.ac3
911 , xal_bal.ac4
912 , xal_bal.ac5
913 , xal_bal.year_end_carry_forward_code --Bug13498442
914 , xal_bal.period_name period_name
915 , xal_bal.effective_period_num
916 , xal_bal.period_balance_dr
917 , xal_bal.period_balance_cr
918 , xal_bal.period_year
919 , LAG (NVL (xal_bal.period_balance_dr, 0)
920 , 1
921 , NVL (xal_bal.beginning_balance_dr, 0)
922 ) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
923 , xal_bal.code_combination_id, xal_bal.analytical_criterion_type_code
924 , xal_bal.amb_context_code, xal_bal.ac1, xal_bal.ac2, xal_bal.ac3, xal_bal.ac4
925 , xal_bal.ac5 ORDER BY xal_bal.application_id
926 , xal_bal.ledger_id
927 , xal_bal.code_combination_id
928 , xal_bal.analytical_criterion_code
929 , xal_bal.analytical_criterion_type_code
930 , xal_bal.amb_context_code
931 , xal_bal.ac1
932 , xal_bal.ac2
933 , xal_bal.ac3
934 , xal_bal.ac4
935 , xal_bal.ac5
936 , xal_bal.effective_period_num) lag_dr
937 , LAG (NVL (xal_bal.period_balance_cr, 0)
938 , 1
939 , NVL (xal_bal.beginning_balance_cr, 0)
940 ) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
941 , xal_bal.code_combination_id, xal_bal.analytical_criterion_type_code
942 , xal_bal.amb_context_code, xal_bal.ac1, xal_bal.ac2, xal_bal.ac3
943 , xal_bal.ac4, xal_bal.ac5 ORDER BY xal_bal.application_id
944 , xal_bal.ledger_id
945 , xal_bal.code_combination_id
946 , xal_bal.analytical_criterion_code
947 , xal_bal.analytical_criterion_type_code
948 , xal_bal.amb_context_code
949 , xal_bal.ac1
950 , xal_bal.ac2
954 , xal_bal.effective_period_num) lag_cr
951 , xal_bal.ac3
952 , xal_bal.ac4
953 , xal_bal.ac5
955 FROM (SELECT tmp.application_id
956 , tmp.ledger_id
957 , tmp.code_combination_id
958 , tmp.analytical_criterion_code
959 , tmp.analytical_criterion_type_code
960 , tmp.amb_context_code
961 , decode (xbh.year_end_carry_forward_code,'Y','Y','A','Y','B',decode(gcc.account_type,'A','Y','L','Y','O','Y','N'),'N') year_end_carry_forward_code --Bug13498442
962 , MAX
963 (DECODE
964 (gps.effective_period_num
965 , tmp.effective_period_num, tmp.period_balance_dr
966 , NULL
967 )
968 ) period_balance_dr
969 , MAX
970 (DECODE
971 (gps.effective_period_num
972 , tmp.effective_period_num, tmp.period_balance_cr
973 , NULL
974 )
975 ) period_balance_cr
976 , tmp.beginning_balance_dr
977 , tmp.beginning_balance_cr
978 , tmp.ac1
979 , tmp.ac2
980 , tmp.ac3
981 , tmp.ac4
982 , tmp.ac5
983 , gps.period_name
984 , gps.effective_period_num
985 , gps.period_year
986 FROM gl_period_statuses gps
987 ,xla_analytical_hdrs_b xbh --Bug13498442
988 , gl_code_combinations gcc --Bug13498442
989 , xla_ac_bal_interim_gt tmp
990 , xla_ledger_options xlo
991 , xla_ledger_relationships_v xlr
992 WHERE gps.effective_period_num <= xlo.effective_period_num
993 AND gps.effective_period_num >= tmp.effective_period_num
994 AND gps.closing_status IN ('O', 'C', 'P')
995 AND gps.adjustment_period_flag = 'N'
996 AND gps.application_id = 101
997 AND gps.ledger_id = xlo.ledger_id
998 AND gcc.code_combination_id = tmp.code_combination_id
999 AND xlo.application_id = tmp.application_id
1000 AND tmp.ledger_id = xlr.ledger_id
1001 AND xlo.ledger_id = DECODE(xlr.ledger_category_code, 'ALC'
1002 , xlr.primary_ledger_id, tmp.ledger_id)
1003 --Begin Bug13498442
1004 AND xbh.analytical_criterion_code = tmp.analytical_criterion_code
1005 AND xbh.analytical_criterion_type_code = tmp.analytical_criterion_type_code
1006 AND xbh.amb_context_code = tmp.amb_context_code
1007 --End Bug13498442
1008 GROUP BY tmp.application_id
1009 , tmp.ledger_id
1010 , tmp.code_combination_id
1011 , tmp.analytical_criterion_code
1012 , tmp.analytical_criterion_type_code
1013 , tmp.amb_context_code
1014 , decode (xbh.year_end_carry_forward_code,'Y','Y','A','Y','B',decode(gcc.account_type,'A','Y','L','Y','O','Y','N'),'N') --Bug13498442
1015 , tmp.beginning_balance_dr
1016 , tmp.beginning_balance_cr
1017 , tmp.ac1
1018 , tmp.ac2
1019 , tmp.ac3
1020 , tmp.ac4
1021 , tmp.ac5
1022 , gps.period_name
1023 , gps.effective_period_num
1024 , gps.period_year) xal_bal
1025 ORDER BY xal_bal.application_id
1026 , xal_bal.ledger_id
1027 , xal_bal.code_combination_id
1028 , xal_bal.analytical_criterion_code
1029 , xal_bal.analytical_criterion_type_code
1030 , xal_bal.amb_context_code
1031 , xal_bal.ac1
1032 , xal_bal.ac2
1033 , xal_bal.ac3
1034 , xal_bal.ac4
1035 , xal_bal.ac5
1036 , xal_bal.effective_period_num
1037 , xal_bal.period_year)) tmp
1041 AND stmp.analytical_criterion_code = tmp.analytical_criterion_code
1038 ON ( stmp.application_id = tmp.application_id
1039 AND stmp.ledger_id = tmp.ledger_id
1040 AND stmp.code_combination_id = tmp.code_combination_id
1042 AND stmp.analytical_criterion_type_code = tmp.analytical_criterion_type_code
1043 AND stmp.amb_context_code = tmp.amb_context_code
1044 AND NVL (stmp.ac1, ' ') = NVL (tmp.ac1, ' ')
1045 AND NVL (stmp.ac2, ' ') = NVL (tmp.ac2, ' ')
1046 AND NVL (stmp.ac3, ' ') = NVL (tmp.ac3, ' ')
1047 AND NVL (stmp.ac4, ' ') = NVL (tmp.ac4, ' ')
1048 AND NVL (stmp.ac5, ' ') = NVL (tmp.ac5, ' ')
1049 AND stmp.effective_period_num = tmp.effective_period_num)
1050 WHEN MATCHED THEN
1051 UPDATE
1052 SET stmp.beginning_balance_dr = tmp.xal_beginning_balance_dr
1053 , stmp.beginning_balance_cr = tmp.xal_beginning_balance_cr
1054 WHEN NOT MATCHED THEN
1055 INSERT (stmp.application_id, stmp.ledger_id
1056 , stmp.code_combination_id, stmp.analytical_criterion_code
1057 , stmp.analytical_criterion_type_code
1058 , stmp.amb_context_code, stmp.ac1, stmp.ac2, stmp.ac3
1059 , stmp.ac4, stmp.ac5, stmp.period_balance_dr
1060 , stmp.period_balance_cr, stmp.beginning_balance_dr
1061 , stmp.beginning_balance_cr, stmp.period_name
1062 , stmp.effective_period_num, stmp.period_year)
1063 VALUES (tmp.application_id, tmp.ledger_id
1064 , tmp.code_combination_id, tmp.analytical_criterion_code
1065 , tmp.analytical_criterion_type_code, tmp.amb_context_code
1066 , tmp.ac1, tmp.ac2, tmp.ac3, tmp.ac4, tmp.ac5
1067 , tmp.period_balance_dr, tmp.period_balance_cr
1068 , tmp.xal_beginning_balance_dr
1069 , tmp.xal_beginning_balance_cr, tmp.period_name
1070 , tmp.effective_period_num, tmp.period_year);
1071
1072 l_merge_count := SQL%ROWCOUNT;
1073
1074 IF (c_level_procedure >= g_log_level)
1075 THEN
1076 TRACE (p_module => l_log_module
1077 , p_msg => '# rows merged in XLA_AC_BAL_INTERIM_GT : '
1078 || l_merge_count
1079 , p_level => c_level_procedure
1080 );
1081 END IF;
1082
1083 --
1084 -- Update the BEGINNING BALANCE, PERIOD BALANCE into the xla_ac_balances table if record already exists for that group.
1085 --
1086 l_update_bal := 'UPDATE /*+ index(b,xla_ac_balances_N99) */xla_ac_balances b
1087 SET last_update_date = '''||g_date||'''
1088 , last_updated_by = '||g_user_id||'
1089 , last_update_login = '||g_login_id||'
1090 , program_update_date = '''||g_date||'''
1091 , program_application_id = '||g_prog_appl_id||'
1092 , program_id = '||g_prog_id||'
1093 , request_id = '||g_req_id||'
1094 ,(period_balance_dr, period_balance_cr
1095 , beginning_balance_dr, beginning_balance_cr) = (SELECT /*+ $parallel$ index(tmp,xla_ac_bgnbal_gt_U1) */
1096 NVL (b.period_balance_dr, 0)
1097 + NVL (tmp.period_balance_dr, 0) period_balance_dr
1098 , NVL (b.period_balance_cr, 0)
1099 + NVL (tmp.period_balance_cr, 0) period_balance_cr
1100 , NVL (b.beginning_balance_dr, 0)
1101 + NVL (tmp.beginning_balance_dr, 0) beginning_balance_dr
1102 , NVL (b.beginning_balance_cr, 0)
1103 + NVL (tmp.beginning_balance_cr, 0) beginning_balance_cr
1104 FROM xla_ac_bal_interim_gt tmp
1105 WHERE tmp.application_id = b.application_id
1106 AND tmp.ledger_id = b.ledger_id
1107 AND tmp.code_combination_id = b.code_combination_id
1108 AND tmp.analytical_criterion_code = b.analytical_criterion_code
1109 AND tmp.analytical_criterion_type_code = b.analytical_criterion_type_code
1110 AND tmp.amb_context_code = b.amb_context_code
1111 AND NVL (tmp.ac1, '' '') = NVL (b.ac1, '' '')
1112 AND NVL (tmp.ac2, '' '') = NVL (b.ac2, '' '')
1113 AND NVL (tmp.ac3, '' '') = NVL (b.ac3, '' '')
1114 AND NVL (tmp.ac4, '' '') = NVL (b.ac4, '' '')
1115 AND NVL (tmp.ac5, '' '') = NVL (b.ac5, '' '')
1119 , b.code_combination_id
1116 AND tmp.effective_period_num = b.effective_period_num)
1117 WHERE ( b.application_id
1118 , b.ledger_id
1120 , b.analytical_criterion_code
1121 , b.analytical_criterion_type_code
1122 , b.amb_context_code
1123 , NVL (b.ac1, '' '')
1124 , NVL (b.ac2, '' '')
1125 , NVL (b.ac3, '' '')
1126 , NVL (b.ac4, '' '')
1127 , NVL (b.ac5, '' '')
1128 , b.effective_period_num
1129 ) IN (SELECT /*+ $parallel_1$ full(xal_bal1) */
1130 xal_bal1.application_id
1131 , xal_bal1.ledger_id
1132 , xal_bal1.code_combination_id
1133 , xal_bal1.analytical_criterion_code
1134 , xal_bal1.analytical_criterion_type_code
1135 , xal_bal1.amb_context_code
1136 , NVL (xal_bal1.ac1, '' '')
1137 , NVL (xal_bal1.ac2, '' '')
1138 , NVL (xal_bal1.ac3, '' '')
1139 , NVL (xal_bal1.ac4, '' '')
1140 , NVL (xal_bal1.ac5, '' '')
1141 , xal_bal1.effective_period_num
1142 FROM xla_ac_bal_interim_gt xal_bal1)';
1143
1144 -- Replace parallel hint based on the profile option
1145 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
1146 l_update_bal := REPLACE(l_update_bal,'$parallel$','parallel(tmp)');
1147 l_update_bal := REPLACE(l_update_bal,'$parallel_1$','parallel(xal_bal1)');
1148 ELSE
1149 l_update_bal := REPLACE(l_update_bal,'$parallel$','');
1150 l_update_bal := REPLACE(l_update_bal,'$parallel_1$','');
1151 END IF;
1152
1153 IF (c_level_procedure >= g_log_level)
1154 THEN
1155 trace
1156 (p_msg => 'AC l_update_bal_1:'||substr(l_update_bal, 1, 1000)
1157 ,p_level => C_LEVEL_STATEMENT
1158 ,p_module => l_log_module);
1159 trace
1160 (p_msg => 'AC l_update_bal_2:'||substr(l_update_bal, 1001, 1000)
1161 ,p_level => C_LEVEL_STATEMENT
1162 ,p_module => l_log_module);
1163 trace
1164 (p_msg => 'AC l_update_bal_3:'||substr(l_update_bal, 2001, 1000)
1165 ,p_level => C_LEVEL_STATEMENT
1166 ,p_module => l_log_module);
1167 trace
1168 (p_msg => 'AC l_update_bal_4:'||substr(l_update_bal, 3001, 1000)
1169 ,p_level => C_LEVEL_STATEMENT
1170 ,p_module => l_log_module);
1171 trace
1172 (p_msg => 'AC l_update_bal_5:'||substr(l_update_bal, 4001, 1000)
1173 ,p_level => C_LEVEL_STATEMENT
1174 ,p_module => l_log_module);
1175 trace
1176 (p_msg => 'AC l_update_bal_6:'||substr(l_update_bal, 5001, 999)
1177 ,p_level => C_LEVEL_STATEMENT
1178 ,p_module => l_log_module);
1179 END IF;
1180
1181
1182 --Execute sql
1183
1184 EXECUTE IMMEDIATE l_update_bal;
1185
1186
1187 l_update1_count := SQL%ROWCOUNT;
1188
1189 IF (c_level_procedure >= g_log_level)
1190 THEN
1191 TRACE (p_module => l_log_module
1192 , p_msg => '# rows updated in xla_ac_balances : '
1193 || l_update1_count
1194 , p_level => c_level_procedure
1195 );
1196 END IF;
1197
1198 --
1199 -- Insert record into xla_ac_balance if record does not exist
1200 --
1201 IF l_update1_count <> l_merge_count
1202 THEN
1203 -- insert rows only if the rows updated is not equal to the total no of rows in gt table
1204 l_insert_bal := 'INSERT INTO xla_ac_balances xba
1205 ( application_id
1206 , ledger_id
1207 , code_combination_id
1208 , analytical_criterion_code
1209 , analytical_criterion_type_code
1210 , amb_context_code
1211 , ac1
1212 , ac2
1213 , ac3
1214 , ac4
1215 , ac5
1216 , period_name
1217 , period_year
1218 , first_period_flag
1219 , period_balance_dr
1220 , period_balance_cr
1221 , beginning_balance_dr
1222 , beginning_balance_cr
1223 , initial_balance_flag
1224 , effective_period_num
1225 , creation_date
1226 , created_by
1227 , last_update_date
1228 , last_updated_by
1229 , last_update_login
1230 , program_update_date
1231 , program_application_id
1232 , program_id
1233 , request_id
1234 )
1238 , temp.code_combination_id
1235 SELECT /*+ $parallel$ */
1236 temp.application_id
1237 , temp.ledger_id
1239 , temp.analytical_criterion_code
1240 , temp.analytical_criterion_type_code
1241 , temp.amb_context_code
1242 , temp.ac1
1243 , temp.ac2
1244 , temp.ac3
1245 , temp.ac4
1246 , temp.ac5
1247 , gps.period_name
1248 , gps.period_year
1249 , DECODE (gps.period_num, 1, ''Y'', ''N'') first_period_flag
1250 , temp.period_balance_dr
1251 , temp.period_balance_cr
1252 , temp.beginning_balance_dr
1253 , temp.beginning_balance_cr
1254 , ''N'' initial_balance_flag
1255 , temp.effective_period_num
1256 , '''||g_date||'''
1257 , '||g_user_id||'
1258 , '''||g_date||'''
1259 , '||g_user_id||'
1260 , '||g_login_id||'
1261 , '''||g_date||'''
1262 , '||g_prog_appl_id||'
1263 , '||g_prog_id||'
1264 , '||g_req_id||'
1265 FROM xla_ac_bal_interim_gt temp
1266 , xla_analytical_hdrs_b xbh
1267 , gl_code_combinations gcc
1268 , gl_period_statuses gps
1269 , xla_ledger_relationships_v xlr
1270 WHERE xlr.ledger_id = temp.ledger_id
1271 AND gps.ledger_id = DECODE(xlr.ledger_category_code,''ALC''
1272 ,xlr.primary_ledger_id , temp.ledger_id)
1273 AND gps.effective_period_num = temp.effective_period_num
1274 AND gps.application_id = 101
1275 AND gps.adjustment_period_flag = ''N''
1276 AND gps.closing_status IN (''O'', ''C'', ''P'')
1277 AND gcc.code_combination_id = temp.code_combination_id
1278 AND xbh.analytical_criterion_code = temp.analytical_criterion_code
1279 AND xbh.analytical_criterion_type_code = temp.analytical_criterion_type_code
1280 AND xbh.amb_context_code = temp.amb_context_code
1281 AND xbh.balancing_flag <> ''N''
1282 AND ( gps.period_year = SUBSTR (temp.effective_period_num, 1, 4)
1283 OR xbh.year_end_carry_forward_code = ''A''
1284 OR ( xbh.year_end_carry_forward_code = ''B''
1285 AND gcc.account_type IN (''A'', ''L'', ''O'')
1286 )
1287 )
1288 AND NOT EXISTS ( SELECT /*+ no_unnest $parallel_1$ */ 1
1289 FROM xla_ac_balances xba
1290 WHERE xba.application_id = temp.application_id
1291 AND xba.ledger_id = temp.ledger_id
1292 AND xba.code_combination_id = temp.code_combination_id
1293 AND xba.analytical_criterion_code = temp.analytical_criterion_code
1294 AND xba.analytical_criterion_type_code = temp.analytical_criterion_type_code
1295 AND xba.amb_context_code = temp.amb_context_code
1296 AND NVL (xba.ac1, '' '') = NVL (temp.ac1, '' '')
1297 AND NVL (xba.ac2, '' '') = NVL (temp.ac2, '' '')
1298 AND NVL (xba.ac3, '' '') = NVL (temp.ac3, '' '')
1299 AND NVL (xba.ac4, '' '') = NVL (temp.ac4, '' '')
1300 AND NVL (xba.ac5, '' '') = NVL (temp.ac5, '' '')
1301 AND xba.period_name = gps.period_name)';
1302
1303 -- Replace parallel hint based on profile option
1304 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
1305 l_insert_bal := REPLACE(l_insert_bal,'$parallel$','parallel(temp)');
1306 l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','parallel(xba)');
1307 ELSE
1308 l_insert_bal := REPLACE(l_insert_bal,'$parallel$','');
1309 l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','');
1310 END IF;
1311
1312 IF (c_level_procedure >= g_log_level)
1313 THEN
1314 trace
1315 (p_msg => 'AC l_insert_bal_1:'||substr(l_insert_bal, 1, 1000)
1316 ,p_level => C_LEVEL_STATEMENT
1317 ,p_module => l_log_module);
1318 trace
1319 (p_msg => 'AC l_insert_bal_2:'||substr(l_insert_bal, 1001, 1000)
1320 ,p_level => C_LEVEL_STATEMENT
1321 ,p_module => l_log_module);
1322 trace
1323 (p_msg => 'AC l_insert_bal_3:'||substr(l_insert_bal, 2001, 1000)
1324 ,p_level => C_LEVEL_STATEMENT
1325 ,p_module => l_log_module);
1326 trace
1327 (p_msg => 'AC l_insert_bal_4:'||substr(l_insert_bal, 3001, 1000)
1328 ,p_level => C_LEVEL_STATEMENT
1329 ,p_module => l_log_module);
1330 trace
1331 (p_msg => 'AC l_insert_bal_5:'||substr(l_insert_bal, 4001, 1000)
1332 ,p_level => C_LEVEL_STATEMENT
1333 ,p_module => l_log_module);
1337 ,p_module => l_log_module);
1334 trace
1335 (p_msg => 'AC l_insert_bal_6:'||substr(l_insert_bal, 5001, 999)
1336 ,p_level => C_LEVEL_STATEMENT
1338 END IF;
1339
1340 --Execute sql
1341 EXECUTE IMMEDIATE l_insert_bal;
1342
1343 l_insert2_count := SQL%ROWCOUNT;
1344 END IF;
1345
1346
1347
1348
1349 IF (c_level_procedure >= g_log_level)
1350 THEN
1351 TRACE (p_module => l_log_module
1352 , p_msg => ' # rows inserted into xla_ac_balances : '
1353 || l_insert2_count
1354 , p_level => c_level_procedure
1355 );
1356 END IF;
1357
1358 --
1359 --update records being processed to 'y' in xla_ae_lines
1360 --
1361
1362 l_update_processed := 'UPDATE /*+ use_nl(ael) */xla_ae_lines ael
1363 SET analytical_balance_flag = '''||g_postupdate_flag||'''
1364 WHERE application_id = :'||l_processed_bind_count||'
1365 AND analytical_balance_flag = '''||g_preupdate_flag||'''
1366 AND (ae_header_id,ae_line_num) IN
1367 ( SELECT /*+ $parallel$ leading(aeh) */
1368 ael.ae_header_id
1369 ,ael.ae_line_num
1370 FROM xla_ae_headers aeh
1371 , xla_ae_lines ael
1372 , gl_period_statuses gps
1373 , xla_ledger_options xlo
1374 , xla_ledger_relationships_v xlr
1375 $bal_concurrency$
1376 WHERE aeh.accounting_entry_status_code = ''F''
1377 AND aeh.application_id = :'||l_processed_bind_count||'
1378 AND aeh.ledger_id = xlr.ledger_id
1379 AND ael.ae_header_id = aeh.ae_header_id
1380 AND aeh.balance_type_code = ''A''
1381 AND ael.analytical_balance_flag = '''||g_preupdate_flag||'''
1382 AND ael.application_id = aeh.application_id
1383 AND xlo.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
1384 ,xlr.primary_ledger_id, xlr.ledger_id)
1385 AND gps.ledger_id = xlo.ledger_id
1386 AND gps.application_id = 101
1387 AND gps.closing_status IN (''O'', ''C'', ''P'')
1388 AND gps.effective_period_num <= xlo.effective_period_num
1389 AND gps.adjustment_period_flag = ''N''
1390 AND gps.period_name = aeh.period_name
1391 ' ;
1392 l_processed_bind_array(l_processed_bind_count) := to_char(p_application_id);
1393 l_processed_bind_count := l_processed_bind_count+1;
1394
1395 --Add dynamic conditions
1396 IF p_request_id IS NOT NULL AND p_request_id <> -1
1397 THEN
1398 l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$',',xla_bal_concurrency_control bcc');
1399 l_update_processed := l_update_processed||
1400 ' AND bcc.request_id = :'||l_processed_bind_count||'
1401 AND bcc.accounting_batch_id = aeh.accounting_batch_id
1402 AND bcc.application_id = aeh.application_id' ;
1403
1404 l_processed_bind_array(l_processed_bind_count) := to_char(p_request_id);
1405 l_processed_bind_count := l_processed_bind_count+1;
1406
1407 ELSE
1408 l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$','');
1409 END IF;
1410
1411 IF p_accounting_batch_id IS NOT NULL
1412 THEN
1413 l_update_processed := l_update_processed||
1414 ' AND aeh.accounting_batch_id = :'||l_processed_bind_count;
1415
1416 l_processed_bind_array(l_processed_bind_count) := to_char(p_accounting_batch_id);
1417 l_processed_bind_count := l_processed_bind_count+1;
1418 END IF;
1419
1420 IF p_event_id IS NOT NULL
1421 THEN
1422 l_update_processed := l_update_processed||
1423 ' AND aeh.event_id = :'||l_processed_bind_count;
1424
1425 l_processed_bind_array(l_processed_bind_count) := to_char(p_event_id);
1426 l_processed_bind_count := l_processed_bind_count+1;
1427 END IF;
1428
1429 IF p_entity_id IS NOT NULL
1430 THEN
1431 l_update_processed := l_update_processed||
1432 ' AND aeh.entity_id = :'||l_processed_bind_count;
1433
1437 IF p_ae_header_id IS NOT NULL
1434 l_processed_bind_array(l_processed_bind_count) := to_char(p_entity_id);
1435 l_processed_bind_count := l_processed_bind_count+1;
1436 END IF;
1438 THEN
1439 l_update_processed := l_update_processed||
1440 ' AND aeh.ae_header_id = :'||l_processed_bind_count;
1441
1442 l_processed_bind_array(l_processed_bind_count) := to_char(p_ae_header_id);
1443 l_processed_bind_count := l_processed_bind_count+1;
1444
1445 END IF;
1446 IF p_ae_line_num IS NOT NULL
1447 THEN
1448 l_update_processed := l_update_processed||
1449 ' AND ael.ae_line_num = :'||l_processed_bind_count;
1450
1451 l_processed_bind_array(l_processed_bind_count) := to_char(p_ae_line_num);
1452 l_processed_bind_count := l_processed_bind_count+1;
1453 END IF;
1454
1455 IF p_ledger_id IS NOT NULL
1456 AND p_accounting_batch_id IS NULL
1457 AND p_event_id IS NULL
1458 AND p_entity_id IS NULL
1459 AND p_ae_header_id IS NULL
1460 AND p_ae_line_num IS NULL
1461 THEN
1462 l_update_processed := l_update_processed || '
1463 AND aeh.ledger_id = :'||l_processed_bind_count;
1464
1465 l_processed_bind_array(l_processed_bind_count) := to_char(p_ledger_id);
1466 l_processed_bind_count := l_processed_bind_count+1;
1467 END IF;
1468
1469 l_processed_bind_count := l_processed_bind_count-1 ;
1470
1471 l_update_processed := l_update_processed||')';
1472
1473 -- Replace parallel hint based on the profile option
1474 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
1475 l_update_processed := REPLACE(l_update_processed,'$parallel$','parallel(aeh)');
1476 ELSE
1477 l_update_processed := REPLACE(l_update_processed,'$parallel$','');
1478 END IF;
1479
1480 IF (c_level_procedure >= g_log_level)
1481 THEN
1482 trace
1483 (p_msg => 'AC l_update_processed_1:'||substr(l_update_processed, 1, 1000)
1484 ,p_level => C_LEVEL_STATEMENT
1485 ,p_module => l_log_module);
1486 trace
1487 (p_msg => 'AC l_update_processed_2:'||substr(l_update_processed, 1001, 1000)
1488 ,p_level => C_LEVEL_STATEMENT
1489 ,p_module => l_log_module);
1490 trace
1491 (p_msg => 'AC l_update_processed_3:'||substr(l_update_processed, 2001, 1000)
1492 ,p_level => C_LEVEL_STATEMENT
1493 ,p_module => l_log_module);
1494 trace
1495 (p_msg => 'AC l_update_processed_4:'||substr(l_update_processed, 3001, 1000)
1496 ,p_level => C_LEVEL_STATEMENT
1497 ,p_module => l_log_module);
1498 trace
1499 (p_msg => 'AC l_update_processed_5:'||substr(l_update_processed, 4001, 999)
1500 ,p_level => C_LEVEL_STATEMENT
1501 ,p_module => l_log_module);
1502 END IF;
1503
1504 -- Execute sql
1505 IF l_processed_bind_count =1
1506 THEN
1507 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1);
1508 ELSIF l_processed_bind_count =2
1509 THEN
1510 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2);
1511 ELSIF l_processed_bind_count =3
1512 THEN
1513 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3);
1514 ELSIF l_processed_bind_count =4
1515 THEN
1516 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
1517 ,l_processed_bind_array(4);
1518 ELSIF l_processed_bind_count =5
1519 THEN
1520 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
1521 ,l_processed_bind_array(4),l_processed_bind_array(5);
1522 ELSIF l_processed_bind_count =6
1523 THEN
1524 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
1525 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6);
1526 ELSIF l_processed_bind_count =7
1527 THEN
1528 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
1529 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7);
1530 ELSIF l_processed_bind_count =8
1531 THEN
1532 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
1533 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7)
1534 ,l_processed_bind_array(8);
1535 END IF;
1536
1537
1538 l_update2_count := SQL%ROWCOUNT;
1539
1540 IF (c_level_procedure >= g_log_level)
1541 THEN
1542 TRACE (p_module => l_log_module
1543 , p_msg => '# Rows update in xla_ae_lines' || l_update2_count
1544 , p_level => c_level_procedure
1545 );
1546 END IF;
1547
1548 --
1549 --
1550 IF (c_level_procedure >= g_log_level)
1551 THEN
1552 TRACE (p_module => l_log_module
1553 , p_msg => 'END ' || l_log_module
1554 , p_level => c_level_procedure
1555 );
1556 END IF;
1557
1558 RETURN TRUE;
1559 EXCEPTION
1560 WHEN xla_exceptions_pkg.application_exception
1561 THEN
1565 THEN
1562 ROLLBACK TO SAVEPOINT_BAL;
1563
1564 WHEN OTHERS
1566 ROLLBACK TO SAVEPOINT_BAL;
1567 xla_exceptions_pkg.raise_message
1568 (p_location => 'xla_balances_calc_pkg.calculate_analytical_balances');
1569 --
1570 --
1571 END calculate_analytical_balances;
1572
1573 /*===============================================+
1574 | |
1575 | Private Function |
1576 +------------------------------------------------+
1577 | Calculate Control Balances |
1578 | |
1579 +===============================================*/
1580 FUNCTION calculate_control_balances ( p_application_id IN INTEGER
1581 , p_ledger_id IN INTEGER
1582 , p_entity_id IN INTEGER
1583 , p_event_id IN INTEGER
1584 , p_ae_header_id IN INTEGER
1585 , p_ae_line_num IN INTEGER
1586 , p_request_id IN INTEGER
1587 , p_accounting_batch_id IN INTEGER
1588 , p_operation_code IN VARCHAR2
1589 , p_execution_mode IN VARCHAR2
1590 )
1591 RETURN BOOLEAN
1592 IS
1593 l_log_module VARCHAR2 (240);
1594 l_insert1_count NUMBER;
1595 l_merge_count NUMBER;
1596 l_update1_count NUMBER;
1597 l_insert2_count NUMBER;
1598 l_update2_count NUMBER;
1599 l_update_bal VARCHAR2(6000);
1600 l_insert_bal VARCHAR2(6000);
1601 l_update_processed VARCHAR2(5000);
1602 l_summary_bind_array t_array_varchar;
1603 l_summary_bind_count INTEGER :=1 ;
1604 l_processed_bind_array t_array_varchar;
1605 l_processed_bind_count INTEGER :=1 ;
1606
1607 l_summary_stmt VARCHAR2(6000):= 'INSERT INTO xla_ctrl_bal_interim_gt (
1608 application_id
1609 , ledger_id
1610 , code_combination_id
1611 , party_type_code
1612 , party_id
1613 , party_site_id
1614 , period_name
1615 , effective_period_num
1616 , period_balance_dr
1617 , period_balance_cr
1618 , period_year
1619 )
1620 SELECT /*+ $parallel$ use_nl(aeh) use_nl(ael) */
1621 ael.application_id
1622 , ael.ledger_id
1623 , ael.code_combination_id
1624 , ael.party_type_code
1625 , ael.party_id
1626 , nvl(ael.party_site_id,-999) -- bug11887321
1627 , gps.period_name
1628 , gps.effective_period_num
1629 , $period_balance_dr$
1630 , $period_balance_cr$
1631 , SUBSTR (gps.effective_period_num, 1, 4) period_year
1632 FROM xla_ae_headers aeh
1633 , xla_ae_lines ael
1634 , gl_period_statuses gps
1635 , xla_ledger_options xlo
1636 , xla_ledger_relationships_v xlr
1637 $bal_concurrency$
1638 WHERE aeh.application_id = :'||l_summary_bind_count||'
1639 AND aeh.accounting_entry_status_code = ''F''
1640 AND aeh.balance_type_code = ''A''
1641 AND ael.application_id = aeh.application_id
1642 AND ael.ae_header_id = aeh.ae_header_id
1643 AND ael.control_balance_flag = '''||g_preupdate_flag||'''
1644 AND ael.ledger_id = aeh.ledger_id
1645 AND xlr.ledger_id = aeh.ledger_id
1646 AND xlo.application_id = aeh.application_id
1647 AND xlo.ledger_id = DECODE (xlr.ledger_category_code , ''ALC''
1648 , xlr.primary_ledger_id , xlr.ledger_id )
1649 AND gps.ledger_id = xlo.ledger_id
1650 AND gps.application_id = 101
1651 AND gps.closing_status IN (''O'', ''C'', ''P'')
1652 AND gps.effective_period_num <= xlo.effective_period_num
1653 AND gps.adjustment_period_flag = ''N''
1654 AND gps.period_name = aeh.period_name';
1655 l_group_by_stmt VARCHAR2(1000):= ' GROUP BY ael.application_id
1656 , ael.ledger_id
1657 , ael.code_combination_id
1658 , ael.party_type_code
1662 , gps.effective_period_num';
1659 , ael.party_id
1660 , ael.party_site_id
1661 , gps.period_name
1663 BEGIN
1664 IF g_log_enabled
1665 THEN
1666 l_log_module := c_default_module || '.calculate_control_balances';
1667 END IF;
1668
1669 IF (c_level_procedure >= g_log_level)
1670 THEN
1671 TRACE (p_module => l_log_module
1672 , p_msg => 'BEGIN ' || l_log_module
1673 , p_level => c_level_procedure
1674 );
1675 END IF;
1676 IF (c_level_exception >= g_log_level)
1677 THEN
1678 TRACE (p_module => l_log_module
1679 , p_msg => 'p_application_id : ' || p_application_id
1680 , p_level => c_level_exception
1681 );
1682 END IF;
1683 IF (c_level_exception >= g_log_level)
1684 THEN
1685 TRACE (p_module => l_log_module
1686 , p_msg => 'p_ledger_id : ' || p_ledger_id
1687 , p_level => c_level_exception
1688 );
1689 END IF;
1690 IF (c_level_exception >= g_log_level)
1691 THEN
1692 TRACE (p_module => l_log_module
1693 , p_msg => 'p_accounting_batch_id : '
1694 || p_accounting_batch_id
1695 , p_level => c_level_exception
1696 );
1697 END IF;
1698 IF (c_level_exception >= g_log_level)
1699 THEN
1700 TRACE (p_module => l_log_module
1701 , p_msg => 'p_execution_mode : ' || p_execution_mode
1702 , p_level => c_level_exception
1703 );
1704 END IF;
1705 IF (c_level_exception >= g_log_level)
1706 THEN
1707 TRACE (p_module => l_log_module
1708 , p_msg => 'request_id : ' || g_req_id
1709 , p_level => c_level_exception
1710 );
1711 END IF;
1712 IF (c_level_exception >= g_log_level)
1713 THEN
1714 TRACE (p_module => l_log_module
1715 , p_msg => 'p_event_id : ' || p_event_id
1716 , p_level => c_level_exception
1717 );
1718 END IF;
1719 IF (c_level_exception >= g_log_level)
1720 THEN
1721 TRACE (p_module => l_log_module
1722 , p_msg => 'p_entity_id : ' || p_entity_id
1723 , p_level => c_level_exception
1724 );
1725 END IF;
1726 IF (c_level_exception >= g_log_level)
1727 THEN
1728 TRACE (p_module => l_log_module
1729 , p_msg => 'p_ae_header_id : ' || p_ae_header_id
1730 , p_level => c_level_exception
1731 );
1732 END IF;
1733 IF (c_level_exception >= g_log_level)
1734 THEN
1735 TRACE (p_module => l_log_module
1736 , p_msg => 'p_ae_line_num : ' || p_ae_line_num
1737 , p_level => c_level_exception
1738 );
1739 END IF;
1740 IF (c_level_exception >= g_log_level)
1741 THEN
1742 TRACE (p_module => l_log_module
1743 , p_msg => 'p_operation_code : ' || p_operation_code
1744 , p_level => c_level_exception
1745 );
1746 END IF;
1747
1748 l_summary_bind_array(l_summary_bind_count) := to_char(p_application_id);
1749 l_summary_bind_count := l_summary_bind_count+1;
1750
1751 -- add dynamic conditions
1752 IF p_request_id IS NOT NULL AND p_request_id <> -1
1753 THEN
1754 l_summary_stmt := REPLACE (l_summary_stmt, '$bal_concurrency$', ',xla_bal_concurrency_control bcc');
1755 l_summary_stmt := l_summary_stmt || '
1756 AND bcc.request_id = :'||l_summary_bind_count||'
1757 AND bcc.accounting_batch_id = aeh.accounting_batch_id
1758 AND bcc.application_id = aeh.application_id
1759 AND bcc.ledger_id = aeh.ledger_id';
1760
1761 l_summary_bind_array(l_summary_bind_count) := to_char(p_request_id);
1762 l_summary_bind_count := l_summary_bind_count+1;
1763 ELSE
1764 l_summary_stmt := REPLACE(l_summary_stmt,'$bal_concurrency$','');
1765 END IF;
1766
1767 IF p_accounting_Batch_id IS NOT NULL
1768 THEN
1769 l_summary_stmt := l_summary_stmt || '
1770 AND aeh.accounting_batch_id = :'||l_summary_bind_count;
1771
1772 l_summary_bind_array(l_summary_bind_count) := to_char(p_accounting_Batch_id);
1773 l_summary_bind_count := l_summary_bind_count+1;
1774 END IF;
1775
1776 IF p_ledger_id IS NOT NULL
1777 AND p_accounting_batch_id IS NULL
1778 AND p_event_id IS NULL
1779 AND p_entity_id IS NULL
1780 AND p_ae_header_id IS NULL
1781 AND p_ae_line_num IS NULL
1782 THEN
1783 l_summary_stmt := l_summary_stmt || '
1784 AND aeh.ledger_id = :'||l_summary_bind_count;
1785
1786 l_summary_bind_array(l_summary_bind_count) := to_char(p_ledger_id);
1787 l_summary_bind_count := l_summary_bind_count+1;
1788 END IF;
1789
1790 IF p_entity_id IS NOT NULL
1791 THEN
1792 l_summary_stmt := l_summary_stmt || '
1793 AND aeh.entity_id = :'||l_summary_bind_count;
1794
1795 l_summary_bind_array(l_summary_bind_count) := to_char(p_entity_id);
1796 l_summary_bind_count := l_summary_bind_count+1;
1797 END IF;
1798
1799 IF p_event_id IS NOT NULL
1800 THEN
1801 l_summary_stmt := l_summary_stmt || '
1802 AND aeh.event_id = :'||l_summary_bind_count;
1803
1807
1804 l_summary_bind_array(l_summary_bind_count) := to_char(p_event_id);
1805 l_summary_bind_count := l_summary_bind_count+1;
1806 END IF;
1808 IF p_ae_header_id IS NOT NULL
1809 THEN
1810 l_summary_stmt := l_summary_stmt || '
1811 AND aeh.ae_header_id = :'||l_summary_bind_count;
1812
1813 l_summary_bind_array(l_summary_bind_count) := to_char(p_ae_header_id);
1814 l_summary_bind_count := l_summary_bind_count+1;
1815 END IF;
1816
1817 IF p_ae_line_num IS NOT NULL
1818 THEN
1819 l_summary_stmt := l_summary_stmt || '
1820 AND ael.ae_line_num = :'||l_summary_bind_count;
1821
1822 l_summary_bind_array(l_summary_bind_count) := to_char(p_ae_line_num);
1823 l_summary_bind_count := l_summary_bind_count+1;
1824 END IF;
1825
1826 l_summary_bind_count := l_summary_bind_count-1;
1827
1828 -- Replace perf. hint dynamically
1829 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
1830 l_summary_stmt := REPLACE(l_summary_stmt,'$parallel$','parallel(aeh)');
1831 ELSE
1832 l_summary_stmt := REPLACE(l_summary_stmt,'$parallel$','');
1833 END IF;
1834
1835 IF p_operation_code = 'A' --Add
1836 THEN
1837 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_dr$','SUM (NVL (ael.accounted_dr, 0)) period_balance_dr');
1838 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_cr$','SUM (NVL (ael.accounted_cr, 0)) period_balance_cr');
1839 ELSIF p_operation_code = 'R' -- Remove
1840 THEN
1841 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_dr$','SUM (NVL (ael.accounted_dr, 0)) * -1 period_balance_dr');
1842 l_summary_stmt := REPLACE(l_summary_stmt,'$period_balance_cr$','SUM (NVL (ael.accounted_cr, 0)) * -1 period_balance_cr');
1843 END IF;
1844
1845 l_summary_stmt := l_summary_stmt || l_group_by_stmt;
1846
1847 IF (c_level_procedure >= g_log_level)
1848 THEN
1849 trace
1850 (p_msg => 'CTRL: l_summary_stmt_1:'||substr(l_summary_stmt, 1, 1000)
1851 ,p_level => C_LEVEL_STATEMENT
1852 ,p_module => l_log_module);
1853 trace
1854 (p_msg => 'CTRL: l_summary_stmt_2:'||substr(l_summary_stmt, 1001, 1000)
1855 ,p_level => C_LEVEL_STATEMENT
1856 ,p_module => l_log_module);
1857 trace
1858 (p_msg => 'CTRL: l_summary_stmt_3:'||substr(l_summary_stmt, 2001, 1000)
1859 ,p_level => C_LEVEL_STATEMENT
1860 ,p_module => l_log_module);
1861 trace
1862 (p_msg => 'CTRL: l_summary_stmt_4:'||substr(l_summary_stmt, 3001, 1000)
1863 ,p_level => C_LEVEL_STATEMENT
1864 ,p_module => l_log_module);
1865 trace
1866 (p_msg => 'CTRL: l_summary_stmt_5:'||substr(l_summary_stmt, 4001, 1000)
1867 ,p_level => C_LEVEL_STATEMENT
1868 ,p_module => l_log_module);
1869 trace
1870 (p_msg => 'CTRL: l_summary_stmt_6:'||substr(l_summary_stmt, 5001, 999)
1871 ,p_level => C_LEVEL_STATEMENT
1872 ,p_module => l_log_module);
1873 trace
1874 (p_msg => 'l_summary_bind_count : '||l_summary_bind_count
1875 ,p_level => C_LEVEL_STATEMENT
1876 ,p_module => l_log_module);
1877 END IF;
1878
1879
1880 IF l_summary_bind_count = 1
1881 THEN
1882 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1);
1883 ELSIF l_summary_bind_count = 2
1884 THEN
1885 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2);
1886 ELSIF l_summary_bind_count = 3
1887 THEN
1888 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3);
1889 ELSIF l_summary_bind_count = 4
1890 THEN
1891 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
1892 ,l_summary_bind_array(4);
1893 ELSIF l_summary_bind_count = 5
1894 THEN
1895 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
1896 ,l_summary_bind_array(4), l_summary_bind_array(5);
1897 ELSIF l_summary_bind_count = 6
1898 THEN
1899 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
1900 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6);
1901 ELSIF l_summary_bind_count = 7
1902 THEN
1903 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
1904 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6)
1905 , l_summary_bind_array(7);
1906 ELSIF l_summary_bind_count = 8
1907 THEN
1908 EXECUTE IMMEDIATE l_summary_stmt USING l_summary_bind_array(1) , l_summary_bind_array(2),l_summary_bind_array(3)
1909 ,l_summary_bind_array(4), l_summary_bind_array(5), l_summary_bind_array(6)
1910 , l_summary_bind_array(7), l_summary_bind_array(8);
1911 END IF;
1912
1913 l_insert1_count := SQL%ROWCOUNT;
1914
1915 IF (c_level_procedure >= g_log_level)
1916 THEN
1917 TRACE (p_module => l_log_module
1918 , p_msg => '# rows inserted in xla_ctrl_bal_interim_gt : '
1919 || l_insert1_count
1920 , p_level => c_level_procedure
1921 );
1922 END IF;
1923
1924 IF l_insert1_count = 0
1925 THEN
1926 IF (c_level_procedure >= g_log_level)
1927 THEN
1928 TRACE (p_module => l_log_module
1932 END IF;
1929 , p_msg => 'No Records to process ' || l_insert1_count
1930 , p_level => c_level_procedure
1931 );
1933
1934 RETURN TRUE; --No records to process
1935 END IF;
1936
1937 --
1938 -- Calculate the bgin balance and insert records into summary table for future periods
1939 --
1940 MERGE INTO xla_ctrl_bal_interim_gt stmp
1941 USING (SELECT period_balance_dr
1942 , period_balance_cr
1943 , SUM (lag_dr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
1944 , party_type_code, party_id, party_site_id
1945 ORDER BY application_id
1946 , ledger_id
1947 , code_combination_id
1948 , party_type_code
1949 , party_id
1950 , party_site_id
1951 , effective_period_num) xal_beginning_balance_dr
1952 , SUM (lag_cr) OVER (PARTITION BY application_id, ledger_id, code_combination_id
1953 , party_type_code, party_id, party_site_id
1954 ORDER BY application_id
1955 , ledger_id
1956 , code_combination_id
1957 , party_type_code
1958 , party_id
1959 , party_site_id
1960 , effective_period_num) xal_beginning_balance_cr
1961 , application_id
1962 , ledger_id
1963 , code_combination_id
1964 , party_type_code
1965 , party_id
1966 , party_site_id
1967 , period_name
1968 , effective_period_num
1969 , period_year
1970 FROM (SELECT /*+ leading(xag,xal_bal) */
1971 xal_bal.application_id
1972 , xal_bal.ledger_id
1973 , xal_bal.code_combination_id
1974 , xal_bal.party_type_code
1975 , xal_bal.party_id
1976 , xal_bal.party_site_id
1977 , xal_bal.period_name
1978 , xal_bal.effective_period_num
1979 , xal_bal.period_balance_dr
1980 , xal_bal.period_balance_cr
1981 , xal_bal.period_year
1982 , LAG (NVL (xal_bal.period_balance_dr, 0)
1983 , 1
1984 , NVL (xal_bal.beginning_balance_dr, 0)
1985 ) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
1986 , xal_bal.code_combination_id, xal_bal.party_type_code
1987 , xal_bal.party_id, xal_bal.party_site_id
1988 ORDER BY xal_bal.application_id
1989 , xal_bal.ledger_id
1990 , xal_bal.code_combination_id
1991 , xal_bal.party_type_code
1992 , xal_bal.party_id
1993 , xal_bal.party_site_id
1994 , xal_bal.effective_period_num) lag_dr
1995 , LAG (NVL (xal_bal.period_balance_cr, 0)
1996 , 1
1997 , NVL (xal_bal.beginning_balance_cr, 0)
1998 ) OVER (PARTITION BY xal_bal.application_id, xal_bal.ledger_id
1999 , xal_bal.code_combination_id, xal_bal.party_type_code
2000 , xal_bal.party_id, xal_bal.party_site_id
2001 ORDER BY xal_bal.application_id
2002 , xal_bal.ledger_id
2003 , xal_bal.code_combination_id
2004 , xal_bal.party_type_code
2005 , xal_bal.party_id
2006 , xal_bal.party_site_id
2007 , xal_bal.effective_period_num) lag_cr
2008 FROM (SELECT tmp.application_id
2009 , tmp.ledger_id
2010 , tmp.code_combination_id
2011 , tmp.party_type_code
2012 , tmp.party_id
2013 , tmp.party_site_id
2014 , MAX
2015 (DECODE
2016 (gps.effective_period_num
2017 , tmp.effective_period_num, tmp.period_balance_dr
2018 , NULL
2019 )
2020 ) period_balance_dr
2021 , MAX
2022 (DECODE
2023 (gps.effective_period_num
2024 , tmp.effective_period_num, tmp.period_balance_cr
2025 , NULL
2026 )
2027 ) period_balance_cr
2028 , tmp.beginning_balance_dr
2029 , tmp.beginning_balance_cr
2030 , gps.period_name
2031 , gps.effective_period_num
2032 , gps.period_year
2033 FROM gl_period_statuses gps
2037 WHERE gps.effective_period_num <= xlo.effective_period_num
2034 , xla_ctrl_bal_interim_gt tmp
2035 , xla_ledger_options xlo
2036 , xla_ledger_relationships_v xlr
2038 AND gps.effective_period_num >= tmp.effective_period_num
2039 AND gps.closing_status IN ('O', 'C', 'P')
2040 AND gps.adjustment_period_flag = 'N'
2041 AND gps.application_id = 101
2042 AND gps.ledger_id = xlo.ledger_id
2043 AND tmp.application_id = xlo.application_id
2044 AND tmp.ledger_id = xlr.ledger_id
2045 AND xlo.ledger_id = DECODE(xlr.ledger_category_code, 'ALC'
2046 ,xlr.primary_ledger_id, xlr.ledger_id)
2047 GROUP BY tmp.application_id
2048 , tmp.ledger_id
2049 , tmp.code_combination_id
2050 , tmp.party_type_code
2051 , tmp.party_id
2052 , tmp.party_site_id
2053 , tmp.beginning_balance_dr
2054 , tmp.beginning_balance_cr
2055 , gps.period_name
2056 , gps.effective_period_num
2057 , gps.period_year) xal_bal
2058 ORDER BY xal_bal.application_id
2059 , xal_bal.ledger_id
2060 , xal_bal.code_combination_id
2061 , xal_bal.party_type_code
2062 , xal_bal.party_id
2063 , xal_bal.party_site_id
2064 , xal_bal.effective_period_num
2065 , xal_bal.period_year)) tmp
2066 ON ( stmp.application_id = tmp.application_id
2067 AND stmp.ledger_id = tmp.ledger_id
2068 AND stmp.code_combination_id = tmp.code_combination_id
2069 AND stmp.party_type_code = tmp.party_type_code
2070 AND stmp.party_id = tmp.party_id
2071 AND stmp.party_site_id = tmp.party_site_id
2072 AND stmp.effective_period_num = tmp.effective_period_num)
2073 WHEN MATCHED THEN
2074 UPDATE
2075 SET stmp.beginning_balance_dr = tmp.xal_beginning_balance_dr
2076 , stmp.beginning_balance_cr = tmp.xal_beginning_balance_cr
2077 WHEN NOT MATCHED THEN
2078 INSERT (stmp.application_id, stmp.ledger_id
2079 , stmp.code_combination_id, stmp.party_type_code
2080 , stmp.party_id, stmp.party_site_id, stmp.period_balance_dr
2081 , stmp.period_balance_cr, stmp.beginning_balance_dr
2082 , stmp.beginning_balance_cr, stmp.period_name
2083 , stmp.effective_period_num, stmp.period_year)
2084 VALUES (tmp.application_id, tmp.ledger_id
2085 , tmp.code_combination_id, tmp.party_type_code
2086 , tmp.party_id, tmp.party_site_id, tmp.period_balance_dr
2087 , tmp.period_balance_cr, tmp.xal_beginning_balance_dr
2088 , tmp.xal_beginning_balance_cr, tmp.period_name
2089 , tmp.effective_period_num, tmp.period_year);
2090
2091 l_merge_count := SQL%ROWCOUNT;
2092
2093 IF (c_level_procedure >= g_log_level)
2094 THEN
2095 TRACE (p_module => l_log_module
2096 , p_msg => '# rows merged in xla_ctrl_bal_interim_gt : '
2097 || l_merge_count
2098 , p_level => c_level_procedure
2099 );
2100 END IF;
2101
2102 --
2103 --
2104 -- Update the BEGINNING BALANCE, PERIOD BALANCE into the xla_control_balances table if record already exists for that group.
2105 --
2106 l_update_bal := 'UPDATE /*+ ordered index(b,xla_control_balances_N99) */xla_control_balances b
2107 SET last_update_date = '''||g_date||'''
2108 , last_updated_by = '||g_user_id||'
2109 , last_update_login = '||g_login_id||'
2110 , program_update_date = '''||g_date||'''
2111 , program_application_id = '||g_prog_appl_id||'
2112 , program_id = '||g_prog_id||'
2113 , request_id = '||g_req_id||'
2114 ,(period_balance_dr, period_balance_cr, beginning_balance_dr
2115 , beginning_balance_cr) =
2116 (SELECT /*+ $parallel$ index(tmp,xla_ctrl_bal_interim_gt_U1) */
2117 NVL (b.period_balance_dr, 0)
2118 + NVL (tmp.period_balance_dr, 0) period_balance_dr
2119 , NVL (b.period_balance_cr, 0)
2120 + NVL (tmp.period_balance_cr, 0) period_balance_cr
2121 , NVL (b.beginning_balance_dr, 0)
2122 + NVL (tmp.beginning_balance_dr, 0) beginning_balance_dr
2123 , NVL (b.beginning_balance_cr, 0)
2124 + NVL (tmp.beginning_balance_cr, 0) beginning_balance_cr
2125 FROM xla_ctrl_bal_interim_gt tmp
2126 WHERE tmp.application_id = b.application_id
2127 AND tmp.ledger_id = b.ledger_id
2128 AND tmp.code_combination_id = b.code_combination_id
2129 AND tmp.party_type_code = b.party_type_code
2130 AND tmp.party_id = b.party_id
2131 AND tmp.party_site_id = b.party_site_id
2132 AND tmp.effective_period_num = b.effective_period_num)
2133 WHERE (b.application_id
2134 , b.ledger_id
2135 , b.code_combination_id
2136 , b.party_type_code
2137 , b.party_id
2138 , b.party_site_id
2139 , b.effective_period_num
2143 , xal_bal1.ledger_id
2140 ) IN (
2141 SELECT /*+ $parallel_1$ full(xal_bal1) */
2142 xal_bal1.application_id
2144 , xal_bal1.code_combination_id
2145 , xal_bal1.party_type_code
2146 , xal_bal1.party_id
2147 , xal_bal1.party_site_id
2148 , xal_bal1.effective_period_num
2149 FROM xla_ctrl_bal_interim_gt xal_bal1)';
2150
2151 -- Replace parallel hint based on the profile option
2152 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
2153 l_update_bal := REPLACE(l_update_bal,'$parallel$','parallel(tmp)');
2154 l_update_bal := REPLACE(l_update_bal,'$parallel_1$','parallel(xal_bal1)');
2155 ELSE
2156 l_update_bal := REPLACE(l_update_bal,'$parallel$','');
2157 l_update_bal := REPLACE(l_update_bal,'$parallel_1$','');
2158 END IF;
2159
2160 IF (c_level_procedure >= g_log_level)
2161 THEN
2162 trace
2163 (p_msg => 'CTRL: l_update_bal_1:'||substr(l_update_bal, 1, 1000)
2164 ,p_level => C_LEVEL_STATEMENT
2165 ,p_module => l_log_module);
2166 trace
2167 (p_msg => 'CTRL: l_update_bal_2:'||substr(l_update_bal, 1001, 1000)
2168 ,p_level => C_LEVEL_STATEMENT
2169 ,p_module => l_log_module);
2170 trace
2171 (p_msg => 'CTRL: l_update_bal_3:'||substr(l_update_bal, 2001, 1000)
2172 ,p_level => C_LEVEL_STATEMENT
2173 ,p_module => l_log_module);
2174 trace
2175 (p_msg => 'CTRL: l_update_bal_4:'||substr(l_update_bal, 3001, 1000)
2176 ,p_level => C_LEVEL_STATEMENT
2177 ,p_module => l_log_module);
2178 trace
2179 (p_msg => 'CTRL: l_update_bal_5:'||substr(l_update_bal, 4001, 1000)
2180 ,p_level => C_LEVEL_STATEMENT
2181 ,p_module => l_log_module);
2182 trace
2183 (p_msg => 'CTRL: l_update_bal_6:'||substr(l_update_bal, 5001, 999)
2184 ,p_level => C_LEVEL_STATEMENT
2185 ,p_module => l_log_module);
2186 END IF;
2187 --Execute sql
2188
2189 EXECUTE IMMEDIATE l_update_bal;
2190
2191 l_update1_count:=SQL%ROWCOUNT;
2192
2193 IF (c_level_procedure >= g_log_level)
2194 THEN
2195 TRACE (p_module => l_log_module
2196 , p_msg => '# rows updated in xla_control_balances : '
2197 || l_update1_count
2198 , p_level => c_level_procedure
2199 );
2200 END IF;
2201
2202 --
2203 -- Insert record into xla_control_balance if record does not exist
2204 --
2205 IF l_update1_count <> l_merge_count
2206 THEN
2207 -- insert rows only if the rows updated is not equal to the total no of rows in gt table
2208 l_insert_bal := 'INSERT INTO xla_control_balances xba (
2209 application_id
2210 , ledger_id
2211 , code_combination_id
2212 , party_type_code
2213 , party_id
2214 , party_site_id
2215 , period_name
2216 , period_year
2217 , first_period_flag
2218 , period_balance_dr
2219 , period_balance_cr
2220 , beginning_balance_dr
2221 , beginning_balance_cr
2222 , initial_balance_flag
2223 , effective_period_num
2224 , creation_date
2225 , created_by
2226 , last_update_date
2227 , last_updated_by
2228 , last_update_login
2229 , program_update_date
2230 , program_application_id
2231 , program_id
2232 , request_id
2233 )
2234 SELECT /*+ $parallel$ */
2235 temp.application_id
2236 , temp.ledger_id
2237 , temp.code_combination_id
2238 , temp.party_type_code
2239 , temp.party_id
2240 , temp.party_site_id
2241 , gps.period_name
2242 , gps.period_year
2243 , DECODE (gps.period_num, 1, ''Y'', ''N'') first_period_flag
2244 , temp.period_balance_dr
2245 , temp.period_balance_cr
2246 , temp.beginning_balance_dr
2247 , temp.beginning_balance_cr
2248 , ''N'' initial_balance_flag
2249 , temp.effective_period_num
2250 , '''||g_date||'''
2251 , '||g_user_id||'
2252 , '''||g_date||'''
2253 , '||g_user_id||'
2254 , '||g_login_id||'
2255 , '''||g_date||'''
2256 , '||g_prog_appl_id||'
2257 , '||g_prog_id||'
2258 , '||g_req_id||'
2259 FROM xla_ctrl_bal_interim_gt temp
2260 , gl_period_statuses gps
2261 , xla_ledger_relationships_v xlr
2262 WHERE xlr.ledger_id = temp.ledger_id
2263 AND gps.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
2264 ,xlr.primary_ledger_id , xlr.ledger_id)
2265 AND gps.effective_period_num = temp.effective_period_num
2269 AND NOT EXISTS ( SELECT /*+ no_unnest $parallel_1$ */ 1
2266 AND gps.application_id = 101
2267 AND gps.adjustment_period_flag = ''N''
2268 AND gps.closing_status IN (''O'', ''C'', ''P'')
2270 FROM xla_control_balances xba
2271 WHERE xba.application_id = temp.application_id
2272 AND xba.ledger_id = temp.ledger_id
2273 AND xba.code_combination_id = temp.code_combination_id
2274 AND xba.party_type_code = temp.party_type_code
2275 AND xba.party_id = temp.party_id
2276 AND xba.party_site_id = temp.party_site_id
2277 AND xba.period_name = temp.period_name)';
2278
2279 -- Replace parallel hint based on profile option
2280 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
2281 l_insert_bal := REPLACE(l_insert_bal,'$parallel$','parallel(temp)');
2282 l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','parallel(xba)');
2283 ELSE
2284 l_insert_bal := REPLACE(l_insert_bal,'$parallel$','');
2285 l_insert_bal := REPLACE(l_insert_bal,'$parallel_1$','');
2286 END IF;
2287
2288 IF (c_level_procedure >= g_log_level)
2289 THEN
2290 trace
2291 (p_msg => 'CTRL: l_insert_bal_1:'||substr(l_insert_bal, 1, 1000)
2292 ,p_level => C_LEVEL_STATEMENT
2293 ,p_module => l_log_module);
2294 trace
2295 (p_msg => 'CTRL: l_insert_bal_2:'||substr(l_insert_bal, 1001, 1000)
2296 ,p_level => C_LEVEL_STATEMENT
2297 ,p_module => l_log_module);
2298 trace
2299 (p_msg => 'CTRL: l_insert_bal_3:'||substr(l_insert_bal, 2001, 1000)
2300 ,p_level => C_LEVEL_STATEMENT
2301 ,p_module => l_log_module);
2302 trace
2303 (p_msg => 'CTRL: l_insert_bal_4:'||substr(l_insert_bal, 3001, 1000)
2304 ,p_level => C_LEVEL_STATEMENT
2305 ,p_module => l_log_module);
2306 trace
2307 (p_msg => 'CTRL: l_insert_bal_5:'||substr(l_insert_bal, 4001, 1000)
2308 ,p_level => C_LEVEL_STATEMENT
2309 ,p_module => l_log_module);
2310 trace
2311 (p_msg => 'CTRL: l_insert_bal_6:'||substr(l_insert_bal, 5001, 999)
2312 ,p_level => C_LEVEL_STATEMENT
2313 ,p_module => l_log_module);
2314 END IF;
2315
2316 --Execute sql
2317 EXECUTE IMMEDIATE l_insert_bal;
2318
2319 l_insert2_count := SQL%ROWCOUNT;
2320 END IF;
2321
2322 IF (c_level_procedure >= g_log_level)
2323 THEN
2324 TRACE (p_module => l_log_module
2325 , p_msg => ' # rows inserted into xla_control_balances : '
2326 || l_insert2_count
2327 , p_level => c_level_procedure
2328 );
2329 END IF;
2330
2331 --
2332 --update records processed to 'Y' in xla_ae_lines
2333 --
2334 l_update_processed := 'UPDATE /*+ use_nl(ael) */xla_ae_lines ael
2335 SET control_balance_flag = '''||g_postupdate_flag||'''
2336 WHERE application_id = :'||l_processed_bind_count||'
2337 AND control_balance_flag = '''||g_preupdate_flag||'''
2338 AND (ae_header_id,ae_line_num) IN ( SELECT /*+ $parallel$ leading(aeh) */
2339 ael.ae_header_id
2340 ,ael.ae_line_num
2341 FROM xla_ae_headers aeh
2342 , xla_ae_lines ael
2343 , gl_period_statuses gps
2344 , xla_ledger_options xlo
2345 , xla_ledger_relationships_v xlr
2346 $bal_concurrency$
2347 WHERE aeh.accounting_entry_status_code = ''F''
2348 AND aeh.application_id = :'||l_processed_bind_count||'
2349 AND aeh.balance_type_code = ''A''
2350 AND aeh.ledger_id = xlr.ledger_id
2351 AND ael.ae_header_id = aeh.ae_header_id
2352 AND ael.control_balance_flag = '''||g_preupdate_flag||'''
2353 AND ael.application_id = aeh.application_id
2354 AND xlo.ledger_id = DECODE(xlr.ledger_category_code, ''ALC''
2355 ,xlr.primary_ledger_id, xlr.ledger_id)
2356 AND gps.ledger_id = xlo.ledger_id
2357 AND gps.application_id = 101
2358 AND gps.closing_status IN (''O'', ''C'', ''P'')
2359 AND gps.effective_period_num <= xlo.effective_period_num
2360 AND gps.adjustment_period_flag = ''N''
2361 AND gps.period_name = aeh.period_name' ;
2362
2366 --Add dynamic conditions
2363 l_processed_bind_array(l_processed_bind_count) := to_char(p_application_id);
2364 l_processed_bind_count := l_processed_bind_count+1;
2365
2367 IF p_request_id IS NOT NULL AND p_request_id <> -1
2368 THEN
2369 l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$',',xla_bal_concurrency_control bcc');
2370 l_update_processed := l_update_processed||
2371 ' AND bcc.request_id = :'||l_processed_bind_count||'
2372 AND bcc.accounting_batch_id = aeh.accounting_batch_id
2373 AND bcc.application_id = aeh.application_id' ;
2374
2375 l_processed_bind_array(l_processed_bind_count) := to_char(p_request_id);
2376 l_processed_bind_count := l_processed_bind_count+1;
2377 ELSE
2378 l_update_processed := REPLACE(l_update_processed,'$bal_concurrency$','');
2379 END IF;
2380
2381 IF p_accounting_batch_id IS NOT NULL
2382 THEN
2383 l_update_processed := l_update_processed||
2384 ' AND aeh.accounting_batch_id = :'||l_processed_bind_count;
2385
2386 l_processed_bind_array(l_processed_bind_count) := to_char(p_accounting_batch_id);
2387 l_processed_bind_count := l_processed_bind_count+1;
2388 END IF;
2389
2390 IF p_event_id IS NOT NULL
2391 THEN
2392 l_update_processed := l_update_processed||
2393 ' AND aeh.event_id = :'||l_processed_bind_count;
2394
2395 l_processed_bind_array(l_processed_bind_count) := to_char(p_event_id);
2396 l_processed_bind_count := l_processed_bind_count+1;
2397 END IF;
2398
2399 IF p_entity_id IS NOT NULL
2400 THEN
2401 l_update_processed := l_update_processed||
2402 ' AND aeh.entity_id = :'||l_processed_bind_count;
2403
2404 l_processed_bind_array(l_processed_bind_count) := to_char(p_entity_id);
2405 l_processed_bind_count := l_processed_bind_count+1;
2406 END IF;
2407 IF p_ae_header_id IS NOT NULL
2408 THEN
2409 l_update_processed := l_update_processed||
2410 ' AND aeh.ae_header_id = :'||l_processed_bind_count;
2411
2412 l_processed_bind_array(l_processed_bind_count) := to_char(p_ae_header_id);
2413 l_processed_bind_count := l_processed_bind_count+1;
2414 END IF;
2415 IF p_ae_line_num IS NOT NULL
2416 THEN
2417 l_update_processed := l_update_processed||
2418 ' AND ael.ae_line_num = :'||l_processed_bind_count;
2419
2420 l_processed_bind_array(l_processed_bind_count) := to_char(p_ae_line_num);
2421 l_processed_bind_count := l_processed_bind_count+1;
2422 END IF;
2423
2424 IF p_ledger_id IS NOT NULL
2425 AND p_accounting_batch_id IS NULL
2426 AND p_event_id IS NULL
2427 AND p_entity_id IS NULL
2428 AND p_ae_header_id IS NULL
2429 AND p_ae_line_num IS NULL
2430 THEN
2431 l_update_processed := l_update_processed || '
2432 AND aeh.ledger_id = :'||l_processed_bind_count;
2433
2434 l_processed_bind_array(l_processed_bind_count) := to_char(p_ledger_id);
2435 l_processed_bind_count := l_processed_bind_count+1;
2436 END IF;
2437
2438 l_processed_bind_count := l_processed_bind_count-1;
2439
2440 l_update_processed := l_update_processed||')';
2441
2442 -- Replace parallel hint based on the profile option
2443 IF (nvl(fnd_profile.value('XLA_BAL_PARALLEL_MODE'),'N') ='Y') THEN
2444 l_update_processed := REPLACE(l_update_processed,'$parallel$','parallel(aeh)');
2445 ELSE
2446 l_update_processed := REPLACE(l_update_processed,'$parallel$','');
2447 END IF;
2448
2449 IF (c_level_procedure >= g_log_level)
2450 THEN
2451 trace
2452 (p_msg => 'CTRL: l_update_processed_1:'||substr(l_update_processed, 1, 1000)
2453 ,p_level => C_LEVEL_STATEMENT
2454 ,p_module => l_log_module);
2455 trace
2456 (p_msg => 'CTRL: l_update_processed_2:'||substr(l_update_processed, 1001, 1000)
2457 ,p_level => C_LEVEL_STATEMENT
2458 ,p_module => l_log_module);
2459 trace
2460 (p_msg => 'CTRL: l_update_processed_3:'||substr(l_update_processed, 2001, 1000)
2461 ,p_level => C_LEVEL_STATEMENT
2462 ,p_module => l_log_module);
2463 trace
2464 (p_msg => 'CTRL: l_update_processed_4:'||substr(l_update_processed, 3001, 1000)
2465 ,p_level => C_LEVEL_STATEMENT
2466 ,p_module => l_log_module);
2467 trace
2468 (p_msg => 'CTRL: l_update_processed_5:'||substr(l_update_processed, 4001, 999)
2469 ,p_level => C_LEVEL_STATEMENT
2470 ,p_module => l_log_module);
2471 trace
2472 (p_msg => 'l_processed_bind_count : '||l_processed_bind_count
2473 ,p_level => C_LEVEL_STATEMENT
2474 ,p_module => l_log_module);
2475
2476 END IF;
2477 -- Execute sql
2478 IF l_processed_bind_count =1
2479 THEN
2480 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1);
2481 ELSIF l_processed_bind_count =2
2482 THEN
2483 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2);
2484 ELSIF l_processed_bind_count =3
2485 THEN
2486 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3);
2487 ELSIF l_processed_bind_count =4
2488 THEN
2489 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1), l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
2490 ,l_processed_bind_array(4);
2491 ELSIF l_processed_bind_count =5
2492 THEN
2496 THEN
2493 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
2494 ,l_processed_bind_array(4),l_processed_bind_array(5);
2495 ELSIF l_processed_bind_count =6
2497 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
2498 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6);
2499 ELSIF l_processed_bind_count =7
2500 THEN
2501 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
2502 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7);
2503 ELSIF l_processed_bind_count =8
2504 THEN
2505 EXECUTE IMMEDIATE l_update_processed USING l_processed_bind_array(1),l_processed_bind_array(1),l_processed_bind_array(2),l_processed_bind_array(3)
2506 ,l_processed_bind_array(4),l_processed_bind_array(5),l_processed_bind_array(6),l_processed_bind_array(7)
2507 ,l_processed_bind_array(8);
2508 END IF;
2509 --
2510 --
2511 l_update2_count := SQL%ROWCOUNT;
2512 --
2513 --
2514 IF (c_level_procedure >= g_log_level)
2515 THEN
2516 TRACE (p_module => l_log_module
2517 , p_msg => ' # rows updated in xla_ae_lines : ' || l_update2_count
2518 , p_level => c_level_procedure
2519 );
2520 END IF;
2521 --
2522 --
2523 IF (c_level_procedure >= g_log_level)
2524 THEN
2525 TRACE (p_module => l_log_module
2526 , p_msg => 'END ' || l_log_module
2527 , p_level => c_level_procedure
2528 );
2529 END IF;
2530
2531 RETURN TRUE;
2532 EXCEPTION
2533 WHEN xla_exceptions_pkg.application_exception
2534 THEN
2535 ROLLBACK TO SAVEPOINT_BAL;
2536
2537 WHEN OTHERS
2538 THEN
2539 ROLLBACK TO SAVEPOINT_BAL;
2540 xla_exceptions_pkg.raise_message
2541 (p_location => 'xla_balances_calc_pkg.calculate_control_balances');
2542 --
2543 --
2544 END calculate_control_balances;
2545
2546 /*===============================================+
2547 | |
2548 | public Function |
2549 +------------------------------------------------+
2550 | Calculate Balances |
2551 | |
2552 +===============================================*/
2553 FUNCTION calculate_balances ( p_application_id IN INTEGER
2554 , p_ledger_id IN INTEGER
2555 , p_entity_id IN INTEGER
2556 , p_event_id IN INTEGER
2557 , p_ae_header_id IN INTEGER
2558 , p_ae_line_num IN INTEGER
2559 , p_request_id IN INTEGER
2560 , p_accounting_batch_id IN INTEGER
2561 , p_update_mode IN VARCHAR2
2562 , p_execution_mode IN VARCHAR2
2563 )
2564 RETURN BOOLEAN
2565 IS
2566 l_log_module VARCHAR2 (240);
2567 l_processing_rows NUMBER := 0;
2568 l_return_value BOOLEAN;
2569 l_operation_code VARCHAR2(1);
2570 l_open_period_sql VARCHAR2(2000);
2571 l_eff_period_num NUMBER;
2572
2573 BEGIN
2574 IF g_log_enabled
2575 THEN
2576 l_log_module := c_default_module || '.calculate_balances';
2577 END IF;
2578
2579 IF (c_level_procedure >= g_log_level)
2580 THEN
2581 TRACE (p_module => l_log_module
2582 , p_msg => 'BEGIN ' || l_log_module
2583 , p_level => c_level_procedure
2584 );
2585 END IF;
2586
2587 IF (c_level_exception >= g_log_level)
2588 THEN
2589 TRACE (p_module => l_log_module
2590 , p_msg => 'p_application_id : ' || p_application_id
2591 , p_level => c_level_exception
2592 );
2593 END IF;
2594
2595 IF (c_level_exception >= g_log_level)
2596 THEN
2597 TRACE (p_module => l_log_module
2598 , p_msg => 'p_ledger_id : ' || p_ledger_id
2599 , p_level => c_level_exception
2600 );
2601 END IF;
2602
2603 IF (c_level_exception >= g_log_level)
2604 THEN
2605 TRACE (p_module => l_log_module
2606 , p_msg => 'p_accounting_batch_id : '
2607 || p_accounting_batch_id
2608 , p_level => c_level_exception
2609 );
2610 END IF;
2611
2612 IF (c_level_exception >= g_log_level)
2613 THEN
2614 TRACE (p_module => l_log_module
2615 , p_msg => 'p_execution_mode : ' || p_execution_mode
2616 , p_level => c_level_exception
2617 );
2618 END IF;
2619
2620 IF (c_level_exception >= g_log_level)
2621 THEN
2622 TRACE (p_module => l_log_module
2623 , p_msg => 'p_update_mode : ' || p_execution_mode
2624 , p_level => c_level_exception
2625 );
2626 END IF;
2627
2628 IF (c_level_exception >= g_log_level)
2629 THEN
2630 TRACE (p_module => l_log_module
2631 , p_msg => 'request_id : ' || g_req_id
2632 , p_level => c_level_exception
2633 );
2634 END IF;
2635
2639 AND p_entity_id IS NULL
2636 IF p_ledger_id is not NULL
2637 AND p_accounting_batch_id IS NULL
2638 AND p_event_id IS NULL
2640 AND p_ae_header_id IS NULL
2641 THEN
2642 l_open_period_sql := 'SELECT SUM(
2643 DECODE(xlo_effective_period_num, gps_effective_period_num,0,1)
2644 )
2645 FROM (
2646 SELECT DISTINCT xlo.effective_period_num xlo_effective_period_num
2647 ,(SELECT MAX(gps.effective_period_num)
2648 FROM gl_period_statuses gps
2649 WHERE gps.application_id = 101
2650 AND gps.ledger_id = xlo.ledger_id
2651 AND gps.closing_status IN (''O'',''C'',''P'')
2652 AND gps.adjustment_period_flag = ''N''
2653 )gps_effective_period_num
2654 , xlo.ledger_id
2655 FROM xla_ledger_options xlo
2656 ,xla_ledger_relationships_v xlr
2657 WHERE xlr.ledger_id = '||p_ledger_id || '
2658 AND xlo.ledger_id = DECODE(xlr.ledger_category_code , ''ALC''
2659 ,xlr.primary_ledger_id, xlr.ledger_id)
2660 AND xlo.application_id = '||p_application_id||'
2661 )';
2662 ELSE
2663 l_open_period_sql := 'SELECT SUM(
2664 DECODE(xlo_effective_period_num, gps_effective_period_num,0,1)
2665 )
2666 FROM (
2667 SELECT DISTINCT xlo.effective_period_num xlo_effective_period_num
2668 ,(SELECT MAX(gps.effective_period_num)
2669 FROM gl_period_statuses gps
2670 WHERE gps.application_id = 101
2671 AND gps.ledger_id = xlo.ledger_id
2672 AND gps.closing_status IN (''O'',''C'',''P'')
2673 AND gps.adjustment_period_flag = ''N''
2674 )gps_effective_period_num
2675 , xlo.ledger_id
2676 FROM xla_ledger_options xlo
2677 ,xla_ledger_relationships_v xlr
2678 ,xla_ae_headers xah
2679 WHERE xlo.ledger_id = DECODE(xlr.ledger_category_code , ''ALC''
2680 ,xlr.primary_ledger_id, xlr.ledger_id)
2681 AND xlo.application_id = '||p_application_id||'
2682 AND xah.application_id = ' ||p_application_id ||'
2683 AND xlr.ledger_id = xah.ledger_id';
2684
2685 IF p_entity_id IS NOT NULL
2686 THEN
2687 l_open_period_sql := l_open_period_sql || '
2688 AND xah.entity_id = '||p_entity_id;
2689
2690 END IF;
2691
2692 IF p_event_id IS NOT NULL
2693 THEN
2694 l_open_period_sql := l_open_period_sql || '
2695 AND xah.event_id = '||p_event_id;
2696 END IF;
2697
2698 IF p_accounting_batch_id IS NOT NULL
2699 THEN
2700 l_open_period_sql := l_open_period_sql || '
2701 AND xah.accounting_batch_id = '||p_accounting_batch_id;
2702 END IF;
2703
2704 IF p_ae_header_id IS NOT NULL
2705 THEN
2706 l_open_period_sql := l_open_period_sql || '
2707 AND xah.ae_header_id = '||p_ae_header_id;
2708 END IF;
2709
2710 l_open_period_sql := l_open_period_sql || ')';
2711
2712 END IF;
2713
2714 IF (c_level_procedure >= g_log_level)
2715 THEN
2716 trace
2717 (p_msg => 'l_open_period_sql_1:'||substr(l_open_period_sql, 1, 1000)
2718 ,p_level => C_LEVEL_STATEMENT
2719 ,p_module => l_log_module);
2720
2721 trace
2722 (p_msg => 'l_open_period_sql_1:'||substr(l_open_period_sql, 1001, 999)
2723 ,p_level => C_LEVEL_STATEMENT
2724 ,p_module => l_log_module);
2725 END IF;
2726
2727 EXECUTE IMMEDIATE l_open_period_sql INTO l_eff_period_num;
2728 --
2729 --Proceed with balance calculation only if the balnaces are carried forward to the latest open peirod
2730 --
2731 IF l_eff_period_num > 0
2732 THEN
2733 fnd_file.put_line
2734 (fnd_file.LOG
2735 , 'Balances are not initialized for the latest open period.
2736 Before proceeding with balance calculation, run Open Period Balances Program for Ledger ID: '||p_ledger_id||' for the latest open period in General Ledger.'
2737 );
2738 xla_exceptions_pkg.raise_message
2739 (p_appli_s_name => 'XLA'
2740 , p_msg_name => 'XLA_COMMON_ERROR'
2741 , p_token_1 => 'LOCATION'
2742 , p_value_1 => 'xla_balances_calc_pkg.calculate_balances'
2743 , p_token_2 => 'ERROR'
2744 , p_value_2 => 'Balances are not initialized for the latest open period.
2748 --
2745 Before proceeding with balance calculation, run Open Period Balances Program for Ledger ID: '||p_ledger_id||' for the latest open period in General Ledger.'
2746 );
2747 END IF;
2749 -- Validate Input Parameters
2750 --
2751 IF p_execution_mode IS NULL
2752 THEN
2753 IF (c_level_exception >= g_log_level)
2754 THEN
2755 TRACE (p_module => l_log_module
2756 , p_msg => 'EXCEPTION:'
2757 || 'p_execution_mode cannot be NULL'
2758 , p_level => c_level_exception
2759 );
2760 END IF;
2761
2762 xla_exceptions_pkg.raise_message
2763 (p_appli_s_name => 'XLA'
2764 , p_msg_name => 'XLA_COMMON_ERROR'
2765 , p_token_1 => 'LOCATION'
2766 , p_value_1 => 'xla_balances_calc_pkg.calculate_balances'
2767 , p_token_2 => 'ERROR'
2768 , p_value_2 => 'EXCEPTION:'
2769 || 'p_execution_mode cannot be NULL'
2770 );
2771 END IF;
2772 -- End validation
2773
2774 IF p_update_mode IN ('A','F','M')
2775 THEN
2776 l_operation_code := 'A';
2777 ELSIF p_update_mode = 'D'
2778 THEN
2779 l_operation_code := 'R'; --remove
2780 ELSE
2781 IF (c_level_exception >= g_log_level)
2782 THEN
2783 TRACE
2784 (p_module => l_log_module
2785 , p_msg => 'EXCEPTION:'
2786 || 'Invalid value for Update Mode '|| p_update_mode
2787 , p_level => c_level_exception
2788 );
2789 END IF;
2790
2791 xla_exceptions_pkg.raise_message
2792 (p_appli_s_name => 'XLA'
2793 , p_msg_name => 'XLA_COMMON_ERROR'
2794 , p_token_1 => 'LOCATION'
2795 , p_value_1 => 'xla_balances_calc_pkg.calculate_balances'
2796 , p_token_2 => 'ERROR'
2797 , p_value_2 => 'EXCEPTION:'
2798 || 'Invalid value for update mode '||p_update_mode
2799 );
2800 END IF;
2801 IF l_operation_code = 'A'
2802 THEN
2803 g_preupdate_flag := 'P';
2804 g_postupdate_flag := 'Y';
2805 ELSIF l_operation_code = 'R'
2806 THEN
2807 g_preupdate_flag := 'Y';
2808 g_postupdate_flag := 'P';
2809 END If;
2810 IF (c_level_procedure >= g_log_level)
2811 THEN
2812 TRACE (p_module => l_log_module
2813 , p_msg => 'Calling calculate_analytical_balances'
2814 , p_level => c_level_procedure
2815 );
2816 END IF;
2817
2818 l_return_value :=
2819 calculate_analytical_balances ( p_application_id => p_application_id
2820 , p_ledger_id => p_ledger_id
2821 , p_entity_id => p_entity_id
2822 , p_event_id => p_event_id
2823 , p_ae_header_id => p_ae_header_id
2824 , p_ae_line_num => p_ae_line_num
2825 , p_request_id => p_request_id
2826 , p_accounting_batch_id => p_accounting_batch_id
2827 , p_operation_code => l_operation_code
2828 , p_execution_mode => p_execution_mode
2829 );
2830
2831 IF (c_level_procedure >= g_log_level)
2832 THEN
2833 TRACE (p_module => l_log_module
2834 , p_msg => 'Calling calculate_control_balances'
2835 , p_level => c_level_procedure
2836 );
2837 END IF;
2838
2839 l_return_value :=
2840 l_return_value
2841 AND calculate_control_balances( p_application_id => p_application_id
2842 , p_ledger_id => p_ledger_id
2843 , p_entity_id => p_entity_id
2844 , p_event_id => p_event_id
2845 , p_ae_header_id => p_ae_header_id
2846 , p_ae_line_num => p_ae_line_num
2847 , p_request_id => p_request_id
2848 , p_accounting_batch_id => p_accounting_batch_id
2849 , p_operation_code => l_operation_code
2850 , p_execution_mode => p_execution_mode
2851 );
2852
2853 IF (c_level_procedure >= g_log_level)
2854 THEN
2855 TRACE (p_module => l_log_module
2856 , p_msg => 'END ' || l_log_module
2857 , p_level => c_level_procedure
2858 );
2859 END IF;
2860
2861 RETURN l_return_value;
2862 EXCEPTION
2863 WHEN xla_exceptions_pkg.application_exception
2864 THEN
2865 ROLLBACK TO SAVEPOINT_BAL;
2866 RAISE;
2867 WHEN OTHERS
2868 THEN
2869 ROLLBACK TO SAVEPOINT_BAL;
2870 xla_exceptions_pkg.raise_message
2871 (p_location => 'xla_balances_calc_pkg.calculate_balances');
2872 RAISE;
2873 END calculate_balances;
2874
2878 +------------------------------------------------+
2875 /*===============================================+
2876 | |
2877 | Private Function |
2879 | Description: To carry forward the balances |
2880 | to target period for a given ledger |
2881 +===============================================*/
2882
2883 FUNCTION move_balances_forward (
2884 p_ledger_id IN INTEGER
2885 , p_effective_period_num IN NUMBER
2886 , p_period_name IN VARCHAR2
2887 )
2888 RETURN BOOLEAN
2889 IS
2890 l_log_module VARCHAR2 (240);
2891 l_from_effective_period_num NUMBER;
2892 l_count NUMBER;
2893
2894 CURSOR csr_eff_period (p_ledger_id VARCHAR2)
2895 IS
2896 SELECT distinct effective_period_num
2897 FROM xla_ledger_options
2898 WHERE ledger_id = p_ledger_id
2899 AND effective_period_num is not null;
2900 BEGIN
2901 IF g_log_enabled
2902 THEN
2903 l_log_module := c_default_module || '.open_period_event';
2904 END IF;
2905
2906 IF (c_level_procedure >= g_log_level)
2907 THEN
2908 TRACE (p_msg => 'BEGIN of procedure move_balances_forward'
2909 , p_level => c_level_procedure
2910 , p_module => l_log_module
2911 );
2912 END IF;
2913
2914 OPEN csr_eff_period (p_ledger_id => p_ledger_id);
2915 FETCH csr_eff_period INTO l_from_effective_period_num;
2916 CLOSE csr_eff_period;
2917
2918 -- Validate the Target Period
2919 SELECT count(1)
2920 INTO l_count
2921 FROM gl_period_statuses
2922 WHERE application_id=101
2923 AND ledger_id = p_ledger_id
2924 AND effective_period_num = p_effective_period_num
2925 AND closing_status in ('O','C','P')
2926 AND adjustment_period_flag = 'N';
2927
2928 IF (c_level_procedure >= g_log_level)
2929 THEN
2930 TRACE (p_msg => 'p_ledger_id'||p_ledger_id
2931 , p_level => c_level_procedure
2932 , p_module => l_log_module
2933 );
2934 END IF;
2935
2936 IF (c_level_procedure >= g_log_level)
2937 THEN
2938 TRACE (p_msg => 'l_from_effective_period_num'||l_from_effective_period_num
2939 , p_level => c_level_procedure
2940 , p_module => l_log_module
2941 );
2942 END IF;
2943
2944 IF (c_level_procedure >= g_log_level)
2945 THEN
2946 TRACE (p_msg => 'target Effective_period_num'||p_effective_period_num
2947 , p_level => c_level_procedure
2948 , p_module => l_log_module
2949 );
2950 END IF;
2951
2952 IF l_from_effective_period_num IS NULL
2953 THEN
2954 fnd_file.put_line
2955 (fnd_file.LOG
2956 , 'There is no record in xla_ledger_options for ledger '||p_ledger_id
2957 );
2958 RETURN FALSE;
2959 ELSIF l_from_effective_period_num >= p_effective_period_num
2960 THEN -- Proceed only if target period is greater than current open period
2961
2962 fnd_file.put_line
2963 (fnd_file.LOG
2964 , 'Balances exists for the target period ' ||p_period_name||' and ledger '||p_ledger_id
2965 );
2966 RETURN TRUE; -- Bug 13702056
2967
2968 ELSIF l_count = 0
2969 THEN -- if target period is Future enterable or Never open period EXIT
2970 fnd_file.put_line
2971 (fnd_file.LOG
2972 , 'Target period '||p_period_name||' is not Open/Close/Pending close Period '
2973 );
2974 return FALSE;
2975
2976 ELSE
2977
2978 IF (c_level_procedure >= g_log_level)
2979 THEN
2980 TRACE (p_msg => 'Opening Analytical Balances'
2981 , p_level => c_level_procedure
2982 , p_module => l_log_module
2983 );
2984 END IF;
2985
2986 INSERT INTO xla_ac_balances
2987 (application_id
2988 , ledger_id
2989 , code_combination_id
2990 , analytical_criterion_code
2991 , analytical_criterion_type_code
2992 , amb_context_code
2993 , ac1
2994 , ac2
2995 , ac3
2996 , ac4
2997 , ac5
2998 , period_name
2999 , first_period_flag
3000 , effective_period_num
3001 , initial_balance_flag
3002 , creation_date
3003 , created_by
3004 , last_update_date
3005 , last_updated_by
3006 , beginning_balance_dr
3007 , beginning_balance_cr
3008 , period_year
3009 , last_update_login
3010 , program_update_date
3011 , program_application_id
3012 , program_id
3013 , request_id
3014 )
3015 SELECT /*+ parallel(bal,24) */
3016 bal.application_id
3017 , bal.ledger_id
3018 , bal.code_combination_id
3019 , bal.analytical_criterion_code
3020 , bal.analytical_criterion_type_code
3021 , bal.amb_context_code
3022 , bal.ac1
3023 , bal.ac2
3024 , bal.ac3
3025 , bal.ac4
3026 , bal.ac5
3027 , gps.period_name
3028 , DECODE (period_num, 1, 'Y', 'N') first_period_flag
3029 , gps.effective_period_num
3033 , g_date
3030 , 'N' initial_balance_flag
3031 , g_date
3032 , g_user_id
3034 , g_user_id
3035 , DECODE (gps.period_year
3036 , SUBSTR (bal.effective_period_num, 1, 4), ( NVL
3037 (bal.beginning_balance_dr
3038 , 0
3039 )
3040 + NVL
3041 (bal.period_balance_dr
3042 , 0
3043 )
3044 )
3045 , DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
3046 + NVL (bal.period_balance_dr, 0)
3047 )
3048 - ( NVL (bal.beginning_balance_cr, 0)
3049 + NVL (bal.period_balance_cr, 0)
3050 )
3051 )
3052 , 1, ( ( NVL (bal.beginning_balance_dr, 0)
3053 + NVL (bal.period_balance_dr, 0)
3054 )
3055 - ( NVL (bal.beginning_balance_cr, 0)
3056 + NVL (bal.period_balance_cr, 0)
3057 )
3058 )
3059 , 0
3060 )
3061 ) beginning_balance_dr
3062 , DECODE (gps.period_year
3063 , SUBSTR (bal.effective_period_num, 1, 4), ( NVL
3064 (bal.beginning_balance_cr
3065 , 0
3066 )
3067 + NVL
3068 (bal.period_balance_cr
3069 , 0
3070 )
3071 )
3072 , DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
3073 + NVL (bal.period_balance_dr, 0)
3074 )
3075 - ( NVL (bal.beginning_balance_cr, 0)
3076 + NVL (bal.period_balance_cr, 0)
3077 )
3078 )
3079 , -1, ( NVL (bal.beginning_balance_cr, 0)
3080 + NVL (bal.period_balance_cr, 0)
3081 )
3082 - ( NVL (bal.beginning_balance_dr, 0)
3083 + NVL (bal.period_balance_dr, 0)
3084 )
3085 , 0
3086 )
3087 ) beginning_balance_cr
3088 ,gps.period_year
3089 ,g_login_id
3090 ,g_date
3091 ,g_prog_appl_id
3092 ,g_prog_id
3093 ,g_req_id
3094 FROM gl_period_statuses gps
3095 , xla_ac_balances bal
3096 , gl_code_combinations gcc
3097 , xla_analytical_hdrs_b xbh
3098 , (select ledger_id
3099 from xla_ledger_relationships_v
3100 where (ledger_category_code IN ('PRIMARY','ALC')
3101 and primary_ledger_id = p_ledger_id)
3102 or (ledger_category_code = 'SECONDARY'
3103 and ledger_id = p_ledger_id)
3104 ) xlr
3105 WHERE gps.application_id = 101
3106 AND gps.ledger_id = p_ledger_id
3107 AND gps.closing_status IN ('O', 'C', 'P')
3108 AND gps.adjustment_period_flag = 'N'
3109 AND gps.effective_period_num <= p_effective_period_num
3110 AND gps.effective_period_num > l_from_effective_period_num
3111 AND bal.effective_period_num = l_from_effective_period_num
3112 AND bal.ledger_id = xlr.ledger_id
3113 AND gcc.code_combination_id = bal.code_combination_id
3114 AND xbh.analytical_criterion_code = bal.analytical_criterion_code
3115 AND xbh.analytical_criterion_type_code =
3116 bal.analytical_criterion_type_code
3117 AND xbh.amb_context_code = bal.amb_context_code
3118 AND xbh.balancing_flag <> 'N'
3119 AND ( gps.period_year = SUBSTR (bal.effective_period_num, 1, 4)
3120 OR xbh.year_end_carry_forward_code = 'A'
3121 OR ( xbh.year_end_carry_forward_code = 'B'
3122 AND gcc.account_type IN ('A', 'L', 'O')
3123 )
3124 );
3125
3126 IF (c_level_procedure >= g_log_level)
3127 THEN
3128 TRACE (p_msg => '# rows created for Analytical Balances : ' || SQL%ROWCOUNT
3129 , p_level => c_level_procedure
3130 , p_module => l_log_module
3131 );
3132 END IF;
3133
3134 IF (c_level_procedure >= g_log_level)
3135 THEN
3136 TRACE (p_msg => 'Opening Control Balances'
3137 , p_level => c_level_procedure
3141
3138 , p_module => l_log_module
3139 );
3140 END IF;
3142 INSERT INTO xla_control_balances
3143 (application_id
3144 , ledger_id
3145 , code_combination_id
3146 , party_type_code
3147 , party_id
3148 , party_site_id
3149 , period_name
3150 , first_period_flag
3151 , effective_period_num
3152 , initial_balance_flag
3153 , creation_date
3154 , created_by
3155 , last_update_date
3156 , last_updated_by
3157 , beginning_balance_dr
3158 , beginning_balance_cr
3159 , period_year
3160 , last_update_login
3161 , program_update_date
3162 , program_application_id
3163 , program_id
3164 , request_id
3165 )
3166 SELECT /*+ parallel(bal,24) */
3167 bal.application_id
3168 , bal.ledger_id
3169 , bal.code_combination_id
3170 , bal.party_type_code
3171 , bal.party_id
3172 , bal.party_site_id
3173 , gps.period_name
3174 , DECODE (period_num, 1, 'Y', 'N') first_period_flag
3175 , gps.effective_period_num
3176 , 'N' initial_balance_flag
3177 , g_date
3178 , g_user_id
3179 , g_date
3180 , g_user_id
3181 , DECODE (gps.period_year
3182 , SUBSTR (bal.effective_period_num, 1, 4), ( NVL
3183 (bal.beginning_balance_dr
3184 , 0
3185 )
3186 + NVL
3187 (bal.period_balance_dr
3188 , 0
3189 )
3190 )
3191 , DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
3192 + NVL (bal.period_balance_dr, 0)
3193 )
3194 - ( NVL (bal.beginning_balance_cr, 0)
3195 + NVL (bal.period_balance_cr, 0)
3196 )
3197 )
3198 , 1, ( ( NVL (bal.beginning_balance_dr, 0)
3199 + NVL (bal.period_balance_dr, 0)
3200 )
3201 - ( NVL (bal.beginning_balance_cr, 0)
3202 + NVL (bal.period_balance_cr, 0)
3203 )
3204 )
3205 , 0
3206 )
3207 ) beginning_balance_dr
3208 , DECODE (gps.period_year
3209 , SUBSTR (bal.effective_period_num, 1, 4), ( NVL
3210 (bal.beginning_balance_cr
3211 , 0
3212 )
3213 + NVL
3214 (bal.period_balance_cr
3215 , 0
3216 )
3217 )
3218 , DECODE (SIGN ( ( NVL (bal.beginning_balance_dr, 0)
3219 + NVL (bal.period_balance_dr, 0)
3220 )
3221 - ( NVL (bal.beginning_balance_cr, 0)
3222 + NVL (bal.period_balance_cr, 0)
3223 )
3224 )
3225 , -1, ( NVL (bal.beginning_balance_cr, 0)
3226 + NVL (bal.period_balance_cr, 0)
3227 )
3228 - ( NVL (bal.beginning_balance_dr, 0)
3229 + NVL (bal.period_balance_dr, 0)
3230 )
3231 , 0
3232 )
3233 ) beginning_balance_cr
3234 ,gps.period_year
3235 ,g_login_id
3236 ,g_date
3237 ,g_prog_appl_id
3238 ,g_prog_id
3239 ,g_req_id
3240 FROM gl_period_statuses gps
3241 , xla_control_balances bal
3242 ,(select ledger_id
3243 from xla_ledger_relationships_v
3244 where (ledger_category_code IN ('PRIMARY','ALC')
3245 and primary_ledger_id = p_ledger_id)
3246 or (ledger_category_code = 'SECONDARY'
3247 and ledger_id = p_ledger_id)
3248 ) xlr
3249 WHERE gps.application_id = 101
3250 AND gps.ledger_id = p_ledger_id
3251 AND gps.closing_status IN ('O', 'C', 'P')
3252 AND gps.adjustment_period_flag = 'N'
3253 AND gps.effective_period_num <= p_effective_period_num
3257
3254 AND gps.effective_period_num > l_from_effective_period_num
3255 AND bal.effective_period_num = l_from_effective_period_num
3256 AND bal.ledger_id = xlr.ledger_id;
3258 IF (c_level_procedure >= g_log_level)
3259 THEN
3260 TRACE (p_msg => '# rows created for Control Balances : ' || SQL%ROWCOUNT
3261 , p_level => c_level_procedure
3262 , p_module => l_log_module
3263 );
3264 END IF;
3265
3266 --Bug 12673914
3267 --Added condition effective_period_num < p_effective_period_num below
3268 UPDATE xla_ledger_options
3269 SET effective_period_num = p_effective_period_num
3270 WHERE ledger_id = p_ledger_id
3271 AND nvl(effective_period_num,-1) < p_effective_period_num;
3272
3273 IF (c_level_procedure >= g_log_level)
3274 THEN
3275 TRACE (p_msg => '# rows updated in xla_ledger_options : ' || SQL%ROWCOUNT
3276 , p_level => c_level_procedure
3277 , p_module => l_log_module
3278 );
3279 END IF;
3280
3281 IF (c_level_procedure >= g_log_level)
3282 THEN
3283 TRACE (p_msg => 'xla_ledger_options updated with effective_period_num '||p_effective_period_num
3284 , p_level => c_level_procedure
3285 , p_module => l_log_module
3286 );
3287 END IF;
3288 END IF;
3289
3290 IF (c_level_procedure >= g_log_level)
3291 THEN
3292 TRACE (p_msg => 'END of procedure move_balances_forward'
3293 , p_level => c_level_procedure
3294 , p_module => l_log_module
3295 );
3296 END IF;
3297
3298 RETURN TRUE;
3299 EXCEPTION
3300 WHEN xla_exceptions_pkg.application_exception
3301 THEN
3302 RAISE;
3303 WHEN OTHERS
3304 THEN
3305 xla_exceptions_pkg.raise_message
3306 (p_location => 'xla_balances_calc_pkg.move_balances_forward');
3307 RETURN FALSE;
3308 END move_balances_forward;
3309
3310 /*===============================================+
3311 | |
3312 | public Function |
3313 |----------------- |
3314 | Description: |
3315 | |
3316 | |
3317 +===============================================*/
3318 PROCEDURE open_period_srs (
3319 p_errbuf OUT NOCOPY VARCHAR2
3320 , p_retcode OUT NOCOPY NUMBER
3321 , p_application_id IN NUMBER
3322 , p_ledger_id IN NUMBER
3323 , p_period_name IN VARCHAR2
3324 )
3325 IS
3326 /*======================================================================+
3327 | |
3328 | Public Function |
3329 | |
3330 | Description |
3331 | ----------- |
3332 | Just the SRS wrapper for Open Period balances |
3333 | |
3334 | Pseudo-code |
3335 | ----------- |
3336 | Call create_new_period_balances and assign its return code to |
3337 | p_retcode |
3338 | RETURN p_retcode (0=success, 1=warning, 2=error) |
3339 | |
3340 | |
3341 | |
3342 +======================================================================*/
3343 l_sobname VARCHAR2 (30);
3344 l_log_module VARCHAR2 (2000);
3345 l_effective_period_num NUMBER;
3346 l_ledger_category_code VARCHAR2(30);
3347 l_adjustment_period_flag VARCHAR2(1); -- Bug 12613841
3348 l_xlo_effperiod_count NUMBER;
3349 l_xlo_not_nul_count NUMBER;
3350 l_bal_count NUMBER;
3351 l_bal_sob_count NUMBER;
3352 l_not_nul_count NUMBER;
3353
3354 CURSOR lock_bal_control (p_ledger_id NUMBER)
3355 IS
3356 SELECT application_id
3357 ,ledger_id
3358 FROM xla_bal_concurrency_control
3359 WHERE ledger_id = p_ledger_id
3360 AND application_id IN ( SELECT * FROM TABLE(g_application_array))
3361 FOR UPDATE NOWAIT; --Lock All the applications belonging to this ledger
3362
3363 CURSOR csr_ledger(p_ledger_id NUMBER, p_period_name VARCHAR2)
3364 IS
3365 SELECT l.ledger_category_code
3366 , gps.effective_period_num
3367 , adjustment_period_flag -- Bug 12613841
3368 FROM gl_period_statuses gps
3369 , gl_ledgers l
3370 WHERE l.ledger_id = p_ledger_id
3371 AND gps.ledger_id = l.ledger_id
3372 AND gps.application_id = 101
3373 AND gps.period_name = p_period_name;
3374
3375 CURSOR csr_xlo_effperiod(p_ledger_id NUMBER)
3376 IS
3377 SELECT COUNT(1) xlo_effperiod_count
3378 FROM xla_ledger_options
3379 WHERE ledger_id = p_ledger_id
3380 AND effective_period_num is not null;
3381 -- Bug12613841 modified cursor csr_bal_count to add where effective_period_num is null
3382 CURSOR csr_bal_count
3383 IS
3387 OR EXISTS (SELECT 1 FROM xla_ac_balances where effective_period_num is null);
3384 SELECT COUNT(1) bal_count
3385 FROM dual
3386 WHERE EXISTS (SELECT 1 FROM xla_control_balances where effective_period_num is null)
3388
3389 CURSOR csr_bal_sob_count(p_ledger_id NUMBER)
3390 IS
3391 SELECT COUNT(1) bal_sob_count
3392 FROM dual
3393 WHERE EXISTS (SELECT 1 FROM xla_control_balances WHERE ledger_id = p_ledger_id)
3394 OR EXISTS (SELECT 1 FROM xla_ac_balances WHERE ledger_id = p_ledger_id);
3395
3396 -- Bug12613841 added the cursor csr_xlo_not_null_count
3397 CURSOR csr_xlo_not_null_count
3398 IS
3399 SELECT count(1)
3400 INTO l_not_nul_count
3401 FROM xla_ledger_options
3402 WHERE effective_period_num IS NOT NULL;
3403
3404 BEGIN
3405 IF g_log_enabled
3406 THEN
3407 l_log_module := c_default_module || '.open_period_srs';
3408 END IF;
3409
3410 IF (c_level_procedure >= g_log_level)
3411 THEN
3412 TRACE (p_module => l_log_module
3413 , p_msg => 'BEGIN ' || l_log_module
3414 , p_level => c_level_procedure
3415 );
3416 END IF;
3417
3418 OPEN csr_ledger(p_ledger_id => p_ledger_id
3419 , p_period_name => p_period_name);
3420 FETCH csr_ledger
3421 INTO l_ledger_category_code, l_effective_period_num, l_adjustment_period_flag;
3422 CLOSE csr_ledger;
3423
3424 OPEN csr_xlo_effperiod(p_ledger_id => p_ledger_id);
3425 FETCH csr_xlo_effperiod
3426 INTO l_xlo_effperiod_count;
3427 CLOSE csr_xlo_effperiod;
3428
3429 OPEN csr_bal_count;
3430 FETCH csr_bal_count INTO l_bal_count;
3431 CLOSE csr_bal_count;
3432
3433 OPEN csr_bal_sob_count (p_ledger_id => p_ledger_id);
3434 FETCH csr_bal_sob_count INTO l_bal_sob_count;
3435 CLOSE csr_bal_sob_count;
3436
3437 OPEN csr_xlo_not_null_count;
3438 fetch csr_xlo_not_null_count into l_xlo_not_nul_count;
3439 CLOSE csr_xlo_not_null_count;
3440
3441 --ESTABLISH SAVEPOINT -- Bug 13614923
3442 SAVEPOINT SAVEPOINT_BAL; -- Bug 13614923
3443
3444
3445 if l_adjustment_period_flag = 'Y' then -- Bug12613841
3446 fnd_file.put_line(fnd_file.LOG ,'Period:'||p_period_name||' is Adjustment Period so Balances data need not be maintained for this period');
3447 p_retcode := 0;
3448 else
3449
3450
3451 IF l_bal_count > 0
3452 AND l_xlo_effperiod_count = 0
3453 THEN
3454 IF l_xlo_not_nul_count > 0 THEN -- added Bug12613841
3455
3456 UPDATE xla_ledger_options xlo
3457 SET effective_period_num = (SELECT gps.effective_period_num
3458 FROM gl_period_statuses gps,gl_ledgers gll
3459 WHERE gps.application_id= 101
3460 AND gps.ledger_id = xlo.ledger_id
3461 AND gll.ledger_id = xlo.ledger_id
3462 AND gps.period_name = gll.FIRST_LEDGER_PERIOD_NAME
3463 )
3464 WHERE ledger_id in
3465 (
3466 SELECT ledger_id
3467 FROM xla_ledger_relationships_v xlr
3468 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3469 AND xlr.relationship_enabled_flag = 'Y'
3470 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3471 )
3472 and effective_period_num is null;
3473
3474 -- Begin Bug 13928188
3475
3476 UPDATE xla_ac_balances xab
3477 SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
3478 FROM gl_period_statuses gps
3479 WHERE gps.ledger_id = xab.ledger_id
3480 AND gps.application_id = 101
3481 AND gps.adjustment_period_flag = 'N'
3482 AND gps.period_name = xab.period_name
3483 )
3484 WHERE effective_period_num is null
3485 AND ledger_id in
3486 (
3487 SELECT ledger_id
3488 FROM xla_ledger_relationships_v xlr
3489 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3490 AND xlr.relationship_enabled_flag = 'Y'
3491 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3492 );
3493
3494
3495 IF (c_level_procedure >= g_log_level)
3496 THEN
3497 TRACE (p_module => l_log_module
3498 , p_msg => '# Rows update in xla_ac_balances ' || SQL%ROWCOUNT
3499 , p_level => c_level_procedure
3500 );
3501 END IF;
3502
3503
3504 UPDATE xla_control_balances xab
3505 SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
3506 FROM gl_period_statuses gps
3507 WHERE gps.ledger_id = xab.ledger_id
3508 AND gps.application_id = 101
3509 AND gps.adjustment_period_flag = 'N'
3510 AND gps.period_name=xab.period_name
3511 )
3512 WHERE effective_period_num is null
3513 AND ledger_id in
3514 (
3515 SELECT ledger_id
3516 FROM xla_ledger_relationships_v xlr
3517 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3521
3518 AND xlr.relationship_enabled_flag = 'Y'
3519 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3520 );
3522
3523 IF (c_level_procedure >= g_log_level)
3524 THEN
3525 TRACE (p_module => l_log_module
3526 , p_msg => '# Rows update in xla_control_balances ' || SQL%ROWCOUNT
3527 , p_level => c_level_procedure
3528 );
3529 END IF;
3530
3531
3532
3533 -- End Bug 13928188
3534
3535
3536 ELSE
3537 fnd_file.put_line(fnd_file.LOG
3538 ,'Balances upgrade script xlabalupg.sql has not been run.
3539 Run xlabalupg.sql to use Update Subledger Accounting Balances program'
3540 );
3541 p_retcode := 0;
3542 END IF;
3543 ELSIF l_bal_sob_count = 0
3544 AND l_ledger_category_code <> 'ALC'
3545 THEN -- No record in balances table. So, update xla_ledger_options and exit
3546
3547 --Bug 12673914
3548 --Added condition effective_period_num < l_effective_period_num
3549 UPDATE xla_ledger_options
3550 SET effective_period_num = l_effective_period_num
3551 WHERE ledger_id in
3552 (
3553 SELECT ledger_id
3554 FROM xla_ledger_relationships_v xlr
3555 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3556 AND xlr.relationship_enabled_flag = 'Y'
3557 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3558 )
3559 AND nvl(effective_period_num,-1) < l_effective_period_num;
3560
3561
3562
3563 IF (c_level_procedure >= g_log_level)
3564 THEN
3565 TRACE (p_module => l_log_module
3566 , p_msg => '# Rows update in xla_ledger_options ' || SQL%ROWCOUNT
3567 , p_level => c_level_procedure
3568 );
3569 END IF;
3570
3571
3572 -- Begin Bug 13928188
3573
3574 UPDATE xla_ac_balances xab
3575 SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
3576 FROM gl_period_statuses gps
3577 WHERE gps.ledger_id = xab.ledger_id
3578 AND gps.application_id = 101
3579 AND gps.adjustment_period_flag = 'N'
3580 AND gps.period_name = xab.period_name
3581 )
3582 WHERE effective_period_num is null
3583 AND ledger_id in
3584 (
3585 SELECT ledger_id
3586 FROM xla_ledger_relationships_v xlr
3587 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3588 AND xlr.relationship_enabled_flag = 'Y'
3589 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3590 );
3591
3592
3593 IF (c_level_procedure >= g_log_level)
3594 THEN
3595 TRACE (p_module => l_log_module
3596 , p_msg => '# Rows update in xla_ac_balances ' || SQL%ROWCOUNT
3597 , p_level => c_level_procedure
3598 );
3599 END IF;
3600
3601
3602 UPDATE xla_control_balances xab
3603 SET EFFECTIVE_PERIOD_NUM = ( SELECT gps.effective_period_num
3604 FROM gl_period_statuses gps
3605 WHERE gps.ledger_id = xab.ledger_id
3606 AND gps.application_id = 101
3607 AND gps.adjustment_period_flag = 'N'
3608 AND gps.period_name=xab.period_name
3609 )
3610 WHERE effective_period_num is null
3611 AND ledger_id in
3612 (
3613 SELECT ledger_id
3614 FROM xla_ledger_relationships_v xlr
3615 WHERE (xlr.primary_ledger_id = p_ledger_id OR ledger_id = p_ledger_id)
3616 AND xlr.relationship_enabled_flag = 'Y'
3617 AND xlr.ledger_category_code IN('ALC', 'PRIMARY', 'SECONDARY')
3618 );
3619
3620
3621 IF (c_level_procedure >= g_log_level)
3622 THEN
3623 TRACE (p_module => l_log_module
3624 , p_msg => '# Rows update in xla_control_balances ' || SQL%ROWCOUNT
3625 , p_level => c_level_procedure
3626 );
3627 END IF;
3628
3629
3630
3631 -- End Bug 13928188
3632
3633
3634 p_retcode := 0;
3635 ELSE
3636 IF l_ledger_category_code <> 'ALC'
3637 THEN -- ALC ledger's balances are created along with primary.
3638 -- So exit if the program is called for ALC ledger.
3639
3640 fnd_file.put_line (fnd_file.LOG, 'p_ledger_id: ' || p_ledger_id);
3641 fnd_file.put_line (fnd_file.LOG, 'p_effective_period_num: ' || l_effective_period_num);
3642 p_retcode := 0;
3643
3644 IF NOT lock_bal_concurrency_control ( p_application_id => NULL
3645 , p_ledger_id => p_ledger_id
3646 , p_entity_id => NULL
3647 , p_event_id => NULL
3648 , p_ae_header_id => NULL
3652 , p_execution_mode => NULL
3649 , p_ae_line_num => NULL
3650 , p_request_id => g_req_id
3651 , p_accounting_batch_id => NULL
3653 , p_concurrency_class => 'OPEN_PERIOD_BALANCE'
3654 )
3655 THEN
3656 xla_exceptions_pkg.raise_message
3657 (p_appli_s_name => 'XLA'
3658 , p_msg_name => 'XLA_COMMON_ERROR'
3659 , p_token_1 => 'LOCATION'
3660 , p_value_1 => 'xla_balances_calc_pkg.open_period_srs'
3661 , p_token_2 => 'ERROR'
3662 , p_value_2 => 'EXCEPTION:'|| 'Record cannot be inserted into XLA_BAL_CONCURRENCY_CONTROL '
3663 );
3664 END IF;
3665
3666 OPEN lock_bal_control (p_ledger_id => p_ledger_id );
3667 CLOSE lock_bal_control;
3668
3669 fnd_file.put_line (fnd_file.LOG, 'Opening SLA Period Balances');
3670
3671 IF move_balances_forward (p_ledger_id => p_ledger_id
3672 , p_effective_period_num => l_effective_period_num
3673 , p_period_name => p_period_name
3674 )
3675 THEN
3676 fnd_file.put_line (fnd_file.LOG, 'Open Period Balances Successfully completed');
3677
3678 delete xla_bal_concurrency_control where request_id = g_req_id; --Bug 13614923
3679
3680
3681 ELSE
3682 delete xla_bal_concurrency_control where request_id = g_req_id; --Bug 13614923
3683
3684 p_retcode := p_retcode + 1;
3685 fnd_file.put_line (fnd_file.LOG, 'Unsuccessful');
3686 ROLLBACK TO SAVEPOINT_BAL;
3687 END IF;
3688 END IF;
3689
3690 IF (c_level_procedure >= g_log_level)
3691 THEN
3692 TRACE (p_module => l_log_module
3693 , p_msg => 'END ' || l_log_module
3694 , p_level => c_level_procedure
3695 );
3696 END IF;
3697 END IF;
3698 end if; -- bug 12613841
3699 EXCEPTION
3700 WHEN le_resource_busy or le_wait_expire or le_deadlock_detected
3701 THEN
3702 IF (c_level_error >= g_log_level)
3703 THEN
3704 TRACE (p_module => l_log_module
3705 , p_msg => 'Cannot lock XLA_BAL_CONCURRENCY_CONTROL'
3706 , p_level => c_level_error
3707 );
3708 END IF;
3709
3710 IF (c_level_procedure >= g_log_level)
3711 THEN
3712 TRACE (p_module => l_log_module
3713 , p_msg => 'END ' || l_log_module
3714 , p_level => c_level_procedure
3715 );
3716 END IF;
3717
3718 p_retcode := 1;
3719 fnd_file.put_line
3720 (fnd_file.LOG
3721 ,'There is another request running for the ledger_id : '
3722 || p_ledger_id
3723 || '. Pls. submit Open Period Balances Concurrent Program once the running request is completed'
3724 );
3725 --Added below rollback statement
3726 ROLLBACK TO SAVEPOINT_BAL; -- Bug13614923
3727 delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
3728
3729 WHEN xla_exceptions_pkg.application_exception
3730 THEN
3731 p_retcode := 2;
3732 p_errbuf := SQLERRM;
3733 --Added below rollback statement
3734 ROLLBACK TO SAVEPOINT_BAL; -- Bug13614923
3735 delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
3736 WHEN OTHERS
3737 THEN
3738 p_retcode := 2;
3739 --Added below rollback statement
3740 ROLLBACK TO SAVEPOINT_BAL; -- Bug13614923
3741 delete xla_bal_concurrency_control where request_id = g_req_id; -- Bug13614923
3742 p_errbuf := SQLERRM;
3743 END open_period_srs;
3744
3745 /*===============================================+
3746 | |
3747 | public Function |
3748 | Description: |
3749 | |
3750 +===============================================*/
3751
3752 PROCEDURE massive_update_srs (
3753 p_errbuf OUT NOCOPY VARCHAR2
3754 , p_retcode OUT NOCOPY NUMBER
3755 , p_application_id IN NUMBER
3756 , p_ledger_id IN NUMBER
3757 , p_accounting_batch_id IN NUMBER
3758 , p_update_mode IN VARCHAR2
3759 )
3760 IS
3761 /*======================================================================+
3762 | |
3763 | Public Function |
3764 | |
3765 | Description |
3766 | ----------- |
3767 | Just the SRS wrapper for massive_update in batch mode |
3768 | |
3769 | Pseudo-code |
3770 | ----------- |
3774 | |
3771 | Call massive_update and assign its return code to |
3772 | p_retcode |
3773 | RETURN p_retcode (0=success, 1=warning, 2=error) |
3775 | Open issues |
3776 | ----------- |
3777 | |
3778 | 1) Need to review the value assigned to p_errbuf |
3779 | |
3780 | |
3781 | |
3782 +======================================================================*/
3783 l_commit_flag VARCHAR2 (1);
3784 l_log_module VARCHAR2 (2000);
3785 l_execution_mode VARCHAR2 (1);
3786 l_count NUMBER;
3787 l_ledger_count NUMBER;
3788 l_ledger_nul_count NUMBER;
3789
3790 CURSOR lock_bal_control (p_application_id NUMBER)
3791 IS
3792 SELECT application_id
3793 ,ledger_id
3794 FROM xla_bal_concurrency_control
3795 WHERE application_id = p_application_id
3796 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
3797 FOR UPDATE NOWAIT;
3798
3799
3800 -- End Bug 13614923
3801
3802
3803 BEGIN
3804 IF g_log_enabled
3805 THEN
3806 l_log_module := c_default_module || '.massive_update_srs';
3807 END IF;
3808
3809 IF (c_level_procedure >= g_log_level)
3810 THEN
3811 TRACE (p_module => l_log_module
3812 , p_msg => 'BEGIN ' || l_log_module
3813 , p_level => c_level_procedure
3814 );
3815 END IF;
3816
3817 IF (c_level_procedure >= g_log_level)
3818 THEN
3819 TRACE (p_module => l_log_module
3820 , p_msg => 'p_application_id ' || p_application_id
3821 , p_level => c_level_procedure
3822 );
3823 END IF;
3824 IF (c_level_procedure >= g_log_level)
3825 THEN
3826 TRACE (p_module => l_log_module
3827 , p_msg => 'p_ledger_id ' || p_ledger_id
3828 , p_level => c_level_procedure
3829 );
3830 END IF;
3831 IF (c_level_procedure >= g_log_level)
3832 THEN
3833 TRACE (p_module => l_log_module
3834 , p_msg => 'p_accounting_batch_id ' || p_accounting_batch_id
3835 , p_level => c_level_procedure
3836 );
3837 END IF;
3838 IF (c_level_procedure >= g_log_level)
3839 THEN
3840 TRACE (p_module => l_log_module
3841 , p_msg => 'p_update_mode ' || p_update_mode
3842 , p_level => c_level_procedure
3843 );
3844 END IF;
3845
3846 fnd_file.put_line (fnd_file.LOG
3847 , 'p_application_id: ' || p_application_id
3848 );
3849 fnd_file.put_line (fnd_file.LOG, 'p_ledger_id: ' || p_ledger_id);
3850 fnd_file.put_line (fnd_file.LOG
3851 , 'p_accounting_batch_id: ' || p_accounting_batch_id
3852 );
3853 fnd_file.put_line (fnd_file.LOG, 'p_request_id: ' || g_req_id);
3854
3855 --Bug 12575468
3856 --ESTABLISH SAVEPOINT
3857 SAVEPOINT SAVEPOINT_BAL;
3858
3859 -- Check if balances upgrade script has been run.
3860 SELECT count(1)
3861 INTO l_ledger_count
3862 FROM xla_ledger_options
3863 WHERE effective_period_num IS NOT NULL;
3864
3865 IF l_ledger_count = 0
3866 THEN
3867 fnd_file.put_line(fnd_file.LOG
3868 ,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
3869 );
3870 p_retcode := 1; --bug13614923
3871 ELSE
3872
3873 --parameter validation
3874 --p_application_id must have a value, always
3875 IF p_application_id IS NULL
3876 THEN
3877 IF (c_level_exception >= g_log_level)
3878 THEN
3879 TRACE (p_module => l_log_module
3880 , p_msg => 'EXCEPTION:'
3881 || 'p_application_id cannot be NULL'
3882 , p_level => c_level_exception
3883 );
3884 END IF;
3885
3886 xla_exceptions_pkg.raise_message
3887 (p_appli_s_name => 'XLA'
3888 , p_msg_name => 'XLA_COMMON_ERROR'
3889 , p_token_1 => 'LOCATION'
3890 , p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
3891 , p_token_2 => 'ERROR'
3892 , p_value_2 => 'EXCEPTION:'
3893 || 'p_application_id cannot be NULL'
3894 );
3895 END IF;
3896
3897 IF p_ledger_id IS NULL AND p_accounting_batch_id IS NULL
3898 THEN
3899 IF (c_level_exception >= g_log_level)
3900 THEN
3901 TRACE
3902 (p_module => l_log_module
3903 , p_msg => 'EXCEPTION:'
3904 || 'p_ledger_id and p_accounting_batch_id cannot be NULL'
3905 , p_level => c_level_exception
3906 );
3907 END IF;
3908
3909 xla_exceptions_pkg.raise_message
3910 (p_appli_s_name => 'XLA'
3911 , p_msg_name => 'XLA_COMMON_ERROR'
3915 , p_value_2 => 'EXCEPTION:'
3912 , p_token_1 => 'LOCATION'
3913 , p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
3914 , p_token_2 => 'ERROR'
3916 || 'p_ledger_id and p_accounting_batch_id cannot be NULL'
3917 );
3918 END IF;
3919
3920 l_execution_mode := 'C';
3921
3922 IF NOT lock_bal_concurrency_control ( p_application_id => p_application_id
3923 , p_ledger_id => p_ledger_id
3924 , p_entity_id => NULL
3925 , p_event_id => NULL
3926 , p_ae_header_id => NULL
3927 , p_ae_line_num => NULL
3928 , p_request_id => g_req_id
3929 , p_accounting_batch_id => p_accounting_batch_id
3930 , p_execution_mode => l_execution_mode
3931 , p_concurrency_class => 'BALANCES_CALCULATION'
3932 )
3933 THEN
3934 xla_exceptions_pkg.raise_message
3935 (p_appli_s_name => 'XLA'
3936 , p_msg_name => 'XLA_COMMON_ERROR'
3937 , p_token_1 => 'LOCATION'
3938 , p_value_1 => 'xla_balances_calc_pkg.massive_update_srs'
3939 , p_token_2 => 'ERROR'
3940 , p_value_2 => 'EXCEPTION:'
3941 || 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
3942 );
3943 END IF;
3944
3945
3946 -- End bug 13614923
3947
3948
3949 OPEN lock_bal_control (p_application_id => p_application_id);
3950
3951 CLOSE lock_bal_control;
3952
3953 fnd_file.put_line (fnd_file.LOG , 'Successfully Locked');
3954
3955 IF calculate_balances ( p_application_id => p_application_id
3956 , p_ledger_id => p_ledger_id
3957 , p_entity_id => NULL
3958 , p_event_id => NULL
3959 , p_ae_header_id => NULL
3960 , p_ae_line_num => NULL
3961 , p_request_id => g_req_id
3962 , p_accounting_batch_id => p_accounting_batch_id
3963 , p_update_mode => p_update_mode
3964 , p_execution_mode => l_execution_mode
3965 )
3966 THEN
3967 p_retcode := 0; --bug14255667
3968
3969 --DELETE RECORDS FROM XLA_BAL_CONCURRENCY_CONTROL TABLE
3970 DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
3971 ELSE
3972 ROLLBACK TO SAVEPOINT_BAL;
3973 DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
3974 p_retcode := 1;
3975 END IF;
3976 END IF;
3977 EXCEPTION
3978 WHEN le_resource_busy or le_wait_expire or le_deadlock_detected --Bug 13614923
3979 THEN
3980 IF (c_level_error >= g_log_level)
3981 THEN
3982 TRACE (p_module => l_log_module
3983 , p_msg => 'Cannot lock XLA_BAL_CONCURRENCY_CONTROL'
3984 , p_level => c_level_error
3985 );
3986 END IF;
3987
3988 IF (c_level_procedure >= g_log_level)
3989 THEN
3990 TRACE (p_module => l_log_module
3991 , p_msg => 'END ' || l_log_module
3992 , p_level => c_level_procedure
3993 );
3994 END IF;
3995
3996 p_retcode := 1; --bug13614923
3997
3998 for i in ( SELECT distinct application_id
3999 ,ledger_id
4000 FROM xla_bal_concurrency_control
4001 WHERE application_id = p_application_id
4002 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
4003 loop
4004 fnd_file.put_line
4005 (fnd_file.LOG
4006 , 'There is another request(s) running for the ledger_id : '
4007 || i.ledger_id
4008 || ' application_id : '
4009 || i.application_id);
4010 end loop;
4011
4012 fnd_file.put_line
4013 (fnd_file.LOG ,
4014 'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
4015
4016 --Bug 12575468
4017 --Added below rollback statement
4018 ROLLBACK TO SAVEPOINT_BAL;
4019 DELETE xla_bal_concurrency_control WHERE request_id = g_req_id;
4020
4021 WHEN xla_exceptions_pkg.application_exception
4022 THEN
4023 p_retcode := 2; --bug13614923
4024 --Bug 12575468
4025 --Added below rollback statement
4026 ROLLBACK TO SAVEPOINT_BAL;
4027 DELETE xla_bal_concurrency_control WHERE request_id = g_req_id; --Bug 13614923
4028 RAISE;
4029 WHEN OTHERS
4030 THEN
4031 p_retcode := 2; --bug13614923
4032 --Bug 12575468
4033 --Added below rollback statement
4034 ROLLBACK TO SAVEPOINT_BAL;
4035 DELETE xla_bal_concurrency_control WHERE request_id = g_req_id;
4036 xla_exceptions_pkg.raise_message
4037 (p_location => 'xla_balances_calc_pkg.massive_update_srs');
4038 END massive_update_srs;
4039
4040 /*===============================================+
4041 | |
4042 | public Function |
4043 +------------------------------------------------+
4047 FUNCTION massive_update (
4044 | Description: |
4045 | |
4046 +===============================================*/
4048 p_application_id IN INTEGER
4049 , p_ledger_id IN INTEGER
4050 , p_entity_id IN INTEGER
4051 , p_event_id IN INTEGER
4052 , p_request_id IN INTEGER
4053 , p_accounting_batch_id IN INTEGER
4054 , p_update_mode IN VARCHAR2
4055 , p_execution_mode IN VARCHAR2
4056 )
4057 RETURN BOOLEAN
4058 IS
4059 /*======================================================================+
4060 | |
4061 | Public Function |
4062 | |
4063 | Description |
4064 | ----------- |
4065 | Called in online accounting flow |
4066 | |
4067 | Pseudo-code |
4068 | ----------- |
4069 | Call massive_update and assign its return code to |
4070 | p_retcode |
4071 | RETURN p_retcode (0=success, 1=warning, 2=error) |
4072 | |
4073 | Open issues |
4074 | ----------- |
4075 | |
4076 | 1) Need to review the value assigned to p_errbuf |
4077 | |
4078 | |
4079 | |
4080 +======================================================================*/
4081 l_commit_flag VARCHAR2 (1);
4082 l_log_module VARCHAR2 (2000);
4083 l_count NUMBER;
4084 l_success VARCHAR2 (1);
4085 l_return_value BOOLEAN;
4086 l_result BOOLEAN;
4087 l_req_id NUMBER;
4088 l_ledger_count NUMBER;
4089 l_ledger_nul_count NUMBER;
4090
4091 CURSOR lock_bal_control (p_application_id NUMBER)
4092 IS
4093 SELECT application_id
4094 ,ledger_id
4095 FROM xla_bal_concurrency_control
4096 WHERE application_id = p_application_id
4097 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
4098 FOR UPDATE NOWAIT;
4099
4100
4101 BEGIN
4102 IF g_log_enabled
4103 THEN
4104 l_log_module := c_default_module || '.massive_update';
4105 END IF;
4106
4107 IF (c_level_procedure >= g_log_level)
4108 THEN
4109 TRACE (p_module => l_log_module
4110 , p_msg => 'BEGIN ' || l_log_module
4111 , p_level => c_level_procedure
4112 );
4113 END IF;
4114
4115 IF (c_level_procedure >= g_log_level)
4116 THEN
4117 TRACE (p_module => l_log_module
4118 , p_msg => 'p_application_id ' || p_application_id
4119 , p_level => c_level_procedure
4120 );
4121 END IF;
4122 IF (c_level_procedure >= g_log_level)
4123 THEN
4124 TRACE (p_module => l_log_module
4125 , p_msg => 'p_ledger_id ' || p_ledger_id
4126 , p_level => c_level_procedure
4127 );
4128 END IF;
4129 IF (c_level_procedure >= g_log_level)
4130 THEN
4131 TRACE (p_module => l_log_module
4132 , p_msg => 'p_entity_id ' || p_entity_id
4133 , p_level => c_level_procedure
4134 );
4135 END IF;
4136 IF (c_level_procedure >= g_log_level)
4137 THEN
4138 TRACE (p_module => l_log_module
4139 , p_msg => 'p_event_id ' || p_event_id
4140 , p_level => c_level_procedure
4141 );
4142 END IF;
4143 IF (c_level_procedure >= g_log_level)
4144 THEN
4145 TRACE (p_module => l_log_module
4146 , p_msg => 'p_request_id ' || p_request_id
4147 , p_level => c_level_procedure
4148 );
4149 END IF;
4150 IF (c_level_procedure >= g_log_level)
4151 THEN
4152 TRACE (p_module => l_log_module
4153 , p_msg => 'p_accounting_batch_id ' || p_accounting_batch_id
4154 , p_level => c_level_procedure
4155 );
4156 END IF;
4157 IF (c_level_procedure >= g_log_level)
4158 THEN
4159 TRACE (p_module => l_log_module
4160 , p_msg => 'p_update_mode ' || p_update_mode
4161 , p_level => c_level_procedure
4162 );
4163 END IF;
4164 IF (c_level_procedure >= g_log_level)
4165 THEN
4166 TRACE (p_module => l_log_module
4167 , p_msg => 'p_execution_mode ' || p_execution_mode
4168 , p_level => c_level_procedure
4169 );
4170 END IF;
4171
4172
4173 fnd_file.put_line (fnd_file.LOG
4174 , 'p_application_id: ' || p_application_id
4175 );
4176 fnd_file.put_line (fnd_file.LOG, 'p_ledger_id: ' || p_ledger_id);
4177 fnd_file.put_line (fnd_file.LOG
4178 , 'p_accounting_batch_id: ' || p_accounting_batch_id
4179 );
4183 SAVEPOINT SAVEPOINT_BAL;
4180 fnd_file.put_line (fnd_file.LOG, 'p_request_id: ' || g_req_id);
4181
4182 --ESTABLISH SAVEPOINT
4184
4185 -- Check if balances upgrade script has been run.
4186 SELECT count(1)
4187 INTO l_ledger_count
4188 FROM xla_ledger_options
4189 WHERE effective_period_num IS NOT NULL;
4190
4191 IF l_ledger_count = 0
4192 THEN
4193 fnd_file.put_line(fnd_file.LOG
4194 ,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
4195 );
4196 RETURN TRUE;
4197 ELSE
4198
4199 --parameter validation
4200 --p_application_id must have a value
4201 IF p_application_id IS NULL
4202 THEN
4203 IF (c_level_exception >= g_log_level)
4204 THEN
4205 TRACE (p_module => l_log_module
4206 , p_msg => 'EXCEPTION:'
4207 || 'p_application_id cannot be NULL'
4208 , p_level => c_level_exception
4209 );
4210 END IF;
4211
4212 xla_exceptions_pkg.raise_message
4213 (p_appli_s_name => 'XLA'
4214 , p_msg_name => 'XLA_COMMON_ERROR'
4215 , p_token_1 => 'LOCATION'
4216 , p_value_1 => 'xla_balances_calc_pkg.massive_update'
4217 , p_token_2 => 'ERROR'
4218 , p_value_2 => 'EXCEPTION:'
4219 || 'p_application_id cannot be NULL'
4220 );
4221 END IF;
4222
4223 IF p_execution_mode = 'C'
4224 THEN
4225 --batch execution
4226 l_result := fnd_request.set_mode (TRUE);
4227 l_req_id :=
4228 fnd_request.submit_request (application => 'XLA'
4229 , program => 'XLABAPUB'
4230 , description => NULL
4231 , argument1 => p_application_id
4232 , argument2 => p_ledger_id
4233 , argument3 => p_accounting_batch_id
4234 , argument4 => p_update_mode
4235 );
4236
4237 IF (c_level_statement >= g_log_level)
4238 THEN
4239 TRACE (p_module => l_log_module
4240 , p_msg => 'Request ID: ' || l_req_id
4241 , p_level => c_level_statement
4242 );
4243 END IF;
4244
4245 IF l_req_id = 0
4246 THEN
4247 IF (c_level_statement >= g_log_level)
4248 THEN
4249 TRACE (p_module => l_log_module
4250 , p_msg => 'Unable to submit request'
4251 , p_level => c_level_statement
4252 );
4253 END IF;
4254
4255 l_return_value := FALSE;
4256 ELSE
4257 l_return_value := TRUE;
4258 END IF;
4259 ELSIF p_execution_mode = 'O'
4260 THEN
4261 IF NOT lock_bal_concurrency_control ( p_application_id => p_application_id
4262 , p_ledger_id => p_ledger_id
4263 , p_entity_id => p_entity_id
4264 , p_event_id => p_event_id
4265 , p_ae_header_id => NULL
4266 , p_ae_line_num => NULL
4267 , p_request_id => g_req_id
4268 , p_accounting_batch_id => p_accounting_batch_id
4269 , p_execution_mode => p_execution_mode
4270 , p_concurrency_class => 'BALANCES_CALCULATION'
4271 )
4272 THEN
4273 xla_exceptions_pkg.raise_message
4274 (p_appli_s_name => 'XLA'
4275 , p_msg_name => 'XLA_COMMON_ERROR'
4276 , p_token_1 => 'LOCATION'
4277 , p_value_1 => 'xla_balances_calc_pkg.MASSIVE_UPDATE'
4278 , p_token_2 => 'ERROR'
4279 , p_value_2 => 'EXCEPTION:'
4280 || 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
4281 );
4282 END IF;
4283
4284
4285 OPEN lock_bal_control (p_application_id => p_application_id );
4286
4287 CLOSE lock_bal_control;
4288
4289 fnd_file.put_line (fnd_file.LOG , 'Successfully Locked');
4290
4291 IF calculate_balances ( p_application_id => p_application_id
4292 , p_ledger_id => p_ledger_id
4293 , p_entity_id => p_entity_id
4294 , p_event_id => p_event_id
4295 , p_ae_header_id => NULL
4296 , p_ae_line_num => NULL
4297 , p_request_id => -1
4298 , p_accounting_batch_id => p_accounting_batch_id
4299 , p_update_mode => p_update_mode
4300 , p_execution_mode => p_execution_mode
4301 )
4302 THEN
4303 if p_entity_id is null then
4307 end if;
4304 DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
4305 else
4306 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
4308 RETURN TRUE;
4309 END IF;
4310 END IF;
4311
4312 -- Begin Bug 14115273
4313 if l_return_value then
4314 RETURN TRUE;
4315 else
4316 ROLLBACK TO SAVEPOINT_BAL;
4317 if p_entity_id is null then
4318 DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
4319 else
4320 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
4321 end if;
4322 RETURN TRUE;
4323 end if;
4324 -- End Bug 14115273
4325
4326 END IF;
4327 EXCEPTION
4328 WHEN le_resource_busy or le_wait_expire or le_deadlock_detected
4329 THEN
4330 IF (c_level_error >= g_log_level)
4331 THEN
4332 TRACE (p_module => l_log_module
4333 , p_msg => 'Cannot lock XLA_BAL_CONCURRENCY_CONTROL'
4334 , p_level => c_level_error
4335 );
4336 END IF;
4337
4338 for i in ( SELECT distinct application_id
4339 ,ledger_id
4340 FROM xla_bal_concurrency_control
4341 WHERE application_id = p_application_id
4342 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
4343 loop
4344 fnd_file.put_line
4345 (fnd_file.LOG
4346 , 'There is another request(s) running for the ledger_id : '
4347 || i.ledger_id
4348 || ' application_id : '
4349 || i.application_id);
4350 end loop;
4351
4352 fnd_file.put_line
4353 (fnd_file.LOG ,
4354 'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
4355
4356 IF (c_level_procedure >= g_log_level)
4357 THEN
4358 TRACE (p_module => l_log_module
4359 , p_msg => 'END ' || l_log_module
4360 , p_level => c_level_procedure
4361 );
4362 END IF;
4363
4364 ROLLBACK TO SAVEPOINT_BAL;
4365 if p_entity_id is null then
4366 DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
4367 else
4368 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
4369 end if;
4370
4371 RETURN TRUE; -- Bug 13614923
4372 WHEN xla_exceptions_pkg.application_exception
4373 THEN
4374 ROLLBACK TO SAVEPOINT_BAL;
4375 if p_entity_id is null then
4376 DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
4377 else
4378 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
4379 end if;
4380
4381 RETURN TRUE; -- Bug 13614923
4382 WHEN OTHERS
4383 THEN
4384 ROLLBACK TO SAVEPOINT_BAL;
4385 if p_entity_id is null then
4386 DELETE FROM xla_bal_concurrency_control where request_id=g_req_id;
4387 else
4388 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_entity_id;
4389 end if;
4390
4391 xla_exceptions_pkg.raise_message
4392 (p_location => 'xla_balances_calc_pkg.massive_update');
4393 RETURN TRUE; -- Bug 13614923
4394 END massive_update;
4395
4396 /*===============================================+
4397 | |
4398 | public Function |
4399 +------------------------------------------------+
4400 | Description: |
4401 | |
4402 +===============================================*/
4403
4404 FUNCTION single_update
4405 (
4406 p_application_id IN INTEGER
4407 ,p_ae_header_id IN INTEGER
4408 ,p_ae_line_num IN INTEGER
4409 ,p_update_mode IN VARCHAR2
4410 ) RETURN BOOLEAN
4411 IS
4412 l_return_value BOOLEAN ;
4413 l_log_module VARCHAR2 (2000);
4414 l_execution_mode VARCHAR2(1) := 'O';
4415 l_ledger_count NUMBER;
4416 l_ledger_nul_count NUMBER;
4417
4418 CURSOR lock_bal_control
4419 IS
4420 SELECT application_id
4421 ,ledger_id
4422 FROM xla_bal_concurrency_control
4423 WHERE application_id IN ( SELECT * FROM TABLE(g_application_array))
4424 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array))
4425 FOR UPDATE NOWAIT;
4426 BEGIN
4427 IF g_log_enabled THEN
4428 l_log_module := C_DEFAULT_MODULE||'.single_update';
4429 END IF;
4430 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4431 trace
4432 (p_module => l_log_module
4433 ,p_msg => 'BEGIN ' || l_log_module
4434 ,p_level => C_LEVEL_PROCEDURE);
4435 END IF;
4436
4437 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4438 trace
4439 (p_module => l_log_module
4440 ,p_msg => 'p_application_id ' || p_application_id
4441 ,p_level => C_LEVEL_PROCEDURE);
4442 END IF;
4443
4444 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4445 trace
4446 (p_module => l_log_module
4447 ,p_msg => 'p_update_mode ' || p_update_mode
4448 ,p_level => C_LEVEL_PROCEDURE);
4449 END IF;
4450
4451 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4452 trace
4453 (p_module => l_log_module
4457
4454 ,p_msg => 'p_ae_header_id ' || p_ae_header_id
4455 ,p_level => C_LEVEL_PROCEDURE);
4456 END IF;
4458 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4459 trace
4460 (p_module => l_log_module
4461 ,p_msg => 'p_ae_line_num ' || p_ae_line_num
4462 ,p_level => C_LEVEL_PROCEDURE);
4463 END IF;
4464 --ESTABLISH SAVEPOINT
4465 SAVEPOINT SAVEPOINT_BAL;
4466 -- Check if balances upgrade script has been run.
4467 SELECT count(1)
4468 INTO l_ledger_count
4469 FROM xla_ledger_options
4470 WHERE effective_period_num is not null;
4471
4472
4473 IF l_ledger_count = 0
4474 THEN
4475 fnd_file.put_line(fnd_file.LOG
4476 ,'Balances upgrade script xlabalupg.sql has not been run. Run xlabalupg.sql to use Update Subledger Accounting Balances program'
4477 );
4478 RETURN TRUE;
4479 ELSE
4480 --parameter validation
4481 IF p_application_id IS NULL
4482 THEN
4483 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4484 trace
4485 (p_module => l_log_module
4486 ,p_msg => 'EXCEPTION:' ||'p_application_id cannot be NULL'
4487 ,p_level => C_LEVEL_EXCEPTION
4488 );
4489 END IF;
4490 xla_exceptions_pkg.raise_message
4491 (p_appli_s_name => 'XLA'
4492 ,p_msg_name => 'XLA_COMMON_ERROR'
4493 ,p_token_1 => 'LOCATION'
4494 ,p_value_1 => 'xla_balances_calc_pkg.pre_accounting'
4495 ,p_token_2 => 'ERROR'
4496 ,p_value_2 => 'EXCEPTION:' ||
4497 'p_application_id cannot be NULL');
4498 END IF;
4499
4500 IF p_ae_header_id IS NULL
4501 THEN
4502 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4503 trace (p_module => l_log_module
4504 ,p_msg => 'EXCEPTION:' ||'p_ae_header_id cannot be NULL'
4505 ,p_level => C_LEVEL_EXCEPTION
4506 );
4507 END IF;
4508 xla_exceptions_pkg.raise_message
4509 (p_appli_s_name => 'XLA'
4510 ,p_msg_name => 'XLA_COMMON_ERROR'
4511 ,p_token_1 => 'LOCATION'
4512 ,p_value_1 => 'xla_balances_calc_pkg.pre_accounting'
4513 ,p_token_2 => 'ERROR'
4514 ,p_value_2 => 'EXCEPTION:' || 'p_ae_header_id cannot be NULL');
4515 END IF;
4516
4517 IF p_update_mode IS NULL
4518 THEN
4519 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4520 trace
4521 (p_module => l_log_module
4522 ,p_msg => 'EXCEPTION:' ||'p_update_mode cannot be NULL'
4523 ,p_level => C_LEVEL_EXCEPTION
4524 );
4525 END IF;
4526 xla_exceptions_pkg.raise_message
4527 (p_appli_s_name => 'XLA'
4528 ,p_msg_name => 'XLA_COMMON_ERROR'
4529 ,p_token_1 => 'LOCATION'
4530 ,p_value_1 => 'xla_balances_calc_pkg.pre_accounting'
4531 ,p_token_2 => 'ERROR'
4532 ,p_value_2 => 'EXCEPTION:' ||'p_update_mode cannot be NULL');
4533 ELSIF p_update_mode NOT IN ('A', 'D', 'F')
4534 THEN
4535 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4536 trace
4537 (p_module => l_log_module
4538 ,p_msg => 'EXCEPTION:' || 'Unsupported value for p_update_mode: ' || p_update_mode
4539 ,p_level => C_LEVEL_EXCEPTION
4540 );
4541 END IF;
4542 xla_exceptions_pkg.raise_message
4543 (p_appli_s_name => 'XLA'
4544 ,p_msg_name => 'XLA_COMMON_ERROR'
4545 ,p_token_1 => 'LOCATION'
4546 ,p_value_1 => 'xla_balances_calc_pkg.pre_accounting'
4547 ,p_token_2 => 'ERROR'
4548 ,p_value_2 => 'EXCEPTION:' ||'Unsupported value for p_update_mode: ' || p_update_mode);
4549 END IF;
4550 -- END parameter validation
4551
4552 IF NOT lock_bal_concurrency_control ( p_application_id => p_application_id
4553 , p_ledger_id => NULL
4554 , p_entity_id => NULL
4555 , p_event_id => NULL
4556 , p_ae_header_id => p_ae_header_id
4557 , p_ae_line_num => p_ae_line_num
4558 , p_request_id => g_req_id
4559 , p_accounting_batch_id => NULL
4560 , p_execution_mode => l_execution_mode
4561 , p_concurrency_class => 'BALANCES_CALCULATION'
4562 )
4563 THEN
4564 xla_exceptions_pkg.raise_message
4565 (p_appli_s_name => 'XLA'
4566 , p_msg_name => 'XLA_COMMON_ERROR'
4567 , p_token_1 => 'LOCATION'
4571 || 'XLA_BAL_CONCURRENCY_CONTROL COULD NOT BE LOCKED. RESOURCE BUSY'
4568 , p_value_1 => 'xla_balances_calc_pkg.MASSIVE_UPDATE'
4569 , p_token_2 => 'ERROR'
4570 , p_value_2 => 'EXCEPTION:'
4572 );
4573 END IF;
4574 OPEN lock_bal_control;
4575
4576 CLOSE lock_bal_control;
4577
4578 fnd_file.put_line (fnd_file.LOG , 'Successfully Locked');
4579
4580 l_return_value := calculate_balances ( p_application_id => p_application_id
4581 , p_ledger_id => NULL
4582 , p_entity_id => NULL
4583 , p_event_id => NULL
4584 , p_ae_header_id => p_ae_header_id
4585 , p_ae_line_num => p_ae_line_num
4586 , p_request_id => g_req_id
4587 , p_accounting_batch_id => NULL
4588 , p_update_mode => p_update_mode
4589 , p_execution_mode => l_execution_mode
4590 );
4591
4592 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
4593
4594 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4595 trace (p_module => l_log_module
4596 ,p_msg => 'END ' || l_log_module
4597 ,p_level => C_LEVEL_PROCEDURE);
4598 END IF;
4599
4600 -- Begin Bug 14115273
4601 if l_return_value then
4602 RETURN TRUE;
4603 else
4604 ROLLBACK TO SAVEPOINT_BAL;
4605 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
4606 RETURN TRUE;
4607 end if;
4608 -- End Bug 14115273
4609
4610 END IF;
4611 EXCEPTION
4612 WHEN le_resource_busy or le_wait_expire or le_deadlock_detected -- Bug 13614923
4613 THEN
4614 IF (c_level_error >= g_log_level)
4615 THEN
4616 TRACE (p_module => l_log_module
4617 , p_msg => 'Cannot lock XLA_BAL_CONCURRENCY_CONTROL'
4618 , p_level => c_level_error
4619 );
4620 END IF;
4621
4622 IF (c_level_procedure >= g_log_level)
4623 THEN
4624 TRACE (p_module => l_log_module
4625 , p_msg => 'END ' || l_log_module
4626 , p_level => c_level_procedure
4627 );
4628 END IF;
4629
4630 for i in ( SELECT distinct application_id
4631 ,ledger_id
4632 FROM xla_bal_concurrency_control
4633 WHERE application_id = p_application_id
4634 AND ledger_id IN ( SELECT * FROM TABLE(g_ledger_array)))
4635 loop
4636 fnd_file.put_line
4637 (fnd_file.LOG
4638 , 'There is another request(s) running for the ledger_id : '
4639 || i.ledger_id
4640 || ' application_id : '
4641 || i.application_id);
4642 end loop;
4643
4644 fnd_file.put_line
4645 (fnd_file.LOG ,
4646 'Pls. submit Subledger Accounting Balances Update Concurrent Program once the running request is completed');
4647
4648 ROLLBACK TO SAVEPOINT_BAL;
4649 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
4650 RETURN TRUE;
4651 WHEN xla_exceptions_pkg.application_exception
4652 THEN
4653 ROLLBACK TO SAVEPOINT_BAL;
4654 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
4655 RETURN TRUE;
4656 WHEN OTHERS
4657 THEN
4658 ROLLBACK TO SAVEPOINT_BAL;
4659 DELETE FROM xla_bal_concurrency_control where request_id=-1*p_ae_header_id;
4660 xla_exceptions_pkg.raise_message
4661 (p_location => 'xla_balances_calc_pkg.single_update');
4662 RETURN TRUE;
4663 END single_update;
4664
4665 FUNCTION is_control_account
4666 ( p_code_combination_id IN INTEGER
4667 ,p_natural_account IN VARCHAR2
4668 ,p_ledger_id IN INTEGER
4669 ,p_application_id IN INTEGER
4670 ) RETURN INTEGER
4671 IS
4672 l_qualifier_value VARCHAR2(25);
4673 l_je_source_name VARCHAR2(30);
4674 l_chart_of_accounts_id INTEGER;
4675 l_cid_count INTEGER;
4676
4677 l_log_module VARCHAR2 (2000);
4678
4679 BEGIN
4680 IF g_log_enabled THEN
4681 l_log_module := C_DEFAULT_MODULE||'.is_control_account';
4682 END IF;
4683
4684 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4685 trace
4686 (p_module => l_log_module
4687 ,p_msg => 'BEGIN ' || l_log_module
4688 ,p_level => C_LEVEL_PROCEDURE);
4689 END IF;
4690
4691 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4692 trace
4693 ( p_module => l_log_module
4694 ,p_msg => 'p_code_combination_id :' || p_code_combination_id
4695 ,p_level => C_LEVEL_STATEMENT
4696 );
4697 trace
4698 ( p_module => l_log_module
4699 ,p_msg => 'p_natural_account :' || p_natural_account
4700 ,p_level => C_LEVEL_STATEMENT
4701 );
4702 trace
4703 ( p_module => l_log_module
4704 ,p_msg => 'p_ledger_id :' || p_ledger_id
4705 ,p_level => C_LEVEL_STATEMENT
4706 );
4707 trace
4708 ( p_module => l_log_module
4709 ,p_msg => 'p_application_id :' || p_application_id
4710 ,p_level => C_LEVEL_STATEMENT
4711 );
4712 END IF;
4713
4717 THEN
4714 IF p_code_combination_id IS NOT NULL
4715 AND p_natural_account IS NULL
4716 -- AND p_ledger_id IS NULL
4718 BEGIN
4719
4720 SELECT gcc.reference3
4721 INTO l_qualifier_value
4722 FROM gl_code_combinations gcc
4723 WHERE gcc.code_combination_id = p_code_combination_id;
4724 EXCEPTION
4725 WHEN NO_DATA_FOUND THEN
4726 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4727 trace
4728 ( p_module => l_log_module
4729 ,p_msg => 'EXCEPTION: ' ||
4730 'Code combination id '||p_code_combination_id ||
4731 ' not found. in the table gl_code_combinations'
4732 ,p_level => C_LEVEL_EXCEPTION
4733 );
4734 END IF;
4735 RETURN C_NOT_CONTROL_ACCOUNT;
4736 WHEN OTHERS THEN
4737 xla_exceptions_pkg.raise_message
4738 (p_location => 'xla_balances_calc_pkg.is_control_account');
4739 END;
4740
4741 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4742 trace
4743 ( p_module => l_log_module
4744 ,p_msg => 'Qualifier value: ' || l_qualifier_value
4745 ,p_level => C_LEVEL_STATEMENT
4746 );
4747 END IF;
4748
4749 IF NVL(l_qualifier_value, 'N') = 'N'
4750 THEN
4751 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4752 trace
4753 ( p_module => l_log_module
4754 ,p_msg => 'END ' || l_log_module
4755 ,p_level => C_LEVEL_PROCEDURE);
4756 END IF;
4757 RETURN C_NOT_CONTROL_ACCOUNT;
4758 END IF;
4759
4760 IF p_application_id IS NOT NULL
4761 THEN
4762 BEGIN
4763 SELECT xsl.control_account_type_code
4764 INTO l_je_source_name
4765 FROM xla_subledgers xsl
4766 WHERE xsl.application_id = p_application_id;
4767 EXCEPTION
4768 WHEN NO_DATA_FOUND THEN
4769
4770 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
4771 trace
4772 ( p_module => l_log_module
4773 ,p_msg => 'EXCEPTION: ' || 'Application id '|| p_application_id || ' not found.' || ' in the table xla_subledgers'
4774 ,p_level => C_LEVEL_EXCEPTION
4775 );
4776 END IF;
4777 RETURN C_NOT_CONTROL_ACCOUNT;
4778 WHEN OTHERS THEN
4779 xla_exceptions_pkg.raise_message
4780 (p_location => 'xla_balances_calc_pkg.is_control_account');
4781 RETURN C_NOT_CONTROL_ACCOUNT;
4782 END;
4783
4784
4785 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
4786 trace
4787 ( p_module => l_log_module
4788 ,p_msg => 'Source name: ' || l_je_source_name
4789 ,p_level => C_LEVEL_STATEMENT
4790 );
4791 END IF;
4792
4793 IF (l_qualifier_value = l_je_source_name
4794 OR (l_qualifier_value = 'Y' and nvl(l_je_source_name, 'N') <> 'N')
4795 or l_je_source_name = 'Y' )
4796 THEN
4797 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4798 trace
4799 ( p_module => l_log_module
4800 ,p_msg => 'END ' || l_log_module
4801 ,p_level => C_LEVEL_PROCEDURE);
4802 END IF;
4803 RETURN C_IS_CONTROL_ACCOUNT;
4804 ELSE
4805 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4806 trace
4807 ( p_module => l_log_module
4808 ,p_msg => 'END ' || l_log_module
4809 ,p_level => C_LEVEL_PROCEDURE);
4810 END IF;
4811 RETURN C_IS_CONTROL_ACCOUNT_OTHER_APP;
4812 END IF;
4813 ELSE --p_application_id IS NULL
4814 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4815 trace
4816 ( p_module => l_log_module
4817 ,p_msg => 'END ' || l_log_module
4818 ,p_level => C_LEVEL_PROCEDURE);
4819 END IF;
4820 RETURN C_IS_CONTROL_ACCOUNT;
4821 END IF;
4822
4823 ELSIF p_natural_account IS NOT NULL
4824 AND p_code_combination_id IS NULL
4825 THEN
4826 xla_exceptions_pkg.raise_message
4827 ('XLA'
4828 ,'XLA_COMMON_ERROR'
4829 ,'ERROR'
4830 ,'p_natural_account NOT NULL: functionality not implemented'
4831 ,'LOCATION'
4832 ,'xla_balances_calc_pkg.is_control_account');
4833 END IF;
4834
4835 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
4836 trace
4837 ( p_module => l_log_module
4838 ,p_msg => 'END ' || l_log_module
4839 ,p_level => C_LEVEL_PROCEDURE);
4840 END IF;
4841
4842 EXCEPTION
4843 WHEN NO_DATA_FOUND THEN
4844 RETURN C_NOT_CONTROL_ACCOUNT;
4845 WHEN xla_exceptions_pkg.application_exception THEN
4846 RAISE;
4847 WHEN OTHERS THEN
4848 xla_exceptions_pkg.raise_message
4849 (p_location => 'xla_balances_calc_pkg.is_control_account');
4850 END is_control_account;
4851
4852 /*===============================================+
4853 | |
4854 | public procedure |
4855 +------------------------------------------------+
4856 | Description: to identify records for which |
4860
4857 | balances are not processed |
4858 | |
4859 +===============================================*/
4861 PROCEDURE sr_bal_unprocessed(p_application_id IN INTEGER
4862 ,p_ledger_id IN INTEGER
4863 ,p_code_combination_id IN INTEGER
4864 ,p_anacri_code IN VARCHAR2
4865 ,p_anacri_type_code IN VARCHAR2
4866 ,p_amb_context_code IN VARCHAR2
4867 ,p_ac1 IN VARCHAR2
4868 ,p_ac2 IN VARCHAR2
4869 ,p_ac3 IN VARCHAR2
4870 ,p_ac4 IN VARCHAR2
4871 ,p_ac5 IN VARCHAR2
4872 ,p_eff_period_num_from IN INTEGER
4873 ,p_eff_period_num_to IN INTEGER
4874 ,p_unprocessed_flag OUT NOCOPY VARCHAR2
4875 )
4876 IS
4877 l_log_module VARCHAR2 (2000);
4878 l_count number;
4879 l_query varchar2(3000);
4880 l_bind_array t_array_varchar;
4881 l_bind_count INTEGER :=1 ;
4882 BEGIN
4883 IF g_log_enabled THEN
4884 l_log_module := C_DEFAULT_MODULE||'.sr_bal_unprocessed';
4885 END IF;
4886
4887 IF (c_level_procedure >= g_log_level)
4888 THEN
4889 trace
4890 (p_msg => 'p_application_id: '||p_application_id||' p_ledger_id : '||p_ledger_id||' p_code_combination_id '||p_code_combination_id
4891 ,p_level => C_LEVEL_STATEMENT
4892 ,p_module => l_log_module);
4893 trace
4894 (p_msg => 'p_anacri_code: '||p_anacri_code||' p_anacri_type_code '||p_anacri_type_code||' p_amb_context_code '||p_amb_context_code
4895 ,p_level => C_LEVEL_STATEMENT
4896 ,p_module => l_log_module);
4897 trace
4898 (p_msg => 'p_ac1 : '||p_ac1||' p_ac2: '||p_ac2||' p_ac3: '||p_ac3||' p_ac4: '||p_ac4||' p_ac5: '||p_ac5
4899 ,p_level => C_LEVEL_STATEMENT
4900 ,p_module => l_log_module);
4901
4902 trace
4903 (p_msg => 'p_eff_period_num_from : '||p_eff_period_num_from||' p_eff_period_num_to: '||p_eff_period_num_to||' p_unprocessed_flag: '||p_unprocessed_flag
4904 ,p_level => C_LEVEL_STATEMENT
4905 ,p_module => l_log_module);
4906
4907 END IF;
4908 l_query := 'SELECT count(1)
4909 FROM dual
4910 where exists(select 1
4911 from xla_ae_lines xal
4912 ,xla_ae_line_acs xac
4913 ,gl_period_statuses gps1
4914 ,gl_period_statuses gps2
4915 ,xla_ae_headers xah
4916 where xah.application_id = :'||to_number(l_bind_count)||'
4917 and xah.ledger_id = :'||to_number(l_bind_count+1)||'
4918 and xah.accounting_date between gps1.start_date and gps2.end_date
4919 and xal.application_id = xah.application_id
4920 and xal.ae_header_id = xah.ae_header_id
4921 and xal.analytical_balance_flag ='|| '''P'''||'
4922 and xac.ae_header_id = xal.ae_header_id
4923 and xac.ae_line_num = xal.ae_line_num
4924 and xac.analytical_criterion_code like :'||to_number(l_bind_count+2)||'
4925 and xac.analytical_criterion_type_code like :'||to_number(l_bind_count+3)||'
4926 and xac.amb_context_code = :'||to_number(l_bind_count+4)||'
4927 and gps1.application_id = 101
4928 and gps1.ledger_id= :'||to_number(l_bind_count+5)||'
4929 and gps1.effective_period_num = :'||to_number(l_bind_count+6)||'
4930 and gps2.application_id = gps1.application_id
4931 and gps2.ledger_id = gps1.ledger_id
4932 and gps2.effective_period_num = :'||to_number(l_bind_count+7);
4933
4934 l_bind_array(l_bind_count) := to_char(p_application_id);
4935 l_bind_count := l_bind_count+1;
4936
4937 l_bind_array(l_bind_count) := to_char(p_ledger_id);
4938 l_bind_count := l_bind_count+1;
4939
4940 l_bind_array(l_bind_count) := to_char(p_anacri_code);
4941 l_bind_count := l_bind_count+1;
4942
4943 l_bind_array(l_bind_count) := to_char(p_anacri_type_code);
4944 l_bind_count := l_bind_count+1;
4945
4946 l_bind_array(l_bind_count) := to_char(p_amb_context_code);
4947 l_bind_count := l_bind_count+1;
4948
4949 l_bind_array(l_bind_count) := to_char(p_ledger_id);
4950 l_bind_count := l_bind_count+1;
4951
4952 l_bind_array(l_bind_count) := to_char(p_eff_period_num_from);
4953 l_bind_count := l_bind_count+1;
4954
4955 l_bind_array(l_bind_count) := to_char(p_eff_period_num_to);
4956 l_bind_count := l_bind_count+1;
4957
4958 IF p_code_combination_id IS NOT NULL
4959 THEN
4960 l_query := l_query || '
4961 and xal.code_combination_id = :'||l_bind_count;
4962
4963 l_bind_array(l_bind_count) := to_char(p_code_combination_id);
4964 l_bind_count := l_bind_count+1;
4965 END IF;
4966
4967 IF p_ac1 IS NOT NULL
4968 THEN
4969 l_query := l_query || '
4970 and nvl(xac.ac1,'||''' '''||') like :'||l_bind_count;
4971
4972 l_bind_array(l_bind_count) := to_char(p_ac1);
4973 l_bind_count := l_bind_count+1;
4974 END IF;
4975
4976 IF p_ac2 IS NOT NULL
4977 THEN
4978 l_query := l_query || '
4979 and nvl(xac.ac2,'||''' '''||') like :'||l_bind_count;
4980
4981 l_bind_array(l_bind_count) := to_char(p_ac2);
4982 l_bind_count := l_bind_count+1;
4983 END IF;
4984
4985 IF p_ac3 IS NOT NULL
4986 THEN
4987 l_query := l_query || '
4988 and nvl(xac.ac3,'||''' '''||') like :'||l_bind_count;
4989
4990 l_bind_array(l_bind_count) := to_char(p_ac3);
4991 l_bind_count := l_bind_count+1;
4992 END IF;
4993
4994 IF p_ac4 IS NOT NULL
4995 THEN
4996 l_query := l_query || '
4997 and nvl(xac.ac4,'||''' '''||') like :'||l_bind_count;
4998
4999 l_bind_array(l_bind_count) := to_char(p_ac4);
5000 l_bind_count := l_bind_count+1;
5001 END IF;
5002
5003 IF p_ac5 IS NOT NULL
5004 THEN
5005 l_query := l_query || '
5006 and nvl(xac.ac5,'||''' '''||') like :'||l_bind_count;
5007
5008 l_bind_array(l_bind_count) := to_char(p_ac5);
5009 l_bind_count := l_bind_count+1;
5010 END IF;
5011
5012 l_query := l_query||')';
5013
5014 IF (c_level_procedure >= g_log_level)
5015 THEN
5016 trace
5017 (p_msg => 'AC l_query_1:'||substr(l_query, 1, 1000)
5018 ,p_level => C_LEVEL_STATEMENT
5019 ,p_module => l_log_module);
5020 trace
5021 (p_msg => 'AC l_query_2:'||substr(l_query, 1001, 1000)
5022 ,p_level => C_LEVEL_STATEMENT
5023 ,p_module => l_log_module);
5024 trace
5025 (p_msg => 'AC l_query_3:'||substr(l_query, 2001, 1000)
5026 ,p_level => C_LEVEL_STATEMENT
5027 ,p_module => l_log_module);
5028
5029
5030
5031 END IF;
5032
5033 l_bind_count := l_bind_count-1;
5034
5035 FOR i in 1..l_bind_count
5036 LOOP
5037
5038 IF (c_level_procedure >= g_log_level)
5039 THEN
5040 trace
5041 (p_msg => 'i'||l_bind_array(i)
5042 ,p_level => C_LEVEL_STATEMENT
5043 ,p_module => l_log_module);
5044 END IF;
5045 END LOOP;
5046
5047 IF l_bind_count = 8
5048 THEN
5049 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5050 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8);
5051 ELSIF l_bind_count = 9
5052 THEN
5053 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5054 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9);
5055 ELSIF l_bind_count = 10
5056 THEN
5057 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5058 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9), l_bind_array(10);
5059 ELSIF l_bind_count = 11
5060 THEN
5061 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5062 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9), l_bind_array(10)
5063 , l_bind_array(11);
5064 ELSIF l_bind_count = 12
5065 THEN
5066 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5067 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9), l_bind_array(10)
5068 , l_bind_array(11), l_bind_array(12);
5069 ELSIF l_bind_count = 13
5070 THEN
5071 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5072 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9), l_bind_array(10)
5073 , l_bind_array(11), l_bind_array(12), l_bind_array(13);
5074 ELSIF l_bind_count = 14
5075 THEN
5076 EXECUTE IMMEDIATE l_query INTO l_count USING l_bind_array(1) , l_bind_array(2),l_bind_array(3) , l_bind_array(4)
5077 ,l_bind_array(5) , l_bind_array(6),l_bind_array(7) , l_bind_array(8), l_bind_array(9), l_bind_array(10)
5078 , l_bind_array(11), l_bind_array(12), l_bind_array(13), l_bind_array(14);
5079 END IF;
5080
5081 IF l_count >0
5082 THEN
5083 p_unprocessed_flag := 'Y';
5084 ELSE
5085 p_unprocessed_flag := 'N';
5086 END IF;
5087
5088 IF (c_level_procedure >= g_log_level)
5089 THEN
5090 trace
5091 (p_msg => 'p_unprocessed_flag'||p_unprocessed_flag
5092 ,p_level => C_LEVEL_STATEMENT
5093 ,p_module => l_log_module);
5094 trace
5095 (p_msg => 'END sr_bal_unprocessed'
5096 ,p_level => C_LEVEL_STATEMENT
5097 ,p_module => l_log_module);
5098 END IF;
5099
5100 EXCEPTION
5101 WHEN OTHERS
5102 THEN
5103 RAISE;
5104 xla_exceptions_pkg.raise_message
5105 (p_location => 'xla_balances_calc_pkg.sr_bal_unprocessed');
5106 END sr_bal_unprocessed;
5107
5108
5109
5110 BEGIN
5111 g_log_level := fnd_log.g_current_runtime_level;
5112 g_log_enabled :=
5113 fnd_log.TEST (log_level => g_log_level
5114 , module => c_default_module);
5115
5116 IF NOT g_log_enabled
5117 THEN
5118 g_log_level := c_level_log_disabled;
5119 END IF;
5120
5121 g_user_id := xla_environment_pkg.g_usr_id;
5122 g_login_id := xla_environment_pkg.g_login_id;
5123 g_date := SYSDATE;
5124 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
5125 g_prog_id := xla_environment_pkg.g_prog_id;
5126 g_req_id := NVL (xla_environment_pkg.g_req_id, -1);
5127 g_cached_single_period := FALSE;
5128 END xla_balances_calc_pkg;