[Home] [Help]
PACKAGE BODY: APPS.HXC_RESTORE
Source
1 PACKAGE BODY hxc_restore AS
2 /* $Header: hxcrestore.pkb 120.12.12020000.2 2012/07/04 09:31:18 amnaraya ship $ */
3 ----------------------------------------------------------------------------
4 -- Procedure Name : restore_process
5 -- Description : This procedure is called durINg Restore Data Set process.
6 -- For a given data set, it copies the records FROM archive
7 -- tables to base tables AND deletes the records IN archive table.
8 -- It restores the links IN hxc_tc_ap_links AND hxc_ap_detail_links.
9 -- It starts the approval process agaIN for eligible
10 -- application period ids. This process is done IN chunks.
11 ----------------------------------------------------------------------------
12 PROCEDURE restore_process(p_data_set_id NUMBER,
13 p_data_set_start_date DATE,
14 p_data_set_end_date DATE)
15 IS
16
17
18
19 -- For the given data set id, pick up all the timecard scope records
20 -- to be dumped into the temp table for processing.
21
22 CURSOR get_timecards( p_data_set_id IN NUMBER)
23 IS SELECT /*+ INDEX (ar HXC_TIME_BLDNG_BLOCKS_AR_FK5)*/
24 TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,UNIT_OF_MEASURE,
25 START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,PARENT_BUILDING_BLOCK_OVN,
26 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
27 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
28 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,APPLICATION_SET_ID,DATA_SET_ID,
29 TRANSLATION_DISPLAY_KEY
30 FROM hxc_time_building_blocks_ar ar
31 WHERE data_set_id = p_data_set_id
32 AND scope = 'TIMECARD'
33 ORDER BY time_building_block_id;
34
35
36 -- To pick up the details of the timecards processed, grouped by threads- chunks.
37
38 CURSOR get_log_details
39 IS SELECT temp1.resource_id,
40 temp1.start_time,
41 temp1.time_building_block_id tc_id,
42 temp2.thread_id||'('||temp2.chunk_number||')' detail
43 FROM hxc_ar_detail_log temp2,
44 hxc_ar_tc_ids_temp temp1
45 WHERE temp1.time_building_block_id = temp2.time_building_block_id
46 AND temp1.object_version_number = temp2.object_version_number
47 AND temp2.process_type NOT LIKE '%INCOMPLETE%'
48 ORDER BY temp2.thread_id,
49 temp2.chunk_number,
50 temp1.start_time,
51 temp1.resource_id ;
52
53 -- To pick up the details of the timecards not processed, grouped by threads- chunks.
54
55 CURSOR get_log_details_failed
56 IS SELECT temp1.resource_id,
57 temp1.start_time,
58 temp1.time_building_block_id tc_id,
59 temp2.thread_id||'('||temp2.chunk_number||')' detail
60 FROM hxc_ar_detail_log temp2,
61 hxc_ar_tc_ids_temp temp1
62 WHERE temp1.time_building_block_id = temp2.time_building_block_id
63 AND temp1.object_version_number = temp2.object_version_number
64 AND temp2.process_type LIKE '%INCOMPLETE%'
65 ORDER BY temp2.thread_id,
66 temp2.chunk_number,
67 temp1.start_time,
68 temp1.resource_id ;
69
70
71
72
73 l_chunk_size number;
74
75 TYPE NUMTABLE IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE;
76 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(4000);
77
78
79 TYPE NUMBERTABLE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
80 bb_id_tab NUMBERTABLE;
81
82 TYPE tc_tab IS TABLE OF get_timecards%ROWTYPE;
83 l_tc_tab tc_tab;
84
85 l_tc_count NUMBER;
86 l_bb_ctr BINARY_INTEGER ;
87
88 l_tc_chunk NUMBER;
89 l_start NUMBER;
90 l_stop NUMBER;
91 id_start NUMBER;
92 id_stop NUMBER;
93 j NUMBER := 1;
94 l_req_id_tab NUMBERTABLE ;
95
96 l_call_status BOOLEAN ;
97 l_interval NUMBER := 30;
98 l_phase VARCHAR2(30);
99 l_status VARCHAR2(30);
100 l_dev_phase VARCHAR2(30);
101 l_dev_status VARCHAR2(30);
102 l_message VARCHAR2(30);
103 iloop NUMBER;
104
105 all_threads_complete BOOLEAN := FALSE;
106
107 trans_count NUMBER:=0;
108 l_debug_info VARCHAR2(50);
109 l_thread_success BOOLEAN := TRUE;
110
111
112 BEGIN
113
114
115 -- POST RE-ARCHITECTURE THIS PROCESS FOLLOWS THE SAME ALGORITHM OF
116 -- THE ARCHIVE PROCESS IN HXC_ARCHIVE PACKAGE. PLS REFER THAT PACKAGE
117 -- FOR DETAILED COMMENTS.
118
119 hr_general.g_data_migrator_mode := 'Y';
120
121 l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),50);
122
123 fnd_file.put_line(fnd_file.log,'--- > Chunk Size is: '||l_chunk_size);
124
125 -- Update hxc_data_sets as RESTORE_IN_PROGRESS because the process is going to take some time.
126
127 UPDATE hxc_data_sets
128 SET status = 'RESTORE_IN_PROGRESS'
129 WHERE data_set_id = p_data_set_id;
130
131 -- Delete from the temp tables, if there is any left over data.
132
133 DELETE FROM hxc_ar_detail_log ;
134 DELETE FROM hxc_ar_tc_ids_temp;
135 DELETE FROM hxc_ar_trans_temp;
136 DELETE FROM hxc_data_set_details;
137
138 COMMIT;
139 l_bb_ctr := 0;
140
141 -- Get timecards in this data set.
142 OPEN get_timecards(p_data_set_id);
143 LOOP
144
145 -- Fetch 500 timecard scope records ( one record is one tbb_id- ovn combination )
146
147 FETCH get_timecards
148 BULK COLLECT INTO l_tc_tab LIMIT 500;
149
150 EXIT WHEN l_tc_tab.COUNT = 0;
151
152 -- Insert these 500 records into hxc_ar_tc_ids_temp
153 FORALL i IN l_tc_tab.FIRST..l_tc_tab.LAST
154 INSERT INTO hxc_ar_tc_ids_temp
155 VALUES l_tc_tab(i) ;
156
157 -- Loop thru the timecard records fetched
158 -- and record the time_building_block_ids
159 -- coming in positions which are multiples of 10.
160 -- Eg. For 500 timecards, pick 0th, 10th, 20th, 30th ids etc.
161
162 iloop := l_tc_tab.FIRST;
163 WHILE iloop < l_tc_tab.last
164 LOOP
165 l_bb_ctr := l_bb_ctr + 1;
166 bb_id_tab(l_bb_ctr) := l_tc_tab(iloop).time_building_block_id;
167 iloop := iloop + 10;
168 IF iloop >= l_tc_tab.last
169 THEN
170 iloop := l_tc_tab.last;
171 l_bb_ctr := l_bb_ctr + 1;
172 bb_id_tab(l_bb_ctr) := l_tc_tab(iloop).time_building_block_id;
173 END IF;
174 END LOOP;
175
176 END LOOP;
177
178 COMMIT;
179 --return;
180
181 -- Check how many are there in the plsql table,
182 -- which has timecard ids at an offset of 10.
183 l_tc_count := bb_id_tab.COUNT;
184
185 -- We would process the below construct of launching the
186 -- multithreaded structure only if there are 10 entries above.
187 -- Meaning 10 * 10, 100 timecards in all to be archived.
188
189 IF l_tc_count > 10
190 THEN
191 -- There are five threads in all anyways.
192 -- This variable decides how many timecards go to each thread.
193 l_tc_chunk := ceil(l_tc_count/5);
194
195 l_start := 1;
196 l_stop := l_tc_chunk ;
197 id_stop := bb_id_tab(l_start);
198 fnd_file.put_line(fnd_file.log,'Following are the bb id ranges for the threads ');
199 fnd_file.put_line(fnd_file.log,'==============================================');
200 FOR i IN 1..4
201 LOOP
202 -- Calculate the start and stop ids and launch the threads.
203 -- ( 1- 4 threads get launched in this loop
204
205 id_start := id_stop;
206 id_stop := bb_id_tab(l_stop);
207
208 l_req_id_tab(i) := FND_REQUEST.SUBMIT_REQUEST( application => 'HXC',
209 program => 'HXCRESCHILD',
210 description => NULL,
211 sub_request => FALSE,
212 argument1 => id_start,
213 argument2 => id_stop,
214 argument3 => p_data_set_id,
215 argument4 => i );
216 fnd_file.put_line(fnd_file.log,id_start||' -> '||id_stop);
217 COMMIT;
218 l_stop := l_stop + l_tc_chunk ;
219 IF l_stop > l_tc_count
220 THEN
221 EXIT;
222 END IF;
223 END LOOP;
224
225 id_start := id_stop;
226 id_stop := bb_id_tab(bb_id_tab.LAST)+1;
227
228 fnd_file.put_line(fnd_file.log,id_start||' -> '||id_stop);
229
230 l_req_id_tab(5) := FND_REQUEST.SUBMIT_REQUEST( application => 'HXC',
231 program => 'HXCRESCHILD',
232 description => NULL,
233 sub_request => FALSE,
234 argument1 => id_start,
235 argument2 => id_stop,
236 argument3 => p_data_set_id,
237 argument4 => 5 );
238
239 COMMIT;
240 ELSE
241 id_start := bb_id_tab(bb_id_tab.first);
242 id_stop := bb_id_tab(bb_id_tab.last)+1;
243 l_req_id_tab(5) := FND_REQUEST.SUBMIT_REQUEST( application => 'HXC',
244 program => 'HXCRESCHILD',
245 description => NULL,
246 sub_request => FALSE,
247 argument1 => id_start,
248 argument2 => id_stop,
249 argument3 => p_data_set_id,
250 argument4 => 5 );
251 COMMIT;
252 END IF;
253
254 WHILE all_threads_complete <> TRUE
255 LOOP
256 all_threads_complete := TRUE;
257 FOR i IN l_req_id_tab.FIRST..l_req_id_tab.LAST
258 LOOP
259 IF l_req_id_tab.EXISTS(i)
260 THEN
261 l_call_status := FND_CONCURRENT.get_request_status(l_req_id_tab(i), '', '',
262 l_phase,
263 l_status,
264 l_dev_phase,
265 l_dev_status,
266 l_message);
267
268 IF l_call_status = FALSE
269 THEN
270 fnd_file.put_line(fnd_file.log,i||'th request failed');
271 l_thread_success := FALSE;
272 END IF;
273 IF l_dev_phase <> 'COMPLETE'
274 THEN
275 all_threads_complete := FALSE;
276 END IF ;
277 END IF;
278 END LOOP;
279
280 SELECT count(1)
281 INTO trans_count
282 FROM hxc_ar_trans_temp
283 WHERE rownum < 2;
284
285 IF trans_count >= 1
286 THEN
287 UPDATE hxc_ar_trans_temp
288 SET thread_id = 0 ;
289
290 -- Bug 11781607
291 -- Removed Data set id condition.
292
293 INSERT INTO hxc_transactions
294 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
295 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
296 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
297 SELECT bkuptxn.DATA_SET_ID,bkuptxn.TRANSACTION_ID,TRANSACTION_PROCESS_ID,
298 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
299 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
300 TRANSACTION_CODE
301 FROM hxc_transactions_ar bkuptxn
302 WHERE bkuptxn.transaction_id IN ( SELECT temp.transaction_id
303 FROM hxc_ar_trans_temp temp
304 WHERE thread_id = 0 )
305 AND bkuptxn.transaction_id NOT IN ( SELECT transaction_id
306 FROM hxc_transactions hxc
307 WHERE bkuptxn.transaction_id = hxc.transaction_id)
308 ;
309
310 -- Bug 11781607
311 -- Added the NOT EXISTS condtion to take care of transactions spanning
312 -- multiple data sets.
313
314 DELETE FROM hxc_transactions_ar ht
315 WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
316 FROM hxc_ar_trans_temp
317 WHERE thread_id = 0 )
318 AND NOT EXISTS ( SELECT 1
319 FROM hxc_transaction_details_ar htd
320 WHERE htd.transaction_id = ht.transaction_id) ;
321
322
323 DELETE FROM hxc_ar_trans_temp
324 WHERE thread_id = 0 ;
325
326 COMMIT;
327 ELSE
328 dbms_lock.sleep(10);
329 END IF;
330 END LOOP;
331
332 trans_count := 0;
333 SELECT COUNT(1)
334 INTO trans_count
335 FROM hxc_data_set_details;
336
337
338 IF l_thread_success and trans_count = 0
339 THEN
340 UPDATE hxc_data_sets
341 SET status = 'ON_LINE', validation_status = 'E'
342 WHERE data_set_id = p_data_set_id;
343
344 ELSE
345 fnd_file.put_line(fnd_file.LOG,' There were some issues with the process');
346 fnd_file.put_line(fnd_file.LOG,' Either one of the threads failed or there is a data issue ');
347 fnd_file.put_line(fnd_file.LOG,' Please run the process once again to reprocess the failed ');
348 fnd_file.put_line(fnd_file.LOG,' timecards. ');
349 fnd_file.put_line(fnd_file.LOG,' ');
350
351 END IF;
352
353
354 COMMIT;
355
356
357
358 l_debug_info := ' ';
359 IF fnd_profile.value('HXC_AR_ENABLE_LOG') = 'Y'
360 THEN
361 fnd_file.put_line(fnd_file.LOG,'Following are the timecards processed ');
362 fnd_file.put_line(fnd_file.LOG,'======================================');
363 fnd_file.put_line(fnd_file.LOG,' ');
364 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
365 fnd_file.put_line(fnd_file.LOG,' ');
366 FOR log_rec IN get_log_details
367 LOOP
368 IF l_debug_info <> log_rec.detail
369 THEN
370 fnd_file.put_line(fnd_file.log,' ---- ');
371 l_debug_info := log_rec.detail;
372 END IF;
373 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
374 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
375 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
376 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
377 END LOOP;
378
379 l_debug_info := ' ';
380 fnd_file.put_line(fnd_file.LOG,' ');
381 fnd_file.put_line(fnd_file.LOG,'Following are the timecards not processed ');
382 fnd_file.put_line(fnd_file.LOG,'======================================');
383 fnd_file.put_line(fnd_file.LOG,' ');
384 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
385 fnd_file.put_line(fnd_file.LOG,' ');
386 FOR log_rec IN get_log_details_failed
387 LOOP
388 IF l_debug_info <> log_rec.detail
389 THEN
390 fnd_file.put_line(fnd_file.log,' ---- ');
391 l_debug_info := log_rec.detail;
392 END IF;
393 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
394 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
395 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
396 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
397 END LOOP;
398
399
400 END IF;
401
402
403 COMMIT;
404
405
406 END restore_process;
407
408
409
410
411 PROCEDURE child_restore_process ( errbuf OUT NOCOPY VARCHAR2,
412 retcode OUT NOCOPY NUMBER,
413 p_from_id IN NUMBER,
414 p_to_id IN NUMBER,
415 p_data_set_id IN NUMBER,
416 p_thread_id IN NUMBER )
417 IS
418
419
420 CURSOR get_tcs
421 IS SELECT time_building_block_id,
422 object_version_number
423 FROM hxc_ar_tc_ids_temp
424 WHERE time_building_block_id >= p_from_id
425 AND time_building_block_id < p_to_id ;
426
427 -- Bug 8888813
428 -- Modified the below cursor to pick up only RETRIEVAL type
429
430 CURSOR get_transactions
431 IS SELECT /*+ LEADING(temp) */
432 transaction_detail_id,
433 transaction_id,
434 ROWIDTOCHAR(ar.ROWID)
435 FROM hxc_temp_timecard_chunks temp,
436 hxc_transaction_details_ar ar
437 WHERE ar.time_building_block_id = temp.id
438 AND ar.time_building_block_ovn = temp.ref_ovn
439 AND EXISTS ( SELECT 1
440 FROM hxc_transactions_ar tr
441 WHERE tr.transaction_id = ar.transaction_id
442 AND tr.type = 'RETRIEVAL' )
443 AND thread_id = p_thread_id;
444
445 -- Bug 8888813
446 -- Added the following cursors to pick up DEPOSIT type
447 -- transactions from hxc_dep_transactions_ar and hxc_transactions_ar
448
449 CURSOR get_old_transactions
450 IS SELECT /*+ LEADING(temp) */
451 transaction_detail_id,
452 transaction_id,
453 ROWIDTOCHAR(ar.ROWID)
454 FROM hxc_temp_timecard_chunks temp,
455 hxc_transaction_details_ar ar
456 WHERE ar.time_building_block_id = temp.id
457 AND ar.time_building_block_ovn = temp.ref_ovn
458 AND EXISTS ( SELECT 1
459 FROM hxc_transactions_ar tr
460 WHERE tr.transaction_id = ar.transaction_id
461 AND tr.type = 'DEPOSIT' )
462 AND thread_id = p_thread_id;
463
464
465 CURSOR get_dep_transactions
466 IS SELECT /*+ LEADING(temp) */
467 transaction_detail_id,
468 transaction_id,
469 ROWIDTOCHAR(ar.ROWID)
470 FROM hxc_temp_timecard_chunks temp,
471 hxc_dep_txn_details_ar ar
472 WHERE ar.time_building_block_id = temp.id
473 AND ar.time_building_block_ovn = temp.ref_ovn
474 AND thread_id = p_thread_id ;
475
476
477
478 CURSOR get_attributes
479 IS SELECT /*+ LEADING(temp) */
480 DISTINCT
481 time_attribute_usage_id,
482 time_attribute_id,
483 ROWIDTOCHAR(ar.ROWID)
484 FROM hxc_temp_timecard_chunks temp,
485 hxc_time_attribute_usages_ar ar
486 WHERE ar.time_building_block_id = temp.id
487 AND thread_id = p_thread_id ;
488
489 CURSOR get_tbb_rowid (p_scope VARCHAR2)
490 IS SELECT ref_rowid
491 FROM hxc_temp_timecard_chunks
492 WHERE scope = p_scope
493 AND thread_id = p_thread_id ;
494
495 CURSOR get_dup_trans
496 IS SELECT master_id,
497 MAX(ROWID)
498 FROM hxc_archive_temp
499 WHERE thread_id = p_thread_id
500 GROUP BY master_id ;
501
502 CURSOR get_latest_details
503 IS SELECT /*+ LEADING(temp) */
504 id,
505 ref_ovn,
506 ROWIDTOCHAR(temp.ROWID)
507 FROM hxc_temp_timecard_chunks temp,
508 hxc_latest_details det
509 WHERE temp.scope = 'DETAIL'
510 AND temp.id = det.time_building_block_id
511 AND thread_id = p_thread_id ;
512
513 CURSOR get_max_ovn
514 IS SELECT id,
515 MAX(ref_ovn)
516 FROM hxc_temp_timecard_chunks temp
517 WHERE scope = 'DETAIL'
518 AND thread_id = p_thread_id
519 GROUP by id ;
520
521 CURSOR pick_sec_attributes
522 IS SELECT /*+ LEADING(temp)
523 INDEX(hta HXC_TIME_ATTRIBUES_PK) */
524 id,
525 ref_ovn,
526 attribute1,
527 attribute2
528 FROM hxc_temp_timecard_chunks temp,
529 hxc_time_attribute_usages hau,
530 hxc_time_attributes hta
531 WHERE thread_id = p_thread_id
532 AND temp.id = hau.time_building_block_id
533 AND temp.ref_ovn = hau.time_building_block_ovn
534 AND hau.time_attribute_id = hta.time_attribute_id
535 AND hta.attribute_category = 'SECURITY';
536
537
538 -- Bug 9790410
539 -- Added this cursor to pick up HXT records.
540
541 CURSOR get_sum_hrs
542 IS SELECT DISTINCT
543 sum.id,
544 sum.tim_id
545 FROM hxc_temp_timecard_chunks temp,
546 hxt_sum_hours_worked_f_ar sum
547 WHERE temp.id = sum.time_building_block_id
548 AND temp.thread_id = p_thread_id;
549
550
551
552 l_chunk_size number;
553
554
555 TYPE NUMTABLE IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE;
556 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(4000);
557
558 tc_id_tab NUMTABLE;
559 tc_ovn_tab NUMTABLE;
560 tc_rowid_tab VARCHARTABLE;
561 rowid_tab VARCHARTABLE;
562 td_rowid_tab VARCHARTABLE;
563 trans_detail_tab NUMTABLE;
564 trans_tab NUMTABLE;
565 usage_tab NUMTABLE;
566 attribute_tab NUMTABLE;
567 usage_rowid_tab VARCHARTABLE;
568 uniq_rowid_tab VARCHARTABLE;
569 trans_id_tab NUMTABLE;
570
571 latest_id_tab NUMTABLE;
572 latest_ovn_Tab NUMTABLE;
573 latest_rowid_tab VARCHARTABLE;
574
575 mastertab NUMTABLE;
576 threadtab NUMTABLE;
577
578 hldidtab NUMTABLE;
579 hldovntab NUMTABLE;
580 orgtab NUMTABLE;
581 bgtab NUMTABLE;
582
583
584 -- Bug 9790410
585 sum_id_tab NUMTABLE;
586 tim_id_tab NUMTABLE;
587
588
589 l_tc_count NUMBER := 0;
590 l_day_count NUMBER := 0;
591 l_detail_count NUMBER := 0;
592 l_det_count NUMBER := 0;
593 l_app_period_count NUMBER := 0;
594 l_tau_count NUMBER := 0;
595 l_td_count NUMBER := 0;
596 l_trans_count NUMBER := 0;
597 l_tal_count NUMBER := 0;
598 l_adl_count NUMBER := 0;
599 l_app_period_sum_count NUMBER := 0;
600 l_ta_count NUMBER := 0;
601
602 l_hxt_sum_count NUMBER := 0;
603 l_hxt_det_count NUMBER := 0;
604
605
606 l_tc_del_count NUMBER := 0;
607 l_tal_del_count NUMBER := 0;
608 l_day_del_count NUMBER := 0;
609 l_det_del_count NUMBER := 0;
610 l_app_del_count NUMBER := 0;
611 l_app_sum_del_count NUMBER := 0;
612 l_tau_del_count NUMBER := 0;
613 l_ta_del_count NUMBER := 0;
614 l_td_del_count NUMBER := 0;
615 l_trans_del_count NUMBER := 0;
616 l_adl_del_count NUMBER := 0;
617 l_hxt_sum_del_count NUMBER := 0;
618 l_hxt_det_del_count NUMBER := 0;
619
620
621
622
623 l_data_set_end_date DATE;
624 l_data_set_start_date DATE;
625
626
627
628
629 data_mismatch BOOLEAN;
630 iloop NUMBER := 0;
631 l_chunk_no NUMBER := 0;
632
633
634 DEADLOCK_DETECTED EXCEPTION;
635 PRAGMA EXCEPTION_INIT(DEADLOCK_DETECTED,-60);
636
637 PROCEDURE write_data_mismatch(p_scope IN VARCHAR2)
638 IS
639
640 BEGIN
641 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
642 INSERT INTO hxc_ar_detail_log
643 (time_building_block_id,
644 object_version_number,
645 process_type,
646 thread_id,
647 chunk_number)
648 VALUES (tc_id_tab(i),
649 tc_ovn_tab(i),
650 'RESTORE-INCOMPLETE',
651 p_thread_id,
652 l_chunk_no);
653 INSERT INTO hxc_data_set_details
654 (data_set_id,
655 scope,
656 table_name,
657 row_count)
658 VALUES (p_data_set_id,
659 p_scope,
660 p_thread_id,
661 l_chunk_no );
662
663 COMMIT;
664 -- Mark the thread as failed.
665 retcode := 2;
666
667 END;
668
669
670
671 BEGIN
672
673 -- BUG 7358756
674 -- After rewrite of Archive/Restore process, this new proceduce supports the new
675 -- multithreaded design.
676 -- To have an idea of the detailed Algorithm/approach, pls refer to hxc_archive
677 -- ( hxcarchive.pkb ).
678
679
680 hr_general.g_data_migrator_mode := 'Y';
681
682 l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),50);
683
684 fnd_file.put_line(fnd_file.log,'--- > Chunk Size is: '||l_chunk_size);
685
686
687 SELECT start_date,
688 end_date
689 INTO l_data_set_start_date,
690 l_data_set_end_date
691 FROM hxc_data_sets
692 WHERE data_set_id = p_data_set_id;
693
694 OPEN get_tcs ;
695 LOOP
696
697 FETCH get_tcs
698 BULK COLLECT
699 INTO tc_id_tab,
700 tc_ovn_tab LIMIT l_chunk_size ;
701
702 EXIT WHEN tc_id_tab.COUNT = 0;
703
704 l_td_count := 0;
705 l_trans_count := 0;
706 l_tau_count := 0;
707 l_ta_count := 0;
708 data_mismatch := FALSE;
709 iloop := 1;
710 l_chunk_no := l_chunk_no + 1;
711
712 fnd_file.put_line(fnd_file.log,' ');
713 fnd_file.put_line(fnd_file.log,'*****************************************************************');
714 fnd_file.put_line(fnd_file.log,'=================================================================');
715 fnd_file.put_line(fnd_file.log,'Entering in a new chunk l_tbb_id_tab.count ');
716 fnd_file.put_line(fnd_file.log,'==================================================================');
717
718
719 << TO_CONTINUE_TO_NEXT_CHUNK >>
720 WHILE iloop = 1
721 LOOP
722 iloop := 0;
723 BEGIN
724 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
725 INSERT INTO hxc_temp_timecard_chunks
726 ( id,
727 ref_ovn,
728 scope,
729 thread_id )
730 VALUES ( tc_id_tab(i),
731 tc_ovn_tab(i),
732 'TIMECARD',
733 p_thread_id );
734
735
736
737 INSERT INTO hxc_time_building_blocks
738 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
739 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
740 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
741 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
742 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
743 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
744 TRANSLATION_DISPLAY_KEY)
745 SELECT /*+ LEADING(temp) INDEX(bkup HXC_TIME_BUILDING_BLOCKS_AR_PK)*/
746 bkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
747 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
748 bkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
749 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
750 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
751 TRANSLATION_DISPLAY_KEY
752 FROM hxc_temp_timecard_chunks temp,
753 hxc_time_building_blocks_ar bkup
754 WHERE bkup.scope = 'TIMECARD'
755 AND bkup.time_building_block_id = temp.id
756 AND bkup.object_version_number = temp.ref_ovn
757 AND bkup.data_set_id = p_data_set_id
758 AND thread_id = p_thread_id ;
759
760 l_tc_count := sql%rowcount;
761
762
763 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
764 DELETE
765 FROM hxc_time_building_blocks_ar
766 WHERE time_building_block_id = tc_id_tab(i)
767 AND object_version_number = tc_ovn_tab(i)
768 AND data_set_id = p_data_set_id ;
769
770
771 l_tc_del_count := sql%rowcount;
772
773 hxc_restore.log_data_mismatch(p_scope => 'Timecard',
774 p_insert => l_tc_count,
775 p_delete => l_tc_del_count,
776 p_mismatch => data_mismatch
777 );
778
779 IF data_mismatch
780 THEN
781 write_data_mismatch('Timecard');
782 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
783 END IF;
784
785
786 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
787 INSERT INTO hxc_ar_detail_log
788 (time_building_block_id,
789 object_version_number,
790 process_type,
791 thread_id,
792 chunk_number)
793 VALUES (tc_id_tab(i),
794 tc_ovn_tab(i),
795 'RESTORE',
796 p_thread_id,
797 l_chunk_no);
798
799 INSERT INTO hxc_temp_timecard_chunks
800 ( id,
801 scope,
802 ref_rowid,
803 thread_id )
804 SELECT /*+ ORDERED */
805 DISTINCT
806 talbkup.application_period_id,
807 'APPLICATION_PERIOD',
808 ROWIDTOCHAR(talbkup.ROWID),
809 thread_id
810 FROM hxc_temp_timecard_chunks temp,
811 hxc_tc_ap_links_ar talbkup
812 WHERE temp.id = talbkup.timecard_id
813 AND temp.scope IN ('TIMECARD')
814 AND thread_id = p_thread_id ;
815
816
817 INSERT INTO hxc_tc_ap_links
818 ( timecard_id,
819 application_period_id)
820 SELECT timecard_id,
821 application_period_id
822 FROM hxc_temp_timecard_chunks temp,
823 hxc_tc_ap_links_ar talbkup
824 WHERE temp.ref_rowid = talbkup.ROWID
825 AND temp.scope = ('APPLICATION_PERIOD')
826 AND thread_id = p_thread_id ;
827
828
829 l_tal_count := SQL%ROWCOUNT;
830
831 DELETE FROM hxc_tc_ap_links_ar
832 WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
833 FROM hxc_temp_timecard_chunks
834 WHERE scope = 'APPLICATION_PERIOD'
835 AND thread_id = p_thread_id );
836
837 l_tal_del_count := SQL%ROWCOUNT;
838
839 hxc_restore.log_data_mismatch(p_scope => 'TC App LINKs ',
840 p_insert => l_tal_count,
841 p_delete => l_tal_del_count,
842 p_mismatch => data_mismatch );
843
844 IF data_mismatch
845 THEN
846 write_data_mismatch('TC App LINKs ');
847 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
848 END IF;
849
850
851 INSERT INTO hxc_time_building_blocks
852 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
853 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
854 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
855 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
856 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
857 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
858 SELECT /*+ ORDERED */
859 appbkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
860 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
861 appbkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
862 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
863 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,
864 APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY
865 FROM hxc_time_building_blocks_ar appbkup
866 WHERE appbkup.scope = 'APPLICATION_PERIOD'
867 AND appbkup.data_set_id = p_data_set_id
868 AND appbkup.time_building_block_id IN ( SELECT id
869 FROM hxc_temp_timecard_chunks temp
870 WHERE temp.scope IN
871 ('APPLICATION_PERIOD')
872 AND thread_id = p_thread_id ) ;
873
874
875 l_app_period_count := SQL%ROWCOUNT;
876
877 DELETE FROM hxc_time_building_blocks_ar
878 WHERE time_building_block_id IN ( SELECT id
879 FROM hxc_temp_timecard_chunks
880 WHERE scope IN
881 ('APPLICATION_PERIOD')
882 AND thread_id = p_thread_id )
883 AND data_set_id = p_data_set_id ;
884
885 l_app_del_count := SQL%ROWCOUNT;
886
887 hxc_restore.log_data_mismatch( p_scope => 'Application Period ',
888 p_insert => l_app_period_count,
889 p_delete => l_app_del_count,
890 p_mismatch => data_mismatch );
891
892 IF data_mismatch
893 THEN
894 write_data_mismatch('Application Period ');
895 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
896 END IF;
897
898
899 INSERT INTO hxc_app_period_summary
900 (APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,TIME_RECIPIENT_ID,
901 TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,RECIPIENT_SEQUENCE,
902 CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,APPROVER_ID,APPROVAL_COMP_ID,
903 APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,APPROVAL_ITEM_KEY,DATA_SET_ID)
904 SELECT APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,
905 TIME_RECIPIENT_ID,TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,
906 RECIPIENT_SEQUENCE,CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,
907 APPROVER_ID,APPROVAL_COMP_ID,APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,
908 APPROVAL_ITEM_KEY,apsbkup.DATA_SET_ID
909 FROM hxc_app_period_summary_ar apsbkup
910 WHERE application_period_id IN (SELECT id
911 FROM hxc_temp_timecard_chunks
912 WHERE scope = 'APPLICATION_PERIOD'
913 AND thread_id = p_thread_id )
914 AND data_set_id = p_data_set_id ;
915
916 l_app_period_sum_count := SQL%ROWCOUNT;
917
918
919 DELETE FROM hxc_app_period_summary_ar
920 WHERE application_period_id IN (SELECT id
921 FROM hxc_temp_timecard_chunks
922 WHERE scope = 'APPLICATION_PERIOD'
923 AND thread_id = p_thread_id )
924 AND data_set_id = p_data_set_id ;
925
926 l_app_sum_del_count := SQL%ROWCOUNT;
927
928 hxc_restore.log_data_mismatch( p_scope => 'App Period Summary ',
929 p_insert => l_app_period_sum_count,
930 p_delete => l_app_sum_del_count,
931 p_mismatch => data_mismatch );
932
933 IF data_mismatch
934 THEN
935 write_data_mismatch('App Period Summary ');
936 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
937 END IF;
938
939 INSERT INTO hxc_temp_timecard_chunks
940 ( id,
941 ref_ovn,
942 scope,
943 ref_rowid,
944 day_start_time,
945 day_stop_time,
946 thread_id )
947 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BLDNG_BLOCKS_AR_FK3) */
948 time_building_block_id,
949 object_version_number,
950 'DAY',
951 ROWIDTOCHAR(ar.ROWID),
952 ar.start_time,
953 ar.stop_time,
954 thread_id
955 FROM hxc_temp_timecard_chunks temp,
956 hxc_time_building_blocks_ar ar
957 WHERE parent_building_block_id = temp.id
958 AND parent_building_block_ovn = temp.ref_ovn
959 AND temp.scope = 'TIMECARD'
960 AND thread_id = p_thread_id
961 AND ar.data_set_id = p_data_set_id ;
962
963
964 INSERT INTO hxc_time_building_blocks
965 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
966 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
967 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
968 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
969 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
970 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
971 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
972 bkupday.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
973 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
974 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
975 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
976 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
977 TRANSLATION_DISPLAY_KEY
978 FROM hxc_temp_timecard_chunks temp,
979 hxc_time_building_blocks_ar bkupday
980 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
981 AND temp.scope = 'DAY'
982 AND thread_id = p_thread_id
983 AND bkupday.data_set_id = p_data_set_id ;
984
985
986 l_day_count := sql%rowcount;
987
988
989 l_day_del_count := 0;
990 OPEN get_tbb_rowid('DAY');
991 LOOP
992 FETCH get_tbb_rowid
993 BULK COLLECT INTO
994 rowid_tab LIMIT 501;
995
996 EXIT WHEN rowid_tab.COUNT = 0;
997
998 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
999 DELETE FROM hxc_time_building_blocks_ar
1000 WHERE ROWID = CHARTOROWID(rowid_tab(i))
1001 AND data_set_id = p_data_set_id;
1002
1003 l_day_del_count := l_day_del_count + SQL%ROWCOUNT;
1004 rowid_tab.DELETE;
1005
1006 END LOOP;
1007 CLOSE get_tbb_rowid;
1008
1009 hxc_restore.log_data_mismatch( p_scope => 'Day ',
1010 p_insert => l_day_count,
1011 p_delete => l_day_del_count,
1012 p_mismatch => data_mismatch );
1013
1014 IF data_mismatch
1015 THEN
1016 write_data_mismatch('Day ');
1017 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1018 END IF;
1019
1020 INSERT INTO hxc_temp_timecard_chunks
1021 ( id,
1022 ref_ovn,
1023 scope,
1024 day_start_time,
1025 day_stop_time,
1026 ref_rowid,
1027 thread_id )
1028 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BLDNG_BLOCKS_AR_FK3) */
1029 time_building_block_id,
1030 object_version_number,
1031 'DETAIL',
1032 nvl(day_start_time,ar.start_time),
1033 nvl(day_stop_time,ar.stop_time),
1034 ROWIDTOCHAR(ar.ROWID),
1035 thread_id
1036 FROM hxc_temp_timecard_chunks temp,
1037 hxc_time_building_blocks_ar ar
1038 WHERE parent_building_block_id = temp.id
1039 AND parent_building_block_ovn = temp.ref_ovn
1040 AND temp.scope = 'DAY'
1041 AND thread_id = p_thread_id
1042 AND ar.data_set_id = p_data_set_id;
1043
1044
1045 INSERT INTO hxc_time_building_blocks
1046 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1047 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1048 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1049 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
1050 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
1051 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
1052 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
1053 bkupday.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1054 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1055 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1056 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
1057 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
1058 TRANSLATION_DISPLAY_KEY
1059 FROM hxc_temp_timecard_chunks temp,
1060 hxc_time_building_blocks_ar bkupday
1061 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
1062 AND temp.scope = 'DETAIL'
1063 AND thread_id = p_thread_id
1064 AND bkupday.data_set_id = p_data_set_id;
1065
1066
1067 l_det_count := sql%rowcount;
1068
1069 l_det_del_count := 0;
1070 OPEN get_tbb_rowid('DETAIL');
1071 LOOP
1072 FETCH get_tbb_rowid
1073 BULK COLLECT INTO
1074 rowid_tab LIMIT 501;
1075
1076 EXIT WHEN rowid_tab.COUNT = 0;
1077
1078 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
1079 DELETE FROM hxc_time_building_blocks_ar
1080 WHERE ROWID = CHARTOROWID(rowid_tab(i))
1081 AND data_set_id = p_data_set_id;
1082
1083 l_det_del_count := l_det_del_count + SQL%ROWCOUNT;
1084 rowid_tab.DELETE;
1085 END LOOP;
1086 CLOSE get_tbb_rowid;
1087
1088 hxc_restore.log_data_mismatch( p_scope => 'Detail ',
1089 p_insert => l_det_count,
1090 p_delete => l_det_del_count,
1091 p_mismatch => data_mismatch );
1092
1093 IF data_mismatch
1094 THEN
1095 write_data_mismatch('Detail ');
1096 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1097 END IF;
1098
1099 l_td_count := 0;
1100 l_td_del_count := 0;
1101 l_trans_count := 0;
1102 l_trans_del_count := 0;
1103 IF get_transactions%ISOPEN
1104 THEN
1105 CLOSE get_transactions;
1106 END IF;
1107 OPEN get_transactions ;
1108 LOOP
1109
1110 FETCH get_transactions
1111 BULK COLLECT INTO trans_detail_tab,
1112 trans_tab,
1113 td_rowid_tab LIMIT 250;
1114
1115 EXIT WHEN trans_detail_tab.COUNT = 0 ;
1116
1117 FORALL i IN trans_detail_tab.FIRST..trans_detail_tab.LAST
1118 INSERT INTO hxc_archive_temp
1119 ( detail_id,
1120 master_id,
1121 ref_rowid,
1122 thread_id )
1123 VALUES ( trans_detail_tab(i),
1124 trans_tab(i),
1125 td_rowid_tab(i),
1126 p_thread_id );
1127
1128 INSERT INTO hxc_transaction_details
1129 (DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1130 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1131 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
1132 SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
1133 bkuptxnd.DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1134 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1135 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
1136 FROM hxc_archive_temp temp,
1137 hxc_transaction_details_ar bkuptxnd
1138 WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
1139 AND thread_id = p_thread_id
1140 AND bkuptxnd.data_set_id = p_data_set_id;
1141
1142
1143 l_td_count := l_td_count + SQL%ROWCOUNT;
1144
1145
1146 FORALL i IN td_rowid_tab.FIRST..td_rowid_tab.LAST
1147 DELETE FROM hxc_transaction_details_ar
1148 WHERE ROWID = CHARTOROWID(td_rowid_tab(i))
1149 AND data_set_id = p_data_set_id ;
1150
1151
1152 l_td_del_count := l_td_del_count + SQL%ROWCOUNT;
1153
1154 trans_detail_tab.DELETE;
1155 trans_tab.DELETE;
1156 td_rowid_tab.DELETE;
1157
1158 OPEN get_dup_trans;
1159 LOOP
1160 FETCH get_dup_trans
1161 BULK COLLECT INTO trans_id_tab,
1162 uniq_rowid_tab LIMIT 500;
1163
1164 EXIT WHEN trans_id_tab.COUNT = 0;
1165
1166 FORALL i IN trans_id_tab.FIRST..trans_id_tab.LAST
1167 DELETE FROM hxc_archive_temp
1168 WHERE master_id = trans_id_tab(i)
1169 AND ROWID <> uniq_rowid_tab(i)
1170 AND thread_id = p_thread_id ;
1171 END LOOP;
1172 CLOSE get_dup_trans;
1173
1174 -- Bug 11781607
1175 -- No longer required since this should be handled by the Parent
1176 -- process.
1177 /*
1178
1179 DELETE FROM hxc_archive_temp
1180 WHERE EXISTS ( SELECT 1
1181 FROM hxc_transactions
1182 WHERE transaction_id = master_id )
1183 AND thread_id = p_thread_id ;
1184 */
1185
1186 -- Bug 8888813
1187 -- Commented out handling of transaction_ids because this
1188 -- construct is no longer handling DEPOSIT transactions, and
1189 -- transactions of RETRIEVAL details handled here are handled
1190 -- in the Parent process.
1191 /*
1192 INSERT INTO hxc_transactions
1193 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
1194 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1195 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
1196 SELECT /*+ LEADING(temp) USE_NL(bkuptxn) *
1197 bkuptxn.DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
1198 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
1199 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1200 TRANSACTION_CODE
1201 FROM hxc_transactions_ar bkuptxn,
1202 hxc_archive_temp temp
1203 WHERE transaction_id = master_id
1204 AND thread_id = p_thread_id
1205 AND transaction_id NOT IN ( SELECT transaction_id
1206 FROM hxc_transactions hxc
1207 WHERE bkuptxn.transaction_id = hxc.transaction_id)
1208 AND type <> 'RETRIEVAL'
1209 AND bkuptxn.data_set_id = p_data_set_id
1210 ;
1211
1212
1213 l_trans_count := l_trans_count + SQL%ROWCOUNT;
1214
1215 DELETE /*+ LEADING(temp) USE_NL(bkuptxn) *
1216 FROM hxc_transactions_ar bkuptxn
1217 WHERE transaction_id IN ( SELECT master_id
1218 FROM hxc_archive_temp temp
1219 WHERE thread_id = p_thread_id)
1220 AND type <> 'RETRIEVAL'
1221 AND data_set_id = p_data_set_id ;
1222
1223 l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1224 */
1225
1226
1227 -- Bug 11781607
1228 -- Removed data set id condition to take care of
1229 -- transactions spanning data sets.
1230
1231 INSERT INTO hxc_ar_trans_temp
1232 ( transaction_id, data_set_id, thread_id, trans_rowid )
1233 SELECT bkuptxn.transaction_id,
1234 p_data_set_id,
1235 p_thread_id,
1236 ROWIDTOCHAR(bkuptxn.ROWID)
1237 FROM hxc_transactions_ar bkuptxn,
1238 hxc_archive_temp temp
1239 WHERE transaction_id = master_id
1240 AND thread_id = p_thread_id
1241 AND type = 'RETRIEVAL';
1242
1243
1244
1245 DELETE FROM hxc_archive_temp
1246 WHERE thread_id = p_thread_id ;
1247
1248 END LOOP;
1249
1250 CLOSE get_transactions;
1251
1252 hxc_restore.log_data_mismatch( p_scope => 'Transaction Detail ',
1253 p_insert => l_td_count,
1254 p_delete => l_td_del_count,
1255 p_mismatch => data_mismatch );
1256
1257 IF data_mismatch
1258 THEN
1259 write_data_mismatch('Transaction Detail ');
1260 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1261 END IF;
1262
1263
1264 l_td_count := 0;
1265 l_td_del_count := 0;
1266 l_trans_count := 0;
1267 l_trans_del_count := 0;
1268
1269
1270 -- Bug 8888813
1271 -- Added the below two cursor processing to pick up DEPOSIT transactions.
1272 -- hxc_transaction_details_ar, and hxc_dep_txn_details_ar are scanned here.
1273 -- so that the process works fine irrespective of the upgrade.
1274
1275 IF get_old_transactions%ISOPEN
1276 THEN
1277 CLOSE get_old_transactions;
1278 END IF;
1279 OPEN get_old_transactions ;
1280 LOOP
1281
1282 FETCH get_old_transactions
1283 BULK COLLECT INTO trans_detail_tab,
1284 trans_tab,
1285 td_rowid_tab LIMIT 250;
1286
1287 EXIT WHEN trans_detail_tab.COUNT = 0 ;
1288
1289 FORALL i IN trans_detail_tab.FIRST..trans_detail_tab.LAST
1290 INSERT INTO hxc_archive_temp
1291 ( detail_id,
1292 master_id,
1293 ref_rowid,
1294 thread_id )
1295 VALUES ( trans_detail_tab(i),
1296 trans_tab(i),
1297 td_rowid_tab(i),
1298 p_thread_id );
1299
1300 INSERT INTO hxc_dep_transaction_details
1301 (DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1302 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1303 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
1304 SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
1305 bkuptxnd.DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1306 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1307 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
1308 FROM hxc_archive_temp temp,
1309 hxc_transaction_details_ar bkuptxnd
1310 WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
1311 AND thread_id = p_thread_id
1312 AND bkuptxnd.data_set_id = p_data_set_id;
1313
1314
1315 l_td_count := l_td_count + SQL%ROWCOUNT;
1316
1317
1318 FORALL i IN td_rowid_tab.FIRST..td_rowid_tab.LAST
1319 DELETE FROM hxc_transaction_details_ar
1320 WHERE ROWID = CHARTOROWID(td_rowid_tab(i))
1321 AND data_set_id = p_data_set_id ;
1322
1323
1324 l_td_del_count := l_td_del_count + SQL%ROWCOUNT;
1325
1326 trans_detail_tab.DELETE;
1327 trans_tab.DELETE;
1328 td_rowid_tab.DELETE;
1329
1330 OPEN get_dup_trans;
1331 LOOP
1332 FETCH get_dup_trans
1333 BULK COLLECT INTO trans_id_tab,
1334 uniq_rowid_tab LIMIT 500;
1335
1336 EXIT WHEN trans_id_tab.COUNT = 0;
1337
1338 FORALL i IN trans_id_tab.FIRST..trans_id_tab.LAST
1339 DELETE FROM hxc_archive_temp
1340 WHERE master_id = trans_id_tab(i)
1341 AND ROWID <> uniq_rowid_tab(i)
1342 AND thread_id = p_thread_id ;
1343 END LOOP;
1344 CLOSE get_dup_trans;
1345
1346 DELETE FROM hxc_archive_temp
1347 WHERE EXISTS ( SELECT 1
1348 FROM hxc_transactions
1349 WHERE transaction_id = master_id )
1350 AND thread_id = p_thread_id ;
1351
1352 INSERT INTO hxc_dep_transactions
1353 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
1354 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1355 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
1356 SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
1357 bkuptxn.DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
1358 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
1359 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1360 TRANSACTION_CODE
1361 FROM hxc_transactions_ar bkuptxn,
1362 hxc_archive_temp temp
1363 WHERE transaction_id = master_id
1364 AND thread_id = p_thread_id
1365 AND transaction_id NOT IN ( SELECT transaction_id
1366 FROM hxc_dep_transactions hxc
1367 WHERE bkuptxn.transaction_id = hxc.transaction_id)
1368 AND type = 'DEPOSIT'
1369 AND bkuptxn.data_set_id = p_data_set_id
1370 ;
1371
1372 l_trans_count := l_trans_count + SQL%ROWCOUNT;
1373
1374 DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
1375 FROM hxc_transactions_ar bkuptxn
1376 WHERE transaction_id IN ( SELECT master_id
1377 FROM hxc_archive_temp temp
1378 WHERE thread_id = p_thread_id)
1379 AND type = 'DEPOSIT'
1380 AND data_set_id = p_data_set_id ;
1381
1382 l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1383
1384
1385 DELETE FROM hxc_archive_temp
1386 WHERE thread_id = p_thread_id ;
1387
1388 END LOOP;
1389
1390 CLOSE get_old_transactions;
1391
1392 IF get_dep_transactions%ISOPEN
1393 THEN
1394 CLOSE get_dep_transactions;
1395 END IF;
1396 OPEN get_dep_transactions ;
1397 LOOP
1398
1399 FETCH get_dep_transactions
1400 BULK COLLECT INTO trans_detail_tab,
1401 trans_tab,
1402 td_rowid_tab LIMIT 250;
1403
1404 EXIT WHEN trans_detail_tab.COUNT = 0 ;
1405
1406 FORALL i IN trans_detail_tab.FIRST..trans_detail_tab.LAST
1407 INSERT INTO hxc_archive_temp
1408 ( detail_id,
1409 master_id,
1410 ref_rowid,
1411 thread_id )
1412 VALUES ( trans_detail_tab(i),
1413 trans_tab(i),
1414 td_rowid_tab(i),
1415 p_thread_id );
1416
1417 INSERT INTO hxc_dep_transaction_details
1418 (DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1419 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1420 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
1421 SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
1422 bkuptxnd.DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1423 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1424 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
1425 FROM hxc_archive_temp temp,
1426 hxc_dep_txn_details_ar bkuptxnd
1427 WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
1428 AND thread_id = p_thread_id
1429 AND bkuptxnd.data_set_id = p_data_set_id;
1430
1431
1432 l_td_count := l_td_count + SQL%ROWCOUNT;
1433
1434
1435 FORALL i IN td_rowid_tab.FIRST..td_rowid_tab.LAST
1436 DELETE FROM hxc_dep_txn_details_ar
1437 WHERE ROWID = CHARTOROWID(td_rowid_tab(i))
1438 AND data_set_id = p_data_set_id ;
1439
1440
1441 l_td_del_count := l_td_del_count + SQL%ROWCOUNT;
1442
1443 trans_detail_tab.DELETE;
1444 trans_tab.DELETE;
1445 td_rowid_tab.DELETE;
1446
1447 OPEN get_dup_trans;
1448 LOOP
1449 FETCH get_dup_trans
1450 BULK COLLECT INTO trans_id_tab,
1451 uniq_rowid_tab LIMIT 500;
1452
1453 EXIT WHEN trans_id_tab.COUNT = 0;
1454
1455 FORALL i IN trans_id_tab.FIRST..trans_id_tab.LAST
1456 DELETE FROM hxc_archive_temp
1457 WHERE master_id = trans_id_tab(i)
1458 AND ROWID <> uniq_rowid_tab(i)
1459 AND thread_id = p_thread_id ;
1460 END LOOP;
1461 CLOSE get_dup_trans;
1462
1463 DELETE FROM hxc_archive_temp
1464 WHERE EXISTS ( SELECT 1
1465 FROM hxc_transactions
1466 WHERE transaction_id = master_id )
1467 AND thread_id = p_thread_id ;
1468
1469
1470 INSERT INTO hxc_dep_transactions
1471 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
1472 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1473 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
1474 SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
1475 bkuptxn.DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
1476 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
1477 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1478 TRANSACTION_CODE
1479 FROM hxc_dep_transactions_ar bkuptxn,
1480 hxc_archive_temp temp
1481 WHERE transaction_id = master_id
1482 AND thread_id = p_thread_id
1483 AND transaction_id NOT IN ( SELECT transaction_id
1484 FROM hxc_dep_transactions hxc
1485 WHERE bkuptxn.transaction_id = hxc.transaction_id)
1486 AND bkuptxn.data_set_id = p_data_set_id
1487 ;
1488
1489
1490 l_trans_count := l_trans_count + SQL%ROWCOUNT;
1491
1492 DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
1493 FROM hxc_dep_transactions_ar bkuptxn
1494 WHERE transaction_id IN ( SELECT master_id
1495 FROM hxc_archive_temp temp
1496 WHERE thread_id = p_thread_id)
1497 AND data_set_id = p_data_set_id ;
1498
1499 l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1500
1501 DELETE FROM hxc_archive_temp
1502 WHERE thread_id = p_thread_id ;
1503
1504 END LOOP;
1505
1506 CLOSE get_dep_transactions;
1507 hxc_restore.log_data_mismatch( p_scope => 'Deposit Transaction ',
1508 p_insert => l_trans_count,
1509 p_delete => l_trans_del_count,
1510 p_mismatch => data_mismatch );
1511
1512
1513 IF data_mismatch
1514 THEN
1515 write_data_mismatch('Deposit Transaction ');
1516 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1517 END IF;
1518
1519
1520 hxc_restore.log_data_mismatch( p_scope => 'Deposit Transaction Detail ',
1521 p_insert => l_td_count,
1522 p_delete => l_td_del_count,
1523 p_mismatch => data_mismatch );
1524
1525 IF data_mismatch
1526 THEN
1527 write_data_mismatch('Deposit Transaction Detail ');
1528 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1529 END IF;
1530
1531
1532 l_ta_count := 0;
1533 l_ta_del_count := 0;
1534 l_tau_count := 0;
1535 l_tau_del_count := 0;
1536
1537 IF get_attributes%ISOPEN
1538 THEN
1539 CLOSE get_attributes;
1540 END IF;
1541 OPEN get_attributes;
1542
1543 LOOP
1544 FETCH get_attributes
1545 BULK COLLECT INTO usage_tab,
1546 attribute_tab,
1547 usage_rowid_tab LIMIT 250;
1548
1549 EXIT WHEN usage_tab.COUNT = 0;
1550
1551
1552 FORALL i IN usage_tab.FIRST..usage_tab.LAST
1553 INSERT INTO hxc_archive_temp
1554 ( detail_id,
1555 master_id,
1556 ref_rowid,
1557 thread_id)
1558 VALUES ( usage_tab(i),
1559 attribute_tab(i),
1560 usage_rowid_tab(i),
1561 p_thread_id );
1562
1563
1564 INSERT INTO hxc_time_attribute_usages
1565 (DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1566 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1567 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN)
1568 SELECT /*+ LEADING(temp) USE_NL(bkuptau) */
1569 bkuptau.DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1570 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1571 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN
1572 FROM hxc_archive_temp temp,
1573 hxc_time_attribute_usages_ar bkuptau
1574 WHERE bkuptau.ROWID = CHARTOROWID(temp.ref_rowid)
1575 AND thread_id = p_thread_id
1576 AND bkuptau.data_set_id = p_data_set_id;
1577
1578
1579 l_tau_count := l_tau_count + SQL%ROWCOUNT;
1580
1581 FORALL i IN usage_rowid_tab.FIRST..usage_rowid_tab.LAST
1582 DELETE FROM hxc_time_attribute_usages_ar
1583 WHERE ROWID = CHARTOROWID(usage_rowid_tab(i))
1584 AND data_set_id = p_data_set_id;
1585
1586 l_tau_del_count := l_tau_del_count + SQL%ROWCOUNT;
1587
1588 DELETE FROM hxc_archive_temp
1589 WHERE EXISTS ( SELECT 1
1590 FROM hxc_time_attributes
1591 WHERE time_attribute_id = master_id )
1592 AND thread_id = p_thread_id ;
1593
1594 INSERT INTO hxc_time_attributes
1595 (ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1596 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1597 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1598 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1599 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1600 ATTRIBUTE14,CONSOLIDATED_FLAG,DATA_SET_ID)
1601 SELECT /*+ LEADING(temp) USE_NL(bkupta) */
1602 ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1603 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1604 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1605 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1606 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1607 ATTRIBUTE14,null,DATA_SET_ID
1608 FROM hxc_time_attributes_ar bkupta
1609 WHERE bkupta.time_attribute_id in ( SELECT /*+ NO_INDEX(temp) */
1610 master_id
1611 FROM hxc_archive_temp temp
1612 WHERE thread_id = p_thread_id )
1613 AND bkupta.data_set_id = p_data_set_id;
1614
1615
1616 l_ta_count := l_ta_count + SQL%ROWCOUNT;
1617
1618 DELETE /*+ LEADING(temp) USE_NL(bkupta) */
1619 FROM hxc_time_attributes_ar bkupta
1620 WHERE time_attribute_id IN ( SELECT /*+ NO_INDEX(temp) */
1621 master_id
1622 FROM hxc_archive_temp temp
1623 WHERE thread_id = p_thread_id )
1624 AND data_set_id = p_data_set_id ;
1625
1626 l_ta_del_count := l_ta_del_count + SQL%ROWCOUNT;
1627
1628 usage_tab.DELETE;
1629 attribute_tab.DELETE;
1630 usage_rowid_tab.DELETE;
1631
1632 DELETE FROM hxc_archive_temp
1633 WHERE thread_id = p_thread_id ;
1634
1635 END LOOP ;
1636 CLOSE get_attributes;
1637
1638 hxc_restore.log_data_mismatch( p_scope => 'Attribute Usages ',
1639 p_insert => l_tau_count,
1640 p_delete => l_tau_del_count,
1641 p_mismatch => data_mismatch );
1642
1643 IF data_mismatch
1644 THEN
1645 write_data_mismatch('Attribute Usages ');
1646 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1647 END IF;
1648
1649 hxc_restore.log_data_mismatch( p_scope => 'Attributes ',
1650 p_insert => l_ta_count,
1651 p_delete => l_ta_del_count,
1652 p_mismatch => data_mismatch );
1653
1654 IF data_mismatch
1655 THEN
1656 write_data_mismatch('Attributes ');
1657 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1658 END IF;
1659
1660 DELETE FROM hxc_temp_timecard_chunks
1661 WHERE scope IN ( 'TIMECARD', 'DAY','APPLICATION_PERIOD')
1662 AND thread_id = p_thread_id ;
1663
1664 -- Bug 9790410
1665 -- Added the below construct to process OTLR records.
1666
1667 OPEN get_sum_hrs;
1668 LOOP
1669 FETCH get_sum_hrs BULK COLLECT INTO sum_id_tab,
1670 tim_id_tab LIMIT 500;
1671
1672
1673 EXIT WHEN sum_id_tab.COUNT = 0;
1674
1675
1676 FORALL i IN sum_id_tab.FIRST..sum_id_tab.LAST
1677 INSERT INTO hxc_archive_temp
1678 ( detail_id,
1679 master_id,
1680 thread_id)
1681 VALUES ( sum_id_tab(i),
1682 tim_id_tab(i),
1683 p_thread_id);
1684
1685 INSERT INTO hxt_sum_hours_worked_f
1686 ( state_name, county_name, city_name, zip_code, ffv_cost_center_id,
1687 ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
1688 ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
1689 separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1690 last_update_date, last_update_login, actual_time_in, actual_time_out,
1691 job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
1692 effective_end_date, attribute_category, attribute1, attribute2, attribute3,
1693 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
1694 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
1695 attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,
1696 attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,
1697 attribute28, attribute29, attribute30, object_version_number, time_building_block_id,
1698 time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
1699 hours, time_in, time_out, element_type_id, fcl_earn_reason_code)
1700 SELECT DISTINCT state_name, county_name, city_name, zip_code, ffv_cost_center_id,
1701 ffv_labor_account_id, tas_id, location_id, sht_id, hrw_comment,
1702 ffv_rate_code_id, rate_multiple, hourly_rate, amount, fcl_tax_rule_code,
1703 separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1704 last_update_date, last_update_login, actual_time_in, actual_time_out,
1705 job_id, earn_pol_id, project_id, prev_wage_code, effective_start_date,
1706 effective_end_date, attribute_category, attribute1, attribute2, attribute3,
1707 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
1708 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15,
1709 attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,
1710 attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,
1711 attribute28, attribute29, attribute30, object_version_number, time_building_block_id,
1712 time_building_block_ovn, id, line_status, tim_id, date_worked, assignment_id,
1713 hours, time_in, time_out, element_type_id, fcl_earn_reason_code
1714 FROM hxt_sum_hours_worked_f_ar sum,
1715 hxc_archive_temp temp
1716 WHERE temp.detail_id = sum.id
1717 AND temp.thread_id = p_thread_id;
1718
1719
1720 l_hxt_sum_count := l_hxt_sum_count + SQL%ROWCOUNT;
1721
1722 FORALL i IN sum_id_tab.FIRST..sum_id_tab.LAST
1723 DELETE FROM hxt_sum_hours_worked_f_ar
1724 WHERE id = sum_id_tab(i);
1725
1726 l_hxt_sum_del_count := l_hxt_sum_del_count + SQL%ROWCOUNT;
1727
1728 INSERT INTO hxt_det_hours_worked_f
1729 ( state_name, county_name, city_name, zip_code, id, parent_id, line_status,
1730 tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
1731 fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
1732 sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
1733 fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1734 last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
1735 effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
1736 prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number )
1737 SELECT DISTINCT
1738 state_name, county_name, city_name, zip_code, id, parent_id, line_status,
1739 tim_id, date_worked, assignment_id, hours, time_in, time_out, element_type_id,
1740 fcl_earn_reason_code, ffv_cost_center_id, ffv_labor_account_id, tas_id, location_id,
1741 sht_id, hrw_comment, ffv_rate_code_id, rate_multiple, hourly_rate, amount,
1742 fcl_tax_rule_code, separate_check_flag, seqno, created_by, creation_date, last_updated_by,
1743 last_update_date, last_update_login, actual_time_in, actual_time_out, job_id, earn_pol_id,
1744 effective_start_date, effective_end_date, pbl_line_id, retro_pbl_line_id, project_id,
1745 prev_wage_code, pa_status, pay_status, retro_batch_id, object_version_number
1746 FROM hxc_archive_temp temp,
1747 hxt_det_hours_worked_f_ar det
1748 WHERE temp.detail_id = det.parent_id
1749 AND temp.thread_id = p_thread_id;
1750
1751 l_hxt_det_count := l_hxt_det_count + SQL%ROWCOUNT;
1752
1753
1754 FORALL i IN sum_id_tab.FIRST..sum_id_tab.LAST
1755 DELETE FROM hxt_det_hours_worked_f_ar
1756 WHERE parent_id = sum_id_tab(i);
1757
1758 l_hxt_det_del_count := l_hxt_det_del_count + SQL%ROWCOUNT;
1759
1760 FORALL i IN tim_id_tab.FIRST..tim_id_tab.LAST
1761 UPDATE hxt_timecards_f
1762 SET data_set_id = p_data_set_id
1763 WHERE id = tim_id_tab(i);
1764
1765 DELETE FROM hxc_archive_temp
1766 WHERE thread_id = p_thread_id;
1767
1768 END LOOP;
1769 CLOSE get_sum_hrs;
1770
1771
1772 hxc_archive.log_data_mismatch( p_scope => 'OTLR Summary ',
1773 p_insert => l_hxt_sum_count,
1774 p_delete => l_hxt_sum_del_count,
1775 p_mismatch => data_mismatch );
1776 IF data_mismatch
1777 THEN
1778 write_data_mismatch('OTLR Summary ');
1779 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1780 END IF;
1781
1782 hxc_archive.log_data_mismatch( p_scope => 'OTLR Details',
1783 p_insert => l_hxt_det_count,
1784 p_delete => l_hxt_det_del_count,
1785 p_mismatch => data_mismatch );
1786
1787 IF data_mismatch
1788 THEN
1789 write_data_mismatch('OTLR Details');
1790 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1791 END IF;
1792
1793
1794
1795 INSERT INTO hxc_ap_detail_links
1796 (application_period_id,
1797 time_building_block_id,
1798 time_building_block_ovn)
1799 SELECT /*+ INDEX( adlbkup HXC_AP_DETAIL_LINKS_AR_FK1) */
1800 application_period_id, time_building_block_id, time_building_block_ovn
1801 FROM hxc_temp_timecard_chunks temp,
1802 hxc_ap_detail_links_ar adlbkup
1803 WHERE temp.id = adlbkup.time_building_block_id
1804 AND temp.ref_ovn = adlbkup.time_building_block_ovn
1805 AND temp.scope = ('DETAIL')
1806 AND thread_id = p_thread_id ;
1807
1808 l_adl_count := SQL%ROWCOUNT;
1809
1810
1811 DELETE FROM hxc_ap_detail_links_ar
1812 WHERE (time_building_block_id,time_building_block_ovn)
1813 IN ( SELECT id,
1814 ref_ovn
1815 FROM hxc_temp_timecard_chunks
1816 WHERE scope = 'DETAIL'
1817 AND thread_id = p_thread_id );
1818 l_adl_del_count := SQL%ROWCOUNT;
1819
1820 hxc_restore.log_data_mismatch( p_scope => 'App Detail LINKs ',
1821 p_insert => l_adl_count,
1822 p_delete => l_adl_del_count,
1823 p_mismatch => data_mismatch );
1824
1825 IF data_mismatch
1826 THEN
1827 write_data_mismatch('App Detail LINKs ');
1828 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1829 END IF;
1830
1831 OPEN get_latest_details;
1832 LOOP
1833 FETCH get_latest_details
1834 BULK COLLECT INTO latest_id_tab,
1835 latest_ovn_tab,
1836 latest_rowid_tab LIMIT 500;
1837 EXIT WHEN latest_id_tab.COUNT = 0;
1838
1839 FORALL i IN latest_id_Tab.FIRST..latest_id_Tab.last
1840 DELETE FROM hxc_temp_timecard_chunks
1841 WHERE ROWID = CHARTOROWID(latest_rowid_tab(i))
1842 AND thread_id = p_thread_id ;
1843
1844 latest_rowid_tab.DELETE;
1845 latest_id_tab.DELETE;
1846 latest_ovn_tab.DELETE;
1847 END LOOP;
1848 CLOSE get_latest_details;
1849
1850 OPEN get_max_ovn;
1851 LOOP
1852 FETCH get_max_ovn
1853 BULK COLLECT INTO latest_id_tab,
1854 latest_ovn_tab
1855 LIMIT 500;
1856 EXIT WHEN latest_id_tab.count = 0;
1857 FORALL i IN latest_id_tab.first..latest_id_tab.last
1858 DELETE FROM hxc_temp_timecard_chunks
1859 WHERE id = latest_id_tab(i)
1860 AND ref_ovn <> latest_ovn_tab(i)
1861 AND thread_id = p_thread_id ;
1862
1863 latest_rowid_tab.DELETE;
1864 latest_id_tab.DELETE;
1865 latest_ovn_tab.DELETE;
1866
1867 END LOOP;
1868 CLOSE get_max_ovn;
1869
1870
1871 INSERT INTO hxc_latest_details
1872 (resource_id,
1873 time_building_block_id,
1874 object_version_number,
1875 approval_status,
1876 application_set_id,
1877 last_update_date,
1878 resource_type,
1879 comment_text,
1880 start_time,
1881 stop_time )
1882 SELECT /*+ ORDERED */
1883 resource_id,
1884 time_building_block_id,
1885 object_version_number,
1886 approval_status,
1887 application_set_id,
1888 last_update_date,
1889 resource_type,
1890 comment_text,
1891 day_start_time,
1892 day_stop_time
1893 FROM hxc_temp_timecard_chunks temp,
1894 hxc_time_building_blocks hxc
1895 WHERE temp.id = hxc.time_building_block_id
1896 AND temp.ref_ovn = hxc.object_version_number
1897 AND temp.scope = 'DETAIL'
1898 AND thread_id = p_thread_id ;
1899
1900
1901 OPEN pick_sec_attributes;
1902 LOOP
1903 FETCH pick_sec_attributes
1904 BULK COLLECT INTO hldidtab,
1905 hldovntab,
1906 orgtab,
1907 bgtab LIMIT 1000;
1908 EXIT WHEN orgtab.COUNT =0;
1909
1910 FORALL i IN orgtab.FIRST..orgtab.LAST
1911 UPDATE hxc_latest_details
1912 SET org_id = orgtab(i),
1913 business_group_id = bgtab(i)
1914 WHERE time_building_block_id = hldidtab(i)
1915 AND object_version_number = hldovntab(i);
1916 END LOOP;
1917 CLOSE pick_sec_attributes;
1918
1919 hldidtab.DELETE;
1920 hldovntab.DELETE;
1921 orgtab.DELETE;
1922 bgtab.DELETE;
1923
1924
1925 fnd_file.put_line(fnd_file.log,' ');
1926 fnd_file.put_line(fnd_file.log,' ');
1927 fnd_file.put_line(fnd_file.log,' ');
1928 fnd_file.put_line(fnd_file.log,' ');
1929 fnd_file.put_line(fnd_file.log,' ');
1930
1931 fnd_file.put_line(fnd_file.log,'*****************************************');
1932 fnd_file.put_line(fnd_file.log,' ');
1933 COMMIT;
1934 tc_id_tab.DELETE;
1935 tc_ovn_tab.DELETE;
1936 iloop:= 0;
1937 EXCEPTION
1938 WHEN DUP_VAL_ON_INDEX THEN
1939 ROLLBACK;
1940 iloop := 1;
1941 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention, will sleep for a minute ');
1942 fnd_file.put_line(fnd_file.log,'=================================================================');
1943 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1944 fnd_file.put_line(fnd_file.log,'==================================================================');
1945 l_td_count := 0;
1946 l_trans_count := 0;
1947 l_tau_count := 0;
1948 l_ta_count := 0;
1949 data_mismatch := FALSE;
1950 dbms_lock.sleep(60);
1951 WHEN DEADLOCK_DETECTED THEN
1952 ROLLBACK;
1953 iloop := 1;
1954 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention(deadlock), will sleep for a minute ');
1955 fnd_file.put_line(fnd_file.log,'=================================================================');
1956 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1957 fnd_file.put_line(fnd_file.log,'==================================================================');
1958 l_td_count := 0;
1959 l_trans_count := 0;
1960 l_tau_count := 0;
1961 l_ta_count := 0;
1962 data_mismatch := FALSE;
1963 dbms_lock.sleep(60);
1964
1965 END ;
1966 END LOOP TO_CONTINUE_TO_NEXT_CHUNK;
1967 END LOOP;
1968
1969
1970
1971 END child_restore_process;
1972
1973 PROCEDURE log_data_mismatch( p_scope IN VARCHAR2,
1974 p_insert IN NUMBER,
1975 p_delete IN NUMBER,
1976 p_mismatch IN OUT NOCOPY BOOLEAN)
1977 IS
1978
1979 BEGIN
1980 IF p_insert = p_delete
1981 THEN
1982 fnd_file.put_line(fnd_file.log,' ');
1983 fnd_file.put_line(fnd_file.log,' '||p_scope||' records moved : '||p_insert);
1984 ELSE
1985 fnd_file.put_line(fnd_file.log,' ');
1986 fnd_file.put_line(fnd_file.log,'==========================================================================');
1987 fnd_file.put_line(fnd_file.log,' An error occured while processing '||p_scope||' records');
1988 fnd_file.put_line(fnd_file.log,'==========================================================================');
1989 fnd_file.put_line(fnd_file.log, p_insert||' records were inserted into online table ');
1990 fnd_file.put_line(fnd_file.log, p_delete||' records were deleted from offline table ');
1991 fnd_file.put_line(fnd_file.log,'This chunk is rolled back, pls check up data. ');
1992 p_mismatch := TRUE;
1993 ROLLBACK;
1994 END IF;
1995
1996 RETURN;
1997 END log_data_mismatch ;
1998
1999
2000
2001
2002
2003 END hxc_restore;