[Home] [Help]
PACKAGE BODY: APPS.XLA_HIST_LEDGER_UPG_PKG
Source
1 PACKAGE BODY xla_hist_ledger_upg_pkg AS
2 /* $Header: xlahupg.pkb 120.18 2008/06/27 06:48:42 kapkumar noship $ */
3 /*======================================================================+
4 | Copyright (c) 2000-2001 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_hist_ledger_upg_pkg |
10 | |
11 | |
12 | DESCRIPTION |
13 | Description |
14 | |
15 | HISTORY |
16 | 01-DEC-07 Kapil Kumar Created |
17 | |
18 +======================================================================*/
19
20
21 --=============================================================================
22 -- *********** Local Variables **********
23 --=============================================================================
24 l_primary_ledger_id NUMBER; -- user input
25 l_secondary_alc_ledger_id NUMBER; -- user input
26 l_primary_currency_code VARCHAR2(15);
27 l_sec_alc_currency_code VARCHAR2(15);
28 l_start_period VARCHAR2(15); -- user input
29 l_start_date DATE DEFAULT NULL;
30 l_ledger_category_code VARCHAR2(30);
31 l_relationship_id NUMBER;
32 l_exchange_rate NUMBER;
33 l_precision NUMBER;
34 l_mau NUMBER;
35 l_deno_rate NUMBER;
36 l_nume_rate NUMBER;
37 l_from_currency VARCHAR2(15);
38 l_to_currency VARCHAR2(15);
39 l_headers_insert BOOLEAN DEFAULT FALSE;
40 l_lines_insert BOOLEAN DEFAULT FALSE;
41 l_rates_insert BOOLEAN DEFAULT FALSE;
42 l_links_insert BOOLEAN DEFAULT FALSE;
43 l_ae_insert BOOLEAN DEFAULT FALSE;
44 l_upgrade_id NUMBER;
45 l_sec_alc_end_date DATE;
46 l_upgrade_status NUMBER DEFAULT 0;
47 l_mapping_relationship_id NUMBER DEFAULT NULL;
48 l_primary_coa NUMBER;
49 l_secondary_coa NUMBER;
50 l_coa_mapping_name VARCHAR2(50) DEFAULT NULL;
51 l_conversion_option VARCHAR2(10); --user input if secondary
52 l_sec_alc_mau NUMBER;
53 l_sec_alc_precision NUMBER;
54 l_currency_conversion_type VARCHAR2(100); --user input if secondary
55 l_currency_conversion_date DATE; --user input if secondary
56 l_sql_time VARCHAR2(100);
57 l_error_text VARCHAR2(1000);
58 l_validation_mode VARCHAR2(20) DEFAULT 'F';
59 --=============================================================================
60 -- *********** Local Trace Routine **********
61 --=============================================================================
62 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
63 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
64 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
65 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
66 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
67 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
68
69 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
70 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_hist_ledger_upg_pkg';
71
72 g_log_level NUMBER;
73 g_log_enabled BOOLEAN;
74
75
76 C_LOG_SIZE CONSTANT NUMBER := 2000;
77
78 PROCEDURE trace
79 (p_msg IN VARCHAR2
80 ,p_level IN NUMBER
81 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE)
82 IS
83
84 l_max NUMBER;
85 l_pos NUMBER := 1;
86
87 BEGIN
88
89 l_pos := 1;
90
91 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
92 fnd_log.message(p_level, p_module);
93 ELSIF p_level >= g_log_level THEN
94
95 l_max := length(p_msg);
96 IF l_max <= C_LOG_SIZE THEN
97 fnd_log.string(p_level, p_module, p_msg);
98 ELSE
99 -- 5221578 log messages in C_LOG_SIZE
100 WHILE (l_pos-1)*C_LOG_SIZE <= l_max LOOP
101 fnd_log.string(p_level, p_module, substr(p_msg, (l_pos-1)*C_LOG_SIZE+1, C_LOG_SIZE));
102 l_pos := l_pos+1;
103 END LOOP;
104 END IF;
105 END IF;
106
107 EXCEPTION
108 WHEN xla_exceptions_pkg.application_exception THEN
109 RAISE;
110 WHEN OTHERS THEN
111 xla_exceptions_pkg.raise_message
112 (p_location => 'xla_hist_ledger_upg_pkg.trace');
113 END trace;
114
115
116
117 PROCEDURE upg_main
118 (p_errbuf OUT NOCOPY VARCHAR2
119 ,p_retcode OUT NOCOPY NUMBER
120 ,p_primary_ledger_id IN NUMBER
121 ,p_reporting_ledger_id IN NUMBER
122 ,p_start_period IN VARCHAR2
123 ,p_rep_ledger_type IN VARCHAR2
124 ,p_conversion_option IN VARCHAR2
125 ,p_currency_conversion_type IN VARCHAR2
126 ,p_currency_conversion_date IN VARCHAR2
127 ,p_mode IN VARCHAR2
128 )
129
130
131 IS
132
133
134
135 l_log_module VARCHAR2(240);
136 abnormal_termination EXCEPTION;
137 already_run EXCEPTION;
138 validation_end EXCEPTION;
139
140
141 BEGIN
142
143
144 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
145
146 IF g_log_enabled THEN
147 l_log_module := C_DEFAULT_MODULE||'.upg_main';
148 END IF;
149
150
151 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
152 INTO l_sql_time
153 FROM dual;
154
155 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
156 trace
157 (p_msg => 'UPG_MAIN procedure start time: ' || l_sql_time
158 ,p_level => C_LEVEL_PROCEDURE
159 ,p_module => l_log_module);
160 END IF;
161 fnd_file.put_line(fnd_file.log,'UPG_MAIN procedure start time: ' || l_sql_time);
162
163 l_upgrade_id := fnd_global.conc_request_id();
164 l_primary_ledger_id := p_primary_ledger_id;
165 l_secondary_alc_ledger_id := p_reporting_ledger_id;
166 l_start_period := p_start_period;
167 l_validation_mode := p_mode;
168
169 l_conversion_option := p_conversion_option;
170 l_currency_conversion_type := p_currency_conversion_type;
171 l_currency_conversion_date := fnd_date.canonical_to_date(p_currency_conversion_date);
172
173
174 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
175 trace
176 (p_msg => 'BEGIN of procedure UPG_MAIN'
177 ,p_level => C_LEVEL_PROCEDURE
178 ,p_module => l_log_module);
179 trace
180 (p_msg => 'Validation mode is = ' || l_validation_mode
181 ,p_level => C_LEVEL_PROCEDURE
182 ,p_module => l_log_module);
183 trace
184 (p_msg => 'Primary Ledger id = '|| l_primary_ledger_id
185 ,p_level => C_LEVEL_PROCEDURE
186 ,p_module => l_log_module);
187 trace
188 (p_msg => 'alc or secondary ledger id = '|| l_secondary_alc_ledger_id
189 ,p_level => C_LEVEL_PROCEDURE
190 ,p_module => l_log_module);
191 trace
192 (p_msg => 'Start period (user specified) = '||l_start_period
193 ,p_level => C_LEVEL_PROCEDURE
194 ,p_module => l_log_module);
195 trace
196 (p_msg => 'Upgrade Run ID = ' || l_upgrade_id
197 ,p_level => C_LEVEL_PROCEDURE
198 ,p_module => l_log_module);
199
200 END IF;
201
202
203 -- recovery mode begin
204 IF l_validation_mode = 'R' THEN
205 crash_recovery();
206 IF l_upgrade_status = 0 THEN
207 p_retcode := 0;
208 p_errbuf := 'Upgrade Recovery Completed Successfully';
209 fnd_file.put_line(fnd_file.log, 'Upgrade Recovery Completed Successfully');
210 ELSE
211 p_retcode := 1;
212 p_errbuf := 'Upgrade Recovery Run Unsuccessful';
213 fnd_file.put_line(fnd_file.log, 'Upgrade Recovery Run Unsuccessful');
214 END IF;
215
216 RAISE validation_end;
217
218 END IF;
219 -- recovery mode end
220
221
222
223
224 BEGIN
225
226 SELECT end_date --- removed start_date
227 INTO l_sec_alc_end_date
228 FROM gl_period_statuses
229 WHERE period_name = (SELECT latest_opened_period_name -- removed first_ledger_period_name
230 FROM gl_ledgers
231 WHERE ledger_id = l_primary_ledger_id) -- removed l_secondary_alc_ledger_id
232 AND ledger_id = l_primary_ledger_id
233 AND application_id = 101;
234
235 fnd_file.put_line(fnd_file.log, 'Validation successful: end_date');
236 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
237 trace
238 (p_msg => 'End date of last open period of sec/alc ledger = ' || l_sec_alc_end_date
239 ,p_level => C_LEVEL_PROCEDURE
240 ,p_module => l_log_module);
241 END IF;
242
243
244 EXCEPTION WHEN OTHERS THEN
245
246 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
247 trace
248 (p_msg => 'error in upg main in end date select' || l_sec_alc_end_date
249 ,p_level => C_LEVEL_PROCEDURE
250 ,p_module => l_log_module);
251 END IF;
252 l_upgrade_status := 1;
253 fnd_file.put_line(fnd_file.log, 'Validation failed: end_date');
254 IF l_validation_mode = 'F' THEN
255 RAISE abnormal_termination;
256 END IF;
257
258 END;
259
260
261 pre_validation();
262
263 IF l_upgrade_status = -1 AND l_validation_mode = 'F' THEN
264 RAISE already_run;
265 END IF;
266
267
268 IF l_upgrade_status = 1 AND l_validation_mode = 'F' THEN
269 RAISE abnormal_termination;
270 END IF;
271
272
273 SELECT currency_code
274 INTO l_primary_currency_code
275 FROM gl_ledgers
276 WHERE ledger_id = l_primary_ledger_id;
277
278
279 SELECT ledger_category_code, currency_code
280 INTO l_ledger_category_code, l_sec_alc_currency_code
281 FROM gl_ledgers
282 WHERE ledger_id = l_secondary_alc_ledger_id;
283
284 SELECT minimum_accountable_unit, precision
285 INTO l_sec_alc_mau, l_sec_alc_precision
286 FROM fnd_currencies
287 WHERE currency_code = l_sec_alc_currency_code;
288
289
290 IF l_ledger_category_code = 'ALC' THEN
291
292 SELECT alc_init_conv_option_code, alc_initializing_rate_type, alc_initializing_rate_date
293 INTO l_conversion_option, l_currency_conversion_type, l_currency_conversion_date
294 FROM gl_ledger_relationships
295 WHERE relationship_id = l_relationship_id;
296
297 IF ( (l_conversion_option IS NULL) OR (l_currency_conversion_type IS NULL) OR (l_currency_conversion_date IS NULL) ) THEN
298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
299 trace
300 (p_msg => 'Error: gl_ledger_relationships returned a blank value for either l_conversion_option, l_currency_conversion_type, or l_currency_conversion_date for ALC ledger'
301 ,p_level => C_LEVEL_PROCEDURE
302 ,p_module => l_log_module);
303 END IF;
304 fnd_message.set_name('XLA','XLA_UPG_GL_LEDG_NULL');
305 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
306 l_upgrade_status := 1;
307
308 IF l_validation_mode = 'F' THEN
309 RAISE abnormal_termination;
310 END IF;
311
312 END IF;
313 END IF;
314
315
316
317 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
318 trace
319 (p_msg => 'Conversion option = ' || l_conversion_option
320 ,p_level => C_LEVEL_PROCEDURE
321 ,p_module => l_log_module);
322 trace
323 (p_msg => 'Currency conversion type = ' || l_currency_conversion_type
324 ,p_level => C_LEVEL_PROCEDURE
325 ,p_module => l_log_module);
326 trace
327 (p_msg => 'Currency conversion date = ' || l_currency_conversion_date
328 ,p_level => C_LEVEL_PROCEDURE
329 ,p_module => l_log_module);
330
331 trace
332 (p_msg => 'Primary ledgers functional/ledger currency = ' || l_primary_currency_code
333 ,p_level => C_LEVEL_PROCEDURE
334 ,p_module => l_log_module);
335
336 trace
337 (p_msg => 'sec/alc ledgers functional/ledger currency = ' || l_sec_alc_currency_code
338 ,p_level => C_LEVEL_PROCEDURE
339 ,p_module => l_log_module);
340
341 trace
342 (p_msg => 'Ledger category code = ' || l_ledger_category_code
343 ,p_level => C_LEVEL_PROCEDURE
344 ,p_module => l_log_module);
345 trace
346 (p_msg => 'sec/alc minimum accountable unit = ' || l_sec_alc_mau
347 ,p_level => C_LEVEL_PROCEDURE
348 ,p_module => l_log_module);
349 trace
350 (p_msg => 'sec/alc precision = ' || l_sec_alc_precision
351 ,p_level => C_LEVEL_PROCEDURE
352 ,p_module => l_log_module);
353 END IF;
354
355
356
357
358 -- validation mode only
359
360 IF l_validation_mode = 'V' THEN
361 IF l_ledger_category_code = 'ALC' THEN
362 validate_alc();
363 END IF;
364
365 IF l_ledger_category_code = 'SECONDARY' THEN
366 validate_secondary();
367 END IF;
368
369 populate_rates();
370
371 ccid_check();
372
373 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
374 INTO l_sql_time
375 FROM dual;
376
377 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
378 trace
379 (p_msg => 'UPG_MAIN validation procedure end time: ' || l_sql_time
380 ,p_level => C_LEVEL_PROCEDURE
381 ,p_module => l_log_module);
382 END IF;
383 fnd_file.put_line(fnd_file.log,'UPG_MAIN validation procedure end time: ' || l_sql_time);
384
385 IF l_upgrade_status = 0 THEN
386 p_retcode := 0;
387 p_errbuf := 'Upgrade Validation Completed Successfully';
388 END IF;
389
390 IF l_upgrade_status = 1 THEN
391 p_retcode := 0;
392 p_errbuf := 'Upgrade Validation WARNINGS';
393 END IF;
394
395 RAISE validation_end;
396
397 END IF;
398 -- validation mode end
399
400
401
402
403
404 populate_rates();
405 IF l_upgrade_status = 1 THEN
406 RAISE abnormal_termination;
407 END IF;
408
409
410
411 IF l_ledger_category_code = 'ALC' THEN
412
413 validate_alc();
414
415 IF l_upgrade_status = 1 THEN
416 RAISE abnormal_termination;
417 END IF;
418
419 alc_insert();
420
421 IF l_upgrade_status = 1 THEN
422 RAISE abnormal_termination;
423 END IF;
424
425
426
427 ELSIF l_ledger_category_code = 'SECONDARY' THEN
428
429 validate_secondary();
430
431 IF l_upgrade_status = 1 THEN
432 RAISE abnormal_termination;
433 END IF;
434
435 secondary_insert();
436
437 map_ccid();
438
439 IF l_upgrade_status = 1 THEN
440 RAISE abnormal_termination;
441 END IF;
442
443 calendar_convert();
444
445 IF l_upgrade_status = 1 THEN
446 RAISE abnormal_termination;
447 END IF;
448
449
450
451 END IF;
452
453 line_num_resequence();
454 IF l_upgrade_status = 1 THEN
455 RAISE abnormal_termination;
456 END IF;
457
458 post_validation();
459 IF l_upgrade_status = 1 THEN
460 RAISE abnormal_termination;
461 END IF;
462
463
464 insert_links_segments();
465 IF l_upgrade_status = 1 THEN
466 RAISE abnormal_termination;
467 END IF;
468
469
470 p_retcode := 0;
471 p_errbuf := 'Upgrade Completed Successfully';
472
473 UPDATE gl_ledger_relationships
474 SET hist_conv_status_code = 'SUCCESSFUL'
475 WHERE relationship_id = l_relationship_id;
476
477
478 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
479 trace
480 (p_msg => 'Upgrade Successfully Completed. Rows Updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
481 ,p_level => C_LEVEL_PROCEDURE
482 ,p_module => l_log_module);
483
484 END IF;
485 fnd_file.put_line(fnd_file.log,'Upgrade Successfully Completed');
486
487 COMMIT;
488
489
490
491 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
492 INTO l_sql_time
493 FROM dual;
494
495 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
496 trace
497 (p_msg => 'UPG_MAIN procedure end time: ' || l_sql_time
498 ,p_level => C_LEVEL_PROCEDURE
499 ,p_module => l_log_module);
500 END IF;
501 fnd_file.put_line(fnd_file.log,'UPG_MAIN procedure end time: ' || l_sql_time);
502
503
504
505 EXCEPTION
506
507 WHEN validation_end THEN
508
509 NULL;
510
511 WHEN already_run THEN
512
513 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
514 trace
515 (p_msg => 'BEGIN of already_run EXCEPTION'
516 ,p_level => C_LEVEL_PROCEDURE
517 ,p_module => l_log_module);
518 END IF;
519
520 p_retcode := 1;
521 p_errbuf := 'Upgrade Already Run';
522
523
524 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
525 trace
526 (p_msg => 'END of already_run EXCEPTION'
527 ,p_level => C_LEVEL_PROCEDURE
528 ,p_module => l_log_module);
529 END IF;
530
531
532
533
534 WHEN abnormal_termination THEN
535
536 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
537 trace
538 (p_msg => 'BEGIN of Abnormal Termination EXCEPTION'
539 ,p_level => C_LEVEL_PROCEDURE
540 ,p_module => l_log_module);
541 END IF;
542
543
544 upgrade_rollback;
545 p_retcode := 1;
546 p_errbuf := 'Upgrade Failed';
547
548
549 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
550 trace
551 (p_msg => 'END of Abnormal Termination EXCEPTION'
552 ,p_level => C_LEVEL_PROCEDURE
553 ,p_module => l_log_module);
554 END IF;
555
556
557 WHEN others THEN
558
559 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
560 trace
561 (p_msg => 'BEGIN of others EXCEPTION'
562 ,p_level => C_LEVEL_PROCEDURE
563 ,p_module => l_log_module);
564 END IF;
565
566 l_error_text := SQLCODE || ' ' || SQLERRM;
567
568
569 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
570 trace
571 (p_msg => 'sql exception raised in upg_main is = ' || l_error_text
572 ,p_level => C_LEVEL_PROCEDURE
573 ,p_module => l_log_module);
574 END IF;
575 fnd_file.put_line(fnd_file.log, 'sql exception raised in upg_main is = ' || l_error_text);
576
577
578 upgrade_rollback;
579 p_retcode := 1;
580 p_errbuf := 'Upgrade Failed';
581
582
583 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
584 trace
585 (p_msg => 'END of others EXCEPTION'
586 ,p_level => C_LEVEL_PROCEDURE
587 ,p_module => l_log_module);
588 END IF;
589
590
591
592 END;
593
594
595 PROCEDURE upgrade_rollback
596 IS
597
598 l_log_module VARCHAR2(240);
599
600 BEGIN
601
602 IF g_log_enabled THEN
603 l_log_module := C_DEFAULT_MODULE||'.upgrade_rollback';
604 END IF;
605
606
607
608 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
609 trace
610 (p_msg => 'BEGIN of upgrade rollback. SQL error msg above'
611 ,p_level => C_LEVEL_PROCEDURE
612 ,p_module => l_log_module);
613 END IF;
614
615
616
617 UPDATE gl_ledger_relationships
618 SET hist_conv_status_code = 'FAILED'
619 WHERE relationship_id = l_relationship_id;
620
621 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
622 trace
623 (p_msg => 'Ledger id stamped as failed. Rows updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
624 ,p_level => C_LEVEL_PROCEDURE
625 ,p_module => l_log_module);
626 END IF;
627
628
629
630 COMMIT;
631
632
633 IF l_rates_insert = TRUE THEN
634
635 DELETE
636 FROM xla_rc_upgrade_rates
637 WHERE relationship_id = l_relationship_id
638 AND upgrade_run_id = l_upgrade_id;
639
640 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
641 trace
642 (p_msg => 'deleted rows from xla_rc_upgrade_rates = ' || SQL%ROWCOUNT
643 ,p_level => C_LEVEL_PROCEDURE
644 ,p_module => l_log_module);
645 END IF;
646
647
648 COMMIT;
649 END IF;
650
651
652 IF l_ae_insert = TRUE THEN
653
654 DELETE
655 FROM xla_ae_segment_values
656 WHERE upg_batch_id = l_upgrade_id;
657
658 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
659 trace
660 (p_msg => 'deleted rows from xla_ae_segment_values = ' || SQL%ROWCOUNT
661 ,p_level => C_LEVEL_PROCEDURE
662 ,p_module => l_log_module);
663 END IF;
664 COMMIT;
665 END IF;
666
667
668
669
670
671 IF l_lines_insert = TRUE THEN
672
673 DELETE
674 FROM xla_ae_lines
675 WHERE request_id = l_upgrade_id;
676
677 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
678 trace
679 (p_msg => 'deleted rows from xla_ae_lines = ' || SQL%ROWCOUNT
680 ,p_level => C_LEVEL_PROCEDURE
681 ,p_module => l_log_module);
682 END IF;
683 COMMIT;
684 END IF;
685
686
687
688
689 IF l_headers_insert = TRUE THEN
690
691 DELETE
692 FROM xla_ae_headers
693 WHERE request_id = l_upgrade_id;
694
695 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
696 trace
697 (p_msg => 'deleted rows from xla_ae_headers = ' || SQL%ROWCOUNT
698 ,p_level => C_LEVEL_PROCEDURE
699 ,p_module => l_log_module);
700 END IF;
701 COMMIT;
702 END IF;
703
704
705 IF l_links_insert = TRUE THEN
706
707 DELETE
708 FROM xla_distribution_links
709 WHERE gain_or_loss_ref = to_char(l_upgrade_id);
710
711 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
712 trace
713 (p_msg => 'deleted rows from xla_distribution_links = ' || SQL%ROWCOUNT
714 ,p_level => C_LEVEL_PROCEDURE
715 ,p_module => l_log_module);
716 END IF;
717 COMMIT;
718 END IF;
719
720
721
722
723 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
724 trace
725 (p_msg => 'END of upgrade rollback'
726 ,p_level => C_LEVEL_PROCEDURE
727 ,p_module => l_log_module);
728 END IF;
729
730
731 EXCEPTION WHEN others THEN
732
733 l_error_text := SQLCODE || ' ' || SQLERRM;
734
735
736 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
737 trace
738 (p_msg => 'inside upgrade_rollback SQL error msg = ' || l_error_text
739 ,p_level => C_LEVEL_PROCEDURE
740 ,p_module => l_log_module);
741 END IF;
742
743
744 l_upgrade_status := 1;
745
746
747
748 END;
749
750
751
752
753
754 PROCEDURE pre_validation IS
755
756 l_previous_status VARCHAR2(30);
757 l_relationship_exists NUMBER DEFAULT NULL;
758 l_untransferred_headers NUMBER DEFAULT NULL;
759 l_sec_alc_data_check NUMBER DEFAULT NULL;
760 l_log_module VARCHAR2(240);
761
762
763 BEGIN
764
765 IF g_log_enabled THEN
766 l_log_module := C_DEFAULT_MODULE||'.pre_validation';
767 END IF;
768
769
770
771 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
772 trace
773 (p_msg => 'BEGIN of procedure PRE_VALIDATION'
774 ,p_level => C_LEVEL_PROCEDURE
775 ,p_module => l_log_module);
776 END IF;
777
778
779
780 -- Check if alc/secondary ledger is related to the primary ledger
781
782 BEGIN
783
784 SELECT 1
785 INTO l_relationship_exists
786 FROM DUAL
787 WHERE EXISTS (SELECT 1
788 FROM gl_ledger_relationships
789 WHERE primary_ledger_id = l_primary_ledger_id
790 AND target_ledger_id = l_secondary_alc_ledger_id
791 AND relationship_enabled_flag = 'Y');
792
793 SELECT min(relationship_id)
794 INTO l_relationship_id
795 FROM gl_ledger_relationships
796 WHERE primary_ledger_id = l_primary_ledger_id
797 AND target_ledger_id = l_secondary_alc_ledger_id
798 AND relationship_enabled_flag = 'Y';
799
800 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
801 trace
802 (p_msg => 'Relationship between primary and secondary/alc is valid. Relationship id = ' || l_relationship_id
803 ,p_level => C_LEVEL_PROCEDURE
804 ,p_module => l_log_module);
805 END IF;
806
807 fnd_file.put_line(fnd_file.log, 'Validation successful: ledger relationship');
808
809
810 EXCEPTION when OTHERS THEN
811
812
813 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
814 trace
815 (p_msg => 'Error: Relationship between primary and secondary/alc does not exist, or is disabled'
816 ,p_level => C_LEVEL_PROCEDURE
817 ,p_module => l_log_module);
818 END IF;
819
820 fnd_message.set_name('XLA','XLA_UPG_GL_REL_FAIL');
821 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
822
823 l_upgrade_status := 1;
824
825 IF l_validation_mode = 'F' THEN
826 RETURN;
827 END IF;
828 END;
829
830
831
832
833 -- Check if upgrade has run successfully for same ledger earlier
834
835 IF l_validation_mode = 'F' THEN --if running in validation mode, not concerned with last run
836
837 SELECT hist_conv_status_code
838 INTO l_previous_status
839 FROM gl_ledger_relationships
840 WHERE relationship_id = l_relationship_id;
841
842 IF l_previous_status = 'SUCCESSFUL' OR l_previous_status = 'RUNNING' THEN
843 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
844 trace
845 (p_msg => 'Error: Upgrade has already been run successfully for this ledger or is currently running'
846 ,p_level => C_LEVEL_PROCEDURE
847 ,p_module => l_log_module);
848 END IF;
849
850 fnd_message.set_name('XLA', 'XLA_UPG_ALREADY_RUN');
851 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
852
853
854 l_upgrade_status := -1;
855 RETURN;
856 ELSE
857 UPDATE gl_ledger_relationships
858 SET hist_conv_status_code = 'RUNNING'
859 WHERE relationship_id = l_relationship_id;
860
861
862 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
863 trace
864 (p_msg => 'Conversion status set to RUNNING, row updated in gl_ledger_relationships = ' || SQL%ROWCOUNT
865 ,p_level => C_LEVEL_PROCEDURE
866 ,p_module => l_log_module);
867 END IF;
868
869 COMMIT;
870
871 END IF;
872
873 END IF;
874
875
876 -- Check if the ledger start period is valid
877
878
879 BEGIN
880
881
882 SELECT start_date
883 INTO l_start_date
884 FROM gl_period_statuses
885 WHERE period_name = l_start_period
886 AND ledger_id = l_secondary_alc_ledger_id
887 AND application_id = 101;
888 --AND adjustment_period_flag <> 'Y'; --condition removed since first open period may be adjustment period??
889
890
891
892 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
893 trace
894 (p_msg => 'User specified start period for sec/alc ledger is valid. Starting date of this start period = ' || l_start_date
895 ,p_level => C_LEVEL_PROCEDURE
896 ,p_module => l_log_module);
897 END IF;
898 fnd_file.put_line(fnd_file.log, 'Validation successful: start date');
899
900
901 EXCEPTION WHEN others THEN
902
903
904 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
905 trace
906 (p_msg => 'Error: Start Period is not valid' || l_start_period
907 ,p_level => C_LEVEL_PROCEDURE
908 ,p_module => l_log_module);
909 END IF;
910 fnd_message.set_name('XLA','XLA_UPG_PERIOD_INVAL');
911 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
912
913 l_upgrade_status := 1;
914 IF l_validation_mode = 'F' THEN
915 RETURN;
916 END IF;
917
918 END;
919
920
921 -- Check if all fully accounted data has also been transferred to GL
922
923
924 BEGIN
925
926 SELECT 1
927 INTO l_untransferred_headers
928 FROM DUAL
929 WHERE EXISTS (SELECT 1
930 FROM xla_ae_headers
931 WHERE ledger_id = l_primary_ledger_id
932 AND accounting_entry_status_code = 'F'
933 AND gl_transfer_status_code <> 'Y'
934 AND accounting_date >= l_start_date
935 AND accounting_date <= l_sec_alc_end_date);
936
937
938
939 IF l_untransferred_headers = 1 THEN
940 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
941 trace
942 (p_msg => 'Error: There are valid accounting entries that have not been transferred to General Ledger. Please transfer these entries to GL before proceeding with Upgrade'
943 ,p_level => C_LEVEL_PROCEDURE
944 ,p_module => l_log_module);
945 END IF;
946
947 fnd_message.set_name('XLA','XLA_UPG_UNTRANS_ENTRIES');
948 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
949
950
951 l_upgrade_status := 1;
952 IF l_validation_mode = 'F' THEN
953 RETURN;
954 END IF;
955 END IF;
956
957
958 EXCEPTION WHEN others THEN
959
960 fnd_file.put_line(fnd_file.log, 'Validation successful: data transferred to GL');
961 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
962 trace
963 (p_msg => 'All valid entries for primary ledger have been transferred to GL'
964 ,p_level => C_LEVEL_PROCEDURE
965 ,p_module => l_log_module);
966 END IF;
967
968 END;
969
970
971
972 -- Check if there is any existing data for the secondary/alc ledger?
973
974
975 BEGIN
976
977
978 SELECT 1
979 INTO l_sec_alc_data_check
980 FROM DUAL
981 WHERE EXISTS (SELECT 1
982 FROM xla_ae_headers
983 WHERE ledger_id = l_secondary_alc_ledger_id);
984
985
986 IF l_sec_alc_data_check = 1 THEN
987 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
988 trace
989 (p_msg => 'Error: sec/alc ledger already contains data'
990 ,p_level => C_LEVEL_PROCEDURE
991 ,p_module => l_log_module);
992 END IF;
993
994 fnd_message.set_name('XLA','XLA_UPG_PREEXISTING_DATA');
995 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
996
997
998
999 l_upgrade_status := 1;
1000 IF l_validation_mode = 'F' THEN
1001 RETURN;
1002 END IF;
1003 END IF;
1004
1005
1006 EXCEPTION WHEN others THEN
1007
1008 fnd_file.put_line(fnd_file.log, 'Validation successful: pre-existing data');
1009 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1010 trace
1011 (p_msg => 'No data in sec/alc before upgrade start'
1012 ,p_level => C_LEVEL_PROCEDURE
1013 ,p_module => l_log_module);
1014 END IF;
1015
1016
1017 END;
1018
1019
1020 EXCEPTION WHEN others THEN
1021
1022 l_error_text := SQLCODE || ' ' || SQLERRM;
1023
1024
1025 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1026 trace
1027 (p_msg => 'inside pre_validation SQL error msg = ' || l_error_text
1028 ,p_level => C_LEVEL_PROCEDURE
1029 ,p_module => l_log_module);
1030 END IF;
1031 fnd_file.put_line(fnd_file.log,'inside pre_validation SQL error msg = ' || l_error_text);
1032
1033
1034 l_upgrade_status := 1;
1035
1036 END;
1037
1038
1039
1040
1041
1042 PROCEDURE populate_rates IS
1043
1044
1045 l_rate_exists NUMBER DEFAULT NULL;
1046 l_currency_count NUMBER;
1047 l_log_module VARCHAR2(240);
1048
1049
1050 l_numerator NUMBER;
1051 l_denominator NUMBER;
1052 l_rate NUMBER;
1053
1054 CURSOR c_entered_currencies
1055 IS
1056 SELECT distinct(l.currency_code)
1057 FROM xla_ae_lines l
1058 WHERE exists
1059 (SELECT 1
1060 FROM xla_ae_headers h
1061 WHERE h.ledger_id = l_primary_ledger_id
1062 AND l.ae_header_id = h.ae_header_id
1063 AND l.application_id = h.application_id
1064 AND h.accounting_entry_status_code = 'F'
1065 AND h.accounting_date >= l_start_date
1066 AND h.accounting_date <= l_sec_alc_end_date);
1067
1068
1069
1070
1071 BEGIN
1072
1073
1074 IF g_log_enabled THEN
1075 l_log_module := C_DEFAULT_MODULE||'.populate_rates';
1076 END IF;
1077
1078
1079
1080
1081 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1082
1083 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1084 INTO l_sql_time
1085 FROM dual;
1086
1087 trace
1088 (p_msg => 'populate_rates procedure start: ' || l_sql_time
1089 ,p_level => C_LEVEL_PROCEDURE
1090 ,p_module => l_log_module);
1091 END IF;
1092
1093
1094 -- Check if exchange rates already exists for this relationship id/upgrade id
1095
1096
1097 BEGIN
1098
1099
1100 SELECT 1
1101 INTO l_rate_exists
1102 FROM DUAL
1103 WHERE EXISTS (SELECT 1
1104 FROM xla_rc_upgrade_rates
1105 WHERE relationship_id = l_relationship_id
1106 AND upgrade_run_id = l_upgrade_id);
1107
1108
1109 IF l_rate_exists = 1 THEN
1110 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1111 trace
1112 (p_msg => 'Error: Currency rates already defined for this currency'
1113 ,p_level => C_LEVEL_PROCEDURE
1114 ,p_module => l_log_module);
1115 END IF;
1116
1117
1118 fnd_message.set_name('XLA','XLA_UPG_RATES_EXIST');
1119 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1120
1121
1122 l_upgrade_status := 1;
1123 RETURN;
1124 END IF;
1125
1126
1127 EXCEPTION WHEN others THEN
1128
1129 fnd_file.put_line(fnd_file.log, 'Validation successful: rates table consistency');
1130 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1131 trace
1132 (p_msg => 'Currency rates not already defined for this currency'
1133 ,p_level => C_LEVEL_PROCEDURE
1134 ,p_module => l_log_module);
1135 END IF;
1136
1137 END;
1138
1139
1140
1141
1142 IF l_conversion_option = 'I' THEN
1143
1144
1145 FOR c_entered_currencies_rec IN c_entered_currencies
1146 LOOP
1147
1148
1149
1150 BEGIN
1151 -- glustcrs.pls
1152 gl_currency_api.get_triangulation_rate(c_entered_currencies_rec.currency_code,
1153 l_sec_alc_currency_code,
1154 l_currency_conversion_date,
1155 l_currency_conversion_type,
1156 l_denominator, -- output from API
1157 l_numerator, -- output from API
1158 l_rate); -- output from API
1159 EXCEPTION WHEN others THEN
1160 IF ( (l_denominator IS NULL) OR (l_numerator IS NULL) OR (l_rate IS NULL) OR
1161 (l_denominator <= 0) OR (l_numerator <= 0) OR (l_rate <= 0)) THEN
1162 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1163 trace
1164 (p_msg => 'Error: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || c_entered_currencies_rec.currency_code
1165 ,p_level => C_LEVEL_PROCEDURE
1166 ,p_module => l_log_module);
1167 END IF;
1168
1169 --fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1170 --fnd_file.put_line(fnd_file.log, fnd_message.get);
1171
1172
1173 l_upgrade_status := 1;
1174 IF l_validation_mode = 'F' THEN
1175 RETURN;
1176 END IF;
1177 END IF;
1178 END;
1179
1180
1181
1182
1183 IF ( (l_denominator IS NULL) OR (l_numerator IS NULL) OR (l_rate IS NULL) OR
1184 (l_denominator <= 0) OR (l_numerator <= 0) OR (l_rate <= 0)) THEN
1185
1186 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1187 trace
1188 (p_msg => 'Error: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || c_entered_currencies_rec.currency_code
1189 ,p_level => C_LEVEL_PROCEDURE
1190 ,p_module => l_log_module);
1191 END IF;
1192
1193 fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1194 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get || ' ' || c_entered_currencies_rec.currency_code);
1195
1196 l_upgrade_status := 1;
1197 IF l_validation_mode = 'F' THEN
1198 RETURN;
1199 END IF;
1200 ELSE
1201 fnd_file.put_line(fnd_file.log, 'Validation successful: currency API ' || c_entered_currencies_rec.currency_code);
1202 END IF;
1203
1204
1205
1206 IF l_validation_mode = 'F' THEN
1207 INSERT INTO xla_rc_upgrade_rates
1208 (relationship_id
1209 ,upgrade_run_id
1210 ,from_currency
1211 ,to_currency
1212 ,denominator_rate
1213 ,numerator_rate
1214 ,conversion_rate
1215 ,precision
1216 ,minimum_accountable_unit
1217 ,creation_date
1218 ,created_by
1219 ,last_update_date
1220 ,last_updated_by
1221 ,last_update_login)
1222 VALUES(l_relationship_id
1223 ,l_upgrade_id
1224 ,c_entered_currencies_rec.currency_code
1225 ,l_sec_alc_currency_code
1226 ,l_denominator
1227 ,l_numerator
1228 ,l_rate
1229 ,null
1230 ,null
1231 ,SYSDATE
1232 ,fnd_global.user_id
1233 ,SYSDATE
1234 ,fnd_global.user_id
1235 ,fnd_global.login_id);
1236 END IF;
1237
1238 END LOOP;
1239
1240
1241
1242 l_rates_insert := TRUE;
1243
1244
1245
1246 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1247
1248 SELECT count(*)
1249 INTO l_currency_count
1250 FROM xla_rc_upgrade_rates
1251 WHERE relationship_id = l_relationship_id
1252 AND upgrade_run_id = l_upgrade_id;
1253
1254
1255 trace
1256 (p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || l_currency_count
1257 ,p_level => C_LEVEL_PROCEDURE
1258 ,p_module => l_log_module);
1259 END IF;
1260
1261 ELSE
1262
1263
1264 BEGIN
1265 gl_currency_api.get_triangulation_rate(l_primary_currency_code,
1266 l_sec_alc_currency_code,
1267 l_currency_conversion_date,
1268 l_currency_conversion_type,
1269 l_denominator, -- output from API
1270 l_numerator, -- output from API
1271 l_rate); -- output from API
1272
1273 EXCEPTION WHEN others THEN
1274 IF ( (l_denominator IS NULL) OR (l_numerator IS NULL) OR (l_rate IS NULL)
1275 OR (l_denominator <= 0) OR (l_numerator <= 0) OR (l_rate <= 0) ) THEN
1276 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1277 trace
1278 (p_msg => 'Error: GL API returned a null value for currency or threw back an EXCEPTION for currency ' || l_primary_currency_code
1279 ,p_level => C_LEVEL_PROCEDURE
1280 ,p_module => l_log_module);
1281 END IF;
1282
1283
1284 --fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1285 --fnd_file.put_line(fnd_file.log, fnd_message.get);
1286
1287 l_upgrade_status := 1;
1288 IF l_validation_mode = 'F' THEN
1289 RETURN;
1290 END IF;
1291 END IF;
1292 END;
1293
1294
1295
1296
1297
1298 IF ( (l_denominator IS NULL) OR (l_numerator IS NULL) OR (l_rate IS NULL)
1299 OR (l_denominator <= 0) OR (l_numerator <= 0) OR (l_rate <= 0) ) THEN
1300
1301 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1302 trace
1303 (p_msg => 'Error: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || l_primary_currency_code
1304 ,p_level => C_LEVEL_PROCEDURE
1305 ,p_module => l_log_module);
1306 END IF;
1307
1308 fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1309 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get || ' ' || l_primary_currency_code);
1310
1311 l_upgrade_status := 1;
1312 IF l_validation_mode = 'F' THEN
1313 RETURN;
1314 END IF;
1315 ELSE
1316 fnd_file.put_line(fnd_file.log, 'Validation successful: currency API ' || l_primary_currency_code);
1317 END IF;
1318
1319
1320 IF l_validation_mode = 'F' THEN
1321 INSERT INTO xla_rc_upgrade_rates
1322 (relationship_id
1323 ,upgrade_run_id
1324 ,from_currency
1325 ,to_currency
1326 ,denominator_rate
1327 ,numerator_rate
1328 ,conversion_rate
1329 ,precision
1330 ,minimum_accountable_unit
1331 ,creation_date
1332 ,created_by
1333 ,last_update_date
1334 ,last_updated_by
1335 ,last_update_login)
1336 VALUES(l_relationship_id
1337 ,l_upgrade_id
1338 ,l_primary_currency_code
1339 ,l_sec_alc_currency_code
1340 ,l_denominator
1341 ,l_numerator
1342 ,l_rate
1343 ,null
1344 ,null
1345 ,SYSDATE
1346 ,fnd_global.user_id
1347 ,SYSDATE
1348 ,fnd_global.user_id
1349 ,fnd_global.login_id);
1350 END IF;
1351
1352
1353 l_rates_insert := TRUE;
1354
1355 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1356
1357 SELECT count(*)
1358 INTO l_currency_count
1359 FROM xla_rc_upgrade_rates
1360 WHERE relationship_id = l_relationship_id
1361 AND upgrade_run_id = l_upgrade_id;
1362
1363 trace
1364 (p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || l_currency_count
1365 ,p_level => C_LEVEL_PROCEDURE
1366 ,p_module => l_log_module);
1367 END IF;
1368
1369 END IF;
1370
1371
1372
1373 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1374
1375 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1376 INTO l_sql_time
1377 FROM dual;
1378
1379 trace
1380 (p_msg => 'populate_rates procedure end: ' || l_sql_time
1381 ,p_level => C_LEVEL_PROCEDURE
1382 ,p_module => l_log_module);
1383 END IF;
1384
1385
1386
1387
1388 EXCEPTION WHEN others THEN
1389
1390
1391 l_error_text := SQLCODE || ' ' || SQLERRM;
1392
1393
1394 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1395 trace
1396 (p_msg => 'inside populate_rates SQL error msg = ' || l_error_text
1397 ,p_level => C_LEVEL_PROCEDURE
1398 ,p_module => l_log_module);
1399 END IF;
1400
1401 ROLLBACK;
1402 l_upgrade_status := 1;
1403
1404
1405
1406 END;
1407
1408
1409
1410
1411 PROCEDURE validate_alc
1412 IS
1413
1414 CURSOR c_alc_applications
1415 IS
1416 SELECT *
1417 FROM xla_subledgers
1418 WHERE alc_enabled_flag = 'Y';
1419
1420 l_alc_exists NUMBER DEFAULT NULL;
1421 l_log_module VARCHAR2(240);
1422
1423 BEGIN
1424
1425 IF g_log_enabled THEN
1426 l_log_module := C_DEFAULT_MODULE||'.validate_alc';
1427 END IF;
1428
1429
1430
1431 BEGIN
1432
1433
1434 SELECT 1
1435 INTO l_alc_exists
1436 FROM DUAL
1437 WHERE EXISTS (SELECT 1
1438 FROM xla_subledgers
1439 WHERE alc_enabled_flag = 'Y');
1440
1441
1442
1443 IF l_alc_exists = 1 THEN
1444 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1445 trace
1446 (p_msg => 'ALC enabled applications that will be upgraded are listed below'
1447 ,p_level => C_LEVEL_PROCEDURE
1448 ,p_module => l_log_module);
1449
1450 FOR c_alc_record IN c_alc_applications
1451 LOOP
1452 trace
1453 (p_msg => 'Application id ' || c_alc_record.application_id || ' ' || c_alc_record.je_source_name
1454 ,p_level => C_LEVEL_PROCEDURE
1455 ,p_module => l_log_module);
1456 END LOOP;
1457 END IF;
1458 END IF;
1459
1460
1461 EXCEPTION WHEN others THEN
1462
1463 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1464 trace
1465 (p_msg => 'Error: No applications are enabled for ALC in SLA'
1466 ,p_level => C_LEVEL_PROCEDURE
1467 ,p_module => l_log_module);
1468 END IF;
1469
1470
1471
1472 fnd_message.set_name('XLA','XLA_UPG_ALC_DISABLED');
1473 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1474
1475 l_upgrade_status := 1;
1476 RETURN;
1477
1478 END;
1479
1480
1481
1482 EXCEPTION WHEN others THEN
1483
1484 l_error_text := SQLCODE || ' ' || SQLERRM;
1485
1486
1487 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1488 trace
1489 (p_msg => 'inside validate_alc SQL error msg = ' || l_error_text
1490 ,p_level => C_LEVEL_PROCEDURE
1491 ,p_module => l_log_module);
1492 END IF;
1493
1494
1495 l_upgrade_status := 1;
1496
1497 END;
1498
1499
1500
1501
1502
1503 PROCEDURE validate_secondary
1504 IS
1505
1506 l_log_module VARCHAR2(240);
1507 l_primary_slam VARCHAR2(300);
1508 l_secondary_slam VARCHAR2(300);
1509
1510
1511 BEGIN
1512
1513 IF g_log_enabled THEN
1514 l_log_module := C_DEFAULT_MODULE||'.validate_secondary';
1515 END IF;
1516
1517
1518 -- Check if source and target ledger have the same SLAM
1519
1520
1521 BEGIN
1522
1523
1524 SELECT sla_accounting_method_code, chart_of_accounts_id
1525 INTO l_primary_slam, l_primary_coa
1526 FROM xla_ledger_relationships_v
1527 WHERE ledger_id = l_primary_ledger_id;
1528
1529
1530 SELECT sla_accounting_method_code, chart_of_accounts_id
1531 INTO l_secondary_slam, l_secondary_coa
1532 FROM xla_ledger_relationships_v
1533 WHERE ledger_id = l_secondary_alc_ledger_id;
1534
1535
1536 IF l_secondary_slam <> l_primary_slam THEN
1537 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1538 trace
1539 (p_msg => 'Error: Primary and Secondary Ledger have different SLAM'
1540 ,p_level => C_LEVEL_PROCEDURE
1541 ,p_module => l_log_module);
1542 END IF;
1543
1544 fnd_message.set_name('XLA','XLA_UPG_DIFF_SLAM');
1545 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1546
1547
1548
1549
1550 l_upgrade_status := 1;
1551 IF l_validation_mode = 'F' THEN
1552 RETURN;
1553 END IF;
1554 ELSE
1555 fnd_file.put_line(fnd_file.log, 'Validation successful: SLAMs are same');
1556 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1557 trace
1558 (p_msg => 'Primary and Secondary Ledger SLAM are the same'
1559 ,p_level => C_LEVEL_PROCEDURE
1560 ,p_module => l_log_module);
1561 END IF;
1562 END IF;
1563
1564
1565 EXCEPTION WHEN others THEN
1566
1567 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1568 trace
1569 (p_msg => 'Error: SLAMs or COAs not retrived'
1570 ,p_level => C_LEVEL_PROCEDURE
1571 ,p_module => l_log_module);
1572 END IF;
1573
1574 fnd_message.set_name('XLA','XLA_UPG_SLAM_COA_FAIL');
1575 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1576
1577
1578
1579
1580 END;
1581
1582
1583
1584 -- Check if chart of accounts mapping is defined
1585
1586
1587 IF l_primary_coa <> l_secondary_coa THEN
1588
1589
1590 BEGIN
1591
1592
1593 SELECT sl_coa_mapping_id
1594 INTO l_mapping_relationship_id
1595 FROM xla_ledger_relationships_v
1596 WHERE ledger_id = l_secondary_alc_ledger_id
1597 AND primary_ledger_id = l_primary_ledger_id;
1598
1599 SELECT name
1600 INTO l_coa_mapping_name
1601 FROM gl_coa_mappings
1602 WHERE coa_mapping_id = l_mapping_relationship_id;
1603
1604
1605 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1606 trace
1607 (p_msg => 'Valid chart of accounts mapping exists = ' || l_mapping_relationship_id
1608 ,p_level => C_LEVEL_PROCEDURE
1609 ,p_module => l_log_module);
1610 END IF;
1611 fnd_file.put_line(fnd_file.log, 'Validation successful: mapping exists');
1612
1613 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1614 trace
1615 (p_msg => 'Valid name for chart of accounts mapping exists = ' || l_coa_mapping_name
1616 ,p_level => C_LEVEL_PROCEDURE
1617 ,p_module => l_log_module);
1618 END IF;
1619 fnd_file.put_line(fnd_file.log, 'Validation successful: mapping name exists');
1620
1621 EXCEPTION WHEN others THEN
1622
1623 IF l_mapping_relationship_id IS NULL THEN
1624 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1625 trace
1626 (p_msg => 'Error: No chart of accounts mapping defined'
1627 ,p_level => C_LEVEL_PROCEDURE
1628 ,p_module => l_log_module);
1629 END IF;
1630
1631
1632 fnd_message.set_name('XLA','XLA_UPG_NO_COA');
1633 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1634
1635
1636
1637 l_upgrade_status := 1;
1638 IF l_validation_mode = 'F' THEN
1639 RETURN;
1640 END IF;
1641 END IF;
1642
1643
1644 IF l_coa_mapping_name IS NULL THEN
1645 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1646 trace
1647 (p_msg => 'Error: No name defined for the chart of accounts mapping'
1648 ,p_level => C_LEVEL_PROCEDURE
1649 ,p_module => l_log_module);
1650 END IF;
1651
1652 fnd_message.set_name('XLA','XLA_UPG_COA_INV_NAME');
1653 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1654
1655
1656
1657 l_upgrade_status := 1;
1658 IF l_validation_mode = 'F' THEN
1659 RETURN;
1660 END IF;
1661 END IF;
1662
1663 END;
1664
1665
1666 END IF;
1667
1668
1669 EXCEPTION WHEN others THEN
1670
1671
1672 l_error_text := SQLCODE || ' ' || SQLERRM;
1673
1674
1675 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1676 trace
1677 (p_msg => 'inside validate_secondary SQL error msg = ' || l_error_text
1678 ,p_level => C_LEVEL_PROCEDURE
1679 ,p_module => l_log_module);
1680 END IF;
1681 fnd_file.put_line(fnd_file.log, 'inside validate_secondary SQL error msg = ' || l_error_text);
1682
1683 l_upgrade_status := 1;
1684
1685 END;
1686
1687
1688
1689 PROCEDURE alc_insert
1690 IS
1691
1692 l_log_module VARCHAR2(240);
1693
1694
1695 BEGIN
1696
1697
1698 IF g_log_enabled THEN
1699 l_log_module := C_DEFAULT_MODULE||'.alc_insert';
1700 END IF;
1701
1702
1703 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1704
1705 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1706 INTO l_sql_time
1707 FROM dual;
1708
1709 trace
1710 (p_msg => 'xla_ae_headers insert SQL start: ' || l_sql_time
1711 ,p_level => C_LEVEL_PROCEDURE
1712 ,p_module => l_log_module);
1713 END IF;
1714
1715
1716
1717 INSERT /*+ append */ INTO XLA_AE_HEADERS
1718 (
1719 AE_HEADER_ID
1720 , APPLICATION_ID
1721 , LEDGER_ID
1722 , ENTITY_ID
1723 , EVENT_ID
1724 , EVENT_TYPE_CODE
1725 , ACCOUNTING_DATE
1726 , GL_TRANSFER_STATUS_CODE
1727 , GL_TRANSFER_DATE
1728 , JE_CATEGORY_NAME
1729 , ACCOUNTING_ENTRY_STATUS_CODE
1730 , ACCOUNTING_ENTRY_TYPE_CODE
1731 , AMB_CONTEXT_CODE
1732 , PRODUCT_RULE_TYPE_CODE
1733 , PRODUCT_RULE_CODE
1734 , PRODUCT_RULE_VERSION
1735 , DESCRIPTION
1736 , DOC_SEQUENCE_ID
1737 , DOC_SEQUENCE_VALUE
1738 , ACCOUNTING_BATCH_ID
1739 , COMPLETION_ACCT_SEQ_VERSION_ID
1740 , CLOSE_ACCT_SEQ_VERSION_ID
1741 , COMPLETION_ACCT_SEQ_VALUE
1742 , CLOSE_ACCT_SEQ_VALUE
1743 , BUDGET_VERSION_ID
1744 , FUNDS_STATUS_CODE
1745 , ENCUMBRANCE_TYPE_ID
1746 , BALANCE_TYPE_CODE
1747 , REFERENCE_DATE
1748 , COMPLETED_DATE
1749 , PERIOD_NAME
1750 , PACKET_ID
1751 , COMPLETION_ACCT_SEQ_ASSIGN_ID
1752 , CLOSE_ACCT_SEQ_ASSIGN_ID
1753 , DOC_CATEGORY_CODE
1754 , ATTRIBUTE_CATEGORY
1755 , ATTRIBUTE1
1756 , ATTRIBUTE2
1757 , ATTRIBUTE3
1758 , ATTRIBUTE4
1759 , ATTRIBUTE5
1760 , ATTRIBUTE6
1761 , ATTRIBUTE7
1762 , ATTRIBUTE8
1763 , ATTRIBUTE9
1764 , ATTRIBUTE10
1765 , ATTRIBUTE11
1766 , ATTRIBUTE12
1767 , ATTRIBUTE13
1768 , ATTRIBUTE14
1769 , ATTRIBUTE15
1770 , GROUP_ID
1771 , DOC_SEQUENCE_VERSION_ID
1772 , DOC_SEQUENCE_ASSIGN_ID
1773 , CREATION_DATE
1774 , CREATED_BY
1775 , LAST_UPDATE_DATE
1776 , LAST_UPDATED_BY
1777 , LAST_UPDATE_LOGIN
1778 , PROGRAM_UPDATE_DATE
1779 , PROGRAM_APPLICATION_ID
1780 , PROGRAM_ID
1781 , REQUEST_ID
1782 , UPG_BATCH_ID
1783 , UPG_SOURCE_APPLICATION_ID
1784 , UPG_VALID_FLAG
1785 , ZERO_AMOUNT_FLAG
1786 , PARENT_AE_HEADER_ID
1787 , PARENT_AE_LINE_NUM
1788 , ACCRUAL_REVERSAL_FLAG
1789 , MERGE_EVENT_ID )
1790 SELECT /*+ parallel(xah) */
1791 XLA_AE_HEADERS_S.nextval
1792 , XAH.APPLICATION_ID
1793 , l_secondary_alc_ledger_id
1794 , XAH.ENTITY_ID
1795 , XAH.EVENT_ID
1796 , XAH.EVENT_TYPE_CODE
1797 , XAH.ACCOUNTING_DATE
1798 , XAH.GL_TRANSFER_STATUS_CODE
1799 , XAH.GL_TRANSFER_DATE
1800 , XAH.JE_CATEGORY_NAME
1801 , XAH.ACCOUNTING_ENTRY_STATUS_CODE
1802 , XAH.ACCOUNTING_ENTRY_TYPE_CODE
1803 , XAH.AMB_CONTEXT_CODE
1804 , XAH.PRODUCT_RULE_TYPE_CODE
1805 , XAH.PRODUCT_RULE_CODE
1806 , XAH.PRODUCT_RULE_VERSION
1807 , XAH.DESCRIPTION ---??????
1808 , XAH.DOC_SEQUENCE_ID
1809 , XAH.DOC_SEQUENCE_VALUE
1810 , NULL -- XAH.ACCOUNTING_BATCH_ID
1811 , NULL -- XAH.COMPLETION_ACCT_SEQ_VERSION_ID
1812 , NULL -- XAH.CLOSE_ACCT_SEQ_VERSION_ID
1813 , NULL -- XAH.COMPLETION_ACCT_SEQ_VALUE
1814 , NULL -- XAH.CLOSE_ACCT_SEQ_VALUE
1815 , XAH.BUDGET_VERSION_ID
1816 , XAH.FUNDS_STATUS_CODE
1817 , XAH.ENCUMBRANCE_TYPE_ID
1818 , XAH.BALANCE_TYPE_CODE
1819 , XAH.REFERENCE_DATE
1820 , XAH.COMPLETED_DATE
1821 , XAH.PERIOD_NAME
1822 , XAH.PACKET_ID
1823 , NULL -- XAH.COMPLETION_ACCT_SEQ_ASSIGN_ID
1824 , NULL -- XAH.CLOSE_ACCT_SEQ_ASSIGN_ID
1825 , XAH.DOC_CATEGORY_CODE
1826 , XAH.ATTRIBUTE_CATEGORY
1827 , XAH.ATTRIBUTE1
1828 , XAH.ATTRIBUTE2
1829 , XAH.ATTRIBUTE3
1830 , XAH.ATTRIBUTE4
1831 , XAH.ATTRIBUTE5
1832 , XAH.ATTRIBUTE6
1833 , XAH.ATTRIBUTE7
1834 , XAH.ATTRIBUTE8
1835 , XAH.ATTRIBUTE9
1836 , XAH.ATTRIBUTE10
1837 , XAH.ATTRIBUTE11
1838 , XAH.ATTRIBUTE12
1839 , XAH.ATTRIBUTE13
1840 , XAH.ATTRIBUTE14
1841 , XAH.ATTRIBUTE15
1842 , GROUP_ID --??????
1843 , XAH.DOC_SEQUENCE_VERSION_ID
1844 , XAH.DOC_SEQUENCE_ASSIGN_ID
1845 , SYSDATE
1846 , fnd_global.user_id
1847 , SYSDATE
1848 , fnd_global.user_id
1849 , fnd_global.login_id
1850 , SYSDATE
1851 , -1 --G_PGM_APPL_ID
1852 , -1 --G_PROGRAM_ID
1853 , l_upgrade_id
1854 , XAH.AE_HEADER_ID -- UPG_BATCH_ID = Header ID of parent to be used during lines insertion
1855 , -602 -- XAH.UPG_SOURCE_APPLICATION_ID
1856 , XAH.UPG_VALID_FLAG
1857 , XAH.ZERO_AMOUNT_FLAG
1858 , NULL -- No need to populate this for historical transactions
1859 , NULL -- No need to populate this for historical transactions
1860 , XAH.ACCRUAL_REVERSAL_FLAG
1861 , XAH.MERGE_EVENT_ID
1862 FROM XLA_AE_HEADERS XAH
1863 WHERE
1864 XAH.accounting_date >= l_start_date
1865 AND XAH.accounting_date <= l_sec_alc_end_date
1866 AND XAH.ledger_id = l_primary_ledger_id
1867 AND XAH.accounting_entry_status_code = 'F'
1868 AND XAH.balance_type_code in ('A', 'E')
1869 --This would be only for Reporting ledger
1870 AND EXISTS
1871 (SELECT 1
1872 FROM xla_subledgers s
1873 WHERE s.alc_enabled_flag = 'Y'
1874 AND s.application_id =XAH.application_id);
1875
1876
1877 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1878 trace
1879 (p_msg => 'Rows inserted into xla_ae_headers: ' || SQL%ROWCOUNT
1880 ,p_level => C_LEVEL_PROCEDURE
1881 ,p_module => l_log_module);
1882
1883 END IF;
1884
1885 COMMIT;
1886 l_headers_insert := TRUE;
1887
1888
1889
1890 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1891
1892 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1893 INTO l_sql_time
1894 FROM dual;
1895
1896
1897 trace
1898 (p_msg => 'xla_ae_headers insert SQL end and xla_ae_lines SQL start: ' || l_sql_time
1899 ,p_level => C_LEVEL_PROCEDURE
1900 ,p_module => l_log_module);
1901 END IF;
1902
1903
1904
1905
1906
1907 INSERT /*+ append */ INTO XLA_AE_LINES
1908 ( AE_HEADER_ID
1909 , AE_LINE_NUM
1910 , APPLICATION_ID
1911 , CODE_COMBINATION_ID
1912 , GL_TRANSFER_MODE_CODE
1913 , GL_SL_LINK_ID
1914 , ACCOUNTING_CLASS_CODE
1915 , PARTY_ID
1916 , PARTY_SITE_ID
1917 , PARTY_TYPE_CODE
1918 , ENTERED_DR
1919 , ENTERED_CR
1920 , ACCOUNTED_DR
1921 , ACCOUNTED_CR
1922 , DESCRIPTION
1923 , STATISTICAL_AMOUNT
1924 , CURRENCY_CODE
1925 , CURRENCY_CONVERSION_DATE
1926 , CURRENCY_CONVERSION_RATE
1927 , CURRENCY_CONVERSION_TYPE
1928 , USSGL_TRANSACTION_CODE
1929 , JGZZ_RECON_REF
1930 , CONTROL_BALANCE_FLAG
1931 , ANALYTICAL_BALANCE_FLAG
1932 , ATTRIBUTE_CATEGORY
1933 , ATTRIBUTE1
1934 , ATTRIBUTE2
1935 , ATTRIBUTE3
1936 , ATTRIBUTE4
1937 , ATTRIBUTE5
1938 , ATTRIBUTE6
1939 , ATTRIBUTE7
1940 , ATTRIBUTE8
1941 , ATTRIBUTE9
1942 , ATTRIBUTE10
1943 , ATTRIBUTE11
1944 , ATTRIBUTE12
1945 , ATTRIBUTE13
1946 , ATTRIBUTE14
1947 , ATTRIBUTE15
1948 , GL_SL_LINK_TABLE
1949 , DISPLAYED_LINE_NUMBER
1950 , CREATION_DATE
1951 , CREATED_BY
1952 , LAST_UPDATE_DATE
1953 , LAST_UPDATED_BY
1954 , LAST_UPDATE_LOGIN
1955 , PROGRAM_UPDATE_DATE
1956 , PROGRAM_APPLICATION_ID
1957 , PROGRAM_ID
1958 , REQUEST_ID
1959 , UPG_BATCH_ID
1960 , UPG_TAX_REFERENCE_ID1
1961 , UPG_TAX_REFERENCE_ID2
1962 , UPG_TAX_REFERENCE_ID3
1963 , UNROUNDED_ACCOUNTED_DR
1964 , UNROUNDED_ACCOUNTED_CR
1965 , GAIN_OR_LOSS_FLAG
1966 , UNROUNDED_ENTERED_DR
1967 , UNROUNDED_ENTERED_CR
1968 , SUBSTITUTED_CCID
1969 , BUSINESS_CLASS_CODE
1970 , MPA_ACCRUAL_ENTRY_FLAG
1971 , ENCUMBRANCE_TYPE_ID
1972 , FUNDS_STATUS_CODE
1973 , MERGE_CODE_COMBINATION_ID
1974 , MERGE_PARTY_ID
1975 , MERGE_PARTY_SITE_ID
1976 , ACCOUNTING_DATE
1977 , LEDGER_ID
1978 , SOURCE_TABLE
1979 , SOURCE_ID
1980 , ACCOUNT_OVERLAY_SOURCE_ID )
1981 SELECT /*+ parallel(xah) parallel (xal) */
1982 XAH.AE_HEADER_ID
1983 , XAL.AE_LINE_NUM
1984 , XAL.APPLICATION_ID
1985 , XAL.CODE_COMBINATION_ID
1986 , XAL.GL_TRANSFER_MODE_CODE
1987 , NULL --XAL.GL_SL_LINK_ID
1988 , XAL.ACCOUNTING_CLASS_CODE
1989 , XAL.PARTY_ID
1990 , XAL.PARTY_SITE_ID
1991 , XAL.PARTY_TYPE_CODE
1992 , XAL.ENTERED_DR
1993 , XAL.ENTERED_CR
1994 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
1995 ENTERED_DR,
1996 DECODE(l_sec_alc_mau,null,
1997 ROUND(((DECODE(l_conversion_option, 'D',
1998 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
1999 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2000 XRUR.numerator_rate),l_sec_alc_precision),
2001 ROUND(((DECODE(l_conversion_option, 'D',
2002 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
2003 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2004 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
2005 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2006 ENTERED_CR,
2007 DECODE(l_sec_alc_mau,null,
2008 ROUND(((DECODE(l_conversion_option, 'D',
2009 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2010 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2011 XRUR.numerator_rate),l_sec_alc_precision),
2012 ROUND(((DECODE(l_conversion_option, 'D',
2013 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2014 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2015 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
2016 , XAL.DESCRIPTION
2017 , XAL.STATISTICAL_AMOUNT
2018 , XAL.CURRENCY_CODE
2019 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2020 null,
2021 DECODE(l_conversion_option, 'D',
2022 NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
2023 l_currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
2024 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2025 null,
2026 DECODE(l_conversion_option, 'D',
2027 NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
2028 XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
2029 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2030 null,
2031 DECODE(l_conversion_option, 'D',
2032 NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
2033 l_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
2034
2035 , NULL --XAL.USSGL_TRANSACTION_CODE
2036 , XAL.JGZZ_RECON_REF
2037 , NULL -- XAL.CONTROL_BALANCE_FLAG
2038 , XAL.ANALYTICAL_BALANCE_FLAG
2039 , XAL.ATTRIBUTE_CATEGORY
2040 , XAL.ATTRIBUTE1
2041 , XAL.ATTRIBUTE2
2042 , XAL.ATTRIBUTE3
2043 , XAL.ATTRIBUTE4
2044 , XAL.ATTRIBUTE5
2045 , XAL.ATTRIBUTE6
2046 , XAL.ATTRIBUTE7
2047 , XAL.ATTRIBUTE8
2048 , XAL.ATTRIBUTE9
2049 , XAL.ATTRIBUTE10
2050 , XAL.ATTRIBUTE11
2051 , XAL.ATTRIBUTE12
2052 , XAL.ATTRIBUTE13
2053 , XAL.ATTRIBUTE14
2054 , XAL.ATTRIBUTE15
2055 , XAL.GL_SL_LINK_TABLE
2056 , XAL.DISPLAYED_LINE_NUMBER
2057 , SYSDATE
2058 , fnd_global.user_id
2059 , SYSDATE
2060 , fnd_global.user_id
2061 , fnd_global.login_id
2062 , SYSDATE
2063 , -1 --G_PGM_APPL_ID
2064 , -1 --G_PROGRAM_ID
2065 , l_upgrade_id
2066 , XAH.UPG_BATCH_ID --XAL.UPG_BATCH_ID original header in xla_ae_headers
2067 , XAL.UPG_TAX_REFERENCE_ID1
2068 , XAL.UPG_TAX_REFERENCE_ID2
2069 , XAL.UPG_TAX_REFERENCE_ID3
2070 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2071 UNROUNDED_ENTERED_DR,
2072 DECODE(l_sec_alc_mau,null,
2073 (((DECODE(l_conversion_option, 'D',
2074 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2075 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2076 XRUR.numerator_rate)),
2077 (((DECODE(l_conversion_option, 'D',
2078 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2079 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2080 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
2081 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2082 UNROUNDED_ENTERED_CR,
2083 DECODE(l_sec_alc_mau,null,
2084 (((DECODE(l_conversion_option, 'D',
2085 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2086 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2087 XRUR.numerator_rate)),
2088 (((DECODE(l_conversion_option, 'D',
2089 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2090 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2091 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
2092 , XAL.GAIN_OR_LOSS_FLAG
2093 , XAL.UNROUNDED_ENTERED_DR
2094 , XAL.UNROUNDED_ENTERED_CR
2095 , NULL -- XAL.SUBSTITUTED_CCID
2096 , XAL.BUSINESS_CLASS_CODE
2097 , XAL.MPA_ACCRUAL_ENTRY_FLAG
2098 , XAL.ENCUMBRANCE_TYPE_ID
2099 , XAL.FUNDS_STATUS_CODE
2100 , NULL --XAL.MERGE_CODE_COMBINATION_ID
2101 , XAL.MERGE_PARTY_ID
2102 , XAL.MERGE_PARTY_SITE_ID
2103 , XAL.ACCOUNTING_DATE
2104 , l_secondary_alc_ledger_id
2105 , XAL.SOURCE_TABLE
2106 , XAL.SOURCE_ID
2107 , XAL.ACCOUNT_OVERLAY_SOURCE_ID
2108 FROM XLA_AE_HEADERS XAH
2109 ,XLA_AE_LINES XAL
2110 ,XLA_RC_UPGRADE_RATES XRUR
2111 WHERE
2112 XAH.ledger_id = l_secondary_alc_ledger_id
2113 AND XAL.ledger_id = l_primary_ledger_id
2114 AND XAH.upg_batch_id = XAL.ae_header_id
2115 AND XRUR.relationship_id = l_relationship_id
2116 AND XAH.accounting_date >= l_start_date
2117 AND XAH.accounting_date <= l_sec_alc_end_date
2118 AND XAL.gain_or_loss_flag <> 'Y'
2119 AND XRUR.from_currency = DECODE(l_conversion_option, 'D',
2120 l_primary_currency_code,
2121 XAL.currency_code)
2122 AND XRUR.upgrade_run_id = l_upgrade_id
2123 AND XRUR.to_currency = l_sec_alc_currency_code
2124 AND XAH.application_id = XAL.application_id;
2125
2126
2127
2128
2129 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2130 trace
2131 (p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT
2132 ,p_level => C_LEVEL_PROCEDURE
2133 ,p_module => l_log_module);
2134
2135 END IF;
2136
2137
2138 COMMIT;
2139 l_lines_insert := TRUE;
2140
2141
2142 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2143
2144 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2145 INTO l_sql_time
2146 FROM dual;
2147
2148 trace
2149 (p_msg => 'xla_ae_lines insert SQL end: ' || l_sql_time
2150 ,p_level => C_LEVEL_PROCEDURE
2151 ,p_module => l_log_module);
2152 END IF;
2153
2154
2155
2156
2157
2158
2159
2160 EXCEPTION
2161 WHEN OTHERS THEN
2162
2163 l_error_text := SQLCODE || SQLERRM;
2164
2165
2166 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2167 trace
2168 (p_msg => 'inside alc lines insert. SQL error msg = ' || l_error_text
2169 ,p_level => C_LEVEL_PROCEDURE
2170 ,p_module => l_log_module);
2171 END IF;
2172
2173
2174 ROLLBACK;
2175 l_upgrade_status := 1;
2176
2177 END;
2178
2179
2180 PROCEDURE secondary_insert
2181 IS
2182
2183 l_log_module VARCHAR2(240);
2184
2185 BEGIN
2186
2187
2188 IF g_log_enabled THEN
2189 l_log_module := C_DEFAULT_MODULE||'.secondary_insert';
2190 END IF;
2191
2192
2193
2194 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2195
2196 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2197 INTO l_sql_time
2198 FROM dual;
2199
2200 trace
2201 (p_msg => 'xla_ae_headers insert SQL start: ' || l_sql_time
2202 ,p_level => C_LEVEL_PROCEDURE
2203 ,p_module => l_log_module);
2204 END IF;
2205
2206
2207
2208 INSERT /*+ append */ INTO XLA_AE_HEADERS
2209 (
2210 AE_HEADER_ID
2211 , APPLICATION_ID
2212 , LEDGER_ID
2213 , ENTITY_ID
2214 , EVENT_ID
2215 , EVENT_TYPE_CODE
2216 , ACCOUNTING_DATE
2217 , GL_TRANSFER_STATUS_CODE
2218 , GL_TRANSFER_DATE
2219 , JE_CATEGORY_NAME
2220 , ACCOUNTING_ENTRY_STATUS_CODE
2221 , ACCOUNTING_ENTRY_TYPE_CODE
2222 , AMB_CONTEXT_CODE
2223 , PRODUCT_RULE_TYPE_CODE
2224 , PRODUCT_RULE_CODE
2225 , PRODUCT_RULE_VERSION
2226 , DESCRIPTION
2227 , DOC_SEQUENCE_ID
2228 , DOC_SEQUENCE_VALUE
2229 , ACCOUNTING_BATCH_ID
2230 , COMPLETION_ACCT_SEQ_VERSION_ID
2231 , CLOSE_ACCT_SEQ_VERSION_ID
2232 , COMPLETION_ACCT_SEQ_VALUE
2233 , CLOSE_ACCT_SEQ_VALUE
2234 , BUDGET_VERSION_ID
2235 , FUNDS_STATUS_CODE
2236 , ENCUMBRANCE_TYPE_ID
2237 , BALANCE_TYPE_CODE
2238 , REFERENCE_DATE
2239 , COMPLETED_DATE
2240 , PERIOD_NAME
2241 , PACKET_ID
2242 , COMPLETION_ACCT_SEQ_ASSIGN_ID
2243 , CLOSE_ACCT_SEQ_ASSIGN_ID
2244 , DOC_CATEGORY_CODE
2245 , ATTRIBUTE_CATEGORY
2246 , ATTRIBUTE1
2247 , ATTRIBUTE2
2248 , ATTRIBUTE3
2249 , ATTRIBUTE4
2250 , ATTRIBUTE5
2251 , ATTRIBUTE6
2252 , ATTRIBUTE7
2253 , ATTRIBUTE8
2254 , ATTRIBUTE9
2255 , ATTRIBUTE10
2256 , ATTRIBUTE11
2257 , ATTRIBUTE12
2258 , ATTRIBUTE13
2259 , ATTRIBUTE14
2260 , ATTRIBUTE15
2261 , GROUP_ID
2262 , DOC_SEQUENCE_VERSION_ID
2263 , DOC_SEQUENCE_ASSIGN_ID
2264 , CREATION_DATE
2265 , CREATED_BY
2266 , LAST_UPDATE_DATE
2267 , LAST_UPDATED_BY
2268 , LAST_UPDATE_LOGIN
2269 , PROGRAM_UPDATE_DATE
2270 , PROGRAM_APPLICATION_ID
2271 , PROGRAM_ID
2272 , REQUEST_ID
2273 , UPG_BATCH_ID
2274 , UPG_SOURCE_APPLICATION_ID
2275 , UPG_VALID_FLAG
2276 , ZERO_AMOUNT_FLAG
2277 , PARENT_AE_HEADER_ID
2278 , PARENT_AE_LINE_NUM
2279 , ACCRUAL_REVERSAL_FLAG
2280 , MERGE_EVENT_ID )
2281 SELECT /*+ parallel(xah) */
2282 XLA_AE_HEADERS_S.nextval
2283 , XAH.APPLICATION_ID
2284 , l_secondary_alc_ledger_id
2285 , XAH.ENTITY_ID
2286 , XAH.EVENT_ID
2287 , XAH.EVENT_TYPE_CODE
2288 , XAH.ACCOUNTING_DATE
2289 , XAH.GL_TRANSFER_STATUS_CODE
2290 , XAH.GL_TRANSFER_DATE
2291 , XAH.JE_CATEGORY_NAME
2292 , XAH.ACCOUNTING_ENTRY_STATUS_CODE
2293 , XAH.ACCOUNTING_ENTRY_TYPE_CODE
2294 , XAH.AMB_CONTEXT_CODE
2295 , XAH.PRODUCT_RULE_TYPE_CODE
2296 , XAH.PRODUCT_RULE_CODE
2297 , XAH.PRODUCT_RULE_VERSION
2298 , XAH.DESCRIPTION ---??????
2299 , XAH.DOC_SEQUENCE_ID
2300 , XAH.DOC_SEQUENCE_VALUE
2301 , NULL --XAH.ACCOUNTING_BATCH_ID
2302 , NULL --XAH.COMPLETION_ACCT_SEQ_VERSION_ID
2303 , NULL --XAH.CLOSE_ACCT_SEQ_VERSION_ID
2304 , NULL --XAH.COMPLETION_ACCT_SEQ_VALUE
2305 , NULL --XAH.CLOSE_ACCT_SEQ_VALUE
2306 , XAH.BUDGET_VERSION_ID
2307 , XAH.FUNDS_STATUS_CODE
2308 , XAH.ENCUMBRANCE_TYPE_ID
2309 , XAH.BALANCE_TYPE_CODE
2310 , XAH.REFERENCE_DATE
2311 , XAH.COMPLETED_DATE
2312 , XAH.PERIOD_NAME
2313 , XAH.PACKET_ID
2314 , NULL -- XAH.COMPLETION_ACCT_SEQ_ASSIGN_ID
2315 , NULL --XAH.CLOSE_ACCT_SEQ_ASSIGN_ID
2316 , XAH.DOC_CATEGORY_CODE
2317 , XAH.ATTRIBUTE_CATEGORY
2318 , XAH.ATTRIBUTE1
2319 , XAH.ATTRIBUTE2
2320 , XAH.ATTRIBUTE3
2321 , XAH.ATTRIBUTE4
2322 , XAH.ATTRIBUTE5
2323 , XAH.ATTRIBUTE6
2324 , XAH.ATTRIBUTE7
2325 , XAH.ATTRIBUTE8
2326 , XAH.ATTRIBUTE9
2327 , XAH.ATTRIBUTE10
2328 , XAH.ATTRIBUTE11
2329 , XAH.ATTRIBUTE12
2330 , XAH.ATTRIBUTE13
2331 , XAH.ATTRIBUTE14
2332 , XAH.ATTRIBUTE15
2333 , GROUP_ID --??????
2334 , XAH.DOC_SEQUENCE_VERSION_ID
2335 , XAH.DOC_SEQUENCE_ASSIGN_ID
2336 , SYSDATE
2337 , fnd_global.user_id
2338 , SYSDATE
2339 , fnd_global.user_id
2340 , fnd_global.login_id
2341 , SYSDATE
2342 , -1 --G_PGM_APPL_ID
2343 , -1 --G_PROGRAM_ID
2344 , l_upgrade_id
2345 , XAH.AE_HEADER_ID -- UPG_BATCH_ID = Header ID of parent to be used during lines insertion
2346 , -602 -- XAH.UPG_SOURCE_APPLICATION_ID
2347 , XAH.UPG_VALID_FLAG
2348 , XAH.ZERO_AMOUNT_FLAG
2349 , NULL -- No need to populate this for historical transactions
2350 , NULL -- No need to populate this for historical transactions
2351 , XAH.ACCRUAL_REVERSAL_FLAG
2352 , XAH.MERGE_EVENT_ID
2353 FROM XLA_AE_HEADERS XAH
2354 WHERE
2355 XAH.accounting_date >= l_start_date
2356 AND XAH.accounting_date <= l_sec_alc_end_date
2357 AND XAH.ledger_id = l_primary_ledger_id
2358 AND XAH.accounting_entry_status_code = 'F'
2359 AND XAH.balance_type_code in ('A', 'E');
2360
2361
2362
2363 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2364 trace
2365 (p_msg => 'Rows inserted into xla_ae_headers: ' || SQL%ROWCOUNT
2366 ,p_level => C_LEVEL_PROCEDURE
2367 ,p_module => l_log_module);
2368
2369 END IF;
2370
2371 COMMIT;
2372 l_headers_insert := TRUE;
2373
2374
2375
2376 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2377
2378 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2379 INTO l_sql_time
2380 FROM dual;
2381
2382
2383 trace
2384 (p_msg => 'xla_ae_headers insert SQL end and xla_ae_lines SQL start: ' || l_sql_time
2385 ,p_level => C_LEVEL_PROCEDURE
2386 ,p_module => l_log_module);
2387 END IF;
2388
2389
2390
2391
2392
2393 INSERT /*+ append */ INTO XLA_AE_LINES
2394 ( AE_HEADER_ID
2395 , AE_LINE_NUM
2396 , APPLICATION_ID
2397 , CODE_COMBINATION_ID
2398 , GL_TRANSFER_MODE_CODE
2399 , GL_SL_LINK_ID
2400 , ACCOUNTING_CLASS_CODE
2401 , PARTY_ID
2402 , PARTY_SITE_ID
2403 , PARTY_TYPE_CODE
2404 , ENTERED_DR
2405 , ENTERED_CR
2406 , ACCOUNTED_DR
2407 , ACCOUNTED_CR
2408 , DESCRIPTION
2409 , STATISTICAL_AMOUNT
2410 , CURRENCY_CODE
2411 , CURRENCY_CONVERSION_DATE
2412 , CURRENCY_CONVERSION_RATE
2413 , CURRENCY_CONVERSION_TYPE
2414 , USSGL_TRANSACTION_CODE
2415 , JGZZ_RECON_REF
2416 , CONTROL_BALANCE_FLAG
2417 , ANALYTICAL_BALANCE_FLAG
2418 , ATTRIBUTE_CATEGORY
2419 , ATTRIBUTE1
2420 , ATTRIBUTE2
2421 , ATTRIBUTE3
2422 , ATTRIBUTE4
2423 , ATTRIBUTE5
2424 , ATTRIBUTE6
2425 , ATTRIBUTE7
2426 , ATTRIBUTE8
2427 , ATTRIBUTE9
2428 , ATTRIBUTE10
2429 , ATTRIBUTE11
2430 , ATTRIBUTE12
2431 , ATTRIBUTE13
2432 , ATTRIBUTE14
2433 , ATTRIBUTE15
2434 , GL_SL_LINK_TABLE
2435 , DISPLAYED_LINE_NUMBER
2436 , CREATION_DATE
2437 , CREATED_BY
2438 , LAST_UPDATE_DATE
2439 , LAST_UPDATED_BY
2440 , LAST_UPDATE_LOGIN
2441 , PROGRAM_UPDATE_DATE
2442 , PROGRAM_APPLICATION_ID
2443 , PROGRAM_ID
2444 , REQUEST_ID
2445 , UPG_BATCH_ID
2446 , UPG_TAX_REFERENCE_ID1
2447 , UPG_TAX_REFERENCE_ID2
2448 , UPG_TAX_REFERENCE_ID3
2449 , UNROUNDED_ACCOUNTED_DR
2450 , UNROUNDED_ACCOUNTED_CR
2451 , GAIN_OR_LOSS_FLAG
2452 , UNROUNDED_ENTERED_DR
2453 , UNROUNDED_ENTERED_CR
2454 , SUBSTITUTED_CCID
2455 , BUSINESS_CLASS_CODE
2456 , MPA_ACCRUAL_ENTRY_FLAG
2457 , ENCUMBRANCE_TYPE_ID
2458 , FUNDS_STATUS_CODE
2459 , MERGE_CODE_COMBINATION_ID
2460 , MERGE_PARTY_ID
2461 , MERGE_PARTY_SITE_ID
2462 , ACCOUNTING_DATE
2463 , LEDGER_ID
2464 , SOURCE_TABLE
2465 , SOURCE_ID
2466 , ACCOUNT_OVERLAY_SOURCE_ID )
2467 SELECT /*+ parallel(xah) parallel (xal) */
2468 XAH.AE_HEADER_ID
2469 , XAL.AE_LINE_NUM
2470 , XAL.APPLICATION_ID
2471 , XAL.CODE_COMBINATION_ID
2472 , XAL.GL_TRANSFER_MODE_CODE
2473 , NULL --XAL.GL_SL_LINK_ID
2474 , XAL.ACCOUNTING_CLASS_CODE
2475 , XAL.PARTY_ID
2476 , XAL.PARTY_SITE_ID
2477 , XAL.PARTY_TYPE_CODE
2478 , XAL.ENTERED_DR
2479 , XAL.ENTERED_CR
2480 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2481 ENTERED_DR,
2482 DECODE(l_sec_alc_mau,null,
2483 ROUND(((DECODE(l_conversion_option, 'D',
2484 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
2485 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2486 XRUR.numerator_rate),l_sec_alc_precision),
2487 ROUND(((DECODE(l_conversion_option, 'D',
2488 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
2489 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2490 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
2491 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2492 ENTERED_CR,
2493 DECODE(l_sec_alc_mau,null,
2494 ROUND(((DECODE(l_conversion_option, 'D',
2495 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2496 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2497 XRUR.numerator_rate),l_sec_alc_precision),
2498 ROUND(((DECODE(l_conversion_option, 'D',
2499 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2500 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2501 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau))
2502 , XAL.DESCRIPTION
2503 , XAL.STATISTICAL_AMOUNT
2504 , XAL.CURRENCY_CODE
2505 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2506 null,
2507 DECODE(l_conversion_option, 'D',
2508 NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
2509 l_currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
2510 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2511 null,
2512 DECODE(l_conversion_option, 'D',
2513 NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
2514 XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
2515 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2516 null,
2517 DECODE(l_conversion_option, 'D',
2518 NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
2519 l_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
2520
2521 , NULL --XAL.USSGL_TRANSACTION_CODE
2522 , XAL.JGZZ_RECON_REF
2523 , NULL --XAL.CONTROL_BALANCE_FLAG
2524 , XAL.ANALYTICAL_BALANCE_FLAG
2525 , XAL.ATTRIBUTE_CATEGORY
2526 , XAL.ATTRIBUTE1
2527 , XAL.ATTRIBUTE2
2528 , XAL.ATTRIBUTE3
2529 , XAL.ATTRIBUTE4
2530 , XAL.ATTRIBUTE5
2531 , XAL.ATTRIBUTE6
2532 , XAL.ATTRIBUTE7
2533 , XAL.ATTRIBUTE8
2534 , XAL.ATTRIBUTE9
2535 , XAL.ATTRIBUTE10
2536 , XAL.ATTRIBUTE11
2537 , XAL.ATTRIBUTE12
2538 , XAL.ATTRIBUTE13
2539 , XAL.ATTRIBUTE14
2540 , XAL.ATTRIBUTE15
2541 , XAL.GL_SL_LINK_TABLE
2542 , XAL.DISPLAYED_LINE_NUMBER
2543 , SYSDATE
2544 , fnd_global.user_id
2545 , SYSDATE
2546 , fnd_global.user_id
2547 , fnd_global.login_id
2548 , SYSDATE
2549 , -1 --G_PGM_APPL_ID
2550 , -1 --G_PROGRAM_ID
2551 , l_upgrade_id
2552 , XAH.UPG_BATCH_ID --XAL.UPG_BATCH_ID original header in xla_ae_headers
2553 , XAL.UPG_TAX_REFERENCE_ID1
2554 , XAL.UPG_TAX_REFERENCE_ID2
2555 , XAL.UPG_TAX_REFERENCE_ID3
2556 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2557 UNROUNDED_ENTERED_DR,
2558 DECODE(l_sec_alc_mau,null,
2559 (((DECODE(l_conversion_option, 'D',
2560 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2561 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2562 XRUR.numerator_rate)),
2563 (((DECODE(l_conversion_option, 'D',
2564 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2565 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2566 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
2567 , DECODE(XAL.currency_code,l_sec_alc_currency_code,
2568 UNROUNDED_ENTERED_CR,
2569 DECODE(l_sec_alc_mau,null,
2570 (((DECODE(l_conversion_option, 'D',
2571 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2572 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2573 XRUR.numerator_rate)),
2574 (((DECODE(l_conversion_option, 'D',
2575 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2576 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2577 XRUR.numerator_rate)/l_sec_alc_mau)*l_sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
2578 , XAL.GAIN_OR_LOSS_FLAG
2579 , XAL.UNROUNDED_ENTERED_DR
2580 , XAL.UNROUNDED_ENTERED_CR
2581 , NULL -- XAL.SUBSTITUTED_CCID
2582 , XAL.BUSINESS_CLASS_CODE
2583 , XAL.MPA_ACCRUAL_ENTRY_FLAG
2584 , XAL.ENCUMBRANCE_TYPE_ID
2585 , XAL.FUNDS_STATUS_CODE
2586 , NULL --XAL.MERGE_CODE_COMBINATION_ID
2587 , XAL.MERGE_PARTY_ID
2588 , XAL.MERGE_PARTY_SITE_ID
2589 , XAL.ACCOUNTING_DATE
2590 , l_secondary_alc_ledger_id
2591 , XAL.SOURCE_TABLE
2592 , XAL.SOURCE_ID
2593 , XAL.ACCOUNT_OVERLAY_SOURCE_ID
2594 FROM XLA_AE_HEADERS XAH
2595 ,XLA_AE_LINES XAL
2596 ,XLA_RC_UPGRADE_RATES XRUR
2597 WHERE
2598 XAH.ledger_id = l_secondary_alc_ledger_id
2599 AND XAL.ledger_id = l_primary_ledger_id
2600 AND XAH.upg_batch_id = XAL.ae_header_id
2601 AND XRUR.relationship_id = l_relationship_id
2602 AND XAH.accounting_date >= l_start_date
2603 AND XAH.accounting_date <= l_sec_alc_end_date
2604 AND XAL.gain_or_loss_flag <> 'Y'
2605 AND XRUR.from_currency = DECODE(l_conversion_option, 'D',
2606 l_primary_currency_code,
2607 XAL.currency_code)
2608 AND XRUR.upgrade_run_id = l_upgrade_id
2609 AND XRUR.to_currency = l_sec_alc_currency_code
2610 AND XAH.application_id = XAL.application_id;
2611
2612
2613
2614 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2615 trace
2616 (p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT
2617 ,p_level => C_LEVEL_PROCEDURE
2618 ,p_module => l_log_module);
2619
2620 END IF;
2621
2622
2623 COMMIT;
2624 l_lines_insert := TRUE;
2625
2626
2627 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2628
2629 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2630 INTO l_sql_time
2631 FROM dual;
2632
2633 trace
2634 (p_msg => 'xla_ae_lines insert SQL end: ' || l_sql_time
2635 ,p_level => C_LEVEL_PROCEDURE
2636 ,p_module => l_log_module);
2637 END IF;
2638
2639
2640
2641
2642
2643 EXCEPTION
2644 WHEN OTHERS THEN
2645
2646 l_error_text := SQLCODE || SQLERRM;
2647
2648
2649 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2650 trace
2651 (p_msg => 'inside secondary lines insert. SQL error msg = ' || l_error_text
2652 ,p_level => C_LEVEL_PROCEDURE
2653 ,p_module => l_log_module);
2654 END IF;
2655
2656
2657
2658 ROLLBACK;
2659 l_upgrade_status := 1;
2660
2661 END;
2662
2663
2664
2665 PROCEDURE ccid_check
2666 IS
2667
2668
2669 l_dynamic_inserts VARCHAR2(10);
2670 l_ccid_check NUMBER DEFAULT NULL;
2671 l_log_module VARCHAR2(240);
2672
2673
2674 CURSOR c_invalid_ccids
2675 IS
2676 SELECT from_ccid
2677 FROM gl_accts_map_int_gt
2678 WHERE coa_mapping_id = l_mapping_relationship_id
2679 AND to_ccid IS NULL;
2680
2681
2682 BEGIN
2683
2684 IF g_log_enabled THEN
2685 l_log_module := C_DEFAULT_MODULE||'.ccid_check';
2686 END IF;
2687
2688
2689 IF l_primary_coa = l_secondary_coa THEN
2690 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2691 trace
2692 (p_msg => 'Exiting ccid_check since chart of accounts are the same for both secondary ledgers'
2693 ,p_level => C_LEVEL_PROCEDURE
2694 ,p_module => l_log_module);
2695 END IF;
2696 fnd_file.put_line(fnd_file.log,'Not running ccid_check since chart of accounts are the same for both ledgers');
2697 RETURN;
2698 END IF;
2699
2700
2701 SELECT dynamic_inserts_allowed_flag
2702 INTO l_dynamic_inserts
2703 FROM fnd_id_flex_structures_vl
2704 WHERE application_id = 101
2705 AND id_flex_code = 'GL#'
2706 AND id_flex_num = l_primary_coa; -- why is this flag for primary???
2707
2708
2709 DELETE FROM gl_accts_map_int_gt; -- bug 4564062
2710
2711
2712 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2713
2714 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2715 INTO l_sql_time
2716 FROM dual;
2717
2718 trace
2719 (p_msg => 'ccid check SQL start: ' || l_sql_time
2720 ,p_level => C_LEVEL_PROCEDURE
2721 ,p_module => l_log_module);
2722 END IF;
2723
2724
2725
2726 INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
2727 SELECT distinct l_mapping_relationship_id, code_combination_id
2728 FROM xla_ae_lines xal
2729 WHERE XAL.gain_or_loss_flag <> 'Y'
2730 AND EXISTS (SELECT 1
2731 FROM xla_ae_headers xah
2732 WHERE xah.ae_header_id = xal.ae_header_id
2733 AND XAH.accounting_date >= l_start_date
2734 AND XAH.accounting_date <= l_sec_alc_end_date
2735 AND XAH.ledger_id = l_primary_ledger_id
2736 AND XAH.accounting_entry_status_code = 'F'
2737 AND XAH.balance_type_code in ('A', 'E')
2738 );
2739
2740
2741 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2742 trace
2743 (p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt = ' || SQL%ROWCOUNT
2744 ,p_level => C_LEVEL_PROCEDURE
2745 ,p_module => l_log_module);
2746 END IF;
2747
2748 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2749
2750 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2751 INTO l_sql_time
2752 FROM dual;
2753
2754 trace
2755 (p_msg => 'ccid check SQL end: ' || l_sql_time
2756 ,p_level => C_LEVEL_PROCEDURE
2757 ,p_module => l_log_module);
2758 END IF;
2759
2760
2761 GL_ACCOUNTS_MAP_GRP.MAP(mapping_name => l_coa_mapping_name
2762 ,create_ccid => (NVL(l_dynamic_inserts,'N') ='Y' )
2763 ,debug => g_log_enabled);
2764
2765 BEGIN
2766
2767 SELECT 1
2768 INTO l_ccid_check
2769 FROM DUAL
2770 WHERE EXISTS (SELECT 1
2771 FROM gl_accts_map_int_gt
2772 WHERE coa_mapping_id = l_mapping_relationship_id
2773 AND to_ccid IS NULL);
2774
2775 IF l_ccid_check = 1 THEN
2776
2777 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2778 trace
2779 (p_msg => 'Headers generated with -1 CCID '
2780 ,p_level => C_LEVEL_PROCEDURE
2781 ,p_module => l_log_module);
2782 END IF;
2783
2784
2785 fnd_message.set_name('XLA','XLA_UPG_CCID_NEG_ONE');
2786 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
2787
2788
2789 FOR c_invalid_ccids_record IN c_invalid_ccids
2790 LOOP
2791
2792 fnd_file.put_line(fnd_file.log, ' Could not generate seconadary/alc code_combination_id for = ' || c_invalid_ccids_record.from_ccid);
2793
2794
2795 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2796 trace
2797 (p_msg => 'Could not generate to_ccid for from_ccid = ' || c_invalid_ccids_record.from_ccid
2798 ,p_level => C_LEVEL_PROCEDURE
2799 ,p_module => l_log_module);
2800 END IF;
2801
2802
2803 END LOOP;
2804
2805
2806 ROLLBACK;
2807 l_upgrade_status := 1;
2808 RETURN;
2809
2810 END IF;
2811
2812
2813
2814 EXCEPTION WHEN others THEN
2815
2816 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2817 trace
2818 (p_msg => 'No Headers Generated with CCID -1. End of check_ccid'
2819 ,p_level => C_LEVEL_PROCEDURE
2820 ,p_module => l_log_module);
2821 END IF;
2822
2823 fnd_file.put_line(fnd_file.log, 'Validation successful: No Headers Generated with CCID -1');
2824
2825
2826 COMMIT;
2827
2828 END;
2829
2830
2831
2832 EXCEPTION WHEN others THEN
2833
2834
2835 l_error_text := SQLCODE || SQLERRM;
2836
2837 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2838 trace
2839 (p_msg => 'inside check_ccid SQL error msg = ' || l_error_text
2840 ,p_level => C_LEVEL_PROCEDURE
2841 ,p_module => l_log_module);
2842 END IF;
2843
2844
2845 END;
2846
2847
2848
2849
2850 PROCEDURE map_ccid
2851 IS
2852
2853
2854 CURSOR c_invalid_ccids
2855 IS
2856 SELECT from_ccid
2857 FROM gl_accts_map_int_gt
2858 WHERE coa_mapping_id = l_mapping_relationship_id
2859 AND to_ccid IS NULL;
2860
2861
2862 l_log_module VARCHAR2(240);
2863 l_dynamic_inserts VARCHAR2(10);
2864 l_ccid_check NUMBER DEFAULT NULL;
2865
2866 BEGIN
2867
2868 IF g_log_enabled THEN
2869 l_log_module := C_DEFAULT_MODULE||'.map_ccid';
2870 END IF;
2871
2872
2873 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2874 trace
2875 (p_msg => 'Begin of MAP_CCID'
2876 ,p_level => C_LEVEL_PROCEDURE
2877 ,p_module => l_log_module);
2878 END IF;
2879
2880
2881
2882 IF l_primary_coa = l_secondary_coa THEN
2883 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2884 trace
2885 (p_msg => 'Exiting MAP_CCID since chart of accounts are the same for both secondary ledgers'
2886 ,p_level => C_LEVEL_PROCEDURE
2887 ,p_module => l_log_module);
2888 END IF;
2889 RETURN;
2890 END IF;
2891
2892
2893
2894 -- At this stage, secondary ledgers should have same ccid values as primary ledger
2895
2896 SELECT dynamic_inserts_allowed_flag
2897 INTO l_dynamic_inserts
2898 FROM fnd_id_flex_structures_vl
2899 WHERE application_id = 101
2900 AND id_flex_code = 'GL#'
2901 AND id_flex_num = l_primary_coa; -- why is this flag for primary???
2902
2903
2904 DELETE FROM gl_accts_map_int_gt; -- bug 4564062
2905
2906 INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
2907 SELECT distinct l_mapping_relationship_id, code_combination_id
2908 FROM xla_ae_lines
2909 WHERE request_id = l_upgrade_id;
2910
2911
2912
2913 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2914 trace
2915 (p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt = ' || SQL%ROWCOUNT
2916 ,p_level => C_LEVEL_PROCEDURE
2917 ,p_module => l_log_module);
2918 END IF;
2919
2920
2921
2922 GL_ACCOUNTS_MAP_GRP.MAP(mapping_name => l_coa_mapping_name
2923 ,create_ccid => (NVL(l_dynamic_inserts,'N') ='Y' )
2924 ,debug => g_log_enabled);
2925
2926
2927
2928 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2929
2930 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2931 INTO l_sql_time
2932 FROM dual;
2933
2934 trace
2935 (p_msg => 'ccid update SQL start: ' || l_sql_time
2936 ,p_level => C_LEVEL_PROCEDURE
2937 ,p_module => l_log_module);
2938 END IF;
2939
2940
2941 UPDATE xla_ae_lines xal
2942 SET code_combination_id = (SELECT (nvl(gl_int.to_ccid, -1))
2943 FROM gl_accts_map_int_gt gl_int
2944 WHERE xal.code_combination_id = gl_int.from_ccid
2945 AND gl_int.coa_mapping_id = l_mapping_relationship_id)
2946 WHERE xal.request_id = l_upgrade_id;
2947
2948
2949 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2950 trace
2951 (p_msg => 'Rows in lines updated with new ccid = ' || SQL%ROWCOUNT
2952 ,p_level => C_LEVEL_PROCEDURE
2953 ,p_module => l_log_module);
2954
2955
2956 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2957 INTO l_sql_time
2958 FROM dual;
2959
2960 trace
2961 (p_msg => 'ccid update SQL end: ' || l_sql_time
2962 ,p_level => C_LEVEL_PROCEDURE
2963 ,p_module => l_log_module);
2964 END IF;
2965
2966
2967
2968
2969 BEGIN
2970
2971 SELECT 1
2972 INTO l_ccid_check
2973 FROM DUAL
2974 WHERE EXISTS (SELECT 1
2975 FROM xla_ae_lines
2976 WHERE code_combination_id = -1
2977 AND request_id = l_upgrade_id);
2978
2979
2980 IF l_ccid_check = 1 THEN
2981
2982 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2983 trace
2984 (p_msg => 'Headers generated with -1 CCID '
2985 ,p_level => C_LEVEL_PROCEDURE
2986 ,p_module => l_log_module);
2987 END IF;
2988
2989
2990 fnd_message.set_name('XLA','XLA_UPG_CCID_NEG_ONE');
2991 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
2992
2993
2994 FOR c_invalid_ccids_record IN c_invalid_ccids
2995 LOOP
2996
2997 fnd_file.put_line(fnd_file.log, ' Could not generate seconadary/alc code_combination_id for = ' || c_invalid_ccids_record.from_ccid);
2998
2999
3000 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3001 trace
3002 (p_msg => 'Could not generate to_ccid for from_ccid = ' || c_invalid_ccids_record.from_ccid
3003 ,p_level => C_LEVEL_PROCEDURE
3004 ,p_module => l_log_module);
3005 END IF;
3006
3007
3008 END LOOP;
3009
3010
3011 ROLLBACK;
3012 l_upgrade_status := 1;
3013 RETURN;
3014
3015 END IF;
3016
3017
3018
3019 EXCEPTION WHEN others THEN
3020
3021 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3022 trace
3023 (p_msg => 'No Headers Generated with CCID -1. End of MAP_CCID'
3024 ,p_level => C_LEVEL_PROCEDURE
3025 ,p_module => l_log_module);
3026 END IF;
3027
3028 fnd_file.put_line(fnd_file.log, 'Validation success: No Headers Generated with CCID -1');
3029
3030
3031 COMMIT;
3032
3033 END;
3034
3035
3036 EXCEPTION WHEN others THEN
3037
3038
3039 l_error_text := SQLCODE || SQLERRM;
3040
3041 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3042 trace
3043 (p_msg => 'inside map_ccid SQL error msg = ' || l_error_text
3044 ,p_level => C_LEVEL_PROCEDURE
3045 ,p_module => l_log_module);
3046 END IF;
3047
3048
3049
3050 ROLLBACK;
3051 l_upgrade_status := 1;
3052
3053 END;
3054
3055
3056
3057
3058 PROCEDURE calendar_convert IS
3059
3060 l_log_module VARCHAR2(240);
3061 l_primary_cal_set_name VARCHAR2(100);
3062 l_primary_cal_per_type VARCHAR2(100);
3063 l_primary_sec_alc_set_name VARCHAR2(100);
3064 l_primary_sec_alc_per_type VARCHAR2(100);
3065
3066
3067 BEGIN
3068
3069 IF g_log_enabled THEN
3070 l_log_module := C_DEFAULT_MODULE||'.calendar_convert';
3071 END IF;
3072
3073
3074 SELECT period_set_name, accounted_period_type
3075 INTO l_primary_cal_set_name, l_primary_cal_per_type
3076 FROM gl_ledgers
3077 WHERE ledger_id = l_primary_ledger_id;
3078
3079
3080 SELECT period_set_name, accounted_period_type
3081 INTO l_primary_sec_alc_set_name, l_primary_sec_alc_per_type
3082 FROM gl_ledgers
3083 WHERE ledger_id = l_secondary_alc_ledger_id;
3084
3085
3086 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3087 trace
3088 (p_msg => 'Primary calendar name and period type is ' || l_primary_cal_set_name || ' and ' || l_primary_cal_per_type
3089 ,p_level => C_LEVEL_PROCEDURE
3090 ,p_module => l_log_module);
3091
3092 trace
3093 (p_msg => 'Secondary calendar name and period type is ' || l_primary_sec_alc_set_name || ' and ' || l_primary_sec_alc_per_type
3094 ,p_level => C_LEVEL_PROCEDURE
3095 ,p_module => l_log_module);
3096 END IF;
3097
3098 IF ((l_primary_cal_set_name <> l_primary_sec_alc_set_name) OR (l_primary_cal_per_type <> l_primary_sec_alc_per_type)) THEN
3099
3100 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3101
3102 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3103 INTO l_sql_time
3104 FROM dual;
3105
3106 trace
3107 (p_msg => 'calendar conversion SQL start: ' || l_sql_time
3108 ,p_level => C_LEVEL_PROCEDURE
3109 ,p_module => l_log_module);
3110 END IF;
3111
3112
3113
3114 UPDATE xla_ae_headers xah
3115 SET period_name = (SELECT gls.period_name
3116 FROM gl_period_statuses gls
3117 WHERE gls.ledger_id = l_secondary_alc_ledger_id
3118 AND gls.application_id = 101
3119 AND gls.adjustment_period_flag = 'N'
3120 AND xah.accounting_date BETWEEN gls.start_date AND gls.end_date
3121 )
3122 WHERE xah.ledger_id = l_secondary_alc_ledger_id
3123 AND xah.request_id = l_upgrade_id;
3124
3125
3126 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3127 trace
3128 (p_msg => 'Rows updated in headers with new calendar period = ' || SQL%ROWCOUNT
3129 ,p_level => C_LEVEL_PROCEDURE
3130 ,p_module => l_log_module);
3131
3132
3133 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3134 INTO l_sql_time
3135 FROM dual;
3136
3137 trace
3138 (p_msg => 'calendar convert SQL end: ' || l_sql_time
3139 ,p_level => C_LEVEL_PROCEDURE
3140 ,p_module => l_log_module);
3141 END IF;
3142
3143 COMMIT;
3144
3145 ELSE
3146 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3147 trace
3148 (p_msg => 'primary and secondary calendars are the same, so calendar conversion was not run'
3149 ,p_level => C_LEVEL_PROCEDURE
3150 ,p_module => l_log_module);
3151 END IF;
3152
3153 END IF;
3154
3155
3156 EXCEPTION WHEN others THEN
3157
3158
3159 l_error_text := SQLCODE || SQLERRM;
3160
3161 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3162 trace
3163 (p_msg => 'inside calendar convert SQL error msg = ' || l_error_text
3164 ,p_level => C_LEVEL_PROCEDURE
3165 ,p_module => l_log_module);
3166 END IF;
3167
3168 ROLLBACK;
3169 l_upgrade_status := 1;
3170 END;
3171
3172
3173
3174 PROCEDURE line_num_resequence IS
3175
3176 l_log_module VARCHAR2(240);
3177 l_array_gain_loss_header xla_cmp_source_pkg.t_array_Num;
3178
3179
3180 CURSOR c_gain_loss_headers
3181 IS
3182 SELECT distinct ae_header_id
3183 FROM xla_ae_headers XAH
3184 WHERE XAH.accounting_date >= l_start_date
3185 AND XAH.accounting_date <= l_sec_alc_end_date
3186 AND XAH.ledger_id = l_primary_ledger_id
3187 AND XAH.accounting_entry_status_code = 'F'
3188 AND XAH.balance_type_code in ('A', 'E')
3189 AND EXISTS (SELECT 1
3190 FROM xla_ae_lines xal
3191 WHERE xal.ae_header_id = xah.ae_header_id
3192 AND xal.gain_or_loss_flag = 'Y'
3193 AND xah.application_id = xal.application_id);
3194
3195
3196 BEGIN
3197
3198 IF g_log_enabled THEN
3199 l_log_module := C_DEFAULT_MODULE||'.line_num_resequence';
3200 END IF;
3201
3202
3203 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3204
3205 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3206 INTO l_sql_time
3207 FROM dual;
3208
3209 trace
3210 (p_msg => 'displayed line num SQL start: ' || l_sql_time
3211 ,p_level => C_LEVEL_PROCEDURE
3212 ,p_module => l_log_module);
3213 END IF;
3214
3215
3216 OPEN c_gain_loss_headers;
3217 FETCH c_gain_loss_headers BULK COLLECT INTO l_array_gain_loss_header;
3218 CLOSE c_gain_loss_headers;
3219
3220
3221
3222 IF (l_array_gain_loss_header.COUNT > 0) THEN
3223
3224
3225 FORALL i IN 1..l_array_gain_loss_header.COUNT
3226 UPDATE xla_ae_lines xx
3227 SET displayed_line_number = (SELECT new_line from
3228 (SELECT ae_header_id,
3229 decode(nvl(accounted_cr, 0) + nvl(accounted_dr, 0), 0, -1, 1) *
3230 (ROW_NUMBER() over (PARTITION BY ae_header_id order by
3231 DECODE(SIGN(abs(nvl(accounted_dr, 0)) - abs(nvl(accounted_cr, 0))), 1, 3, -1, 2, 0) desc,
3232 abs(nvl(accounted_dr, 0) + nvl(accounted_cr, 0)) desc,
3233 SIGN(nvl(accounted_dr, 0) + nvl(accounted_cr, 0)) desc)) new_line,
3234 ae_line_num,
3235 displayed_line_number
3236 FROM xla_ae_lines
3237 WHERE request_id = l_upgrade_id
3238 AND upg_batch_id = l_array_gain_loss_header(i)
3239 ) yy
3240 WHERE yy.ae_header_id = xx.ae_header_id
3241 AND yy.ae_line_num = xx.ae_line_num)
3242 WHERE request_id = l_upgrade_id
3243 AND upg_batch_id = l_array_gain_loss_header(i);
3244
3245
3246
3247
3248 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3249 trace
3250 (p_msg => 'Rows updated in lines with new sequence number, only gain loss headers updated = ' || l_array_gain_loss_header.COUNT
3251 ,p_level => C_LEVEL_PROCEDURE
3252 ,p_module => l_log_module);
3253
3254
3255 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3256 INTO l_sql_time
3257 FROM dual;
3258
3259 trace
3260 (p_msg => 'displayed line num SQL end: ' || l_sql_time
3261 ,p_level => C_LEVEL_PROCEDURE
3262 ,p_module => l_log_module);
3263 END IF;
3264
3265
3266
3267 ELSE
3268
3269 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3270 trace
3271 (p_msg => 'No gain or loss headers'
3272 ,p_level => C_LEVEL_PROCEDURE
3273 ,p_module => l_log_module);
3274
3275
3276 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3277 INTO l_sql_time
3278 FROM dual;
3279
3280 trace
3281 (p_msg => 'displayed line num SQL end: ' || l_sql_time
3282 ,p_level => C_LEVEL_PROCEDURE
3283 ,p_module => l_log_module);
3284 END IF;
3285 END IF;
3286
3287
3288
3289
3290
3291 COMMIT;
3292
3293
3294 EXCEPTION WHEN others THEN
3295
3296
3297 l_error_text := SQLCODE || SQLERRM;
3298 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3299 trace
3300 (p_msg => 'inside line resequence. SQL error msg = ' || l_error_text
3301 ,p_level => C_LEVEL_PROCEDURE
3302 ,p_module => l_log_module);
3303 END IF;
3304
3305
3306 ROLLBACK;
3307 l_upgrade_status := 1;
3308
3309 END;
3310
3311
3312
3313
3314
3315
3316
3317 PROCEDURE insert_links_segments
3318 IS
3319
3320 l_log_module VARCHAR2(240);
3321
3322 BEGIN
3323
3324
3325 IF g_log_enabled THEN
3326 l_log_module := C_DEFAULT_MODULE||'.insert_links_segments';
3327 END IF;
3328
3329
3330 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3331
3332 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3333 INTO l_sql_time
3334 FROM dual;
3335
3336 trace
3337 (p_msg => 'distribution links SQL start time: ' || l_sql_time
3338 ,p_level => C_LEVEL_PROCEDURE
3339 ,p_module => l_log_module);
3340 END IF;
3341
3342
3343
3344
3345 INSERT /*+ append */ INTO XLA_DISTRIBUTION_LINKS
3346 (
3347 APPLICATION_ID
3348 , EVENT_ID
3349 , AE_HEADER_ID
3350 , AE_LINE_NUM
3351 , SOURCE_DISTRIBUTION_TYPE
3352 , SOURCE_DISTRIBUTION_ID_CHAR_1
3353 , SOURCE_DISTRIBUTION_ID_CHAR_2
3354 , SOURCE_DISTRIBUTION_ID_CHAR_3
3355 , SOURCE_DISTRIBUTION_ID_CHAR_4
3356 , SOURCE_DISTRIBUTION_ID_CHAR_5
3357 , SOURCE_DISTRIBUTION_ID_NUM_1
3358 , SOURCE_DISTRIBUTION_ID_NUM_2
3359 , SOURCE_DISTRIBUTION_ID_NUM_3
3360 , SOURCE_DISTRIBUTION_ID_NUM_4
3361 , SOURCE_DISTRIBUTION_ID_NUM_5
3362 , TAX_LINE_REF_ID
3363 , TAX_SUMMARY_LINE_REF_ID
3364 , TAX_REC_NREC_DIST_REF_ID
3365 , STATISTICAL_AMOUNT
3366 , REF_AE_HEADER_ID
3367 , REF_TEMP_LINE_NUM
3368 , ACCOUNTING_LINE_CODE
3369 , ACCOUNTING_LINE_TYPE_CODE
3370 , MERGE_DUPLICATE_CODE
3371 , TEMP_LINE_NUM
3372 , REF_EVENT_ID
3373 , LINE_DEFINITION_OWNER_CODE
3374 , LINE_DEFINITION_CODE
3375 , EVENT_CLASS_CODE
3376 , EVENT_TYPE_CODE
3377 , UPG_BATCH_ID
3378 , CALCULATE_ACCTD_AMTS_FLAG
3379 , CALCULATE_G_L_AMTS_FLAG
3380 , ROUNDING_CLASS_CODE
3381 , DOCUMENT_ROUNDING_LEVEL
3382 , UNROUNDED_ENTERED_DR
3383 , UNROUNDED_ENTERED_CR
3384 , DOC_ROUNDING_ENTERED_AMT
3385 , DOC_ROUNDING_ACCTD_AMT
3386 , UNROUNDED_ACCOUNTED_CR
3387 , UNROUNDED_ACCOUNTED_DR
3388 , APPLIED_TO_APPLICATION_ID
3389 , APPLIED_TO_ENTITY_CODE
3390 , APPLIED_TO_ENTITY_ID
3391 , APPLIED_TO_SOURCE_ID_NUM_1
3392 , APPLIED_TO_SOURCE_ID_NUM_2
3393 , APPLIED_TO_SOURCE_ID_NUM_3
3394 , APPLIED_TO_SOURCE_ID_NUM_4
3395 , APPLIED_TO_SOURCE_ID_CHAR_1
3396 , APPLIED_TO_SOURCE_ID_CHAR_2
3397 , APPLIED_TO_SOURCE_ID_CHAR_3
3398 , APPLIED_TO_SOURCE_ID_CHAR_4
3399 , APPLIED_TO_DISTRIBUTION_TYPE
3400 , APPLIED_TO_DIST_ID_NUM_1
3401 , APPLIED_TO_DIST_ID_NUM_2
3402 , APPLIED_TO_DIST_ID_NUM_3
3403 , APPLIED_TO_DIST_ID_NUM_4
3404 , APPLIED_TO_DIST_ID_NUM_5
3405 , APPLIED_TO_DIST_ID_CHAR_1
3406 , APPLIED_TO_DIST_ID_CHAR_2
3407 , APPLIED_TO_DIST_ID_CHAR_3
3408 , APPLIED_TO_DIST_ID_CHAR_4
3409 , APPLIED_TO_DIST_ID_CHAR_5
3410 , ALLOC_TO_APPLICATION_ID
3411 , ALLOC_TO_ENTITY_CODE
3412 , ALLOC_TO_SOURCE_ID_NUM_1
3413 , ALLOC_TO_SOURCE_ID_NUM_2
3414 , ALLOC_TO_SOURCE_ID_NUM_3
3415 , ALLOC_TO_SOURCE_ID_NUM_4
3416 , ALLOC_TO_SOURCE_ID_CHAR_1
3417 , ALLOC_TO_SOURCE_ID_CHAR_2
3418 , ALLOC_TO_SOURCE_ID_CHAR_3
3419 , ALLOC_TO_SOURCE_ID_CHAR_4
3420 , ALLOC_TO_DISTRIBUTION_TYPE
3421 , ALLOC_TO_DIST_ID_NUM_1
3422 , ALLOC_TO_DIST_ID_NUM_2
3423 , ALLOC_TO_DIST_ID_NUM_3
3424 , ALLOC_TO_DIST_ID_NUM_4
3425 , ALLOC_TO_DIST_ID_NUM_5
3426 , ALLOC_TO_DIST_ID_CHAR_1
3427 , ALLOC_TO_DIST_ID_CHAR_2
3428 , ALLOC_TO_DIST_ID_CHAR_3
3429 , ALLOC_TO_DIST_ID_CHAR_4
3430 , ALLOC_TO_DIST_ID_CHAR_5
3431 , GAIN_OR_LOSS_REF)
3432 SELECT /*+ parallel(xal) parallel(xdl) */
3433 XDL.APPLICATION_ID
3434 , XDL.EVENT_ID
3435 , XAL.AE_HEADER_ID
3436 , XDL.AE_LINE_NUM
3437 , XDL.SOURCE_DISTRIBUTION_TYPE
3438 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_1
3439 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_2
3440 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_3
3441 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_4
3442 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_5
3443 , XDL.SOURCE_DISTRIBUTION_ID_NUM_1
3444 , XDL.SOURCE_DISTRIBUTION_ID_NUM_2
3445 , XDL.SOURCE_DISTRIBUTION_ID_NUM_3
3446 , XDL.SOURCE_DISTRIBUTION_ID_NUM_4
3447 , XDL.SOURCE_DISTRIBUTION_ID_NUM_5
3448 , XDL.TAX_LINE_REF_ID
3449 , XDL.TAX_SUMMARY_LINE_REF_ID
3450 , XDL.TAX_REC_NREC_DIST_REF_ID
3451 , XDL.STATISTICAL_AMOUNT
3452 , decode(XDL.AE_HEADER_ID, XDL.REF_AE_HEADER_ID, XAL.AE_HEADER_ID, -1*XDL.REF_AE_HEADER_ID)
3453 , null -- XDL.REF_TEMP_LINE_NUM What should be inserted
3454 , XDL.ACCOUNTING_LINE_CODE
3455 , XDL.ACCOUNTING_LINE_TYPE_CODE
3456 , XDL.MERGE_DUPLICATE_CODE
3457 , XDL.TEMP_LINE_NUM
3458 , XDL.REF_EVENT_ID
3459 , XDL.LINE_DEFINITION_OWNER_CODE
3460 , XDL.LINE_DEFINITION_CODE
3461 , XDL.EVENT_CLASS_CODE
3462 , XDL.EVENT_TYPE_CODE
3463 , XAL.UPG_BATCH_ID -- original header in xla_ae_headers
3464 , XDL.CALCULATE_ACCTD_AMTS_FLAG
3465 , XDL.CALCULATE_G_L_AMTS_FLAG
3466 , XDL.ROUNDING_CLASS_CODE
3467 , XDL.DOCUMENT_ROUNDING_LEVEL
3468 , XAL.UNROUNDED_ENTERED_DR -- retrieved from lines
3469 , XAL.UNROUNDED_ENTERED_CR -- retrieved from lines
3470 , XDL.DOC_ROUNDING_ENTERED_AMT
3471 , XDL.DOC_ROUNDING_ACCTD_AMT
3472 , XAL.UNROUNDED_ACCOUNTED_CR -- retrieved from lines
3473 , XAL.UNROUNDED_ACCOUNTED_DR -- retrieved from lines
3474 , XDL.APPLIED_TO_APPLICATION_ID
3475 , XDL.APPLIED_TO_ENTITY_CODE
3476 , XDL.APPLIED_TO_ENTITY_ID
3477 , XDL.APPLIED_TO_SOURCE_ID_NUM_1
3478 , XDL.APPLIED_TO_SOURCE_ID_NUM_2
3479 , XDL.APPLIED_TO_SOURCE_ID_NUM_3
3480 , XDL.APPLIED_TO_SOURCE_ID_NUM_4
3481 , XDL.APPLIED_TO_SOURCE_ID_CHAR_1
3482 , XDL.APPLIED_TO_SOURCE_ID_CHAR_2
3483 , XDL.APPLIED_TO_SOURCE_ID_CHAR_3
3484 , XDL.APPLIED_TO_SOURCE_ID_CHAR_4
3485 , XDL.APPLIED_TO_DISTRIBUTION_TYPE
3486 , XDL.APPLIED_TO_DIST_ID_NUM_1
3487 , XDL.APPLIED_TO_DIST_ID_NUM_2
3488 , XDL.APPLIED_TO_DIST_ID_NUM_3
3489 , XDL.APPLIED_TO_DIST_ID_NUM_4
3490 , XDL.APPLIED_TO_DIST_ID_NUM_5
3491 , XDL.APPLIED_TO_DIST_ID_CHAR_1
3492 , XDL.APPLIED_TO_DIST_ID_CHAR_2
3493 , XDL.APPLIED_TO_DIST_ID_CHAR_3
3494 , XDL.APPLIED_TO_DIST_ID_CHAR_4
3495 , XDL.APPLIED_TO_DIST_ID_CHAR_5
3496 , XDL.ALLOC_TO_APPLICATION_ID
3497 , XDL.ALLOC_TO_ENTITY_CODE
3498 , XDL.ALLOC_TO_SOURCE_ID_NUM_1
3499 , XDL.ALLOC_TO_SOURCE_ID_NUM_2
3500 , XDL.ALLOC_TO_SOURCE_ID_NUM_3
3501 , XDL.ALLOC_TO_SOURCE_ID_NUM_4
3502 , XDL.ALLOC_TO_SOURCE_ID_CHAR_1
3503 , XDL.ALLOC_TO_SOURCE_ID_CHAR_2
3504 , XDL.ALLOC_TO_SOURCE_ID_CHAR_3
3505 , XDL.ALLOC_TO_SOURCE_ID_CHAR_4
3506 , XDL.ALLOC_TO_DISTRIBUTION_TYPE
3507 , XDL.ALLOC_TO_DIST_ID_NUM_1
3508 , XDL.ALLOC_TO_DIST_ID_NUM_2
3509 , XDL.ALLOC_TO_DIST_ID_NUM_3
3510 , XDL.ALLOC_TO_DIST_ID_NUM_4
3511 , XDL.ALLOC_TO_DIST_ID_NUM_5
3512 , XDL.ALLOC_TO_DIST_ID_CHAR_1
3513 , XDL.ALLOC_TO_DIST_ID_CHAR_2
3514 , XDL.ALLOC_TO_DIST_ID_CHAR_3
3515 , XDL.ALLOC_TO_DIST_ID_CHAR_4
3516 , XDL.ALLOC_TO_DIST_ID_CHAR_5
3517 , l_upgrade_id -- concurrent request id stored in column XDL.GAIN_OR_LOSS_REF
3518 FROM XLA_DISTRIBUTION_LINKS XDL
3519 ,XLA_AE_LINES XAL
3520 WHERE
3521 XAL.upg_batch_id = XDL.ae_header_id
3522 AND XAL.ledger_id = l_secondary_alc_ledger_id
3523 AND XAL.accounting_date >= l_start_date
3524 AND XAL.accounting_date <= l_sec_alc_end_date
3525 AND XAL.gain_or_loss_flag <> 'Y'
3526 AND XAL.ae_line_num = XDL.ae_line_num
3527 AND XAL.application_id = XDL.application_id;
3528
3529
3530
3531
3532
3533 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3534 trace
3535 (p_msg => 'Rows inserted into xla_distribution_links = ' || SQL%ROWCOUNT
3536 ,p_level => C_LEVEL_PROCEDURE
3537 ,p_module => l_log_module);
3538
3539 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3540 INTO l_sql_time
3541 FROM dual;
3542
3543 trace
3544 (p_msg => 'distribution links insert SQL end, segment insert start (if applicable): ' || l_sql_time
3545 ,p_level => C_LEVEL_PROCEDURE
3546 ,p_module => l_log_module);
3547 END IF;
3548
3549 COMMIT;
3550 l_links_insert := TRUE;
3551
3552 /*
3553
3554 -- section commented bug 7201652. update can be used in post script if needed. query for some cases
3555 -- still throws cannot update to null error. ref_ae_header should not have an impact on reversal/bflow
3556 -- since joins are not based on this column
3557
3558 UPDATE xla_distribution_links xdl
3559 SET ref_ae_header_id = (SELECT ae_header_id
3560 FROM xla_ae_headers xah
3561 WHERE xah.upg_batch_id = -1 * xdl.ref_ae_header_id
3562 --AND xah.application_id = xdl.application_id
3563 AND xah.event_id = xdl.ref_event_id
3564 AND xah.ledger_id = l_secondary_alc_ledger_id
3565 AND xah.request_id = l_upgrade_id
3566 )
3567 WHERE xdl.gain_or_loss_ref = to_char(l_upgrade_id)
3568 AND xdl.ref_ae_header_id < 0
3569 AND xdl.ref_event_id IS NOT NULL
3570 AND xdl.ref_event_id <> -1 * xdl.ref_ae_header_id
3571 AND EXISTS (SELECT 1
3572 FROM xla_ae_headers xah2
3573 WHERE xah2.upg_batch_id = -1* xdl.ref_ae_header_id); -- exists condition added since ref headers might refer to secondary headers we havent created because of start dates or status codes of primary data
3574
3575
3576 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3577 trace
3578 (p_msg => 'Rows updated in xla_distribution_links with negative ref headers = ' || SQL%ROWCOUNT
3579 ,p_level => C_LEVEL_PROCEDURE
3580 ,p_module => l_log_module);
3581
3582 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3583 INTO l_sql_time
3584 FROM dual;
3585
3586 trace
3587 (p_msg => 'distribution links update SQL end: ' || l_sql_time
3588 ,p_level => C_LEVEL_PROCEDURE
3589 ,p_module => l_log_module);
3590 END IF;
3591
3592
3593 COMMIT;
3594
3595 */
3596
3597 IF (l_ledger_category_code = 'ALC') OR ( (l_ledger_category_code = 'SECONDARY') and (l_primary_coa = l_secondary_coa) ) THEN
3598
3599 INSERT INTO XLA_AE_SEGMENT_VALUES
3600 ( ae_header_id
3601 , segment_type_code
3602 , segment_value
3603 , ae_lines_count
3604 , upg_batch_id)
3605 SELECT xah.ae_header_id
3606 , xasv.segment_type_code
3607 , xasv.segment_value
3608 , xasv.ae_lines_count
3609 , l_upgrade_id
3610 FROM xla_ae_headers xah
3611 , xla_ae_segment_values xasv
3612 WHERE XAH.upg_batch_id = XASV.ae_header_id
3613 AND XAH.accounting_date >= l_start_date
3614 AND XAH.accounting_date <= l_sec_alc_end_date
3615 AND XAH.ledger_id = l_secondary_alc_ledger_id;
3616
3617
3618
3619 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3620 trace
3621 (p_msg => 'Rows inserted into xla_ae_segment_values: ' || SQL%ROWCOUNT
3622 ,p_level => C_LEVEL_PROCEDURE
3623 ,p_module => l_log_module);
3624
3625 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3626 INTO l_sql_time
3627 FROM dual;
3628
3629 trace
3630 (p_msg => 'xla_ae_segment SQL end time: ' || l_sql_time
3631 ,p_level => C_LEVEL_PROCEDURE
3632 ,p_module => l_log_module);
3633 END IF;
3634
3635 COMMIT;
3636 l_ae_insert := TRUE;
3637
3638 ELSE
3639 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3640 trace
3641 (p_msg => 'Rows not inserted into segment values since conditions not met'
3642 ,p_level => C_LEVEL_PROCEDURE
3643 ,p_module => l_log_module);
3644
3645 END IF;
3646
3647 END IF;
3648
3649
3650
3651
3652
3653
3654 EXCEPTION WHEN others THEN
3655
3656
3657 l_error_text := SQLCODE || SQLERRM;
3658
3659
3660 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3661 trace
3662 (p_msg => 'inside distribution links insert. SQL error msg = ' || l_error_text
3663 ,p_level => C_LEVEL_PROCEDURE
3664 ,p_module => l_log_module);
3665 END IF;
3666
3667 l_upgrade_status := 1;
3668
3669
3670 END;
3671
3672
3673
3674 PROCEDURE post_validation
3675 IS
3676
3677
3678 CURSOR c_validate_lines
3679 IS
3680 SELECT ae_header_id
3681 FROM xla_ae_lines
3682 WHERE request_id = l_upgrade_id
3683 GROUP BY ae_header_id
3684 HAVING sum(entered_cr) <> sum(entered_dr)
3685 OR sum(accounted_dr) <> sum(accounted_cr)
3686 OR sum(unrounded_entered_cr) <> sum(unrounded_entered_dr)
3687 OR sum(unrounded_accounted_dr) <> sum(unrounded_accounted_cr);
3688
3689 l_error_lines NUMBER DEFAULT NULL;
3690 l_log_module VARCHAR2(240);
3691
3692
3693 BEGIN
3694
3695
3696 IF g_log_enabled THEN
3697 l_log_module := C_DEFAULT_MODULE||'.post_validation';
3698 END IF;
3699
3700 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3701
3702 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3703 INTO l_sql_time
3704 FROM dual;
3705
3706 trace
3707 (p_msg => 'post validation procedure start time: ' || l_sql_time
3708 ,p_level => C_LEVEL_PROCEDURE
3709 ,p_module => l_log_module);
3710
3711 END IF;
3712
3713
3714 BEGIN
3715
3716 SELECT 1
3717 INTO l_error_lines
3718 FROM DUAL
3719 WHERE EXISTS (SELECT 1
3720 FROM xla_ae_lines
3721 WHERE request_id = l_upgrade_id
3722 GROUP BY ae_header_id
3723 HAVING sum(entered_cr) <> sum(entered_dr)
3724 OR sum(accounted_dr) <> sum(accounted_cr)
3725 OR sum(unrounded_entered_cr) <> sum(unrounded_entered_dr)
3726 OR sum(unrounded_accounted_dr) <> sum(unrounded_accounted_cr));
3727
3728
3729 IF l_error_lines = 1 THEN
3730 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3731 trace
3732 (p_msg => 'Manual Adjustment may be needed since entered/accounted sums differ. It will be needed for these headers below = '
3733 ,p_level => C_LEVEL_PROCEDURE
3734 ,p_module => l_log_module);
3735 END IF;
3736
3737
3738 fnd_message.set_name('XLA','XLA_UPG_MANUAL_ADJUSTMENT');
3739 fnd_file.put_line(fnd_file.log, fnd_message.get);
3740
3741
3742 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3743
3744 FOR lines_record IN c_validate_lines
3745 LOOP
3746 trace
3747 (p_msg => 'Manual Adjustment needed for ae_header_id = ' || lines_record.ae_header_id
3748 ,p_level => C_LEVEL_PROCEDURE
3749 ,p_module => l_log_module);
3750 END LOOP;
3751 END IF;
3752 END IF;
3753
3754
3755 EXCEPTION WHEN others THEN
3756 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3757 trace
3758 (p_msg => 'Manual Adjustment not needed for any lines'
3759 ,p_level => C_LEVEL_PROCEDURE
3760 ,p_module => l_log_module);
3761 END IF;
3762
3763 fnd_file.put_line(fnd_file.log, 'Manual Adjustment not needed for any lines');
3764
3765
3766 END;
3767
3768
3769
3770 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3771
3772
3773 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3774 INTO l_sql_time
3775 FROM dual;
3776
3777 trace
3778 (p_msg => 'post validation procedure end time: ' || l_sql_time
3779 ,p_level => C_LEVEL_PROCEDURE
3780 ,p_module => l_log_module);
3781
3782 END IF;
3783
3784
3785
3786 EXCEPTION WHEN others THEN
3787
3788 l_error_text := SQLCODE || SQLERRM;
3789
3790
3791 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3792 trace
3793 (p_msg => 'inside post validation procedure. SQL error msg = ' || l_error_text
3794 ,p_level => C_LEVEL_PROCEDURE
3795 ,p_module => l_log_module);
3796 END IF;
3797
3798 l_upgrade_status := 1;
3799 END;
3800
3801 PROCEDURE crash_recovery
3802 IS
3803
3804 l_log_module VARCHAR2(240);
3805 last_crash_run NUMBER := 0;
3806 l_relationship_exists NUMBER DEFAULT NULL;
3807
3808 BEGIN
3809
3810 IF g_log_enabled THEN
3811 l_log_module := C_DEFAULT_MODULE||'.crash_recovery';
3812 END IF;
3813
3814 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3815 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3816 INTO l_sql_time
3817 FROM dual;
3818 trace
3819 (p_msg => 'crash recovery procedure start time: ' || l_sql_time
3820 ,p_level => C_LEVEL_PROCEDURE
3821 ,p_module => l_log_module);
3822 END IF;
3823
3824 BEGIN
3825
3826 SELECT 1
3827 INTO l_relationship_exists
3828 FROM DUAL
3829 WHERE EXISTS (SELECT 1
3830 FROM gl_ledger_relationships
3831 WHERE primary_ledger_id = l_primary_ledger_id
3832 AND target_ledger_id = l_secondary_alc_ledger_id
3833 AND relationship_enabled_flag = 'Y');
3834
3835 SELECT min(relationship_id)
3836 INTO l_relationship_id
3837 FROM gl_ledger_relationships
3838 WHERE primary_ledger_id = l_primary_ledger_id
3839 AND target_ledger_id = l_secondary_alc_ledger_id
3840 AND relationship_enabled_flag = 'Y';
3841
3842 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3843 trace
3844 (p_msg => 'Relationship between primary and secondary/alc is valid. Relationship id = ' || l_relationship_id
3845 ,p_level => C_LEVEL_PROCEDURE
3846 ,p_module => l_log_module);
3847 END IF;
3848
3849 fnd_file.put_line(fnd_file.log, 'Validation successful: ledger relationship');
3850
3851
3852 EXCEPTION when OTHERS THEN
3853 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3854 trace
3855 (p_msg => 'Error: Relationship between primary and secondary/alc does not exist, or is disabled'
3856 ,p_level => C_LEVEL_PROCEDURE
3857 ,p_module => l_log_module);
3858 END IF;
3859
3860 fnd_message.set_name('XLA','XLA_UPG_GL_REL_FAIL');
3861 fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
3862 l_upgrade_status := 1;
3863 RETURN;
3864
3865 END;
3866
3867
3868 BEGIN
3869
3870 SELECT max(upgrade_run_id)
3871 INTO last_crash_run
3872 FROM xla_rc_upgrade_rates rc, gl_ledger_relationships gl
3873 WHERE gl.relationship_id = rc.relationship_id
3874 AND gl.relationship_id = l_relationship_id
3875 AND rc.relationship_id = l_relationship_id -- redundant
3876 AND gl.hist_conv_status_code = 'RUNNING';
3877
3878
3879 EXCEPTION WHEN others THEN
3880
3881 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3882 trace
3883 (p_msg => 'Recovery Not Needed. DB Tables Consistent after last run'
3884 ,p_level => C_LEVEL_PROCEDURE
3885 ,p_module => l_log_module);
3886 END IF;
3887
3888 fnd_file.put_line(fnd_file.log, 'Recovery Not Needed. DB Tables Consistent');
3889 RETURN;
3890
3891 END;
3892
3893
3894
3895 IF last_crash_run IS NULL or last_crash_run = 0 THEN
3896
3897 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3898 trace
3899 (p_msg => 'Recovery Not Needed. DB Tables Consistent after last run'
3900 ,p_level => C_LEVEL_PROCEDURE
3901 ,p_module => l_log_module);
3902 END IF;
3903
3904 fnd_file.put_line(fnd_file.log, 'Recovery Not Needed. DB Tables Consistent');
3905 RETURN;
3906
3907 END IF;
3908
3909 -- set current run id to the last run id (when crash occured)
3910 l_upgrade_id := last_crash_run;
3911
3912
3913 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3914 trace
3915 (p_msg => 'last crash run is' || last_crash_run
3916 ,p_level => C_LEVEL_PROCEDURE
3917 ,p_module => l_log_module);
3918 END IF;
3919
3920 -- assume that data was entered in all tables during last run, then terminated
3921
3922 l_headers_insert := TRUE;
3923 l_lines_insert := TRUE;
3924 l_rates_insert := TRUE;
3925 l_links_insert := TRUE;
3926 l_ae_insert := TRUE;
3927
3928 upgrade_rollback();
3929
3930
3931 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3932
3933 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3934 INTO l_sql_time
3935 FROM dual;
3936
3937 trace
3938 (p_msg => 'crash recovery procedure end time: ' || l_sql_time
3939 ,p_level => C_LEVEL_PROCEDURE
3940 ,p_module => l_log_module);
3941
3942 END IF;
3943
3944 EXCEPTION WHEN others THEN
3945
3946 l_error_text := SQLCODE || SQLERRM;
3947
3948
3949 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3950 trace
3951 (p_msg => 'inside crash recovery procedure. SQL error msg = ' || l_error_text
3952 ,p_level => C_LEVEL_PROCEDURE
3953 ,p_module => l_log_module);
3954 END IF;
3955
3956 l_upgrade_status := 1;
3957 END;
3958
3959
3960
3961 BEGIN
3962 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3963 g_log_enabled := fnd_log.test
3964 (log_level => g_log_level
3965 ,MODULE => C_DEFAULT_MODULE);
3966
3967 IF NOT g_log_enabled THEN
3968 g_log_level := C_LEVEL_LOG_DISABLED;
3969 END IF;
3970
3971
3972 END xla_hist_ledger_upg_pkg;