批量更新员工入职日期 Oracle HRMS API 晒太阳
发现系统历史数据中，有好几千人的入职日期都错了，是由于一期项目的时候，没有上HR，所以搞进入的员工的入职日期都是错误的，所以写了个 tst脚本。哗啦啦一下下搞定了，4000多人大概6分钟。
-- Created on 2008-7-29 by Mark Gao
DECLARE
-- Local variables here
i INTEGER;
o_warn_ee VARCHAR2(200);
o_err_code NUMBER;
o_err_msg NUMBER;
i_person_id NUMBER;
i_old_start_date DATE;
i_new_start_date DATE;
CURSOR p_id IS
SELECT pe.person_id,
pe.start_date old_date,
to_date(pu.start_date, 'yyyy-mm-dd') new_date,
pe.employee_number
FROM per_all_people_f pe, xxsg_hr_people_update_temp pu,per_all_assignments_f pa
WHERE pe.employee_number = TRIM(pu.employee_number)
And pe.person_id = pa.person_id
And pe.start_date Between pa.effective_start_date And pa.effective_end_date
And pa.supervisor_id Is Null
AND pe.start_date BETWEEN pe.effective_start_date AND
pe.effective_end_date
AND pe.business_group_id = 0;
BEGIN
-- Test statements here
FOR c1 IN p_id LOOP
hr_change_start_date_api.update_start_date(p_validate => FALSE,
p_person_id => c1.person_id,
p_old_start_date => c1.old_date,
p_new_start_date => c1.new_date,
p_update_type => 'E',
p_applicant_number => NULL,
p_warn_ee => o_warn_ee);
END LOOP;
/*EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(substr(SQLERRM, 1, 300));*/
END;
页:
[1]