95: --
96: BEGIN
97: --
98:
99: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.add_details', 5);
100: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
101: '(p_phase_id - ' || p_phase_id ||
102: '(p_phase_item_id - ' || p_phase_item_id ||
103: ')', 10);
96: BEGIN
97: --
98:
99: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.add_details', 5);
100: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
101: '(p_phase_id - ' || p_phase_id ||
102: '(p_phase_item_id - ' || p_phase_item_id ||
103: ')', 10);
104:
247: ELSIF (l_parameter9 = ':secgrp') THEN l_parameter9 := null;
248: ELSIF (l_parameter10 = ':secgrp') THEN l_parameter10 := null;
249: END IF;
250:
251: hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
248: ELSIF (l_parameter10 = ':secgrp') THEN l_parameter10 := null;
249: END IF;
250:
251: hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
249: END IF;
250:
251: hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
250:
251: hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
251: hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
252: hr_dm_utility.message('INFO','program ' || l_program, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
253: hr_dm_utility.message('INFO','sub_request TRUE', 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
254: hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
255: hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263: hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
256: hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263: hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
264:
257: hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263: hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
264:
265: -- get the destination database name
258: hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263: hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
264:
265: -- get the destination database name
266: SELECT DESTINATION_DATABASE_INSTANCE
259: hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260: hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261: hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262: hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263: hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
264:
265: -- get the destination database name
266: SELECT DESTINATION_DATABASE_INSTANCE
267: INTO l_destination_database
286:
287:
288: utl_file.put_line(p_file_handle, l_string);
289:
290: hr_dm_utility.message('INFO','Added UA entry', 115);
291: hr_dm_utility.message('SUMM','Added UA entry', 120);
292: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.add_details', 125);
293: hr_dm_utility.message('PARA','(none)', 130);
294:
287:
288: utl_file.put_line(p_file_handle, l_string);
289:
290: hr_dm_utility.message('INFO','Added UA entry', 115);
291: hr_dm_utility.message('SUMM','Added UA entry', 120);
292: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.add_details', 125);
293: hr_dm_utility.message('PARA','(none)', 130);
294:
295:
288: utl_file.put_line(p_file_handle, l_string);
289:
290: hr_dm_utility.message('INFO','Added UA entry', 115);
291: hr_dm_utility.message('SUMM','Added UA entry', 120);
292: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.add_details', 125);
293: hr_dm_utility.message('PARA','(none)', 130);
294:
295:
296: -- error handling
289:
290: hr_dm_utility.message('INFO','Added UA entry', 115);
291: hr_dm_utility.message('SUMM','Added UA entry', 120);
292: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.add_details', 125);
293: hr_dm_utility.message('PARA','(none)', 130);
294:
295:
296: -- error handling
297: EXCEPTION
295:
296: -- error handling
297: EXCEPTION
298: WHEN e_fatal_error THEN
299: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.add_details',
300: l_fatal_error_message, 'R');
301: hr_dm_master.report_error('UA', p_migration_id,
302: 'Error in hr_dm_aol_up.add_details', 'P');
303: RAISE;
301: hr_dm_master.report_error('UA', p_migration_id,
302: 'Error in hr_dm_aol_up.add_details', 'P');
303: RAISE;
304: WHEN OTHERS THEN
305: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.add_details','(none)','R');
306: hr_dm_master.report_error('UA', p_migration_id,
307: 'Untrapped error in hr_dm_aol_up.add_details',
308: 'P');
309: RAISE;
354: --
355: BEGIN
356: --
357:
358: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.post_aol_process', 5);
359: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
360: ')', 10);
361:
362: SELECT business_group_id
355: BEGIN
356: --
357:
358: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.post_aol_process', 5);
359: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
360: ')', 10);
361:
362: SELECT business_group_id
363: INTO l_business_group_id
421:
422: COMMIT;
423:
424:
425: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.post_aol_process', 25);
426: hr_dm_utility.message('PARA','(none)', 10);
427: EXCEPTION
428: WHEN OTHERS THEN
429: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.post_aol_process',
422: COMMIT;
423:
424:
425: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.post_aol_process', 25);
426: hr_dm_utility.message('PARA','(none)', 10);
427: EXCEPTION
428: WHEN OTHERS THEN
429: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.post_aol_process',
430: '(none)','R');
425: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.post_aol_process', 25);
426: hr_dm_utility.message('PARA','(none)', 10);
427: EXCEPTION
428: WHEN OTHERS THEN
429: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.post_aol_process',
430: '(none)','R');
431: RAISE;
432:
433: --
555: --
556:
557: -- initialize messaging (only for concurrent processing)
558: IF (p_concurrent_process = 'Y') THEN
559: hr_dm_utility.message_init;
560: END IF;
561:
562: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.main', 5);
563: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
558: IF (p_concurrent_process = 'Y') THEN
559: hr_dm_utility.message_init;
560: END IF;
561:
562: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.main', 5);
563: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
564: ')(p_last_migration_date - ' ||
565: p_last_migration_date || ')', 10);
566:
559: hr_dm_utility.message_init;
560: END IF;
561:
562: hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.main', 5);
563: hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
564: ')(p_last_migration_date - ' ||
565: p_last_migration_date || ')', 10);
566:
567: -- get the current phase_id
564: ')(p_last_migration_date - ' ||
565: p_last_migration_date || ')', 10);
566:
567: -- get the current phase_id
568: l_phase_id := hr_dm_utility.get_phase_id('UA', p_migration_id);
569:
570:
571: -- get status of UA phase, is phase completed?
572: -- if null returned, then assume it is NS.
569:
570:
571: -- get status of UA phase, is phase completed?
572: -- if null returned, then assume it is NS.
573: l_current_phase_status := NVL(hr_dm_utility.get_phase_status('UA',
574: p_migration_id), 'NS');
575:
576:
577: -- if status is error, then raise an exception
584:
585: -- find logfile directory and open file for AOL Loader commands
586: fnd_profile.get('UTL_FILE_LOG', l_location);
587: l_aol_filename := 'DM' || p_migration_id || '.txt';
588: hr_dm_utility.message('INFO','l_location ' || l_location, 13);
589: hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
590:
591: IF l_location IS NULL THEN
592: l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
585: -- find logfile directory and open file for AOL Loader commands
586: fnd_profile.get('UTL_FILE_LOG', l_location);
587: l_aol_filename := 'DM' || p_migration_id || '.txt';
588: hr_dm_utility.message('INFO','l_location ' || l_location, 13);
589: hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
590:
591: IF l_location IS NULL THEN
592: l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
593: 'Directory has not been set. Set to a ' ||
595: 'write files to.';
596: RAISE e_fatal_error2;
597: END IF;
598:
599: hr_dm_utility.message('INFO','Opening file', 13);
600: l_filehandle := utl_file.fopen(l_location, l_aol_filename, 'w');
601: hr_dm_utility.message('INFO','File opened ', 13);
602:
603: -- get migration info
597: END IF;
598:
599: hr_dm_utility.message('INFO','Opening file', 13);
600: l_filehandle := utl_file.fopen(l_location, l_aol_filename, 'w');
601: hr_dm_utility.message('INFO','File opened ', 13);
602:
603: -- get migration info
604: OPEN csr_mig_info;
605: FETCH csr_mig_info INTO
648: FETCH csr_loader INTO l_loader_group;
649: EXIT WHEN csr_loader%NOTFOUND;
650:
651: -- send info on current table to logfile
652: hr_dm_utility.message('INFO','Processing - ' || l_loader_group, 13);
653:
654: -- show start of group
655: utl_file.put_line(l_filehandle, '# ');
656: utl_file.put_line(l_filehandle, '# <' || l_loader_group || '>');
666:
667: -- add entry for this phase item
668:
669: -- update status to started
670: hr_dm_utility.update_phase_items(p_new_status => 'S',
671: p_id => l_phase_item_id);
672:
673: -- call code for AOL loader
674: add_details(p_migration_id => p_migration_id,
676: p_phase_item_id => l_phase_item_id,
677: p_file_handle => l_filehandle);
678:
679: -- update status to completed
680: hr_dm_utility.update_phase_items(p_new_status => 'C',
681: p_id => l_phase_item_id);
682:
683: END LOOP;
684:
728: retcode := 0;
729: errbuf := 'No errors - examine logfiles for detailed reports.';
730:
731:
732: hr_dm_utility.message('INFO','UA - main controller', 15);
733: hr_dm_utility.message('SUMM','UA - main controller', 20);
734: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.main', 25);
735: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
736: ')(errbuf - ' || errbuf || ')', 30);
729: errbuf := 'No errors - examine logfiles for detailed reports.';
730:
731:
732: hr_dm_utility.message('INFO','UA - main controller', 15);
733: hr_dm_utility.message('SUMM','UA - main controller', 20);
734: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.main', 25);
735: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
736: ')(errbuf - ' || errbuf || ')', 30);
737:
730:
731:
732: hr_dm_utility.message('INFO','UA - main controller', 15);
733: hr_dm_utility.message('SUMM','UA - main controller', 20);
734: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.main', 25);
735: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
736: ')(errbuf - ' || errbuf || ')', 30);
737:
738: -- error handling
731:
732: hr_dm_utility.message('INFO','UA - main controller', 15);
733: hr_dm_utility.message('SUMM','UA - main controller', 20);
734: hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.main', 25);
735: hr_dm_utility.message('PARA','(retcode - ' || retcode ||
736: ')(errbuf - ' || errbuf || ')', 30);
737:
738: -- error handling
739: EXCEPTION
740: WHEN e_fatal_error THEN
741: retcode := 2;
742: errbuf := 'An error occurred during the migration - ' ||
743: 'examine logfiles for detailed reports.';
744: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
745: l_fatal_error_message,'UA');
746: hr_dm_utility.update_phase_items(p_new_status => 'E',
747: p_id => l_phase_item_id);
748: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
742: errbuf := 'An error occurred during the migration - ' ||
743: 'examine logfiles for detailed reports.';
744: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
745: l_fatal_error_message,'UA');
746: hr_dm_utility.update_phase_items(p_new_status => 'E',
747: p_id => l_phase_item_id);
748: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
749: WHEN e_fatal_error2 THEN
750: retcode := 0;
744: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
745: l_fatal_error_message,'UA');
746: hr_dm_utility.update_phase_items(p_new_status => 'E',
747: p_id => l_phase_item_id);
748: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
749: WHEN e_fatal_error2 THEN
750: retcode := 0;
751: errbuf := 'An error occurred during the migration - ' ||
752: 'examine logfiles for detailed reports.';
749: WHEN e_fatal_error2 THEN
750: retcode := 0;
751: errbuf := 'An error occurred during the migration - ' ||
752: 'examine logfiles for detailed reports.';
753: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
754: l_fatal_error_message,'UA');
755: hr_dm_utility.update_phases(p_new_status => 'E',
756: p_id => l_phase_id);
757: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
751: errbuf := 'An error occurred during the migration - ' ||
752: 'examine logfiles for detailed reports.';
753: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
754: l_fatal_error_message,'UA');
755: hr_dm_utility.update_phases(p_new_status => 'E',
756: p_id => l_phase_id);
757: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
758: WHEN OTHERS THEN
759: retcode := 2;
753: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
754: l_fatal_error_message,'UA');
755: hr_dm_utility.update_phases(p_new_status => 'E',
756: p_id => l_phase_id);
757: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
758: WHEN OTHERS THEN
759: retcode := 2;
760: errbuf := 'An error occurred during the migration - ' ||
761: 'examine logfiles for detailed reports.';
759: retcode := 2;
760: errbuf := 'An error occurred during the migration - ' ||
761: 'examine logfiles for detailed reports.';
762: -- update status to error
763: hr_dm_utility.update_phase_items(p_new_status => 'E',
764: p_id => l_phase_item_id);
765: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
766:
767:
761: 'examine logfiles for detailed reports.';
762: -- update status to error
763: hr_dm_utility.update_phase_items(p_new_status => 'E',
764: p_id => l_phase_item_id);
765: hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
766:
767:
768: --
769: END main;