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