[Home] [Help]
PACKAGE BODY: APPS.XLA_TRANSFER_PKG
Source
1 PACKAGE BODY XLA_TRANSFER_PKG AS
2 /* $Header: xlaaptrn.pkb 120.92.12020000.2 2012/08/16 05:37:25 sragadde 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 | 12-Aug-2009 rajose bug#8691650 Phase 2 |
79 | 01-Sep-2010 VGOPISET 10047096 Perf changes in the UPDATE of |
80 | XLA_AE_HEADERS in Select_Journal_Entries |
81 | 26-NOV-2010 Narayanan M.S. Bug#10124492 Headers with gl_transfer_sta|
82 | tus_code as 'NT' will not be reset to 'N'|
83 | or 'Y' |
84 | Bug#9839301 Modified procedure |
85 | get_ledger_options to use table |
86 | gl_access_set_assignments instead of |
87 | gl_access_sets to derive the access_set_id|
88 | 11-Mar-2011 Narayanan M.S. Bug 11855000 Funds check Enhancement |
89 | 20-May-2011 VGOPISET Bug12542507 changes for failure of this |
90 | GL Transfer post 12.2 edition changes |
91 | 28-SEP-2011 Narayanan M.S. Bug 12965313. Performance fix for GL. |
92 | Passing value 'TRUE' for the paramter |
93 | 'create_n3_index' when creating temporary|
94 | interface table. |
95 +===========================================================================*/
96 -- Constants
97
98 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
99 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
100 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
101 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
102 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
103 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
104 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
105 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_transfer_pkg';
106
107 --Added for bug 11855000 Start
108 -------------------------------------------------------------------------------
109 -- forward declarion of private procedures and functions
110 -------------------------------------------------------------------------------
111
112 FUNCTION reserve_funds
113 (
114 p_group_id number,
115 p_application_id number
116 )
117 RETURN NUMBER;
118 --Added for bug 11855000 end
119
120 -- PLSQL Data Types
121
122 TYPE t_array_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
123
124 TYPE r_ledger_rec IS RECORD
125 (ledger_id NUMBER
126 ,NAME gl_ledgers.NAME%TYPE
127 ,ledger_category_code gl_ledgers.ledger_category_code%TYPE
128 ,group_id gl_interface.group_id%TYPE
129 ,interface_run_id gl_interface_control.interface_run_id%TYPE
130 ,gllezl_request_id NUMBER
131 ,access_set_id NUMBER
132 );
133 TYPE t_array_ledgers IS TABLE OF r_ledger_rec INDEX BY BINARY_INTEGER;
134
135 --
136 -- Global Variables
137 --
138 --
139 -- Input Parameters
140 --
141 g_application_id PLS_INTEGER;
142 g_program_id PLS_INTEGER;
143 g_user_id PLS_INTEGER;
144 g_request_id PLS_INTEGER;
145 g_end_date DATE;
146 g_batch_name VARCHAR2(50);
147 g_accounting_batch_id PLS_INTEGER;
148 g_entity_id NUMBER; -- 8761772
149 g_process_category xla_event_class_grps_b.event_class_group_code%TYPE;
150 g_security_id_int_1 xla_transaction_entities.source_id_int_1%TYPE;
151 g_security_id_int_2 xla_transaction_entities.source_id_int_2%TYPE;
152 g_security_id_int_3 xla_transaction_entities.source_id_int_3%TYPE;
153 g_security_id_char_1 xla_transaction_entities.source_id_char_1%TYPE;
154 g_security_id_char_2 xla_transaction_entities.source_id_char_2%TYPE;
155 g_security_id_char_3 xla_transaction_entities.source_id_char_3%TYPE;
156 g_valuation_method xla_transaction_entities.valuation_method%TYPE;
157 g_caller VARCHAR2(80);
158
159
160 -- Batch level global variables
161 g_interface_run_id PLS_INTEGER;
162 g_je_source_name gl_je_sources.user_je_source_name%TYPE;
163 g_user_source_name gl_je_sources.user_je_source_name%TYPE;
164 g_import_key_flag gl_je_sources.import_using_key_flag%TYPE;
165 g_transfer_mode VARCHAR2(30);
166 g_primary_ledger_id PLS_INTEGER;
167 g_parent_group_id PLS_INTEGER;
168 g_transaction_security VARCHAR2(4000);
169 g_use_ledger_security VARCHAR2(1)
170 := nvl(fnd_profile.value('XLA_USE_LEDGER_SECURITY'), 'N');
171 g_disable_gllezl_flag VARCHAR2(1) := NVL(fnd_profile.value('XLA_DISABLE_GLLEZL'),'N');
172
173 -- Ledger level global variables
174 g_group_id PLS_INTEGER;
175 g_transfer_summary_mode VARCHAR2(1);
176 g_access_set_id PLS_INTEGER := fnd_profile.value('GL_ACCESS_SET_ID');
177 g_sec_access_set_id PLS_INTEGER := fnd_profile.value('XLA_GL_SECONDARY_ACCESS_SET_ID');
178 g_gl_interface_table_name VARCHAR2(30);
179 g_budgetary_control_flag gl_ledgers.enable_budgetary_control_flag%TYPE;
180 --
181 -- Flow Control Flags
182 --
183 g_proceed VARCHAR2(1) := 'Y';
184
185
186 -- Ledger Arrarys
187 g_primary_ledgers_tab t_array_ledgers; -- primary,secondary ledgers
188 g_all_ledgers_tab t_array_ledgers; -- primary,secondary, ALC
189 g_alc_ledger_id_tab t_array_ids; -- primary+ALC
190 g_ledger_id_tab t_array_ids;
191 g_gllezl_requests_tab t_array_ids;
192 g_group_id_tab t_array_ids;
193 g_all_ledger_ids_tab XLA_NUMBER_ARRAY_TYPE;
194
195 --Added for bug 11855000 Start
196
197 -- Funds check variables
198 g_packet_id number;
199 g_reserve_flag BOOLEAN := FALSE;
200
201 --Added for bug 11855000 End
202
203 -- Global variables for debugging
204 g_log_level PLS_INTEGER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
205 g_log_enabled BOOLEAN := fnd_log.test
206 (log_level => g_log_level
207 ,module => C_DEFAULT_MODULE);
208
209
210 /*===================================================================
211 print DEBUG messages
212
213 =====================================================================*/
214 PROCEDURE trace (p_msg IN VARCHAR2
215 ,p_level IN NUMBER
216 ,p_module IN VARCHAR2) IS
217 BEGIN
218
219 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
220 fnd_log.message(p_level, p_module);
221 ELSIF p_level >= g_log_level THEN
222 fnd_log.string(p_level, p_module, p_msg);
223 END IF;
224 EXCEPTION
225 WHEN xla_exceptions_pkg.application_exception THEN
226 RAISE;
227 WHEN OTHERS THEN
228 xla_exceptions_pkg.raise_message
229 (p_location => 'xla_acct_setup_pub_pkg.trace');
230 END trace;
231
232 --=============================================================================
233 -- ******* Print Log File **********
234 --=============================================================================
235 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
236 BEGIN
237
238 fnd_file.put_line(fnd_file.log,p_msg);
239
240 EXCEPTION
241 WHEN xla_exceptions_pkg.application_exception THEN
242 RAISE;
243 WHEN OTHERS THEN
244 xla_exceptions_pkg.raise_message
245 (p_location => 'xla_acct_setup_pub_pkg.print_logfile');
246 END print_logfile;
247
248
249 /*===========================================================================+
250 PROCEDURE
251 GET_GLLEZL_STATUS
252
253 DESCRIPTION
254 THE routine checkes status OF THE previously submitted journal import
255 requests.
256
257 THE FUNCTION returns FALSE IF it finds a failed JI request.
258
259 SCOPE - PRIVATE
260
261 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
262
263 ARGUMENTS
264
265
266 NOTES
267
268 +===========================================================================*/
269
270 FUNCTION get_gllezl_status
271 RETURN BOOLEAN IS
272 l_callStatus BOOLEAN;
273 l_phase VARCHAR2(30);
274 l_status VARCHAR2(30);
275 l_dev_phase VARCHAR2(30);
276 l_dev_status VARCHAR2(30);
277 l_message VARCHAR2(240);
278 l_gllezl_status gl_interface.status%TYPE;
279 l_index PLS_INTEGER := 0;
280 l_log_module VARCHAR2(240);
281
282 l_get_gllezl_status BOOLEAN := TRUE;
283
284 BEGIN
285
286 IF g_log_enabled THEN
287 l_log_module := C_DEFAULT_MODULE||'.get_gllezl_status';
288 END IF;
289 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
290 trace('get_gllezl_status.Begin',C_LEVEL_PROCEDURE,l_log_module);
291 END IF;
292
293 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
294 trace('Journal import request count = ' || g_gllezl_requests_tab.COUNT,C_LEVEL_STATEMENT,l_Log_module);
295 END IF;
296 --
297 -- Check if any previous requests failed
298 --
299 IF (g_gllezl_requests_tab.COUNT > 0) THEN
300 l_index := g_gllezl_requests_tab.FIRST;
301 FOR i IN 1..g_gllezl_requests_tab.COUNT
302 LOOP
303
304 trace('Calling fnd_concurrent.get_request_status',C_LEVEL_EVENT,l_Log_module);
305 /*
306 l_callStatus := fnd_concurrent.get_request_status
307 (request_id => g_gllezl_requests_tab(l_index)
308 ,phase => l_phase
309 ,status => l_status
310 ,dev_phase => l_dev_phase
311 ,dev_status => l_dev_status
312 ,message => l_message
313 );
314 */
315
316 -- bug#8691650 used wait for request to avoid any -ve ledger_id issue in gl tables
317
318 l_callStatus := fnd_concurrent.wait_for_request
319 (request_id => g_gllezl_requests_tab(l_index)
320 ,interval => 5
321 ,phase => l_phase
322 ,status => l_status
323 ,dev_phase => l_dev_phase
324 ,dev_status => l_dev_status
325 ,message => l_message);
326
327
328 IF (
329 l_dev_phase = 'COMPLETE' AND l_dev_status <> 'NORMAL'
330 AND l_dev_status <> 'WARNING' -- bug#8691650 dont raise exception if request is in warning
331 )
332 THEN
333 xla_accounting_err_pkg.build_message
334 (p_appli_s_name => 'XLA'
335 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
336 ,p_token_1 => 'REQUEST_ID'
337 ,p_value_1 => g_gllezl_requests_tab(l_index)
338 -- ,p_token_2 => 'LEDGER_NAME'
339 -- ,p_value_2 => g_primary_ledgers_tab(i).name
340 ,p_entity_id => NULL
341 ,p_event_id => NULL
342 );
343
344 l_get_gllezl_status := FALSE; -- bug#8691650 wait for the loop to complete
345 --RETURN FALSE;
346
347 END IF;
348
349 l_index := g_gllezl_requests_tab.NEXT(l_index);
350 END LOOP;
351
352 RETURN l_get_gllezl_status; --added bug#8691650
353
354 ELSE
355
356 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
357 trace('There are no journal import requets submitted at this time.',C_LEVEL_STATEMENT,l_Log_module);
358 END IF;
359
360 END IF;
361
362 --RETURN TRUE;
363
364 RETURN l_get_gllezl_status; --added bug#8691650
365
366 EXCEPTION
367 WHEN xla_exceptions_pkg.application_exception THEN
368 RAISE;
369 WHEN OTHERS THEN
370 xla_exceptions_pkg.raise_message
371 (p_location => 'xla_transfer_pkg.get_gllezl_status');
372 END get_gllezl_status;
373
374 --Added for bug 11855000 Start
375 /*----------------------------------------------------------------------
376 Function Reserve_funds
377 This function inserts data in gl_bc_packets and on success
378 returns a positive packet_id.
379 */----------------------------------------------------------------------
380
381 FUNCTION reserve_funds
382 (p_group_id number,
383 p_application_id number)
384 RETURN NUMBER IS
385
386
387 CURSOR c_get_arrival_seq IS
388 SELECT gl_bc_packet_arrival_order_s.nextval
389 FROM dual;
390
391
392 CURSOR c_get_packet_id IS
393 SELECT gl_bc_packets_s.NEXTVAL
394 FROM dual;
395
396 --local Variables
397 l_log_module VARCHAR2(240);
398 l_packet_id NUMBER := 0;
399 l_bc_count NUMBER := 0;
400 l_arrival_seq NUMBER := 0;
401 l_ledger_category gl_ledgers.ledger_category_code%type;
402
403
404 BEGIN
405 IF g_log_enabled THEN
406 l_log_module := C_DEFAULT_MODULE||'.reserve_funds';
407 END IF;
408 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
409 trace('reserve_funds.Begin',C_LEVEL_PROCEDURE,l_Log_module);
410 END IF;
411
412 --- Print Parameter values
413 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
414 trace('p_group_id = ' || p_group_id
415 ,C_LEVEL_STATEMENT
416 ,l_log_module);
417 trace('p_application_id = ' || p_application_id
418 ,C_LEVEL_STATEMENT
419 ,l_log_module);
420 END IF;
421
422
423 --
424 -- Get arrival Sequence
425 --
426 OPEN c_get_arrival_seq;
427 FETCH c_get_arrival_seq INTO l_arrival_seq;
428
429 IF c_get_arrival_seq%FOUND THEN
430 CLOSE c_get_arrival_seq;
431
432 IF (C_LEVEL_EVENT >= g_log_level) THEN
433 trace('l_arrival_seq is = ' || l_arrival_seq
434 ,C_LEVEL_STATEMENT
435 ,l_log_module);
436 END IF;
437
438 else
439 CLOSE c_get_arrival_seq;
440 l_packet_id := -1;
441 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
442 fnd_message.set_token('SEQUENCE', 'GL_BC_PACKETS_S');
443 app_exception.raise_exception;
444 END IF;
445
446 --
447 -- Get the packet id
448 --
449 OPEN c_get_packet_id;
450 FETCH c_get_packet_id INTO l_packet_id;
451
452 IF c_get_packet_id%FOUND THEN
453 CLOSE c_get_packet_id;
454
455
456 IF (C_LEVEL_EVENT >= g_log_level) THEN
457 trace('l_packet_id is = ' || l_packet_id
458 ,C_LEVEL_STATEMENT
459 ,l_log_module);
460 END IF;
461
462 --
463 -- Insert data into gl_bc_packets
464 --
465 FOR i in g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
466 loop
467
468 SELECT enable_budgetary_control_flag, ledger_category_code
469 INTO g_budgetary_control_flag, l_ledger_category
470 FROM gl_ledgers led
471 WHERE led.ledger_id = g_ledger_id_tab(i);
472
473 IF (C_LEVEL_EVENT >= g_log_level) THEN
474 trace('Ledger_id in loop is = ' || g_ledger_id_tab(i)
475 ,C_LEVEL_STATEMENT
476 ,l_log_module);
477 trace('g_budgetary_control_flag is = ' || g_budgetary_control_flag
478 ,C_LEVEL_STATEMENT
479 ,l_log_module);
480 trace('Ledger_category_code is = ' || l_ledger_category
481 ,C_LEVEL_STATEMENT
482 ,l_log_module);
483 END IF;
484
485 if g_budgetary_control_flag = 'Y' AND l_ledger_category <> 'ALC' then
486 insert into gl_bc_packets
487 (PACKET_ID
488 ,APPLICATION_ID
489 ,LEDGER_ID
490 ,JE_SOURCE_NAME
491 ,JE_CATEGORY_NAME
492 ,CODE_COMBINATION_ID
493 ,ACTUAL_FLAG
494 ,PERIOD_NAME
495 ,PERIOD_YEAR
496 ,PERIOD_NUM
497 ,QUARTER_NUM
498 ,CURRENCY_CODE
499 ,STATUS_CODE
500 ,LAST_UPDATE_DATE
501 ,LAST_UPDATED_BY
502 ,ENCUMBRANCE_TYPE_ID
503 ,BUDGET_VERSION_ID
504 ,ENTERED_DR
505 ,ENTERED_CR
506 ,ACCOUNTED_DR
507 ,ACCOUNTED_CR
508 ,EVENT_ID
509 ,AE_HEADER_ID
510 ,AE_LINE_NUM
511 ,SESSION_ID
512 ,SERIAL_ID
513 ,BC_DATE
514 ,group_id
515 ,entity_id
516 ,effect_on_funds_code
517 )
518 SELECT
519 l_packet_id
520 , p_application_id
521 , xah.ledger_id
522 , xs.je_source_name
523 , xah.je_category_name
524 , xal.code_combination_id
525 , xah.balance_type_code
526 , xah.period_name
527 , gps.period_year
528 , gps.period_num
529 , gps.quarter_num
530 , xal.currency_code
531 , 'C'
532 , sysdate
533 , xla_environment_pkg.g_usr_id
534 , xal.encumbrance_type_id
535 , xah.budget_version_id
536 , xal.entered_dr
537 , xal.entered_cr
538 , xal.accounted_dr
539 , xal.accounted_cr
540 , xah.event_id
541 , xal.ae_header_id
542 , xal.ae_line_num
543 , ses.sid
544 , ses.serial#
545 , xah.accounting_date
546 , xah.group_id
547 , xah.entity_id
548 , Case
549 When xal.accounted_dr is not null Then 'D'
550 Else 'I'
551 END
552 FROM xla_ae_headers xah
553 , xla_ae_lines xal
554 , xla_subledgers xs
555 , gl_period_statuses gps
556 , v$session ses
557 WHERE xal.application_id = xah.application_id
558 AND xal.ae_header_id = xah.ae_header_id
559 AND xs.application_id = xah.application_id
560 AND gps.application_id = 101
561 AND gps.ledger_id = xah.ledger_id
562 AND gps.period_name = xah.period_name
563 AND ses.audsid = userenv('SESSIONID')
564 and xah.group_id = p_group_id
565 and xah.gl_transfer_status_code = 'S'
566 and xah.application_id = p_application_id
567 and xah.ledger_id = g_ledger_id_tab(i)
568 and xah.funds_status_code is null
569 and xah.balance_type_code in ('A', 'E')
570 and exists
571 (select 1 from xla_events xe
572 where xe.entity_id = xah.entity_id
573 and xe.application_id = xah.application_id
574 and xe.budgetary_control_flag = 'Y')
575 ;
576 l_bc_count := SQL%ROWCOUNT;
577
578 IF (C_LEVEL_EVENT >= g_log_level) THEN
579 trace(p_msg => 'Num of rows inserted: '|| l_bc_count,
580 p_module => l_log_module,
581 p_level => C_LEVEL_EVENT);
582 END IF;
583
584
585 --Insert data into gl_bc_packet_arrival_order
586
587 IF (NVL(l_bc_count,0) > 0) THEN
588 insert into gl_bc_packet_arrival_order
589 (
590 packet_id,
591 ledger_id,
592 arrival_seq,
593 affect_funds_flag,
594 last_update_date,
595 last_updated_by
596 )
597 values
598 (
599 l_packet_id,
600 g_ledger_id_tab(i),
601 l_arrival_seq,
602 'Y', -- FC mode will be 'C' for funds check, 'R' for reserve. For reserve, affect_funds_flag should be set to 'Y'
603 sysdate,
604 g_user_id
605 )
606 ;
607 IF (C_LEVEL_EVENT >= g_log_level) THEN
608 trace(p_msg => 'Num of rows inserted into gl_bc_packet_arrival_order table: '|| SQL%ROWCOUNT,
609 p_module => l_log_module,
610 p_level => C_LEVEL_EVENT);
611 END IF;
612 end if;
613
614 IF (NVL(l_bc_count,0) > 0) THEN
615
616 -- update xla_ae_headers as funds reserved
617 g_reserve_flag := true;
618
619 update xla_ae_headers xah
620 set xah.funds_status_code = 'S'
621 where xah.ledger_id = g_ledger_id_tab(i)
622 and xah.application_id = p_application_id
623 and xah.group_id = p_group_id
624 and xah.gl_transfer_status_code = 'S'
625 and xah.funds_status_code is null
626 and xah.balance_type_code in ('A', 'E')
627 and exists
628 (
629 select 1 from gl_bc_packets glc
630 where glc.group_id = xah.group_id
631 and glc.ae_header_id = xah.ae_header_id
632 and glc.packet_id = l_packet_id
633 and glc.event_id = xah.event_id
634 and glc.application_id = xah.application_id
635 )
636 ;
637
638 IF (C_LEVEL_EVENT >= g_log_level) THEN
639 trace('No of headers updated as funds reserved = ' || SQL%ROWCOUNT
640 ,C_LEVEL_STATEMENT
641 ,l_log_module);
642 END IF;
643 --update the funding budget version_id
644 UPDATE gl_bc_packets bp
645 SET bp.funding_budget_version_id =
646 (SELECT DECODE(pk.actual_flag, 'B', pk.budget_version_id, bo.funding_budget_version_id)
647 FROM gl_budget_assignments ba,
648 gl_budgets b ,
649 gl_budget_versions bv ,
650 gl_period_statuses ps ,
651 gl_bc_packets pk ,
652 gl_budorg_bc_options bo
653 WHERE ba.ledger_id(+) = g_ledger_id_tab(i)
654 AND ba.currency_code(+) = DECODE(PK.currency_code, 'STAT', 'STAT', pk.currency_code)
655 AND ba.code_combination_id (+) = PK.code_combination_id
656 AND bo.range_id(+) = ba.range_id
657 AND bo.funding_budget_version_id = bv.budget_version_id
658 AND bv.budget_name = b.budget_name
659 AND ((b.budget_type = 'payment'
660 AND pk.actual_flag IN ('P', 'F')
661 )
662 OR (b.budget_type = 'standard'
663 AND pk.actual_flag NOT IN ('P', 'F')
664 )
665 )
666 AND ps.application_id = 101
667 AND ps.ledger_id = g_ledger_id_tab(i)
668 AND ps.period_name = pk.period_name
669 AND ps.start_date >=
670 (SELECT p1.start_date
671 FROM gl_period_statuses p1
672 WHERE p1.period_name = b.first_valid_period_name
673 AND p1.application_id = ps.application_id
674 AND p1.ledger_id = ps.ledger_id
675 )
676 AND ps.end_date <=
677 (SELECT p2.end_date
678 FROM gl_period_statuses p2
679 WHERE p2.period_name = b.last_valid_period_name
680 AND p2.application_id = ps.application_id
681 AND p2.ledger_id = ps.ledger_id
682 )
683 AND pk.rowid = bp.rowid
684 )
685 WHERE bp.packet_id = l_packet_id
686 AND bp.template_id IS NULL
687 AND bp.funding_budget_version_id IS NULL
688 ;
689
690 IF (C_LEVEL_EVENT >= g_log_level) THEN
691 trace('No of rows updated in GL_BC_PACKETS with funding_budget_version_id = ' || SQL%ROWCOUNT
692 ,C_LEVEL_STATEMENT
693 ,l_log_module);
694 END IF;
695 else
696 l_packet_id := 0;
697 trace('No eligible headers tobe funds reserved',C_LEVEL_PROCEDURE,l_log_module);
698 END IF;
699 else
700 trace('Ledger is not BC enabled',C_LEVEL_PROCEDURE,l_log_module);
701 l_packet_id := 0;
702 end if;
703 end loop;
704
705 ELSE
706 CLOSE c_get_packet_id;
707 l_packet_id := -1;
708 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
709 fnd_message.set_token('SEQUENCE', 'GL_BC_PACKETS_S');
710 app_exception.raise_exception;
711 END IF;
712
713 RETURN l_packet_id;
714
715 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
716 trace('reserve_funds.End',C_LEVEL_PROCEDURE,l_log_module);
717 END IF;
718
719 EXCEPTION
720 WHEN xla_exceptions_pkg.application_exception THEN
721 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
722 trace('Reserve Funds failed',C_LEVEL_UNEXPECTED,l_log_module);
723 END IF;
724 RAISE;
725 WHEN OTHERS THEN
726 xla_exceptions_pkg.raise_message
727 (p_location => 'xla_transfer_pkg.reserve_funds');
728 END reserve_funds;
729
730 --Added for bug 11855000 End
731
732
733 /*===================================================================
734 | INSERT ROWS INTO THE GL_INTERFACE_CONTROL |
735 | |
736 =====================================================================*/
737 PROCEDURE insert_interface_control(p_ledger_id NUMBER
738 ,p_table_name VARCHAR2) IS
739
740 l_log_module VARCHAR2(240);
741 BEGIN
742 IF g_log_enabled THEN
743 l_log_module := C_DEFAULT_MODULE||'.insert_interface_control';
744 END IF;
745 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
746 trace('insert_interface_control.Begin',C_LEVEL_PROCEDURE,l_Log_module);
747 END IF;
748
749 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
750 print_logfile ('p_ledger_id = ' || p_ledger_id);
751 print_logfile ('g_group_id = ' || g_group_id);
752 print_logfile ('g_budgetary_control_flag = ' || g_budgetary_control_flag);
753
754 trace('g_budgetary_control_flag:',C_LEVEL_STATEMENT,l_Log_module);
755 END IF;
756
757 --8429053
758 --Passing NULL for GL_INTERFACE table so data will not be
759 --retained in GL_INTERFACE table with PROCESSED status
760
761 INSERT INTO gl_interface_control
762 (
763 je_source_name,
764 status,
765 interface_run_id,
766 group_id,
767 set_of_books_id,
768 packet_id,
769 interface_table_name,
770 processed_table_code
771 )
772 VALUES
773 (
774 g_je_source_name,
775 'S',
776 g_interface_run_id,
777 g_group_id,
778 -1,
779 Decode(g_budgetary_control_flag, 'N', NULL, -3),
780 p_table_name,
781 'S' --8429053, 8691650 Commented
782 --decode(g_gl_interface_table_name, 'GL_INTERFACE',NULL,'S') --8429053
783 );
784
785 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
786 trace(SQL%ROWCOUNT|| ' rows inserted into the interface control table' ,C_LEVEL_STATEMENT,l_Log_module);
787 END IF;
788
789 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
790 trace('insert_interface_control.End',C_LEVEL_PROCEDURE,l_Log_module);
791 END IF;
792
793 EXCEPTION
794 WHEN xla_exceptions_pkg.application_exception THEN
795 RAISE;
796 WHEN OTHERS THEN
797 trace('Insert into the GL_INTERFACE_CONTROL failed',C_LEVEL_UNEXPECTED,l_Log_module);
798 xla_exceptions_pkg.raise_message
799 (p_location => 'xla_transfer_pkg.get_gllezl_status');
800
801 END insert_interface_control;
802 /*===========================================================================+
803 PROCEDURE
804 CREATE_LOG_ENTRS
805
806 DESCRIPTION
807 THE PROCEDURE creates log ENTRY FOR EACH PRIMARY AND secondary ledger.
808
809
810 SCOPE - PRIVATE
811
812 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
813
814 ARGUMENTS
815 p_ledger_id - PRIMARY/secondary ledger identifier.
816
817
818 NOTES
819
820 +===========================================================================*/
821
822 PROCEDURE insert_transfer_log ( p_ledger_id NUMBER) IS
823
824 l_log_module VARCHAR2(240);
825 BEGIN
826 IF g_log_enabled THEN
827 l_log_module := C_DEFAULT_MODULE||'.insert_transfer_log';
828 END IF;
829 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
830 trace('insert_transfer_log.Begin',C_LEVEL_PROCEDURE,l_Log_module);
831 END IF;
832
833 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
834 trace('Inserting a row into the transfer to GL log table.',C_LEVEL_STATEMENT,l_Log_module);
835 END IF;
836
837 INSERT INTO xla_transfer_logs
838 (
839 application_id
840 ,ledger_id
841 ,parent_group_id
842 ,group_id
843 ,transfer_status_code
844 ,CREATION_DATE
845 ,CREATED_BY
846 ,LAST_UPDATE_DATE
847 ,LAST_UPDATED_BY
848 ,LAST_UPDATE_LOGIN
849 ,PROGRAM_UPDATE_DATE
850 ,PROGRAM_APPLICATION_ID
851 ,PROGRAM_ID
852 ,REQUEST_ID
853 )
854 VALUES
855 (
856 g_application_id
857 ,p_ledger_id
858 ,g_parent_group_id
859 ,g_group_id
860 ,'INCOMPLETE' -- Incomplete
861 ,SYSDATE
862 ,g_user_id
863 ,SYSDATE
864 ,xla_environment_pkg.g_usr_id
865 ,xla_environment_pkg.g_login_id
866 ,SYSDATE
867 ,xla_environment_pkg.g_prog_appl_id
868 ,xla_environment_pkg.g_prog_id
869 ,xla_environment_pkg.g_Req_Id
870 );
871
872 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
873 trace('insert_transfer_log.End',C_LEVEL_PROCEDURE,l_Log_module);
874 END IF;
875
876
877 EXCEPTION
878 WHEN xla_exceptions_pkg.application_exception THEN
879 RAISE;
880 WHEN OTHERS THEN
881 xla_exceptions_pkg.raise_message
882 (p_location => 'xla_transfer_pkg.insert_transfer_log');
883 END insert_transfer_log;
884
885
886 /*===========================================================================+
887 FUNCTION
888 SUBMIT_JOURNAL_IMPORT
889
890 DESCRIPTION
891 THE PROCEDURE handles THE Journal Import submission.
892
893
894 SCOPE - PRIVATE
895
896 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
897
898 ARGUMENTS
899
900
901 NOTES
902
903 +===========================================================================*/
904 FUNCTION submit_journal_import (p_ledger_id IN NUMBER
905 ,p_interface_run_id IN NUMBER
906 ) RETURN NUMBER IS
907 l_gllezl_request_id NUMBER;
908 l_summary_flag VARCHAR2(1);
909 l_log_module VARCHAR2(240);
910 BEGIN
911 IF g_log_enabled THEN
912 l_log_module := C_DEFAULT_MODULE||'.submit_journal_import';
913 END IF;
914 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
915 trace('submit_journal_import.Begin',C_LEVEL_PROCEDURE,l_Log_module);
916 END IF;
917
918 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
919 trace('p_ledger_id = ' || p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
920 trace('p_interface_run_id = ' || p_interface_run_id,C_LEVEL_STATEMENT,l_Log_module);
921 END IF;
922
923
924 IF g_transfer_summary_mode IN ('A','P') THEN
925 l_summary_flag := 'Y';
926 ELSE
927 l_summary_flag := 'N';
928 END IF;
929
930 l_gllezl_request_id:= fnd_request.submit_request
931 (
932 application => 'SQLGL', -- application short name
933 program => 'GLLEZL', -- program short name
934 description => NULL, -- program name
935 start_time => NULL, -- start date
936 sub_request => FALSE, -- sub-request
937 argument1 => p_interface_run_id, -- interface run id
938 argument2 => -602, -- set of books id
939 argument3 => 'N', -- error to suspense flag
940 argument4 => NULL, -- from accounting date
941 argument5 => NULL, -- to accounting date
942 argument6 => l_summary_flag, -- create summary flag
943 argument7 => 'N', -- import desc flex flag
944 argument8 => 'Y' -- Data security mode flag
945 );
946
947 IF NVL(l_gllezl_request_id,0) = 0 THEN
948 IF (C_LEVEL_ERROR >= g_log_level) THEN
949 trace('Unable to submit the Journal Import',C_LEVEL_ERROR,l_Log_module);
950 END IF;
951
952 -- Add an error message.
953 xla_accounting_err_pkg.build_message
954 (p_appli_s_name => 'XLA'
955 ,p_msg_name => 'XLA_GLT_GLLEZL_SUBMIT_FAILED'
956 ,p_token_1 => 'LEDGER_NAME'
957 ,p_value_1 => g_all_ledgers_tab(p_ledger_id).NAME
958 ,p_entity_id => NULL
959 ,p_event_id => NULL
960 );
961 ELSE
962 IF (g_log_enabled AND C_LEVEL_EVENT >= g_log_level) THEN
963 trace('The Journal Import has been submitted successfully. Request Id = ' || l_gllezl_request_id,C_LEVEL_EVENT,l_Log_module);
964 END IF;
965
966 --
967 -- Journal Import is submitted successfully.
968 --
969 g_all_ledgers_tab(p_ledger_id).gllezl_request_id
970 := l_gllezl_request_id;
971
972 -- Populate GLLEZL request ID for ALC ledgers
973 IF g_all_ledgers_tab(p_ledger_id).ledger_category_code = 'PRIMARY' THEN
974 FOR i IN g_alc_ledger_id_tab.FIRST..g_alc_ledger_id_tab.LAST
975 LOOP
976 g_all_ledgers_tab(g_alc_ledger_id_tab(i)).gllezl_request_id := l_gllezl_request_id;
977 trace('GLLEZL Request Id = ' || g_all_ledgers_tab(g_alc_ledger_id_tab(i)).gllezl_request_id,C_LEVEL_EVENT,l_Log_module);
978 END LOOP;
979 END IF;
980 g_gllezl_requests_tab(p_ledger_id) := l_gllezl_request_id;
981
982 UPDATE xla_transfer_logs
983 SET gllezl_request_id = l_gllezl_request_id
984 WHERE group_id = g_group_id;
985 END IF;
986 COMMIT;
987
988 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
989 trace('submit_journal_import.End',C_LEVEL_PROCEDURE,l_Log_module);
990 END IF;
991
992 RETURN(l_gllezl_request_id);
993
994
995 EXCEPTION
996 WHEN xla_exceptions_pkg.application_exception THEN
997 RAISE;
998 WHEN OTHERS THEN
999 xla_exceptions_pkg.raise_message
1000 (p_location => 'xla_transfer_pkg.submit_journal_import');
1001 END submit_journal_import;
1002
1003
1004 /*====================================================================
1005 Get ledgers associated WITH THE PRIMARY ledger
1006
1007 =====================================================================*/
1008 PROCEDURE get_ledgers (p_ledger_id IN NUMBER) IS
1009
1010 CURSOR c_getledgers(p_ledger_id NUMBER
1011 ,p_application_id NUMBER ) IS
1012 SELECT ledger_id
1013 ,NAME
1014 ,ledger_category_code
1015 FROM xla_ledger_relationships_v xlr
1016 WHERE xlr.primary_ledger_id = p_ledger_id
1017 AND xlr.relationship_enabled_flag = 'Y'
1018 AND EXISTS (SELECT 1
1019 FROM xla_ledger_options xlo
1020 WHERE application_id = p_application_id
1021 AND DECODE(xlr.ledger_category_code
1022 ,'ALC',xlr.ledger_id
1023 ,xlo.ledger_id) = xlr.ledger_id
1024 AND DECODE(xlr.ledger_category_code
1025 ,'SECONDARY',xlo.capture_event_flag
1026 ,'N') = 'N'
1027 AND DECODE(xlr.ledger_category_code
1028 ,'ALC','Y'
1029 ,xlo.enabled_flag) = 'Y')
1030 ORDER BY DECODE(xlr.ledger_category_code,
1031 'PRIMARY',1,
1032 'ALC',2
1033 ,3);
1034
1035 l_ledger_name gl_ledgers.NAME%TYPE;
1036 l_count PLS_INTEGER := 0;
1037 l_alc_count PLS_INTEGER := 0;
1038 l_log_module VARCHAR2(240);
1039 l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
1040
1041
1042 BEGIN
1043 IF g_log_enabled THEN
1044 l_log_module := C_DEFAULT_MODULE||'.get_ledgers';
1045 END IF;
1046 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1047 trace('get_ledgers.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1048 END IF;
1049
1050 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1051 trace('p_ledger_id = ' ||p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
1052 END IF;
1053
1054
1055 SELECT NAME
1056 ,ledger_category_code
1057 ,enable_budgetary_control_flag
1058 INTO l_ledger_name
1059 ,l_ledger_category_code
1060 ,g_budgetary_control_flag
1061 FROM gl_ledgers led
1062 WHERE led.ledger_id = p_ledger_id;
1063
1064
1065 --
1066 -- If the transfer is submitted for a primary ledger then derive
1067 -- all associated ledgers for processing. If the transfer is submitted
1068 -- for a secondary ledger (For VM based products only) then process only
1069 -- the secondary ledger.
1070 --
1071
1072 IF (l_ledger_category_code = 'PRIMARY') THEN
1073 g_primary_ledger_id := p_ledger_id;
1074 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1075 trace('Deriving ledgers associated with the primary ledger',C_LEVEL_STATEMENT,l_Log_module);
1076 END IF;
1077
1078 FOR ledger_rec IN c_getledgers(p_ledger_id,g_application_id)
1079 LOOP
1080 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1081 trace('Ledger Rec Name = '||ledger_rec.NAME,C_LEVEL_STATEMENT,l_Log_module);
1082 trace('Ledger Rec id = ' ||ledger_rec.ledger_id,C_LEVEL_STATEMENT,l_Log_module);
1083 END IF;
1084
1085 g_all_ledgers_tab(ledger_rec.ledger_id).ledger_id := ledger_rec.ledger_id;
1086 g_all_ledgers_tab(ledger_rec.ledger_id).NAME := ledger_rec.NAME;
1087 g_all_ledgers_tab(ledger_rec.ledger_id).ledger_category_code
1088 := ledger_rec.ledger_category_code;
1089
1090 IF (ledger_rec.ledger_category_code IN ('PRIMARY','SECONDARY')) THEN
1091 l_count := l_count+1;
1092 g_primary_ledgers_tab(l_count).ledger_id := ledger_rec.ledger_id;
1093 g_primary_ledgers_tab(l_count).NAME := ledger_rec.NAME;
1094 g_primary_ledgers_tab(l_count).ledger_category_code
1095 := ledger_rec.ledger_category_code;
1096 END IF;
1097
1098 IF (ledger_rec.ledger_category_code IN ('ALC','PRIMARY')) THEN
1099 l_alc_count := l_alc_count+1;
1100 g_alc_ledger_id_tab(l_alc_count) := ledger_rec.ledger_id;
1101 END IF;
1102 END LOOP;
1103 ELSIF (l_ledger_category_code = 'SECONDARY') THEN
1104 l_count := l_count+1;
1105 g_primary_ledgers_tab(l_count).ledger_id := p_ledger_id;
1106 g_all_ledgers_tab(p_ledger_id).ledger_id := p_ledger_id;
1107 g_all_ledgers_tab(p_ledger_id).NAME := l_ledger_name;
1108 ELSE
1109 IF (C_LEVEL_ERROR >= g_log_level) THEN
1110 trace('Invalid ledger. A ledger must be either a primary or a secondary ledger',C_LEVEL_PROCEDURE,l_Log_module);
1111 END IF;
1112
1113 -- Add error message
1114 END IF;
1115
1116 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1117 trace('Total number of ledgers selected = '|| g_all_ledgers_tab.COUNT,C_LEVEL_PROCEDURE,l_Log_module);
1118 trace('get_ledgers.End',C_LEVEL_PROCEDURE,l_Log_module);
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN xla_exceptions_pkg.application_exception THEN
1123 RAISE;
1124 WHEN OTHERS THEN
1125 xla_exceptions_pkg.raise_message
1126 (p_location => 'xla_transfer_pkg.get_ledgers');
1127 END get_ledgers;
1128
1129 /*===========================================================================+
1130 | PROCEDURE |
1131 | GET_LEDGER_OPTIONS |
1132 | |
1133 | DESCRIPTION |
1134 | Derive ledger LEVEL options |
1135 | are called FROM FROM this PROCEDURE. |
1136 | |
1137 | SCOPE - PRIVATE |
1138 | |
1139 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1140 | |
1141 | ARGUMENTS |
1142 | p_ledger_id PRIMARY/Secondary ledger id |
1143 | |
1144 | NOTES |
1145 | |
1146 +===========================================================================*/
1147
1148 PROCEDURE get_ledger_options(p_ledger_id IN NUMBER) IS
1149 l_access_set_id NUMBER;
1150 l_log_module VARCHAR2(240);
1151 BEGIN
1152 IF g_log_enabled THEN
1153 l_log_module := C_DEFAULT_MODULE||'.get_ledger_options';
1154 END IF;
1155
1156 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1157 trace('get_ledger_options.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1158 trace('p_ledger_id = ' || p_ledger_id,C_LEVEL_STATEMENT,l_Log_module);
1159 END IF;
1160
1161 BEGIN
1162 SELECT xlo.transfer_to_gl_mode_code
1163 INTO g_transfer_summary_mode
1164 FROM xla_ledger_options xlo
1165 WHERE xlo.application_id = g_application_id
1166 AND xlo.ledger_id = p_ledger_id;
1167 EXCEPTION
1168 WHEN NO_DATA_FOUND THEN
1169 xla_exceptions_pkg.raise_message
1170 (p_appli_s_name => 'XLA'
1171 ,p_msg_name => 'XLA_COMMON_ERROR'
1172 ,p_token_1 => 'ERROR'
1173 ,p_value_1 => 'The ledger setup is not complete. Please run Update Subledger Accounting Options program for your application '||
1174 'ledger_id = '||p_ledger_id||
1175 ' application_id = '|| g_application_id
1176 ,p_token_2 => 'LOCATION'
1177 ,p_value_2 => 'xla_events_pkg.get_ledger_options');
1178 END;
1179
1180 -- Derive access set id based on the use ledger security option
1181 --
1182 IF (g_use_ledger_security = 'Y') THEN
1183 IF (g_access_set_id IS NOT NULL OR g_sec_access_set_id IS NOT NULL) THEN
1184 BEGIN
1185 --Added for bug 9839301
1186 /*SELECT access_set_id
1187 INTO l_access_set_id
1188 FROM gl_access_sets aset, gl_ledgers led
1189 WHERE aset.chart_of_accounts_id = led.chart_of_accounts_id
1190 AND led.ledger_id = p_ledger_id
1191 AND aset.access_set_id IN (g_access_set_id, g_sec_access_set_id)
1192 AND ROWNUM = 1;*/
1193 SELECT access_set_id
1194 INTO l_access_set_id
1195 FROM gl_access_set_assignments gasa
1196 WHERE gasa.ledger_id = p_ledger_id
1197 AND gasa.access_set_id IN (g_access_set_id, g_sec_access_set_id)
1198 AND ROWNUM = 1;
1199 --Added for bug 9839301
1200 EXCEPTION
1201 WHEN NO_DATA_FOUND THEN
1202 xla_exceptions_pkg.raise_message
1203 (p_appli_s_name => 'XLA'
1204 ,p_msg_name => 'XLA_COMMON_ERROR'
1205 ,p_token_1 => 'ERROR'
1206 ,p_value_1 => 'Access set Id not found for the ledger ID = '
1207 ||p_ledger_id
1208 ,p_token_2 => 'LOCATION'
1209 ,p_value_2 => 'xla_events_pkg.get_ledger_options');
1210 trace('Access set Id not found.',C_LEVEL_STATEMENT,l_Log_module);
1211
1212 END;
1213 END IF;
1214 ELSE
1215 SELECT implicit_access_set_id
1216 INTO l_access_set_id
1217 FROM gl_ledgers led
1218 WHERE led.ledger_id = p_ledger_id;
1219 END IF;
1220
1221 g_all_ledgers_tab(p_ledger_id).access_set_id := l_access_set_id;
1222
1223 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1224 trace('g_transfer_summary_mode = ' || g_transfer_summary_mode,C_LEVEL_STATEMENT,l_Log_module);
1225 trace('l_access_set_id = ' || l_access_set_id,C_LEVEL_STATEMENT,l_Log_module);
1226 END IF;
1227
1228 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1229 trace('get_ledger_options.End',C_LEVEL_PROCEDURE,l_Log_module);
1230 END IF;
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 trace('Error deriving subledger options for the ledger',C_LEVEL_UNEXPECTED,l_Log_module);
1234 xla_exceptions_pkg.raise_message
1235 (p_location => 'xla_transfer_pkg.get_ledger_options');
1236 END get_ledger_options;
1237
1238 /*====================================================================
1239 Populate ALC ledgers
1240
1241 *====================================================================*/
1242
1243 PROCEDURE get_alc_ledgers IS
1244 l_log_module VARCHAR2(240);
1245 BEGIN
1246 IF g_log_enabled THEN
1247 l_log_module := C_DEFAULT_MODULE||'.get_alc_ledgers';
1248 END IF;
1249 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1250 trace('get_alc_ledgers.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1251 END IF;
1252
1253 g_ledger_id_tab := g_alc_ledger_id_tab;
1254
1255 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1256 trace('get_alc_ledgers.End',C_LEVEL_PROCEDURE,l_Log_module);
1257 END IF;
1258
1259 EXCEPTION
1260 WHEN xla_exceptions_pkg.application_exception THEN
1261 RAISE;
1262 WHEN OTHERS THEN
1263 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
1264 trace('Error assigning ALC ledgers',C_LEVEL_UNEXPECTED,l_Log_module);
1265 END IF;
1266 xla_exceptions_pkg.raise_message
1267 (p_location => 'xla_transfer_pkg.get_alc_ledgers');
1268 END get_alc_ledgers;
1269
1270 /*===========================================================================+
1271 | PROCEDURE |
1272 | set_transaction_security |
1273 | |
1274 | DESCRIPTION |
1275 | Dynamically build THE TRANSACTION security clause based ON |
1276 | input PARAMETERS |
1277 | |
1278 | SCOPE - PRIVATE |
1279 | |
1280 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1281 | |
1282 | ARGUMENTS |
1283 | |
1284 | NOTES |
1285 | |
1286 +===========================================================================*/
1287 PROCEDURE set_transaction_security IS
1288 l_log_module VARCHAR2(240);
1289 BEGIN
1290 IF g_log_enabled THEN
1291 l_log_module := C_DEFAULT_MODULE||'.set_transaction_security';
1292 END IF;
1293
1294 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1295 trace('set_transaction_security.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1296 END IF;
1297
1298
1299 --
1300 -- Check if security has been specified
1301 --
1302
1303 IF ( g_security_id_int_1 IS NOT NULL OR
1304 g_security_id_int_2 IS NOT NULL OR
1305 g_security_id_int_3 IS NOT NULL OR
1306 g_security_id_char_1 IS NOT NULL OR
1307 g_security_id_char_2 IS NOT NULL OR
1308 g_security_id_char_3 IS NOT NULL OR
1309 g_valuation_method IS NOT NULL) THEN
1310
1311 -- Security info has been provided.
1312 g_transaction_security := NULL;
1313
1314 -- commented bug 14307411
1315 /*IF (g_security_id_int_1 IS NOT NULL) THEN
1316 g_transaction_security := ' AND xte.security_id_int_1 = ' || g_security_id_int_1;
1317 END IF;*/
1318
1319 --added bug 14307411
1320 IF (g_security_id_int_1 IS NOT NULL) THEN
1321 select DECODE(g_application_id,707, ' AND NVL(xte.security_id_int_1,'|| g_security_id_int_1||') = ' ||g_security_id_int_1,
1322 ' AND xte.security_id_int_1 = '|| g_security_id_int_1)
1323 into g_transaction_security from dual ;
1324
1325 END IF;
1326 -- end bug 14307411
1327 IF (g_security_id_int_2 IS NOT NULL) THEN
1328 g_transaction_security := g_transaction_security ||' AND xte.security_id_int_2 = ' || g_security_id_int_2;
1329 END IF;
1330 IF (g_security_id_int_3 IS NOT NULL) THEN
1331 g_transaction_security := g_transaction_security ||' AND xte.security_id_int_3 = ' || g_security_id_int_3;
1332 END IF;
1333 IF (g_security_id_char_1 IS NOT NULL) THEN
1334 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_1 = ''' || g_security_id_char_1 || '''';
1335 END IF;
1336 IF (g_security_id_char_2 IS NOT NULL) THEN
1337 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_2 = ''' || g_security_id_char_2 || '''';
1338 END IF;
1339 IF (g_security_id_char_3 IS NOT NULL) THEN
1340 g_transaction_security := g_transaction_security ||' AND xte.security_id_char_3 = ''' || g_security_id_char_3 || '''';
1341 END IF;
1342 IF (g_valuation_method IS NOT NULL) THEN
1343 g_transaction_security := g_transaction_security ||' AND xte.valuation_method = ''' || g_valuation_method || '''';
1344 END IF;
1345 END IF;
1346
1347 --trace('g_transaction_security = ' || g_transaction_security,C_LEVEL_STATEMENT,l_Log_module);
1348 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1349 trace('set_transaction_security.End',C_LEVEL_PROCEDURE,l_Log_module);
1350 END IF;
1351
1352
1353 END set_transaction_security;
1354
1355
1356 /*====================================================================
1357 * VALIDATE input PARAMETERS
1358
1359 *====================================================================*/
1360 PROCEDURE validate_input_parameters IS
1361 l_log_module VARCHAR2(240);
1362 BEGIN
1363 IF g_log_enabled THEN
1364 l_log_module := C_DEFAULT_MODULE||'.validate_input_parameters';
1365 END IF;
1366
1367 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1368 trace('validate_input_parameters.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1369 END IF;
1370
1371
1372 -- Validate input parameters
1373
1374 CASE
1375 WHEN g_transfer_mode NOT IN ('STANDALONE','COMBINED')
1376 THEN
1377 trace('Invalid transfer mode. The transfer mode must be either Standalone or Combined.'
1378 ,C_LEVEL_ERROR,l_Log_module);
1379
1380 xla_exceptions_pkg.raise_message
1381 (p_appli_s_name => 'XLA'
1382 ,p_msg_name => 'XLA_COMMON_ERROR'
1383 ,p_token_1 => 'LOCATION'
1384 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
1385 ,p_token_2 => 'ERROR'
1386 ,p_value_2 => 'Transfer mode must be either Standalone or Combined');
1387
1388 WHEN g_caller IN (C_TP_MERGE,C_MPA_COMPLETE)
1389 AND g_accounting_batch_id IS NULL
1390 THEN
1391 trace('Accounting batch identifier must be specified.'
1392 ,C_LEVEL_ERROR
1393 ,l_log_module);
1394
1395 xla_exceptions_pkg.raise_message
1396 (p_appli_s_name => 'XLA'
1397 ,p_msg_name => 'XLA_COMMON_ERROR'
1398 ,p_token_1 => 'LOCATION'
1399 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
1400 ,p_token_2 => 'ERROR'
1401 ,p_value_2 => 'Accounting batch identifier must be specified.');
1402
1403 WHEN g_caller IN (C_ACCTPROG_DOCUMENT)
1404 AND g_entity_id IS NULL
1405 THEN
1406 trace('Entity Identifier must be specified.'
1407 ,C_LEVEL_ERROR
1408 ,l_log_module);
1409
1410 xla_exceptions_pkg.raise_message
1411 (p_appli_s_name => 'XLA'
1412 ,p_msg_name => 'XLA_COMMON_ERROR'
1413 ,p_token_1 => 'LOCATION'
1414 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
1415 ,p_token_2 => 'ERROR'
1416 ,p_value_2 => 'Entity identifier must be specified.');
1417
1418 WHEN g_transfer_mode = 'STANDALONE'
1419 AND g_caller IN (C_ACCTPROG_BATCH)
1420 AND g_end_date IS NULL
1421 THEN
1422 trace('An end date must be specified for batch accounting in standalone mode'
1423 ,C_LEVEL_ERROR,l_Log_module);
1424
1425 xla_exceptions_pkg.raise_message
1426 (p_appli_s_name => 'XLA'
1427 ,p_msg_name => 'XLA_COMMON_ERROR'
1428 ,p_token_1 => 'LOCATION'
1429 ,p_value_1 => 'xla_transfer_pkg.validate_input_parameters'
1430 ,p_token_2 => 'ERROR'
1431 ,p_value_2 => 'End date must be specified for batch accounting in Standalone mode');
1432
1433 ELSE
1434 NULL;
1435 END CASE;
1436
1437 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1438 trace('validate_input_parameters.End',C_LEVEL_PROCEDURE,l_Log_module);
1439 END IF;
1440 EXCEPTION
1441 WHEN xla_exceptions_pkg.application_exception THEN
1442 RAISE;
1443 WHEN OTHERS THEN
1444 xla_exceptions_pkg.raise_message
1445 (p_location => 'xla_transfer_pkg.validate_input_parameters');
1446 END validate_input_parameters;
1447
1448
1449 /*===========================================================================+
1450 PROCEDURE
1451 RECOVER_BATCH
1452
1453 DESCRIPTION
1454 Performs RECOVERY opration FOR THE previously failed transfer batches.
1455
1456
1457 SCOPE - PRIVATE
1458
1459 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1460
1461 ARGUMENTS
1462
1463
1464 NOTES
1465
1466 +===========================================================================*/
1467 PROCEDURE recover_batch IS
1468 l_log_module VARCHAR2(240);
1469 l_first_time_recover BOOLEAN := FALSE;
1470
1471 BEGIN
1472 IF g_log_enabled THEN
1473 l_log_module := C_DEFAULT_MODULE||'.Recover_Batch';
1474 END IF;
1475 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1476 trace('recover_batch.Begin',C_LEVEL_PROCEDURE,l_Log_module);
1477 END IF;
1478
1479 -- Check if there are any previously failed batches. Ignore request that
1480 -- are either runnning, pending or inactive.
1481 -- Phase Code: R - Running, P - Pending, I - Inactive
1482
1483 IF ( g_group_id_tab.COUNT <= 0) THEN
1484 SELECT group_id
1485 ,gllezl_request_id
1486 BULK COLLECT INTO
1487 g_group_id_tab
1488 ,g_gllezl_requests_tab
1489 FROM xla_transfer_logs xtb1
1490 WHERE application_id = g_application_id
1491 AND request_id NOT IN
1492 ( SELECT xtb.request_id
1493 FROM xla_transfer_logs xtb
1494 ,fnd_concurrent_requests fcr
1495 WHERE xtb.application_id = g_application_id
1496 AND xtb.transfer_status_code = 'INCOMPLETE'
1497 --AND xtb.gllezl_request_id IS NOT NULL
1498 AND xtb.request_id = fcr.request_id
1499 AND fcr.phase_code IN ('R','P','I'));
1500
1501 /*bug#8691650 The l_first_time_recover flag indicates that there are group id's to be recovered */
1502 IF g_group_id_tab.COUNT > 0 THEN
1503 l_first_time_recover := TRUE;
1504 END IF;
1505
1506 END IF;
1507
1508 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1509 trace('Found '|| g_group_id_tab.COUNT || ' batches to recover', C_LEVEL_STATEMENT,l_log_module);
1510 END IF;
1511
1512 IF (g_group_id_tab.COUNT > 0) THEN
1513 --Added for bug 11855000 Start
1514 --
1515 -- Delete rows from gl_bc_packets and update gl_interface control with packet_id as -1
1516 --
1517 IF g_application_id = 200 THEN
1518
1519 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1520 UPDATE xla_Ae_headers xah
1521 set funds_status_code = null
1522 where group_id = g_group_id_tab(i)
1523 and funds_status_code = 'S'
1524 and exists
1525 (
1526 select 1 from gl_bc_packets glc
1527 where glc.group_id = xah.group_id
1528 and glc.ae_header_id = xah.ae_header_id
1529 and glc.event_id = xah.event_id
1530 and glc.application_id = xah.application_id
1531 and glc.status_code = 'C'
1532 )
1533 ;
1534 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1535 trace('No of headers updated as unreserved in recover_batch = '||SQL%ROWCOUNT,C_LEVEL_STATEMENT,l_log_module);
1536 END IF;
1537
1538
1539 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1540 DELETE gl_bc_packet_arrival_order
1541 where packet_id in
1542 (select packet_id from gl_bc_packets
1543 WHERE group_id = g_group_id_tab(i)
1544 and status_code = 'C')
1545 ;
1546
1547 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1548 trace(SQL%ROWCOUNT || 'Rows deleted from the gl_bc_packet_arrival_order in recover_batch',C_LEVEL_STATEMENT,l_log_module);
1549 END IF;
1550
1551 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1552 DELETE gl_bc_packets
1553 WHERE group_id = g_group_id_tab(i)
1554 and status_code = 'C'
1555 ;
1556
1557 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1558 trace(SQL%ROWCOUNT || 'Rows deleted from the gl_bc_packets in recover_batch',C_LEVEL_STATEMENT,l_log_module);
1559 END IF;
1560 ELSE
1561 trace('Funds check performed only for application_id 200. Provided application_id is '||g_application_id,C_LEVEL_STATEMENT,l_log_module);
1562 end if;
1563 --Added for bug 11855000 end
1564
1565 --
1566 -- Reset journal entry headers
1567 --
1568 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1569 trace('Updating XLA_AE_HEADERS',C_LEVEL_STATEMENT,l_log_module);
1570 END IF;
1571 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1572 UPDATE xla_ae_headers
1573 SET group_id = NULL
1574 ,gl_transfer_status_code = 'N'
1575 ,gl_transfer_date = NULL
1576 ,program_update_date = SYSDATE
1577 ,program_id = g_program_id
1578 ,request_id = g_request_id
1579 WHERE group_id = g_group_id_tab(i)
1580 --Added for 10124492
1581 AND gl_transfer_status_code <> 'NT';
1582 --Added for 10124492
1583
1584 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1585 trace(SQL%ROWCOUNT || ' Headers updated.',C_LEVEL_STATEMENT,l_log_module);
1586 --
1587 -- Delete log entries
1588 --
1589 trace('Deleting rows from xla_transfer_logs ',C_LEVEL_STATEMENT,l_log_module);
1590 END IF;
1591 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1592 DELETE xla_transfer_logs
1593 WHERE group_id = g_group_id_tab(i);
1594
1595 --
1596 -- Delete XLA_TRANSFER_LEDGERS
1597 --
1598
1599 trace('Deleting rows from XLA_TRANSFER_LEDGERS',C_LEVEL_STATEMENT,l_log_module);
1600 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
1601 DELETE xla_transfer_ledgers
1602 WHERE group_id = g_group_id_tab(i);
1603
1604 IF SQL%NOTFOUND THEN
1605 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1606 trace('No rows found in the XLA_TRANSFER_LEDGERS table.',C_LEVEL_STATEMENT,l_log_module);
1607 END IF;
1608 ELSE
1609 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1610 trace(SQL%ROWCOUNT || 'Rows deleted from the XLA_TRANSFER_LEDGERS',C_LEVEL_STATEMENT,l_log_module);
1611 END IF;
1612 END IF;
1613
1614
1615
1616
1617 --
1618 -- Delete rows from gl_interface, GL journals
1619 --
1620
1621 /* bug#8691650 use the g_group_id_tab to delete batches which needs to be recovered */
1622 IF l_first_time_recover THEN
1623 FOR i IN g_group_id_tab.FIRST .. g_group_id_tab.LAST
1624 LOOP
1625
1626 IF (C_LEVEL_EVENT >= g_log_level) THEN
1627 trace('First time recover calling gl_journal_import_sla_pkg.delete_batches',C_LEVEL_EVENT,l_log_module);
1628 END IF;
1629
1630 gl_journal_import_sla_pkg.delete_batches
1631 (x_je_source_name => g_je_source_name
1632 ,x_group_id => g_group_id_tab(i)
1633 );
1634
1635 END LOOP;
1636
1637 END IF;
1638
1639 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
1640 LOOP
1641 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1642 trace('Looping for each group identifier ' ,C_LEVEL_STATEMENT,l_log_module);
1643 END IF;
1644 IF (g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL) THEN
1645 IF (C_LEVEL_EVENT >= g_log_level) THEN
1646 trace('Calling gl_journal_import_sla_pkg.delete_batches',C_LEVEL_EVENT,l_log_module);
1647 END IF;
1648 gl_journal_import_sla_pkg.delete_batches
1649 (x_je_source_name => g_je_source_name
1650 ,x_group_id => g_primary_ledgers_tab(i).group_id
1651 );
1652 END IF;
1653 END LOOP;
1654
1655 COMMIT;
1656 END IF;
1657
1658 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1659 trace('recover_batch.End',C_LEVEL_PROCEDURE,l_log_module);
1660 END IF;
1661
1662 EXCEPTION
1663 WHEN xla_exceptions_pkg.application_exception THEN
1664 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
1665 trace('Batch Recovery failed',C_LEVEL_UNEXPECTED,l_log_module);
1666 END IF;
1667 RAISE;
1668 WHEN OTHERS THEN
1669 xla_exceptions_pkg.raise_message
1670 (p_location => 'xla_transfer_pkg.recover_batch');
1671 END recover_batch;
1672
1673 /*====================================================================
1674 * Perform period VALIDATION IF GL IS Installed AND THE transfer IS *
1675 * submitted IN stanalone MODE. *
1676 *====================================================================*/
1677
1678 PROCEDURE validate_accounting_periods ( p_ledger_id IN NUMBER) IS
1679 TYPE t_period_name IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
1680 TYPE t_period_year IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1681
1682 l_period_name_tab t_period_name;
1683 l_period_year_tab t_period_year;
1684 l_ledger_ids_tab t_array_ids;
1685 l_budget_version_id_tab t_array_ids;
1686 l_budget_name_tab t_period_name;
1687 l_period_val_failed BOOLEAN := FALSE;
1688 l_index PLS_INTEGER;
1689 l_actual_flag xla_event_class_attrs.ALLOW_ACTUALS_FLAG%TYPE;
1690 l_budget_flag xla_event_class_attrs.ALLOW_BUDGETS_FLAG%TYPE;
1691 l_encum_flag xla_event_class_attrs.ALLOW_ENCUMBRANCE_FLAG%TYPE;
1692 l_statement VARCHAR2(4000);
1693 l_log_module VARCHAR2(240);
1694 BEGIN
1695 IF g_log_enabled THEN
1696 l_log_module := C_DEFAULT_MODULE||'.validate_accounting_periods';
1697 END IF;
1698
1699 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1700 trace('validate_accounting_periods.Begin',C_LEVEL_PROCEDURE,l_log_module);
1701 END IF;
1702 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1703 trace('Number of ledgers selected for a period validation = ' ||g_all_ledgers_tab.count,C_LEVEL_STATEMENT,l_log_module);
1704 END IF;
1705
1706 -- Populate a SQL variable to be used for casting.
1707 g_all_ledger_ids_tab := XLA_NUMBER_ARRAY_TYPE();
1708
1709 l_index := g_all_ledgers_tab.FIRST;
1710 FOR i IN 1..g_all_ledgers_tab.COUNT
1711 LOOP
1712 --trace('Ledger Id = ' || g_all_ledgers_tab(l_index).ledger_id,C_LEVEL_STATEMENT,l_log_module);
1713 g_all_ledger_ids_tab.EXTEND;
1714 g_all_ledger_ids_tab(i) := g_all_ledgers_tab(l_index).ledger_id;
1715 l_index := g_all_ledgers_tab.NEXT(l_index);
1716 END LOOP;
1717
1718 -- Get balance types allowed for an application.
1719
1720 SELECT actual_flag,budget_flag,encumbrance_flag
1721 INTO l_actual_flag, l_budget_flag, l_encum_flag
1722 FROM (SELECT MAX(DECODE(NVL(ALLOW_ACTUALS_FLAG,'N'),'Y','Y','Z')) actual_flag
1723 ,MAX(DECODE(NVL(ALLOW_BUDGETS_FLAG,'N'),'Y','Y','Z')) budget_flag
1724 ,MAX(DECODE(NVL(ALLOW_encumbrance_FLAG,'N'),'Y','Y','Z')) encumbrance_flag
1725 FROM xla_event_class_attrs
1726 WHERE application_id = g_application_id
1727 GROUP BY allow_actuals_flag, allow_budgets_flag, allow_encumbrance_flag
1728 ORDER BY actual_flag,budget_flag,encumbrance_flag)
1729 WHERE ROWNUM = 1;
1730
1731 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1732 trace('l_actual_flag = ' || l_actual_flag,C_LEVEL_STATEMENT,l_log_module);
1733 trace('l_budget_flag = ' || l_budget_flag,C_LEVEL_STATEMENT,l_log_module);
1734 trace('l_encum_flag = ' || l_encum_flag,C_LEVEL_STATEMENT,l_log_module);
1735 END IF;
1736
1737 -- Check for closed periods
1738 IF (g_entity_id IS NOT NULL) THEN
1739 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1740 trace('Performing period validations for the document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1741 END IF;
1742 IF (l_actual_flag = 'Y') THEN
1743
1744 l_statement :=
1745 'SELECT DISTINCT aeh.period_name
1746 ,aeh.ledger_id
1747 FROM xla_ae_headers aeh
1748 ,gl_period_statuses gps
1749 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1750 WHERE aeh.application_id = :2 --g_application_id
1751 AND aeh.ledger_id = led.column_value
1752 AND aeh.entity_id = :3 --g_entity_id
1753 AND aeh.gl_transfer_status_code = ''N''
1754 AND aeh.accounting_entry_status_code = ''F''
1755 AND aeh.balance_type_code = ''A''
1756 AND gps.application_id = 101
1757 AND gps.ledger_id = aeh.ledger_id
1758 AND gps.period_name = aeh.period_name
1759 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1760 AND gps.closing_status IN (''C'',''N'',''P'')';
1761
1762 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1763 trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1764 END IF;
1765 EXECUTE IMMEDIATE l_statement
1766 BULK COLLECT INTO
1767 l_period_name_tab
1768 ,l_ledger_ids_tab
1769 USING g_all_ledger_ids_tab
1770 ,g_application_id
1771 ,g_entity_id;
1772
1773 IF SQL%FOUND THEN
1774 IF (C_LEVEL_ERROR >= g_log_level) THEN
1775 trace('There are journal entries in a closed period.',C_LEVEL_ERROR,l_log_module);
1776 END IF;
1777 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1778 trace('Number of closed periods = ' || l_period_name_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1779 END IF;
1780
1781 l_period_val_failed := TRUE;
1782 FOR j IN l_period_name_tab.FIRST..l_period_name_tab.LAST
1783 LOOP
1784 xla_accounting_err_pkg.build_message
1785 (p_appli_s_name => 'XLA'
1786 ,p_msg_name => 'XLA_GLT_PERIOD_CLOSED'
1787 ,p_token_1 => 'PERIOD_NAME'
1788 ,p_value_1 => l_period_name_tab(j)
1789 ,p_token_2 => 'LEDGER_NAME'
1790 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1791 ,p_entity_id => NULL
1792 ,p_event_id => NULL
1793 );
1794 -- Display error message when there are unposted
1795 -- records in given period and the period is closed.
1796 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1797 trace('The period ' ||l_period_name_tab(j) || ' is closed for the ledger '
1798 || g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME ,C_LEVEL_STATEMENT,l_log_module);
1799 END IF;
1800 END LOOP;
1801 END IF;
1802 END IF;
1803 -- Perform period validations for budget entries
1804 --
1805 IF (l_budget_flag = 'Y') THEN
1806 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1807 trace('Performing budget period validations for a document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1808 END IF;
1809 l_statement := '
1810 SELECT DISTINCT gps.period_year
1811 ,gbv.budget_name
1812 FROM xla_ae_headers aeh
1813 ,gl_period_statuses gps
1814 ,gl_budget_period_ranges gbp
1815 ,gl_budget_versions gbv
1816 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1817 WHERE aeh.application_id = :2
1818 AND aeh.ledger_id = led.column_value
1819 AND aeh.balance_type_code = ''B''
1820 AND aeh.entity_id = :3 --g_entity_id
1821 AND aeh.gl_transfer_status_code = ''N''
1822 AND aeh.accounting_entry_status_code = ''F''
1823 AND gps.application_id = 101
1824 AND gps.ledger_id = aeh.ledger_id
1825 AND gps.period_name = aeh.period_name
1826 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1827 AND gps.period_year = gbp.period_year
1828 AND aeh.budget_version_id = gbp.budget_version_id
1829 AND gbp.open_flag <> ''O''
1830 AND gbv.budget_version_id = aeh.budget_version_id ';
1831
1832 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1833 trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1834 END IF;
1835 EXECUTE IMMEDIATE l_statement
1836 BULK COLLECT INTO
1837 l_period_year_tab
1838 ,l_budget_name_tab
1839 USING g_all_ledger_ids_tab
1840 ,g_application_id
1841 ,g_entity_id;
1842
1843 IF SQL%FOUND THEN
1844 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1845 trace('Number of closed budget versions = ' || l_period_year_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1846 END IF;
1847 l_period_val_failed := TRUE;
1848 FOR j IN l_budget_name_tab.FIRST..l_budget_name_tab.LAST
1849 LOOP
1850 xla_accounting_err_pkg.build_message
1851 (p_appli_s_name => 'XLA'
1852 ,p_msg_name => 'XLA_GLT_BUDGET_YEAR_CLOSED'
1853 ,p_token_1 => 'YEAR'
1854 ,p_value_1 => l_period_year_tab(j)
1855 ,p_entity_id => NULL
1856 ,p_event_id => NULL
1857 );
1858 -- Display error message when there are unposted
1859 -- records in given period and the period is closed.
1860 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1861 trace('The budget ' ||l_budget_name_tab(j) || ' is in a closed year. ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
1862 END IF;
1863 END LOOP;
1864 END IF;
1865 END IF; -- l_budget_flag = 'Y'
1866 IF (l_encum_flag = 'Y') THEN
1867 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1868 trace('Performing encumbrance period validations for a document level transfer.',C_LEVEL_STATEMENT,l_log_module);
1869 END IF;
1870 l_statement :=
1871 ' SELECT DISTINCT aeh.ledger_id
1872 ,gll.latest_encumbrance_year
1873 FROM xla_ae_headers aeh
1874 ,gl_period_statuses gps
1875 ,gl_ledgers gll
1876 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1877 WHERE aeh.application_id = :2 --g_application_id
1878 AND aeh.entity_id = :3 --g_entity_id
1879 AND aeh.ledger_id = led.column_value
1880 AND aeh.balance_type_code = ''E''
1881 AND aeh.ledger_id = gll.ledger_id
1882 AND aeh.gl_transfer_status_code = ''N''
1883 AND aeh.accounting_entry_status_code = ''F''
1884 AND gps.application_id = 101
1885 AND gps.ledger_id = aeh.ledger_id
1886 AND gps.period_name = aeh.period_name
1887 AND gps.period_year > gll.latest_encumbrance_year ';
1888
1889 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1890 trace('l_statement = ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1891 END IF;
1892 EXECUTE IMMEDIATE l_statement
1893 BULK COLLECT INTO
1894 l_ledger_ids_tab
1895 ,l_period_year_tab
1896 USING g_all_ledger_ids_tab
1897 ,g_application_id
1898 ,g_entity_id;
1899
1900 IF SQL%FOUND THEN
1901 l_period_val_failed := TRUE;
1902 FOR j IN l_ledger_ids_tab.FIRST..l_ledger_ids_tab.LAST
1903 LOOP
1904 xla_accounting_err_pkg.build_message
1905 (p_appli_s_name => 'XLA'
1906 ,p_msg_name => 'XLA_GLT_ENCUM_YEAR_CLOSED'
1907 ,p_token_1 => 'LEDGER_NAME'
1908 ,p_value_1 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1909 ,p_token_2 => 'YEAR'
1910 ,p_value_2 => l_period_year_tab(j)
1911 ,p_entity_id => NULL
1912 ,p_event_id => NULL
1913 );
1914 -- Display an error message when there are unposted
1915 -- records the closed period.
1916 IF (C_LEVEL_ERROR >= g_log_level) THEN
1917 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);
1918 END IF;
1919 END LOOP;
1920 END IF;
1921 END IF;
1922 ELSIF ( g_end_date IS NOT NULL ) THEN
1923 IF (l_actual_flag = 'Y') THEN
1924 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1925 trace('Performing period validations for the batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
1926 END IF;
1927 l_statement :=
1928 'SELECT DISTINCT aeh.period_name
1929 ,aeh.ledger_id
1930 FROM xla_ae_headers aeh
1931 ,gl_period_statuses gps
1932 ,xla_transaction_entities xte
1933 ,xla_event_types_b xet
1934 ,xla_event_class_attrs xec
1935 ,xla_ledger_relationships_v xlr
1936 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
1937 WHERE xte.entity_id = aeh.entity_id
1938 AND aeh.application_id = :2 --g_application_id
1939 AND aeh.ledger_id = led.column_value
1940 AND aeh.accounting_date <= :3 --g_end_date
1941 AND aeh.balance_type_code = ''A''
1942 AND aeh.ledger_id = xlr.ledger_id
1943 AND xte.entity_code = xec.entity_code
1944 AND xte.application_id = xec.application_id
1945 AND xec.application_id = xet.application_id
1946 AND xec.entity_code = xet.entity_code
1947 AND xec.event_class_code = xet.event_class_code
1948 AND xec.event_class_group_code
1949 = NVL(:4,xec.event_class_group_code)
1950 AND xet.event_type_code = aeh.event_type_code
1951 AND xet.application_id = aeh.application_id
1952 AND xet.entity_code = xte.entity_code
1953 AND aeh.gl_transfer_status_code = ''N''
1954 AND aeh.accounting_entry_status_code = ''F''
1955 AND gps.application_id = 101
1956 AND gps.ledger_id = aeh.ledger_id
1957 AND gps.period_name = aeh.period_name
1958 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
1959 AND gps.closing_status IN (''C'',''N'',''P'')'
1960 || g_transaction_security;
1961
1962 --trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
1963 EXECUTE IMMEDIATE l_statement
1964 BULK COLLECT INTO
1965 l_period_name_tab
1966 ,l_ledger_ids_tab
1967 USING g_all_ledger_ids_tab
1968 ,g_application_id
1969 ,g_end_date
1970 ,g_process_category;
1971
1972 IF SQL%FOUND THEN
1973 IF (C_LEVEL_ERROR >= g_log_level) THEN
1974 trace('There are journal entries in a closed period.',C_LEVEL_ERROR,l_log_module);
1975 END IF;
1976 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1977 trace('Number of periods closed = ' || l_period_name_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
1978 END IF;
1979 l_period_val_failed := TRUE;
1980 FOR j IN l_period_name_tab.FIRST..l_period_name_tab.LAST
1981 LOOP
1982 xla_accounting_err_pkg.build_message
1983 (p_appli_s_name => 'XLA'
1984 ,p_msg_name => 'XLA_GLT_PERIOD_CLOSED'
1985 ,p_token_1 => 'PERIOD_NAME'
1986 ,p_value_1 => l_period_name_tab(j)
1987 ,p_token_2 => 'LEDGER_NAME'
1988 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
1989 ,p_entity_id => NULL
1990 ,p_event_id => NULL
1991 );
1992 -- Display error message when there are unposted
1993 -- records in given period and the period is closed.
1994 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1995 trace('The period ' ||l_period_name_tab(j) || ' is closed for the ledger '
1996 || g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME ,C_LEVEL_STATEMENT,l_log_module);
1997 END IF;
1998 END LOOP;
1999 END IF;
2000 END IF;
2001 -- Perform period validations for budget entries
2002 --
2003 IF (l_budget_flag = 'Y') THEN
2004 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2005 trace('Performing budget period validations for a batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
2006 END IF;
2007 l_statement := '
2008 SELECT DISTINCT gps.period_year
2009 ,gbv.budget_name
2010 FROM xla_ae_headers aeh
2011 ,xla_transaction_entities xte
2012 ,xla_event_types_b xet
2013 ,xla_event_class_attrs xec
2014 ,gl_period_statuses gps
2015 ,gl_budget_period_ranges gbp
2016 ,gl_budget_versions gbv
2017 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
2018 WHERE xte.entity_id = aeh.entity_id
2019 AND aeh.application_id = :2
2020 AND aeh.ledger_id = led.column_value
2021 AND aeh.accounting_date <= :3 --g_end_date
2022 AND aeh.balance_type_code = ''B''
2023 AND xte.entity_code = xec.entity_code
2024 AND xte.application_id = xec.application_id
2025 AND xec.application_id = xet.application_id
2026 AND xec.entity_code = xet.entity_code
2027 AND xec.event_class_code = xet.event_class_code
2028 AND xec.event_class_group_code
2029 = NVL(:4,xec.event_class_group_code)
2030 AND xet.event_type_code = aeh.event_type_code
2031 AND xet.application_id = aeh.application_id
2032 AND xet.entity_code = xte.entity_code
2033 AND aeh.gl_transfer_status_code = ''N''
2034 AND aeh.accounting_entry_status_code = ''F''
2035 AND gps.application_id = 101
2036 AND gps.ledger_id = aeh.ledger_id
2037 AND gps.period_name = aeh.period_name
2038 AND NVL(gps.adjustment_period_flag,''N'') = ''N''
2039 AND gps.period_year = gbp.period_year
2040 AND aeh.budget_version_id = gbp.budget_version_id
2041 AND gbp.open_flag <> ''O''
2042 AND gbv.budget_version_id = aeh.budget_version_id '
2043 || g_transaction_security;
2044
2045 --trace('l_statement := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
2046 EXECUTE IMMEDIATE l_statement
2047 BULK COLLECT INTO
2048 l_period_year_tab
2049 ,l_budget_name_tab
2050 USING g_all_ledger_ids_tab
2051 ,g_application_id
2052 ,g_end_date
2053 ,g_process_category;
2054
2055 IF SQL%FOUND THEN
2056 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2057 trace('Number of closed budget versions = ' || l_period_year_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
2058 END IF;
2059 l_period_val_failed := TRUE;
2060 FOR j IN l_budget_name_tab.FIRST..l_budget_name_tab.LAST
2061 LOOP
2062 xla_accounting_err_pkg.build_message
2063 (p_appli_s_name => 'XLA'
2064 ,p_msg_name => 'XLA_GLT_BUDGET_YEAR_CLOSED'
2065 ,p_token_1 => 'YEAR'
2066 ,p_value_1 => l_period_year_tab(j)
2067 ,p_entity_id => NULL
2068 ,p_event_id => NULL
2069 );
2070 -- Display error message when there are unposted
2071 -- records in given period and the period is closed.
2072 IF (C_LEVEL_ERROR >= g_log_level) THEN
2073 trace('The budget ' ||l_budget_name_tab(j) || ' is in a closed year. ' || l_period_year_tab(j),C_LEVEL_ERROR,l_log_module);
2074 END IF;
2075 END LOOP;
2076 END IF;
2077 END IF;
2078
2079 IF (l_encum_flag = 'Y') THEN
2080 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2081 trace('Performing encumbrance period validations for a batch level transfer.',C_LEVEL_STATEMENT,l_log_module);
2082 END IF;
2083 l_statement :=
2084 ' SELECT DISTINCT aeh.ledger_id
2085 ,gll.latest_encumbrance_year
2086 FROM xla_ae_headers aeh
2087 ,gl_period_statuses gps
2088 ,xla_transaction_entities xte
2089 ,xla_event_types_b xet
2090 ,xla_event_class_attrs xec
2091 ,gl_ledgers gll
2092 ,TABLE (CAST(:1 AS XLA_NUMBER_ARRAY_TYPE))led
2093 WHERE xte.entity_id = aeh.entity_id
2094 AND aeh.application_id = :2 --g_application_id
2095 AND aeh.ledger_id = led.column_value
2096 AND aeh.accounting_date <= :3 --g_end_date
2097 AND aeh.balance_type_code = ''E''
2098 AND aeh.ledger_id = gll.ledger_id
2099 AND xte.entity_code = xec.entity_code
2100 AND xte.application_id = xec.application_id
2101 AND xec.application_id = xet.application_id
2102 AND xec.entity_code = xet.entity_code
2103 AND xec.event_class_code = xet.event_class_code
2104 AND xec.event_class_group_code = NVL(:4,xec.event_class_group_code)
2105 AND xet.event_type_code = aeh.event_type_code
2106 AND xet.application_id = aeh.application_id
2107 AND xet.entity_code = xte.entity_code
2108 AND aeh.gl_transfer_status_code = ''N''
2109 AND aeh.accounting_entry_status_code = ''F''
2110 AND gps.application_id = 101
2111 AND gps.ledger_id = aeh.ledger_id
2112 AND gps.period_name = aeh.period_name
2113 AND gps.period_year > gll.latest_encumbrance_year '
2114 || g_transaction_security;
2115
2116 EXECUTE IMMEDIATE l_statement
2117 BULK COLLECT INTO
2118 l_ledger_ids_tab
2119 ,l_period_year_tab
2120 USING g_all_ledger_ids_tab
2121 ,g_application_id
2122 ,g_end_date
2123 ,g_process_category;
2124
2125 IF SQL%FOUND THEN
2126 l_period_val_failed := TRUE;
2127 FOR j IN l_ledger_ids_tab.FIRST..l_ledger_ids_tab.LAST
2128 LOOP
2129 xla_accounting_err_pkg.build_message
2130 (p_appli_s_name => 'XLA'
2131 ,p_msg_name => 'XLA_GLT_ENCUM_YEAR_CLOSED'
2132 ,p_token_1 => 'YEAR'
2133 ,p_value_1 => l_period_year_tab(j)
2134 ,p_token_2 => 'LEDGER_NAME'
2135 ,p_value_2 => g_all_ledgers_tab(l_ledger_ids_tab(j)).NAME
2136 ,p_entity_id => NULL
2137 ,p_event_id => NULL
2138 );
2139 -- Display an error message when there are unposted
2140 -- records the closed period.
2141 IF (C_LEVEL_ERROR >= g_log_level) THEN
2142 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);
2143 END IF;
2144 END LOOP;
2145 END IF;
2146 END IF;
2147 END IF;
2148
2149 IF (l_period_val_failed) THEN
2150 IF (C_LEVEL_ERROR >= g_log_level) THEN
2151 trace('Transfer to GL period validation has failed.',C_LEVEL_ERROR,l_log_module);
2152 END IF;
2153 xla_exceptions_pkg.raise_exception;
2154 END IF;
2155
2156 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2157 trace('validate_accounting_periods.End',C_LEVEL_PROCEDURE,l_log_module);
2158 END IF;
2159 EXCEPTION
2160 WHEN xla_exceptions_pkg.application_exception THEN
2161 RAISE;
2162 WHEN OTHERS THEN
2163 xla_exceptions_pkg.raise_message
2164 (p_location => 'xla_trasnfer_pkg.validate_accounting_periods');
2165 END validate_accounting_periods;
2166
2167
2168 /*====================================================================
2169 THE PROCEDURE selects AND marks THE journal entries
2170 *====================================================================*/
2171 -- removed parameter p_ledger_id
2172 PROCEDURE select_journal_entries IS
2173 l_statement VARCHAR2(4000);
2174 l_log_module VARCHAR2(240);
2175 l_je_count NUMBER;
2176 BEGIN
2177 IF g_log_enabled THEN
2178 l_log_module := C_DEFAULT_MODULE||'.select_journal_entries';
2179 END IF;
2180
2181 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2182 trace('select_journal_entries.Begin',C_LEVEL_PROCEDURE,l_log_module);
2183 END IF;
2184 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2185 trace('Number of ledgers selected = ' || g_ledger_id_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
2186 END IF;
2187
2188 -- Select accounting headers for the transfer
2189
2190 IF (g_transfer_mode = 'COMBINED') THEN
2191 CASE g_caller
2192 WHEN C_ACCTPROG_BATCH THEN
2193 -- Bug 5056632.
2194 -- group_id, gl_transfer_date and gl_transfer_status_code
2195 -- is pre populated in the accounting program. get the count.
2196 -- Bug 5437400 - update transfer status and transfer date in
2197 -- combined mode.
2198 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
2199 UPDATE /*+ index(xah, XLA_AE_HEADERS_N1) */
2200 xla_ae_headers xah
2201 SET gl_transfer_date = sysdate,
2202 gl_transfer_status_code = 'S'
2203 WHERE application_id = g_application_id
2204 AND ledger_id = g_ledger_id_tab(i)
2205 AND group_id = g_group_id
2206 AND gl_transfer_status_code = 'N'
2207 AND accounting_entry_status_code = 'F'
2208 -- added Bug#8691650
2209 AND EXISTS
2210 (
2211 SELECT 1 FROM xla_ae_lines xal
2212 WHERE xah.ae_header_id = xal.ae_header_id
2213 AND xah.application_id = xal.application_id
2214 )
2215 AND EXISTS
2216 (
2217 -- added hint for perf bug#10047096
2218 SELECT /*+ no_unnest */ 1 FROM xla_events xle
2219 WHERE xah.event_id = xle.event_id
2220 AND xah.application_id = xle.application_id
2221 AND xle.event_status_code = 'P'
2222 AND xle.process_status_code = 'P'
2223 );
2224
2225
2226 l_je_count := SQL%ROWCOUNT;
2227
2228 WHEN C_ACCTPROG_DOCUMENT THEN
2229 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2230 trace('Selecting journal entris for the document ' || g_entity_id,C_LEVEL_STATEMENT,l_log_module);
2231 END IF;
2232 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
2233 UPDATE xla_ae_headers aeh
2234 SET program_update_date = SYSDATE,
2235 program_id = g_program_id,
2236 request_id = g_request_id,
2237 group_id = g_group_id,
2238 gl_transfer_date = sysdate,
2239 gl_transfer_status_code = 'S'
2240 WHERE application_id = g_application_id
2241 AND ledger_id = g_ledger_id_tab(i)
2242 AND gl_transfer_status_code = 'N'
2243 AND entity_id = g_entity_id
2244 AND accounting_entry_status_code = 'F'
2245 -- added Bug#8691650
2246 AND EXISTS
2247 (
2248 SELECT 1 FROM xla_ae_lines xal
2249 WHERE aeh.ae_header_id = xal.ae_header_id
2250 AND aeh.application_id = xal.application_id
2251 )
2252 AND EXISTS
2253 (
2254 SELECT 1 FROM xla_events xle
2255 WHERE aeh.event_id = xle.event_id
2256 AND aeh.application_id = xle.application_id
2257 AND xle.event_status_code = 'P'
2258 AND xle.process_status_code = 'P'
2259 );
2260
2261
2262 l_je_count := SQL%ROWCOUNT;
2263
2264 ELSE -- When C_TP_MERGE or C_MPA_COMPLETE
2265 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2266 trace('Selecting journal entries for the accounting batch id ' || g_accounting_batch_id,C_LEVEL_STATEMENT,l_log_module);
2267 END IF;
2268 FORALL i IN g_ledger_id_tab.FIRST .. g_ledger_id_tab.LAST
2269 UPDATE xla_ae_headers aeh
2270 SET program_update_date = SYSDATE,
2271 program_id = g_program_id,
2272 group_id = g_group_id,
2273 gl_transfer_date = SYSDATE,
2274 gl_transfer_status_code = 'S'
2275 WHERE application_id = g_application_id
2276 AND ledger_id = g_ledger_id_tab(i)
2277 AND gl_transfer_status_code = 'N'
2278 AND accounting_batch_id = g_accounting_batch_id
2279 AND accounting_entry_status_code = 'F'
2280 -- added Bug#8691650
2281 AND EXISTS
2282 (
2283 SELECT 1 FROM xla_ae_lines xal
2284 WHERE aeh.ae_header_id = xal.ae_header_id
2285 AND aeh.application_id = xal.application_id
2286 )
2287 AND EXISTS
2288 (
2289 SELECT 1 FROM xla_events xle
2290 WHERE aeh.event_id = xle.event_id
2291 AND aeh.application_id = xle.application_id
2292 AND xle.event_status_code = 'P'
2293 AND xle.process_status_code = 'P'
2294 );
2295
2296 l_je_count := SQL%ROWCOUNT;
2297
2298 END CASE;
2299
2300 ELSIF g_transfer_mode = 'STANDALONE' THEN
2301 IF g_caller = C_ACCTPROG_DOCUMENT THEN
2302 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
2303 UPDATE xla_ae_headers aeh
2304 SET program_update_date = SYSDATE,
2305 program_id = g_program_id,
2306 request_id = g_request_id,
2307 gl_transfer_date = sysdate,
2308 gl_transfer_status_code = 'S',
2309 group_id = g_group_id
2310 WHERE application_id = g_application_id
2311 AND ledger_id = g_ledger_id_tab(i)
2312 AND entity_id = g_entity_id
2313 AND gl_transfer_status_code = 'N'
2314 AND accounting_entry_status_code = 'F'
2315 -- added Bug#8691650
2316 AND EXISTS
2317 (
2318 SELECT 1 FROM xla_ae_lines xal
2319 WHERE aeh.ae_header_id = xal.ae_header_id
2320 AND aeh.application_id = xal.application_id
2321 )
2322 AND EXISTS
2323 (
2324 SELECT 1 FROM xla_events xle
2325 WHERE aeh.event_id = xle.event_id
2326 AND aeh.application_id = xle.application_id
2327 AND xle.event_status_code = 'P'
2328 AND xle.process_status_code = 'P'
2329 );
2330
2331 l_je_count := SQL%ROWCOUNT;
2332
2333 ELSIF g_caller = C_ACCTPROG_BATCH THEN -- Standalone batch transfer
2334 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2335 trace('Standalone batch transfer.',C_LEVEL_STATEMENT,l_log_module);
2336 END IF;
2337
2338 -- for bug 8417930
2339 -- added extra conditions to make the below update and
2340 -- gl_interface insert query in SYNC.
2341
2342 -- For bug12542507 commented the below hint
2343 -- /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)
2344 -- swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
2345
2346 l_statement :=
2347 ' UPDATE
2348 (SELECT /*+ leading(aeh) use_nl(xte) */ -- added for bug12542507
2349 aeh.program_update_date -- added hint per performance change 7259699
2350 ,aeh.program_id
2351 ,aeh.request_id
2352 ,aeh.gl_transfer_date
2353 ,aeh.gl_transfer_status_code
2354 ,aeh.group_id
2355 FROM xla_ae_headers aeh
2356 ,xla_transaction_entities xte
2357 --,xla_event_types_b xet
2358 --,xla_event_class_attrs xeca
2359 --,xla_event_classes_b xec -- commented for bug12542507 and made it as IN clause
2360 WHERE xte.entity_id = aeh.entity_id
2361 AND xte.application_id = :1 --g_application_id
2362 AND aeh.application_id = xte.application_id
2363 AND aeh.ledger_id = :2 --g_ledger_id_tab(i)
2364 AND aeh.je_category_name in (select je_category_name from gl_je_categories) --8417930
2365 AND EXISTS(select 1 from gl_period_statuses glp
2366 where glp.application_id = 101
2367 and glp.ledger_id = aeh.ledger_id
2368 and glp.period_name = aeh.period_name) --8417930
2369 AND aeh.accounting_date <= :3 --g_end_date
2370 /* AND xte.entity_code = xec.entity_code
2371 AND xeca.application_id = xec.application_id
2372 AND xeca.event_class_code = xec.event_class_code
2373 AND xeca.entity_code = xec.entity_code
2374 AND xeca.event_class_group_code = Nvl(:4,xeca.event_class_group_code)
2375 AND xec.event_class_code = xet.event_class_code
2376 AND xet.event_type_code = aeh.event_type_code
2377 AND xet.application_id = aeh.application_id
2378 AND xec.application_id = xet.application_id
2379 AND xet.event_class_code = xec.event_class_code */ --commented for bug12542507 and made it as IN clause as below
2380 AND (aeh.application_id , aeh.event_type_code) IN ( SELECT xet.application_id , xet.event_type_code
2381 FROM xla_event_types_b xet
2382 ,xla_event_class_attrs xeca
2383 ,xla_event_classes_b xec
2384 WHERE 1 = 1
2385 AND xeca.event_class_group_code = Nvl(:4,xeca.event_class_group_code)
2386 AND xeca.application_id = :5
2387 AND xeca.application_id = xec.application_id
2388 AND xeca.event_class_code = xec.event_class_code
2389 AND xeca.entity_code = xec.entity_code
2390 AND xec.event_class_code = xet.event_class_code
2391 AND xec.application_id = xet.application_id
2392 )
2393 AND aeh.gl_transfer_status_code = ''N''
2394 AND aeh.accounting_entry_status_code = ''F''
2395 AND EXISTS
2396 (
2397 SELECT 1 FROM xla_ae_lines xal
2398 WHERE aeh.ae_header_id = xal.ae_header_id
2399 AND aeh.application_id = xal.application_id
2400 )
2401 AND EXISTS
2402 (
2403 SELECT 1 FROM xla_events xle
2404 WHERE aeh.event_id = xle.event_id
2405 AND aeh.application_id = xle.application_id
2406 AND xle.event_status_code = ''P''
2407 AND xle.process_status_code = ''P''
2408 )
2409 '
2410 || g_transaction_security
2411 || ' ) SET program_update_date = SYSDATE
2412 ,program_id = :6 --g_program_id
2413 ,request_id = :7 --g_request_id
2414 ,gl_transfer_date = Sysdate
2415 ,group_id = :8 --g_group_id
2416 ,gl_transfer_status_code = ''S''';
2417
2418 trace('l_statement_2 := ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
2419
2420 FORALL i IN g_ledger_id_tab.FIRST..g_ledger_id_tab.LAST
2421 EXECUTE IMMEDIATE l_statement
2422 USING g_application_id
2423 ,g_ledger_id_tab(i)
2424 ,g_end_date
2425 ,g_process_category
2426 ,g_application_id --added for bug12542507
2427 ,g_program_id
2428 ,g_request_id
2429 ,g_group_id;
2430
2431 l_je_count := SQL%ROWCOUNT;
2432
2433 END IF;
2434 END IF;
2435
2436
2437 IF (NVL(l_je_count,0) = 0) THEN
2438 -- Add the code to stop the transfer batch if no entries are
2439 -- found for the primary ledger.
2440 g_proceed := 'N';
2441 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2442 trace('No subledger journal entries were found for the specified criteria.',C_LEVEL_STATEMENT,l_log_module);
2443 END IF;
2444 ELSE
2445 g_proceed := 'Y';
2446 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2447 trace('Total journal entries selected = ' || l_je_count,C_LEVEL_STATEMENT,l_log_module);
2448 END IF;
2449 --
2450 -- Create log entry
2451 --
2452 --insert_transfer_log(p_ledger_id => p_ledger_id);
2453 END IF;
2454
2455 --
2456 -- Display number of journal entries selected for each ledger.
2457 --
2458 /* FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last LOOP
2459 trace('g_ledger_id = ' ||g_ledger_id_tab(i),C_LEVEL_STATEMENT,l_log_module);
2460 trace('ledger_name = ' ||g_all_ledgers_tab(g_ledger_id_tab(i)).NAME,C_LEVEL_STATEMENT,l_log_module);
2461 trace('Rowcount = ' ||SQL%BULK_ROWCOUNT(i),C_LEVEL_STATEMENT,l_log_module);
2462
2463 IF (SQL%BULK_ROWCOUNT(i) > 0) THEN
2464 xla_accounting_err_pkg.build_message
2465 (p_appli_s_name => 'XLA'
2466 ,p_msg_name => 'XLA_GLT_JE_COUNT'
2467 ,p_token_1 => 'LEDGER_NAME'
2468 ,p_value_1 => g_all_ledgers_tab(g_ledger_id_tab(i)).NAME
2469 ,p_token_2 => 'COUNT'
2470 ,p_value_2 => SQL%BULK_ROWCOUNT(i)
2471 ,p_entity_id => NULL
2472 ,p_event_id => NULL
2473 );
2474 END IF;
2475 END LOOP;
2476 */
2477
2478 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2479 trace('select_journal_entries.End',C_LEVEL_PROCEDURE,l_log_module);
2480 END IF;
2481 EXCEPTION
2482 WHEN xla_exceptions_pkg.application_exception THEN
2483 IF (C_LEVEL_UNEXPECTED >= g_log_level) THEN
2484 trace('Unexpected error in ',C_LEVEL_UNEXPECTED,l_log_module);
2485 END IF;
2486 RAISE;
2487 WHEN OTHERS THEN
2488 xla_exceptions_pkg.raise_message
2489 (p_location => 'xla_transfer_pkg.select_journal_entries');
2490 END select_journal_entries;
2491
2492 /*===========================================================================+
2493 | PROCEDURE |
2494 | SET_APPLICATION_INFO |
2495 | |
2496 | DESCRIPTION |
2497 | Derive application LEVEL information. |
2498 | |
2499 | SCOPE - PRIVATE |
2500 | |
2501 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2502 | |
2503 | ARGUMENTS |
2504 | p_application_id Application ID OF THE CALLING application. |
2505 | |
2506 | NOTES |
2507 | |
2508 +===========================================================================*/
2509
2510
2511 PROCEDURE set_application_info IS
2512 l_log_module VARCHAR2(240);
2513 BEGIN
2514 IF g_log_enabled THEN
2515 l_log_module := C_DEFAULT_MODULE||'.set_application_info';
2516 END IF;
2517
2518 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2519 trace('set_application_info.Begin',C_LEVEL_PROCEDURE,l_log_module);
2520 END IF;
2521 -- Populate application level info
2522 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2523 trace('Populate application level information',C_LEVEL_STATEMENT,l_log_module);
2524 END IF;
2525 SELECT js.je_source_name
2526 ,decode(js.import_using_key_flag,'Y',js.je_source_key
2527 ,js.user_je_source_name)
2528 ,js.import_using_key_flag
2529 INTO g_je_source_name
2530 ,g_user_source_name
2531 ,g_import_key_flag
2532 FROM gl_je_sources js
2533 ,xla_subledgers xsl
2534 WHERE xsl.application_id = g_application_id
2535 AND js.je_source_name = xsl.je_source_name;
2536
2537
2538 --
2539 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2540 trace('set_application_info.End',C_LEVEL_PROCEDURE,l_log_module);
2541 END IF;
2542 EXCEPTION
2543 WHEN xla_exceptions_pkg.application_exception THEN
2544 RAISE;
2545 WHEN OTHERS THEN
2546 xla_exceptions_pkg.raise_message
2547 (p_location => 'xla_transfer_pkg.set_application_info');
2548 END set_application_info;
2549
2550 /*===========================================================================+
2551 PROCEDURE
2552 gl_interface_insert
2553
2554 DESCRIPTION
2555 Inserts ROWS INTO THE GL_ITERFACE TABLE
2556
2557 SCOPE - PRIVATE
2558
2559 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2560
2561 ARGUMENTS
2562
2563
2564 NOTES
2565
2566 +===========================================================================*/
2567
2568 PROCEDURE insert_gl_interface IS
2569 l_log_module VARCHAR2(240);
2570 l_statement VARCHAR2(4500);
2571 l_je_count NUMBER;
2572
2573
2574 BEGIN
2575 IF g_log_enabled THEN
2576 l_log_module := C_DEFAULT_MODULE||'.insert_gl_interface';
2577 END IF;
2578
2579 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2580 trace('gl_interface_insert.Begin',C_LEVEL_PROCEDURE,l_log_module);
2581 trace('g_disable_gllezl_flag = '||g_disable_gllezl_flag,C_LEVEL_PROCEDURE,l_log_module);
2582 END IF;
2583
2584 -- Check if GL Journal Import should be submitted. Do not use multi table insert if
2585 -- GL is not installed or for document level transfer.
2586
2587 IF g_disable_gllezl_flag = 'Y' OR g_entity_id IS NOT NULL THEN
2588 g_gl_interface_table_name := 'GL_INTERFACE';
2589 ELSE
2590 g_gl_interface_table_name := 'XLA_GLT_'||to_char(g_group_id);
2591 --Added for bug 12965313 start
2592 GL_JOURNAL_IMPORT_PKG.create_table
2593 (g_gl_interface_table_name,
2594 create_n1_index => FALSE,
2595 create_n2_index => FALSE,
2596 create_n3_index => TRUE
2597 );
2598 --Added for bug 12965313 end
2599 END IF;
2600
2601 print_logfile ('GL Inerface tablename = ' || g_gl_interface_table_name);
2602 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2603 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2604 END IF;
2605
2606
2607
2608 print_logfile ('GL Inerface tablename = ' || g_gl_interface_table_name);
2609 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2610 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2611 END IF;
2612
2613
2614 print_logfile ('tablename = ' || g_gl_interface_table_name);
2615 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2616 trace('tablename = '||g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2617 END IF;
2618
2619 --7512923 added extra columns to gl_interface_table
2620 --7419726 changed the decode statement of funds_reserved_flag
2621 --7591257 added extra columns to pass Currency Conversion rate, type and date
2622 l_statement := 'INSERT INTO '||g_gl_interface_table_name||
2623 '(
2624 status, ledger_id
2625 ,user_je_source_name, user_je_category_name
2626 ,accounting_date
2627 ,currency_code
2628 ,date_created, created_by
2629 ,actual_flag
2630 ,budget_version_id
2631 ,encumbrance_type_id
2632 ,code_combination_id, stat_amount
2633 ,entered_dr
2634 ,entered_cr
2635 ,accounted_dr
2636 ,accounted_cr
2637 ,reference1
2638 ,reference4
2639 ,reference5
2640 ,reference10
2641 ,reference11
2642 ,subledger_doc_sequence_id
2643 ,subledger_doc_sequence_value
2644 ,gl_sl_link_table
2645 ,gl_sl_link_id
2646 ,request_id
2647 ,ussgl_transaction_code
2648 ,je_header_id, group_id
2649 ,period_name, jgzz_recon_ref
2650 ,reference_date
2651 ,funds_reserved_flag
2652 ,reference25
2653 ,reference26
2654 ,reference27
2655 ,reference28
2656 ,reference29
2657 ,reference30
2658 )
2659 SELECT /*+ ordered index(aeh,xla_ae_headers_n1) use_nl(jc,led,ael,gps) */
2660 ''NEW'', aeh.ledger_id
2661 ,:1 , decode(:2,''Y'',jc.je_category_key
2662 ,jc.user_je_category_name)
2663 ,DECODE(:3, ''P'' , gps.end_date , aeh.accounting_date)
2664 ,DECODE(aeh.balance_type_code , ''E'' , led.currency_code , ael.currency_code)
2665 ,SYSDATE, :4
2666 ,aeh.balance_type_code
2667 ,aeh.budget_version_id
2668 ,ael.encumbrance_type_id -- 4458381
2669 ,ael.code_combination_id, ael.statistical_amount
2670 ,DECODE(aeh.balance_type_code, ''E'', ael.accounted_dr, ael.entered_dr) -- 4458381
2671 ,DECODE(aeh.balance_type_code, ''E'', ael.accounted_cr, ael.entered_cr) -- 4458381
2672 ,accounted_dr
2673 ,accounted_cr
2674 ,:5 -- Reference1
2675 ,DECODE(reference_date , NULL , NULL
2676 ,TO_CHAR(reference_date,''DD-MON-YYYY''))||
2677 DECODE(:6 , ''A'' , TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'')
2678 ,''P'' ,aeh.period_name
2679 ,''D'' ,aeh.ae_header_id
2680 ,''E'' ,TO_CHAR(aeh.accounting_date ,''DD-MON-YYYY'') -- added E/F lookup code for bug8681466
2681 ,''F'' ,aeh.period_name) --Reference4
2682 ,DECODE(:7,''D'',substrb(aeh.description,1,240),null)
2683 ,DECODE(DECODE(:8,''D'',''D'',''E'',''D'',''F'',''D'',''S'')||ael.gl_transfer_mode_code --added bug 8846459 to show line description
2684 ,''SS'',null,substrb(ael.description,1,240))
2685 ,DECODE(:9||ael.gl_transfer_mode_code,
2686 ''AS'',jgzz_recon_ref,
2687 ''PS'',jgzz_recon_ref,
2688 aeh.ae_header_id||''-''||ael.ae_line_num) -- Reference11
2689 ,aeh.doc_sequence_id
2690 ,aeh.doc_sequence_value
2691 ,ael.gl_sl_link_table
2692 ,ael.gl_sl_link_id
2693 ,:10
2694 ,ael.ussgl_transaction_code
2695 ,aeh.ae_header_id, :11
2696 ,aeh.period_name, ael.jgzz_recon_ref
2697 ,aeh.reference_date
2698 ,decode(led.enable_budgetary_control_flag
2699 ,''Y'',
2700 decode(aeh.funds_status_code, ''A'', ''Y'', ''S'', ''Y'', ''P'', ''Y'', NULL)
2701 ,''Y'')
2702 ,aeh.entity_id
2703 ,aeh.event_id
2704 ,ael.ae_header_id
2705 ,ael.ae_line_num
2706 ,ael.accounted_dr
2707 ,ael.accounted_cr
2708 FROM xla_ae_headers aeh
2709 ,xla_ae_lines ael
2710 ,gl_je_categories jc
2711 ,gl_period_statuses gps
2712 ,gl_ledgers led
2713 WHERE ael.application_id = aeh.application_id
2714 AND ael.ae_header_id = aeh.ae_header_id
2715 AND aeh.group_id = :12
2716 AND aeh.application_id = :13 --4769315
2717 AND aeh.je_category_name = jc.je_category_name
2718 AND gps.application_id = 101
2719 AND gps.ledger_id = aeh.ledger_id
2720 AND led.ledger_id = gps.ledger_id
2721 AND aeh.period_name = gps.period_name
2722 AND aeh.gl_transfer_status_code = ''S''';
2723
2724 /*
2725 For bug 8407619
2726 Following columns have been removed from the above Insert and Select
2727 currency_conversion_date
2728 ,user_currency_conversion_type
2729 ,currency_conversion_rate
2730
2731 */
2732
2733 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2734 trace('l_statement = ' || l_statement,C_LEVEL_STATEMENT,l_log_module);
2735 END IF;
2736
2737 EXECUTE IMMEDIATE l_statement
2738 USING g_user_source_name
2739 ,g_import_key_flag
2740 ,g_transfer_summary_mode
2741 ,g_user_id
2742 ,g_batch_name
2743 ,g_transfer_summary_mode
2744 ,g_transfer_summary_mode
2745 ,g_transfer_summary_mode
2746 ,g_transfer_summary_mode
2747 ,g_request_id
2748 ,g_group_id
2749 ,g_group_id
2750 ,g_application_id;
2751
2752 /*
2753 Comment start for 8417930 (reverting the changes done earlier)
2754
2755 l_je_count := SQL%ROWCOUNT;
2756
2757
2758 IF (NVL(l_je_count,0) = 0) THEN
2759 -- Add the code to stop the transfer batch if no entries are
2760 -- found for the primary ledger.
2761 g_proceed := 'N';
2762 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2763 trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
2764 END IF;
2765 ELSE
2766 g_proceed := 'Y';
2767 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2768 trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
2769 END IF;
2770
2771 END IF;
2772
2773 Comment end for 8417930 */
2774
2775 -- For bug 8417930
2776 -- Added to find how many rows were inserted into GL_INTERFACE table
2777
2778 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2779 trace(SQL%ROWCOUNT || ' rows are inserted into the GL_INTERFACE table',C_LEVEL_STATEMENT,l_log_module);
2780 END IF;
2781
2782 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2783 trace('gl_interface_insert.End',C_LEVEL_PROCEDURE,l_log_module);
2784 END IF;
2785 EXCEPTION
2786 WHEN xla_exceptions_pkg.application_exception THEN
2787 RAISE;
2788 WHEN OTHERS THEN
2789 xla_exceptions_pkg.raise_message
2790 (p_location => 'xla_transfer_pkg.gl_interface_insert');
2791 END insert_gl_interface;
2792
2793 /*===========================================================================+
2794 PROCEDURE
2795 wait_for_gllezl
2796
2797 DESCRIPTION
2798 Wait FOR THE journal import request TO COMPLETE.
2799
2800 SCOPE - PRIVATE
2801
2802 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2803
2804 ARGUMENTS
2805
2806
2807 NOTES
2808
2809 +===========================================================================*/
2810 FUNCTION wait_for_gllezl RETURN BOOLEAN IS
2811 l_callStatus BOOLEAN;
2812 l_phase VARCHAR2(30);
2813 l_status VARCHAR2(30);
2814 l_dev_phase VARCHAR2(30);
2815 l_dev_status VARCHAR2(30);
2816 l_message VARCHAR2(240);
2817 l_gllezl_status BOOLEAN := TRUE;
2818 l_index PLS_INTEGER := 0;
2819 l_log_module VARCHAR2(240);
2820 l_gl_status VARCHAR2(30);
2821 g_gl_interface_table_name VARCHAR2(30);
2822 l_journal_import_status BOOLEAN :=TRUE;
2823 BEGIN
2824 IF g_log_enabled THEN
2825 l_log_module := C_DEFAULT_MODULE||'.wait_for_gllezl';
2826 END IF;
2827
2828 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2829 trace('wait_for_gllezl.Begin',C_LEVEL_PROCEDURE,l_log_module);
2830 END IF;
2831
2832 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2833 trace('Ledgers count = ' || g_primary_ledgers_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
2834 END IF;
2835
2836 FOR i IN REVERSE g_primary_ledgers_tab.first..g_primary_ledgers_tab.last
2837 LOOP
2838 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2839 trace('Looping for the ledger = ' || g_primary_ledgers_tab(i).ledger_id,C_LEVEL_STATEMENT,l_log_module);
2840 END IF;
2841
2842 IF (g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL) THEN
2843 IF (C_LEVEL_EVENT >= g_log_level) THEN
2844 trace('Checking status for request id = ' || g_primary_ledgers_tab(i).gllezl_request_id,C_LEVEL_EVENT,l_log_module);
2845 END IF;
2846
2847 l_callStatus := fnd_concurrent.wait_for_request
2848 (request_id => g_primary_ledgers_tab(i).gllezl_request_id
2849 ,interval => 5
2850 ,phase => l_phase
2851 ,status => l_status
2852 ,dev_phase => l_dev_phase
2853 ,dev_status => l_dev_status
2854 ,message => l_message);
2855
2856 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2857 trace('l_dev_phase = ' || l_dev_phase,C_LEVEL_STATEMENT,l_log_module);
2858 trace('l_dev_status = ' || l_dev_status,C_LEVEL_STATEMENT,l_log_module);
2859 END IF;
2860
2861 /*
2862 --For bug 8417930
2863 -- Added below IF condition to avoid online transfer to GL failing
2864 -- with "table or view does not exist".
2865 */
2866
2867 IF g_disable_gllezl_flag = 'Y' OR g_entity_id IS NOT NULL THEN
2868 g_gl_interface_table_name := 'GL_INTERFACE';
2869 ELSE
2870 g_gl_interface_table_name := 'XLA_GLT_'||to_char(g_primary_ledgers_tab(i).group_id);
2871 END IF;
2872
2873
2874 --added bug 6945231
2875 IF ( l_dev_phase = 'COMPLETE' AND l_dev_status ='WARNING') THEN
2876 IF (C_LEVEL_ERROR >= g_log_level) THEN
2877 trace('selecting from gl interface '|| g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2878 END IF;
2879
2880 -- removed join of ledger id from below query for bug 7529513
2881 BEGIN
2882 EXECUTE IMMEDIATE
2883 'select status from ' ||g_gl_interface_table_name||
2884 ' where user_je_source_name= :1
2885 and group_id = :2
2886 and request_id = :4
2887 -- and status like ''E%''
2888 and status <> ''PROCESSED'' AND status NOT LIKE ''W%''
2889 and rownum=1 ' into l_gl_status
2890 using g_user_source_name,g_primary_ledgers_tab(i).group_id, g_primary_ledgers_tab(i).gllezl_request_id;
2891
2892 --IF l_gl_status like 'E%' THEN --Bug#8691650
2893 IF ( l_gl_status <> 'PROCESSED' AND l_gl_status NOT LIKE 'W%' ) THEN
2894
2895 IF (C_LEVEL_ERROR >= g_log_level) THEN
2896 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);
2897 END IF;
2898 l_journal_import_status:=FALSE;
2899 xla_accounting_err_pkg.build_message
2900 (p_appli_s_name => 'XLA'
2901 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
2902 ,p_token_1 => 'REQUEST_ID'
2903 ,p_value_1 => g_primary_ledgers_tab(i).gllezl_request_id
2904 ,p_token_2 => 'LEDGER_NAME'
2905 ,p_value_2 => g_primary_ledgers_tab(i).NAME
2906 ,p_entity_id => NULL
2907 ,p_event_id => NULL
2908 );
2909 -- Perform Recovery
2910 recover_batch;
2911 END IF;
2912
2913 EXCEPTION
2914 WHEN NO_DATA_FOUND THEN
2915 IF (C_LEVEL_ERROR >= g_log_level) THEN
2916 trace('No data in gl_interface '|| g_gl_interface_table_name,C_LEVEL_PROCEDURE,l_log_module);
2917 END IF;
2918 /*no data exists in error so dont recover */
2919 NULL; --Bug#8691650
2920 END;
2921
2922 END IF;
2923
2924 l_gl_status :=NULL;
2925
2926
2927 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
2928 IF (C_LEVEL_ERROR >= g_log_level) THEN
2929 trace('The journal import request failed. Request Id = ' || g_primary_ledgers_tab(i).gllezl_request_id,C_LEVEL_ERROR,l_log_module);
2930 END IF;
2931 l_journal_import_status:=FALSE;
2932 xla_accounting_err_pkg.build_message
2933 (p_appli_s_name => 'XLA'
2934 ,p_msg_name => 'XLA_GLT_GLLEZL_FAILED'
2935 ,p_token_1 => 'REQUEST_ID'
2936 ,p_value_1 => g_primary_ledgers_tab(i).gllezl_request_id
2937 ,p_token_2 => 'LEDGER_NAME'
2938 ,p_value_2 => g_primary_ledgers_tab(i).NAME
2939 ,p_entity_id => NULL
2940 ,p_event_id => NULL
2941 );
2942 -- Perform Recovery
2943 recover_batch;
2944 END IF;
2945
2946 --8429053 Added following IF condition in order to delete
2947 --E% status data from GL_INTERFACE.
2948
2949 If g_gl_interface_table_name = 'GL_INTERFACE' Then
2950 delete from gl_interface
2951 where user_je_source_name = g_user_source_name
2952 and group_id = g_primary_ledgers_tab(i).group_id;
2953 --and request_id = g_primary_ledgers_tab(i).gllezl_request_id
2954
2955 IF SQL%NOTFOUND THEN
2956 IF (C_LEVEL_ERROR >= g_log_level) THEN
2957 trace('No rows found in GL_INTERFACE Table With E% errors.',C_LEVEL_ERROR,l_log_module);
2958 END IF;
2959 ELSE
2960 IF (C_LEVEL_ERROR >= g_log_level) THEN
2961 trace(SQL%ROWCOUNT || 'Rows deleted from GL_INTERFACE Table',C_LEVEL_ERROR,l_log_module);
2962 END IF;
2963 END IF;
2964 END IF;
2965 --8429053 END
2966
2967 END IF;
2968
2969 END LOOP;
2970
2971 IF l_journal_import_status = TRUE THEN
2972 l_gllezl_status := TRUE;
2973 ELSE l_gllezl_status:=FALSE;
2974 END IF;
2975
2976 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2977 trace('wait_for_gllezl.End',C_LEVEL_PROCEDURE,l_log_module);
2978 END IF;
2979
2980 RETURN(l_gllezl_status);
2981
2982 EXCEPTION
2983 WHEN xla_exceptions_pkg.application_exception THEN
2984 RAISE;
2985 WHEN OTHERS THEN
2986 xla_exceptions_pkg.raise_message
2987 (p_location => 'xla_transfer_pkg.wait_for_gllezl');
2988 END wait_for_gllezl;
2989
2990 /*===========================================================================+
2991 | PROCEDURE |
2992 | set_transfer_status |
2993 | |
2994 | DESCRIPTION |
2995 | Updates the transfer to GL status to yes to indicate that journal entries|
2996 | have been transferred successfully. |
2997 | SCOPE - PRIVATE |
2998 | |
2999 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3000 | |
3001 | ARGUMENTS |
3002 | |
3003 | NOTES |
3004 | |
3005 +===========================================================================*/
3006 PROCEDURE set_transfer_status IS
3007 l_log_module VARCHAR2(240);
3008 BEGIN
3009 IF g_log_enabled THEN
3010 l_log_module := C_DEFAULT_MODULE||'.set_transfer_status';
3011 END IF;
3012
3013 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3014 trace('set_transfer_status.Begin',C_LEVEL_PROCEDURE,l_log_module);
3015 END IF;
3016 --
3017 -- Update XLA_AE_HEADERS
3018 --
3019 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3020 trace('Updating xla_ae_headers ',C_LEVEL_STATEMENT,l_log_module);
3021 END IF;
3022
3023 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3024 UPDATE /*+ index(XLA_AE_HEADERS,XLA_AE_HEADERS_N1) */
3025 xla_ae_headers
3026 SET gl_transfer_status_code = 'Y',
3027 gl_transfer_date = sysdate -- bug#5437400
3028 WHERE group_id = g_group_id_tab(i)
3029 AND application_id = g_application_id --4769315
3030 --Added for 10124492
3031 AND gl_transfer_status_code <> 'NT';
3032 --Added for 10124492
3033
3034 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3035 trace('set_transfer_status.End',C_LEVEL_PROCEDURE,l_log_module);
3036 END IF;
3037 EXCEPTION
3038 WHEN OTHERS THEN
3039 xla_exceptions_pkg.raise_message
3040 (p_location => 'xla_transfer_pkg.set_transfer_status');
3041 END set_transfer_status;
3042
3043 /*===========================================================================+
3044 | PROCEDURE |
3045 | delete_transfer_log |
3046 | |
3047 | DESCRIPTION |
3048 | Deletes the transfer to GL log. |
3049 | |
3050 | SCOPE - PRIVATE |
3051 | |
3052 | |
3053 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3054 | |
3055 | ARGUMENTS |
3056 | |
3057 | NOTES |
3058 | |
3059 +===========================================================================*/
3060 PROCEDURE delete_transfer_log IS
3061 l_log_module VARCHAR2(240);
3062 BEGIN
3063 IF g_log_enabled THEN
3064 l_log_module := C_DEFAULT_MODULE||'.delete_transfer_log';
3065 END IF;
3066
3067 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3068 trace('delete_transfer_log.Begin',C_LEVEL_PROCEDURE,l_log_module);
3069 END IF;
3070
3071 -- Delete transfer to GL log
3072 --
3073 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3074 trace('Deleting rows from the transfer log.',C_LEVEL_STATEMENT,l_log_module);
3075 END IF;
3076
3077 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3078 DELETE xla_transfer_logs
3079 WHERE group_id = g_group_id_tab(i);
3080
3081 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3082 trace('delete_transfer_log.End',C_LEVEL_PROCEDURE,l_log_module);
3083 END IF;
3084 EXCEPTION
3085 WHEN OTHERS THEN
3086 xla_exceptions_pkg.raise_message
3087 (p_location => 'xla_transfer_pkg.delete_transfer_log');
3088 END delete_transfer_log;
3089
3090 /*===========================================================================+
3091 | PROCEDURE |
3092 | insert_secondary_ledgers |
3093 | |
3094 | DESCRIPTION |
3095 | Keeps track of journal entries transferred for secondary ledgers |
3096 | |
3097 | SCOPE - PRIVATE |
3098 | |
3099 | |
3100 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3101 | |
3102 | ARGUMENTS |
3103 | p_secondary_ledger_id -- Secondary ledger identifier | |
3104 | NOTES |
3105 | |
3106 +===========================================================================*/
3107 PROCEDURE insert_secondary_ledgers ( p_secondary_ledger_id IN NUMBER ) IS
3108 l_log_module VARCHAR2(240);
3109 BEGIN
3110 IF g_log_enabled THEN
3111 l_log_module := C_DEFAULT_MODULE||'.insert_secondary_ledgers';
3112 END IF;
3113
3114 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3115 trace('insert_secondary_ledgers.Begin',C_LEVEL_PROCEDURE,l_log_module);
3116 END IF;
3117
3118 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3119 trace('insert_secondary_ledgers.End',C_LEVEL_PROCEDURE,l_log_module);
3120 END IF;
3121 INSERT INTO xla_transfer_ledgers
3122 (GROUP_ID
3123 ,SECONDARY_LEDGER_ID
3124 ,PRIMARY_LEDGER_ID
3125 ,CREATION_DATE
3126 ,CREATED_BY
3127 ,LAST_UPDATE_DATE
3128 ,LAST_UPDATED_BY
3129 ,LAST_UPDATE_LOGIN
3130 ,PROGRAM_UPDATE_DATE
3131 ,PROGRAM_APPLICATION_ID
3132 ,PROGRAM_ID
3133 ,REQUEST_ID
3134 )
3135 VALUES
3136 (g_group_id
3137 ,p_secondary_ledger_id
3138 ,g_primary_ledger_id
3139 ,SYSDATE
3140 ,g_user_id
3141 ,SYSDATE
3142 ,xla_environment_pkg.g_usr_id
3143 ,xla_environment_pkg.g_login_id
3144 ,SYSDATE
3145 ,xla_environment_pkg.g_prog_appl_id
3146 ,xla_environment_pkg.g_prog_id
3147 ,xla_environment_pkg.g_Req_Id
3148 );
3149 EXCEPTION
3150 WHEN OTHERS THEN
3151 xla_exceptions_pkg.raise_message
3152 (p_location => 'xla_transfer_pkg.insert_secondary_ledgers');
3153 END insert_secondary_ledgers;
3154
3155 /*===========================================================================+
3156 | PROCEDURE |
3157 | COMPLETE_BATCH |
3158 | |
3159 | DESCRIPTION |
3160 | The procedure performs the finishing tasks after inserting journal |
3161 | entries into the GL interface table. |
3162 | |
3163 | |
3164 | SCOPE - PRIVATE |
3165 | |
3166 | |
3167 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3168 | |
3169 | ARGUMENTS |
3170 | p_submit_gl_post Submit GL post |
3171 | |
3172 | NOTES |
3173 | |
3174 +===========================================================================*/
3175
3176 PROCEDURE complete_batch(p_submit_gl_post VARCHAR2) IS
3177 l_req_id NUMBER;
3178 l_submit_post BOOLEAN := FALSE;
3179 l_ledger_id NUMBER;
3180 l_log_module VARCHAR2(240);
3181 BEGIN
3182 IF g_log_enabled THEN
3183 l_log_module := C_DEFAULT_MODULE||'.complete_batch';
3184 END IF;
3185
3186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3187 trace('complete_batch.Begin',C_LEVEL_PROCEDURE,l_log_module);
3188 END IF;
3189
3190
3191 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3192 trace('p_submit_gl_post = ' || p_submit_gl_post,C_LEVEL_STATEMENT,l_log_module);
3193 END IF;
3194
3195 --Added for bug 11855000 Start
3196 /*
3197 Need to update gl_bc_packets here because when GL posting happens,
3198 data from gl_bc_packets gets flushed. Hence we update the
3199 gl_bc_packets as funds reserved and then call keep_batches
3200 where gl posting happens if customer has provided
3201 post_to_gl parameter as Yes.
3202 */
3203
3204 IF g_application_id = 200 then
3205
3206 If (g_reserve_flag) then
3207 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3208 trace('Updating gl_bc_packets ',C_LEVEL_STATEMENT,l_log_module);
3209 END IF;
3210 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3211 UPDATE gl_bc_packets
3212 SET status_code = 'A'
3213 , result_code = 'P01'
3214 WHERE group_id = g_group_id_tab(i)
3215 AND application_id = g_application_id
3216 and status_code = 'C';
3217
3218 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3219 trace('No of rows updated as Reserved in GL_BC_PACKETS ='||SQL%ROWCOUNT,C_LEVEL_PROCEDURE,l_log_module);
3220 END IF;
3221 end if;
3222 ELSE
3223 trace('Funds check performed only for application_id 200. Provided application_id is '||g_application_id,C_LEVEL_STATEMENT,l_log_module);
3224 end if;
3225
3226 --Added for bug 11855000 End
3227
3228 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3229 LOOP
3230 l_ledger_id := g_primary_ledgers_tab(i).ledger_id;
3231 IF (NVL(p_submit_gl_post,'N') = 'Y'
3232 AND g_all_ledgers_tab(l_ledger_id).access_set_id IS NOT NULL) THEN
3233
3234 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3235 trace('l_submit_post = TRUE',C_LEVEL_STATEMENT,l_log_module);
3236 END IF;
3237 l_submit_post := TRUE;
3238 ELSE
3239 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3240 trace('l_submit_post = FALSE',C_LEVEL_STATEMENT,l_log_module);
3241 END IF;
3242 END IF;
3243
3244 IF (g_primary_ledgers_tab(i).ledger_category_code = 'SECONDARY') THEN
3245 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3246 trace('Inserting a row into the XLA_TRANSFER_LEDGERS table.',C_LEVEL_STATEMENT,l_log_module);
3247 END IF;
3248
3249 insert_secondary_ledgers
3250 (p_secondary_ledger_id => g_primary_ledgers_tab(i).ledger_id
3251 );
3252 END IF;
3253 IF (g_log_enabled AND C_LEVEL_EVENT >= g_log_level) THEN
3254 trace('Calling gl_journal_import_SLA_pkg.keep_batches ',C_LEVEL_EVENT,l_log_module);
3255 END IF;
3256
3257 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
3258 trace('p_submit_gl_post = ' || p_submit_gl_post,C_LEVEL_STATEMENT,l_log_module);
3259 trace('access_set_id = ' || g_all_ledgers_tab(l_ledger_id).access_set_id,C_LEVEL_STATEMENT,l_log_module);
3260 END IF;
3261
3262 -- keep batches and submit GL post
3263 gl_journal_import_SLA_pkg.keep_batches
3264 (x_je_source_name => g_je_source_name
3265 ,x_group_id => g_primary_ledgers_tab(i).group_id
3266 ,start_posting => l_submit_post
3267 ,data_access_set_id => g_all_ledgers_tab(l_ledger_id).access_set_id
3268 ,req_id => l_req_id);
3269
3270 END LOOP;
3271
3272 IF (g_group_id_tab.COUNT > 0) THEN
3273 set_transfer_status;
3274 delete_transfer_log;
3275 END IF;
3276
3277 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3278 trace('complete_batch.End',C_LEVEL_PROCEDURE,l_log_module);
3279 END IF;
3280
3281 EXCEPTION
3282 WHEN xla_exceptions_pkg.application_exception THEN
3283 RAISE;
3284 WHEN OTHERS THEN
3285 xla_exceptions_pkg.raise_message
3286 (p_location => 'xla_transfer_pkg.complete_batch');
3287 END complete_batch;
3288
3289 /*===========================================================================+
3290 | FUNCTION |
3291 | IS_REPORT_DEFN_FOUND |
3292 | |
3293 | DESCRIPTION |
3294 | For a given ledger, check if an Open Account Balances Listing Report | |
3295 | Definition does exist. |
3296 | When no report definition is found, data manager is not submitted |
3297 | for the ledger. |
3298 | |
3299 | SCOPE - PRIVATE |
3300 | |
3301 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3302 | |
3303 | ARGUMENTS |
3304 | p_ledger_id |
3305 | |
3306 | NOTES |
3307 | |
3308 +===========================================================================*/
3309
3310 FUNCTION is_report_defn_found
3311 (p_ledger_id IN NUMBER
3312 ,p_je_source_name IN VARCHAR2)
3313 RETURN BOOLEAN IS
3314
3315 l_cnt PLS_INTEGER DEFAULT 0;
3316 l_log_module VARCHAR2(240);
3317
3318 BEGIN
3319 IF g_log_enabled THEN
3320 l_log_module := C_DEFAULT_MODULE||'.is_report_defn_found';
3321 END IF;
3322
3323 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3324 trace('is_report_defn_found.Begin'
3325 ,C_LEVEL_PROCEDURE
3326 ,l_log_module);
3327 END IF;
3328
3329 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3330 -- Print all input parameters
3331 trace('p_ledger_id = ' || p_ledger_id
3332 ,C_LEVEL_STATEMENT
3333 ,l_log_module);
3334 trace('p_je_source_name = ' || p_je_source_name
3335 ,C_LEVEL_STATEMENT
3336 ,l_log_module);
3337 END IF;
3338
3339 SELECT COUNT(1)
3340 INTO l_cnt
3341 FROM xla_tb_definitions_b xtd
3342 ,xla_tb_defn_je_sources xjs
3343 WHERE xtd.definition_code = xjs.definition_code
3344 AND xjs.je_source_name = p_je_source_name
3345 and xtd.ledger_id = p_ledger_id;
3346
3347 IF l_cnt > 0 THEN
3348
3349 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3350 trace('# of report definitions ' || l_cnt
3351 ,C_LEVEL_STATEMENT
3352 ,l_log_module);
3353 END IF;
3354
3355 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3356 trace('is_report_defn_found.End'
3357 ,C_LEVEL_PROCEDURE
3358 ,l_log_module);
3359 END IF;
3360
3361 RETURN TRUE;
3362
3363 ELSE
3364
3365 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3366 trace('No report definition for this ledger'
3367 ,C_LEVEL_STATEMENT
3368 ,l_log_module);
3369 END IF;
3370
3371 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3372 trace('is_report_defn_found.End'
3373 ,C_LEVEL_PROCEDURE
3374 ,l_log_module);
3375 END IF;
3376
3377 RETURN FALSE;
3378
3379 END IF;
3380
3381
3382 EXCEPTION
3383 WHEN xla_exceptions_pkg.application_exception THEN
3384 RAISE;
3385 WHEN OTHERS THEN
3386 xla_exceptions_pkg.raise_message
3387 (p_location => 'xla_transfer_pkg.is_report_defn_found');
3388 END is_report_defn_found;
3389
3390 /*===========================================================================+
3391 | PROCEDURE |
3392 | GL_TRANSFER_MAIN |
3393 | |
3394 | DESCRIPTION |
3395 | Main PROCEDURE that controls THE process flow. ALL THE sub procedures |
3396 | are called FROM FROM this PROCEDURE. |
3397 | |
3398 | SCOPE - PUBLIC |
3399 | |
3400 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3401 | |
3402 | ARGUMENTS |
3403 | p_application_id Application ID OF THE CALLING application. |
3404 | |
3405 | NOTES |
3406 | |
3407 +===========================================================================*/
3408
3409
3410 PROCEDURE gl_transfer_main(p_application_id IN NUMBER
3411 ,p_transfer_mode IN VARCHAR2
3412 ,p_ledger_id IN NUMBER
3413 ,p_securiy_id_int_1 IN NUMBER DEFAULT NULL
3414 ,p_securiy_id_int_2 IN NUMBER DEFAULT NULL
3415 ,p_securiy_id_int_3 IN NUMBER DEFAULT NULL
3416 ,p_securiy_id_char_1 IN VARCHAR2 DEFAULT NULL
3417 ,p_securiy_id_char_2 IN VARCHAR2 DEFAULT NULL
3418 ,p_securiy_id_char_3 IN VARCHAR2 DEFAULT NULL
3419 ,p_valuation_method IN VARCHAR2 DEFAULT NULL
3420 ,p_process_category IN VARCHAR2 DEFAULT NULL
3421 ,p_accounting_batch_id IN NUMBER DEFAULT NULL
3422 ,p_entity_id IN NUMBER DEFAULT NULL
3423 ,p_batch_name IN VARCHAR2 DEFAULT NULL
3424 ,p_end_date IN DATE DEFAULT NULL
3425 ,p_submit_gl_post IN VARCHAR2 DEFAULT 'N'
3426 ,p_caller IN VARCHAR2 DEFAULT C_ACCTPROG_BATCH
3427 ) IS
3428 --Local Variables
3429 l_ledger_id NUMBER;
3430 l_gllezl_request_id PLS_INTEGER;
3431 l_log_module VARCHAR2(240);
3432 l_count PLS_INTEGER;
3433 l_req_id PLS_INTEGER;
3434 l_index PLS_INTEGER;
3435
3436 BEGIN
3437 IF g_log_enabled THEN
3438 l_log_module := C_DEFAULT_MODULE||'.gl_transfer_main';
3439 END IF;
3440
3441 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3442 trace('gl_transfer_main.Begin',C_LEVEL_PROCEDURE,l_log_module);
3443 END IF;
3444
3445
3446 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3447 -- Print all input parameters
3448 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
3449 trace('p_application_id = ' || p_application_id ,C_LEVEL_STATEMENT,l_log_module);
3450 trace('p_transfer_mode = ' || p_transfer_mode ,C_LEVEL_STATEMENT,l_log_module);
3451 trace('p_ledger_id = ' || p_ledger_id ,C_LEVEL_STATEMENT,l_log_module);
3452 trace('p_securiy_id_int_1 = ' || p_securiy_id_int_1 ,C_LEVEL_STATEMENT,l_log_module);
3453 trace('p_securiy_id_int_2 = ' || p_securiy_id_int_2 ,C_LEVEL_STATEMENT,l_log_module);
3454 trace('p_securiy_id_int_3 = ' || p_securiy_id_int_3 ,C_LEVEL_STATEMENT,l_log_module);
3455 trace('p_securiy_id_char_1 = ' || p_securiy_id_char_1 ,C_LEVEL_STATEMENT,l_log_module);
3456 trace('p_securiy_id_char_2 = ' || p_securiy_id_char_2 ,C_LEVEL_STATEMENT,l_log_module);
3457 trace('p_securiy_id_char_3 = ' || p_securiy_id_char_3 ,C_LEVEL_STATEMENT,l_log_module);
3458 trace('p_valuation_method = ' || p_valuation_method ,C_LEVEL_STATEMENT,l_log_module);
3459 trace('p_process_category = ' || p_process_category ,C_LEVEL_STATEMENT,l_log_module);
3460 trace('p_accounting_batch_id = ' || p_accounting_batch_id,C_LEVEL_STATEMENT,l_log_module);
3461 trace('p_entity_id = ' || p_entity_id ,C_LEVEL_STATEMENT,l_log_module);
3462 trace('p_batch_name = ' || p_batch_name ,C_LEVEL_STATEMENT,l_log_module);
3463 trace('p_end_date = ' || TO_CHAR(p_end_date,'MM/DD/YYYY'),C_LEVEL_STATEMENT,l_log_module);
3464 trace('p_submit_gl_post = ' || p_submit_gl_post ,C_LEVEL_STATEMENT,l_log_module);
3465 trace('p_caller = ' || p_caller ,C_LEVEL_STATEMENT,l_log_module);
3466 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
3467
3468 trace('Global variables',C_LEVEL_STATEMENT,l_log_module);
3469 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
3470 trace('g_use_ledger_security = ' || g_use_ledger_security,C_LEVEL_STATEMENT,l_log_module);
3471 trace('g_access_set_id = ' || g_access_set_id ,C_LEVEL_STATEMENT,l_log_module);
3472 trace('g_sec_access_set_id = ' || g_sec_access_set_id ,C_LEVEL_STATEMENT,l_log_module);
3473 trace('---------------------------------------------------',C_LEVEL_STATEMENT,l_log_module);
3474 END IF;
3475
3476
3477 -- Set Global Variables
3478 g_application_id := p_application_id;
3479 g_entity_id := p_entity_id;
3480 g_end_date := p_end_date;
3481 g_user_id := fnd_global.user_id;
3482 g_request_id := fnd_global.conc_request_id;
3483 g_transfer_mode := p_transfer_mode;
3484 g_accounting_batch_id := p_accounting_batch_id;
3485 g_batch_name := p_batch_name;
3486 g_program_id := fnd_global.conc_program_id;
3487 g_security_id_int_1 := p_securiy_id_int_1;
3488 g_security_id_int_2 := p_securiy_id_int_2;
3489 g_security_id_int_3 := p_securiy_id_int_3;
3490 g_security_id_char_1 := p_securiy_id_char_1;
3491 g_security_id_char_2 := p_securiy_id_char_2;
3492 g_security_id_char_3 := p_securiy_id_char_3;
3493 g_valuation_method := p_valuation_method;
3494 g_process_category := p_process_category;
3495 g_caller := p_caller;
3496
3497 -- Validate input parameters
3498 validate_input_parameters;
3499 set_transaction_security;
3500
3501 --Get application information
3502 set_application_info;
3503
3504 -- Check if GL is installed.
3505 IF g_disable_gllezl_flag = 'N' THEN
3506 trace('Submit Journal Import has been enabled.',C_LEVEL_STATEMENT,l_log_module);
3507 ELSE
3508 trace('Submit Journal Import has been disabled.',C_LEVEL_STATEMENT,l_log_module);
3509 END IF;
3510
3511 --Get ledgers to process
3512 get_ledgers(p_ledger_id);
3513
3514 IF (p_transfer_mode = 'STANDALONE') THEN
3515 trace('Checking for failed batches',C_LEVEL_STATEMENT,l_log_module);
3516 -- Check for previously failed batches.
3517 --
3518 recover_batch;
3519 -- Initialize the group_id table.
3520 g_group_id_tab.DELETE;
3521 --
3522 -- Perform period validation.
3523 --
3524 --
3525 -- Commenting out the call to validate_accounting_periods for bug 5438564
3526 --
3527 /* validate_accounting_periods (p_ledger_id => p_ledger_id); */
3528 END IF;
3529
3530 --
3531 -- Loop for each primary and secondary ledger
3532 --
3533
3534 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3535 trace('Number of primary and secondary ledgers to process = ' || g_primary_ledgers_tab.COUNT,C_LEVEL_STATEMENT,l_log_module);
3536 END IF;
3537
3538 IF g_caller = C_ACCTPROG_BATCH AND g_transfer_mode = 'COMBINED' THEN
3539 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3540 LOOP
3541 FOR j IN xla_accounting_pkg.g_array_ledger_id.FIRST..xla_accounting_pkg.g_array_ledger_id.LAST
3542 LOOP
3543 IF xla_accounting_pkg.g_array_ledger_id(j) = g_primary_ledgers_tab(i).ledger_id THEN
3544 g_primary_ledgers_tab(i).group_id := xla_accounting_pkg.g_array_group_id(j);
3545 exit;
3546 END IF;
3547 END LOOP;
3548 END LOOP;
3549 END IF;
3550
3551 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3552 LOOP
3553 l_ledger_id := g_primary_ledgers_tab(i).ledger_id;
3554 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3555 trace('Primary/Secondary ledger loop',C_LEVEL_STATEMENT,l_log_module);
3556 trace('Loop for each primary and secondary ledger',C_LEVEL_STATEMENT,l_log_module);
3557 trace('Ledger Name = ' || g_all_ledgers_tab(l_ledger_id).NAME || ' Ledger Id = ' || l_ledger_id,C_LEVEL_STATEMENT,l_log_module);
3558 END IF;
3559
3560 -- Initialize ledgers array
3561 g_ledger_id_tab.DELETE;
3562
3563 --Get ledger level options
3564 get_ledger_options(p_ledger_id => l_ledger_id);
3565
3566
3567 --
3568 -- Populate group id and inter_run_id;
3569 --
3570 --For bug fix 7677948
3571 IF g_caller = C_ACCTPROG_BATCH AND g_transfer_mode = 'COMBINED' THEN
3572 SELECT gl_journal_import_s.NEXTVAL
3573 INTO g_primary_ledgers_tab(i).interface_run_id
3574 FROM dual;
3575 ELSE
3576 SELECT gl_journal_import_s.NEXTVAL
3577 ,gl_interface_control_s.NEXTVAL
3578 INTO g_primary_ledgers_tab(i).interface_run_id
3579 ,g_primary_ledgers_tab(i).group_id
3580 FROM dual;
3581
3582 --For bug fix 6941347
3583 g_arr_group_id(g_arr_group_id.COUNT +1):= g_primary_ledgers_tab(i).group_id;
3584
3585 END IF;
3586
3587 g_group_id := g_primary_ledgers_tab(i).group_id;
3588 g_interface_run_id := g_primary_ledgers_tab(i).interface_run_id;
3589 g_group_id_tab(i) := g_group_id;
3590
3591
3592 IF (g_primary_ledgers_tab(i).ledger_category_code = 'PRIMARY') THEN
3593 -- Get associated ALC ledgers
3594 --
3595 get_alc_ledgers;
3596 ELSE
3597 g_ledger_id_tab(1) := g_primary_ledgers_tab(i).ledger_id;
3598 END IF;
3599
3600
3601 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3602 trace('Updating Group ID',C_LEVEL_STATEMENT,l_log_module);
3603 END IF;
3604
3605 -- Set the group id
3606 FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last
3607 LOOP
3608 g_all_ledgers_tab(g_ledger_id_tab(i)).group_id := g_group_id;
3609 END LOOP;
3610
3611 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3612 FOR i IN g_ledger_id_tab.first..g_ledger_id_tab.last
3613 LOOP
3614 trace('Ledgers selected for the processing',C_LEVEL_STATEMENT,l_log_module);
3615 trace('Ledger id = ' ||g_ledger_id_tab(i),C_LEVEL_STATEMENT,l_log_module);
3616 END LOOP;
3617 END IF;
3618
3619 IF (g_parent_group_id IS NULL) THEN
3620 g_parent_group_id := g_group_id;
3621 END IF;
3622
3623 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3624 ---------------------------------------------------------------------
3625 trace('Group_id = ' || g_group_id,C_LEVEL_STATEMENT,l_log_module);
3626 trace('Interface_run_id = ' || g_interface_run_id,C_LEVEL_STATEMENT,l_log_module);
3627 trace('Inserting an entry into the audit table',C_LEVEL_STATEMENT,l_log_module);
3628 ---------------------------------------------------------------------
3629 END IF;
3630
3631 -- Select entries to transfer
3632 select_journal_entries;
3633
3634 -- Proceed further only if there are records to process.
3635 IF g_proceed = 'Y' THEN
3636 --
3637 -- Create a log entry
3638 --
3639 insert_transfer_log(g_primary_ledgers_tab(i).ledger_id);
3640
3641 --Added for bug 11855000 Start
3642 --perform funds reserve if the ledger is bc enabled
3643 --and for application AP
3644 --initialize packet_id to zero
3645
3646 IF g_application_id = 200 THEN
3647 g_packet_id := 0;
3648
3649 g_packet_id := reserve_funds(g_group_id, g_application_id);
3650
3651 if g_packet_id >0 then
3652 trace('Packet_id returned is '||g_packet_id,C_LEVEL_STATEMENT,l_log_module);
3653 elsif g_packet_id = 0 then
3654 trace('Packet_id returned is '||g_packet_id||'. No Eligible headers to be reserved',C_LEVEL_STATEMENT,l_log_module);
3655 else
3656 trace('Error when funds reservation Packet_id is '||g_packet_id,C_LEVEL_STATEMENT,l_log_module);
3657 recover_batch;
3658 end if;
3659 ELSE
3660 trace('Funds check performed only for application_id 200. Provided application_id is '||g_application_id,C_LEVEL_STATEMENT,l_log_module);
3661 END IF;
3662
3663 --
3664 --Added for bug 11855000 End
3665
3666 --
3667 -- Populate the GL_INTERFACE table
3668 --
3669 insert_gl_interface;
3670 IF g_disable_gllezl_flag = 'N' THEN
3671 IF (get_gllezl_status) THEN
3672 insert_interface_control
3673 (p_ledger_id => g_primary_ledgers_tab(i).ledger_id
3674 ,p_table_name => g_gl_interface_table_name
3675 );
3676
3677 IF (C_LEVEL_STATEMENT>= g_log_level) THEN
3678 trace('Calling PSA_FUNDS_CHECKER_PKG',C_LEVEL_STATEMENT,l_log_module);
3679 END IF;
3680
3681 PSA_FUNDS_CHECKER_PKG.populate_group_id
3682 (p_grp_id => g_primary_ledgers_tab(i).group_id
3683 ,p_application_id => g_application_id
3684 ,p_je_batch_name => g_batch_name
3685 );
3686
3687 -- Submit Journal Import
3688 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'- Submitting the Journal Import');
3689 l_gllezl_request_id := submit_journal_import
3690 (p_ledger_id => g_primary_ledgers_tab(i).ledger_id
3691 ,p_interface_run_id => g_interface_run_id
3692 );
3693
3694 IF l_gllezl_request_id > 0 THEN
3695 -- Journal Import Success
3696 g_gllezl_requests_tab(i) := l_gllezl_request_id;
3697 g_primary_ledgers_tab(i).gllezl_request_id := l_gllezl_request_id;
3698 ELSE
3699 -- Journal Import Failed
3700
3701 -- Journal Import Failed
3702 -- bug#8691650
3703 IF g_gl_interface_table_name = 'GL_INTERFACE' THEN
3704 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3705 DELETE FROM gl_interface
3706 WHERE user_je_source_name = g_user_source_name
3707 AND group_id = g_group_id_tab(i);
3708 END IF;
3709
3710 recover_batch;
3711 xla_exceptions_pkg.raise_exception;
3712 END IF;
3713
3714 ELSE
3715 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3716 trace('get_gllezl_status return false, raise exception',C_LEVEL_STATEMENT,l_log_module);
3717 END IF;
3718
3719 --8429053
3720 --When get_gllezl_status returns false, data that are newly inserted
3721 --into GL_INTERFACE table are not deleted. Added below code to
3722 --delete those data in GL_INTERFACE.
3723
3724 If g_gl_interface_table_name = 'GL_INTERFACE' Then
3725
3726 --bug#8691650 delete for all the group ids in a loop
3727
3728 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3729 delete from gl_interface
3730 where user_je_source_name = g_user_source_name
3731 and group_id = g_group_id_tab(i);
3732
3733 /* commented 8691650
3734 delete from gl_interface
3735 where user_je_source_name = g_user_source_name
3736 and group_id = g_primary_ledgers_tab(i).group_id
3737 and request_id = g_request_id;
3738 */
3739
3740 IF SQL%NOTFOUND THEN
3741 IF (C_LEVEL_ERROR >= g_log_level) THEN
3742 trace('No rows found in GL_INTERFACE Table.',C_LEVEL_ERROR,l_log_module);
3743 END IF;
3744 ELSE
3745 IF (C_LEVEL_ERROR >= g_log_level) THEN
3746 trace(SQL%ROWCOUNT || 'Rows deleted from GL_INTERFACE Table',C_LEVEL_ERROR,l_log_module);
3747 END IF;
3748 END IF;
3749 END IF;
3750
3751 recover_batch;
3752 xla_exceptions_pkg.raise_exception;
3753 END IF;
3754 END IF;
3755 ELSE
3756 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3757 trace('There are no entries to process in the ledger '|| g_all_ledgers_tab(l_ledger_id).NAME,C_LEVEL_STATEMENT,l_log_module);
3758 END IF;
3759 -- g_group_id_tab.DELETE(i); group id is needed to drop the tables
3760 END IF;
3761 END LOOP; -- primary/secondary ledgers loop
3762
3763 -- Wait for journal import to complete.
3764 -- IF g_proceed = 'Y' THEN -- Commented for bug 8417930
3765
3766 IF g_disable_gllezl_flag = 'N' AND g_gllezl_requests_tab.COUNT > 0 THEN
3767 IF (wait_for_gllezl) THEN
3768 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||'- Journal Import completed ');
3769 complete_batch(p_submit_gl_post => p_submit_gl_post);
3770
3771 -- Drop GL_INTERFACE tables.
3772 --7512923 GL_INTERFACE tables will not be dropped.
3773 /*IF g_caller <> C_ACCTPROG_DOCUMENT THEN -- Document mode use GL_INTERFACE table only
3774 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3775 LOOP
3776 IF g_primary_ledgers_tab(i).gllezl_request_id IS NOT NULL
3777 AND (g_entity_id IS NULL OR g_disable_gllezl_flag = 'N') THEN
3778 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3779 trace('Dropping table ' || 'XLA_GLT_'||g_primary_ledgers_tab(i).group_id,C_LEVEL_STATEMENT,l_log_module);
3780 END IF;
3781 GL_JOURNAL_IMPORT_PKG.drop_table('XLA_GLT_' || g_primary_ledgers_tab(i).group_id);
3782 END IF;
3783 END LOOP;
3784 END IF;*/
3785
3786 l_index := g_all_ledgers_tab.FIRST;
3787 FOR i in g_all_ledgers_tab.FIRST..g_all_ledgers_tab.LAST
3788 LOOP
3789 -- Submit Trial Balance Data Manager only if definitions exist
3790 -- for a Ledger and a JE source.
3791 --
3792 IF is_report_defn_found
3793 (p_ledger_id => g_all_ledgers_tab(l_index).ledger_id
3794 ,p_je_source_name => g_je_source_name)
3795 THEN
3796 IF g_all_ledgers_tab(l_index).gllezl_request_id IS NOT NULL THEN
3797 trace('Submitting Trial Balance Data Manager for ledger ID = ' || g_all_ledgers_tab(l_index).ledger_id,C_LEVEL_STATEMENT,l_log_module);
3798 l_req_id := FND_REQUEST.SUBMIT_REQUEST
3799 (application => 'XLA'
3800 ,program => 'XLATBDMG'
3801 ,description => NULL
3802 ,start_time => SYSDATE
3803 ,sub_request => NULL
3804 ,argument1 => NULL -- dummy application (for bug 8271212 nksurana)
3805 ,argument2 => g_all_ledgers_tab(l_index).ledger_id -- Foster City Corp. l_ledger_id
3806 ,argument3 => g_all_ledgers_tab(l_index).GROUP_id --l_group_id
3807 ,argument4 => NULL --l_definition_code
3808 ,argument5 => NULL --l_request_mode
3809 ,argument6 => g_je_source_name --bug#7320079 NULL --l_je_source_name
3810 ,argument7 => NULL --l_upg_batch_id
3811 );
3812 /*bug#7320079 Passed the je_source_name while spawning data manager. This helps in finding the correct
3813 application from which the data manager has been spawned. */
3814
3815 trace('Trial Balance Data Manager Request Id = ' || l_req_id,C_LEVEL_STATEMENT,l_log_module);
3816 END IF;
3817 COMMIT;
3818 END IF;
3819
3820 l_index := g_all_ledgers_tab.NEXT(l_index);
3821 IF l_index IS NULL THEN
3822 EXIT;
3823 END IF;
3824 END LOOP;
3825 END IF;
3826 ELSIF g_disable_gllezl_flag = 'Y' THEN
3827 trace('Journal Import is Disabled',C_LEVEL_STATEMENT,l_log_module);
3828 set_transfer_status;
3829 --Added for bug 11855000 Start
3830 /*
3831 Need to update gl_bc_packets here because Journal import is disabled
3832 and funds in gl bc packets needs to be marked as reserved.
3833 */
3834
3835 IF g_application_id = 200 then
3836 If (g_reserve_flag) then
3837 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3838 trace('Updating gl_bc_packets ',C_LEVEL_STATEMENT,l_log_module);
3839 END IF;
3840 FORALL i IN g_group_id_tab.FIRST..g_group_id_tab.LAST
3841 UPDATE gl_bc_packets
3842 SET status_code = 'A'
3843 WHERE group_id = g_group_id_tab(i)
3844 AND application_id = g_application_id
3845 and status_code = 'C';
3846
3847 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3848 trace('No of rows updated as Reserved in GL_BC_PACKETS ='||SQL%ROWCOUNT,C_LEVEL_PROCEDURE,l_log_module);
3849 END IF;
3850 END IF;
3851 ELSE
3852 trace('Funds check performed only for application_id 200. Provided application_id is '||g_application_id,C_LEVEL_STATEMENT,l_log_module);
3853 END IF;
3854
3855
3856 --Added for bug 11855000 End
3857
3858 -- Added for Bug 9087997 Start
3859
3860 l_index := g_all_ledgers_tab.FIRST;
3861 FOR i in g_all_ledgers_tab.FIRST..g_all_ledgers_tab.LAST
3862 LOOP
3863 -- Submit Trial Balance Data Manager only if definitions exist
3864 -- for a Ledger and a JE source.
3865 --
3866 IF is_report_defn_found
3867 (p_ledger_id => g_all_ledgers_tab(l_index).ledger_id
3868 ,p_je_source_name => g_je_source_name)
3869 THEN
3870
3871 trace('Submitting Trial Balance Data Manager for ledger ID = ' || g_all_ledgers_tab(l_index).ledger_id,C_LEVEL_STATEMENT,l_log_module);
3872 l_req_id := FND_REQUEST.SUBMIT_REQUEST
3873 (application => 'XLA'
3874 ,program => 'XLATBDMG'
3875 ,description => NULL
3876 ,start_time => SYSDATE
3877 ,sub_request => NULL
3878 ,argument1 => NULL -- dummy application (for bug 8271212 nksurana)
3879 ,argument2 => g_all_ledgers_tab(l_index).ledger_id -- Foster City Corp. l_ledger_id
3880 ,argument3 => g_all_ledgers_tab(l_index).GROUP_id --l_group_id
3881 ,argument4 => NULL --l_definition_code
3882 ,argument5 => NULL --l_request_mode
3883 ,argument6 => g_je_source_name --bug#7320079 NULL --l_je_source_name
3884 ,argument7 => NULL --l_upg_batch_id
3885 );
3886 /*bug#7320079 Passed the je_source_name while spawning data manager. This helps in finding the correct
3887 application from which the data manager has been spawned. */
3888
3889 trace('Trial Balance Data Manager Request Id = ' || l_req_id,C_LEVEL_STATEMENT,l_log_module);
3890
3891 COMMIT;
3892 END IF;
3893
3894 l_index := g_all_ledgers_tab.NEXT(l_index);
3895 IF l_index IS NULL THEN
3896 EXIT;
3897 END IF;
3898 END LOOP;
3899
3900 -- Added for Bug 9087997 End
3901
3902 delete_transfer_log;
3903 END IF;
3904
3905 /* Comment start for bug 8417930
3906
3907 ELSE -- g_proceed flag is 'N'
3908 FOR i IN g_primary_ledgers_tab.FIRST..g_primary_ledgers_tab.LAST
3909 LOOP
3910 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3911 trace('Resetting Gl Transfer Flag to N in XLA_AE_HEADERS',C_LEVEL_STATEMENT,l_log_module);
3912 END IF;
3913 UPDATE xla_ae_headers
3914 SET group_id = NULL
3915 ,gl_transfer_status_code = 'N'
3916 ,gl_transfer_date = NULL
3917 ,program_update_date = SYSDATE
3918 ,program_id = g_program_id
3919 ,request_id = g_request_id
3920 WHERE group_id = g_primary_ledgers_tab(i).group_id;
3921 END LOOP;
3922 delete_transfer_log;
3923
3924 Comment End for bug 8417930 */
3925
3926 /* IF g_total_rows_created > 0 THEN
3927 trace('The transfer process completed successfully.',C_LEVEL_STATEMENT,l_log_module);
3928 ELSE
3929 trace('There are no entries to transfer for the specified criteria.',C_LEVEL_STATEMENT,l_log_module);
3930 END IF;
3931 */
3932
3933 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3934 trace('GL_TRANSFER_MAIN.End',C_LEVEL_PROCEDURE,l_log_module);
3935 END IF;
3936 EXCEPTION
3937 WHEN xla_exceptions_pkg.application_exception THEN
3938 RAISE;
3939 WHEN OTHERS THEN
3940 xla_exceptions_pkg.raise_message
3941 (p_location => 'xla_transfer_pkg.gl_transfer_main');
3942 END GL_TRANSFER_MAIN;
3943
3944 BEGIN
3945 -- l_log_module := C_DEFAULT_MODULE;
3946 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3947 g_log_enabled := fnd_log.test
3948 (log_level => g_log_level
3949 ,module => C_DEFAULT_MODULE);
3950
3951 IF NOT g_log_enabled THEN
3952 g_log_level := C_LEVEL_LOG_DISABLED;
3953 END IF;
3954 END XLA_TRANSFER_PKG;