1 PACKAGE BODY hxc_archive_restore_process as
2 /* $Header: hxcarcrespkg.pkb 120.8.12010000.4 2008/10/16 10:00:51 asrajago ship $ */
3
4
5
6 ----------------------------------------------------------------------------
7 -- Procedure Name : define_data_set
8 -- Description : This is the starting point of the concurrent program
9 -- 'Define Data Set'
10 ----------------------------------------------------------------------------
11 PROCEDURE define_data_set(errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY NUMBER,
13 p_data_set_name IN VARCHAR2,
14 p_description IN VARCHAR2,
15 p_start_date IN VARCHAR2,
16 p_stop_date IN VARCHAR2)
17 IS
18
19 l_data_set_id number;
20 l_start_date date;
21 l_stop_date date;
22 l_dummy number;
23
24 BEGIN
25
26
27 -- set the canonical start data and stop date of the data seto
28 l_start_date := trunc(fnd_date.canonical_to_date(p_start_date));
29 l_stop_date := trunc(fnd_date.canonical_to_date(p_stop_date)) + 1 - (1 / (24 * 60 * 60));
30
31 fnd_file.put_line(fnd_file.LOG,'--- >Parameters:');
32 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Name :' || p_data_set_name);
33 fnd_file.put_line(fnd_file.LOG,'--- >Description :' || p_description);
34 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Start Date :' || l_start_date);
35 fnd_file.put_line(fnd_file.LOG,'--- >Data Set End Date :' || l_stop_date);
36
37 -- check if the range of the data set does not overlap with an existing data set.
38 -- if it does we stop the process
39 fnd_file.put_line(fnd_file.LOG,'--- >Before validating Data Set range and unique name');
40
41 IF NOT (hxc_data_set.validate_data_set_range(
42 p_data_set_name => p_data_set_name,
43 p_start_date => l_start_date,
44 p_stop_date => l_stop_date)) THEN
45 retcode := 2;
46 return;
47 END IF;
48
49 fnd_file.put_line(fnd_file.LOG,'--- >Before inserting the record into HXC_DATA_SETS table');
50 --
51 -- insert into the data set.
52 --
53 hxc_data_set.insert_into_data_set(l_data_set_id,
54 p_data_set_name,
55 p_description,
56 l_start_date,
57 l_stop_date,
58 'MARKING_IN_PROGRESS');
59
60 fnd_file.put_line(fnd_file.LOG,'--- > New Data Set id is: '||l_data_set_id);
61
62 hxc_data_set.show_data_set;
63
64 fnd_file.put_line(fnd_file.LOG,'--- > Marking the tables with the data set');
65
66 --
67 -- Mark the tables with the data set
68 --
69 hxc_data_set.mark_tables_with_data_set
70 (p_data_set_id => l_data_set_id,
71 p_start_date => l_start_date,
72 p_stop_date => l_stop_date);
73
74 -- finally update the data set to be ON_LINE
75 update hxc_data_sets
76 set status = 'ON_LINE'
77 where data_set_id = l_data_set_id;
78
79 fnd_file.put_line(fnd_file.LOG,'--- > End process ');
80
81
82 EXCEPTION
83 WHEN OTHERS THEN
84 fnd_file.put_line(fnd_file.LOG,'Define Data Set process failed because of :'||sqlerrm);
85 fnd_file.put_line(fnd_file.LOG,'Define Data Set process failed because of :'||sqlerrm);
86
87 ROLLBACK;
88 retcode := 2;
89
90 end define_data_set;
91
92
93 ----------------------------------------------------------------------------
94 -- Procedure Name : undo_define_data_set
95 -- Description : This is the starting point of the concurrent program
96 -- 'Undo Define Data Set'
97 ----------------------------------------------------------------------------
98 PROCEDURE undo_define_data_set
99 (errbuf OUT NOCOPY VARCHAR2,
100 retcode OUT NOCOPY NUMBER,
101 p_data_set_id IN NUMBER)
102 IS
103
104 l_data_set_name hxc_data_sets.data_set_name%type;
105 l_description hxc_data_sets.description%type;
106 l_start_date hxc_data_sets.start_date%type;
107 l_stop_date hxc_data_sets.end_date%type;
108 l_data_set_mode hxc_data_sets.data_set_mode%type;
109 l_status hxc_data_sets.status%type;
110 l_validation_status hxc_data_sets.validation_status%type;
111 l_found_data_set BOOLEAN := FALSE;
112
113
114 BEGIN
115
116 --
117 -- get the data set information
118 --
119 hxc_data_set.get_data_set_info
120 (p_data_set_id => p_data_set_id,
121 p_data_set_name => l_data_set_name,
122 p_description => l_description,
123 p_start_date => l_start_date,
124 p_stop_date => l_stop_date,
125 p_data_set_mode => l_data_set_mode,
126 p_status => l_status,
127 p_validation_status => l_validation_status,
128 p_found_data_set => l_found_data_set);
129
130 --
131 -- if the data set is not found
132 -- we are erroring out the process
133 --
134 IF NOT(l_found_data_set) THEN
135 --error
136 retcode := 2;
137 fnd_file.put_line
138 (fnd_file.LOG,'--- >Data Set Not Found');
139 RETURN;
140 END IF;
141
142 fnd_file.put_line(fnd_file.LOG,'--- >Parameters:');
143 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Id :' || p_data_set_id);
144 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Name :' || l_data_set_name);
145 --fnd_file.put_line(fnd_file.LOG,'--- >Data Set Status :' || l_status);
146
147 -- if the status is different of online I cannot undo the data set
148 IF l_status not in ('ON_LINE','MARKING_IN_PROGRESS') THEN
149 --error
150 retcode := 2;
151 fnd_file.put_line
152 (fnd_file.LOG,'--- >Data Set needs to be in ON_LINE status for Undo Define Data Set process');
153 RETURN;
154 END IF;
155
156 --
157 -- Undo define data set
158 --
159 hxc_data_set.undo_define_data_set(p_data_set_id => p_data_set_id);
160
161
162 EXCEPTION
163 WHEN others THEN
164 fnd_file.put_line
165 (fnd_file.LOG,'--- >Undo Define Data Set failed because of following error: '||sqlerrm);
166 ROLLBACK;
167 retcode := 2;
168 END undo_define_data_set;
169
170
171 ----------------------------------------------------------------------------
172 -- Procedure Name : validate_data_set
173 -- Description : This is the starting point of the concurrent program
174 -- 'Validate Data Set'
175 ----------------------------------------------------------------------------
176 PROCEDURE validate_data_set(errbuf OUT NOCOPY varchar2,
177 retcode OUT NOCOPY number,
178 p_data_set_id in number)
179 IS
180
181 l_error_count number;
182
183 BEGIN
184
185 --
186 -- validate the data set
187 --
188 hxc_data_set.validate_data_set (p_data_set_id,
189 l_error_count,
190 false
191 );
192
193 IF (l_error_count>1) THEN
194 retcode:=1;
195 END IF;
196
197
198 Exception when others then
199
200 --Set in error if it fails because of some process related problems.
201 fnd_message.set_name('HXC', 'HXC_VALIDATE_PROCESS_FAIL');
202 fnd_message.set_token('ERR',SQLERRM );
203 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
204 rollback;
205 retcode := 2;
206
207 END validate_data_set;
208
209 ----------------------------------------------------------------------------
210 -- Procedure Name : archive_data_set
211 -- Description : This is the starting point of the concurrent program
212 -- 'Archive Data Set'
213 ----------------------------------------------------------------------------
214 PROCEDURE archive_data_set(errbuf OUT NOCOPY varchar2,
215 retcode OUT NOCOPY number,
216 p_data_set_id in number,
217 p_ignore_errors in varchar2)
218 IS
219
220 l_dummy varchar2(1);
221 l_errbuf varchar2(240);
222
223 l_iter BINARY_INTEGER;
224
225 l_retcode number;
226 l_rerun varchar2(1);
227
228 l_data_set_age number;
229 l_min_data_set_age number := 0;
230
231 -- all the counter
232 -- Core table first count before archiving
233 l_tbb_count_1 NUMBER;
234 l_tau_count_1 NUMBER;
235 l_ta_count_1 NUMBER;
236 l_td_count_1 NUMBER;
237 l_trans_count_1 NUMBER;
238 l_tal_count_1 NUMBER;
239 l_aps_count_1 NUMBER;
240 l_adl_count_1 NUMBER;
241 l_ld_count_1 NUMBER;
242 l_ts_count_1 NUMBER;
243
244 -- backup table first count before archiving
245 l_tbb_ar_count_1 NUMBER;
246 l_tau_ar_count_1 NUMBER;
247 l_ta_ar_count_1 NUMBER;
248 l_td_ar_count_1 NUMBER;
249 l_trans_ar_count_1 NUMBER;
250 l_tal_ar_count_1 NUMBER;
251 l_adl_ar_count_1 NUMBER;
252 l_aps_ar_count_1 NUMBER;
253
254 -- Core table first count after archiving
255 l_tbb_count_2 NUMBER;
256 l_tau_count_2 NUMBER;
257 l_ta_count_2 NUMBER;
258 l_td_count_2 NUMBER;
259 l_trans_count_2 NUMBER;
260 l_tal_count_2 NUMBER;
261 l_aps_count_2 NUMBER;
262 l_adl_count_2 NUMBER;
263 l_ld_count_2 NUMBER;
264 l_ts_count_2 NUMBER;
265
266 -- backup table first count after archiving
267 l_tbb_ar_count_2 NUMBER;
268 l_tau_ar_count_2 NUMBER;
269 l_ta_ar_count_2 NUMBER;
270 l_td_ar_count_2 NUMBER;
271 l_trans_ar_count_2 NUMBER;
272 l_tal_ar_count_2 NUMBER;
273 l_adl_ar_count_2 NUMBER;
274 l_aps_ar_count_2 NUMBER;
275
276
277 l_data_set_start_date date;
278 l_data_set_end_date date;
279 l_data_set_name VARCHAR2(80);-- hxc_data_sets.data_set_name%type;
280 l_description hxc_data_sets.description%type;
281 l_data_set_mode hxc_data_sets.data_set_mode%type;
282 l_current_status hxc_data_sets.status%type;
283 l_validation_status hxc_data_sets.validation_status%type;
284 l_found_data_set BOOLEAN := FALSE;
285
286 l_error_count NUMBER;
287 l_data_set_lock BOOLEAN;
288
289 BEGIN
290
291
292 --
293 -- get the data set information
294 --
295 hxc_data_set.get_data_set_info
296 (p_data_set_id => p_data_set_id,
297 p_data_set_name => l_data_set_name,
298 p_description => l_description,
299 p_start_date => l_data_set_start_date,
300 p_stop_date => l_data_set_end_date,
301 p_data_set_mode => l_data_set_mode,
302 p_status => l_current_status,
303 p_validation_status => l_validation_status,
304 p_found_data_set => l_found_data_set);
305
306 --
307 -- if the data set is not found
308 -- we are erroring out the process
309 --
310 IF NOT(l_found_data_set) THEN
311 --error
312 retcode := 2;
313 fnd_file.put_line(fnd_file.LOG,'--- >Data Set not found');
314 RETURN;
315
316 END IF;
317
318
319 IF hxc_archive_restore_utils.incompatibility_pg_running
320 THEN
321 fnd_file.put_line(fnd_file.LOG,'Detected another Archive/Restore or Consolidation Attributes process(es) running.');
322 retcode := 2;
323 RETURN;
324 END IF;
325
326
327 fnd_file.put_line(fnd_file.LOG,'--- >Parameters:');
328 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Id :' || p_data_set_id);
329 fnd_file.put_line(fnd_file.LOG,'--- >Ignore Validation Errors Flag :' || p_ignore_errors);
330 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Name :' || l_data_set_name);
331 --fnd_file.put_line(fnd_file.LOG,'--- >Data Set Status :' || l_current_status);
332 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Validation Status :' || l_validation_status);
333
334 ----------------------------------------------------------------------------
335 -- Question : How do we determine if the archive process is being rerun ?
336 -- Answer : If the data set status is BACKUP_IN_PROGRESS, then it means
337 -- that Archive has been run previously on this data set and has
338 -- failed due to some reason, which is why the status is still
339 -- BACKUP_IN_PROGRESS. Thus we determine that rerun is happening
340 -- for that data set
341 ----------------------------------------------------------------------------
342
343 -- Validate the data set for archiving
344 IF l_current_status in ('OFF_LINE','RESTORE_IN_PROGRESS')
345 THEN
346
347 fnd_file.put_line(fnd_file.LOG,'--- >Data Set is currently in '||l_current_status||' status. Data Set can be'||
348 ' archived only if the data set status is ON_LINE or BACKUP_IN_PROGRESS ');
349 retcode := 2;
350 RETURN;
351
352 ELSIF l_current_status = 'BACKUP_IN_PROGRESS' then
353 l_rerun := 'Y';
354 ELSE
355 l_rerun := 'N';
356 END IF;
357
358
359 l_data_set_age := months_between(trunc(sysdate),trunc(l_data_set_end_date));
360 l_min_data_set_age := nvl(fnd_profile.value('HXC_ARCHIVE_DATA_SET_MIN_AGE'),6);
361
362 fnd_file.put_line(fnd_file.LOG,'--- >Profile Value for minimum age value is: '||l_min_data_set_age);
363
364 IF (l_data_set_age < 0)
365 THEN
366
367 fnd_file.put_line(fnd_file.LOG,'--- >The Data Set '||l_data_set_name||' extends into the future.');
368 fnd_file.put_line(fnd_file.LOG,'--- >Hence the Data Set cannot be archived.');
369 retcode := 2;
370 RETURN;
371
372 ELSIF (l_data_set_age < 6 and l_min_data_set_age is null)
373 THEN
374
375 fnd_file.put_line(fnd_file.LOG,'--- >The age of the Data Set '||l_data_set_name||' is '||round(l_data_set_age,3)||' months, which is less than 6 months');
376 fnd_file.put_line(fnd_file.LOG,'--- >Hence the Data Set cannot be archived.');
377 retcode := 2;
378 RETURN;
379
380 ELSIF (l_min_data_set_age is not null and l_data_set_age < l_min_data_set_age)
381 THEN
382
383 fnd_file.put_line(fnd_file.LOG,'--- >The profile option OTL: Minimum age of Data Set for archiving has been set to the value '||l_min_data_set_age||'.');
384 fnd_file.put_line(fnd_file.LOG,'--- >The age of the Data Set '||l_data_set_name||' is '||round(l_data_set_age,3)||' months, which is less than '|| l_min_data_set_age||' months.');
385 fnd_file.put_line(fnd_file.LOG,'--- >Hence the Data Set cannot be archived.');
386 retcode := 2;
387 RETURN;
388
389 END IF;
390
391 /********* THAT SHOULD BE DONE DURING THE DATA SET VALIDATION *********/
392 /*
393 if hxc_archive_restore_utils.check_data_corruption(p_data_set_id) then
394
395 fnd_file.put_line(fnd_file.LOG,'--- >Data Corruption observed before beginning to Archive the data set');
396 retcode := 2;
397 return;
398
399 end if;
400 */
401 ----------------------------------------------------------------------------
402 -- Question : What should we do in case of rerun ?
403 -- Answer : We should not populate the hxc_data_set_details table because
404 -- some records may have already been transferred during the previous
405 -- runs. Also the first run of Archive has already populated the
406 -- hxc_data_set_details with the correct number of records.
407 ----------------------------------------------------------------------------
408
409 IF l_rerun = 'N' THEN
410
411 IF l_validation_status IS NULL
412 THEN
413 -- error...force user to run validation atleast once
414 fnd_file.put_line(fnd_file.LOG,'--- >Please run the Validate Data Set Process at least once');
415 retcode := 2;
416 RETURN;
417
418 ELSIF (l_validation_status = 'E' AND p_ignore_errors = 'N')
419 THEN
420 --error.....ask user to correct all errors and then run archive
421 fnd_file.put_line(fnd_file.LOG,'--- >The Validate Data Set process has reported warnings.'
422 ||' Either correct those warnings or set the Ignore Validation Warnings flag to Y.');
423 retcode := 2;
424 RETURN;
425
426 END IF;
427
428 --fnd_file.put_line(fnd_file.LOG,'--- >Before populating the hxc_data_set_details table with table counts');
429 --hxc_archive_restore_utils.populate_hxc_data_set_details(p_data_set_id);
430 END IF;
431
432
433
434 -- Bug No - 7358756
435
436 /* As part of re- architecture, removed the following.
437
438 The re-written code takes care of all the below things from
439 within the process. Plus there is no need to validate the
440 data set since it is validated once.
441
442 -- Marking tables with Data set again
443 -- Locking Data Sets
444 -- Validating Data Set
445 -- Core Table count snapshot
446 -- Backup Table count snapshot
447 -- Count Snapshot check.
448
449
450 hxc_data_set.mark_tables_with_data_set
451 (p_data_set_id => p_data_set_id,
452 p_start_date => l_data_set_start_date,
453 p_stop_date => l_data_set_end_date);
454
455
456 l_data_set_lock := FALSE;
457
458 hxc_data_set.lock_data_set
459 (p_data_set_id => p_data_set_id,
460 p_start_date => l_data_set_start_date,
461 p_stop_date => l_data_set_end_date,
462 p_data_set_lock => l_data_set_lock);
463
464 IF not(l_data_set_lock)
465 THEN
466 retcode := 2;
467 RETURN;
468 END IF;
469
470 hxc_data_set.validate_data_set (p_data_set_id => p_data_set_id,
471 p_error_count => l_error_count,
472 p_all_errors => true);
473
474 hxc_archive_restore_utils.core_table_count_snapshot
475 (p_tbb_count => l_tbb_count_1,
476 p_tau_count => l_tau_count_1,
477 p_ta_count => l_ta_count_1,
478 p_td_count => l_td_count_1,
479 p_trans_count => l_trans_count_1,
480 p_tal_count => l_tal_count_1,
481 p_aps_count => l_aps_count_1,
482 p_adl_count => l_adl_count_1,
483 p_ld_count => l_ld_count_1,
484 p_ts_count => l_ts_count_1);
485
486
487 hxc_archive_restore_utils.bkup_table_count_snapshot
488 (p_tbb_ar_count => l_tbb_ar_count_1,
489 p_tau_ar_count => l_tau_ar_count_1,
490 p_ta_ar_count => l_ta_ar_count_1,
491 p_td_ar_count => l_td_ar_count_1,
492 p_trans_ar_count => l_trans_ar_count_1,
493 p_tal_ar_count => l_tal_ar_count_1,
494 p_adl_ar_count => l_adl_ar_count_1,
495 p_aps_ar_count => l_aps_ar_count_1);
496
497 */
498 --
499 -- call the archive process
500 --
501 hxc_archive.archive_process(p_data_set_id => p_data_set_id,
502 p_data_set_start_date => l_data_set_start_date,
503 p_data_set_end_date => l_data_set_end_date);
504
505
506 IF check_data_mismatch
507 THEN
508 fnd_file.put_line(fnd_file.log,' ');
509 fnd_file.put_line(fnd_file.log,' Data mismatch reported in the threads. Correct the data ');
510 fnd_file.put_line(fnd_file.log,' You wont be able to Restore this Data again until you Archive it completely.');
511 fnd_file.put_line(fnd_file.log,' ');
512 retcode := 2;
513 RETURN;
514 END IF;
515
516
517
518 /*
519
520 hxc_data_set.release_lock_data_set(p_data_set_id);
521
522 hxc_archive_restore_utils.core_table_count_snapshot
523 (p_tbb_count => l_tbb_count_2,
524 p_tau_count => l_tau_count_2,
525 p_ta_count => l_ta_count_2,
526 p_td_count => l_td_count_2,
527 p_trans_count => l_trans_count_2,
528 p_tal_count => l_tal_count_2,
529 p_aps_count => l_aps_count_2,
530 p_adl_count => l_adl_count_2,
531 p_ld_count => l_ld_count_2,
532 p_ts_count => l_ts_count_2);
533
534
535 hxc_archive_restore_utils.bkup_table_count_snapshot
536 (p_tbb_ar_count => l_tbb_ar_count_2,
537 p_tau_ar_count => l_tau_ar_count_2,
538 p_ta_ar_count => l_ta_ar_count_2,
539 p_td_ar_count => l_td_ar_count_2,
540 p_trans_ar_count => l_trans_ar_count_2,
541 p_tal_ar_count => l_tal_ar_count_2,
542 p_adl_ar_count => l_adl_ar_count_2,
543 p_aps_ar_count => l_aps_ar_count_2);
544
545 hxc_archive_restore_utils.count_snapshot_check
546 (p_tbb_count_1 => l_tbb_count_1,
547 p_tau_count_1 => l_tau_count_1,
548 p_ta_count_1 => l_ta_count_1,
549 p_td_count_1 => l_td_count_1,
550 p_trans_count_1 => l_trans_count_1,
551 p_tal_count_1 => l_tal_count_1,
552 p_aps_count_1 => l_aps_count_1,
553 p_adl_count_1 => l_adl_count_1,
554 p_ld_count_1 => l_ld_count_1,
555 p_ts_count_1 => l_ts_count_1,
556 p_tbb_ar_count_1 => l_tbb_ar_count_1,
557 p_tau_ar_count_1 => l_tau_ar_count_1,
558 p_ta_ar_count_1 => l_ta_ar_count_1,
559 p_td_ar_count_1 => l_td_ar_count_1,
560 p_trans_ar_count_1 => l_trans_ar_count_1,
561 p_tal_ar_count_1 => l_tal_ar_count_1,
562 p_adl_ar_count_1 => l_adl_ar_count_1,
563 p_aps_ar_count_1 => l_aps_ar_count_1,
564 p_tbb_count_2 => l_tbb_count_2,
565 p_tau_count_2 => l_tau_count_2,
566 p_ta_count_2 => l_ta_count_2,
567 p_td_count_2 => l_td_count_2,
568 p_trans_count_2 => l_trans_count_2,
569 p_tal_count_2 => l_tal_count_2,
570 p_aps_count_2 => l_aps_count_2,
571 p_adl_count_2 => l_adl_count_2,
572 p_ld_count_2 => l_ld_count_2,
573 p_ts_count_2 => l_ts_count_2,
574 p_tbb_ar_count_2 => l_tbb_ar_count_2,
575 p_tau_ar_count_2 => l_tau_ar_count_2,
576 p_ta_ar_count_2 => l_ta_ar_count_2,
577 p_td_ar_count_2 => l_td_ar_count_2,
578 p_trans_ar_count_2 => l_trans_ar_count_2,
579 p_tal_ar_count_2 => l_tal_ar_count_2,
580 p_adl_ar_count_2 => l_adl_ar_count_2,
581 p_aps_ar_count_2 => l_aps_ar_count_2,
582 retcode => retcode);
583
584 IF retcode <> 2 THEN
585 */
586 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
587 fnd_file.put_line(fnd_file.LOG,'---------------------- ARCHIVE COMPLETE ---------------------');
588 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
589 /*
590 ELSE
591 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
592 fnd_file.put_line(fnd_file.LOG,'-------------------- ARCHIVE INCOMPLETE ---------------------');
593 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
594 END IF;
595 */
596 EXCEPTION
597
598 WHEN e_chunk_count THEN
599 fnd_file.put_line(fnd_file.LOG,'=== > WRONG COUNT IN THE CHUNK');
600 hxc_data_set.release_lock_data_set(p_data_set_id);
601 ROLLBACK;
602 retcode := 2;
603
604
605 END archive_data_set;
606
607
608 ----------------------------------------------------------------------------
609 -- Procedure Name : restore_data_set
610 -- Description : This is the starting point of the concurrent program
611 -- 'Restore Data Set'. It calls restore_data_set_from_backup
612 -- if the mode is 'B' (backup mode).
613 ----------------------------------------------------------------------------
614 PROCEDURE restore_data_set(errbuf OUT NOCOPY VARCHAR2,
615 retcode OUT NOCOPY NUMBER,
616 p_data_set_id IN NUMBER)
617 IS
618
619
620
621 -- all the counter
622 -- Core table first count before restore
623 l_tbb_count_1 NUMBER;
624 l_tau_count_1 NUMBER;
625 l_ta_count_1 NUMBER;
626 l_td_count_1 NUMBER;
627 l_trans_count_1 NUMBER;
628 l_tal_count_1 NUMBER;
629 l_aps_count_1 NUMBER;
630 l_adl_count_1 NUMBER;
631 l_ld_count_1 NUMBER;
632 l_ts_count_1 NUMBER;
633
634 -- backup table first count before restore
635 l_tbb_ar_count_1 NUMBER;
636 l_tau_ar_count_1 NUMBER;
637 l_ta_ar_count_1 NUMBER;
638 l_td_ar_count_1 NUMBER;
639 l_trans_ar_count_1 NUMBER;
640 l_tal_ar_count_1 NUMBER;
641 l_adl_ar_count_1 NUMBER;
642 l_aps_ar_count_1 NUMBER;
643
644 -- Core table first count after restore
645 l_tbb_count_2 NUMBER;
646 l_tau_count_2 NUMBER;
647 l_ta_count_2 NUMBER;
648 l_td_count_2 NUMBER;
649 l_trans_count_2 NUMBER;
650 l_tal_count_2 NUMBER;
651 l_aps_count_2 NUMBER;
652 l_adl_count_2 NUMBER;
653 l_ld_count_2 NUMBER;
654 l_ts_count_2 NUMBER;
655
656 -- backup table first count after restore
657 l_tbb_ar_count_2 NUMBER;
658 l_tau_ar_count_2 NUMBER;
659 l_ta_ar_count_2 NUMBER;
660 l_td_ar_count_2 NUMBER;
661 l_trans_ar_count_2 NUMBER;
662 l_tal_ar_count_2 NUMBER;
663 l_adl_ar_count_2 NUMBER;
664 l_aps_ar_count_2 NUMBER;
665
666
667 l_data_set_start_date date;
668 l_data_set_end_date date;
669 l_data_set_name hxc_data_sets.data_set_name%type;
670 l_description hxc_data_sets.description%type;
671 l_data_set_mode hxc_data_sets.data_set_mode%type;
672 l_current_status hxc_data_sets.status%type;
673 l_validation_status hxc_data_sets.validation_status%type;
674 l_found_data_set BOOLEAN := FALSE;
675
676
677 BEGIN
678
679
680 --
681 -- get the data set information
682 --
683 hxc_data_set.get_data_set_info
684 (p_data_set_id => p_data_set_id,
685 p_data_set_name => l_data_set_name,
686 p_description => l_description,
687 p_start_date => l_data_set_start_date,
688 p_stop_date => l_data_set_end_date,
689 p_data_set_mode => l_data_set_mode,
690 p_status => l_current_status,
691 p_validation_status => l_validation_status,
692 p_found_data_set => l_found_data_set);
693
694 fnd_file.put_line(fnd_file.LOG,'--- >Parameters:');
695 fnd_file.put_line(fnd_file.LOG,'--- >Data Set id :' || p_data_set_id);
696 fnd_file.put_line(fnd_file.LOG,'--- >Other Information:');
697 fnd_file.put_line(fnd_file.LOG,'--- >Data Set Name :' || l_data_set_name);
698 --fnd_file.put_line(fnd_file.LOG,'--- >Data Set Status :' || l_current_status);
699
700
701 --
702 -- if the data set is not found
703 -- we are erroring out the process
704 --
705 IF NOT(l_found_data_set) THEN
706 --error
707 retcode := 2;
708 fnd_file.put_line(fnd_file.LOG,'--- >Data Set not found');
709 RETURN;
710
711 END IF;
712
713
714 IF l_current_status IN ('ON_LINE','BACKUP_IN_PROGRESS')
715 THEN
716 fnd_file.put_line(fnd_file.LOG,'--- >Data Set is currently in '||l_current_status||' status. Data Set can be'||
720 END IF;
717 ' restored only if the data set status is OFF_LINE or RESTORE_IN_PROGRESS ');
718 retcode := 2;
719 RETURN;
721
722
723 -- let's check if there are no program that are running
724 -- and are incompatible with this one
725 IF hxc_archive_restore_utils.incompatibility_pg_running
726 THEN
727 fnd_file.put_line(fnd_file.LOG,'Detected another Archive/Restore or Consolidation Attributes process(es) running.');
728 retcode := 2;
729 RETURN;
730 END IF;
731
732
733 -- Bug No - 7358756
734
735 /* As part of re- architecture, removed the following.
736
737 The re-written code takes care of all the below things from
738 within the process. Plus there is no need to validate the
739 data set since it is validated once.
740
741 -- Core Table count snapshot
742 -- Backup Table count snapshot
743 -- Count Snapshot check.
744
745 hxc_archive_restore_utils.core_table_count_snapshot
746 (p_tbb_count => l_tbb_count_1,
747 p_tau_count => l_tau_count_1,
748 p_ta_count => l_ta_count_1,
749 p_td_count => l_td_count_1,
750 p_trans_count => l_trans_count_1,
751 p_tal_count => l_tal_count_1,
752 p_aps_count => l_aps_count_1,
753 p_adl_count => l_adl_count_1,
754 p_ld_count => l_ld_count_1,
755 p_ts_count => l_ts_count_1);
756
757
758 hxc_archive_restore_utils.bkup_table_count_snapshot
759 (p_tbb_ar_count => l_tbb_ar_count_1,
760 p_tau_ar_count => l_tau_ar_count_1,
761 p_ta_ar_count => l_ta_ar_count_1,
762 p_td_ar_count => l_td_ar_count_1,
763 p_trans_ar_count => l_trans_ar_count_1,
764 p_tal_ar_count => l_tal_ar_count_1,
765 p_adl_ar_count => l_adl_ar_count_1,
766 p_aps_ar_count => l_aps_ar_count_1);
767
768 */
769
770
771 -- Before calling the restore process check if any corrupted data sets already exist.
772 -- If yes, error out issuing the message. It needs to be corrected first.
773
774 IF check_null_data_set_id
775 THEN
776 fnd_file.put_line(fnd_file.log,' ');
777 fnd_file.put_line(fnd_file.log,'There are some data sets Archived before the new Multi-threaded Archive Process ');
778 fnd_file.put_line(fnd_file.log,'was implemented. Please follow the Metalink note available to update these data ');
779 fnd_file.put_line(fnd_file.log,'sets as per the new Multi-threaded architecture. ');
780 fnd_file.put_line(fnd_file.log,' ');
781 retcode := 2;
782 RETURN;
783 END IF;
784
785
786 --
787 -- calling the restore process
788 --
789 hxc_restore.restore_process(p_data_set_id => p_data_set_id,
790 p_data_set_start_date => l_data_set_start_date,
791 p_data_set_end_date => l_data_set_end_date);
792
793
794 IF check_data_mismatch
795 THEN
796 fnd_file.put_line(fnd_file.log,' ');
797 fnd_file.put_line(fnd_file.log,' Data mismatch reported in the threads. Correct the data ');
798 fnd_file.put_line(fnd_file.log,' You wont be able to Archive this Data again until you Restore it completely.');
799 fnd_file.put_line(fnd_file.log,' ');
800 retcode := 2;
801 RETURN;
802 END IF;
803
804
805 /*
806 hxc_archive_restore_utils.core_table_count_snapshot
807 (p_tbb_count => l_tbb_count_2,
808 p_tau_count => l_tau_count_2,
809 p_ta_count => l_ta_count_2,
810 p_td_count => l_td_count_2,
811 p_trans_count => l_trans_count_2,
812 p_tal_count => l_tal_count_2,
813 p_aps_count => l_aps_count_2,
814 p_adl_count => l_adl_count_2,
815 p_ld_count => l_ld_count_2,
816 p_ts_count => l_ts_count_2);
817
818
819 hxc_archive_restore_utils.bkup_table_count_snapshot
820 (p_tbb_ar_count => l_tbb_ar_count_2,
821 p_tau_ar_count => l_tau_ar_count_2,
822 p_ta_ar_count => l_ta_ar_count_2,
823 p_td_ar_count => l_td_ar_count_2,
824 p_trans_ar_count => l_trans_ar_count_2,
825 p_tal_ar_count => l_tal_ar_count_2,
826 p_adl_ar_count => l_adl_ar_count_2,
827 p_aps_ar_count => l_aps_ar_count_2);
828
829 hxc_archive_restore_utils.count_snapshot_check
830 (p_tbb_count_1 => l_tbb_count_1,
831 p_tau_count_1 => l_tau_count_1,
832 p_ta_count_1 => l_ta_count_1,
833 p_td_count_1 => l_td_count_1,
834 p_trans_count_1 => l_trans_count_1,
835 p_tal_count_1 => l_tal_count_1,
836 p_aps_count_1 => l_aps_count_1,
837 p_adl_count_1 => l_adl_count_1,
838 p_ld_count_1 => l_ld_count_1,
839 p_ts_count_1 => l_ts_count_1,
840 p_tbb_ar_count_1 => l_tbb_ar_count_1,
841 p_tau_ar_count_1 => l_tau_ar_count_1,
842 p_ta_ar_count_1 => l_ta_ar_count_1,
843 p_td_ar_count_1 => l_td_ar_count_1,
844 p_trans_ar_count_1 => l_trans_ar_count_1,
845 p_tal_ar_count_1 => l_tal_ar_count_1,
846 p_adl_ar_count_1 => l_adl_ar_count_1,
847 p_aps_ar_count_1 => l_aps_ar_count_1,
848 p_tbb_count_2 => l_tbb_count_2,
849 p_tau_count_2 => l_tau_count_2,
850 p_ta_count_2 => l_ta_count_2,
851 p_td_count_2 => l_td_count_2,
852 p_trans_count_2 => l_trans_count_2,
853 p_tal_count_2 => l_tal_count_2,
854 p_aps_count_2 => l_aps_count_2,
855 p_adl_count_2 => l_adl_count_2,
856 p_ld_count_2 => l_ld_count_2,
857 p_ts_count_2 => l_ts_count_2,
858 p_tbb_ar_count_2 => l_tbb_ar_count_2,
859 p_tau_ar_count_2 => l_tau_ar_count_2,
860 p_ta_ar_count_2 => l_ta_ar_count_2,
861 p_td_ar_count_2 => l_td_ar_count_2,
862 p_trans_ar_count_2 => l_trans_ar_count_2,
863 p_tal_ar_count_2 => l_tal_ar_count_2,
864 p_adl_ar_count_2 => l_adl_ar_count_2,
865 p_aps_ar_count_2 => l_aps_ar_count_2,
866 retcode => retcode);
867
868 IF retcode <> 2 THEN
869 */
870 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
871 fnd_file.put_line(fnd_file.LOG,'---------------------- RESTORE COMPLETE ---------------------');
872 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
873 /*
874 ELSE
875 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
876 fnd_file.put_line(fnd_file.LOG,'-------------------- RESTORE INCOMPLETE ---------------------');
877 fnd_file.put_line(fnd_file.LOG,'--------------------------------------------------------------');
878 END IF;
879
880
881 IF NOT (hxc_archive_restore_utils.check_if_copy_successful
882 (p_data_set_id,'RESTORE'))
883 THEN
884 --error
885 fnd_file.put_line(fnd_file.LOG,'--- >Copy failed');
886 retcode := 2;
887 ROLLBACK;
888 RETURN;
889 END IF;
890 */
891
892
893 EXCEPTION
894
895 WHEN e_chunk_count THEN
896 fnd_file.put_line(fnd_file.LOG,'=== > WRONG COUNT IN THE CHUNK');
897 ROLLBACK;
898 retcode := 2;
899
900
901 END restore_data_set;
902
903
904 -- Public Function check_null_data_set_id
905 -- Checks if any of the said tables have NULL data set id
906 -- columns. If yes, sends a TRUE, and if no returns a
907 -- FALSE.
908
909 FUNCTION check_null_data_set_id
910 RETURN BOOLEAN
911 IS
912
913 l_count NUMBER := 0;
914
915 BEGIN
916
917 -- In the below queries, added a ROWNUM condition
918 -- so that the counts wont go on for ever. We only need
919 -- to find if there is atleast one records with NULL data
920 -- set id; the exact count is unncecessary.
921
922 SELECT count(1)
923 INTO l_count
924 FROM hxc_time_building_blocks_ar
925 WHERE data_set_id IS NULL
926 AND ROWNUM < 2;
927
928 IF l_count >= 1
929 THEN
930 RETURN TRUE;
931 END IF;
932
933 SELECT count(1)
934 INTO l_count
935 FROM hxc_time_attributes_ar
936 WHERE data_set_id IS NULL
937 AND ROWNUM < 2;
938
939 IF l_count >= 1
940 THEN
941 RETURN TRUE;
942 END IF;
943
944 SELECT count(1)
945 INTO l_count
946 FROM hxc_transaction_details_ar
947 WHERE data_set_id IS NULL
948 AND ROWNUM < 2;
949
950 IF l_count >= 1
951 THEN
952 RETURN TRUE;
953 END IF;
954
955 SELECT count(1)
956 INTO l_count
957 FROM hxc_transactions_ar
958 WHERE data_set_id IS NULL
959 AND ROWNUM < 2;
960
961 IF l_count >= 1
962 THEN
963 RETURN TRUE;
964 END IF;
965
966
967 -- None of the above queries returned
968 -- any thing, so send back FALSE; there is
969 -- no data corruption.
970
971 RETURN FALSE;
972
973 END check_null_data_set_id;
974
975
976
977
978 -- Public function check_data_mismatch
979 -- To check if any data mismatch has been recorded earlier in the threads.
980 -- If yes, the process has to error out, hence return a TRUE.
981 -- Else return a FALSE.
982
983 FUNCTION check_data_mismatch
984 RETURN BOOLEAN
985 IS
986
987 l_count NUMBER := 0;
988
989 BEGIN
990
991 -- Again we want only a Yes or No answer for whether there are records;
992 -- no need of the exact count, hence the rownum condition.
993
994 SELECT count(1)
995 INTO l_count
996 FROM hxc_data_set_details
997 WHERE ROWNUM < 2 ;
998
999 IF l_count >= 1
1000 THEN
1001 RETURN TRUE;
1002 ELSE
1003 RETURN FALSE;
1004 END IF;
1005
1006 END check_data_mismatch;
1007
1008 ----------------------------------------------------------------------------------------
1009
1010
1011 End hxc_archive_restore_process;
[Home] [Help]