[Home] [Help]
PACKAGE BODY: APPS.GL_FUSION_TRANSFER_PKG
Source
1 PACKAGE BODY gl_fusion_transfer_pkg AS
2 -- $Header: glufutrb.pls 120.6 2011/08/13 00:19:25 djogg noship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | gl_fusion_transfer_pkg |
10 | |
11 | DESCRIPTION |
12 | Package to transfer data to fusion. |
13 | |
14 | HISTORY |
15 | 24-MAR-10 D J Ogg Created |
16 +===========================================================================*/
17
18 --=============================================================================
19 -- **************** declarations ********************
20 --=============================================================================
21 -------------------------------------------------------------------------------
22 -- declaring private types
23 -------------------------------------------------------------------------------
24 TYPE NumList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
25 TYPE Var30List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
26
27 -------------------------------------------------------------------------------
28 -- declaring constants
29 -------------------------------------------------------------------------------
30 C_INCREMENTAL CONSTANT CHAR := 'I';
31 C_FULL CONSTANT CHAR := 'F';
32
33 -------------------------------------------------------------------------------
34 -- declaring exceptions
35 -------------------------------------------------------------------------------
36 fatal_exception EXCEPTION;
37 warning_exception EXCEPTION;
38
39 -------------------------------------------------------------------------------
40 -- declaring private variables
41 -------------------------------------------------------------------------------
42
43 --
44 -- Information about current run
45 --
46 g_transfer_mode CHAR;
47 g_access_set_id NUMBER;
48 g_debug_mode BOOLEAN;
49 g_interface_table_name VARCHAR2(30);
50 g_interface_row_count NUMBER;
51
52 --
53 -- System information
54 --
55 g_system_id NUMBER;
56 g_system_map_id NUMBER;
57 g_je_source_key VARCHAR2(25);
58 g_bsv_assignment_type VARCHAR2(30);
59 g_fusion_user VARCHAR2(240);
60
61 --
62 -- Full transfer information
63 --
64 g_from_period VARCHAR2(15);
65 g_from_eff_per_num NUMBER;
66 g_to_period VARCHAR2(15);
67 g_first_period_changed BOOLEAN := FALSE;
68 g_original_first_period VARCHAR2(15);
69
70 --
71 -- Incremental transfer information
72 --
73 g_low_delta_run_id NUMBER;
74 g_high_delta_run_id NUMBER;
75
76 --
77 -- Mapping information
78 --
79 g_lgr_mapping_id NUMBER;
80 g_coa_mapping_id NUMBER;
81 g_cal_mapping_id NUMBER;
82 g_coa_mapping_name VARCHAR2(33);
83 g_first_eff_per_num NUMBER;
84
85 --
86 -- From ledger information
87 --
88 g_from_ledger_id NUMBER;
89 g_from_ledger_name VARCHAR2(30);
90 g_from_ledger_shortname VARCHAR2(30);
91 g_from_coa_id NUMBER;
92 g_from_segs Var30List;
93 g_from_delim VARCHAR2(1);
94 g_from_efb_on BOOLEAN;
95 g_funct_curr VARCHAR2(15);
96 g_from_balance_alc BOOLEAN;
97
98 --
99 -- To ledger information
100 --
101 g_to_ledger_id NUMBER;
102 g_to_ledger_name VARCHAR2(30);
103 g_to_coa_id NUMBER;
104 g_to_segs Var30List;
105 g_to_delim VARCHAR2(1);
106
107 --
108 -- Who information
109 g_user_id NUMBER;
110 g_login_id NUMBER;
111 g_request_id NUMBER;
112
113 --
114 -- Journal Import information
115 --
116 g_group_id NUMBER;
117
118 -------------------------------------------------------------------------------
119 -- forward declaration of private procedures and functions
120 -------------------------------------------------------------------------------
121 PROCEDURE get_delta_id_range;
122 PROCEDURE insert_interface_rows;
123 PROCEDURE do_ccid_mapping;
124 PROCEDURE insert_tracking_data(p_status_code VARCHAR2);
125 PROCEDURE get_mapping_info;
126 PROCEDURE get_ledger_info(p_ledger_shortname VARCHAR2,
127 p_ledger_id NUMBER,
128 p_ledger_name OUT NOCOPY VARCHAR2,
129 p_coa_id OUT NOCOPY NUMBER,
130 p_efb_on OUT NOCOPY BOOLEAN,
131 p_curr_code OUT NOCOPY VARCHAR2,
132 p_balance_alc OUT NOCOPY BOOLEAN);
133 PROCEDURE get_coa_info(p_coa_id NUMBER,
134 p_segs OUT NOCOPY Var30List,
135 p_delim OUT NOCOPY VARCHAR2);
136 PROCEDURE update_transfer_tables;
137 PROCEDURE get_system_info;
138 PROCEDURE get_system_id;
139 PROCEDURE check_access;
140 PROCEDURE check_bsv_access;
141
142 --=============================================================================
143 -- *********** Local Trace Routine **********
144 --=============================================================================
145 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
146 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
147 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
148 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
149 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
150 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
151
152 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
153 C_DEFAULT_MODULE CONSTANT VARCHAR2(240)
154 := 'gl.plsql.gl_fusion_transfer_pkg';
155
156 g_log_level NUMBER;
157 g_log_enabled BOOLEAN;
158
159 --=============================================================================
160 -- ******* trace **********
161 --=============================================================================
162 PROCEDURE trace
163 (p_msg IN VARCHAR2
164 ,p_level IN NUMBER
165 ,p_module IN VARCHAR2) IS
166 BEGIN
167
168 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
169 fnd_log.message(p_level, p_module);
170 ELSIF p_level >= g_log_level THEN
171 fnd_log.string(p_level, p_module, p_msg);
172 END IF;
173
174 END trace;
175
176 --=============================================================================
177 -- ******* Print Log File **********
178 --=============================================================================
179 PROCEDURE print_logfile(p_msg IN VARCHAR2) IS
180 BEGIN
181
182 fnd_file.put_line(fnd_file.log,p_msg);
183
184 END print_logfile;
185
186 --=============================================================================
187 -- ******* Print SQL to File **********
188 --=============================================================================
189 PROCEDURE print_sql_logfile(p_sqlstr IN VARCHAR2) IS
190 strlen NUMBER;
191 currpos NUMBER;
192 BEGIN
193
194 strlen := LENGTH (p_sqlstr);
195 currpos := 1;
196
197 WHILE (currpos < strlen) LOOP
198
199 fnd_file.put_line(fnd_file.log, substr(p_sqlstr, currpos, 2000));
200
201 currpos := currpos + 2000;
202
203 END LOOP;
204
205 END print_sql_logfile;
206
207 --=============================================================================
208 -- ******* Enter Routine *******
209 --=============================================================================
210 PROCEDURE enter_routine(p_routine IN VARCHAR2) IS
211 BEGIN
212
213 IF (g_debug_mode) THEN
214 print_logfile ('>> '|| p_routine);
215 END IF;
216
217 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
218 trace
219 (p_msg => 'BEGIN of procedure '||p_routine,
220 p_level => C_LEVEL_PROCEDURE,
221 p_module => p_routine);
222 END IF;
223 END enter_routine;
224
225 PROCEDURE success_exit(p_routine IN VARCHAR2) IS
226 BEGIN
227
228 IF (g_debug_mode) THEN
229 print_logfile ('<< '|| p_routine);
230 END IF;
231
232 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
233 trace
234 (p_msg => 'END of procedure '||p_routine,
235 p_level => C_LEVEL_PROCEDURE,
236 p_module => p_routine);
237 END IF;
238 END success_exit;
239
240 PROCEDURE failure_exit(p_routine IN VARCHAR2) IS
241 BEGIN
242
243 IF (g_debug_mode) THEN
244 print_logfile ('<x '|| p_routine);
245 END IF;
246
247 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
248 trace
249 (p_msg => 'FAIL procedure '||p_routine,
250 p_level => C_LEVEL_PROCEDURE,
251 p_module => p_routine);
252 END IF;
253 END failure_exit;
254
255 PROCEDURE print_variable(p_variable IN VARCHAR2,
256 p_value IN VARCHAR2,
257 p_routine IN VARCHAR2) IS
258 BEGIN
259
260 IF (g_debug_mode) THEN
261 print_logfile (p_variable || ' = '|| p_value);
262 END IF;
263
264 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
265 trace
266 (p_msg => p_variable || ' = ' || p_value,
267 p_level => C_LEVEL_PROCEDURE,
268 p_module => p_routine);
269 END IF;
270 END print_variable;
271
272 --=============================================================================
273 -- *********** public procedures and functions **********
274 --=============================================================================
275 --=============================================================================
276 --
277 --
278 --
279 --
280 --
281 --
282 --
283 --
284 --
285 --
286 -- Following are the public routines:
287 --
288 -- 1. BeforeReport
289 -- 2. AfterReport
290 -- 3. do_transfer
291 -- 4. purge_data
292 --
293 --=============================================================================
294
295 --=============================================================================
296 --
297 --
298 --
299 -- BeforeReport (API called by BIP)
300 --
301 --
302 --
303 --=============================================================================
304 FUNCTION BeforeReport RETURN BOOLEAN IS
305 l_errbuf VARCHAR2(2000);
306 BEGIN
307 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||
308 ' - Beginning of the Report');
309
310 --
311 -- Get the request id
312 --
313 g_request_id_str := to_char(fnd_global.conc_request_id());
314
315 --
316 -- Get the system id
317 --
318 gl_fusion_transfer_pkg.get_system_id;
319 g_system_id_str := to_char(nvl(g_system_id, -1));
320
321 --
322 -- Do the transfer
323 --
324 gl_fusion_transfer_pkg.do_transfer
325 (p_errbuf => l_errbuf,
326 p_retcode => P_BI_RETCODE,
327 p_from_ledger_shortname => P_BI_FROM_LEDGER_SHORTNAME,
328 p_from_ledger_id => P_BI_FROM_LEDGER_ID,
329 p_transfer_mode => P_BI_TRANSFER_MODE,
330 p_from_period_name => P_BI_FROM_PERIOD_NAME,
331 p_access_set_id => P_BI_ACCESS_SET_ID,
332 p_debug_mode => P_BI_DEBUG_MODE);
333
334
335 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||
336 ' - Finished Before Report');
337 RETURN(TRUE);
338
339 END;
340
341
342 --=============================================================================
343 --
344 --
345 --
346 -- AfterReport (API called by BIP)
347 --
348 --
349 --
350 --=============================================================================
351
352 FUNCTION AfterReport RETURN BOOLEAN IS
353 l_temp BOOLEAN;
354 l_log_module VARCHAR2(240);
355 l_dummy NUMBER;
356 BEGIN
357
358 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||
359 ' - Starting After Report');
360
361 --
362 -- Detect any bad interface data. Make sure it isn't processed
363 --
364 SELECT nvl(sum(1),0)
365 INTO l_dummy
366 FROM gl_xfr_tracking
367 WHERE xfer_request_id = g_request_id
368 AND system_id = g_system_id
369 AND status_code NOT IN ('SUCCESS', 'FIRST_PERIOD_CHANGED');
370
371 IF ( (l_dummy = 1)
372 AND (g_group_id IS NOT NULL)) THEN
373 UPDATE gl_xfr_interface
374 SET status = 'PROCESSED'
375 WHERE system_id = g_system_id
376 AND group_id = g_group_id;
377 END IF;
378
379 IF P_BI_RETCODE = 0 THEN
380 NULL;
381 ELSIF P_BI_RETCODE = 1 THEN
382 l_temp := fnd_concurrent.set_completion_status
383 (status => 'WARNING'
384 ,message => NULL);
385 ELSE
386 l_temp := fnd_concurrent.set_completion_status
387 (status => 'ERROR'
388 ,message => NULL);
389 END IF;
390
391 COMMIT;
392
393 print_logfile(to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')|| ' - End of the Report');
394 RETURN (TRUE);
395
396 END;
397
398 --=============================================================================
399 -- The main procedure of the fusion transfer.
400 -- Parameters are:
401 -- p_errbuf Standard concurrent program parameter
402 -- p_retcode Standard concurrent program parameter
403 -- p_from_ledger_shortname Ledger shortname to transfer from (can be
404 -- ledger or reporting currency). Need to
405 -- pass ledger shortname to support balance level
406 -- reporting currencies.
407 -- p_from_ledger_id Ledger to transfer from
408 -- p_transfer_mode Type of transfer. Either:
409 -- 'F' - Full
410 -- 'I' - Incremental
411 -- p_from_period_name For full transfer, the period to transfer.
412 -- For incremental, should be null. Incremental
413 -- always transfers data for all periods that
414 -- have been transferred in full
415 -- p_access_set_id Access set id for security
416 -- p_debug_mode Debug mode on? (Y or N)
417 --=============================================================================
418 PROCEDURE do_transfer
419 (p_errbuf OUT NOCOPY VARCHAR2,
420 p_retcode OUT NOCOPY NUMBER,
421 p_from_ledger_shortname IN VARCHAR2,
422 p_from_ledger_id IN NUMBER,
423 p_transfer_mode IN VARCHAR2,
424 p_from_period_name IN VARCHAR2,
425 p_access_set_id IN NUMBER,
426 p_debug_mode IN VARCHAR2) IS
427 l_log_module VARCHAR2(240);
428 BEGIN
429 l_log_module := C_DEFAULT_MODULE||'.do_transfer';
430 enter_routine(l_log_module);
431 print_variable('p_from_ledger_shortname', p_from_ledger_shortname, l_log_module);
432 print_variable('p_from_ledger_id', p_from_ledger_id, l_log_module);
433 print_variable('p_transfer_mode', p_transfer_mode, l_log_module);
434 print_variable('p_from_period_name', p_from_period_name, l_log_module);
435 print_variable('p_access_set_id', p_access_set_id, l_log_module);
436 print_variable('p_debug_mode', p_debug_mode, l_log_module);
437
438 ----------------------------------------------------------------------------
439 -- Initialize global variables
440 ----------------------------------------------------------------------------
441 g_transfer_mode := p_transfer_mode;
442 g_access_set_id := p_access_set_id;
443 g_from_period := p_from_period_name;
444 g_from_ledger_id := p_from_ledger_id;
445 g_from_ledger_shortname := p_from_ledger_shortname;
446 g_user_id := fnd_global.user_id;
447 g_login_id := fnd_global.login_id;
448 g_request_id := fnd_global.conc_request_id;
449 g_interface_table_name := 'GL_XFR_INTERFACE';
450
451 IF (p_debug_mode = 'Y') THEN
452 g_debug_mode := TRUE;
453 ELSE
454 IF (fnd_profile.value('GL_DEBUG_MODE') = 'Y') THEN
455 g_debug_mode := TRUE;
456 ELSE
457 g_debug_mode := FALSE;
458 END IF;
459 END IF;
460
461 ----------------------------------------------------------------------------
462 -- Check access (data_access_set)
463 ----------------------------------------------------------------------------
464 gl_fusion_transfer_pkg.check_access;
465
466 ----------------------------------------------------------------------------
467 -- Get mapping and ledger information
468 ----------------------------------------------------------------------------
469 gl_fusion_transfer_pkg.get_mapping_info;
470
471 ----------------------------------------------------------------------------
472 -- Get system information. Also check that this system is allowed to
473 -- populate that from ledger
474 ----------------------------------------------------------------------------
475 gl_fusion_transfer_pkg.get_system_info;
476
477 ----------------------------------------------------------------------------
478 -- For incremental mode, get the range of ids to process
479 ----------------------------------------------------------------------------
480 IF (g_transfer_mode = C_INCREMENTAL) THEN
481 gl_fusion_transfer_pkg.get_delta_id_range;
482 END IF;
483
484 ----------------------------------------------------------------------------
485 -- Insert the rows to be transferred into the interface table
486 ----------------------------------------------------------------------------
487 gl_fusion_transfer_pkg.insert_interface_rows;
488
489 ----------------------------------------------------------------------------
490 -- If no data was found, then stop here
491 ----------------------------------------------------------------------------
492 IF (g_interface_row_count = 0) THEN
493 gl_message.write_log('GLFUXFR000');
494
495 gl_fusion_transfer_pkg.insert_tracking_data('NO_DATA');
496
497 IF (g_transfer_mode = C_INCREMENTAL) THEN
498 p_retcode := 0;
499 ELSE
500 p_retcode := 1;
501 END IF;
502 p_errbuf := '';
503
504 RETURN;
505 END IF;
506
507 ----------------------------------------------------------------------------
508 -- Do the account mappings
509 ----------------------------------------------------------------------------
510 IF (g_coa_mapping_id IS NOT NULL) THEN
511 gl_fusion_transfer_pkg.do_ccid_mapping;
512 END IF;
513
514 ----------------------------------------------------------------------------
515 -- Check that all the bsvs used will be accepted by fusion.
516 ----------------------------------------------------------------------------
517 IF (g_bsv_assignment_type <> 'A') THEN
518 gl_fusion_transfer_pkg.check_bsv_access;
519 END IF;
520
521 ----------------------------------------------------------------------------
522 -- Insert the tracking data for fusion. Record that the first period
523 -- changed if necessary.
524 ----------------------------------------------------------------------------
525 IF (g_first_period_changed) THEN
526 gl_fusion_transfer_pkg.insert_tracking_data('FIRST_PERIOD_CHANGED');
527 ELSE
528 gl_fusion_transfer_pkg.insert_tracking_data('SUCCESS');
529 END IF;
530
531 ----------------------------------------------------------------------------
532 -- Update transfer tables as necessary, to track the new max delta run
533 -- id and the full transfer done flag
534 ----------------------------------------------------------------------------
535 gl_fusion_transfer_pkg.update_transfer_tables;
536
537 ----------------------------------------------------------------------------
538 -- Set the return status
539 ----------------------------------------------------------------------------
540 IF (g_first_period_changed) THEN
541 p_retcode := 1;
542 p_errbuf := '';
543 ELSE
544 p_retcode := 0;
545 p_errbuf := '';
546 END IF;
547
548 print_variable('p_retcode', p_retcode, l_log_module);
549 print_variable('p_errbuf', p_errbuf, l_log_module);
550
551 success_exit(l_log_module);
552
553 EXCEPTION
554 WHEN fatal_exception THEN
555 p_retcode := 2;
556 p_errbuf := '';
557 WHEN warning_exception THEN
558 p_retcode := 1;
559 p_errbuf := '';
560 END do_transfer;
561
562
563 --=============================================================================
564 -- The purge routine to purge imported data.
565 -- Parameters are:
566 -- ** None **
567 --=============================================================================
568 --=============================================================================
569 -- The main procedure of the fusion transfer.
570 -- Parameters are:
571 -- p_errbuf Standard concurrent program parameter
572 -- p_retcode Standard concurrent program parameter
573 -- p_from_ledger_shortname Ledger shortname to transfer from (can be
574 -- ledger or reporting currency). Need to
575 -- pass ledger shortname to support balance level
576 -- reporting currencies.
577 -- p_from_ledger_id Ledger to transfer from
578 -- p_transfer_mode Type of transfer. Either:
579 -- 'F' - Full
580 -- 'I' - Incremental
581 -- p_from_period_name For full transfer, the period to transfer.
582 -- For incremental, should be null. Incremental
583 -- always transfers data for all periods that
584 -- have been transferred in full
585 -- p_access_set_id Access set id for security
586 -- p_debug_mode Debug mode on? (Y or N)
587 --=============================================================================
588 PROCEDURE purge_data
589 (p_errbuf OUT NOCOPY VARCHAR2,
590 p_retcode OUT NOCOPY NUMBER) IS
591 l_log_module VARCHAR2(240);
592 l_row_count NUMBER;
593 BEGIN
594 l_log_module := C_DEFAULT_MODULE||'.purge_data';
595 enter_routine(l_log_module);
596
597 --
598 -- Get system id
599 --
600 gl_fusion_transfer_pkg.get_system_id;
601
602 ----------------------------------------------------------------------------
603 -- Delete processed data
604 ----------------------------------------------------------------------------
605 DELETE gl_xfr_interface
606 WHERE status = 'PROCESSED'
607 AND system_id = g_system_id;
608
609 l_row_count := SQL%ROWCOUNT;
610
611 gl_message.write_log('GLFUPRG001', 2,
612 'ROW_COUNT', l_row_count,
613 'TABLE', 'gl_xfr_interface');
614
615 success_exit(l_log_module);
616
617 p_retcode := 0;
618 p_errbuf := '';
619
620 return;
621 EXCEPTION
622 WHEN OTHERS THEN
623 p_retcode := 2;
624 p_errbuf := '';
625 END purge_data;
626
627 --=============================================================================
628 --
629 -- Name: get_delta_id_range
630 -- Description:
631 -- This routine gets the low and id delta ids to be processed for
632 -- incremental mode.
633 --
634 -- DOES A COMMIT
635 --
636 -- Parameters:
637 -- *** NONE ***
638 --=============================================================================
639 PROCEDURE get_delta_id_range IS
640 l_log_module VARCHAR2(240);
641 BEGIN
642
643 l_log_module := C_DEFAULT_MODULE||'.get_delta_id_range';
644 enter_routine(l_log_module);
645
646 ---
647 --- Get the largest delta id fully processed so far
648 ---
649 SELECT nvl(max_delta_run_id + 1, 0)
650 INTO g_low_delta_run_id
651 FROM gl_xfr_ledger_mappings
652 WHERE ledger_mapping_id = g_lgr_mapping_id;
653
654 print_variable('g_low_delta_run_id', g_low_delta_run_id, l_log_module);
655
656 ---
657 --- Lock the table to make sure no one is inserting into it right now
658 ---
659 LOCK TABLE gl_balances_delta IN EXCLUSIVE MODE NOWAIT;
660
661 ---
662 --- Get the largest delta id data exists for
663 ---
664 SELECT nvl(max(delta_run_id), 0)
665 into g_high_delta_run_id
666 FROM GL_BALANCES_DELTA;
667
668 print_variable('g_high_delta_run_id', g_high_delta_run_id, l_log_module);
669
670 print_logfile('Processing delta run ids from '
671 || to_char(g_low_delta_run_id) || ' to '
672 || to_char(g_high_delta_run_id));
673
674 ---
675 --- Commit to release the lock
676 ---
677 COMMIT;
678
679 success_exit(l_log_module);
680 END get_delta_id_range;
681
682
683 --=============================================================================
684 --
685 -- Name: insert_interface_rows
686 -- Description:
687 -- This routine inserts all of the rows into the interface table.
688 -- For incremental mode, it tries to reuse the ccid mappings, if
689 -- available. For full mode or in incremental mode when no
690 -- mappings are available, it leaves the segment values null,
691 -- populates the ccid column with the from ccid, and populates the
692 -- status of 'MAP'.
693 --
694 -- Parameters:
695 -- *** NONE ***
696 --=============================================================================
697 PROCEDURE insert_interface_rows IS
698 l_log_module VARCHAR2(240);
699 l_sqlbuf VARCHAR2(20000);
700 l_eff_date NUMBER;
701 l_name VARCHAR2(2000);
702 BEGIN
703
704 l_log_module := C_DEFAULT_MODULE||'.insert_interface_rows';
705 enter_routine(l_log_module);
706
707 ---
708 --- Figure out the effective date to be used
709 ---
710 IF (g_transfer_mode = C_INCREMENTAL) THEN
711 IF (g_cal_mapping_id IS NULL) THEN
712 UPDATE gl_xfr_led_map_periods lmper
713 SET temp_date = (SELECT least(greatest(trunc(sysdate),ps.start_date),ps.end_date)
714 FROM gl_period_statuses ps
715 WHERE ps.application_id = 101
716 AND ps.ledger_id = g_from_ledger_id
717 AND ps.period_name = lmper.from_period_name)
718 WHERE ledger_mapping_id = g_lgr_mapping_id;
719 ELSE
720 UPDATE gl_xfr_led_map_periods lmper
721 SET temp_date = (SELECT least(greatest(trunc(sysdate),per.start_date),per.end_date)
722 FROM gl_xfr_period_mappings map,
723 gl_xfr_ledgers lgr,
724 gl_xfr_periods per
725 WHERE map.calendar_mapping_id = g_cal_mapping_id
726 AND map.from_period_name = lmper.from_period_name
727 AND lgr.ledger_id = g_to_ledger_id
728 AND per.period_set_name = lgr.period_set_name
729 AND per.period_name = map.to_period_name)
730 WHERE ledger_mapping_id = g_lgr_mapping_id;
731 END IF;
732 ELSE
733 IF (g_cal_mapping_id IS NULL) THEN
734 SELECT to_number(to_char(least(greatest(trunc(sysdate),ps.start_date),ps.end_date),'J'))
735 INTO l_eff_date
736 FROM gl_period_statuses ps
737 WHERE ps.application_id = 101
738 AND ps.ledger_id = g_from_ledger_id
739 AND ps.period_name = g_from_period;
740 ELSE
741 SELECT to_number(to_char(least(greatest(trunc(sysdate),per.start_date),per.end_date),'J'))
742 INTO l_eff_date
743 FROM gl_xfr_period_mappings map,
744 gl_xfr_ledgers lgr,
745 gl_xfr_periods per
746 WHERE map.calendar_mapping_id = g_cal_mapping_id
747 AND map.from_period_name = g_from_period
748 AND lgr.ledger_id = g_to_ledger_id
749 AND per.period_set_name = lgr.period_set_name
750 AND per.period_name = map.to_period_name;
751 END IF;
752 END IF;
753
754 ---
755 --- Get group id
756 ---
757 SELECT gl_interface_control_s.NEXTVAL
758 INTO g_group_id
759 FROM DUAL;
760
761 ---
762 --- If we are running in full mode, and this period is before the
763 --- first period ever transferred, then make it the first period
764 --- ever transferred.
765 ---
766 g_first_period_changed := FALSE;
767 IF ( (g_transfer_mode = C_FULL)
768 AND (g_from_eff_per_num < g_first_eff_per_num)) THEN
769
770 IF (g_first_eff_per_num <> 99999999) THEN
771 g_first_period_changed := TRUE;
772
773 SELECT period_name
774 INTO g_original_first_period
775 FROM gl_period_statuses
776 WHERE application_id = 101
777 AND ledger_id = g_from_ledger_id
778 AND effective_period_num = g_first_eff_per_num;
779
780 gl_message.write_log('GLFUXFR009', 2,
781 'OLD_PERIOD_NAME', g_original_first_period,
782 'NEW_PERIOD_NAME', g_from_period);
783 END IF;
784
785 g_first_eff_per_num := g_from_eff_per_num;
786
787 END IF;
788
789 ---
790 --- Get the batch and journal name
791 ---
792 IF (g_transfer_mode = C_FULL) THEN
793 l_name := gl_message.get_message(
794 msg_name => 'GLFUXFR013',
795 show_num => 'N',
796 t1 => 'LEDGER',
797 v1 => substrb(g_from_ledger_shortname, 1, 7),
798 t2 => 'PERIOD',
799 v2 => substrb(g_from_period, 1, 6));
800 l_name := substrb(l_name, 1, 100);
801 ELSE
802 l_name := gl_message.get_message(
803 msg_name => 'GLFUXFR014',
804 show_num => 'N',
805 t1 => 'LEDGER',
806 v1 => substrb(g_from_ledger_shortname, 1, 7));
807 l_name := substrb(l_name, 1, 93);
808 END IF;
809
810
811 ---
812 --- Build sql*statement
813 ---
814 l_sqlbuf :=
815 'INSERT INTO ' || g_interface_table_name || '
816 (status, ledger_id, set_of_books_id,
817 user_je_source_name, user_je_category_name,
818 accounting_date, currency_code,
819 date_created, actual_flag, je_header_id,
820 created_by, period_name, reference1, reference4,
821 reference10,
822 entered_dr, entered_cr, accounted_dr, accounted_cr,
823 code_combination_id, request_id, group_id,
824 row_number, system_id,
825 reference21, reference22, reference23, reference24 ';
826
827 IF ( (g_transfer_mode = C_INCREMENTAL)
828 OR (g_coa_mapping_id IS NULL)) THEN
829 FOR i IN 1 .. g_to_segs.LAST LOOP
830 l_sqlbuf := l_sqlbuf ||
831 ', ' || g_to_segs(i);
832 END LOOP;
833 END IF;
834
835 l_sqlbuf := l_sqlbuf ||
836 ')
837 SELECT ';
838
839 -- Status. Mark everything that needs to be
840 -- mapped with a status of 'MAP'.
841 IF (g_coa_mapping_id IS NULL) THEN
842 l_sqlbuf := l_sqlbuf || '
843 ''NEW'', ';
844 ELSIF (g_transfer_mode = C_INCREMENTAL) THEN
845 l_sqlbuf := l_sqlbuf || '
846 decode(dat.to_code_combination_id, NULL, ''MAP'', ''NEW''), ';
847 ELSE
848 l_sqlbuf := l_sqlbuf || '
849 ''MAP'', ';
850 END IF;
851
852 l_sqlbuf := l_sqlbuf || '
853 :to_lgr_id, :to_lgr_id, :je_source_key, ''Remote Transfer'',
854 dat.effective_date,
855 dat.currency_code, sysdate, ''A'', -1,
856 :usr_id, dat.to_period_name, ';
857
858 IF (g_transfer_mode = C_INCREMENTAL) THEN
859 l_sqlbuf := l_sqlbuf || '
860 :bname || '' '' || substrb(dat.from_period_name,1, 6),
861 :hname || '' '' || substrb(dat.from_period_name,1, 6), ';
862 ELSE
863 l_sqlbuf := l_sqlbuf || '
864 :bname, :hname, ';
865 END IF;
866
867 l_sqlbuf := l_sqlbuf || '
868 dat.description,
869 dat.entered_dr, dat.entered_cr,
870 decode(dat.currency_code, ''STAT'', NULL, dat.accounted_dr),
871 decode(dat.currency_code, ''STAT'', NULL, dat.accounted_cr),
872 decode(dat.to_code_combination_id,
873 NULL, dat.from_code_combination_id,
874 NULL),
875 :req_id, :grp_id, rownum, :system_id,
876 to_char(dat.from_ledger_id),
877 to_char(dat.from_code_combination_id),
878 dat.from_period_name,
879 :g_fusion_user ';
880
881 IF ( (g_transfer_mode = C_INCREMENTAL)
882 OR (g_coa_mapping_id IS NULL)) THEN
883 FOR i IN 1 .. g_to_segs.LAST LOOP
884 l_sqlbuf := l_sqlbuf ||
885 ', dat.' || g_to_segs(i);
886 END LOOP;
887 END IF;
888
889 l_sqlbuf := l_sqlbuf || '
890 FROM (SELECT
891 bal.currency_code currency_code,
892 bal.ledger_id from_ledger_id,
893 bal.code_combination_id from_code_combination_id,
894 bal.period_name from_period_name, ';
895
896 IF ( (g_transfer_mode = C_INCREMENTAL)
897 AND (g_coa_mapping_id IS NOT NULL)) THEN
898 l_sqlbuf := l_sqlbuf || '
899 map.to_ccid to_code_combination_id, ';
900 ELSE
901 l_sqlbuf := l_sqlbuf || '
902 NULL to_code_combination_id, ';
903 END IF;
904
905 IF (g_cal_mapping_id IS NOT NULL) THEN
906 l_sqlbuf := l_sqlbuf || '
907 per.to_period_name to_period_name, ';
908 ELSE
909 l_sqlbuf := l_sqlbuf || '
910 bal.period_name to_period_name, ';
911 END IF;
912
913 IF (g_transfer_mode = C_INCREMENTAL) THEN
914 l_sqlbuf := l_sqlbuf || '
915 lmper.temp_date effective_date, ';
916 ELSE
917 l_sqlbuf := l_sqlbuf || '
918 to_date(to_char(:eff_date), ''J'') effective_date, ';
919 END IF;
920
921 IF (g_coa_mapping_id IS NOT NULL) THEN
922 IF (g_transfer_mode = C_INCREMENTAL) THEN
923 FOR i IN 1 .. g_from_segs.LAST LOOP
924 IF (i < g_from_segs.LAST) THEN
925 l_sqlbuf := l_sqlbuf ||
926 ' max(fromcc.'||g_from_segs(i)||') || ''' || g_from_delim || ''' || ';
927 ELSE
928 l_sqlbuf := l_sqlbuf ||
929 ' max(fromcc.' || g_from_segs(i) || ') description, ';
930 END IF;
931 END LOOP;
932 ELSE
933 FOR i IN 1 .. g_from_segs.LAST LOOP
934 IF (i < g_from_segs.LAST) THEN
935 l_sqlbuf := l_sqlbuf ||
936 ' fromcc.'||g_from_segs(i)||' || ''' || g_from_delim || ''' || ';
937 ELSE
938 l_sqlbuf := l_sqlbuf ||
939 ' fromcc.' || g_from_segs(i) || ' description, ';
940 END IF;
941 END LOOP;
942 END IF;
943 ELSE
944 l_sqlbuf := l_sqlbuf || '
945 NULL description, ';
946 END IF;
947
948 --- Get the amounts.
949 IF (g_transfer_mode = C_INCREMENTAL) THEN
950
951 IF (g_from_efb_on) THEN
952
953 -- Incremental and entered functional balances. Transfer
954 -- functional entered amount columns from the beq columns
955 -- and the others from the regular columns. Transfer
956 -- accounted amounts from the beq columns for all
957 -- currencies. (They should be null for STAT.)
958 l_sqlbuf := l_sqlbuf || '
959 decode(mult.multiplier,
960 1, decode(bal.currency_code,
961 :g_funct_curr,
962 decode(lmper.from_eff_per_num,
963 :g_first_eff_per_num,
964 sum(nvl(bal.begin_balance_dr_beq, 0)
965 + nvl(bal.period_net_dr_beq, 0)),
966 sum(nvl(bal.period_net_dr_beq,0))),
967 decode(lmper.from_eff_per_num,
968 :g_first_eff_per_num,
969 sum(nvl(bal.begin_balance_dr, 0)
970 + nvl(bal.period_net_dr, 0)),
971 sum(nvl(bal.period_net_dr,0)))),
972 NULL) entered_dr,
973 decode(mult.multiplier,
974 2, decode(bal.currency_code,
975 :g_funct_curr,
976 decode(lmper.from_eff_per_num,
977 :g_first_eff_per_num,
978 sum(nvl(bal.begin_balance_cr_beq, 0)
979 + nvl(bal.period_net_cr_beq, 0)),
980 sum(nvl(bal.period_net_cr_beq,0))),
981 decode(lmper.from_eff_per_num,
982 :g_first_eff_per_num,
983 sum(nvl(bal.begin_balance_cr, 0)
984 + nvl(bal.period_net_cr, 0)),
985 sum(nvl(bal.period_net_cr,0)))),
986 NULL) entered_cr,
987 decode(mult.multiplier,
988 1, decode(lmper.from_eff_per_num,
989 :g_first_eff_per_num,
990 sum(nvl(bal.begin_balance_dr_beq, 0)
991 + nvl(bal.period_net_dr_beq, 0)),
992 sum(nvl(bal.period_net_dr_beq,0))),
993 NULL) accounted_dr,
994 decode(mult.multiplier,
995 2, decode(lmper.from_eff_per_num,
996 :g_first_eff_per_num,
997 sum(nvl(bal.begin_balance_cr_beq, 0)
998 + nvl(bal.period_net_cr_beq, 0)),
999 sum(nvl(bal.period_net_cr_beq,0))),
1000 NULL) accounted_cr ';
1001
1002 -- Incremental and cumulative balances. Just transfer
1003 -- the amounts in the normal columns.
1004 ELSE
1005 l_sqlbuf := l_sqlbuf || '
1006 decode(mult.multiplier,
1007 1, decode(lmper.from_eff_per_num,
1008 :g_first_eff_per_num,
1009 sum(nvl(bal.begin_balance_dr, 0)
1010 + nvl(bal.period_net_dr, 0)),
1011 sum(nvl(bal.period_net_dr,0))),
1012 NULL) entered_dr,
1013 decode(mult.multiplier,
1014 2, decode(lmper.from_eff_per_num,
1015 :g_first_eff_per_num,
1016 sum(nvl(bal.begin_balance_cr, 0)
1017 + nvl(bal.period_net_cr, 0)),
1018 sum(nvl(bal.period_net_cr,0))),
1019 NULL) entered_cr,
1020 decode(mult.multiplier,
1021 1, decode(lmper.from_eff_per_num,
1022 :g_first_eff_per_num,
1023 sum(nvl(bal.begin_balance_dr, 0)
1024 + nvl(bal.period_net_dr, 0)),
1025 sum(nvl(bal.period_net_dr,0))),
1026 NULL) accounted_dr,
1027 decode(mult.multiplier,
1028 2, decode(lmper.from_eff_per_num,
1029 :g_first_eff_per_num,
1030 sum(nvl(bal.begin_balance_cr, 0)
1031 + nvl(bal.period_net_cr, 0)),
1032 sum(nvl(bal.period_net_cr,0))),
1033 NULL) accounted_cr ';
1034 END IF;
1035
1036 ELSE
1037 IF (g_from_efb_on) THEN
1038 IF (g_from_eff_per_num = g_first_eff_per_num) THEN
1039 -- Full, with entered functional balances, transferring
1040 -- YTD balances. Transfer functional entered amount columns
1041 -- from the beq columns and the others from the regular columns.
1042 -- Transfer accounted amounts from the beq columns for all
1043 -- currencies. (They should be null for STAT.)
1044 l_sqlbuf := l_sqlbuf || '
1045 decode(mult.multiplier,
1046 1, decode(bal.currency_code,
1047 :g_funct_curr,
1048 nvl(bal.begin_balance_dr_beq, 0)
1049 + nvl(bal.period_net_dr_beq, 0),
1050 nvl(bal.begin_balance_dr, 0)
1051 + nvl(bal.period_net_dr, 0)),
1052 NULL) entered_dr,
1053 decode(mult.multiplier,
1054 2, decode(bal.currency_code,
1055 :g_funct_curr,
1056 nvl(bal.begin_balance_cr_beq, 0)
1057 + nvl(bal.period_net_cr_beq, 0),
1058 nvl(bal.begin_balance_cr, 0)
1059 + nvl(bal.period_net_cr, 0)),
1060 NULL) entered_cr,
1061 decode(mult.multiplier, 1, nvl(bal.begin_balance_dr_beq, 0)
1062 + nvl(bal.period_net_dr_beq, 0),
1063 NULL) accounted_dr,
1064 decode(mult.multiplier, 2, nvl(bal.begin_balance_cr_beq, 0)
1065 + nvl(bal.period_net_cr_beq, 0),
1066 NULL) accounted_cr ';
1067 ELSE
1068 -- Full, with entered functional balances, transferring
1069 -- PTD balances. Transfer functional entered amount columns
1070 -- from the beq columns and the others from the regular columns.
1071 -- Transfer accounted amounts from the beq columns for all
1072 -- currencies. (They should be null for STAT.)
1073 l_sqlbuf := l_sqlbuf || '
1074 decode(mult.multiplier,
1075 1, decode(bal.currency_code,
1076 :g_funct_curr, nvl(bal.period_net_dr_beq, 0),
1077 nvl(bal.period_net_dr, 0)),
1078 NULL) entered_dr,
1079 decode(mult.multiplier,
1080 2, decode(bal.currency_code,
1081 :g_funct_curr, nvl(bal.period_net_cr_beq, 0),
1082 nvl(bal.period_net_cr, 0)),
1083 NULL) entered_cr,
1084 decode(mult.multiplier, 1, nvl(bal.period_net_dr_beq, 0),
1085 NULL) accounted_dr,
1086 decode(mult.multiplier, 2, nvl(bal.period_net_cr_beq, 0),
1087 NULL) accounted_cr ';
1088 END IF;
1089 ELSE
1090 IF (g_from_eff_per_num = g_first_eff_per_num) THEN
1091 -- Full, with cumulative balances, transferring
1092 -- YTD balances. Just use the regular columns.
1093 l_sqlbuf := l_sqlbuf || '
1094 decode(mult.multiplier, 1, nvl(bal.begin_balance_dr, 0)
1095 + nvl(bal.period_net_dr, 0),
1096 NULL) entered_dr,
1097 decode(mult.multiplier, 2, nvl(bal.begin_balance_cr, 0)
1098 + nvl(bal.period_net_cr, 0),
1099 NULL) entered_cr,
1100 decode(mult.multiplier, 1, nvl(bal.begin_balance_dr, 0)
1101 + nvl(bal.period_net_dr, 0),
1102 NULL) accounted_dr,
1103 decode(mult.multiplier, 2, nvl(bal.begin_balance_cr, 0)
1104 + nvl(bal.period_net_cr, 0),
1105 NULL) accounted_cr ';
1106 ELSE
1107 -- Full, with cumulative balances, transferring
1108 -- PTD balances. Just use the regular columns.
1109 l_sqlbuf := l_sqlbuf || '
1110 decode(mult.multiplier, 1, nvl(bal.period_net_dr, 0),
1111 NULL) entered_dr,
1112 decode(mult.multiplier, 2, nvl(bal.period_net_cr, 0),
1113 NULL) entered_cr,
1114 decode(mult.multiplier, 1, nvl(bal.period_net_dr, 0),
1115 NULL) accounted_dr,
1116 decode(mult.multiplier, 2, nvl(bal.period_net_cr, 0),
1117 NULL) accounted_cr ';
1118 END IF;
1119 END IF;
1120 END IF;
1121
1122 IF (g_transfer_mode = C_INCREMENTAL) THEN
1123 FOR i IN 1 .. g_to_segs.LAST LOOP
1124 l_sqlbuf := l_sqlbuf ||
1125 ', max(cc.' || g_to_segs(i) || ') '|| g_to_segs(i);
1126 END LOOP;
1127 ELSIF (g_coa_mapping_id IS NULL) THEN
1128 FOR i IN 1 .. g_to_segs.LAST LOOP
1129 l_sqlbuf := l_sqlbuf ||
1130 ', cc.' || g_to_segs(i);
1131 END LOOP;
1132 END IF;
1133
1134 IF (g_transfer_mode = C_INCREMENTAL) THEN
1135 l_sqlbuf := l_sqlbuf || '
1136 FROM gl_balances_delta bal,
1137 gl_xfr_led_map_periods lmper, gl_row_multipliers mult, ';
1138
1139 IF (g_coa_mapping_id IS NOT NULL) THEN
1140 l_sqlbuf := l_sqlbuf || '
1141 gl_xfr_ccid_mappings map,
1142 gl_code_combinations fromcc, ';
1143 END IF;
1144
1145 IF (g_cal_mapping_id IS NOT NULL) THEN
1146 l_sqlbuf := l_sqlbuf || '
1147 gl_xfr_period_mappings per, ';
1148 END IF;
1149
1150 l_sqlbuf := l_sqlbuf || '
1151 gl_code_combinations cc
1152 WHERE bal.delta_run_id between :low and :high
1153 AND bal.ledger_id = :from_ledger_id ';
1154
1155 IF (g_from_balance_alc) THEN
1156 l_sqlbuf := l_sqlbuf || '
1157 AND bal.currency_code = :g_funct_curr
1158 AND bal.translated_flag IN (''Y'', ''N'') ';
1159 ELSIF (NOT g_from_efb_on) THEN
1160 l_sqlbuf := l_sqlbuf || '
1161 AND bal.currency_code IN (:g_funct_curr, ''STAT'')
1162 AND bal.translated_flag IS NULL ';
1163 ELSE
1164 l_sqlbuf := l_sqlbuf || '
1165 AND :g_funct_curr IS NOT NULL
1166 AND nvl(bal.translated_flag, ''R'') = ''R'' ';
1167 END IF;
1168
1169 l_sqlbuf := l_sqlbuf || '
1170 AND bal.template_id IS NULL
1171 AND bal.actual_flag = ''A'' ';
1172
1173 IF (g_coa_mapping_id IS NOT NULL) THEN
1174 l_sqlbuf := l_sqlbuf || '
1175 AND map.ledger_mapping_id(+) = :lgr_mapping_id
1176 AND map.from_ccid(+) = bal.code_combination_id
1177 AND map.period_name(+) = bal.period_name
1178 AND fromcc.code_combination_id = bal.code_combination_id
1179 AND cc.code_combination_id(+) = map.to_ccid ';
1180 ELSE
1181 l_sqlbuf := l_sqlbuf || '
1182 AND :lgr_mapping_id IS NOT NULL
1183 AND cc.code_combination_id = bal.code_combination_id ';
1184 END IF;
1185
1186 IF (g_cal_mapping_id IS NOT NULL) THEN
1187 l_sqlbuf := l_sqlbuf || '
1188 AND per.calendar_mapping_id = :cal_mapping_id
1189 AND per.from_period_name = bal.period_name ';
1190 ELSE
1191 l_sqlbuf := l_sqlbuf || '
1192 AND :cal_mapping_id IS NULL ';
1193 END IF;
1194
1195 l_sqlbuf := l_sqlbuf || '
1196 AND lmper.ledger_mapping_id = :lgr_mapping_id
1197 AND lmper.from_period_name = bal.period_name
1198 AND lmper.full_transfer_done_flag = ''Y''
1199 AND lmper.max_delta_run_id < bal.delta_run_id
1200 AND mult.multiplier between 1 and 2
1201 GROUP BY bal.ledger_id, bal.code_combination_id,
1202 bal.period_name, bal.currency_code,
1203 lmper.from_eff_per_num, lmper.temp_date,
1204 mult.multiplier, ';
1205
1206 IF (g_coa_mapping_id IS NOT NULL) THEN
1207 l_sqlbuf := l_sqlbuf || '
1208 map.to_ccid, ';
1209 END IF;
1210
1211 IF (g_cal_mapping_id IS NOT NULL) THEN
1212 l_sqlbuf := l_sqlbuf || '
1213 per.to_period_name ';
1214 ELSE
1215 l_sqlbuf := l_sqlbuf || '
1216 bal.period_name ';
1217 END IF;
1218
1219 l_sqlbuf := l_sqlbuf || '
1220 ) dat';
1221
1222 ELSE
1223 l_sqlbuf := l_sqlbuf || '
1224 FROM gl_balances bal, gl_row_multipliers mult ';
1225
1226 IF (g_coa_mapping_id IS NULL) THEN
1227 l_sqlbuf := l_sqlbuf || '
1228 , gl_code_combinations cc ';
1229 ELSE
1230 l_sqlbuf := l_sqlbuf || '
1231 , gl_code_combinations fromcc ';
1232 END IF;
1233
1234 IF (g_cal_mapping_id IS NULL) THEN
1235 l_sqlbuf := l_sqlbuf || '
1236 WHERE :cal_mapping_id IS NULL
1237 AND bal.period_name = :from_period_name
1238 AND bal.ledger_id = :from_ledger_id ';
1239 ELSE
1240 l_sqlbuf := l_sqlbuf || '
1241 , gl_xfr_period_mappings per
1242 WHERE per.calendar_mapping_id = :cal_mapping_id
1243 AND per.from_period_name = :from_period_name
1244 AND bal.ledger_id = :from_ledger_id
1245 AND bal.period_name = per.from_period_name ';
1246 END IF;
1247
1248 IF (g_from_balance_alc) THEN
1249 l_sqlbuf := l_sqlbuf || '
1250 AND bal.currency_code = :g_funct_curr
1251 AND bal.translated_flag IN (''Y'', ''N'') ';
1252 ELSIF (NOT g_from_efb_on) THEN
1253 l_sqlbuf := l_sqlbuf || '
1254 AND bal.currency_code IN (:g_funct_curr, ''STAT'')
1255 AND bal.translated_flag IS NULL ';
1256 ELSE
1257 l_sqlbuf := l_sqlbuf || '
1258 AND :g_funct_curr IS NOT NULL
1259 AND nvl(bal.translated_flag, ''R'') = ''R'' ';
1260 END IF;
1261
1262 l_sqlbuf := l_sqlbuf || '
1263 AND bal.template_id IS NULL
1264 AND bal.actual_flag = ''A'' ';
1265
1266 IF (g_coa_mapping_id IS NULL) THEN
1267 l_sqlbuf := l_sqlbuf || '
1268 AND cc.code_combination_id = bal.code_combination_id';
1269 ELSE
1270 l_sqlbuf := l_sqlbuf || '
1271 AND fromcc.code_combination_id = bal.code_combination_id';
1272 END IF;
1273
1274 l_sqlbuf := l_sqlbuf || '
1275 AND mult.multiplier between 1 and 2) dat';
1276 END IF;
1277
1278 l_sqlbuf := l_sqlbuf || '
1279 WHERE nvl(dat.entered_dr, 0) <> 0
1280 OR nvl(dat.entered_cr, 0) <> 0
1281 OR nvl(dat.accounted_dr, 0) <> 0
1282 OR nvl(dat.accounted_cr, 0) <> 0 ';
1283
1284 IF (g_debug_mode) THEN
1285 print_logfile('SQL Statement to insert interface rows: ');
1286 gl_fusion_transfer_pkg.print_sql_logfile(l_sqlbuf);
1287 END IF;
1288
1289 ---
1290 --- Execute it
1291 ---
1292 IF (g_transfer_mode = C_INCREMENTAL) THEN
1293 IF (g_from_efb_on) THEN
1294 EXECUTE IMMEDIATE l_sqlbuf
1295 USING g_to_ledger_id, g_to_ledger_id, g_je_source_key, g_user_id,
1296 l_name, l_name, g_request_id, g_group_id, g_system_id, g_fusion_user,
1297 g_funct_curr, g_first_eff_per_num, g_first_eff_per_num,
1298 g_funct_curr, g_first_eff_per_num, g_first_eff_per_num,
1299 g_first_eff_per_num, g_first_eff_per_num,
1300 g_low_delta_run_id, g_high_delta_run_id,
1301 g_from_ledger_id, g_funct_curr, g_lgr_mapping_id,
1302 g_cal_mapping_id, g_lgr_mapping_id;
1303 ELSE
1304 EXECUTE IMMEDIATE l_sqlbuf
1305 USING g_to_ledger_id, g_to_ledger_id, g_je_source_key, g_user_id,
1306 l_name, l_name, g_request_id, g_group_id, g_system_id, g_fusion_user,
1307 g_first_eff_per_num, g_first_eff_per_num,
1308 g_first_eff_per_num, g_first_eff_per_num,
1309 g_low_delta_run_id, g_high_delta_run_id, g_from_ledger_id,
1310 g_funct_curr, g_lgr_mapping_id,
1311 g_cal_mapping_id, g_lgr_mapping_id;
1312 END IF;
1313 ELSE
1314 IF (g_from_efb_on) THEN
1315 EXECUTE IMMEDIATE l_sqlbuf
1316 USING g_to_ledger_id, g_to_ledger_id, g_je_source_key, g_user_id,
1317 l_name, l_name, g_request_id, g_group_id,
1318 g_system_id, g_fusion_user, l_eff_date, g_funct_curr, g_funct_curr,
1319 g_cal_mapping_id, g_from_period, g_from_ledger_id,
1320 g_funct_curr;
1321 ELSE
1322 EXECUTE IMMEDIATE l_sqlbuf
1323 USING g_to_ledger_id, g_to_ledger_id, g_je_source_key, g_user_id,
1324 l_name, l_name, g_request_id, g_group_id,
1325 g_system_id, g_fusion_user, l_eff_date, g_cal_mapping_id, g_from_period,
1326 g_from_ledger_id, g_funct_curr;
1327 END IF;
1328 END IF;
1329
1330 g_interface_row_count := SQL%ROWCOUNT;
1331
1332 gl_message.write_log('GLFUXFR001', 2,
1333 'ROW_COUNT', g_interface_row_count,
1334 'TABLE', g_interface_table_name);
1335
1336 success_exit(l_log_module);
1337 END insert_interface_rows;
1338
1339
1340 --=============================================================================
1341 --
1342 -- Name: do_ccid_mapping
1343 -- Description:
1344 -- This routine does account mapping for anything that hasn't been
1345 -- mapped yet. For full mode, that will be all combinations, while
1346 -- in incremental mode, it will be a subset.
1347 --
1348 -- Parameters:
1349 -- *** NONE ***
1350 --=============================================================================
1351 PROCEDURE do_ccid_mapping IS
1352 l_log_module VARCHAR2(240);
1353 l_sqlbuf VARCHAR2(20000);
1354 l_row_count NUMBER;
1355 l_dummy NUMBER;
1356 BEGIN
1357
1358 l_log_module := C_DEFAULT_MODULE||'.do_ccid_mapping';
1359 enter_routine(l_log_module);
1360
1361 ---
1362 --- Insert all of the ccids to be mapped into gl_accounts_map_int_gt
1363 ---
1364 l_sqlbuf :=
1365 'INSERT INTO gl_accts_map_int_gt
1366 (from_ccid, coa_mapping_id)
1367 SELECT DISTINCT int.code_combination_id, :coa_map
1368 FROM ' || g_interface_table_name || ' int
1369 WHERE int.request_id = :request_id
1370 AND int.code_combination_id IS NOT NULL
1371 AND int.status = ''MAP'' ';
1372
1373 IF (g_debug_mode) THEN
1374 print_logfile('SQL Statement to insert into gl_accounts_map_int_gt: ');
1375 gl_fusion_transfer_pkg.print_sql_logfile(l_sqlbuf);
1376 END IF;
1377
1378 EXECUTE IMMEDIATE l_sqlbuf USING g_coa_mapping_id, g_request_id;
1379
1380 l_row_count := SQL%ROWCOUNT;
1381
1382 gl_message.write_log('GLFUXFR001', 2,
1383 'ROW_COUNT', l_row_count,
1384 'TABLE', 'gl_accounts_map_int_gt');
1385
1386 IF (l_row_count = 0) THEN
1387 success_exit(l_log_module);
1388 RETURN;
1389 END IF;
1390
1391
1392 ---
1393 --- Do the mapping
1394 ---
1395
1396 BEGIN
1397 GL_ACCOUNTS_MAP_GRP.MAP(
1398 mapping_name => g_coa_mapping_name,
1399 create_ccid => TRUE,
1400 debug => g_debug_mode);
1401 EXCEPTION
1402 WHEN GL_ACCOUNTS_MAP_GRP.GL_INVALID_MAPPING_RULES THEN
1403 gl_message.write_log('GLFUXFR002', 1,
1404 'COA_MAPPING_NAME', g_coa_mapping_name);
1405 gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_INVALID_RULES');
1406 failure_exit(l_log_module);
1407 RAISE warning_exception;
1408 WHEN GL_ACCOUNTS_MAP_GRP.GL_DISABLED_MAPPING THEN
1409 gl_message.write_log('GLFUXFR003', 1,
1410 'COA_MAPPING_NAME', g_coa_mapping_name);
1411 gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_OUT_OF_DATE');
1412 failure_exit(l_log_module);
1413 RAISE warning_exception;
1414 WHEN OTHERS THEN
1415 gl_message.write_log('GLFUXFR004', 1,
1416 'COA_MAPPING_NAME', g_coa_mapping_name);
1417 gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_UNEXPECTED_ERROR');
1418 failure_exit(l_log_module);
1419 RAISE warning_exception;
1420 END;
1421
1422
1423 ---
1424 --- Look for chart of accounts mapping errors. If there are errors, then
1425 --- stop here.
1426 ---
1427
1428 BEGIN
1429 SELECT 1
1430 INTO l_dummy
1431 FROM dual
1432 WHERE EXISTS
1433 (SELECT 'has errors'
1434 FROM gl_accts_map_int_gt
1435 WHERE error_code IS NOT NULL);
1436
1437 gl_message.write_log('GLFUXFR005', 1,
1438 'COA_MAPPING_NAME', g_coa_mapping_name);
1439 gl_fusion_transfer_pkg.insert_tracking_data('COA_MAP_UNMAPPED');
1440 success_exit(l_log_module);
1441 RAISE warning_exception;
1442 EXCEPTION
1443 WHEN NO_DATA_FOUND THEN
1444 NULL;
1445 END;
1446
1447
1448 ---
1449 --- Insert the mapping results into gl_ccid_mappings so we don't have
1450 --- to map again. If we are running in incremental mode, we need to
1451 --- get these from the interface table so we can get the period
1452 --- information. If we are running in batch mode, we can get them
1453 --- from gl_accts_map_int_gt directly.
1454 ---
1455 --- Note that if we are running in batch mode, we need to delete the
1456 --- old mappings as they may have changed. That would be one
1457 --- reason for them to rerun in batch mode.
1458 ---
1459 IF (g_transfer_mode = C_INCREMENTAL) THEN
1460 l_sqlbuf :=
1461 'INSERT into gl_xfr_ccid_mappings
1462 (ledger_mapping_id, from_ccid, to_ccid, period_name,
1463 creation_date, created_by, last_update_date, last_updated_by,
1464 last_update_login)
1465 SELECT DISTINCT :g_lgr_mapping_id, map.from_ccid, map.to_ccid, int.reference23,
1466 sysdate, :user_id, sysdate, :user_id, :login_id
1467 FROM gl_accts_map_int_gt map,
1468 '|| g_interface_table_name || ' int
1469 WHERE int.request_id = :request_id
1470 AND int.code_combination_id = map.from_ccid
1471 AND int.status = ''MAP''
1472 AND map.coa_mapping_id = :coa_map_id ';
1473
1474 IF (g_debug_mode) THEN
1475 print_logfile('SQL Statement to insert into gl_xfr_ccid_mappings');
1476 gl_fusion_transfer_pkg.print_sql_logfile(l_sqlbuf);
1477 END IF;
1478
1479 EXECUTE IMMEDIATE l_sqlbuf
1480 USING g_lgr_mapping_id, g_user_id, g_user_id, g_login_id,
1481 g_request_id, g_coa_mapping_id;
1482
1483 ELSE
1484 DELETE gl_xfr_ccid_mappings
1485 WHERE ledger_mapping_id = g_lgr_mapping_id
1486 AND period_name = g_from_period;
1487
1488 INSERT into gl_xfr_ccid_mappings
1489 (ledger_mapping_id, from_ccid, to_ccid, period_name,
1490 creation_date, created_by, last_update_date, last_updated_by,
1491 last_update_login)
1492 SELECT g_lgr_mapping_id, from_ccid, to_ccid, g_from_period,
1493 sysdate, g_user_id, sysdate, g_user_id, g_login_id
1494 FROM gl_accts_map_int_gt map;
1495 END IF;
1496
1497 l_row_count := SQL%ROWCOUNT;
1498
1499 gl_message.write_log('GLFUXFR001', 2,
1500 'ROW_COUNT', l_row_count,
1501 'TABLE', 'gl_xfr_ccid_mappings');
1502
1503
1504 ---
1505 --- Since there weren't any errors, go ahead and update the
1506 --- interface rows
1507 ---
1508 l_sqlbuf :=
1509 'UPDATE '|| g_interface_table_name || ' int
1510 SET (code_combination_id ';
1511
1512 FOR i IN 1 .. g_to_segs.LAST LOOP
1513 l_sqlbuf := l_sqlbuf || '
1514 , int.' || g_to_segs(i);
1515 END LOOP;
1516
1517 l_sqlbuf := l_sqlbuf || '
1518 ) = (SELECT NULL ';
1519
1520 FOR i IN 1 .. g_to_segs.LAST LOOP
1521 l_sqlbuf := l_sqlbuf || '
1522 , cc.' || g_to_segs(i);
1523 END LOOP;
1524
1525 l_sqlbuf := l_sqlbuf || '
1526 FROM gl_accts_map_int_gt map,
1527 gl_code_combinations cc
1528 WHERE map.coa_mapping_id = :coa_map_id
1529 And map.from_ccid = int.code_combination_id
1530 AND cc.code_combination_id = map.to_ccid)
1531 WHERE int.request_id = :request_id
1532 AND int.code_combination_id IS NOT NULL
1533 AND int.status = ''MAP'' ';
1534
1535 IF (g_debug_mode) THEN
1536 print_logfile('SQL Statement to update '|| g_interface_table_name);
1537 gl_fusion_transfer_pkg.print_sql_logfile(l_sqlbuf);
1538 END IF;
1539
1540 EXECUTE IMMEDIATE l_sqlbuf USING g_coa_mapping_id, g_request_id;
1541
1542 l_row_count := SQL%ROWCOUNT;
1543
1544 gl_message.write_log('GLFUXFR006', 2,
1545 'ROW_COUNT', l_row_count,
1546 'TABLE', g_interface_table_name);
1547
1548 success_exit(l_log_module);
1549 RETURN;
1550 END do_ccid_mapping;
1551
1552
1553 --=============================================================================
1554 --
1555 -- Name: insert_tracking_data
1556 -- Description:
1557 -- This routine inserts the necessary tracking data for fusion
1558 --
1559 -- Parameters:
1560 -- p_status_code Resulting status code
1561 --=============================================================================
1562 PROCEDURE insert_tracking_data (p_status_code VARCHAR2) IS
1563 l_log_module VARCHAR2(240);
1564 BEGIN
1565
1566 l_log_module := C_DEFAULT_MODULE||'.insert_tracking_data';
1567 enter_routine(l_log_module);
1568
1569 IF (g_system_id IS NULL) THEN
1570 gl_fusion_transfer_pkg.get_system_id;
1571 g_system_id := nvl(g_system_id, -1);
1572 END IF;
1573
1574 INSERT INTO gl_xfr_tracking
1575 (xfer_request_id, je_source_key, group_id,
1576 system_id, interface_table_name, ledger_id, status_code,
1577 original_first_period,
1578 creation_date, created_by, last_update_date, last_updated_by,
1579 last_update_login)
1580 VALUES (g_request_id, nvl(g_je_source_key, ' '), nvl(g_group_id, -1),
1581 g_system_id, g_interface_table_name,
1582 g_to_ledger_id, p_status_code,
1583 g_original_first_period,
1584 sysdate, g_user_id, sysdate, g_user_id, g_login_id);
1585
1586 success_exit(l_log_module);
1587 RETURN;
1588 END insert_tracking_data;
1589
1590
1591 --=============================================================================
1592 --
1593 -- Name: update_transfer_tables
1594 -- Description:
1595 -- This routine does any necessary maintenance on gl_xfr_ledger_mappings
1596 -- and gl_xfr_led_map_periods
1597 --
1598 -- Parameters:
1599 -- *** NONE ***
1600 --=============================================================================
1601 PROCEDURE update_transfer_tables IS
1602 l_log_module VARCHAR2(240);
1603 BEGIN
1604
1605 l_log_module := C_DEFAULT_MODULE||'.update_transfer_tables';
1606 enter_routine(l_log_module);
1607
1608 IF (g_transfer_mode = C_INCREMENTAL) THEN
1609 UPDATE gl_xfr_ledger_mappings
1610 SET max_delta_run_id = g_high_delta_run_id,
1611 last_update_date = sysdate,
1612 last_updated_by = g_user_id,
1613 last_update_login = g_login_id
1614 WHERE ledger_mapping_id = g_lgr_mapping_id;
1615
1616 ELSE
1617
1618 ---
1619 --- If the first period ever transferred was changed, then record
1620 --- the new first period
1621 ---
1622 IF ( g_first_period_changed
1623 OR (g_from_eff_per_num = g_first_eff_per_num)) THEN
1624 UPDATE gl_xfr_ledger_mappings
1625 SET first_eff_per_num = g_from_eff_per_num,
1626 last_update_date = sysdate,
1627 last_updated_by = g_user_id,
1628 last_update_login = g_login_id
1629 WHERE ledger_mapping_id = g_lgr_mapping_id;
1630 END IF;
1631
1632 INSERT INTO gl_xfr_led_map_periods
1633 (ledger_mapping_id, from_period_name,
1634 from_eff_per_num, full_transfer_done_flag,
1635 max_delta_run_id,
1636 creation_date, created_by, last_update_date,
1637 last_updated_by, last_update_login)
1638 SELECT g_lgr_mapping_id, g_from_period,
1639 g_from_eff_per_num, 'Y',
1640 gl_balances_delta_s.nextval,
1641 sysdate, g_user_id,
1642 sysdate, g_user_id,
1643 g_login_id
1644 FROM dual
1645 WHERE NOT EXISTS
1646 (SELECT 1
1647 FROM gl_xfr_led_map_periods
1648 WHERE ledger_mapping_id = g_lgr_mapping_id
1649 AND from_period_name = g_from_period);
1650
1651 IF (SQL%ROWCOUNT = 0) THEN
1652 UPDATE gl_xfr_led_map_periods
1653 SET full_transfer_done_flag = 'Y',
1654 max_delta_run_id = gl_balances_delta_s.nextval,
1655 last_update_date = sysdate,
1656 last_updated_by = g_user_id,
1657 last_update_login = g_login_id
1658 WHERE ledger_mapping_id = g_lgr_mapping_id
1659 AND from_period_name = g_from_period;
1660 END IF;
1661
1662 INSERT INTO gl_track_delta_balances
1663 (ledger_id, program_code, period_name, actual_flag,
1664 extract_level_code, currency_type_code,
1665 enabled_flag, last_update_date, last_updated_by,
1666 creation_date, created_by, last_update_login)
1667 SELECT
1668 g_from_ledger_id, 'FUSIONTRANSFER', g_from_period, 'A',
1669 'DTL', 'B',
1670 'Y', sysdate, g_user_id,
1671 sysdate, g_user_id, g_login_id
1672 FROM dual
1673 WHERE NOT EXISTS (
1674 SELECT 'row exists'
1675 FROM gl_track_delta_balances t2
1676 WHERE t2.ledger_id = g_from_ledger_id
1677 AND t2.program_code = 'FUSIONTRANSFER'
1678 AND t2.period_name = g_from_period
1679 AND t2.actual_flag = 'A'
1680 AND t2.extract_level_code = 'DTL');
1681 END IF;
1682
1683 success_exit(l_log_module);
1684 RETURN;
1685 END update_transfer_tables;
1686
1687
1688 --=============================================================================
1689 --
1690 -- Name: get_mapping_info
1691 -- Description:
1692 -- This routine retrieves the ledger and mapping information
1693 -- information
1694 --
1695 -- Parameters:
1696 -- *** NONE ***
1697 --=============================================================================
1698 PROCEDURE get_mapping_info IS
1699 l_log_module VARCHAR2(240);
1700 l_dummy NUMBER;
1701 BEGIN
1702
1703 l_log_module := C_DEFAULT_MODULE||'.get_mapping_info';
1704 enter_routine(l_log_module);
1705
1706 ---
1707 --- Get from ledger information
1708 ---
1709 gl_fusion_transfer_pkg.get_ledger_info(
1710 p_ledger_shortname => g_from_ledger_shortname,
1711 p_ledger_id => g_from_ledger_id,
1712 p_ledger_name => g_from_ledger_name,
1713 p_coa_id => g_from_coa_id,
1714 p_efb_on => g_from_efb_on,
1715 p_curr_code => g_funct_curr,
1716 p_balance_alc => g_from_balance_alc);
1717
1718 ---
1719 --- Get mapping set information
1720 ---
1721 BEGIN
1722 SELECT lgr.to_ledger_id, lgr.ledger_mapping_id,
1723 map.name, map.coa_mapping_id,
1724 nvl(lgr.first_eff_per_num, 99999999),
1725 map.to_coa_id, lgr.calendar_mapping_id,
1726 tlgr.name
1727 INTO g_to_ledger_id, g_lgr_mapping_id,
1728 g_coa_mapping_name, g_coa_mapping_id,
1729 g_first_eff_per_num, g_to_coa_id,
1730 g_cal_mapping_id, g_to_ledger_name
1731 FROM gl_xfr_ledger_mappings lgr,
1732 gl_coa_mappings map,
1733 gl_xfr_ledgers tlgr
1734 WHERE lgr.from_ledger_id = g_from_ledger_id
1735 AND lgr.from_ledger_currency = g_funct_curr
1736 AND map.coa_mapping_id(+) = lgr.coa_mapping_id
1737 AND tlgr.ledger_id = lgr.to_ledger_id;
1738
1739 EXCEPTION
1740 WHEN NO_DATA_FOUND THEN
1741 print_logfile('Please define a ledger mapping for this ledger');
1742 failure_exit(l_log_module);
1743 RAISE fatal_exception;
1744 END;
1745
1746 ---
1747 --- If no chart of accounts mapping, then assume the chart of
1748 --- accounts are the same.
1749 ---
1750 IF (g_coa_mapping_id IS NULL) THEN
1751 g_to_coa_id := g_from_coa_id;
1752 END IF;
1753
1754 print_variable('g_to_ledger_id', g_to_ledger_id, l_log_module);
1755 print_variable('g_lgr_mapping_id', g_lgr_mapping_id, l_log_module);
1756 print_variable('g_coa_mapping_name', g_coa_mapping_name, l_log_module);
1757 print_variable('g_coa_mapping_id', g_coa_mapping_id, l_log_module);
1758 print_variable('g_cal_mapping_id', g_cal_mapping_id, l_log_module);
1759 print_variable('g_first_eff_per_num', g_first_eff_per_num, l_log_module);
1760
1761 print_logfile('To ledger id: '|| to_char(g_to_ledger_id));
1762 print_logfile('Ledger mapping id: '|| to_char(g_lgr_mapping_id));
1763 print_logfile('Chart of accounts mapping id: '|| to_char(g_coa_mapping_id));
1764 print_logfile('Period mapping id: '|| to_char(g_cal_mapping_id));
1765 print_logfile('First effective period number: '
1766 || to_char(g_first_eff_per_num));
1767
1768
1769 ---
1770 --- Get the effective period number for the from period
1771 ---
1772 IF (g_transfer_mode = C_FULL) THEN
1773 SELECT effective_period_num
1774 INTO g_from_eff_per_num
1775 FROM gl_period_statuses
1776 WHERE application_id = 101
1777 AND ledger_id = g_from_ledger_id
1778 AND period_name = g_from_period;
1779
1780 print_variable('g_from_eff_per_num', g_from_eff_per_num, l_log_module);
1781 END IF;
1782
1783 ---
1784 --- If we are running in full mode and there is a calendar mapping, make
1785 --- sure the from period is mapped to a to period
1786 ---
1787 IF (g_transfer_mode = C_FULL) THEN
1788 IF (g_cal_mapping_id IS NOT NULL) THEN
1789 BEGIN
1790 SELECT to_period_name
1791 INTO g_to_period
1792 FROM gl_xfr_period_mappings
1793 WHERE calendar_mapping_id = g_cal_mapping_id
1794 AND from_period_name = g_from_period;
1795 EXCEPTION
1796 WHEN NO_DATA_FOUND THEN
1797 print_logfile('Please define a period mapping for this period');
1798 failure_exit(l_log_module);
1799 RAISE fatal_exception;
1800 END;
1801
1802 ---
1803 --- If we are running in full mode and there isn't a calendar mapping, make
1804 --- sure the from period is a valid period for that ledger
1805 ---
1806 ELSE
1807 BEGIN
1808 SELECT period_name
1809 INTO g_to_period
1810 FROM gl_xfr_ledgers lgr, gl_xfr_periods per
1811 WHERE lgr.ledger_id = g_to_ledger_id
1812 AND per.period_set_name = lgr.period_set_name
1813 AND per.period_type = lgr.accounted_period_type
1814 AND per.period_name = g_from_period;
1815 EXCEPTION
1816 WHEN NO_DATA_FOUND THEN
1817 gl_message.write_log('GLFUXFR008', 2,
1818 'GENERAL_LEDGER_PERIOD_NAME', g_from_period,
1819 'LEDGER_NAME', g_from_ledger_name);
1820 gl_fusion_transfer_pkg.insert_tracking_data('BAD_PERIOD');
1821 failure_exit(l_log_module);
1822 RAISE warning_exception;
1823 END;
1824 END IF;
1825 END IF;
1826
1827 ---
1828 --- Get from coa information
1829 ---
1830 gl_fusion_transfer_pkg.get_coa_info(
1831 p_coa_id => g_from_coa_id,
1832 p_segs => g_from_segs,
1833 p_delim => g_from_delim);
1834
1835 ---
1836 --- Get to coa information
1837 ---
1838 gl_fusion_transfer_pkg.get_coa_info(
1839 p_coa_id => g_to_coa_id,
1840 p_segs => g_to_segs,
1841 p_delim => g_to_delim);
1842
1843 success_exit(l_log_module);
1844 END get_mapping_info;
1845
1846
1847 --=============================================================================
1848 --
1849 -- Name: get_ledger_info
1850 -- Description:
1851 -- This routine retrieves ledger information
1852 --
1853 -- Parameters:
1854 -- p_ledger_shortname Shortname of ledger or alc to get information from
1855 -- p_ledger_id Ledger to get the information for
1856 -- p_ledger_name Name of ledger
1857 -- p_coa_id Chart of accounts of ledger
1858 -- p_efb_on Is entered functional balances on for ledger?
1859 -- p_curr_code Currency of ledger
1860 -- p_balance_alc Is this a balance level reporting currency?
1861 --=============================================================================
1862 PROCEDURE get_ledger_info(p_ledger_shortname VARCHAR2,
1863 p_ledger_id NUMBER,
1864 p_ledger_name OUT NOCOPY VARCHAR2,
1865 p_coa_id OUT NOCOPY NUMBER,
1866 p_efb_on OUT NOCOPY BOOLEAN,
1867 p_curr_code OUT NOCOPY VARCHAR2,
1868 p_balance_alc OUT NOCOPY BOOLEAN) IS
1869 l_log_module VARCHAR2(240);
1870 l_dummy CHAR;
1871 BEGIN
1872
1873 l_log_module := C_DEFAULT_MODULE||'.get_ledger_info';
1874 enter_routine(l_log_module);
1875 print_variable('p_ledger_shortname', p_ledger_shortname, l_log_module);
1876 print_variable('p_ledger_id', p_ledger_id, l_log_module);
1877
1878 SELECT rel.target_ledger_name, lgr.chart_of_accounts_id, rel.target_currency_code,
1879 decode(rel.target_ledger_category_code,
1880 'ALC', decode(rel.relationship_type_code, 'BALANCE', 'Y', 'N'), 'N')
1881 INTO p_ledger_name, p_coa_id, p_curr_code, l_dummy
1882 FROM gl_ledger_relationships rel, gl_ledgers lgr
1883 WHERE rel.target_ledger_short_name = p_ledger_shortname
1884 AND rel.application_id = 101
1885 AND rel.source_ledger_id = rel.target_ledger_id
1886 AND lgr.ledger_id = rel.target_ledger_id;
1887
1888 print_variable('p_coa_id', p_coa_id, l_log_module);
1889
1890 IF (l_dummy = 'Y') THEN
1891 p_balance_alc := TRUE;
1892 ELSE
1893 p_balance_alc := FALSE;
1894 END IF;
1895
1896 l_dummy := 'N';
1897 IF (NOT p_balance_alc) THEN
1898 SELECT nvl(efb_upgrade_flag, 'N')
1899 INTO l_dummy
1900 FROM gl_system_usages;
1901 END IF;
1902
1903 IF (l_dummy = 'Y') THEN
1904 p_efb_on := TRUE;
1905
1906 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1907 trace
1908 (p_msg => 'entered functional balances on',
1909 p_level => C_LEVEL_PROCEDURE,
1910 p_module => l_log_module);
1911 END IF;
1912 ELSE
1913 p_efb_on := FALSE;
1914
1915 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1916 trace
1917 (p_msg => 'entered functional balances on',
1918 p_level => C_LEVEL_PROCEDURE,
1919 p_module => l_log_module);
1920 END IF;
1921 END IF;
1922
1923 success_exit(l_log_module);
1924 END get_ledger_info;
1925
1926 --=============================================================================
1927 --
1928 -- Name: get_coa_info
1929 -- Description:
1930 -- This routine retrieves coa information
1931 --
1932 -- Parameters:
1933 -- p_coa_id Chart of accounts id to get the information for
1934 -- p_segs Array of application column names in which
1935 -- the segments are stored.
1936 --=============================================================================
1937 PROCEDURE get_coa_info(p_coa_id NUMBER,
1938 p_segs OUT NOCOPY Var30List,
1939 p_delim OUT NOCOPY VARCHAR2) IS
1940 l_log_module VARCHAR2(240);
1941 l_segnums NumList;
1942 l_applcols Var30List;
1943 BEGIN
1944
1945 l_log_module := C_DEFAULT_MODULE||'.get_coa_info';
1946 enter_routine(l_log_module);
1947 print_variable('p_coa_id', p_coa_id, l_log_module);
1948
1949 ---
1950 --- Get segment information
1951 ---
1952 SELECT seg.segment_num, seg.application_column_name
1953 BULK COLLECT INTO l_segnums, l_applcols
1954 FROM fnd_id_flex_segments seg
1955 WHERE seg.application_id = 101
1956 AND seg.id_flex_code = 'GL#'
1957 AND seg.id_flex_num = p_coa_id
1958 ORDER BY segment_num;
1959
1960 ---
1961 --- Copy into array. This will break if the segment
1962 --- numbers aren't in order, starting with 1.
1963 ---
1964 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1965 trace
1966 (p_msg => 'Segments: ',
1967 p_level => C_LEVEL_PROCEDURE,
1968 p_module => l_log_module);
1969 END IF;
1970
1971 FOR i IN (l_segnums.FIRST)..(l_segnums.LAST) LOOP
1972 p_segs(l_segnums(i)) := l_applcols(i);
1973
1974 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1975 trace
1976 (p_msg => ' ' || to_char(l_segnums(i))|| ': '||l_applcols(i),
1977 p_level => C_LEVEL_PROCEDURE,
1978 p_module => l_log_module);
1979 END IF;
1980
1981 END LOOP;
1982
1983 --- Get delimiter
1984 p_delim := fnd_flex_apis.get_segment_delimiter(
1985 x_application_id => 101,
1986 x_id_flex_code => 'GL#',
1987 x_id_flex_num => p_coa_id) ;
1988
1989 success_exit(l_log_module);
1990 END get_coa_info;
1991
1992
1993 --=============================================================================
1994 --
1995 -- Name: get_system_info
1996 -- Description:
1997 -- This routine retrieves the ledger and mapping information
1998 -- information
1999 --
2000 -- Parameters:
2001 -- *** NONE ***
2002 --=============================================================================
2003 PROCEDURE get_system_info IS
2004 l_log_module VARCHAR2(240);
2005 l_dummy NUMBER;
2006 l_profile_name VARCHAR2(240);
2007 BEGIN
2008
2009 l_log_module := C_DEFAULT_MODULE||'.get_system_info';
2010 enter_routine(l_log_module);
2011
2012 ---
2013 --- Get system id
2014 ---
2015 gl_fusion_transfer_pkg.get_system_id;
2016
2017 IF (g_system_id IS NULL) THEN
2018 gl_message.write_log('GLFUXFR007', 0);
2019 failure_exit(l_log_module);
2020 RAISE fatal_exception;
2021 END IF;
2022
2023 print_variable('g_system_id', g_system_id, l_log_module);
2024
2025 ---
2026 --- Get Journal Source Key
2027 ---
2028 BEGIN
2029 SELECT je_source_key, system_map_id
2030 INTO g_je_source_key, g_system_map_id
2031 FROM gl_xfr_source_systems
2032 WHERE system_id = g_system_id;
2033 EXCEPTION
2034 WHEN NO_DATA_FOUND THEN
2035 g_je_source_key := NULL;
2036 END;
2037
2038 IF (g_je_source_key IS NULL) THEN
2039 gl_message.write_log('GLFUXFR010', 0);
2040 gl_fusion_transfer_pkg.insert_tracking_data('SYSTEM_MISSING');
2041 failure_exit(l_log_module);
2042 RAISE warning_exception;
2043 END IF;
2044
2045 print_variable('g_je_source_key', g_je_source_key, l_log_module);
2046
2047 ---
2048 --- Make sure this EBS instance is allowed to
2049 --- populate the fusion ledger. If so, get
2050 --- the balancing segment value assignment code.
2051 ---
2052 BEGIN
2053 SELECT bsv_assignment_type
2054 INTO g_bsv_assignment_type
2055 FROM gl_xfr_system_ledgers
2056 WHERE system_map_id = g_system_map_id
2057 AND ledger_id = g_to_ledger_id;
2058 EXCEPTION
2059 WHEN NO_DATA_FOUND THEN
2060 g_bsv_assignment_type := NULL;
2061 END;
2062
2063 IF (g_bsv_assignment_type IS NULL) THEN
2064 gl_message.write_log('GLFUXFR011', 1,
2065 'LEDGER_NAME', g_to_ledger_name);
2066 failure_exit(l_log_module);
2067 gl_fusion_transfer_pkg.insert_tracking_data('LEDGER_REJECTED');
2068 RAISE warning_exception;
2069 END IF;
2070
2071 print_variable('g_bsv_assignment_type', g_bsv_assignment_type, l_log_module);
2072
2073
2074 ---
2075 --- Get the fusion user to be used
2076 ---
2077 BEGIN
2078 g_fusion_user := substrb(fnd_profile.value('GL_AU_FUSION_USER'), 1, 240);
2079 EXCEPTION
2080 WHEN OTHERS THEN
2081 g_fusion_user := NULL;
2082 END;
2083
2084 IF (g_fusion_user IS NULL) THEN
2085
2086 BEGIN
2087 SELECT user_profile_option_name
2088 INTO l_profile_name
2089 FROM fnd_profile_options_tl
2090 WHERE profile_option_name = 'GL_AU_FUSION_USER'
2091 AND language = userenv('LANG');
2092 EXCEPTION
2093 WHEN OTHERS THEN
2094 l_profile_name := NULL;
2095 END;
2096
2097 gl_message.write_log('GLFUXFR015', 1,
2098 'PROFILE_NAME', l_profile_name);
2099
2100 failure_exit(l_log_module);
2101 RAISE fatal_exception;
2102 END IF;
2103
2104 success_exit(l_log_module);
2105 END get_system_info;
2106
2107
2108 --=============================================================================
2109 --
2110 -- Name: get_system_id
2111 -- Description:
2112 -- This routine gets the system id and stores it in g_system_id.
2113 --
2114 -- Parameters:
2115 -- ** None **
2116 --=============================================================================
2117 PROCEDURE get_system_id IS
2118 BEGIN
2119
2120 BEGIN
2121 g_system_id := fnd_profile.value('GL_AU_SYSTEM_ID');
2122
2123 EXCEPTION
2124 WHEN OTHERS THEN
2125 g_system_id := NULL;
2126 END;
2127
2128 return;
2129 END get_system_id;
2130
2131 --=============================================================================
2132 --
2133 -- Name: check_access
2134 -- Description:
2135 -- This routine verifies that the indicated data access set has full access
2136 -- to the indicated ledger
2137 --
2138 -- Parameters:
2139 -- ** None **
2140 --=============================================================================
2141 PROCEDURE check_access IS
2142 l_log_module VARCHAR2(240);
2143 l_access_level VARCHAR2(1);
2144 BEGIN
2145
2146 l_log_module := C_DEFAULT_MODULE||'.check_access';
2147 enter_routine(l_log_module);
2148
2149 ---
2150 --- Get access level
2151 ---
2152 BEGIN
2153 SELECT access_privilege_code
2154 INTO l_access_level
2155 FROM gl_access_set_ledgers
2156 WHERE access_set_id = g_access_set_id
2157 AND ledger_id = g_from_ledger_id;
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 l_access_level := 'N';
2161 END;
2162
2163 IF (l_access_level <> 'F') THEN
2164 print_logfile('You do not have full access to this ledger. Aborting.');
2165 failure_exit(l_log_module);
2166 RAISE fatal_exception;
2167 END IF;
2168 END check_access;
2169
2170
2171 --=============================================================================
2172 --
2173 -- Name: check_bsv_access
2174 -- Description:
2175 --
2176 -- This routine verifies that fusion will accept all of the
2177 -- fusion ledger/bsv combinations that have been populated
2178 --
2179 -- Parameters:
2180 -- *** NONE ***
2181 --=============================================================================
2182 PROCEDURE check_bsv_access IS
2183 l_log_module VARCHAR2(240);
2184 l_bsv_column VARCHAR2(30);
2185 l_sqlbuf VARCHAR2(2000);
2186 l_row_count NUMBER;
2187 BEGIN
2188
2189 l_log_module := C_DEFAULT_MODULE||'.check_bsv_access';
2190 enter_routine(l_log_module);
2191
2192 ---
2193 --- If the assignment code is A, then all bsvs are good
2194 ---
2195 IF (g_bsv_assignment_type = 'A') THEN
2196 RETURN;
2197 END IF;
2198
2199 ---
2200 --- Get balancing segment column
2201 ---
2202 SELECT bal_seg_column_name
2203 INTO l_bsv_column
2204 FROM gl_xfr_ledgers
2205 WHERE ledger_id = g_to_ledger_id;
2206
2207 ---
2208 --- Build sql*statement
2209 ---
2210 l_sqlbuf :=
2211 'INSERT INTO gl_xfr_rejected_bsvs_gt
2212 (balancing_segment_value)
2213 SELECT bsvs.bal_seg_val
2214 FROM
2215 (SELECT int.' || l_bsv_column || ' bal_seg_val
2216 FROM ' || g_interface_table_name || ' int
2217 WHERE int.system_id = :g_system_id
2218 AND int.group_id = :g_group_id
2219 GROUP BY int.'|| l_bsv_column || ') bsvs,
2220 gl_xfr_ledger_bsv_maps map
2221 WHERE map.system_map_id(+) = :g_system_map_id
2222 AND map.ledger_id(+) = :g_to_ledger_id
2223 AND map.balancing_segment_value(+) = bsvs.bal_seg_val
2224 AND map.rowid IS NULL';
2225
2226 IF (g_debug_mode) THEN
2227 print_logfile('SQL Statement to update '|| g_interface_table_name);
2228 gl_fusion_transfer_pkg.print_sql_logfile(l_sqlbuf);
2229 END IF;
2230
2231 EXECUTE IMMEDIATE l_sqlbuf USING g_system_id, g_group_id,
2232 g_system_map_id, g_to_ledger_id;
2233
2234 l_row_count := SQL%ROWCOUNT;
2235
2236 gl_message.write_log('GLFUXFR001', 2,
2237 'ROW_COUNT', l_row_count,
2238 'TABLE', 'gl_xfr_rejected_bsvs_gt');
2239
2240 ---
2241 --- If we found bsvs that wouldn't be accepted, record this.
2242 ---
2243 IF (l_row_count > 0) THEN
2244 gl_message.write_log('GLFUXFR012', 0);
2245 failure_exit(l_log_module);
2246 gl_fusion_transfer_pkg.insert_tracking_data('BSVS_REJECTED');
2247 RAISE warning_exception;
2248 END IF;
2249
2250 success_exit(l_log_module);
2251 END check_bsv_access;
2252
2253
2254 --=============================================================================
2255 -- *********** Initialization routine **********
2256 --=============================================================================
2257
2258 --=============================================================================
2259 --
2260 --
2261 --
2262 --
2263 --
2264 --
2265 --
2266 --
2267 --
2268 --
2269 -- Following code is executed when the package body is referenced for the first
2270 -- time
2271 --
2272 --
2273 --
2274 --
2275 --
2276 --
2277 --
2278 --
2279 --
2280 --
2281 --
2282 --
2283 --=============================================================================
2284
2285 BEGIN
2286 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2287 g_log_enabled := fnd_log.test
2288 (log_level => g_log_level
2289 ,module => C_DEFAULT_MODULE);
2290
2291 IF NOT g_log_enabled THEN
2292 g_log_level := C_LEVEL_LOG_DISABLED;
2293 END IF;
2294
2295 END gl_fusion_transfer_pkg;