/*CREATE TABLE hr_employee_imp_b
(employee_number varchar2(200),
last_name varchar2(200),
first_name varchar2(200),
sex varchar2(200),
national_identifier varchar2(200),
date_of_birth date,
person_id NUMBER);
ALTER TABLE hr_employee_imp_b ADD (date_of_birth date);
SELECT * FROM hr_employee_imp_b FOR UPDATE;
delete from hr_employee_imp_b*/
DECLARE
cursor cur_emp is
select trim(t.employee_number) Emp_Num,
trim(t.last_name) last_name,
trim(t.first_name) first_name,
decode(trim(t.SEX), '男', 'M', '女', 'F') SEX,
to_date(to_char(date_of_birth,'yyyy/mm/dd'),'yyyy/mm/dd') date_of_birth,
trim(t.national_identifier) national_identifier
from hr_employee_imp_b t
where not exists (select 1 from Per_All_People_f where Employee_Number=t.Employee_Number);
p_validate boolean := false;
p_hire_date date;
p_business_group_id number;
p_last_name varchar2(200);
p_sex varchar2(200);
p_person_type_id number;
p_per_comments varchar2(200);
p_date_employee_data_verified date;
p_date_of_birth date;
p_email_address varchar2(200);
p_employee_number varchar2(200);
p_expense_check_send_to_addres varchar2(200);
p_first_name varchar2(200);
p_known_as varchar2(200);
p_marital_status varchar2(200);
p_middle_names varchar2(200);
p_nationality varchar2(200);
p_national_identifier varchar2(200);
p_previous_last_name varchar2(200);
p_registered_disabled_flag varchar2(200);
p_title varchar2(200);
p_vendor_id number;
p_work_telephone varchar2(200);
p_attribute_category varchar2(200);
p_attribute1 varchar2(200);
p_attribute2 varchar2(200);
p_attribute3 varchar2(200);
p_attribute4 varchar2(200);
p_attribute5 varchar2(200);
p_attribute6 varchar2(200);
p_attribute7 varchar2(200);
p_attribute8 varchar2(200);
p_attribute9 varchar2(200);
p_attribute10 varchar2(200);
p_attribute11 varchar2(200);
p_attribute12 varchar2(200);
p_attribute13 varchar2(200);
p_attribute14 varchar2(200);
p_attribute15 varchar2(200);
p_attribute16 varchar2(200);
p_attribute17 varchar2(200);
p_attribute18 varchar2(200);
p_attribute19 varchar2(200);
p_attribute20 varchar2(200);
p_attribute21 varchar2(200);
p_attribute22 varchar2(200);
p_attribute23 varchar2(200);
p_attribute24 varchar2(200);
p_attribute25 varchar2(200);
p_attribute26 varchar2(200);
p_attribute27 varchar2(200);
p_attribute28 varchar2(200);
p_attribute29 varchar2(200);
p_attribute30 varchar2(200);
p_per_information_category varchar2(200);
p_per_information1 varchar2(200);
p_per_information2 varchar2(200);
p_per_information3 varchar2(200);
p_per_information4 varchar2(200);
p_per_information5 varchar2(200);
p_per_information6 varchar2(200);
p_per_information7 varchar2(200);
p_per_information8 varchar2(200);
p_per_information9 varchar2(200);
p_per_information10 varchar2(200);
p_per_information11 varchar2(200);
p_per_information12 varchar2(200);
p_per_information13 varchar2(200);
p_per_information14 varchar2(200);
p_per_information15 varchar2(200);
p_per_information16 varchar2(200);
p_per_information17 varchar2(200);
p_per_information18 varchar2(200);
p_per_information19 varchar2(200);
p_per_information20 varchar2(200);
p_per_information21 varchar2(200);
p_per_information22 varchar2(200);
p_per_information23 varchar2(200);
p_per_information24 varchar2(200);
p_per_information25 varchar2(200);
p_per_information26 varchar2(200);
p_per_information27 varchar2(200);
p_per_information28 varchar2(200);
p_per_information29 varchar2(200);
p_per_information30 varchar2(200);
p_date_of_death date;
p_background_check_status varchar2(200);
p_background_date_check date;
p_blood_type varchar2(200);
p_correspondence_language varchar2(200);
p_fast_path_employee varchar2(200);
p_fte_capacity number;
p_honors varchar2(200);
p_internal_location varchar2(200);
p_last_medical_test_by varchar2(200);
p_last_medical_test_date date;
p_mailstop varchar2(200);
p_office_number varchar2(200);
p_on_military_service varchar2(200);
p_pre_name_adjunct varchar2(200);
p_rehire_recommendation varchar2(200);
p_projected_start_date date;
p_resume_exists varchar2(200);
p_resume_last_updated date;
p_second_passport_exists varchar2(200);
p_student_status varchar2(200);
p_work_schedule varchar2(200);
p_suffix varchar2(200);
p_benefit_group_id number;
p_receipt_of_death_cert_date date;
p_coord_ben_med_pln_no varchar2(200);
p_coord_ben_no_cvg_flag varchar2(200);
p_coord_ben_med_ext_er varchar2(200);
p_coord_ben_med_pl_name varchar2(200);
p_coord_ben_med_insr_crr_name varchar2(200);
p_coord_ben_med_insr_crr_ident varchar2(200);
p_coord_ben_med_cvg_strt_dt date;
p_coord_ben_med_cvg_end_dt date;
p_uses_tobacco_flag varchar2(200);
p_dpdnt_adoption_date date;
p_dpdnt_vlntry_svce_flag varchar2(200);
p_original_date_of_hire date;
p_adjusted_svc_date date;
p_town_of_birth varchar2(200);
p_region_of_birth varchar2(200);
p_country_of_birth varchar2(200);
p_global_person_id varchar2(200);
p_party_id number;
p_person_id number;
p_assignment_id number;
p_per_object_version_number number;
p_asg_object_version_number number;
p_per_effective_start_date date;
p_per_effective_end_date date;
p_full_name varchar2(200);
p_per_comment_id number;
p_assignment_sequence number;
p_assignment_number varchar2(200);
p_name_combination_warning boolean;
p_assign_payroll_warning boolean;
begin
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 1110
,RESP_ID => 23991
,RESP_APPL_ID => 800);
for rec_emp in cur_emp Loop
hr_employee_api.create_employee(p_validate => p_validate,
p_hire_date => trunc(sysdate) /*雇佣日期,默认1900-01-01*/,
p_business_group_id => 101 /*业务组ID,默认0*/,
p_last_name => rec_emp.last_name /*姓名*/,
p_sex => rec_emp.Sex /*性别 M/F 需要搜集*/,
p_person_type_id => 1127 /*员工类型 默认为6*/,
p_per_comments => p_per_comments,
p_date_employee_data_verified => p_date_employee_data_verified,
p_date_of_birth => rec_emp.date_of_birth,
p_email_address => p_email_address,
p_employee_number => rec_emp.Emp_Num /*员工编号 需要搜集*/,
p_expense_check_send_to_addres => p_expense_check_send_to_addres,
p_first_name => rec_emp.first_name,
p_known_as => p_known_as,
p_marital_status => p_marital_status,
p_middle_names => p_middle_names,
p_nationality => p_nationality,
p_national_identifier => rec_emp.national_identifier,
p_previous_last_name => p_previous_last_name,
p_registered_disabled_flag => p_registered_disabled_flag,
p_title => p_title,
p_vendor_id => p_vendor_id,
p_work_telephone => p_work_telephone,
p_attribute_category => p_attribute_category,
p_attribute1 => 'Y',
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_attribute16 => p_attribute16,
p_attribute17 => p_attribute17,
p_attribute18 => p_attribute18,
p_attribute19 => p_attribute19,
p_attribute20 => p_attribute20,
p_attribute21 => p_attribute21,
p_attribute22 => p_attribute22,
p_attribute23 => p_attribute23,
p_attribute24 => p_attribute24,
p_attribute25 => p_attribute25,
p_attribute26 => p_attribute26,
p_attribute27 => p_attribute27,
p_attribute28 => p_attribute28,
p_attribute29 => p_attribute29,
p_attribute30 => p_attribute30,
p_per_information_category => p_per_information_category,
p_per_information1 => p_per_information1,
p_per_information2 => p_per_information2,
p_per_information3 => p_per_information3,
p_per_information4 => p_per_information4,
p_per_information5 => p_per_information5,
p_per_information6 => p_per_information6,
p_per_information7 => p_per_information7,
p_per_information8 => p_per_information8,
p_per_information9 => p_per_information9,
p_per_information10 => p_per_information10,
p_per_information11 => p_per_information11,
p_per_information12 => p_per_information12,
p_per_information13 => p_per_information13,
p_per_information14 => p_per_information14,
p_per_information15 => p_per_information15,
p_per_information16 => p_per_information16,
p_per_information17 => p_per_information17,
p_per_information18 => p_per_information18,
p_per_information19 => p_per_information19,
p_per_information20 => p_per_information20,
p_per_information21 => p_per_information21,
p_per_information22 => p_per_information22,
p_per_information23 => p_per_information23,
p_per_information24 => p_per_information24,
p_per_information25 => p_per_information25,
p_per_information26 => p_per_information26,
p_per_information27 => p_per_information27,
p_per_information28 => p_per_information28,
p_per_information29 => p_per_information29,
p_per_information30 => p_per_information30,
p_date_of_death => p_date_of_death,
p_background_check_status => p_background_check_status,
p_background_date_check => p_background_date_check,
p_blood_type => p_blood_type,
p_correspondence_language => p_correspondence_language,
p_fast_path_employee => p_fast_path_employee,
p_fte_capacity => p_fte_capacity,
p_honors => p_honors,
p_internal_location => p_internal_location,
p_last_medical_test_by => p_last_medical_test_by,
p_last_medical_test_date => p_last_medical_test_date,
p_mailstop => p_mailstop,
p_office_number => p_office_number,
p_on_military_service => p_on_military_service,
p_pre_name_adjunct => p_pre_name_adjunct,
p_rehire_recommendation => p_rehire_recommendation,
p_projected_start_date => p_projected_start_date,
p_resume_exists => p_resume_exists,
p_resume_last_updated => p_resume_last_updated,
p_second_passport_exists => p_second_passport_exists,
p_student_status => p_student_status,
p_work_schedule => p_work_schedule,
p_suffix => p_suffix,
p_benefit_group_id => p_benefit_group_id,
p_receipt_of_death_cert_date => p_receipt_of_death_cert_date,
p_coord_ben_med_pln_no => p_coord_ben_med_pln_no,
p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag,
p_coord_ben_med_ext_er => p_coord_ben_med_ext_er,
p_coord_ben_med_pl_name => p_coord_ben_med_pl_name,
p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name,
p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident,
p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt,
p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt,
p_uses_tobacco_flag => p_uses_tobacco_flag,
p_dpdnt_adoption_date => p_dpdnt_adoption_date,
p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag,
p_original_date_of_hire => trunc(sysdate) /*原始雇佣日期,默认1900-01-01*/,
p_adjusted_svc_date => p_adjusted_svc_date,
p_town_of_birth => p_town_of_birth,
p_region_of_birth => p_region_of_birth,
p_country_of_birth => p_country_of_birth,
p_global_person_id => p_global_person_id,
p_party_id => p_party_id,
p_person_id => p_person_id,
p_assignment_id => p_assignment_id,
p_per_object_version_number => p_per_object_version_number,
p_asg_object_version_number => p_asg_object_version_number,
p_per_effective_start_date => p_per_effective_start_date,
p_per_effective_end_date => p_per_effective_end_date,
p_full_name => p_full_name,
p_per_comment_id => p_per_comment_id,
p_assignment_sequence => p_assignment_sequence,
p_assignment_number => p_assignment_number,
p_name_combination_warning => p_name_combination_warning,
p_assign_payroll_warning => p_assign_payroll_warning);
Dbms_Output.put_line('p_person_id'||p_person_id);
update hr_employee_imp_b t
set t.person_id = P_person_id
where t.employee_number = rec_emp.Emp_Num;
--commit;
end loop;
commit;
end;