DBA Data[Home] [Help]

PACKAGE BODY: APPS.XLA_TRANSFER_PKG

Source


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