[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.25 2011/04/14 05:54:00 vgopiset 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 PHASE 1 |
17 | 02-JAN-10 Kapil Kumar PHASE 2 (redesign) |
18 | 31-MAR-10 Kapil Kumar PHASE 2.1 |
19 | 06-Apr-11 VGOPISET Bug#12329205 Calculate_Acct_Amts_Flag |
20 | to be maded as Y for correct Bflow Amounts |
21 | calculation for Upstream entry in new ledger|
22 +======================================================================*/
23
24
25 --=============================================================================
26 -- *********** Global Variables **********
27 --=============================================================================
28
29 g_application_id NUMBER; -- concurrent program user input
30 g_primary_ledger_id NUMBER; -- concurrent program user input
31 g_secondary_alc_ledger_id NUMBER; -- concurrent program user input
32 g_start_date DATE; -- concurrent program user input
33 g_conversion_option VARCHAR2(10); -- concurrent program user input if secondary else derived
34 g_currency_conversion_type VARCHAR2(100); -- concurrent program user input if secondary else derived
35 g_currency_conversion_date DATE; -- concurrent program user input if secondary else derived
36 g_validation_mode VARCHAR2(20); -- concurrent program user input
37 g_batch_size NUMBER; -- concurrent program user input
38 g_num_workers NUMBER; -- concurrent program user input
39
40 g_sec_alc_end_date DATE; -- derived value
41 g_sec_alc_min_acctng_batch_id NUMBER; -- added by vgopiset
42 g_upgrade_id NUMBER;
43 g_relationship_id NUMBER;
44 g_script_name VARCHAR2(100);
45
46 g_ledger_category_code VARCHAR2(30);
47 g_primary_currency_code VARCHAR2(15);
48 g_sec_alc_currency_code VARCHAR2(15);
49 g_sec_alc_mau NUMBER;
50 g_sec_alc_precision NUMBER;
51 g_primary_slam VARCHAR2(300);
52 g_secondary_slam VARCHAR2(300);
53 g_secondary_slam_type VARCHAR2(10);
54 g_primary_slam_type VARCHAR2(10);
55 g_secondary_budget VARCHAR2(300);
56 g_primary_budget VARCHAR2(300);
57 g_primary_aad VARCHAR2(30);
58 g_primary_aad_owner VARCHAR2(1);
59 g_secondary_aad VARCHAR2(30);
60 g_secondary_aad_owner VARCHAR2(1);
61 g_primary_coa NUMBER;
62 g_secondary_coa NUMBER;
63 g_mapping_relationship_id NUMBER;
64 g_coa_mapping_name VARCHAR2(50);
65 g_dynamic_inserts VARCHAR2(10);
66 g_primary_cal_set_name VARCHAR2(100);
67 g_primary_cal_per_type VARCHAR2(100);
68 g_primary_sec_alc_set_name VARCHAR2(100);
69 g_primary_sec_alc_per_type VARCHAR2(100);
70
71 g_untransferred_headers NUMBER DEFAULT NULL;
72 g_alc_exists NUMBER DEFAULT NULL;
73 g_rate_exists NUMBER DEFAULT NULL;
74
75 TYPE workerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
76 g_worker workerList;
77
78 g_ccid_map VARCHAR2(10);
79 g_calendar_convert VARCHAR2(10);
80 g_dynamic_flag VARCHAR2(10);
81 g_mapping_rel_id NUMBER;
82 g_coa_map_name VARCHAR2(50);
83
84 g_currency_count NUMBER;
85 g_numerator NUMBER;
86 g_denominator NUMBER;
87 g_rate NUMBER;
88
89 g_failed_runs NUMBER;
90 g_success_runs NUMBER;
91 g_recovery_failed_runs NUMBER;
92
93 g_validation_failed EXCEPTION;
94 g_child_failed EXCEPTION;
95
96 g_error_text VARCHAR2(10000);
97
98
99
100 --=============================================================================
101 -- *********** Local Trace Routine **********
102 --=============================================================================
103 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
104 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
105 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
106 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
107 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
108 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
109
110 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
111 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_hist_ledger_upg_pkg';
112
113 g_log_level NUMBER;
114 g_log_enabled BOOLEAN;
115
116
117 C_LOG_SIZE CONSTANT NUMBER := 2000;
118
119 PROCEDURE trace
120 (p_msg IN VARCHAR2
121 ,p_level IN NUMBER
122 ,p_module IN VARCHAR2 DEFAULT C_DEFAULT_MODULE)
123 IS
124
125 l_max NUMBER;
126 l_pos NUMBER := 1;
127
128 BEGIN
129
130 l_pos := 1;
131
132 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
133 fnd_log.message(p_level, p_module);
134 ELSIF p_level >= g_log_level THEN
135
136 l_max := length(p_msg);
137 IF l_max <= C_LOG_SIZE THEN
138 fnd_log.string(p_level, p_module, p_msg);
139 ELSE
140 -- 5221578 log messages in C_LOG_SIZE
141 WHILE (l_pos-1)*C_LOG_SIZE <= l_max LOOP
142 fnd_log.string(p_level, p_module, substr(p_msg, (l_pos-1)*C_LOG_SIZE+1, C_LOG_SIZE));
143 l_pos := l_pos+1;
144 END LOOP;
145 END IF;
146 END IF;
147
148 EXCEPTION
149 WHEN xla_exceptions_pkg.application_exception THEN
150 RAISE;
151 WHEN OTHERS THEN
152 xla_exceptions_pkg.raise_message
153 (p_location => 'xla_hist_ledger_upg_pkg.trace');
154 END trace;
155
156
157
158 PROCEDURE upg_main
159 (
160 p_errbuf OUT NOCOPY VARCHAR2
161 ,p_retcode OUT NOCOPY NUMBER
162 ,p_application_id IN NUMBER
163 ,p_primary_ledger_id IN NUMBER
164 ,p_sec_alc_ledger_id IN NUMBER
165 ,p_rep_ledger_type IN VARCHAR2
166 ,p_mode IN VARCHAR2
167 ,p_mode_check IN VARCHAR2
168 ,p_start_date IN DATE
169 ,p_conversion_option IN VARCHAR2
170 ,p_currency_conversion_type IN VARCHAR2
171 ,p_currency_conversion_date IN DATE
172 ,p_batch_size IN NUMBER
173 ,p_num_workers IN NUMBER
174 )
175 IS
176
177 l_log_module VARCHAR2(240);
178 l_mode_meaning VARCHAR2(20);
179
180 BEGIN
181
182
183 --EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
184 --p_rep_ledger_type and p_mode_check are concurrent program dependency parameters only
185
186 IF g_log_enabled THEN
187 l_log_module := C_DEFAULT_MODULE||'.upg_main';
188 END IF;
189
190
191 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
192 trace
193 (p_msg => 'UPG_MAIN procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
194 ,p_level => C_LEVEL_PROCEDURE
195 ,p_module => l_log_module);
196 END IF;
197 fnd_file.put_line(fnd_file.log,'UPG_MAIN procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
198
199
200 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
201 trace
202 (p_msg => 'p_application_id = ' || p_application_id
203 ,p_level => C_LEVEL_PROCEDURE
204 ,p_module => l_log_module);
205 trace
206 (p_msg => 'p_primary_ledger_id = ' || p_primary_ledger_id
207 ,p_level => C_LEVEL_PROCEDURE
208 ,p_module => l_log_module);
209 trace
210 (p_msg => 'p_sec_alc_ledger_id = ' || p_sec_alc_ledger_id
211 ,p_level => C_LEVEL_PROCEDURE
212 ,p_module => l_log_module);
213 trace
214 (p_msg => 'p_start_date = ' || to_char(p_start_date, 'DD-MON-RRRR')
215 ,p_level => C_LEVEL_PROCEDURE
216 ,p_module => l_log_module);
217 trace
218 (p_msg => 'p_conversion_option = ' || p_conversion_option
219 ,p_level => C_LEVEL_PROCEDURE
220 ,p_module => l_log_module);
221 trace
222 (p_msg => 'p_currency_conversion_type = ' || p_currency_conversion_type
223 ,p_level => C_LEVEL_PROCEDURE
224 ,p_module => l_log_module);
225 trace
226 (p_msg => 'p_currency_conversion_date = ' || to_char(p_currency_conversion_date, 'DD-MON-RRRR')
227 ,p_level => C_LEVEL_PROCEDURE
228 ,p_module => l_log_module);
229 trace
230 (p_msg => 'p_mode = ' || p_mode
231 ,p_level => C_LEVEL_PROCEDURE
232 ,p_module => l_log_module);
233 trace
234 (p_msg => 'p_batch_size = ' || p_batch_size
235 ,p_level => C_LEVEL_PROCEDURE
236 ,p_module => l_log_module);
237 trace
238 (p_msg => 'p_num_workers = ' || p_num_workers
239 ,p_level => C_LEVEL_PROCEDURE
240 ,p_module => l_log_module);
241
242 END IF;
243
244 g_application_id := p_application_id;
245 g_primary_ledger_id := p_primary_ledger_id;
246 g_secondary_alc_ledger_id := p_sec_alc_ledger_id;
247
248 --g_start_date := fnd_date.canonical_to_date(p_start_date);
249 --caused incorrect conversion when special value set used instead of FND_STARDARD_DATE
250 --g_start_date := to_date(p_start_date, 'DD-MON-RRRR');
251 g_start_date := p_start_date;
252
253 g_conversion_option := p_conversion_option;
254 g_currency_conversion_type := p_currency_conversion_type;
255
256 --g_currency_conversion_date := fnd_date.canonical_to_date(p_currency_conversion_date);
257 --caused incorrect conversion when special value set used instead of FND_STARDARD_DATE
258 --g_currency_conversion_date := to_date(g_currency_conversion_date, 'DD-MON-RRRR');
259 g_currency_conversion_date := p_currency_conversion_date;
260
261
262 g_validation_mode := p_mode;
263 g_batch_size := NVL(p_batch_size , 1000);
264 g_num_workers := NVL(p_num_workers, 1);
265
266 -- possible status during run: PHASE-RATE, PHASE-DATA-START, SUCCESS
267 -- possible committed status in xla_historic_control: no row, PHASE-DATA-START, SUCCESS
268 -- possible p_mode values: V (validation), F (final), R (recovery)
269
270 -- added this to show meaning in the logfile rather than the code.
271 SELECT meaning
272 INTO l_mode_meaning
273 FROM xla_lookups
274 WHERE lookup_type = 'XLA_HIST_UPG_MODE'
275 AND lookup_code = p_mode ;
276
277
278
279 IF p_mode = 'V' THEN --validation mode
280
281 -- no commit in this mode
282 -- all inserts (rates/historic control) are rolled back
283
284 retrieve_validate();
285 validate_final_mode();
286
287 INSERT INTO xla_historic_control(primary_ledger,
288 secondary_alc_ledger,
289 application_id,
290 upgrade_id,
291 relationship_id,
292 script_name,
293 start_date,
294 end_date,
295 batch_size,
296 num_workers,
297 status,
298 ledger_category_code,
299 last_update_date,
300 primary_currency_code,
301 sec_alc_currency_code,
302 sec_alc_mau,
303 sec_alc_precision,
304 conversion_option,
305 currency_conversion_type,
306 currency_conversion_date,
307 primary_slam,
308 primary_coa,
309 primary_slam_type,
310 primary_aad ,
311 primary_aad_owner ,
312 secondary_slam,
313 secondary_coa,
314 secondary_slam_type,
315 secondary_aad ,
316 secondary_aad_owner ,
317 mapping_relationship_id,
318 coa_mapping_name,
319 primary_cal_set_name,
320 primary_cal_per_type,
321 primary_sec_alc_set_name,
322 primary_sec_alc_per_type,
323 dynamic_inserts ,
324 parent_request_id )
325 VALUES (
326 g_primary_ledger_id,
327 g_secondary_alc_ledger_id,
328 g_application_id,
329 g_upgrade_id,
330 g_relationship_id,
331 g_script_name,
332 g_start_date,
333 g_sec_alc_end_date,
334 g_batch_size,
335 g_num_workers,
336 'PHASE-RATE', --NULL,
337 g_ledger_category_code,
338 SYSDATE,
339 g_primary_currency_code,
340 g_sec_alc_currency_code,
341 g_sec_alc_mau,
342 g_sec_alc_precision,
343 g_conversion_option,
344 g_currency_conversion_type,
345 g_currency_conversion_date,
346 g_primary_slam,
347 g_primary_coa,
348 g_primary_slam_type,
349 g_primary_aad ,
350 g_primary_aad_owner ,
351 g_secondary_slam,
352 g_secondary_coa,
353 g_secondary_slam_type,
354 g_secondary_aad ,
355 g_secondary_aad_owner ,
356 g_mapping_relationship_id,
357 g_coa_mapping_name,
358 g_primary_cal_set_name,
359 g_primary_cal_per_type,
360 g_primary_sec_alc_set_name,
361 g_primary_sec_alc_per_type,
362 g_dynamic_inserts ,
363 fnd_global.conc_request_id()
364 );
365
366 populate_rates();
367
368 ROLLBACK;
369
370
371 ELSIF p_mode = 'F' THEN -- final mode
372
373 retrieve_validate(); /* retrieve all information needed for run, except start_date, end_date
374 perform all validations, except gl_transfer since this needs end date
375 no commit! */
376
377 validate_final_mode();
378 /* Check if:
379 a) no failed run status exists for this ledger/applincation/relationship in history table,
380 meaning either first run or all SUCCESS rows only.
381 b) if first run, end date is min(accounting_date)-1 of secondary ledger or infinity if min is null
382 c) if second+ run, end date is min start date - 1 of all previously upgraded start dates
383 d) start should be less than end date retrieved
384 no commit! */
385
386 INSERT INTO xla_historic_control(primary_ledger,
387 secondary_alc_ledger,
388 application_id,
389 upgrade_id,
390 relationship_id,
391 script_name,
392 start_date,
393 end_date,
394 batch_size,
395 num_workers,
396 status,
397 ledger_category_code,
398 last_update_date,
399 primary_currency_code,
400 sec_alc_currency_code,
401 sec_alc_mau,
402 sec_alc_precision,
403 conversion_option,
404 currency_conversion_type,
405 currency_conversion_date,
406 primary_slam,
407 primary_coa,
408 primary_slam_type,
409 primary_aad ,
410 primary_aad_owner,
411 secondary_slam,
412 secondary_coa,
413 secondary_slam_type,
414 secondary_aad,
415 secondary_aad_owner ,
416 mapping_relationship_id,
417 coa_mapping_name,
418 primary_cal_set_name,
419 primary_cal_per_type,
420 primary_sec_alc_set_name,
421 primary_sec_alc_per_type,
422 dynamic_inserts,
423 sec_alc_min_acctng_batch_id ,
424 parent_request_id )
425 VALUES (
426 g_primary_ledger_id,
427 g_secondary_alc_ledger_id,
428 g_application_id,
429 g_upgrade_id,
430 g_relationship_id,
431 g_script_name,
432 g_start_date,
433 g_sec_alc_end_date,
434 g_batch_size,
435 g_num_workers,
436 'PHASE-RATE', --NULL,
437 g_ledger_category_code,
438 SYSDATE,
439 g_primary_currency_code,
440 g_sec_alc_currency_code,
441 g_sec_alc_mau,
442 g_sec_alc_precision,
443 g_conversion_option,
444 g_currency_conversion_type,
445 g_currency_conversion_date,
446 g_primary_slam,
447 g_primary_coa,
448 g_primary_slam_type,
449 g_primary_aad,
450 g_primary_aad_owner ,
451 g_secondary_slam,
452 g_secondary_coa,
453 g_secondary_slam_type,
454 g_secondary_aad ,
455 g_secondary_aad_owner ,
456 g_mapping_relationship_id,
457 g_coa_mapping_name,
458 g_primary_cal_set_name,
459 g_primary_cal_per_type,
460 g_primary_sec_alc_set_name,
461 g_primary_sec_alc_per_type,
462 g_dynamic_inserts,
463 g_sec_alc_min_acctng_batch_id ,
464 fnd_global.conc_request_id()
465 );
466
467
468 populate_rates(); /* for different date-range runs, rates will be re-inserted with same
469 relationship id same but different upgrade id */
470
471 UPDATE xla_historic_control
472 SET status = 'PHASE-DATA-START'
473 WHERE primary_ledger = g_primary_ledger_id
474 AND secondary_alc_ledger = g_secondary_alc_ledger_id
475 AND application_id = g_application_id
476 AND relationship_id = g_relationship_id
477 AND upgrade_id = g_upgrade_id
478 AND script_name = g_script_name
479 AND status = 'PHASE-RATE';
480
481 COMMIT;
482
483
484 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
485 trace
486 (p_msg => 'xla_historic_control updated to PHASE-DATA-START, commit executed'
487 ,p_level => C_LEVEL_PROCEDURE
488 ,p_module => l_log_module);
489 END IF;
490
491
492 insert_data(g_primary_ledger_id,
493 g_secondary_alc_ledger_id,
494 g_application_id,
495 g_relationship_id,
496 g_upgrade_id,
497 g_script_name,
498 g_batch_size,
499 g_num_workers); /*contains commit after FND submit, and after child workers completed successfully*/
500
501
502 ELSIF p_mode = 'R' THEN -- recovery mode
503
504
505 validate_recovery_mode(); /* if mode recovery, input parameters in SRS Submit only allow application/ledgers
506 do not retrieve_validate or populate_rates again, instead use information commited from last failed run
507 Check if:
508 a) failed run should exist for ledger/period/relationship/sec_alc_ledger/application in history table,
509 b) only one failed RUN should exist, if two, error out, if none, error out
510 c) reset all parameters to failed run from table */
511
512 insert_data(g_primary_ledger_id
513 ,g_secondary_alc_ledger_id
514 ,g_application_id
515 ,g_relationship_id
516 ,g_upgrade_id
517 ,g_script_name
518 ,g_batch_size
519 ,g_num_workers); -- call procedure with last run details
520
521 END IF;
522
523
524 p_retcode := 0;
525 p_errbuf := 'Upgrade Mode: ' || l_mode_meaning || ' completed successfully.'; -- changed from p_mode to l_mode_meaning
526
527
528 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
529 trace
530 (p_msg => 'UPG_MAIN procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
531 ,p_level => C_LEVEL_PROCEDURE
532 ,p_module => l_log_module);
533 END IF;
534 fnd_file.put_line(fnd_file.log,'UPG_MAIN procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
535
536
537
538
539 EXCEPTION WHEN g_validation_failed THEN
540
541 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
542 trace
543 (p_msg => 'BEGIN of Abnormal Termination EXCEPTION'
544 ,p_level => C_LEVEL_PROCEDURE
545 ,p_module => l_log_module);
546 END IF;
547
548 p_retcode := 2;
549 p_errbuf := 'Upgrade Failed (g_validation_failed)';
550
551 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
552 trace
553 (p_msg => 'END of Abnormal Termination EXCEPTION'
554 ,p_level => C_LEVEL_PROCEDURE
555 ,p_module => l_log_module);
556 END IF;
557
558
559 WHEN others THEN
560
561 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
562 trace
563 (p_msg => 'BEGIN of others EXCEPTION'
564 ,p_level => C_LEVEL_PROCEDURE
565 ,p_module => l_log_module);
566 END IF;
567
568 g_error_text := SQLCODE || ' ' || SQLERRM;
569
570
571 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
572 trace
573 (p_msg => 'SQL exception raised in upg_main is = ' || g_error_text
574 ,p_level => C_LEVEL_PROCEDURE
575 ,p_module => l_log_module);
576 END IF;
577 fnd_file.put_line(fnd_file.log, 'SQL exception raised in upg_main is = ' || g_error_text);
578
579
580 p_retcode := 2;
581 p_errbuf := 'Upgrade Failed (2)';
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 END;
591
592
593
594
595 PROCEDURE validate_recovery_mode
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||'.validate_recovery_mode';
604 END IF;
605
606 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
607 trace
608 (p_msg => 'validate_recovery_mode procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
609 ,p_level => C_LEVEL_PROCEDURE
610 ,p_module => l_log_module);
611 END IF;
612 fnd_file.put_line(fnd_file.log,'validate_recovery_mode procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
613
614
615 -- these sqls are called before current run row is inserted into xla_historic_control
616
617 SELECT COUNT(*)
618 INTO g_recovery_failed_runs
619 FROM xla_historic_control
620 WHERE primary_ledger = g_primary_ledger_id
621 AND secondary_alc_ledger = g_secondary_alc_ledger_id
622 AND application_id = g_application_id
623 AND status <> 'SUCCESS';
624
625
626 IF g_recovery_failed_runs = 0 THEN
627
628 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
629 trace
630 (p_msg => 'Recovery not needed, no failed run. Please re-submit in Final Mode'
631 ,p_level => C_LEVEL_PROCEDURE
632 ,p_module => l_log_module);
633 END IF;
634 fnd_file.put_line(fnd_file.log, 'validation failed: recovery not needed, no failed run exists. please re-submit in final mode' );
635
636 ROLLBACK;
637 RAISE g_validation_failed;
638
639 END IF;
640
641
642 -- below condition should not be possible in standard code flow without user manipulation
643
644 IF g_recovery_failed_runs > 1 THEN
645
646 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
647 trace
648 (p_msg => 'internal error: multiple failed runs, recovery not feasible'
649 ,p_level => C_LEVEL_PROCEDURE
650 ,p_module => l_log_module);
651 END IF;
652 fnd_file.put_line(fnd_file.log, 'validation failed: multiple failed runs, recovery not feasible' );
653
654 ROLLBACK;
655 RAISE g_validation_failed;
656
657 END IF;
658
659
660 -- below validations assume there is only one failed row existing for the ledger/application
661
662
663 SELECT primary_ledger
664 ,secondary_alc_ledger
665 ,application_id
666 ,relationship_id
667 ,upgrade_id
668 ,script_name
669 ,batch_size
670 ,num_workers
671 INTO g_primary_ledger_id
672 ,g_secondary_alc_ledger_id
673 ,g_application_id
674 ,g_relationship_id
675 ,g_upgrade_id
676 ,g_script_name
677 ,g_batch_size
678 ,g_num_workers
679 FROM xla_historic_control
680 WHERE primary_ledger = g_primary_ledger_id
681 AND secondary_alc_ledger = g_secondary_alc_ledger_id
682 AND application_id = g_application_id
683 AND status = 'PHASE-DATA-START';
684
685
686 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
687 trace
688 (p_msg => 'validate_recovery_mode procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
689 ,p_level => C_LEVEL_PROCEDURE
690 ,p_module => l_log_module);
691 END IF;
692 fnd_file.put_line(fnd_file.log,'validate_recovery_mode procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
693
694
695 END;
696
697
698
699
700 PROCEDURE validate_final_mode
701 IS
702
703 l_log_module VARCHAR2(240);
704
705 BEGIN
706
707 IF g_log_enabled THEN
708 l_log_module := C_DEFAULT_MODULE||'.validate_final_mode';
709 END IF;
710
711
712 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
713 trace
714 (p_msg => 'validate_final_mode procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
715 ,p_level => C_LEVEL_PROCEDURE
716 ,p_module => l_log_module);
717 END IF;
718 fnd_file.put_line(fnd_file.log,'validate_final_mode procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
719
720
721 -- these sqls are called before current run row is inserted into xla_historic_control
722
723 SELECT COUNT(*)
724 INTO g_failed_runs
725 FROM xla_historic_control
726 WHERE primary_ledger = g_primary_ledger_id
727 AND secondary_alc_ledger = g_secondary_alc_ledger_id
728 AND application_id = g_application_id
729 AND relationship_id = g_relationship_id
730 AND status <> 'SUCCESS';
731
732
733 SELECT COUNT(*)
734 INTO g_success_runs
735 FROM xla_historic_control
736 WHERE primary_ledger = g_primary_ledger_id
737 AND secondary_alc_ledger = g_secondary_alc_ledger_id
738 AND application_id = g_application_id
739 AND relationship_id = g_relationship_id
740 AND status = 'SUCCESS';
741
742
743
744 IF g_failed_runs > 0 THEN
745
746 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
747 trace
748 (p_msg => 'Failed run exists for specified input criteria, please run in Recovery Mode'
749 ,p_level => C_LEVEL_PROCEDURE
750 ,p_module => l_log_module);
751 END IF;
752 fnd_file.put_line(fnd_file.log, 'validation failed: failed run exists for specified input criteria, please run in recovery mode' );
753
754 ROLLBACK;
755 RAISE g_validation_failed;
756
757 END IF;
758
759
760 -- below validations assume this is either a first time run, or x+1 run with all x runs having status SUCCESS
761
762
763
764 IF g_success_runs = 0 THEN
765
766 -- if accounted data already exists for secondary ledger, take min accounting date less one, else infinity
767 /*
768 SELECT nvl(min(accounting_date-1), to_date('31/12/9999', 'DD/MM/YYYY'))
769 INTO g_sec_alc_end_date
770 FROM xla_ae_headers
771 WHERE application_id = g_application_id
772 AND ledger_id = g_secondary_alc_ledger_id
773 AND accounting_entry_status_code = 'F';
774 */
775
776 SELECT min(accounting_batch_id)-1
777 INTO g_sec_alc_min_acctng_batch_id
778 FROM xla_ae_headers
779 WHERE application_id = g_application_id
780 AND ledger_id = g_secondary_alc_ledger_id
781 AND accounting_entry_status_code = 'F';
782
783 g_sec_alc_end_date := to_date('31/12/9999', 'DD/MM/YYYY');
784
785
786 -- added by vgopiset
787 IF g_sec_alc_min_acctng_batch_id IS NULL THEN
788 SELECT xla_accounting_batches_s.NEXTVAL INTO g_sec_alc_min_acctng_batch_id FROM DUAL;
789 END IF;
790
791
792 ELSIF g_success_runs > 0 THEN
793
794 --current run is the 2nd or higher run, set end date to start date less one of last run
795
796 SELECT min(start_date-1) , min(sec_alc_min_acctng_batch_id)
797 INTO g_sec_alc_end_date , g_sec_alc_min_acctng_batch_id
798 FROM xla_historic_control
799 WHERE primary_ledger = g_primary_ledger_id
800 AND secondary_alc_ledger = g_secondary_alc_ledger_id
801 AND application_id = g_application_id
802 AND relationship_id = g_relationship_id
803 AND status = 'SUCCESS';
804
805 END IF;
806
807
808 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
809 trace
810 (p_msg => 'g_start_date = ' || to_char(g_start_date, 'DD-MON-RRRR')
811 ,p_level => C_LEVEL_PROCEDURE
812 ,p_module => l_log_module);
813 trace
814 (p_msg => 'g_sec_alc_end_date calculated = ' || to_char(g_sec_alc_end_date, 'DD-MON-RRRR')
815 ,p_level => C_LEVEL_PROCEDURE
816 ,p_module => l_log_module);
817 END IF;
818
819
820
821 IF g_start_date > g_sec_alc_end_date THEN
822
823 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
824 trace
825 (p_msg => 'start date specified should be equal to or less than the retrieved end date of ' || to_char(g_sec_alc_end_date, 'DD-MON-RRRR')
826 ,p_level => C_LEVEL_PROCEDURE
827 ,p_module => l_log_module);
828 END IF;
829 fnd_file.put_line(fnd_file.log, 'validation failed: start date specified should be equal to or less than the retrieved end date of ' || to_char(g_sec_alc_end_date,'DD-MON-RRRR'));
830
831 ROLLBACK;
832 RAISE g_validation_failed;
833
834
835 END IF;
836
837
838
839 BEGIN -- Check if all fully accounted data has also been transferred to GL
840
841 SELECT 1
842 INTO g_untransferred_headers
843 FROM DUAL
844 WHERE EXISTS (SELECT /*+ parallel (xla_ae_headers) */ 1
845 FROM xla_ae_headers
846 WHERE ledger_id = g_primary_ledger_id
847 AND accounting_entry_status_code = 'F'
848 AND application_id = g_application_id
849 AND balance_type_code in ('A', 'E')
850 AND event_type_code <> 'MANUAL'
851 AND gl_transfer_status_code = 'N' -- bug9278306
852 AND accounting_date >= g_start_date
853 AND accounting_date <= g_sec_alc_end_date);
854
855 EXCEPTION WHEN others THEN
856
857 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
858 trace
859 (p_msg => 'All valid entries for primary ledger have been transferred to GL'
860 ,p_level => C_LEVEL_PROCEDURE
861 ,p_module => l_log_module);
862 END IF;
863
864 END;
865
866 IF g_untransferred_headers = 1 THEN
867 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
868 trace
869 (p_msg => 'Error: There are valid accounting entries that have not been transferred to General Ledger'
870 ,p_level => C_LEVEL_PROCEDURE
871 ,p_module => l_log_module);
872 END IF;
873
874 --fnd_message.set_name('XLA','XLA_UPG_UNTRANS_ENTRIES');
875 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
876 fnd_file.put_line(fnd_file.log, 'validation failed: there are valid accounting entries that have not been transferred to GL');
877
878 ROLLBACK;
879 RAISE g_validation_failed;
880
881 END IF;
882
883 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
884 trace
885 (p_msg => 'validate_final_mode procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
886 ,p_level => C_LEVEL_PROCEDURE
887 ,p_module => l_log_module);
888 END IF;
889 fnd_file.put_line(fnd_file.log,'validate_final_mode procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
890
891 END;
892
893
894
895 PROCEDURE retrieve_validate
896 IS
897
898 l_log_module VARCHAR2(240);
899
900 BEGIN
901
902 IF g_log_enabled THEN
903 l_log_module := C_DEFAULT_MODULE||'.retrieve_validate';
904 END IF;
905
906
907 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
908 trace
909 (p_msg => 'retrieve_validate procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
910 ,p_level => C_LEVEL_PROCEDURE
911 ,p_module => l_log_module);
912 END IF;
913 fnd_file.put_line(fnd_file.log,'retrieve_validate procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
914
915
916 -- g_upgrade_id := fnd_global.conc_request_id(); commented out
917
918 SELECT xla_upg_batches_s.NEXTVAL INTO g_upgrade_id FROM DUAL;
919 g_script_name := 'xlahupg_' || g_upgrade_id;
920
921 --g_sec_alc_end_date: set in validate_final_mode
922
923 SELECT min(relationship_id)
924 INTO g_relationship_id
925 FROM gl_ledger_relationships
926 WHERE primary_ledger_id = g_primary_ledger_id
927 AND target_ledger_id = g_secondary_alc_ledger_id
928 AND application_id = 101
929 AND relationship_enabled_flag = 'Y';
930
931 SELECT currency_code
932 INTO g_primary_currency_code
933 FROM gl_ledgers
934 WHERE ledger_id = g_primary_ledger_id;
935
936 SELECT ledger_category_code, currency_code
937 INTO g_ledger_category_code, g_sec_alc_currency_code -- ledger category will store 'SECONDARY' or 'ALC' or 'NONE'
938 FROM gl_ledgers
939 WHERE ledger_id = g_secondary_alc_ledger_id;
940
941 SELECT minimum_accountable_unit, precision
942 INTO g_sec_alc_mau, g_sec_alc_precision
943 FROM fnd_currencies
944 WHERE currency_code = g_sec_alc_currency_code;
945
946
947 /*
948 IF g_ledger_category_code = 'ALC' THEN
949
950 SELECT alc_init_conv_option_code, alc_initializing_rate_type, alc_initializing_rate_date
951 INTO g_conversion_option, g_currency_conversion_type, g_currency_conversion_date
952 FROM gl_ledger_relationships
953 WHERE relationship_id = g_relationship_id;
954
955 IF ( (g_conversion_option IS NULL) OR (g_currency_conversion_type IS NULL) OR (g_currency_conversion_date IS NULL) ) THEN
956 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
957 trace
958 (p_msg => 'Error: g_conversion_option, g_currency_conversion_type, or g_currency_conversion_date is NULL for ALC ledger'
959 ,p_level => C_LEVEL_PROCEDURE
960 ,p_module => l_log_module);
961 END IF;
962 --fnd_message.set_name('XLA','XLA_UPG_GL_LEDG_NULL');
963 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
964 fnd_file.put_line(fnd_file.log, 'validation failed: g_conversion_option, g_currency_conversion_type, or g_currency_conversion_date is NULL for ALC ledger');
965
966 ROLLBACK;
967 RAISE g_validation_failed;
968
969 ELSE
970 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
971 trace
972 (p_msg => 'ALC conversion details retrieved'
973 ,p_level => C_LEVEL_PROCEDURE
974 ,p_module => l_log_module);
975 END IF;
976 END IF;
977 END IF;
978 */
979
980
981 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
982 trace
983 (p_msg => 'g_upgrade_id = ' || g_upgrade_id
984 ,p_level => C_LEVEL_PROCEDURE
985 ,p_module => l_log_module);
986 trace
987 (p_msg => 'g_script_name = ' || g_script_name
988 ,p_level => C_LEVEL_PROCEDURE
989 ,p_module => l_log_module);
990 trace
991 (p_msg => 'g_relationship_id = ' || g_relationship_id
992 ,p_level => C_LEVEL_PROCEDURE
993 ,p_module => l_log_module);
994 trace
995 (p_msg => 'g_primary_currency_code = ' || g_primary_currency_code
996 ,p_level => C_LEVEL_PROCEDURE
997 ,p_module => l_log_module);
998 trace
999 (p_msg => 'g_ledger_category_code = ' || g_ledger_category_code
1000 ,p_level => C_LEVEL_PROCEDURE
1001 ,p_module => l_log_module);
1002 trace
1003 (p_msg => 'g_sec_alc_currency_code = ' || g_sec_alc_currency_code
1004 ,p_level => C_LEVEL_PROCEDURE
1005 ,p_module => l_log_module);
1006 trace
1007 (p_msg => 'g_sec_alc_mau = ' || g_sec_alc_mau
1008 ,p_level => C_LEVEL_PROCEDURE
1009 ,p_module => l_log_module);
1010 trace
1011 (p_msg => 'g_sec_alc_precision = ' || g_sec_alc_precision
1012 ,p_level => C_LEVEL_PROCEDURE
1013 ,p_module => l_log_module);
1014 trace
1015 (p_msg => 'g_conversion_option = ' || g_conversion_option
1016 ,p_level => C_LEVEL_PROCEDURE
1017 ,p_module => l_log_module);
1018 trace
1019 (p_msg => 'g_currency_conversion_type = ' || g_currency_conversion_type
1020 ,p_level => C_LEVEL_PROCEDURE
1021 ,p_module => l_log_module);
1022 trace
1023 (p_msg => 'g_currency_conversion_date = ' || to_char(g_currency_conversion_date, 'DD-MON-RRRR')
1024 ,p_level => C_LEVEL_PROCEDURE
1025 ,p_module => l_log_module);
1026 END IF;
1027
1028
1029
1030 SELECT period_set_name, accounted_period_type
1031 INTO g_primary_cal_set_name, g_primary_cal_per_type
1032 FROM gl_ledgers
1033 WHERE ledger_id = g_primary_ledger_id;
1034
1035
1036 SELECT period_set_name, accounted_period_type
1037 INTO g_primary_sec_alc_set_name, g_primary_sec_alc_per_type
1038 FROM gl_ledgers
1039 WHERE ledger_id = g_secondary_alc_ledger_id;
1040
1041
1042 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1043 trace
1044 (p_msg => 'g_primary_cal_set_name = ' || g_primary_cal_set_name
1045 ,p_level => C_LEVEL_PROCEDURE
1046 ,p_module => l_log_module);
1047 trace
1048 (p_msg => 'g_primary_cal_per_type = ' || g_primary_cal_per_type
1049 ,p_level => C_LEVEL_PROCEDURE
1050 ,p_module => l_log_module);
1051 trace
1052 (p_msg => 'g_primary_sec_alc_set_name = ' || g_primary_sec_alc_set_name
1053 ,p_level => C_LEVEL_PROCEDURE
1054 ,p_module => l_log_module);
1055 trace
1056 (p_msg => 'g_primary_sec_alc_per_type = ' || g_primary_sec_alc_per_type
1057 ,p_level => C_LEVEL_PROCEDURE
1058 ,p_module => l_log_module);
1059 END IF;
1060
1061
1062 IF g_ledger_category_code = 'ALC' THEN
1063
1064 BEGIN
1065 SELECT 1
1066 INTO g_alc_exists
1067 FROM DUAL
1068 WHERE EXISTS (SELECT 1
1069 FROM xla_subledgers
1070 WHERE alc_enabled_flag = 'Y'
1071 AND application_id = g_application_id);
1072
1073
1074 IF g_alc_exists = 1 THEN
1075 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1076 trace
1077 (p_msg => 'Application ' || g_application_id || ' is ALC enabled'
1078 ,p_level => C_LEVEL_PROCEDURE
1079 ,p_module => l_log_module);
1080 END IF;
1081 END IF;
1082
1083
1084 EXCEPTION WHEN others THEN
1085
1086 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1087 trace
1088 (p_msg => 'Error: Application ' || g_application_id || ' is not ALC-enabled in SLA'
1089 ,p_level => C_LEVEL_PROCEDURE
1090 ,p_module => l_log_module);
1091 END IF;
1092
1093 --fnd_message.set_name('XLA','XLA_UPG_ALC_DISABLED');
1094 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1095 fnd_file.put_line(fnd_file.log, 'validation failed: application is not ALC-enabled in SLA');
1096
1097 ROLLBACK;
1098 RAISE g_validation_failed;
1099
1100 END;
1101 END IF; -- ledger category code ALC
1102
1103
1104
1105
1106 IF g_ledger_category_code = 'SECONDARY' THEN
1107
1108 BEGIN
1109 SELECT xlr.sla_accounting_method_code
1110 ,xlr.chart_of_accounts_id
1111 ,xlr.sla_accounting_method_type
1112 ,xlr.enable_budgetary_control_flag
1113 ,xamr.product_rule_code
1114 ,xamr.PRODUCT_RULE_TYPE_CODE
1115 INTO g_primary_slam
1116 ,g_primary_coa
1117 ,g_primary_slam_type
1118 ,g_primary_budget
1119 ,g_primary_aad
1120 ,g_primary_aad_owner
1121 FROM xla_ledger_relationships_v xlr ,
1122 XLA_ACCTG_METHOD_RULES xamr
1123 WHERE xlr.ledger_id = g_primary_ledger_id
1124 AND xamr.amb_context_code = 'DEFAULT'
1125 AND xlr.sla_ACCOUNTING_METHOD_CODE = xamr.ACCOUNTING_METHOD_CODE
1126 AND xlr.sla_ACCOUNTING_METHOD_TYPE = xamr.ACCOUNTING_METHOD_TYPE_CODE
1127 AND xamr.application_id = g_application_id
1128 AND TRUNC(SYSDATE) BETWEEN TRUNC(xamr.start_date_active) AND TRUNC(NVL(xamr.END_DATE_ACTIVE , SYSDATE));
1129
1130 SELECT xlr.sla_accounting_method_code
1131 ,xlr.chart_of_accounts_id
1132 ,xlr.sla_accounting_method_type
1133 ,xlr.enable_budgetary_control_flag
1134 ,xamr.product_rule_code
1135 ,xamr.PRODUCT_RULE_TYPE_CODE
1136 INTO g_secondary_slam
1137 ,g_secondary_coa
1138 ,g_secondary_slam_type
1139 ,g_secondary_budget
1140 ,g_secondary_aad
1141 ,g_secondary_aad_owner
1142 FROM xla_ledger_relationships_v xlr ,
1143 XLA_ACCTG_METHOD_RULES xamr
1144 WHERE xlr.ledger_id = g_secondary_alc_ledger_id
1145 AND xamr.amb_context_code = 'DEFAULT'
1146 AND xlr.sla_ACCOUNTING_METHOD_CODE = xamr.ACCOUNTING_METHOD_CODE
1147 AND xlr.sla_ACCOUNTING_METHOD_TYPE = xamr.ACCOUNTING_METHOD_TYPE_CODE
1148 AND xamr.application_id = g_application_id
1149 AND TRUNC(SYSDATE) BETWEEN TRUNC(xamr.start_date_active) AND TRUNC(NVL(xamr.END_DATE_ACTIVE , SYSDATE));
1150
1151 IF (g_secondary_aad <> g_primary_aad) OR (g_secondary_aad_owner <> g_primary_aad_owner) OR
1152 (g_primary_budget <> g_secondary_budget) THEN
1153
1154 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1155 trace
1156 (p_msg => 'Error: Primary and Secondary Ledger have different AADs or budegtary control '
1157 ,p_level => C_LEVEL_PROCEDURE
1158 ,p_module => l_log_module);
1159 END IF;
1160 ROLLBACK;
1161 --fnd_message.set_name('XLA','XLA_UPG_DIFF_SLAM');
1162 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1163 fnd_file.put_line(fnd_file.log, 'validation failed: primary and secondary ledger have different AAD or budegtary control');
1164
1165 RAISE g_validation_failed;
1166
1167 ELSE
1168 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1169 trace
1170 (p_msg => 'Primary and Secondary Ledger AAD are the same'
1171 ,p_level => C_LEVEL_PROCEDURE
1172 ,p_module => l_log_module);
1173 END IF;
1174 END IF;
1175
1176 EXCEPTION WHEN others THEN
1177
1178 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1179 trace
1180 (p_msg => 'Error: AADs or COAs not retrieved'
1181 ,p_level => C_LEVEL_PROCEDURE
1182 ,p_module => l_log_module);
1183 END IF;
1184
1185 --fnd_message.set_name('XLA','XLA_UPG_SLAM_COA_FAIL');
1186 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1187 fnd_file.put_line(fnd_file.log, 'validation failed: AADs or COAs not retrieved');
1188
1189 ROLLBACK;
1190 RAISE g_validation_failed;
1191
1192 END;
1193
1194 IF g_primary_coa <> g_secondary_coa THEN
1195
1196 BEGIN
1197
1198 SELECT sl_coa_mapping_id
1199 INTO g_mapping_relationship_id
1200 FROM xla_ledger_relationships_v
1201 WHERE ledger_id = g_secondary_alc_ledger_id
1202 AND primary_ledger_id = g_primary_ledger_id;
1203
1204 SELECT name
1205 INTO g_coa_mapping_name
1206 FROM gl_coa_mappings
1207 WHERE coa_mapping_id = g_mapping_relationship_id;
1208
1209
1210 SELECT dynamic_inserts_allowed_flag
1211 INTO g_dynamic_inserts
1212 FROM fnd_id_flex_structures_vl
1213 WHERE application_id = 101
1214 AND id_flex_code = 'GL#'
1215 AND id_flex_num = g_primary_coa; -- why is this flag for primary???
1216
1217
1218 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1219 trace
1220 (p_msg => 'Chart of accounts mapping exists = ' || g_mapping_relationship_id
1221 ,p_level => C_LEVEL_PROCEDURE
1222 ,p_module => l_log_module);
1223 trace
1224 (p_msg => 'Chart of accounts mapping name = ' || g_coa_mapping_name
1225 ,p_level => C_LEVEL_PROCEDURE
1226 ,p_module => l_log_module);
1227 trace
1228 (p_msg => 'g_dynamic_inserts = ' || g_dynamic_inserts
1229 ,p_level => C_LEVEL_PROCEDURE
1230 ,p_module => l_log_module);
1231 END IF;
1232
1233
1234 EXCEPTION WHEN others THEN
1235
1236 IF g_mapping_relationship_id IS NULL THEN
1237 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1238 trace
1239 (p_msg => 'Error: No chart of accounts mapping defined'
1240 ,p_level => C_LEVEL_PROCEDURE
1241 ,p_module => l_log_module);
1242 END IF;
1243 --fnd_message.set_name('XLA','XLA_UPG_NO_COA');
1244 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1245 fnd_file.put_line(fnd_file.log, 'validation failed: no chart of accounts mapping defined');
1246
1247 ROLLBACK;
1248 RAISE g_validation_failed;
1249 END IF;
1250
1251 IF g_coa_mapping_name IS NULL THEN
1252 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1253 trace
1254 (p_msg => 'Error: No name defined for the chart of accounts mapping'
1255 ,p_level => C_LEVEL_PROCEDURE
1256 ,p_module => l_log_module);
1257 END IF;
1258 --fnd_message.set_name('XLA','XLA_UPG_COA_INV_NAME');
1259 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1260 fnd_file.put_line(fnd_file.log, 'validation failed: no name defined for chart of accounts mapping');
1261
1262 ROLLBACK;
1263 RAISE g_validation_failed;
1264 END IF;
1265
1266 END;
1267
1268
1269 END IF; -- primary secondary coa
1270
1271 END IF; --ledger category code secondary
1272
1273
1274
1275 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1276 trace
1277 (p_msg => 'retrieve_validate procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1278 ,p_level => C_LEVEL_PROCEDURE
1279 ,p_module => l_log_module);
1280 END IF;
1281 fnd_file.put_line(fnd_file.log,'retrieve_validate procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1282
1283
1284 END;
1285
1286
1287 PROCEDURE populate_rates IS
1288
1289 l_log_module VARCHAR2(240);
1290 l_error_exists BOOLEAN := FALSE ;
1291
1292 CURSOR c_entered_currencies
1293 IS
1294 SELECT /*+ parallel(xah) parallel(xal) leading(xah) */ distinct(XAL.currency_code)
1295 FROM xla_ae_headers XAH,
1296 xla_historic_control XHC,
1297 xla_ae_lines XAL
1298 WHERE XHC.primary_ledger = g_primary_ledger_id
1299 AND XHC.secondary_alc_ledger = g_secondary_alc_ledger_id
1300 AND XHC.upgrade_id = g_upgrade_id
1301 AND XHC.application_id = g_application_id
1302 AND XHC.script_name = g_script_name
1303 AND XHC.relationship_id = g_relationship_id
1304 AND XHC.status = 'PHASE-RATE'
1305 AND XAH.ledger_id = XHC.primary_ledger
1306 AND XAH.application_id = XHC.application_id
1307 AND XAH.accounting_entry_status_code = 'F'
1308 AND XAH.gl_transfer_status_code IN ('Y', 'NT') -- bug9278306
1309 AND XAH.balance_type_code in ('A', 'E')
1310 AND XAH.accounting_date >= XHC.start_date
1311 AND XAH.accounting_date <= XHC.end_date
1312 AND XAL.application_id = XAH.application_id
1313 AND XAL.ae_header_id = XAH.ae_header_id
1314 AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0));
1315
1316
1317 BEGIN
1318
1319 IF g_log_enabled THEN
1320 l_log_module := C_DEFAULT_MODULE||'.populate_rates';
1321 END IF;
1322
1323 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1324 trace
1325 (p_msg => 'populate_rates procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1326 ,p_level => C_LEVEL_PROCEDURE
1327 ,p_module => l_log_module);
1328 END IF;
1329 fnd_file.put_line(fnd_file.log,'populate_rates procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1330
1331 -- Check if somehow exchange rates already exists for this relationship id/upgrade id
1332
1333
1334 BEGIN
1335
1336 SELECT 1
1337 INTO g_rate_exists
1338 FROM DUAL
1339 WHERE EXISTS (SELECT 1
1340 FROM xla_rc_upgrade_rates
1341 WHERE relationship_id = g_relationship_id
1342 AND upgrade_run_id = g_upgrade_id);
1343
1344
1345 IF g_rate_exists = 1 THEN
1346
1347 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1348 trace
1349 (p_msg => 'Error: Currency rates already defined for this upgrade_id and relationship_id'
1350 ,p_level => C_LEVEL_PROCEDURE
1351 ,p_module => l_log_module);
1352 END IF;
1353
1354
1355 --fnd_message.set_name('XLA','XLA_UPG_RATES_EXIST');
1356 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get);
1357 fnd_file.put_line(fnd_file.log, 'validation failed: currency rates already defined for this upgrade_id and relationship_id');
1358
1359 ROLLBACK;
1360 RAISE g_validation_failed;
1361 END IF;
1362
1363
1364 EXCEPTION WHEN others THEN
1365
1366 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1367 trace
1368 (p_msg => 'Currency rates not already defined for this currency, starting rate population'
1369 ,p_level => C_LEVEL_PROCEDURE
1370 ,p_module => l_log_module);
1371 END IF;
1372
1373 END;
1374
1375
1376
1377
1378 IF g_conversion_option = 'I' THEN
1379
1380 FOR c_entered_currencies_rec IN c_entered_currencies
1381 LOOP
1382
1383 BEGIN
1384 -- glustcrs.pls
1385 gl_currency_api.get_triangulation_rate(c_entered_currencies_rec.currency_code,
1386 g_sec_alc_currency_code,
1387 g_currency_conversion_date,
1388 g_currency_conversion_type,
1389 g_denominator, -- output from API
1390 g_numerator, -- output from API
1391 g_rate); -- output from API
1392 EXCEPTION WHEN others THEN
1393 IF ( (g_denominator IS NULL) OR (g_numerator IS NULL) OR (g_rate IS NULL) OR
1394 (g_denominator <= 0) OR (g_numerator <= 0) OR (g_rate <= 0)) THEN
1395 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1396 trace
1397 (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
1398 ,p_level => C_LEVEL_PROCEDURE
1399 ,p_module => l_log_module);
1400 END IF;
1401
1402 --fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1403 --fnd_file.put_line(fnd_file.log, fnd_message.get);
1404 fnd_file.put_line(fnd_file.log, 'validation failed: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || c_entered_currencies_rec.currency_code);
1405
1406 /* ROLLBACK;
1407 RAISE g_validation_failed; */ -- commented by vgopiset
1408
1409 l_error_exists := TRUE ;
1410
1411 ELSE
1412 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1413 trace
1414 (p_msg => 'undefined error in populate_rates '
1415 ,p_level => C_LEVEL_PROCEDURE
1416 ,p_module => l_log_module);
1417 END IF;
1418
1419 fnd_file.put_line(fnd_file.log, 'validation failed: undefined error in populate rates');
1420
1421 ROLLBACK;
1422 RAISE g_validation_failed;
1423
1424
1425 END IF;
1426 END;
1427
1428
1429
1430
1431 IF ( (g_denominator IS NULL) OR (g_numerator IS NULL) OR (g_rate IS NULL) OR
1432 (g_denominator <= 0) OR (g_numerator <= 0) OR (g_rate <= 0)) THEN
1433
1434 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1435 trace
1436 (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
1437 ,p_level => C_LEVEL_PROCEDURE
1438 ,p_module => l_log_module);
1439 END IF;
1440
1441 --fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1442 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get || ' ' || c_entered_currencies_rec.currency_code);
1443 fnd_file.put_line(fnd_file.log, 'validation failed: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || c_entered_currencies_rec.currency_code);
1444
1445 /* ROLLBACK;
1446 RAISE g_validation_failed;
1447 */ -- commented by vgopiset
1448 l_error_exists := TRUE ;
1449 ELSE
1450
1451 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1452 trace
1453 (p_msg => 'Validation successful: currency API ' || c_entered_currencies_rec.currency_code
1454 ,p_level => C_LEVEL_PROCEDURE
1455 ,p_module => l_log_module);
1456 END IF;
1457 END IF;
1458
1459 INSERT INTO xla_rc_upgrade_rates
1460 (relationship_id
1461 ,upgrade_run_id
1462 ,from_currency
1463 ,to_currency
1464 ,denominator_rate
1465 ,numerator_rate
1466 ,conversion_rate
1467 ,precision
1468 ,minimum_accountable_unit
1469 ,creation_date
1470 ,created_by
1471 ,last_update_date
1472 ,last_updated_by
1473 ,last_update_login)
1474 VALUES(g_relationship_id
1475 ,g_upgrade_id
1476 ,c_entered_currencies_rec.currency_code
1477 ,g_sec_alc_currency_code
1478 ,g_denominator
1479 ,g_numerator
1480 ,g_rate
1481 ,null
1482 ,null
1483 ,SYSDATE
1484 ,fnd_global.user_id
1485 ,SYSDATE
1486 ,fnd_global.user_id
1487 ,fnd_global.login_id);
1488
1489
1490 END LOOP; --c_entered_currencies_rec
1491
1492
1493 IF l_error_exists THEN
1494 ROLLBACK;
1495 RAISE g_validation_failed;
1496 END IF;
1497
1498
1499
1500 ELSIF g_conversion_option = 'D' THEN
1501
1502
1503 BEGIN
1504 gl_currency_api.get_triangulation_rate(g_primary_currency_code,
1505 g_sec_alc_currency_code,
1506 g_currency_conversion_date,
1507 g_currency_conversion_type,
1508 g_denominator, -- output from API
1509 g_numerator, -- output from API
1510 g_rate); -- output from API
1511
1512 EXCEPTION WHEN others THEN
1513 IF ( (g_denominator IS NULL) OR (g_numerator IS NULL) OR (g_rate IS NULL)
1514 OR (g_denominator <= 0) OR (g_numerator <= 0) OR (g_rate <= 0) ) THEN
1515 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1516 trace
1517 (p_msg => 'Error: GL API returned a null value for currency or threw back an EXCEPTION for currency ' || g_primary_currency_code
1518 ,p_level => C_LEVEL_PROCEDURE
1519 ,p_module => l_log_module);
1520 END IF;
1521
1522 fnd_file.put_line(fnd_file.log, 'validation failed: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || g_primary_currency_code);
1523
1524
1525 ROLLBACK;
1526 RAISE g_validation_failed;
1527 END IF;
1528 END;
1529
1530
1531
1532
1533
1534 IF ( (g_denominator IS NULL) OR (g_numerator IS NULL) OR (g_rate IS NULL)
1535 OR (g_denominator <= 0) OR (g_numerator <= 0) OR (g_rate <= 0) ) THEN
1536
1537 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1538 trace
1539 (p_msg => 'Error: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || g_primary_currency_code
1540 ,p_level => C_LEVEL_PROCEDURE
1541 ,p_module => l_log_module);
1542 END IF;
1543
1544 --fnd_message.set_name('XLA','XLA_UPG_CURRENCY_API');
1545 --fnd_file.put_line(fnd_file.log, 'Validation failed: ' || fnd_message.get || ' ' || g_primary_currency_code);
1546 fnd_file.put_line(fnd_file.log, 'validation failed: GL API returned a null value for the currency or threw back an EXCEPTION for currency = ' || g_primary_currency_code);
1547
1548 ROLLBACK;
1549 RAISE g_validation_failed;
1550
1551 ELSE
1552 --fnd_file.put_line(fnd_file.log, 'Validation successful: currency API ' || g_primary_currency_code);
1553 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1554 trace
1555 (p_msg => 'Validation successful: currency API ' || g_primary_currency_code
1556 ,p_level => C_LEVEL_PROCEDURE
1557 ,p_module => l_log_module);
1558 END IF;
1559
1560
1561 END IF;
1562
1563
1564 INSERT INTO xla_rc_upgrade_rates
1565 (relationship_id
1566 ,upgrade_run_id
1567 ,from_currency
1568 ,to_currency
1569 ,denominator_rate
1570 ,numerator_rate
1571 ,conversion_rate
1572 ,precision
1573 ,minimum_accountable_unit
1574 ,creation_date
1575 ,created_by
1576 ,last_update_date
1577 ,last_updated_by
1578 ,last_update_login)
1579 VALUES(g_relationship_id
1580 ,g_upgrade_id
1581 ,g_primary_currency_code
1582 ,g_sec_alc_currency_code
1583 ,g_denominator
1584 ,g_numerator
1585 ,g_rate
1586 ,null
1587 ,null
1588 ,SYSDATE
1589 ,fnd_global.user_id
1590 ,SYSDATE
1591 ,fnd_global.user_id
1592 ,fnd_global.login_id);
1593
1594
1595
1596 END IF; -- g_conversion_option end if
1597
1598
1599 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1600
1601 SELECT count(*)
1602 INTO g_currency_count
1603 FROM xla_rc_upgrade_rates
1604 WHERE relationship_id = g_relationship_id
1605 AND upgrade_run_id = g_upgrade_id;
1606
1607 trace
1608 (p_msg => 'Rows inserted into xla_rc_upgrade_rates after calling GL API = ' || g_currency_count
1609 ,p_level => C_LEVEL_PROCEDURE
1610 ,p_module => l_log_module);
1611 END IF;
1612
1613 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1614 trace
1615 (p_msg => 'populate_rates procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1616 ,p_level => C_LEVEL_PROCEDURE
1617 ,p_module => l_log_module);
1618 END IF;
1619 fnd_file.put_line(fnd_file.log,'populate_rates procedure end time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1620
1621
1622 END;
1623
1624
1625
1626
1627
1628
1629
1630
1631 PROCEDURE insert_data(p_primary_ledger_id IN NUMBER,
1632 p_sec_alc_ledger_id IN NUMBER,
1633 p_application_id IN NUMBER,
1634 p_relationship_id IN NUMBER,
1635 p_upgrade_id IN NUMBER,
1636 p_script_name IN VARCHAR2,
1637 p_batch_size IN NUMBER,
1638 p_num_workers IN NUMBER)
1639 IS
1640
1641 l_log_module VARCHAR2(240);
1642 l_child_notcomplete BOOLEAN := TRUE;
1643 l_phase VARCHAR2(500) := NULL;
1644 l_req_status VARCHAR2(500) := NULL;
1645 l_devphase VARCHAR2(500) := NULL;
1646 l_devstatus VARCHAR2(500) := NULL;
1647 l_message VARCHAR2(500) := NULL;
1648 l_child_success VARCHAR2(1);
1649
1650 BEGIN
1651
1652 IF g_log_enabled THEN
1653 l_log_module := C_DEFAULT_MODULE||'.insert_data';
1654 END IF;
1655
1656
1657 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1658 trace
1659 (p_msg => 'insert_data procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1660 ,p_level => C_LEVEL_PROCEDURE
1661 ,p_module => l_log_module);
1662 END IF;
1663 fnd_file.put_line(fnd_file.log,'insert_data procedure start time: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1664
1665
1666 SELECT DECODE(primary_coa, secondary_coa, 'N', 'Y'),
1667 DECODE(primary_cal_set_name, primary_sec_alc_set_name,
1668 DECODE(primary_cal_per_type, primary_sec_alc_per_type, 'N', 'Y'),
1669 'Y'),
1670 dynamic_inserts,
1671 mapping_relationship_id,
1672 coa_mapping_name
1673 INTO g_ccid_map,
1674 g_calendar_convert,
1675 g_dynamic_flag,
1676 g_mapping_rel_id,
1677 g_coa_map_name
1678 FROM xla_historic_control
1679 WHERE primary_ledger = p_primary_ledger_id
1680 AND secondary_alc_ledger = p_sec_alc_ledger_id
1681 AND application_id = p_application_id
1682 AND relationship_id = p_relationship_id
1683 AND upgrade_id = p_upgrade_id
1684 AND script_name = p_script_name
1685 AND status = 'PHASE-DATA-START';
1686
1687
1688 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1689 trace
1690 (p_msg => 'g_ccid_map ' || g_ccid_map
1691 ,p_level => C_LEVEL_PROCEDURE
1692 ,p_module => l_log_module);
1693 trace
1694 (p_msg => 'g_calendar_convert ' || g_calendar_convert
1695 ,p_level => C_LEVEL_PROCEDURE
1696 ,p_module => l_log_module);
1697 trace
1698 (p_msg => 'g_dynamic_flag ' || g_dynamic_flag
1699 ,p_level => C_LEVEL_PROCEDURE
1700 ,p_module => l_log_module);
1701 trace
1702 (p_msg => 'g_mapping_rel_id ' || g_mapping_rel_id
1703 ,p_level => C_LEVEL_PROCEDURE
1704 ,p_module => l_log_module);
1705 trace
1706 (p_msg => 'g_coa_map_name ' || g_coa_map_name
1707 ,p_level => C_LEVEL_PROCEDURE
1708 ,p_module => l_log_module);
1709 END IF;
1710
1711
1712 FOR i in 1..p_num_workers
1713 LOOP
1714
1715 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1716 trace
1717 (p_msg => 'Calling FND_REQUEST.SUBMIT_REQUEST for worker '|| i
1718 ,p_level => C_LEVEL_PROCEDURE
1719 ,p_module => l_log_module);
1720 END IF;
1721
1722 g_worker(i) := FND_REQUEST.SUBMIT_REQUEST
1723 ( application => 'XLA'
1724 ,program => 'XLAHUPGSUB'
1725 ,description => NULL
1726 ,start_time => NULL
1727 ,sub_request => FALSE
1728 ,argument1 => p_batch_size
1729 ,argument2 => i -- worker_id
1730 ,argument3 => p_num_workers
1731 ,argument4 => p_script_name
1732 ,argument5 => p_application_id
1733 ,argument6 => p_primary_ledger_id
1734 ,argument7 => p_sec_alc_ledger_id
1735 ,argument8 => p_upgrade_id
1736 ,argument9 => p_relationship_id
1737 ,argument10 => g_ccid_map
1738 ,argument11 => g_calendar_convert
1739 ,argument12 => g_dynamic_flag
1740 ,argument13 => g_mapping_rel_id
1741 ,argument14 => g_coa_map_name
1742 );
1743
1744
1745 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1746 trace
1747 (p_msg => 'Called FND_REQUEST.SUBMIT_REQUEST for worker '|| i || ' generated REQUEST_ID ' || g_worker(i)
1748 ,p_level => C_LEVEL_PROCEDURE
1749 ,p_module => l_log_module);
1750 END IF;
1751
1752
1753 --added below check since parent program ends in normal status even if child workers not spawned
1754 IF (g_worker(i) = 0) OR (g_worker(i) IS NULL) OR (g_worker(i) < 0) THEN
1755
1756 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1757 trace
1758 (p_msg => 'validation failed: invalid request_id, worker '|| i || ' generated REQUEST_ID ' || g_worker(i)
1759 ,p_level => C_LEVEL_PROCEDURE
1760 ,p_module => l_log_module);
1761 END IF;
1762
1763 fnd_file.put_line(fnd_file.log, 'validation failed: invalid request_id, worker '|| i || ' generated REQUEST_ID ' || g_worker(i));
1764 ROLLBACK;
1765 RAISE G_CHILD_FAILED;
1766
1767
1768 END IF;
1769
1770
1771
1772 END LOOP;
1773
1774
1775 COMMIT; -- commit needed after FND request submission
1776
1777 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1778 trace
1779 (p_msg => 'commit executed after spawning child workers'
1780 ,p_level => C_LEVEL_PROCEDURE
1781 ,p_module => l_log_module);
1782 END IF;
1783
1784
1785
1786
1787 WHILE l_child_notcomplete
1788 LOOP
1789
1790 dbms_lock.sleep(100);
1791
1792 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1793 trace
1794 (p_msg => 'Checking all child request status after sleeping 100: '
1795 ,p_level => C_LEVEL_PROCEDURE
1796 ,p_module => l_log_module);
1797 END IF;
1798
1799 l_child_notcomplete := FALSE;
1800
1801 FOR i in 1..p_num_workers
1802 LOOP
1803
1804 IF (FND_CONCURRENT.GET_REQUEST_STATUS
1805 (g_worker(i),
1806 NULL,
1807 NULL,
1808 l_phase,
1809 l_req_status,
1810 l_devphase,
1811 l_devstatus,
1812 l_message))
1813 THEN NULL;
1814 END IF;
1815
1816
1817 IF (l_devphase <> 'COMPLETE') THEN
1818 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1819 trace
1820 (p_msg => 'Worker ' || i || ' with request ' || g_worker(i) || ' not complete, loop again'
1821 ,p_level => C_LEVEL_PROCEDURE
1822 ,p_module => l_log_module);
1823 END IF;
1824
1825 l_child_notcomplete := TRUE; --one or multiple workers will set the same variable to TRUE, never false
1826 END IF;
1827
1828
1829 IF (l_devphase = 'COMPLETE') AND l_devstatus NOT IN ('NORMAL','WARNING') THEN
1830 l_child_success := 'N'; -- one or multiple workers will set the same variable to N, or never set
1831 END IF;
1832
1833 END LOOP;
1834 END LOOP;
1835
1836
1837 -- above loop will continue till all DEVPHASE is complete
1838
1839
1840
1841 -- if any subworkers have failed then raise an error */
1842 IF l_child_success = 'N' THEN
1843
1844 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1845 trace
1846 (p_msg => 'l_child_success = N, raising exception G_CHILD_FAILED'
1847 ,p_level => C_LEVEL_PROCEDURE
1848 ,p_module => l_log_module);
1849 END IF;
1850
1851 fnd_file.put_line(fnd_file.log, 'validation failed: atleast one child failed with an error');
1852
1853
1854 ROLLBACK;
1855 RAISE G_CHILD_FAILED;
1856 -- run status remains PHASE-DATA-START even if rollback
1857
1858 ELSE
1859
1860 UPDATE xla_historic_control
1861 SET status = 'SUCCESS'
1862 WHERE primary_ledger = p_primary_ledger_id
1863 AND secondary_alc_ledger = p_sec_alc_ledger_id
1864 AND application_id = p_application_id
1865 AND script_name = p_script_name
1866 AND relationship_id = p_relationship_id
1867 AND g_upgrade_id = p_upgrade_id
1868 AND status = 'PHASE-DATA-START';
1869
1870 COMMIT;
1871
1872 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1873 trace
1874 (p_msg => 'commit executed all spawned child workers successful'
1875 ,p_level => C_LEVEL_PROCEDURE
1876 ,p_module => l_log_module);
1877 END IF;
1878 END IF;
1879
1880 END;
1881
1882
1883
1884
1885 PROCEDURE historic_worker
1886 (
1887 p_errbuf OUT NOCOPY VARCHAR2
1888 ,p_retcode OUT NOCOPY NUMBER
1889 ,p_batch_size IN NUMBER
1890 ,p_worker_id IN NUMBER
1891 ,p_num_workers IN NUMBER
1892 ,p_script_name IN VARCHAR2
1893 ,p_application_id IN NUMBER
1894 ,p_primary_ledger_id IN NUMBER
1895 ,p_sec_alc_ledger_id IN NUMBER
1896 ,p_ugprade_id IN NUMBER
1897 ,p_relationship_id IN NUMBER
1898 ,p_ccid_map IN VARCHAR2
1899 ,p_calendar_convert IN VARCHAR2
1900 ,p_dynamic_flag IN VARCHAR2
1901 ,p_mapping_rel_id IN NUMBER
1902 ,p_coa_map_name IN VARCHAR2
1903 )
1904 IS
1905
1906 l_log_module VARCHAR2(240);
1907 l_child_request NUMBER;
1908 l_table_name VARCHAR2(50);
1909 l_table_owner VARCHAR2(30);
1910 --commented for bug12329205 variable assignment of value 'XLA';
1911 -- added for bug12349350
1912 l_status VARCHAR2(5);
1913 l_industry VARCHAR2(5);
1914 e_no_table_owner Exception;
1915
1916 l_any_rows_to_process BOOLEAN;
1917 l_rows_processed NUMBER;
1918 l_mapping_rows NUMBER;
1919 l_gt_count NUMBER;
1920
1921 g_sub_validation_failed EXCEPTION;
1922 gl_invalid_mapping_name EXCEPTION;
1923 gl_disabled_mapping EXCEPTION;
1924 gl_invalid_mapping_rules EXCEPTION;
1925 gl_map_unexpected_error EXCEPTION;
1926 gl_bsv_map_no_source_bal_seg EXCEPTION;
1927 gl_bsv_map_no_target_bal_seg EXCEPTION;
1928 gl_bsv_map_no_segment_map EXCEPTION;
1929 gl_bsv_map_no_single_value EXCEPTION;
1930 gl_bsv_map_no_from_segment EXCEPTION;
1931 gl_bsv_map_not_bsv_derived EXCEPTION;
1932 gl_bsv_map_setup_error EXCEPTION;
1933 gl_bsv_map_mapping_error EXCEPTION;
1934 gl_bsv_map_unexpected_error EXCEPTION;
1935
1936 l_start_rowid ROWID;
1937 l_end_rowid ROWID;
1938
1939 l_iterations NUMBER;
1940
1941
1942
1943 BEGIN
1944
1945
1946 IF g_log_enabled THEN
1947 l_log_module := C_DEFAULT_MODULE||'.historic_worker';
1948 END IF;
1949
1950 l_child_request := fnd_global.conc_request_id();
1951 l_iterations := 0;
1952
1953 fnd_file.put_line(fnd_file.log, 'Started child worker request ' || l_child_request || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
1954
1955 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1956
1957 trace
1958 (p_msg => 'Started child worker request ' || l_child_request || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
1959 ,p_level => C_LEVEL_PROCEDURE
1960 ,p_module => l_log_module);
1961 trace
1962 (p_msg => 'p_batch_size = ' || p_batch_size
1963 ,p_level => C_LEVEL_PROCEDURE
1964 ,p_module => l_log_module);
1965 trace
1966 (p_msg => 'p_worker_id = ' || p_worker_id
1967 ,p_level => C_LEVEL_PROCEDURE
1968 ,p_module => l_log_module);
1969 trace
1970 (p_msg => 'p_num_workers = ' || p_num_workers
1971 ,p_level => C_LEVEL_PROCEDURE
1972 ,p_module => l_log_module);
1973 trace
1974 (p_msg => 'p_script_name = ' || p_script_name
1975 ,p_level => C_LEVEL_PROCEDURE
1976 ,p_module => l_log_module);
1977 trace
1978 (p_msg => 'p_application_id = ' || p_application_id
1979 ,p_level => C_LEVEL_PROCEDURE
1980 ,p_module => l_log_module);
1981 trace
1982 (p_msg => 'p_primary_ledger_id = ' || p_primary_ledger_id
1983 ,p_level => C_LEVEL_PROCEDURE
1984 ,p_module => l_log_module);
1985 trace
1986 (p_msg => 'p_sec_alc_ledger_id = ' || p_sec_alc_ledger_id
1987 ,p_level => C_LEVEL_PROCEDURE
1988 ,p_module => l_log_module);
1989 trace
1990 (p_msg => 'p_ugprade_id = ' || p_ugprade_id
1991 ,p_level => C_LEVEL_PROCEDURE
1992 ,p_module => l_log_module);
1993 trace
1994 (p_msg => 'p_relationship_id = ' || p_relationship_id
1995 ,p_level => C_LEVEL_PROCEDURE
1996 ,p_module => l_log_module);
1997 trace
1998 (p_msg => 'p_ccid_map = ' || p_ccid_map
1999 ,p_level => C_LEVEL_PROCEDURE
2000 ,p_module => l_log_module);
2001 trace
2002 (p_msg => 'p_calendar_convert = ' || p_calendar_convert
2003 ,p_level => C_LEVEL_PROCEDURE
2004 ,p_module => l_log_module);
2005 trace
2006 (p_msg => 'p_dynamic_flag = ' || p_dynamic_flag
2007 ,p_level => C_LEVEL_PROCEDURE
2008 ,p_module => l_log_module);
2009 trace
2010 (p_msg => 'p_mapping_rel_id = ' || p_mapping_rel_id
2011 ,p_level => C_LEVEL_PROCEDURE
2012 ,p_module => l_log_module);
2013 trace
2014 (p_msg => 'p_coa_map_name = ' || p_coa_map_name
2015 ,p_level => C_LEVEL_PROCEDURE
2016 ,p_module => l_log_module);
2017 END IF;
2018
2019 BEGIN
2020
2021 IF NOT fnd_installation.get_app_info (application_short_name => 'XLA',
2022 status => l_status ,
2023 industry => l_industry,
2024 oracle_schema => l_table_owner) THEN
2025
2026 --Added for 12349350
2027 RAISE e_no_table_owner;
2028 END IF;
2029
2030 EXCEPTION
2031 --Added for 12349350
2032 WHEN e_no_table_owner THEN
2033 fnd_file.put_line(fnd_file.log,to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')||' - Unable to derive table owner ');
2034 RAISE;
2035 WHEN OTHERS THEN
2036 RAISE;
2037
2038 END;
2039
2040
2041 l_table_name := 'XLA_AE_HEADERS';
2042
2043
2044 ad_parallel_updates_pkg.initialize_rowid_range(
2045 ad_parallel_updates_pkg.ROWID_RANGE,
2046 l_table_owner,
2047 l_table_name,
2048 p_script_name,
2049 p_worker_id,
2050 p_num_workers,
2051 p_batch_size,
2052 0);
2053
2054
2055 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2056 trace
2057 (p_msg => 'finished calling initialize_rowid_range '
2058 ,p_level => C_LEVEL_PROCEDURE
2059 ,p_module => l_log_module);
2060 END IF;
2061
2062
2063 ad_parallel_updates_pkg.get_rowid_range(
2064 l_start_rowid,
2065 l_end_rowid,
2066 l_any_rows_to_process,
2067 p_batch_size,
2068 TRUE);
2069
2070 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2071 trace
2072 (p_msg => 'finished calling get_rowid_range'
2073 ,p_level => C_LEVEL_PROCEDURE
2074 ,p_module => l_log_module);
2075 END IF;
2076
2077
2078 WHILE (l_any_rows_to_process = TRUE)
2079 LOOP
2080
2081 l_iterations := l_iterations + 1;
2082
2083 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2084 trace
2085 (p_msg => 'LOOP iteration started ' || l_iterations
2086 ,p_level => C_LEVEL_PROCEDURE
2087 ,p_module => l_log_module);
2088 END IF;
2089
2090
2091 SELECT COUNT(*)
2092 INTO l_gt_count
2093 FROM xla_historic_mapping_gt;
2094
2095 IF l_gt_count <> 0 THEN
2096
2097 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2098 trace
2099 (p_msg => 'GT count not 0 at iteration ' || l_iterations
2100 ,p_level => C_LEVEL_PROCEDURE
2101 ,p_module => l_log_module);
2102 END IF;
2103
2104 RAISE g_validation_failed;
2105 ROLLBACK;
2106
2107 END IF;
2108
2109
2110 INSERT INTO XLA_AE_HEADERS
2111 (
2112 AE_HEADER_ID
2113 , APPLICATION_ID
2114 , LEDGER_ID
2115 , ENTITY_ID
2116 , EVENT_ID
2117 , EVENT_TYPE_CODE
2118 , ACCOUNTING_DATE
2119 , GL_TRANSFER_STATUS_CODE
2120 , GL_TRANSFER_DATE
2121 , JE_CATEGORY_NAME
2122 , ACCOUNTING_ENTRY_STATUS_CODE
2123 , ACCOUNTING_ENTRY_TYPE_CODE
2124 , AMB_CONTEXT_CODE
2125 , PRODUCT_RULE_TYPE_CODE
2126 , PRODUCT_RULE_CODE
2127 , PRODUCT_RULE_VERSION
2128 , DESCRIPTION
2129 , DOC_SEQUENCE_ID
2130 , DOC_SEQUENCE_VALUE
2131 , ACCOUNTING_BATCH_ID
2132 , COMPLETION_ACCT_SEQ_VERSION_ID
2133 , CLOSE_ACCT_SEQ_VERSION_ID
2134 , COMPLETION_ACCT_SEQ_VALUE
2135 , CLOSE_ACCT_SEQ_VALUE
2136 , BUDGET_VERSION_ID
2137 , FUNDS_STATUS_CODE
2138 , ENCUMBRANCE_TYPE_ID
2139 , BALANCE_TYPE_CODE
2140 , REFERENCE_DATE
2141 , COMPLETED_DATE
2142 , PERIOD_NAME
2143 , PACKET_ID
2144 , COMPLETION_ACCT_SEQ_ASSIGN_ID
2145 , CLOSE_ACCT_SEQ_ASSIGN_ID
2146 , DOC_CATEGORY_CODE
2147 , ATTRIBUTE_CATEGORY
2148 , ATTRIBUTE1
2149 , ATTRIBUTE2
2150 , ATTRIBUTE3
2151 , ATTRIBUTE4
2152 , ATTRIBUTE5
2153 , ATTRIBUTE6
2154 , ATTRIBUTE7
2155 , ATTRIBUTE8
2156 , ATTRIBUTE9
2157 , ATTRIBUTE10
2158 , ATTRIBUTE11
2159 , ATTRIBUTE12
2160 , ATTRIBUTE13
2161 , ATTRIBUTE14
2162 , ATTRIBUTE15
2163 , GROUP_ID
2164 , DOC_SEQUENCE_VERSION_ID
2165 , DOC_SEQUENCE_ASSIGN_ID
2166 , CREATION_DATE
2167 , CREATED_BY
2168 , LAST_UPDATE_DATE
2169 , LAST_UPDATED_BY
2170 , LAST_UPDATE_LOGIN
2171 , PROGRAM_UPDATE_DATE
2172 , PROGRAM_APPLICATION_ID
2173 , PROGRAM_ID
2174 , REQUEST_ID
2175 , UPG_BATCH_ID
2176 , UPG_SOURCE_APPLICATION_ID
2177 , UPG_VALID_FLAG
2178 , ZERO_AMOUNT_FLAG
2179 , PARENT_AE_HEADER_ID
2180 , PARENT_AE_LINE_NUM
2181 , ACCRUAL_REVERSAL_FLAG
2182 , MERGE_EVENT_ID )
2183 SELECT /*+ rowid(xah) leading(xah) */
2184 XLA_AE_HEADERS_S.nextval
2185 , XAH.APPLICATION_ID
2186 , XHC.secondary_alc_ledger
2187 , XAH.ENTITY_ID
2188 , XAH.EVENT_ID
2189 , XAH.EVENT_TYPE_CODE
2190 , XAH.ACCOUNTING_DATE
2191 , XAH.GL_TRANSFER_STATUS_CODE -- stamped as Y or NT but assumed transferred via balance intialization
2192 , XAH.GL_TRANSFER_DATE
2193 , XAH.JE_CATEGORY_NAME
2194 , XAH.ACCOUNTING_ENTRY_STATUS_CODE
2195 , XAH.ACCOUNTING_ENTRY_TYPE_CODE
2196 , XAH.AMB_CONTEXT_CODE
2197 , XAH.PRODUCT_RULE_TYPE_CODE
2198 , XAH.PRODUCT_RULE_CODE
2199 , XAH.PRODUCT_RULE_VERSION
2200 , XAH.DESCRIPTION -- inherit primary description
2201 , XAH.DOC_SEQUENCE_ID
2202 , XAH.DOC_SEQUENCE_VALUE
2203 , NULL -- ACCOUNTING_BATCH_ID
2204 , NULL -- COMPLETION_ACCT_SEQ_VERSION_ID
2205 , NULL -- CLOSE_ACCT_SEQ_VERSION_ID
2206 , NULL -- COMPLETION_ACCT_SEQ_VALUE
2207 , NULL -- CLOSE_ACCT_SEQ_VALUE
2208 , XAH.BUDGET_VERSION_ID
2209 , XAH.FUNDS_STATUS_CODE
2210 , XAH.ENCUMBRANCE_TYPE_ID
2211 , XAH.BALANCE_TYPE_CODE -- validation in place to ensure both ledgers budgetary
2212 , XAH.REFERENCE_DATE
2213 , XAH.COMPLETED_DATE
2214 , XAH.PERIOD_NAME -- period name will be converted later if calendar different
2215 , XAH.PACKET_ID
2216 , NULL -- COMPLETION_ACCT_SEQ_ASSIGN_ID
2217 , NULL -- CLOSE_ACCT_SEQ_ASSIGN_ID
2218 , XAH.DOC_CATEGORY_CODE
2219 , XAH.ATTRIBUTE_CATEGORY
2220 , XAH.ATTRIBUTE1
2221 , XAH.ATTRIBUTE2
2222 , XAH.ATTRIBUTE3
2223 , XAH.ATTRIBUTE4
2224 , XAH.ATTRIBUTE5
2225 , XAH.ATTRIBUTE6
2226 , XAH.ATTRIBUTE7
2227 , XAH.ATTRIBUTE8
2228 , XAH.ATTRIBUTE9
2229 , XAH.ATTRIBUTE10
2230 , XAH.ATTRIBUTE11
2231 , XAH.ATTRIBUTE12
2232 , XAH.ATTRIBUTE13
2233 , XAH.ATTRIBUTE14
2234 , XAH.ATTRIBUTE15
2235 , XAH.GROUP_ID
2236 , XAH.DOC_SEQUENCE_VERSION_ID
2237 , XAH.DOC_SEQUENCE_ASSIGN_ID
2238 , SYSDATE -- CREATION_DATE
2239 , fnd_global.user_id -- CREATED_BY
2240 , SYSDATE -- LAST_UPDATE_DATE
2241 , fnd_global.user_id -- LAST_UPDATED_BY
2242 , fnd_global.login_id -- LAST_UPDATE_LOGIN
2243 , SYSDATE -- PROGRAM_UPDATE_DATE
2244 , fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID reverted -601
2245 , fnd_global.conc_program_id -- PROGRAM_ID reverted -601
2246
2247 , XHC.upgrade_id -- REQUEST_ID
2248 , XAH.ae_header_id -- UPG_BATCH_ID = AE_HEADER_ID of PRIMARY ledger, cleared later
2249
2250
2251 , -602 -- UPG_SOURCE_APPLICATION_ID
2252 , XAH.UPG_VALID_FLAG
2253 , XAH.ZERO_AMOUNT_FLAG
2254 , XAH.PARENT_AE_HEADER_ID
2255 , XAH.PARENT_AE_LINE_NUM
2256 , XAH.ACCRUAL_REVERSAL_FLAG
2257 , XAH.MERGE_EVENT_ID
2258 FROM xla_ae_headers XAH,
2259 xla_historic_control XHC
2260 WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
2261 AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
2262 AND XHC.upgrade_id = p_ugprade_id -- worker input
2263 AND XHC.application_id = p_application_id -- worker input
2264 AND XHC.script_name = p_script_name -- worker input
2265 AND XHC.relationship_id = p_relationship_id -- worker input
2266 AND XHC.status = 'PHASE-DATA-START'
2267 AND XAH.ledger_id = XHC.primary_ledger
2268 AND XAH.application_id = XHC.application_id
2269 AND XAH.accounting_entry_status_code = 'F'
2270 AND XAH.gl_transfer_status_code IN ('Y','NT') -- bug9278306
2271 AND XAH.balance_type_code in ('A', 'E')
2272 AND XAH.accounting_date >= XHC.start_date -- next run
2273 AND XAH.accounting_date <= XHC.end_date
2274 --AND XAH.accounting_batch_id <= NVL(XHC.sec_alc_min_acctng_batch_id , XAH.accounting_batch_id) -- modified by vgopiset
2275 AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0)) -- added to handle 11i data
2276 AND XAH.event_type_code <> 'MANUAL' -- added by vgopiset
2277 AND XAH.ROWID BETWEEN l_start_rowid AND l_end_rowid;
2278
2279 l_rows_processed := SQL%ROWCOUNT;
2280
2281
2282 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2283
2284 trace
2285 (p_msg => 'Rows inserted into xla_ae_headers: ' || l_rows_processed || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2286 ,p_level => C_LEVEL_PROCEDURE
2287 ,p_module => l_log_module);
2288
2289 END IF;
2290
2291
2292
2293 IF (l_rows_processed <> 0) THEN --insert into GT/XDL/XAL only when headers are inserted.
2294
2295
2296
2297 INSERT INTO xla_historic_mapping_gt(primary_header_id, new_header_id)
2298 SELECT /*+ rowid(xah) leading(xah) index(xahnew xla_ae_headers_n2)*/
2299 XAH.ae_header_id, XAHNEW.ae_header_id
2300 FROM xla_ae_headers XAH,
2301 xla_historic_control XHC,
2302 xla_ae_headers XAHNEW
2303 WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
2304 AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
2305 AND XHC.upgrade_id = p_ugprade_id -- worker input
2306 AND XHC.application_id = p_application_id -- worker input
2307 AND XHC.script_name = p_script_name -- worker input
2308 AND XHC.relationship_id = p_relationship_id -- worker input
2309 AND XHC.status = 'PHASE-DATA-START'
2310 AND XAH.ledger_id = XHC.primary_ledger
2311 AND XAH.application_id = XHC.application_id
2312 AND XAH.accounting_entry_status_code = 'F'
2313 AND XAH.gl_transfer_status_code IN ('Y','NT') -- bug9278306
2314 AND XAH.balance_type_code in ('A', 'E')
2315 AND XAH.accounting_date >= XHC.start_date
2316 AND XAH.accounting_date <= XHC.end_date
2317 --AND XAH.accounting_batch_id <= NVL(XHC.sec_alc_min_acctng_batch_id , XAH.accounting_batch_id)
2318 AND nvl(XAH.accounting_batch_id,0) <= NVL(XHC.sec_alc_min_acctng_batch_id , nvl(XAH.accounting_batch_id,0)) -- added to handle 11i data
2319 AND XAH.event_type_code <> 'MANUAL' -- added by vgopiset
2320 AND XAH.ROWID BETWEEN l_start_rowid AND l_end_rowid
2321 AND XAHNEW.application_id = XHC.application_id
2322 AND XAHNEW.ledger_id = secondary_alc_ledger
2323 AND XAHNEW.accounting_entry_status_code = 'F'
2324 AND XAHNEW.gl_transfer_status_code IN ('Y','NT') -- bug9278306
2325 AND XAHNEW.balance_type_code in ('A', 'E')
2326 AND XAHNEW.accounting_date >= XHC.start_date
2327 AND XAHNEW.accounting_date <= XHC.end_date
2328 AND XAHNEW.upg_batch_id = XAH.ae_header_id
2329 AND XAHNEW.event_id = XAH.event_id
2330 AND XAHNEW.application_id = XAH.application_id;
2331
2332
2333 l_mapping_rows := SQL%ROWCOUNT;
2334
2335 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2336
2337 trace
2338 (p_msg => 'Rows inserted into xla_historic_mapping_gt: ' || l_mapping_rows || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2339 ,p_level => C_LEVEL_PROCEDURE
2340 ,p_module => l_log_module);
2341
2342 END IF;
2343
2344
2345 IF l_mapping_rows <> l_rows_processed THEN
2346
2347 ROLLBACK; -- added by vgopiset as above INSERTED HEADERS should not be COMMITED
2348
2349 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2350 trace
2351 (p_msg => 'XAH rowcount and mapping GT rowcount mismatch'
2352 ,p_level => C_LEVEL_PROCEDURE
2353 ,p_module => l_log_module);
2354 END IF;
2355
2356 RAISE g_sub_validation_failed;
2357
2358 END IF;
2359
2360
2361
2362 -- rowid condition not needed below since only x-y rowid XAH headers stored in xla_historic_mapping_gt
2363
2364 INSERT INTO XLA_DISTRIBUTION_LINKS
2365 (
2366 APPLICATION_ID
2367 , EVENT_ID
2368 , AE_HEADER_ID
2369 , AE_LINE_NUM
2370 , SOURCE_DISTRIBUTION_TYPE
2371 , SOURCE_DISTRIBUTION_ID_CHAR_1
2372 , SOURCE_DISTRIBUTION_ID_CHAR_2
2373 , SOURCE_DISTRIBUTION_ID_CHAR_3
2374 , SOURCE_DISTRIBUTION_ID_CHAR_4
2375 , SOURCE_DISTRIBUTION_ID_CHAR_5
2376 , SOURCE_DISTRIBUTION_ID_NUM_1
2377 , SOURCE_DISTRIBUTION_ID_NUM_2
2378 , SOURCE_DISTRIBUTION_ID_NUM_3
2379 , SOURCE_DISTRIBUTION_ID_NUM_4
2380 , SOURCE_DISTRIBUTION_ID_NUM_5
2381 , TAX_LINE_REF_ID
2382 , TAX_SUMMARY_LINE_REF_ID
2383 , TAX_REC_NREC_DIST_REF_ID
2384 , STATISTICAL_AMOUNT
2385 , REF_AE_HEADER_ID
2386 , REF_TEMP_LINE_NUM
2387 , ACCOUNTING_LINE_CODE
2388 , ACCOUNTING_LINE_TYPE_CODE
2389 , MERGE_DUPLICATE_CODE
2390 , TEMP_LINE_NUM
2391 , REF_EVENT_ID
2392 , LINE_DEFINITION_OWNER_CODE
2393 , LINE_DEFINITION_CODE
2394 , EVENT_CLASS_CODE
2395 , EVENT_TYPE_CODE
2396 , UPG_BATCH_ID
2397 , CALCULATE_ACCTD_AMTS_FLAG
2398 , CALCULATE_G_L_AMTS_FLAG
2399 , ROUNDING_CLASS_CODE
2400 , DOCUMENT_ROUNDING_LEVEL
2401 , UNROUNDED_ENTERED_DR
2402 , UNROUNDED_ENTERED_CR
2403 , DOC_ROUNDING_ENTERED_AMT
2404 , DOC_ROUNDING_ACCTD_AMT
2405 , UNROUNDED_ACCOUNTED_CR
2406 , UNROUNDED_ACCOUNTED_DR
2407 , APPLIED_TO_APPLICATION_ID
2408 , APPLIED_TO_ENTITY_CODE
2409 , APPLIED_TO_ENTITY_ID
2410 , APPLIED_TO_SOURCE_ID_NUM_1
2411 , APPLIED_TO_SOURCE_ID_NUM_2
2412 , APPLIED_TO_SOURCE_ID_NUM_3
2413 , APPLIED_TO_SOURCE_ID_NUM_4
2414 , APPLIED_TO_SOURCE_ID_CHAR_1
2415 , APPLIED_TO_SOURCE_ID_CHAR_2
2416 , APPLIED_TO_SOURCE_ID_CHAR_3
2417 , APPLIED_TO_SOURCE_ID_CHAR_4
2418 , APPLIED_TO_DISTRIBUTION_TYPE
2419 , APPLIED_TO_DIST_ID_NUM_1
2420 , APPLIED_TO_DIST_ID_NUM_2
2421 , APPLIED_TO_DIST_ID_NUM_3
2422 , APPLIED_TO_DIST_ID_NUM_4
2423 , APPLIED_TO_DIST_ID_NUM_5
2424 , APPLIED_TO_DIST_ID_CHAR_1
2425 , APPLIED_TO_DIST_ID_CHAR_2
2426 , APPLIED_TO_DIST_ID_CHAR_3
2427 , APPLIED_TO_DIST_ID_CHAR_4
2428 , APPLIED_TO_DIST_ID_CHAR_5
2429 , ALLOC_TO_APPLICATION_ID
2430 , ALLOC_TO_ENTITY_CODE
2431 , ALLOC_TO_SOURCE_ID_NUM_1
2432 , ALLOC_TO_SOURCE_ID_NUM_2
2433 , ALLOC_TO_SOURCE_ID_NUM_3
2434 , ALLOC_TO_SOURCE_ID_NUM_4
2435 , ALLOC_TO_SOURCE_ID_CHAR_1
2436 , ALLOC_TO_SOURCE_ID_CHAR_2
2437 , ALLOC_TO_SOURCE_ID_CHAR_3
2438 , ALLOC_TO_SOURCE_ID_CHAR_4
2439 , ALLOC_TO_DISTRIBUTION_TYPE
2440 , ALLOC_TO_DIST_ID_NUM_1
2441 , ALLOC_TO_DIST_ID_NUM_2
2442 , ALLOC_TO_DIST_ID_NUM_3
2443 , ALLOC_TO_DIST_ID_NUM_4
2444 , ALLOC_TO_DIST_ID_NUM_5
2445 , ALLOC_TO_DIST_ID_CHAR_1
2446 , ALLOC_TO_DIST_ID_CHAR_2
2447 , ALLOC_TO_DIST_ID_CHAR_3
2448 , ALLOC_TO_DIST_ID_CHAR_4
2449 , ALLOC_TO_DIST_ID_CHAR_5
2450 , GAIN_OR_LOSS_REF)
2451 SELECT /*+ leading(xmap) */
2452 XDL.APPLICATION_ID
2453 , XDL.EVENT_ID
2454 , XMAP.new_header_id
2455 , XDL.AE_LINE_NUM
2456 , XDL.SOURCE_DISTRIBUTION_TYPE
2457 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_1
2458 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_2
2459 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_3
2460 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_4
2461 , XDL.SOURCE_DISTRIBUTION_ID_CHAR_5
2462 , XDL.SOURCE_DISTRIBUTION_ID_NUM_1
2463 , XDL.SOURCE_DISTRIBUTION_ID_NUM_2
2464 , XDL.SOURCE_DISTRIBUTION_ID_NUM_3
2465 , XDL.SOURCE_DISTRIBUTION_ID_NUM_4
2466 , XDL.SOURCE_DISTRIBUTION_ID_NUM_5
2467 , XDL.TAX_LINE_REF_ID
2468 , XDL.TAX_SUMMARY_LINE_REF_ID
2469 , XDL.TAX_REC_NREC_DIST_REF_ID
2470 , XDL.STATISTICAL_AMOUNT
2471 , XDL.ref_ae_header_id -- REF_AE_HEADER_ID
2472 , NULL -- REF_TEMP_LINE_NUM
2473 , XDL.ACCOUNTING_LINE_CODE
2474 , XDL.ACCOUNTING_LINE_TYPE_CODE
2475 , XDL.MERGE_DUPLICATE_CODE
2476 -- Taking Absolute Value to ensure we always create reversal entries (even if it means DOUBLE
2477 -- REVERSAL) and adding a LARGE VALUE to ensure that PREPAYMENT ADJUSTED cases where +ve/-ve
2478 -- exists in the SAME HEADER doesn't cause UNIQUE CONSTRAINT EXCEPTION because of ABS value.
2479 , ABS( XDL.TEMP_LINE_NUM + 5000000 )
2480 , XDL.REF_EVENT_ID
2481 , XDL.LINE_DEFINITION_OWNER_CODE
2482 , XDL.LINE_DEFINITION_CODE
2483 , XDL.EVENT_CLASS_CODE
2484 , XDL.EVENT_TYPE_CODE
2485 , XDL.UPG_BATCH_ID
2486 , 'Y' CALCULATE_ACCTD_AMTS_FLAG -- XDL.CALCULATE_ACCTD_AMTS_FLAG commented for bug12329205
2487 , 'Y' CALCULATE_G_L_AMTS_FLAG -- XDL.CALCULATE_G_L_AMTS_FLAG commented for bug12329205
2488 , XDL.ROUNDING_CLASS_CODE
2489 , XDL.DOCUMENT_ROUNDING_LEVEL
2490 , XDL.UNROUNDED_ENTERED_DR -- retain XDL unrounded entered dr
2491 , XDL.UNROUNDED_ENTERED_CR -- retain XDL unrounded entered cr
2492 , XDL.DOC_ROUNDING_ENTERED_AMT
2493 , XDL.DOC_ROUNDING_ACCTD_AMT
2494
2495 ,DECODE(XAL.currency_code, XHC.sec_alc_currency_code, XDL.UNROUNDED_ENTERED_CR,
2496 -- if ledger currency transaction for secondary then set unrounded accounted to unrouned entered
2497 -- if not ledger currency transaction for secondary ledger then use either of following
2498 DECODE(XHC.sec_alc_mau,NULL,
2499
2500 ((( DECODE(XHC.conversion_option, 'D', NVL(XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ENTERED_CR),XDL.UNROUNDED_ENTERED_CR)
2501 /XRUR.denominator_rate
2502 ) * XRUR.numerator_rate)
2503 ),
2504
2505 ((( DECODE(XHC.conversion_option, 'D',NVL(XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ENTERED_CR),XDL.UNROUNDED_ENTERED_CR)
2506 /XRUR.denominator_rate
2507 ) * XRUR.numerator_rate)
2508 /XHC.sec_alc_mau)*XHC.sec_alc_mau
2509 )
2510 ) -- XDL.UNROUNDED_ACCOUNTED_CR
2511
2512 ,DECODE(XAL.currency_code, XHC.sec_alc_currency_code, XDL.UNROUNDED_ENTERED_DR,
2513 -- if ledger currency transaction for secondary then set unrounded accounted to unrouned entered
2514 -- if not ledger currency transaction for secondary ledger then use either of following
2515 DECODE(XHC.sec_alc_mau,NULL,
2516
2517 ((( DECODE(XHC.conversion_option, 'D', NVL(XDL.UNROUNDED_ACCOUNTED_DR, XDL.UNROUNDED_ENTERED_DR),XDL.UNROUNDED_ENTERED_DR)
2518 /XRUR.denominator_rate
2519 ) *XRUR.numerator_rate)
2520 ),
2521
2522 ((( DECODE(XHC.conversion_option, 'D',NVL(XDL.UNROUNDED_ACCOUNTED_DR, XDL.UNROUNDED_ENTERED_DR),XDL.UNROUNDED_ENTERED_DR)
2523 /XRUR.denominator_rate
2524 ) * XRUR.numerator_rate)
2525 /XHC.sec_alc_mau)*XHC.sec_alc_mau
2526 )
2527 ) -- XDL.UNROUNDED_ACCOUNTED_DR
2528 , XDL.APPLIED_TO_APPLICATION_ID
2529 , XDL.APPLIED_TO_ENTITY_CODE
2530 , XDL.APPLIED_TO_ENTITY_ID
2531 , XDL.APPLIED_TO_SOURCE_ID_NUM_1
2532 , XDL.APPLIED_TO_SOURCE_ID_NUM_2
2533 , XDL.APPLIED_TO_SOURCE_ID_NUM_3
2534 , XDL.APPLIED_TO_SOURCE_ID_NUM_4
2535 , XDL.APPLIED_TO_SOURCE_ID_CHAR_1
2536 , XDL.APPLIED_TO_SOURCE_ID_CHAR_2
2537 , XDL.APPLIED_TO_SOURCE_ID_CHAR_3
2538 , XDL.APPLIED_TO_SOURCE_ID_CHAR_4
2539 , XDL.APPLIED_TO_DISTRIBUTION_TYPE
2540 , XDL.APPLIED_TO_DIST_ID_NUM_1
2541 , XDL.APPLIED_TO_DIST_ID_NUM_2
2542 , XDL.APPLIED_TO_DIST_ID_NUM_3
2543 , XDL.APPLIED_TO_DIST_ID_NUM_4
2544 , XDL.APPLIED_TO_DIST_ID_NUM_5
2545 , XDL.APPLIED_TO_DIST_ID_CHAR_1
2546 , XDL.APPLIED_TO_DIST_ID_CHAR_2
2547 , XDL.APPLIED_TO_DIST_ID_CHAR_3
2548 , XDL.APPLIED_TO_DIST_ID_CHAR_4
2549 , XDL.APPLIED_TO_DIST_ID_CHAR_5
2550 , XDL.ALLOC_TO_APPLICATION_ID
2551 , XDL.ALLOC_TO_ENTITY_CODE
2552 , XDL.ALLOC_TO_SOURCE_ID_NUM_1
2553 , XDL.ALLOC_TO_SOURCE_ID_NUM_2
2554 , XDL.ALLOC_TO_SOURCE_ID_NUM_3
2555 , XDL.ALLOC_TO_SOURCE_ID_NUM_4
2556 , XDL.ALLOC_TO_SOURCE_ID_CHAR_1
2557 , XDL.ALLOC_TO_SOURCE_ID_CHAR_2
2558 , XDL.ALLOC_TO_SOURCE_ID_CHAR_3
2559 , XDL.ALLOC_TO_SOURCE_ID_CHAR_4
2560 , XDL.ALLOC_TO_DISTRIBUTION_TYPE
2561 , XDL.ALLOC_TO_DIST_ID_NUM_1
2562 , XDL.ALLOC_TO_DIST_ID_NUM_2
2563 , XDL.ALLOC_TO_DIST_ID_NUM_3
2564 , XDL.ALLOC_TO_DIST_ID_NUM_4
2565 , XDL.ALLOC_TO_DIST_ID_NUM_5
2566 , XDL.ALLOC_TO_DIST_ID_CHAR_1
2567 , XDL.ALLOC_TO_DIST_ID_CHAR_2
2568 , XDL.ALLOC_TO_DIST_ID_CHAR_3
2569 , XDL.ALLOC_TO_DIST_ID_CHAR_4
2570 , XDL.ALLOC_TO_DIST_ID_CHAR_5
2571 , GAIN_OR_LOSS_REF
2572 FROM xla_historic_control XHC,
2573 xla_historic_mapping_gt XMAP,
2574 xla_ae_lines XAL,
2575 xla_distribution_links XDL,
2576 xla_rc_upgrade_rates XRUR
2577 WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
2578 AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
2579 AND XHC.upgrade_id = p_ugprade_id -- worker input
2580 AND XHC.application_id = p_application_id -- worker input
2581 AND XHC.script_name = p_script_name -- worker input
2582 AND XHC.relationship_id = p_relationship_id -- worker input
2583 AND XHC.status = 'PHASE-DATA-START'
2584 AND XMAP.primary_header_id = XAL.ae_header_id
2585 AND XAL.application_id = XHC.application_id
2586 AND XAL.gain_or_loss_flag <> DECODE(XHC.conversion_option, 'D',' ','Y')
2587 AND XDL.application_id = XAL.application_id
2588 AND XDL.ae_header_id = XAL.ae_header_id -- primary header id
2589 AND XDL.ae_line_num = XAL.ae_line_num
2590 AND XRUR.upgrade_run_id = XHC.upgrade_id
2591 AND XRUR.relationship_id = XHC.relationship_id
2592 AND XRUR.to_currency = XHC.sec_alc_currency_code
2593 AND XRUR.from_currency = DECODE(XHC.conversion_option, 'D',
2594 XHC.primary_currency_code, -- functional currency of primary in case of D, single rate
2595 XAL.currency_code); -- entered currency in XAL in case of I, multiple rates
2596
2597
2598 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2599
2600 trace
2601 (p_msg => 'Rows inserted into xla_distribution_links: ' || SQL%ROWCOUNT ||' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2602 ,p_level => C_LEVEL_PROCEDURE
2603 ,p_module => l_log_module);
2604
2605 END IF;
2606
2607
2608
2609 INSERT INTO XLA_AE_LINES
2610 ( AE_HEADER_ID
2611 , AE_LINE_NUM
2612 , APPLICATION_ID
2613 , CODE_COMBINATION_ID
2614 , GL_TRANSFER_MODE_CODE
2615 , GL_SL_LINK_ID
2616 , ACCOUNTING_CLASS_CODE
2617 , PARTY_ID
2618 , PARTY_SITE_ID
2619 , PARTY_TYPE_CODE
2620 , ENTERED_DR
2621 , ENTERED_CR
2622 , ACCOUNTED_DR
2623 , ACCOUNTED_CR
2624 , DESCRIPTION
2625 , STATISTICAL_AMOUNT
2626 , CURRENCY_CODE
2627 , CURRENCY_CONVERSION_DATE
2628 , CURRENCY_CONVERSION_RATE
2629 , CURRENCY_CONVERSION_TYPE
2630 , USSGL_TRANSACTION_CODE
2631 , JGZZ_RECON_REF
2632 , CONTROL_BALANCE_FLAG
2633 , ANALYTICAL_BALANCE_FLAG
2634 , ATTRIBUTE_CATEGORY
2635 , ATTRIBUTE1
2636 , ATTRIBUTE2
2637 , ATTRIBUTE3
2638 , ATTRIBUTE4
2639 , ATTRIBUTE5
2640 , ATTRIBUTE6
2641 , ATTRIBUTE7
2642 , ATTRIBUTE8
2643 , ATTRIBUTE9
2644 , ATTRIBUTE10
2645 , ATTRIBUTE11
2646 , ATTRIBUTE12
2647 , ATTRIBUTE13
2648 , ATTRIBUTE14
2649 , ATTRIBUTE15
2650 , GL_SL_LINK_TABLE
2651 , DISPLAYED_LINE_NUMBER
2652 , CREATION_DATE
2653 , CREATED_BY
2654 , LAST_UPDATE_DATE
2655 , LAST_UPDATED_BY
2656 , LAST_UPDATE_LOGIN
2657 , PROGRAM_UPDATE_DATE
2658 , PROGRAM_APPLICATION_ID
2659 , PROGRAM_ID
2660 , REQUEST_ID
2661 , UPG_BATCH_ID
2662 , UPG_TAX_REFERENCE_ID1
2663 , UPG_TAX_REFERENCE_ID2
2664 , UPG_TAX_REFERENCE_ID3
2665 , UNROUNDED_ACCOUNTED_DR
2666 , UNROUNDED_ACCOUNTED_CR
2667 , GAIN_OR_LOSS_FLAG
2668 , UNROUNDED_ENTERED_DR
2669 , UNROUNDED_ENTERED_CR
2670 , SUBSTITUTED_CCID
2671 , BUSINESS_CLASS_CODE
2672 , MPA_ACCRUAL_ENTRY_FLAG
2673 , ENCUMBRANCE_TYPE_ID
2674 , FUNDS_STATUS_CODE
2675 , MERGE_CODE_COMBINATION_ID
2676 , MERGE_PARTY_ID
2677 , MERGE_PARTY_SITE_ID
2678 , ACCOUNTING_DATE
2679 , LEDGER_ID
2680 , SOURCE_TABLE
2681 , SOURCE_ID
2682 , ACCOUNT_OVERLAY_SOURCE_ID )
2683 SELECT /*+ leading(xmap) */
2684 XMAP.new_header_id
2685 , XAL.AE_LINE_NUM
2686 , XAL.APPLICATION_ID
2687 , XAL.CODE_COMBINATION_ID
2688 , XAL.GL_TRANSFER_MODE_CODE
2689 , NULL --GL_SL_LINK_ID
2690 , XAL.ACCOUNTING_CLASS_CODE
2691 , XAL.PARTY_ID
2692 , XAL.PARTY_SITE_ID
2693 , XAL.PARTY_TYPE_CODE
2694 , XAL.ENTERED_DR
2695 , XAL.ENTERED_CR
2696 , DECODE(XAL.currency_code, XHC.sec_alc_currency_code,
2697 ENTERED_DR,
2698 DECODE(XHC.sec_alc_mau,NULL,
2699 ROUND(((DECODE(XHC.conversion_option, 'D',
2700 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
2701 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2702 XRUR.numerator_rate),XHC.sec_alc_precision),
2703 ROUND(((DECODE(XHC.conversion_option, 'D',
2704 NVL(XAL.ACCOUNTED_DR, XAL.ENTERED_DR),
2705 XAL.ENTERED_DR)/XRUR.denominator_rate)*
2706 XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau))
2707 , DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
2708 ENTERED_CR,
2709 DECODE(XHC.sec_alc_mau,NULL,
2710 ROUND(((DECODE(XHC.conversion_option, 'D',
2711 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2712 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2713 XRUR.numerator_rate),XHC.sec_alc_precision),
2714 ROUND(((DECODE(XHC.conversion_option, 'D',
2715 NVL(XAL.ACCOUNTED_CR, XAL.ENTERED_CR),
2716 XAL.ENTERED_CR)/XRUR.denominator_rate)*
2717 XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau))
2718 , XAL.DESCRIPTION
2719 , XAL.STATISTICAL_AMOUNT
2720 , XAL.CURRENCY_CODE
2721 , DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
2722 null,
2723 DECODE(XHC.conversion_option, 'D',
2724 NVL(XAL.CURRENCY_CONVERSION_DATE, XAL.ACCOUNTING_DATE),
2725 XHC.currency_conversion_date)) -- CURRENCY_CONVERSION_DATE
2726 , DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
2727 null,
2728 DECODE(XHC.conversion_option, 'D',
2729 NVL(XAL.currency_conversion_rate,1)*XRUR.conversion_rate,
2730 XRUR.conversion_rate)) -- CURRENCY_CONVERSION_RATE
2731 , DECODE(XAL.currency_code,XHC.sec_alc_currency_code,
2732 null,
2733 DECODE(XHC.conversion_option, 'D',
2734 NVL(XAL.CURRENCY_CONVERSION_TYPE, 'EMU FIXED'),
2735 g_currency_conversion_type)) --CURRENCY_CONVERSION_TYPE
2736
2737 , XAL.USSGL_TRANSACTION_CODE
2738 , XAL.JGZZ_RECON_REF
2739 , NULL --XAL.CONTROL_BALANCE_FLAG
2740 , XAL.ANALYTICAL_BALANCE_FLAG
2741 , XAL.ATTRIBUTE_CATEGORY
2742 , XAL.ATTRIBUTE1
2743 , XAL.ATTRIBUTE2
2744 , XAL.ATTRIBUTE3
2745 , XAL.ATTRIBUTE4
2746 , XAL.ATTRIBUTE5
2747 , XAL.ATTRIBUTE6
2748 , XAL.ATTRIBUTE7
2749 , XAL.ATTRIBUTE8
2750 , XAL.ATTRIBUTE9
2751 , XAL.ATTRIBUTE10
2752 , XAL.ATTRIBUTE11
2753 , XAL.ATTRIBUTE12
2754 , XAL.ATTRIBUTE13
2755 , XAL.ATTRIBUTE14
2756 , XAL.ATTRIBUTE15
2757 , XAL.GL_SL_LINK_TABLE
2758 , XAL.DISPLAYED_LINE_NUMBER
2759 , SYSDATE
2760 , fnd_global.user_id
2761 , SYSDATE
2762 , fnd_global.user_id
2763 , fnd_global.login_id
2764 , SYSDATE
2765 , -602
2766 , -602
2767 , XHC.upgrade_id
2768 , XAL.UPG_BATCH_ID
2769 , XAL.UPG_TAX_REFERENCE_ID1
2770 , XAL.UPG_TAX_REFERENCE_ID2
2771 , XAL.UPG_TAX_REFERENCE_ID3
2772 , DECODE(XAL.currency_code, XHC.sec_alc_currency_code,
2773 UNROUNDED_ENTERED_DR,
2774 DECODE(XHC.sec_alc_mau,null,
2775 (((DECODE(XHC.conversion_option, 'D',
2776 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2777 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2778 XRUR.numerator_rate)),
2779 (((DECODE(XHC.conversion_option, 'D',
2780 NVL(XAL.UNROUNDED_ACCOUNTED_DR, XAL.UNROUNDED_ENTERED_DR),
2781 XAL.UNROUNDED_ENTERED_DR)/XRUR.denominator_rate)*
2782 XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau)) --UNROUNDED_ACCOUNTED_DR
2783 , DECODE(XAL.currency_code, XHC.sec_alc_currency_code,
2784 UNROUNDED_ENTERED_CR,
2785 DECODE(XHC.sec_alc_mau,null,
2786 (((DECODE(XHC.conversion_option, 'D',
2787 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2788 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2789 XRUR.numerator_rate)),
2790 (((DECODE(XHC.conversion_option, 'D',
2791 NVL(XAL.UNROUNDED_ACCOUNTED_CR, XAL.UNROUNDED_ENTERED_CR),
2792 XAL.UNROUNDED_ENTERED_CR)/XRUR.denominator_rate)*
2793 XRUR.numerator_rate)/XHC.sec_alc_mau)*XHC.sec_alc_mau)) --UNROUNDED_ACCOUNTED_CR
2794 , XAL.GAIN_OR_LOSS_FLAG
2795 , XAL.UNROUNDED_ENTERED_DR
2796 , XAL.UNROUNDED_ENTERED_CR
2797 , XAL.SUBSTITUTED_CCID
2798 , XAL.BUSINESS_CLASS_CODE
2799 , XAL.MPA_ACCRUAL_ENTRY_FLAG
2800 , XAL.ENCUMBRANCE_TYPE_ID
2801 , XAL.FUNDS_STATUS_CODE
2802 , XAL.MERGE_CODE_COMBINATION_ID
2803 , XAL.MERGE_PARTY_ID
2804 , XAL.MERGE_PARTY_SITE_ID
2805 , XAL.ACCOUNTING_DATE
2806 , XHC.secondary_alc_ledger
2807 , XAL.SOURCE_TABLE
2808 , XAL.SOURCE_ID
2809 , XAL.ACCOUNT_OVERLAY_SOURCE_ID
2810 FROM xla_historic_control XHC,
2811 xla_historic_mapping_gt XMAP,
2812 xla_ae_lines XAL,
2813 xla_rc_upgrade_rates XRUR
2814 WHERE XHC.primary_ledger = p_primary_ledger_id -- worker input
2815 AND XHC.secondary_alc_ledger = p_sec_alc_ledger_id -- worker input
2816 AND XHC.upgrade_id = p_ugprade_id -- worker input
2817 AND XHC.application_id = p_application_id -- worker input
2818 AND XHC.script_name = p_script_name -- worker input
2819 AND XHC.relationship_id = p_relationship_id -- worker input
2820 AND XHC.status = 'PHASE-DATA-START'
2821 AND XMAP.primary_header_id = XAL.ae_header_id -- primary header id
2822 AND XAL.application_id = XHC.application_id
2823 AND XAL.gain_or_loss_flag <> DECODE(XHC.conversion_option, 'D',' ','Y')
2824 AND XRUR.upgrade_run_id = XHC.upgrade_id
2825 AND XRUR.relationship_id = XHC.relationship_id
2826 AND XRUR.to_currency = XHC.sec_alc_currency_code
2827 AND XRUR.from_currency = DECODE(XHC.conversion_option, 'D',
2828 XHC.primary_currency_code, -- functional currency of primary in case of D, single rate
2829 XAL.currency_code);
2830
2831
2832
2833 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2834
2835 trace
2836 (p_msg => 'Rows inserted into xla_ae_lines: ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2837 ,p_level => C_LEVEL_PROCEDURE
2838 ,p_module => l_log_module);
2839
2840 END IF;
2841
2842
2843 IF p_ccid_map = 'Y' THEN
2844
2845
2846 DELETE FROM gl_accts_map_int_gt; -- bug 4564062
2847
2848
2849 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2850 trace
2851 (p_msg => 'Rows deleted from gl_accts_map_int_gt = ' || SQL%ROWCOUNT
2852 ,p_level => C_LEVEL_PROCEDURE
2853 ,p_module => l_log_module);
2854 END IF;
2855
2856
2857 INSERT INTO gl_accts_map_int_gt(coa_mapping_id, from_ccid)
2858 SELECT distinct p_mapping_rel_id, code_combination_id
2859 FROM xla_ae_lines XAL
2860 WHERE application_id = p_application_id
2861 AND EXISTS (SELECT 1
2862 FROM xla_historic_mapping_gt XMAP
2863 WHERE XMAP.new_header_id = XAL.ae_header_id); -- sec/alc header id
2864
2865
2866 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2867
2868 trace
2869 (p_msg => 'Rows (distinct ccids) inserted into gl_accts_map_int_gt ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2870 ,p_level => C_LEVEL_PROCEDURE
2871 ,p_module => l_log_module);
2872
2873 END IF;
2874
2875
2876 GL_ACCOUNTS_MAP_GRP.MAP(mapping_name => p_coa_map_name
2877 ,create_ccid => (NVL(p_dynamic_flag,'N') ='Y' )
2878 ,debug => g_log_enabled);
2879
2880 UPDATE xla_ae_lines XAL
2881 SET code_combination_id = (SELECT (nvl(GL_INT.to_ccid, -1))
2882 FROM gl_accts_map_int_gt GL_INT
2883 WHERE XAL.code_combination_id = GL_INT.from_ccid
2884 AND GL_INT.coa_mapping_id = p_mapping_rel_id)
2885 WHERE application_id = p_application_id
2886 AND EXISTS (SELECT 1
2887 FROM xla_historic_mapping_gt XMAP
2888 WHERE XMAP.new_header_id = XAL.ae_header_id); -- sec/alc header id
2889
2890 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2891
2892 trace
2893 (p_msg => 'Rows updated in xla_ae_lines with new ccid = ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2894 ,p_level => C_LEVEL_PROCEDURE
2895 ,p_module => l_log_module);
2896
2897 END IF;
2898
2899
2900 END IF; --p_ccid_map = 'Y'
2901
2902
2903
2904 IF p_calendar_convert = 'Y' THEN
2905
2906 UPDATE xla_ae_headers XAH
2907 SET period_name = (SELECT GLS.period_name
2908 FROM gl_period_statuses GLS
2909 WHERE GLS.ledger_id = p_sec_alc_ledger_id
2910 AND GLS.application_id = 101
2911 AND GLS.adjustment_period_flag = 'N'
2912 AND XAH.accounting_date BETWEEN GLS.start_date AND GLS.end_date
2913 )
2914 , XAH.upg_batch_id = NULL
2915 WHERE XAH.application_id = p_application_id
2916 AND XAH.ledger_id = p_sec_alc_ledger_id
2917 AND EXISTS (SELECT 1
2918 FROM xla_historic_mapping_gt XMAP
2919 WHERE XMAP.new_header_id = XAH.ae_header_id); -- sec/alc header id
2920
2921
2922 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2923
2924 trace
2925 (p_msg => 'Rows updated in xla_ae_headers with new period and null upgbatchid = ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS') -- to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2926 ,p_level => C_LEVEL_PROCEDURE
2927 ,p_module => l_log_module);
2928
2929 END IF;
2930
2931
2932 ELSE
2933 UPDATE xla_ae_headers XAH
2934 SET XAH.upg_batch_id = NULL
2935 WHERE XAH.application_id = p_application_id
2936 AND XAH.ledger_id = p_sec_alc_ledger_id
2937 AND XAH.upg_batch_id IS NOT NULL
2938 AND EXISTS (SELECT 1
2939 FROM xla_historic_mapping_gt GT
2940 WHERE GT.new_header_id = XAH.ae_header_id);
2941
2942 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2943 trace
2944 (p_msg => 'Rows updated in xla_ae_headers with null upgbatchid: ' || SQL%ROWCOUNT || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2945 ,p_level => C_LEVEL_PROCEDURE
2946 ,p_module => l_log_module);
2947 END IF;
2948
2949 END IF; --p_calendar_convert = 'Y'
2950
2951 ELSE
2952 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2953
2954 trace
2955 (p_msg => 'No Headers to insert, so insert to GT/XDL/XAL Skipped at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2956 ,p_level => C_LEVEL_PROCEDURE
2957 ,p_module => l_log_module);
2958
2959 END IF;
2960
2961 END IF; -- (l_rows_processed <> 0) condition
2962
2963
2964
2965 ad_parallel_updates_pkg.processed_rowid_range(l_rows_processed, --stamps XAH rowcount only
2966 l_end_rowid);
2967
2968 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2969 trace
2970 (p_msg => 'finished calling processed_rowid_range'
2971 ,p_level => C_LEVEL_PROCEDURE
2972 ,p_module => l_log_module);
2973 END IF;
2974
2975 COMMIT; -- GT table cleared for next run
2976
2977
2978 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2979 trace
2980 (p_msg => 'commit in subworker'
2981 ,p_level => C_LEVEL_PROCEDURE
2982 ,p_module => l_log_module);
2983 END IF;
2984
2985
2986 -- get new range of rowids
2987 ad_parallel_updates_pkg.get_rowid_range(l_start_rowid,
2988 l_end_rowid,
2989 l_any_rows_to_process,
2990 p_batch_size,
2991 FALSE);
2992
2993 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2994 trace
2995 (p_msg => 'finished calling get_rowid_range'
2996 ,p_level => C_LEVEL_PROCEDURE
2997 ,p_module => l_log_module);
2998 END IF;
2999
3000
3001 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3002 trace
3003 (p_msg => 'LOOP iteration completed ' || l_iterations
3004 ,p_level => C_LEVEL_PROCEDURE
3005 ,p_module => l_log_module);
3006 END IF;
3007
3008 END LOOP;
3009
3010 p_retcode := 0;
3011 p_errbuf := 'Subworker successful';
3012
3013
3014 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3015
3016 trace
3017 (p_msg => 'Child worker request completed successfully ' || l_child_request || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
3018 ,p_level => C_LEVEL_PROCEDURE
3019 ,p_module => l_log_module);
3020 END IF;
3021 fnd_file.put_line(fnd_file.log, 'Child worker request completed successfully ' || l_child_request || ' at ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
3022
3023
3024 EXCEPTION WHEN GL_INVALID_MAPPING_NAME THEN
3025 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3026 trace
3027 (p_msg => 'Child worker failed with GL_INVALID_MAPPING_NAME'
3028 ,p_level => C_LEVEL_PROCEDURE
3029 ,p_module => l_log_module);
3030 END IF;
3031 fnd_file.put_line(fnd_file.log, 'Exception: no mapping with the mapping name');
3032 p_retcode := 2;
3033 p_errbuf := 'Subworker failed(0)';
3034
3035
3036 WHEN GL_DISABLED_MAPPING THEN
3037 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3038 trace
3039 (p_msg => 'Child worker failed with GL_DISABLED_MAPPING'
3040 ,p_level => C_LEVEL_PROCEDURE
3041 ,p_module => l_log_module);
3042 END IF;
3043 fnd_file.put_line(fnd_file.log, 'Exception: mapping is disabled, current date is outside the active date range for the mapping');
3044 p_retcode := 2;
3045 p_errbuf := 'Subworker failed(0)';
3046
3047
3048 WHEN GL_INVALID_MAPPING_RULES THEN
3049 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3050 trace
3051 (p_msg => 'Child worker failed with GL_INVALID_MAPPING_RULES'
3052 ,p_level => C_LEVEL_PROCEDURE
3053 ,p_module => l_log_module);
3054 END IF;
3055 fnd_file.put_line(fnd_file.log, 'Exception: mapping rules are incorrectly defined');
3056 p_retcode := 2;
3057 p_errbuf := 'Subworker failed(0)';
3058
3059
3060 WHEN GL_MAP_UNEXPECTED_ERROR THEN
3061 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3062 trace
3063 (p_msg => 'Child worker failed with GL_MAP_UNEXPECTED_ERROR'
3064 ,p_level => C_LEVEL_PROCEDURE
3065 ,p_module => l_log_module);
3066 END IF;
3067 fnd_file.put_line(fnd_file.log, 'Exception: unexpected error');
3068 p_retcode := 2;
3069 p_errbuf := 'Subworker failed(0)';
3070
3071
3072 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
3073 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3074 trace
3075 (p_msg => 'Child worker failed with GL_BSV_MAP_NO_SOURCE_BAL_SEG'
3076 ,p_level => C_LEVEL_PROCEDURE
3077 ,p_module => l_log_module);
3078 END IF;
3079 fnd_file.put_line(fnd_file.log, 'Exception: source chart of accounts has no balancing segment');
3080 p_retcode := 2;
3081 p_errbuf := 'Subworker failed(0)';
3082
3083
3084 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
3085 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3086 trace
3087 (p_msg => 'Child worker failed with GL_BSV_MAP_NO_TARGET_BAL_SEG'
3088 ,p_level => C_LEVEL_PROCEDURE
3089 ,p_module => l_log_module);
3090 END IF;
3091 fnd_file.put_line(fnd_file.log, 'Exception: target chart of accounts has no balancing segment');
3092 p_retcode := 2;
3093 p_errbuf := 'Subworker failed(0)';
3094
3095
3096 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
3097 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3098 trace
3099 (p_msg => 'Child worker failed with GL_BSV_MAP_NO_SEGMENT_MAP'
3100 ,p_level => C_LEVEL_PROCEDURE
3101 ,p_module => l_log_module);
3102 END IF;
3103 fnd_file.put_line(fnd_file.log, 'Exception: no segment mapping for the balancing segment');
3104 p_retcode := 2;
3105 p_errbuf := 'Subworker failed(0)';
3106
3107 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
3108 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3109 trace
3110 (p_msg => 'Child worker failed with GL_BSV_MAP_NO_SINGLE_VALUE'
3111 ,p_level => C_LEVEL_PROCEDURE
3112 ,p_module => l_log_module);
3113 END IF;
3114 fnd_file.put_line(fnd_file.log, 'Exception: no single value to assign to the balancing segment');
3115 p_retcode := 2;
3116 p_errbuf := 'Subworker failed(0)';
3117
3118
3119 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
3120 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3121 trace
3122 (p_msg => 'Child worker failed with GL_BSV_MAP_NO_FROM_SEGMENT'
3123 ,p_level => C_LEVEL_PROCEDURE
3124 ,p_module => l_log_module);
3125 END IF;
3126 fnd_file.put_line(fnd_file.log, 'Exception: no derive-from segment');
3127 p_retcode := 2;
3128 p_errbuf := 'Subworker failed(0)';
3129
3130
3131 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
3132 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3133 trace
3134 (p_msg => 'Child worker failed with GL_BSV_MAP_NOT_BSV_DERIVED'
3135 ,p_level => C_LEVEL_PROCEDURE
3136 ,p_module => l_log_module);
3137 END IF;
3138 fnd_file.put_line(fnd_file.log, 'Exception: derive-from segment is not the balancing segment');
3139 p_retcode := 2;
3140 p_errbuf := 'Subworker failed(0)';
3141
3142 WHEN GL_BSV_MAP_SETUP_ERROR THEN
3143 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3144 trace
3145 (p_msg => 'Child worker failed with GL_BSV_MAP_SETUP_ERROR'
3146 ,p_level => C_LEVEL_PROCEDURE
3147 ,p_module => l_log_module);
3148 END IF;
3149 fnd_file.put_line(fnd_file.log, 'Exception: mapping setup information could not be obtained');
3150 p_retcode := 2;
3151 p_errbuf := 'Subworker failed(0)';
3152
3153
3154 WHEN GL_BSV_MAP_MAPPING_ERROR THEN
3155 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3156 trace
3157 (p_msg => 'Child worker failed with GL_BSV_MAP_MAPPING_ERROR'
3158 ,p_level => C_LEVEL_PROCEDURE
3159 ,p_module => l_log_module);
3160 END IF;
3161 fnd_file.put_line(fnd_file.log, 'Exception: mapping could not be performed');
3162 p_retcode := 2;
3163 p_errbuf := 'Subworker failed(0)';
3164
3165
3166 WHEN GL_BSV_MAP_UNEXPECTED_ERROR THEN
3167 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3168 trace
3169 (p_msg => 'Child worker failed with GL_BSV_MAP_UNEXPECTED_ERROR'
3170 ,p_level => C_LEVEL_PROCEDURE
3171 ,p_module => l_log_module);
3172 END IF;
3173 fnd_file.put_line(fnd_file.log, 'Exception: unexpected error in BSV mapping information');
3174 p_retcode := 2;
3175 p_errbuf := 'Subworker failed(0)';
3176
3177
3178
3179 WHEN g_sub_validation_failed THEN
3180 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3181 trace
3182 (p_msg => 'Child worker failed with g_sub_validation_failed'
3183 ,p_level => C_LEVEL_PROCEDURE
3184 ,p_module => l_log_module);
3185 END IF;
3186 fnd_file.put_line(fnd_file.log, 'XAH rowcount and mapping GT rowcount mismatch');
3187 p_retcode := 2;
3188 p_errbuf := 'Subworker failed(1)';
3189
3190
3191 WHEN OTHERS THEN
3192 g_error_text := SQLCODE || SQLERRM;
3193 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
3194 trace
3195 (p_msg => 'Child worker failed with ' || g_error_text
3196 ,p_level => C_LEVEL_PROCEDURE
3197 ,p_module => l_log_module);
3198 END IF;
3199 fnd_file.put_line(fnd_file.log, 'SQL exception child worker = ' || g_error_text);
3200 p_retcode := 2;
3201 p_errbuf := 'Subworker failed(2)';
3202
3203 END;
3204
3205
3206 BEGIN
3207 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3208 g_log_enabled := fnd_log.test
3209 (log_level => g_log_level
3210 ,MODULE => C_DEFAULT_MODULE);
3211
3212 IF NOT g_log_enabled THEN
3213 g_log_level := C_LEVEL_LOG_DISABLED;
3214 END IF;
3215
3216
3217 END xla_hist_ledger_upg_pkg;