[Home] [Help]
PACKAGE BODY: APPS.XLA_TRANSFER_PKG
Source
1 PACKAGE BODY XLA_TRANSFER_PKG AS
2 /* $Header: xlaaptrn.pkb 120.63.12010000.9 2009/01/15 13:23:42 nmsubram ship $ */
3 /*==========================================================================+
4 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA |
5 | ALL rights reserved. |
6 +===========================================================================+
7 | |
8 | FILENAME |
9 | |
10 | xlaaptrn.pkb Common Transfer TO GL API |
11 | |
12 | |
13 | DESCRIPTION |
14 | THE routine transfers subledger journal entries TO GL. |
15 | |
16 | THE journal Import request IS submitted FOR EACH PRIMARY AND secondary |
17 | ledgers. ALC ledgers are processed along WITH THE PRIMARY ledger. |
18 | |
19 | |
20 | PUBLIC PROCEDURES |
21 | xla_transfer_main |
22 | |
23 | PRIVATE FUNCTIONS |
24 | |
25 | PUBLIC FUNCTIONS |
26 | |
27 | PRIVATE PROCEDURES |
28 | |
29 | MODIFICATION HISTORY |
30 | |
31 | 21-Jul-2003 Shishir Joshi Created. |
32 | 18-Sep-2003 Shishir Joshi Made changes for document LEVEL transfer|
33 | 29-Sep-2003 Shishir Joshi Added code TO hanlde GL Post security. |
34 | 03-Oct-2003 Shishir Joshi Populate reference11 WITH recon reference|
35 | IN summary transfer. |
36 | 01-Dec-2003 Shishir Joshi For encumbrnace entries entered amounts |
37 | would be same as accounted amounts. Also,|
38 | encumbrance entries will always be in the|
39 | currency of the ledger. |
40 | 02-Jan-2004 Shishir Joshi Bug 3344168. Also includes trace changes |
41 | - Modified the code to support 'Disable |
42 | Journal Import' profile option. |
43 | 01-Mar-2005 Shishir Joshi Inserting -1 value for set of books when |
44 | inserting rows into the |
45 | gl_interface_confrol table per Deborah's |
46 | recommendation. JI is modified to |
47 | support intercompany functionality. |
48 | 15-Apr-2005 Swapna Vellani Added mutl-table Journal Import. |
49 | 04-Aug-2005 Wynne Chan Bug 4458381 - Public Sector Enhancements |
50 | 07-Oct-2005 Shishir Joshi Trial Balance chnages. Bug 4630945 |
51 | 30-Nov-2005 Vinay Kumar Bug4769315 Added filter on application_id|
52 | 13-Jan-2005 Vinay Kumar Modified the logic to pick JE to transfer|
53 | and signature of gl_transfer_main |
54 | Bug 4945075 Acoid creating n1_index and |
55 | n2_index on GL INTERFACE Table |
56 | 03-Mar-2006 Vinay Kumar Bug 5041325 Removed the procedure |
57 | update_gl_sl_link |
58 | 09-Mar-2006 S. Singhania Bug 5056632. |
59 | - Modified validate_input_parameters. |
60 | - Added paramter p_caller to |
61 | gl_tranfer_main |
62 | - Modified select_journal_entries. |
63 | - Modified logic to get group_ids |
64 | 02-Jun-2006 Vinay Kumar Bug 5254655 Fix for Standalone Transfer |
65 | to GL |
66 | 22-Aug-2006 Ejaz Sayyed Bug#5437400 - update gl_transfer_date in |
67 | set_transfer_status procedure and |
68 | in select_journal_entries procedure, |
69 | set trnsfr status code 'S'for combined mod|
70 | and remove parameter p_ledger_id |
71 | 22-Aug-2006 V. Swapna Bug 5438564. Comment out the call to |
72 | validate_accounting_periods to handle |
73 | a performance issue. |
74 | 4-Sep-2008 rajose bug#7320079 To pass the je_source_name |
75 | while spawning data manager. This helps |
76 | in finding the application from |
77 | which the data manager has been spawned. |
78 +===========================================================================*/
79 -- Constants
80
81 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
82 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
83 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
84 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
85 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
86 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
87 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
88 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_transfer_pkg';
89
90 -- PLSQL Data Types
91
92 TYPE t_array_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
93
94 TYPE r_ledger_rec IS RECORD
95 (ledger_id NUMBER
96 ,NAME gl_ledgers.NAME%TYPE
97 ,ledger_category_code gl_ledgers.ledger_category_code%TYPE
98 ,group_id gl_interface.group_id%TYPE
99 ,interface_run_id gl_interface_control.interface_run_id%TYPE
100 ,gllezl_request_id NUMBER
101 ,access_set_id NUMBER
102 );
103 TYPE t_array_ledgers IS TABLE OF r_ledger_rec INDEX BY BINARY_INTEGER;
104
105 --
106 -- Global Variables
107 --
108 --
109 -- Input Parameters
110 --
111 g_application_id PLS_INTEGER;
112 g_program_id PLS_INTEGER;
113 g_user_id PLS_INTEGER;
114 g_request_id PLS_INTEGER;
115 g_end_date DATE;
116 g_batch_name VARCHAR2(50);
117 g_accounting_batch_id PLS_INTEGER;
118 g_entity_id PLS_INTEGER;
119 g_process_category xla_event_class_grps_b.event_class_group_code%TYPE;
120 g_security_id_int_1 xla_transaction_entities.source_id_int_1%TYPE;
121 g_security_id_int_2 xla_transaction_entities.source_id_int_2%TYPE;
122 g_security_id_int_3 xla_transaction_entities.source_id_int_3%TYPE;
123 g_security_id_char_1 xla_transaction_entities.source_id_char_1%TYPE;
124 g_security_id_char_2 xla_transaction_entities.source_id_char_2%TYPE;
125 g_security_id_char_3 xla_transaction_entities.source_id_char_3%TYPE;
126 g_valuation_method xla_transaction_entities.valuation_method%TYPE;
127 g_caller VARCHAR2(80);
128
129
130 -- Batch level global variables
131 g_interface_run_id PLS_INTEGER;
132 g_je_source_name gl_je_sources.user_je_source_name%TYPE;
133 g_user_source_name gl_je_sources.user_je_source_name%TYPE;
134 g_transfer_mode VARCHAR2(30);
135 g_primary_ledger_id PLS_INTEGER;
136 g_parent_group_id PLS_INTEGER;
137 g_transaction_security VARCHAR2(4000);
138 g_use_ledger_security VARCHAR2(1)
139 := nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'), 'N');
140 g_disable_gllezl_flag VARCHAR2(1) := NVL(fnd_profile.value('XLA_DISABLE_GLLEZL'),'N');
141
142 -- Ledger level global variables
143 g_group_id PLS_INTEGER;
144 g_transfer_summary_mode VARCHAR2(1);
145 g_access_set_id PLS_INTEGER := fnd_profile.value('GL_ACCESS_SET_ID');
146 g_sec_access_set_id PLS_INTEGER := fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID');
147 g_gl_interface_table_name VARCHAR2(30);
148 g_budgetary_control_flag gl_ledgers.enable_budgetary_control_flag%TYPE;
149 --
150 -- Flow Control Flags
151 --
152 g_proceed VARCHAR2(1) := 'Y';
153
154
155 -- Ledger Arrarys
156 g_primary_ledgers_tab t_array_ledgers; -- primary,secondary ledgers
157 g_all_ledgers_tab t_array_ledgers; -- primary,secondary, ALC
158 g_alc_ledger_id_tab t_array_ids; -- primary+ALC
159 g_ledger_id_tab t_array_ids;
160 g_gllezl_requests_tab t_array_ids;
161 g_group_id_tab t_array_ids;
162 g_all_ledger_ids_tab XLA_NUMBER_ARRAY_TYPE;
163
164 -- Global variables for debugging
165 g_log_level PLS_INTEGER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
166 g_log_enabled BOOLEAN := fnd_log.test
167 (log_level => g_log_level
168 ,module => C_DEFAULT_MODULE);
169
170
171 /*===================================================================
172 print DEBUG messages
173
174 =====================================================================*/
175 PROCEDURE trace (p_msg IN VARCHAR2
176 ,p_level IN NUMBER
177 ,p_module IN VARCHAR2) IS
178 BEGIN
179
180 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
181 fnd_log.message(p_level, p_module);
182 ELSIF p_level >= g_log_level THEN
183 fnd_log.string(p_level, p_module, p_msg);
184 END IF;
185 EXCEPTION
186 WHEN xla_exceptions_pkg.application_exception THEN
187 RAISE;
188 WHEN OTHERS THEN
189 xla_exceptions_pkg.raise_message
190 (p_location => 'xla_acct_setup_pub_pkg.trace');
191 END trace;
192
193 --=============================================================================
194 -- ******* Print Log File **********
195 --=============================================================================
196 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
197 BEGIN
198
199 fnd_file.put_line(fnd_file.log,p_msg);
200
201 EXCEPTION
202 WHEN xla_exceptions_pkg.application_exception THEN
203 RAISE;
204 WHEN OTHERS THEN
205 xla_exceptions_pkg.raise_message
206 (p_location => 'xla_acct_setup_pub_pkg.print_logfile');
207 END print_logfile;
208
209
210 /*===========================================================================+
211 PROCEDURE
212 GET_GLLEZL_STATUS
213
214 DESCRIPTION
215 THE routine checkes status OF THE previously submitted journal import
216 requests.
217
218 THE FUNCTION returns FALSE IF it finds a failed JI request.
219
220 SCOPE - PRIVATE
221
222 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
223
224 ARGUMENTS
225
226
227 NOTES
228
229 +===========================================================================*/
230
231 FUNCTION get_gllezl_status
232 RETURN BOOLEAN IS
233 l_callStatus BOOLEAN;
234 l_phase VARCHAR2(30);
235 l_status VARCHAR2(30);
236 l_dev_phase VARCHAR2(30);
237 l_dev_status VARCHAR2(30);
238 l_message VARCHAR2(240);
239 l_gllezl_status gl_interface.status%TYPE;
240 l_index PLS_INTEGER := 0;
241 l_log_module VARCHAR2(240);
242 BEGIN
243
244 IF g_log_enabled THEN
245 l_log_module := C_DEFAULT_MODULE||'.get_gllezl_status';
246 END IF;
247 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
248 trace('get_gllezl_status.Begin',C_LEVEL_PROCEDURE,l_log_module);
249 END IF;
250
251 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
252 trace('Journal import request count = ' || g_gllezl_requests_tab.COUNT,C_LEVEL_STATEMENT,l_Log_module);
253 END IF;
254 --
255 -- Check if any previous requests failed
256 --
257 IF (g_gllezl_requests_tab.COUNT > 0) THEN
258 l_index := g_gllezl_requests_tab.FIRST;
259 FOR i IN 1..g_gllezl_requests_tab.COUNT
260 LOOP
261 trace('Calling fnd_concurrent.get_request_status',C_LEVEL_EVENT,l_Log_module);
262 l_callStatus := fnd_concurrent.get_request_status
263 (request_id => g_gllezl_requests_tab(l_index)
264 ,phase => l_phase
265 ,status => l_status
266 ,dev_phase => l_dev_phase
267 ,dev_status => l_dev_status
268 ,message => l_message
269 );
270
271 IF ( l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL') THEN
272 xla_accounting_err_pkg.build_message
273 (p_appli_s_name => 'XLA'
274 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
275 ,p_token_1 => 'REQUEST_ID'
276 ,p_value_1 => g_gllezl_requests_tab(l_index)
277 -- ,p_token_2 => 'LEDGER_NAME'
278 -- ,p_value_2 => g_primary_ledgers_tab(i).name
279 ,p_entity_id => NULL
280 ,p_event_id => NULL
281 );
282 RETURN FALSE;
283 END IF;
284 l_index := g_gllezl_requests_tab.NEXT(l_index);
285 END LOOP;
286 ELSE
287 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
288 trace('There are no journal import requets submitted at this time.',C_LEVEL_STATEMENT,l_Log_module);
289 END IF;
290 END IF;
291 RETURN TRUE;
292 EXCEPTION
293 WHEN xla_exceptions_pkg.application_exception THEN
294 RAISE;
295 WHEN OTHERS THEN
296 xla_exceptions_pkg.raise_message
297 (p_location => 'xla_transfer_pkg.get_gllezl_status');
298 END get_gllezl_status;
299
300
301
302 /*===================================================================
303 | INSERT ROWS INTO THE GL_INTERFACE_CONTROL |
304 | |
305 =====================================================================*/
306 PROCEDURE insert_interface_control(p_ledger_id NUMBER
307 ,p_table_name VARCHAR2) IS
308
309 l_log_module VARCHAR2(240);
310 BEGIN
311 IF g_log_enabled THEN
312 l_log_module := C_DEFAULT_MODULE||'.insert_interface_control';
313 END IF;
314 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
315 trace('insert_interface_control.Begin',C_LEVEL_PROCEDURE,l_Log_module);
316 END IF;
317
318 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
319 print_logfile ('p_ledger_id = ' || p_ledger_id);
320 print_logfile ('g_group_id = ' || g_group_id);
321 print_logfile ('g_budgetary_control_flag = ' || g_budgetary_control_flag);
322
323 trace('g_budgetary_control_flag:',C_LEVEL_STATEMENT,l_Log_module);
324 END IF;
325
326 INSERT INTO gl_interface_control
327 (
328 je_source_name,
329 status,
330 interface_run_id,
331 group_id,
332 set_of_books_id,
333 packet_id,
334 interface_table_name,
335 processed_table_code
336 )
337 VALUES
338 (
339 g_je_source_name,
340 'S',
341 g_interface_run_id,
342 g_group_id,
343 -1,
344 Decode(g_budgetary_control_flag, 'N', NULL, -3),
345 p_table_name,
346 'S' --7512923 Save rows in interface control table
347 );
348
349 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
350 trace(SQL%ROWCOUNT|| ' rows inserted into the interface control table' ,C_LEVEL_STATEMENT,l_Log_module);
351 END IF;
352
353 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
354 trace('insert_interface_control.End',C_LEVEL_PROCEDURE,l_Log_module);
355 END IF;
356
357 EXCEPTION
358 WHEN xla_exceptions_pkg.application_exception THEN
359 RAISE;
360 WHEN OTHERS THEN
361 trace('Insert into the GL_INTERFACE_CONTROL failed',C_LEVEL_UNEXPECTED,l_Log_module);
362 xla_exceptions_pkg.raise_message
363 (p_location => 'xla_transfer_pkg.get_gllezl_status');
364
365 END insert_interface_control;
366 /*===========================================================================+
367 PROCEDURE
368 CREATE_LOG_ENTRS
369
370 DESCRIPTION
371 THE PROCEDURE creates log ENTRY FOR EACH PRIMARY AND secondary ledger.
372
373
374 SCOPE - PRIVATE
375
376 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
377
378 ARGUMENTS
379 p_ledger_id - PRIMARY/secondary ledger identifier.
380
381
382 NOTES
383
384 +===========================================================================*/
385
386 PROCEDURE insert_transfer_log ( p_ledger_id NUMBER) IS
387
388 l_log_module VARCHAR2(240);
389 BEGIN
390 IF g_log_enabled THEN
391 l_log_module := C_DEFAULT_MODULE||'.insert_transfer_log';
392 END IF;
393 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
394 trace('insert_transfer_log.Begin',C_LEVEL_PROCEDURE,l_Log_module);
395 END IF;
396
397 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
398 trace('Inserting a row into the transfer to GL log table.',C_LEVEL_STATEMENT,l_Log_module);
399 END IF;
400
401 INSERT INTO xla_transfer_logs
402 (
403 application_id
404 ,ledger_id
405 ,parent_group_id
406 ,group_id
407 ,transfer_status_code
408 ,CREATION_DATE
409 ,CREATED_BY
410 ,LAST_UPDATE_DATE
411 ,LAST_UPDATED_BY
412 ,LAST_UPDATE_LOGIN
413 ,PROGRAM_UPDATE_DATE
414 ,PROGRAM_APPLICATION_ID
415 ,PROGRAM_ID
416 ,REQUEST_ID
417 )
418 VALUES
419 (
420 g_application_id
421 ,p_ledger_id
422 ,g_parent_group_id
423 ,g_group_id
424 ,'INCOMPLETE' -- Incomplete
425 ,SYSDATE
426 ,g_user_id
427 ,SYSDATE
428 ,xla_environment_pkg.g_usr_id
429 ,xla_environment_pkg.g_login_id
430 ,SYSDATE
431 ,xla_environment_pkg.g_prog_appl_id
432 ,xla_environment_pkg.g_prog_id
433 ,xla_environment_pkg.g_Req_Id
434 );
435
436 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
437 trace('insert_transfer_log.End',C_LEVEL_PROCEDURE,l_Log_module);
438 END IF;
439
440
441 EXCEPTION
442 WHEN xla_exceptions_pkg.application_exception THEN
443 RAISE;
444 WHEN OTHERS THEN
445 xla_exceptions_pkg.raise_message
446 (p_location => 'xla_transfer_pkg.insert_transfer_log');
447 END insert_transfer_log;
448
449
450 /*===========================================================================+
451 FUNCTION
452 SUBMIT_JOURNAL_IMPORT
453
454 DESCRIPTION
455 THE PROCEDURE handles THE Journal Import submission.
456
457
458 SCOPE - PRIVATE
459
460 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
461
462 ARGUMENTS
463
464
465 NOTES
466
467 +===========================================================================*/
468 FUNCTION submit_journal_import (p_ledger_id IN NUMBER
469 ,p_interface_run_id IN NUMBER
470 ) RETURN NUMBER IS
471 l_gllezl_request_id NUMBER;
472 l_summary_flag VARCHAR2(1);
473 l_log_module VARCHAR2(240);
474 BEGIN
475 IF g_log_enabled THEN
476 l_log_module := C_DEFAULT_MODULE||'.submit_journal_import';
477 END IF;
478 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
479 trace('submit_journal_import.Begin',C_LEVEL_PROCEDURE,l_Log_module);
480 END IF;
481
482 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
483 trace('p_ledger_id = ' || p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
484 trace('p_interface_run_id = ' || p_interface_run_id,C_LEVEL_STATEMENT,l_Log_module);
485 END IF;
486
487
488 IF g_transfer_summary_mode IN ('A','P') THEN
489 l_summary_flag := 'Y';
490 ELSE
491 l_summary_flag := 'N';
492 END IF;
493
494 l_gllezl_request_id:= fnd_request.submit_request
495 (
496 application => 'SQLGL', -- application short name
497 program => 'GLLEZL', -- program short name
498 description => NULL, -- program name
499 start_time => NULL, -- start date
500 sub_request => FALSE, -- sub-request
501 argument1 => p_interface_run_id, -- interface run id
502 argument2 => -602, -- set of books id
503 argument3 => 'N', -- error to suspense flag
504 argument4 => NULL, -- from accounting date
505 argument5 => NULL, -- to accounting date
506 argument6 => l_summary_flag, -- create summary flag
507 argument7 => 'N', -- import desc flex flag
508 argument8 => 'Y' -- Data security mode flag
509 );
510
511 IF NVL(l_gllezl_request_id,0) = 0 THEN
512 IF (C_LEVEL_ERROR >= g_log_level) THEN
513 trace('Unable to submit the Journal Import',C_LEVEL_ERROR,l_Log_module);
514 END IF;
515
516 -- Add an error message.
517 xla_accounting_err_pkg.build_message
518 (p_appli_s_name => 'XLA'
519 ,p_msg_name => 'XLA_GLT_GLLEZL_SUBMIT_FAILED'
520 ,p_token_1 => 'LEDGER_NAME'
521 ,p_value_1 => g_all_ledgers_tab(p_ledger_id).NAME
522 ,p_entity_id => NULL
523 ,p_event_id => NULL
524 );
525 ELSE
526 IF (g_log_enabled AND C_LEVEL_EVENT >= g_log_level) THEN
527 trace('The Journal Import has been submitted successfully. Request Id = ' || l_gllezl_request_id,C_LEVEL_EVENT,l_Log_module);
528 END IF;
529
530 --
531 -- Journal Import is submitted successfully.
532 --
533 g_all_ledgers_tab(p_ledger_id).gllezl_request_id
534 := l_gllezl_request_id;
535
536 -- Populate GLLEZL request ID for ALC ledgers
537 IF g_all_ledgers_tab(p_ledger_id).ledger_category_code = 'PRIMARY' THEN
538 FOR i IN g_alc_ledger_id_tab.FIRST..g_alc_ledger_id_tab.LAST
539 LOOP
540 g_all_ledgers_tab(g_alc_ledger_id_tab(i)).gllezl_request_id := l_gllezl_request_id;
541 trace('GLLEZL Request Id = ' || g_all_ledgers_tab(g_alc_ledger_id_tab(i)).gllezl_request_id,C_LEVEL_EVENT,l_Log_module);
542 END LOOP;
543 END IF;
544 g_gllezl_requests_tab(p_ledger_id) := l_gllezl_request_id;
545
546 UPDATE xla_transfer_logs
547 SET gllezl_request_id = l_gllezl_request_id
548 WHERE group_id = g_group_id;
549 END IF;
550 COMMIT;
551
552 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
553 trace('submit_journal_import.End',C_LEVEL_PROCEDURE,l_Log_module);
554 END IF;
555
556 RETURN(l_gllezl_request_id);
557
558
559 EXCEPTION
560 WHEN xla_exceptions_pkg.application_exception THEN
561 RAISE;
562 WHEN OTHERS THEN
563 xla_exceptions_pkg.raise_message
564 (p_location => 'xla_transfer_pkg.submit_journal_import');
565 END submit_journal_import;
566
567
568 /*====================================================================
569 Get ledgers associated WITH THE PRIMARY ledger
570
571 =====================================================================*/
572 PROCEDURE get_ledgers (p_ledger_id IN NUMBER) IS
573
574 CURSOR c_getledgers(p_ledger_id NUMBER
575 ,p_application_id NUMBER ) IS
576 SELECT ledger_id
577 ,NAME
578 ,ledger_category_code
579 FROM xla_ledger_relationships_v xlr
580 WHERE xlr.primary_ledger_id = p_ledger_id
581 AND xlr.relationship_enabled_flag = 'Y'
582 AND EXISTS (SELECT 1
583 FROM xla_ledger_options xlo
584 WHERE application_id = p_application_id
585 AND DECODE(xlr.ledger_category_code
586 ,'ALC',xlr.ledger_id
587 ,xlo.ledger_id) = xlr.ledger_id
588 AND DECODE(xlr.ledger_category_code
589 ,'SECONDARY',xlo.capture_event_flag
590 ,'N') = 'N'
591 AND DECODE(xlr.ledger_category_code
592 ,'ALC','Y'
593 ,xlo.enabled_flag) = 'Y')
594 ORDER BY DECODE(xlr.ledger_category_code,
595 'PRIMARY',1,
596 'ALC',2
597 ,3);
598
599 l_ledger_name gl_ledgers.NAME%TYPE;
600 l_count PLS_INTEGER := 0;
601 l_alc_count PLS_INTEGER := 0;
602 l_log_module VARCHAR2(240);
603 l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
604
605
606 BEGIN
607 IF g_log_enabled THEN
608 l_log_module := C_DEFAULT_MODULE||'.get_ledgers';
609 END IF;
610 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
611 trace('get_ledgers.Begin',C_LEVEL_PROCEDURE,l_Log_module);
612 END IF;
613
614 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
615 trace('p_ledger_id = ' ||p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
616 END IF;
617
618
619 SELECT NAME
620 ,ledger_category_code
621 ,enable_budgetary_control_flag
622 INTO l_ledger_name
623 ,l_ledger_category_code
624 ,g_budgetary_control_flag
625 FROM gl_ledgers led
626 WHERE led.ledger_id = p_ledger_id;
627
628
629 --
630 -- If the transfer is submitted for a primary ledger then derive
631 -- all associated ledgers for processing. If the transfer is submitted
632 -- for a secondary ledger (For VM based products only) then process only
633 -- the secondary ledger.
634 --
635
636 IF (l_ledger_category_code = 'PRIMARY') THEN
637 g_primary_ledger_id := p_ledger_id;
638 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
639 trace('Deriving ledgers associated with the primary ledger',C_LEVEL_STATEMENT,l_Log_module);
640 END IF;
641
642 FOR ledger_rec IN c_getledgers(p_ledger_id,g_application_id)
643 LOOP
644 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
645 trace('Ledger Rec Name = '||ledger_rec.NAME,C_LEVEL_STATEMENT,l_Log_module);
646 trace('Ledger Rec id = ' ||ledger_rec.ledger_id,C_LEVEL_STATEMENT,l_Log_module);
647 END IF;
648
649 g_all_ledgers_tab(ledger_rec.ledger_id).ledger_id := ledger_rec.ledger_id;
650 g_all_ledgers_tab(ledger_rec.ledger_id).NAME := ledger_rec.NAME;
651 g_all_ledgers_tab(ledger_rec.ledger_id).ledger_category_code
652 := ledger_rec.ledger_category_code;
653
654 IF (ledger_rec.ledger_category_code IN ('PRIMARY','SECONDARY')) THEN
655 l_count := l_count+1;
656 g_primary_ledgers_tab(l_count).ledger_id := ledger_rec.ledger_id;
657 g_primary_ledgers_tab(l_count).NAME := ledger_rec.NAME;
658 g_primary_ledgers_tab(l_count).ledger_category_code
659 := ledger_rec.ledger_category_code;
660 END IF;
661
662 IF (ledger_rec.ledger_category_code IN ('ALC','PRIMARY')) THEN
663 l_alc_count := l_alc_count+1;
664 g_alc_ledger_id_tab(l_alc_count) := ledger_rec.ledger_id;
665 END IF;
666 END LOOP;
667 ELSIF (l_ledger_category_code = 'SECONDARY') THEN
668 l_count := l_count+1;
669 g_primary_ledgers_tab(l_count).ledger_id := p_ledger_id;
670 g_all_ledgers_tab(p_ledger_id).ledger_id := p_ledger_id;
671 g_all_ledgers_tab(p_ledger_id).NAME := l_ledger_name;
672 ELSE
673 IF (C_LEVEL_ERROR >= g_log_level) THEN
674 trace('Invalid ledger. A ledger must be either a primary or a secondary ledger',C_LEVEL_PROCEDURE,l_Log_module);
675 END IF;
676
677 -- Add error message
678 END IF;
679
680 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
681 trace('Total number of ledgers selected = '|| g_all_ledgers_tab.COUNT,C_LEVEL_PROCEDURE,l_Log_module);
682 trace('get_ledgers.End',C_LEVEL_PROCEDURE,l_Log_module);
683 END IF;
684
685 EXCEPTION
686 WHEN xla_exceptions_pkg.application_exception THEN
687 RAISE;
688 WHEN OTHERS THEN
689 xla_exceptions_pkg.raise_message
690 (p_location => 'xla_transfer_pkg.get_ledgers');
691 END get_ledgers;
692
693 /*===========================================================================+
694 | PROCEDURE |
695 | GET_LEDGER_OPTIONS |
696 | |
697 | DESCRIPTION |
698 | Derive ledger LEVEL options |
699 | are called FROM FROM this PROCEDURE. |
700 | |
701 | SCOPE - PRIVATE |
702 | |
703 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
704 | |
705 | ARGUMENTS |
706 | p_ledger_id PRIMARY/Secondary ledger id |
707 | |
708 | NOTES |
709 | |
710 +===========================================================================*/
711
712 PROCEDURE get_ledger_options(p_ledger_id IN NUMBER) IS
713 l_access_set_id NUMBER;
714 l_log_module VARCHAR2(240);
715 BEGIN
716 IF g_log_enabled THEN
717 l_log_module := C_DEFAULT_MODULE||'.get_ledger_options';
718 END IF;
719
720 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 trace('get_ledger_options.Begin',C_LEVEL_PROCEDURE,l_Log_module);
722 trace('p_ledger_id = ' || p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
723 END IF;
724
725 BEGIN
726 SELECT xlo.transfer_to_gl_mode_code
727 INTO g_transfer_summary_mode
728 FROM xla_ledger_options xlo
729 WHERE xlo.application_id = g_application_id
730 AND xlo.ledger_id = p_ledger_id;
731 EXCEPTION
732 WHEN NO_DATA_FOUND THEN
733 xla_exceptions_pkg.raise_message
734 (p_appli_s_name => 'XLA'
735 ,p_msg_name => 'XLA_COMMON_ERROR'
736 ,p_token_1 => 'ERROR'
737 ,p_value_1 => 'The ledger setup is not complete. Please run Update Subledger Accounting Options program for your application '||
738 'ledger_id = '||p_ledger_id||
739 ' application_id = '|| g_application_id
740 ,p_token_2 => 'LOCATION'
741 ,p_value_2 => 'xla_events_pkg.get_ledger_options');
742 END;
743
744 -- Derive access set id based on the use ledger security option
745 --
746 IF (g_use_ledger_security = 'Y') THEN
747 IF (g_access_set_id IS NOT NULL OR g_sec_access_set_id IS NOT NULL) THEN
748 BEGIN
749 SELECT access_set_id
750 INTO l_access_set_id
751 FROM gl_access_sets aset, gl_ledgers led
752 WHERE aset.chart_of_accounts_id = led.chart_of_accounts_id
753 AND led.ledger_id = p_ledger_id
754 AND aset.access_set_id IN (g_access_set_id, g_sec_access_set_id)
755 AND ROWNUM = 1;
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 xla_exceptions_pkg.raise_message
759 (p_appli_s_name => 'XLA'
760 ,p_msg_name => 'XLA_COMMON_ERROR'
761 ,p_token_1 => 'ERROR'
762 ,p_value_1 => 'Access set Id not found for the ledger ID = '
763 ||p_ledger_id
764 ,p_token_2 => 'LOCATION'
765 ,p_value_2 => 'xla_events_pkg.get_ledger_options');
766 trace('Access set Id not found.',C_LEVEL_STATEMENT,l_Log_module);
767
768 END;
769 END IF;
770 ELSE
771 SELECT implicit_access_set_id
772 INTO l_access_set_id
773 FROM gl_ledgers led
774 WHERE led.ledger_id = p_ledger_id;
775 END IF;
776
777 g_all_ledgers_tab(p_ledger_id).access_set_id := l_access_set_id;
778
779 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
780 trace('g_transfer_summary_mode = ' || g_transfer_summary_mode,C_LEVEL_STATEMENT,l_Log_module);
781 trace('l_access_set_id = ' || l_access_set_id,C_LEVEL_STATEMENT,l_Log_module);
782 END IF;
783
784 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 trace('get_ledger_options.End',C_LEVEL_PROCEDURE,l_Log_module);
786 END IF;
787 EXCEPTION
788 WHEN OTHERS THEN
789 trace('Error deriving subledger options for the ledger',C_LEVEL_UNEXPECTED,l_Log_module);
790 xla_exceptions_pkg.raise_message
791 (p_location => 'xla_transfer_pkg.get_ledger_options');
792 END get_ledger_options;
793
794 /*====================================================================
795 Populate ALC ledgers
796
797 *====================================================================*/
798
799 PROCEDURE get_alc_ledgers IS
800 l_log_module VARCHAR2(240);
801 BEGIN
802 IF g_log_enabled THEN
803 l_log_module := C_DEFAULT_MODULE||'.get_alc_ledgers';
804 END IF;
805 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
806 trace('get_alc_ledgers.Begin',C_LEVEL_PROCEDURE,l_Log_module);
807 END IF;
808
809 g_ledger_id_tab := g_alc_ledger_id_tab;
810
811 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
812 trace('get_alc_ledgers.End',C_LEVEL_PROCEDURE,l_Log_module);
813 END IF;
814
815 EXCEPTION
816 WHEN xla_exceptions_pkg.application_exception THEN
817 RAISE;
818 WHEN OTHERS THEN
819 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
820 trace('Error assigning ALC ledgers',C_LEVEL_UNEXPECTED,l_Log_module);
821 END IF;
822 xla_exceptions_pkg.raise_message
823 (p_location => 'xla_transfer_pkg.get_alc_ledgers');
824 END get_alc_ledgers;
825
826 /*===========================================================================+
827 | PROCEDURE |
828 | set_transaction_security |
829 | |
830 | DESCRIPTION |
831 | Dynamically build THE TRANSACTION security clause based ON |
832 | input PARAMETERS |
833 | |
834 | SCOPE - PRIVATE |
835 | |
836 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
837 | |
838 | ARGUMENTS |
839 | |
840 | NOTES |
841 | |
842 +===========================================================================*/
843 PROCEDURE set_transaction_security IS
844 l_log_module VARCHAR2(240);
845 BEGIN
846 IF g_log_enabled THEN
847 l_log_module := C_DEFAULT_MODULE||'.set_transaction_security';
848 END IF;
849
850 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
851 trace('set_transaction_security.Begin',C_LEVEL_PROCEDURE,l_Log_module);
852 END IF;
853
854
855 --
856 -- Check if security has been specified
857 --
858
859 IF ( g_security_id_int_1 IS NOT NULL OR
860 g_security_id_int_2 IS NOT NULL OR
861 g_security_id_int_3 IS NOT NULL OR
862 g_security_id_char_1 IS NOT NULL OR
863 g_security_id_char_2 IS NOT NULL OR
864 g_security_id_char_3 IS NOT NULL OR
865 g_valuation_method IS NOT NULL) THEN
866
867 -- Security info has been provided.
868 g_transaction_security := NULL;
869
870 IF (g_security_id_int_1 IS NOT NULL) THEN
871 g_transaction_security := ' AND xte.security_id_int_1 = ' || g_security_id_int_1;
872 END IF;
873 IF (g_security_id_int_2 IS NOT NULL) THEN
874 g_transaction_security := g_transaction_security ||' AND xte.security_id_int_2 = ' || g_security_id_int_2;
875 END IF;
876 IF (g_security_id_int_3 IS NOT NULL) THEN
877 g_transaction_security := g_transaction_security ||' AND xte.security_id_int_3 = ' || g_security_id_int_3;
878 END IF;
879 IF (g_security_id_char_1 IS NOT NULL) THEN
880 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_1 = ''' || g_security_id_char_1 || '''';
881 END IF;
882 IF (g_security_id_char_2 IS NOT NULL) THEN
883 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_2 = ''' || g_security_id_char_2 || '''';
884 END IF;
885 IF (g_security_id_char_3 IS NOT NULL) THEN
886 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_3 = ''' || g_security_id_char_3 || '''';
887 END IF;
888 IF (g_valuation_method IS NOT NULL) THEN
889 g_transaction_security := g_transaction_security ||' AND xte.valuation_method = ''' || g_valuation_method || '''';
890 END IF;
891 END IF;
892
893 --trace('g_transaction_security = ' || g_transaction_security,C_LEVEL_STATEMENT,l_Log_module);
894 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
895 trace('set_transaction_security.End',C_LEVEL_PROCEDURE,l_Log_module);
896 END IF;
897
898
899 END set_transaction_security;
900
901
902 /*====================================================================
903 * VALIDATE input PARAMETERS
904
905 *====================================================================*/
906 PROCEDURE validate_input_parameters IS
907 l_log_module VARCHAR2(240);
908 BEGIN
909 IF g_log_enabled THEN
910 l_log_module := C_DEFAULT_MODULE||'.validate_input_parameters';
911 END IF;
912
913 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
914 trace('validate_input_parameters.Begin',C_LEVEL_PROCEDURE,l_Log_module);
915 END IF;
916
917
918 -- Validate input parameters
919
920 CASE
921 WHEN g_transfer_mode NOT IN ('STANDALONE','COMBINED')
922 THEN
923 trace('Invalid transfer mode. The transfer mode must be either Standalone or Combined.'
924 ,C_LEVEL_ERROR,l_Log_module);
925
926 xla_exceptions_pkg.raise_message
927 (p_appli_s_name => 'XLA'
928 ,p_msg_name => 'XLA_COMMON_ERROR'
929 ,p_token_1 => 'LOCATION'
930 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
931 ,p_token_2 => 'ERROR'
932 ,p_value_2 => 'Transfer mode must be either Standalone or Combined');
933
934 WHEN g_caller IN (C_TP_MERGE,C_MPA_COMPLETE)
935 AND g_accounting_batch_id IS NULL
936 THEN
937 trace('Accounting batch identifier must be specified.'
938 ,C_LEVEL_ERROR
939 ,l_log_module);
940
941 xla_exceptions_pkg.raise_message
942 (p_appli_s_name => 'XLA'
943 ,p_msg_name => 'XLA_COMMON_ERROR'
944 ,p_token_1 => 'LOCATION'
945 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
946 ,p_token_2 => 'ERROR'
947 ,p_value_2 => 'Accounting batch identifier must be specified.');
948
949 WHEN g_caller IN (C_ACCTPROG_DOCUMENT)
950 AND g_entity_id IS NULL
951 THEN
952 trace('Entity Identifier must be specified.'
953 ,C_LEVEL_ERROR
954 ,l_log_module);
955
956 xla_exceptions_pkg.raise_message
957 (p_appli_s_name => 'XLA'
958 ,p_msg_name => 'XLA_COMMON_ERROR'
959 ,p_token_1 => 'LOCATION'
960 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
961 ,p_token_2 => 'ERROR'
962 ,p_value_2 => 'Entity identifier must be specified.');
963
964 WHEN g_transfer_mode = 'STANDALONE'
965 AND g_caller IN (C_ACCTPROG_BATCH)
966 AND g_end_date IS NULL
967 THEN
968 trace('An end date must be specified for batch accounting in standalone mode'
969 ,C_LEVEL_ERROR,l_Log_module);
970
971 xla_exceptions_pkg.raise_message
972 (p_appli_s_name => 'XLA'
973 ,p_msg_name => 'XLA_COMMON_ERROR'
974 ,p_token_1 => 'LOCATION'
975 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
976 ,p_token_2 => 'ERROR'
977 ,p_value_2 => 'End date must be specified for batch accounting in Standalone mode');
978
979 ELSE
980 NULL;
981 END CASE;
982
983 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
984 trace('validate_input_parameters.End',C_LEVEL_PROCEDURE,l_Log_module);
985 END IF;
986 EXCEPTION
987 WHEN xla_exceptions_pkg.application_exception THEN
988 RAISE;
989 WHEN OTHERS THEN
990 xla_exceptions_pkg.raise_message
991 (p_location => 'xla_transfer_pkg.validate_input_parameters');
992 END validate_input_parameters;
993
994
995 /*===========================================================================+
996 PROCEDURE
997 RECOVER_BATCH
998
999 DESCRIPTION
1000 Performs RECOVERY opration FOR THE previously failed transfer batches.
1001
1002
1003 SCOPE - PRIVATE
1004
1005 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1006
1007 ARGUMENTS
1008
1009
1010 NOTES
1011
1012 +===========================================================================*/
1013 PROCEDURE recover_batch IS
1014 l_log_module VARCHAR2(240);
1015 BEGIN
1016 IF g_log_enabled THEN
1017 l_log_module := C_DEFAULT_MODULE||'.Recover_Batch';
1018 END IF;
1019 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1020 trace('recover_batch.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1021 END IF;
1022
1023 -- Check if there are any previously failed batches. Ignore request that
1024 -- are either runnning, pending or inactive.
1025 -- Phase Code: R - Running, P - Pending, I - Inactive
1026
1027 IF ( g_group_id_tab.COUNT <= 0) THEN
1028 SELECT group_id
1029 ,gllezl_request_id
1030 BULK COLLECT INTO
1031 g_group_id_tab
1032 ,g_gllezl_requests_tab
1033 FROM xla_transfer_logs xtb1
1034 WHERE application_id = g_application_id
1035 AND request_id NOT IN
1036 ( SELECT xtb.request_id
1037 FROM xla_transfer_logs xtb
1038 ,fnd_concurrent_requests fcr
1039 WHERE xtb.application_id = g_application_id
1040 AND xtb.transfer_status_code = 'INCOMPLETE'
1041 --AND xtb.gllezl_request_id IS NOT NULL
1042 AND xtb.request_id = fcr.request_id
1043 AND fcr.phase_code IN ('R','P','I'));
1044 END IF;
1045
1046 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1047 trace('Found '|| g_group_id_tab.COUNT || ' batches to recover', C_LEVEL_STATEMENT,l_log_module);
1048 END IF;
1049
1050 IF (g_group_id_tab.COUNT > 0) THEN
1051 --
1052 -- Reset journal entry headers
1053 --
1054 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1055 trace('Updating XLA_AE_HEADERS',C_LEVEL_STATEMENT,l_log_module);
1056 END IF;
1057 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1058 UPDATE xla_ae_headers
1059 SET group_id = NULL
1060 ,gl_transfer_status_code = 'N'
1061 ,gl_transfer_date = NULL
1062 ,program_update_date = SYSDATE
1063 ,program_id = g_program_id
1064 ,request_id = g_request_id
1065 WHERE group_id = g_group_id_tab(i);
1066
1067
1068 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1069 trace(SQL%ROWCOUNT || ' Headers updated.',C_LEVEL_STATEMENT,l_log_module);
1070 --
1071 -- Delete log entries
1072 --
1073 trace('Deleting rows from xla_transfer_logs ',C_LEVEL_STATEMENT,l_log_module);
1074 END IF;
1075 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1076 DELETE xla_transfer_logs
1077 WHERE group_id = g_group_id_tab(i);
1078
1079 --
1080 -- Delete XLA_TRANSFER_LEDGERS
1081 --
1082
1083 trace('Deleting rows from XLA_TRANSFER_LEDGERS',C_LEVEL_STATEMENT,l_log_module);
1084 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1085 DELETE xla_transfer_ledgers
1086 WHERE group_id = g_group_id_tab(i);
1087
1088 IF SQL%NOTFOUND THEN
1089 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1090 trace('No rows found in the XLA_TRANSFER_LEDGERS table.',C_LEVEL_STATEMENT,l_log_module);
1091 END IF;
1092 ELSE
1093 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1094 trace(SQL%ROWCOUNT || 'Rows deleted from the XLA_TRANSFER_LEDGERS',C_LEVEL_STATEMENT,l_log_module);
1095 END IF;
1096 END IF;
1097
1098 --
1099 -- Delete rows from gl_interface, GL journals
1100 --
1101
1102 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
1103 LOOP
1104 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1105 trace('Looping for each group identifier ' ,C_LEVEL_STATEMENT,l_log_module);
1106 END IF;
1107 IF (g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL) THEN
1108 IF (C_LEVEL_EVENT >= g_log_level) THEN
1109 trace('Calling gl_journal_import_sla_pkg.delete_batches',C_LEVEL_EVENT,l_log_module);
1110 END IF;
1111 gl_journal_import_sla_pkg.delete_batches
1112 (x_je_source_name => g_je_source_name
1113 ,x_group_id => g_primary_ledgers_tab(i).group_id
1114 );
1115 END IF;
1116 END LOOP;
1117
1118 COMMIT;
1119 END IF;
1120
1121 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1122 trace('recover_batch.End',C_LEVEL_PROCEDURE,l_log_module);
1123 END IF;
1124
1125 EXCEPTION
1126 WHEN xla_exceptions_pkg.application_exception THEN
1127 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
1128 trace('Batch Recovery failed',C_LEVEL_UNEXPECTED,l_log_module);
1129 END IF;
1130 RAISE;
1131 WHEN OTHERS THEN
1132 xla_exceptions_pkg.raise_message
1133 (p_location => 'xla_transfer_pkg.recover_batch');
1134 END recover_batch;
1135
1136 /*====================================================================
1137 * Perform period VALIDATION IF GL IS Installed AND THE transfer IS *
1138 * submitted IN stanalone MODE. *
1139 *====================================================================*/
1140
1141 PROCEDURE validate_accounting_periods ( p_ledger_id IN NUMBER) IS
1142 TYPE t_period_name IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
1143 TYPE t_period_year IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1144
1145 l_period_name_tab t_period_name;
1146 l_period_year_tab t_period_year;
1147 l_ledger_ids_tab t_array_ids;
1148 l_budget_version_id_tab t_array_ids;
1149 l_budget_name_tab t_period_name;
1150 l_period_val_failed BOOLEAN := FALSE;
1151 l_index PLS_INTEGER;
1152 l_actual_flag xla_event_class_attrs.ALLOW_ACTUALS_FLAG%TYPE;
1153 l_budget_flag xla_event_class_attrs.ALLOW_BUDGETS_FLAG%TYPE;
1154 l_encum_flag xla_event_class_attrs.ALLOW_ENCUMBRANCE_FLAG%TYPE;
1155 l_statement VARCHAR2(4000);
1156 l_log_module VARCHAR2(240);
1157 BEGIN
1158 IF g_log_enabled THEN
1159 l_log_module := C_DEFAULT_MODULE||'.validate_accounting_periods';
1160 END IF;
1161
1162 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1163 trace('validate_accounting_periods.Begin',C_LEVEL_PROCEDURE,l_log_module);
1164 END IF;
1165 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1166 trace('Number of ledgers selected for a period validation = ' ||g_all_ledgers_tab.count,C_LEVEL_STATEMENT,l_log_module);
1167 END IF;
1168
1169 -- Populate a SQL variable to be used for casting.
1170 g_all_ledger_ids_tab := XLA_NUMBER_ARRAY_TYPE();
1171
1172 l_index := g_all_ledgers_tab.FIRST;
1173 FOR i IN 1..g_all_ledgers_tab.COUNT
1174 LOOP
1175 --trace('Ledger Id = ' || g_all_ledgers_tab(l_index).ledger_id,C_LEVEL_STATEMENT,l_log_module);
1176 g_all_ledger_ids_tab.EXTEND;
1177 g_all_ledger_ids_tab(i) := g_all_ledgers_tab(l_index).ledger_id;
1178 l_index := g_all_ledgers_tab.NEXT(l_index);
1179 END LOOP;
1180
1181 -- Get balance types allowed for an application.
1182
1183 SELECT actual_flag,budget_flag,encumbrance_flag
1184 INTO l_actual_flag, l_budget_flag, l_encum_flag
1185 FROM (SELECT MAX(DECODE(NVL(ALLOW_ACTUALS_FLAG,'N'),'Y','Y','Z')) actual_flag
1186 ,MAX(DECODE(NVL(ALLOW_BUDGETS_FLAG,'N'),'Y','Y','Z')) budget_flag
1187 ,MAX(DECODE(NVL(ALLOW_encumbrance_FLAG,'N'),'Y','Y','Z')) encumbrance_flag
1188 FROM xla_event_class_attrs
1189 WHERE application_id = g_application_id
1190 GROUP BY allow_actuals_flag, allow_budgets_flag, allow_encumbrance_flag
1191 ORDER BY actual_flag,budget_flag,encumbrance_flag)
1192 WHERE ROWNUM = 1;
1193
1194 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1195 trace('l_actual_flag = ' || l_actual_flag,C_LEVEL_STATEMENT,l_log_module);
1196 trace('l_budget_flag = ' || l_budget_flag,C_LEVEL_STATEMENT,l_log_module);
1197 trace('l_encum_flag = ' || l_encum_flag,C_LEVEL_STATEMENT,l_log_module);
1198 END IF;
1199
1200 -- Check for closed periods
1201 IF (g_entity_id IS NOT NULL) THEN
1202 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1203 trace('Performing period validations for the document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1204 END IF;
1205 IF (l_actual_flag = 'Y') THEN
1206
1207 l_statement :=
1208 'SELECT DISTINCT aeh.period_name
1209 ,aeh.ledger_id
1210 FROM xla_ae_headers aeh
1211 ,gl_period_statuses gps
1212 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1213 WHERE aeh.application_id = :2 --g_application_id
1214 AND aeh.ledger_id = led.column_value
1215 AND aeh.entity_id = :3 --g_entity_id
1216 AND aeh.gl_transfer_status_code = ''N''
1217 AND aeh.accounting_entry_status_code = ''F''
1218 AND aeh.balance_type_code = ''A''
1219 AND gps.application_id = 101
1220 AND gps.ledger_id = aeh.ledger_id
1221 AND gps.period_name = aeh.period_name
1222 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1223 AND gps.closing_status IN (''C'',''N'',''P'')';
1224
1225 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1226 trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1227 END IF;
1228 EXECUTE IMMEDIATE l_statement
1229 BULK COLLECT INTO
1230 l_period_name_tab
1231 ,l_ledger_ids_tab
1232 USING g_all_ledger_ids_tab
1233 ,g_application_id
1234 ,g_entity_id;
1235
1236 IF SQL%FOUND THEN
1237 IF (C_LEVEL_ERROR >= g_log_level) THEN
1238 trace('There are journal entries in a closed period.',C_LEVEL_ERROR,l_log_module);
1239 END IF;
1240 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1241 trace('Number of closed periods = ' || l_period_name_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1242 END IF;
1243
1244 l_period_val_failed := TRUE;
1245 FOR j IN l_period_name_tab.FIRST..l_period_name_tab.LAST
1246 LOOP
1247 xla_accounting_err_pkg.build_message
1248 (p_appli_s_name => 'XLA'
1249 ,p_msg_name => 'XLA_GLT_PERIOD_CLOSED'
1250 ,p_token_1 => 'PERIOD_NAME'
1251 ,p_value_1 => l_period_name_tab(j)
1252 ,p_token_2 => 'LEDGER_NAME'
1253 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1254 ,p_entity_id => NULL
1255 ,p_event_id => NULL
1256 );
1257 -- Display error message when there are unposted
1258 -- records in given period and the period is closed.
1259 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1260 trace('The period ' ||l_period_name_tab(j) || ' is closed for the ledger '
1261 || g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME ,C_LEVEL_STATEMENT,l_log_module);
1262 END IF;
1263 END LOOP;
1264 END IF;
1265 END IF;
1266 -- Perform period validations for budget entries
1267 --
1268 IF (l_budget_flag = 'Y') THEN
1269 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1270 trace('Performing budget period validations for a document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1271 END IF;
1272 l_statement := '
1273 SELECT DISTINCT gps.period_year
1274 ,gbv.budget_name
1275 FROM xla_ae_headers aeh
1276 ,gl_period_statuses gps
1277 ,gl_budget_period_ranges gbp
1278 ,gl_budget_versions gbv
1279 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1280 WHERE aeh.application_id = :2
1281 AND aeh.ledger_id = led.column_value
1282 AND aeh.balance_type_code = ''B''
1283 AND aeh.entity_id = :3 --g_entity_id
1284 AND aeh.gl_transfer_status_code = ''N''
1285 AND aeh.accounting_entry_status_code = ''F''
1286 AND gps.application_id = 101
1287 AND gps.ledger_id = aeh.ledger_id
1288 AND gps.period_name = aeh.period_name
1289 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1290 AND gps.period_year = gbp.period_year
1291 AND aeh.budget_version_id = gbp.budget_version_id
1292 AND gbp.open_flag <> ''O''
1293 AND gbv.budget_version_id = aeh.budget_version_id ';
1294
1295 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1296 trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1297 END IF;
1298 EXECUTE IMMEDIATE l_statement
1299 BULK COLLECT INTO
1300 l_period_year_tab
1301 ,l_budget_name_tab
1302 USING g_all_ledger_ids_tab
1303 ,g_application_id
1304 ,g_entity_id;
1305
1306 IF SQL%FOUND THEN
1307 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1308 trace('Number of closed budget versions = ' || l_period_year_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1309 END IF;
1310 l_period_val_failed := TRUE;
1311 FOR j IN l_budget_name_tab.FIRST..l_budget_name_tab.LAST
1312 LOOP
1313 xla_accounting_err_pkg.build_message
1314 (p_appli_s_name => 'XLA'
1315 ,p_msg_name => 'XLA_GLT_BUDGET_YEAR_CLOSED'
1316 ,p_token_1 => 'YEAR'
1317 ,p_value_1 => l_period_year_tab(j)
1318 ,p_entity_id => NULL
1319 ,p_event_id => NULL
1320 );
1321 -- Display error message when there are unposted
1322 -- records in given period and the period is closed.
1323 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1324 trace('The budget ' ||l_budget_name_tab(j) || ' is in a closed year. ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
1325 END IF;
1326 END LOOP;
1327 END IF;
1328 END IF; -- l_budget_flag = 'Y'
1329 IF (l_encum_flag = 'Y') THEN
1330 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1331 trace('Performing encumbrance period validations for a document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1332 END IF;
1333 l_statement :=
1334 ' SELECT DISTINCT aeh.ledger_id
1335 ,gll.latest_encumbrance_year
1336 FROM xla_ae_headers aeh
1337 ,gl_period_statuses gps
1338 ,gl_ledgers gll
1339 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1340 WHERE aeh.application_id = :2 --g_application_id
1341 AND aeh.entity_id = :3 --g_entity_id
1342 AND aeh.ledger_id = led.column_value
1343 AND aeh.balance_type_code = ''E''
1344 AND aeh.ledger_id = gll.ledger_id
1345 AND aeh.gl_transfer_status_code = ''N''
1346 AND aeh.accounting_entry_status_code = ''F''
1347 AND gps.application_id = 101
1348 AND gps.ledger_id = aeh.ledger_id
1349 AND gps.period_name = aeh.period_name
1350 AND gps.period_year > gll.latest_encumbrance_year ';
1351
1352 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1353 trace('l_statement = ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1354 END IF;
1355 EXECUTE IMMEDIATE l_statement
1356 BULK COLLECT INTO
1357 l_ledger_ids_tab
1358 ,l_period_year_tab
1359 USING g_all_ledger_ids_tab
1360 ,g_application_id
1361 ,g_entity_id;
1362
1363 IF SQL%FOUND THEN
1364 l_period_val_failed := TRUE;
1365 FOR j IN l_ledger_ids_tab.FIRST..l_ledger_ids_tab.LAST
1366 LOOP
1367 xla_accounting_err_pkg.build_message
1368 (p_appli_s_name => 'XLA'
1369 ,p_msg_name => 'XLA_GLT_ENCUM_YEAR_CLOSED'
1370 ,p_token_1 => 'LEDGER_NAME'
1371 ,p_value_1 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1372 ,p_token_2 => 'YEAR'
1373 ,p_value_2 => l_period_year_tab(j)
1374 ,p_entity_id => NULL
1375 ,p_event_id => NULL
1376 );
1377 -- Display an error message when there are unposted
1378 -- records the closed period.
1379 IF (C_LEVEL_ERROR >= g_log_level) THEN
1380 trace('The last open encumbrance year for the ledger ' ||g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME || ' is ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
1381 END IF;
1382 END LOOP;
1383 END IF;
1384 END IF;
1385 ELSIF ( g_end_date IS NOT NULL ) THEN
1386 IF (l_actual_flag = 'Y') THEN
1387 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1388 trace('Performing period validations for the batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
1389 END IF;
1390 l_statement :=
1391 'SELECT DISTINCT aeh.period_name
1392 ,aeh.ledger_id
1393 FROM xla_ae_headers aeh
1394 ,gl_period_statuses gps
1395 ,xla_transaction_entities xte
1396 ,xla_event_types_b xet
1397 ,xla_event_class_attrs xec
1398 ,xla_ledger_relationships_v xlr
1399 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1400 WHERE xte.entity_id = aeh.entity_id
1401 AND aeh.application_id = :2 --g_application_id
1402 AND aeh.ledger_id = led.column_value
1403 AND aeh.accounting_date <= :3 --g_end_date
1404 AND aeh.balance_type_code = ''A''
1405 AND aeh.ledger_id = xlr.ledger_id
1406 AND xte.entity_code = xec.entity_code
1407 AND xte.application_id = xec.application_id
1408 AND xec.application_id = xet.application_id
1409 AND xec.entity_code = xet.entity_code
1410 AND xec.event_class_code = xet.event_class_code
1411 AND xec.event_class_group_code
1412 = NVL(:4,xec.event_class_group_code)
1413 AND xet.event_type_code = aeh.event_type_code
1414 AND xet.application_id = aeh.application_id
1415 AND xet.entity_code = xte.entity_code
1416 AND aeh.gl_transfer_status_code = ''N''
1417 AND aeh.accounting_entry_status_code = ''F''
1418 AND gps.application_id = 101
1419 AND gps.ledger_id = aeh.ledger_id
1420 AND gps.period_name = aeh.period_name
1421 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1422 AND gps.closing_status IN (''C'',''N'',''P'')'
1423 || g_transaction_security;
1424
1425 --trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1426 EXECUTE IMMEDIATE l_statement
1427 BULK COLLECT INTO
1428 l_period_name_tab
1429 ,l_ledger_ids_tab
1430 USING g_all_ledger_ids_tab
1431 ,g_application_id
1432 ,g_end_date
1433 ,g_process_category;
1434
1435 IF SQL%FOUND THEN
1436 IF (C_LEVEL_ERROR >= g_log_level) THEN
1437 trace('There are journal entries in a closed period.',C_LEVEL_ERROR,l_log_module);
1438 END IF;
1439 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1440 trace('Number of periods closed = ' || l_period_name_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1441 END IF;
1442 l_period_val_failed := TRUE;
1443 FOR j IN l_period_name_tab.FIRST..l_period_name_tab.LAST
1444 LOOP
1445 xla_accounting_err_pkg.build_message
1446 (p_appli_s_name => 'XLA'
1447 ,p_msg_name => 'XLA_GLT_PERIOD_CLOSED'
1448 ,p_token_1 => 'PERIOD_NAME'
1449 ,p_value_1 => l_period_name_tab(j)
1450 ,p_token_2 => 'LEDGER_NAME'
1451 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1452 ,p_entity_id => NULL
1453 ,p_event_id => NULL
1454 );
1455 -- Display error message when there are unposted
1456 -- records in given period and the period is closed.
1457 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1458 trace('The period ' ||l_period_name_tab(j) || ' is closed for the ledger '
1459 || g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME ,C_LEVEL_STATEMENT,l_log_module);
1460 END IF;
1461 END LOOP;
1462 END IF;
1463 END IF;
1464 -- Perform period validations for budget entries
1465 --
1466 IF (l_budget_flag = 'Y') THEN
1467 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1468 trace('Performing budget period validations for a batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
1469 END IF;
1470 l_statement := '
1471 SELECT DISTINCT gps.period_year
1472 ,gbv.budget_name
1473 FROM xla_ae_headers aeh
1474 ,xla_transaction_entities xte
1475 ,xla_event_types_b xet
1476 ,xla_event_class_attrs xec
1477 ,gl_period_statuses gps
1478 ,gl_budget_period_ranges gbp
1479 ,gl_budget_versions gbv
1480 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1481 WHERE xte.entity_id = aeh.entity_id
1482 AND aeh.application_id = :2
1483 AND aeh.ledger_id = led.column_value
1484 AND aeh.accounting_date <= :3 --g_end_date
1485 AND aeh.balance_type_code = ''B''
1486 AND xte.entity_code = xec.entity_code
1487 AND xte.application_id = xec.application_id
1488 AND xec.application_id = xet.application_id
1489 AND xec.entity_code = xet.entity_code
1490 AND xec.event_class_code = xet.event_class_code
1491 AND xec.event_class_group_code
1492 = NVL(:4,xec.event_class_group_code)
1493 AND xet.event_type_code = aeh.event_type_code
1494 AND xet.application_id = aeh.application_id
1495 AND xet.entity_code = xte.entity_code
1496 AND aeh.gl_transfer_status_code = ''N''
1497 AND aeh.accounting_entry_status_code = ''F''
1498 AND gps.application_id = 101
1499 AND gps.ledger_id = aeh.ledger_id
1500 AND gps.period_name = aeh.period_name
1501 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1502 AND gps.period_year = gbp.period_year
1503 AND aeh.budget_version_id = gbp.budget_version_id
1504 AND gbp.open_flag <> ''O''
1505 AND gbv.budget_version_id = aeh.budget_version_id '
1506 || g_transaction_security;
1507
1508 --trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1509 EXECUTE IMMEDIATE l_statement
1510 BULK COLLECT INTO
1511 l_period_year_tab
1512 ,l_budget_name_tab
1513 USING g_all_ledger_ids_tab
1514 ,g_application_id
1515 ,g_end_date
1516 ,g_process_category;
1517
1518 IF SQL%FOUND THEN
1519 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1520 trace('Number of closed budget versions = ' || l_period_year_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1521 END IF;
1522 l_period_val_failed := TRUE;
1523 FOR j IN l_budget_name_tab.FIRST..l_budget_name_tab.LAST
1524 LOOP
1525 xla_accounting_err_pkg.build_message
1526 (p_appli_s_name => 'XLA'
1527 ,p_msg_name => 'XLA_GLT_BUDGET_YEAR_CLOSED'
1528 ,p_token_1 => 'YEAR'
1529 ,p_value_1 => l_period_year_tab(j)
1530 ,p_entity_id => NULL
1531 ,p_event_id => NULL
1532 );
1533 -- Display error message when there are unposted
1534 -- records in given period and the period is closed.
1535 IF (C_LEVEL_ERROR >= g_log_level) THEN
1536 trace('The budget ' ||l_budget_name_tab(j) || ' is in a closed year. ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
1537 END IF;
1538 END LOOP;
1539 END IF;
1540 END IF;
1541
1542 IF (l_encum_flag = 'Y') THEN
1543 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1544 trace('Performing encumbrance period validations for a batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
1545 END IF;
1546 l_statement :=
1547 ' SELECT DISTINCT aeh.ledger_id
1548 ,gll.latest_encumbrance_year
1549 FROM xla_ae_headers aeh
1550 ,gl_period_statuses gps
1551 ,xla_transaction_entities xte
1552 ,xla_event_types_b xet
1553 ,xla_event_class_attrs xec
1554 ,gl_ledgers gll
1555 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1556 WHERE xte.entity_id = aeh.entity_id
1557 AND aeh.application_id = :2 --g_application_id
1558 AND aeh.ledger_id = led.column_value
1559 AND aeh.accounting_date <= :3 --g_end_date
1560 AND aeh.balance_type_code = ''E''
1561 AND aeh.ledger_id = gll.ledger_id
1562 AND xte.entity_code = xec.entity_code
1563 AND xte.application_id = xec.application_id
1564 AND xec.application_id = xet.application_id
1565 AND xec.entity_code = xet.entity_code
1566 AND xec.event_class_code = xet.event_class_code
1567 AND xec.event_class_group_code = NVL(:4,xec.event_class_group_code)
1568 AND xet.event_type_code = aeh.event_type_code
1569 AND xet.application_id = aeh.application_id
1570 AND xet.entity_code = xte.entity_code
1571 AND aeh.gl_transfer_status_code = ''N''
1572 AND aeh.accounting_entry_status_code = ''F''
1573 AND gps.application_id = 101
1574 AND gps.ledger_id = aeh.ledger_id
1575 AND gps.period_name = aeh.period_name
1576 AND gps.period_year > gll.latest_encumbrance_year '
1577 || g_transaction_security;
1578
1579 EXECUTE IMMEDIATE l_statement
1580 BULK COLLECT INTO
1581 l_ledger_ids_tab
1582 ,l_period_year_tab
1583 USING g_all_ledger_ids_tab
1584 ,g_application_id
1585 ,g_end_date
1586 ,g_process_category;
1587
1588 IF SQL%FOUND THEN
1589 l_period_val_failed := TRUE;
1590 FOR j IN l_ledger_ids_tab.FIRST..l_ledger_ids_tab.LAST
1591 LOOP
1592 xla_accounting_err_pkg.build_message
1593 (p_appli_s_name => 'XLA'
1594 ,p_msg_name => 'XLA_GLT_ENCUM_YEAR_CLOSED'
1595 ,p_token_1 => 'YEAR'
1596 ,p_value_1 => l_period_year_tab(j)
1597 ,p_token_2 => 'LEDGER_NAME'
1598 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1599 ,p_entity_id => NULL
1600 ,p_event_id => NULL
1601 );
1602 -- Display an error message when there are unposted
1603 -- records the closed period.
1604 IF (C_LEVEL_ERROR >= g_log_level) THEN
1605 trace('The last open encumbrance year for the ledger ' ||g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME || ' is ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
1606 END IF;
1607 END LOOP;
1608 END IF;
1609 END IF;
1610 END IF;
1611
1612 IF (l_period_val_failed) THEN
1613 IF (C_LEVEL_ERROR >= g_log_level) THEN
1614 trace('Transfer to GL period validation has failed.',C_LEVEL_ERROR,l_log_module);
1615 END IF;
1616 xla_exceptions_pkg.raise_exception;
1617 END IF;
1618
1619 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1620 trace('validate_accounting_periods.End',C_LEVEL_PROCEDURE,l_log_module);
1621 END IF;
1622 EXCEPTION
1623 WHEN xla_exceptions_pkg.application_exception THEN
1624 RAISE;
1625 WHEN OTHERS THEN
1626 xla_exceptions_pkg.raise_message
1627 (p_location => 'xla_trasnfer_pkg.validate_accounting_periods');
1628 END validate_accounting_periods;
1629
1630
1631 /*====================================================================
1632 THE PROCEDURE selects AND marks THE journal entries
1633 *====================================================================*/
1634 -- removed parameter p_ledger_id
1635 PROCEDURE select_journal_entries IS
1636 l_statement VARCHAR2(4000);
1637 l_log_module VARCHAR2(240);
1638 l_je_count NUMBER;
1639 BEGIN
1640 IF g_log_enabled THEN
1641 l_log_module := C_DEFAULT_MODULE||'.select_journal_entries';
1642 END IF;
1643
1644 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1645 trace('select_journal_entries.Begin',C_LEVEL_PROCEDURE,l_log_module);
1646 END IF;
1647 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1648 trace('Number of ledgers selected = ' || g_ledger_id_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1649 END IF;
1650
1651 -- Select accounting headers for the transfer
1652
1653 IF (g_transfer_mode = 'COMBINED') THEN
1654 CASE g_caller
1655 WHEN C_ACCTPROG_BATCH THEN
1656 -- Bug 5056632.
1657 -- group_id, gl_transfer_date and gl_transfer_status_code
1658 -- is pre populated in the accounting program. get the count.
1659 -- Bug 5437400 - update transfer status and transfer date in
1660 -- combined mode.
1661 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
1662 UPDATE /*+ index(XLA_AE_HEADERS,XLA_AE_HEADERS_N1) */
1663 xla_ae_headers
1664 SET gl_transfer_date = sysdate,
1665 gl_transfer_status_code = 'S'
1666 WHERE application_id = g_application_id
1667 AND ledger_id = g_ledger_id_tab(i)
1668 AND group_id = g_group_id
1669 AND gl_transfer_status_code = 'N'
1670 AND accounting_entry_status_code = 'F';
1671
1672 l_je_count := SQL%ROWCOUNT;
1673
1674 WHEN C_ACCTPROG_DOCUMENT THEN
1675 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1676 trace('Selecting journal entris for the document ' || g_entity_id,C_LEVEL_STATEMENT,l_log_module);
1677 END IF;
1678 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
1679 UPDATE xla_ae_headers aeh
1680 SET program_update_date = SYSDATE,
1681 program_id = g_program_id,
1682 request_id = g_request_id,
1683 group_id = g_group_id,
1684 gl_transfer_date = sysdate,
1685 gl_transfer_status_code = 'S'
1686 WHERE application_id = g_application_id
1687 AND ledger_id = g_ledger_id_tab(i)
1688 AND gl_transfer_status_code = 'N'
1689 AND entity_id = g_entity_id
1690 AND accounting_entry_status_code = 'F';
1691
1692 l_je_count := SQL%ROWCOUNT;
1693
1694 ELSE -- When C_TP_MERGE or C_MPA_COMPLETE
1695 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1696 trace('Selecting journal entries for the accounting batch id ' || g_accounting_batch_id,C_LEVEL_STATEMENT,l_log_module);
1697 END IF;
1698 FORALL i IN g_ledger_id_tab.FIRST .. g_ledger_id_tab.LAST
1699 UPDATE xla_ae_headers aeh
1700 SET program_update_date = SYSDATE,
1701 program_id = g_program_id,
1702 group_id = g_group_id,
1703 gl_transfer_date = SYSDATE,
1704 gl_transfer_status_code = 'S'
1705 WHERE application_id = g_application_id
1706 AND ledger_id = g_ledger_id_tab(i)
1707 AND gl_transfer_status_code = 'N'
1708 AND accounting_batch_id = g_accounting_batch_id
1709 AND accounting_entry_status_code = 'F';
1710
1711 l_je_count := SQL%ROWCOUNT;
1712
1713 END CASE;
1714
1715 ELSIF g_transfer_mode = 'STANDALONE' THEN
1716 IF g_caller = C_ACCTPROG_DOCUMENT THEN
1717 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
1718 UPDATE xla_ae_headers aeh
1719 SET program_update_date = SYSDATE,
1720 program_id = g_program_id,
1721 request_id = g_request_id,
1722 gl_transfer_date = sysdate,
1723 gl_transfer_status_code = 'S',
1724 group_id = g_group_id
1725 WHERE application_id = g_application_id
1726 AND ledger_id = g_ledger_id_tab(i)
1727 AND entity_id = g_entity_id
1728 AND gl_transfer_status_code = 'N'
1729 AND accounting_entry_status_code = 'F'
1730 ;
1731
1732 l_je_count := SQL%ROWCOUNT;
1733
1734 ELSIF g_caller = C_ACCTPROG_BATCH THEN -- Standalone batch transfer
1735 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1736 trace('Standalone batch transfer.',C_LEVEL_STATEMENT,l_log_module);
1737 END IF;
1738
1739 l_statement :=
1740 ' UPDATE
1741 (SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
1742 swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
1743 aeh.program_update_date -- added hint per performance change 7259699
1744 ,aeh.program_id
1745 ,aeh.request_id
1746 ,aeh.gl_transfer_date
1747 ,aeh.gl_transfer_status_code
1748 ,aeh.group_id
1749 FROM xla_ae_headers aeh
1750 ,xla_transaction_entities xte
1751 ,xla_event_types_b xet
1752 ,xla_event_class_attrs xeca
1753 ,xla_event_classes_b xec
1754 WHERE xte.entity_id = aeh.entity_id
1755 AND xte.application_id = :1 --g_application_id
1756 AND aeh.application_id = xte.application_id
1757 AND aeh.ledger_id = :2 --g_ledger_id_tab(i)
1758 AND aeh.accounting_date <= :3 --g_end_date
1759 AND xte.entity_code = xec.entity_code
1760 AND xeca.application_id = xec.application_id
1761 AND xeca.event_class_code = xec.event_class_code
1762 AND xeca.entity_code = xec.entity_code
1763 AND xeca.event_class_group_code = Nvl(:4,xeca.event_class_group_code)
1764 AND xec.event_class_code = xet.event_class_code
1765 AND xet.event_type_code = aeh.event_type_code
1766 AND xet.application_id = aeh.application_id
1767 AND xec.application_id = xet.application_id
1768 AND xet.event_class_code = xec.event_class_code
1769 AND aeh.gl_transfer_status_code = ''N''
1770 AND aeh.accounting_entry_status_code = ''F''
1771 '
1772 || g_transaction_security
1773 || ' ) SET program_update_date = SYSDATE
1774 ,program_id = :5 --g_program_id
1775 ,request_id = :6 --g_request_id
1776 ,gl_transfer_date = Sysdate
1777 ,group_id = :7 --g_group_id
1778 ,gl_transfer_status_code = ''S''';
1779
1780 trace('l_statement_2 := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1781
1782 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
1783 EXECUTE IMMEDIATE l_statement
1784 USING g_application_id
1785 ,g_ledger_id_tab(i)
1786 ,g_end_date
1787 ,g_process_category
1788 ,g_program_id
1789 ,g_request_id
1790 ,g_group_id;
1791
1792 l_je_count := SQL%ROWCOUNT;
1793
1794 END IF;
1795 END IF;
1796
1797
1798 IF (NVL(l_je_count,0) = 0) THEN
1799 -- Add the code to stop the transfer batch if no entries are
1800 -- found for the primary ledger.
1801 g_proceed := 'N';
1802 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1803 trace('No subledger journal entries were found for the specified criteria.',C_LEVEL_STATEMENT,l_log_module);
1804 END IF;
1805 ELSE
1806 g_proceed := 'Y';
1807 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1808 trace('Total journal entries selected = ' || l_je_count,C_LEVEL_STATEMENT,l_log_module);
1809 END IF;
1810 --
1811 -- Create log entry
1812 --
1813 --insert_transfer_log(p_ledger_id => p_ledger_id);
1814 END IF;
1815
1816 --
1817 -- Display number of journal entries selected for each ledger.
1818 --
1819 /* FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last LOOP
1820 trace('g_ledger_id = ' ||g_ledger_id_tab(i),C_LEVEL_STATEMENT,l_log_module);
1821 trace('ledger_name = ' ||g_all_ledgers_tab(g_ledger_id_tab(i)).NAME,C_LEVEL_STATEMENT,l_log_module);
1822 trace('Rowcount = ' ||SQL%BULK_ROWCOUNT(i),C_LEVEL_STATEMENT,l_log_module);
1823
1824 IF (SQL%BULK_ROWCOUNT(i) > 0) THEN
1825 xla_accounting_err_pkg.build_message
1826 (p_appli_s_name => 'XLA'
1827 ,p_msg_name => 'XLA_GLT_JE_COUNT'
1828 ,p_token_1 => 'LEDGER_NAME'
1829 ,p_value_1 => g_all_ledgers_tab(g_ledger_id_tab(i)).NAME
1830 ,p_token_2 => 'COUNT'
1831 ,p_value_2 => SQL%BULK_ROWCOUNT(i)
1832 ,p_entity_id => NULL
1833 ,p_event_id => NULL
1834 );
1835 END IF;
1836 END LOOP;
1837 */
1838
1839 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1840 trace('select_journal_entries.End',C_LEVEL_PROCEDURE,l_log_module);
1841 END IF;
1842 EXCEPTION
1843 WHEN xla_exceptions_pkg.application_exception THEN
1844 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
1845 trace('Unexpected error in ',C_LEVEL_UNEXPECTED,l_log_module);
1846 END IF;
1847 RAISE;
1848 WHEN OTHERS THEN
1849 xla_exceptions_pkg.raise_message
1850 (p_location => 'xla_transfer_pkg.select_journal_entries');
1851 END select_journal_entries;
1852
1853 /*===========================================================================+
1854 | PROCEDURE |
1855 | SET_APPLICATION_INFO |
1856 | |
1857 | DESCRIPTION |
1858 | Derive application LEVEL information. |
1859 | |
1860 | SCOPE - PRIVATE |
1861 | |
1862 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1863 | |
1864 | ARGUMENTS |
1865 | p_application_id Application ID OF THE CALLING application. |
1866 | |
1867 | NOTES |
1868 | |
1869 +===========================================================================*/
1870
1871
1872 PROCEDURE set_application_info IS
1873 l_log_module VARCHAR2(240);
1874 BEGIN
1875 IF g_log_enabled THEN
1876 l_log_module := C_DEFAULT_MODULE||'.set_application_info';
1877 END IF;
1878
1879 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1880 trace('set_application_info.Begin',C_LEVEL_PROCEDURE,l_log_module);
1881 END IF;
1882 -- Populate application level info
1883 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1884 trace('Populate application level information',C_LEVEL_STATEMENT,l_log_module);
1885 END IF;
1886 SELECT js.je_source_name
1887 ,user_je_source_name
1888 INTO g_je_source_name
1889 ,g_user_source_name
1890 FROM gl_je_sources js
1891 ,xla_subledgers xsl
1892 WHERE xsl.application_id = g_application_id
1893 AND js.je_source_name = xsl.je_source_name;
1894
1895
1896 --
1897 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1898 trace('set_application_info.End',C_LEVEL_PROCEDURE,l_log_module);
1899 END IF;
1900 EXCEPTION
1901 WHEN xla_exceptions_pkg.application_exception THEN
1902 RAISE;
1903 WHEN OTHERS THEN
1904 xla_exceptions_pkg.raise_message
1905 (p_location => 'xla_transfer_pkg.set_application_info');
1906 END set_application_info;
1907
1908 /*===========================================================================+
1909 PROCEDURE
1910 gl_interface_insert
1911
1912 DESCRIPTION
1913 Inserts ROWS INTO THE GL_ITERFACE TABLE
1914
1915 SCOPE - PRIVATE
1916
1917 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1918
1919 ARGUMENTS
1920
1921
1922 NOTES
1923
1924 +===========================================================================*/
1925
1926 PROCEDURE insert_gl_interface IS
1927 l_log_module VARCHAR2(240);
1928 l_statement VARCHAR2(4000);
1929 l_je_count NUMBER;
1930
1931
1932 BEGIN
1933 IF g_log_enabled THEN
1934 l_log_module := C_DEFAULT_MODULE||'.insert_gl_interface';
1935 END IF;
1936
1937 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1938 trace('gl_interface_insert.Begin',C_LEVEL_PROCEDURE,l_log_module);
1939 trace('g_disable_gllezl_flag = '||g_disable_gllezl_flag,C_LEVEL_PROCEDURE,l_log_module);
1940 END IF;
1941
1942 -- Check if GL Journal Import should be submitted. Do not use multi table insert if
1943 -- GL is not installed or for document level transfer.
1944
1945 IF g_disable_gllezl_flag = 'Y' OR g_entity_id IS NOT NULL THEN
1946 g_gl_interface_table_name := 'GL_INTERFACE';
1947 ELSE
1948 g_gl_interface_table_name := 'XLA_GLT_'||to_char(g_group_id);
1949 GL_JOURNAL_IMPORT_PKG.create_table(g_gl_interface_table_name);
1950 END IF;
1951
1952 print_logfile ('GL Inerface tablename = ' || g_gl_interface_table_name);
1953 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1954 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
1955 END IF;
1956
1957
1958
1959 print_logfile ('GL Inerface tablename = ' || g_gl_interface_table_name);
1960 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1961 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
1962 END IF;
1963
1964
1965 print_logfile ('tablename = ' || g_gl_interface_table_name);
1966 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1967 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
1968 END IF;
1969
1970 --7512923 added extra columns to gl_interface_table
1971 --7419726 changed the decode statement of funds_reserved_flag
1972 l_statement := 'INSERT INTO '||g_gl_interface_table_name||
1973 '(
1974 status, ledger_id
1975 ,user_je_source_name, user_je_category_name
1976 ,accounting_date
1977 ,currency_code
1978 ,date_created, created_by
1979 ,actual_flag
1980 ,budget_version_id
1981 ,encumbrance_type_id
1982 ,code_combination_id, stat_amount
1983 ,entered_dr
1984 ,entered_cr
1985 ,accounted_dr
1986 ,accounted_cr
1987 ,reference1
1988 ,reference4
1989 ,reference5
1990 ,reference10
1991 ,reference11
1992 ,subledger_doc_sequence_id
1993 ,subledger_doc_sequence_value
1994 ,gl_sl_link_table
1995 ,gl_sl_link_id
1996 ,request_id
1997 ,ussgl_transaction_code
1998 ,je_header_id, group_id
1999 ,period_name, jgzz_recon_ref
2000 ,reference_date
2001 ,funds_reserved_flag
2002 ,reference25
2003 ,reference26
2004 ,reference27
2005 ,reference28
2006 ,reference29
2007 ,reference30
2008 )
2009 SELECT /*+ ordered index(aeh,xla_ae_headers_n1) use_nl(jc,led,ael,gps) */
2010 ''NEW'', aeh.ledger_id
2011 ,:1 , jc.user_je_category_name
2012 ,DECODE(:2, ''P'' , gps.end_date , aeh.accounting_date)
2013 ,DECODE(aeh.balance_type_code , ''E'' , led.currency_code , ael.currency_code)
2014 ,SYSDATE, :3
2015 ,aeh.balance_type_code
2016 ,aeh.budget_version_id
2017 ,ael.encumbrance_type_id -- 4458381
2018 ,ael.code_combination_id, ael.statistical_amount
2019 ,DECODE(aeh.balance_type_code, ''E'', ael.accounted_dr, ael.entered_dr) -- 4458381
2020 ,DECODE(aeh.balance_type_code, ''E'', ael.accounted_cr, ael.entered_cr) -- 4458381
2021 ,accounted_dr
2022 ,accounted_cr
2023 ,:4 -- Reference1
2024 ,DECODE(reference_date , NULL , NULL
2025 ,TO_CHAR(reference_date,''DD-MON-YYYY''))||
2026 DECODE(:5 , ''A'' , TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'')
2027 ,''P'' ,aeh.period_name
2028 ,''D'' ,aeh.ae_header_id) --Reference4
2029 ,DECODE(:6,''D'',substrb(aeh.description,1,240),null)
2030 ,DECODE(DECODE(:7,''D'',''D'',''S'')||ael.gl_transfer_mode_code
2031 ,''SS'',null,substrb(ael.description,1,240))
2032 ,DECODE(:8||ael.gl_transfer_mode_code,
2033 ''AS'',jgzz_recon_ref,
2034 ''PS'',jgzz_recon_ref,
2035 aeh.ae_header_id||''-''||ael.ae_line_num) -- Reference11
2036 ,aeh.doc_sequence_id
2037 ,aeh.doc_sequence_value
2038 ,ael.gl_sl_link_table
2039 ,ael.gl_sl_link_id
2040 ,:9
2041 ,ael.ussgl_transaction_code
2042 ,aeh.ae_header_id, :10
2043 ,aeh.period_name, ael.jgzz_recon_ref
2044 ,aeh.reference_date
2045 ,decode(led.enable_budgetary_control_flag
2046 ,''Y'',
2047 decode(aeh.funds_status_code, ''A'', ''Y'', ''S'', ''Y'', ''P'', ''Y'', NULL)
2048 ,''Y'')
2049 ,aeh.entity_id
2050 ,aeh.event_id
2051 ,ael.ae_header_id
2052 ,ael.ae_line_num
2053 ,ael.accounted_dr
2054 ,ael.accounted_cr
2055 FROM xla_ae_headers aeh
2056 ,xla_ae_lines ael
2057 ,gl_je_categories jc
2058 ,gl_period_statuses gps
2059 ,gl_ledgers led
2060 WHERE ael.application_id = aeh.application_id
2061 AND ael.ae_header_id = aeh.ae_header_id
2062 AND aeh.group_id = :11
2063 AND aeh.application_id = :12 --4769315
2064 AND aeh.je_category_name = jc.je_category_name
2065 AND gps.application_id = 101
2066 AND gps.ledger_id = aeh.ledger_id
2067 AND led.ledger_id = gps.ledger_id
2068 AND aeh.period_name = gps.period_name
2069 AND aeh.gl_transfer_status_code = ''S''';
2070
2071 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2072 trace('l_statement = ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
2073 END IF;
2074
2075 EXECUTE IMMEDIATE l_statement
2076 USING g_user_source_name
2077 ,g_transfer_summary_mode
2078 ,g_user_id
2079 ,g_batch_name
2080 ,g_transfer_summary_mode
2081 ,g_transfer_summary_mode
2082 ,g_transfer_summary_mode
2083 ,g_transfer_summary_mode
2084 ,g_request_id
2085 ,g_group_id
2086 ,g_group_id
2087 ,g_application_id;
2088
2089
2090 --added bug 7640725
2091 l_je_count := SQL%ROWCOUNT;
2092
2093
2094 IF (NVL(l_je_count,0) = 0) THEN
2095 -- Add the code to stop the transfer batch if no entries are
2096 -- found for the primary ledger.
2097 g_proceed := 'N';
2098 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2099 trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
2100 END IF;
2101 ELSE
2102 g_proceed := 'Y';
2103 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2104 trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
2105 END IF;
2106
2107 END IF;
2108 -- end bug 7640725
2109
2110 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2111 trace('gl_interface_insert.End',C_LEVEL_PROCEDURE,l_log_module);
2112 END IF;
2113 EXCEPTION
2114 WHEN xla_exceptions_pkg.application_exception THEN
2115 RAISE;
2116 WHEN OTHERS THEN
2117 xla_exceptions_pkg.raise_message
2118 (p_location => 'xla_transfer_pkg.gl_interface_insert');
2119 END insert_gl_interface;
2120
2121 /*===========================================================================+
2122 PROCEDURE
2123 wait_for_gllezl
2124
2125 DESCRIPTION
2126 Wait FOR THE journal import request TO COMPLETE.
2127
2128 SCOPE - PRIVATE
2129
2130 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2131
2132 ARGUMENTS
2133
2134
2135 NOTES
2136
2137 +===========================================================================*/
2138 FUNCTION wait_for_gllezl RETURN BOOLEAN IS
2139 l_callStatus BOOLEAN;
2140 l_phase VARCHAR2(30);
2141 l_status VARCHAR2(30);
2142 l_dev_phase VARCHAR2(30);
2143 l_dev_status VARCHAR2(30);
2144 l_message VARCHAR2(240);
2145 l_gllezl_status BOOLEAN := TRUE;
2146 l_index PLS_INTEGER := 0;
2147 l_log_module VARCHAR2(240);
2148 l_gl_status VARCHAR2(30);
2149 g_gl_interface_table_name VARCHAR2(30);
2150 l_journal_import_status BOOLEAN :=TRUE;
2151 BEGIN
2152 IF g_log_enabled THEN
2153 l_log_module := C_DEFAULT_MODULE||'.wait_for_gllezl';
2154 END IF;
2155
2156 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2157 trace('wait_for_gllezl.Begin',C_LEVEL_PROCEDURE,l_log_module);
2158 END IF;
2159
2160 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2161 trace('Ledgers count = ' || g_primary_ledgers_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
2162 END IF;
2163
2164 FOR i IN REVERSE g_primary_ledgers_tab.first..g_primary_ledgers_tab.last
2165 LOOP
2166 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2167 trace('Looping for the ledger = ' || g_primary_ledgers_tab(i).ledger_id,C_LEVEL_STATEMENT,l_log_module);
2168 END IF;
2169
2170 IF (g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL) THEN
2171 IF (C_LEVEL_EVENT >= g_log_level) THEN
2172 trace('Checking status for request id = ' || g_primary_ledgers_tab(i).gllezl_request_id,C_LEVEL_EVENT,l_log_module);
2173 END IF;
2174
2175 l_callStatus := fnd_concurrent.wait_for_request
2176 (request_id => g_primary_ledgers_tab(i).gllezl_request_id
2177 ,interval => 5
2178 ,phase => l_phase
2179 ,status => l_status
2180 ,dev_phase => l_dev_phase
2181 ,dev_status => l_dev_status
2182 ,message => l_message);
2183
2184 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2185 trace('l_dev_phase = ' || l_dev_phase,C_LEVEL_STATEMENT,l_log_module);
2186 trace('l_dev_status = ' || l_dev_status,C_LEVEL_STATEMENT,l_log_module);
2187 END IF;
2188
2189 g_gl_interface_table_name := 'XLA_GLT_'||to_char(g_primary_ledgers_tab(i).group_id);
2190
2191
2192 --added bug 6945231
2193 IF ( l_dev_phase = 'COMPLETE' AND l_dev_status ='WARNING') THEN
2194 IF (C_LEVEL_ERROR >= g_log_level) THEN
2195 trace('selecting from gl interface '|| g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2196 END IF;
2197
2198 -- removed join of ledger id from below query for bug 7529513
2199 BEGIN
2200 EXECUTE IMMEDIATE
2201 'select status from ' ||g_gl_interface_table_name||
2202 ' where user_je_source_name= :1
2203 and group_id = :2
2204 and request_id = :4
2205 and status like ''E%''
2206 and rownum=1 ' into l_gl_status
2207 using g_user_source_name,g_primary_ledgers_tab(i).group_id, g_primary_ledgers_tab(i).gllezl_request_id;
2208 EXCEPTION
2209 WHEN NO_DATA_FOUND THEN
2210 IF (C_LEVEL_ERROR >= g_log_level) THEN
2211 trace('No data in gl_interface '|| g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2212 END IF;
2213 END;
2214
2215
2216
2217
2218
2219 IF l_gl_status like 'E%' THEN
2220 IF (C_LEVEL_ERROR >= g_log_level) THEN
2221 trace('Data found in Gl interface with Error Status and journal import request failed. Request Id = ' || g_primary_ledgers_tab(i).gllezl_request_id,C_LEVEL_ERROR,l_log_module);
2222 END IF;
2223 l_journal_import_status:=FALSE;
2224 xla_accounting_err_pkg.build_message
2225 (p_appli_s_name => 'XLA'
2226 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
2227 ,p_token_1 => 'REQUEST_ID'
2228 ,p_value_1 => g_primary_ledgers_tab(i).gllezl_request_id
2229 ,p_token_2 => 'LEDGER_NAME'
2230 ,p_value_2 => g_primary_ledgers_tab(i).NAME
2231 ,p_entity_id => NULL
2232 ,p_event_id => NULL
2233 );
2234 -- Perform Recovery
2235 recover_batch;
2236 END IF;
2237 END IF;
2238
2239 l_gl_status :=NULL;
2240
2241
2242 IF ( l_dev_phase = 'COMPLETE' AND l_dev_status NOT IN ( 'NORMAL','WARNING')) THEN -- added bug 7653258 Transfer to Gl should issue a rollback for all other JI statuses like cancelled/terminated/Error
2243 IF (C_LEVEL_ERROR >= g_log_level) THEN
2244 trace('The journal import request failed. Request Id = ' || g_primary_ledgers_tab(i).gllezl_request_id,C_LEVEL_ERROR,l_log_module);
2245 END IF;
2246 l_journal_import_status:=FALSE;
2247 xla_accounting_err_pkg.build_message
2248 (p_appli_s_name => 'XLA'
2249 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
2250 ,p_token_1 => 'REQUEST_ID'
2251 ,p_value_1 => g_primary_ledgers_tab(i).gllezl_request_id
2252 ,p_token_2 => 'LEDGER_NAME'
2253 ,p_value_2 => g_primary_ledgers_tab(i).NAME
2254 ,p_entity_id => NULL
2255 ,p_event_id => NULL
2256 );
2257 -- Perform Recovery
2258 recover_batch;
2259 END IF;
2260
2261
2262
2263 END IF;
2264
2265 END LOOP;
2266
2267 IF l_journal_import_status = TRUE THEN
2268 l_gllezl_status := TRUE;
2269 ELSE l_gllezl_status:=FALSE;
2270 END IF;
2271
2272 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2273 trace('wait_for_gllezl.End',C_LEVEL_PROCEDURE,l_log_module);
2274 END IF;
2275
2276 RETURN(l_gllezl_status);
2277
2278 EXCEPTION
2279 WHEN xla_exceptions_pkg.application_exception THEN
2280 RAISE;
2281 WHEN OTHERS THEN
2282 xla_exceptions_pkg.raise_message
2283 (p_location => 'xla_transfer_pkg.wait_for_gllezl');
2284 END wait_for_gllezl;
2285
2286 /*===========================================================================+
2287 | PROCEDURE |
2288 | set_transfer_status |
2289 | |
2290 | DESCRIPTION |
2291 | Updates the transfer to GL status to yes to indicate that journal entries|
2292 | have been transferred successfully. |
2293 | SCOPE - PRIVATE |
2294 | |
2295 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2296 | |
2297 | ARGUMENTS |
2298 | |
2299 | NOTES |
2300 | |
2301 +===========================================================================*/
2302 PROCEDURE set_transfer_status IS
2303 l_log_module VARCHAR2(240);
2304 BEGIN
2305 IF g_log_enabled THEN
2306 l_log_module := C_DEFAULT_MODULE||'.set_transfer_status';
2307 END IF;
2308
2309 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2310 trace('set_transfer_status.Begin',C_LEVEL_PROCEDURE,l_log_module);
2311 END IF;
2312 --
2313 -- Update XLA_AE_HEADERS
2314 --
2315 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2316 trace('Updating xla_ae_headers ',C_LEVEL_STATEMENT,l_log_module);
2317 END IF;
2318
2319 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
2320 UPDATE /*+ index(XLA_AE_HEADERS,XLA_AE_HEADERS_N1) */
2321 xla_ae_headers
2322 SET gl_transfer_status_code = 'Y',
2323 gl_transfer_date = sysdate -- bug#5437400
2324 WHERE group_id = g_group_id_tab(i)
2325 AND application_id = g_application_id; --4769315
2326
2327 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2328 trace('set_transfer_status.End',C_LEVEL_PROCEDURE,l_log_module);
2329 END IF;
2330 EXCEPTION
2331 WHEN OTHERS THEN
2332 xla_exceptions_pkg.raise_message
2333 (p_location => 'xla_transfer_pkg.set_transfer_status');
2334 END set_transfer_status;
2335
2336 /*===========================================================================+
2337 | PROCEDURE |
2338 | delete_transfer_log |
2339 | |
2340 | DESCRIPTION |
2341 | Deletes the transfer to GL log. |
2342 | |
2343 | SCOPE - PRIVATE |
2344 | |
2345 | |
2346 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2347 | |
2348 | ARGUMENTS |
2349 | |
2350 | NOTES |
2351 | |
2352 +===========================================================================*/
2353 PROCEDURE delete_transfer_log IS
2354 l_log_module VARCHAR2(240);
2355 BEGIN
2356 IF g_log_enabled THEN
2357 l_log_module := C_DEFAULT_MODULE||'.delete_transfer_log';
2358 END IF;
2359
2360 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2361 trace('delete_transfer_log.Begin',C_LEVEL_PROCEDURE,l_log_module);
2362 END IF;
2363
2364 -- Delete transfer to GL log
2365 --
2366 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2367 trace('Deleting rows from the transfer log.',C_LEVEL_STATEMENT,l_log_module);
2368 END IF;
2369
2370 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
2371 DELETE xla_transfer_logs
2372 WHERE group_id = g_group_id_tab(i);
2373
2374 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2375 trace('delete_transfer_log.End',C_LEVEL_PROCEDURE,l_log_module);
2376 END IF;
2377 EXCEPTION
2378 WHEN OTHERS THEN
2379 xla_exceptions_pkg.raise_message
2380 (p_location => 'xla_transfer_pkg.delete_transfer_log');
2381 END delete_transfer_log;
2382
2383 /*===========================================================================+
2384 | PROCEDURE |
2385 | insert_secondary_ledgers |
2386 | |
2387 | DESCRIPTION |
2388 | Keeps track of journal entries transferred for secondary ledgers |
2389 | |
2390 | SCOPE - PRIVATE |
2391 | |
2392 | |
2393 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2394 | |
2395 | ARGUMENTS |
2396 | p_secondary_ledger_id -- Secondary ledger identifier | |
2397 | NOTES |
2398 | |
2399 +===========================================================================*/
2400 PROCEDURE insert_secondary_ledgers ( p_secondary_ledger_id IN NUMBER ) IS
2401 l_log_module VARCHAR2(240);
2402 BEGIN
2403 IF g_log_enabled THEN
2404 l_log_module := C_DEFAULT_MODULE||'.insert_secondary_ledgers';
2405 END IF;
2406
2407 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2408 trace('insert_secondary_ledgers.Begin',C_LEVEL_PROCEDURE,l_log_module);
2409 END IF;
2410
2411 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2412 trace('insert_secondary_ledgers.End',C_LEVEL_PROCEDURE,l_log_module);
2413 END IF;
2414 INSERT INTO xla_transfer_ledgers
2415 (GROUP_ID
2416 ,SECONDARY_LEDGER_ID
2417 ,PRIMARY_LEDGER_ID
2418 ,CREATION_DATE
2419 ,CREATED_BY
2420 ,LAST_UPDATE_DATE
2421 ,LAST_UPDATED_BY
2422 ,LAST_UPDATE_LOGIN
2423 ,PROGRAM_UPDATE_DATE
2424 ,PROGRAM_APPLICATION_ID
2425 ,PROGRAM_ID
2426 ,REQUEST_ID
2427 )
2428 VALUES
2429 (g_group_id
2430 ,p_secondary_ledger_id
2431 ,g_primary_ledger_id
2432 ,SYSDATE
2433 ,g_user_id
2434 ,SYSDATE
2435 ,xla_environment_pkg.g_usr_id
2436 ,xla_environment_pkg.g_login_id
2437 ,SYSDATE
2438 ,xla_environment_pkg.g_prog_appl_id
2439 ,xla_environment_pkg.g_prog_id
2440 ,xla_environment_pkg.g_Req_Id
2441 );
2442 EXCEPTION
2443 WHEN OTHERS THEN
2444 xla_exceptions_pkg.raise_message
2445 (p_location => 'xla_transfer_pkg.insert_secondary_ledgers');
2446 END insert_secondary_ledgers;
2447
2448 /*===========================================================================+
2449 | PROCEDURE |
2450 | COMPLETE_BATCH |
2451 | |
2452 | DESCRIPTION |
2453 | The procedure performs the finishing tasks after inserting journal |
2454 | entries into the GL interface table. |
2455 | |
2456 | |
2457 | SCOPE - PRIVATE |
2458 | |
2459 | |
2460 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2461 | |
2462 | ARGUMENTS |
2463 | p_submit_gl_post Submit GL post |
2464 | |
2465 | NOTES |
2466 | |
2467 +===========================================================================*/
2468
2469 PROCEDURE complete_batch(p_submit_gl_post VARCHAR2) IS
2470 l_req_id NUMBER;
2471 l_submit_post BOOLEAN := FALSE;
2472 l_ledger_id NUMBER;
2473 l_log_module VARCHAR2(240);
2474 BEGIN
2475 IF g_log_enabled THEN
2476 l_log_module := C_DEFAULT_MODULE||'.complete_batch';
2477 END IF;
2478
2479 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2480 trace('complete_batch.Begin',C_LEVEL_PROCEDURE,l_log_module);
2481 END IF;
2482
2483
2484 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2485 trace('p_submit_gl_post = ' || p_submit_gl_post,C_LEVEL_STATEMENT,l_log_module);
2486 END IF;
2487
2488 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
2489 LOOP
2490 l_ledger_id := g_primary_ledgers_tab(i).ledger_id;
2491 IF (NVL(p_submit_gl_post,'N') = 'Y'
2492 AND g_all_ledgers_tab(l_ledger_id).access_set_id IS NOT NULL) THEN
2493
2494 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2495 trace('l_submit_post = TRUE',C_LEVEL_STATEMENT,l_log_module);
2496 END IF;
2497 l_submit_post := TRUE;
2498 ELSE
2499 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2500 trace('l_submit_post = FALSE',C_LEVEL_STATEMENT,l_log_module);
2501 END IF;
2502 END IF;
2503
2504 IF (g_primary_ledgers_tab(i).ledger_category_code = 'SECONDARY') THEN
2505 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2506 trace('Inserting a row into the XLA_TRANSFER_LEDGERS table.',C_LEVEL_STATEMENT,l_log_module);
2507 END IF;
2508
2509 insert_secondary_ledgers
2510 (p_secondary_ledger_id => g_primary_ledgers_tab(i).ledger_id
2511 );
2512 END IF;
2513 IF (g_log_enabled AND C_LEVEL_EVENT >= g_log_level) THEN
2514 trace('Calling gl_journal_import_SLA_pkg.keep_batches ',C_LEVEL_EVENT,l_log_module);
2515 END IF;
2516
2517 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2518 trace('p_submit_gl_post = ' || p_submit_gl_post,C_LEVEL_STATEMENT,l_log_module);
2519 trace('access_set_id = ' || g_all_ledgers_tab(l_ledger_id).access_set_id,C_LEVEL_STATEMENT,l_log_module);
2520 END IF;
2521
2522 -- keep batches and submit GL post
2523 gl_journal_import_SLA_pkg.keep_batches
2524 (x_je_source_name => g_je_source_name
2525 ,x_group_id => g_primary_ledgers_tab(i).group_id
2526 ,start_posting => l_submit_post
2527 ,data_access_set_id => g_all_ledgers_tab(l_ledger_id).access_set_id
2528 ,req_id => l_req_id);
2529
2530 END LOOP;
2531
2532 IF (g_group_id_tab.COUNT > 0) THEN
2533 set_transfer_status;
2534 delete_transfer_log;
2535 END IF;
2536
2537 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2538 trace('complete_batch.End',C_LEVEL_PROCEDURE,l_log_module);
2539 END IF;
2540
2541 EXCEPTION
2542 WHEN xla_exceptions_pkg.application_exception THEN
2543 RAISE;
2544 WHEN OTHERS THEN
2545 xla_exceptions_pkg.raise_message
2546 (p_location => 'xla_transfer_pkg.complete_batch');
2547 END complete_batch;
2548
2549 /*===========================================================================+
2550 | FUNCTION |
2551 | IS_REPORT_DEFN_FOUND |
2552 | |
2553 | DESCRIPTION |
2554 | For a given ledger, check if an Open Account Balances Listing Report | |
2555 | Definition does exist. |
2556 | When no report definition is found, data manager is not submitted |
2557 | for the ledger. |
2558 | |
2559 | SCOPE - PRIVATE |
2560 | |
2561 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2562 | |
2563 | ARGUMENTS |
2564 | p_ledger_id |
2565 | |
2566 | NOTES |
2567 | |
2568 +===========================================================================*/
2569
2570 FUNCTION is_report_defn_found
2571 (p_ledger_id IN NUMBER
2572 ,p_je_source_name IN VARCHAR2)
2573 RETURN BOOLEAN IS
2574
2575 l_cnt PLS_INTEGER DEFAULT 0;
2576 l_log_module VARCHAR2(240);
2577
2578 BEGIN
2579 IF g_log_enabled THEN
2580 l_log_module := C_DEFAULT_MODULE||'.is_report_defn_found';
2581 END IF;
2582
2583 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2584 trace('is_report_defn_found.Begin'
2585 ,C_LEVEL_PROCEDURE
2586 ,l_log_module);
2587 END IF;
2588
2589 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2590 -- Print all input parameters
2591 trace('p_ledger_id = ' || p_ledger_id
2592 ,C_LEVEL_STATEMENT
2593 ,l_log_module);
2594 trace('p_je_source_name = ' || p_je_source_name
2595 ,C_LEVEL_STATEMENT
2596 ,l_log_module);
2597 END IF;
2598
2599 SELECT COUNT(1)
2600 INTO l_cnt
2601 FROM xla_tb_definitions_b xtd
2602 ,xla_tb_defn_je_sources xjs
2603 WHERE xtd.definition_code = xjs.definition_code
2604 AND xjs.je_source_name = p_je_source_name
2605 and xtd.ledger_id = p_ledger_id;
2606
2607 IF l_cnt > 0 THEN
2608
2609 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2610 trace('# of report definitions ' || l_cnt
2611 ,C_LEVEL_STATEMENT
2612 ,l_log_module);
2613 END IF;
2614
2615 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2616 trace('is_report_defn_found.End'
2617 ,C_LEVEL_PROCEDURE
2618 ,l_log_module);
2619 END IF;
2620
2621 RETURN TRUE;
2622
2623 ELSE
2624
2625 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2626 trace('No report definition for this ledger'
2627 ,C_LEVEL_STATEMENT
2628 ,l_log_module);
2629 END IF;
2630
2631 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2632 trace('is_report_defn_found.End'
2633 ,C_LEVEL_PROCEDURE
2634 ,l_log_module);
2635 END IF;
2636
2637 RETURN FALSE;
2638
2639 END IF;
2640
2641
2642 EXCEPTION
2643 WHEN xla_exceptions_pkg.application_exception THEN
2644 RAISE;
2645 WHEN OTHERS THEN
2646 xla_exceptions_pkg.raise_message
2647 (p_location => 'xla_transfer_pkg.is_report_defn_found');
2648 END is_report_defn_found;
2649
2650 /*===========================================================================+
2651 | PROCEDURE |
2652 | GL_TRANSFER_MAIN |
2653 | |
2654 | DESCRIPTION |
2655 | Main PROCEDURE that controls THE process flow. ALL THE sub procedures |
2656 | are called FROM FROM this PROCEDURE. |
2657 | |
2658 | SCOPE - PUBLIC |
2659 | |
2660 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2661 | |
2662 | ARGUMENTS |
2663 | p_application_id Application ID OF THE CALLING application. |
2664 | |
2665 | NOTES |
2666 | |
2667 +===========================================================================*/
2668
2669
2670 PROCEDURE gl_transfer_main(p_application_id IN NUMBER
2671 ,p_transfer_mode IN VARCHAR2
2672 ,p_ledger_id IN NUMBER
2673 ,p_securiy_id_int_1 IN NUMBER DEFAULT NULL
2674 ,p_securiy_id_int_2 IN NUMBER DEFAULT NULL
2675 ,p_securiy_id_int_3 IN NUMBER DEFAULT NULL
2676 ,p_securiy_id_char_1 IN VARCHAR2 DEFAULT NULL
2677 ,p_securiy_id_char_2 IN VARCHAR2 DEFAULT NULL
2678 ,p_securiy_id_char_3 IN VARCHAR2 DEFAULT NULL
2679 ,p_valuation_method IN VARCHAR2 DEFAULT NULL
2680 ,p_process_category IN VARCHAR2 DEFAULT NULL
2681 ,p_accounting_batch_id IN NUMBER DEFAULT NULL
2682 ,p_entity_id IN NUMBER DEFAULT NULL
2683 ,p_batch_name IN VARCHAR2 DEFAULT NULL
2684 ,p_end_date IN DATE DEFAULT NULL
2685 ,p_submit_gl_post IN VARCHAR2 DEFAULT 'N'
2686 ,p_caller IN VARCHAR2 DEFAULT C_ACCTPROG_BATCH
2687 ) IS
2688 --Local Variables
2689 l_ledger_id NUMBER;
2690 l_gllezl_request_id PLS_INTEGER;
2691 l_log_module VARCHAR2(240);
2692 l_count PLS_INTEGER;
2693 l_req_id PLS_INTEGER;
2694 l_index PLS_INTEGER;
2695
2696 BEGIN
2697 IF g_log_enabled THEN
2698 l_log_module := C_DEFAULT_MODULE||'.gl_transfer_main';
2699 END IF;
2700
2701 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2702 trace('gl_transfer_main.Begin',C_LEVEL_PROCEDURE,l_log_module);
2703 END IF;
2704
2705
2706 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2707 -- Print all input parameters
2708 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
2709 trace('p_application_id = ' || p_application_id ,C_LEVEL_STATEMENT,l_log_module);
2710 trace('p_transfer_mode = ' || p_transfer_mode ,C_LEVEL_STATEMENT,l_log_module);
2711 trace('p_ledger_id = ' || p_ledger_id ,C_LEVEL_STATEMENT,l_log_module);
2712 trace('p_securiy_id_int_1 = ' || p_securiy_id_int_1 ,C_LEVEL_STATEMENT,l_log_module);
2713 trace('p_securiy_id_int_2 = ' || p_securiy_id_int_2 ,C_LEVEL_STATEMENT,l_log_module);
2714 trace('p_securiy_id_int_3 = ' || p_securiy_id_int_3 ,C_LEVEL_STATEMENT,l_log_module);
2715 trace('p_securiy_id_char_1 = ' || p_securiy_id_char_1 ,C_LEVEL_STATEMENT,l_log_module);
2716 trace('p_securiy_id_char_2 = ' || p_securiy_id_char_2 ,C_LEVEL_STATEMENT,l_log_module);
2717 trace('p_securiy_id_char_3 = ' || p_securiy_id_char_3 ,C_LEVEL_STATEMENT,l_log_module);
2718 trace('p_valuation_method = ' || p_valuation_method ,C_LEVEL_STATEMENT,l_log_module);
2719 trace('p_process_category = ' || p_process_category ,C_LEVEL_STATEMENT,l_log_module);
2720 trace('p_accounting_batch_id = ' || p_accounting_batch_id,C_LEVEL_STATEMENT,l_log_module);
2721 trace('p_entity_id = ' || p_entity_id ,C_LEVEL_STATEMENT,l_log_module);
2722 trace('p_batch_name = ' || p_batch_name ,C_LEVEL_STATEMENT,l_log_module);
2723 trace('p_end_date = ' || TO_CHAR(p_end_date,'MM/DD/YYYY'),C_LEVEL_STATEMENT,l_log_module);
2724 trace('p_submit_gl_post = ' || p_submit_gl_post ,C_LEVEL_STATEMENT,l_log_module);
2725 trace('p_caller = ' || p_caller ,C_LEVEL_STATEMENT,l_log_module);
2726 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
2727
2728 trace('Global variables',C_LEVEL_STATEMENT,l_log_module);
2729 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
2730 trace('g_use_ledger_security = ' || g_use_ledger_security,C_LEVEL_STATEMENT,l_log_module);
2731 trace('g_access_set_id = ' || g_access_set_id ,C_LEVEL_STATEMENT,l_log_module);
2732 trace('g_sec_access_set_id = ' || g_sec_access_set_id ,C_LEVEL_STATEMENT,l_log_module);
2733 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
2734 END IF;
2735
2736
2737 -- Set Global Variables
2738 g_application_id := p_application_id;
2739 g_entity_id := p_entity_id;
2740 g_end_date := p_end_date;
2741 g_user_id := fnd_global.user_id;
2742 g_request_id := fnd_global.conc_request_id;
2743 g_transfer_mode := p_transfer_mode;
2744 g_accounting_batch_id := p_accounting_batch_id;
2745 g_batch_name := p_batch_name;
2746 g_program_id := fnd_global.conc_program_id;
2747 g_security_id_int_1 := p_securiy_id_int_1;
2748 g_security_id_int_2 := p_securiy_id_int_2;
2749 g_security_id_int_3 := p_securiy_id_int_3;
2750 g_security_id_char_1 := p_securiy_id_char_1;
2751 g_security_id_char_2 := p_securiy_id_char_2;
2752 g_security_id_char_3 := p_securiy_id_char_3;
2753 g_valuation_method := p_valuation_method;
2754 g_process_category := p_process_category;
2755 g_caller := p_caller;
2756
2757 -- Validate input parameters
2758 validate_input_parameters;
2759 set_transaction_security;
2760
2761 --Get application information
2762 set_application_info;
2763
2764 -- Check if GL is installed.
2765 IF g_disable_gllezl_flag = 'N' THEN
2766 trace('Submit Journal Import has been enabled.',C_LEVEL_STATEMENT,l_log_module);
2767 ELSE
2768 trace('Submit Journal Import has been disabled.',C_LEVEL_STATEMENT,l_log_module);
2769 END IF;
2770
2771 --Get ledgers to process
2772 get_ledgers(p_ledger_id);
2773
2774 IF (p_transfer_mode = 'STANDALONE') THEN
2775 trace('Checking for failed batches',C_LEVEL_STATEMENT,l_log_module);
2776 -- Check for previously failed batches.
2777 --
2778 recover_batch;
2779 -- Initialize the group_id table.
2780 g_group_id_tab.DELETE;
2781 --
2782 -- Perform period validation.
2783 --
2784 --
2785 -- Commenting out the call to validate_accounting_periods for bug 5438564
2786 --
2787 /* validate_accounting_periods (p_ledger_id => p_ledger_id); */
2788 END IF;
2789
2790 --
2791 -- Loop for each primary and secondary ledger
2792 --
2793
2794 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2795 trace('Number of primary and secondary ledgers to process = ' || g_primary_ledgers_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
2796 END IF;
2797
2798 IF g_caller = C_ACCTPROG_BATCH AND g_transfer_mode = 'COMBINED' THEN
2799 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
2800 LOOP
2801 FOR j IN xla_accounting_pkg.g_array_ledger_id.FIRST..xla_accounting_pkg.g_array_ledger_id.LAST
2802 LOOP
2803 IF xla_accounting_pkg.g_array_ledger_id(j) = g_primary_ledgers_tab(i).ledger_id THEN
2804 g_primary_ledgers_tab(i).group_id := xla_accounting_pkg.g_array_group_id(j);
2805 exit;
2806 END IF;
2807 END LOOP;
2808 END LOOP;
2809 END IF;
2810
2811 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
2812 LOOP
2813 l_ledger_id := g_primary_ledgers_tab(i).ledger_id;
2814 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2815 trace('Primary/Secondary ledger loop',C_LEVEL_STATEMENT,l_log_module);
2816 trace('Loop for each primary and secondary ledger',C_LEVEL_STATEMENT,l_log_module);
2817 trace('Ledger Name = ' || g_all_ledgers_tab(l_ledger_id).NAME || ' Ledger Id = ' || l_ledger_id,C_LEVEL_STATEMENT,l_log_module);
2818 END IF;
2819
2820 -- Initialize ledgers array
2821 g_ledger_id_tab.DELETE;
2822
2823 --Get ledger level options
2824 get_ledger_options(p_ledger_id => l_ledger_id);
2825
2826
2827 --
2828 -- Populate group id and inter_run_id;
2829 --
2830 --For bug fix 7677948
2831 IF g_caller = C_ACCTPROG_BATCH AND g_transfer_mode = 'COMBINED' THEN
2832 SELECT gl_journal_import_s.NEXTVAL
2833 INTO g_primary_ledgers_tab(i).interface_run_id
2834 FROM dual;
2835 ELSE
2836 SELECT gl_journal_import_s.NEXTVAL
2837 ,gl_interface_control_s.NEXTVAL
2838 INTO g_primary_ledgers_tab(i).interface_run_id
2839 ,g_primary_ledgers_tab(i).group_id
2840 FROM dual;
2841
2842 --For bug fix 6941347
2843 g_arr_group_id(g_arr_group_id.COUNT +1):= g_primary_ledgers_tab(i).group_id;
2844
2845 END IF;
2846
2847 g_group_id := g_primary_ledgers_tab(i).group_id;
2848 g_interface_run_id := g_primary_ledgers_tab(i).interface_run_id;
2849 g_group_id_tab(i) := g_group_id;
2850
2851
2852 IF (g_primary_ledgers_tab(i).ledger_category_code = 'PRIMARY') THEN
2853 -- Get associated ALC ledgers
2854 --
2855 get_alc_ledgers;
2856 ELSE
2857 g_ledger_id_tab(1) := g_primary_ledgers_tab(i).ledger_id;
2858 END IF;
2859
2860
2861 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2862 trace('Updating Group ID',C_LEVEL_STATEMENT,l_log_module);
2863 END IF;
2864
2865 -- Set the group id
2866 FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last
2867 LOOP
2868 g_all_ledgers_tab(g_ledger_id_tab(i)).group_id := g_group_id;
2869 END LOOP;
2870
2871 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2872 FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last
2873 LOOP
2874 trace('Ledgers selected for the processing',C_LEVEL_STATEMENT,l_log_module);
2875 trace('Ledger id = ' ||g_ledger_id_tab(i),C_LEVEL_STATEMENT,l_log_module);
2876 END LOOP;
2877 END IF;
2878
2879 IF (g_parent_group_id IS NULL) THEN
2880 g_parent_group_id := g_group_id;
2881 END IF;
2882
2883 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2884 ---------------------------------------------------------------------
2885 trace('Group_id = ' || g_group_id,C_LEVEL_STATEMENT,l_log_module);
2886 trace('Interface_run_id = ' || g_interface_run_id,C_LEVEL_STATEMENT,l_log_module);
2887 trace('Inserting an entry into the audit table',C_LEVEL_STATEMENT,l_log_module);
2888 ---------------------------------------------------------------------
2889 END IF;
2890
2891 -- Select entries to transfer
2892 select_journal_entries;
2893
2894 -- Proceed further only if there are records to process.
2895 IF g_proceed = 'Y' THEN
2896 --
2897 -- Create a log entry
2898 --
2899 insert_transfer_log(g_primary_ledgers_tab(i).ledger_id);
2900 --
2901 -- Populate the GL_INTERFACE table
2902 --
2903 insert_gl_interface;
2904 IF g_disable_gllezl_flag = 'N' THEN
2905 IF (get_gllezl_status) THEN
2906 insert_interface_control
2907 (p_ledger_id => g_primary_ledgers_tab(i).ledger_id
2908 ,p_table_name => g_gl_interface_table_name
2909 );
2910
2911 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2912 trace('Calling PSA_FUNDS_CHECKER_PKG',C_LEVEL_STATEMENT,l_log_module);
2913 END IF;
2914
2915 PSA_FUNDS_CHECKER_PKG.populate_group_id
2916 (p_grp_id => g_primary_ledgers_tab(i).group_id
2917 ,p_application_id => g_application_id
2918 ,p_je_batch_name => g_batch_name
2919 );
2920
2921 -- Submit Journal Import
2922 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'- Submitting the Journal Import');
2923 l_gllezl_request_id := submit_journal_import
2924 (p_ledger_id => g_primary_ledgers_tab(i).ledger_id
2925 ,p_interface_run_id => g_interface_run_id
2926 );
2927
2928 IF l_gllezl_request_id > 0 THEN
2929 -- Journal Import Success
2930 g_gllezl_requests_tab(i) := l_gllezl_request_id;
2931 g_primary_ledgers_tab(i).gllezl_request_id := l_gllezl_request_id;
2932 ELSE
2933 -- Journal Import Failed
2934 recover_batch;
2935 xla_exceptions_pkg.raise_exception;
2936 END IF;
2937
2938 ELSE
2939 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2940 trace('get_gllezl_status return false, raise exception',C_LEVEL_STATEMENT,l_log_module);
2941 END IF;
2942 recover_batch;
2943 xla_exceptions_pkg.raise_exception;
2944 END IF;
2945 END IF;
2946 ELSE
2947 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2948 trace('There are no entries to process in the ledger '|| g_all_ledgers_tab(l_ledger_id).NAME,C_LEVEL_STATEMENT,l_log_module);
2949 END IF;
2950 -- g_group_id_tab.DELETE(i); group id is needed to drop the tables
2951 END IF;
2952 END LOOP; -- primary/secondary ledgers loop
2953
2954 -- Wait for journal import to complete.
2955 IF g_proceed = 'Y' THEN -- added bug 7640725
2956 IF g_disable_gllezl_flag = 'N' AND g_gllezl_requests_tab.COUNT > 0 THEN
2957 IF (wait_for_gllezl) THEN
2958 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'- Journal Import completed ');
2959 complete_batch(p_submit_gl_post => p_submit_gl_post);
2960
2961 -- Drop GL_INTERFACE tables.
2962 --7512923 GL_INTERFACE tables will not be dropped.
2963 /*IF g_caller <> C_ACCTPROG_DOCUMENT THEN -- Document mode use GL_INTERFACE table only
2964 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
2965 LOOP
2966 IF g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL
2967 AND (g_entity_id IS NULL OR g_disable_gllezl_flag = 'N') THEN
2968 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2969 trace('Dropping table ' || 'XLA_GLT_'||g_primary_ledgers_tab(i).group_id,C_LEVEL_STATEMENT,l_log_module);
2970 END IF;
2971 GL_JOURNAL_IMPORT_PKG.drop_table('XLA_GLT_' || g_primary_ledgers_tab(i).group_id);
2972 END IF;
2973 END LOOP;
2974 END IF;*/
2975
2976 l_index := g_all_ledgers_tab.FIRST;
2977 FOR i in g_all_ledgers_tab.FIRST..g_all_ledgers_tab.LAST
2978 LOOP
2979 -- Submit Trial Balance Data Manager only if definitions exist
2980 -- for a Ledger and a JE source.
2981 --
2982 IF is_report_defn_found
2983 (p_ledger_id => g_all_ledgers_tab(l_index).ledger_id
2984 ,p_je_source_name => g_je_source_name)
2985 THEN
2986 IF g_all_ledgers_tab(l_index).gllezl_request_id IS NOT NULL THEN
2987 trace('Submitting Trial Balance Data Manager for ledger ID = ' || g_all_ledgers_tab(l_index).ledger_id,C_LEVEL_STATEMENT,l_log_module);
2988 l_req_id := FND_REQUEST.SUBMIT_REQUEST
2989 (application => 'XLA'
2990 ,program => 'XLATBDMG'
2991 ,description => NULL
2992 ,start_time => SYSDATE
2993 ,sub_request => NULL
2994 ,argument1 => g_all_ledgers_tab(l_index).ledger_id -- Foster City Corp. l_ledger_id
2995 ,argument2 => g_all_ledgers_tab(l_index).GROUP_id --l_group_id
2996 ,argument3 => NULL --l_definition_code
2997 ,argument4 => NULL --l_request_mode
2998 ,argument5 => g_je_source_name --bug#7320079 NULL --l_je_source_name
2999 ,argument6 => NULL --l_upg_batch_id
3000 );
3001 /*bug#7320079 Passed the je_source_name while spawning data manager. This helps in finding the correct
3002 application from which the data manager has been spawned. */
3003
3004 trace('Trial Balance Data Manager Request Id = ' || l_req_id,C_LEVEL_STATEMENT,l_log_module);
3005 END IF;
3006 COMMIT;
3007 END IF;
3008
3009 l_index := g_all_ledgers_tab.NEXT(l_index);
3010 IF l_index IS NULL THEN
3011 EXIT;
3012 END IF;
3013 END LOOP;
3014 END IF;
3015 ELSIF g_disable_gllezl_flag = 'Y' THEN
3016 trace('Journal Import is Disabled',C_LEVEL_STATEMENT,l_log_module);
3017 set_transfer_status;
3018 delete_transfer_log;
3019 END IF;
3020
3021 ELSE -- g_proceed flag is 'N'
3022 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3023 LOOP
3024 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3025 trace('Resetting Gl Transfer Flag to N in XLA_AE_HEADERS',C_LEVEL_STATEMENT,l_log_module);
3026 END IF;
3027 UPDATE xla_ae_headers
3028 SET group_id = NULL
3029 ,gl_transfer_status_code = 'N'
3030 ,gl_transfer_date = NULL
3031 ,program_update_date = SYSDATE
3032 ,program_id = g_program_id
3033 ,request_id = g_request_id
3034 WHERE group_id = g_primary_ledgers_tab(i).group_id;
3035 END LOOP;
3036 delete_transfer_log;
3037
3038 END IF; --end bug 7640725
3039
3040 /* IF g_total_rows_created > 0 THEN
3041 trace('The transfer process completed successfully.',C_LEVEL_STATEMENT,l_log_module);
3042 ELSE
3043 trace('There are no entries to transfer for the specified criteria.',C_LEVEL_STATEMENT,l_log_module);
3044 END IF;
3045 */
3046 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3047 trace('GL_TRANSFER_MAIN.End',C_LEVEL_PROCEDURE,l_log_module);
3048 END IF;
3049 EXCEPTION
3050 WHEN xla_exceptions_pkg.application_exception THEN
3051 RAISE;
3052 WHEN OTHERS THEN
3053 xla_exceptions_pkg.raise_message
3054 (p_location => 'xla_transfer_pkg.gl_transfer_main');
3055 END GL_TRANSFER_MAIN;
3056
3057 BEGIN
3058 -- l_log_module := C_DEFAULT_MODULE;
3059 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3060 g_log_enabled := fnd_log.test
3061 (log_level => g_log_level
3062 ,module => C_DEFAULT_MODULE);
3063
3064 IF NOT g_log_enabled THEN
3065 g_log_level := C_LEVEL_LOG_DISABLED;
3066 END IF;
3067 END XLA_TRANSFER_PKG;