The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_phase_id NUMBER;
SELECT group_id
FROM hr_dm_application_groups
WHERE (migration_type = 'D')
AND (group_order IN (
SELECT MAX(apg.group_order)
FROM hr_dm_phase_items pi_dn,
hr_dm_tables tbl,
hr_dm_migration_ranges mr,
hr_dm_phase_items pi_rg,
hr_dm_table_groupings tgp,
hr_dm_application_groups apg
WHERE pi_rg.phase_id = l_range_phase_id
AND pi_rg.phase_item_id = mr.phase_item_id
AND pi_rg.table_name = tbl.table_name
AND pi_dn.phase_id = l_delete_phase_id
AND pi_dn.group_id = pi_rg.group_id
AND tbl.table_id = tgp.table_id
AND tgp.group_id = apg.group_id
AND apg.migration_type = 'D'
AND mr.status = 'NS')
);
hr_dm_utility.message('ROUT','entry:hr_dm_delete.set_active', 5);
l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
UPDATE hr_dm_migrations
SET active_group = l_active_group
WHERE migration_id = p_migration_id;
hr_dm_utility.message('ROUT','exit:hr_dm_delete.set_active', 25);
hr_dm_utility.error(SQLCODE,'hr_dm_delete.set_active',
'Error setting currently active group','D');
SELECT security_group_id
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
SELECT lookup_type,
view_application_id
FROM fnd_lookup_types
WHERE security_group_id = l_security_group_id;
SELECT DISTINCT lookup_code
FROM FND_LOOKUP_VALUES
WHERE security_group_id = l_security_group_id
AND lookup_type = l_lookup_type
AND view_application_id = l_view_application_id;
SELECT u.user_name username,
a.application_short_name resp_app,
r.responsibility_key resp_key,
s.security_group_key security_group
FROM fnd_user_resp_groups rg,
fnd_user u,
fnd_application a,
fnd_responsibility r,
fnd_security_groups s
WHERE rg.user_id = u.user_id
AND rg.responsibility_application_id = a.application_id
AND rg.responsibility_id = r.responsibility_id
AND rg.security_group_id = s.security_group_id
AND rg.security_group_id = l_security_group_id;
fnd_lookup_values_pkg.delete_row(
X_LOOKUP_TYPE => l_lookup_type,
X_SECURITY_GROUP_ID => l_security_group_id,
X_VIEW_APPLICATION_ID => l_view_application_id,
X_LOOKUP_CODE => l_lookup_code);
fnd_lookup_types_pkg.delete_row(
X_LOOKUP_TYPE => l_lookup_type,
X_SECURITY_GROUP_ID => l_security_group_id,
X_VIEW_APPLICATION_ID => l_view_application_id);
fnd_security_groups_pkg.delete_row(l_security_group_id);
hr_utility.trace(SQLERRM || ' in hr_dm_delete.del_fnd_info');
l_delete_phase_id NUMBER;
SELECT business_group_id, migration_type
FROM hr_dm_migrations
WHERE migration_id = p_migration_id;
SELECT active_group
FROM hr_dm_migrations
WHERE migration_id = p_migration_id;
SELECT group_id
FROM hr_dm_application_groups
WHERE (migration_type = 'D')
AND (group_order IN (
SELECT MAX(apg.group_order)
FROM hr_dm_phase_items pi_dn,
hr_dm_tables tbl,
hr_dm_migration_ranges mr,
hr_dm_phase_items pi_rg,
hr_dm_table_groupings tgp,
hr_dm_application_groups apg
WHERE pi_rg.phase_id = l_range_phase_id
AND pi_rg.phase_item_id = mr.phase_item_id
AND pi_rg.table_name = tbl.table_name
AND pi_dn.phase_id = l_delete_phase_id
AND pi_dn.group_id = pi_rg.group_id
AND tbl.table_id = tgp.table_id
AND tgp.group_id = apg.group_id
AND apg.migration_type = 'D'
AND mr.status ='NS')
);
SELECT mr.range_id,
tbl.table_name,
tbl.short_name,
mr.starting_process_sequence,
mr.ending_process_sequence,
pi_del.phase_item_id
FROM hr_dm_phase_items pi_del,
hr_dm_tables tbl,
hr_dm_migration_ranges mr,
hr_dm_phase_items pi_rg,
hr_dm_table_groupings tgp
WHERE pi_rg.phase_id = l_range_phase_id
AND pi_rg.phase_item_id = mr.phase_item_id
AND mr.status = 'NS'
AND pi_rg.table_name = tbl.table_name
AND pi_del.phase_id = l_delete_phase_id
AND pi_del.group_id = pi_rg.group_id
AND tgp.table_id = tbl.table_id
AND tgp.group_id = l_active_group
AND ((MOD(mr.range_id, l_no_of_threads) + 1) = p_process_number);
hr_dm_utility.message('ROUT','entry:hr_dm_delete.main', 5);
l_delete_phase_id := hr_dm_utility.get_phase_id('D', p_migration_id);
hr_dm_utility.update_migration_ranges(p_new_status => 'S',
p_id => l_range_id);
l_string := 'begin hrdmd_' || l_short_name || '.delete_source( ' ||
l_business_group_id || ', ''' ||
l_starting_process_sequence || ''', ' ||
l_ending_process_sequence || ', ' ||
l_chunk_size || '); end;';
hr_dm_utility.update_migration_ranges(p_new_status => 'C',
p_id => l_range_id);
hr_dm_utility.message('INFO','delete - slave process', 15);
hr_dm_utility.message('SUMM','delete - slave process', 20);
hr_dm_utility.message('ROUT','exit:hr_dm_delete.main', 25);
hr_dm_utility.error(SQLCODE,'hr_dm_delete.main',l_fatal_error_message,'R');
hr_dm_utility.update_phase_items(p_new_status => 'E',
p_id => l_phase_item_id);
hr_dm_utility.error(SQLCODE,'hr_dm_delete.main','(none)','R');
PROCEDURE pre_delete_process(r_migration_data IN
hr_dm_utility.r_migration_rec) IS
--
l_location VARCHAR2(2000);
hr_dm_utility.message('ROUT','entry:hr_dm_delete.pre_delete_process', 5);
utl_file.put_line(l_filehandle, 'REM Data Migrator Delete Script');
SELECT to_char(sysdate,'HH:MI DD-MON-YYYY')
INTO l_sysdate
FROM dual;
utl_file.put_line(l_filehandle, 'hr_dm_delete.del_fnd_info(' ||
r_migration_data.business_group_id || ');');
utl_file.put_line(l_filehandle, 'delete from HR_ALL_ORGANIZATION_UNITS_TL');
utl_file.put_line(l_filehandle, ' select ORGANIZATION_ID');
utl_file.put_line(l_filehandle, 'delete from HR_LOCATIONS_ALL_TL');
utl_file.put_line(l_filehandle, ' select LOCATION_ID');
utl_file.put_line(l_filehandle, 'delete from HR_ORGANIZATION_INFORMATION ');
utl_file.put_line(l_filehandle, ' select ORGANIZATION_ID');
utl_file.put_line(l_filehandle, 'delete from HR_ALL_ORGANIZATION_UNITS');
utl_file.put_line(l_filehandle, 'delete from HR_LOCATIONS_ALL');
utl_file.put_line(l_filehandle, 'delete from BEN_BATCH_PARAMETER');
utl_file.put_line(l_filehandle, 'delete from BEN_EXT_CHG_EVT_LOG');
utl_file.put_line(l_filehandle, 'delete from hr_dm_migration_requests');
utl_file.put_line(l_filehandle, ' (select migration_id');
utl_file.put_line(l_filehandle, 'delete from hr_dm_migration_ranges');
utl_file.put_line(l_filehandle, ' (select phase_item_id');
utl_file.put_line(l_filehandle, ' (select phase_id');
utl_file.put_line(l_filehandle, ' (select migration_id');
utl_file.put_line(l_filehandle, 'delete from hr_dm_phase_items');
utl_file.put_line(l_filehandle, ' (select phase_item_id');
utl_file.put_line(l_filehandle, ' (select phase_id');
utl_file.put_line(l_filehandle, ' (select migration_id');
utl_file.put_line(l_filehandle, 'delete from hr_dm_phases');
utl_file.put_line(l_filehandle, ' (select phase_id');
utl_file.put_line(l_filehandle, ' (select migration_id');
utl_file.put_line(l_filehandle, 'delete from hr_dm_migrations');
utl_file.put_line(l_filehandle, ' (select migration_id');
hr_dm_utility.message('INFO','Delete - cleanup script', 15);
hr_dm_utility.message('SUMM','Delete - cleanup script', 20);
hr_dm_utility.message('ROUT','exit:hr_dm_delete.pre_delete_process', 25);
hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
l_fatal_error_message,'R');
hr_dm_utility.error(SQLCODE,'hr_dm_delete.pre_delete_process',
'(none)','R');
END pre_delete_process;