1 PACKAGE BODY hr_dm_utility AS
2 /* $Header: perdmutl.pkb 120.0 2005/05/31 17:15:29 appldev noship $ */
3
4 /*--------------------------- GLOBAL VARIABLES ----------------------------*/
5
6
7 -- message globals
8 -- start
9
10 g_debug_message_pipe VARCHAR2(50);
11 g_debug_message_log VARCHAR2(50);
12 g_debug_message_indent NUMBER;
13 g_debug_message_indent_size NUMBER := 2;
14
15 -- message globals
16 -- end
17
18
19 /*------------------------------- ROUTINES --------------------------------*/
20
21 -- general purpose procedures
22 -- start
23
24 -- ------------------------- number_of_threads ------------------------
25 -- Description: Finds the number of threads for the concurrent manager
26 -- to use by looking at ben_batch_parameters which is striped by business
27 -- group id.
28 --
29 --
30 -- Input Parameters
31 -- p_business_group_id - for the current business group
32 --
33 --
34 -- Output Parameters
35 -- <none>
36 --
37 --
38 -- Return Value
39 -- number of threads
40 --
41 --
42 -- ------------------------------------------------------------------------
43
44 --
45 FUNCTION number_of_threads(p_business_group_id IN NUMBER) RETURN NUMBER IS
46 --
47
48 l_threads NUMBER;
49
50
51 CURSOR csr_threads IS
52 SELECT thread_cnt_num
53 FROM ben_batch_parameter
54 WHERE (business_group_id = p_business_group_id)
55 AND (batch_exe_cd = 'HRDM');
56
57
58 --
59 BEGIN
60 --
61
62 message('ROUT','entry:hr_dm_utility.number_of_threads', 5);
63 message('PARA','(p_business_group_id - ' || p_business_group_id || ')', 10);
64
65
66 OPEN csr_threads;
67 LOOP
68 FETCH csr_threads INTO l_threads;
69 EXIT when csr_threads%NOTFOUND;
70 END LOOP;
71 CLOSE csr_threads;
72
73 -- set default value if no entry exists for the current business group
74 IF (l_threads IS NULL) THEN
75 l_threads := 3;
76 message('INFO','No value for the number of threads found in ' ||
77 'ben_batch_parameters - using default value', 12);
78 END IF;
79
80 -- make sure that we have at least one thread
81 IF (l_threads <1) THEN
82 l_threads := 1;
83 message('INFO','The number of threads has been set to one as this is' ||
84 ' The minimum value permitted.', 13);
85 END IF;
86
87
88 message('INFO','Found number of threads', 15);
89 message('SUMM','Found number of threads', 20);
90 message('ROUT','exit:hr_dm_utility.number_of_threads', 25);
91 message('PARA','(l_threads - ' || l_threads || ')', 30);
92
93 RETURN(l_threads);
94
95 -- error handling
96 EXCEPTION
97 WHEN OTHERS THEN
98 error(SQLCODE,'hr_dm_utility.number_of_threads','(none)','R');
99 RAISE;
100
101 --
102 END number_of_threads;
103 --
104
105
106
107 -- ------------------------- chunk_size -----------------------------------
108 -- Description: Finds the chunk size to use for the DP, UP and D phases
109 -- to use by looking at ben_batch_parameters which is striped by business
110 -- group id.
111 --
112 --
113 -- Input Parameters
114 -- p_business_group_id - for the current business group
115 --
116 --
117 -- Output Parameters
118 -- <none>
119 --
120 --
121 -- Return Value
122 -- chunk_size
123 --
124 --
125 -- ------------------------------------------------------------------------
126
127
128 --
129 FUNCTION chunk_size(p_business_group_id IN NUMBER) RETURN NUMBER IS
130 --
131
132 l_chunk_size NUMBER;
133
134
135 CURSOR csr_chunk_size IS
136 SELECT chunk_size
137 FROM ben_batch_parameter
138 WHERE (business_group_id = p_business_group_id)
139 AND (batch_exe_cd = 'HRDM');
140
141 --
142 BEGIN
143 --
144
145 message('ROUT','entry:hr_dm_utility.chunk_size', 5);
146 message('PARA','(p_business_group_id - ' || p_business_group_id || ')', 10);
147
148
149 OPEN csr_chunk_size;
150 LOOP
151 FETCH csr_chunk_size INTO l_chunk_size;
152 EXIT when csr_chunk_size%NOTFOUND;
153 END LOOP;
154 CLOSE csr_chunk_size;
155
156 -- set default value if no entry exists for the current business group
157 IF (l_chunk_size IS NULL) THEN
158 l_chunk_size := 10;
159 message('INFO','No value for the number of chunk size found in ' ||
160 'ben_batch_parameters - using default value', 15);
161 END IF;
162
163 message('INFO','Found chunk size', 15);
164 message('SUMM','Found chunk size', 20);
165 message('ROUT','exit:hr_dm_utility.chunk_size', 25);
166 message('PARA','(l_chunk_size - ' || l_chunk_size || ')', 30);
167
168 RETURN(l_chunk_size);
169
170 -- error handling
171 EXCEPTION
172 WHEN OTHERS THEN
173 error(SQLCODE,'hr_dm_utility.chunk_size','(none)','R');
174 RAISE;
175
176 --
177 END chunk_size;
178 --
179
180
181 -- ------------------------- get_phase_status ------------------------
182 -- Description: Reads the status of the passed phase from the hr_dm_phases
183 -- table.
184 --
185 --
186 -- Input Parameters
187 -- p_phase - code for phase to be reported on
188 --
189 -- p_migration_id - migration id of current migration
190 --
191 --
192 -- Output Parameters
193 -- <none>
194 --
195 --
196 -- Return Value
197 -- status of phase
198 --
199 --
200 -- ------------------------------------------------------------------------
201
202
203 --
204 FUNCTION get_phase_status(p_phase IN VARCHAR2, p_migration_id IN NUMBER)
205 RETURN VARCHAR2 IS
206 --
207
208 l_phase_status VARCHAR2(30);
209
210 CURSOR csr_status IS
211 SELECT status
212 FROM hr_dm_phases
213 WHERE ((migration_id = p_migration_id)
214 AND (phase_name = p_phase));
215
216 --
217 BEGIN
218 --
219
220 message('ROUT','entry:hr_dm_utility.get_phase_status', 5);
221 message('PARA','(p_phase - ' || p_phase ||
222 ')(p_migration_id - ' || p_migration_id || ')', 10);
223
224 OPEN csr_status;
225 LOOP
226 FETCH csr_status INTO l_phase_status;
227 EXIT when csr_status%NOTFOUND;
228 END LOOP;
229 CLOSE csr_status;
230
231 -- use a ? to represent a null value being returned
232 l_phase_status := NVL(l_phase_status, '?');
233
234
235 message('INFO','Find Phase Status', 15);
236 message('SUMM','Find Phase Status', 20);
237 message('ROUT','exit:hr_dm_utility.get_phase_status', 25);
238 message('PARA','(l_phase_status - ' || l_phase_status || ')', 30);
239
240 RETURN(l_phase_status);
241
242 -- error handling
243 EXCEPTION
244 WHEN OTHERS THEN
245 error(SQLCODE,'hr_dm_utility.get_phase_status','(none)','R');
246 RAISE;
247
248 --
249 END get_phase_status;
250 --
251
252 -- ------------------------- get_phase_id ------------------------
253 -- Description: Reads the phase id of the passed phase from the
254 -- hr_dm_phases table.
255 --
256 --
257 -- Input Parameters
258 -- p_phase - code for phase to be reported on
259 --
260 -- p_migration_id - migration id of current migration
261 --
262 --
263 -- Output Parameters
264 -- <none>
265 --
266 --
267 -- Return Value
268 -- phase id
269 --
270 --
271 -- ------------------------------------------------------------------------
272
273
274 --
275 FUNCTION get_phase_id(p_phase IN VARCHAR2, p_migration_id IN NUMBER)
276 RETURN NUMBER IS
277 --
278
279 l_phase_id NUMBER;
280
281 CURSOR csr_phase IS
282 SELECT phase_id
283 FROM hr_dm_phases
284 WHERE ((migration_id = p_migration_id)
285 AND (phase_name = p_phase));
286
287 --
288 BEGIN
289 --
290
291 message('ROUT','entry:hr_dm_utility.get_phase_id', 5);
292 message('PARA','(p_phase - ' || p_phase ||
293 ')(p_migration_id - ' || p_migration_id || ')', 10);
294
295
296 OPEN csr_phase;
297 LOOP
298 FETCH csr_phase INTO l_phase_id;
299 EXIT when csr_phase%NOTFOUND;
300 END LOOP;
301 CLOSE csr_phase;
302
303
304 message('INFO','Find Phase ID', 15);
305 message('SUMM','Find Phase ID', 20);
306 message('ROUT','exit:hr_dm_utility.get_phase_id', 25);
307 message('PARA','(l_phase_id - ' || l_phase_id || ')', 30);
308
309 RETURN(l_phase_id);
310
311 -- error handling
312 EXCEPTION
313 WHEN OTHERS THEN
314 error(SQLCODE,'hr_dm_utility.get_phase_id','(none)','R');
315 RAISE;
316
317 --
318 END get_phase_id;
319 --
320
321 -- ------------------------- set_process ------------------------
322 -- Description: Updates hr_dm_migrations with the current process
323 -- being undertaken by the DM
324 --
325 --
326 -- Input Parameters
327 -- p_process - text describing the process
328 --
329 -- p_migration_id - migration id of current migration
330 --
331 --
332 -- Output Parameters
333 -- <none>
334 --
335 --
336 -- ------------------------------------------------------------------------
337
338
339 --
340 PROCEDURE set_process(p_process_text IN VARCHAR2,
341 p_phase IN VARCHAR2,
342 p_migration_id IN NUMBER) IS
343 --
344
345 l_text VARCHAR2(60);
346
347 --
348 BEGIN
349 --
350
351 message('ROUT','entry:hr_dm_utility.set_process', 5);
352 message('PARA','(p_process_text - ' || p_process_text ||
353 ')(p_phase - ' || p_phase ||
354 ')(p_migration_id - ' || p_migration_id || ')', 10);
355
356 -- build up message
357 l_text := hr_general.decode_lookup('HR_DM_MIGRATION_PHASE', p_phase) ||
358 ' - ' || p_process_text;
359
360 -- update table
361 UPDATE hr_dm_migrations
362 SET migration_process = l_text
363 WHERE migration_id = p_migration_id;
364 COMMIT;
365
366 message('INFO','Set process in hr_dm_migrations', 15);
367 message('SUMM','Set process in hr_dm_migrations', 20);
368 message('ROUT','exit:hr_dm_utility.set_process', 25);
369 message('PARA','(none)', 30);
370
371
372 -- error handling
373 EXCEPTION
374 WHEN OTHERS THEN
375 error(SQLCODE,'hr_dm_utility.set_process','(none)','R');
376 RAISE;
377
378 --
379 END set_process;
380 --
381
382
383
384 -- general purpose procedures
385 -- end
386
387 ----------------------------------------------------------------------------
388
389
390
391 -- error procedures
392 -- start
393
394
395 -- ------------------------- error ------------------------
396 -- Description: When an error has occurred elsewhere, this procedure is
397 -- called to log the FAIL message which includes the failure code and
398 -- the failure message plus additional information supplied by the
399 -- function/procedure that errored. It can also do a commit or rollback
400 -- when called.
401 --
402 --
403 -- Input Parameters
404 -- p_sqlcode - the sql error code
405 --
406 -- p_procedure - the procedure / function name where the error
407 -- occurred, including the package name
408 --
409 -- p_extra - any additional text to be appended to the
410 -- FAIL message
414 --
411 --
412 -- p_rollback - if a rollback (R) (default) or commit (C) is required
413 --
415 -- Output Parameters
416 -- <none>
417 --
418 --
419 -- ------------------------------------------------------------------------
420
421
422 --
423 PROCEDURE error (p_sqlcode IN NUMBER, p_procedure IN VARCHAR2,
424 p_extra IN VARCHAR2, p_rollback IN VARCHAR2 DEFAULT 'R') IS
425 --
426
427 --
428 BEGIN
429 --
430
431 message('ROUT','entry:hr_dm_utility.error', 5);
432 message('PARA','(p_sqlcode - ' || p_sqlcode ||
433 ')(p_procedure - ' || p_procedure || ')', 10);
434
435 message('FAIL',p_sqlcode || ':' || SQLERRM(p_sqlcode) || ':'
436 || p_extra, 15);
437
438 IF (p_rollback = 'R') THEN
439 ROLLBACK;
440 END IF;
441 IF (p_rollback = 'C') THEN
442 COMMIT;
443 END IF;
444
445 message('INFO','Error Handler - ' || p_procedure, 20);
446 message('SUMM','Error Handler - ' || p_procedure, 25);
447 message('ROUT','exit:hr_dm_utility.error', 30);
448 message('PARA','(none)', 35);
449
450 -- error handling
451 EXCEPTION
452 WHEN OTHERS THEN
453 error(SQLCODE,'hr_dm_utility.error','(none)','R');
454 RAISE;
455
456
457 --
458 END error;
459 --
460
461
462 -- error procedures
463 -- end
464
465 ----------------------------------------------------------------------------
466
467
468
469 -- message procedures
470 -- start
471
472 -- ------------------------- message_init ------------------------
473 -- Description: Message logging for concurrent managers is initialized
474 -- by calling this procedure. It obtains the logging options from the
475 -- table pay_action_parameters. By default, SUMM and FAIL messages are
476 -- always enabled for sending to the log file.
477 --
478 --
479 -- Input Parameters
480 -- <none>
481 --
482 --
483 -- Output Parameters
484 -- <none>
485 --
486 --
487 -- ------------------------------------------------------------------------
488
489 --
490 PROCEDURE message_init IS
491 --
492 CURSOR csr_c1 IS
493 SELECT parameter_value
494 FROM pay_action_parameters
495 WHERE parameter_name = 'HR_DM_DEBUG_PIPE';
496 CURSOR csr_c2 IS
497 SELECT parameter_value
498 FROM pay_action_parameters
499 WHERE parameter_name = 'HR_DM_DEBUG_LOG';
500
501 --
502 BEGIN
503 --
504
505 -- read values from pay_action_parameters
506
507 OPEN csr_c1;
508 LOOP
509 FETCH csr_c1 INTO g_debug_message_pipe;
510 EXIT WHEN csr_c1%NOTFOUND;
511 END LOOP;
512 CLOSE csr_c1;
513
514 OPEN csr_c2;
515 LOOP
516 FETCH csr_c2 INTO g_debug_message_log;
517 EXIT WHEN csr_c2%NOTFOUND;
518 END LOOP;
519 CLOSE csr_c2;
520
521 -- ensure that summary and fail settings are set
522
523 IF ((INSTRB(g_debug_message_log, 'SUMM') IS NULL) OR
524 (INSTRB(g_debug_message_log, 'SUMM') = 0)) THEN
525 g_debug_message_log := g_debug_message_log || ':SUMM';
526 END IF;
527
528 IF ((INSTRB(g_debug_message_log, 'FAIL') IS NULL) OR
529 (INSTRB(g_debug_message_log, 'FAIL') = 0)) THEN
530 g_debug_message_log := g_debug_message_log || ':FAIL';
531 END IF;
532
533 -- start the indenting to zero indentation
534 g_debug_message_indent := 0;
535
536
537 -- error handling
538 EXCEPTION
539 WHEN OTHERS THEN
540 error(SQLCODE,'hr_dm_utility.message_init','(none)','R');
541 RAISE;
542
543 --
544 END message_init;
545 --
546
547 -- ------------------------- message ------------------------
548 -- Description: Logs the message to the log file and / or the
549 -- pipe for the options that have been configured by calling message_init.
550 --
551 --
552 -- Input Parameters
553 -- p_type - message type
554 --
555 -- p_message - text of message
556 --
557 -- p_position - position value for piped messages
558 --
559 --
560 -- Output Parameters
561 -- <none>
562 --
563 --
564 -- ------------------------------------------------------------------------
565
566 --
567 PROCEDURE message (p_type IN VARCHAR2, p_message IN VARCHAR2,
568 p_position IN NUMBER) IS
569 --
570
571 l_header VARCHAR2(30);
572 l_debug VARCHAR2(100);
573 l_message VARCHAR2(32767);
574
575 --
576 BEGIN
577 --
578
579 -- are we interested in this type of message?
580 l_debug := g_debug_message_pipe || ':' || g_debug_message_log;
581
582 IF (INSTRB(l_debug, p_type) <> 0) THEN
583 l_message := p_message;
584 -- indent non-routing messages
585 -- IF (p_type <> 'ROUT') THEN
586 -- l_message := ' ' || l_message;
587 -- END IF;
588
589 -- for ROUT entry messages change indent
590 -- decrease for exit messages
591 IF (p_type = 'ROUT') AND (substr(p_message,1,5) = 'exit:') THEN
592 g_debug_message_indent := g_debug_message_indent -
593 g_debug_message_indent_size;
594 END IF;
595
596
597 -- indent all messages to show nesting of functions
598 l_message := rpad(' ', g_debug_message_indent) || l_message;
599
600 -- for ROUT entry messages change indent
601 -- increase for entry messages
602 IF (p_type = 'ROUT') AND (substr(p_message,1,6) = 'entry:') THEN
603 g_debug_message_indent := g_debug_message_indent +
607
604 g_debug_message_indent_size;
605 END IF;
606
608
609 -- build header
610 l_header := p_type || ':' || TO_CHAR(sysdate,'HH24MISS');
611
612 -- send to pipe?
613 IF (INSTRB(g_debug_message_pipe, p_type) <> 0) THEN
614 hr_utility.set_location(l_header || ':-:' || l_message, p_position);
615 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_header || ':-:' || l_message);
616 END IF;
617
618 -- send to log file?
619 IF (INSTRB(g_debug_message_log, p_type) <> 0) THEN
620 FND_FILE.PUT_LINE(FND_FILE.LOG, l_header || ':-:' || l_message);
621 END IF;
622 END IF;
623
624
625 -- error handling
626 EXCEPTION
627 WHEN OTHERS THEN
628 error(SQLCODE,'hr_dm_utility.message','(none)','R');
629 RAISE;
630
631 --
632 END message;
633 --
634
635
636 -- message procedures
637 -- end
638
639 ----------------------------------------------------------------------------
640
641
642
643 -- rollback procedures
644 -- start
645
646 -- ------------------------- rollback ------------------------
647 -- Description: The appropriate code (phase / phase item specific) for
648 -- the phase or phase item to be rolled back is called.
649 --
650 --
651 -- Input Parameters
652 -- p_phase - code for the phase to be rolled back
653 --
654 -- p_masterslave - MASTER indicates the rollback is for the phase
655 -- SLAVE is for a single phase item
656 --
657 -- p_migration_id - current migration id
658 --
659 -- p_phase_item_id - phase item to be rolled back
660 --
661 --
662 -- Output Parameters
663 -- <none>
664 --
665 --
666 -- ------------------------------------------------------------------------
667
668 --
669 PROCEDURE rollback (p_phase IN VARCHAR2,
670 p_masterslave IN VARCHAR2 DEFAULT NULL,
671 p_migration_id IN NUMBER DEFAULT NULL,
672 p_phase_item_id IN NUMBER DEFAULT NULL) IS
673 --
674
675 --
676 BEGIN
677 --
678
679 message('ROUT','entry:hr_dm_utility.rollback', 5);
680 message('PARA','(p_phase - ' || p_phase ||
681 ')(p_masterslave - ' || p_masterslave || ')', 10);
682
683 -- what type of rollback?
684
685 -- Init
686 IF ((p_phase = 'I') AND (p_migration_id IS NOT NULL)) THEN
687 rollback_init(p_migration_id);
688 END IF;
689
690 -- Generator
691 IF ((p_phase = 'G') AND (p_migration_id IS NOT NULL)) THEN
692 rollback_generator(p_migration_id);
693 END IF;
694
695 -- Range
696 IF (p_phase = 'R') THEN
697 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
698 rollback_range_master(p_migration_id);
699 END IF;
700 END IF;
701
702 -- Download AOL
703 IF (p_phase = 'DA') THEN
704 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
705 rollback_down_aol_master(p_migration_id);
706 END IF;
707 END IF;
708
709 -- Download
710 IF (p_phase = 'DP') THEN
711 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
712 rollback_download_master(p_migration_id);
713 END IF;
714 END IF;
715
716 -- Copy
717 -- non-required
718
719 -- Upload
720 IF (p_phase = 'UP') THEN
721 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
722 rollback_upload(p_migration_id);
723 END IF;
724 END IF;
725
726 -- Upload AOL
727 IF (p_phase = 'UA') THEN
728 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
729 rollback_up_aol_master(p_migration_id);
730 END IF;
731 END IF;
732
733 -- Cleanup
734 IF ((p_phase = 'C') AND (p_migration_id IS NOT NULL)) THEN
735 rollback_cleanup(p_migration_id);
736 END IF;
737
738 -- Delete
739 IF ((p_phase = 'D') AND (p_migration_id IS NOT NULL)) THEN
740 rollback_delete(p_migration_id);
741 END IF;
742
743 message('INFO','Rollback', 15);
744 message('SUMM','Rollback', 20);
745 message('ROUT','exit:hr_dm_utility.rollback', 25);
746 message('PARA','(none)', 30);
747
748 -- error handling
749 EXCEPTION
750 WHEN OTHERS THEN
751 error(SQLCODE,'hr_dm_utility.rollback','(none)','R');
752 RAISE;
753 --
754 END rollback;
755 --
756
757 /*-------------------------- PRIVATE ROUTINES ----------------------------*/
758
759
760 -- ------------------------- rollback_range_master ------------------------
761 -- Description: All entries in the hr_dm_migration_ranges table for the range
762 -- phase are deleted and the phase item status in hr_dm_phase_items is reset
763 -- to NS.
764 --
765 --
766 -- Input Parameters
767 -- p_migration_id - current migration id
768 --
769 -- Output Parameters
770 -- <none>
771 --
772 --
773 -- ------------------------------------------------------------------------
774
775
776 --
777 PROCEDURE rollback_range_master (p_migration_id IN NUMBER) IS
778 --
779
780 CURSOR csr_c1 IS
781 SELECT phi.phase_item_id
782 FROM hr_dm_phase_items phi,
783 hr_dm_phases ph
784 WHERE (ph.migration_id = p_migration_id)
785 AND (ph.phase_name = 'R')
786 AND (phi.phase_id = ph.phase_id)
787 AND (phi.status IN ('S', 'E'));
788
789 l_phase_item_id NUMBER;
790
791 --
792 BEGIN
793 --
794
798 -- find all range phases for this migration that are started or in error
795 message('ROUT','entry:hr_dm_utility.rollback_range_master', 5);
796 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
797
799 OPEN csr_c1;
800 LOOP
801 FETCH csr_c1 INTO l_phase_item_id;
802 EXIT WHEN csr_c1%NOTFOUND;
803 -- delete information from hr_dm_migration_ranges
804 DELETE FROM hr_dm_migration_ranges
805 WHERE phase_item_id = l_phase_item_id;
806 -- update status to not started
807 update_phase_items('NS', l_phase_item_id);
808 END LOOP;
809 CLOSE csr_c1;
810
811 message('INFO','Rollback - range_master', 15);
812 message('SUMM','Rollback - range_master', 20);
813 message('ROUT','exit:hr_dm_utility.rollback_range_master', 25);
814 message('PARA','(none)', 30);
815
816 -- error handling
817 EXCEPTION
818 WHEN OTHERS THEN
819 error(SQLCODE,'hr_dm_utility.rollback_range_master','(none)','R');
820 RAISE;
821 --
822 END rollback_range_master;
823 --
824
825
826 -- ---------------------- rollback_down_aol_master ------------------------
827 -- Description: All entries in the hr_dm_phase_items table for the download
828 -- aol phase which have a status of S or E are reset to NS.
829 --
830 --
831 -- Input Parameters
832 -- p_migration_id - current migration id
833 --
834 -- Output Parameters
835 -- <none>
836 --
837 --
838 -- ------------------------------------------------------------------------
839
840
841 --
842 PROCEDURE rollback_down_aol_master (p_migration_id IN NUMBER) IS
843 --
844
845 CURSOR csr_c1 IS
846 SELECT phi.phase_item_id
847 FROM hr_dm_phase_items phi,
848 hr_dm_phases ph
849 WHERE (ph.migration_id = p_migration_id)
850 AND (ph.phase_name = 'DA')
851 AND (phi.phase_id = ph.phase_id)
852 AND (phi.status IN ('S', 'E'));
853
854 l_phase_item_id NUMBER;
855
856 --
857 BEGIN
858 --
859
860 message('ROUT','entry:hr_dm_utility.rollback_down_aol_master', 5);
861 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
862
863 -- find all DA phases for this migration that are started or in error
864 OPEN csr_c1;
865 LOOP
866 FETCH csr_c1 INTO l_phase_item_id;
867 EXIT WHEN csr_c1%NOTFOUND;
868 -- update status to not started
869 update_phase_items('NS', l_phase_item_id);
870 END LOOP;
871 CLOSE csr_c1;
872
873 message('INFO','Rollback - download_aol_master', 15);
874 message('SUMM','Rollback - download_aol_master', 20);
875 message('ROUT','exit:hr_dm_utility.rollback_down_aol_master', 25);
876 message('PARA','(none)', 30);
877
878 -- error handling
879 EXCEPTION
880 WHEN OTHERS THEN
881 error(SQLCODE,'hr_dm_utility.rollback_down_aol_master','(none)','R');
882 RAISE;
883 --
884 END rollback_down_aol_master;
885 --
886
887 -- ---------------------- rollback_up_aol_master ------------------------
888 -- Description: All entries in the hr_dm_phase_items table for the upload
889 -- aol phase are reset to NS.
890 --
891 --
892 -- Input Parameters
893 -- p_migration_id - current migration id
894 --
895 -- Output Parameters
896 -- <none>
897 --
898 --
899 -- ------------------------------------------------------------------------
900
901
902 --
903 PROCEDURE rollback_up_aol_master (p_migration_id IN NUMBER) IS
904 --
905
906 CURSOR csr_c1 IS
907 SELECT phi.phase_item_id
908 FROM hr_dm_phase_items phi,
909 hr_dm_phases ph
910 WHERE (ph.migration_id = p_migration_id)
911 AND (ph.phase_name = 'UA')
912 AND (phi.phase_id = ph.phase_id);
913
914 l_phase_item_id NUMBER;
915
916 --
917 BEGIN
918 --
919
920 message('ROUT','entry:hr_dm_utility.rollback_up_aol_master', 5);
921 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
922
923 -- find all UA phases for this migration
924 OPEN csr_c1;
925 LOOP
926 FETCH csr_c1 INTO l_phase_item_id;
927 EXIT WHEN csr_c1%NOTFOUND;
928 -- update status to not started
929 update_phase_items('NS', l_phase_item_id);
930 END LOOP;
931 CLOSE csr_c1;
932
933 message('INFO','Rollback - upload_aol_master', 15);
934 message('SUMM','Rollback - upload_aol_master', 20);
935 message('ROUT','exit:hr_dm_utility.rollback_up_aol_master', 25);
936 message('PARA','(none)', 30);
937
938 -- error handling
939 EXCEPTION
940 WHEN OTHERS THEN
941 error(SQLCODE,'hr_dm_utility.rollback_up_aol_master','(none)','R');
942 RAISE;
943 --
944 END rollback_up_aol_master;
945 --
946
947 -- ---------------------- rollback_download_master ------------------------
948 -- Description: Rows in the datapump for batches corresponding to the current
949 -- migration are deleted. All entries in the hr_dm_migration_ranges and the
950 -- hr_dm_phase_items table for the download phase which have a status of
951 -- S or E are reset to NS.
952 --
953 --
954 -- Input Parameters
955 -- p_migration_id - current migration id
956 --
957 -- Output Parameters
958 -- <none>
959 --
960 --
961 -- ------------------------------------------------------------------------
962
963 --
964 PROCEDURE rollback_download_master (p_migration_id IN NUMBER) IS
965 --
966 l_phase_item_id NUMBER;
967 l_short_name VARCHAR2(30);
968 l_table_name VARCHAR2(30);
969 l_starting_process_sequence NUMBER;
970 l_ending_process_sequence NUMBER;
971 l_batch_id NUMBER;
972 l_range_id NUMBER;
973 l_call_delete VARCHAR2(200);
974 l_range_phase_id NUMBER;
975 l_phase_id NUMBER;
976
977 CURSOR csr_c1 IS
978 SELECT phi.phase_item_id
979 FROM hr_dm_phase_items phi,
980 hr_dm_phases ph
981 WHERE (ph.migration_id = p_migration_id)
982 AND (ph.phase_name = 'DP')
983 AND (phi.phase_id = ph.phase_id)
984 AND (phi.status IN ('S', 'E'));
985
986 CURSOR csr_c2 IS
987 SELECT tbl.table_name,
988 tbl.short_name,
989 mr.starting_process_sequence,
990 mr.ending_process_sequence,
991 mr.range_id
992 FROM hr_dm_migration_ranges mr,
993 hr_dm_tables tbl,
994 hr_dm_phase_items pi
995 WHERE (pi.phase_id = l_range_phase_id)
996 AND (pi.phase_item_id = mr.phase_item_id)
997 AND (pi.table_name = tbl.table_name)
998 AND (mr.status IN ('S', 'E'));
999
1000 CURSOR csr_c3 IS
1001 SELECT pi.batch_id
1002 FROM hr_dm_migration_ranges mr,
1003 hr_dm_tables tbl,
1004 hr_dm_phase_items pi,
1005 hr_dm_table_groupings tgp
1006 WHERE (pi.phase_id = l_phase_id)
1007 AND (pi.group_id = tgp.group_id)
1008 AND (tbl.table_name = l_table_name)
1009 AND (tbl.table_id = tgp.table_id);
1010
1011
1012 --
1013 BEGIN
1014 --
1015
1016 message('ROUT','entry:hr_dm_utility.rollback_download_master', 5);
1017 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1018
1019 -- find the range phase id
1020 l_range_phase_id := get_phase_id('R', p_migration_id);
1021
1022 -- find the DP phase id
1023 l_phase_id := get_phase_id('DP', p_migration_id);
1024
1025 -- find all download phase items for this migration that are started or
1026 -- in error
1027 OPEN csr_c1;
1028 LOOP
1029 FETCH csr_c1 INTO l_phase_item_id;
1030 EXIT WHEN csr_c1%NOTFOUND;
1031 -- delete information from batch_lines...
1032 OPEN csr_c2;
1033 LOOP
1034 FETCH csr_c2 INTO l_table_name,l_short_name,
1035 l_starting_process_sequence,
1036 l_ending_process_sequence, l_range_id;
1037 EXIT WHEN csr_c2%NOTFOUND;
1038
1039 -- get the batch_id
1040 OPEN csr_c3;
1041 FETCH csr_c3 INTO l_batch_id;
1042 CLOSE csr_c3;
1043
1044 -- now call the delete function
1045 l_call_delete := 'begin hrdmd_' || l_short_name || '.delete_datapump'
1046 || '(p_batch_id => ' || l_batch_id
1047 || ', p_start_id => ' || l_starting_process_sequence
1048 || ', p_end_id => ' || l_ending_process_sequence
1049 || ', p_chunk_size => 10'
1050 || '); end;';
1051 hr_dm_library.run_sql(l_call_delete);
1052
1053
1054 -- update status to not started
1055 update_migration_ranges('NS', l_range_id);
1056 END LOOP;
1057 CLOSE csr_c2;
1058
1059 -- now update phase_item to avoid problem if no migration
1060 -- ranges were in error
1061 update_phase_items('NS',l_phase_item_id);
1062 END LOOP;
1063 CLOSE csr_c1;
1064
1065
1066
1067 message('INFO','Rollback', 15);
1068 message('SUMM','Rollback', 20);
1069 message('ROUT','exit:hr_dm_utility.rollback_download_master', 25);
1070 message('PARA','(none)', 30);
1071
1072 -- error handling
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 error(SQLCODE,'hr_dm_utility.rollback_download_master','(none)','R');
1076 RAISE;
1077 --
1078 END rollback_download_master;
1079 --
1080
1081
1082 -- ---------------------- rollback_init ------------------------
1083 -- Description: All entries in the hr_dm_phases and the
1084 -- hr_dm_phase_items table are deleted and the status of the
1085 -- migration is reset to NS.
1086 --
1087 --
1088 -- Input Parameters
1089 -- p_phase_item_id - current phase item id
1090 --
1091 -- Output Parameters
1092 -- <none>
1093 --
1094 --
1095 -- ------------------------------------------------------------------------
1096
1097 --
1098 PROCEDURE rollback_init (p_migration_id IN NUMBER) IS
1099 --
1100
1101 l_phase_id NUMBER;
1102
1103 CURSOR csr_c1 IS
1104 SELECT ph.phase_id
1105 FROM hr_dm_phases ph
1106 WHERE (ph.migration_id = p_migration_id)
1107 AND (ph.phase_name = 'I')
1108 AND (ph.status IN ('S', 'E'));
1109
1110 --
1111 BEGIN
1112 --
1113
1114 message('ROUT','entry:hr_dm_utility.rollback_init', 5);
1115 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1116
1117 -- find all init phases for this migration that are started or in error
1118 OPEN csr_c1;
1119 LOOP
1120 FETCH csr_c1 INTO l_phase_id;
1121 EXIT WHEN csr_c1%NOTFOUND;
1122 -- delete all entries in hr_dm_phase_items
1123 DELETE FROM hr_dm_phase_items
1124 WHERE phase_id = l_phase_id;
1125
1126 -- delete information from hr_dm_phases
1127 DELETE FROM hr_dm_phases
1128 WHERE phase_id = l_phase_id;
1129
1130 END LOOP;
1131 CLOSE csr_c1;
1132
1133 -- update status to started
1134 update_migrations('S', p_migration_id);
1135
1136
1137 message('INFO','Rollback - init', 15);
1138 message('SUMM','Rollback - init', 20);
1139 message('ROUT','exit:hr_dm_utility.rollback_init', 25);
1140 message('PARA','(none)', 30);
1141
1142 -- error handling
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 error(SQLCODE,'hr_dm_utility.rollback_init','(none)','R');
1146 RAISE;
1147 --
1148 END rollback_init;
1149 --
1150
1151 -- ---------------------------- rollback_generator -------------------------
1152 -- Description: All entries in the hr_dm_phase_items table for the generator
1153 -- phase are reset to NS.
1154 --
1155 --
1156 -- Input Parameters
1157 -- p_migration_id - current migration id
1158 --
1159 -- Output Parameters
1160 -- <none>
1161 --
1162 --
1163 -- ------------------------------------------------------------------------
1164
1165
1166 --
1167 PROCEDURE rollback_generator (p_migration_id IN NUMBER) IS
1168 --
1169
1170 CURSOR csr_c1 IS
1171 SELECT phi.phase_item_id
1172 FROM hr_dm_phase_items phi,
1173 hr_dm_phases ph
1174 WHERE (ph.migration_id = p_migration_id)
1175 AND (ph.phase_name = 'G')
1176 AND (phi.phase_id = ph.phase_id)
1177 AND (phi.status IN ('S', 'E'));
1178
1179 l_phase_item_id NUMBER;
1180
1181 --
1182 BEGIN
1183 --
1184
1185 message('ROUT','entry:hr_dm_utility.rollback_generator', 5);
1186 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1187
1188 -- find all generator phases for this migration that are started or in error
1189 OPEN csr_c1;
1190 LOOP
1191 FETCH csr_c1 INTO l_phase_item_id;
1192 EXIT WHEN csr_c1%NOTFOUND;
1193 -- update status to not started
1194 update_phase_items('NS', l_phase_item_id);
1195 END LOOP;
1196 CLOSE csr_c1;
1197
1198 message('INFO','Rollback - generator', 15);
1199 message('SUMM','Rollback - generator', 20);
1200 message('ROUT','exit:hr_dm_utility.rollback_generator', 25);
1201 message('PARA','(none)', 30);
1202
1203 -- error handling
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 error(SQLCODE,'hr_dm_utility.rollback_generator','(none)','R');
1207 RAISE;
1208 --
1209 END rollback_generator;
1210 --
1211
1212 -- ---------------------------- rollback_cleanup -------------------------
1213 -- Description: All entries in the hr_dm_phase_items table for the cleanup
1214 -- phase are reset to NS.
1215 --
1216 --
1217 -- Input Parameters
1218 -- p_migration_id - current migration id
1219 --
1220 -- Output Parameters
1221 -- <none>
1222 --
1223 --
1224 -- ------------------------------------------------------------------------
1225
1226
1227 --
1228 PROCEDURE rollback_cleanup (p_migration_id IN NUMBER) IS
1229 --
1230
1231 CURSOR csr_c1 IS
1232 SELECT phi.phase_item_id
1233 FROM hr_dm_phase_items phi,
1234 hr_dm_phases ph
1235 WHERE (ph.migration_id = p_migration_id)
1236 AND (ph.phase_name = 'C')
1237 AND (phi.phase_id = ph.phase_id)
1238 AND (phi.status IN ('S', 'E'));
1239
1240 l_phase_item_id NUMBER;
1241
1242 --
1243 BEGIN
1244 --
1245
1246 message('ROUT','entry:hr_dm_utility.rollback_cleanup', 5);
1247 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1248
1249 -- find all cleanup phases for this migration that are started or in error
1250 OPEN csr_c1;
1251 LOOP
1252 FETCH csr_c1 INTO l_phase_item_id;
1253 EXIT WHEN csr_c1%NOTFOUND;
1254 -- update status to not started
1255 update_phase_items('NS', l_phase_item_id);
1256 END LOOP;
1257 CLOSE csr_c1;
1258
1259 message('INFO','Rollback - cleanup', 15);
1260 message('SUMM','Rollback - cleanup', 20);
1261 message('ROUT','exit:hr_dm_utility.rollback_cleanup', 25);
1265 EXCEPTION
1262 message('PARA','(none)', 30);
1263
1264 -- error handling
1266 WHEN OTHERS THEN
1267 error(SQLCODE,'hr_dm_utility.rollback_cleanup','(none)','R');
1268 RAISE;
1269 --
1270 END rollback_cleanup;
1271 --
1272
1273 -- ------------------------- rollback_delete ------------------------
1274 -- Description: All entries in the hr_dm_migration_ranges and the
1275 -- hr_dm_phase_items tables for the delete phase are reset to NS.
1276 --
1277 --
1278 -- Input Parameters
1279 -- p_migration_id - current migration id
1280 --
1281 -- Output Parameters
1282 -- <none>
1283 --
1284 --
1285 -- ------------------------------------------------------------------------
1286
1287
1288 --
1289 PROCEDURE rollback_delete (p_migration_id IN NUMBER) IS
1290 --
1291
1292 l_phase_item_id NUMBER;
1293 l_range_id NUMBER;
1294 l_range_phase_id NUMBER;
1295
1296 CURSOR csr_c1 IS
1297 SELECT phi.phase_item_id
1298 FROM hr_dm_phase_items phi,
1299 hr_dm_phases ph
1300 WHERE (ph.migration_id = p_migration_id)
1301 AND (ph.phase_name = 'D')
1302 AND (phi.phase_id = ph.phase_id)
1303 AND (phi.status IN ('S', 'E'));
1304
1305 CURSOR csr_c2 IS
1306 SELECT mr.range_id
1307 FROM hr_dm_migration_ranges mr,
1308 hr_dm_phase_items pi
1309 WHERE (pi.phase_id = l_range_phase_id)
1310 AND (mr.phase_item_id = pi.phase_item_id)
1311 AND (mr.status IN ('S', 'E'));
1312
1313 --
1314 BEGIN
1315 --
1316
1317 message('ROUT','entry:hr_dm_utility.rollback_delete', 5);
1318 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1319
1320 -- find the range phase id
1321 l_range_phase_id := get_phase_id('R', p_migration_id);
1322
1323 -- find all delete phases for this migration that are started or in error
1324 OPEN csr_c1;
1325 LOOP
1326 FETCH csr_c1 INTO l_phase_item_id;
1327 EXIT WHEN csr_c1%NOTFOUND;
1328 -- update status to not started if required
1329 update_phase_items('NS', l_phase_item_id);
1330 END LOOP;
1331 CLOSE csr_c1;
1332
1333 -- reset all the migration ranges
1334 OPEN csr_c2;
1335 LOOP
1336 FETCH csr_c2 INTO l_range_id;
1337 EXIT WHEN csr_c2%NOTFOUND;
1338 IF csr_c2%FOUND THEN
1339 -- update status to not started
1340 update_migration_ranges('NS', l_range_id);
1341 END IF;
1342 END LOOP;
1343 CLOSE csr_c2;
1344
1345 message('INFO','Rollback - delete', 15);
1346 message('SUMM','Rollback - delete', 20);
1347 message('ROUT','exit:hr_dm_utility.rollback_delete', 25);
1348 message('PARA','(none)', 30);
1349
1350 -- error handling
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 error(SQLCODE,'hr_dm_utility.rollback_delete','(none)','R');
1354 RAISE;
1355 --
1356 END rollback_delete;
1357 --
1358
1359 -- ---------------------------- rollback_upload -------------------------
1360 -- Description: All entries in the hr_dm_phase_items table for the upload
1361 -- phase are reset to NS.
1362 --
1363 --
1364 -- Input Parameters
1365 -- p_migration_id - current migration id
1366 --
1367 -- Output Parameters
1368 -- <none>
1369 --
1370 --
1371 -- ------------------------------------------------------------------------
1372
1373
1374 --
1375 PROCEDURE rollback_upload (p_migration_id IN NUMBER) IS
1376 --
1377
1378 CURSOR csr_c1 IS
1379 SELECT phi.phase_item_id
1380 FROM hr_dm_phase_items phi,
1381 hr_dm_phases ph
1382 WHERE (ph.migration_id = p_migration_id)
1383 AND (ph.phase_name = 'UP')
1384 AND (phi.phase_id = ph.phase_id)
1385 AND (phi.status IN ('S', 'E'));
1386
1387 l_phase_item_id NUMBER;
1388
1389 --
1390 BEGIN
1391 --
1392
1393 message('ROUT','entry:hr_dm_utility.rollback_upload', 5);
1394 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1395
1396 -- find all upload phases for this migration that are started or in error
1397 OPEN csr_c1;
1398 LOOP
1399 FETCH csr_c1 INTO l_phase_item_id;
1400 EXIT WHEN csr_c1%NOTFOUND;
1401 -- update status to not started
1402 update_phase_items('NS', l_phase_item_id);
1403 END LOOP;
1404 CLOSE csr_c1;
1405
1406 message('INFO','Rollback - upload', 15);
1407 message('SUMM','Rollback - upload', 20);
1408 message('ROUT','exit:hr_dm_utility.rollback_upload', 25);
1409 message('PARA','(none)', 30);
1410
1411 -- error handling
1412 EXCEPTION
1413 WHEN OTHERS THEN
1414 error(SQLCODE,'hr_dm_utility.rollback_upload','(none)','R');
1415 RAISE;
1416 --
1417 END rollback_upload;
1418 --
1419
1420
1421 -- rollback procedures
1422 -- end
1423
1424 ----------------------------------------------------------------------------
1425
1426
1427 -- update status procedures
1428 -- start
1429
1430 -- ------------------------- update_migrations ------------------------
1431 -- Description: Updates the status of the migration in the hr_dm_migrations
1432 -- table. If the status is to be set to C then all child entries in
1433 -- hr_dm_phases are checked to ensure that they have completed.
1434 --
1435 --
1436 -- Input Parameters
1437 -- p_new_status - new status code
1438 --
1439 -- p_id - migration id
1440 --
1441 --
1442 -- Output Parameters
1443 -- <none>
1444 --
1445 --
1446 -- ------------------------------------------------------------------------
1447
1448 --
1449 PROCEDURE update_migrations (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
1450 --
1454
1451 -- table is hr_dm_migrations
1452 -- parent of hr_dm_phases
1453 -- child of (none)
1455 l_parent_table_id NUMBER(9);
1456 l_complete VARCHAR2(30);
1457 l_start_date DATE;
1458
1459 -- search child table for all complete
1460 CURSOR csr_child_table_complete IS
1461 SELECT status
1462 FROM hr_dm_phases
1463 WHERE ((migration_id = p_id)
1464 AND (status <> 'C'));
1465
1466 --
1467 BEGIN
1468 --
1469
1470 message('ROUT','entry:hr_dm_utility.update_migrations', 5);
1471 message('PARA','(p_new_status - ' || p_new_status ||
1472 ')(p_id - ' || p_id || ')', 10);
1473
1474 -- set start date for 'S'
1475 IF (p_new_status = 'S') THEN
1476 l_start_date := sysdate;
1477 END IF;
1478
1479
1480 -- non-complete
1481 IF (p_new_status IN('S', 'NS', 'E')) THEN
1482 -- update the status for this row
1483 UPDATE hr_dm_migrations
1484 SET status = p_new_status,
1485 migration_start_date = NVL(l_start_date, migration_start_date)
1486 WHERE migration_id = p_id;
1487 COMMIT;
1488 END IF;
1489
1490 -- complete
1491 IF (p_new_status = 'C') THEN
1492 -- check if really complete
1493 -- are any child rows not complete?
1494 OPEN csr_child_table_complete;
1495 FETCH csr_child_table_complete INTO l_complete;
1496
1497 IF (csr_child_table_complete%NOTFOUND) THEN
1498 -- update the status for this row since no child rows
1499 -- are incomplete
1500 UPDATE hr_dm_migrations
1501 SET status = p_new_status,
1502 migration_end_date = sysdate
1503 WHERE migration_id = p_id;
1504 COMMIT;
1505 -- set current processing
1506 set_process('Completed', NULL, p_id);
1507 END IF;
1508 CLOSE csr_child_table_complete;
1509 END IF;
1510
1511 message('INFO','Update status - update_migrations', 15);
1512 message('SUMM','Update status - update_migrations', 20);
1513 message('ROUT','exit:hr_dm_utility.update_migrations', 25);
1514 message('PARA','(none)', 30);
1515
1516 -- error handling
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 error(SQLCODE,'hr_dm_utility.update_migrations','(none)','R');
1520 RAISE;
1521
1522 --
1523 END update_migrations;
1524 --
1525
1526
1527 -- ------------------------- update_migrations_ranges ----------------------
1528 -- Description: Updates the status of the migration range in the
1529 -- hr_dm_migration_ranges table. If the status is to be set to C or E then
1530 -- the update status is cascaded up to the parent phase.
1531 --
1532 --
1533 -- Input Parameters
1534 -- p_new_status - new status code
1535 --
1536 -- p_id - migration range id
1537 --
1538 --
1539 -- Output Parameters
1540 -- <none>
1541 --
1542 --
1543 -- ------------------------------------------------------------------------
1544
1545 --
1546 PROCEDURE update_migration_ranges (p_new_status IN VARCHAR2,
1547 p_id IN NUMBER) IS
1548 --
1549 -- table is hr_dm_migration_ranges
1550 -- parent of (none)
1551 -- child of hr_dm_phase_items
1552
1553 l_parent_table_id NUMBER(9);
1554 l_parent_table_status VARCHAR2(30);
1555 l_start_time DATE;
1556 l_end_time DATE;
1557 l_phase_item_id NUMBER;
1558 l_complete VARCHAR2(30);
1559 l_phase_id NUMBER;
1560 l_migration_id NUMBER;
1561
1562 -- find migration id
1563 CURSOR csr_mig_id IS
1564 SELECT ph.migration_id
1565 FROM hr_dm_migration_ranges mr,
1566 hr_dm_phase_items pi,
1567 hr_dm_phases ph
1568 WHERE p_id = mr.range_id
1569 AND mr.phase_item_id = pi.phase_item_id
1570 AND pi.phase_id = ph.phase_id;
1571
1572 -- find parent phase item id
1573 CURSOR csr_par_pi_id IS
1574 SELECT pi_par.phase_item_id
1575 FROM hr_dm_phase_items pi_par,
1576 hr_dm_tables tbl,
1577 hr_dm_migration_ranges mr,
1578 hr_dm_phase_items pi_rg,
1579 hr_dm_table_groupings tgp
1580 WHERE p_id = mr.range_id
1581 AND mr.phase_item_id = pi_rg.phase_item_id
1582 AND pi_rg.table_name = tbl.table_name
1583 AND tbl.table_id = tgp.table_id
1584 AND tgp.group_id = pi_par.group_id
1585 AND pi_par.phase_id = l_phase_id;
1586
1587
1588 -- search 'child' table for all complete
1589 CURSOR csr_child_table_complete IS
1590 SELECT mr.status
1591 FROM hr_dm_migration_ranges mr,
1592 hr_dm_phase_items rg_pi
1593 WHERE (mr.phase_item_id = rg_pi.phase_item_id)
1594 AND (rg_pi.group_id = (SELECT rg_pi.group_id
1595 FROM hr_dm_phase_items rg_pi,
1596 hr_dm_migration_ranges mr
1597 WHERE p_id = mr.range_id
1598 AND mr.phase_item_id = rg_pi.phase_item_id))
1599 AND (mr.status <> 'C');
1600
1601
1602 CURSOR csr_parent_status IS
1603 SELECT status
1604 FROM hr_dm_phase_items
1605 WHERE phase_item_id = l_parent_table_id;
1606
1607
1608
1609 --
1610 BEGIN
1611 --
1612 message('ROUT','entry:hr_dm_utility.update_migration_ranges', 5);
1613 message('PARA','(p_new_status - ' || p_new_status ||
1614 ')(p_id - ' || p_id || ')', 10);
1615
1616 -- get the parent phase_id
1617 OPEN csr_mig_id;
1618 FETCH csr_mig_id INTO l_migration_id;
1619 CLOSE csr_mig_id;
1620
1621 -- first see if it is a delete migration
1622 l_phase_id := hr_dm_utility.get_phase_id('D', l_migration_id);
1623 -- if null returned then it must be a DP phase we are looking for
1624 IF (l_phase_id IS NULL) THEN
1628 -- set start time for 'S'
1625 l_phase_id := hr_dm_utility.get_phase_id('DP', l_migration_id);
1626 END IF;
1627
1629 IF (p_new_status = 'S') THEN
1630 l_start_time := sysdate;
1631 END IF;
1632
1633 -- set end time for 'C'
1634 IF (p_new_status = 'C') THEN
1635 l_end_time := sysdate;
1636 END IF;
1637
1638 -- update the status for this row
1639 UPDATE hr_dm_migration_ranges
1640 SET status = p_new_status,
1641 start_time = NVL(l_start_time, start_time),
1642 end_time = NVL(l_end_time, end_time)
1643 WHERE range_id = p_id;
1644 COMMIT;
1645
1646 -- update parent for error
1647 IF (p_new_status = 'E') THEN
1648 -- now cascade to parent table
1649 OPEN csr_par_pi_id;
1650 FETCH csr_par_pi_id INTO l_parent_table_id;
1651 CLOSE csr_par_pi_id;
1652 update_phase_items('E',l_parent_table_id);
1653 END IF;
1654
1655 -- if all rows are complete, then update parent
1656 IF (p_new_status = 'C') THEN
1657 -- get parent phase item
1658 OPEN csr_par_pi_id;
1659 FETCH csr_par_pi_id INTO l_parent_table_id;
1660 CLOSE csr_par_pi_id;
1661
1662 OPEN csr_child_table_complete;
1663 FETCH csr_child_table_complete INTO l_complete;
1664 IF (csr_child_table_complete%NOTFOUND) THEN
1665 -- now cascade to parent table
1666 update_phase_items(p_new_status,l_parent_table_id);
1667 END IF;
1668 CLOSE csr_child_table_complete;
1669 END IF;
1670
1671
1672 -- update parent status to started if parent has a status of NS
1673 IF (p_new_status = 'S') THEN
1674 -- get parent phase item
1675 OPEN csr_par_pi_id;
1676 FETCH csr_par_pi_id INTO l_parent_table_id;
1677 CLOSE csr_par_pi_id;
1678
1679 OPEN csr_parent_status;
1680 FETCH csr_parent_status INTO l_parent_table_status;
1681 CLOSE csr_parent_status;
1682
1683 IF (l_parent_table_status = 'NS') THEN
1684 update_phase_items('S',l_parent_table_id);
1685 END IF;
1686
1687
1688 END IF;
1689
1690
1691 message('INFO','Update status - update_migration_ranges', 15);
1692 message('SUMM','Update status - update_migration_ranges', 20);
1693 message('ROUT','exit:hr_dm_utility.update_migration_ranges', 25);
1694 message('PARA','(none)', 30);
1695
1696 -- error handling
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 error(SQLCODE,'hr_dm_utility.update_migration_ranges','(none)','R');
1700 RAISE;
1701
1702 --
1703 END update_migration_ranges;
1704 --
1705
1706
1707 -- ------------------------- update_phase_items ----------------------
1708 -- Description: Updates the status of the phase item in the
1709 -- hr_dm_phase_items table. If the status is to be set to C or E then
1710 -- the update status is cascaded up to the parent phase.
1711 --
1712 --
1713 -- Input Parameters
1714 -- p_new_status - new status code
1715 --
1716 -- p_id - phase_item id
1717 --
1718 --
1719 -- Output Parameters
1720 -- <none>
1721 --
1722 --
1723 -- ------------------------------------------------------------------------
1724
1725 --
1726 PROCEDURE update_phase_items (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
1727 --
1728 -- table is hr_dm_phase_items
1729 -- parent of n/a
1730 -- child of hr_dm_phases
1731
1732 l_parent_table_id NUMBER(9);
1733 l_start_time DATE;
1734 l_end_time DATE;
1735
1736 -- find parent table id
1737 CURSOR csr_parent_id IS
1738 SELECT phase_id
1739 FROM hr_dm_phase_items
1740 WHERE phase_item_id = p_id;
1741
1742
1743 --
1744 BEGIN
1745 --
1746
1747 message('ROUT','entry:hr_dm_utility.update_phase_items', 5);
1748 message('PARA','(p_new_status - ' || p_new_status ||
1749 ')(p_id - ' || p_id || ')', 10);
1750
1751 -- set start time for 'S'
1752 IF (p_new_status = 'S') THEN
1753 l_start_time := sysdate;
1754 END IF;
1755
1756 -- set end time for 'C'
1757 IF (p_new_status = 'C') THEN
1758 l_end_time := sysdate;
1759 END IF;
1760
1761 -- update the status for this row
1762 UPDATE hr_dm_phase_items
1763 SET status = p_new_status,
1764 start_time = NVL(l_start_time, start_time),
1765 end_time = NVL(l_end_time, end_time)
1766 WHERE phase_item_id = p_id;
1767 COMMIT;
1768
1769 -- update parent?
1770 IF (p_new_status IN('C', 'E')) THEN
1771 OPEN csr_parent_id;
1772 FETCH csr_parent_id INTO l_parent_table_id;
1773 CLOSE csr_parent_id;
1774 update_phases(p_new_status,l_parent_table_id);
1775 END IF;
1776
1777 message('INFO','Update status - update_phase_items', 15);
1778 message('SUMM','Update status - update_phase_items', 20);
1779 message('ROUT','exit:hr_dm_utility.update_phase_items', 25);
1780 message('PARA','(none)', 30);
1781
1782 -- error handling
1783 EXCEPTION
1784 WHEN OTHERS THEN
1785 error(SQLCODE,'hr_dm_utility.update_phase_items','(none)','R');
1786 RAISE;
1787
1788 --
1789 END update_phase_items;
1790 --
1791
1792 -- ------------------------- update_phases ----------------------
1793 -- Description: Updates the status of the phase in the
1794 -- hr_dm_phases table. If the status is to be set to C or E then
1795 -- the update status is cascaded up to the parent phase. For a C,
1796 -- the status of all the child rows in the hr_dm_phase_items is
1797 -- checked.
1798 --
1799 --
1800 -- Input Parameters
1801 -- p_new_status - new status code
1802 --
1803 -- p_id - phase_item id
1804 --
1805 --
1806 -- Output Parameters
1807 -- <none>
1808 --
1809 --
1810 -- ------------------------------------------------------------------------
1811
1812 --
1813 PROCEDURE update_phases (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
1814 --
1815 -- table is hr_dm_phases
1816 -- parent of hr_dm_phase_items
1817 -- child of hr_dm_migrations
1818
1819 l_parent_table_id NUMBER(9);
1820 l_complete VARCHAR2(30);
1821 l_start_time DATE;
1822 l_new_status VARCHAR2(30);
1823
1824 -- search child table for all complete
1825 CURSOR csr_child_table_complete IS
1826 SELECT status
1827 FROM hr_dm_phase_items
1828 WHERE ((phase_id = p_id)
1829 AND (status <> 'C'));
1830
1831 -- find parent table id
1832 CURSOR csr_parent_id IS
1833 SELECT migration_id
1834 FROM hr_dm_phases
1835 WHERE phase_id = p_id;
1836
1837
1838 --
1839 BEGIN
1840 --
1841
1842 message('ROUT','entry:hr_dm_utility.update_phases', 5);
1843 message('PARA','(p_new_status - ' || p_new_status ||
1844 ')(p_id - ' || p_id || ')', 10);
1845
1846 l_new_status := p_new_status;
1847
1848 -- set start time for 'S'
1849 IF (l_new_status = 'S') THEN
1850 l_start_time := sysdate;
1851 END IF;
1852
1853 -- non-complete
1854 IF (l_new_status IN('S', 'NS', 'E')) THEN
1855 -- update the status for this row
1856 UPDATE hr_dm_phases
1857 SET status = l_new_status,
1858 start_time = NVL(l_start_time, start_time)
1859 WHERE phase_id = p_id;
1860 COMMIT;
1861 END IF;
1862
1863 -- complete
1864 IF (l_new_status = 'C') THEN
1865 -- check if really complete
1866 -- are any child rows not complete?
1867 OPEN csr_child_table_complete;
1868 FETCH csr_child_table_complete INTO l_complete;
1869
1870 IF (csr_child_table_complete%NOTFOUND) THEN
1871 -- update the status for this row since no child rows
1872 -- are incomplete
1873 UPDATE hr_dm_phases
1874 SET status = l_new_status,
1875 end_time = sysdate
1876 WHERE phase_id = p_id;
1877 COMMIT;
1878 ELSE
1879 -- unset status to preven cascade
1880 l_new_status := 'c';
1881 END IF;
1882 CLOSE csr_child_table_complete;
1883 END IF;
1884
1885 -- update parent?
1886 IF (l_new_status IN('C', 'E')) THEN
1887 OPEN csr_parent_id;
1888 FETCH csr_parent_id INTO l_parent_table_id;
1889 CLOSE csr_parent_id;
1890 update_migrations(l_new_status,l_parent_table_id);
1891 END IF;
1892
1893
1894 message('INFO','Update status - update_phases', 15);
1895 message('SUMM','Update status - update_phases', 20);
1896 message('ROUT','exit:hr_dm_utility.update_phases', 25);
1897 message('PARA','(none)', 30);
1898
1899 -- error handling
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 error(SQLCODE,'hr_dm_utility.update_phases','(none)','R');
1903 RAISE;
1904
1905 --
1906 END update_phases;
1907 --
1908
1909
1910 -- update status procedures
1911 -- end
1912
1913
1914
1915 end hr_dm_utility;
[Home] [Help]