[Home] [Help]
PACKAGE BODY: APPS.XLA_GL_TRANSFER_PKG
Source
1 PACKAGE BODY xla_gl_transfer_pkg AS
2 /* $Header: XLACGLXB.pls 120.21 2006/09/12 22:52:01 svjoshi 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 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, 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,
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 dbms_sql.bind_variable(cid,':b_batch_desc', p_batch_desc);
1463 dbms_sql.bind_variable(cid,':b_je_desc', p_je_desc);
1464 dbms_sql.bind_variable(cid,':b_je_line_desc', p_je_line_desc);
1465 dbms_sql.bind_variable(cid,':b_next_period', p_next_period);
1466 dbms_sql.bind_variable(cid,':b_reversal_date', p_reversal_date);
1467 dbms_sql.bind_variable(cid,':b_average_balances_flag', p_average_balances_flag);
1468 dbms_sql.bind_variable(cid,':b_program_name', g_program_name);
1469
1470 IF p_gl_transfer_mode = 'P' THEN
1471 dbms_sql.bind_variable(cid,':b_period_name', p_period_name);
1472 END IF;
1473
1474 rows_processed := dbms_sql.execute(cid);
1475
1476 IF rows_processed = 0 THEN
1477 g_proceed := 'N';
1478 ELSE
1479 g_sob_rows_created := g_sob_rows_created + rows_processed;
1480 g_rec_transfer_flag := 'Y'; --set the globle flag to 'Y' whenever there are records transferred.
1481 END IF;
1482 dbms_sql.close_cursor(cid);
1483
1484 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1485 xla_message('XLA_GLT_GL_INSERT','COUNT','(summary) ' || rows_processed,'','','','',
1486 l_log_module,
1487 C_LEVEL_STATEMENT);
1488 END IF;
1489
1490 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1491 trace
1492 (p_msg => 'END of procedure GL_INSERT_SUMMARY'
1493 ,p_level => C_LEVEL_PROCEDURE
1494 ,p_module => l_log_module);
1495 END IF;
1496
1497 END gl_insert_summary ;
1498
1499 -- This procedure transfers all the journal lines in detail mode.
1500
1501 -- Data in XLA_JE_LINE_TYPES will be ignored.
1502
1503 PROCEDURE gl_insert_detail( p_request_id NUMBER,
1504 p_source_name VARCHAR2,
1505 p_transfer_run_id NUMBER,
1506 p_period_name VARCHAR2,
1507 p_start_date DATE,
1508 p_end_date DATE,
1509 p_next_period VARCHAR2,
1510 p_reversal_date DATE,
1511 p_average_balances_flag VARCHAR2,
1512 p_gl_transfer_mode VARCHAR2,
1513 p_group_id NUMBER,
1514 p_batch_desc VARCHAR2,
1515 p_je_desc VARCHAR2,
1516 p_je_line_desc VARCHAR2) IS
1517
1518 statement_detail VARCHAR2(10000) ;
1519
1520 cid BINARY_INTEGER;
1521 rows_processed NUMBER;
1522
1523 l_from VARCHAR2(1000);
1524 l_where VARCHAR2(1000);
1525 l_reference3 VARCHAR2(400);
1526
1527 l_select_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1528 l_insert_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1529 l_group_by_actual_flag VARCHAR2(1000); -- This is for different entry type A or B
1530
1531 l_log_module VARCHAR2(255);
1532
1533 BEGIN
1534
1535 IF g_log_enabled THEN
1536 l_log_module := C_DEFAULT_MODULE||'.gl_insert_detail';
1537 END IF;
1538
1539 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1540 trace
1541 (p_msg => 'BEGIN of procedure GL_INSERT_DETAIL'
1542 ,p_level => C_LEVEL_PROCEDURE
1543 ,p_module => l_log_module);
1544
1545 END IF;
1546
1547
1548 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1549 trace
1550 (p_msg => 'l_from = ' || l_from
1551 ,p_level => C_LEVEL_STATEMENT
1552 ,p_module => l_log_module);
1553
1554 trace
1555 (p_msg => 'l_where = ' || l_where
1556 ,p_level => C_LEVEL_STATEMENT
1557 ,p_module => l_log_module);
1558
1559
1560 trace
1561 (p_msg => 'l_reference3 = ' || l_reference3
1562 ,p_level => C_LEVEL_STATEMENT
1563 ,p_module => l_log_module);
1564 END IF;
1565
1566
1567 /*----------------------------------------------------------------
1568 1. 'A' is for Actual -- only for AP and CST -- old source code
1569 2. 'B' is for Budget and Actual -- new requirement for PSB
1570 -----------------------------------------------------------------*/
1571 IF g_entry_type = 'A' THEN
1572 l_select_actual_flag := '''A'',';
1573 l_insert_actual_flag := '';
1574 l_group_by_actual_flag := '';
1575 ELSE --g_entry_type = 'B'
1576 l_select_actual_flag := 'NVL(ael.actual_flag,''A''), aeh.budget_version_id,';
1577 l_insert_actual_flag := 'budget_version_id,';
1578 l_group_by_actual_flag := ',NVL(ael.actual_flag,''A''), aeh.budget_version_id';
1579 END IF;
1580
1581 l_from := ' FROM '|| g_headers_table ||' aeh, '
1582 || g_lines_table ||' ael, '
1583 || ' gl_je_categories jc ';
1584
1585
1586 l_where := ' WHERE ael.ae_header_id = aeh.ae_header_id
1587 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1588 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1589 AND jc.je_category_name = Decode(Nvl(aeh.cross_currency_flag,''N''),
1590 ''Y'',''Cross Currency'', aeh.ae_category) ';
1591
1592 IF g_line_type IS NOT NULL THEN
1593 l_where := l_where || 'AND ael.ae_line_type_code IN (' || g_line_type ||')';
1594 END IF;
1595
1596
1597 IF ( p_gl_transfer_mode = 'D' ) OR ( g_line_type IS NOT NULL ) THEN -- Detail Transfer
1598 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1599 xla_message('XLA_GLT_TRANSFER_MODE_D','','','','','','',
1600 l_log_module,
1601 C_LEVEL_STATEMENT);
1602 END IF;
1603
1604 statement_detail := 'INSERT INTO gl_interface(
1605 status, set_of_books_id,
1606 user_je_source_name, user_je_category_name,
1607 accounting_date, currency_code,
1608 date_created, created_by,
1609 actual_flag,
1610 '|| l_insert_actual_flag ||'
1611 code_combination_id, stat_amount,
1612 entered_dr, entered_cr,
1613 accounted_dr, accounted_cr,
1614 reference1, reference2,
1615 reference3, reference5,
1616 reference7, reference8,
1617 reference10,
1618 reference21, reference22,
1619 reference23, reference24,
1620 reference25, reference26,
1621 reference27, reference28,
1622 reference29, reference30,
1623 subledger_doc_sequence_id,
1624 subledger_doc_sequence_value,
1625 gl_sl_link_table,
1626 gl_sl_link_id, request_id,
1627 ussgl_transaction_code,
1628 je_header_id, group_id,
1629 period_name
1630 )
1631 SELECT /*+ ORDERED */
1632 ''NEW'', aeh.set_of_books_id,
1633 :b_source_name, jc.user_je_category_name,
1634 aeh.accounting_date, ael.currency_code,
1635 Sysdate, :b_user_id,
1636 '|| l_select_actual_flag ||'
1637 ael.code_combination_id, stat_amount,
1638 entered_dr, entered_cr,
1639 accounted_dr, accounted_cr,
1640 :b_batch_name , :b_batch_desc,
1641 NULL, :b_je_desc,
1642 aeh.gl_reversal_flag,
1643 Decode(Nvl(aeh.gl_reversal_flag,''N''),
1644 ''Y'',Decode(Nvl(:b_average_balances_flag,''N''),
1645 ''Y'',To_char(:b_reversal_date),:b_next_period),NULL),
1646 Nvl(ael.description, :b_je_line_desc),
1647 Nvl(ael.reference1,:b_transfer_run_id),
1648 ael.reference2,
1649 ael.reference3, ael.reference4,
1650 ael.reference5, ael.reference6,
1651 ael.reference7, ael.reference8,
1652 ael.reference9, ael.reference10,
1653 ael.subledger_doc_sequence_id,
1654 ael.subledger_doc_sequence_value,
1655 :b_link_table,
1656 ael.gl_sl_link_id, :b_request_id,
1657 ael.ussgl_transaction_code,
1658 :b_transfer_run_id, :b_group_id,
1659 aeh.period_name
1660 ' || l_from
1661 || l_where;
1662 END IF;
1663
1664
1665 cid := dbms_sql.open_cursor;
1666 dbms_sql.parse(cid, statement_detail, dbms_sql.native);
1667
1668 --Bind Variables
1669 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1670 dbms_sql.bind_variable(cid,':b_user_id', g_user_id);
1671 dbms_sql.bind_variable(cid,':b_group_id', p_group_id);
1672 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1673 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1674 dbms_sql.bind_variable(cid,':b_source_name', p_source_name);
1675 dbms_sql.bind_variable(cid,':b_batch_name', g_batch_name);
1676 dbms_sql.bind_variable(cid,':b_link_table', g_actual_table_alias);
1677 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1678 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1679 dbms_sql.bind_variable(cid,':b_batch_desc', p_batch_desc);
1680 dbms_sql.bind_variable(cid,':b_je_desc', p_je_desc);
1681 dbms_sql.bind_variable(cid,':b_je_line_desc', p_je_line_desc);
1682 dbms_sql.bind_variable(cid,':b_next_period', p_next_period);
1683 dbms_sql.bind_variable(cid,':b_reversal_date', p_reversal_date);
1684 dbms_sql.bind_variable(cid,':b_average_balances_flag', p_average_balances_flag);
1685
1686 rows_processed := dbms_sql.execute(cid);
1687
1688 IF rows_processed = 0 THEN
1689 g_proceed := 'N';
1690 ELSE
1691 g_sob_rows_created := g_sob_rows_created + rows_processed;
1692 g_rec_transfer_flag := 'Y'; --set the globle flag to 'Y' whenever there are records transferred.
1693 END IF;
1694 dbms_sql.close_cursor(cid);
1695
1696 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1697 xla_message('XLA_GLT_GL_INSERT','COUNT','(Detail) ' || rows_processed,'','','','',
1698 l_log_module,
1699 C_LEVEL_STATEMENT);
1700 END IF;
1701
1702 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1703 trace
1704 (p_msg => 'END of procedure GL_INSERT_DETAIL'
1705 ,p_level => C_LEVEL_PROCEDURE
1706 ,p_module => l_log_module);
1707
1708 END IF;
1709
1710 END gl_insert_detail;
1711
1712 -- This procedure stamps the gl_sl_linkid for all those accounting
1713 -- entries transferred in Summary. This routine is called after
1714 -- lines have been transferred to GL_INTERFACE.
1715
1716 PROCEDURE update_linkid_summary( p_request_id NUMBER,
1717 p_gl_transfer_mode VARCHAR2,
1718 p_transfer_run_id NUMBER,
1719 p_start_date DATE,
1720 p_end_date DATE
1721 ) IS
1722 statement VARCHAR2(2000) ;
1723 cid NUMBER;
1724 rows_processed NUMBER;
1725 l_and VARCHAR2(1000);
1726 l_budget_version VARCHAR2(100);
1727 l_log_module VARCHAR2(255);
1728 l_line_type_cond VARCHAR2(1000);
1729
1730 BEGIN
1731
1732 l_line_type_cond := '';
1733
1734 IF g_log_enabled THEN
1735 l_log_module := C_DEFAULT_MODULE||'.update_linkid_summary';
1736 END IF;
1737
1738 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1739 trace
1740 (p_msg => 'BEGIN of procedure update_linkid_summary'
1741 ,p_level => C_LEVEL_PROCEDURE
1742 ,p_module => l_log_module);
1743 END IF;
1744
1745
1746 -- Use accounting date in join condition if records are summarized
1747 -- by accounting date or use period name if records summarized by period
1748
1749 IF p_gl_transfer_mode = 'A' THEN
1750 l_and := ' AND aeh.accounting_date = gi.accounting_date ';
1751 ELSIF p_gl_transfer_mode = 'P' THEN
1752 l_and := ' AND aeh.period_name = gi.period_name ';
1753 END IF;
1754
1755 /*----------------------------------------------------------------
1756 1. 'A' is for actual -- only for AP and CST -- old source code
1757 2. 'B' is for budget and actual -- new requirement for PSB
1758 -----------------------------------------------------------------*/
1759 IF g_entry_type = 'A' THEN
1760 l_budget_version := '';
1761 ELSE --g_entry_type = 'B'
1762 l_budget_version := 'AND aeh.budget_version_id = gi.budget_version_id';
1763 END IF;
1764
1765 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1766 xla_message('XLA_GLT_UPDATE_SUM_LINKID','','','','','','',
1767 l_log_module,
1768 C_LEVEL_STATEMENT);
1769 END IF;
1770
1771 statement :=
1772 'UPDATE ' || g_lines_table || ' ael
1773 SET program_update_date = Sysdate,
1774 program_id = :b_program_id,
1775 request_id = :b_request_id,
1776 gl_sl_link_id =
1777 (
1778 SELECT gi.gl_sl_link_id
1779 FROM gl_interface gi, ' || g_headers_table || ' aeh
1780 WHERE gi.request_id = :b_request_id
1781 AND gi.je_header_id = :b_transfer_run_id
1782 AND aeh.gl_transfer_run_id = :b_transfer_run_id
1783 AND aeh.accounting_date BETWEEN :b_start_date AND :b_end_date
1784 AND Decode(Nvl(aeh.cross_currency_flag,''N''), ''Y'', ''Cross Currency'',
1785 aeh.ae_category) = gi.status
1786 AND Nvl(aeh.gl_reversal_flag,''N'') = nvl(gi.reference7,''N'')
1787 AND gi.gl_sl_link_table = :b_actual_table_alias '
1788 || l_and || '
1789 AND aeh.set_of_books_id = gi.set_of_books_id '
1790 || l_budget_version ||'
1791 AND ael.code_combination_id = gi.code_combination_id
1792 AND ael.currency_code = gi.currency_code
1793 AND aeh.ae_header_id = ael.ae_header_id
1794 AND Decode(Sign(gi.entered_dr), 1,''dr'', -1, ''dr'', 0,
1795 Decode(Sign(gi.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'') =
1796 Decode(Sign(ael.entered_dr), 1,''dr'', -1, ''dr'', 0,
1797 Decode(Sign(ael.entered_cr), 1,''cr'', -1, ''cr'',''dr''),''cr'')
1798 )
1799 WHERE ael.ae_header_id IN ( SELECT ae_header_id
1800 FROM ' || g_headers_table ||
1801 ' WHERE gl_transfer_run_id = :b_transfer_run_id
1802 AND accounting_date BETWEEN :b_start_date AND :b_end_date )';
1803
1804 IF g_line_type IS NOT NULL THEN
1805 statement := statement || ' AND ael.ae_line_type_code NOT IN ( ' || g_line_type || ' )';
1806 END IF;
1807
1808 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1809 trace
1810 (p_msg => 'statement = ' || statement
1811 ,p_level => C_LEVEL_STATEMENT
1812 ,p_module => l_log_module);
1813 END IF;
1814
1815
1816 cid := dbms_sql.open_cursor;
1817 dbms_sql.parse(cid, statement, dbms_sql.native);
1818
1819 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1820 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1821 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
1822 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1823 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1824 dbms_sql.bind_variable(cid,':b_actual_table_alias', g_actual_table_alias);
1825
1826 rows_processed := dbms_sql.execute(cid);
1827
1828 dbms_sql.close_cursor(cid);
1829
1830 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1831 trace
1832 (p_msg => 'END of procedure update_linkid_summary'
1833 ,p_level => C_LEVEL_PROCEDURE
1834 ,p_module => l_log_module);
1835 END IF;
1836
1837 END update_linkid_summary ;
1838
1839 -- This procedure stamps the gl_sl_linkid for all those accounting
1840 -- entries to be transferred in DETAIL. This routine is called before
1841 -- GL_INSERT_DETAIL is called.
1842
1843 PROCEDURE update_linkid_detail( p_transfer_run_id NUMBER,
1844 p_request_id NUMBER,
1845 p_start_date DATE,
1846 p_end_date DATE) IS
1847 statement VARCHAR2(2000) ;
1848 cid NUMBER;
1849 rows_processed NUMBER;
1850 l_log_module VARCHAR2(255);
1851
1852 BEGIN
1853
1854 IF g_log_enabled THEN
1855 l_log_module := C_DEFAULT_MODULE||'.update_linkid_detail';
1856 END IF;
1857
1858 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1859 trace
1860 (p_msg => 'BEGIN of procedure UPDATE_LINKID_DETAIL'
1861 ,p_level => C_LEVEL_PROCEDURE
1862 ,p_module => l_log_module);
1863 END IF;
1864
1865 IF g_proceed = 'N' THEN
1866 RETURN;
1867 END IF;
1868
1869 statement := 'UPDATE ' || g_lines_table || ' ael ' ||
1870 ' SET program_update_date = Sysdate,
1871 program_id = :b_program_id,
1872 request_id = :b_request_id,
1873 gl_sl_link_id = ' || g_lines_sequence_name || '.NEXTVAL
1874 WHERE ae_header_id in
1875 ( SELECT ae_header_id
1876 FROM ' || g_headers_table ||
1877 ' WHERE gl_transfer_run_id = :b_transfer_run_id
1878 AND accounting_date BETWEEN :b_start_date AND :b_end_date )' ;
1879
1880 IF g_line_type IS NOT NULL THEN
1881 statement := statement || ' AND ael.ae_line_type_code IN ( ' || g_line_type || ' )';
1882 END IF;
1883
1884 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1885 trace
1886 (p_msg => 'statement = ' || statement
1887 ,p_level => C_LEVEL_STATEMENT
1888 ,p_module => l_log_module);
1889 END IF;
1890
1891
1892 cid := dbms_sql.open_cursor;
1893 dbms_sql.parse(cid, statement, dbms_sql.native);
1894
1895 dbms_sql.bind_variable(cid,':b_transfer_run_id', p_transfer_run_id);
1896 dbms_sql.bind_variable(cid,':b_request_id', p_request_id);
1897 dbms_sql.bind_variable(cid,':b_program_id', g_program_id);
1898 dbms_sql.bind_variable(cid,':b_start_date', p_start_date);
1899 dbms_sql.bind_variable(cid,':b_end_date', p_end_date);
1900
1901 rows_processed := dbms_sql.execute(cid);
1902
1903 IF rows_processed = 0 THEN
1904 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1905 xla_message('XLA_GLT_NO_ACCT_LINES','','','','','','',
1906 l_log_module,
1907 C_LEVEL_STATEMENT);
1908 END IF;
1909
1910 g_proceed := 'N';
1911 END IF;
1912 dbms_sql.close_cursor(cid);
1913
1914 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1915 trace
1916 (p_msg => 'END of procedure UPDATE_LINKID_DETAIL'
1917 ,p_level => C_LEVEL_PROCEDURE
1918 ,p_module => l_log_module);
1919 END IF;
1920
1921 END update_linkid_detail;
1922
1923 -- Check input parameters
1924 PROCEDURE check_input_param(p_selection_type NUMBER,
1925 p_start_date DATE,
1926 p_end_date DATE,
1927 p_gl_transfer_mode VARCHAR2,
1928 p_source_doc_id NUMBER,
1929 p_source_document_table VARCHAR2) IS
1930 l_log_module VARCHAR2(255);
1931
1932 BEGIN
1933
1934 IF g_log_enabled THEN
1935 l_log_module := C_DEFAULT_MODULE||'.check_input_param';
1936 END IF;
1937
1938 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1939 trace
1940 (p_msg => 'BEGIN of procedure CHECK_INPUT_PARAM'
1941 ,p_level => C_LEVEL_PROCEDURE
1942 ,p_module => l_log_module);
1943 END IF;
1944
1945
1946 -- Check gl_transfer_mode
1947 IF (p_gl_transfer_mode IS NULL) OR (p_gl_transfer_mode NOT IN ('D','A','P')) THEN
1948 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1949 xla_message('XLA_GLT_INVALID_MODE', '','','','','','',
1950 l_log_module,
1951 C_LEVEL_EXCEPTION);
1952 END IF;
1953 APP_EXCEPTION.RAISE_EXCEPTION;
1954 END IF;
1955
1956 IF p_selection_type = 1 THEN
1957 -- Date validation
1958 IF p_start_date IS NOT NULL THEN
1959 IF p_start_date > p_end_date THEN
1960 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1961 xla_message('XLA_GLT_INVALID_DATE_RANGE','','','','','','',
1962 l_log_module,
1963 C_LEVEL_EXCEPTION);
1964 END IF;
1965 APP_EXCEPTION.RAISE_EXCEPTION;
1966 END IF;
1967 END IF;
1968 -- Check document parameter
1969 IF (p_source_doc_id IS NOT NULL) OR (p_source_document_table IS NOT NULL) THEN
1970 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1971 xla_message('','Source document Id and Source document table should be NULL for batch Transfer','','','','','',
1972 l_log_module,
1973 C_LEVEL_EXCEPTION);
1974 END IF;
1975 APP_EXCEPTION.RAISE_EXCEPTION;
1976 END IF;
1977 ELSIF p_selection_type = 2 THEN
1978 IF (p_source_doc_id IS NULL) OR (p_source_document_table IS NULL) THEN
1979 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1980 xla_message('','Source document Id and Source document table should be NULL for document Transfer','','','','','',
1981 l_log_module,
1982 C_LEVEL_EXCEPTION);
1983 END IF;
1984 APP_EXCEPTION.RAISE_EXCEPTION;
1985 END IF;
1986 ELSE
1987 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
1988 xla_message('XLA_GLT_INVALID_SELECTION_TYPE','','','','','','',
1989 l_log_module,
1990 C_LEVEL_EXCEPTION);
1991 END IF;
1992 APP_EXCEPTION.RAISE_EXCEPTION;
1993 END IF;
1994
1995 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1996 trace
1997 (p_msg => 'END of procedure CHECK_INPUT_PARAM'
1998 ,p_level => C_LEVEL_PROCEDURE
1999 ,p_module => l_log_module);
2000 END IF;
2001
2002 END check_input_param;
2003
2004
2005 /***********************************************************************************
2006 * FUNCTION
2007 * get_funds_check_flag
2008 *
2009 * DESCRIPTION
2010 * get_funds_check_flag will return TRUE if
2011 * 1.encumbrance accounting is being used
2012 * 2.Bugetary control is enabled for this set_of_books_id
2013 * 3.USSGL profile option is Yes --not available currently
2014 * SCOPE - PRIVATE
2015 *
2016 * ARGUMENTS:
2017 * IN: p_encumbrance_flag -- flag to check if encumbrance accounting
2018 * is being used
2019 * p_user_source_name -- it is used to get budget accounting flag
2020 * p_group_id -- it is used to get budget accounting flag
2021 * p_set_of_books_id -- it is used to get budget accounting flag
2022 *
2023 **********************************************************************************/
2024
2025 FUNCTION get_funds_check_flag(p_encumbrance_flag VARCHAR2,
2026 p_user_source_name VARCHAR2,
2027 p_group_id NUMBER,
2028 p_set_of_books_id NUMBER) RETURN BOOLEAN IS
2029 l_log_module VARCHAR2(255);
2030 l_budget_entries NUMBER;
2031 l_budget_control_flag VARCHAR2(1);
2032 BEGIN
2033
2034 IF g_log_enabled THEN
2035 l_log_module := C_DEFAULT_MODULE||'.get_funds_check_flag';
2036 END IF;
2037
2038 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2039 trace
2040 (p_msg => 'BEGIN of function GET_FUNDS_CHECK_FLAG'
2041 ,p_level => C_LEVEL_PROCEDURE
2042 ,p_module => l_log_module);
2043 END IF;
2044
2045 -- check if there are budget entries processed
2046 SELECT COUNT(*)
2047 INTO l_budget_entries
2048 FROM dual
2049 WHERE EXISTS ( SELECT 'x'
2050 FROM gl_interface
2051 WHERE user_je_source_name = p_user_source_name
2052 AND group_id = p_group_id
2053 AND set_of_books_id = p_set_of_books_id );
2054
2055
2056 -- check if budget control is enabled
2057 SELECT enable_budgetary_control_flag
2058 INTO l_budget_control_flag
2059 FROM gl_sets_of_books
2060 WHERE set_of_books_id = p_set_of_books_id;
2061
2062
2063 IF ( Nvl(p_encumbrance_flag,'N') = 'Y' AND g_enc_proceed = 'Y' ) OR
2064 ( l_budget_control_flag = 'Y' AND l_budget_entries > 0 ) THEN
2065 RETURN (TRUE);
2066 ELSE
2067 RETURN (FALSE);
2068 END IF;
2069
2070 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2071 trace
2072 (p_msg => 'END of function GET_FUNDS_CHECK_FLAG'
2073 ,p_level => C_LEVEL_PROCEDURE
2074 ,p_module => l_log_module);
2075 END IF;
2076
2077 EXCEPTION
2078 WHEN OTHERS THEN
2079 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
2080 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
2081 l_log_module,
2082 'Unexpected Error While Executing ' || l_log_module);
2083 END IF;
2084 END get_funds_check_flag;
2085
2086 -- This procedure is used to derive line_type_code that need to be transferred in DETAIL.
2087
2088 -- Also sets the flag whether a detail transfer is required.
2089
2090 PROCEDURE derive_line_types IS
2091 l_log_module VARCHAR2(255);
2092 BEGIN
2093
2094 IF g_log_enabled THEN
2095 l_log_module := C_DEFAULT_MODULE||'.derive_line_types';
2096 END IF;
2097
2098 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2099 trace
2100 (p_msg => 'BEGIN of procedure derive_line_types'
2101 ,p_level => C_LEVEL_PROCEDURE
2102 ,p_module => l_log_module);
2103 END IF;
2104
2105 FOR select_line_type_rec IN ( SELECT Line_Type_Code
2106 FROM xla_je_line_types
2107 WHERE application_id = g_application_id
2108 AND summary_flag = 'D' )
2109 LOOP
2110 IF g_line_type IS NULL THEN
2111 g_line_type := '''' || select_line_type_rec.Line_Type_Code || ''',';
2112 ELSE
2113 g_line_type := g_line_type || '''' || select_line_type_rec.Line_Type_Code || ''',';
2114 END IF;
2115 END LOOP;
2116
2117 g_line_type := RTRIM(g_line_type,',');
2118
2119 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2120 xla_message('','Line types to be transferred in detail: ' || g_line_type,'','','','','',l_log_module,C_LEVEL_STATEMENT );
2121 END IF;
2122
2123 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2124 trace
2125 (p_msg => 'END of procedure derive_line_types'
2126 ,p_level => C_LEVEL_PROCEDURE
2127 ,p_module => l_log_module);
2128 END IF;
2129
2130 EXCEPTION
2131 WHEN OTHERS THEN
2132 IF g_log_level <= FND_LOG.LEVEL_UNEXPECTED THEN
2133 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
2134 l_log_module,
2135 'Unexpected Error While Executing ' || l_log_module);
2136 END IF;
2137 END derive_line_types;
2138
2139
2140 /*===========================================================================+
2141 | PROCEDURE |
2142 | XLA_GL_TRANSFER |
2143 | |
2144 | DESCRIPTION |
2145 | Main procedure for the transfer. All the sub procedures are called from |
2146 | from this procedure. |
2147 | |
2148 | SCOPE - PUBLIC |
2149 | |
2150 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2151 | |
2152 | ARGUMENTS |
2153 | p_application_id Application ID of the calling application. |
2154 | p_program_name Unique program name for the calling application. |
2155 | p_selection_type Transfer Type 1-Batch , 2- Doc. Level Transfer |
2156 | p_fc_force_flag Force flag for the funds checker. |
2157 | |
2158 | NOTES |
2159 | |
2160 +===========================================================================*/
2161
2162 PROCEDURE xla_gl_transfer(p_application_id NUMBER,
2163 p_user_id NUMBER,
2164 p_org_id NUMBER,
2165 p_request_id NUMBER,
2166 p_program_name VARCHAR2,
2167 p_selection_type NUMBER DEFAULT 1,
2168 p_sob_list t_sob_list,
2169 p_batch_name VARCHAR2,
2170 p_source_doc_id NUMBER DEFAULT NULL,
2171 p_source_document_table VARCHAR2 DEFAULT NULL,
2172 p_start_date DATE,
2173 p_end_date DATE,
2174 p_journal_category t_ae_category,
2175 p_validate_account VARCHAR2,
2176 p_gl_transfer_mode VARCHAR2,
2177 p_submit_journal_import VARCHAR2,
2178 p_summary_journal_entry VARCHAR2,
2179 p_process_days NUMBER ,
2180 p_batch_desc VARCHAR2 DEFAULT NULL,
2181 p_je_desc VARCHAR2 DEFAULT NULL,
2182 p_je_line_desc VARCHAR2 DEFAULT NULL,
2183 p_fc_force_flag BOOLEAN DEFAULT TRUE,
2184 p_debug_flag VARCHAR2
2185 ) IS
2186 l_start_date DATE;
2187 l_end_date DATE;
2188 l_period_start_date DATE;
2189 l_period_end_date DATE;
2190 l_open_start_date DATE;
2191 l_open_end_date DATE;
2192 l_min_start_date DATE;
2193 l_max_end_date DATE;
2194 l_next_period gl_period_statuses.period_name%TYPE;
2195 l_reversal_date DATE; -- Bug #974204
2196 l_application_id NUMBER(15);
2197 l_period_status VARCHAR2(1);
2198 l_period_name gl_period_statuses.period_name%TYPE;
2199 l_transfer_run_id NUMBER;
2200 l_set_of_books_id NUMBER;
2201 l_batch_run_id NUMBER;
2202 l_gl_installed_flag VARCHAR2(10);
2203 l_group_id NUMBER;
2204 l_interface_run_id NUMBER;
2205 l_encumbrance_flag VARCHAR2(1);
2206 l_source_name gl_je_sources.je_source_name%TYPE;
2207 l_user_source_name gl_je_sources.user_je_source_name%TYPE;
2208 industry VARCHAR2(10);
2209 l_debug_info VARCHAR2(2000);
2210 l_submittedreqid NUMBER;
2211 l_packet_id NUMBER;
2212 l_request_id NUMBER;
2213 l_sob_name gl_sets_of_books.name%TYPE;
2214 l_sob_type gl_sets_of_books.mrc_sob_type_code%TYPE;
2215 l_coa_id NUMBER;
2216 l_acct_validation_flag VARCHAR2(1);
2217 l_pre_commit_api xla_gl_transfer_programs.pre_commit_api_name%TYPE;
2218 l_budget_entries NUMBER;
2219 l_fc_force_flag VARCHAR2(10);
2220
2221 l_log_module VARCHAR2(255);
2222
2223
2224 -- Get Period Information
2225 -- Added the entry type to check if the entry is an actual/budget entry
2226 CURSOR c_getPeriods(c_sob_id NUMBER,
2227 c_start_date DATE,
2228 c_end_date DATE) IS
2229 SELECT gp1.period_name, gp1.start_date, gp1.end_date,
2230 gp2.period_name, gp2.start_date
2231 FROM gl_period_statuses gp1,
2232 gl_period_statuses gp2
2233 WHERE gp1.application_id = 101
2234 AND gp1.set_of_books_id = c_sob_id
2235 AND gp1.end_date >= Nvl(c_start_date,gp1.end_date-1)
2236 AND gp1.start_date <= c_end_date
2237 AND gp1.closing_status = DECODE( g_entry_type,'A', DECODE( gp1.closing_status, 'O', 'O', 'F', 'F','Z'),
2238 'B', gp1.closing_status )
2239 AND nvl(gp1.adjustment_period_flag,'N') = 'N'
2240 AND gp2.application_id(+) = 101
2241 AND gp2.set_of_books_id(+) = c_sob_id
2242 AND gp2.start_date(+) = gp1.end_date+1
2243 AND nvl(gp2.adjustment_period_flag,'N') = 'N'
2244 ORDER BY gp1.start_date;
2245 BEGIN
2246
2247 g_proceed := 'Y';
2248 g_rec_transfer_flag := 'N';
2249 g_enc_proceed := 'N';
2250
2251 IF g_log_enabled THEN
2252 l_log_module := C_DEFAULT_MODULE||'.xla_gl_transfer';
2253 END IF;
2254
2255 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2256 trace
2257 (p_msg => 'BEGIN of procedure XLA_GL_TRANSFER'
2258 ,p_level => C_LEVEL_PROCEDURE
2259 ,p_module => l_log_module);
2260
2261 xla_message('' , 'p_application_id = ' || p_application_id,'','','','','',
2262 l_log_module,
2263 C_LEVEL_PROCEDURE);
2264
2265 xla_message('' , 'p_user_id = ' || p_user_id,'','','','','',
2266 l_log_module,
2267 C_LEVEL_PROCEDURE);
2268
2269 xla_message('' , 'p_org_id = ' || p_org_id,'','','','','',
2270 l_log_module,
2271 C_LEVEL_PROCEDURE);
2272
2273 xla_message('' , 'p_request_id = ' || p_request_id,'','','','','',
2274 l_log_module,
2275 C_LEVEL_PROCEDURE);
2276
2277 xla_message('' , 'p_program_name = ' || p_program_name,'','','','','',
2278 l_log_module,
2279 C_LEVEL_PROCEDURE);
2280
2281 xla_message('' , 'p_selection_type = ' || p_selection_type,'','','','','',
2282 l_log_module,
2283 C_LEVEL_PROCEDURE);
2284
2285 xla_message('' , 'p_batch_name = ' || p_batch_name,'','','','','',
2286 l_log_module,
2287 C_LEVEL_PROCEDURE);
2288
2289 xla_message('' , 'p_source_doc_id = ' || p_source_doc_id,'','','','','',
2290 l_log_module,
2291 C_LEVEL_PROCEDURE);
2292
2293 xla_message('' , 'p_source_document_table = ' || p_source_document_table,'','','','','',
2294 l_log_module,
2295 C_LEVEL_PROCEDURE);
2296
2297 xla_message('' , 'p_start_date = ' || To_char(p_start_date,'MM/DD/YYYY'),'','','','','',
2298 l_log_module,
2299 C_LEVEL_PROCEDURE);
2300
2301 xla_message('' , 'p_end_date = ' || To_char(p_end_date,'MM/DD/YYYY'),'','','','','',
2302 l_log_module,
2303 C_LEVEL_PROCEDURE);
2304
2305 xla_message('' , 'p_validate_account = ' || p_validate_account,'','','','','',
2306 l_log_module,
2307 C_LEVEL_PROCEDURE);
2308
2309 xla_message('' , 'p_gl_transfer_mode = ' || p_gl_transfer_mode,'','','','','',
2310 l_log_module,
2311 C_LEVEL_PROCEDURE);
2312
2313 xla_message('' , 'p_submit_journal_import = ' || NVL(p_submit_journal_import,'Y'),'','','','','',
2314 l_log_module,
2315 C_LEVEL_PROCEDURE);
2316
2317 xla_message('' , 'p_summary_journal_entry = ' || NVL(p_summary_journal_entry,'N'),'','','','','',
2318 l_log_module,
2319 C_LEVEL_PROCEDURE);
2320
2321 xla_message('' , 'p_process_days = ' || p_process_days,'','','','','',
2322 l_log_module,
2323 C_LEVEL_PROCEDURE);
2324
2325 xla_message('' , 'p_batch_desc = ' || p_batch_desc,'','','','','',
2326 l_log_module,
2327 C_LEVEL_PROCEDURE);
2328
2329 xla_message('' , 'p_je_desc = ' || p_je_desc,'','','','','',
2330 l_log_module,
2331 C_LEVEL_PROCEDURE);
2332
2333 xla_message('' , 'p_je_line_desc = ' || p_je_line_desc,'','','','','',
2334 l_log_module,
2335 C_LEVEL_PROCEDURE);
2336
2337 xla_message('' , 'p_fc_force_flag = ' || l_fc_force_flag,'','','','','',
2338 l_log_module,
2339 C_LEVEL_PROCEDURE);
2340
2341 xla_message('' , 'p_debug_flag = ' || p_debug_flag,'','','','','',
2342 l_log_module,
2343 C_LEVEL_PROCEDURE);
2344
2345 END IF;
2346
2347 IF p_fc_force_flag THEN
2348 l_fc_force_flag := 'TRUE';
2349 ELSE
2350 l_fc_force_flag := 'FALSE';
2351 END IF;
2352 -- Initialize Variables
2353
2354 g_application_id := p_application_id;
2355 g_program_id := fnd_global.conc_program_id;
2356 g_user_id := p_user_id;
2357 g_program_name := p_program_name;
2358
2359 -- Check input parameters
2360 check_input_param(p_selection_type,
2361 p_start_date,
2362 p_end_date,
2363 p_gl_transfer_mode,
2364 p_source_doc_id,
2365 p_source_document_table
2366 );
2367
2368 -- Get the user source name
2369
2370 SELECT je_source_name, account_validation_flag, period_status_table_name,
2371 pre_commit_api_name, application_id, NVL(entry_type,'A')
2372 INTO l_source_name, l_acct_validation_flag, g_periods_table,
2373 l_pre_commit_api, l_application_id, g_entry_type
2374 FROM xla_gl_transfer_programs
2375 WHERE program_name = p_program_name;
2376
2377 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2378 xla_message('' , 'SOB list count = ' || p_sob_list.count,'','','','','',l_log_module,
2379 C_LEVEL_STATEMENT);
2380 END IF;
2381 FOR i IN p_sob_list.first..p_sob_list.last LOOP
2382 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2383 xla_message('' , 'SOB(' || i || ').sob_id = ' || p_sob_list(i).sob_id,'','','','','',
2384 l_log_module,
2385 C_LEVEL_STATEMENT);
2386
2387 xla_message('' , 'SOB(' || i || ').sob_name = ' || p_sob_list(i).sob_name,'','','','','',
2388 l_log_module,
2389 C_LEVEL_STATEMENT);
2390
2391 xla_message('' , 'SOB(' || i || ').sob_curr_code = ' || p_sob_list(i).sob_curr_code,'','','','','',
2392 l_log_module,
2393 C_LEVEL_STATEMENT);
2394
2395 xla_message('' , 'SOB(' || i || ').ave_bal_flag = ' || p_sob_list(i).average_balances_flag,'','','','','',
2396 l_log_module,
2397 C_LEVEL_STATEMENT);
2398
2399 xla_message('' , 'SOB(' || i || ').legal_entity_id = ' || p_sob_list(i).legal_entity_id,'','','','','',
2400 l_log_module,
2401 C_LEVEL_STATEMENT);
2402
2403 xla_message('' , 'SOB(' || i || ').cost_group_id = ' || p_sob_list(i).cost_group_id,'','','','','',
2404 l_log_module,
2405 C_LEVEL_STATEMENT);
2406
2407 xla_message('' , 'SOB(' || i || ').cost_type_id = ' || p_sob_list(i).cost_type_id,'','','','','',
2408 l_log_module,
2409 C_LEVEL_STATEMENT);
2410
2411 END IF;
2412
2413 END LOOP;
2414
2415 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2416 xla_message('' , 'p_journal_category count = ' || p_journal_category.count,'','','','','',
2417 l_log_module,
2418 C_LEVEL_STATEMENT);
2419
2420 END IF;
2421 FOR i IN p_journal_category.FIRST..p_journal_category.LAST LOOP
2422 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2423 xla_message('' , 'journal_category(' || i || ') = ' || p_journal_category(i),'','','','','',
2424 l_log_module,
2425 C_LEVEL_STATEMENT);
2426 END IF;
2427 END LOOP;
2428 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2429 xla_message('' , '------------------------------------------','','','','','',
2430 l_log_module,
2431 C_LEVEL_STATEMENT);
2432 END IF;
2433
2434 -- Legal Entity, Cost Group, Cost Type is one is not null then
2435 -- all three must be not null.
2436
2437 FOR i IN p_sob_list.first..p_sob_list.last LOOP
2438 IF p_sob_list(i).legal_entity_id IS NOT NULL THEN
2439 IF p_sob_list(i).cost_group_id IS NULL OR p_sob_list(i).cost_type_id IS NULL THEN
2440 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2441 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2442 l_log_module,
2443 C_LEVEL_EXCEPTION);
2444 END IF;
2445 APP_EXCEPTION.RAISE_EXCEPTION;
2446 END IF;
2447 ELSIF p_sob_list(i).cost_group_id IS NOT NULL THEN
2448 IF p_sob_list(i).legal_entity_id IS NULL OR p_sob_list(i).cost_type_id IS NULL THEN
2449 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2450 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2451 l_log_module,
2452 C_LEVEL_EXCEPTION);
2453 END IF;
2454 APP_EXCEPTION.RAISE_EXCEPTION;
2455 END IF;
2456 ELSIF p_sob_list(i).cost_type_id IS NOT NULL THEN
2457 IF p_sob_list(i).legal_entity_id IS NULL OR p_sob_list(i).cost_group_id IS NULL THEN
2458 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2459 xla_message('XLA_GLT_INVALID_CST_DATA','','','','','','',
2460 l_log_module,
2461 C_LEVEL_EXCEPTION);
2462 END IF;
2463 APP_EXCEPTION.RAISE_EXCEPTION;
2464 END IF;
2465 END IF;
2466 END LOOP;
2467
2468 -- Check if GL is installed.
2469 IF (FND_INSTALLATION.GET(101, 101, l_gl_installed_flag, industry)) THEN
2470 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2471 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2472
2473 xla_message('XLA_GLT_GL_INSTALLED','','','','',
2474 '','', l_log_module,
2475 C_LEVEL_STATEMENT);
2476
2477 END IF;
2478 ELSE
2479 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2480
2481 xla_message('XLA_GLT_GL_NOT_INSTALLED','','','',
2482 '','','', l_log_module,
2483 C_LEVEL_STATEMENT);
2484
2485 END IF;
2486 END IF;
2487 END IF;
2488
2489 -- Get the user source name for an application.
2490 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2491 xla_message('XLA_GLT_GET_SOURCE_NAME','','','',
2492 '','','', l_log_module,
2493 C_LEVEL_STATEMENT);
2494 END IF;
2495
2496 SELECT user_je_source_name
2497 INTO l_user_source_name
2498 FROM gl_je_sources js
2499 WHERE je_source_name = l_source_name;
2500
2501 -- Validate periods if GL is installed.
2502 -- Bug2543724. Skipping Accounting Period validation for Budget journals
2503 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2504 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2505 xla_message('XLA_GLT_VALIDATE_PERIODS','','','','','',
2506 '', l_log_module,
2507 C_LEVEL_STATEMENT);
2508 END IF;
2509
2510 IF p_selection_type = 1 THEN -- this is only for SRS, Doc Transfer will call it later.
2511 IF(g_entry_type = 'A') THEN
2512 validate_periods(p_selection_type,
2513 p_sob_list,
2514 p_program_name,
2515 p_start_date,
2516 p_end_date
2517 );
2518
2519 END IF;
2520
2521 END IF;
2522 END IF;
2523
2524 -- If the transfer is submitted for more than one sobs then we will
2525 -- process one SOB at a time.
2526
2527 -- Loop to process each set of books.
2528 FOR i IN p_sob_list.FIRST..p_sob_list.LAST LOOP
2529 l_set_of_books_id := p_sob_list(i).sob_id;
2530 l_sob_name := p_sob_list(i).sob_name;
2531 g_base_currency_code := p_sob_list(i).sob_curr_code;
2532 l_encumbrance_flag := p_sob_list(i).encum_flag;
2533
2534 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2535 xla_message('XLA_GLT_PROCESS_SOB','SOB_NAME', l_sob_name,'','','','', l_log_module,
2536 C_LEVEL_STATEMENT);
2537 END IF;
2538
2539 IF l_set_of_books_id IS NOT NULL THEN
2540 SELECT chart_of_accounts_id
2541 INTO l_coa_id
2542 FROM gl_sets_of_books
2543 WHERE set_of_books_id = l_set_of_books_id;
2544 END IF;
2545
2546 -- Get Transfer Run Id
2547 SELECT xla_gl_transfer_runid_s.NEXTVAL
2548 INTO l_transfer_run_id
2549 FROM dual;
2550
2551 -- Set the batch Name
2552
2553 --Bug3196153. p_batch_name exceeds the limit of varchar2(30)
2554 --during translation in some languages.
2555 g_batch_name := SUBSTRB(p_batch_name || ' ' || l_transfer_run_id,1,30);
2556
2557 -- If GL is installed populate group id and inter_run_id;
2558 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
2559 SELECT gl_interface_control_s.NEXTVAL, gl_journal_import_s.NEXTVAL
2560 INTO l_group_id, l_interface_run_id
2561 FROM dual;
2562 END IF;
2563
2564 ---------------------------------------------------------------------------
2565 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2566
2567 xla_message('' , 'Batch_Name = ' || g_batch_name,'','','','','',
2568 l_log_module,
2569 C_LEVEL_STATEMENT);
2570
2571 xla_message('' , 'Transfer_run_id = ' || l_transfer_run_id,'','','','','',
2572 l_log_module,
2573 C_LEVEL_STATEMENT);
2574
2575 xla_message('' , 'Group_id = ' || l_group_id,'','','','','',
2576 l_log_module,
2577 C_LEVEL_STATEMENT);
2578
2579 xla_message('' , 'Interface_run_id = ' || l_interface_run_id,'','','','','',
2580 l_log_module,
2581 C_LEVEL_STATEMENT);
2582
2583 END IF;
2584
2585 ---------------------------------------------------------------------------
2586
2587 ---------------------------------------------------------------------
2588 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2589
2590 xla_message('XLA_GLT_INSERT_XTB','','','','','','',
2591 l_log_module,
2592 C_LEVEL_STATEMENT);
2593
2594 END IF;
2595 ---------------------------------------------------------------------
2596 INSERT INTO xla_gl_transfer_batches_all
2597 ( gl_transfer_run_id,
2598 request_id ,
2599 application_id ,
2600 user_id ,
2601 selection_type ,
2602 set_of_books_id ,
2603 batch_name,
2604 source_id ,
2605 source_table ,
2606 transfer_from_date,
2607 transfer_to_date,
2608 ae_category ,
2609 gl_transfer_mode ,
2610 submit_journal_import ,
2611 summary_journal_entry ,
2612 process_days ,
2613 gl_transfer_date,
2614 group_id,
2615 interface_run_id,
2616 org_id,
2617 legal_entity_id,
2618 cost_group_id,
2619 cost_type_id,
2620 transfer_status
2621 )
2622 VALUES
2623 ( l_transfer_run_id,
2624 p_request_id,
2625 p_application_id,
2626 p_user_id,
2627 p_selection_type ,
2628 p_sob_list(i).sob_id ,
2629 g_batch_name ,
2630 p_source_doc_id ,
2631 p_source_document_table ,
2632 p_start_date ,
2633 p_end_date ,
2634 p_journal_category(1),
2635 p_gl_transfer_mode ,
2636 NVL(p_submit_journal_import,'Y') ,
2637 NVL(p_summary_journal_entry,'N') ,
2638 p_process_days ,
2639 Sysdate,
2640 l_group_id,
2641 l_interface_run_id,
2642 p_org_id,
2643 p_sob_list(i).legal_entity_id,
2644 p_sob_list(i).cost_group_id,
2645 p_sob_list(i).cost_type_id,
2646 'P'
2647 );
2648
2649 g_rec_transfer_flag := 'N'; --reset the global flag for each sob
2650
2651 IF p_selection_type = 1 THEN
2652 -- If processing more than one period then break the date range into
2653 -- multiple peirods.
2654 OPEN c_getPeriods(p_sob_list(i).sob_id,
2655 p_start_date,
2656 p_end_date
2657 );
2658 LOOP -- Proecss Periods
2659 FETCH c_getPeriods
2660 INTO l_period_name, l_period_start_date,l_period_end_date,
2661 l_next_period, l_reversal_date;
2662 EXIT WHEN c_getPeriods%NOTFOUND;
2663
2664 -- Bug-4014659 deleted the if loop which checks for the NULL starting date
2665 -- because the loop makes the starting date of the latest open period as the
2666 -- start date of the GL transfer for reporting SOB which gives some inconsistency
2667 -- while posting.
2668 l_start_date := Nvl(p_start_date, l_period_start_date);
2669
2670 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2671 xla_message('XLA_GLT_GET_PERIOD_INFO','','','','','','', l_log_module,
2672 C_LEVEL_STATEMENT);
2673 END IF;
2674
2675
2676 g_periods_cnt := g_periods_cnt + 1;
2677 g_control_info(g_periods_cnt).sob_id := l_set_of_books_id;
2678 g_control_info(g_periods_cnt).period_name := l_period_name;
2679 g_control_info(g_periods_cnt).rec_transferred := 0;
2680 g_control_info(g_periods_cnt).cnt_transfer_errors := 0;
2681 g_control_info(g_periods_cnt).cnt_acct_errors := 0;
2682
2683 <<process_commit_cycle>>
2684 LOOP
2685 -- Set the date range. Ignore process days specified by the user
2686 -- when summarized by period or encumbrance is used.
2687
2688 IF (NVL(p_process_days,0) = 0 OR
2689 Nvl(l_encumbrance_flag,'N') = 'Y' OR
2690 p_gl_transfer_mode = 'P') THEN
2691 -- If period end date > transfer end date then set
2692 -- the end date to transfer end date.
2693 l_end_date := Least(p_end_date, l_period_end_date);
2694 ELSE
2695 l_end_date := Least(l_start_date+(p_process_days-1),
2696 Least(l_period_end_date,p_end_date));
2697 END IF;
2698
2699 --temporarily fixed for the bug2139573 (add 23:59:59)
2700 l_end_date := trunc(l_end_date) + 86399/86400;
2701
2702 -- Reset proceed to 'Y' for each process_commit_cycle
2703 g_proceed := 'Y';
2704
2705 OPEN c_get_program_info(p_program_name);
2706 <<multiple_entities>>
2707 LOOP -- to process multiple accounting entities
2708 FETCH c_get_program_info
2709 INTO g_events_table, g_headers_table, g_lines_table,
2710 g_encumbrance_table, g_lines_sequence_name,
2711 g_enc_sequence_name, g_actual_table_alias, g_enc_table_alias;
2712 EXIT WHEN c_get_program_info%NOTFOUND;
2713
2714 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2715 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_start_date,
2716 'END_DATE',l_end_date,'','',l_log_module,
2717 C_LEVEL_STATEMENT);
2718 END IF;
2719
2720
2721 select_acct_headers( p_selection_type,
2722 l_set_of_books_id,
2723 p_source_doc_id,
2724 p_source_document_table,
2725 l_transfer_run_id,
2726 p_request_id,
2727 p_journal_category,
2728 l_start_date,
2729 l_end_date,
2730 p_sob_list(i).legal_entity_id,
2731 p_sob_list(i).cost_group_id,
2732 p_sob_list(i).cost_type_id,
2733 p_validate_account);
2734 -- Validate account on accounting entry lines if necessary
2735 IF Nvl(p_validate_account, Nvl(l_acct_validation_flag,'N')) = 'Y'
2736 AND g_proceed = 'Y' THEN
2737 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2738 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
2739 '',l_log_module,
2740 C_LEVEL_STATEMENT);
2741 END IF;
2742
2743 validate_acct_lines( p_selection_type,
2744 l_set_of_books_id,
2745 l_coa_id,
2746 l_transfer_run_id,
2747 l_start_date,
2748 l_end_date);
2749
2750 -- Update headers for the lines that failed accounting
2751 -- validation. Do not call routine if there are no
2752 -- accounting entry lines to process.
2753 IF g_proceed = 'Y' THEN
2754 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2755 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
2756 '',l_log_module,
2757 C_LEVEL_STATEMENT);
2758 END IF;
2759 validate_acct_headers( p_selection_type,
2760 l_set_of_books_id,
2761 l_transfer_run_id,
2762 l_start_date,
2763 l_end_date);
2764 END IF;
2765 END IF;
2766 -- Call following procedures only if there are records to
2767 -- process in this period
2768 IF g_proceed = 'Y' THEN
2769 IF p_gl_transfer_mode = 'D' THEN
2770 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2771 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2772 '',l_log_module,
2773 C_LEVEL_STATEMENT);
2774 END IF;
2775
2776 update_linkid_detail( l_transfer_run_id,
2777 p_request_id,
2778 l_start_date,
2779 l_end_date);
2780
2781 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2782 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2783 l_log_module,
2784 C_LEVEL_STATEMENT);
2785 END IF;
2786
2787 gl_insert_detail( p_request_id,
2788 l_user_source_name,
2789 l_transfer_run_id,
2790 l_period_name,
2791 l_start_date,
2792 l_end_date,
2793 l_next_period,
2794 l_reversal_date,
2795 p_sob_list(i).average_balances_flag,
2796 p_gl_transfer_mode,
2797 l_group_id,
2798 p_batch_desc,
2799 p_je_desc,
2800 p_je_line_desc);
2801
2802 ELSE -- Summarize By Accounting Date
2803
2804 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2805 xla_message('','Calling derive line types ','','','','','',l_log_module,C_LEVEL_STATEMENT );
2806 END IF;
2807
2808 IF g_line_type IS NULL THEN
2809 derive_line_types;
2810 END IF;
2811
2812 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2813 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2814 l_log_module,
2815 C_LEVEL_STATEMENT);
2816 END IF;
2817
2818 gl_insert_summary( p_request_id,
2819 l_user_source_name,
2820 l_transfer_run_id,
2821 l_period_name,
2822 l_start_date,
2823 l_end_date,
2824 l_next_period,
2825 l_reversal_date,
2826 p_sob_list(i).average_balances_flag,
2827 p_gl_transfer_mode,
2828 l_group_id,
2829 p_batch_desc,
2830 p_je_desc,
2831 p_je_line_desc);
2832
2833 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2834 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2835 '',l_log_module,
2836 C_LEVEL_STATEMENT);
2837 END IF;
2838
2839 update_linkid_summary( p_request_id,
2840 p_gl_transfer_mode,
2841 l_transfer_run_id,
2842 l_start_date,
2843 l_end_date);
2844
2845 IF g_line_type IS NOT NULL THEN
2846
2847 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2848 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
2849 '',l_log_module,
2850 C_LEVEL_STATEMENT);
2851 END IF;
2852
2853 update_linkid_detail( l_transfer_run_id,
2854 p_request_id,
2855 l_start_date,
2856 l_end_date);
2857
2858 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2859 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
2860 l_log_module,
2861 C_LEVEL_STATEMENT);
2862 END IF;
2863
2864 gl_insert_detail( p_request_id,
2865 l_user_source_name,
2866 l_transfer_run_id,
2867 l_period_name,
2868 l_start_date,
2869 l_end_date,
2870 l_next_period,
2871 l_reversal_date,
2872 p_sob_list(i).average_balances_flag,
2873 p_gl_transfer_mode,
2874 l_group_id,
2875 p_batch_desc,
2876 p_je_desc,
2877 p_je_line_desc);
2878
2879 END IF;
2880
2881 END IF;
2882
2883 -- Transfer encumbrance reversals to gl_interface if
2884 -- encumbrance is used.
2885 IF (Nvl(l_encumbrance_flag,'N') = 'Y') THEN
2886 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2887 xla_message('XLA_GLT_CALL_ENCUM_ROUTINE','','','','','',
2888 '',l_log_module,
2889 C_LEVEL_STATEMENT);
2890 END IF;
2891
2892 transfer_enc_lines( p_application_id,
2893 l_set_of_books_id,
2894 l_transfer_run_id,
2895 l_start_date,
2896 l_end_date,
2897 l_next_period,
2898 l_reversal_date,
2899 p_sob_list(i).average_balances_flag,
2900 l_user_source_name,
2901 l_group_id,
2902 l_request_id,
2903 p_batch_desc,
2904 p_je_desc,
2905 p_je_line_desc);
2906 END IF;
2907 -- Call Globalization Routine
2908
2909 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2910 xla_message('XLA_GLT_CALLING_JG_PKG','','','','','','',l_log_module,
2911 C_LEVEL_STATEMENT);
2912 END IF;
2913
2914
2915 JG_XLA_GL_TRANSFER_PKG.jg_xla_gl_transfer
2916 ( p_application_id,
2917 p_user_id,
2918 p_org_id,
2919 p_request_id,
2920 l_transfer_run_id,
2921 p_program_name,
2922 p_selection_type,
2923 p_batch_name,
2924 l_start_date,
2925 l_end_date,
2926 p_gl_transfer_mode,
2927 p_process_days,
2928 p_debug_flag
2929 );
2930
2931 END IF;
2932 END LOOP multiple_entities; -- Multiple entities loop
2933 CLOSE c_get_program_info;
2934
2935 IF p_process_days IS NOT NULL THEN
2936 -- Save changes if commit cycle is needed
2937 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2938 xla_message('XLA_GLT_SAVE_WORK','','','','','','',l_log_module,
2939 C_LEVEL_STATEMENT);
2940 END IF;
2941 COMMIT;
2942 END IF;
2943
2944 IF ( p_selection_type = 1 AND
2945 l_end_date < Least(p_end_date,l_period_end_date)) THEN
2946 l_start_date := l_end_date+1;
2947 ELSE
2948 EXIT;
2949 END IF;
2950 END LOOP process_commit_cycle ;
2951 END LOOP; --process_periods
2952
2953 -- Log an error if there are no open periods
2954 IF c_getPeriods%ROWCOUNT = 0 THEN
2955 CLOSE c_getPeriods;
2956 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
2957
2958 xla_message('XLA_GLT_NO_OPEN_PERIODS','SOB_NAME',l_sob_name,
2959 '','','','', l_log_module,C_LEVEL_EXCEPTION);
2960 END IF;
2961 APP_EXCEPTION.RAISE_EXCEPTION;
2962 END IF;
2963 CLOSE c_getPeriods;
2964 ELSE
2965 -- Document Specific Transfer
2966 OPEN c_get_program_info(p_program_name);
2967 LOOP
2968 FETCH c_get_program_info
2969 INTO g_events_table, g_headers_table, g_lines_table,
2970 g_encumbrance_table, g_lines_sequence_name,
2971 g_enc_sequence_name, g_actual_table_alias, g_enc_table_alias;
2972 EXIT WHEN c_get_program_info%NOTFOUND;
2973
2974 -- Select Accounting Entries.
2975 select_acct_headers( p_selection_type,
2976 l_set_of_books_id,
2977 p_source_doc_id,
2978 p_source_document_table,
2979 l_transfer_run_id,
2980 p_request_id,
2981 p_journal_category,
2982 l_start_date,
2983 l_end_date,
2984 p_sob_list(i).legal_entity_id,
2985 p_sob_list(i).cost_group_id,
2986 p_sob_list(i).cost_type_id,
2987 p_validate_account
2988 );
2989 -- Get the Date range to see if the entries are in
2990 -- multiple accounting periods
2991 IF g_proceed = 'Y' THEN
2992 get_date_range(l_transfer_run_id,
2993 l_start_date,
2994 l_end_date
2995 );
2996
2997 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2998 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_start_date,
2999 'END_DATE',l_end_date,'','',l_log_module,
3000 C_LEVEL_STATEMENT);
3001 END IF;
3002 -- Validate Period/ Year
3003 -- Bug2543724. Skipping Accounting Period validation for Budget journals
3004
3005 IF Nvl(l_gl_installed_flag,'N') = 'I' THEN
3006
3007 IF(g_entry_type = 'A') THEN
3008 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3009 xla_message('','Calling validate periods ','','','','','',l_log_module,C_LEVEL_STATEMENT );
3010 END IF;
3011 validate_periods(p_selection_type,
3012 p_sob_list,
3013 p_program_name,
3014 l_start_date,
3015 l_end_date
3016 );
3017 END IF;
3018
3019 END IF;
3020
3021 -- Process entries by periods
3022 OPEN c_getPeriods(p_sob_list(i).sob_id,
3023 l_start_date,
3024 l_end_date
3025 );
3026 LOOP -- Proecss Periods
3027 FETCH c_getPeriods
3028 INTO l_period_name, l_period_start_date,l_period_end_date,
3029 l_next_period, l_reversal_date;
3030 EXIT WHEN c_getPeriods%NOTFOUND;
3031
3032 --temporarily fixed for the bug2139573 (add 23:59:59)
3033 l_period_end_date := trunc(l_period_end_date) + 86399/86400;
3034
3035 -- Reset proceed to 'Y' for each period
3036 g_proceed := 'Y';
3037
3038 g_periods_cnt := g_periods_cnt + 1;
3039 g_control_info(g_periods_cnt).sob_id := l_set_of_books_id;
3040 g_control_info(g_periods_cnt).period_name := l_period_name;
3041 g_control_info(g_periods_cnt).rec_transferred := 0;
3042 g_control_info(g_periods_cnt).cnt_transfer_errors := 0;
3043 g_control_info(g_periods_cnt).cnt_acct_errors := 0;
3044
3045 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3046 xla_message('','~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~','','','','','',l_log_module,C_LEVEL_STATEMENT );
3047 xla_message('XLA_GLT_DATE_RANGE','START_DATE',l_period_start_date,
3048 'END_DATE',l_period_end_date,'','',l_log_module,C_LEVEL_STATEMENT );
3049 END IF;
3050
3051 -- Validate account on accounting entry lines if necessary
3052 IF Nvl(p_validate_account, Nvl(l_acct_validation_flag,'N')) = 'Y'
3053 AND g_proceed = 'Y' THEN
3054 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3055 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
3056 '',l_log_module,
3057 C_LEVEL_STATEMENT);
3058 END IF;
3059 validate_acct_lines( p_selection_type,
3060 l_set_of_books_id,
3061 l_coa_id,
3062 l_transfer_run_id,
3063 l_period_start_date,
3064 l_period_end_date);
3065
3066 -- Update headers for the lines that failed accounting
3067 -- validation. Do not call routine if there are no
3068 -- accounting entry lines to process.
3069 IF g_proceed = 'Y' THEN
3070 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3071 xla_message('XLA_GLT_CALL_VALIDATE_LINES','','','','','',
3072 '',l_log_module,
3073 C_LEVEL_STATEMENT);
3074 END IF;
3075 validate_acct_headers( p_selection_type,
3076 l_set_of_books_id,
3077 l_transfer_run_id,
3078 l_period_start_date,
3079 l_period_end_date);
3080
3081 END IF;
3082 END IF;
3083 -- Call following procedures only if there are records to
3084 -- process in this period
3085 IF g_proceed = 'Y' THEN
3086 IF p_gl_transfer_mode = 'D' THEN
3087 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3088 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3089 '',l_log_module,
3090 C_LEVEL_STATEMENT);
3091 END IF;
3092 update_linkid_detail( l_transfer_run_id,
3093 p_request_id,
3094 l_period_start_date,
3095 l_period_end_date);
3096
3097 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3098 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3099 l_log_module,
3100 C_LEVEL_STATEMENT);
3101 END IF;
3102
3103 gl_insert_detail( p_request_id,
3104 l_user_source_name,
3105 l_transfer_run_id,
3106 l_period_name,
3107 l_period_start_date,
3108 l_period_end_date,
3109 l_next_period,
3110 l_reversal_date,
3111 p_sob_list(i).average_balances_flag,
3112 p_gl_transfer_mode,
3113 l_group_id,
3114 p_batch_desc,
3115 p_je_desc,
3116 p_je_line_desc);
3117
3118 ELSE -- Summarize By Accounting Date
3119
3120 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3121 xla_message('','Calling derive line types ','','','','','',l_log_module,C_LEVEL_STATEMENT );
3122 END IF;
3123
3124 IF g_line_type IS NULL THEN
3125 derive_line_types;
3126 END IF;
3127
3128 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3129 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3130 l_log_module,
3131 C_LEVEL_STATEMENT);
3132 END IF;
3133
3134 gl_insert_summary( p_request_id,
3135 l_user_source_name,
3136 l_transfer_run_id,
3137 l_period_name,
3138 l_period_start_date,
3139 l_period_end_date,
3140 l_next_period,
3141 l_reversal_date,
3142 p_sob_list(i).average_balances_flag,
3143 p_gl_transfer_mode,
3144 l_group_id,
3145 p_batch_desc,
3146 p_je_desc,
3147 p_je_line_desc);
3148
3149 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3150 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3151 '',l_log_module,
3152 C_LEVEL_STATEMENT);
3153 END IF;
3154
3155 update_linkid_summary( p_request_id,
3156 p_gl_transfer_mode,
3157 l_transfer_run_id,
3158 l_period_start_date,
3159 l_period_end_date);
3160
3161
3162 IF g_line_type IS NOT NULL THEN
3163
3164 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3165 xla_message('XLA_GLT_CALL_UPDATE_LINKID','','','','','',
3166 '',l_log_module,
3167 C_LEVEL_STATEMENT);
3168 END IF;
3169
3170 update_linkid_detail( l_transfer_run_id,
3171 p_request_id,
3172 l_start_date,
3173 l_end_date);
3174
3175 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3176 xla_message('XLA_GLT_CREATE_JOURNAL_ENTRIES','','','','','','',
3177 l_log_module,
3178 C_LEVEL_STATEMENT);
3179 END IF;
3180
3181
3182 gl_insert_detail( p_request_id,
3183 l_user_source_name,
3184 l_transfer_run_id,
3185 l_period_name,
3186 l_period_start_date,
3187 l_period_end_date,
3188 l_next_period,
3189 l_reversal_date,
3190 p_sob_list(i).average_balances_flag,
3191 p_gl_transfer_mode,
3192 l_group_id,
3193 p_batch_desc,
3194 p_je_desc,
3195 p_je_line_desc);
3196
3197 END IF;
3198
3199 END IF;
3200
3201 -- Transfer encumbrance reversals to gl_interface if
3202 -- encumbrance is used.
3203 IF (Nvl(l_encumbrance_flag,'N') = 'Y') THEN
3204 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3205 xla_message('XLA_GLT_CALL_ENCUM_ROUTINE','','','','','',
3206 '',l_log_module,
3207 C_LEVEL_STATEMENT);
3208 END IF;
3209 transfer_enc_lines( p_application_id,
3210 l_set_of_books_id,
3211 l_transfer_run_id,
3212 l_period_start_date,
3213 l_period_end_date,
3214 l_next_period,
3215 l_reversal_date,
3216 p_sob_list(i).average_balances_flag,
3217 l_user_source_name,
3218 l_group_id,
3219 l_request_id,
3220 p_batch_desc,
3221 p_je_desc,
3222 p_je_line_desc);
3223 END IF;
3224 -- Call Globalization Routine
3225 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3226 xla_message('XLA_GLT_CALLING_JG_PKG','','','','','','',l_log_module,
3227 C_LEVEL_STATEMENT);
3228 END IF;
3229
3230 JG_XLA_GL_TRANSFER_PKG.jg_xla_gl_transfer
3231 ( p_application_id,
3232 p_user_id,
3233 p_org_id,
3234 p_request_id,
3235 l_transfer_run_id,
3236 p_program_name,
3237 p_selection_type,
3238 p_batch_name,
3239 l_period_start_date,
3240 l_period_end_date,
3241 p_gl_transfer_mode,
3242 p_process_days,
3243 p_debug_flag
3244 );
3245 END IF;
3246 END LOOP; -- Process Periods
3247 CLOSE c_getPeriods;
3248 END IF; -- Entries Found
3249 END LOOP; -- Multiple Entities
3250 CLOSE c_get_program_info;
3251 END IF; -- Selection Type
3252
3253 -- Call product specific API
3254 -- AP Trial Balance
3255 IF l_pre_commit_api IS NOT NULL THEN
3256 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3257 xla_message('XLA_GLT_CALL_PRE_COMMIT_API', 'API_NAME',
3258 l_pre_commit_api, '','','','',l_log_module,
3259 C_LEVEL_STATEMENT);
3260 END IF;
3261 EXECUTE IMMEDIATE
3262 ' begin ' || l_pre_commit_api ||'( '|| l_transfer_run_id || ' ); end;';
3263 END IF;
3264
3265 -- Bug# 4675862 - Call PSA API if Bugetary control is enabled for this
3266 -- set_of_books_id or USSGL profile option is Yes or encumbrance
3267 -- accounting is being used.
3268 -- (call the funds checker only if there are entries to process)
3269
3270 IF Nvl(l_gl_installed_flag,'N') = 'I' AND g_rec_transfer_flag = 'Y' THEN
3271 -- Bug2691999. Insert records only if, Submit Journal Import = Y
3272 IF NVL(p_submit_journal_import,'Y') = 'Y' THEN
3273 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3274 xla_message('XLA_GLT_INSERT_GIC','STATUS','S','','','','',l_log_module,
3275 C_LEVEL_STATEMENT);
3276 END IF;
3277
3278 INSERT INTO gl_interface_control
3279 ( JE_SOURCE_NAME,
3280 STATUS,
3281 INTERFACE_RUN_ID,
3282 GROUP_ID,
3283 SET_OF_BOOKS_ID,
3284 PACKET_ID
3285 )
3286 VALUES
3287 (
3288 l_source_name,
3289 'S',
3290 l_interface_run_id,
3291 l_group_id,
3292 l_set_of_books_id,
3293 ''
3294 );
3295 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3296 xla_message('XLA_GLT_SUBMIT_JOURNAL_IMP','','','','','','',l_log_module,C_LEVEL_STATEMENT);
3297 END IF;
3298 l_submittedreqid:= fnd_request.submit_request
3299 (
3300 'SQLGL', -- application short name
3301 'GLLEZL', -- program short name
3302 NULL, -- program name
3303 NULL, -- start date
3304 FALSE, -- sub-request
3305 l_interface_run_id, -- interface run id
3306 l_set_of_books_id, -- set of books id
3307 'N', -- error to suspense flag
3308 NULL, -- from accounting date
3309 NULL, -- to accounting date
3310 NVL(p_summary_journal_entry,'N'), -- create summary flag
3311 'N' -- import desc flex flag
3312 );
3313
3314
3315 IF Nvl(l_submittedreqid,0) = 0 THEN
3316 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
3317 xla_message('XLA_GLT_JOURNALIMP_ERROR','','','','','','',l_log_module,C_LEVEL_EXCEPTION);
3318 END IF;
3319 ELSE
3320 -- Journal Import is submitted successfully.
3321 -- Call PSA routine. Bug# 4675862
3322 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3323 xla_message('XLA_GLT_JOURNALIMP_SUBMITTED','REQUEST_ID',
3324 l_submittedreqid,'','','','',l_log_module,C_LEVEL_STATEMENT);
3325 END IF;
3326 xla_utility_pkg.print_logfile('Calling PSA_FUNDS_CHECKER_PKG');
3327 PSA_FUNDS_CHECKER_PKG.populate_group_id
3328 (p_grp_id => l_group_id
3329 ,p_application_id => g_application_id
3330 );
3331 END IF;
3332 END IF;
3333 END IF;
3334
3335 UPDATE xla_gl_transfer_batches_all
3336 SET gllezl_request_id = l_submittedreqid,
3337 transfer_status = Decode(g_sob_rows_created,0,'N','C'),
3338 packet_id = l_packet_id
3339 WHERE gl_transfer_run_id = l_transfer_run_id;
3340 COMMIT;
3341 g_total_rows_created := g_total_rows_created + g_sob_rows_created;
3342
3343 END LOOP; -- process sobs
3344 IF g_total_rows_created > 0 THEN
3345 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3346 xla_message('XLA_GLT_TRANSFER_SUCCESS','','','',
3347 '','','',l_log_module,C_LEVEL_STATEMENT);
3348 END IF;
3349 ELSE
3350 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
3351 xla_message('XLA_GLT_NO_DATA','','','',
3352 '','','',l_log_module,C_LEVEL_STATEMENT);
3353 END IF;
3354 END IF;
3355 EXCEPTION
3356 WHEN OTHERS THEN
3357 IF (SQLCODE <> -20001) THEN
3358 IF (C_LEVEL_ERROR >= g_log_level) THEN
3359 xla_message('XLA_GLT_DEBUG','ERROR', Sqlerrm, 'DEBUG_INFO',
3360 l_debug_info,'','',l_log_module,C_LEVEL_ERROR);
3361 END IF;
3362 END IF;
3363 RAISE;
3364 END XLA_GL_TRANSFER;
3365
3366 BEGIN
3367 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3368 g_log_enabled := fnd_log.test
3369 (log_level => g_log_level
3370 ,module => C_DEFAULT_MODULE);
3371
3372 IF NOT g_log_enabled THEN
3373 g_log_level := C_LEVEL_LOG_DISABLED;
3374 END IF;
3375
3376 END XLA_GL_TRANSFER_PKG;