DBA Data[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;