1 PACKAGE BODY hxc_archive AS
2 /* $Header: hxcarchive.pkb 120.3.12010000.7 2008/10/08 10:54:43 asrajago 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 DELETE FROM hxc_transactions
336 WHERE ROWID IN ( SELECT CHARTOROWID(trans_rowid)
337 FROM hxc_ar_trans_temp
338 WHERE thread_id = 0 );
339
340
341 -- Clear all the records, which are processed.
342
343 DELETE FROM hxc_ar_trans_temp
344 WHERE thread_id = 0 ;
345
346 COMMIT;
347
348 ELSE
349
350 -- There are no records still in the temp table for transactions.
351 -- Sleep for 10 seconds and try again.
352 dbms_lock.sleep(10);
353
354 END IF;
355
356 END LOOP;
357
358 COMMIT;
359
360
361
362 -- Check if hxc_data_set_details table has records.
363 -- ( Rownum condition put so that no FTS is issued by the DB.
364 -- We dont want the total count, just wanna know if it has atleast
365 -- one record )
366
367 trans_count := 0;
368 SELECT COUNT(1)
369 INTO trans_count
370 FROM hxc_data_set_details;
371
372 -- Done with the processing, update the data set status to OFFLINE.
373 IF l_thread_success AND trans_count = 0
374 THEN
375 -- If all threads finished successfully and there is no data left in
376 -- hxc_data_set_details, then mark the data set as OFF_LINE.
377 UPDATE hxc_data_sets
378 SET status = 'OFF_LINE', validation_status = ' '
379 WHERE data_set_id = p_data_set_id;
380
381 ELSE
382 fnd_file.put_line(fnd_file.LOG,' There were some issues with the process');
383 fnd_file.put_line(fnd_file.LOG,' Either one of the threads failed or there is a data issue ');
384 fnd_file.put_line(fnd_file.LOG,' Please run the process again to reprocess the failed timecards ');
385 fnd_file.put_line(fnd_file.LOG,' ');
386
387 END IF;
388
389
390 COMMIT;
391
392 l_debug_info := ' ';
393 -- Check if the profile is set for detailed logging.
394 IF fnd_profile.value('HXC_AR_ENABLE_LOG') = 'Y'
395 THEN
396 -- Print out the log.
397 fnd_file.put_line(fnd_file.LOG,' ');
398 fnd_file.put_line(fnd_file.LOG,'Following are the timecards processed ');
399 fnd_file.put_line(fnd_file.LOG,'======================================');
400 fnd_file.put_line(fnd_file.LOG,' ');
401 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
402 fnd_file.put_line(fnd_file.LOG,' ');
403 FOR log_rec IN get_log_details
404 LOOP
405 IF l_debug_info <> log_rec.detail
406 THEN
407 fnd_file.put_line(fnd_file.log,' ---- ');
408 l_debug_info := log_rec.detail;
409 END IF;
410 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
411 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
412 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
413 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
414 END LOOP;
415
416 l_debug_info := ' ';
417 fnd_file.put_line(fnd_file.LOG,' ');
418 fnd_file.put_line(fnd_file.LOG,'Following are the timecards not processed ');
419 fnd_file.put_line(fnd_file.LOG,'==========================================');
420 fnd_file.put_line(fnd_file.LOG,' ');
421 fnd_file.put_line(fnd_file.LOG,'Person id - Timecard start time [TC ID - Debug information]');
422 fnd_file.put_line(fnd_file.LOG,' ');
423 FOR log_rec IN get_log_details_failed
424 LOOP
425 IF l_debug_info <> log_rec.detail
426 THEN
427 fnd_file.put_line(fnd_file.log,' ---- ');
428 l_debug_info := log_rec.detail;
429 END IF;
430 hr_utility.trace(log_rec.resource_id||' - '||log_rec.start_time||
431 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
432 fnd_file.put_line(fnd_file.log,log_rec.resource_id||' - '||log_rec.start_time||
433 ' ['||log_rec.tc_id||'-'||log_rec.detail||']');
434 END LOOP;
435 END IF;
436
437 -- COMMIT and come out of the process.
438 COMMIT;
439
440 END archive_process;
441
442
443
444
445 -- Public Procedure child_archive_process
446 -- Added as part of re-architecture to multithread the process.
447 -- Would be called from the Worker process for Archive Data Set.
448
449
450 PROCEDURE child_archive_process ( errbuf OUT NOCOPY VARCHAR2,
451 retcode OUT NOCOPY NUMBER,
452 p_from_id IN NUMBER,
453 p_to_id IN NUMBER,
454 p_data_set_id IN NUMBER,
455 p_thread_id IN NUMBER )
456 IS
457
458
459 CURSOR get_tcs
460 IS SELECT time_building_block_id,
461 object_version_number
462 FROM hxc_ar_tc_ids_temp
463 WHERE time_building_block_id >= p_from_id
464 AND time_building_block_id < p_to_id ;
465
466 CURSOR get_transactions
467 IS SELECT /*+ LEADING(temp) */
468 transaction_detail_id,
469 transaction_id,
470 ROWIDTOCHAR(ar.ROWID)
471 FROM hxc_temp_timecard_chunks temp,
472 hxc_transaction_details ar
473 WHERE ar.time_building_block_id = temp.id
474 AND ar.time_building_block_ovn = temp.ref_ovn
475 AND thread_id = p_thread_id ;
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
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
503 CURSOR get_latest_details
504 IS SELECT det.time_building_block_id,
505 ROWIDTOCHAR(det.ROWID)
506 FROM hxc_temp_timecard_chunks temp,
507 hxc_latest_details det
508 WHERE temp.scope = 'DETAIL'
509 AND temp.id = det.time_building_block_id ;
510
511
512 CURSOR get_max_ovn
513 IS SELECT id,
514 max(ref_ovn)
515 FROM hxc_temp_timecard_chunks temp
516 WHERE scope = 'DETAIL'
517 AND thread_id = p_thread_id
518 GROUP by id ;
519
520
521 l_chunk_size number;
522
523 TYPE NUMTABLE IS TABLE OF hxc_time_building_blocks_ar.time_building_block_id%TYPE;
524 TYPE VARCHARTABLE IS TABLE OF VARCHAR2(4000);
525
526 tc_id_tab NUMTABLE;
527 tc_ovn_tab NUMTABLE;
528 tc_rowid_tab VARCHARTABLE;
529 rowid_tab VARCHARTABLE;
530 td_rowid_tab VARCHARTABLE;
531 trans_detail_tab NUMTABLE;
532 trans_tab NUMTABLE;
533 usage_tab NUMTABLE;
534 attribute_tab NUMTABLE;
535 usage_rowid_tab VARCHARTABLE;
536 uniq_rowid_tab VARCHARTABLE;
537 trans_id_tab NUMTABLE;
538
539 latest_id_tab NUMTABLE;
540 latest_ovn_Tab NUMTABLE;
541 latest_rowid_tab VARCHARTABLE;
542
543 l_tc_count NUMBER := 0;
544 l_day_count NUMBER := 0;
545 l_detail_count NUMBER := 0;
546 l_det_count NUMBER := 0;
547 l_app_period_count NUMBER := 0;
548 l_tau_count NUMBER := 0;
549 l_td_count NUMBER := 0;
550 l_trans_count NUMBER := 0;
551 l_tal_count NUMBER := 0;
552 l_adl_count NUMBER := 0;
553 l_app_period_sum_count NUMBER := 0;
554 l_ta_count NUMBER := 0;
555
556 l_tc_del_count NUMBER := 0;
557 l_tal_del_count NUMBER := 0;
558 l_day_del_count NUMBER := 0;
559 l_det_del_count NUMBER := 0;
560 l_app_del_count NUMBER := 0;
561 l_app_sum_del_count NUMBER := 0;
562 l_tau_del_count NUMBER := 0;
563 l_ta_del_count NUMBER := 0;
564 l_td_del_count NUMBER := 0;
565 l_trans_del_count NUMBER := 0;
566 l_adl_del_count NUMBER := 0;
567
568
569
570 l_data_set_end_date DATE;
571 l_data_set_start_date DATE;
572
573 data_mismatch BOOLEAN := FALSE;
574 iloop NUMBER := 0;
575 l_chunk_no NUMBER := 0;
576
577 DEADLOCK_DETECTED EXCEPTION;
578 PRAGMA EXCEPTION_INIT(DEADLOCK_DETECTED,-60);
579
580
581 PROCEDURE write_data_mismatch(p_scope IN VARCHAR2)
582 IS
583
584 BEGIN
585 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
586 INSERT INTO hxc_ar_detail_log
587 (time_building_block_id,
588 object_version_number,
589 process_type,
590 thread_id,
591 chunk_number)
592 VALUES (tc_id_tab(i),
593 tc_ovn_tab(i),
594 'ARCHIVE-INCOMPLETE',
595 p_thread_id,
596 l_chunk_no);
597 INSERT INTO hxc_data_set_details
598 (data_set_id,
599 scope,
600 table_name,
601 row_count)
602 VALUES (p_data_set_id,
603 p_scope,
604 p_thread_id,
605 l_chunk_no );
606
607 COMMIT;
608 retcode := 2;
609
610 END;
611
612
613
614 BEGIN
615
616
617 -- Bug 7358756
618 -- REWRITTEN ARCHIVE PROCESS
619
620 -- This procedure is rewritten in line with the changed Archive process
621 -- The process is now multithreaded, and each thread processes independant
622 -- of others. Detailed algorithm/approach as below.
623
624 -- HXC_AR_TC_IDS_TEMP table populated by the parent process holds all the timecard
625 -- scope records that are to be archived. The process parameters are as below.
626 -- p_from_id -- Starting time_building_block_id for this thread.
627 -- p_to_id -- Ending time_building_block_id for this thread
628 -- p_data_set_id -- Data set id to be populated in the tables.
629 -- p_thread_id -- Thread number for this thread.
630 --
631 -- Following are the steps.
632 --
633 -- * Pick up the timecards(id-ovn) from hxc_ar_tc_ids_temp table, limited by
634 -- chunk size profile option.
635 -- * Insert the above id-ovn combination into hxc_temp_timecard_chunks table.
636 -- * Pick up all records from hxc_time_building_blocks for these combinations
637 -- and insert into hxc_time_building_blocks_ar
638 -- * For the timecard records in hxc_temp_timecard_chunks, pick up all records
639 -- from hxc_tc_ap_links, and insert into itself with application_period scope.
640 -- * Join hxc_temp_timecard_chunks with hxc_tc_ap_links and insert into hxc_tc_ap_links_ar
641 -- table.
642 -- * Delete these records from hxc_tc_ap_links table.
643 -- * For all the application_period records in hxc_temp_timecard_chunks table, select from
644 -- hxc_time_building_blocks and insert into hxc_time_building_blocks_ar.
645 -- * Delete from hxc_time_building_blocks these records.
646 -- * Pick up all records for the application_period from hxc_app_period_summary
647 -- table and insert into hxc_app_period_summary_ar table.
648 -- * Delete from hxc_app_period_summary table, the corresponding records.
649 -- * Join hxc_temp_timecard_chunks with hxc_time_building_blocks, DAY scope and pick up
650 -- all DAY records for these timecards, insert them into hxc_temp_timecard_chunks.
651 -- ( id, ovn, ROWID )
652 -- * Insert into hxc_time_building_blocks_ar these records, joining them by ROWID.
653 -- * Delete the above records from hxc_time_building_blocks.
654 -- * Join hxc_temp_timecard_chunks with hxc_time_building_blocks, DETAIL scope and pick up
655 -- all DETAIL records for these timecards, insert them into hxc_temp_timecard_chunks.
656 -- ( id, ovn, ROWID )
657 -- * Insert into hxc_time_building_blocks_ar these records, joining them by ROWID.
658 -- * Delete the above records from hxc_time_building_blocks.
659 -- * Loop to process transaction records.
660 -- Pick up transaction detail records for the records in hxc_temp_timecard_chunks
661 -- as of now. ( detail_id, transaction_id, detail_rowid ), and insert into
662 -- hxc_archive_temp.
663 -- Using the rowids, insert the transaction detail records into
664 -- hxc_transaction_details_ar.
665 -- Delete the records from hxc_transaction_details_ar using the ROWID.
666 -- Delete duplicate transaction ids from hxc_archive_temp.
667 -- Select all deposit transaction records from hxc_transactions table and
668 -- insert into hxc_transactions_ar table.
669 -- Select all retrieval transaction ids and insert into hxc_ar_trans_temp
670 -- table for the parent thread to process.
671 -- Delete all transaction records from hxc_transactions table, if the transaction
672 -- id is present in hxc_archive_temp.
673 -- Delete from hxc_archive temp for the the next iteration of this loop.
674 -- * Loop to process attribute records.
675 -- Pick up attribute usage records for the records in hxc_temp_timecard_chunks
676 -- as of now. ( usage_id, attribute_id, usage_rowid ), and insert into
677 -- hxc_archive_temp.
678 -- Using the rowids, insert the attribute usage records into
679 -- hxc_attribute_usages_ar.
680 -- Delete the records from hxc_transaction_details_ar using the ROWID.
681 -- Delete all records from hxc_archive_temp if attribute_id is
682 -- present in hxc_time_attributes_ar
683 -- Select all attribute records from hxc_time_attributes table and
684 -- insert into hxc_time_attributes_ar table.
685 -- Delete all attribute records from hxc_time_attributes table, if the attribute
686 -- id is present in hxc_archive_temp.
687 -- Delete from hxc_archive temp for the the next iteration of this loop.
688 -- * Delete all other scopes except DETAIL from hxc_temp_timecard_chunks.
689 -- * Select from hxc_ap_detail_links table, the records corresponding to the
690 -- details and insert into hxc_ap_detail_links_ar table.
691 -- * Delete from hxc_ap_detail_links table the above records.
692 -- * Delete from hxc_temp_timecard_chunks all records except the latest ovns.
693 -- * Insert these records into hxc_latest_details.
694 -- * COMMIT and pick up the next chunk.
695 --
696 -- Technical points to look for.
697 -- 1. Simulated Continue with each chunk check.
698 -- Each insert into AR - Delete from core action has to be followed
699 -- by a count check to see if total inserted and total deleted is the same.
700 -- If no, we have to rollback that chunk and proceed with the rest of the
701 -- chunks. That also means that we need to have a 'continue' to next chunk
702 -- after all errored count checks. To enable this, there is an inner loop
703 -- << TO_CONTINUE_TO_NEXT_CHUNK >>. For a normal chunk, this will iterate
704 -- once and exit out ( look at the WHILE condition.) -- meaning there is
705 -- no functional addition here. To continue to next chunk at any point, the
706 -- code will EXIT from this loop. That will bring it to the end of the code inside
707 -- the original loop and would function like a continue. This handles count checks
708 -- after moving data from each set of tables.
709 -- 2. Avoiding issues of resource contention.
710 -- Since we are dealing with multiple sessions doing the same thing, there are issues
711 -- of resource contention. The threads are working in different sets of timecards, but
712 -- still there could be some records shared between the threads. For eg, a retrieval
713 -- transaction id can be shared across two versions of the timecard, and it might
714 -- be possible that they fall in different thread. ( All timecards of the same
715 -- building block id fall in the same thread, but in case of a template overwrite,
716 -- the building block ids are different and might fall in different threads. ).
717 -- Hence the retrieval transactions are all moved to the parent thread for processing, so
718 -- there is no issue of resource contention here.
719 -- Apart from this, there are application period records which are shared. For example,
720 -- by weekly timecards having monthly approval, four timecards can share a single application
721 -- period record, and this might cause a resource contention. But we cannot afford to
722 -- move this also to parent process, because in that case, the application attributes also
723 -- need to be moved out, meaning we lose too much of the multithreaded advantage.
724 -- To work this around, the whole processing is put inside a BEGIN-END block. In case
725 -- of a resource contention ( you would have dup_val_on_index error on the _AR ) tables,
726 -- the process will come to exception block, and wait sleep there for a minute. This would
727 -- let the thread which committed the record earlier to finish. The thread would continue
728 -- after a minute iterating again in the inner while loop as mentioned above.
729 --
730 -- The Worker Process for Restore Data set follows the same logic, except that the
731 -- source and destination tables interchange.
732 --
733
734 hr_general.g_data_migrator_mode := 'Y';
735
736 l_chunk_size := nvl(fnd_profile.value('HXC_ARCHIVE_RESTORE_CHUNK_SIZE'),50);
737
738 fnd_file.put_line(fnd_file.LOG,'--- > Chunk Size is: '||l_chunk_size);
739
740 -- firs let's get the min start time of
741 -- the timecard for this data set
742
743 SELECT start_date,
744 end_date
745 INTO l_data_set_start_date,
746 l_data_set_end_date
747 FROM hxc_data_sets
748 WHERE data_set_id = p_data_set_id;
749
750 OPEN get_tcs ;
751 LOOP
752
753 FETCH get_tcs
754 BULK COLLECT
755 INTO tc_id_tab,
756 tc_ovn_tab LIMIT l_chunk_size ;
757
758 EXIT WHEN tc_id_tab.COUNT = 0;
759
760 l_td_count := 0;
761 l_trans_count := 0;
762 l_tau_count := 0;
763 l_ta_count := 0;
764 data_mismatch := FALSE;
765 iloop := 1;
766 l_chunk_no := l_chunk_no + 1;
767
768 fnd_file.put_line(fnd_file.log,' ');
769 fnd_file.put_line(fnd_file.log,'*****************************************************************');
770 fnd_file.put_line(fnd_file.log,'=================================================================');
771 fnd_file.put_line(fnd_file.log,'Entering in a new chunk ');
772 fnd_file.put_line(fnd_file.log,'==================================================================');
773
774 <<TO_CONTINUE_TO_NEXT_CHUNK >>
775 WHILE iloop = 1
776 LOOP
777 iloop := 0;
778
779 BEGIN
780
781 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
782 INSERT INTO hxc_temp_timecard_chunks
783 ( id,
784 ref_ovn,
785 scope,
786 thread_id )
787 VALUES ( tc_id_tab(i),
788 tc_ovn_tab(i),
789 'TIMECARD',
790 p_thread_id );
791
792
793 INSERT INTO hxc_time_building_blocks_ar
794 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
795 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
796 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
797 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
798 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
799 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
800 TRANSLATION_DISPLAY_KEY)
801 SELECT /*+ INDEX(bkup HXC_TIME_BUILDING_BLOCKS_PK) */
802 bkup.DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
803 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
804 bkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
805 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
806 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
807 TRANSLATION_DISPLAY_KEY
808 FROM hxc_temp_timecard_chunks temp,
809 hxc_time_building_blocks bkup
810 WHERE bkup.scope = 'TIMECARD'
811 AND bkup.time_building_block_id = temp.id
812 AND bkup.object_version_number = temp.ref_ovn
813 AND bkup.data_set_id = p_data_set_id
814 AND thread_id = p_thread_id ;
815
816 l_tc_count := sql%rowcount;
817
818 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
819 DELETE
820 FROM hxc_time_building_blocks
821 WHERE time_building_block_id = tc_id_tab(i)
822 AND object_version_number = tc_ovn_tab(i) ;
823
824 l_tc_del_count := sql%rowcount;
825
826 hxc_archive.log_data_mismatch(p_scope => 'Timecard',
827 p_insert => l_tc_count,
828 p_delete => l_tc_del_count,
829 p_mismatch => data_mismatch
830 );
831 IF data_mismatch
832 THEN
833 write_data_mismatch('Timecard');
834 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
835 END IF;
836
837 FORALL i IN tc_id_tab.FIRST..tc_id_tab.LAST
838 INSERT INTO hxc_ar_detail_log
839 (time_building_block_id,
840 object_version_number,
841 process_type,
842 thread_id,
843 chunk_number)
844 VALUES (tc_id_tab(i),
845 tc_ovn_tab(i),
846 'ARCHIVE',
847 p_thread_id,
848 l_chunk_no);
849
850
851 INSERT INTO hxc_temp_timecard_chunks
852 ( id,
853 scope,
854 ref_rowid,
855 thread_id )
856 SELECT /*+ ORDERED */
857 DISTINCT talbkup.application_period_id,
858 'APPLICATION_PERIOD',
859 ROWIDTOCHAR(talbkup.ROWID),
860 thread_id
861 FROM hxc_temp_timecard_chunks temp,
862 hxc_tc_ap_links talbkup
863 WHERE temp.id = talbkup.timecard_id
864 AND temp.scope IN ('TIMECARD')
865 AND thread_id = p_thread_id ;
866
867
868 INSERT INTO hxc_tc_ap_links_ar
869 ( timecard_id,
870 application_period_id)
871 SELECT timecard_id,
872 application_period_id
873 FROM hxc_temp_timecard_chunks temp,
874 hxc_tc_ap_links talbkup
875 WHERE temp.ref_rowid = talbkup.ROWID
876 AND temp.scope = ('APPLICATION_PERIOD')
877 AND thread_id = p_thread_id ;
878
879
880 l_tal_count := SQL%ROWCOUNT;
881
882
883 DELETE FROM hxc_tc_ap_links
884 WHERE ROWID IN ( SELECT CHARTOROWID(ref_rowid)
885 FROM hxc_temp_timecard_chunks
886 WHERE scope = 'APPLICATION_PERIOD'
887 AND thread_id = p_thread_id );
888
889 l_tal_del_count := SQL%ROWCOUNT;
890
891 hxc_archive.log_data_mismatch(p_scope => 'TC App LINKs ',
892 p_insert => l_tal_count,
893 p_delete => l_tal_del_count,
894 p_mismatch => data_mismatch );
895
896 IF data_mismatch
897 THEN
898 write_data_mismatch('TC App LINKs ');
899 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
900 END IF;
901
902 INSERT INTO hxc_time_building_blocks_ar
903 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
904 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
905 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
906 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
907 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
908 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
909 SELECT /*+ ORDERED */
910 p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
911 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
912 appbkup.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
913 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
914 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,
915 APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY
916 FROM hxc_time_building_blocks appbkup
917 WHERE appbkup.scope = 'APPLICATION_PERIOD'
918 AND appbkup.time_building_block_id IN ( SELECT id
919 FROM hxc_temp_timecard_chunks temp
920 WHERE temp.scope IN
921 ('APPLICATION_PERIOD')
922 AND thread_id = p_thread_id ) ;
923
924 l_app_period_count := SQL%ROWCOUNT;
925
926 DELETE FROM hxc_time_building_blocks
927 WHERE time_building_block_id IN ( SELECT id
928 FROM hxc_temp_timecard_chunks
929 WHERE scope IN
930 ('APPLICATION_PERIOD')
931 AND thread_id = p_thread_id );
932
933 l_app_del_count := SQL%ROWCOUNT;
934
935 hxc_archive.log_data_mismatch( p_scope => 'Application Period ',
936 p_insert => l_app_period_count,
937 p_delete => l_app_del_count,
938 p_mismatch => data_mismatch );
939
940 IF data_mismatch
941 THEN
942 write_data_mismatch('Application Period ');
943 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
944 END IF;
945
946 INSERT INTO hxc_app_period_summary_ar
947 (APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,TIME_RECIPIENT_ID,
948 TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,RECIPIENT_SEQUENCE,
949 CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,APPROVER_ID,APPROVAL_COMP_ID,
950 APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,APPROVAL_ITEM_KEY,DATA_SET_ID)
951 SELECT APPLICATION_PERIOD_ID,APPLICATION_PERIOD_OVN,APPROVAL_STATUS,
952 TIME_RECIPIENT_ID,TIME_CATEGORY_ID,START_TIME,STOP_TIME,RESOURCE_ID,
953 RECIPIENT_SEQUENCE,CATEGORY_SEQUENCE,CREATION_DATE,NOTIFICATION_STATUS,
954 APPROVER_ID,APPROVAL_COMP_ID,APPROVAL_ITEM_TYPE,APPROVAL_PROCESS_NAME,
955 APPROVAL_ITEM_KEY,p_data_set_id
956 FROM hxc_app_period_summary apsbkup
957 WHERE application_period_id IN (SELECT id
958 FROM hxc_temp_timecard_chunks
959 WHERE scope = 'APPLICATION_PERIOD'
960 AND thread_id = p_thread_id );
961
962 l_app_period_sum_count := SQL%ROWCOUNT;
963
964
965 DELETE FROM hxc_app_period_summary
966 WHERE application_period_id IN (SELECT id
967 FROM hxc_temp_timecard_chunks
968 WHERE scope = 'APPLICATION_PERIOD'
969 AND thread_id = p_thread_id );
970
971 l_app_sum_del_count := SQL%ROWCOUNT;
972
973 hxc_archive.log_data_mismatch( p_scope => 'App Period Summary ',
974 p_insert => l_app_period_sum_count,
975 p_delete => l_app_sum_del_count,
976 p_mismatch => data_mismatch );
977
978 IF data_mismatch
979 THEN
980 write_data_mismatch('App Period Summary ');
981 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
982 END IF;
983
984 INSERT INTO hxc_temp_timecard_chunks
985 ( id,
986 ref_ovn,
987 scope,
988 ref_rowid,
989 day_start_time,
990 day_stop_time,
991 thread_id )
992 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BUILDING_BLOCKS_FK3) */
993 time_building_block_id,
994 object_version_number,
995 'DAY',
996 ROWIDTOCHAR(ar.ROWID),
997 ar.start_time,
998 ar.stop_time,
999 thread_id
1000 FROM hxc_temp_timecard_chunks temp,
1001 hxc_time_building_blocks ar
1002 WHERE parent_building_block_id = temp.id
1003 AND parent_building_block_ovn = temp.ref_ovn
1004 AND temp.scope = 'TIMECARD'
1005 AND thread_id = p_thread_id ;
1006
1007
1008 INSERT INTO hxc_time_building_blocks_ar
1009 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1010 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1011 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1012 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
1013 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
1014 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
1015 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
1016 p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1017 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1018 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1019 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
1020 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
1021 TRANSLATION_DISPLAY_KEY
1022 FROM hxc_temp_timecard_chunks temp,
1023 hxc_time_building_blocks bkupday
1024 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
1025 AND temp.scope = 'DAY'
1026 AND thread_id = p_thread_id;
1027
1028
1029 l_day_count := sql%rowcount;
1030
1031 l_day_del_count := 0;
1032 OPEN get_tbb_rowid('DAY');
1033 LOOP
1034 FETCH get_tbb_rowid
1035 BULK COLLECT INTO
1036 rowid_tab LIMIT 501;
1037
1038 EXIT WHEN rowid_tab.COUNT = 0;
1039
1040 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
1041 DELETE FROM hxc_time_building_blocks
1042 WHERE ROWID = CHARTOROWID(rowid_tab(i));
1043
1044 l_day_del_count := l_day_del_count + SQL%ROWCOUNT;
1045 rowid_tab.DELETE;
1046 END LOOP;
1047 CLOSE get_tbb_rowid;
1048
1049 hxc_archive.log_data_mismatch( p_scope => 'Day ',
1050 p_insert => l_day_count,
1051 p_delete => l_day_del_count,
1052 p_mismatch => data_mismatch );
1053
1054 IF data_mismatch
1055 THEN
1056 write_data_mismatch('Day ');
1057 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1058 END IF;
1059
1060 INSERT INTO hxc_temp_timecard_chunks
1061 ( id,
1062 ref_ovn,
1063 scope,
1064 day_start_time,
1065 day_stop_time,
1066 ref_rowid,
1067 thread_id )
1068 SELECT /*+ LEADING(TEMP) INDEX(AR HXC_TIME_BUILDING_BLOCKS_FK3) */
1069 time_building_block_id,
1070 object_version_number,
1071 'DETAIL',
1072 nvl(day_start_time,ar.start_time),
1073 nvl(day_stop_time,ar.stop_time),
1074 ROWIDTOCHAR(ar.ROWID),
1075 thread_id
1076 FROM hxc_temp_timecard_chunks temp,
1077 hxc_time_building_blocks ar
1078 WHERE parent_building_block_id = temp.id
1079 AND parent_building_block_ovn = temp.ref_ovn
1080 AND temp.scope = 'DAY'
1081 AND thread_id = p_thread_id ;
1082
1083
1084 INSERT INTO hxc_time_building_blocks_ar
1085 (DATA_SET_ID,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1086 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1087 SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1088 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,
1089 RESOURCE_TYPE,APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,
1090 PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,TRANSLATION_DISPLAY_KEY)
1091 SELECT /*+ LEADING(temp) NO_INDEX(bkupday)*/
1092 p_data_set_id,TIME_BUILDING_BLOCK_ID,TYPE,MEASURE,
1093 UNIT_OF_MEASURE,START_TIME,STOP_TIME,PARENT_BUILDING_BLOCK_ID,
1094 bkupday.SCOPE,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
1095 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,APPROVAL_STATUS,RESOURCE_ID,RESOURCE_TYPE,
1096 APPROVAL_STYLE_ID,DATE_FROM,DATE_TO,COMMENT_TEXT,PARENT_BUILDING_BLOCK_OVN,APPLICATION_SET_ID,
1097 TRANSLATION_DISPLAY_KEY
1098 FROM hxc_temp_timecard_chunks temp,
1099 hxc_time_building_blocks bkupday
1100 WHERE bkupday.ROWID = CHARTOROWID(temp.ref_rowid)
1101 AND temp.scope = 'DETAIL'
1102 AND thread_id = p_thread_id ;
1103
1104 l_det_count := sql%rowcount;
1105
1106 l_det_del_count := 0;
1107 OPEN get_tbb_rowid('DETAIL');
1108 LOOP
1109 FETCH get_tbb_rowid
1110 BULK COLLECT INTO
1111 rowid_tab LIMIT 501;
1112
1113 EXIT WHEN rowid_tab.COUNT = 0;
1114
1115 FORALL i IN rowid_tab.FIRST..rowid_tab.LAST
1116 DELETE FROM hxc_time_building_blocks
1117 WHERE ROWID = CHARTOROWID(rowid_tab(i));
1118
1119 l_det_del_count := l_det_del_count + SQL%ROWCOUNT;
1120 rowid_tab.DELETE;
1121 END LOOP;
1122 CLOSE get_tbb_rowid;
1123
1124 hxc_archive.log_data_mismatch( p_scope => 'Detail ',
1125 p_insert => l_det_count,
1126 p_delete => l_det_del_count,
1127 p_mismatch => data_mismatch );
1128 IF data_mismatch
1129 THEN
1130 write_data_mismatch('Detail ');
1131 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1132 END IF;
1133
1134 l_td_count := 0;
1135 l_td_del_count := 0;
1136 l_trans_count := 0;
1137 l_trans_del_count := 0;
1138
1139 IF get_transactions%ISOPEN
1140 THEN
1141 CLOSE get_transactions;
1142 END IF;
1143 OPEN get_transactions ;
1144 LOOP
1145 FETCH get_transactions
1146 BULK COLLECT INTO trans_detail_tab,
1147 trans_tab,
1148 td_rowid_tab LIMIT 250;
1149
1150 EXIT WHEN trans_detail_tab.COUNT = 0 ;
1151
1152 FORALL i IN trans_detail_tab.FIRST..trans_detail_tab.LAST
1153 INSERT INTO hxc_archive_temp
1154 ( detail_id,
1155 master_id,
1156 ref_rowid,
1157 thread_id )
1158 VALUES ( trans_detail_tab(i),
1159 trans_tab(i),
1160 td_rowid_tab(i),
1161 p_thread_id );
1162
1163 INSERT INTO hxc_transaction_details_ar
1164 (DATA_SET_ID,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1165 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1166 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN)
1167 SELECT /*+ LEADING(temp) USE_NL(bkuptxnd) */
1168 p_data_set_id,TRANSACTION_DETAIL_ID,TIME_BUILDING_BLOCK_ID,TRANSACTION_ID,
1169 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1170 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_BUILDING_BLOCK_OVN
1171 FROM hxc_archive_temp temp,
1172 hxc_transaction_details bkuptxnd
1173 WHERE CHARTOROWID(temp.ref_rowid) = bkuptxnd.ROWID
1174 AND thread_id = p_thread_id ;
1175
1176 l_td_count := l_td_count + SQL%ROWCOUNT;
1177
1178 FORALL i IN td_rowid_tab.FIRST..td_rowid_tab.LAST
1179 DELETE FROM hxc_transaction_details
1180 WHERE ROWID = CHARTOROWID(td_rowid_tab(i));
1181
1182 l_td_del_count := l_td_del_count + SQL%ROWCOUNT;
1183
1184 trans_detail_tab.DELETE;
1185 trans_tab.DELETE;
1186 td_rowid_tab.DELETE;
1187
1188 OPEN get_dup_trans;
1189 LOOP
1190 FETCH get_dup_trans
1191 BULK COLLECT INTO trans_id_tab,
1192 uniq_rowid_tab LIMIT 500;
1193
1194 EXIT WHEN trans_id_tab.COUNT = 0;
1195
1196 FORALL i IN trans_id_tab.FIRST..trans_id_tab.LAST
1197 DELETE FROM hxc_archive_temp
1198 WHERE master_id = trans_id_tab(i)
1199 AND ROWID <> uniq_rowid_tab(i)
1200 AND thread_id = p_thread_id ;
1201 END LOOP;
1202 CLOSE get_dup_trans;
1203
1204 DELETE FROM hxc_archive_temp
1205 WHERE EXISTS ( SELECT 1
1206 FROM hxc_transactions_ar
1207 WHERE transaction_id = master_id )
1208 AND thread_id = p_thread_id ;
1209
1210 INSERT INTO hxc_transactions_ar
1211 (DATA_SET_ID,TRANSACTION_ID,TRANSACTION_PROCESS_ID,TRANSACTION_DATE,TYPE,
1212 STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,CREATED_BY,CREATION_DATE,
1213 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TRANSACTION_CODE)
1214 SELECT /*+ LEADING(temp) USE_NL(bkuptxn) */
1215 p_data_set_id,TRANSACTION_ID,TRANSACTION_PROCESS_ID,
1216 TRANSACTION_DATE,TYPE,STATUS,EXCEPTION_DESCRIPTION,OBJECT_VERSION_NUMBER,
1217 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1218 TRANSACTION_CODE
1219 FROM hxc_transactions bkuptxn,
1220 hxc_archive_temp temp
1221 WHERE transaction_id = master_id
1222 AND thread_id = p_thread_id
1223 AND transaction_id NOT IN ( SELECT transaction_id
1224 FROM hxc_transactions_ar hxc
1225 WHERE bkuptxn.transaction_id = hxc.transaction_id)
1226 AND type <> 'RETRIEVAL'
1227 ;
1228
1229 l_trans_count := l_trans_count + SQL%ROWCOUNT;
1230
1231 DELETE /*+ LEADING(temp) USE_NL(bkuptxn) */
1232 FROM hxc_transactions bkuptxn
1233 WHERE transaction_id IN ( SELECT master_id
1234 FROM hxc_archive_temp temp
1235 WHERE thread_id = p_thread_id)
1236 AND type <> 'RETRIEVAL';
1237
1238 l_trans_del_count := l_trans_del_count + SQL%ROWCOUNT;
1239
1240 INSERT INTO hxc_ar_trans_temp
1241 ( transaction_id, data_set_id, thread_id, trans_rowid )
1242 SELECT bkuptxn.transaction_id,
1243 p_data_set_id,
1244 p_thread_id,
1245 ROWIDTOCHAR(bkuptxn.ROWID)
1246 FROM hxc_transactions bkuptxn,
1247 hxc_archive_temp temp
1248 WHERE transaction_id = master_id
1249 AND thread_id = p_thread_id
1250 AND type = 'RETRIEVAL';
1251
1252 DELETE FROM hxc_archive_temp
1253 WHERE thread_id = p_thread_id ;
1254
1255 END LOOP;
1256 CLOSE get_transactions;
1257
1258 hxc_archive.log_data_mismatch( p_scope => 'Transaction Detail ',
1259 p_insert => l_td_count,
1260 p_delete => l_td_del_count,
1261 p_mismatch => data_mismatch );
1262
1263 IF data_mismatch
1264 THEN
1265 write_data_mismatch('Transaction Detail ');
1266 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1267 END IF;
1268
1269 hxc_archive.log_data_mismatch( p_scope => 'Transaction ',
1270 p_insert => l_trans_count,
1271 p_delete => l_trans_del_count,
1272 p_mismatch => data_mismatch );
1273
1274 IF data_mismatch
1275 THEN
1276 write_data_mismatch('Transaction ');
1277 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1278 END IF;
1279
1280 l_tau_count := 0;
1281 l_tau_del_count := 0;
1282 l_ta_count := 0;
1283 l_ta_del_count := 0;
1284 IF get_attributes%ISOPEN
1285 THEN
1286 CLOSE get_attributes;
1287 END IF;
1288 OPEN get_attributes;
1289 LOOP
1290 FETCH get_attributes
1291 BULK COLLECT INTO usage_tab,
1292 attribute_tab,
1293 usage_rowid_tab LIMIT 250;
1294
1295 EXIT WHEN usage_tab.COUNT = 0;
1296
1297
1298 FORALL i IN usage_tab.FIRST..usage_tab.LAST
1299 INSERT INTO hxc_archive_temp
1300 ( detail_id,
1301 master_id,
1302 ref_rowid,
1303 thread_id)
1304 VALUES ( usage_tab(i),
1305 attribute_tab(i),
1306 usage_rowid_tab(i),
1307 p_thread_id );
1308
1309
1310 INSERT INTO hxc_time_attribute_usages_ar
1311 (DATA_SET_ID,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1312 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1313 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN)
1314 SELECT /*+ LEADING(temp) USE_NL(bkuptau) */
1315 p_data_set_id,TIME_ATTRIBUTE_USAGE_ID,TIME_ATTRIBUTE_ID,TIME_BUILDING_BLOCK_ID,
1316 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,
1317 OBJECT_VERSION_NUMBER,TIME_BUILDING_BLOCK_OVN
1318 FROM hxc_archive_temp temp,
1319 hxc_time_attribute_usages bkuptau
1320 WHERE bkuptau.ROWID = CHARTOROWID(temp.ref_rowid)
1321 AND thread_id = p_thread_id ;
1322
1323 l_tau_count := l_tau_count + SQL%ROWCOUNT;
1324
1325 FORALL i IN usage_rowid_tab.FIRST..usage_rowid_tab.LAST
1326 DELETE FROM hxc_time_attribute_usages
1327 WHERE ROWID = CHARTOROWID(usage_rowid_tab(i)) ;
1328
1329 l_tau_del_count := l_tau_del_count + SQL%ROWCOUNT;
1330
1331 DELETE FROM hxc_archive_temp
1332 WHERE EXISTS ( SELECT 1
1333 FROM hxc_time_attributes_ar
1334 WHERE time_attribute_id = master_id )
1335 AND thread_id = p_thread_id ;
1336
1337 INSERT INTO hxc_time_attributes_ar
1338 (ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1339 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1340 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1341 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1342 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1343 ATTRIBUTE14,CONSOLIDATED_FLAG,DATA_SET_ID)
1344 SELECT /*+ LEADING(temp) USE_NL(bkupta) */
1345 ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,
1346 ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,
1347 ATTRIBUTE29,ATTRIBUTE30,BLD_BLK_INFO_TYPE_ID,OBJECT_VERSION_NUMBER,TIME_ATTRIBUTE_ID,
1348 ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,
1349 ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,
1350 ATTRIBUTE14,null,p_data_set_id
1351 FROM hxc_time_attributes bkupta
1352 WHERE bkupta.time_attribute_id in ( SELECT /*+ NO_INDEX(temp) */
1353 master_id
1354 FROM hxc_archive_temp temp
1355 WHERE thread_id = p_thread_id );
1356
1357 l_ta_count := l_ta_count + SQL%ROWCOUNT;
1358
1359 DELETE /*+ LEADING(temp) USE_NL(bkupta) */
1360 FROM hxc_time_attributes bkupta
1361 WHERE time_attribute_id IN ( SELECT /*+ NO_INDEX(temp) */
1362 master_id
1363 FROM hxc_archive_temp temp
1364 WHERE thread_id = p_thread_id ) ;
1365
1366 l_ta_del_count := l_ta_del_count + SQL%ROWCOUNT;
1367
1368 usage_tab.DELETE;
1369 attribute_tab.DELETE;
1370 usage_rowid_tab.DELETE;
1371
1372 DELETE FROM hxc_archive_temp
1373 WHERE thread_id = p_thread_id ;
1374
1375 END LOOP ;
1376 CLOSE get_attributes;
1377
1378 hxc_archive.log_data_mismatch( p_scope => 'Attribute Usages ',
1379 p_insert => l_tau_count,
1380 p_delete => l_tau_del_count,
1381 p_mismatch => data_mismatch );
1382 IF data_mismatch
1383 THEN
1384 write_data_mismatch('Attribute Usages ');
1385 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1386 END IF;
1387
1388 hxc_archive.log_data_mismatch( p_scope => 'Attributes ',
1389 p_insert => l_ta_count,
1390 p_delete => l_ta_del_count,
1391 p_mismatch => data_mismatch );
1392
1393 IF data_mismatch
1394 THEN
1395 write_data_mismatch('Attributes ');
1396 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1397 END IF;
1398
1399 DELETE FROM hxc_temp_timecard_chunks
1400 WHERE scope IN ( 'TIMECARD', 'DAY','APPLICATION_PERIOD')
1401 AND thread_id = p_thread_id ;
1402
1403
1404 INSERT INTO hxc_ap_detail_links_ar
1405 (application_period_id,
1406 time_building_block_id,
1407 time_building_block_ovn)
1408 SELECT application_period_id, time_building_block_id, time_building_block_ovn
1409 FROM hxc_temp_timecard_chunks temp,
1410 hxc_ap_detail_links adlbkup
1411 WHERE temp.id = adlbkup.time_building_block_id
1412 AND temp.ref_ovn = adlbkup.time_building_block_ovn
1413 AND temp.scope = ('DETAIL')
1414 AND thread_id = p_thread_id ;
1415
1416 l_adl_count := SQL%ROWCOUNT;
1417
1418
1419 DELETE FROM hxc_ap_detail_links
1420 WHERE (time_building_block_id,time_building_block_ovn)
1421 IN ( SELECT id,
1422 ref_ovn
1423 FROM hxc_temp_timecard_chunks
1424 WHERE scope = 'DETAIL'
1425 AND thread_id = p_thread_id );
1426
1427 l_adl_del_count := SQL%ROWCOUNT;
1428
1429 hxc_archive.log_data_mismatch( p_scope => 'App Detail LINKs ',
1430 p_insert => l_adl_count,
1431 p_delete => l_adl_del_count,
1432 p_mismatch => data_mismatch );
1433
1434 IF data_mismatch
1435 THEN
1436 write_data_mismatch('App Detail LINKs ');
1437 EXIT TO_CONTINUE_TO_NEXT_CHUNK ;
1438 END IF;
1439
1440 OPEN get_latest_details;
1441 LOOP
1442 FETCH get_latest_details
1443 BULK COLLECT INTO latest_id_tab,
1444 latest_rowid_tab LIMIT 500;
1445 EXIT WHEN latest_id_tab.COUNT = 0;
1446 FORALL i IN latest_id_Tab.FIRST..latest_id_Tab.last
1447 DELETE FROM hxc_latest_details
1448 WHERE ROWID = CHARTOROWID(latest_rowid_tab(i)) ;
1449
1450 latest_rowid_tab.DELETE;
1451 latest_id_tab.DELETE;
1452 END LOOP;
1453 CLOSE get_latest_details;
1454
1455
1456 fnd_file.put_line(fnd_file.log,' ');
1457 fnd_file.put_line(fnd_file.log,' ');
1458 fnd_file.put_line(fnd_file.log,'========================================================== ');
1459 fnd_file.put_line(fnd_file.log,' ');
1460 fnd_file.put_line(fnd_file.log,' ');
1461 COMMIT;
1462 tc_id_tab.DELETE;
1463 tc_ovn_tab.DELETE;
1464 iloop:= 0;
1465 EXCEPTION
1466 WHEN DUP_VAL_ON_INDEX THEN
1467 ROLLBACK;
1468 iloop := 1;
1469 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention, will sleep for a minute ');
1470 fnd_file.put_line(fnd_file.log,'=================================================================');
1471 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1472 fnd_file.put_line(fnd_file.log,'==================================================================');
1473 l_td_count := 0;
1474 l_trans_count := 0;
1475 l_tau_count := 0;
1476 l_ta_count := 0;
1477 data_mismatch := FALSE;
1478 dbms_lock.sleep(60);
1479 WHEN DEADLOCK_DETECTED THEN
1480 ROLLBACK;
1481 iloop := 1;
1482 fnd_file.put_line(fnd_file.LOG,'This chunk found a resource contention(deadlock), will sleep for a minute ');
1483 fnd_file.put_line(fnd_file.log,'=================================================================');
1484 fnd_file.put_line(fnd_file.log,'Reprocessing this chunk ');
1485 fnd_file.put_line(fnd_file.log,'==================================================================');
1486 l_td_count := 0;
1487 l_trans_count := 0;
1488 l_tau_count := 0;
1489 l_ta_count := 0;
1490 data_mismatch := FALSE;
1491 dbms_lock.sleep(60);
1492 END ;
1493 END LOOP TO_CONTINUE_TO_NEXT_CHUNK ;
1494
1495 END LOOP;
1496
1497 END child_archive_process;
1498
1499
1500
1501 PROCEDURE log_data_mismatch( p_scope IN VARCHAR2,
1502 p_insert IN NUMBER,
1503 p_delete IN NUMBER,
1504 p_mismatch IN OUT NOCOPY BOOLEAN)
1505 IS
1506
1507 BEGIN
1508 IF p_insert = p_delete
1509 THEN
1510 fnd_file.put_line(fnd_file.log,' ');
1511 fnd_file.put_line(fnd_file.log,' '||p_scope||' records moved : '||p_insert);
1512 ELSE
1513 fnd_file.put_line(fnd_file.log,' ');
1514 fnd_file.put_line(fnd_file.log,'==========================================================================');
1515 fnd_file.put_line(fnd_file.log,' An error occured while processing '||p_scope||' records');
1516 fnd_file.put_line(fnd_file.log,'==========================================================================');
1517 fnd_file.put_line(fnd_file.log, p_insert||' records were inserted into offline table ');
1518 fnd_file.put_line(fnd_file.log, p_delete||' records were deleted from online table ');
1519 fnd_file.put_line(fnd_file.log,'This chunk is rolled back, pls check up data. ');
1520 p_mismatch := TRUE;
1521 ROLLBACK;
1522 END IF;
1523
1524 RETURN;
1525 END log_data_mismatch ;
1526
1527
1528 END hxc_archive;
[Home] [Help]