[Home] [Help]
PACKAGE BODY: APPS.XLA_MULTIPERIOD_ACCOUNTING_PKG
Source
1 PACKAGE BODY xla_multiperiod_accounting_pkg AS
2 -- $Header: xlampaac.pkb 120.10.12010000.3 2008/08/18 06:05:21 nmsubram ship $
3 /*===========================================================================+
4 | Copyright (c) 2005 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_multiperiod_accounting_pkg |
10 | |
11 | DESCRIPTION |
12 | This package contains the APIs related to the Complete Multiperiod |
13 | Accounting Program. |
14 | |
15 | HISTORY |
16 | 05/23/2005 eklau Created |
17 | 02/15/2006 awan 5039413 performance fix |
18 | 03/29/2006 awan 5115223 cannot complete MPA |
19 +===========================================================================*/
20
21 -------------------------------------------------------------------------------
22 -- *********** Local Exceptions ************
23 -------------------------------------------------------------------------------
24
25 normal_termination EXCEPTION;
26 resource_busy EXCEPTION;
27 PRAGMA EXCEPTION_INIT(resource_busy, -54);
28
29 --=============================================================================
30 -- *********** Local Trace Routine **********
31 --=============================================================================
32
33 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
34 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
35 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
36 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
37 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
38 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
39
40 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
41 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_multiperiod_accounting_pkg';
42
43 g_log_level NUMBER;
44 g_log_enabled BOOLEAN;
45
46
47 -- Parameters
48
49 g_application_id PLS_INTEGER;
50 g_ledger_id PLS_INTEGER;
51 g_process_category_code VARCHAR2(30);
52 g_end_date DATE;
53 g_errors_only_flag VARCHAR2(1);
54 g_transfer_to_gl_flag VARCHAR2(1);
55 g_post_in_gl_flag VARCHAR2(1);
56 g_gl_batch_name VARCHAR2(50);
57 g_valuation_method_code VARCHAR2(30);
58 g_security_id_int_1 PLS_INTEGER;
59 g_security_id_int_2 PLS_INTEGER;
60 g_security_id_int_3 PLS_INTEGER;
61 g_security_id_char_1 VARCHAR2(30);
62 g_security_id_char_2 VARCHAR2(30);
63 g_security_id_char_3 VARCHAR2(30);
64
65 g_request_id NUMBER;
66 g_accounting_batch_id NUMBER;
67 g_total_error_count NUMBER;
68 g_total_error_count_main NUMBER;
69 g_security_condition VARCHAR2(2000);
70 g_process_category_condition VARCHAR2(2000);
71
72 g_array_ae_header_id xla_je_validation_pkg.t_array_int; -- 5115223
73
74 --========================================================
75 -- Forward declarion of private procedures and functions
76 --========================================================
77
78 PROCEDURE Initialize
79 (p_application_id IN NUMBER
80 ,p_ledger_id IN NUMBER
81 ,p_process_category_code IN VARCHAR2
82 ,p_end_date IN DATE
83 ,p_errors_only_flag IN VARCHAR2
84 ,p_transfer_to_gl_flag IN VARCHAR2
85 ,p_post_in_gl_flag IN VARCHAR2
86 ,p_gl_batch_name IN VARCHAR2
87 ,p_valuation_method_code IN VARCHAR2
88 ,p_security_id_int_1 IN NUMBER
89 ,p_security_id_int_2 IN NUMBER
90 ,p_security_id_int_3 IN NUMBER
91 ,p_security_id_char_1 IN VARCHAR2
92 ,p_security_id_char_2 IN VARCHAR2
93 ,p_security_id_char_3 IN VARCHAR2);
94
95 PROCEDURE Populate_Journal_Entries;
96
97 PROCEDURE Update_Journal_Entries;
98
99 PROCEDURE Populate_Sequences;
100
101 PROCEDURE Transfer_To_GL;
102
103 --===========================================================================
104 -- Local trace routine.
105 --===========================================================================
106
107 PROCEDURE trace
108 (p_msg IN VARCHAR2
109 ,p_level IN NUMBER
110 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE) IS
111 BEGIN
112 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
113 fnd_log.message(p_level, p_module);
114 ELSIF p_level >= g_log_level THEN
115 fnd_log.string(p_level, p_module, p_msg);
116 END IF;
117
118 EXCEPTION
119 WHEN xla_exceptions_pkg.application_exception THEN
120 RAISE;
121 WHEN OTHERS THEN
122 xla_exceptions_pkg.raise_message
123 (p_location => 'xla_multiperiod_accounting_pkg.trace');
124 END trace;
125
126
127 --=============================================================================
128 -- ******* Print Log File **********
129 --=============================================================================
130 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
131 BEGIN
132
133 fnd_file.put_line(fnd_file.log,p_msg);
134
135 EXCEPTION
136 WHEN xla_exceptions_pkg.application_exception THEN
137 RAISE;
138 WHEN OTHERS THEN
139 xla_exceptions_pkg.raise_message
140 (p_location => 'xla_multiperiod_accounting_pkg.print_logfile');
141 END print_logfile;
142
143
144 --============================================================================
145 --
146 -- Public API which completes incomplete recognition journal entries and
147 -- accrual reversal journal entries.
148 --
149 --============================================================================
150
151 PROCEDURE Complete_Journal_Entries
152 (p_application_id IN NUMBER
153 ,p_ledger_id IN NUMBER
154 ,p_process_category_code IN VARCHAR2
155 ,p_end_date IN DATE
156 ,p_errors_only_flag IN VARCHAR2
157 ,p_transfer_to_gl_flag IN VARCHAR2
158 ,p_post_in_gl_flag IN VARCHAR2
159 ,p_gl_batch_name IN VARCHAR2
160 ,p_valuation_method_code IN VARCHAR2
161 ,p_security_id_int_1 IN NUMBER
162 ,p_security_id_int_2 IN NUMBER
163 ,p_security_id_int_3 IN NUMBER
164 ,p_security_id_char_1 IN VARCHAR2
165 ,p_security_id_char_2 IN VARCHAR2
166 ,p_security_id_char_3 IN VARCHAR2
167 ,p_accounting_batch_id OUT NOCOPY NUMBER
168 ,p_errbuf OUT NOCOPY VARCHAR2
169 ,p_retcode OUT NOCOPY NUMBER) IS
170
171 l_log_module VARCHAR2(240);
172 l_validation NUMBER := 0;
173 l_ret_flag_bal_reversal BOOLEAN := FALSE;
174
175 BEGIN
176
177 IF g_log_enabled THEN
178 l_log_module := C_DEFAULT_MODULE||'.Complete_Journal_Entries';
179 END IF;
180
181 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
182 trace
183 (p_msg => 'BEGIN procedure COMPLETE_JOURNAL_ENTRIES'
184 ,p_level => C_LEVEL_PROCEDURE
185 ,p_module => l_log_module);
186
187 trace
188 (p_msg => 'p_application_id = '||p_application_id
189 ,p_level => C_LEVEL_PROCEDURE
190 ,p_module => l_log_module);
191
192 trace
193 (p_msg => 'p_ledger_id = '||p_ledger_id
194 ,p_level => C_LEVEL_PROCEDURE
195 ,p_module => l_log_module);
196
197 trace
198 (p_msg => 'p_process_category_code = '||p_process_category_code
199 ,p_level => C_LEVEL_PROCEDURE
200 ,p_module => l_log_module);
201
202 trace
203 (p_msg => 'p_end_date = '||to_char(p_end_date,'DD-MON-YYYY')
204 ,p_level => C_LEVEL_PROCEDURE
205 ,p_module => l_log_module);
206
207 trace
208 (p_msg => 'p_errors_only_flag = '||p_errors_only_flag
209 ,p_level => C_LEVEL_PROCEDURE
210 ,p_module => l_log_module);
211
212 trace
213 (p_msg => 'p_transfer_to_gl_flag = '||p_transfer_to_gl_flag
214 ,p_level => C_LEVEL_PROCEDURE
215 ,p_module => l_log_module);
216
217 trace
218 (p_msg => 'p_post_in_gl_flag = '||p_post_in_gl_flag
219 ,p_level => C_LEVEL_PROCEDURE
220 ,p_module => l_log_module);
221
222 trace
223 (p_msg => 'p_gl_batch_name = '||p_gl_batch_name
224 ,p_level => C_LEVEL_PROCEDURE
225 ,p_module => l_log_module);
226
227 trace
228 (p_msg => 'p_valuation_method_code = '||p_valuation_method_code
229 ,p_level => C_LEVEL_PROCEDURE
230 ,p_module => l_log_module);
231
232 trace
233 (p_msg => 'p_security_id_int_1 = '||p_security_id_int_1
234 ,p_level => C_LEVEL_PROCEDURE
235 ,p_module => l_log_module);
236
237 trace
238 (p_msg => 'p_security_id_int_1 = '||p_security_id_int_1
239 ,p_level => C_LEVEL_PROCEDURE
240 ,p_module => l_log_module);
241
242 trace
243 (p_msg => 'p_security_id_int_3 = '||p_security_id_int_3
244 ,p_level => C_LEVEL_PROCEDURE
245 ,p_module => l_log_module);
246
247 trace
248 (p_msg => 'p_security_id_char_1 = '||p_security_id_char_1
249 ,p_level => C_LEVEL_PROCEDURE
250 ,p_module => l_log_module);
251
252 trace
253 (p_msg => 'p_security_id_char_2 = '||p_security_id_char_2
254 ,p_level => C_LEVEL_PROCEDURE
255 ,p_module => l_log_module);
256
257 trace
258 (p_msg => 'p_security_id_char_3 = '||p_security_id_char_3
259 ,p_level => C_LEVEL_PROCEDURE
260 ,p_module => l_log_module);
261
262 END IF;
263
264 Initialize
265 (p_application_id => p_application_id
266 ,p_ledger_id => p_ledger_id
267 ,p_process_category_code => p_process_category_code
268 ,p_end_date => p_end_date
269 ,p_errors_only_flag => p_errors_only_flag
270 ,p_transfer_to_gl_flag => p_transfer_to_gl_flag
271 ,p_post_in_gl_flag => p_post_in_gl_flag
272 ,p_gl_batch_name => p_gl_batch_name
273 ,p_valuation_method_code => p_valuation_method_code
274 ,p_security_id_int_1 => p_security_id_int_1
275 ,p_security_id_int_2 => p_security_id_int_2
276 ,p_security_id_int_3 => p_security_id_int_3
277 ,p_security_id_char_1 => p_security_id_char_1
278 ,p_security_id_char_2 => p_security_id_char_2
279 ,p_security_id_char_3 => p_security_id_char_3);
280
281 -- Populate xla_ae_headers_gt table for processing.
282
283 Populate_Journal_Entries;
284
285 -- Invoke API to validate and balance journal entries by balancing segments.
286
287 IF (C_LEVEL_EVENT >= g_log_level) THEN
288 trace
289 (p_msg => 'Calling the function XLA_JE_VALIDATION_PKG.BALANCE_AMOUNTS'
290 ,p_level => C_LEVEL_EVENT
291 ,p_module => l_log_module);
292 END IF;
293
294 xla_accounting_cache_pkg.load_application_ledgers
295 (p_application_id => g_application_id
296 ,p_event_ledger_id => g_ledger_id);
297
298 l_validation := XLA_JE_VALIDATION_PKG.Balance_Amounts
299 (p_application_id => g_application_id
300 ,p_ledger_id => g_ledger_id
301 ,p_mode => 'COMPLETE_MPA'
302 ,p_end_date => g_end_date
303 ,p_budgetary_control_mode => 'NONE'
304 ,p_accounting_mode => 'F');
305
306 If (l_validation = 1) then
307
308 -- Error encountered in validation and balancing program.
309
310 IF (C_LEVEL_EVENT >= g_log_level) THEN
311 trace
312 (p_msg => 'Error encountered in the XLA_JE_VALIDATION_PKG.Balance_Amounts function.'
313 ,p_level => C_LEVEL_EVENT
314 ,p_module => l_log_module);
315 END IF;
316 Else
317 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
318 trace
319 (p_msg => 'Routine XLA_JE_VALIDATION_PKG.Balance_Amounts executed.'
320 ,p_level => C_LEVEL_STATEMENT
321 ,p_module => l_log_module);
322 END IF;
323 End If;
324
325 -- Invoke API to balance control account balances and analytical criterion balances.
326
327 IF (C_LEVEL_EVENT >= g_log_level) THEN
328 trace
329 (p_msg => 'Calling the function XLA_BALANCES_PKG.MASSIVE_UPDATE'
330 ,p_level => C_LEVEL_EVENT
331 ,p_module => l_log_module);
332 END IF;
333
334 l_ret_flag_bal_reversal :=
335 XLA_BALANCES_PKG.Massive_Update
336 (p_application_id => g_application_id
337 ,p_ledger_id => NULL
338 ,p_entity_id => NULL
339 ,p_event_id => NULL
340 ,p_request_id => NULL
341 ,p_accounting_batch_id => g_accounting_batch_id
342 ,p_update_mode => 'A'
343 ,p_execution_mode => 'O');
344
345 IF NOT l_ret_flag_bal_reversal THEN
346
347 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
348 trace
349 (p_msg => 'Error encountered in the function XLA_BALANCES_PKG.Massive_Update '
350 ,p_level => C_LEVEL_STATEMENT
351 ,p_module => l_log_module);
352 END IF;
353
354 xla_accounting_err_pkg.build_message
355 (p_appli_s_name => 'XLA'
356 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
357 ,p_token_1 => 'APPLICATION_NAME'
358 ,p_value_1 => 'SLA'
359 ,p_entity_id => NULL
360 ,p_event_id => NULL);
361
362 print_logfile('Technical problem : Problem in the routine XLA_BALANCES_PKG.Massive_Update');
363
364 xla_exceptions_pkg.raise_message
365 (p_appli_s_name => 'XLA'
366 ,p_msg_name => 'XLA_COMMON_ERROR'
367 ,p_token_1 => 'LOCATION'
368 ,p_value_1 => 'xla_multiperiod_accounting_pkg.complete_journal_entries'
369 ,p_token_2 => 'ERROR'
370 ,p_value_2 => 'Technical problem : Problem in the routine XLA_BALANCES_PKG.Massive_Update');
371 ELSE
372
373 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
374 trace
375 (p_msg => 'Call to function XLA_BALANCES_PKG.Massive_Update completed.'
376 ,p_level => C_LEVEL_STATEMENT
377 ,p_module => l_log_module);
378 END IF;
379 print_logfile('- call to XLA_BALANCES_PKG.Massive_Update completed');
380 END IF;
381
382 -- Update journal entry status.
383
384 Update_journal_Entries;
385
386 -- Track number of errors encountered during XLA routines.
387
388 g_total_error_count_main := xla_accounting_err_pkg.g_error_count;
389
390 -- Populate document sequence.
391
392 Populate_Sequences;
393
394 -- Transfer to GL.
395
396 If (g_transfer_to_gl_flag = 'Y') then
397 Transfer_To_Gl;
398 End If;
399
400 ----------------------------------------------------------------------------
401 -- insert any errors that were build in this session (for them to appear
402 -- on the report).
403 ----------------------------------------------------------------------------
404 g_total_error_count := xla_accounting_err_pkg.g_error_count;
405
406 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
407 trace
408 (p_msg => 'g_total_error_count = '||g_total_error_count
409 ,p_level => C_LEVEL_STATEMENT
410 ,p_module => l_log_module);
411 END IF;
412
413 --
414 -- If errors were encountered during sequencing and/or transfer to GL
415 -- then rollback to allow reprocessing of JE headers. Otherwise, it
416 -- will be possible to have "completed" entries without the proper
417 -- sequencing data and/or properly transferred to GL.
418 --
419
420 If (g_total_error_count > g_total_error_count_main) then
421 rollback;
422 End If;
423
424 xla_accounting_err_pkg.insert_errors;
425 COMMIT;
426
427 ----------------------------------------------------------------------------
428 -- set return variables
429 ----------------------------------------------------------------------------
430
431 p_accounting_batch_id := g_accounting_batch_id;
432
433 IF g_total_error_count = 0 THEN
434 p_retcode := 0;
435 p_errbuf := 'Complete Multiperiod Accounting Program completed Normal';
436 ELSE
437 p_retcode := 1;
438 p_errbuf := 'Complete Multiperiod Accounting Program completed Normal with some entries in error';
439 END IF;
440
441 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
442 trace
443 (p_msg => 'END of procedure COMPLETE_JOURNAL_ENTRIES'
444 ,p_level => C_LEVEL_PROCEDURE
445 ,p_module => l_log_module);
446 END IF;
447
448
449 EXCEPTION
450 WHEN xla_exceptions_pkg.application_exception THEN
451
452 ----------------------------------------------------------------------------
453 -- set out variables
454 ----------------------------------------------------------------------------
455 p_accounting_batch_id := g_accounting_batch_id;
456 p_retcode := 2;
457 p_errbuf := xla_messages_pkg.get_message;
458
459 print_logfile(p_errbuf);
460
461 IF (C_LEVEL_ERROR >= g_log_level) THEN
462 trace
463 (p_msg => NULL
464 ,p_level => C_LEVEL_ERROR
465 ,p_module => l_log_module);
466 END IF;
467
468 ----------------------------------------------------------------------------
469 -- insert any errors that were build in this session (for them to appear
470 -- on the report).
471 ----------------------------------------------------------------------------
472 rollback;
473
474 xla_accounting_err_pkg.insert_errors;
475 COMMIT;
476
477 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
478 trace
479 (p_msg => 'p_retcode = '||p_retcode
480 ,p_level => C_LEVEL_PROCEDURE
481 ,p_module => l_log_module);
482 trace
483 (p_msg => 'p_errbuf = '||p_errbuf
484 ,p_level => C_LEVEL_PROCEDURE
485 ,p_module => l_log_module);
486 trace
487 (p_msg => 'END of procedure COMPLETE_JOURNAL_ENTRIES'
488 ,p_level => C_LEVEL_PROCEDURE
489 ,p_module => l_log_module);
490 END IF;
491
492 WHEN OTHERS THEN
493 xla_accounting_err_pkg.build_message
494 (p_appli_s_name => 'XLA'
495 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
496 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Complete_Journal_Entries'
497 ,p_value_1 => 'SLA'
498 ,p_entity_id => NULL
499 ,p_event_id => NULL);
500
501 rollback;
502
503 xla_accounting_err_pkg.insert_errors;
504 COMMIT;
505
506 xla_exceptions_pkg.raise_message
507 (p_location => 'xla_multiperiod_accounting_pkg.complete_journal_entries');
508
509 END complete_journal_entries; -- end of procedure
510
511
512 --============================================================================
513 --
514 -- Private API which initializes the complete multiperiod accounting program.
515 --
516 --============================================================================
517
518 PROCEDURE Initialize
519 (p_application_id IN NUMBER
520 ,p_ledger_id IN NUMBER
521 ,p_process_category_code IN VARCHAR2
522 ,p_end_date IN DATE
523 ,p_errors_only_flag IN VARCHAR2
524 ,p_transfer_to_gl_flag IN VARCHAR2
525 ,p_post_in_gl_flag IN VARCHAR2
526 ,p_gl_batch_name IN VARCHAR2
527 ,p_valuation_method_code IN VARCHAR2
528 ,p_security_id_int_1 IN NUMBER
529 ,p_security_id_int_2 IN NUMBER
530 ,p_security_id_int_3 IN NUMBER
531 ,p_security_id_char_1 IN VARCHAR2
532 ,p_security_id_char_2 IN VARCHAR2
533 ,p_security_id_char_3 IN VARCHAR2)
534 IS
535 l_log_module VARCHAR2(240);
536 BEGIN
537 IF g_log_enabled THEN
538 l_log_module := C_DEFAULT_MODULE||'.Initialize';
539 END IF;
540
541 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
542 trace
543 (p_msg => 'BEGIN procedure INITIALIZE'
544 ,p_level => C_LEVEL_PROCEDURE
545 ,p_module => l_log_module);
546 END IF;
547
548
549 -- Initialize global params.
550
551 g_application_id := p_application_id;
552 g_ledger_id := p_ledger_id;
553 g_process_category_code := p_process_category_code;
554 g_end_date := p_end_date;
555 g_errors_only_flag := p_errors_only_flag;
556 g_transfer_to_gl_flag := p_transfer_to_gl_flag;
557 g_post_in_gl_flag := p_post_in_gl_flag;
558 g_gl_batch_name := p_gl_batch_name;
559 g_valuation_method_code := p_valuation_method_code;
560 g_security_id_int_1 := p_security_id_int_1;
561 g_security_id_int_2 := p_security_id_int_2;
562 g_security_id_int_3 := p_security_id_int_3;
563 g_security_id_char_1 := p_security_id_char_1;
564 g_security_id_char_2 := p_security_id_char_2;
565 g_security_id_char_3 := p_security_id_char_3;
566
567
568 -- Set request id.
569
570 g_request_id := FND_GLOBAL.Conc_Request_Id();
571
572 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
573 trace
574 (p_msg => 'g_request_id = '||g_request_id
575 ,p_level => C_LEVEL_STATEMENT
576 ,p_module => l_log_module);
577 END IF;
578
579 xla_security_pkg.set_security_context(p_application_id);
580
581 -- Set new accounting batch id.
582
583 Select xla_accounting_batches_s.nextval
584 into g_accounting_batch_id
585 from dual;
586
587 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
588 trace
589 (p_msg => 'g_accounting_batch_id = '||g_accounting_batch_id
590 ,p_level => C_LEVEL_STATEMENT
591 ,p_module => l_log_module);
592 END IF;
593
594 -- Initialize errors package.
595
596 XLA_ACCOUNTING_ERR_PKG.Set_Options
597 (p_error_source => xla_accounting_err_pkg.C_ACCT_PROGRAM
598 ,p_request_id => g_request_id
599 ,p_application_id => p_application_id);
600
601
602 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
603 trace
604 (p_msg => 'END of procedure INITIALIZE '
605 ,p_level => C_LEVEL_PROCEDURE
606 ,p_module => l_log_module);
607 END IF;
608
609 EXCEPTION
610 WHEN xla_exceptions_pkg.application_exception THEN
611 RAISE;
612 WHEN OTHERS THEN
613 xla_accounting_err_pkg.build_message
614 (p_appli_s_name => 'XLA'
615 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
616 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Initialize'
617 ,p_value_1 => 'SLA'
618 ,p_entity_id => NULL
619 ,p_event_id => NULL);
620
621 xla_exceptions_pkg.raise_message
622 (p_location => 'xla_multiperiod_accounting_pkg.Initialize');
623 END Initialize;
624
625
626 --============================================================================
627 --
628 -- Private API which populates the xla_ae_headers_gt table with journal
629 -- entries to be completed and reset its status to 'Incomplete'.
630 --
631 -- The accounting entries to be completed must fullfill the following
632 -- conditions:
633 --
634 -- (1) The journal entry is an accrual reversal entry or a multiperiod
635 -- accounting recognition entry.
636 -- (2) Filtered by the input parameters.
637 -- (3) The accounting entry status of the accrual entry of the entry to
638 -- be completed is 'Final'.
639 --
640 --============================================================================
641
642 PROCEDURE Populate_Journal_Entries
643 IS
644
645 l_stmt VARCHAR2(5000);
646 l_count NUMBER := 0;
647 l_log_module VARCHAR2(240);
648
649 l_err_msg varchar2(100);
650 l_err_num number;
651
652 Cursor C_SEL_HDRS is
653 Select accounting_entry_status_code
654 from xla_ae_headers
655 where ae_header_id in (Select distinct ae_header_id from xla_ae_headers_gt)
656 for update nowait;
657
658 BEGIN
659
660 IF g_log_enabled THEN
661 l_log_module := C_DEFAULT_MODULE||'.Populate_Journal_Entries';
662 END IF;
663
664 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
665 trace
666 (p_msg => 'BEGIN procedure POPULATE_JOURNAL_ENTRIES'
667 ,p_level => C_LEVEL_PROCEDURE
668 ,p_module => l_log_module);
669 END IF;
670
671 g_security_condition := NULL;
672
673 -- Building filter condition based on security columns and valuation method
674 -- This condition will be added dynamically to select statemtents.
675
676 SELECT DECODE(g_valuation_method_code,NULL,NULL,'and valuation_method = '''||g_valuation_method_code||''' ')||
677 DECODE(g_security_id_int_1,NULL,NULL,'and security_id_int_1 = '||g_security_id_int_1||' ')||
678 DECODE(g_security_id_int_2,NULL,NULL,'and security_id_int_2 = '||g_security_id_int_2||' ')||
679 DECODE(g_security_id_int_3,NULL,NULL,'and security_id_int_3 = '||g_security_id_int_3||' ')||
680 DECODE(g_security_id_char_1,NULL,NULL,'and security_id_char_1 = '''||g_security_id_char_1||''' ')||
681 DECODE(g_security_id_char_2,NULL,NULL,'and security_id_char_2 = '''||g_security_id_char_2||''' ')||
682 DECODE(g_security_id_char_3,NULL,NULL,'and security_id_char_3 = '''||g_security_id_char_3||''' ')
683 INTO g_security_condition
684 FROM DUAL;
685
686 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
687 trace
688 (p_msg => 'g_security_condition = '||g_security_condition
689 ,p_level => C_LEVEL_STATEMENT
690 ,p_module => l_log_module);
691 End If;
692
693 -- Building filter condition based process_category.
694 -- This condition will be added dynamically to select statemtents.
695
696 g_process_category_condition := NULL;
697
698 SELECT DECODE(g_process_category_code,NULL,NULL,'and event_class_group_code = '''||g_process_category_code||'''')
699 INTO g_process_category_condition
700 FROM DUAL;
701
702 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
703 trace
704 (p_msg => 'g_process_category_condition = '||g_process_category_condition
705 ,p_level => C_LEVEL_STATEMENT
706 ,p_module => l_log_module);
707 End If;
708
709 -- Insert the journal entries to be completed into xla_ae_headers_gt.
710
711 l_stmt := 'INSERT INTO xla_ae_headers_gt
712 (ae_header_id
713 ,ledger_id
714 ,entity_id
715 ,event_id
716 ,accounting_date
717 ,balance_type_code
718 ,je_category_name
719 ,product_rule_type_code
720 ,product_rule_code
721 ,period_name
722 ,doc_sequence_id
723 ,doc_category_code
724 ,gl_transfer_status_code
725 ,accrual_reversal_flag
726 ,accounting_entry_status_code)
727 SELECT xah.ae_header_id
728 ,xlr.ledger_id
729 ,xah.entity_id
730 ,xah.event_id
731 ,xah.accounting_date
732 ,xah.balance_type_code
733 ,xah.je_category_name
734 ,xah.product_rule_type_code
735 ,xah.product_rule_code
736 ,xah.period_name
737 ,xah.doc_sequence_id
738 ,xah.doc_category_code
739 ,xah.gl_transfer_status_code
740 ,xah.accrual_reversal_flag
741 ,''F''
742 FROM xla_ae_headers xah
743 , xla_ae_headers xah2
744 , xla_ledger_relationships_v xlr
745 , xla_subledgers xs
746 , xla_event_types_b xet
747 , xla_event_class_attrs xec
748 , xla_transaction_entities xte
749 WHERE xlr.primary_ledger_id = :1
750 and xlr.relationship_enabled_flag = ''Y''
751 and xlr.ledger_category_code in (''ALC'',''PRIMARY'',''SECONDARY'')
752 and xlr.ledger_id = xah.ledger_id
753 and xah.application_id = :2
754 and xah.accounting_date <= :3
755 and xah.accounting_entry_status_code
756 IN (''I'', DECODE(:4, ''Y'', ''I'', ''N''))
757 AND xah.application_id = xah2.application_id
758 AND xah.parent_ae_header_id = xah2.ae_header_id
759 AND xah2.accounting_entry_status_code = ''F''
760 AND xs.application_id = xah.application_id
761 AND EXISTS (SELECT NULL
762 FROM xla_ledger_options xlo
763 WHERE application_id = xah.application_id
764 AND DECODE(xlr.ledger_category_code
765 ,''ALC'',xlr.ledger_id
766 ,xlo.ledger_id) = xlr.ledger_id
767 AND DECODE(xlr.ledger_category_code
768 ,''SECONDARY'',xlo.capture_event_flag
769 ,''N'') = ''N''
770 AND DECODE(xlr.ledger_category_code
771 ,''ALC'',''Y''
772 ,xlo.enabled_flag) = ''Y'')
773 AND xte.application_id = xah.application_id
774 AND xte.entity_id = xah.entity_id
775 AND xte.entity_code <> ''MANUAL''
776 AND xet.application_id = xah.application_id
777 AND xet.event_type_code = xah.event_type_code
778 AND xec.application_id = xet.application_id
779 AND xec.entity_code = xet.entity_code
780 AND xec.event_class_code = xet.event_class_code' ||
781 g_security_condition || ' ' ||
782 g_process_category_condition;
783
784
785 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
786 trace
787 (p_msg => 'l_stmt = '||l_stmt
788 ,p_level => C_LEVEL_STATEMENT
789 ,p_module => l_log_module);
790 End If;
791
792 print_logfile('- Dynamic stmt to populate xla_ae_headers_gt table built');
793
794 EXECUTE IMMEDIATE l_stmt
795 USING g_ledger_id
796 ,g_application_id
797 ,g_end_date
798 ,g_errors_only_flag;
799
800 l_count := SQL%ROWCOUNT;
801
802 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
803 trace(p_msg => '# lines inserted = '||SQL%ROWCOUNT,
804 p_module => l_log_module,
805 p_level => C_LEVEL_STATEMENT);
806 END IF;
807
808 print_logfile('- xla_ae_headers_gt table populuated');
809
810 If (l_count = 0) then
811 If (C_LEVEL_EVENT >= g_log_level) THEN
812 trace
813 (p_msg => 'No incomplete recognition journal entries and accrual reversal ' ||
814 'journal entries fetched for the application. '||
815 'There are no events to process in this run.'
816 ,p_level => C_LEVEL_EVENT
817 ,p_module => l_log_module);
818 END IF;
819
820 xla_accounting_err_pkg.build_message
821 (p_appli_s_name => 'XLA'
822 ,p_msg_name => 'XLA_AP_NO_EVENT_TO_PROCESS'
823 ,p_entity_id => NULL
824 ,p_event_id => NULL);
825
826 print_logfile('Technical warning : There are no Incomplete entries to process.');
827
828 Else
829
830 --
831 -- Reset journal entry status for the journal entry to be completed
832 --
833
834 Update xla_ae_headers
835 Set accounting_entry_status_code = 'N'
836 , request_id = g_request_id
837 , accounting_batch_id = g_accounting_batch_id
838 , last_update_date = sysdate
839 , last_updated_by = xla_environment_pkg.g_usr_id
840 , last_update_login = xla_environment_pkg.g_login_id
841 Where application_id = g_application_id
842 and ae_header_id in (Select ae_header_id from xla_ae_headers_gt)
843 RETURNING ae_header_id BULK COLLECT INTO g_array_ae_header_id; -- 5115223
844
845 -- Lock rows in main headers table selected for processing.
846
847 Begin
848 Open C_SEL_HDRS;
849 Close C_SEL_HDRS;
850 Exception
851 When resource_busy Then
852 xla_accounting_err_pkg.build_message
853 (p_appli_s_name => 'XLA'
854 ,p_msg_name => 'XLA_MA_HDR_LOCKED'
855 ,p_entity_id => NULL
856 ,p_event_id => NULL);
857
858 print_logfile('Technical problem : JE Headers of transactions to be completed cannot be locked.');
859
860 xla_exceptions_pkg.raise_message
861 (p_appli_s_name => 'XLA'
862 ,p_msg_name => 'XLA_MA_HDR_LOCKED');
863
864 When Others Then
865 xla_exceptions_pkg.raise_message
866 (p_location => 'xla_multiperiod_accounting_pkg.Populate_Journal_Entries');
867 End;
868
869 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
870 trace(p_msg => '# lines updated = '||SQL%ROWCOUNT,
871 p_module => l_log_module,
872 p_level => C_LEVEL_STATEMENT);
873 END IF;
874
875 End If;
876
877 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
878 trace
879 (p_msg => 'END procedure POPULATE_JOURNAL_ENTRIES'
880 ,p_level => C_LEVEL_PROCEDURE
881 ,p_module => l_log_module);
882 END IF;
883
884 EXCEPTION
885 WHEN xla_exceptions_pkg.application_exception THEN
886 RAISE;
887 WHEN OTHERS THEN
888 xla_accounting_err_pkg.build_message
889 (p_appli_s_name => 'XLA'
890 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
891 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Populate_Journal_Entries'
892 ,p_value_1 => 'SLA'
893 ,p_entity_id => NULL
894 ,p_event_id => NULL);
895
896 xla_exceptions_pkg.raise_message
897 (p_location => 'xla_multiperiod_accounting_pkg.Populate_Journal_Entries');
898
899 END Populate_Journal_Entries;
900
901
902 --============================================================================
903 --
904 -- Private API which updates the journal entry completed in the current run.
905 --
906 --============================================================================
907
908 PROCEDURE Update_Journal_Entries
909 IS
910 l_log_module VARCHAR2(240);
911 BEGIN
912 IF g_log_enabled THEN
913 l_log_module := C_DEFAULT_MODULE||'.Update_Journal_Entries';
914 END IF;
915
916 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
917 trace
918 (p_msg => 'BEGIN of procedure UPDATE_JOURNAL_ENTRIES'
919 ,p_level => C_LEVEL_STATEMENT
920 ,p_module => l_log_module);
921 trace
922 (p_msg => '# incomplete ='||g_array_ae_header_id.COUNT
923 ,p_level => C_LEVEL_STATEMENT
924 ,p_module => l_log_module);
925 END IF;
926
927 FORALL i IN 1..g_array_ae_header_id.COUNT -- 5115223
928 UPDATE xla_ae_headers xah
929 SET accounting_entry_status_code = 'F'
930 ,completed_date = sysdate
931 WHERE xah.request_id = g_request_id
932 AND xah.accounting_batch_id = g_accounting_batch_id
933 AND xah.application_id = g_application_id
934 AND xah.ae_header_id = g_array_ae_header_id(i)
935 AND accounting_entry_status_code NOT IN ('I', 'R');
936
937
938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
939 trace
940 (p_msg => 'Number of headers updated = '||SQL%ROWCOUNT
941 ,p_level => C_LEVEL_PROCEDURE
942 ,p_module => l_log_module);
943
944 trace
945 (p_msg => 'END of procedure UPDATE_JOURNAL_ENTRIES '
946 ,p_level => C_LEVEL_PROCEDURE
947 ,p_module => l_log_module);
948 END IF;
949
950 EXCEPTION
951 WHEN xla_exceptions_pkg.application_exception THEN
952 RAISE;
953
954 WHEN OTHERS THEN
955 xla_accounting_err_pkg.build_message
956 (p_appli_s_name => 'XLA'
957 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
958 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Update_Journal_Entries'
959 ,p_value_1 => 'SLA'
960 ,p_entity_id => NULL
961 ,p_event_id => NULL);
962
963 xla_exceptions_pkg.raise_message
964 (p_location => 'xla_multiperiod_accounting_pkg.Update_Journal_Entries');
965 END Update_Journal_Entries;
966
967
968 --============================================================================
969 --
970 -- Private API which populates the completion sequence number for the journal
971 -- entries to be completed.
972 --
973 --============================================================================
974
975 PROCEDURE Populate_Sequences
976 IS
977 l_seq_context_value fun_seq_batch.context_value_tbl_type;
978 l_xla_seq_status VARCHAR2(30);
979 l_xla_seq_context_id NUMBER;
980 l_log_module VARCHAR2(240);
981
982 BEGIN
983 IF g_log_enabled THEN
984 l_log_module := C_DEFAULT_MODULE||'.Populate_Sequences';
985 END IF;
986
987 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
988 trace
989 (p_msg => 'BEGIN of procedure POPULATE_SEQUENCES'
990 ,p_level => C_LEVEL_STATEMENT
991 ,p_module => l_log_module);
992 END IF;
993
994 --
995 -- Insert the journal entry to be sequenced to the XLA_EVENTS_GT
996 -- and the sequencing batch API will use the GT table to identify
997 -- the journal entry to be sequenced.
998 --
999
1000 INSERT INTO xla_events_gt
1001 (application_id
1002 ,ledger_id
1003 ,entity_id
1004 ,entity_code
1005 ,event_id)
1006 SELECT DISTINCT
1007 g_application_id
1008 ,g_ledger_id
1009 ,h.entity_id
1010 ,t.entity_code
1011 ,h.event_id
1012 FROM xla_ae_headers_gt h,
1013 xla_transaction_entities t
1014 WHERE h.entity_id = t.entity_id
1015 AND h.ledger_id = t.ledger_id
1016 AND t.application_id = g_application_id
1017 AND t.ledger_id = g_ledger_id;
1018
1019 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1020 trace(p_msg => 'Number of journal entries to be sequenced = '||SQL%ROWCOUNT,
1021 p_module => l_log_module,
1022 p_level => C_LEVEL_STATEMENT);
1023 END IF;
1024
1025 --
1026 -- Retrieve all primary, secondary, and ALC ledgers
1027 --
1028
1029 SELECT xlr.ledger_id BULK COLLECT
1030 INTO l_seq_context_value
1031 FROM xla_ledger_relationships_v xlr
1032 ,xla_subledger_options_v xso
1033 WHERE xlr.relationship_enabled_flag = 'Y'
1034 AND xlr.ledger_category_code IN ('ALC','PRIMARY','SECONDARY')
1035 AND DECODE(xso.valuation_method_flag
1036 ,'N',xlr.primary_ledger_id
1037 ,DECODE(xlr.ledger_category_code
1038 ,'ALC',xlr.primary_ledger_id
1039 ,xlr.ledger_id)
1040 ) = g_ledger_id
1041 AND xso.application_id = g_application_id
1042 AND xso.ledger_id = DECODE(xlr.ledger_category_code
1043 ,'ALC',xlr.primary_ledger_id
1044 ,xlr.ledger_id)
1045 AND xso.enabled_flag = 'Y';
1046
1047 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1048 trace(p_msg => 'Number of ledgers to be sequenced = '|| l_seq_context_value.COUNT
1049 ,p_module => l_log_module
1050 ,p_level => C_LEVEL_STATEMENT);
1051 END IF;
1052
1053 --
1054 -- Create sequence in batch mode
1055 --
1056
1057 IF (C_LEVEL_EVENT>= g_log_level) THEN
1058 trace(p_msg => 'Calling FUN_SEQ_BATCH.Batch_Init'
1059 ,p_level => C_LEVEL_EVENT
1060 ,p_module => l_log_module);
1061 END IF;
1062
1063 fun_seq_batch.batch_init
1064 (p_application_id => 602
1065 ,p_table_name => 'XLA_AE_HEADERS'
1066 ,p_event_code => 'COMPLETION'
1067 ,p_context_type => 'LEDGER_AND_CURRENCY'
1068 ,p_context_value_tbl => l_seq_context_value
1069 ,p_request_id => g_request_id
1070 ,x_status => l_xla_seq_status
1071 ,x_seq_context_id => l_xla_seq_context_id);
1072
1073 IF l_xla_seq_status <> 'NO_SEQUENCING' THEN
1074
1075 fun_seq_batch.populate_acct_seq_info
1076 (p_calling_program => 'ACCOUNTING'
1077 ,p_request_id => g_request_id);
1078
1079 fun_seq_batch.batch_exit
1080 (p_request_id => g_request_id
1081 ,x_status => l_xla_seq_status);
1082
1083 END IF;
1084
1085 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1086 trace
1087 (p_msg => 'END of procedure POPULATE_SEQUENCES '
1088 ,p_level => C_LEVEL_PROCEDURE
1089 ,p_module => l_log_module);
1090 END IF;
1091
1092 EXCEPTION
1093 WHEN xla_exceptions_pkg.application_exception THEN
1094 RAISE;
1095
1096 WHEN OTHERS THEN
1097 xla_accounting_err_pkg.build_message
1098 (p_appli_s_name => 'XLA'
1099 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
1100 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Populate_Sequences'
1101 ,p_value_1 => 'SLA'
1102 ,p_entity_id => NULL
1103 ,p_event_id => NULL);
1104
1105 xla_exceptions_pkg.raise_message
1106 (p_location => 'xla_multiperiod_accounting_pkg.Populate_Sequences');
1107 END Populate_Sequences;
1108
1109
1110
1111 --============================================================================
1112 --
1113 -- Private API which transfer the accounting entry to GL if required.
1114 --
1115 --============================================================================
1116
1117 PROCEDURE Transfer_To_GL
1118 IS
1119 l_log_module VARCHAR2(240);
1120 BEGIN
1121 IF g_log_enabled THEN
1122 l_log_module := C_DEFAULT_MODULE||'.Transfer_To_GL';
1123 END IF;
1124
1125 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1126 trace
1127 (p_msg => 'BEGIN of procedure TRANSFER_TO_GL'
1128 ,p_level => C_LEVEL_STATEMENT
1129 ,p_module => l_log_module);
1130 END IF;
1131
1132 xla_accounting_err_pkg.set_options
1133 (p_error_source => xla_accounting_err_pkg.C_TRANSFER_TO_GL);
1134
1135 IF (C_LEVEL_EVENT >= g_log_level) THEN
1136 trace
1137 (p_msg => 'Calling transfer routine XLA_TRANSFER_PKG.GL_TRANSFER_MAIN'
1138 ,p_level => C_LEVEL_EVENT
1139 ,p_module => l_log_module);
1140 END IF;
1141
1142 xla_transfer_pkg.gl_transfer_main
1143 (p_application_id => g_application_id
1144 ,p_transfer_mode => 'COMBINED'
1145 ,p_ledger_id => g_ledger_id
1146 ,p_securiy_id_int_1 => NULL
1147 ,p_securiy_id_int_2 => NULL
1148 ,p_securiy_id_int_3 => NULL
1149 ,p_securiy_id_char_1 => NULL
1150 ,p_securiy_id_char_2 => NULL
1151 ,p_securiy_id_char_3 => NULL
1152 ,p_valuation_method => NULL
1153 ,p_process_category => g_process_category_code
1154 ,p_accounting_batch_id => g_accounting_batch_id
1155 ,p_entity_id => NULL
1156 ,p_batch_name => g_gl_batch_name
1157 ,p_end_date => g_end_date
1158 ,p_submit_gl_post => g_post_in_gl_flag
1159 ,p_caller => xla_transfer_pkg.C_MPA_COMPLETE); -- Bug 5056632
1160
1161 IF (C_LEVEL_EVENT >= g_log_level) THEN
1162 trace
1163 (p_msg => 'Transfer routine XLA_TRANSFER_PKG.GL_TRANSFER_MAIN executed'
1164 ,p_level => C_LEVEL_EVENT
1165 ,p_module => l_log_module);
1166 END IF;
1167
1168 xla_accounting_err_pkg.set_options
1169 (p_error_source => xla_accounting_err_pkg.C_ACCT_PROGRAM);
1170
1171 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1172 trace
1173 (p_msg => 'END of procedure TRANSFER_TO_GL '
1174 ,p_level => C_LEVEL_PROCEDURE
1175 ,p_module => l_log_module);
1176 END IF;
1177
1178 EXCEPTION
1179 WHEN xla_exceptions_pkg.application_exception THEN
1180 RAISE;
1181 WHEN OTHERS THEN
1182 xla_accounting_err_pkg.build_message
1183 (p_appli_s_name => 'XLA'
1184 ,p_msg_name => 'XLA_AP_TECHNICAL_ERROR'
1185 ,p_token_1 => 'XLA_MULTIPERIOD_ACCOUNTING_PKG.Transfer_To_GL'
1186 ,p_value_1 => 'SLA'
1187 ,p_entity_id => NULL
1188 ,p_event_id => NULL);
1189
1190 xla_exceptions_pkg.raise_message
1191 (p_location => 'xla_multiperiod_accounting_pkg.Transfer_To_GL');
1192 END Transfer_To_GL;
1193
1194
1195 --=============================================================================
1196 --
1197 -- Following code is executed when the package body is referenced for the first
1198 -- time
1199 --
1200 --=============================================================================
1201
1202 BEGIN
1203 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1204 g_log_enabled := fnd_log.test
1205 (log_level => g_log_level
1206 ,module => C_DEFAULT_MODULE);
1207
1208 IF NOT g_log_enabled THEN
1209 g_log_level := C_LEVEL_LOG_DISABLED;
1210 END IF;
1211
1212 END xla_multiperiod_accounting_pkg; -- end of package body