[Home] [Help]
PACKAGE BODY: APPS.FA_UPGHARNESS_PKG
Source
1 PACKAGE BODY FA_UPGHARNESS_PKG as
2 /* $Header: FAHAUPGB.pls 120.16 2011/10/03 15:16:30 spooyath ship $ */
3
4 TYPE WorkerList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6 Procedure submit_request(p_program IN VARCHAR2,
7 p_description IN VARCHAR2,
8 p_workers_num IN NUMBER,
9 p_worker_id IN NUMBER,
10 p_batch_size IN NUMBER,
11 p_script_name IN VARCHAR2,
12 x_req_id OUT NOCOPY NUMBER);
13
14 Procedure verify_status(
15 p_worker IN WorkerList,
16 p_workers_num IN NUMBER,
17 x_child_success OUT NOCOPY VARCHAR2);
18
19 Procedure fa_master_upg (
20 errbuf OUT NOCOPY VARCHAR2,
21 retcode OUT NOCOPY VARCHAR2,
22 p_workers_num IN NUMBER,
23 p_batch_size IN NUMBER
24 ) IS
25
26 l_worker WorkerList;
27
28 l_worker_id number;
29 l_workers_num number;
30 l_batch_size number;
31
32 l_table_owner varchar2(30);
33 l_status varchar2(1);
34 l_industry varchar2(1);
35
36 l_child_success VARCHAR2(1);
37 l_errors NUMBER;
38
39 l_batch_id number(15);
40 l_evt_script_name varchar2(30) := 'faevt';
41 l_evd_script_name varchar2(30) := 'faevd';
42
43 l_already_running number;
44 l_existing_error number;
45
46 nothing_to_run EXCEPTION;
47 already_running EXCEPTION;
48 existing_error EXCEPTION;
49
50 begin
51
52 l_batch_size := nvl(p_batch_size, 1000);
53 l_workers_num := nvl(p_workers_num, 1);
54
55 if (l_workers_num > 99) then
56 errbuf := 'Too many workers';
57
58 retcode := 1;
59 return;
60 end if;
61
62 if (l_workers_num < 1) then
63 raise nothing_to_run;
64 end if;
65
66 -- Fix for Bug #8797839. Need to check if upgrade is already running
67 -- or is in error for some existing book.
68 select count(*)
69 into l_already_running
70 from fa_deprn_periods
71 where substr(xla_conversion_status, 1, 1) in
72 ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9');
73
74 if (l_already_running > 0) then
75 raise already_running;
76 end if;
77
78 select count(*)
79 into l_existing_error
80 from fa_deprn_periods
81 where substr(xla_conversion_status, 1, 1) = 'E';
82
83 if (l_already_running > 0) then
84 raise existing_error;
85 end if;
86
87 update fa_deprn_periods dp
88 set dp.xla_conversion_status = 'H'
89 where dp.xla_conversion_status is not null
90 and dp.xla_conversion_status not like 'U%'
91 and exists
92 (
93 select 'x'
94 from gl_period_statuses ps,
95 fa_book_controls bc
96 where ps.application_id = 101
97 and ps.migration_status_code in ('P', 'U')
98 and bc.set_of_books_id = ps.set_of_books_id
99 and dp.book_type_code = bc.book_type_code
100 and dp.period_name = ps.period_name
101 );
102
103 select xla_upg_batches_s.nextval
104 into l_batch_id
105 from dual;
106
107 -- Bug 13055635 : Use the same script for all the workers
108
109 l_evt_script_name := l_evt_script_name || to_char(l_batch_id);
110 l_evd_script_name := l_evd_script_name || to_char(l_batch_id);
111
112 for i in 1..l_workers_num loop
113
114 l_worker_id := i;
115
116 submit_request(p_program => 'FAXLAUPGCP',
117 p_description => 'FA XLA Upgrade On Demand',
118 p_workers_num => l_workers_num,
119 p_worker_id => l_worker_id,
120 p_batch_size => l_batch_size,
121 p_script_name => l_evt_script_name,
122 x_req_id => l_worker(i));
123
124 end loop;
125
126 verify_status(p_worker => l_worker,
127 p_workers_num => l_workers_num,
128 x_child_success => l_child_success);
129
130 -- If any subworkers have failed then raise an error
131 if (l_child_success = 'N') THEN
132 errbuf := 'Execution failed';
133
134 retcode := 1;
135 return;
136 end if;
137
138 fa_trx2_upg (
139 errbuf => errbuf,
140 retcode => retcode,
141 p_worker_id => 1,
142 p_workers_num => l_workers_num
143 );
144
145 l_worker.delete;
146
147 for i in 1..l_workers_num loop
148
149 l_worker_id := i;
150
151 submit_request(p_program => 'FAXLAUPGCP2',
152 p_description => 'FA XLA Upgrade On Demand',
153 p_workers_num => l_workers_num,
154 p_worker_id => l_worker_id,
155 p_batch_size => l_batch_size,
156 p_script_name => l_evd_script_name,
157 x_req_id => l_worker(i));
158
159 end loop;
160
161 verify_status(p_worker => l_worker,
162 p_workers_num => l_workers_num,
163 x_child_success => l_child_success);
164
165 -- If any subworkers have failed then raise an error
166 if (l_child_success = 'N') THEN
167 errbuf := 'Execution failed';
168
169 retcode := 1;
170 return;
171 end if;
172
173 fa_deprn2_upg (
174 errbuf => errbuf,
175 retcode => retcode,
176 p_worker_id => 1,
177 p_workers_num => l_workers_num
178 );
179
180 -- Check to see if all periods were upgraded successfully.
181 select count(*)
182 into l_errors
183 from fa_deprn_periods
184 where nvl(xla_conversion_status, 'UA') not in ('UT', 'UD', 'UA', 'H');
185
186 if (l_errors > 0) then
187
188 errbuf := 'Execution failed';
189 retcode := 1;
190
191 else
192
193 errbuf := 'Execution is successful';
194 retcode := 0;
195
196 end if;
197
198 exception
199 when nothing_to_run then
200
201 retcode := 0;
202 errbuf := 'Number of workers = ' || to_char(p_workers_num) ||
203 '. Nothing to run.';
204
205 when already_running then
206 retcode := 1;
207 errbuf := 'Upgrade is already running in another session. Exiting.';
208
209 raise;
210
211 when existing_error then
212 retcode := 1;
213 errbuf := 'Upgrade has an error from a previous run. Exiting.';
214
215 raise;
216
217
218 end fa_master_upg;
219
220 -- Bug 13055635 : New Arguement p_script_name
221
222 Procedure upgrade_by_request (
223 errbuf OUT NOCOPY VARCHAR2,
224 retcode OUT NOCOPY VARCHAR2,
225 p_workers_num IN NUMBER,
226 p_worker_id IN NUMBER,
227 p_batch_size IN NUMBER,
228 p_script_name IN VARCHAR2
229 ) IS
230
231 l_table_owner varchar2(30);
232 l_status varchar2(1);
233 l_industry varchar2(1);
234
235 l_batch_id number(15);
236 l_evt_script_name varchar2(30) := 'faevt';
237 l_evd_script_name varchar2(30) := 'faevd';
238
239 BEGIN
240
241 retcode := 0;
242 errbuf := 'Execution is successful';
243
244 if not (FND_INSTALLATION.get_app_info (
245 application_short_name => 'OFA',
246 status => l_status,
247 industry => l_industry,
248 oracle_schema => l_table_owner
249 )) then
250 retcode := 1;
251 errbuf := 'Unable to find schema name';
252 return;
253 end if;
254
255 /* select xla_upg_batches_s.nextval
256 into l_batch_id
257 from dual; */
258
259 fa_trx_upg (
260 errbuf => errbuf,
261 retcode => retcode,
262 p_script_name => p_script_name,
263 p_table_owner => l_table_owner,
264 p_worker_id => p_worker_id,
265 p_workers_num => nvl(p_workers_num, 1),
266 p_batch_size => nvl(p_batch_size, 1000)
267 );
268
269 end upgrade_by_request;
270
271 -- Bug 13055635 : New Arguement p_script_name
272
273 Procedure upgrade_by_request2 (
274 errbuf OUT NOCOPY VARCHAR2,
275 retcode OUT NOCOPY VARCHAR2,
276 p_workers_num IN NUMBER,
277 p_worker_id IN NUMBER,
278 p_batch_size IN NUMBER,
279 p_script_name IN VARCHAR2
280 ) IS
281
282 l_table_owner varchar2(30);
283 l_status varchar2(1);
284 l_industry varchar2(1);
285
286 l_batch_id number(15);
287 l_evt_script_name varchar2(30) := 'faevt';
288 l_evd_script_name varchar2(30) := 'faevd';
289
290 BEGIN
291
292 retcode := 0;
293 errbuf := 'Execution is successful';
294
295 if not (FND_INSTALLATION.get_app_info (
296 application_short_name => 'OFA',
297 status => l_status,
298 industry => l_industry,
299 oracle_schema => l_table_owner
300 )) then
301 retcode := 1;
302 errbuf := 'Unable to find schema name';
303 return;
304 end if;
305
306 /* select xla_upg_batches_s.nextval
307 into l_batch_id
308 from dual;*/
309
310 fa_deprn_upg (
311 errbuf => errbuf,
312 retcode => retcode,
313 p_script_name => p_script_name,
314 p_mode => 'uptime',
315 p_table_owner => l_table_owner,
316 p_worker_id => p_worker_id,
317 p_workers_num => nvl(p_workers_num, 1),
318 p_batch_size => nvl(p_batch_size, 1000)
319 );
320
321 errbuf := 'Execution is successful';
322 retcode := 0;
323
324 end upgrade_by_request2;
325
326 Procedure fa_trx_upg (
327 errbuf OUT NOCOPY VARCHAR2,
328 retcode OUT NOCOPY VARCHAR2,
329 p_script_name IN VARCHAR2,
330 p_table_owner IN VARCHAR2,
331 p_worker_id IN NUMBER,
332 p_workers_num IN NUMBER,
333 p_batch_size IN NUMBER
334 ) IS
335
336 -- for parallelization
337 l_batch_size number;
338 l_any_rows_to_process boolean;
339
340 l_table_name1 varchar2(30) := 'FA_TRX_REFERENCES';
341 l_table_name2 varchar2(30) := 'FA_TRANSACTION_HEADERS';
342
343 l_start_rowid rowid;
344 l_end_rowid rowid;
345 l_rows_processed number;
346
347 l_group_books number(15);
348
349 l_success_count number;
350 l_failure_count number;
351 l_return_status number;
352
353 cursor c_periods is
354 select bc.book_type_code, ps.period_name
355 from gl_period_statuses ps,
356 fa_book_controls bc
357 where ps.application_id = 101
358 and ps.migration_status_code in ('P', 'U')
359 and bc.set_of_books_id = ps.set_of_books_id;
360
361 type char_tbl_type is table of varchar2(150) index by binary_integer;
362
363 l_book_type_code_tbl char_tbl_type;
364 l_period_name_tbl char_tbl_type;
365
366 begin
367
368 l_batch_size := p_batch_size;
369
370 ad_parallel_updates_pkg.initialize_rowid_range(
371 ad_parallel_updates_pkg.ROWID_RANGE,
372 p_table_owner,
373 l_table_name1,
374 p_script_name,
375 p_worker_id,
376 p_workers_num,
377 l_batch_size, 0);
378
379 ad_parallel_updates_pkg.get_rowid_range(
380 l_start_rowid,
381 l_end_rowid,
382 l_any_rows_to_process,
383 l_batch_size,
384 TRUE);
385
386 WHILE (l_any_rows_to_process = TRUE) LOOP
387
388 FA_SLA_EVENTS_UPG_PKG.Upgrade_Inv_Events (
389 p_start_rowid => l_start_rowid,
390 p_end_rowid => l_end_rowid,
391 p_batch_size => l_batch_size,
392 x_success_count => l_success_count,
393 x_failure_count => l_failure_count,
394 x_return_status => l_return_status
395 );
396
397 l_rows_processed := l_batch_size;
398
399 ad_parallel_updates_pkg.processed_rowid_range(
400 l_rows_processed,
401 l_end_rowid);
402
403 commit;
404
405 --
406 -- get new range of rowids
407 --
408 ad_parallel_updates_pkg.get_rowid_range(
409 l_start_rowid,
410 l_end_rowid,
411 l_any_rows_to_process,
412 l_batch_size,
413 FALSE);
414
415 END LOOP;
416
417 COMMIT;
418
419 -----------------------------------------------------------------------
420
421 select count(*)
422 into l_group_books
423 from fa_book_controls
424 where allow_group_deprn_flag = 'Y';
425
426 ad_parallel_updates_pkg.initialize_rowid_range(
427 ad_parallel_updates_pkg.ROWID_RANGE,
428 p_table_owner,
429 l_table_name2,
430 p_script_name,
431 p_worker_id,
432 p_workers_num,
433 l_batch_size, 0);
434
435 ad_parallel_updates_pkg.get_rowid_range(
436 l_start_rowid,
437 l_end_rowid,
438 l_any_rows_to_process,
439 l_batch_size,
440 TRUE);
441
442 WHILE (l_any_rows_to_process = TRUE) LOOP
443
444 if (l_group_books > 0) then
445
446 FA_SLA_EVENTS_UPG_PKG.Upgrade_Group_Trxn_Events (
447 p_start_rowid => l_start_rowid,
448 p_end_rowid => l_end_rowid,
449 p_batch_size => l_batch_size,
450 x_success_count => l_success_count,
451 x_failure_count => l_failure_count,
452 x_return_status => l_return_status
453 );
454 end if;
455
456 FA_SLA_EVENTS_UPG_PKG.Upgrade_Trxn_Events (
457 p_start_rowid => l_start_rowid,
458 p_end_rowid => l_end_rowid,
459 p_batch_size => l_batch_size,
460 x_success_count => l_success_count,
461 x_failure_count => l_failure_count,
462 x_return_status => l_return_status
463 );
464
465 l_rows_processed := l_batch_size;
466
467 ad_parallel_updates_pkg.processed_rowid_range(
468 l_rows_processed,
469 l_end_rowid);
470
471
472 commit;
473
474 --
475 -- get new range of rowids
476 --
477 ad_parallel_updates_pkg.get_rowid_range(
478 l_start_rowid,
479 l_end_rowid,
480 l_any_rows_to_process,
481 l_batch_size,
482 FALSE);
483
484 END LOOP;
485
486 COMMIT;
487
488 open c_periods;
489 loop
490
491 fetch c_periods bulk collect
492 into l_book_type_code_tbl,
493 l_period_name_tbl
494 limit 100;
495
496 forall i in 1..l_book_type_code_tbl.count
497 update fa_deprn_periods dp
498 set dp.xla_conversion_status =
499 decode (substr(dp.xla_conversion_status, 1, 1),
500 'H', '1',
501 'E', '1',
502 'U', '1',
503 '0', to_char(to_number(dp.xla_conversion_status) + 1),
504 '1', to_char(to_number(dp.xla_conversion_status) + 1),
505 '2', to_char(to_number(dp.xla_conversion_status) + 1),
506 '3', to_char(to_number(dp.xla_conversion_status) + 1),
507 '4', to_char(to_number(dp.xla_conversion_status) + 1),
508 '5', to_char(to_number(dp.xla_conversion_status) + 1),
509 '6', to_char(to_number(dp.xla_conversion_status) + 1),
510 '7', to_char(to_number(dp.xla_conversion_status) + 1),
511 '8', to_char(to_number(dp.xla_conversion_status) + 1),
512 '9', to_char(to_number(dp.xla_conversion_status) + 1),
513 dp.xla_conversion_status)
514 where dp.book_type_code = l_book_type_code_tbl(i)
515 and dp.period_name = l_period_name_tbl(i)
516 and dp.xla_conversion_status is not null
517 and dp.xla_conversion_status not in ('UA', 'UT');
518
519 COMMIT;
520
521 exit when c_periods%notfound;
522 end loop;
523 close c_periods;
524
525 errbuf := 'Execution is successful';
526 retcode := 0;
527
528 end fa_trx_upg;
529
530 Procedure fa_trx2_upg (
531 errbuf OUT NOCOPY VARCHAR2,
532 retcode OUT NOCOPY VARCHAR2,
533 p_worker_id IN NUMBER,
534 p_workers_num IN NUMBER
535 ) IS
536
537 begin
538
539 -- We need to find the total number of workers, but we need this script
540 -- to only run once, so exit if it is not the first worker.
541 if (p_worker_id <> 1) then return; end if;
542
543 -- If all of the workers completed successfully, mark the period as
544 -- successful.
545 update fa_deprn_periods dp
546 set dp.xla_conversion_status = 'UT'
547 where dp.xla_conversion_status = to_char (p_workers_num)
548 and exists
549 (
550 select 'x'
551 from gl_period_statuses ps,
552 fa_book_controls bc
553 where ps.application_id = 101
554 and ps.migration_status_code in ('P', 'U')
555 and bc.set_of_books_id = ps.set_of_books_id
556 and dp.book_type_code = bc.book_type_code
557 and ps.period_name = dp.period_name
558 );
559
560 -- Mark as error any periods where the workers did not complete.
561 update fa_deprn_periods dp
562 set dp.xla_conversion_status = 'ET'
563 where dp.xla_conversion_status <> to_char (p_workers_num)
564 and dp.xla_conversion_status not in ('UA', 'UT')
565 and dp.xla_conversion_status is not null
566 and exists
567 (
568 select 'x'
569 from gl_period_statuses ps,
570 fa_book_controls bc
571 where ps.application_id = 101
572 and ps.migration_status_code in ('P', 'U')
573 and bc.set_of_books_id = ps.set_of_books_id
574 and dp.book_type_code = bc.book_type_code
575 and ps.period_name = dp.period_name
576 );
577
578 COMMIT;
579
580 errbuf := 'Execution is successful';
581 retcode := 0;
582
583 end fa_trx2_upg;
584
585 Procedure fa_deprn_upg (
586 errbuf OUT NOCOPY VARCHAR2,
587 retcode OUT NOCOPY VARCHAR2,
588 p_script_name IN VARCHAR2,
589 p_mode IN VARCHAR2,
590 p_table_owner IN VARCHAR2,
591 p_worker_id IN NUMBER,
592 p_workers_num IN NUMBER,
593 p_batch_size IN NUMBER
594 ) IS
595
596 l_batch_size varchar2(30);
597 l_any_rows_to_process boolean;
598
599 l_table_name1 varchar2(30) := 'FA_DEPRN_SUMMARY';
600 l_table_name2 varchar2(30) := 'FA_DEFERRED_DEPRN';
601
602 l_start_rowid rowid;
603 l_end_rowid rowid;
604 l_rows_processed number;
605
606 l_group_books number(15);
607 l_deprn_run number(15);
608
609 l_success_count number;
610 l_failure_count number;
611 l_return_status number;
612
613 cursor c_periods is
614 select bc.book_type_code, ps.period_name
615 from gl_period_statuses ps,
616 fa_book_controls bc
617 where ps.application_id = 101
618 and ps.migration_status_code in ('P', 'U')
619 and bc.set_of_books_id = ps.set_of_books_id;
620
621 type char_tbl_type is table of varchar2(150) index by binary_integer;
622
623 l_book_type_code_tbl char_tbl_type;
624 l_period_name_tbl char_tbl_type;
625
626 begin
627
628 l_batch_size := p_batch_size;
629
630 if (p_mode = 'downtime') then
631
632 select count(*)
633 into l_deprn_run
634 from fa_deprn_periods dp
635 where dp.period_close_date is null
636 and dp.deprn_run = 'Y'
637 and dp.xla_conversion_status is not null
638 and dp.xla_conversion_status not in ('UA', 'UD')
639 and exists
640 (
641 select 'x'
642 from gl_period_statuses ps,
643 fa_book_controls bc
644 where ps.application_id = 101
645 and ps.migration_status_code in ('P', 'U')
646 and bc.set_of_books_id = ps.set_of_books_id
647 and dp.book_type_code = bc.book_type_code
648 and dp.period_name = ps.period_name
649 );
650 end if;
651
652 if (p_mode <> 'downtime') or (l_deprn_run > 0) then
653
654 -- for deprn table
655 ad_parallel_updates_pkg.initialize_rowid_range(
656 ad_parallel_updates_pkg.ROWID_RANGE,
657 p_table_owner,
658 l_table_name1,
659 p_script_name,
660 p_worker_id,
661 p_workers_num,
662 l_batch_size, 0);
663
664 ad_parallel_updates_pkg.get_rowid_range(
665 l_start_rowid,
666 l_end_rowid,
667 l_any_rows_to_process,
668 l_batch_size,
669 TRUE);
670
671 WHILE (l_any_rows_to_process = TRUE) LOOP
672
673 FA_SLA_EVENTS_UPG_PKG.Upgrade_Deprn_Events (
674 p_mode => p_mode,
675 p_start_rowid => l_start_rowid,
676 p_end_rowid => l_end_rowid,
677 p_batch_size => l_batch_size,
678 x_success_count => l_success_count,
679 x_failure_count => l_failure_count,
680 x_return_status => l_return_status
681 );
682
683 l_rows_processed := l_batch_size;
684
685 ad_parallel_updates_pkg.processed_rowid_range(
686 l_rows_processed,
687 l_end_rowid);
688
689 commit;
690
691 --
692 -- get new range of rowids
693 --
694 ad_parallel_updates_pkg.get_rowid_range(
695 l_start_rowid,
696 l_end_rowid,
697 l_any_rows_to_process,
698 l_batch_size,
699 FALSE);
700
701 END LOOP;
702
703 COMMIT;
704
705 end if;
706
707 -----------------------------------------------------------------------
708
709 -- Only run the deferred deprn upgrade in uptime, and run it single
710 -- threaded
711 if (p_mode <> 'downtime') and (p_worker_id = 1) then
712
713 -- for deferred table
714 ad_parallel_updates_pkg.initialize_rowid_range(
715 ad_parallel_updates_pkg.ROWID_RANGE,
716 p_table_owner,
717 l_table_name2,
718 p_script_name,
719 1, -- p_worker_id
720 1, -- p_workers_num
721 l_batch_size, 0);
722
723 ad_parallel_updates_pkg.get_rowid_range(
724 l_start_rowid,
725 l_end_rowid,
726 l_any_rows_to_process,
727 l_batch_size,
728 TRUE);
729
730 WHILE (l_any_rows_to_process = TRUE) LOOP
731
732 FA_SLA_EVENTS_UPG_PKG.Upgrade_Deferred_Events (
733 p_start_rowid => l_start_rowid,
734 p_end_rowid => l_end_rowid,
735 p_batch_size => l_batch_size,
736 x_success_count => l_success_count,
737 x_failure_count => l_failure_count,
738 x_return_status => l_return_status
739 );
740
741 l_rows_processed := l_batch_size;
742
743 ad_parallel_updates_pkg.processed_rowid_range(
744 l_rows_processed,
745 l_end_rowid);
746
747 commit;
748
749 --
750 -- get new range of rowids
751 --
752 ad_parallel_updates_pkg.get_rowid_range(
753 l_start_rowid,
754 l_end_rowid,
755 l_any_rows_to_process,
756 l_batch_size,
757 FALSE);
758
759 END LOOP;
760
761 COMMIT;
762
763 end if;
764
765 if (p_mode = 'downtime') then
766
767 update fa_deprn_periods dp
768 set dp.xla_conversion_status =
769 decode (substr(dp.xla_conversion_status, 1, 1),
770 'U', '1',
771 'E', '1',
772 '0', to_char(to_number(dp.xla_conversion_status) + 1),
773 '1', to_char(to_number(dp.xla_conversion_status) + 1),
774 '2', to_char(to_number(dp.xla_conversion_status) + 1),
775 '3', to_char(to_number(dp.xla_conversion_status) + 1),
776 '4', to_char(to_number(dp.xla_conversion_status) + 1),
777 '5', to_char(to_number(dp.xla_conversion_status) + 1),
778 '6', to_char(to_number(dp.xla_conversion_status) + 1),
779 '7', to_char(to_number(dp.xla_conversion_status) + 1),
780 '8', to_char(to_number(dp.xla_conversion_status) + 1),
781 '9', to_char(to_number(dp.xla_conversion_status) + 1),
782 dp.xla_conversion_status)
783 where dp.xla_conversion_status is not null
784 and dp.xla_conversion_status not in ('UA', 'UD', 'H')
785 and dp.period_close_date is null;
786
787 COMMIT;
788
789 else
790
791 open c_periods;
792 loop
793
794 fetch c_periods bulk collect
795 into l_book_type_code_tbl,
796 l_period_name_tbl
797 limit 100;
798
799 forall i in 1..l_book_type_code_tbl.count
800 update fa_deprn_periods dp
801 set dp.xla_conversion_status =
802 decode (substr(dp.xla_conversion_status, 1, 1),
803 'U', '1',
804 'E', '1',
805 '0', to_char(to_number(dp.xla_conversion_status) + 1),
806 '1', to_char(to_number(dp.xla_conversion_status) + 1),
807 '2', to_char(to_number(dp.xla_conversion_status) + 1),
808 '3', to_char(to_number(dp.xla_conversion_status) + 1),
809 '4', to_char(to_number(dp.xla_conversion_status) + 1),
810 '5', to_char(to_number(dp.xla_conversion_status) + 1),
811 '6', to_char(to_number(dp.xla_conversion_status) + 1),
812 '7', to_char(to_number(dp.xla_conversion_status) + 1),
813 '8', to_char(to_number(dp.xla_conversion_status) + 1),
814 '9', to_char(to_number(dp.xla_conversion_status) + 1),
815 dp.xla_conversion_status)
816 where dp.book_type_code = l_book_type_code_tbl(i)
817 and dp.period_name = l_period_name_tbl(i)
818 and dp.xla_conversion_status is not null
819 and dp.xla_conversion_status not in ('UA', 'UD', 'H');
820
821 COMMIT;
822
823 exit when c_periods%notfound;
824 end loop;
825 close c_periods;
826
827 end if;
828
829 errbuf := 'Execution is successful';
830 retcode := 0;
831
832 end fa_deprn_upg;
833
834 Procedure fa_deprn2_upg (
835 errbuf OUT NOCOPY VARCHAR2,
836 retcode OUT NOCOPY VARCHAR2,
837 p_worker_id IN NUMBER,
838 p_workers_num IN NUMBER
839 ) IS
840 PROCEDURE check_period_status ( p_ledger_id IN NUMBER
841 , p_period_name IN VARCHAR2
842 , o_status_is_u OUT NOCOPY BOOLEAN
843 , o_status_is_p OUT NOCOPY BOOLEAN
844 , o_status_is_n OUT NOCOPY BOOLEAN
845 ) IS
846 l_u_count number; -- processed
847 l_h_count number; -- still in status 'H', pending upgrade
848 l_e_count number; -- error status
849 l_n_count number; -- null status
850
851 cursor c_check_conv (cp_period_name in varchar2, cp_ledger_id in number) is
852 select distinct nvl(xla_conversion_status,'N') xla_conversion_status
853 from fa_deprn_periods
854 where period_name = cp_period_name
855 and book_type_code in ( select book_type_code
856 from fa_book_controls
857 where set_of_books_id = cp_ledger_id )
858 order by xla_conversion_status
859 ;
860 BEGIN
861 o_status_is_u := FALSE;
862 o_status_is_p := FALSE;
863 o_status_is_n := FALSE;
864
865 l_u_count := 0; /* processed */
866 l_h_count := 0; /* pending for xla upgrade */
867 l_e_count := 0; /* E* statuses are errors */
868 l_n_count := 0; /* null is 'N' */
869
870 for l_conv in c_check_conv (p_period_name,p_ledger_id ) loop
871 if l_conv.xla_conversion_status in ( 'UA','UD','UT') then
872 l_u_count := l_u_count + 1;
873 elsif l_conv.xla_conversion_status = 'H' then
874 l_h_count := l_h_count + 1;
875 elsif l_conv.xla_conversion_status = 'N' then
876 l_n_count := l_n_count + 1;
877 else
878 l_e_count := l_e_count + 1;
879 end if;
880 end loop;
881
882
883 if l_h_count = 0 and l_e_count = 0 AND l_n_count = 0 then
884 o_status_is_u := TRUE;
885 o_status_is_p := FALSE;
886 o_status_is_n := FALSE;
887 elsif l_h_count <> 0 and l_e_count = 0 AND l_n_count = 0 THEN /* not too sure, if this is P */
888 o_status_is_u := FALSE;
889 o_status_is_p := FALSE;
890 o_status_is_n := TRUE;
891 elsif l_h_count = 0 AND l_e_count = 0 AND l_n_count <> 0 THEN
892 o_status_is_u := FALSE;
893 o_status_is_p := FALSE;
894 o_status_is_n := TRUE;
895 ELSIF l_h_count <> 0 AND l_e_count = 0 AND l_n_count <> 0 THEN
896 o_status_is_u := FALSE;
897 o_status_is_p := FALSE;
898 o_status_is_n := TRUE;
899 ELSIF l_e_count <> 0 THEN
900 o_status_is_u := FALSE;
901 o_status_is_p := TRUE;
902 o_status_is_n := FALSE;
903 end if;
904 EXCEPTION WHEN OTHERS THEN raise;
905 END ;
906
907 procedure sync_gl_period_statuses is
908 begin
909 -- Fix for Bug #5596250. Need to update migration_status_code to null
910 -- for any periods that do not exist.
911 -- we can run this step any number of times without impacting upgrade
912 -- for FA.
913 --
914 -- ledgers in gl not used by FA or periods not used by FA
915 -- need to be set to 'U' from 'P'
916 UPDATE gl_period_statuses ps
917 SET ps.migration_status_code = 'U'
918 WHERE ps.migration_status_code = 'P'
919 AND ps.application_id = 101
920 AND (ps.ledger_id, ps.period_name) not in
921 (
922 SELECT DISTINCT fbc.set_of_books_id, fdp.period_name
923 FROM fa_deprn_periods fdp, fa_book_controls fbc
924 WHERE fdp.book_type_code = fbc.book_type_code
925 );
926
927 COMMIT;
928
929 -- this routine ensures we have a clear idea of the status of
930 -- gl ledger period that is associated with multiple books and takes
931 -- into account the xla conversion status across all the fa books and periods.
932 -- mainly, this code will aid debugging if customer reports issues via OWC.
933
934 DECLARE
935 l_status_is_u boolean;
936 l_status_is_p boolean;
937 l_status_is_n boolean;
938
939 CURSOR c_periods IS
940 SELECT period_name, ledger_id
941 FROM gl_period_statuses
942 WHERE migration_status_code = 'P'
943 AND application_id = 101
944 ORDER BY ledger_id, period_name
945 FOR UPDATE OF migration_status_code;
946
947 BEGIN
948
949 FOR l_per IN c_periods LOOP
950 l_status_is_u := false;
951 l_status_is_p := true;
952 l_status_is_n := false;
953
954 check_period_status ( p_ledger_id => l_per.ledger_id
955 , p_period_name => l_per.period_name
956 , o_status_is_u => l_status_is_u
957 , o_status_is_p => l_status_is_p
958 , o_status_is_n => l_status_is_n
959 );
960 IF (NOT l_status_is_u) AND (l_status_is_p) AND (NOT l_status_is_n) THEN
961
962 UPDATE gl_period_statuses
963 SET migration_status_code = 'P'
964 WHERE CURRENT OF c_periods;
965
966 ELSIF (l_status_is_u) AND (NOT l_status_is_p) AND (NOT l_status_is_n) THEN
967
968 UPDATE gl_period_statuses
969 SET migration_status_code = 'U'
970 WHERE CURRENT OF c_periods;
971
972 ELSIF (NOT l_status_is_u) AND (NOT l_status_is_p) AND ( l_status_is_n) THEN
973
974 UPDATE gl_period_statuses
975 SET migration_status_code = null /* this will be reset to P when hot patch is re-run */
976 WHERE CURRENT OF c_periods;
977
978 END IF;
979 END LOOP;
980 EXCEPTION WHEN OTHERS THEN NULL;
981 END;
982 COMMIT;
983 EXCEPTION WHEN OTHERS THEN
984 raise;
985 end;
986
987 begin
988
989 -- We sync gl period status info before and after the
990 -- main logic to ensure that we are on safe side....
991 -- We have hit bugs with XLA HOT Patch...
992
993 sync_gl_period_statuses;
994 -- We need to find the total number of workers, but we need this script
995 -- to only run once, so exit if it is not the first worker.
996
997 if (p_worker_id <> 1) then return; end if;
998
999 -- If all of the workers completed successfully, mark the period as
1000 -- successful.
1001 update fa_deprn_periods dp
1002 set dp.xla_conversion_status = 'UA'
1003 where dp.xla_conversion_status = to_char (p_workers_num)
1004 and exists
1005 (
1006 select 'x'
1007 from gl_period_statuses ps,
1008 fa_book_controls bc
1009 where ps.application_id = 101
1010 and ps.migration_status_code in ('P', 'U')
1011 and bc.set_of_books_id = ps.set_of_books_id
1012 and dp.book_type_code = bc.book_type_code
1013 and ps.period_name = dp.period_name
1014 );
1015
1016 COMMIT;
1017
1018 -- Mark as error any periods where the workers did not complete.
1019 update fa_deprn_periods dp
1020 set dp.xla_conversion_status = 'ED'
1021 where dp.xla_conversion_status <> to_char (p_workers_num)
1022 and dp.xla_conversion_status not in ('UA', 'UT', 'UD')
1023 and dp.xla_conversion_status is not null
1024 and exists
1025 (
1026 select 'x'
1027 from gl_period_statuses ps,
1028 fa_book_controls bc
1029 where ps.application_id = 101
1030 and ps.migration_status_code in ('P', 'U')
1031 and bc.set_of_books_id = ps.set_of_books_id
1032 and dp.book_type_code = bc.book_type_code
1033 and ps.period_name = dp.period_name
1034 );
1035
1036 COMMIT;
1037
1038
1039 sync_gl_period_statuses; /* we need this again to sync deprn periods with gl period statuses */
1040
1041 errbuf := 'Execution is successful';
1042 retcode := 0;
1043 EXCEPTION WHEN OTHERS THEN
1044 errbuf := SQLERRM;
1045 retcode := -1;
1046 /* force the routine to fail in the XLA hot patch when there are issues */
1047 end fa_deprn2_upg;
1048
1049 -- Bug 13055635 : New Arguement p_script_name
1050 Procedure submit_request(p_program IN VARCHAR2,
1051 p_description IN VARCHAR2,
1052 p_workers_num IN NUMBER,
1053 p_worker_id IN NUMBER,
1054 p_batch_size IN NUMBER,
1055 p_script_name IN VARCHAR2,
1056 x_req_id OUT NOCOPY NUMBER)
1057 is
1058
1059 begin
1060
1061 x_req_id:= FND_REQUEST.SUBMIT_REQUEST
1062 (application => 'OFA',
1063 program => p_program,
1064 description => p_description,
1065 start_time => to_char(sysdate,'DD-MON-YY HH:MI:SS'),
1066 sub_request => FALSE,
1067 argument1 => p_workers_num,
1068 argument2 => p_worker_id,
1069 argument3 => p_batch_size,
1070 argument4 => p_script_name);
1071
1072
1073 end submit_request;
1074
1075 Procedure verify_status(
1076 p_worker IN WorkerList,
1077 p_workers_num IN NUMBER,
1078 x_child_success OUT NOCOPY VARCHAR2
1079 ) IS
1080
1081 l_result BOOLEAN;
1082 l_phase VARCHAR2(500) := NULL;
1083 l_req_status VARCHAR2(500) := NULL;
1084 l_devphase VARCHAR2(500) := NULL;
1085 l_devstatus VARCHAR2(500) := NULL;
1086 l_message VARCHAR2(500) := NULL;
1087 l_child_notcomplete BOOLEAN := TRUE;
1088
1089 l_req_id NUMBER;
1090
1091 begin
1092
1093 x_child_success := 'Y';
1094
1095 while l_child_notcomplete loop
1096
1097 dbms_lock.sleep(10);
1098 l_child_notcomplete := FALSE;
1099 commit;
1100
1101 for i in 1..p_workers_num loop
1102
1103 l_req_id := p_worker(i);
1104
1105 if (FND_CONCURRENT.GET_REQUEST_STATUS
1106 (l_req_id,
1107 NULL,
1108 NULL,
1109 l_phase,
1110 l_req_status,
1111 l_devphase,
1112 l_devstatus,
1113 l_message)) THEN
1114 null;
1115 end if;
1116
1117 commit;
1118
1119 if (l_devphase <> 'COMPLETE') then
1120 l_child_notcomplete := TRUE;
1121 end if;
1122
1123 if (l_devstatus = 'ERROR') THEN
1124 x_child_success := 'N';
1125 end if;
1126
1127 end loop;
1128 end loop;
1129
1130 end verify_status;
1131
1132 END FA_UPGHARNESS_PKG;