[Home] [Help]
PACKAGE BODY: APPS.HXC_RESTORE
Source
1 PACKAGE BODY hxc_restore AS
2 /* $Header: hxcrestore.pkb 120.0.12010000.8 2008/10/08 17:03:01 asrajago 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 INSERT INTO hxc_transactions
291 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
292 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
293 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
294 SELECT bkuptxn.DATA_SET_ID,bkuptxn.TRANSACTION_ID,TRANSACTION_PROCESS_ID,
295 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
296 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
297 TRANSACTION_CODE
298 FROM hxc_transactions_ar bkuptxn
299 WHERE bkuptxn.transaction_id IN ( SELECT temp.transaction_id
300 FROM hxc_ar_trans_temp temp
301 WHERE thread_id = 0 )
302 AND bkuptxn.transaction_id NOT IN ( SELECT transaction_id
303 FROM hxc_transactions hxc
304 WHERE bkuptxn.transaction_id = hxc.transaction_id)
305 AND bkuptxn.data_set_id = p_data_set_id
306 ;
307
308 DELETE FROM hxc_transactions_ar
309 WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
310 FROM hxc_ar_trans_temp
311 WHERE thread_id = 0 )
312 AND data_set_id = p_data_set_id ;
313
314
315 DELETE FROM hxc_ar_trans_temp
316 WHERE thread_id = 0 ;
317
318 COMMIT;
319 ELSE
320 dbms_lock.sleep(10);
321 END IF;
322 END LOOP;
323
324 trans_count := 0;
325 SELECT COUNT(1)
326 INTO trans_count
327 FROM hxc_data_set_details;
328
329
330 IF l_thread_success and trans_count = 0
331 THEN
332 UPDATE hxc_data_sets
333 SET status = 'ON_LINE', validation_status = 'E'
334 WHERE data_set_id = p_data_set_id;
335
336 ELSE
337 fnd_file.put_line(fnd_file.LOG,' There were some issues with the process');
338 fnd_file.put_line(fnd_file.LOG,' Either one of the threads failed or there is a data issue ');
339 fnd_file.put_line(fnd_file.LOG,' Please run the process once again to reprocess the failed ');
340 fnd_file.put_line(fnd_file.LOG,' timecards. ');
341 fnd_file.put_line(fnd_file.LOG,' ');
342
343 END IF;
344
345
346 COMMIT;
347
348
349
350 l_debug_info := ' ';
351 IF fnd_profile.value('HXC_AR_ENABLE_LOG') = 'Y'
352 THEN
353 fnd_file.put_line(fnd_file.LOG,'Following are the timecards processed ');
354 fnd_file.put_line(fnd_file.LOG,'======================================');
355 fnd_file.put_line(fnd_file.LOG,' ');
356 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
357 fnd_file.put_line(fnd_file.LOG,' ');
358 FOR log_rec IN get_log_details
359 LOOP
360 IF l_debug_info <> log_rec.detail
361 THEN
362 fnd_file.put_line(fnd_file.log,' ---- ');
363 l_debug_info := log_rec.detail;
364 END IF;
365 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
366 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
367 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
368 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
369 END LOOP;
370
371 l_debug_info := ' ';
372 fnd_file.put_line(fnd_file.LOG,' ');
373 fnd_file.put_line(fnd_file.LOG,'Following are the timecards not processed ');
374 fnd_file.put_line(fnd_file.LOG,'======================================');
375 fnd_file.put_line(fnd_file.LOG,' ');
376 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
377 fnd_file.put_line(fnd_file.LOG,' ');
378 FOR log_rec IN get_log_details_failed
379 LOOP
380 IF l_debug_info <> log_rec.detail
381 THEN
382 fnd_file.put_line(fnd_file.log,' ---- ');
383 l_debug_info := log_rec.detail;
384 END IF;
385 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
386 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
387 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
388 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
389 END LOOP;
390
391
392 END IF;
393
394
395 COMMIT;
396
397
398 END restore_process;
399
400
401
402
403 PROCEDURE child_restore_process ( errbuf OUT NOCOPY VARCHAR2,
404 retcode OUT NOCOPY NUMBER,
405 p_from_id IN NUMBER,
406 p_to_id IN NUMBER,
407 p_data_set_id IN NUMBER,
408 p_thread_id IN NUMBER )
409 IS
410
411
412 CURSOR get_tcs
413 IS SELECT time_building_block_id,
414 object_version_number
415 FROM hxc_ar_tc_ids_temp
416 WHERE time_building_block_id >= p_from_id
417 AND time_building_block_id < p_to_id ;
418
419 CURSOR get_transactions
420 IS SELECT /*+ LEADING(temp) */
421 transaction_detail_id,
422 transaction_id,
423 ROWIDTOCHAR(ar.ROWID)
424 FROM hxc_temp_timecard_chunks temp,
425 hxc_transaction_details_ar ar
426 WHERE ar.time_building_block_id = temp.id
427 AND ar.time_building_block_ovn = temp.ref_ovn
428 AND thread_id = p_thread_id ;
429
430
431 CURSOR get_attributes
432 IS SELECT /*+ LEADING(temp) */
433 DISTINCT
434 time_attribute_usage_id,
435 time_attribute_id,
436 ROWIDTOCHAR(ar.ROWID)
437 FROM hxc_temp_timecard_chunks temp,
438 hxc_time_attribute_usages_ar ar
439 WHERE ar.time_building_block_id = temp.id
440 AND thread_id = p_thread_id ;
441
442 CURSOR get_tbb_rowid (p_scope VARCHAR2)
443 IS SELECT ref_rowid
444 FROM hxc_temp_timecard_chunks
445 WHERE scope = p_scope
446 AND thread_id = p_thread_id ;
447
448 CURSOR get_dup_trans
449 IS SELECT master_id,
450 MAX(ROWID)
451 FROM hxc_archive_temp
452 WHERE thread_id = p_thread_id
453 GROUP BY master_id ;
454
455 CURSOR get_latest_details
456 IS SELECT /*+ LEADING(temp) */
457 id,
458 ref_ovn,
459 ROWIDTOCHAR(temp.ROWID)
460 FROM hxc_temp_timecard_chunks temp,
461 hxc_latest_details det
462 WHERE temp.scope = 'DETAIL'
463 AND temp.id = det.time_building_block_id
464 AND thread_id = p_thread_id ;
465
466 CURSOR get_max_ovn
467 IS SELECT id,
468 MAX(ref_ovn)
469 FROM hxc_temp_timecard_chunks temp
470 WHERE scope = 'DETAIL'
471 AND thread_id = p_thread_id
472 GROUP by id ;
473
474
475 l_chunk_size number;
476
477
478 TYPE NUMTABLE IS TABLE OF hxc_time_building_blocks.time_building_block_id%TYPE;
479 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(4000);
480
481 tc_id_tab NUMTABLE;
482 tc_ovn_tab NUMTABLE;
483 tc_rowid_tab VARCHARTABLE;
484 rowid_tab VARCHARTABLE;
485 td_rowid_tab VARCHARTABLE;
486 trans_detail_tab NUMTABLE;
487 trans_tab NUMTABLE;
488 usage_tab NUMTABLE;
489 attribute_tab NUMTABLE;
490 usage_rowid_tab VARCHARTABLE;
491 uniq_rowid_tab VARCHARTABLE;
492 trans_id_tab NUMTABLE;
493
497
494 latest_id_tab NUMTABLE;
495 latest_ovn_Tab NUMTABLE;
496 latest_rowid_tab VARCHARTABLE;
498 mastertab NUMTABLE;
499 threadtab NUMTABLE;
500
501 l_tc_count NUMBER := 0;
502 l_day_count NUMBER := 0;
503 l_detail_count NUMBER := 0;
504 l_det_count NUMBER := 0;
505 l_app_period_count NUMBER := 0;
506 l_tau_count NUMBER := 0;
507 l_td_count NUMBER := 0;
508 l_trans_count NUMBER := 0;
509 l_tal_count NUMBER := 0;
510 l_adl_count NUMBER := 0;
511 l_app_period_sum_count NUMBER := 0;
512 l_ta_count NUMBER := 0;
513
514 l_tc_del_count NUMBER := 0;
515 l_tal_del_count NUMBER := 0;
516 l_day_del_count NUMBER := 0;
517 l_det_del_count NUMBER := 0;
518 l_app_del_count NUMBER := 0;
519 l_app_sum_del_count NUMBER := 0;
520 l_tau_del_count NUMBER := 0;
521 l_ta_del_count NUMBER := 0;
522 l_td_del_count NUMBER := 0;
523 l_trans_del_count NUMBER := 0;
524 l_adl_del_count NUMBER := 0;
525
526
527
528 l_data_set_end_date DATE;
529 l_data_set_start_date DATE;
530
531
532
533
534 data_mismatch BOOLEAN;
535 iloop NUMBER := 0;
536 l_chunk_no NUMBER := 0;
537
538
539 DEADLOCK_DETECTED EXCEPTION;
540 PRAGMA EXCEPTION_INIT(DEADLOCK_DETECTED,-60);
541
542 PROCEDURE write_data_mismatch(p_scope IN VARCHAR2)
543 IS
544
545 BEGIN
546 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
547 INSERT INTO hxc_ar_detail_log
548 (time_building_block_id,
549 object_version_number,
550 process_type,
551 thread_id,
552 chunk_number)
553 VALUES (tc_id_tab(i),
554 tc_ovn_tab(i),
555 'RESTORE-INCOMPLETE',
556 p_thread_id,
557 l_chunk_no);
558 INSERT INTO hxc_data_set_details
559 (data_set_id,
560 scope,
561 table_name,
562 row_count)
563 VALUES (p_data_set_id,
564 p_scope,
565 p_thread_id,
566 l_chunk_no );
567
568 COMMIT;
569 -- Mark the thread as failed.
570 retcode := 2;
571
572 END;
573
574
575
576 BEGIN
577
578 -- BUG 7358756
579 -- After rewrite of Archive/Restore process, this new proceduce supports the new
580 -- multithreaded design.
581 -- To have an idea of the detailed Algorithm/approach, pls refer to hxc_archive
582 -- ( hxcarchive.pkb ).
583
584
585 hr_general.g_data_migrator_mode := 'Y';
586
587 l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),50);
588
589 fnd_file.put_line(fnd_file.log,'--- > Chunk Size is: '||l_chunk_size);
590
591
592 SELECT start_date,
593 end_date
594 INTO l_data_set_start_date,
595 l_data_set_end_date
596 FROM hxc_data_sets
597 WHERE data_set_id = p_data_set_id;
598
599 OPEN get_tcs ;
600 LOOP
601
602 FETCH get_tcs
603 BULK COLLECT
604 INTO tc_id_tab,
605 tc_ovn_tab LIMIT l_chunk_size ;
606
607 EXIT WHEN tc_id_tab.COUNT = 0;
608
609 l_td_count := 0;
610 l_trans_count := 0;
611 l_tau_count := 0;
612 l_ta_count := 0;
613 data_mismatch := FALSE;
614 iloop := 1;
615 l_chunk_no := l_chunk_no + 1;
616
617 fnd_file.put_line(fnd_file.log,' ');
618 fnd_file.put_line(fnd_file.log,'*****************************************************************');
619 fnd_file.put_line(fnd_file.log,'=================================================================');
620 fnd_file.put_line(fnd_file.log,'Entering in a new chunk l_tbb_id_tab.count ');
621 fnd_file.put_line(fnd_file.log,'==================================================================');
622
623
624 << TO_CONTINUE_TO_NEXT_CHUNK >>
625 WHILE iloop = 1
626 LOOP
627 iloop := 0;
628 BEGIN
629 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
630 INSERT INTO hxc_temp_timecard_chunks
631 ( id,
632 ref_ovn,
633 scope,
634 thread_id )
635 VALUES ( tc_id_tab(i),
636 tc_ovn_tab(i),
637 'TIMECARD',
638 p_thread_id );
639
640
641
642 INSERT INTO hxc_time_building_blocks
643 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
644 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
645 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
649 TRANSLATION_DISPLAY_KEY)
646 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
647 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
648 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
650 SELECT /*+ LEADING(temp) INDEX(bkup HXC_TIME_BUILDING_BLOCKS_AR_PK)*/
651 bkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
652 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
653 bkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
654 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
655 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
656 TRANSLATION_DISPLAY_KEY
657 FROM hxc_temp_timecard_chunks temp,
658 hxc_time_building_blocks_ar bkup
659 WHERE bkup.scope = 'TIMECARD'
660 AND bkup.time_building_block_id = temp.id
661 AND bkup.object_version_number = temp.ref_ovn
662 AND bkup.data_set_id = p_data_set_id
663 AND thread_id = p_thread_id ;
664
665 l_tc_count := sql%rowcount;
666
667
668 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
669 DELETE
670 FROM hxc_time_building_blocks_ar
671 WHERE time_building_block_id = tc_id_tab(i)
672 AND object_version_number = tc_ovn_tab(i)
673 AND data_set_id = p_data_set_id ;
674
675
676 l_tc_del_count := sql%rowcount;
677
678 hxc_restore.log_data_mismatch(p_scope => 'Timecard',
679 p_insert => l_tc_count,
680 p_delete => l_tc_del_count,
681 p_mismatch => data_mismatch
682 );
683
684 IF data_mismatch
685 THEN
686 write_data_mismatch('Timecard');
687 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
688 END IF;
689
690
691 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
692 INSERT INTO hxc_ar_detail_log
693 (time_building_block_id,
694 object_version_number,
695 process_type,
696 thread_id,
697 chunk_number)
698 VALUES (tc_id_tab(i),
699 tc_ovn_tab(i),
700 'RESTORE',
701 p_thread_id,
702 l_chunk_no);
703
704 INSERT INTO hxc_temp_timecard_chunks
705 ( id,
706 scope,
707 ref_rowid,
708 thread_id )
709 SELECT /*+ ORDERED */
710 DISTINCT
711 talbkup.application_period_id,
712 'APPLICATION_PERIOD',
713 ROWIDTOCHAR(talbkup.ROWID),
714 thread_id
715 FROM hxc_temp_timecard_chunks temp,
716 hxc_tc_ap_links_ar talbkup
717 WHERE temp.id = talbkup.timecard_id
718 AND temp.scope IN ('TIMECARD')
719 AND thread_id = p_thread_id ;
720
721
722 INSERT INTO hxc_tc_ap_links
723 ( timecard_id,
724 application_period_id)
725 SELECT timecard_id,
726 application_period_id
727 FROM hxc_temp_timecard_chunks temp,
728 hxc_tc_ap_links_ar talbkup
729 WHERE temp.ref_rowid = talbkup.ROWID
730 AND temp.scope = ('APPLICATION_PERIOD')
731 AND thread_id = p_thread_id ;
732
733
734 l_tal_count := SQL%ROWCOUNT;
735
736 DELETE FROM hxc_tc_ap_links_ar
737 WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
738 FROM hxc_temp_timecard_chunks
739 WHERE scope = 'APPLICATION_PERIOD'
740 AND thread_id = p_thread_id );
741
742 l_tal_del_count := SQL%ROWCOUNT;
743
744 hxc_restore.log_data_mismatch(p_scope => 'TC App LINKs ',
745 p_insert => l_tal_count,
746 p_delete => l_tal_del_count,
747 p_mismatch => data_mismatch );
748
749 IF data_mismatch
750 THEN
751 write_data_mismatch('TC App LINKs ');
752 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
753 END IF;
754
755
756 INSERT INTO hxc_time_building_blocks
757 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
758 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
759 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
760 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
761 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
765 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
762 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
763 SELECT /*+ ORDERED */
764 appbkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
766 appbkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
767 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
768 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,
769 APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY
770 FROM hxc_time_building_blocks_ar appbkup
771 WHERE appbkup.scope = 'APPLICATION_PERIOD'
772 AND appbkup.data_set_id = p_data_set_id
773 AND appbkup.time_building_block_id IN ( SELECT id
774 FROM hxc_temp_timecard_chunks temp
775 WHERE temp.scope IN
776 ('APPLICATION_PERIOD')
777 AND thread_id = p_thread_id ) ;
778
779
780 l_app_period_count := SQL%ROWCOUNT;
781
782 DELETE FROM hxc_time_building_blocks_ar
783 WHERE time_building_block_id IN ( SELECT id
784 FROM hxc_temp_timecard_chunks
785 WHERE scope IN
786 ('APPLICATION_PERIOD')
787 AND thread_id = p_thread_id )
788 AND data_set_id = p_data_set_id ;
789
790 l_app_del_count := SQL%ROWCOUNT;
791
792 hxc_restore.log_data_mismatch( p_scope => 'Application Period ',
793 p_insert => l_app_period_count,
794 p_delete => l_app_del_count,
795 p_mismatch => data_mismatch );
796
797 IF data_mismatch
798 THEN
799 write_data_mismatch('Application Period ');
800 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
801 END IF;
802
803
804 INSERT INTO hxc_app_period_summary
805 (APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,TIME_RECIPIENT_ID,
806 TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,RECIPIENT_SEQUENCE,
807 CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,APPROVER_ID,APPROVAL_COMP_ID,
808 APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,APPROVAL_ITEM_KEY,DATA_SET_ID)
809 SELECT APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,
810 TIME_RECIPIENT_ID,TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,
811 RECIPIENT_SEQUENCE,CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,
812 APPROVER_ID,APPROVAL_COMP_ID,APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,
813 APPROVAL_ITEM_KEY,apsbkup.DATA_SET_ID
814 FROM hxc_app_period_summary_ar apsbkup
815 WHERE application_period_id IN (SELECT id
816 FROM hxc_temp_timecard_chunks
817 WHERE scope = 'APPLICATION_PERIOD'
818 AND thread_id = p_thread_id )
819 AND data_set_id = p_data_set_id ;
820
821 l_app_period_sum_count := SQL%ROWCOUNT;
822
823
824 DELETE FROM hxc_app_period_summary_ar
825 WHERE application_period_id IN (SELECT id
826 FROM hxc_temp_timecard_chunks
827 WHERE scope = 'APPLICATION_PERIOD'
828 AND thread_id = p_thread_id )
829 AND data_set_id = p_data_set_id ;
830
831 l_app_sum_del_count := SQL%ROWCOUNT;
832
833 hxc_restore.log_data_mismatch( p_scope => 'App Period Summary ',
834 p_insert => l_app_period_sum_count,
835 p_delete => l_app_sum_del_count,
836 p_mismatch => data_mismatch );
837
838 IF data_mismatch
839 THEN
840 write_data_mismatch('App Period Summary ');
841 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
842 END IF;
843
844 INSERT INTO hxc_temp_timecard_chunks
845 ( id,
846 ref_ovn,
847 scope,
848 ref_rowid,
849 day_start_time,
850 day_stop_time,
851 thread_id )
852 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BLDNG_BLOCKS_AR_FK3) */
853 time_building_block_id,
854 object_version_number,
855 'DAY',
856 ROWIDTOCHAR(ar.ROWID),
857 ar.start_time,
858 ar.stop_time,
859 thread_id
860 FROM hxc_temp_timecard_chunks temp,
861 hxc_time_building_blocks_ar ar
862 WHERE parent_building_block_id = temp.id
863 AND parent_building_block_ovn = temp.ref_ovn
864 AND temp.scope = 'TIMECARD'
865 AND thread_id = p_thread_id
866 AND ar.data_set_id = p_data_set_id ;
867
868
869 INSERT INTO hxc_time_building_blocks
870 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
871 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
872 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
873 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
874 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
875 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
876 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
877 bkupday.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
878 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
879 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
880 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
881 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
882 TRANSLATION_DISPLAY_KEY
883 FROM hxc_temp_timecard_chunks temp,
884 hxc_time_building_blocks_ar bkupday
885 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
886 AND temp.scope = 'DAY'
887 AND thread_id = p_thread_id
888 AND bkupday.data_set_id = p_data_set_id ;
889
890
891 l_day_count := sql%rowcount;
892
893
894 l_day_del_count := 0;
895 OPEN get_tbb_rowid('DAY');
896 LOOP
897 FETCH get_tbb_rowid
898 BULK COLLECT INTO
899 rowid_tab LIMIT 501;
900
901 EXIT WHEN rowid_tab.COUNT = 0;
902
903 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
904 DELETE FROM hxc_time_building_blocks_ar
905 WHERE ROWID = CHARTOROWID(rowid_tab(i))
906 AND data_set_id = p_data_set_id;
907
908 l_day_del_count := l_day_del_count + SQL%ROWCOUNT;
909 rowid_tab.DELETE;
910
911 END LOOP;
912 CLOSE get_tbb_rowid;
913
914 hxc_restore.log_data_mismatch( p_scope => 'Day ',
915 p_insert => l_day_count,
916 p_delete => l_day_del_count,
917 p_mismatch => data_mismatch );
918
919 IF data_mismatch
923 END IF;
920 THEN
921 write_data_mismatch('Day ');
922 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
924
925 INSERT INTO hxc_temp_timecard_chunks
926 ( id,
927 ref_ovn,
928 scope,
929 day_start_time,
930 day_stop_time,
931 ref_rowid,
932 thread_id )
933 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BLDNG_BLOCKS_AR_FK3) */
934 time_building_block_id,
935 object_version_number,
936 'DETAIL',
937 nvl(day_start_time,ar.start_time),
938 nvl(day_stop_time,ar.stop_time),
939 ROWIDTOCHAR(ar.ROWID),
940 thread_id
941 FROM hxc_temp_timecard_chunks temp,
942 hxc_time_building_blocks_ar ar
943 WHERE parent_building_block_id = temp.id
944 AND parent_building_block_ovn = temp.ref_ovn
945 AND temp.scope = 'DAY'
946 AND thread_id = p_thread_id
947 AND ar.data_set_id = p_data_set_id;
948
949
950 INSERT INTO hxc_time_building_blocks
951 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
952 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
953 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
954 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
955 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
956 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
957 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
958 bkupday.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
959 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
960 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
961 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
962 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
963 TRANSLATION_DISPLAY_KEY
964 FROM hxc_temp_timecard_chunks temp,
965 hxc_time_building_blocks_ar bkupday
966 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
967 AND temp.scope = 'DETAIL'
968 AND thread_id = p_thread_id
969 AND bkupday.data_set_id = p_data_set_id;
970
971
972 l_det_count := sql%rowcount;
973
974 l_det_del_count := 0;
975 OPEN get_tbb_rowid('DETAIL');
976 LOOP
977 FETCH get_tbb_rowid
978 BULK COLLECT INTO
979 rowid_tab LIMIT 501;
980
981 EXIT WHEN rowid_tab.COUNT = 0;
982
983 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
984 DELETE FROM hxc_time_building_blocks_ar
985 WHERE ROWID = CHARTOROWID(rowid_tab(i))
986 AND data_set_id = p_data_set_id;
987
988 l_det_del_count := l_det_del_count + SQL%ROWCOUNT;
989 rowid_tab.DELETE;
990 END LOOP;
991 CLOSE get_tbb_rowid;
992
993 hxc_restore.log_data_mismatch( p_scope => 'Detail ',
994 p_insert => l_det_count,
995 p_delete => l_det_del_count,
996 p_mismatch => data_mismatch );
997
998 IF data_mismatch
999 THEN
1000 write_data_mismatch('Detail ');
1001 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1002 END IF;
1003
1004 l_td_count := 0;
1005 l_td_del_count := 0;
1006 l_trans_count := 0;
1007 l_trans_del_count := 0;
1008 IF get_transactions%ISOPEN
1009 THEN
1010 CLOSE get_transactions;
1011 END IF;
1012 OPEN get_transactions ;
1013 LOOP
1014
1015 FETCH get_transactions
1016 BULK COLLECT INTO trans_detail_tab,
1017 trans_tab,
1018 td_rowid_tab LIMIT 250;
1019
1020 EXIT WHEN trans_detail_tab.COUNT = 0 ;
1021
1022 FORALL i IN trans_detail_tab.FIRST..trans_detail_tab.LAST
1023 INSERT INTO hxc_archive_temp
1024 ( detail_id,
1025 master_id,
1026 ref_rowid,
1027 thread_id )
1028 VALUES ( trans_detail_tab(i),
1029 trans_tab(i),
1030 td_rowid_tab(i),
1031 p_thread_id );
1032
1033 INSERT INTO hxc_transaction_details
1034 (DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1035 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1036 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
1040 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
1037 SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
1038 bkuptxnd.DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1039 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1041 FROM hxc_archive_temp temp,
1042 hxc_transaction_details_ar bkuptxnd
1043 WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
1044 AND thread_id = p_thread_id
1045 AND bkuptxnd.data_set_id = p_data_set_id;
1046
1047
1048 l_td_count := l_td_count + SQL%ROWCOUNT;
1049
1050
1051 FORALL i IN td_rowid_tab.FIRST..td_rowid_tab.LAST
1052 DELETE FROM hxc_transaction_details_ar
1053 WHERE ROWID = CHARTOROWID(td_rowid_tab(i))
1054 AND data_set_id = p_data_set_id ;
1055
1056
1057 l_td_del_count := l_td_del_count + SQL%ROWCOUNT;
1058
1059 trans_detail_tab.DELETE;
1060 trans_tab.DELETE;
1061 td_rowid_tab.DELETE;
1062
1063 OPEN get_dup_trans;
1064 LOOP
1065 FETCH get_dup_trans
1066 BULK COLLECT INTO trans_id_tab,
1067 uniq_rowid_tab LIMIT 500;
1068
1069 EXIT WHEN trans_id_tab.COUNT = 0;
1070
1071 FORALL i IN trans_id_tab.FIRST..trans_id_tab.LAST
1072 DELETE FROM hxc_archive_temp
1073 WHERE master_id = trans_id_tab(i)
1074 AND ROWID <> uniq_rowid_tab(i)
1075 AND thread_id = p_thread_id ;
1076 END LOOP;
1077 CLOSE get_dup_trans;
1078
1079 DELETE FROM hxc_archive_temp
1080 WHERE EXISTS ( SELECT 1
1081 FROM hxc_transactions
1082 WHERE transaction_id = master_id )
1083 AND thread_id = p_thread_id ;
1084
1085 INSERT INTO hxc_transactions
1086 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
1087 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1088 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
1089 SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
1090 bkuptxn.DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
1091 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
1092 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1093 TRANSACTION_CODE
1094 FROM hxc_transactions_ar bkuptxn,
1095 hxc_archive_temp temp
1096 WHERE transaction_id = master_id
1097 AND thread_id = p_thread_id
1098 AND transaction_id NOT IN ( SELECT transaction_id
1099 FROM hxc_transactions hxc
1100 WHERE bkuptxn.transaction_id = hxc.transaction_id)
1101 AND type <> 'RETRIEVAL'
1102 AND bkuptxn.data_set_id = p_data_set_id
1103 ;
1104
1105 l_trans_count := l_trans_count + SQL%ROWCOUNT;
1106
1107 DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
1108 FROM hxc_transactions_ar bkuptxn
1109 WHERE transaction_id IN ( SELECT master_id
1110 FROM hxc_archive_temp temp
1111 WHERE thread_id = p_thread_id)
1112 AND type <> 'RETRIEVAL'
1113 AND data_set_id = p_data_set_id ;
1114
1115 l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1116
1117 INSERT INTO hxc_ar_trans_temp
1118 ( transaction_id, data_set_id, thread_id, trans_rowid )
1119 SELECT bkuptxn.transaction_id,
1120 p_data_set_id,
1121 p_thread_id,
1122 ROWIDTOCHAR(bkuptxn.ROWID)
1123 FROM hxc_transactions_ar bkuptxn,
1124 hxc_archive_temp temp
1125 WHERE transaction_id = master_id
1126 AND thread_id = p_thread_id
1127 AND type = 'RETRIEVAL'
1128 AND data_set_id = p_data_set_id ;
1129
1130
1131
1132 DELETE FROM hxc_archive_temp
1133 WHERE thread_id = p_thread_id ;
1134
1135 END LOOP;
1136
1137 CLOSE get_transactions;
1138
1139 hxc_restore.log_data_mismatch( p_scope => 'Transaction Detail ',
1140 p_insert => l_td_count,
1141 p_delete => l_td_del_count,
1142 p_mismatch => data_mismatch );
1143
1144 IF data_mismatch
1145 THEN
1146 write_data_mismatch('Transaction Detail ');
1147 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1151 p_insert => l_trans_count,
1148 END IF;
1149
1150 hxc_restore.log_data_mismatch( p_scope => 'Transaction ',
1152 p_delete => l_trans_del_count,
1153 p_mismatch => data_mismatch );
1154
1155
1156 IF data_mismatch
1157 THEN
1158 write_data_mismatch('Transaction ');
1159 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1160 END IF;
1161
1162
1163 l_ta_count := 0;
1164 l_ta_del_count := 0;
1165 l_tau_count := 0;
1166 l_tau_del_count := 0;
1167
1168 IF get_attributes%ISOPEN
1169 THEN
1170 CLOSE get_attributes;
1171 END IF;
1172 OPEN get_attributes;
1173
1174 LOOP
1175 FETCH get_attributes
1176 BULK COLLECT INTO usage_tab,
1177 attribute_tab,
1178 usage_rowid_tab LIMIT 250;
1179
1180 EXIT WHEN usage_tab.COUNT = 0;
1181
1182
1183 FORALL i IN usage_tab.FIRST..usage_tab.LAST
1184 INSERT INTO hxc_archive_temp
1185 ( detail_id,
1186 master_id,
1187 ref_rowid,
1188 thread_id)
1189 VALUES ( usage_tab(i),
1190 attribute_tab(i),
1191 usage_rowid_tab(i),
1192 p_thread_id );
1193
1194
1195 INSERT INTO hxc_time_attribute_usages
1196 (DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1197 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1198 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN)
1199 SELECT /*+ LEADING(temp) USE_NL(bkuptau) */
1200 bkuptau.DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1201 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1202 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN
1203 FROM hxc_archive_temp temp,
1204 hxc_time_attribute_usages_ar bkuptau
1205 WHERE bkuptau.ROWID = CHARTOROWID(temp.ref_rowid)
1206 AND thread_id = p_thread_id
1207 AND bkuptau.data_set_id = p_data_set_id;
1208
1209
1210 l_tau_count := l_tau_count + SQL%ROWCOUNT;
1211
1212 FORALL i IN usage_rowid_tab.FIRST..usage_rowid_tab.LAST
1213 DELETE FROM hxc_time_attribute_usages_ar
1214 WHERE ROWID = CHARTOROWID(usage_rowid_tab(i))
1215 AND data_set_id = p_data_set_id;
1216
1217 l_tau_del_count := l_tau_del_count + SQL%ROWCOUNT;
1218
1219 DELETE FROM hxc_archive_temp
1220 WHERE EXISTS ( SELECT 1
1221 FROM hxc_time_attributes
1222 WHERE time_attribute_id = master_id )
1223 AND thread_id = p_thread_id ;
1224
1225 INSERT INTO hxc_time_attributes
1226 (ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1227 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1228 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1229 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1230 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1231 ATTRIBUTE14,CONSOLIDATED_FLAG,DATA_SET_ID)
1232 SELECT /*+ LEADING(temp) USE_NL(bkupta) */
1233 ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1234 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1235 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1236 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1237 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1238 ATTRIBUTE14,null,DATA_SET_ID
1239 FROM hxc_time_attributes_ar bkupta
1240 WHERE bkupta.time_attribute_id in ( SELECT /*+ NO_INDEX(temp) */
1241 master_id
1242 FROM hxc_archive_temp temp
1243 WHERE thread_id = p_thread_id )
1244 AND bkupta.data_set_id = p_data_set_id;
1245
1246
1247 l_ta_count := l_ta_count + SQL%ROWCOUNT;
1248
1249 DELETE /*+ LEADING(temp) USE_NL(bkupta) */
1250 FROM hxc_time_attributes_ar bkupta
1251 WHERE time_attribute_id IN ( SELECT /*+ NO_INDEX(temp) */
1252 master_id
1253 FROM hxc_archive_temp temp
1257 l_ta_del_count := l_ta_del_count + SQL%ROWCOUNT;
1254 WHERE thread_id = p_thread_id )
1255 AND data_set_id = p_data_set_id ;
1256
1258
1259 usage_tab.DELETE;
1260 attribute_tab.DELETE;
1261 usage_rowid_tab.DELETE;
1262
1263 DELETE FROM hxc_archive_temp
1264 WHERE thread_id = p_thread_id ;
1265
1266 END LOOP ;
1267 CLOSE get_attributes;
1268
1269 hxc_restore.log_data_mismatch( p_scope => 'Attribute Usages ',
1270 p_insert => l_tau_count,
1271 p_delete => l_tau_del_count,
1272 p_mismatch => data_mismatch );
1273
1274 IF data_mismatch
1275 THEN
1276 write_data_mismatch('Attribute Usages ');
1277 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1278 END IF;
1279
1280 hxc_restore.log_data_mismatch( p_scope => 'Attributes ',
1281 p_insert => l_ta_count,
1282 p_delete => l_ta_del_count,
1283 p_mismatch => data_mismatch );
1284
1285 IF data_mismatch
1286 THEN
1287 write_data_mismatch('Attributes ');
1288 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1289 END IF;
1290
1291 DELETE FROM hxc_temp_timecard_chunks
1292 WHERE scope IN ( 'TIMECARD', 'DAY','APPLICATION_PERIOD')
1293 AND thread_id = p_thread_id ;
1294
1295
1296 INSERT INTO hxc_ap_detail_links
1297 (application_period_id,
1298 time_building_block_id,
1299 time_building_block_ovn)
1300 SELECT /*+ INDEX( adlbkup HXC_AP_DETAIL_LINKS_AR_FK1) */
1301 application_period_id, time_building_block_id, time_building_block_ovn
1302 FROM hxc_temp_timecard_chunks temp,
1303 hxc_ap_detail_links_ar adlbkup
1304 WHERE temp.id = adlbkup.time_building_block_id
1305 AND temp.ref_ovn = adlbkup.time_building_block_ovn
1306 AND temp.scope = ('DETAIL')
1307 AND thread_id = p_thread_id ;
1308
1309 l_adl_count := SQL%ROWCOUNT;
1310
1311
1312 DELETE FROM hxc_ap_detail_links_ar
1313 WHERE (time_building_block_id,time_building_block_ovn)
1314 IN ( SELECT id,
1315 ref_ovn
1316 FROM hxc_temp_timecard_chunks
1317 WHERE scope = 'DETAIL'
1318 AND thread_id = p_thread_id );
1319 l_adl_del_count := SQL%ROWCOUNT;
1320
1321 hxc_restore.log_data_mismatch( p_scope => 'App Detail LINKs ',
1322 p_insert => l_adl_count,
1323 p_delete => l_adl_del_count,
1324 p_mismatch => data_mismatch );
1325
1326 IF data_mismatch
1327 THEN
1328 write_data_mismatch('App Detail LINKs ');
1329 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1330 END IF;
1331
1332 OPEN get_latest_details;
1333 LOOP
1334 FETCH get_latest_details
1335 BULK COLLECT INTO latest_id_tab,
1336 latest_ovn_tab,
1337 latest_rowid_tab LIMIT 500;
1338 EXIT WHEN latest_id_tab.COUNT = 0;
1339
1340 FORALL i IN latest_id_Tab.FIRST..latest_id_Tab.last
1341 DELETE FROM hxc_temp_timecard_chunks
1342 WHERE ROWID = CHARTOROWID(latest_rowid_tab(i))
1343 AND thread_id = p_thread_id ;
1344
1345 latest_rowid_tab.DELETE;
1346 latest_id_tab.DELETE;
1347 latest_ovn_tab.DELETE;
1348 END LOOP;
1349 CLOSE get_latest_details;
1350
1351 OPEN get_max_ovn;
1352 LOOP
1353 FETCH get_max_ovn
1354 BULK COLLECT INTO latest_id_tab,
1355 latest_ovn_tab
1356 LIMIT 500;
1357 EXIT WHEN latest_id_tab.count = 0;
1358 FORALL i IN latest_id_tab.first..latest_id_tab.last
1359 DELETE FROM hxc_temp_timecard_chunks
1360 WHERE id = latest_id_tab(i)
1361 AND ref_ovn <> latest_ovn_tab(i)
1362 AND thread_id = p_thread_id ;
1363
1364 latest_rowid_tab.DELETE;
1365 latest_id_tab.DELETE;
1366 latest_ovn_tab.DELETE;
1367
1368 END LOOP;
1369 CLOSE get_max_ovn;
1370
1371
1372 INSERT INTO hxc_latest_details
1373 (resource_id,
1374 time_building_block_id,
1375 object_version_number,
1376 approval_status,
1377 application_set_id,
1378 last_update_date,
1379 resource_type,
1380 comment_text,
1381 start_time,
1382 stop_time )
1383 SELECT /*+ ORDERED */
1384 resource_id,
1385 time_building_block_id,
1386 object_version_number,
1387 approval_status,
1388 application_set_id,
1389 last_update_date,
1390 resource_type,
1391 comment_text,
1392 day_start_time,
1393 day_stop_time
1394 FROM hxc_temp_timecard_chunks temp,
1395 hxc_time_building_blocks hxc
1396 WHERE temp.id = hxc.time_building_block_id
1397 AND temp.ref_ovn = hxc.object_version_number
1398 AND temp.scope = 'DETAIL'
1399 AND thread_id = p_thread_id ;
1400
1401
1402
1403
1404 fnd_file.put_line(fnd_file.log,' ');
1405 fnd_file.put_line(fnd_file.log,' ');
1406 fnd_file.put_line(fnd_file.log,' ');
1407 fnd_file.put_line(fnd_file.log,' ');
1408 fnd_file.put_line(fnd_file.log,' ');
1409
1410 fnd_file.put_line(fnd_file.log,'*****************************************');
1411 fnd_file.put_line(fnd_file.log,' ');
1412 COMMIT;
1413 tc_id_tab.DELETE;
1414 tc_ovn_tab.DELETE;
1415 iloop:= 0;
1416 EXCEPTION
1417 WHEN DUP_VAL_ON_INDEX THEN
1418 ROLLBACK;
1419 iloop := 1;
1420 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention, will sleep for a minute ');
1421 fnd_file.put_line(fnd_file.log,'=================================================================');
1422 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1423 fnd_file.put_line(fnd_file.log,'==================================================================');
1424 l_td_count := 0;
1425 l_trans_count := 0;
1426 l_tau_count := 0;
1427 l_ta_count := 0;
1428 data_mismatch := FALSE;
1429 dbms_lock.sleep(60);
1430 WHEN DEADLOCK_DETECTED THEN
1431 ROLLBACK;
1432 iloop := 1;
1433 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention(deadlock), will sleep for a minute ');
1434 fnd_file.put_line(fnd_file.log,'=================================================================');
1435 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1436 fnd_file.put_line(fnd_file.log,'==================================================================');
1437 l_td_count := 0;
1438 l_trans_count := 0;
1439 l_tau_count := 0;
1440 l_ta_count := 0;
1441 data_mismatch := FALSE;
1442 dbms_lock.sleep(60);
1443
1444 END ;
1445 END LOOP TO_CONTINUE_TO_NEXT_CHUNK;
1446 END LOOP;
1447
1448
1449
1450 END child_restore_process;
1451
1452 PROCEDURE log_data_mismatch( p_scope IN VARCHAR2,
1453 p_insert IN NUMBER,
1454 p_delete IN NUMBER,
1455 p_mismatch IN OUT NOCOPY BOOLEAN)
1456 IS
1457
1458 BEGIN
1459 IF p_insert = p_delete
1460 THEN
1461 fnd_file.put_line(fnd_file.log,' ');
1462 fnd_file.put_line(fnd_file.log,' '||p_scope||' records moved : '||p_insert);
1463 ELSE
1464 fnd_file.put_line(fnd_file.log,' ');
1465 fnd_file.put_line(fnd_file.log,'==========================================================================');
1466 fnd_file.put_line(fnd_file.log,' An error occured while processing '||p_scope||' records');
1467 fnd_file.put_line(fnd_file.log,'==========================================================================');
1468 fnd_file.put_line(fnd_file.log, p_insert||' records were inserted into online table ');
1469 fnd_file.put_line(fnd_file.log, p_delete||' records were deleted from offline table ');
1470 fnd_file.put_line(fnd_file.log,'This chunk is rolled back, pls check up data. ');
1471 p_mismatch := TRUE;
1472 ROLLBACK;
1473 END IF;
1474
1475 RETURN;
1476 END log_data_mismatch ;
1477
1478
1479
1480
1481
1482 END hxc_restore;