The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT thread_cnt_num
FROM ben_batch_parameter
WHERE (business_group_id = p_business_group_id)
AND (batch_exe_cd = 'HRDM');
SELECT chunk_size
FROM ben_batch_parameter
WHERE (business_group_id = p_business_group_id)
AND (batch_exe_cd = 'HRDM');
SELECT status
FROM hr_dm_phases
WHERE ((migration_id = p_migration_id)
AND (phase_name = p_phase));
SELECT phase_id
FROM hr_dm_phases
WHERE ((migration_id = p_migration_id)
AND (phase_name = p_phase));
UPDATE hr_dm_migrations
SET migration_process = l_text
WHERE migration_id = p_migration_id;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'HR_DM_DEBUG_PIPE';
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'HR_DM_DEBUG_LOG';
rollback_delete(p_migration_id);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'R')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
DELETE FROM hr_dm_migration_ranges
WHERE phase_item_id = l_phase_item_id;
update_phase_items('NS', l_phase_item_id);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'DA')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
update_phase_items('NS', l_phase_item_id);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'UA')
AND (phi.phase_id = ph.phase_id);
update_phase_items('NS', l_phase_item_id);
l_call_delete VARCHAR2(200);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'DP')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
SELECT tbl.table_name,
tbl.short_name,
mr.starting_process_sequence,
mr.ending_process_sequence,
mr.range_id
FROM hr_dm_migration_ranges mr,
hr_dm_tables tbl,
hr_dm_phase_items pi
WHERE (pi.phase_id = l_range_phase_id)
AND (pi.phase_item_id = mr.phase_item_id)
AND (pi.table_name = tbl.table_name)
AND (mr.status IN ('S', 'E'));
SELECT pi.batch_id
FROM hr_dm_migration_ranges mr,
hr_dm_tables tbl,
hr_dm_phase_items pi,
hr_dm_table_groupings tgp
WHERE (pi.phase_id = l_phase_id)
AND (pi.group_id = tgp.group_id)
AND (tbl.table_name = l_table_name)
AND (tbl.table_id = tgp.table_id);
l_call_delete := 'begin hrdmd_' || l_short_name || '.delete_datapump'
|| '(p_batch_id => ' || l_batch_id
|| ', p_start_id => ' || l_starting_process_sequence
|| ', p_end_id => ' || l_ending_process_sequence
|| ', p_chunk_size => 10'
|| '); end;';
hr_dm_library.run_sql(l_call_delete);
update_migration_ranges('NS', l_range_id);
update_phase_items('NS',l_phase_item_id);
SELECT ph.phase_id
FROM hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'I')
AND (ph.status IN ('S', 'E'));
DELETE FROM hr_dm_phase_items
WHERE phase_id = l_phase_id;
DELETE FROM hr_dm_phases
WHERE phase_id = l_phase_id;
update_migrations('S', p_migration_id);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'G')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
update_phase_items('NS', l_phase_item_id);
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'C')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
update_phase_items('NS', l_phase_item_id);
PROCEDURE rollback_delete (p_migration_id IN NUMBER) IS
--
l_phase_item_id NUMBER;
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'D')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
SELECT mr.range_id
FROM hr_dm_migration_ranges mr,
hr_dm_phase_items pi
WHERE (pi.phase_id = l_range_phase_id)
AND (mr.phase_item_id = pi.phase_item_id)
AND (mr.status IN ('S', 'E'));
message('ROUT','entry:hr_dm_utility.rollback_delete', 5);
update_phase_items('NS', l_phase_item_id);
update_migration_ranges('NS', l_range_id);
message('INFO','Rollback - delete', 15);
message('SUMM','Rollback - delete', 20);
message('ROUT','exit:hr_dm_utility.rollback_delete', 25);
error(SQLCODE,'hr_dm_utility.rollback_delete','(none)','R');
END rollback_delete;
SELECT phi.phase_item_id
FROM hr_dm_phase_items phi,
hr_dm_phases ph
WHERE (ph.migration_id = p_migration_id)
AND (ph.phase_name = 'UP')
AND (phi.phase_id = ph.phase_id)
AND (phi.status IN ('S', 'E'));
update_phase_items('NS', l_phase_item_id);
PROCEDURE update_migrations (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is hr_dm_migrations
-- parent of hr_dm_phases
-- child of (none)
l_parent_table_id NUMBER(9);
SELECT status
FROM hr_dm_phases
WHERE ((migration_id = p_id)
AND (status <> 'C'));
message('ROUT','entry:hr_dm_utility.update_migrations', 5);
UPDATE hr_dm_migrations
SET status = p_new_status,
migration_start_date = NVL(l_start_date, migration_start_date)
WHERE migration_id = p_id;
UPDATE hr_dm_migrations
SET status = p_new_status,
migration_end_date = sysdate
WHERE migration_id = p_id;
message('INFO','Update status - update_migrations', 15);
message('SUMM','Update status - update_migrations', 20);
message('ROUT','exit:hr_dm_utility.update_migrations', 25);
error(SQLCODE,'hr_dm_utility.update_migrations','(none)','R');
END update_migrations;
PROCEDURE update_migration_ranges (p_new_status IN VARCHAR2,
p_id IN NUMBER) IS
--
-- table is hr_dm_migration_ranges
-- parent of (none)
-- child of hr_dm_phase_items
l_parent_table_id NUMBER(9);
SELECT ph.migration_id
FROM hr_dm_migration_ranges mr,
hr_dm_phase_items pi,
hr_dm_phases ph
WHERE p_id = mr.range_id
AND mr.phase_item_id = pi.phase_item_id
AND pi.phase_id = ph.phase_id;
SELECT pi_par.phase_item_id
FROM hr_dm_phase_items pi_par,
hr_dm_tables tbl,
hr_dm_migration_ranges mr,
hr_dm_phase_items pi_rg,
hr_dm_table_groupings tgp
WHERE p_id = mr.range_id
AND mr.phase_item_id = pi_rg.phase_item_id
AND pi_rg.table_name = tbl.table_name
AND tbl.table_id = tgp.table_id
AND tgp.group_id = pi_par.group_id
AND pi_par.phase_id = l_phase_id;
SELECT mr.status
FROM hr_dm_migration_ranges mr,
hr_dm_phase_items rg_pi
WHERE (mr.phase_item_id = rg_pi.phase_item_id)
AND (rg_pi.group_id = (SELECT rg_pi.group_id
FROM hr_dm_phase_items rg_pi,
hr_dm_migration_ranges mr
WHERE p_id = mr.range_id
AND mr.phase_item_id = rg_pi.phase_item_id))
AND (mr.status <> 'C');
SELECT status
FROM hr_dm_phase_items
WHERE phase_item_id = l_parent_table_id;
message('ROUT','entry:hr_dm_utility.update_migration_ranges', 5);
UPDATE hr_dm_migration_ranges
SET status = p_new_status,
start_time = NVL(l_start_time, start_time),
end_time = NVL(l_end_time, end_time)
WHERE range_id = p_id;
update_phase_items('E',l_parent_table_id);
update_phase_items(p_new_status,l_parent_table_id);
update_phase_items('S',l_parent_table_id);
message('INFO','Update status - update_migration_ranges', 15);
message('SUMM','Update status - update_migration_ranges', 20);
message('ROUT','exit:hr_dm_utility.update_migration_ranges', 25);
error(SQLCODE,'hr_dm_utility.update_migration_ranges','(none)','R');
END update_migration_ranges;
PROCEDURE update_phase_items (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is hr_dm_phase_items
-- parent of n/a
-- child of hr_dm_phases
l_parent_table_id NUMBER(9);
SELECT phase_id
FROM hr_dm_phase_items
WHERE phase_item_id = p_id;
message('ROUT','entry:hr_dm_utility.update_phase_items', 5);
UPDATE hr_dm_phase_items
SET status = p_new_status,
start_time = NVL(l_start_time, start_time),
end_time = NVL(l_end_time, end_time)
WHERE phase_item_id = p_id;
update_phases(p_new_status,l_parent_table_id);
message('INFO','Update status - update_phase_items', 15);
message('SUMM','Update status - update_phase_items', 20);
message('ROUT','exit:hr_dm_utility.update_phase_items', 25);
error(SQLCODE,'hr_dm_utility.update_phase_items','(none)','R');
END update_phase_items;
PROCEDURE update_phases (p_new_status IN VARCHAR2, p_id IN NUMBER) IS
--
-- table is hr_dm_phases
-- parent of hr_dm_phase_items
-- child of hr_dm_migrations
l_parent_table_id NUMBER(9);
SELECT status
FROM hr_dm_phase_items
WHERE ((phase_id = p_id)
AND (status <> 'C'));
SELECT migration_id
FROM hr_dm_phases
WHERE phase_id = p_id;
message('ROUT','entry:hr_dm_utility.update_phases', 5);
UPDATE hr_dm_phases
SET status = l_new_status,
start_time = NVL(l_start_time, start_time)
WHERE phase_id = p_id;
UPDATE hr_dm_phases
SET status = l_new_status,
end_time = sysdate
WHERE phase_id = p_id;
update_migrations(l_new_status,l_parent_table_id);
message('INFO','Update status - update_phases', 15);
message('SUMM','Update status - update_phases', 20);
message('ROUT','exit:hr_dm_utility.update_phases', 25);
error(SQLCODE,'hr_dm_utility.update_phases','(none)','R');
END update_phases;