[Home] [Help]
PACKAGE BODY: APPS.XLA_GL_TRANSFER_PKG
Source
1 PACKAGE BODY xla_gl_transfer_pkg AS
2 /* $Header: XLACGLXB.pls 120.25.12020000.2 2013/04/04 06:14:34 sragadde ship $ */
3
4 --if g_entry_type is 'A', actual only; else 'B', actual and budget.
5 --the purpuse is that,the new version is still working for AP and CST,
6 --even if they do not add new column to their AEH and AEL.
7 --The new version is still use old gl_insert code when g_entry_type is 'A'.
8 g_entry_type VARCHAR2(1);
9
10 -- Accounting Table variables.
11 g_application_id NUMBER(15);
12 g_events_table VARCHAR2(30);
13 g_headers_table VARCHAR2(30);
14 g_lines_table VARCHAR2(30);
15 g_periods_table VARCHAR2(30);
16 g_encumbrance_table VARCHAR2(30);
17 g_actual_table_alias VARCHAR2(30);
18 g_enc_table_alias VARCHAR2(30);
19 g_enc_sequence_name VARCHAR2(30);
20 g_lines_sequence_name VARCHAR2(30);
21 g_program_id NUMBER;
22 g_user_id NUMBER;
23 g_base_currency_code VARCHAR2(15);
24
25 -- Flow Control Flags
26 g_proceed VARCHAR2(1);
27 g_rec_transfer_flag VARCHAR2(1); -- add the flag to solve fund_check and journal_import calling problem
28 g_enc_proceed VARCHAR2(1);
29
30 g_headers_selected NUMBER := 0; -- No. of headers selected
31 g_batch_name VARCHAR2(30);
32 g_program_name VARCHAR2(30);
33 g_debug_info VARCHAR2(4000);
34 g_sob_rows_created NUMBER := 0;
35 g_total_rows_created NUMBER := 0;
36
37 -- Record counters to display control info.
38
39 g_periods_cnt NUMBER := 0;
40 g_rec_transferred NUMBER := 0;
41 g_cnt_transfer_errors NUMBER := 0;
42 g_cnt_acct_errors NUMBER := 0;
43
44 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
45 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
46 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
47 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
48 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
49 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
50
51 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
52 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_gl_transfer_pkg';
53
54 g_log_level NUMBER;
55 g_log_enabled BOOLEAN;
56
57 g_line_type VARCHAR2(4000);
58
59 PROCEDURE trace
60 (p_msg IN VARCHAR2
61 ,p_level IN NUMBER
62 ,p_module IN VARCHAR2 ) IS
63 l_log_module VARCHAR2(255);
64 BEGIN
65 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
66 fnd_log.message(p_level, NVL(p_module,C_DEFAULT_MODULE));
67 ELSIF p_level >= g_log_level THEN
68 fnd_log.string(p_level, NVL(p_module,C_DEFAULT_MODULE), p_msg);
69 END IF;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
74 fnd_log.string(LOG_LEVEL => FND_LOG.LEVEL_UNEXPECTED,
75 MODULE => NVL(p_module,C_DEFAULT_MODULE),
76 MESSAGE => 'Unexpected Error While Executing ' || p_module );
77 END IF;
78 END trace;
79
80 -- The function is used by the Payables Report
81 -- The function returns number of entries transferred to GL, No. of entries with
82 -- the transfer error and entries with the accounting entry creation errors.
83 FUNCTION get_control_info( p_sob_id NUMBER,
84 p_period_name VARCHAR2,
85 p_error_type VARCHAR2
86 ) RETURN NUMBER IS
87 l_rec_count NUMBER := 0;
88 l_log_module VARCHAR2(255);
89 BEGIN
90
91 IF g_log_enabled THEN
92 l_log_module := C_DEFAULT_MODULE||'.get_control_info';
93 END IF;
94
95 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
96 trace
97 (p_msg => 'BEGIN of function GET_CONTROL_INFO'
98 ,p_level => C_LEVEL_PROCEDURE
99 ,p_module => l_log_module);
100 END IF;
101
102 FOR i IN g_control_info.first..g_control_info.last loop
103 IF (g_control_info(i).sob_id = p_sob_id) AND
104 (g_control_info(i).period_name = p_period_name) then
105 IF p_error_type = 'ENTRIES_TRANSFERRED' then
106 l_rec_count := g_control_info(i).rec_transferred;
107 ELSIF p_error_type = 'TRANSFER_ERRORS' THEN
108 l_rec_count := g_control_info(i).cnt_transfer_errors;
109 ELSE
110 l_rec_count := g_control_info(i).cnt_acct_errors;
111 END IF;
112 END IF;
113 END LOOP;
114
115 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
116 trace
117 (p_msg => 'return value = ' || TO_CHAR(l_rec_count)
118 ,p_level => C_LEVEL_PROCEDURE
119 ,p_module => l_log_module);
120
121 trace
122 (p_msg => 'END of function GET_CONTROL_INFO'
123 ,p_level => C_LEVEL_PROCEDURE
124 ,p_module => l_log_module);
125 END IF;
126
127 RETURN(l_rec_count);
128 EXCEPTION
129 WHEN OTHERS THEN
130 RETURN(0);
131 END get_control_info;
132
133 PROCEDURE xla_message( p_message_code VARCHAR2,
134 p_token_1 VARCHAR2 DEFAULT NULL,
135 p_token_1_value VARCHAR2 DEFAULT NULL,
136 p_token_2 VARCHAR2 DEFAULT NULL,
137 p_token_2_value VARCHAR2 DEFAULT NULL,
138 p_token_3 VARCHAR2 DEFAULT NULL,
139 p_token_3_value VARCHAR2 DEFAULT NULL,
140 p_module_name VARCHAR2,
141 p_level NUMBER
142 ) IS
143 l_log_module VARCHAR2(255);
144 BEGIN
145 -- 1. If p_message_code is NOT NULL, the msg will be interpreted from msg dictionary;
146 -- 2. If p_message_code is NULL, the msg(passed from p_token_1);
147
148 IF g_log_enabled THEN
149 l_log_module := C_DEFAULT_MODULE||'.xla_message';
150 END IF;
151
152 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
153 trace
154 (p_msg => 'BEGIN of procedure XLA_MESSAGE'
155 ,p_level => C_LEVEL_PROCEDURE
156 ,p_module => l_log_module);
157 END IF;
158
159
160 IF p_message_code is NOT NULL THEN
161 FND_MESSAGE.SET_NAME('XLA',p_message_code);
162 IF p_token_1 IS NOT NULL THEN
163 fnd_message.set_token(p_token_1, p_token_1_value);
164 END IF;
165 IF p_token_2 IS NOT NULL THEN
166 fnd_message.set_token(p_token_2, p_token_2_value);
167 END IF;
168 IF p_token_3 IS NOT NULL THEN
169 fnd_message.set_token(p_token_3, p_token_3_value);
170 END IF;
171 trace(fnd_message.get, p_level, p_module_name);
172
173 ELSE
174 trace(p_token_1, p_level, p_module_name);
175 END IF;
176
177 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
178 trace
179 (p_msg => 'END of procedure XLA_MESSAGE'
180 ,p_level => C_LEVEL_PROCEDURE
181 ,p_module => l_log_module);
182 END IF;
183
184
185 END xla_message;
186
187 PROCEDURE xla_app_error( p_message_code VARCHAR2,
188 p_token_1 VARCHAR2,
189 p_token_1_value VARCHAR2,
190 p_token_2 VARCHAR2 DEFAULT NULL,
191 p_token_2_value VARCHAR2 DEFAULT NULL,
192 p_token_3 VARCHAR2 DEFAULT NULL,
193 p_token_3_value VARCHAR2 DEFAULT NULL,
194 p_debug VARCHAR2
195 ) IS
196 l_log_module VARCHAR2(255);
197 BEGIN
198 IF g_log_enabled THEN
199 l_log_module := C_DEFAULT_MODULE||'.xla_app_error';
200 END IF;
201
202 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
203 trace
204 (p_msg => 'BEGIN of procedure XLA_APP_ERROR'
205 ,p_level => C_LEVEL_PROCEDURE
206 ,p_module => l_log_module);
207 END IF;
208
209 FND_MESSAGE.SET_NAME('XLA',p_message_code);
210 IF p_token_1 IS NOT NULL THEN
211 fnd_message.set_token(p_token_1, p_token_1_value);
212 END IF;
213 IF p_token_2 IS NOT NULL THEN
214 fnd_message.set_token(p_token_2, p_token_2_value);
215 END IF;
216 IF p_token_3 IS NOT NULL THEN
217 fnd_message.set_token(p_token_3, p_token_3_value);
218 END IF;
219
220 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
221 trace
222 (p_msg => 'END of procedure XLA_APP_ERROR'
223 ,p_level => C_LEVEL_PROCEDURE
224 ,p_module => l_log_module);
225 END IF;
226
227 END ;
228
229 -- Function to return the link_id
230 FUNCTION get_linkid(p_program_name VARCHAR2) RETURN NUMBER IS
231 l_linkid NUMBER;
232 statement VARCHAR2(1000);
233 l_log_module VARCHAR2(255);
234 BEGIN
235 -- Returns link id
236 IF g_log_enabled THEN
237 l_log_module := C_DEFAULT_MODULE||'.get_linkid';
238 END IF;
239
240 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
241 trace
242 (p_msg => 'BEGIN of function GET_LINKID'
243 ,p_level => C_LEVEL_PROCEDURE
244 ,p_module => l_log_module);
245
246 END IF;
247
248 statement := 'select ' || g_lines_sequence_name ||
249 '.NEXTVAL from dual';
250
251 EXECUTE IMMEDIATE statement INTO l_linkid;
252
253 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
254 trace
255 (p_msg => 'return value = ' || to_char(l_linkid)
256 ,p_level => C_LEVEL_PROCEDURE
257 ,p_module => l_log_module);
258
259 trace
260 (p_msg => 'END of function GET_LINKID'
261 ,p_level => C_LEVEL_PROCEDURE
262 ,p_module => l_log_module);
263
264 END IF;
265
266 RETURN (l_linkid);
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
270 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
271 l_log_module,
272 'Unexpected Error While Executing ' || l_log_module);
273 END IF;
274 END get_linkid;
275
276
277 -- Does period validation if GL is Installed.
278 PROCEDURE validate_periods(p_selection_type IN VARCHAR2,
279 p_sob_list IN t_sob_list,
280 p_program_name IN VARCHAR2,
281 p_start_date IN DATE,
282 p_end_date IN DATE ) IS
283 l_periods VARCHAR2(30);
284 l_start_date DATE;
285 l_begin_date DATE;
286 l_end_date DATE;
287 l_open_start_date DATE;
288 l_open_end_date DATE;
289 l_max_end_date DATE;
290 l_period_status VARCHAR2(1);
291 l_headers_cnt NUMBER := 0;
292 cid NUMBER;
293 statement VARCHAR2(2000);
294 l_log_module VARCHAR2(255);
295 -- Get periods that are not Open or Future Open in the specified
296 -- date range.
297
298 CURSOR c_getClosedPeriods(c_sob_id NUMBER,
299 c_start_date DATE,
300 c_end_date DATE ) IS
301 SELECT gps.period_name, gps.start_date, gps.end_date, gps.closing_status
302 FROM gl_period_statuses gps
303 WHERE gps.application_id = 101
304 AND gps.set_of_books_id = c_sob_id
305 AND Nvl(gps.adjustment_period_flag,'N') = 'N'
306 AND gps.end_date >= c_start_date
307 AND gps.start_date <= c_end_date
308 AND gps.closing_status NOT IN ('O','F')
309 ORDER BY gps.start_date;
310 BEGIN
311
312 IF g_log_enabled THEN
313 l_log_module := C_DEFAULT_MODULE||'.validate_periods';
314 END IF;
315
316 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
317 trace
318 (p_msg => 'BEGIN of procedure VALIDATE_PERIODS'
319 ,p_level => C_LEVEL_PROCEDURE
320 ,p_module => l_log_module);
321
322 END IF;
323
324 -- Validate period for all set of books.
325 FOR i IN p_sob_list.first..p_sob_list.last LOOP
326 IF p_sob_list(i).sob_id IS NOT NULL THEN
327 IF p_selection_type = 1 THEN
328 -- Get the start date of the first open or future open
329 -- period and end date of the last open period.
330 BEGIN
331 SELECT min(start_date), max(end_date)
332 INTO l_open_start_date, l_open_end_date
333 FROM gl_period_statuses
334 WHERE application_id = 101
335 AND set_of_books_id = p_sob_list(i).sob_id
336 AND Nvl(adjustment_period_flag,'N') = 'N'
337 AND closing_status IN ( 'O','F');
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 -- There are no open periods
341 -- Log message to a log file
342 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
343 xla_message('XLA_GLT_NO_OPEN_PERIODS',
344 'SOB_NAME', p_sob_list(i).sob_name,
345 '', '',
346 '','',
347 l_log_module,
348 C_LEVEL_EXCEPTION );
349 END IF;
350
351 -- Log message to output file
352 xla_app_error(
353 p_message_code => 'XLA_GLT_NO_OPEN_PERIODS',
354 p_token_1 => 'SOB_NAME',
355 p_token_1_value => p_sob_list(i).sob_name,
356 p_debug => 'N' );
357
358 APP_EXCEPTION.RAISE_EXCEPTION;
359 END;
360
361 -- Set start date
362
363 -- Bug3226680. Changed the condition from Greatest to NVL.
364 l_start_date := NVL(p_start_date,l_open_start_date);
365
366 -- Check for closed periods
367 OPEN c_getClosedPeriods(p_sob_list(i).sob_id,
368 l_start_date,
369 p_end_date
370 );
371 LOOP
372 FETCH c_getClosedPeriods
373 INTO l_periods, l_begin_date, l_end_date, l_period_status;
374 EXIT WHEN c_getClosedPeriods%NOTFOUND;
375
376 OPEN c_get_program_info(p_program_name);
377 LOOP -- to process multiple accounting entities
378 FETCH c_get_program_info
379 INTO g_events_table, g_headers_table, g_lines_table,
380 g_encumbrance_table, g_lines_sequence_name,
381 g_enc_sequence_name, g_actual_table_alias,
382 g_enc_table_alias;
383 EXIT WHEN c_get_program_info%NOTFOUND;
384 statement :=
385 ' SELECT COUNT(*)
386 FROM dual
387 WHERE EXISTS (
388 SELECT ''x''
389 FROM ' || g_headers_table ||
390 ' WHERE accounting_date BETWEEN :b_begin_date AND :b_end_date
391 AND set_of_books_id = :sob_id
392 AND gl_transfer_flag = ''N'')';
393 EXECUTE IMMEDIATE statement
394 INTO l_headers_cnt
395 USING l_begin_date, l_end_date, p_sob_list(i).sob_id;
396
397 -- Display an error message if there are records in the
398 -- closed period.
399 IF l_headers_cnt > 0 THEN
400 g_proceed := 'N';
401 CLOSE c_get_program_info;
402 CLOSE c_getClosedPeriods;
403
404 -- Display error message when there are unposted records in given period
405 -- and the period is closed.
406 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
407 xla_message('XLA_GLT_PERIOD_CLOSED',
408 'PERIOD', l_periods,
409 'SOB_NAME', p_sob_list(i).sob_name,
410 '','',
411 l_log_module,
412 C_LEVEL_EXCEPTION );
413 END IF;
414
415 xla_app_error(
416 p_message_code => 'XLA_GLT_PERIOD_CLOSED',
417 p_token_1 => 'PERIOD',
418 p_token_1_value => l_periods,
419 p_token_2 => 'SOB_NAME',
420 p_token_2_value => p_sob_list(i).sob_name,
421 p_debug => 'N' );
422
423 APP_EXCEPTION.RAISE_EXCEPTION;
424 END IF;
425 END LOOP; -- Multiple Accounting Entries
426 CLOSE c_get_program_info;
427 END LOOP; -- Cursor c_getClosedPeriods
428 CLOSE c_getClosedPeriods;
429 ELSE -- Document Level Transfer
430 OPEN c_getClosedPeriods(p_sob_list(i).sob_id,
431 p_start_date,
432 p_end_date
433 );
434 LOOP
435 FETCH c_getClosedPeriods
436 INTO l_periods, l_begin_date, l_end_date, l_period_status;
437 EXIT WHEN c_getClosedPeriods%NOTFOUND;
438 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
439 xla_message('XLA_GLT_PERIOD_CLOSED',
440 'PERIOD', l_periods,
441 'SOB_NAME', p_sob_list(i).sob_name,
442 '','',
443 l_log_module,
444 C_LEVEL_ERROR );
445 END IF;
446
447 xla_app_error(
448 p_message_code => 'XLA_GLT_PERIOD_CLOSED',
449 p_token_1 => 'PERIOD',
450 p_token_1_value => l_periods,
451 p_token_2 => 'SOB_NAME',
452 p_token_2_value => p_sob_list(i).sob_name,
453 p_debug => 'N' );
454
455 APP_EXCEPTION.RAISE_EXCEPTION;
456 END LOOP;
457 CLOSE c_getClosedPeriods;
458 END IF; -- Selection Type
459 END IF;
460 END LOOP; -- Set of Books
461 -- There are no closed periods. The transfer should continue.
462
463 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
464 trace
465 (p_msg => 'END of procedure VALIDATE_PERIODS'
466 ,p_level => C_LEVEL_PROCEDURE
467 ,p_module => l_log_module);
468 END IF;
469
470 END validate_periods;
471
472 -- Get the accounting date range
473 PROCEDURE get_date_range(p_transfer_run_id IN NUMBER,
474 p_start_date OUT NOCOPY DATE,
475 p_end_date OUT NOCOPY DATE ) IS
476 l_statement VARCHAR2(2000);
477 l_log_module VARCHAR2(255);
478 BEGIN
479 IF g_log_enabled THEN
480 l_log_module := C_DEFAULT_MODULE||'.get_date_range';
481 END IF;
482
483 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
484 trace
485 (p_msg => 'BEGIN of procedure GET_DATE_RANGE'
486 ,p_level => C_LEVEL_PROCEDURE
487 ,p_module => l_log_module);
488
489 END IF;
490
491
492 l_statement := ' SELECT MIN(accounting_date), MAX(accounting_date)
493 FROM ' || g_headers_table ||
494 ' WHERE gl_transfer_run_id = :b_transfer_run_id ';
495
496 EXECUTE IMMEDIATE l_statement
497 INTO p_start_date, p_end_date
498 USING p_transfer_run_id;
499
500 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
501
502 trace
503 (p_msg => 'return value --> p_start_date = ' || TO_CHAR(p_start_date)
504 ,p_level => C_LEVEL_PROCEDURE
505 ,p_module => l_log_module);
506
507 trace
508 (p_msg => 'return value --> p_end_date = ' || TO_CHAR(p_end_date)
509 ,p_level => C_LEVEL_PROCEDURE
510 ,p_module => l_log_module);
511
512 trace
513 (p_msg => 'END of procedure GET_DATE_RANGE'
514 ,p_level => C_LEVEL_PROCEDURE
515 ,p_module => l_log_module);
516
517 END IF;
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
522 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
523 l_log_module,
524 'Unexpected Error While Executing ' || l_log_module);
525 END IF;
526 END get_date_range;
527
528 PROCEDURE select_acct_headers( p_selection_type NUMBER,
529 p_set_of_books_id NUMBER,
530 p_source_id NUMBER DEFAULT NULL,
531 p_source_table VARCHAR2 DEFAULT NULL,
532 p_transfer_run_id NUMBER,
533 p_request_id NUMBER,
534 p_ae_category t_ae_category,
535 p_start_date DATE,
536 p_end_date DATE,
537 p_legal_entity_id NUMBER,
538 p_cost_group_id NUMBER,
539 p_cost_type_id NUMBER,
540 p_validate_account VARCHAR2 ) IS
541 statement VARCHAR2(4000) ;
542 l_where VARCHAR2(2000) ;
543 l_where_error VARCHAR2(2000);
544 cid NUMBER;
545 rows_processed NUMBER;
546 l_ae_category VARCHAR2(1000);
547 l_acct_errors NUMBER := 0;
548 l_log_module VARCHAR2(255);
549
550 BEGIN
551
552 IF g_log_enabled THEN
553 l_log_module := C_DEFAULT_MODULE||'.select_acct_headers';
554 END IF;
555
556 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
557 trace
558 (p_msg => 'BEGIN of procedure SELECT_ACCT_HEADERS'
559 ,p_level => C_LEVEL_PROCEDURE
560 ,p_module => l_log_module);
561 END IF;
562
563 IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
564 IF p_legal_entity_id IS NOT NULL THEN
565 -- Manufacturing Transfer
566 l_where := ' AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
567 AND aeh.legal_entity_id = :b_legal_entity_id
568 AND aeh.cost_group_id = :b_cost_group_id
569 AND aeh.cost_type_id = :b_cost_type_id ';
570 ELSE
571 -- Allow user to transfer multiple journal categories. Following will
572 -- generate a string to transfer multiple categories.
573 IF p_ae_category.COUNT > 1 THEN
574 l_ae_category := 'AND aeh.ae_category IN ( ';
575 FOR i IN p_ae_category.FIRST..p_ae_category.LAST LOOP
576 l_ae_category := l_ae_category || '''' || p_ae_category(i) || '''';
577 IF i < p_ae_category.COUNT THEN
578 l_ae_category := l_ae_category || ', ';
579 END IF;
580 END LOOP;
581 l_ae_category := l_ae_category || ' ) ';
582 ELSE
583 l_ae_category := ' AND aeh.ae_category = Decode(:b_journal_category,
584 ''A'', aeh.ae_category,
585 :b_journal_category)';
586 END IF;
587
588 --Where clause is different based on if g_events_table is NULL or not.
589 --This is intended for design enhancement. pls refer to bug#1748305.
590 --Which means: ledgers of an event will be transferred only when the event
591 --status is 'ACCOUNTED'.
592 IF g_events_table IS NULL THEN --eg. CST
593 l_where :=
594 ' AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date '
595 || l_ae_category ||
596 ' AND aeh.set_of_books_id = :b_set_of_books_id '
597 ||
598 ' AND NOT EXISTS ( SELECT ''x'' FROM ' || g_lines_table || ' ael
599 WHERE ael.ae_header_id = aeh.ae_header_id
600 AND ael.accounting_error_code IS NOT NULL ) ';
601 ELSE -- g_events_table is not null like AP
602 l_where_error :=
603 ' AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date '
604 || l_ae_category ||
605 ' AND aeh.set_of_books_id = :b_set_of_books_id
606 AND EXISTS
607 ( SELECT ''x''
608 FROM ' || g_events_table || ' ace
609 WHERE aeh.accounting_event_id = ace.accounting_event_id
610 AND ace.event_status_code = ''ACCOUNTED WITH ERROR'' ) ';
611
612 -- Bug2789042. Added the l_where_error to detect errors.
613 l_where :=
614 ' AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date '
615 || l_ae_category ||
616 ' AND aeh.set_of_books_id = :b_set_of_books_id
617 AND EXISTS
618 ( SELECT ''x''
619 FROM ' || g_events_table || ' ace
620 WHERE aeh.accounting_event_id = ace.accounting_event_id
621 AND ace.event_status_code = ''ACCOUNTED'' ) ';
622 END IF;
623
624
625 END IF;
626 ELSE --for Document Level Transfer
627 -- Currently supported for Payables and PSB only.
628 l_where := ' AND aeh.set_of_books_id = :b_set_of_books_id
629 AND EXISTS
630 ( SELECT ''x''
631 FROM ' || g_events_table || ' ace
632 WHERE aeh.accounting_event_id = ace.accounting_event_id
633 AND ace.event_status_code = ''ACCOUNTED''
634 AND ace.source_id = :b_source_id
635 AND ace.source_table = :b_source_table) ';
636
637
638 -- Bug2789042. Added the l_where_error to detect errors.
639 l_where_error := ' AND aeh.set_of_books_id = :b_set_of_books_id
640 AND EXISTS
641 ( SELECT ''x''
642 FROM ' || g_events_table || ' ace
643 WHERE aeh.accounting_event_id = ace.accounting_event_id
644 AND ace.event_status_code = ''ACCOUNTED WITH ERROR''
645 AND ace.source_id = :b_source_id
646 AND ace.source_table = :b_source_table) ';
647 END IF;
648
649 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
650 xla_message('XLA_GLT_SELECTING_HEADERS',
651 '','',
652 '','',
653 '','',
654 l_log_module,
655 C_LEVEL_STATEMENT);
656 END IF;
657
658 -- Select header entries with no line creation errors and update the error code
659 -- the entries are in the closed GL period.
660
661 statement := ' UPDATE ' || g_headers_table || ' aeh
662 SET program_update_date = Sysdate,
663 program_id = :b_program_id,
664 request_id = :b_request_id,
665 gl_transfer_run_id = :b_transfer_run_id,
666 gl_transfer_error_code = NULL,
667 gl_transfer_flag = ''Y''
668 WHERE gl_transfer_run_id = -1
669 AND gl_transfer_flag IN ( ''N'',''E'')
670 AND aeh.accounting_error_code IS NULL ' || l_where;
671
672 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
673
674 trace
675 (p_msg => 'l_where = ' || l_where
676 ,p_level => C_LEVEL_PROCEDURE
677 ,p_module => l_log_module);
678
679 trace
680 (p_msg => 'l_where_error = ' || l_where_error
681 ,p_level => C_LEVEL_PROCEDURE
682 ,p_module => l_log_module);
683
684 trace
685 (p_msg => 'l_ae_category = ' || l_ae_category
686 ,p_level => C_LEVEL_PROCEDURE
687 ,p_module => l_log_module);
688
689 trace
690 (p_msg => 'statement = ' || statement
691 ,p_level => C_LEVEL_PROCEDURE
692 ,p_module => l_log_module);
693
694 END IF;
695
696
697
698 cid := dbms_sql.open_cursor;
699 dbms_sql.parse(cid, statement, dbms_sql.native);
700
701 -- Bind Variables
702 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
703 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
704 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
705
706 IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
707 IF p_legal_entity_id IS NOT NULL THEN
708 -- Manufacturing Transfer
709 dbms_sql.bind_variable(cid,':b_legal_entity_id', p_legal_entity_id);
710 dbms_sql.bind_variable(cid,':b_cost_group_id', p_cost_group_id);
711 dbms_sql.bind_variable(cid,':b_cost_type_id', p_cost_type_id);
712 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
713 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
714 ELSE
715 -- Allow user to transfer multiple journal categories. Following will
716 -- generate a string to transfer multiple categories.
717 IF p_ae_category.COUNT > 1 THEN
718 NULL;
719 ELSE
720 dbms_sql.bind_variable(cid,':b_journal_category', p_ae_category(1));
721 END IF;
722
723 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
724 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
725 dbms_sql.bind_variable(cid,':b_set_of_books_id', p_set_of_books_id);
726 END IF;
727 ELSE --for Document Level Transfer
728 dbms_sql.bind_variable(cid,':b_set_of_books_id', p_set_of_books_id);
729 dbms_sql.bind_variable(cid,':b_source_id', p_source_id);
730 dbms_sql.bind_variable(cid,':b_source_table', p_source_table);
731 END IF;
732
733
734 rows_processed := dbms_sql.execute(cid);
735
736 dbms_sql.close_cursor(cid);
737 IF rows_processed > 0 THEN
738 g_headers_selected := rows_processed;
739 -- Populate records transferred only when account validation
740 -- is not done
741 IF Nvl(p_validate_account,'N') <> 'Y' THEN
742 g_control_info(g_periods_cnt).rec_transferred :=
743 Nvl(g_control_info(g_periods_cnt).rec_transferred,0) + g_headers_selected;
744 END IF;
745
746 g_proceed := 'Y';
747 xla_message('XLA_GLT_SELECTED_HEADERS','COUNT',rows_processed,'','','','',
748 l_log_module,
749 C_LEVEL_STATEMENT);
750 ELSE
751 g_proceed := 'N';
752 xla_message('XLA_GLT_NO_ENTRIES_TO_PROCESS','','','','','','',
753 l_log_module,
754 C_LEVEL_STATEMENT);
755 END IF;
756
757 -- Currently for Payables only. Needs to be modifed for CST
758 IF Nvl(p_selection_type,1) = 1 THEN -- for batch transfer
759 IF p_legal_entity_id IS NULL THEN
760 -- Bug2708663. Removed the extra Exists Condition.
761 statement := ' SELECT COUNT(aeh.gl_transfer_run_id)
762 FROM ' || g_headers_table || ' aeh
763 WHERE gl_transfer_run_id = -1 ' || l_where_error;
764 -- Bug2789042. Added the l_where_error to report errors.
765 IF p_ae_category.COUNT = 1 THEN
766 EXECUTE IMMEDIATE statement
767 INTO l_acct_errors
768 USING p_start_date, p_end_date, p_ae_category(1),
769 p_ae_category(1),p_set_of_books_id;
770 ELSE
771 EXECUTE IMMEDIATE statement
772 INTO l_acct_errors
773 USING p_start_date, p_end_date, p_set_of_books_id;
774
775 END IF;
776 g_control_info(g_periods_cnt).cnt_acct_errors :=
777 g_control_info(g_periods_cnt).cnt_acct_errors + l_acct_errors;
778 END IF;
779 END IF;
780
781 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
782 trace
783 (p_msg => 'BEGIN of procedure SELECT_ACCT_HEADERS'
784 ,p_level => C_LEVEL_PROCEDURE
785 ,p_module => l_log_module);
786 END IF;
787
788 END select_acct_headers;
789
790 PROCEDURE validate_acct_lines( p_selection_type NUMBER,
791 p_set_of_books_id NUMBER,
792 p_coa_id NUMBER,
793 p_transfer_run_id NUMBER,
794 p_start_date DATE,
795 p_end_date DATE ) IS
796 statement VARCHAR2(4000) ;
797 cid NUMBER;
798 rows_processed NUMBER;
799 l_log_module VARCHAR2(255);
800
801 BEGIN
802
803 IF g_log_enabled THEN
804 l_log_module := C_DEFAULT_MODULE||'.validate_acct_lines';
805 END IF;
806
807 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
808 trace
809 (p_msg => 'BEGIN of procedure validate_acct_lines'
810 ,p_level => C_LEVEL_PROCEDURE
811 ,p_module => l_log_module);
812 END IF;
813
814 ----------------------------------------------------------
815 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
816 xla_message('XLA_GLT_VALIDATE_LINES','','','','','','',
817 l_log_module,
818 C_LEVEL_STATEMENT);
819 END IF;
820 ----------------------------------------------------------
821
822 -- Bug2842884. Budget journals need to be validated for detail_budgeting_allowed_flag = 'Y'.
823
824 IF g_entry_type = 'B' THEN
825 statement :=
826 'UPDATE ' || g_lines_table || ' ael
827 SET ael.gl_transfer_error_code =
828 ( SELECT Decode(gcc.detail_budgeting_allowed_flag, ''N'', ''POST'',
829 Decode(gcc.summary_flag, ''Y'', ''POST'',
830 Decode(template_id, NULL,
831 Decode(enabled_flag, ''N'', ''DISABLED'',
832 Decode(nvl(gcc.code_combination_id,-1) , -1, ''INVALID'',
833 Decode(Sign(gcc.start_date_active - aeh.accounting_date), 1, ''INACTIVE'',
834 Decode(Sign(aeh.accounting_date - gcc.end_date_active), 1, ''INACTIVE'',
835 NULL)))),
836 ''POST''))) FROM ' || g_headers_table || ' aeh, gl_code_combinations gcc
837 WHERE aeh.ae_header_id = ael.ae_header_id
838 AND gcc.code_combination_id = ael.code_combination_id
839 AND gcc.chart_of_accounts_id = :b_coa_id )
840 WHERE ael.ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table || ' aeh
841 WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
842 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date) ';
843 ELSE
844 statement :=
845 'UPDATE ' || g_lines_table || ' ael
846 SET ael.gl_transfer_error_code =
847 ( SELECT Decode(gcc.detail_posting_allowed_flag, ''N'', ''POST'',
848 Decode(gcc.summary_flag, ''Y'', ''POST'',
849 Decode(template_id, NULL,
850 Decode(enabled_flag, ''N'', ''DISABLED'',
851 Decode(nvl(gcc.code_combination_id,-1) , -1, ''INVALID'',
852 Decode(Sign(gcc.start_date_active - aeh.accounting_date), 1, ''INACTIVE'',
853 Decode(Sign(aeh.accounting_date - gcc.end_date_active), 1, ''INACTIVE'',
854 NULL)))),
855 ''POST''))) FROM ' || g_headers_table || ' aeh, gl_code_combinations gcc
856 WHERE aeh.ae_header_id = ael.ae_header_id
857 AND gcc.code_combination_id = ael.code_combination_id
858 AND gcc.chart_of_accounts_id = :b_coa_id )
859 WHERE ael.ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table || ' aeh
860 WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
861 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date) ';
862 END IF;
863
864 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
865 trace
866 (p_msg => 'statement = ' || statement
867 ,p_level => C_LEVEL_STATEMENT
868 ,p_module => l_log_module);
869 END IF;
870
871 cid := dbms_sql.open_cursor;
872 dbms_sql.parse(cid, statement, dbms_sql.native);
873
874 -- Bind Variables
875 dbms_sql.bind_variable(cid,':b_coa_id', p_coa_id);
876 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
877 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
878 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
879
880 rows_processed := dbms_sql.execute(cid);
881 dbms_sql.close_cursor(cid);
882
883 IF rows_processed = 0 THEN
884 g_proceed := 'N';
885 END IF;
886
887 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
888 xla_message('XLA_GLT_LINES_UPDATED','COUNT',rows_processed,'','','','',
889 l_log_module,
890 C_LEVEL_STATEMENT);
891 END IF;
892
893 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
894 trace
895 (p_msg => 'END of procedure validate_acct_lines'
896 ,p_level => C_LEVEL_PROCEDURE
897 ,p_module => l_log_module);
898 END IF;
899
900
901 END validate_acct_lines;
902
903 PROCEDURE validate_acct_headers ( p_selection_type NUMBER,
904 p_set_of_books_id NUMBER,
905 p_transfer_run_id NUMBER,
906 p_start_date DATE,
907 p_end_date DATE ) IS
908 cid NUMBER;
909 statement VARCHAR2(4000);
910 l_invalid_headers NUMBER;
911 l_log_module VARCHAR2(255);
912
913 BEGIN
914
915 -- Reset the batch_run_id to -1 and set gl_tranfer_flag to 'E' to
916 -- deselect the headers with erroneous accounting entry lines.
917
918 IF g_log_enabled THEN
919 l_log_module := C_DEFAULT_MODULE||'.validate_acct_lines';
920 END IF;
921
922 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
923 trace
924 (p_msg => 'BEGIN of procedure validate_acct_headers'
925 ,p_level => C_LEVEL_PROCEDURE
926 ,p_module => l_log_module);
927 END IF;
928
929 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
930 xla_message('XLA_GLT_VALIDATE_HEADERS','','','','','','',
931 l_log_module,
932 C_LEVEL_STATEMENT);
933 END IF;
934
935 statement := ' UPDATE ' || g_headers_table || ' aeh
936 SET aeh.gl_transfer_run_id = -1,
937 aeh.gl_transfer_flag = ''E''
938 WHERE aeh.gl_transfer_run_id = :b_transfer_run_id
939 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
940 AND EXISTS ( SELECT ''x'' FROM ' || g_lines_table || ' ael
941 WHERE ael.ae_header_id = aeh.ae_header_id
942 AND ael.gl_transfer_error_code IS NOT NULL )';
943
944 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
945 trace
946 (p_msg => 'statement = ' || statement
947 ,p_level => C_LEVEL_STATEMENT
948 ,p_module => l_log_module);
949 END IF;
950
951 cid := dbms_sql.open_cursor;
952 dbms_sql.parse(cid, statement, dbms_sql.native);
953 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
954 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
955 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
956
957 l_invalid_headers := dbms_sql.execute(cid);
958
959 dbms_sql.close_cursor(cid);
960
961 g_control_info(g_periods_cnt).cnt_transfer_errors :=
962 g_control_info(g_periods_cnt).cnt_transfer_errors + l_invalid_headers;
963
964 -- subtract invalid headers from selected headers. If the number is > 0
965 -- then proceed otherwise stop the transfer.
966 g_headers_selected := g_headers_selected - l_invalid_headers;
967
968 IF (g_headers_selected > 0) THEN
969 g_proceed := 'Y';
970 g_control_info(g_periods_cnt).rec_transferred :=
971 Nvl(g_control_info(g_periods_cnt).rec_transferred,0)
972 + (g_headers_selected);
973 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
974 xla_message('XLA_GLT_HEADERS_TRANSFERRED','COUNT',g_headers_selected ,'','','','',
975 l_log_module,
976 C_LEVEL_STATEMENT);
977 END IF;
978 ELSE
979 g_proceed := 'N';
980 END IF;
981
982 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
983 trace
984 (p_msg => 'END of procedure validate_acct_headers'
985 ,p_level => C_LEVEL_PROCEDURE
986 ,p_module => l_log_module);
987 END IF;
988
989
990 END validate_acct_headers;
991
992 PROCEDURE transfer_enc_lines( p_application_id NUMBER,
993 p_set_of_books_id NUMBER,
994 p_transfer_run_id NUMBER,
995 p_start_date DATE,
996 p_end_date DATE,
997 p_next_period VARCHAR2,
998 p_reversal_date VARCHAR2,
999 p_average_balances_flag VARCHAR2,
1000 p_source_name VARCHAR2,
1001 p_group_id NUMBER,
1002 p_request_id NUMBER,
1003 p_batch_desc VARCHAR2,
1004 p_je_desc VARCHAR2,
1005 p_je_line_desc VARCHAR2 ) IS
1006 statement VARCHAR2(4000) ;
1007 cid NUMBER;
1008 rows_processed NUMBER;
1009 l_log_module VARCHAR2(255);
1010
1011 BEGIN
1012
1013 IF g_log_enabled THEN
1014 l_log_module := C_DEFAULT_MODULE||'.transfer_enc_lines';
1015 END IF;
1016
1017 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1018 trace
1019 (p_msg => 'BEGIN of procedure transfer_enc_lines'
1020 ,p_level => C_LEVEL_PROCEDURE
1021 ,p_module => l_log_module);
1022 END IF;
1023
1024 IF g_proceed = 'N' THEN
1025 RETURN;
1026 END IF;
1027
1028 -- Encumbrances are always transferred in Detail.
1029 -- Populate Link Id for only valid accounting entry headers.
1030 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1031 xla_message('XLA_GLT_UPDATE_ENC_LINKID','','','','','','',
1032 l_log_module,
1033 C_LEVEL_STATEMENT);
1034 END IF;
1035
1036 statement := 'UPDATE ' || g_encumbrance_table ||
1037 ' SET program_update_date = Sysdate,
1038 program_id = :b_program_id,
1039 request_id = :b_request_id,
1040 gl_sl_link_id = ' || g_enc_sequence_name || '.NEXTVAL
1041 WHERE ae_header_id IN ( SELECT ae_header_id FROM ' || g_headers_table
1042 || ' WHERE gl_transfer_run_id = :b_transfer_run_id
1043 AND accounting_date BETWEEN :b_start_date AND :b_end_date )';
1044
1045 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1046 trace
1047 (p_msg => 'statement = ' || statement
1048 ,p_level => C_LEVEL_STATEMENT
1049 ,p_module => l_log_module);
1050 END IF;
1051
1052
1053 cid := dbms_sql.open_cursor;
1054 dbms_sql.parse(cid, statement, dbms_sql.native);
1055 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1056 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
1057 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1058 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1059 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1060
1061 rows_processed := dbms_sql.execute(cid);
1062 dbms_sql.close_cursor(cid);
1063
1064 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1065 xla_message('XLA_GLT_UPDATE_ENC_LINES','COUNT',rows_processed,'','','','',
1066 l_log_module,
1067 C_LEVEL_STATEMENT);
1068 END IF;
1069
1070 -- Transfer Encumbrance entries to gl_interface table.
1071 IF rows_processed > 0 THEN
1072 g_rec_transfer_flag := 'Y'; --set the globle flag to 'Y' whenever there are records transferred.
1073 g_enc_proceed := 'Y'; --set the funds check flag if there are encumbrance entries.
1074
1075 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1076 xla_message('XLA_GLT_INSERTING_ENC_LINES','','','','','','',
1077 l_log_module,
1078 C_LEVEL_STATEMENT);
1079 END IF;
1080
1081 statement := 'INSERT INTO gl_interface(
1082 status, set_of_books_id,
1083 user_je_source_name, user_je_category_name,
1084 accounting_date, currency_code,
1085 date_created, created_by,
1086 actual_flag, encumbrance_type_id,
1087 code_combination_id, stat_amount,
1088 entered_dr, entered_cr,
1089 accounted_dr, accounted_cr,
1090 reference1, reference2,
1091 reference7, reference8,
1092 reference5, reference10,
1093 reference21, reference22,
1094 reference23, reference24,
1095 reference25, reference26,
1096 reference27, reference28,
1097 reference29, reference30,
1098 subledger_doc_sequence_id,
1099 subledger_doc_sequence_value,
1100 gl_sl_link_table, gl_sl_link_id,
1101 je_header_id, group_id
1102 )
1103 SELECT
1104 ''NEW'', aeh.set_of_books_id,
1105 :b_source_name, jc.user_je_category_name,
1106 aeh.accounting_date, :b_base_currency_code,
1107 Sysdate, :b_user_id,
1108 ''E'', ael.encumbrance_type_id,
1109 ael.code_combination_id, stat_amount,
1110 accounted_dr, accounted_cr,
1111 accounted_dr, accounted_cr,
1112 :b_batch_name, :b_batch_desc,
1113 aeh.gl_reversal_flag,
1114 Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
1115 Decode(Nvl(:b_average_balances_flag,''N''),
1116 ''Y'',to_char(:b_reversal_date),:b_next_period),NULL),
1117 :b_je_desc, :b_je_line_desc,
1118 ael.reference1, ael.reference2,
1119 ael.reference3, ael.reference4,
1120 ael.reference5, ael.reference6,
1121 ael.reference7, ael.reference8,
1122 ael.reference9, ael.reference10,
1123 ael.subledger_doc_sequence_id,
1124 ael.subledger_doc_sequence_value,
1125 :b_link_table, ael.gl_sl_link_id,
1126 -1, :b_group_id
1127 FROM '|| g_headers_table ||' aeh, '|| g_encumbrance_table ||
1128 ' ael, gl_je_categories jc
1129 WHERE ael.ae_header_id = aeh.ae_header_id
1130 AND aeh.set_of_books_id = :b_set_of_books_id
1131 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1132 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1133 AND jc.je_category_name = aeh.ae_category';
1134
1135 cid := dbms_sql.open_cursor;
1136 dbms_sql.parse(cid, statement, dbms_sql.native);
1137
1138 -- Bind variables
1139 dbms_sql.bind_variable(cid,':b_set_of_books_id', p_set_of_books_id);
1140 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1141 dbms_sql.bind_variable(cid,':b_source_name', p_source_name);
1142 dbms_sql.bind_variable(cid,':b_batch_name', g_batch_name);
1143 dbms_sql.bind_variable(cid,':b_group_id', p_group_id);
1144 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1145 dbms_sql.bind_variable(cid,':b_base_currency_code', g_base_currency_code);
1146 dbms_sql.bind_variable(cid,':b_link_table', g_enc_table_alias);
1147 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1148 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1149 dbms_sql.bind_variable(cid,':b_batch_desc', p_batch_desc);
1150 dbms_sql.bind_variable(cid,':b_je_desc', p_je_desc);
1151 dbms_sql.bind_variable(cid,':b_je_line_desc', p_je_line_desc);
1152 dbms_sql.bind_variable(cid,':b_next_period', p_next_period);
1153
1154 dbms_sql.bind_variable(cid,':b_reversal_date', p_reversal_date);
1155 dbms_sql.bind_variable(cid,':b_average_balances_flag', p_average_balances_flag);
1156
1157 rows_processed := dbms_sql.execute(cid);
1158 dbms_sql.close_cursor(cid);
1159 g_sob_rows_created := g_sob_rows_created + rows_processed;
1160
1161 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1162 xla_message('XLA_GLT_INSERTED_ENC_LINES','COUNT',rows_processed,'','','','',
1163 l_log_module,
1164 C_LEVEL_STATEMENT);
1165 END IF;
1166
1167 ELSE
1168 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1169 xla_message('XLA_GLT_NO_ENC_LINES','','','','','','',
1170 l_log_module,
1171 C_LEVEL_STATEMENT);
1172 END IF;
1173
1174 END IF;
1175
1176 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1177 trace
1178 (p_msg => 'END of procedure transfer_enc_lines'
1179 ,p_level => C_LEVEL_PROCEDURE
1180 ,p_module => l_log_module);
1181 END IF;
1182
1183 END transfer_enc_lines;
1184
1185 -- This procedure transfers all the journal lines in summarized mode.
1186 -- Summarization can be by accounting date/period depending on what
1187 -- the user has choosen.
1188
1189 -- In case entries exist in XLA_JE_LINE_TYPES, all those records
1190 -- with summary_flag = 'D' will be omitted.
1191
1192 -- All the line_type_code with 'D' are stored in g_line_type variable.
1193
1194 PROCEDURE gl_insert_summary( p_request_id NUMBER,
1195 p_source_name VARCHAR2,
1196 p_transfer_run_id NUMBER,
1197 p_period_name VARCHAR2,
1198 p_start_date DATE,
1199 p_end_date DATE,
1200 p_next_period VARCHAR2,
1201 p_reversal_date DATE,
1202 p_average_balances_flag VARCHAR2,
1203 p_gl_transfer_mode VARCHAR2,
1204 p_group_id NUMBER,
1205 p_batch_desc VARCHAR2,
1206 p_je_desc VARCHAR2,
1207 p_je_line_desc VARCHAR2) IS
1208
1209 statement_summary VARCHAR2(10000) ;
1210
1211 cid BINARY_INTEGER;
1212 rows_processed NUMBER;
1213
1214 l_from VARCHAR2(1000);
1215 l_where VARCHAR2(1000);
1216 l_reference3 VARCHAR2(400);
1217
1218 l_select_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1219 l_insert_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1220 l_group_by_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1221
1222 l_log_module VARCHAR2(255);
1223
1224 BEGIN
1225
1226 IF g_log_enabled THEN
1227 l_log_module := C_DEFAULT_MODULE||'.gl_insert_summary';
1228 END IF;
1229
1230 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1231 trace
1232 (p_msg => 'BEGIN of procedure GL_INSERT_SUMMARY'
1233 ,p_level => C_LEVEL_PROCEDURE
1234 ,p_module => l_log_module);
1235
1236 END IF;
1237
1238
1239 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1240 trace
1241 (p_msg => 'l_from = ' || l_from
1242 ,p_level => C_LEVEL_STATEMENT
1243 ,p_module => l_log_module);
1244
1245 trace
1246 (p_msg => 'l_where = ' || l_where
1247 ,p_level => C_LEVEL_STATEMENT
1248 ,p_module => l_log_module);
1249
1250
1251 trace
1252 (p_msg => 'l_reference3 = ' || l_reference3
1253 ,p_level => C_LEVEL_STATEMENT
1254 ,p_module => l_log_module);
1255 END IF;
1256
1257
1258 /*----------------------------------------------------------------
1259 1. 'A' is for Actual -- only for AP and CST -- old source code
1260 2. 'B' is for Budget and Actual -- new requirement for PSB
1261 -----------------------------------------------------------------*/
1262 IF g_entry_type = 'A' THEN
1263 l_select_actual_flag := '''A'',';
1264 l_insert_actual_flag := '';
1265 l_group_by_actual_flag := '';
1266 ELSE --g_entry_type = 'B'
1267 l_select_actual_flag := 'NVL(ael.actual_flag,''A''), aeh.budget_version_id,';
1268 l_insert_actual_flag := 'budget_version_id,';
1269 l_group_by_actual_flag := ',NVL(ael.actual_flag,''A''), aeh.budget_version_id';
1270 END IF;
1271
1272 l_from := ' FROM '|| g_headers_table ||' aeh, '
1273 || g_lines_table ||' ael, '
1274 || ' gl_je_categories jc ';
1275
1276
1277 l_where := ' WHERE ael.ae_header_id = aeh.ae_header_id
1278 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1279 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1280 AND jc.je_category_name = Decode(Nvl(aeh.cross_currency_flag,''N''),
1281 ''Y'',''Cross Currency'', aeh.ae_category) ';
1282
1283 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1284 xla_message('XLA_GLT_TRANSFER_MODE_A','','','','','','',
1285 l_log_module,
1286 C_LEVEL_STATEMENT);
1287 END IF;
1288
1289 IF g_line_type IS NOT NULL THEN
1290 l_where := l_where || 'AND ael.ae_line_type_code NOT IN (' || g_line_type || ')';
1291 END IF;
1292
1293 IF p_gl_transfer_mode = 'A' THEN
1294
1295 statement_summary := 'INSERT INTO gl_interface(
1296 status,
1297 set_of_books_id,
1298 user_je_source_name,
1299 user_je_category_name,
1300 accounting_date,
1301 currency_code,
1302 date_created,
1303 created_by,
1304 actual_flag,
1305 '|| l_insert_actual_flag ||'
1306 code_combination_id,
1307 stat_amount,
1308 entered_dr,
1309 entered_cr,
1310 accounted_dr,
1311 accounted_cr,
1312 reference1,
1313 reference2,
1314 reference5,
1315 reference10,
1316 reference7,
1317 reference8,
1318 reference21,
1319 gl_sl_link_id,
1320 gl_sl_link_table,
1321 request_id,
1322 ussgl_transaction_code,
1323 je_header_id,
1324 group_id
1325 )
1326 SELECT /*+ ORDERED */
1327 jc.je_category_name,
1328 aeh.set_of_books_id,
1329 :b_source_name,
1330 jc.user_je_category_name,
1331 trunc(aeh.accounting_date) ,
1332 ael.currency_code,
1333 Sysdate,
1334 :b_user_id,
1335 '|| l_select_actual_flag ||'
1336 ael.code_combination_id,
1337 SUM(stat_amount),
1338 SUM(entered_dr),
1339 SUM(entered_cr),
1340 SUM(accounted_dr),
1341 SUM(accounted_cr),
1342 :b_batch_name,
1343 :b_batch_desc,
1344 :b_je_desc,
1345 :b_je_line_desc,
1346 aeh.gl_reversal_flag,
1347 Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
1348 Decode(Nvl(:b_average_balances_flag,''N''),
1349 ''Y'',to_char(:b_reversal_date),:b_next_period),NULL),
1350 To_char(:b_transfer_run_id),
1351 xla_gl_transfer_pkg.get_linkid(:b_program_name),
1352 :b_link_table,
1353 :b_request_id,
1354 ael.ussgl_transaction_code,
1355 :b_transfer_run_id,
1356 :b_group_id '
1357 || l_from ||
1358 l_where ||
1359 ' GROUP BY aeh.set_of_books_id, aeh.ae_category,jc.je_category_name,
1360 jc.user_je_category_name, trunc(aeh.accounting_date),
1361 aeh.gl_reversal_flag, ael.currency_code,
1362 ael.code_combination_id,ael.ussgl_transaction_code,
1363 Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'',
1364 0,Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
1365 '|| l_group_by_actual_flag ;
1366
1367 ELSE -- Summarized by Period
1368 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1369 xla_message('XLA_GLT_TRANSFER_MODE_P','','','','','','',
1370 l_log_module,
1371 C_LEVEL_STATEMENT);
1372 END IF;
1373
1374 statement_summary := 'INSERT INTO gl_interface(
1375 status,
1376 set_of_books_id,
1377 user_je_source_name,
1378 user_je_category_name,
1379 accounting_date,
1380 currency_code,
1381 date_created,
1382 created_by,
1383 actual_flag,
1384 '|| l_insert_actual_flag ||'
1385 encumbrance_type_id,
1386 code_combination_id,
1387 stat_amount,
1388 entered_dr,
1389 entered_cr,
1390 accounted_dr,
1391 accounted_cr,
1392 reference1,
1393 reference2,
1394 reference5,
1395 reference10,
1396 reference7,
1397 reference8,
1398 reference21,
1399 gl_sl_link_id,
1400 gl_sl_link_table,
1401 request_id,
1402 ussgl_transaction_code,
1403 je_header_id,
1404 group_id,
1405 period_name
1406 )
1407 SELECT /*+ ORDERED */
1408 jc.je_category_name,
1409 aeh.set_of_books_id,
1410 :b_source_name,
1411 jc.user_je_category_name,
1412 :b_end_date_truncated,
1413 ael.currency_code,
1414 Sysdate,
1415 :b_user_id,
1416 '|| l_select_actual_flag ||'
1417 NULL,
1418 ael.code_combination_id,
1419 SUM(stat_amount),
1420 SUM(entered_dr),
1421 SUM(entered_cr),
1422 SUM(accounted_dr),
1423 SUM(accounted_cr),
1424 :b_batch_name,
1425 :b_batch_desc,
1426 :b_je_desc,
1427 :b_je_line_desc,
1428 aeh.gl_reversal_flag,
1429 Decode(Nvl(aeh.gl_reversal_flag,''N''), ''Y'',
1430 Decode(Nvl(:b_average_balances_flag,''N''),
1431 ''Y'',To_char(:b_reversal_date),:b_next_period),NULL),
1432 To_char(:b_transfer_run_id),
1433 xla_gl_transfer_pkg.get_linkid(:b_program_name), :b_link_table,
1434 :b_request_id,
1435 ael.ussgl_transaction_code,
1436 :b_transfer_run_id,
1437 :b_group_id,
1438 :b_period_name '
1439 || l_from ||
1440 l_where ||
1441 ' GROUP BY aeh.set_of_books_id, aeh.ae_category, jc.je_category_name,jc.user_je_category_name,
1442 aeh.period_name, aeh.gl_reversal_flag, ael.currency_code,
1443 ael.code_combination_id, ael.ussgl_transaction_code,
1444 Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'',
1445 0,Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
1446 '|| l_group_by_actual_flag;
1447 END IF;
1448
1449 cid := dbms_sql.open_cursor;
1450 dbms_sql.parse(cid, statement_summary, dbms_sql.native);
1451
1452 --Bind Variables
1453 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1454 dbms_sql.bind_variable(cid,':b_group_id', p_group_id);
1455 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1456 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1457 dbms_sql.bind_variable(cid,':b_source_name', p_source_name);
1458 dbms_sql.bind_variable(cid,':b_batch_name', g_batch_name);
1459 dbms_sql.bind_variable(cid,':b_link_table', g_actual_table_alias);
1460 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1461 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1462 IF p_gl_transfer_mode <> 'A' THEN
1463 dbms_sql.bind_variable(cid,':b_end_date_truncated', trunc(p_end_date));
1464 END IF;
1465 dbms_sql.bind_variable(cid,':b_batch_desc', p_batch_desc);
1466 dbms_sql.bind_variable(cid,':b_je_desc', p_je_desc);
1467 dbms_sql.bind_variable(cid,':b_je_line_desc', p_je_line_desc);
1468 dbms_sql.bind_variable(cid,':b_next_period', p_next_period);
1469 dbms_sql.bind_variable(cid,':b_reversal_date', p_reversal_date);
1470 dbms_sql.bind_variable(cid,':b_average_balances_flag', p_average_balances_flag);
1471 dbms_sql.bind_variable(cid,':b_program_name', g_program_name);
1472
1473 IF p_gl_transfer_mode = 'P' THEN
1474 dbms_sql.bind_variable(cid,':b_period_name', p_period_name);
1475 END IF;
1476
1477 rows_processed := dbms_sql.execute(cid);
1478
1479 IF rows_processed = 0 THEN
1480 g_proceed := 'N';
1481 ELSE
1482 g_sob_rows_created := g_sob_rows_created + rows_processed;
1483 g_rec_transfer_flag := 'Y'; --set the globle flag to 'Y' whenever there are records transferred.
1484 END IF;
1485 dbms_sql.close_cursor(cid);
1486
1487 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1488 xla_message('XLA_GLT_GL_INSERT','COUNT','(summary) ' || rows_processed,'','','','',
1489 l_log_module,
1490 C_LEVEL_STATEMENT);
1491 END IF;
1492
1493 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1494 trace
1495 (p_msg => 'END of procedure GL_INSERT_SUMMARY'
1496 ,p_level => C_LEVEL_PROCEDURE
1497 ,p_module => l_log_module);
1498 END IF;
1499
1500 END gl_insert_summary ;
1501
1502 -- This procedure transfers all the journal lines in detail mode.
1503
1504 -- Data in XLA_JE_LINE_TYPES will be ignored.
1505
1506 PROCEDURE gl_insert_detail( p_request_id NUMBER,
1507 p_source_name VARCHAR2,
1508 p_transfer_run_id NUMBER,
1509 p_period_name VARCHAR2,
1510 p_start_date DATE,
1511 p_end_date DATE,
1512 p_next_period VARCHAR2,
1513 p_reversal_date DATE,
1514 p_average_balances_flag VARCHAR2,
1515 p_gl_transfer_mode VARCHAR2,
1516 p_group_id NUMBER,
1517 p_batch_desc VARCHAR2,
1518 p_je_desc VARCHAR2,
1519 p_je_line_desc VARCHAR2) IS
1520
1521 statement_detail VARCHAR2(10000) ;
1522
1523 cid BINARY_INTEGER;
1524 rows_processed NUMBER;
1525
1526 l_from VARCHAR2(1000);
1527 l_where VARCHAR2(1000);
1528 l_reference3 VARCHAR2(400);
1529
1530 l_select_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1531 l_insert_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1532 l_group_by_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1533
1534 l_log_module VARCHAR2(255);
1535
1536 BEGIN
1537
1538 IF g_log_enabled THEN
1539 l_log_module := C_DEFAULT_MODULE||'.gl_insert_detail';
1540 END IF;
1541
1542 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1543 trace
1544 (p_msg => 'BEGIN of procedure GL_INSERT_DETAIL'
1545 ,p_level => C_LEVEL_PROCEDURE
1546 ,p_module => l_log_module);
1547
1548 END IF;
1549
1550
1551 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1552 trace
1553 (p_msg => 'l_from = ' || l_from
1554 ,p_level => C_LEVEL_STATEMENT
1555 ,p_module => l_log_module);
1556
1557 trace
1558 (p_msg => 'l_where = ' || l_where
1559 ,p_level => C_LEVEL_STATEMENT
1560 ,p_module => l_log_module);
1561
1562
1563 trace
1564 (p_msg => 'l_reference3 = ' || l_reference3
1565 ,p_level => C_LEVEL_STATEMENT
1566 ,p_module => l_log_module);
1567 END IF;
1568
1569
1570 /*----------------------------------------------------------------
1571 1. 'A' is for Actual -- only for AP and CST -- old source code
1572 2. 'B' is for Budget and Actual -- new requirement for PSB
1573 -----------------------------------------------------------------*/
1574 IF g_entry_type = 'A' THEN
1575 l_select_actual_flag := '''A'',';
1576 l_insert_actual_flag := '';
1577 l_group_by_actual_flag := '';
1578 ELSE --g_entry_type = 'B'
1579 l_select_actual_flag := 'NVL(ael.actual_flag,''A''), aeh.budget_version_id,';
1580 l_insert_actual_flag := 'budget_version_id,';
1581 l_group_by_actual_flag := ',NVL(ael.actual_flag,''A''), aeh.budget_version_id';
1582 END IF;
1583
1584 l_from := ' FROM '|| g_headers_table ||' aeh, '
1585 || g_lines_table ||' ael, '
1586 || ' gl_je_categories jc ';
1587
1588
1589 l_where := ' WHERE ael.ae_header_id = aeh.ae_header_id
1590 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1591 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1592 AND jc.je_category_name = Decode(Nvl(aeh.cross_currency_flag,''N''),
1593 ''Y'',''Cross Currency'', aeh.ae_category) ';
1594
1595 IF g_line_type IS NOT NULL THEN
1596 l_where := l_where || 'AND ael.ae_line_type_code IN (' || g_line_type ||')';
1597 END IF;
1598
1599
1600 IF ( p_gl_transfer_mode = 'D' ) OR ( g_line_type IS NOT NULL ) THEN -- Detail Transfer
1601 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1602 xla_message('XLA_GLT_TRANSFER_MODE_D','','','','','','',
1603 l_log_module,
1604 C_LEVEL_STATEMENT);
1605 END IF;
1606
1607 statement_detail := 'INSERT INTO gl_interface(
1608 status, set_of_books_id,
1609 user_je_source_name, user_je_category_name,
1610 accounting_date, currency_code,
1611 date_created, created_by,
1612 actual_flag,
1613 '|| l_insert_actual_flag ||'
1614 code_combination_id, stat_amount,
1615 entered_dr, entered_cr,
1616 accounted_dr, accounted_cr,
1617 reference1, reference2,
1618 reference3, reference5,
1619 reference7, reference8,
1620 reference10,
1621 reference21, reference22,
1622 reference23, reference24,
1623 reference25, reference26,
1624 reference27, reference28,
1625 reference29, reference30,
1626 subledger_doc_sequence_id,
1627 subledger_doc_sequence_value,
1628 gl_sl_link_table,
1629 gl_sl_link_id, request_id,
1630 ussgl_transaction_code,
1631 je_header_id, group_id,
1632 period_name
1633 )
1634 SELECT /*+ ORDERED */
1635 ''NEW'', aeh.set_of_books_id,
1636 :b_source_name, jc.user_je_category_name,
1637 aeh.accounting_date, ael.currency_code,
1638 Sysdate, :b_user_id,
1639 '|| l_select_actual_flag ||'
1640 ael.code_combination_id, stat_amount,
1641 entered_dr, entered_cr,
1642 accounted_dr, accounted_cr,
1643 :b_batch_name , :b_batch_desc,
1644 NULL, :b_je_desc,
1645 aeh.gl_reversal_flag,
1646 Decode(Nvl(aeh.gl_reversal_flag,''N''),
1647 ''Y'',Decode(Nvl(:b_average_balances_flag,''N''),
1648 ''Y'',To_char(:b_reversal_date),:b_next_period),NULL),
1649 Nvl(ael.description, :b_je_line_desc),
1650 Nvl(ael.reference1,:b_transfer_run_id),
1651 ael.reference2,
1652 ael.reference3, ael.reference4,
1653 ael.reference5, ael.reference6,
1654 ael.reference7, ael.reference8,
1655 ael.reference9, ael.reference10,
1656 ael.subledger_doc_sequence_id,
1657 ael.subledger_doc_sequence_value,
1658 :b_link_table,
1659 ael.gl_sl_link_id, :b_request_id,
1660 ael.ussgl_transaction_code,
1661 :b_transfer_run_id, :b_group_id,
1662 aeh.period_name
1663 ' || l_from
1664 || l_where;
1665 END IF;
1666
1667
1668 cid := dbms_sql.open_cursor;
1669 dbms_sql.parse(cid, statement_detail, dbms_sql.native);
1670
1671 --Bind Variables
1672 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1673 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1674 dbms_sql.bind_variable(cid,':b_group_id', p_group_id);
1675 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1676 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1677 dbms_sql.bind_variable(cid,':b_source_name', p_source_name);
1678 dbms_sql.bind_variable(cid,':b_batch_name', g_batch_name);
1679 dbms_sql.bind_variable(cid,':b_link_table', g_actual_table_alias);
1680 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1681 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1682 dbms_sql.bind_variable(cid,':b_batch_desc', p_batch_desc);
1683 dbms_sql.bind_variable(cid,':b_je_desc', p_je_desc);
1684 dbms_sql.bind_variable(cid,':b_je_line_desc', p_je_line_desc);
1685 dbms_sql.bind_variable(cid,':b_next_period', p_next_period);
1686 dbms_sql.bind_variable(cid,':b_reversal_date', p_reversal_date);
1687 dbms_sql.bind_variable(cid,':b_average_balances_flag', p_average_balances_flag);
1688
1689 rows_processed := dbms_sql.execute(cid);
1690
1691 IF rows_processed = 0 THEN
1692 g_proceed := 'N';
1693 ELSE
1694 g_sob_rows_created := g_sob_rows_created + rows_processed;
1695 g_rec_transfer_flag := 'Y'; --set the globle flag to 'Y' whenever there are records transferred.
1696 END IF;
1697 dbms_sql.close_cursor(cid);
1698
1699 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1700 xla_message('XLA_GLT_GL_INSERT','COUNT','(Detail) ' || rows_processed,'','','','',
1701 l_log_module,
1702 C_LEVEL_STATEMENT);
1703 END IF;
1704
1705 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1706 trace
1707 (p_msg => 'END of procedure GL_INSERT_DETAIL'
1708 ,p_level => C_LEVEL_PROCEDURE
1709 ,p_module => l_log_module);
1710
1711 END IF;
1712
1713 END gl_insert_detail;
1714
1715 -- This procedure stamps the gl_sl_linkid for all those accounting
1716 -- entries transferred in Summary. This routine is called after
1717 -- lines have been transferred to GL_INTERFACE.
1718
1719 PROCEDURE update_linkid_summary( p_request_id NUMBER,
1720 p_gl_transfer_mode VARCHAR2,
1721 p_transfer_run_id NUMBER,
1722 p_start_date DATE,
1723 p_end_date DATE
1724 ) IS
1725 statement VARCHAR2(2000) ;
1726 cid NUMBER;
1727 rows_processed NUMBER;
1728 l_and VARCHAR2(1000);
1729 l_budget_version VARCHAR2(100);
1730 l_log_module VARCHAR2(255);
1731 l_line_type_cond VARCHAR2(1000);
1732
1733 BEGIN
1734
1735 l_line_type_cond := '';
1736
1737 IF g_log_enabled THEN
1738 l_log_module := C_DEFAULT_MODULE||'.update_linkid_summary';
1739 END IF;
1740
1741 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1742 trace
1743 (p_msg => 'BEGIN of procedure update_linkid_summary'
1744 ,p_level => C_LEVEL_PROCEDURE
1745 ,p_module => l_log_module);
1746 END IF;
1747
1748
1749 -- Use accounting date in join condition if records are summarized
1750 -- by accounting date or use period name if records summarized by period
1751
1752 IF p_gl_transfer_mode = 'A' THEN
1753 l_and := ' AND trunc(aeh.accounting_date) = gi.accounting_date '; --Bug 16567359
1754 ELSIF p_gl_transfer_mode = 'P' THEN
1755 l_and := ' AND aeh.period_name = gi.period_name ';
1756 END IF;
1757
1758 /*----------------------------------------------------------------
1759 1. 'A' is for actual -- only for AP and CST -- old source code
1760 2. 'B' is for budget and actual -- new requirement for PSB
1761 -----------------------------------------------------------------*/
1762 IF g_entry_type = 'A' THEN
1763 l_budget_version := '';
1764 ELSE --g_entry_type = 'B'
1765 l_budget_version := 'AND aeh.budget_version_id = gi.budget_version_id';
1766 END IF;
1767
1768 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1769 xla_message('XLA_GLT_UPDATE_SUM_LINKID','','','','','','',
1770 l_log_module,
1771 C_LEVEL_STATEMENT);
1772 END IF;
1773
1774 statement :=
1775 'UPDATE ' || g_lines_table || ' ael
1776 SET program_update_date = Sysdate,
1777 program_id = :b_program_id,
1778 request_id = :b_request_id,
1779 gl_sl_link_id =
1780 (
1781 SELECT gi.gl_sl_link_id
1782 FROM gl_interface gi, ' || g_headers_table || ' aeh
1783 WHERE gi.request_id = :b_request_id
1784 AND gi.je_header_id = :b_transfer_run_id
1785 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1786 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1787 AND Decode(Nvl(aeh.cross_currency_flag,''N''), ''Y'', ''Cross Currency'',
1788 aeh.ae_category) = gi.status
1789 AND Nvl(aeh.gl_reversal_flag,''N'') = nvl(gi.reference7,''N'')
1790 AND gi.gl_sl_link_table = :b_actual_table_alias '
1791 || l_and || '
1792 AND aeh.set_of_books_id = gi.set_of_books_id '
1793 || l_budget_version ||'
1794 AND ael.code_combination_id = gi.code_combination_id
1795 AND ael.currency_code = gi.currency_code
1796 AND aeh.ae_header_id = ael.ae_header_id
1797 AND Decode(Sign(gi.entered_dr), 1,''dr'', -1, ''dr'', 0,
1798 Decode(Sign(gi.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'') =
1799 Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'', 0,
1800 Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
1801 )
1802 WHERE ael.ae_header_id IN ( SELECT ae_header_id
1803 FROM ' || g_headers_table ||
1804 ' WHERE gl_transfer_run_id = :b_transfer_run_id
1805 AND accounting_date BETWEEN :b_start_date AND :b_end_date )';
1806
1807 IF g_line_type IS NOT NULL THEN
1808 statement := statement || ' AND ael.ae_line_type_code NOT IN ( ' || g_line_type || ' )';
1809 END IF;
1810
1811 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1812 trace
1813 (p_msg => 'statement = ' || statement
1814 ,p_level => C_LEVEL_STATEMENT
1815 ,p_module => l_log_module);
1816 END IF;
1817
1818
1819 cid := dbms_sql.open_cursor;
1820 dbms_sql.parse(cid, statement, dbms_sql.native);
1821
1822 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1823 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1824 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
1825 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1826 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1827 dbms_sql.bind_variable(cid,':b_actual_table_alias', g_actual_table_alias);
1828
1829 rows_processed := dbms_sql.execute(cid);
1830
1831 dbms_sql.close_cursor(cid);
1832
1833 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1834 trace
1835 (p_msg => 'END of procedure update_linkid_summary'
1836 ,p_level => C_LEVEL_PROCEDURE
1837 ,p_module => l_log_module);
1838 END IF;
1839
1840 END update_linkid_summary ;
1841
1842 -- This procedure stamps the gl_sl_linkid for all those accounting
1843 -- entries to be transferred in DETAIL. This routine is called before
1844 -- GL_INSERT_DETAIL is called.
1845
1846 PROCEDURE update_linkid_detail( p_transfer_run_id NUMBER,
1847 p_request_id NUMBER,
1848 p_start_date DATE,
1849 p_end_date DATE) IS
1850 statement VARCHAR2(2000) ;
1851 cid NUMBER;
1852 rows_processed NUMBER;
1853 l_log_module VARCHAR2(255);
1854
1855 BEGIN
1856
1857 IF g_log_enabled THEN
1858 l_log_module := C_DEFAULT_MODULE||'.update_linkid_detail';
1859 END IF;
1860
1861 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1862 trace
1863 (p_msg => 'BEGIN of procedure UPDATE_LINKID_DETAIL'
1864 ,p_level => C_LEVEL_PROCEDURE
1865 ,p_module => l_log_module);
1866 END IF;
1867
1868 IF g_proceed = 'N' THEN
1869 RETURN;
1870 END IF;
1871
1872 statement := 'UPDATE ' || g_lines_table || ' ael ' ||
1873 ' SET program_update_date = Sysdate,
1874 program_id = :b_program_id,
1875 request_id = :b_request_id,
1876 gl_sl_link_id = ' || g_lines_sequence_name || '.NEXTVAL
1877 WHERE ae_header_id in
1878 ( SELECT ae_header_id
1879 FROM ' || g_headers_table ||
1880 ' WHERE gl_transfer_run_id = :b_transfer_run_id
1881 AND accounting_date BETWEEN :b_start_date AND :b_end_date )' ;
1882
1883 IF g_line_type IS NOT NULL THEN
1884 statement := statement || ' AND ael.ae_line_type_code IN ( ' || g_line_type || ' )';
1885 END IF;
1886
1887 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1888 trace
1889 (p_msg => 'statement = ' || statement
1890 ,p_level => C_LEVEL_STATEMENT
1891 ,p_module => l_log_module);
1892 END IF;
1893
1894
1895 cid := dbms_sql.open_cursor;
1896 dbms_sql.parse(cid, statement, dbms_sql.native);
1897
1898 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1899 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1900 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
1901 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1902 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1903
1904 rows_processed := dbms_sql.execute(cid);
1905
1906 IF rows_processed = 0 THEN
1907 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1908 xla_message('XLA_GLT_NO_ACCT_LINES','','','','','','',
1909 l_log_module,
1910 C_LEVEL_STATEMENT);
1911 END IF;
1912
1913 g_proceed := 'N';
1914 END IF;
1915 dbms_sql.close_cursor(cid);
1916
1917 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1918 trace
1919 (p_msg => 'END of procedure UPDATE_LINKID_DETAIL'
1920 ,p_level => C_LEVEL_PROCEDURE
1921 ,p_module => l_log_module);
1922 END IF;
1923
1924 END update_linkid_detail;
1925
1926 -- Check input parameters
1927 PROCEDURE check_input_param(p_selection_type NUMBER,
1928 p_start_date DATE,
1929 p_end_date DATE,
1930 p_gl_transfer_mode VARCHAR2,
1931 p_source_doc_id NUMBER,
1932 p_source_document_table VARCHAR2) IS
1933 l_log_module VARCHAR2(255);
1934
1935 BEGIN
1936
1937 IF g_log_enabled THEN
1938 l_log_module := C_DEFAULT_MODULE||'.check_input_param';
1939 END IF;
1940
1941 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1942 trace
1943 (p_msg => 'BEGIN of procedure CHECK_INPUT_PARAM'
1944 ,p_level => C_LEVEL_PROCEDURE
1945 ,p_module => l_log_module);
1946 END IF;
1947
1948
1949 -- Check gl_transfer_mode
1950 IF (p_gl_transfer_mode IS NULL) OR (p_gl_transfer_mode NOT IN ('D','A','P')) THEN
1951 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1952 xla_message('XLA_GLT_INVALID_MODE', '','','','','','',
1953 l_log_module,
1954 C_LEVEL_EXCEPTION);
1955 END IF;
1956 APP_EXCEPTION.RAISE_EXCEPTION;
1957 END IF;
1958
1959 IF p_selection_type = 1 THEN
1960 -- Date validation
1961 IF p_start_date IS NOT NULL THEN
1962 IF p_start_date > p_end_date THEN
1963 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1964 xla_message('XLA_GLT_INVALID_DATE_RANGE','','','','','','',
1965 l_log_module,
1966 C_LEVEL_EXCEPTION);
1967 END IF;
1968 APP_EXCEPTION.RAISE_EXCEPTION;
1969 END IF;
1970 END IF;
1971 -- Check document parameter
1972 IF (p_source_doc_id IS NOT NULL) OR (p_source_document_table IS NOT NULL) THEN
1973 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1974 xla_message('','Source document Id and Source document table should be NULL for batch Transfer','','','','','',
1975 l_log_module,
1976 C_LEVEL_EXCEPTION);
1977 END IF;
1978 APP_EXCEPTION.RAISE_EXCEPTION;
1979 END IF;
1980 ELSIF p_selection_type = 2 THEN
1981 IF (p_source_doc_id IS NULL) OR (p_source_document_table IS NULL) THEN
1982 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1983 xla_message('','Source document Id and Source document table should be NULL for document Transfer','','','','','',
1984 l_log_module,
1985 C_LEVEL_EXCEPTION);
1986 END IF;
1987 APP_EXCEPTION.RAISE_EXCEPTION;
1988 END IF;
1989 ELSE
1990 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1991 xla_message('XLA_GLT_INVALID_SELECTION_TYPE','','','','','','',
1992 l_log_module,
1993 C_LEVEL_EXCEPTION);
1994 END IF;
1995 APP_EXCEPTION.RAISE_EXCEPTION;
1996 END IF;
1997
1998 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1999 trace
2000 (p_msg => 'END of procedure CHECK_INPUT_PARAM'
2001 ,p_level => C_LEVEL_PROCEDURE
2002 ,p_module => l_log_module);
2003 END IF;
2004
2005 END check_input_param;
2006
2007
2008 /***********************************************************************************
2009 * FUNCTION
2010 * get_funds_check_flag
2011 *
2012 * DESCRIPTION
2013 * get_funds_check_flag will return TRUE if
2014 * 1.encumbrance accounting is being used
2015 * 2.Bugetary control is enabled for this set_of_books_id
2016 * 3.USSGL profile option is Yes --not available currently
2017 * SCOPE - PRIVATE
2018 *
2019 * ARGUMENTS:
2020 * IN: p_encumbrance_flag -- flag to check if encumbrance accounting
2021 * is being used
2022 * p_user_source_name -- it is used to get budget accounting flag
2023 * p_group_id -- it is used to get budget accounting flag
2024 * p_set_of_books_id -- it is used to get budget accounting flag
2025 *
2026 **********************************************************************************/
2027
2028 FUNCTION get_funds_check_flag(p_encumbrance_flag VARCHAR2,
2029 p_user_source_name VARCHAR2,
2030 p_group_id NUMBER,
2031 p_set_of_books_id NUMBER) RETURN BOOLEAN IS
2032 l_log_module VARCHAR2(255);
2033 l_budget_entries NUMBER;
2034 l_budget_control_flag VARCHAR2(1);
2035 BEGIN
2036
2037 IF g_log_enabled THEN
2038 l_log_module := C_DEFAULT_MODULE||'.get_funds_check_flag';
2039 END IF;
2040
2041 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2042 trace
2043 (p_msg => 'BEGIN of function GET_FUNDS_CHECK_FLAG'
2044 ,p_level => C_LEVEL_PROCEDURE
2045 ,p_module => l_log_module);
2046 END IF;
2047
2048 -- check if there are budget entries processed
2049 SELECT COUNT(*)
2050 INTO l_budget_entries
2051 FROM dual
2052 WHERE EXISTS ( SELECT 'x'
2053 FROM gl_interface
2054 WHERE user_je_source_name = p_user_source_name
2055 AND group_id = p_group_id
2056 AND set_of_books_id = p_set_of_books_id );
2057
2058
2059 -- check if budget control is enabled
2060 SELECT enable_budgetary_control_flag
2061 INTO l_budget_control_flag
2062 FROM gl_sets_of_books
2063 WHERE set_of_books_id = p_set_of_books_id;
2064
2065
2066 IF ( Nvl(p_encumbrance_flag,'N') = 'Y' AND g_enc_proceed = 'Y' ) OR
2067 ( l_budget_control_flag = 'Y' AND l_budget_entries > 0 ) THEN
2068 RETURN (TRUE);
2069 ELSE
2070 RETURN (FALSE);
2071 END IF;
2072
2073 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2074 trace
2075 (p_msg => 'END of function GET_FUNDS_CHECK_FLAG'
2076 ,p_level => C_LEVEL_PROCEDURE
2077 ,p_module => l_log_module);
2078 END IF;
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
2083 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
2084 l_log_module,
2085 'Unexpected Error While Executing ' || l_log_module);
2086 END IF;
2087 END get_funds_check_flag;
2088
2089 -- This procedure is used to derive line_type_code that need to be transferred in DETAIL.
2090
2091 -- Also sets the flag whether a detail transfer is required.
2092
2093 PROCEDURE derive_line_types IS
2094 l_log_module VARCHAR2(255);
2095 BEGIN
2096
2097 IF g_log_enabled THEN
2098 l_log_module := C_DEFAULT_MODULE||'.derive_line_types';
2099 END IF;
2100
2101 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2102 trace
2103 (p_msg => 'BEGIN of procedure derive_line_types'
2104 ,p_level => C_LEVEL_PROCEDURE
2105 ,p_module => l_log_module);
2106 END IF;
2107
2108 FOR select_line_type_rec IN ( SELECT Line_Type_Code
2109 FROM xla_je_line_types
2110 WHERE application_id = g_application_id
2111 AND summary_flag = 'D' )
2112 LOOP
2113 IF g_line_type IS NULL THEN
2114 g_line_type := '''' || select_line_type_rec.Line_Type_Code || ''',';
2115 ELSE
2116 g_line_type := g_line_type || '''' || select_line_type_rec.Line_Type_Code || ''',';
2117 END IF;
2118 END LOOP;
2119
2120 g_line_type := RTRIM(g_line_type,',');
2121
2122 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2123 xla_message('','Line types to be transferred in detail: ' || g_line_type,'','','','','',l_log_module,C_LEVEL_STATEMENT );
2124 END IF;
2125
2126 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2127 trace
2128 (p_msg => 'END of procedure derive_line_types'
2129 ,p_level => C_LEVEL_PROCEDURE
2130 ,p_module => l_log_module);
2131 END IF;
2132
2133 EXCEPTION
2134 WHEN OTHERS THEN
2135 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
2136 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
2137 l_log_module,
2138 'Unexpected Error While Executing ' || l_log_module);
2139 END IF;
2140 END derive_line_types;
2141
2142
2143 /*===========================================================================+
2144 | PROCEDURE |
2145 | XLA_GL_TRANSFER |
2146 | |
2147 | DESCRIPTION |
2148 | Main procedure for the transfer. All the sub procedures are called from |
2149 | from this procedure. |
2150 | |
2151 | SCOPE - PUBLIC |
2152 | |
2153 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2154 | |
2155 | ARGUMENTS |
2156 | p_application_id Application ID of the calling application. |
2157 | p_program_name Unique program name for the calling application. |
2158 | p_selection_type Transfer Type 1-Batch , 2- Doc. Level Transfer |
2159 | p_fc_force_flag Force flag for the funds checker. |
2160 | |
2161 | NOTES |
2162 | |
2163 +===========================================================================*/
2164
2165 PROCEDURE xla_gl_transfer(p_application_id NUMBER,
2166 p_user_id NUMBER,
2167 p_org_id NUMBER,
2168 p_request_id NUMBER,
2169 p_program_name VARCHAR2,
2170 p_selection_type NUMBER DEFAULT 1,
2171 p_sob_list t_sob_list,
2172 p_batch_name VARCHAR2,
2173 p_source_doc_id NUMBER DEFAULT NULL,
2174 p_source_document_table VARCHAR2 DEFAULT NULL,
2175 p_start_date DATE,
2176 p_end_date DATE,
2177 p_journal_category t_ae_category,
2178 p_validate_account VARCHAR2,
2179 p_gl_transfer_mode VARCHAR2,
2180 p_submit_journal_import VARCHAR2,
2181 p_summary_journal_entry VARCHAR2,
2182 p_process_days NUMBER ,
2183 p_batch_desc VARCHAR2 DEFAULT NULL,
2184 p_je_desc VARCHAR2 DEFAULT NULL,
2185 p_je_line_desc VARCHAR2 DEFAULT NULL,
2186 p_fc_force_flag BOOLEAN DEFAULT TRUE,
2187 p_debug_flag VARCHAR2
2188 ) IS
2189 l_start_date DATE;
2190 l_end_date DATE;
2191 l_period_start_date DATE;
2192 l_period_end_date DATE;
2193 l_open_start_date DATE;
2194 l_open_end_date DATE;
2195 l_min_start_date DATE;
2196 l_max_end_date DATE;
2197 l_next_period gl_period_statuses.period_name%TYPE;
2198 l_reversal_date DATE; -- Bug #974204
2199 l_application_id NUMBER(15);
2200 l_period_status VARCHAR2(1);
2201 l_period_name gl_period_statuses.period_name%TYPE;
2202 l_transfer_run_id NUMBER;
2203 l_set_of_books_id NUMBER;
2204 l_batch_run_id NUMBER;
2205 l_gl_installed_flag VARCHAR2(10);
2206 l_group_id NUMBER;
2207 l_interface_run_id NUMBER;
2208 l_encumbrance_flag VARCHAR2(1);
2209 l_source_name gl_je_sources.je_source_name%TYPE;
2210 l_user_source_name gl_je_sources.user_je_source_name%TYPE;
2211 industry VARCHAR2(10);
2212 l_debug_info VARCHAR2(2000);
2213 l_submittedreqid NUMBER;
2214 l_packet_id NUMBER;
2215 l_request_id NUMBER;
2216 l_sob_name gl_sets_of_books.name%TYPE;
2217 l_sob_type gl_sets_of_books.mrc_sob_type_code%TYPE;
2218 l_coa_id NUMBER;
2219 l_acct_validation_flag VARCHAR2(1);
2220 l_pre_commit_api xla_gl_transfer_programs.pre_commit_api_name%TYPE;
2221 l_budget_entries NUMBER;
2222 l_fc_force_flag VARCHAR2(10);
2223
2224 l_log_module VARCHAR2(255);
2225
2226
2227 -- Get Period Information
2228 -- Added the entry type to check if the entry is an actual/budget entry
2229 CURSOR c_getPeriods(c_sob_id NUMBER,
2230 c_start_date DATE,
2231 c_end_date DATE) IS
2232 SELECT gp1.period_name, gp1.start_date, gp1.end_date,
2233 gp2.period_name, gp2.start_date
2234 FROM gl_period_statuses gp1,
2235 gl_period_statuses gp2
2236 WHERE gp1.application_id = 101
2237 AND gp1.set_of_books_id = c_sob_id
2238 AND gp1.end_date >= Nvl(c_start_date,gp1.end_date-1)
2239 AND gp1.start_date <= c_end_date
2240 AND gp1.closing_status = DECODE( g_entry_type,'A', DECODE( gp1.closing_status, 'O', 'O', 'F', 'F','Z'),
2241 'B', gp1.closing_status )
2242 AND nvl(gp1.adjustment_period_flag,'N') = 'N'
2243 AND gp2.application_id(+) = 101
2244 AND gp2.set_of_books_id(+) = c_sob_id
2245 AND gp2.start_date(+) = gp1.end_date+1
2246 AND nvl(gp2.adjustment_period_flag,'N') = 'N'
2247 ORDER BY gp1.start_date;
2248 BEGIN
2249
2250 g_proceed := 'Y';
2251 g_rec_transfer_flag := 'N';
2252 g_enc_proceed := 'N';
2253
2254 IF g_log_enabled THEN
2255 l_log_module := C_DEFAULT_MODULE||'.xla_gl_transfer';
2256 END IF;
2257
2258 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2259 trace
2260 (p_msg => 'BEGIN of procedure XLA_GL_TRANSFER'
2261 ,p_level => C_LEVEL_PROCEDURE
2262 ,p_module => l_log_module);
2263
2264 xla_message('' , 'p_application_id = ' || p_application_id,'','','','','',
2265 l_log_module,
2266 C_LEVEL_PROCEDURE);
2267
2268 xla_message('' , 'p_user_id = ' || p_user_id,'','','','','',
2269 l_log_module,
2270 C_LEVEL_PROCEDURE);
2271
2272 xla_message('' , 'p_org_id = ' || p_org_id,'','','','','',
2273 l_log_module,
2274 C_LEVEL_PROCEDURE);
2275
2276 xla_message('' , 'p_request_id = ' || p_request_id,'','','','','',
2277 l_log_module,
2278 C_LEVEL_PROCEDURE);
2279
2280 xla_message('' , 'p_program_name = ' || p_program_name,'','','','','',
2281 l_log_module,
2282 C_LEVEL_PROCEDURE);
2283
2284 xla_message('' , 'p_selection_type = ' || p_selection_type,'','','','','',
2285 l_log_module,
2286 C_LEVEL_PROCEDURE);
2287
2288 xla_message('' , 'p_batch_name = ' || p_batch_name,'','','','','',
2289 l_log_module,
2290 C_LEVEL_PROCEDURE);
2291
2292 xla_message('' , 'p_source_doc_id = ' || p_source_doc_id,'','','','','',
2293 l_log_module,
2294 C_LEVEL_PROCEDURE);
2295
2296 xla_message('' , 'p_source_document_table = ' || p_source_document_table,'','','','','',
2297 l_log_module,
2298 C_LEVEL_PROCEDURE);
2299
2300 xla_message('' , 'p_start_date = ' || To_char(p_start_date,'MM/DD/YYYY'),'','','','','',
2301 l_log_module,
2302 C_LEVEL_PROCEDURE);
2303
2304 xla_message('' , 'p_end_date = ' || To_char(p_end_date,'MM/DD/YYYY'),'','','','','',
2305 l_log_module,
2306 C_LEVEL_PROCEDURE);
2307
2308 xla_message('' , 'p_validate_account = ' || p_validate_account,'','','','','',
2309 l_log_module,
2310 C_LEVEL_PROCEDURE);
2311
2312 xla_message('' , 'p_gl_transfer_mode = ' || p_gl_transfer_mode,'','','','','',
2313 l_log_module,
2314 C_LEVEL_PROCEDURE);
2315
2316 xla_message('' , 'p_submit_journal_import = ' || NVL(p_submit_journal_import,'Y'),'','','','','',
2317 l_log_module,
2318 C_LEVEL_PROCEDURE);
2319
2320 xla_message('' , 'p_summary_journal_entry = ' || NVL(p_summary_journal_entry,'N'),'','','','','',
2321 l_log_module,
2322 C_LEVEL_PROCEDURE);
2323
2324 xla_message('' , 'p_process_days = ' || p_process_days,'','','','','',
2325 l_log_module,
2326 C_LEVEL_PROCEDURE);
2327
2328 xla_message('' , 'p_batch_desc = ' || p_batch_desc,'','','','','',
2329 l_log_module,
2330 C_LEVEL_PROCEDURE);
2331
2332 xla_message('' , 'p_je_desc = ' || p_je_desc,'','','','','',
2333 l_log_module,
2334 C_LEVEL_PROCEDURE);
2335
2336 xla_message('' , 'p_je_line_desc = ' || p_je_line_desc,'','','','','',
2337 l_log_module,
2338 C_LEVEL_PROCEDURE);
2339
2340 xla_message('' , 'p_fc_force_flag = ' || l_fc_force_flag,'','','','','',
2341 l_log_module,
2342 C_LEVEL_PROCEDURE);
2343
2344 xla_message('' , 'p_debug_flag = ' || p_debug_flag,'','','','','',
2345 l_log_module,
2346 C_LEVEL_PROCEDURE);
2347
2348 END IF;
2349
2350 IF p_fc_force_flag THEN
2351 l_fc_force_flag := 'TRUE';
2352 ELSE
2353 l_fc_force_flag := 'FALSE';
2354 END IF;
2355 -- Initialize Variables
2356
2357 g_application_id := p_application_id;
2358 g_program_id := fnd_global.conc_program_id;
2359 g_user_id := p_user_id;
2360 g_program_name := p_program_name;
2361
2362 -- Check input parameters
2363 check_input_param(p_selection_type,
2364 p_start_date,
2365 p_end_date,
2366 p_gl_transfer_mode,
2367 p_source_doc_id,
2368 p_source_document_table
2369 );
2370
2371 -- Get the user source name
2372
2373 SELECT je_source_name, account_validation_flag, period_status_table_name,
2374 pre_commit_api_name, application_id, NVL(entry_type,'A')
2375 INTO l_source_name, l_acct_validation_flag, g_periods_table,
2376 l_pre_commit_api, l_application_id, g_entry_type
2377 FROM xla_gl_transfer_programs
2378 WHERE program_name = p_program_name;
2379
2380 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2381 xla_message('' , 'SOB list count = ' || p_sob_list.count,'','','','','',l_log_module,
2382 C_LEVEL_STATEMENT);
2383 END IF;
2384 FOR i IN p_sob_list.first..p_sob_list.last LOOP
2385 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2386 xla_message('' , 'SOB(' || i || ').sob_id = ' || p_sob_list(i).sob_id,'','','','','',
2387 l_log_module,
2388 C_LEVEL_STATEMENT);
2389
2390 xla_message('' , 'SOB(' || i || ').sob_name = ' || p_sob_list(i).sob_name,'','','','','',
2391 l_log_module,
2392 C_LEVEL_STATEMENT);
2393
2394 xla_message('' , 'SOB(' || i || ').sob_curr_code = ' || p_sob_list(i).sob_curr_code,'','','','','',
2395 l_log_module,
2396 C_LEVEL_STATEMENT);
2397
2398 xla_message('' , 'SOB(' || i || ').ave_bal_flag = ' || p_sob_list(i).average_balances_flag,'','','','','',
2399 l_log_module,
2400 C_LEVEL_STATEMENT);
2401
2402 xla_message('' , 'SOB(' || i || ').legal_entity_id = ' || p_sob_list(i).legal_entity_id,'','','','','',
2403 l_log_module,
2404 C_LEVEL_STATEMENT);
2405
2406 xla_message('' , 'SOB(' || i || ').cost_group_id = ' || p_sob_list(i).cost_group_id,'','','','','',
2407 l_log_module,
2408 C_LEVEL_STATEMENT);
2409
2410 xla_message('' , 'SOB(' || i || ').cost_type_id = ' || p_sob_list(i).cost_type_id,'','','','','',
2411 l_log_module,
2412 C_LEVEL_STATEMENT);
2413
2414 END IF;
2415
2416 END LOOP;
2417
2418 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2419 xla_message('' , 'p_journal_category count = ' || p_journal_category.count,'','','','','',
2420 l_log_module,
2421 C_LEVEL_STATEMENT);
2422
2423 END IF;
2424 FOR i IN p_journal_category.FIRST..p_journal_category.LAST LOOP
2425 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2426 xla_message('' , 'journal_category(' || i || ') = ' || p_journal_category(i),'','','','','',
2427 l_log_module,
2428 C_LEVEL_STATEMENT);
2429 END IF;
2430 END LOOP;
2431 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2432 xla_message('' , '------------------------------------------','','','','','',
2433 l_log_module,
2434 C_LEVEL_STATEMENT);
2435 END IF;
2436
2437 -- Legal Entity, Cost Group, Cost Type is one is not null then
2438 -- all three must be not null.
2439
2440 FOR i IN p_sob_list.first..p_sob_list.last LOOP
2441 IF p_sob_list(i).legal_entity_id IS NOT NULL THEN
2442 IF p_sob_list(i).cost_group_id IS NULL OR p_sob_list(i).cost_type_id IS NULL THEN
2443 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2444 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2445 l_log_module,
2446 C_LEVEL_EXCEPTION);
2447 END IF;
2448 APP_EXCEPTION.RAISE_EXCEPTION;
2449 END IF;
2450 ELSIF p_sob_list(i).cost_group_id IS NOT NULL THEN
2451 IF p_sob_list(i).legal_entity_id IS NULL OR p_sob_list(i).cost_type_id IS NULL THEN
2452 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2453 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2454 l_log_module,
2455 C_LEVEL_EXCEPTION);
2456 END IF;
2457 APP_EXCEPTION.RAISE_EXCEPTION;
2458 END IF;
2459 ELSIF p_sob_list(i).cost_type_id IS NOT NULL THEN
2460 IF p_sob_list(i).legal_entity_id IS NULL OR p_sob_list(i).cost_group_id IS NULL THEN
2461 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2462 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2463 l_log_module,
2464 C_LEVEL_EXCEPTION);
2465 END IF;
2466 APP_EXCEPTION.RAISE_EXCEPTION;
2467 END IF;
2468 END IF;
2469 END LOOP;
2470
2471 -- Check if GL is installed.
2472 IF (FND_INSTALLATION.GET(101, 101, l_gl_installed_flag, industry)) THEN
2473 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2474 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2475
2476 xla_message('XLA_GLT_GL_INSTALLED','','','','',
2477 '','', l_log_module,
2478 C_LEVEL_STATEMENT);
2479
2480 END IF;
2481 ELSE
2482 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2483
2484 xla_message('XLA_GLT_GL_NOT_INSTALLED','','','',
2485 '','','', l_log_module,
2486 C_LEVEL_STATEMENT);
2487
2488 END IF;
2489 END IF;
2490 END IF;
2491
2492 -- Get the user source name for an application.
2493 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2494 xla_message('XLA_GLT_GET_SOURCE_NAME','','','',
2495 '','','', l_log_module,
2496 C_LEVEL_STATEMENT);
2497 END IF;
2498
2499 SELECT user_je_source_name
2500 INTO l_user_source_name
2501 FROM gl_je_sources js
2502 WHERE je_source_name = l_source_name;
2503
2504 -- Validate periods if GL is installed.
2505 -- Bug2543724. Skipping Accounting Period validation for Budget journals
2506 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2507 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2508 xla_message('XLA_GLT_VALIDATE_PERIODS','','','','','',
2509 '', l_log_module,
2510 C_LEVEL_STATEMENT);
2511 END IF;
2512
2513 IF p_selection_type = 1 THEN -- this is only for SRS, Doc Transfer will call it later.
2514 IF(g_entry_type = 'A') THEN
2515 validate_periods(p_selection_type,
2516 p_sob_list,
2517 p_program_name,
2518 p_start_date,
2519 p_end_date
2520 );
2521
2522 END IF;
2523
2524 END IF;
2525 END IF;
2526
2527 -- If the transfer is submitted for more than one sobs then we will
2528 -- process one SOB at a time.
2529
2530 -- Loop to process each set of books.
2531 FOR i IN p_sob_list.FIRST..p_sob_list.LAST LOOP
2532 l_set_of_books_id := p_sob_list(i).sob_id;
2533 l_sob_name := p_sob_list(i).sob_name;
2534 g_base_currency_code := p_sob_list(i).sob_curr_code;
2535 l_encumbrance_flag := p_sob_list(i).encum_flag;
2536
2537 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2538 xla_message('XLA_GLT_PROCESS_SOB','SOB_NAME', l_sob_name,'','','','', l_log_module,
2539 C_LEVEL_STATEMENT);
2540 END IF;
2541
2542 IF l_set_of_books_id IS NOT NULL THEN
2543 SELECT chart_of_accounts_id
2544 INTO l_coa_id
2545 FROM gl_sets_of_books
2546 WHERE set_of_books_id = l_set_of_books_id;
2547 END IF;
2548
2549 -- Get Transfer Run Id
2550 SELECT xla_gl_transfer_runid_s.NEXTVAL
2551 INTO l_transfer_run_id
2552 FROM dual;
2553
2554 -- Set the batch Name
2555
2556 --Bug3196153. p_batch_name exceeds the limit of varchar2(30)
2557 --during translation in some languages.
2558 g_batch_name := SUBSTRB(p_batch_name || ' ' || l_transfer_run_id,1,30);
2559
2560 -- If GL is installed populate group id and inter_run_id;
2561 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2562 SELECT gl_interface_control_s.NEXTVAL, gl_journal_import_s.NEXTVAL
2563 INTO l_group_id, l_interface_run_id
2564 FROM dual;
2565 END IF;
2566
2567 ---------------------------------------------------------------------------
2568 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2569
2570 xla_message('' , 'Batch_Name = ' || g_batch_name,'','','','','',
2571 l_log_module,
2572 C_LEVEL_STATEMENT);
2573
2574 xla_message('' , 'Transfer_run_id = ' || l_transfer_run_id,'','','','','',
2575 l_log_module,
2576 C_LEVEL_STATEMENT);
2577
2578 xla_message('' , 'Group_id = ' || l_group_id,'','','','','',
2579 l_log_module,
2580 C_LEVEL_STATEMENT);
2581
2582 xla_message('' , 'Interface_run_id = ' || l_interface_run_id,'','','','','',
2583 l_log_module,
2584 C_LEVEL_STATEMENT);
2585
2586 END IF;
2587
2588 ---------------------------------------------------------------------------
2589
2590 ---------------------------------------------------------------------
2591 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2592
2593 xla_message('XLA_GLT_INSERT_XTB','','','','','','',
2594 l_log_module,
2595 C_LEVEL_STATEMENT);
2596
2597 END IF;
2598 ---------------------------------------------------------------------
2599 INSERT INTO xla_gl_transfer_batches_all
2600 ( gl_transfer_run_id,
2601 request_id ,
2602 application_id ,
2603 user_id ,
2604 selection_type ,
2605 set_of_books_id ,
2606 batch_name,
2607 source_id ,
2608 source_table ,
2609 transfer_from_date,
2610 transfer_to_date,
2611 ae_category ,
2612 gl_transfer_mode ,
2613 submit_journal_import ,
2614 summary_journal_entry ,
2615 process_days ,
2616 gl_transfer_date,
2617 group_id,
2618 interface_run_id,
2619 org_id,
2620 legal_entity_id,
2621 cost_group_id,
2622 cost_type_id,
2623 transfer_status
2624 )
2625 VALUES
2626 ( l_transfer_run_id,
2627 p_request_id,
2628 p_application_id,
2629 p_user_id,
2630 p_selection_type ,
2631 p_sob_list(i).sob_id ,
2632 g_batch_name ,
2633 p_source_doc_id ,
2634 p_source_document_table ,
2635 p_start_date ,
2636 p_end_date ,
2637 p_journal_category(1),
2638 p_gl_transfer_mode ,
2639 NVL(p_submit_journal_import,'Y') ,
2640 NVL(p_summary_journal_entry,'N') ,
2641 p_process_days ,
2642 Sysdate,
2643 l_group_id,
2644 l_interface_run_id,
2645 p_org_id,
2646 p_sob_list(i).legal_entity_id,
2647 p_sob_list(i).cost_group_id,
2648 p_sob_list(i).cost_type_id,
2649 'P'
2650 );
2651
2652 g_rec_transfer_flag := 'N'; --reset the global flag for each sob
2653
2654 IF p_selection_type = 1 THEN
2655 -- If processing more than one period then break the date range into
2656 -- multiple peirods.
2657 OPEN c_getPeriods(p_sob_list(i).sob_id,
2658 p_start_date,
2659 p_end_date
2660 );
2661 LOOP -- Proecss Periods
2662 FETCH c_getPeriods
2663 INTO l_period_name, l_period_start_date,l_period_end_date,
2664 l_next_period, l_reversal_date;
2665 EXIT WHEN c_getPeriods%NOTFOUND;
2666
2667 -- Bug-4014659 deleted the if loop which checks for the NULL starting date
2668 -- because the loop makes the starting date of the latest open period as the
2669 -- start date of the GL transfer for reporting SOB which gives some inconsistency
2670 -- while posting.
2671
2672 --Added for bug 9276922
2673 -- l_start_date := Nvl(p_start_date, l_period_start_date);
2674 if (p_start_date is not null and p_start_date between
2675 l_period_start_date and l_period_end_date) then
2676 l_start_date := p_start_date;
2677 else
2678 l_start_date := l_period_start_date;
2679 end if;
2680 --Added for bug 9276922
2681
2682 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2683 xla_message('XLA_GLT_GET_PERIOD_INFO','','','','','','', l_log_module,
2684 C_LEVEL_STATEMENT);
2685 END IF;
2686
2687
2688 g_periods_cnt := g_periods_cnt + 1;
2689 g_control_info(g_periods_cnt).sob_id := l_set_of_books_id;
2690 g_control_info(g_periods_cnt).period_name := l_period_name;
2691 g_control_info(g_periods_cnt).rec_transferred := 0;
2692 g_control_info(g_periods_cnt).cnt_transfer_errors := 0;
2693 g_control_info(g_periods_cnt).cnt_acct_errors := 0;
2694
2695 <<process_commit_cycle>>
2696 LOOP
2697 -- Set the date range. Ignore process days specified by the user
2698 -- when summarized by period or encumbrance is used.
2699
2700 IF (NVL(p_process_days,0) = 0 OR
2701 Nvl(l_encumbrance_flag,'N') = 'Y' OR
2702 p_gl_transfer_mode = 'P') THEN
2703 -- If period end date > transfer end date then set
2704 -- the end date to transfer end date.
2705 l_end_date := Least(p_end_date, l_period_end_date);
2706 ELSE
2707 l_end_date := Least(l_start_date+(p_process_days-1),
2708 Least(l_period_end_date,p_end_date));
2709 END IF;
2710
2711 --temporarily fixed for the bug2139573 (add 23:59:59)
2712 l_end_date := trunc(l_end_date) + 86399/86400;
2713
2714 -- Reset proceed to 'Y' for each process_commit_cycle
2715 g_proceed := 'Y';
2716
2717 OPEN c_get_program_info(p_program_name);
2718 <<multiple_entities>>
2719 LOOP -- to process multiple accounting entities
2720 FETCH c_get_program_info
2721 INTO g_events_table, g_headers_table, g_lines_table,
2722 g_encumbrance_table, g_lines_sequence_name,
2723 g_enc_sequence_name, g_actual_table_alias, g_enc_table_alias;
2724 EXIT WHEN c_get_program_info%NOTFOUND;
2725
2726 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2727 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_start_date,
2728 'END_DATE',l_end_date,'','',l_log_module,
2729 C_LEVEL_STATEMENT);
2730 END IF;
2731
2732
2733 select_acct_headers( p_selection_type,
2734 l_set_of_books_id,
2735 p_source_doc_id,
2736 p_source_document_table,
2737 l_transfer_run_id,
2738 p_request_id,
2739 p_journal_category,
2740 l_start_date,
2741 l_end_date,
2742 p_sob_list(i).legal_entity_id,
2743 p_sob_list(i).cost_group_id,
2744 p_sob_list(i).cost_type_id,
2745 p_validate_account);
2746 -- Validate account on accounting entry lines if necessary
2747 IF Nvl(p_validate_account, Nvl(l_acct_validation_flag,'N')) = 'Y'
2748 AND g_proceed = 'Y' THEN
2749 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2750 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
2751 '',l_log_module,
2752 C_LEVEL_STATEMENT);
2753 END IF;
2754
2755 validate_acct_lines( p_selection_type,
2756 l_set_of_books_id,
2757 l_coa_id,
2758 l_transfer_run_id,
2759 l_start_date,
2760 l_end_date);
2761
2762 -- Update headers for the lines that failed accounting
2763 -- validation. Do not call routine if there are no
2764 -- accounting entry lines to process.
2765 IF g_proceed = 'Y' THEN
2766 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2767 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
2768 '',l_log_module,
2769 C_LEVEL_STATEMENT);
2770 END IF;
2771 validate_acct_headers( p_selection_type,
2772 l_set_of_books_id,
2773 l_transfer_run_id,
2774 l_start_date,
2775 l_end_date);
2776 END IF;
2777 END IF;
2778 -- Call following procedures only if there are records to
2779 -- process in this period
2780 IF g_proceed = 'Y' THEN
2781 IF p_gl_transfer_mode = 'D' THEN
2782 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2783 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2784 '',l_log_module,
2785 C_LEVEL_STATEMENT);
2786 END IF;
2787
2788 update_linkid_detail( l_transfer_run_id,
2789 p_request_id,
2790 l_start_date,
2791 l_end_date);
2792
2793 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2794 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2795 l_log_module,
2796 C_LEVEL_STATEMENT);
2797 END IF;
2798
2799 gl_insert_detail( p_request_id,
2800 l_user_source_name,
2801 l_transfer_run_id,
2802 l_period_name,
2803 l_start_date,
2804 l_end_date,
2805 l_next_period,
2806 l_reversal_date,
2807 p_sob_list(i).average_balances_flag,
2808 p_gl_transfer_mode,
2809 l_group_id,
2810 p_batch_desc,
2811 p_je_desc,
2812 p_je_line_desc);
2813
2814 ELSE -- Summarize By Accounting Date
2815
2816 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2817 xla_message('','Calling derive line types ','','','','','',l_log_module,C_LEVEL_STATEMENT );
2818 END IF;
2819
2820 IF g_line_type IS NULL THEN
2821 derive_line_types;
2822 END IF;
2823
2824 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2825 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2826 l_log_module,
2827 C_LEVEL_STATEMENT);
2828 END IF;
2829
2830 gl_insert_summary( p_request_id,
2831 l_user_source_name,
2832 l_transfer_run_id,
2833 l_period_name,
2834 l_start_date,
2835 l_end_date,
2836 l_next_period,
2837 l_reversal_date,
2838 p_sob_list(i).average_balances_flag,
2839 p_gl_transfer_mode,
2840 l_group_id,
2841 p_batch_desc,
2842 p_je_desc,
2843 p_je_line_desc);
2844
2845 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2846 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2847 '',l_log_module,
2848 C_LEVEL_STATEMENT);
2849 END IF;
2850
2851 update_linkid_summary( p_request_id,
2852 p_gl_transfer_mode,
2853 l_transfer_run_id,
2854 l_start_date,
2855 l_end_date);
2856
2857 IF g_line_type IS NOT NULL THEN
2858
2859 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2860 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2861 '',l_log_module,
2862 C_LEVEL_STATEMENT);
2863 END IF;
2864
2865 update_linkid_detail( l_transfer_run_id,
2866 p_request_id,
2867 l_start_date,
2868 l_end_date);
2869
2870 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2871 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2872 l_log_module,
2873 C_LEVEL_STATEMENT);
2874 END IF;
2875
2876 gl_insert_detail( p_request_id,
2877 l_user_source_name,
2878 l_transfer_run_id,
2879 l_period_name,
2880 l_start_date,
2881 l_end_date,
2882 l_next_period,
2883 l_reversal_date,
2884 p_sob_list(i).average_balances_flag,
2885 p_gl_transfer_mode,
2886 l_group_id,
2887 p_batch_desc,
2888 p_je_desc,
2889 p_je_line_desc);
2890
2891 END IF;
2892
2893 END IF;
2894
2895 -- Transfer encumbrance reversals to gl_interface if
2896 -- encumbrance is used.
2897 IF (Nvl(l_encumbrance_flag,'N') = 'Y') THEN
2898 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2899 xla_message('XLA_GLT_CALL_ENCUM_ROUTINE','','','','','',
2900 '',l_log_module,
2901 C_LEVEL_STATEMENT);
2902 END IF;
2903
2904 transfer_enc_lines( p_application_id,
2905 l_set_of_books_id,
2906 l_transfer_run_id,
2907 l_start_date,
2908 l_end_date,
2909 l_next_period,
2910 l_reversal_date,
2911 p_sob_list(i).average_balances_flag,
2912 l_user_source_name,
2913 l_group_id,
2914 l_request_id,
2915 p_batch_desc,
2916 p_je_desc,
2917 p_je_line_desc);
2918 END IF;
2919 -- Call Globalization Routine
2920
2921 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2922 xla_message('XLA_GLT_CALLING_JG_PKG','','','','','','',l_log_module,
2923 C_LEVEL_STATEMENT);
2924 END IF;
2925
2926
2927 JG_XLA_GL_TRANSFER_PKG.jg_xla_gl_transfer
2928 ( p_application_id,
2929 p_user_id,
2930 p_org_id,
2931 p_request_id,
2932 l_transfer_run_id,
2933 p_program_name,
2934 p_selection_type,
2935 p_batch_name,
2936 l_start_date,
2937 l_end_date,
2938 p_gl_transfer_mode,
2939 p_process_days,
2940 p_debug_flag
2941 );
2942
2943 END IF;
2944 END LOOP multiple_entities; -- Multiple entities loop
2945 CLOSE c_get_program_info;
2946
2947 IF p_process_days IS NOT NULL THEN
2948 -- Save changes if commit cycle is needed
2949 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2950 xla_message('XLA_GLT_SAVE_WORK','','','','','','',l_log_module,
2951 C_LEVEL_STATEMENT);
2952 END IF;
2953 COMMIT;
2954 END IF;
2955
2956 IF ( p_selection_type = 1 AND
2957 l_end_date < Least(p_end_date,l_period_end_date)) THEN
2958 l_start_date := l_end_date+1;
2959 ELSE
2960 EXIT;
2961 END IF;
2962 END LOOP process_commit_cycle ;
2963 END LOOP; --process_periods
2964
2965 -- Log an error if there are no open periods
2966 IF c_getPeriods%ROWCOUNT = 0 THEN
2967 CLOSE c_getPeriods;
2968 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2969
2970 xla_message('XLA_GLT_NO_OPEN_PERIODS','SOB_NAME',l_sob_name,
2971 '','','','', l_log_module,C_LEVEL_EXCEPTION);
2972 END IF;
2973 APP_EXCEPTION.RAISE_EXCEPTION;
2974 END IF;
2975 CLOSE c_getPeriods;
2976 ELSE
2977 -- Document Specific Transfer
2978 OPEN c_get_program_info(p_program_name);
2979 LOOP
2980 FETCH c_get_program_info
2981 INTO g_events_table, g_headers_table, g_lines_table,
2982 g_encumbrance_table, g_lines_sequence_name,
2983 g_enc_sequence_name, g_actual_table_alias, g_enc_table_alias;
2984 EXIT WHEN c_get_program_info%NOTFOUND;
2985
2986 -- Select Accounting Entries.
2987 select_acct_headers( p_selection_type,
2988 l_set_of_books_id,
2989 p_source_doc_id,
2990 p_source_document_table,
2991 l_transfer_run_id,
2992 p_request_id,
2993 p_journal_category,
2994 l_start_date,
2995 l_end_date,
2996 p_sob_list(i).legal_entity_id,
2997 p_sob_list(i).cost_group_id,
2998 p_sob_list(i).cost_type_id,
2999 p_validate_account
3000 );
3001 -- Get the Date range to see if the entries are in
3002 -- multiple accounting periods
3003 IF g_proceed = 'Y' THEN
3004 get_date_range(l_transfer_run_id,
3005 l_start_date,
3006 l_end_date
3007 );
3008
3009 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3010 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_start_date,
3011 'END_DATE',l_end_date,'','',l_log_module,
3012 C_LEVEL_STATEMENT);
3013 END IF;
3014 -- Validate Period/ Year
3015 -- Bug2543724. Skipping Accounting Period validation for Budget journals
3016
3017 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
3018
3019 IF(g_entry_type = 'A') THEN
3020 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3021 xla_message('','Calling validate periods ','','','','','',l_log_module,C_LEVEL_STATEMENT );
3022 END IF;
3023 validate_periods(p_selection_type,
3024 p_sob_list,
3025 p_program_name,
3026 l_start_date,
3027 l_end_date
3028 );
3029 END IF;
3030
3031 END IF;
3032
3033 -- Process entries by periods
3034 OPEN c_getPeriods(p_sob_list(i).sob_id,
3035 l_start_date,
3036 l_end_date
3037 );
3038 LOOP -- Proecss Periods
3039 FETCH c_getPeriods
3040 INTO l_period_name, l_period_start_date,l_period_end_date,
3041 l_next_period, l_reversal_date;
3042 EXIT WHEN c_getPeriods%NOTFOUND;
3043
3044 --temporarily fixed for the bug2139573 (add 23:59:59)
3045 l_period_end_date := trunc(l_period_end_date) + 86399/86400;
3046
3047 -- Reset proceed to 'Y' for each period
3048 g_proceed := 'Y';
3049
3050 g_periods_cnt := g_periods_cnt + 1;
3051 g_control_info(g_periods_cnt).sob_id := l_set_of_books_id;
3052 g_control_info(g_periods_cnt).period_name := l_period_name;
3053 g_control_info(g_periods_cnt).rec_transferred := 0;
3054 g_control_info(g_periods_cnt).cnt_transfer_errors := 0;
3055 g_control_info(g_periods_cnt).cnt_acct_errors := 0;
3056
3057 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3058 xla_message('','~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~','','','','','',l_log_module,C_LEVEL_STATEMENT );
3059 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_period_start_date,
3060 'END_DATE',l_period_end_date,'','',l_log_module,C_LEVEL_STATEMENT );
3061 END IF;
3062
3063 -- Validate account on accounting entry lines if necessary
3064 IF Nvl(p_validate_account, Nvl(l_acct_validation_flag,'N')) = 'Y'
3065 AND g_proceed = 'Y' THEN
3066 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3067 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
3068 '',l_log_module,
3069 C_LEVEL_STATEMENT);
3070 END IF;
3071 validate_acct_lines( p_selection_type,
3072 l_set_of_books_id,
3073 l_coa_id,
3074 l_transfer_run_id,
3075 l_period_start_date,
3076 l_period_end_date);
3077
3078 -- Update headers for the lines that failed accounting
3079 -- validation. Do not call routine if there are no
3080 -- accounting entry lines to process.
3081 IF g_proceed = 'Y' THEN
3082 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3083 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
3084 '',l_log_module,
3085 C_LEVEL_STATEMENT);
3086 END IF;
3087 validate_acct_headers( p_selection_type,
3088 l_set_of_books_id,
3089 l_transfer_run_id,
3090 l_period_start_date,
3091 l_period_end_date);
3092
3093 END IF;
3094 END IF;
3095 -- Call following procedures only if there are records to
3096 -- process in this period
3097 IF g_proceed = 'Y' THEN
3098 IF p_gl_transfer_mode = 'D' THEN
3099 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3100 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3101 '',l_log_module,
3102 C_LEVEL_STATEMENT);
3103 END IF;
3104 update_linkid_detail( l_transfer_run_id,
3105 p_request_id,
3106 l_period_start_date,
3107 l_period_end_date);
3108
3109 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3110 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3111 l_log_module,
3112 C_LEVEL_STATEMENT);
3113 END IF;
3114
3115 gl_insert_detail( p_request_id,
3116 l_user_source_name,
3117 l_transfer_run_id,
3118 l_period_name,
3119 l_period_start_date,
3120 l_period_end_date,
3121 l_next_period,
3122 l_reversal_date,
3123 p_sob_list(i).average_balances_flag,
3124 p_gl_transfer_mode,
3125 l_group_id,
3126 p_batch_desc,
3127 p_je_desc,
3128 p_je_line_desc);
3129
3130 ELSE -- Summarize By Accounting Date
3131
3132 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3133 xla_message('','Calling derive line types ','','','','','',l_log_module,C_LEVEL_STATEMENT );
3134 END IF;
3135
3136 IF g_line_type IS NULL THEN
3137 derive_line_types;
3138 END IF;
3139
3140 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3141 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3142 l_log_module,
3143 C_LEVEL_STATEMENT);
3144 END IF;
3145
3146 gl_insert_summary( p_request_id,
3147 l_user_source_name,
3148 l_transfer_run_id,
3149 l_period_name,
3150 l_period_start_date,
3151 l_period_end_date,
3152 l_next_period,
3153 l_reversal_date,
3154 p_sob_list(i).average_balances_flag,
3155 p_gl_transfer_mode,
3156 l_group_id,
3157 p_batch_desc,
3158 p_je_desc,
3159 p_je_line_desc);
3160
3161 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3162 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3163 '',l_log_module,
3164 C_LEVEL_STATEMENT);
3165 END IF;
3166
3167 update_linkid_summary( p_request_id,
3168 p_gl_transfer_mode,
3169 l_transfer_run_id,
3170 l_period_start_date,
3171 l_period_end_date);
3172
3173
3174 IF g_line_type IS NOT NULL THEN
3175
3176 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3177 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3178 '',l_log_module,
3179 C_LEVEL_STATEMENT);
3180 END IF;
3181
3182 update_linkid_detail( l_transfer_run_id,
3183 p_request_id,
3184 l_start_date,
3185 l_end_date);
3186
3187 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3188 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3189 l_log_module,
3190 C_LEVEL_STATEMENT);
3191 END IF;
3192
3193
3194 gl_insert_detail( p_request_id,
3195 l_user_source_name,
3196 l_transfer_run_id,
3197 l_period_name,
3198 l_period_start_date,
3199 l_period_end_date,
3200 l_next_period,
3201 l_reversal_date,
3202 p_sob_list(i).average_balances_flag,
3203 p_gl_transfer_mode,
3204 l_group_id,
3205 p_batch_desc,
3206 p_je_desc,
3207 p_je_line_desc);
3208
3209 END IF;
3210
3211 END IF;
3212
3213 -- Transfer encumbrance reversals to gl_interface if
3214 -- encumbrance is used.
3215 IF (Nvl(l_encumbrance_flag,'N') = 'Y') THEN
3216 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3217 xla_message('XLA_GLT_CALL_ENCUM_ROUTINE','','','','','',
3218 '',l_log_module,
3219 C_LEVEL_STATEMENT);
3220 END IF;
3221 transfer_enc_lines( p_application_id,
3222 l_set_of_books_id,
3223 l_transfer_run_id,
3224 l_period_start_date,
3225 l_period_end_date,
3226 l_next_period,
3227 l_reversal_date,
3228 p_sob_list(i).average_balances_flag,
3229 l_user_source_name,
3230 l_group_id,
3231 l_request_id,
3232 p_batch_desc,
3233 p_je_desc,
3234 p_je_line_desc);
3235 END IF;
3236 -- Call Globalization Routine
3237 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3238 xla_message('XLA_GLT_CALLING_JG_PKG','','','','','','',l_log_module,
3239 C_LEVEL_STATEMENT);
3240 END IF;
3241
3242 JG_XLA_GL_TRANSFER_PKG.jg_xla_gl_transfer
3243 ( p_application_id,
3244 p_user_id,
3245 p_org_id,
3246 p_request_id,
3247 l_transfer_run_id,
3248 p_program_name,
3249 p_selection_type,
3250 p_batch_name,
3251 l_period_start_date,
3252 l_period_end_date,
3253 p_gl_transfer_mode,
3254 p_process_days,
3255 p_debug_flag
3256 );
3257 END IF;
3258 END LOOP; -- Process Periods
3259 CLOSE c_getPeriods;
3260 END IF; -- Entries Found
3261 END LOOP; -- Multiple Entities
3262 CLOSE c_get_program_info;
3263 END IF; -- Selection Type
3264
3265 -- Call product specific API
3266 -- AP Trial Balance
3267 IF l_pre_commit_api IS NOT NULL THEN
3268 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3269 xla_message('XLA_GLT_CALL_PRE_COMMIT_API', 'API_NAME',
3270 l_pre_commit_api, '','','','',l_log_module,
3271 C_LEVEL_STATEMENT);
3272 END IF;
3273 EXECUTE IMMEDIATE
3274 ' begin ' || l_pre_commit_api ||'( '|| l_transfer_run_id || ' ); end;';
3275 END IF;
3276
3277 -- Bug# 4675862 - Call PSA API if Bugetary control is enabled for this
3278 -- set_of_books_id or USSGL profile option is Yes or encumbrance
3279 -- accounting is being used.
3280 -- (call the funds checker only if there are entries to process)
3281
3282 IF Nvl(l_gl_installed_flag,'N') = 'I' AND g_rec_transfer_flag = 'Y' THEN
3283 -- Bug2691999. Insert records only if, Submit Journal Import = Y
3284 IF NVL(p_submit_journal_import,'Y') = 'Y' THEN
3285 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3286 xla_message('XLA_GLT_INSERT_GIC','STATUS','S','','','','',l_log_module,
3287 C_LEVEL_STATEMENT);
3288 END IF;
3289
3290 INSERT INTO gl_interface_control
3291 ( JE_SOURCE_NAME,
3292 STATUS,
3293 INTERFACE_RUN_ID,
3294 GROUP_ID,
3295 SET_OF_BOOKS_ID,
3296 PACKET_ID
3297 )
3298 VALUES
3299 (
3300 l_source_name,
3301 'S',
3302 l_interface_run_id,
3303 l_group_id,
3304 l_set_of_books_id,
3305 ''
3306 );
3307 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3308 xla_message('XLA_GLT_SUBMIT_JOURNAL_IMP','','','','','','',l_log_module,C_LEVEL_STATEMENT);
3309 END IF;
3310 l_submittedreqid:= fnd_request.submit_request
3311 (
3312 'SQLGL', -- application short name
3313 'GLLEZL', -- program short name
3314 NULL, -- program name
3315 NULL, -- start date
3316 FALSE, -- sub-request
3317 l_interface_run_id, -- interface run id
3318 l_set_of_books_id, -- set of books id
3319 'N', -- error to suspense flag
3320 NULL, -- from accounting date
3321 NULL, -- to accounting date
3322 NVL(p_summary_journal_entry,'N'), -- create summary flag
3323 'N' -- import desc flex flag
3324 );
3325
3326
3327 IF Nvl(l_submittedreqid,0) = 0 THEN
3328 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3329 xla_message('XLA_GLT_JOURNALIMP_ERROR','','','','','','',l_log_module,C_LEVEL_EXCEPTION);
3330 END IF;
3331 ELSE
3332 -- Journal Import is submitted successfully.
3333 -- Call PSA routine. Bug# 4675862
3334 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3335 xla_message('XLA_GLT_JOURNALIMP_SUBMITTED','REQUEST_ID',
3336 l_submittedreqid,'','','','',l_log_module,C_LEVEL_STATEMENT);
3337 END IF;
3338 xla_utility_pkg.print_logfile('Calling PSA_FUNDS_CHECKER_PKG');
3339 PSA_FUNDS_CHECKER_PKG.populate_group_id
3340 (p_grp_id => l_group_id
3341 ,p_application_id => g_application_id
3342 );
3343 END IF;
3344 END IF;
3345 END IF;
3346
3347 UPDATE xla_gl_transfer_batches_all
3348 SET gllezl_request_id = l_submittedreqid,
3349 transfer_status = Decode(g_sob_rows_created,0,'N','C'),
3350 packet_id = l_packet_id
3351 WHERE gl_transfer_run_id = l_transfer_run_id;
3352 COMMIT;
3353 g_total_rows_created := g_total_rows_created + g_sob_rows_created;
3354
3355 END LOOP; -- process sobs
3356 IF g_total_rows_created > 0 THEN
3357 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3358 xla_message('XLA_GLT_TRANSFER_SUCCESS','','','',
3359 '','','',l_log_module,C_LEVEL_STATEMENT);
3360 END IF;
3361 ELSE
3362 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3363 xla_message('XLA_GLT_NO_DATA','','','',
3364 '','','',l_log_module,C_LEVEL_STATEMENT);
3365 END IF;
3366 END IF;
3367 EXCEPTION
3368 WHEN OTHERS THEN
3369 IF (SQLCODE <> -20001) THEN
3370 IF (C_LEVEL_ERROR >= g_log_level) THEN
3371 xla_message('XLA_GLT_DEBUG','ERROR', Sqlerrm, 'DEBUG_INFO',
3372 l_debug_info,'','',l_log_module,C_LEVEL_ERROR);
3373 END IF;
3374 END IF;
3375 RAISE;
3376 END XLA_GL_TRANSFER;
3377
3378 BEGIN
3379 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3380 g_log_enabled := fnd_log.test
3381 (log_level => g_log_level
3382 ,module => C_DEFAULT_MODULE);
3383
3384 IF NOT g_log_enabled THEN
3385 g_log_level := C_LEVEL_LOG_DISABLED;
3386 END IF;
3387
3388 END XLA_GL_TRANSFER_PKG;