60: --
61: BEGIN
62: --
63:
64: hr_dm_utility.message('ROUT','entry:hr_dm_delete.set_active', 5);
65: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id || ')',
66: 10);
67:
68:
61: BEGIN
62: --
63:
64: hr_dm_utility.message('ROUT','entry:hr_dm_delete.set_active', 5);
65: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id || ')',
66: 10);
67:
68:
69: -- get the delete phase_id
66: 10);
67:
68:
69: -- get the delete phase_id
70: l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
71:
72: -- get the range phase_id
73: l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
74:
69: -- get the delete phase_id
70: l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
71:
72: -- get the range phase_id
73: l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
74:
75:
76:
77: -- get group id of next (or first) group to be processed
87: COMMIT;
88:
89:
90:
91: hr_dm_utility.message('INFO','Currently Active group set', 15);
92: hr_dm_utility.message('SUMM','Currently Active group set', 20);
93: hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
94: hr_dm_utility.message('PARA','(none)', 30);
95:
88:
89:
90:
91: hr_dm_utility.message('INFO','Currently Active group set', 15);
92: hr_dm_utility.message('SUMM','Currently Active group set', 20);
93: hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
94: hr_dm_utility.message('PARA','(none)', 30);
95:
96: -- error handling
89:
90:
91: hr_dm_utility.message('INFO','Currently Active group set', 15);
92: hr_dm_utility.message('SUMM','Currently Active group set', 20);
93: hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
94: hr_dm_utility.message('PARA','(none)', 30);
95:
96: -- error handling
97: EXCEPTION
90:
91: hr_dm_utility.message('INFO','Currently Active group set', 15);
92: hr_dm_utility.message('SUMM','Currently Active group set', 20);
93: hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
94: hr_dm_utility.message('PARA','(none)', 30);
95:
96: -- error handling
97: EXCEPTION
98: WHEN OTHERS THEN
95:
96: -- error handling
97: EXCEPTION
98: WHEN OTHERS THEN
99: hr_dm_utility.error(SQLCODE,'hr_dm_delete.set_active',
100: 'Error setting currently active group','D');
101: RAISE;
102:
103: --
401: --
402:
403: -- initialize messaging (only for concurrent processing)
404: IF (p_concurrent_process = 'Y') THEN
405: hr_dm_utility.message_init;
406: END IF;
407:
408: hr_dm_utility.message('ROUT','entry:hr_dm_delete.main', 5);
409: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
404: IF (p_concurrent_process = 'Y') THEN
405: hr_dm_utility.message_init;
406: END IF;
407:
408: hr_dm_utility.message('ROUT','entry:hr_dm_delete.main', 5);
409: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
410: ')(p_last_migration_date - ' ||
411: p_last_migration_date || ')', 10);
412:
405: hr_dm_utility.message_init;
406: END IF;
407:
408: hr_dm_utility.message('ROUT','entry:hr_dm_delete.main', 5);
409: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
410: ')(p_last_migration_date - ' ||
411: p_last_migration_date || ')', 10);
412:
413:
419: ,val => 'N'
420: );
421:
422: -- get the delete phase_id
423: l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
424:
425: -- get the range phase_id
426: l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
427:
422: -- get the delete phase_id
423: l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
424:
425: -- get the range phase_id
426: l_range_phase_id := hr_dm_utility.get_phase_id('R', p_migration_id);
427:
428:
429: -- get the business_group_id and migration_type
430: OPEN csr_migration_info;
434: END LOOP;
435: CLOSE csr_migration_info;
436:
437: -- get the number of threads to enable modulus locking
438: l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
439:
440: -- find the chunk size
441: l_chunk_size := hr_dm_utility.chunk_size(l_business_group_id);
442:
437: -- get the number of threads to enable modulus locking
438: l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
439:
440: -- find the chunk size
441: l_chunk_size := hr_dm_utility.chunk_size(l_business_group_id);
442:
443:
444: -- loop until either delete phase is in error or all delete phase items have
445: -- been processed
442:
443:
444: -- loop until either delete phase is in error or all delete phase items have
445: -- been processed
446: hr_dm_utility.message('INFO','loop start', 15);
447: LOOP
448:
449: -- get status of delete phase, is phase completed?
450: -- if null returned, then assume it is NS.
447: LOOP
448:
449: -- get status of delete phase, is phase completed?
450: -- if null returned, then assume it is NS.
451: l_current_phase_status := NVL(hr_dm_utility.get_phase_status('D',
452: p_migration_id), 'NS');
453:
454: -- if status is error, then raise an exception
455: IF (l_current_phase_status = 'E') THEN
460: -- get currently active group
461: OPEN csr_active_group;
462: FETCH csr_active_group INTO l_active_group;
463: CLOSE csr_active_group;
464: hr_dm_utility.message('INFO','active group is ' || l_active_group, 15);
465:
466: -- fetch a row to process
467: l_table_name := NULL;
468: OPEN csr_table_range;
472: l_phase_item_id;
473: CLOSE csr_table_range;
474:
475: IF l_table_name IS NOT NULL THEN
476: hr_dm_utility.message('INFO','deleting - ' || l_table_name, 11);
477: -- update status to started
478: hr_dm_utility.update_migration_ranges(p_new_status => 'S',
479: p_id => l_range_id);
480: --
474:
475: IF l_table_name IS NOT NULL THEN
476: hr_dm_utility.message('INFO','deleting - ' || l_table_name, 11);
477: -- update status to started
478: hr_dm_utility.update_migration_ranges(p_new_status => 'S',
479: p_id => l_range_id);
480: --
481: -- call delete function...
482: --
485: l_business_group_id || ', ''' ||
486: l_starting_process_sequence || ''', ' ||
487: l_ending_process_sequence || ', ' ||
488: l_chunk_size || '); end;';
489: hr_dm_utility.message('INFO','using - ' || l_string, 12);
490: EXECUTE IMMEDIATE l_string;
491:
492: -- update status to completed
493: hr_dm_utility.update_migration_ranges(p_new_status => 'C',
489: hr_dm_utility.message('INFO','using - ' || l_string, 12);
490: EXECUTE IMMEDIATE l_string;
491:
492: -- update status to completed
493: hr_dm_utility.update_migration_ranges(p_new_status => 'C',
494: p_id => l_range_id);
495: COMMIT;
496:
497: ELSE
498: -- no rows left to process in this group
499: -- check if all tables in this group have either been completed or errored
500: -- ie all slaves have completed work on this group
501: LOOP
502: hr_dm_utility.message('INFO','seeing if work on this group to do.', 15);
503:
504: OPEN csr_group_work;
505: FETCH csr_group_work INTO l_group_work;
506: IF csr_group_work%NOTFOUND THEN
508: END IF;
509: CLOSE csr_group_work;
510:
511: -- set new active group
512: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
513: hr_dm_utility.message('INFO','l_active_group - ' || l_active_group, 15);
514: IF l_group_work <> l_active_group THEN
515: hr_dm_utility.message('INFO','setting new active group', 15);
516: set_active(p_migration_id);
509: CLOSE csr_group_work;
510:
511: -- set new active group
512: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
513: hr_dm_utility.message('INFO','l_active_group - ' || l_active_group, 15);
514: IF l_group_work <> l_active_group THEN
515: hr_dm_utility.message('INFO','setting new active group', 15);
516: set_active(p_migration_id);
517: COMMIT;
511: -- set new active group
512: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
513: hr_dm_utility.message('INFO','l_active_group - ' || l_active_group, 15);
514: IF l_group_work <> l_active_group THEN
515: hr_dm_utility.message('INFO','setting new active group', 15);
516: set_active(p_migration_id);
517: COMMIT;
518: ELSE
519: hr_dm_utility.message('INFO','Waiting for other slaves to finish this' ||
515: hr_dm_utility.message('INFO','setting new active group', 15);
516: set_active(p_migration_id);
517: COMMIT;
518: ELSE
519: hr_dm_utility.message('INFO','Waiting for other slaves to finish this' ||
520: ' group', 13);
521: -- sleep for 5 seconds to allow other slaves to finish
522: -- read from a non-existant pipe, using time out feature to
523: -- give delay time
524: l_dummy := dbms_pipe.receive_message('temporary_unused_hrdm_pipe', 5);
525: END IF;
526:
527: EXIT WHEN NVL(l_group_work,0) <> l_active_group
528: OR NVL(hr_dm_utility.get_phase_status('D',p_migration_id), 'NS') = 'E'
529: OR l_group_work IS NULL;
530:
531: END LOOP;
532: END IF;
530:
531: END LOOP;
532: END IF;
533:
534: l_status := NVL(hr_dm_utility.get_phase_status('D', p_migration_id), 'NS');
535:
536: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
537: hr_dm_utility.message('INFO','l_status - ' || l_status, 15);
538: EXIT WHEN l_status IN ('C','E')
532: END IF;
533:
534: l_status := NVL(hr_dm_utility.get_phase_status('D', p_migration_id), 'NS');
535:
536: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
537: hr_dm_utility.message('INFO','l_status - ' || l_status, 15);
538: EXIT WHEN l_status IN ('C','E')
539: OR l_group_work IS NULL;
540:
533:
534: l_status := NVL(hr_dm_utility.get_phase_status('D', p_migration_id), 'NS');
535:
536: hr_dm_utility.message('INFO','l_group_work - ' || l_group_work, 15);
537: hr_dm_utility.message('INFO','l_status - ' || l_status, 15);
538: EXIT WHEN l_status IN ('C','E')
539: OR l_group_work IS NULL;
540:
541: COMMIT;
546: retcode := 0;
547: errbuf := 'No errors - examine logfiles for detailed reports.';
548:
549:
550: hr_dm_utility.message('INFO','delete - slave process', 15);
551: hr_dm_utility.message('SUMM','delete - slave process', 20);
552: hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
553: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
554: ')(errbuf - ' || errbuf || ')', 30);
547: errbuf := 'No errors - examine logfiles for detailed reports.';
548:
549:
550: hr_dm_utility.message('INFO','delete - slave process', 15);
551: hr_dm_utility.message('SUMM','delete - slave process', 20);
552: hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
553: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
554: ')(errbuf - ' || errbuf || ')', 30);
555:
548:
549:
550: hr_dm_utility.message('INFO','delete - slave process', 15);
551: hr_dm_utility.message('SUMM','delete - slave process', 20);
552: hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
553: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
554: ')(errbuf - ' || errbuf || ')', 30);
555:
556: -- error handling
549:
550: hr_dm_utility.message('INFO','delete - slave process', 15);
551: hr_dm_utility.message('SUMM','delete - slave process', 20);
552: hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
553: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
554: ')(errbuf - ' || errbuf || ')', 30);
555:
556: -- error handling
557: EXCEPTION
558: WHEN e_fatal_error THEN
559: retcode := 0;
560: errbuf := 'An error occurred during the migration - examine logfiles for '
561: || 'detailed reports.';
562: hr_dm_utility.error(SQLCODE,'hr_dm_delete.main',l_fatal_error_message,'R');
563: WHEN OTHERS THEN
564: retcode := 2;
565: errbuf := 'An error occurred during the migration - examine logfiles for '
566: || 'detailed reports.';
564: retcode := 2;
565: errbuf := 'An error occurred during the migration - examine logfiles for '
566: || 'detailed reports.';
567: -- update status to error
568: hr_dm_utility.update_phase_items(p_new_status => 'E',
569: p_id => l_phase_item_id);
570: hr_dm_utility.error(SQLCODE,'hr_dm_delete.main','(none)','R');
571:
572: --
566: || 'detailed reports.';
567: -- update status to error
568: hr_dm_utility.update_phase_items(p_new_status => 'E',
569: p_id => l_phase_item_id);
570: hr_dm_utility.error(SQLCODE,'hr_dm_delete.main','(none)','R');
571:
572: --
573: END main;
574: --
592: -- ------------------------------------------------------------------------
593:
594: --
595: PROCEDURE pre_delete_process(r_migration_data IN
596: hr_dm_utility.r_migration_rec) IS
597: --
598:
599: l_location VARCHAR2(2000);
600: l_aol_filename VARCHAR2(30);
606: --
607: BEGIN
608: --
609:
610: hr_dm_utility.message('ROUT','entry:hr_dm_delete.pre_delete_process', 5);
611: hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
612:
613:
614:
607: BEGIN
608: --
609:
610: hr_dm_utility.message('ROUT','entry:hr_dm_delete.pre_delete_process', 5);
611: hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
612:
613:
614:
615: -- find logfile directory and open file for delete script
614:
615: -- find logfile directory and open file for delete script
616: fnd_profile.get('UTL_FILE_LOG', l_location);
617: l_aol_filename := 'DM' || r_migration_data.migration_id || '.sql';
618: hr_dm_utility.message('INFO','l_location ' || l_location, 13);
619: hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
620:
621: IF l_location IS NULL THEN
622: l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
615: -- find logfile directory and open file for delete script
616: fnd_profile.get('UTL_FILE_LOG', l_location);
617: l_aol_filename := 'DM' || r_migration_data.migration_id || '.sql';
618: hr_dm_utility.message('INFO','l_location ' || l_location, 13);
619: hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
620:
621: IF l_location IS NULL THEN
622: l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
623: 'Directory has not been set. Set to a ' ||
772: utl_file.fclose(l_filehandle);
773:
774:
775:
776: hr_dm_utility.message('INFO','Delete - cleanup script', 15);
777: hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
778: hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
779: hr_dm_utility.message('PARA','(none)', 30);
780:
773:
774:
775:
776: hr_dm_utility.message('INFO','Delete - cleanup script', 15);
777: hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
778: hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
779: hr_dm_utility.message('PARA','(none)', 30);
780:
781: -- error handling
774:
775:
776: hr_dm_utility.message('INFO','Delete - cleanup script', 15);
777: hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
778: hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
779: hr_dm_utility.message('PARA','(none)', 30);
780:
781: -- error handling
782: EXCEPTION
775:
776: hr_dm_utility.message('INFO','Delete - cleanup script', 15);
777: hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
778: hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
779: hr_dm_utility.message('PARA','(none)', 30);
780:
781: -- error handling
782: EXCEPTION
783: WHEN e_fatal_error THEN
780:
781: -- error handling
782: EXCEPTION
783: WHEN e_fatal_error THEN
784: hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
785: l_fatal_error_message,'R');
786: RAISE;
787:
788: WHEN OTHERS THEN
785: l_fatal_error_message,'R');
786: RAISE;
787:
788: WHEN OTHERS THEN
789: hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
790: '(none)','R');
791: RAISE;
792:
793: --