Oracle HRMS API 批量删除员工错误的分配信息的脚本
我们系统中，由于之前搞过分配数据，今天要导入分配资料，一检查发现有3000多人 有多条错误的分配信息。比如有一个员工有 4条分配记录，但是我们需要的只有第一条，所以就删除了那些多余的，错误的，没用的分配记录。
[color=red]下面的脚本，是删除分配的多条记录的脚本，谨慎使用。[/color]
-- Created on 2008-7-30 by Mark Gao
DECLARE
-- Local variables here
i INTEGER;
p_assgt_term_elements BOOLEAN;
p_cobra_coverage_elements BOOLEAN;
p_life_events_exists BOOLEAN;
p_cost_warning BOOLEAN;
p_spp_warning BOOLEAN;
p_element_entries_warning BOOLEAN;
p_element_salary_warning BOOLEAN;
p_org_now_no_manager_warning BOOLEAN;
p_delete_asg_budgets BOOLEAN;
p_loc_change_tax_issues BOOLEAN;
p_effective_start_date DATE;
p_effective_end_date DATE;
p_object_version_number NUMBER;
l_count NUMBER :=0 ;
CURSOR g_assignment_id IS
SELECT pa.assignment_id,
pa.assignment_number,
pa.effective_start_date,
pa.object_version_number
FROM per_all_assignments_f pa, t_ass t
WHERE 1 = 1
AND pa.business_group_id = 0
AND t.p_ass_number = pa.assignment_id
AND pa.effective_start_date =
(SELECT pe.start_date
FROM per_all_people_f pe
WHERE pe.person_id = pa.person_id
AND SYSDATE BETWEEN pe.effective_start_date AND
pe.effective_end_date)
--AND pa.assignment_id <> 581
--AND pa.Assignment_Id >2156
AND pa.Assignment_Id <> 66
ORDER BY pa.assignment_id;
BEGIN
-- Test statements here
FOR c1 IN g_assignment_id LOOP
l_count:=l_count+1;
hr_assignment_api.delete_assignment(p_validate => FALSE,
p_effective_date => c1.effective_start_date,
p_datetrack_mode => 'DELETE_NEXT_CHANGE',
p_assignment_id => c1.assignment_id,
p_object_version_number => c1.object_version_number,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_loc_change_tax_issues => p_loc_change_tax_issues,
p_delete_asg_budgets => p_delete_asg_budgets,
p_org_now_no_manager_warning => p_org_now_no_manager_warning,
p_element_salary_warning => p_element_salary_warning,
p_element_entries_warning => p_element_entries_warning,
p_spp_warning => p_spp_warning,
p_cost_warning => p_cost_warning,
p_life_events_exists => p_life_events_exists,
p_cobra_coverage_elements => p_cobra_coverage_elements,
p_assgt_term_elements => p_assgt_term_elements);
dbms_output.put_line(l_count||':'||c1.assignment_id || ':' || c1.assignment_number);
END LOOP;
END;
页:
[1]