[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_UTILITY
Source
1 PACKAGE BODY ben_dm_utility AS
2 /* $Header: benfdmutil.pkb 120.0 2006/05/11 05:04:12 nkkrishn 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 /*------------------------------- ROUTINES --------------------------------*/
18
19 -- general purpose procedures
20 -- start
21
22 -- ------------------------- number_of_threads ------------------------
23 -- Description: Finds the number of threads for the concurrent manager
24 -- to use by looking at ben_batch_parameters which is striped by business
25 -- group id. Fetch the first Thread count found and use that which will
26 -- be irrepective of a BG due to that fact the Person Migrator can work
27 -- across Business Groups and there is no way to change the threads which
28 -- are spawned during a Migrator processing.
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 (batch_exe_cd = 'BENDM');
55
56 --
57 BEGIN
58 --
59
60 message('ROUT','entry:ben_dm_utility.number_of_threads', 5);
61 message('PARA','(p_business_group_id - ' || p_business_group_id || ')', 10);
62
63
64 OPEN csr_threads;
65 LOOP
66 FETCH csr_threads INTO l_threads;
67 EXIT when csr_threads%NOTFOUND;
68 END LOOP;
69 CLOSE csr_threads;
70
71 -- set default value if no entry exists for the current business group
72 IF (l_threads IS NULL) THEN
73 l_threads := 3;
74 message('INFO','No value for the number of threads found in ' ||
75 'ben_batch_parameters - using default value', 12);
76 END IF;
77
78 -- make sure that we have at least one thread
79 IF (l_threads <1) THEN
80 l_threads := 1;
81 message('INFO','The number of threads has been set to one as this is' ||
82 ' The minimum value permitted.', 13);
83 END IF;
84
85 message('INFO','Found number of threads', 15);
86 message('SUMM','Found number of threads', 20);
87 message('ROUT','exit:ben_dm_utility.number_of_threads', 25);
88 message('PARA','(l_threads - ' || l_threads || ')', 30);
89
90
91 RETURN(l_threads);
92
93 -- error handling
94 EXCEPTION
95 WHEN OTHERS THEN
96 -- error(SQLCODE,'ben_dm_utility.number_of_threads','(none)','R');
97 RAISE;
98
99 --
100 END number_of_threads;
101 --
102 -- ------------------------- get_phase_status ------------------------
103 -- Description: Reads the status of the passed phase from the hr_dm_phases
104 -- table.
105 --
106 --
107 -- Input Parameters
108 -- p_phase - code for phase to be reported on
109 --
110 -- p_migration_id - migration id of current migration
111 --
112 --
113 -- Output Parameters
114 -- <none>
115 --
116 --
117 -- Return Value
118 -- status of phase
119 --
120 --
121 -- ------------------------------------------------------------------------
122
123
124 --
125 FUNCTION get_phase_status(p_phase IN VARCHAR2, p_migration_id IN NUMBER)
126 RETURN VARCHAR2 IS
127 --
128
129 l_phase_status VARCHAR2(30);
130
131 CURSOR csr_status IS
132 SELECT status
133 FROM ben_dm_phases
134 WHERE ((migration_id = p_migration_id)
135 AND (phase_name = p_phase));
136
137 --
138 BEGIN
139 --
140
141 message('ROUT','entry:ben_dm_utility.get_phase_status', 5);
142 message('PARA','(p_phase - ' || p_phase ||
143 ')(p_migration_id - ' || p_migration_id || ')', 10);
144
145 OPEN csr_status;
146 LOOP
147 FETCH csr_status INTO l_phase_status;
148 EXIT when csr_status%NOTFOUND;
149 END LOOP;
150 CLOSE csr_status;
151
152 -- use a ? to represent a null value being returned
153 l_phase_status := NVL(l_phase_status, '?');
154
155
156 message('INFO','Find Phase Status', 15);
157 message('SUMM','Find Phase Status', 20);
158 message('ROUT','exit:ben_dm_utility.get_phase_status', 25);
159 message('PARA','(l_phase_status - ' || l_phase_status || ')', 30);
160
161 RETURN(l_phase_status);
162
163 -- error handling
164 EXCEPTION
165 WHEN OTHERS THEN
166 error(SQLCODE,'ben_dm_utility.get_phase_status','(none)','R');
167 RAISE;
168
169 --
170 END get_phase_status;
171 --
172
173 -- ------------------------- get_phase_id ------------------------
174 -- Description: Reads the phase id of the passed phase from the
175 -- hr_dm_phases table.
176 --
177 --
178 -- Input Parameters
179 -- p_phase - code for phase to be reported on
180 --
181 -- p_migration_id - migration id of current migration
182 --
183 --
184 -- Output Parameters
185 -- <none>
186 --
187 --
188 -- Return Value
189 -- phase id
190 --
191 --
192 -- ------------------------------------------------------------------------
193
194
195 --
196 FUNCTION get_phase_id(p_phase IN VARCHAR2, p_migration_id IN NUMBER)
197 RETURN NUMBER IS
198 --
199
200 l_phase_id NUMBER;
201
202 CURSOR csr_phase IS
203 SELECT phase_id
204 FROM ben_dm_phases
205 WHERE ((migration_id = p_migration_id)
206 AND (phase_name = p_phase));
207
208 --
209 BEGIN
210 --
211
212 message('ROUT','entry:ben_dm_utility.get_phase_id', 5);
213 message('PARA','(p_phase - ' || p_phase ||
214 ')(p_migration_id - ' || p_migration_id || ')', 10);
215
216
217 OPEN csr_phase;
218 LOOP
219 FETCH csr_phase INTO l_phase_id;
220 EXIT when csr_phase%NOTFOUND;
221 END LOOP;
222 CLOSE csr_phase;
223
224
225 message('INFO','Find Phase ID', 15);
226 message('SUMM','Find Phase ID', 20);
227 message('ROUT','exit:ben_dm_utility.get_phase_id', 25);
228 message('PARA','(l_phase_id - ' || l_phase_id || ')', 30);
229
230 RETURN(l_phase_id);
231
232 -- error handling
233 EXCEPTION
234 WHEN OTHERS THEN
235 error(SQLCODE,'ben_dm_utility.get_phase_id','(none)','R');
236 RAISE;
237
238 --
239 END get_phase_id;
240 --
241
242 -- ------------------------- error ------------------------
243 -- Description: When an error has occurred elsewhere, this procedure is
244 -- called to log the FAIL message which includes the failure code and
245 -- the failure message plus additional information supplied by the
246 -- function/procedure that errored. It can also do a commit or rollback
247 -- when called.
248 --
249 --
250 -- Input Parameters
251 -- p_sqlcode - the sql error code
252 --
253 -- p_procedure - the procedure / function name where the error
254 -- occurred, including the package name
255 --
256 -- p_extra - any additional text to be appended to the
257 -- FAIL message
258 --
259 -- p_rollback - if a rollback (R) (default) or commit (C) is required
260 --
261 --
262 -- Output Parameters
263 -- <none>
264 --
265 --
266 -- ------------------------------------------------------------------------
267
268
269 --
270 PROCEDURE error (p_sqlcode IN NUMBER, p_procedure IN VARCHAR2,
271 p_extra IN VARCHAR2, p_rollback IN VARCHAR2 DEFAULT 'R') IS
272 --
273
274 --
275 BEGIN
276 --
277
278 message('ROUT','entry:ben_dm_utility.error', 5);
279 message('PARA','(p_sqlcode - ' || p_sqlcode ||
280 ')(p_procedure - ' || p_procedure || ')', 10);
281
282 message('FAIL',p_sqlcode || ':' || SQLERRM(p_sqlcode) || ':'
283 || p_extra, 15);
284
285 IF (p_rollback = 'R') THEN
286 ROLLBACK;
287 END IF;
288 IF (p_rollback = 'C') THEN
289 COMMIT;
290 END IF;
291
292 message('INFO','Error Handler - ' || p_procedure, 20);
293 message('SUMM','Error Handler - ' || p_procedure, 25);
294 message('ROUT','exit:ben_dm_utility.error', 30);
295 message('PARA','(none)', 35);
296
297 -- error handling
298 EXCEPTION
299 WHEN OTHERS THEN
300 error(SQLCODE,'ben_dm_utility.error','(none)','R');
301 RAISE;
302
303
304 --
305 END error;
306 --
307 -- ------------------------- message_init ------------------------
308 -- Description: Message logging for concurrent managers is initialized
309 -- by calling this procedure. It obtains the logging options from the
310 -- table pay_action_parameters. By default, SUMM and FAIL messages are
311 -- always enabled for sending to the log file.
312 --
313 --
314 -- Input Parameters
315 -- <none>
316 --
317 --
318 -- Output Parameters
319 -- <none>
320 --
321 --
322 -- ------------------------------------------------------------------------
323
324 --
325 PROCEDURE message_init IS
326 --
327 CURSOR csr_c1 IS
328 SELECT parameter_value
329 FROM pay_action_parameters
330 WHERE parameter_name = 'HR_DM_DEBUG_PIPE';
331 CURSOR csr_c2 IS
332 SELECT parameter_value
333 FROM pay_action_parameters
334 WHERE parameter_name = 'HR_DM_DEBUG_LOG';
335
336 --
337 BEGIN
338 --
339
340 -- read values from pay_action_parameters
341
342 OPEN csr_c1;
343 LOOP
344 FETCH csr_c1 INTO g_debug_message_pipe;
345 EXIT WHEN csr_c1%NOTFOUND;
346 END LOOP;
347 CLOSE csr_c1;
348
349 OPEN csr_c2;
350 LOOP
351 FETCH csr_c2 INTO g_debug_message_log;
352 EXIT WHEN csr_c2%NOTFOUND;
353 END LOOP;
354 CLOSE csr_c2;
355
356 -- ensure that summary and fail settings are set
357
358 IF ((INSTRB(g_debug_message_log, 'SUMM') IS NULL) OR
359 (INSTRB(g_debug_message_log, 'SUMM') = 0)) THEN
360 g_debug_message_log := g_debug_message_log || ':SUMM';
361 END IF;
362
363 IF ((INSTRB(g_debug_message_log, 'FAIL') IS NULL) OR
364 (INSTRB(g_debug_message_log, 'FAIL') = 0)) THEN
365 g_debug_message_log := g_debug_message_log || ':FAIL';
366 END IF;
367
368 -- start the indenting to zero indentation
369 g_debug_message_indent := 0;
370
371
372 -- error handling
373 EXCEPTION
374 WHEN OTHERS THEN
375 error(SQLCODE,'ben_dm_utility.message_init','(none)','R');
376 RAISE;
377
378 --
379 END message_init;
380 --
381 -- ------------------------- message ------------------------
382 -- Description: Logs the message to the log file and / or the
383 -- pipe for the options that have been configured by calling message_init.
384 --
385 --
386 -- Input Parameters
387 -- p_type - message type
388 --
389 -- p_message - text of message
390 --
391 -- p_position - position value for piped messages
392 --
393 --
394 -- Output Parameters
395 -- <none>
396 --
397 --
398 -- ------------------------------------------------------------------------
399
400 --
401 PROCEDURE message (p_type IN VARCHAR2, p_message IN VARCHAR2,
402 p_position IN NUMBER) IS
403 --
404
405 l_header VARCHAR2(30);
406 l_debug VARCHAR2(100);
407 l_message VARCHAR2(32767);
408
409 --
410 BEGIN
411 --
412
413 -- are we interested in this type of message?
414 l_debug := g_debug_message_pipe || ':' || g_debug_message_log;
415
416 IF (INSTRB(l_debug, p_type) <> 0) THEN
417 l_message := p_message;
418 -- indent non-routing messages
419 -- IF (p_type <> 'ROUT') THEN
420 -- l_message := ' ' || l_message;
421 -- END IF;
422
423 -- for ROUT entry messages change indent
424 -- decrease for exit messages
425 IF (p_type = 'ROUT') AND (substr(p_message,1,5) = 'exit:') THEN
426 g_debug_message_indent := g_debug_message_indent -
427 g_debug_message_indent_size;
428 END IF;
429
430
431 -- indent all messages to show nesting of functions
432 l_message := rpad(' ', g_debug_message_indent) || l_message;
433
434 -- for ROUT entry messages change indent
435 -- increase for entry messages
436 IF (p_type = 'ROUT') AND (substr(p_message,1,6) = 'entry:') THEN
437 g_debug_message_indent := g_debug_message_indent +
438 g_debug_message_indent_size;
439 END IF;
440
441
442
443 -- build header
444 l_header := p_type || ':' || TO_CHAR(sysdate,'HH24MISS');
445
446 -- send to pipe?
447 IF (INSTRB(g_debug_message_pipe, p_type) <> 0) THEN
448 hr_utility.set_location(l_header || ':-:' || l_message, p_position);
449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_header || ':-:' || l_message);
450 END IF;
451
452 -- send to log file?
453 IF (INSTRB(g_debug_message_log, p_type) <> 0) THEN
454 FND_FILE.PUT_LINE(FND_FILE.LOG, l_header || ':-:' || l_message);
455 END IF;
456 END IF;
457
458
459 -- error handling
460 EXCEPTION
461 WHEN OTHERS THEN
462 error(SQLCODE,'ben_dm_utility.message','(none)','R');
463 RAISE;
464
465 --
466 END message;
467 --
468 -- update status procedures
469 -- start
470
471 -- ------------------------- update_migrations ------------------------
472 -- Description: Updates the status of the migration in the ben_dm_migrations
473 -- table. If the status is to be set to C then all child entries in
474 -- ben_dm_phases are checked to ensure that they have completed.
475 --
476 --
477 -- Input Parameters
478 -- p_new_status - new status code
479 --
480 -- p_id - migration id
481 --
482 --
483 -- Output Parameters
484 -- <none>
485 --
486 --
487 -- ------------------------------------------------------------------------
488
489 --
490 PROCEDURE update_migrations (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
491 --
492 -- table is ben_dm_migrations
493 -- parent of ben_dm_phases
494 -- child of (none)
495
496 l_parent_table_id NUMBER(9);
497 l_complete VARCHAR2(30);
498 l_start_date DATE;
499
500 -- search child table for all complete
501 CURSOR csr_child_table_complete IS
502 SELECT status
503 FROM ben_dm_phases
504 WHERE ((migration_id = p_id)
505 AND (status <> 'C'));
506
507 --
508 BEGIN
509 --
510
511 message('ROUT','entry:ben_dm_utility.update_migrations', 5);
512 message('PARA','(p_new_status - ' || p_new_status ||
513 ')(p_id - ' || p_id || ')', 10);
514
515 -- set start date for 'S'
516 IF (p_new_status = 'S') THEN
520
517 l_start_date := sysdate;
518 END IF;
519
521 -- non-complete
522 IF (p_new_status IN('S', 'NS', 'E')) THEN
523 -- update the status for this row
524 UPDATE ben_dm_migrations
525 SET status = p_new_status,
526 migration_start_date = NVL(l_start_date, migration_start_date)
527 WHERE migration_id = p_id;
528 COMMIT;
529 END IF;
530
531 -- complete
532 IF (p_new_status = 'C') THEN
533 -- check if really complete
534 -- are any child rows not complete?
535 OPEN csr_child_table_complete;
536 FETCH csr_child_table_complete INTO l_complete;
537
538 IF (csr_child_table_complete%NOTFOUND) THEN
539 -- update the status for this row since no child rows
540 -- are incomplete
541 UPDATE ben_dm_migrations
542 SET status = p_new_status,
543 migration_end_date = sysdate
544 WHERE migration_id = p_id;
545 COMMIT;
546 END IF;
547 CLOSE csr_child_table_complete;
548 END IF;
549
550 message('INFO','Update status - update_migrations', 15);
551 message('SUMM','Update status - update_migrations', 20);
552 message('ROUT','exit:ben_dm_utility.update_migrations', 25);
553 message('PARA','(none)', 30);
554
555 -- error handling
556 EXCEPTION
557 WHEN OTHERS THEN
558 error(SQLCODE,'ben_dm_utility.update_migrations','(none)','R');
559 RAISE;
560
561 --
562 END update_migrations;
563 --
564 -- ------------------------- update_phase_items ----------------------
565 -- Description: Updates the status of the phase item in the
566 -- ben_dm_phase_items table. If the status is to be set to C or E then
567 -- the update status is cascaded up to the parent phase.
568 --
569 --
570 -- Input Parameters
571 -- p_new_status - new status code
572 --
573 -- p_id - phase_item id
574 --
575 --
576 -- Output Parameters
577 -- <none>
578 --
579 --
580 -- ------------------------------------------------------------------------
581
582 --
583 PROCEDURE update_phase_items (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
584 --
585 -- table is hr_dm_phase_items
586 -- parent of n/a
587 -- child of hr_dm_phases
588
589 l_parent_table_id NUMBER(9);
590 l_start_time DATE;
591 l_end_time DATE;
592
593 -- find parent table id
594 CURSOR csr_parent_id IS
595 SELECT phase_id
596 FROM ben_dm_phase_items
597 WHERE phase_item_id = p_id;
598
599
600 --
601 BEGIN
602 --
603
604 message('ROUT','entry:ben_dm_utility.update_phase_items', 5);
605 message('PARA','(p_new_status - ' || p_new_status ||
606 ')(p_id - ' || p_id || ')', 10);
607
608 -- set start time for 'S'
609 IF (p_new_status = 'S') THEN
610 l_start_time := sysdate;
611 END IF;
612
613 -- set end time for 'C'
614 IF (p_new_status = 'C') THEN
615 l_end_time := sysdate;
616 END IF;
617
618 -- update the status for this row
619 UPDATE ben_dm_phase_items
620 SET status = p_new_status,
621 start_time = NVL(l_start_time, start_time),
622 end_time = NVL(l_end_time, end_time)
623 WHERE phase_item_id = p_id;
624 COMMIT;
625
626 -- update parent?
627 IF (p_new_status IN('C', 'E')) THEN
628 OPEN csr_parent_id;
629 FETCH csr_parent_id INTO l_parent_table_id;
630 CLOSE csr_parent_id;
631 update_phases(p_new_status,l_parent_table_id);
632 END IF;
633
634 message('INFO','Update status - update_phase_items', 15);
635 message('SUMM','Update status - update_phase_items', 20);
636 message('ROUT','exit:ben_dm_utility.update_phase_items', 25);
637 message('PARA','(none)', 30);
638
639 -- error handling
640 EXCEPTION
641 WHEN OTHERS THEN
642 error(SQLCODE,'ben_dm_utility.update_phase_items','(none)','R');
643 RAISE;
644
645 --
646 END update_phase_items;
647 --
648 -- ------------------------- update_phases ----------------------
649 -- Description: Updates the status of the phase in the
650 -- ben_dm_phases table. If the status is to be set to C or E then
651 -- the update status is cascaded up to the parent phase. For a C,
652 -- the status of all the child rows in the ben_dm_phase_items is
653 -- checked.
654 --
655 --
656 -- Input Parameters
657 -- p_new_status - new status code
658 --
659 -- p_id - phase_item id
660 --
661 --
662 -- Output Parameters
663 -- <none>
664 --
665 --
666 -- ------------------------------------------------------------------------
667
668 --
669 PROCEDURE update_phases (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
670 --
671 -- table is ben_dm_phases
672 -- parent of ben_dm_phase_items
673 -- child of ben_dm_migrations
674
675 l_parent_table_id NUMBER(9);
676 l_complete VARCHAR2(30);
677 l_start_time DATE;
678 l_new_status VARCHAR2(30);
679
680 -- search child table for all complete
681 CURSOR csr_child_table_complete IS
682 SELECT status
683 FROM ben_dm_phase_items
684 WHERE ((phase_id = p_id)
685 AND (status <> 'C'));
686
687 -- find parent table id
688 CURSOR csr_parent_id IS
689 SELECT migration_id
690 FROM ben_dm_phases
691 WHERE phase_id = p_id;
692
693
694 --
695 BEGIN
696 --
697
698 message('ROUT','entry:ben_dm_utility.update_phases', 5);
699 message('PARA','(p_new_status - ' || p_new_status ||
700 ')(p_id - ' || p_id || ')', 10);
701
702 l_new_status := p_new_status;
703
707 END IF;
704 -- set start time for 'S'
705 IF (l_new_status = 'S') THEN
706 l_start_time := sysdate;
708
709 -- non-complete
710 IF (l_new_status IN('S', 'NS', 'E')) THEN
711 -- update the status for this row
712 UPDATE ben_dm_phases
713 SET status = l_new_status,
714 start_time = NVL(l_start_time, start_time)
715 WHERE phase_id = p_id;
716 COMMIT;
717 END IF;
718
719 -- complete
720 IF (l_new_status = 'C') THEN
721 -- check if really complete
722 -- are any child rows not complete?
723 OPEN csr_child_table_complete;
724 FETCH csr_child_table_complete INTO l_complete;
725
726 IF (csr_child_table_complete%NOTFOUND) THEN
727 -- update the status for this row since no child rows
728 -- are incomplete
729 UPDATE ben_dm_phases
730 SET status = l_new_status,
731 end_time = sysdate
732 WHERE phase_id = p_id;
733 COMMIT;
734 ELSE
735 -- unset status to preven cascade
736 l_new_status := 'c';
737 END IF;
738 CLOSE csr_child_table_complete;
739 END IF;
740
741 -- update parent?
742 IF (l_new_status IN('C', 'E')) THEN
743 OPEN csr_parent_id;
744 FETCH csr_parent_id INTO l_parent_table_id;
745 CLOSE csr_parent_id;
746 update_migrations(l_new_status,l_parent_table_id);
747 END IF;
748
749
750 message('INFO','Update status - update_phases', 15);
751 message('SUMM','Update status - update_phases', 20);
752 message('ROUT','exit:ben_dm_utility.update_phases', 25);
753 message('PARA','(none)', 30);
754
755 -- error handling
756 EXCEPTION
757 WHEN OTHERS THEN
758 error(SQLCODE,'ben_dm_utility.update_phases','(none)','R');
759 RAISE;
760
761 --
762 END update_phases;
763
764 -- ------------------------- rollback ------------------------
765 -- Description: The appropriate code (phase / phase item specific) for
766 -- the phase or phase item to be rolled back is called.
767 --
768 --
769 -- Input Parameters
770 -- p_phase - code for the phase to be rolled back
771 --
772 -- p_masterslave - MASTER indicates the rollback is for the phase
773 -- SLAVE is for a single phase item
774 --
775 -- p_migration_id - current migration id
776 --
777 -- p_phase_item_id - phase item to be rolled back
778 --
779 --
780 -- Output Parameters
781 -- <none>
782 --
783 --
784 -- ------------------------------------------------------------------------
785
786 --
787 PROCEDURE rollback (p_phase IN VARCHAR2,
788 p_masterslave IN VARCHAR2 DEFAULT NULL,
789 p_migration_id IN NUMBER DEFAULT NULL,
790 p_phase_item_id IN NUMBER DEFAULT NULL) IS
791 --
792
793 --
794 BEGIN
795 --
796
797 message('ROUT','entry:ben_dm_utility.rollback', 5);
798 message('PARA','(p_phase - ' || p_phase ||
799 ')(p_masterslave - ' || p_masterslave || ')', 10);
800
801 -- what type of rollback?
802
803 -- Init
804 IF ((p_phase = 'I') AND (p_migration_id IS NOT NULL)) THEN
805 rollback_init(p_migration_id);
806 END IF;
807
808 -- Generator
809 IF ((p_phase = 'G') AND (p_migration_id IS NOT NULL)) THEN
810 rollback_generator(p_migration_id);
811 END IF;
812
813 -- Download
814 IF (p_phase = 'DP') THEN
815 IF ((p_masterslave = 'MASTER') AND (p_migration_id IS NOT NULL)) THEN
816 rollback_download_master(p_migration_id);
817 END IF;
818 END IF;
819
820 -- Upload
821 IF ((p_phase = 'UP') AND (p_migration_id IS NOT NULL)) THEN
822 rollback_upload(p_migration_id);
823 END IF;
824
825
826 message('INFO','Rollback', 15);
827 message('SUMM','Rollback', 20);
828 message('ROUT','exit:ben_dm_utility.rollback', 25);
829 message('PARA','(none)', 30);
830
831 -- error handling
832 EXCEPTION
833 WHEN OTHERS THEN
834 error(SQLCODE,'ben_dm_utility.rollback','(none)','R');
835 RAISE;
836 --
837 END rollback;
838 --
839
840 -- ---------------------- rollback_download_master ------------------------
841 -- Description: Rows in the datapump for batches corresponding to the current
842 -- migration are deleted. All entries in the hr_dm_phase_items table for
843 -- the download phase which have a status of
844 -- S or E are reset to NS.
845 --
846 --
847 -- Input Parameters
848 -- p_migration_id - current migration id
849 --
850 -- Output Parameters
851 -- <none>
852 --
853 --
854 -- ------------------------------------------------------------------------
855 --
856 PROCEDURE rollback_download_master (p_migration_id IN NUMBER) IS
857 --
858 l_phase_item_id NUMBER;
859 l_group_order NUMBER;
860 l_short_name VARCHAR2(30);
861 l_table_name VARCHAR2(30);
862 l_phase_id NUMBER;
863
864 CURSOR csr_c1 IS
865 SELECT phi.phase_item_id, group_order
866 FROM ben_dm_phase_items phi,
867 ben_dm_phases ph
868 WHERE (ph.migration_id = p_migration_id)
869 AND (ph.phase_name = 'DP')
870 AND (phi.phase_id = ph.phase_id)
871 AND (phi.status IN ('S', 'E'));
872
873 --
874 BEGIN
875 --
876
877 message('ROUT','entry:ben_dm_utility.rollback_download_master', 5);
878 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
879
880 -- find the DP phase id
881 l_phase_id := get_phase_id('DP', p_migration_id);
882
883 -- find all download phase items for this migration that are started or
887 FETCH csr_c1 INTO l_phase_item_id, l_group_order;
884 -- in error
885 OPEN csr_c1;
886 LOOP
888 EXIT WHEN csr_c1%NOTFOUND;
889 --
890 -- delete information from ben_dm_entity_results table.
891 --
892 delete from ben_dm_entity_results
893 where group_order = l_group_order;
894
895 commit;
896 -- ?? Add EXCEPTIOn if no data found.
897 -- now update phase_item to avoid problem if no migration
898 -- ranges were in error
899 update_phase_items('NS',l_phase_item_id);
900 END LOOP;
901 CLOSE csr_c1;
902
903 message('INFO','Rollback', 15);
904 message('SUMM','Rollback', 20);
905 message('ROUT','exit:ben_dm_utility.rollback_download_master', 25);
906 message('PARA','(none)', 30);
907
908 -- error handling
909 EXCEPTION
910 WHEN OTHERS THEN
911 error(SQLCODE,'ben_dm_utility.rollback_download_master','(none)','R');
912 RAISE;
913 --
914 END rollback_download_master;
915 --
916
917 -- ---------------------- rollback_init ------------------------
918 -- Description: All entries in the ben_dm_phases and the
919 -- ben_dm_phase_items table are deleted and the status of the
920 -- migration is reset to NS.
921 --
922 --
923 -- Input Parameters
924 -- p_phase_item_id - current phase item id
925 --
926 -- Output Parameters
927 -- <none>
928 --
929 --
930 -- ------------------------------------------------------------------------
931
932 --
933 PROCEDURE rollback_init (p_migration_id IN NUMBER) IS
934 --
935
936 l_phase_id NUMBER;
937
938 CURSOR csr_c1 IS
939 SELECT ph.phase_id
940 FROM ben_dm_phases ph
941 WHERE (ph.migration_id = p_migration_id)
942 AND (ph.phase_name = 'I')
943 AND (ph.status IN ('S', 'E'));
944
945 --
946 BEGIN
947 --
948
949 message('ROUT','entry:ben_dm_utility.rollback_init', 5);
950 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
951
952 -- find all init phases for this migration that are started or in error
953 OPEN csr_c1;
954 LOOP
955 FETCH csr_c1 INTO l_phase_id;
956 EXIT WHEN csr_c1%NOTFOUND;
957 -- delete all entries in hr_dm_phase_items
958 DELETE FROM ben_dm_phase_items
959 WHERE phase_id = l_phase_id;
960
961 -- delete information from hr_dm_phases
962 DELETE FROM ben_dm_phases
963 WHERE phase_id = l_phase_id;
964
965 END LOOP;
966 CLOSE csr_c1;
967
968 -- update status to started
969 update_migrations('S', p_migration_id);
970
971
972 message('INFO','Rollback - init', 15);
973 message('SUMM','Rollback - init', 20);
974 message('ROUT','exit:ben_dm_utility.rollback_init', 25);
975 message('PARA','(none)', 30);
976
977 -- error handling
978 EXCEPTION
979 WHEN OTHERS THEN
980 error(SQLCODE,'ben_dm_utility.rollback_init','(none)','R');
981 RAISE;
982 --
983 END rollback_init;
984 --
985
986 --
987 -- ---------------------------- rollback_generator -------------------------
988 -- Description: All entries in the ben_dm_phase_items table for the generator
989 -- phase are reset to NS.
990 --
991 --
992 -- Input Parameters
993 -- p_migration_id - current migration id
994 --
995 -- Output Parameters
996 -- <none>
997 --
998 --
999 -- ------------------------------------------------------------------------
1000
1001
1002 --
1003 PROCEDURE rollback_generator (p_migration_id IN NUMBER) IS
1004 --
1005
1006 CURSOR csr_c1 IS
1007 SELECT phi.phase_item_id
1008 FROM ben_dm_phase_items phi,
1009 ben_dm_phases ph
1010 WHERE (ph.migration_id = p_migration_id)
1011 AND (ph.phase_name = 'G')
1012 AND (phi.phase_id = ph.phase_id)
1013 AND (phi.status IN ('S', 'E'));
1014
1015 l_phase_item_id NUMBER;
1016
1017 --
1018 BEGIN
1019 --
1020
1021 message('ROUT','entry:ben_dm_utility.rollback_generator', 5);
1022 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1023
1024 -- find all generator phases for this migration that are started or in error
1025 OPEN csr_c1;
1026 LOOP
1027 FETCH csr_c1 INTO l_phase_item_id;
1028 EXIT WHEN csr_c1%NOTFOUND;
1029 -- update status to not started
1030 update_phase_items('NS', l_phase_item_id);
1031 END LOOP;
1032 CLOSE csr_c1;
1033
1034 message('INFO','Rollback - generator', 15);
1035 message('SUMM','Rollback - generator', 20);
1036 message('ROUT','exit:ben_dm_utility.rollback_generator', 25);
1037 message('PARA','(none)', 30);
1038
1039 -- error handling
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 error(SQLCODE,'ben_dm_utility.rollback_generator','(none)','R');
1043 RAISE;
1044 --
1045 END rollback_generator;
1046 --
1047
1048
1049 -- ------------------------- get_table_id ------------------------
1050 -- Description: Get table_id from ben_dm_tables for a given table_name
1051 --
1052 --
1053 -- Input Parameters
1054 -- p_table_name - table_name
1055 --
1056 --
1057 -- Output Parameters
1058 -- <none>
1059 --
1060 --
1061 -- Return Value
1062 -- Table id
1063 --
1064 --
1065 -- ------------------------------------------------------------------------
1066
1067 --
1068 -- ---------------------------- rollback_upload -------------------------
1069 -- Description: All entries in the ben_dm_phase_items table for the upload
1070 -- phase are reset to NS.
1071 --
1072 --
1073 -- Input Parameters
1077 -- <none>
1074 -- p_migration_id - current migration id
1075 --
1076 -- Output Parameters
1078 --
1079 --
1080 -- ------------------------------------------------------------------------
1081
1082
1083 --
1084 PROCEDURE rollback_upload (p_migration_id IN NUMBER) IS
1085 --
1086
1087 CURSOR csr_c1 IS
1088 SELECT phi.phase_item_id
1089 FROM ben_dm_phase_items phi,
1090 ben_dm_phases ph
1091 WHERE (ph.migration_id = p_migration_id)
1092 AND (ph.phase_name = 'UP')
1093 AND (phi.phase_id = ph.phase_id)
1094 AND (phi.status IN ('S', 'E'));
1095
1096 l_phase_item_id NUMBER;
1097
1098 --
1099 BEGIN
1100 --
1101
1102 message('ROUT','entry:ben_dm_utility.rollback_upload', 5);
1103 message('PARA','(p_migration_id - ' || p_migration_id || ')', 10);
1104
1105 -- find all upload phases for this migration that are started or in error
1106 OPEN csr_c1;
1107 LOOP
1108 FETCH csr_c1 INTO l_phase_item_id;
1109 EXIT WHEN csr_c1%NOTFOUND;
1110 -- update status to not started
1111 update_phase_items('NS', l_phase_item_id);
1112 END LOOP;
1113 CLOSE csr_c1;
1114
1115 message('INFO','Rollback - upload', 15);
1116 message('SUMM','Rollback - upload', 20);
1117 message('ROUT','exit:ben_dm_utility.rollback_upload', 25);
1118 message('PARA','(none)', 30);
1119
1120 -- error handling
1121 EXCEPTION
1122 WHEN OTHERS THEN
1123 error(SQLCODE,'ben_dm_utility.rollback_upload','(none)','R');
1124 RAISE;
1125 --
1126 END rollback_upload;
1127 --
1128
1129
1130 --
1131 FUNCTION get_table_id(p_table_name IN VARCHAR2) RETURN NUMBER IS
1132 --
1133
1134 l_table_id NUMBER;
1135
1136 CURSOR csr_tables IS
1137 SELECT table_id
1138 FROM ben_dm_tables
1139 WHERE table_name = p_table_name;
1140
1141 --
1142 BEGIN
1143 --
1144
1145 -- message('ROUT','entry:ben_dm_utility.number_of_threads', 5);
1146 -- message('PARA','(p_business_group_id - ' || p_business_group_id || ')', 10);
1147 OPEN csr_tables;
1148 FETCH csr_tables INTO l_table_id;
1149 CLOSE csr_tables;
1150
1151 -- set default value if no entry exists for the current business group
1152 IF (l_table_id IS NULL) THEN
1153 -- Raise an exception.
1154 null;
1155 END IF;
1156
1157 -- message('INFO','Found Table ID', 15);
1158 -- message('SUMM','Found Table ID', 20);
1159 -- message('ROUT','exit:ben_dm_utility.get_table_id', 25);
1160 -- message('PARA','(l_table_id - ' || ')', 30);
1161
1162 RETURN(l_table_id);
1163
1164 -- error handling
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 -- error(SQLCODE,'ben_dm_utility.get_table_id','(none)','R');
1168 RAISE;
1169
1170 --
1171 END get_table_id;
1172 --
1173
1174 -- ------------------------- seed_column_mapping --------------------
1175 -- Description: Seeds Mapping into BEN_DM_COLUMN_MAPPINGS for given
1176 -- table
1177 --
1178 --
1179 -- Input Parameters
1180 -- p_table_name - Table Name
1181 --
1182 -- Output Parameters
1183 -- <none>
1184 --
1185 -- ------------------------------------------------------------------------
1186
1187 --
1188 PROCEDURE seed_column_mapping (p_table_name IN VARCHAR2) IS
1189 --
1190 l_er_column_name VARCHAR2(30);
1191 l_column_name VARCHAR2(30);
1192 l_table_name VARCHAR2(30);
1193 l_table_id NUMBER;
1194
1195 -- Get columns for seeding
1196 CURSOR csr_get_columns IS
1197 SELECT column_name, data_type
1198 FROM sys.all_tab_columns cols1
1199 WHERE data_type in ('NUMBER','VARCHAR2','DATE')
1200 AND table_name = p_table_name
1201 AND column_name not in ('REQUEST_ID','PROGRAM_APPLICATION_ID','PROGRAM_ID','PROGRAM_UPDATE_DATE')
1202 AND data_length < 2001
1203 AND not exists (SELECT null
1204 FROM BEN_DM_COLUMN_MAPPINGS d1
1205 WHERE d1.table_id in (select table_id from ben_dm_tables where table_name = cols1.table_name)
1206 AND column_name = cols1.column_name);
1207
1208
1209 -- Find next available Entity Result column
1210 CURSOR csr_get_er_columns (p_data_type VARCHAR2,
1211 p_table_id NUMBER,
1212 p_column_name VARCHAR2) IS
1213 SELECT column_name
1214 FROM sys.all_tab_columns
1215 WHERE data_type = p_data_type
1216 AND table_name = 'BEN_DM_ENTITY_RESULTS'
1217 AND column_name not in ('ENTITY_RESULT_ID','MIGRATION_ID','TABLE_NAME','GROUP_ORDER')
1218 AND column_name not in (SELECT entity_result_column_name
1219 FROM ben_dm_column_mappings
1220 WHERE table_id = p_table_id)
1221 ORDER BY column_id asc;
1222
1223
1224 --
1225 BEGIN
1226 --
1227
1228 -- message('ROUT','entry:ben_dm_utility.seed_column_mapping', 5);
1229 -- message('PARA','(p_table_name - ' || p_table_name ||'), 10);
1230 -- get_table_id
1231
1232 l_table_id := get_table_id(p_table_name);
1233
1234 -- Get Column Information for seeding into ben_dm_mappings if required
1235
1236
1237 FOR column_list IN csr_get_columns LOOP
1238
1239 OPEN csr_get_er_columns(column_list.data_type, l_table_id, column_list.column_name);
1240 FETCH csr_get_er_columns INTO l_er_column_name;
1241 CLOSE csr_get_er_columns;
1242
1243 INSERT into ben_dm_column_mappings
1244 (column_mapping_id
1245 ,table_id
1246 ,column_name
1247 ,entity_result_column_name
1248 ,last_update_date
1249 ,last_updated_by
1250 ,last_update_login
1251 ,created_by
1252 ,creation_date)
1256 ,column_list.column_name
1253 VALUES
1254 (ben_dm_column_mappings_s.nextval
1255 ,l_table_id
1257 ,l_er_column_name
1258 ,sysdate
1259 ,1
1260 ,1
1261 ,1
1262 ,sysdate);
1263
1264 END LOOP;
1265
1266 -- message('INFO','seed_column_maping', 15);
1267 -- message('SUMM','seed_column_maping', 20);
1268 -- message('ROUT','exit:ben_dm_utility.seed_column_maping', 25);
1269 -- message('PARA','(p_table_name - ' || p_table_name ||'), 10);
1270
1271 -- error handling
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 -- error(SQLCODE,'ben_dm_utility.seed_column_maping','(none)','R');
1275 RAISE;
1276 --
1277 END seed_column_mapping;
1278 --
1279 -- ------------------------- seed_table_order --------------------
1280 -- Description: Seeds table order into BEN_DM_TABLE_ORDER for given
1281 -- table
1282 --
1283 --
1284 -- Input Parameters
1285 -- p_table_name - Table Name
1286 -- p_order_no - Order Number
1287 --
1288 -- Output Parameters
1289 -- <none>
1290 --
1291 -- ------------------------------------------------------------------------
1292
1293 --
1294 PROCEDURE seed_table_order (p_table_name IN VARCHAR2, p_order_no IN NUMBER) IS
1295 --
1296 l_table_id NUMBER;
1297
1298 --
1299 BEGIN
1300 --
1301
1302 -- message('ROUT','entry:ben_dm_utility.seed_table_order', 5);
1303 -- message('PARA','(p_table_name - ' || p_table_name ||'), 10);
1304 -- get_table_id
1305
1306 l_table_id := get_table_id(p_table_name);
1307
1308 -- Add some check to make sure there is no duplicate table entries
1309 -- together with order no.
1310
1311 INSERT into ben_dm_table_order
1312 (table_order_id
1313 ,table_id
1314 ,table_order
1315 ,last_update_date
1316 ,last_updated_by
1317 ,last_update_login
1318 ,created_by
1319 ,creation_date)
1320 VALUES
1321 (ben_dm_table_order_s.nextval
1322 ,l_table_id
1323 ,p_order_no
1324 ,sysdate
1325 ,1
1326 ,1
1327 ,1
1328 ,sysdate);
1329
1330 -- message('INFO','seed_table_order', 15);
1331 -- message('SUMM','seed_table_order', 20);
1332 -- message('ROUT','exit:ben_dm_utility.seed_table_order', 25);
1333 -- message('PARA','(p_table_name - ' || p_table_name ||'), 10);
1334
1335 -- error handling
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 -- error(SQLCODE,'ben_dm_utility.seed_column_maping','(none)','R');
1339 RAISE;
1340 --
1341 END seed_table_order;
1342 --
1343
1344 -- ------------------------- ins_hir --------------------
1345 -- Description: Seeds hierarchy information into
1346 -- BEN_DM_HIERARCHY table.
1347 --
1348 --
1349 -- Input Parameters
1350 -- p_table_name - Table Name
1351 -- p_parent_table_name - Parent Table
1352 -- p_column_name - Column Name
1353 -- p_parent_column_name - Parent Column Name
1354 -- p_parent_id_column_name - Surrogate ID Column Name
1355 --
1356 -- Output Parameters
1357 -- <none>
1358 --
1359 -- ------------------------------------------------------------------------
1360 -- insert into hierarchy table
1361 PROCEDURE ins_hir (p_table_name varchar2,
1362 p_parent_table_name varchar2 default null,
1363 p_column_name varchar2 default null,
1364 p_parent_column_name varchar2 default null,
1365 p_parent_id_column_name varchar2 default null,
1366 p_hierarchy_type varchar2 default 'PC') is
1367
1368 l_table_id ben_dm_hierarchies.table_id%type;
1369 l_column_name ben_dm_hierarchies.column_name%type;
1370 l_parent_table_name ben_dm_hierarchies.parent_table_name%type;
1371 l_parent_column_name ben_dm_hierarchies.parent_column_name%type;
1372 l_parent_id_column_name ben_dm_hierarchies.parent_id_column_name%type;
1373 l_hierarchy_type ben_dm_hierarchies.hierarchy_type%type;
1374
1375 BEGIN
1376
1377 l_table_id := get_table_id(p_table_name );
1378 l_column_name := p_column_name;
1379 l_parent_table_name := p_parent_table_name;
1380 l_parent_column_name := p_parent_column_name;
1381 l_hierarchy_type := p_hierarchy_type;
1382
1383 -- for table hierarchy if the joining column name is same in parent and
1384 -- child table then copy the child column name.
1385 if l_parent_column_name is null and l_hierarchy_type = 'PC' then
1386 l_parent_column_name := p_column_name;
1387 end if;
1388
1389 l_parent_id_column_name := p_parent_id_column_name;
1390
1391 -- insert into hr_dm_hierarchy table.
1392 insert into ben_dm_hierarchies
1393 ( hierarchy_id
1394 ,hierarchy_type
1395 ,table_id
1396 ,column_name
1397 ,parent_table_name
1398 ,parent_column_name
1399 ,parent_id_column_name
1400 ,last_update_date
1401 ,last_updated_by
1402 ,last_update_login
1403 ,created_by
1404 ,creation_date )
1405 select ben_dm_hierarchies_s.nextval
1406 ,l_hierarchy_type
1407 ,l_table_id
1408 ,l_column_name
1409 ,l_parent_table_name
1410 ,l_parent_column_name
1411 ,l_parent_id_column_name
1412 ,sysdate
1413 ,1
1414 ,1
1415 ,1
1416 ,sysdate
1417 from dual
1418 where not exists (select 'x'
1419 from ben_dm_hierarchies hir
1420 where hir.hierarchy_type = l_hierarchy_type
1421 and hir.table_id = l_table_id
1422 and nvl(hir.column_name,'X') = nvl(l_column_name,
1423 'X')
1424 and nvl(hir.parent_table_name,-99) = nvl(l_parent_table_name,
1425 -99)
1426 and nvl(hir.parent_column_name,'X') = nvl(l_parent_column_name,
1427 'X')
1428 and nvl(hir.parent_id_column_name,'X') = nvl(l_parent_id_column_name,
1429 'X')
1430 );
1431 if sql%rowcount = 0 then
1432 hr_utility.trace('''' || l_hierarchy_type || ''' - ' || p_table_name ||
1433 ' - ' || l_column_name || ' - ' || p_column_name ||
1434 ' (' || p_parent_table_name || ' - ' || p_parent_column_name ||
1435 ') ( Data not seeded as it already exists)');
1436 end if;
1437 exception
1438
1439 when others then
1440 hr_utility.trace(sqlerrm(sqlcode) || '''' || l_hierarchy_type
1441 || ''' - ' || p_table_name );
1442 END ins_hir;
1443
1444
1445 end ben_dm_utility;