[Home] [Help]
PACKAGE BODY: APPS.HXC_UPGRADE_PKG
Source
1 PACKAGE BODY HXC_UPGRADE_PKG
2 /* $Header: hxcupgpkg.pkb 120.14.12020000.2 2012/12/05 10:40:48 asrajago ship $ */
3 AS
4
5 -- Bug 14281943
6 -- Exception type for all BULK Insert/Updates
7 TABLE_EXCEPTION EXCEPTION;
8 PRAGMA EXCEPTION_INIT(TABLE_EXCEPTION,-24381);
9 INVALID_DETAIL EXCEPTION;
10 PRAGMA EXCEPTION_INIT(INVALID_DETAIL,-6502);
11
12
13 PROCEDURE upgrade( errbuff OUT NOCOPY VARCHAR2,
14 retcode OUT NOCOPY NUMBER,
15 p_type IN VARCHAR2,
16 p_stop_after IN NUMBER DEFAULT -999,
17 p_num_workers IN NUMBER DEFAULT -1)
18 IS
19
20 l_request_id NUMBER;
21
22 CURSOR get_status (c_upg_type varchar2)
23 IS SELECT status
24 FROM hxc_upgrade_definitions
25 WHERE upg_type = c_upg_type;
26
27 CURSOR get_id
28 IS SELECT /*+ FIRST_ROWS */
29 time_building_block_id
30 FROM hxc_latest_details
31 WHERE org_id IS NULL
32 ORDER BY 1;
33
34 -- Start
35
36 CURSOR get_dep_txn_id
37 IS SELECT transaction_id
38 FROM hxc_transactions
39 WHERE type = 'DEPOSIT'
40 ORDER BY 1 asc;
41 -- End
42
43
44 -- Bug 9394446
45 -- Added application Set condition
46
47 -- Cursor to pick up Payroll's details which are not there in hxc_pay_latest_details.
48 CURSOR get_pay_detail_ids(p_retr_id IN NUMBER)
49 IS SELECT hld.time_building_block_id
50 FROM hxc_latest_details hld
51 WHERE NOT EXISTS ( SELECT 1
52 FROM hxc_transaction_details htd,
53 hxc_transactions ht
54 WHERE hld.time_building_block_id = htd.time_building_block_id
55 AND hld.object_version_number = htd.time_building_block_ovn
56 AND htd.status = 'SUCCESS'
57 AND htd.transaction_id = ht.transaction_id
58 AND ht.type = 'RETRIEVAL'
59 AND ht.status = 'SUCCESS'
60 AND ht.transaction_process_id IN (p_retr_id, -1))
61 AND NOT EXISTS ( SELECT 1
62 FROM hxc_pay_latest_details hpl
63 WHERE hpl.time_building_block_id = hld.time_building_block_id
64 AND hpl.object_version_number = hld.object_version_number)
65 AND hld.application_set_id IN ( SELECT application_set_id
66 FROM hxc_application_set_comps_v
67 -- Bug 12609169
68 -- Added HR here
69 WHERE time_recipient_name IN ( 'Payroll','Human Resources'))
70 ORDER BY hld.time_building_block_id ;
71
72 -- Bug 9394446
73 -- Added application Set condition
74 CURSOR get_pa_detail_ids(p_retr_id IN NUMBER)
75 IS SELECT hld.time_building_block_id
76 FROM hxc_latest_details hld
77 WHERE NOT EXISTS ( SELECT 1
78 FROM hxc_transaction_details htd,
79 hxc_transactions ht
80 WHERE hld.time_building_block_id = htd.time_building_block_id
81 AND hld.object_version_number = htd.time_building_block_ovn
82 AND htd.status = 'SUCCESS'
83 AND htd.transaction_id = ht.transaction_id
84 AND ht.type = 'RETRIEVAL'
85 AND ht.status = 'SUCCESS'
86 AND ht.transaction_process_id = p_retr_id)
87 AND NOT EXISTS ( SELECT 1
91 AND hld.application_set_id IN ( SELECT application_set_id
88 FROM hxc_pa_latest_details hpl
89 WHERE hpl.time_building_block_id = hld.time_building_block_id
90 AND hpl.object_version_number = hld.object_version_number)
92 FROM hxc_application_set_comps_v
93 WHERE time_recipient_name = 'Projects')
94 ORDER BY hld.time_building_block_id ;
95
96
97 -- Bug 9790410
98 -- Added to pick up the summary id from HXT table.
99 CURSOR get_tim_id
100 IS SELECT DISTINCT sum.id
101 FROM hxc_time_building_blocks_ar ar,
102 hxt_sum_hours_worked_f sum
103 WHERE ar.time_building_block_id = sum.time_building_block_id
104 AND ar.object_version_number = sum.time_building_block_ovn
105 AND ar.scope = 'DETAIL';
106
107
108 -- Bug 9938394
109 -- Cursor for XFER_TO upgrade
110
111 CURSOR get_timecard_summaries
112 IS SELECT timecard_id,
113 timecard_ovn
114 FROM hxc_timecard_summary
115 WHERE transferred_to IS NULL
116 ORDER BY timecard_id;
117
118
119 l_min_id NUMBER;
120 l_max_id NUMBER;
121 l_batch_size NUMBER;
122
123 TYPE NUMBERTABLE IS TABLE OF NUMBER;
124 l_id_tab NUMBERTABLE;
125 l_ovn_tab NUMBERTABLE;
126
127 TYPE REQREC IS RECORD
128 ( REQUEST_ID NUMBER,
129 start_id NUMBER,
130 end_id NUMBER);
131
132 TYPE REQTAB IS TABLE OF REQREC INDEX BY BINARY_INTEGER;
133 l_reqtab REQTAB;
134 l_ind BINARY_INTEGER := 0;
135 l_count NUMBER := 0;
136 l_req_complete BOOLEAN := FALSE;
137
138 l_call_status BOOLEAN ;
139 l_interval NUMBER := 30;
140 l_phase VARCHAR2(30);
141 l_status VARCHAR2(30);
142 l_dev_phase VARCHAR2(30);
143 l_dev_status VARCHAR2(30);
144 l_message VARCHAR2(30);
145
146 l_req BOOLEAN := FALSE;
147 l_exists NUMBER := 0;
148
149 l_chunk_size NUMBER ;
150 l_work_limit BOOLEAN := FALSE;
151 l_start_time DATE;
152 l_stop_after NUMBER;
153
154 l_process_id NUMBER;
155
156 BEGIN
157 hr_general.g_data_migrator_mode := 'Y';
158 l_start_time := SYSDATE;
159
160 DELETE FROM HXC_UPGRADE_STATUS;
161
162 hr_utility.trace('p_type = '||p_type);
163
164 -- Added if -endif
165
166 OPEN get_status(p_type);
167 FETCH get_status INTO l_status;
168
169 IF get_status%NOTFOUND
170 THEN
171 insert_into_upg_defn(p_type);
172 l_status := 'INCOMPLETE';
173 END IF;
174
175 CLOSE get_status;
176
177 IF l_status = 'COMPLETE'
178 THEN
179 put_log('++++++++++++++++++++++++++++++++++++++++++++');
180 put_log(upgrade_name(p_type)||' is already completed ');
181 put_log('++++++++++++++++++++++++++++++++++++++++++++');
182 RETURN;
183 END IF;
184
185 COMMIT;
186
187 put_log(' ');
188 IF p_num_workers <> -1
189 THEN
190 put_log('----------------------------------------------------------------');
191 put_log(' This process would run with '||p_num_workers||' Worker Programs');
192 put_log('----------------------------------------------------------------');
193 END IF;
194 put_log(' ');
195 IF p_stop_after <> -999
196 THEN
197 put_log('----------------------------------------------------------------');
198 put_log('This Program would stop processing after '||p_stop_after||' Hours');
199 put_log('----------------------------------------------------------------');
200 END IF;
201 put_log(' ');
202
203 l_chunk_size := NVL(FND_PROFILE.VALUE('HXC_UPGRADE_WORKER_SIZE'),100000);
204 put_log('Data Volume size for Worker :'||l_chunk_size);
205
206
207 DELETE FROM hxc_upg_details_temp;
208 DELETE FROM hxc_upg_master_temp;
209 COMMIT;
210
211 IF (p_type = 'LATEST_DETAILS') THEN
212 OPEN get_id;
213 LOOP
214 FETCH get_id
215 BULK
216 COLLECT INTO l_id_tab
217 LIMIT 1000;
218
219 EXIT WHEN l_id_tab.COUNT = 0;
220
221 IF l_count =0
222 THEN
223 l_ind := l_ind + 1;
224 l_reqtab(l_ind).start_id := l_id_tab(l_id_tab.FIRST);
225 END IF;
226
227 IF l_id_tab.COUNT < 1000
228 THEN
229 l_req := TRUE;
230 ELSE
231 l_req := FALSE;
232 END IF;
233
234 l_count := l_count + l_id_tab.COUNT;
235 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
236 IF l_count >= l_chunk_size
237 THEN
238 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
239
240 IF p_stop_after > 0
241 THEN
242 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
243 ELSIF p_stop_after IS NULL
244 THEN
245 l_stop_after := NULL;
246 ELSE
247 l_stop_after := 0;
248 END IF;
249
250 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
251 ,program => 'HXCUPGWK'
252 ,description => NULL
253 ,sub_request => FALSE
254 ,argument1 => p_type
255 ,argument4 => l_stop_after
256 ,argument2 => l_reqtab(l_ind).start_id ,
257 argument3 => l_reqtab(l_ind).end_id);
258 COMMIT;
259 INSERT INTO hxc_upgrade_status
260 (parent_id,
261 child_id,
262 child_status)
263 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
264 l_reqtab(l_ind).request_id,
265 'INCOMPLETE');
266
267 COMMIT;
268 IF p_num_workers > 0
269 AND l_reqtab.COUNT >= p_num_workers
270 THEN
271 l_work_limit := TRUE;
272 EXIT;
273 END IF;
274 l_count := 0;
275 END IF;
276 END LOOP;
277 END IF;
278
279
280 IF (p_type = 'RETRIEVAL_PAY')
281 THEN
282
283 IF NOT ret_upgrade_completed
284 THEN
285 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
286 put_log(upgrade_name('LATEST_DETAILS')||' is not completed ');
287 put_log(upgrade_name(p_type)||' can be run only after this is complete. ');
288 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
289 retcode := 2;
290 RETURN;
291 END IF;
292
293 l_process_id := get_ret_process_id('BEE Retrieval Process');
294
295 OPEN get_pay_detail_ids(l_process_id);
296 LOOP
297 FETCH get_pay_detail_ids
298 BULK
299 COLLECT INTO l_id_tab
300 LIMIT 1000;
301
302 EXIT WHEN l_id_tab.COUNT = 0;
303
304 IF l_count =0
305 THEN
306 l_ind := l_ind + 1;
307 l_reqtab(l_ind).start_id := l_id_tab(l_id_tab.FIRST);
308 END IF;
309
310 IF l_id_tab.COUNT < 1000
311 THEN
312 l_req := TRUE;
313 ELSE
314 l_req := FALSE;
315 END IF;
316
317 l_count := l_count + l_id_tab.COUNT;
318 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
319 IF l_count >= l_chunk_size
320 THEN
321 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
322
323 IF p_stop_after > 0
324 THEN
325 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
326 ELSIF p_stop_after IS NULL
327 THEN
328 l_stop_after := NULL;
329 ELSE
330 l_stop_after := 0;
331 END IF;
332
333 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
334 ,program => 'HXCUPGWK'
335 ,description => NULL
336 ,sub_request => FALSE
337 ,argument1 => p_type
338 ,argument4 => l_stop_after
339 ,argument2 => l_reqtab(l_ind).start_id ,
340 argument3 => l_reqtab(l_ind).end_id);
341 COMMIT;
342 INSERT INTO hxc_upgrade_status
343 (parent_id,
344 child_id,
345 child_status)
346 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
347 l_reqtab(l_ind).request_id,
348 'INCOMPLETE');
349
350 COMMIT;
351
352 IF p_num_workers > 0
353 AND l_reqtab.COUNT >= p_num_workers
354 THEN
355 l_work_limit := TRUE;
356 EXIT;
357 END IF;
358 l_count := 0;
359 END IF;
360 END LOOP;
361 CLOSE get_pay_detail_ids;
362 END IF;
363
364
365 IF (p_type = 'RETRIEVAL_PA')
366 THEN
367
368 IF NOT ret_upgrade_completed
369 THEN
373 END IF;
370 put_log('Generic Upgrade not completed and this upgrade cannot be done now ');
371 retcode := 2;
372 RETURN;
374
375
376 l_process_id := get_ret_process_id('Projects Retrieval Process');
377
378 OPEN get_pa_detail_ids(l_process_id);
379 LOOP
380 FETCH get_pa_detail_ids
381 BULK
382 COLLECT INTO l_id_tab
383 LIMIT 1000;
384
385 EXIT WHEN l_id_tab.COUNT = 0;
386
387 IF l_count =0
388 THEN
389 l_ind := l_ind + 1;
390 l_reqtab(l_ind).start_id := l_id_tab(l_id_tab.FIRST);
391 END IF;
392
393 IF l_id_tab.COUNT < 1000
394 THEN
395 l_req := TRUE;
396 ELSE
397 l_req := FALSE;
398 END IF;
399
400 l_count := l_count + l_id_tab.COUNT;
401 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
402 IF l_count >= l_chunk_size
403 THEN
404 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
405
406 IF p_stop_after > 0
407 THEN
408 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
409 ELSIF p_stop_after IS NULL
410 THEN
411 l_stop_after := NULL;
412 ELSE
413 l_stop_after := 0;
414 END IF;
415
416 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
417 ,program => 'HXCUPGWK'
418 ,description => NULL
419 ,sub_request => FALSE
420 ,argument1 => p_type
421 ,argument4 => l_stop_after
422 ,argument2 => l_reqtab(l_ind).start_id ,
423 argument3 => l_reqtab(l_ind).end_id);
424 COMMIT;
425 INSERT INTO hxc_upgrade_status
426 (parent_id,
427 child_id,
428 child_status)
429 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
430 l_reqtab(l_ind).request_id,
431 'INCOMPLETE');
432
433 COMMIT;
434
435 IF p_num_workers > 0
436 AND l_reqtab.COUNT >= p_num_workers
437 THEN
438 l_work_limit := TRUE;
439 EXIT;
440 END IF;
441 l_count := 0;
442 END IF;
443 END LOOP;
444 CLOSE get_pa_detail_ids;
445 END IF;
446
447
448 -- Bug 9790410
449 -- Added this for the OTLR Archive upgrade.
450
451 IF (p_type = 'HXT_ARCHIVE')
452 THEN
453 OPEN get_tim_id;
454 LOOP
455 FETCH get_tim_id BULK
456 COLLECT
457 INTO l_id_tab LIMIT 100;
458
459 EXIT WHEN l_id_tab.COUNT = 0;
460
461 IF l_count =0
462 THEN
463 l_ind := l_ind + 1;
464 l_reqtab(l_ind).start_id := l_id_tab(l_id_tab.FIRST);
465 END IF;
466
467 IF l_id_tab.COUNT < 1000
468 THEN
469 l_req := TRUE;
470 ELSE
471 l_req := FALSE;
472 END IF;
473
474 l_count := l_count + l_id_tab.COUNT;
475 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
476 IF l_count >= l_chunk_size
477 THEN
478 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
479
480 IF p_stop_after > 0
481 THEN
482 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
483 ELSIF p_stop_after IS NULL
484 THEN
485 l_stop_after := NULL;
486 ELSE
487 l_stop_after := 0;
488 END IF;
489
490 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
491 ,program => 'HXCUPGWK'
492 ,description => NULL
493 ,sub_request => FALSE
494 ,argument1 => p_type
495 ,argument4 => l_stop_after
496 ,argument2 => l_reqtab(l_ind).start_id ,
497 argument3 => l_reqtab(l_ind).end_id);
498 COMMIT;
499
500
501 INSERT INTO hxc_upgrade_status
502 (parent_id,
503 child_id,
504 child_status)
505 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
506 l_reqtab(l_ind).request_id,
507 'INCOMPLETE');
508
509 COMMIT;
510
511 IF p_num_workers > 0
512 AND l_reqtab.COUNT >= p_num_workers
513 THEN
514 l_work_limit := TRUE;
515 EXIT;
516 END IF;
517 l_count := 0;
518
519 END IF;
520
521 END LOOP;
522 CLOSE get_tim_id;
523 END IF;
524
525
526 -- Bug 9938394
527 -- Added code for the Timecard Summary.Transferred_to column upgrade.
528
529 IF (p_type = 'XFER_TO')
530 THEN
531 OPEN get_timecard_summaries;
532 LOOP
533 FETCH get_timecard_summaries
534 BULK
535 COLLECT INTO l_id_tab,
536 l_ovn_tab
537 LIMIT 1000;
538
539 EXIT WHEN l_id_tab.COUNT = 0;
540
541 IF l_count =0
542 THEN
543 l_ind := l_ind + 1;
544 l_reqtab(l_ind).start_id := l_ind;
545 END IF;
546
547
548
549 IF l_id_tab.COUNT < 1000
550 THEN
551 l_req := TRUE;
552 ELSE
553 l_req := FALSE;
554 END IF;
555
556 l_count := l_count + l_id_tab.COUNT;
557 l_reqtab(l_ind).end_id := 0;
558 FORALL i IN l_id_tab.FIRST..l_id_tab.LAST
559 INSERT INTO hxc_upg_master_temp
560 (master_id,
561 master_ovn,
562 thread_id)
563 VALUES
564 (l_id_tab(i),
565 l_ovn_tab(i),
566 l_ind);
567
568
569
570 COMMIT;
571
572 IF l_count >= l_chunk_size
573 THEN
574 l_reqtab(l_ind).end_id :=0;
575
576 IF p_stop_after > 0
577 THEN
578 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
579 ELSIF p_stop_after IS NULL
580 THEN
581 l_stop_after := NULL;
582 ELSE
583 l_stop_after := 0;
584 END IF;
585
586 put_log('Thread number '||l_ind);
587
588 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
589 ,program => 'HXCUPGWK'
590 ,description => NULL
591 ,sub_request => FALSE
592 ,argument1 => p_type
593 ,argument4 => l_stop_after
594 ,argument2 => l_reqtab(l_ind).start_id ,
595 argument3 => l_reqtab(l_ind).end_id);
596
597
598 COMMIT;
599
600 INSERT INTO hxc_upgrade_status
601 (parent_id,
602 child_id,
603 child_status)
604 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
605 l_reqtab(l_ind).request_id,
606 'INCOMPLETE');
607
608
609 IF p_num_workers > 0
610 AND l_reqtab.COUNT >= p_num_workers
611 THEN
612 l_work_limit := TRUE;
613 EXIT;
614 END IF;
615 l_count := 0;
616 END IF;
617 END LOOP;
618 CLOSE get_timecard_summaries;
619
620 END IF;
621
622
623
624 -- Start
625
626 IF (p_type = 'DEPOSIT_TRANSACTIONS') THEN
627 OPEN get_dep_txn_id;
628 LOOP
629 FETCH get_dep_txn_id
630 BULK
631 COLLECT INTO l_id_tab
632 LIMIT 1000;
633
634 EXIT WHEN l_id_tab.COUNT = 0;
635
636 IF l_count =0
637 THEN
638 l_ind := l_ind + 1;
639 l_reqtab(l_ind).start_id := l_id_tab(l_id_tab.FIRST);
640 END IF;
641
642 IF l_id_tab.COUNT < 1000
643 THEN
644 l_req := TRUE;
645 ELSE
646 l_req := FALSE;
647 END IF;
648
649 l_count := l_count + l_id_tab.COUNT;
650 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
651 IF l_count >= l_chunk_size
652 THEN
653 l_reqtab(l_ind).end_id := l_id_tab(l_id_tab.LAST);
654
655 IF p_stop_after > 0
656 THEN
657 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
658 ELSIF p_stop_after IS NULL
659 THEN
660 l_stop_after := NULL;
661 ELSE
662 l_stop_after := 0;
663 END IF;
664
665 l_reqtab(l_ind).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
666 ,program => 'HXCUPGWK'
667 ,description => NULL
668 ,sub_request => FALSE
669 ,argument1 => p_type
670 ,argument4 => l_stop_after
671 ,argument2 => l_reqtab(l_ind).start_id ,
672 argument3 => l_reqtab(l_ind).end_id);
673 COMMIT;
674 INSERT INTO hxc_upgrade_status
675 (parent_id,
676 child_id,
677 child_status)
678 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
679 l_reqtab(l_ind).request_id,
680 'INCOMPLETE');
684 AND l_reqtab.COUNT >= p_num_workers
681
682 COMMIT;
683 IF p_num_workers > 0
685 THEN
686 l_work_limit := TRUE;
687 EXIT;
688 END IF;
689 l_count := 0;
690 END IF;
691 END LOOP;
692 CLOSE get_dep_txn_id;
693 END IF;
694
695 -- Stop
696
697
698 IF l_req = TRUE
699 THEN
700 IF p_stop_after > 0
701 THEN
702 l_stop_after := GREATEST(p_stop_after - ((SYSDATE-l_start_time)/24),0);
703 ELSIF p_stop_after IS NULL
704 THEN
705 l_stop_after := NULL;
706 ELSE
707 l_stop_after := 0;
708 END IF;
709
710
711 l_reqtab(l_reqtab.LAST).request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'HXC'
712 ,program => 'HXCUPGWK'
713 ,description => NULL
714 ,sub_request => FALSE
715 ,argument1 => p_type
716 ,argument4 => l_stop_after
717 ,argument2 => l_reqtab(l_reqtab.LAST).start_id ,
718 argument3 => l_reqtab(l_reqtab.LAST).end_id);
719
720 COMMIT;
721 l_ind := l_reqtab.LAST;
722 INSERT INTO hxc_upgrade_status
723 (parent_id,
724 child_id,
725 child_status)
726 VALUES ( FND_GLOBAL.CONC_REQUEST_ID,
727 l_reqtab(l_ind).request_id,
728 'INCOMPLETE');
729
730
731 COMMIT;
732 END IF;
733
734 WHILE l_req_complete <> TRUE
735 LOOP
736 l_req_complete := TRUE;
737 IF l_reqtab.COUNT > 0
738 THEN
739 FOR i IN l_reqtab.FIRST..l_reqtab.LAST
740 LOOP
741 l_call_status := FND_CONCURRENT.get_request_status(l_reqtab(i).request_id,
742 '',
743 '',
744 l_phase,
745 l_status,
746 l_dev_phase,
747 l_dev_status,
748 l_message);
749
750 IF l_call_status = FALSE
751 THEN
752 put_log(i||'th request failed');
753 END IF;
754 IF l_dev_phase <> 'COMPLETE'
755 THEN
756 l_req_complete := FALSE;
757 END IF;
758 END LOOP;
759 IF l_req_complete <> TRUE
760 THEN
761 dbms_lock.sleep(10);
762 END IF;
763 END IF;
764 END LOOP;
765
766 IF l_work_limit = TRUE
767 THEN
768 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
769 put_log(p_num_workers||' Worker Processes did not complete the Upgrade process');
770 put_log('The Program may be resumed later to complete the Upgrade Process');
771 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
772 RETURN;
773 END IF;
774
775 BEGIN
776 SELECT 1
777 INTO l_exists
778 FROM hxc_upgrade_status
779 WHERE child_status = 'INCOMPLETE'
780 AND ROWNUM < 2 ;
781
782 IF l_exists =1
783 THEN
784 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
785 put_log('This Upgrade Program did not complete processing in the specified time.');
786 put_log('The Program may be resumed later to complete the Upgrade Process');
787 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
788 END IF;
789 EXCEPTION
790 WHEN NO_DATA_FOUND
791 THEN
792 l_exists := 0;
793 END;
794
795 IF l_exists = 0
796 THEN
797 UPDATE hxc_upgrade_definitions
798 SET status = 'COMPLETE',
799 last_update_date = SYSDATE,
800 last_updated_by = FND_GLOBAL.user_id
801 WHERE upg_type = p_type;
802
803 put_log('++++++++++++++++++++++++++++++++++++++++++++++++');
804 put_log(upgrade_name(p_type)||' is Complete ');
805 put_log('++++++++++++++++++++++++++++++++++++++++++++++++');
806 END IF;
807
808 COMMIT;
809
810 END upgrade;
811
812 PROCEDURE upgrade_wk( errbuff OUT NOCOPY VARCHAR2,
813 retcode OUT NOCOPY VARCHAR2,
814 p_type IN VARCHAR2,
815 p_start IN NUMBER,
816 p_stop IN NUMBER,
817 p_stop_after IN NUMBER DEFAULT -999)
818 IS
819
820 CURSOR c_get_details( p_start NUMBER,
821 p_end NUMBER)
822 IS SELECT /*+ LEADING(hld)
823 ORDERED
824 INDEX(hta hxc_time_attributes_pk) */
825 attribute1,
826 attribute2,
827 rowidtochar(hld.rowid)
828 FROM hxc_latest_details hld,
832 AND p_end
829 hxc_time_attribute_usages hau,
830 hxc_time_attributes hta
831 WHERE hld.time_building_block_id BETWEEN p_start
833 AND hld.org_id IS NULL
834 AND hau.time_building_block_id = hld.time_building_block_id
835 AND hau.time_building_block_ovn = hld.object_version_number
836 AND hta.time_attribute_id = hau.time_attribute_id
837 AND hta.attribute_category = 'SECURITY';
838
839 -- start
840 CURSOR c_get_txns( p_start NUMBER,
841 p_end NUMBER)
842 IS SELECT transaction_id
843 FROM hxc_transactions
844 WHERE transaction_id BETWEEN p_start
845 AND p_end
846 AND type = 'DEPOSIT'
847 ORDER BY 1 asc;
848
849
850 -- Bug 9394446
851 -- Added the condition for Application Set
852 CURSOR c_get_pay_details( p_start NUMBER,
853 p_end NUMBER,
854 p_ret_id NUMBER )
855 IS SELECT hld.business_group_id,
856 hld.org_id,
857 hld.resource_id,
858 hld.time_building_block_id,
859 hld.object_version_number,
860 hld.approval_status,
861 hld.start_time,
862 hld.stop_time,
863 hld.application_set_id,
864 hld.last_update_date,
865 hld.resource_type,
866 hld.comment_text,
867 tc.time_building_block_id
868 FROM hxc_latest_details hld,
869 hxc_time_building_blocks det,
870 hxc_time_building_blocks day,
871 hxc_time_building_blocks tc
872 WHERE hld.time_building_block_id BETWEEN p_start
873 AND p_end
874 AND hld.time_building_block_id = det.time_building_block_id
875 AND hld.object_version_number = det.object_version_number
876 AND day.time_building_block_id = det.parent_building_block_id
877 AND day.object_version_number = det.parent_building_block_ovn
878 AND tc.time_building_block_id = day.parent_building_block_id
879 AND tc.object_version_number = day.parent_building_block_ovn
880 AND NOT EXISTS ( SELECT 1
881 FROM hxc_transaction_details htd,
882 hxc_transactions ht
883 WHERE htd.time_building_block_id = hld.time_building_block_id
884 AND htd.time_building_block_ovn = hld.object_version_number
885 AND htd.transaction_id = ht.transaction_id
886 AND htd.status = 'SUCCESS'
887 AND ht.type = 'RETRIEVAL'
888 AND ht.status = 'SUCCESS'
889 AND ht.transaction_process_id IN (p_ret_id,-1))
890 AND NOT EXISTS ( SELECT 1
891 FROM hxc_pay_latest_details hpd
892 WHERE hpd.time_building_block_id = hld.time_building_block_id
893 AND hpd.object_version_number = hld.object_version_number)
894 AND hld.application_set_id IN ( SELECT application_set_id
895 FROM hxc_application_set_comps_v
896 -- Bug 12609169
897 -- Added HR here
898 WHERE time_recipient_name IN ( 'Payroll','Human Resources'))
899 ;
900
901
902 -- Bug 9394446
903 -- Added the condition for Application Set
904 CURSOR c_get_pa_details( p_start NUMBER,
905 p_end NUMBER,
906 p_ret_id NUMBER )
907 IS SELECT hld.business_group_id,
908 hld.org_id,
909 hld.resource_id,
910 hld.time_building_block_id,
911 hld.object_version_number,
912 hld.approval_status,
913 hld.start_time,
914 hld.stop_time,
915 hld.application_set_id,
916 hld.last_update_date,
917 hld.resource_type,
918 hld.comment_text,
919 tc.time_building_block_id
920 FROM hxc_latest_details hld,
921 hxc_time_building_blocks det,
922 hxc_time_building_blocks day,
923 hxc_time_building_blocks tc
924 WHERE hld.time_building_block_id BETWEEN p_start
925 AND p_end
926 AND hld.time_building_block_id = det.time_building_block_id
927 AND hld.object_version_number = det.object_version_number
928 AND day.time_building_block_id = det.parent_building_block_id
929 AND day.object_version_number = det.parent_building_block_ovn
930 AND tc.time_building_block_id = day.parent_building_block_id
931 AND tc.object_version_number = day.parent_building_block_ovn
932 AND NOT EXISTS ( SELECT 1
933 FROM hxc_transaction_details htd,
934 hxc_transactions ht
935 WHERE htd.time_building_block_id = hld.time_building_block_id
936 AND htd.time_building_block_ovn = hld.object_version_number
940 AND ht.status = 'SUCCESS'
937 AND htd.transaction_id = ht.transaction_id
938 AND htd.status = 'SUCCESS'
939 AND ht.type = 'RETRIEVAL'
941 AND ht.transaction_process_id = p_ret_id )
942 AND NOT EXISTS ( SELECT 1
943 FROM hxc_pa_latest_details hpd
944 WHERE hpd.time_building_block_id = hld.time_building_block_id
945 AND hpd.object_version_number = hld.object_version_number)
946 AND hld.application_set_id IN ( SELECT application_set_id
947 FROM hxc_application_set_comps_v
948 WHERE time_recipient_name = 'Projects')
949 ;
950
951 -- Bug 9790410
952
953 CURSOR get_details( p_start NUMBER,
954 p_end NUMBER)
955 IS SELECT DISTINCT sum.id,
956 sum.tim_id,
957 ar.data_set_id
958 FROM hxt_sum_hours_worked_f sum,
959 hxc_time_building_blocks_ar ar
960 WHERE sum.id BETWEEN p_start
961 AND p_end
962 AND sum.time_building_block_id = ar.time_building_block_id
963 AND sum.time_building_block_ovn = ar.object_version_number ;
964
965
966
967 -- Bug 9938394
968
969 -- To pick up BEE Retrieval Process's Id.
970 CURSOR get_bee_process
971 IS SELECT retrieval_process_id
972 FROM hxc_retrieval_processes
973 WHERE name = 'BEE Retrieval Process';
974
975 -- Bug 10008473
976 -- Added Order by so that timecards do not repeat themselves in the
977 -- below cursor.
978 -- To pick up all timecards.
979 CURSOR get_all_timecards(p_start IN NUMBER)
980 IS SELECT master_id
981 FROM hxc_upg_master_temp temp
982 WHERE thread_id = p_start
983 ORDER BY master_id ;
984
985 -- To pick up details pertaining to each recipient application for each timecard.
986 -- Since Application sets is joined, each detail will come out once for each
987 -- time recipient.
988 -- Bug 14281943
989 -- Added a DISTINCT to take care of App set with HR and Payroll
990 CURSOR get_all_details(p_start NUMBER,
991 p_end NUMBER,
992 p_thread NUMBER,
993 p_bee_id NUMBER)
994 IS SELECT DISTINCT
995 temp.master_id,
996 temp.master_ovn,
997 hld.time_building_block_id,
998 hld.object_version_number,
999 DECODE(hrp.NAME, 'HR Retrieval Process', p_bee_id,
1000 hrp.retrieval_process_id),
1001 DECODE(detail.date_to, hr_general.end_of_time,NULL,'D')
1002 FROM hxc_upg_master_temp temp,
1003 hxc_time_building_blocks day,
1004 hxc_time_building_blocks detail,
1005 hxc_latest_details hld,
1006 hxc_application_set_comps_v hasc,
1007 hxc_retrieval_processes hrp
1008 WHERE temp.master_id = day.parent_building_block_id
1009 AND temp.master_ovn = day.parent_building_block_ovn
1010 AND day.time_building_block_id = detail.parent_building_block_id
1011 AND day.object_version_number = detail.parent_building_block_ovn
1012 AND detail.time_building_block_id = hld.time_building_block_id
1013 AND detail.object_version_number = hld.object_version_number
1014 AND hld.application_set_id = hasc.application_set_id
1015 AND hasc.time_recipient_id = hrp.time_recipient_id
1016 AND thread_id = p_thread
1017 AND temp.master_id BETWEEN p_start
1018 AND p_end ;
1019
1020
1021 -- To pick up the details which are Deleted and Not transferred earlier.
1022
1023 CURSOR get_deleted_entries(p_thread_id NUMBER,
1024 p_bee_id NUMBER)
1025 IS SELECT ROWIDTOCHAR(temp.rowid)
1026 FROM hxc_upg_details_temp temp
1027 WHERE temp.thread_id = p_thread_id
1028 AND temp.detail_attribute2 = 'D'
1029 AND NOT EXISTS ( SELECT 1
1030 FROM hxc_transaction_details htd,
1031 hxc_transactions ht
1032 WHERE htd.time_building_block_id = temp.detail_id
1033 AND htd.time_building_block_ovn < temp.detail_ovn
1034 AND htd.transaction_id = ht.transaction_id
1035 AND ht.type = 'RETRIEVAL'
1036 AND ht.status = 'SUCCESS'
1037 AND htd.status = 'SUCCESS'
1038 AND DECODE(ht.transaction_process_id,-1,p_bee_id,
1039 ht.transaction_process_id) = TO_NUMBER(temp.detail_attribute1)) ;
1040
1041
1042 -- To pick up the details which are not transferred yet.
1043 CURSOR get_untransferred_entries(p_thread_id NUMBER,
1044 p_bee_id NUMBER)
1045 IS SELECT temp.master_id,
1046 TO_NUMBER(temp.detail_attribute1)
1047 FROM hxc_upg_details_temp temp
1048 WHERE temp.thread_id = p_thread_id
1052 WHERE htd.time_building_block_id = temp.detail_id
1049 AND NOT EXISTS ( SELECT 1
1050 FROM hxc_transaction_details htd,
1051 hxc_transactions ht
1053 AND htd.time_building_block_ovn = temp.detail_ovn
1054 AND htd.transaction_id = ht.transaction_id
1055 AND ht.type = 'RETRIEVAL'
1056 AND ht.status = 'SUCCESS'
1057 AND htd.status = 'SUCCESS'
1058 AND DECODE(ht.transaction_process_id,-1,p_bee_id,
1059 ht.transaction_process_id) = TO_NUMBER(temp.detail_attribute1)) ;
1060
1061
1062
1063
1064 -- To pick up the timecard ids and retrieval process names.
1065 CURSOR get_timecards_xfer(p_thread_id NUMBER)
1066 IS SELECT DISTINCT master_id,
1067 master_ovn,
1068 htr.name
1069 FROM hxc_upg_details_temp temp,
1070 hxc_retrieval_processes hrp,
1071 hxc_time_recipients htr
1072 WHERE TO_NUMBER(temp.detail_attribute1) = hrp.retrieval_process_id
1073 AND hrp.time_recipient_id = htr.time_recipient_id
1074 AND temp.thread_id = p_thread_id
1075 ORDER BY master_id,
1076 htr.name;
1077
1078 msttab NUMTABLE;
1079 rettab NUMTABLE;
1080 l_id_tab NUMTABLE;
1081 tcsumidtab NUMTABLE;
1082 tcsumovntab NUMTABLE;
1083 xfertab VARCHARTABLE;
1084 l_thread_id NUMBER;
1085 l_bee_id NUMBER;
1086 tcidtab NUMTABLE;
1087 tcovntab NUMTABLE;
1088 detidtab NUMTABLE;
1089 detovntab NUMTABLE;
1090 rtidtab NUMTABLE;
1091 deltab VARCHARTABLE;
1092
1093 sumidtab NUMTABLE;
1094 timidtab NUMTABLE;
1095 dsidtab NUMTABLE;
1096
1097
1098 txnidtab NUMTABLE;
1099
1100 -- stop
1101
1102 orgtab VARCHARTABLE;
1103 bgtab VARCHARTABLE;
1104 rowtab VARCHARTABLE;
1105
1106
1107
1108
1109 business_group_tab NUMTABLE;
1110 org_tab NUMTABLE;
1111 resource_tab NUMTABLE;
1112 time_building_block_tab NUMTABLE;
1113 ovn_tab NUMTABLE;
1114 approval_status_tab VARCHARTABLE;
1115 start_time_tab DATETABLE;
1116 stop_time_tab DATETABLE;
1117 application_set_tab NUMTABLE;
1118 last_update_date_tab DATETABLE;
1119 resource_type_tab VARCHARTABLE;
1120 comment_text_tab VARCHARTABLE;
1121 timecard_tab NUMTABLE;
1122 l_process_id NUMBER;
1123
1124
1125 l_complete BOOLEAN := TRUE;
1126 l_stop_time DATE;
1127 l_count NUMBER := 0;
1128
1129
1130 BEGIN
1131
1132 IF p_stop_after > 0
1133 THEN
1134 l_stop_time := SYSDATE + (p_stop_after/(24));
1135 ELSIF p_stop_after IS NULL
1136 THEN
1137 l_stop_time := NULL;
1138 ELSE
1139 put_log('This Worker Program could not start processing the Upgrade');
1140 RETURN;
1141 END IF;
1142
1143 hr_general.g_data_migrator_mode := 'Y';
1144
1145 IF (p_type ='LATEST_DETAILS')
1146 THEN
1147 OPEN c_get_details(p_start,
1148 p_stop);
1149 LOOP
1150 FETCH c_get_details BULK COLLECT INTO orgtab,
1151 bgtab,
1152 rowtab LIMIT 500;
1153 EXIT WHEN orgtab.COUNT = 0;
1154 l_count := l_count + orgtab.COUNT;
1155
1156 IF l_stop_time IS NOT NULL
1157 AND SYSDATE >= l_stop_time
1158 THEN
1159 l_complete := FALSE;
1160 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1161 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1162 put_log('Total Number of details processed : '||l_count);
1163 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1164 EXIT;
1165 END IF;
1166
1167
1168 FORALL i IN orgtab.FIRST..orgtab.LAST
1169 UPDATE hxc_latest_details
1170 SET org_id = orgtab(i),
1171 business_group_id = bgtab(i)
1172 WHERE rowid = CHARTOROWID(rowtab(i));
1173
1174
1175 COMMIT;
1176 END LOOP;
1177
1178 CLOSE c_get_details;
1179
1180
1181 IF l_complete = TRUE
1182 THEN
1183 UPDATE hxc_upgrade_status
1184 SET child_status = 'COMPLETE'
1185 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1186
1187 COMMIT;
1188
1189 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1190 put_log('Process completed successfully');
1191 put_log('Total Number of details processed :'||l_count);
1192 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1193
1194 END IF;
1195
1196 END IF;
1197
1198
1199 IF (p_type ='RETRIEVAL_PAY')
1200 THEN
1201 l_process_id := get_ret_process_id('BEE Retrieval Process');
1202
1203
1204 OPEN c_get_pay_details(p_start,
1208 FETCH c_get_pay_details
1205 p_stop,
1206 l_process_id );
1207 LOOP
1209 BULK COLLECT INTO business_group_tab,
1210 org_tab,
1211 resource_tab,
1212 time_building_block_tab,
1213 ovn_tab,
1214 approval_status_tab,
1215 start_time_tab,
1216 stop_time_tab,
1217 application_set_tab,
1218 last_update_date_tab,
1219 resource_type_tab,
1220 comment_text_tab,
1221 timecard_tab LIMIT 500;
1222 EXIT WHEN org_tab.COUNT = 0;
1223 l_count := l_count + org_tab.COUNT;
1224
1225 IF l_stop_time IS NOT NULL
1226 AND SYSDATE >= l_stop_time
1227 THEN
1228 l_complete := FALSE;
1229 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1230 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1231 put_log('Total Number of details processed : '||l_count);
1232 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1233 EXIT;
1234 END IF;
1235
1236
1237 FORALL i IN org_tab.FIRST..org_tab.LAST
1238 INSERT INTO hxc_pay_latest_details
1239 (business_group_id,
1240 org_id,
1241 resource_id,
1242 time_building_block_id,
1243 object_version_number,
1244 approval_status,
1245 start_time,
1246 stop_time,
1247 application_set_id,
1248 last_update_date,
1249 resource_type,
1250 comment_text,
1251 timecard_id)
1252 VALUES (business_group_tab(i),
1253 org_tab(i),
1254 resource_tab(i),
1255 time_building_block_tab(i),
1256 ovn_tab(i),
1257 approval_status_tab(i),
1258 start_time_tab(i),
1259 stop_time_tab(i),
1260 application_set_tab(i),
1261 last_update_date_tab(i),
1262 resource_type_tab(i),
1263 comment_text_tab(i),
1264 timecard_tab(i));
1265
1266 COMMIT;
1267 END LOOP;
1268
1269 CLOSE c_get_pay_details;
1270
1271
1272 IF l_complete = TRUE
1273 THEN
1274 UPDATE hxc_upgrade_status
1275 SET child_status = 'COMPLETE'
1276 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1277
1278 COMMIT;
1279
1280 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1281 put_log('Process completed successfully');
1282 put_log('Total Number of details processed :'||l_count);
1283 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1284
1285 END IF;
1286
1287 END IF;
1288
1289
1290 IF (p_type ='RETRIEVAL_PA')
1291 THEN
1292 l_process_id := get_ret_process_id('Projects Retrieval Process');
1293
1294
1295 OPEN c_get_pa_details(p_start,
1296 p_stop,
1297 l_process_id );
1298 LOOP
1299 FETCH c_get_pa_details
1300 BULK COLLECT INTO business_group_tab,
1301 org_tab,
1302 resource_tab,
1303 time_building_block_tab,
1304 ovn_tab,
1305 approval_status_tab,
1306 start_time_tab,
1307 stop_time_tab,
1308 application_set_tab,
1309 last_update_date_tab,
1310 resource_type_tab,
1311 comment_text_tab,
1312 timecard_tab LIMIT 500;
1313 EXIT WHEN org_tab.COUNT = 0;
1314 l_count := l_count + org_tab.COUNT;
1315
1316 IF l_stop_time IS NOT NULL
1317 AND SYSDATE >= l_stop_time
1318 THEN
1319 l_complete := FALSE;
1320 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1321 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1322 put_log('Total Number of details processed : '||l_count);
1323 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1324 EXIT;
1325 END IF;
1326
1327
1328 FORALL i IN org_tab.FIRST..org_tab.LAST
1329 INSERT INTO hxc_pa_latest_details
1330 (business_group_id,
1331 org_id,
1332 resource_id,
1333 time_building_block_id,
1334 object_version_number,
1335 approval_status,
1336 start_time,
1337 stop_time,
1341 comment_text,
1338 application_set_id,
1339 last_update_date,
1340 resource_type,
1342 timecard_id)
1343 VALUES (business_group_tab(i),
1344 org_tab(i),
1345 resource_tab(i),
1346 time_building_block_tab(i),
1347 ovn_tab(i),
1348 approval_status_tab(i),
1349 start_time_tab(i),
1350 stop_time_tab(i),
1351 application_set_tab(i),
1352 last_update_date_tab(i),
1353 resource_type_tab(i),
1354 comment_text_tab(i),
1355 timecard_tab(i));
1356
1357 COMMIT;
1358 END LOOP;
1359
1360 CLOSE c_get_pa_details;
1361
1362
1363 IF l_complete = TRUE
1364 THEN
1365 UPDATE hxc_upgrade_status
1366 SET child_status = 'COMPLETE'
1367 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1368
1369 COMMIT;
1370
1371 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1372 put_log('Process completed successfully');
1373 put_log('Total Number of details processed :'||l_count);
1374 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1375
1376 END IF;
1377
1378 END IF;
1379
1380
1381 IF (p_type ='HXT_ARCHIVE')
1382 THEN
1383
1384 OPEN get_details(p_start,
1385 p_stop );
1386 LOOP
1387 FETCH get_details
1388 BULK COLLECT INTO sumidtab,
1389 timidtab,
1390 dsidtab LIMIT 500;
1391
1392 EXIT WHEN sumidtab.COUNT = 0;
1393
1394 l_count := l_count + sumidtab.COUNT;
1395
1396 IF l_stop_time IS NOT NULL
1397 AND SYSDATE >= l_stop_time
1398 THEN
1399 l_complete := FALSE;
1400 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1401 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1402 put_log('Total Number of details processed : '||l_count);
1403 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1404 EXIT;
1405 END IF;
1406
1407
1408 FORALL i IN sumidtab.FIRST..sumidtab.LAST
1409 INSERT INTO hxc_archive_temp
1410 (detail_id,master_id,thread_id)
1411 VALUES (sumidtab(i),timidtab(i),dsidtab(i));
1412
1413
1414
1415 INSERT INTO hxt_sum_hours_worked_f_ar
1416 ( state_name, county_name, city_name, zip_code, ffv_cost_center_id,
1417 ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
1418 ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
1419 separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1420 last_update_date, last_update_login, actual_time_in, actual_time_out,
1421 job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
1422 effective_end_date, attribute_category, attribute1, attribute2, attribute3,
1423 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
1424 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
1425 attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,
1426 attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,
1427 attribute28, attribute29, attribute30, object_version_number, time_building_block_id,
1428 time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
1429 hours, time_in, time_out, element_type_id, fcl_earn_reason_code,data_set_id )
1430 SELECT DISTINCT state_name, county_name, city_name, zip_code, ffv_cost_center_id,
1431 ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
1432 ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
1433 separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1434 last_update_date, last_update_login, actual_time_in, actual_time_out,
1435 job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
1436 effective_end_date, attribute_category, attribute1, attribute2, attribute3,
1437 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
1438 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
1439 attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,
1440 attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,
1441 attribute28, attribute29, attribute30, object_version_number, time_building_block_id,
1442 time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
1443 hours, time_in, time_out, element_type_id, fcl_earn_reason_code ,thread_id
1444 FROM hxt_sum_hours_worked_f sum,
1445 hxc_archive_temp temp
1446 WHERE temp.detail_id = sum.id ;
1447
1448
1452
1449 FORALL i IN sumidtab.FIRST..sumidtab.LAST
1450 DELETE FROM hxt_sum_hours_worked_f
1451 WHERE id = sumidtab(i);
1453
1454 INSERT INTO hxt_det_hours_worked_f_ar
1455 ( data_set_id, state_name, county_name, city_name, zip_code, id, parent_id, line_status,
1456 tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
1457 fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
1458 sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
1459 fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1460 last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
1461 effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
1462 prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number )
1463 SELECT DISTINCT
1464 thread_id, state_name, county_name, city_name, zip_code, id, parent_id, line_status,
1465 tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
1466 fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
1467 sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
1468 fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1469 last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
1470 effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
1471 prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number
1472 FROM hxc_archive_temp temp,
1473 hxt_det_hours_worked_f det
1474 WHERE temp.detail_id = det.parent_id ;
1475
1476
1477 FORALL i IN sumidtab.FIRST..sumidtab.LAST
1478 DELETE FROM hxt_det_hours_worked_f
1479 WHERE parent_id = sumidtab(i);
1480
1481 FORALL i IN timidtab.FIRST..timidtab.LAST
1482 UPDATE hxt_timecards_f
1483 SET data_set_id = dsidtab(i)
1484 WHERE id = timidtab(i);
1485
1486
1487 COMMIT;
1488 END LOOP;
1489
1490 CLOSE get_details;
1491
1492
1493 IF l_complete = TRUE
1494 THEN
1495 UPDATE hxc_upgrade_status
1496 SET child_status = 'COMPLETE'
1497 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1498
1499 COMMIT;
1500
1501 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1502 put_log('Process completed successfully');
1503 put_log('Total Number of details processed :'||l_count);
1504 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1505
1506 END IF;
1507
1508 END IF;
1509
1510 -- Bug 9938394
1511 -- Populating hxc_timecard_summary.Xferred_to
1512 IF (p_type ='XFER_TO')
1513 THEN
1514
1515 l_thread_id := p_start;
1516
1517 OPEN get_bee_process;
1518 FETCH get_bee_process INTO l_bee_id;
1519 CLOSE get_bee_process;
1520
1521
1522 OPEN get_all_timecards(p_start) ;
1523 LOOP
1524 FETCH get_all_timecards BULK COLLECT INTO l_id_tab LIMIT 20;
1525
1526 EXIT WHEN l_id_tab.COUNT = 0;
1527
1528 OPEN get_all_details(l_id_tab(l_id_tab.FIRST),
1529 l_id_tab(l_id_tab.LAST),
1530 l_thread_id,
1531 l_bee_id);
1532 LOOP
1533 FETCH get_all_details
1534 BULK COLLECT INTO tcidtab,
1535 tcovntab,
1536 detidtab,
1537 detovntab,
1538 rtidtab,
1539 deltab ;
1540
1541 EXIT WHEN tcidtab.COUNT = 0;
1542
1543 l_count := l_count + l_id_tab.COUNT;
1544
1545 IF l_stop_time IS NOT NULL
1546 AND SYSDATE >= l_stop_time
1547 THEN
1548 l_complete := FALSE;
1549 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1550 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1551 put_log('Total Number of details processed : '||l_count);
1552 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1553 EXIT;
1554 END IF;
1555
1556 -- Insert all details into the temp table.
1557
1558 BEGIN
1559
1560 FORALL i IN detidtab.FIRST..detidtab.LAST SAVE EXCEPTIONS
1561 INSERT INTO hxc_upg_details_temp
1562 ( master_id,
1563 master_ovn,
1564 detail_id,
1565 detail_ovn,
1566 detail_attribute1,
1567 detail_attribute2,
1568 thread_id)
1572 detovntab(i),
1569 VALUES ( tcidtab(i),
1570 tcovntab(i),
1571 detidtab(i),
1573 rtidtab(i),
1574 deltab(i),
1575 l_thread_id);
1576
1577
1578 -- Bug 14281943
1579 -- Added this exception block to investigate this bug
1580 -- and kept it.
1581 EXCEPTION
1582 WHEN TABLE_EXCEPTION
1583 THEN
1584
1585 IF SQL%BULK_EXCEPTIONS.COUNT > 0
1586 THEN
1587 put_log('List of Details with Exceptions ');
1588 FOR n IN 1..SQL%BULK_EXCEPTIONS.COUNT
1589 LOOP
1590 put_log('TC_Id '||tcidtab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
1591 ' TC_OVN '||tcovntab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
1592 put_log('Det_Id '||detidtab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
1593 ' Det_OVN '||detovntab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
1594 put_log('RT_Id '||rtidtab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX)||
1595 ' Del '||deltab(SQL%BULK_EXCEPTIONS(n).ERROR_INDEX));
1596 put_log('Error ORA -'||LPAD(SQL%BULK_EXCEPTIONS(n).ERROR_CODE,5,'0'));
1597 END LOOP;
1598 RAISE INVALID_DETAIL;
1599
1600 END IF;
1601
1602 END;
1603
1604 COMMIT;
1605
1606 -- Pick up and delete the details which are deleted and NOT
1607 -- transferred earlier.
1608 OPEN get_deleted_entries(l_thread_id,
1609 l_bee_id);
1610 LOOP
1611 FETCH get_deleted_entries BULK COLLECT INTO ROWTAB LIMIT 500;
1612 EXIT WHEN rowtab.COUNT = 0;
1613
1614 FORALL i IN rowtab.FIRST..rowtab.LAST
1615 DELETE FROM hxc_upg_details_temp
1616 WHERE ROWID = CHARTOROWID(rowtab(i));
1617
1618 COMMIT;
1619 END LOOP;
1620 CLOSE get_deleted_entries;
1621
1622
1623 -- Delete the entries whicha are not transferred yet.
1624 OPEN get_untransferred_entries(l_thread_id,
1625 l_bee_id);
1626 LOOP
1627 FETCH get_untransferred_entries
1628 BULK COLLECT
1629 INTO msttab,
1630 rettab LIMIT 500;
1631 EXIT WHEN rettab.COUNT = 0;
1632
1633 FORALL i IN msttab.FIRST..msttab.LAST
1634 DELETE FROM hxc_upg_details_temp
1635 WHERE master_id = msttab(i)
1636 AND detail_attribute1 = rettab(i);
1637
1638 COMMIT;
1639 END LOOP;
1640 CLOSE get_untransferred_entries;
1641
1642
1643 -- Pick up the remaining timecard ids and update the recipient applications.
1644 OPEN get_timecards_xfer(l_thread_id);
1645 LOOP
1646 FETCH get_timecards_xfer
1647 BULK COLLECT
1648 INTO tcsumidtab,
1649 tcsumovntab,
1650 xfertab LIMIT 500;
1651
1652 EXIT WHEN tcsumidtab.COUNT = 0;
1653
1654 FORALL i IN tcsumidtab.FIRST..tcsumidtab.LAST
1655 UPDATE hxc_timecard_summary
1656 SET transferred_to = LTRIM(NVL(transferred_to,',')||','||xfertab(i),',')
1657 WHERE timecard_id = tcsumidtab(i)
1658 AND timecard_ovn = tcsumovntab(i);
1659
1660 COMMIT;
1661
1662 END LOOP;
1663 CLOSE get_timecards_xfer;
1664
1665 END LOOP;
1666
1667 CLOSE get_all_details;
1668
1669 DELETE FROM hxc_upg_details_temp
1670 WHERE thread_id = l_thread_id;
1671
1672 COMMIT;
1673 END LOOP;
1674
1675 CLOSE get_all_timecards;
1676
1677 IF l_complete = TRUE
1678 THEN
1679 UPDATE hxc_upgrade_status
1680 SET child_status = 'COMPLETE'
1681 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1682
1683 COMMIT;
1684
1685 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1686 put_log('Process completed successfully');
1687 put_log('Total Number of timecards processed :'||l_count);
1688 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1689
1690 END IF;
1691
1692 END IF;
1693
1694
1695
1696 -- start
1697
1698 IF (p_type ='DEPOSIT_TRANSACTIONS')
1699 THEN
1700
1701 OPEN c_get_txns(p_start,
1702 p_stop);
1706 EXIT WHEN txnidtab.COUNT = 0;
1703 LOOP
1704 FETCH c_get_txns BULK COLLECT INTO txnidtab
1705 LIMIT 300;
1707
1708 l_count := l_count + txnidtab.COUNT;
1709
1710 IF l_stop_time IS NOT NULL
1711 AND SYSDATE >= l_stop_time
1712 THEN
1713 l_complete := FALSE;
1714 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1715 put_log('Processing crossed over '||p_stop_after||' minutes. Stopping...');
1716 put_log('Total Number of DEPOSIT transactions processed : '||l_count);
1717 put_log('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1718 EXIT;
1719 END IF;
1720
1721 -- Bug 9845460
1722 -- Insert into new table HXC_DEP_TRANSACTIONS
1723 FORALL i IN txnidtab.FIRST..txnidtab.LAST
1724 INSERT
1725 INTO hxc_dep_transactions
1726 (SELECT transaction_id,
1727 transaction_process_id,
1728 transaction_date,
1729 type,
1730 status,
1731 transaction_code,
1732 exception_description,
1733 object_version_number,
1734 created_by,
1735 creation_date,
1736 last_updated_by,
1737 last_update_date,
1738 last_update_login,
1739 data_set_id
1740 FROM hxc_transactions
1741 WHERE transaction_id = txnidtab(i)
1742 AND type = 'DEPOSIT') ;
1743
1744 put_log('Migrated data to HXC_DEP_TRANSACTIONS');
1745
1746 -- Delete DEPOSIT transaction records from HXC_TRANSACTIONS
1747 FORALL i IN txnidtab.FIRST..txnidtab.LAST
1748 DELETE
1749 FROM hxc_transactions
1750 WHERE transaction_id = txnidtab(i) ;
1751
1752 put_log('Deleted migrated records from HXC_TRANSACTIONS');
1753
1754 -- Bug 9845460
1755 -- Insert into new table HXC_DEP_TRANSACTION_DETAILS
1756 FORALL i IN txnidtab.FIRST..txnidtab.LAST
1757 INSERT INTO hxc_dep_transaction_details
1758 (SELECT transaction_detail_id,
1759 time_building_block_id,
1760 transaction_id,
1761 status,
1762 exception_description,
1763 object_version_number,
1764 created_by,
1765 creation_date,
1766 last_updated_by,
1767 last_update_date,
1768 last_update_login,
1769 time_building_block_ovn,
1770 data_set_id
1771 FROM hxc_transaction_details
1772 WHERE transaction_id = txnidtab(i)) ;
1773
1774 put_log('Migrated data to HXC_DEP_TRANSACTION_DETAILS');
1775
1776 -- Delete DEPOSIT transaction detail records from HXC_TRANSACTION_DETAILS
1777 FORALL i IN txnidtab.FIRST..txnidtab.LAST
1778 DELETE
1779 FROM hxc_transaction_details
1780 WHERE transaction_id = txnidtab(i) ;
1781
1782 put_log('Deleted migrated records from HXC_TRANSACTION_DETAILS');
1783
1784 COMMIT;
1785 END LOOP;
1786
1787 CLOSE c_get_txns;
1788
1789 put_log('Upgrade Worker Process Completed');
1790
1791 IF l_complete = TRUE
1792 THEN
1793 UPDATE hxc_upgrade_status
1794 SET child_status = 'COMPLETE'
1795 WHERE child_id = FND_GLOBAL.CONC_REQUEST_ID;
1796
1797 COMMIT;
1798
1799 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1800 put_log('Process completed successfully');
1801 put_log('Total Number of details processed :'||l_count);
1802 put_log('++++++++++++++++++++++++++++++++++++++++++++++++++++');
1803
1804 END IF;
1805
1806 END IF;
1807 -- stop
1808
1809 END upgrade_wk;
1810
1811
1812 PROCEDURE put_log(p_text IN VARCHAR2)
1813 IS
1814
1815 PRAGMA AUTONOMOUS_TRANSACTION;
1816
1817 BEGIN
1818 FND_FILE.PUT_LINE(FND_FILE.LOG,p_text);
1819 END put_log;
1820
1821 FUNCTION ret_upgrade_completed
1822 RETURN BOOLEAN
1823
1824 IS
1825
1826 l_upgrade NUMBER;
1827
1828 BEGIN
1829
1830 SELECT 1
1831 INTO l_upgrade
1832 FROM hxc_upgrade_definitions
1833 WHERE upg_type = 'LATEST_DETAILS'
1834 AND status = 'COMPLETE';
1835
1836 IF l_upgrade = 1
1837 THEN
1838 RETURN TRUE;
1839 ELSE
1840 RETURN FALSE;
1841 END IF;
1842
1843 EXCEPTION
1844 WHEN NO_DATA_FOUND
1845 THEN
1846 RETURN FALSE;
1847 END ret_upgrade_completed;
1848
1849
1850 -- start
1851 FUNCTION txn_upgrade_completed
1852 RETURN BOOLEAN
1853
1854 IS
1855
1856 l_upgrade NUMBER;
1857
1858 BEGIN
1859
1860 SELECT 1
1861 INTO l_upgrade
1862 FROM hxc_upgrade_definitions
1863 WHERE upg_type = 'DEPOSIT_TRANSACTIONS'
1864 AND status = 'COMPLETE';
1865
1866 IF l_upgrade = 1
1867 THEN
1868 RETURN TRUE;
1869 ELSE
1870 RETURN FALSE;
1871 END IF;
1872
1873 EXCEPTION
1874 WHEN NO_DATA_FOUND
1875 THEN
1876 RETURN FALSE;
1877 END txn_upgrade_completed;
1878
1879
1880 PROCEDURE insert_into_upg_defn(p_upg_type IN VARCHAR2)
1881 IS
1882
1883 BEGIN
1884
1885 INSERT INTO HXC_UPGRADE_DEFINITIONS
1886 ( UPG_TYPE,
1887 STATUS,
1888 last_updated_by,
1889 last_update_date)
1890 VALUES
1891 ( p_upg_type,
1892 'INCOMPLETE',
1893 FND_GLOBAL.user_id,
1894 SYSDATE);
1895
1896 END insert_into_upg_defn;
1897 -- stop
1898
1899
1900 FUNCTION get_ret_process_id (p_process IN VARCHAR2)
1901 RETURN NUMBER
1902 IS
1903
1904 CURSOR get_process_id(p_process_name IN VARCHAR2)
1905 IS SELECT retrieval_process_id
1906 FROM hxc_retrieval_processes
1907 WHERE name = p_process_name;
1908
1909 l_process_id NUMBER:= 0;
1910
1911 BEGIN
1912 OPEN get_process_id(p_process);
1913 FETCH get_process_id INTO l_process_id;
1914 CLOSE get_process_id;
1915 RETURN l_process_id;
1916
1917 END get_ret_process_id;
1918
1919 FUNCTION upgrade_name(p_lookup_code IN VARCHAR2)
1920 RETURN VARCHAR2
1921 IS
1922
1923 CURSOR get_upg_name(p_lookup_code IN VARCHAR2)
1924 IS SELECT meaning
1925 FROM fnd_lookup_values
1926 WHERE lookup_type = 'HXC_UPGRADE_DEFINITIONS'
1927 AND lookup_code = p_lookup_code
1928 AND language = USERENV('LANG');
1929
1930 l_lookup_name VARCHAR2(100);
1931
1932 BEGIN
1933
1934 IF g_upg_name.EXISTS(p_lookup_code)
1935 THEN
1936 RETURN g_upg_name(p_lookup_code);
1937 ELSE
1938 OPEN get_upg_name(p_lookup_code);
1939 FETCH get_upg_name INTO l_lookup_name;
1940 CLOSE get_upg_name;
1941
1942 g_upg_name(p_lookup_code) := l_lookup_name;
1943 RETURN g_upg_name(p_lookup_code);
1944 END IF;
1945
1946 END upgrade_name;
1947
1948
1949 FUNCTION performance_upgrade_complete(p_upg_type IN VARCHAR2)
1950 RETURN BOOLEAN
1951 IS
1952
1953 l_upgrade NUMBER;
1954
1955 BEGIN
1956
1957 SELECT 1
1958 INTO l_upgrade
1959 FROM hxc_upgrade_definitions
1960 WHERE upg_type = p_upg_type
1961 AND status = 'COMPLETE';
1962
1963 IF l_upgrade = 1
1964 THEN
1965 RETURN TRUE;
1966 ELSE
1967 RETURN FALSE;
1968 END IF;
1969
1970 EXCEPTION
1971 WHEN NO_DATA_FOUND
1972 THEN
1973 RETURN FALSE;
1974 END performance_upgrade_complete;
1975
1976
1977
1978 END HXC_UPGRADE_PKG;