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