HRMS Interface -Employee Creation and updation

Hi All,
this is basic to sink employee information between two instances....


CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTF_PROC
(retcode VARCHAR2
, errbuff VARCHAR2)
AS l_emp_num VARCHAR2 (30);
l_person_id NUMBER;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (30);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_employee_number VARCHAR2 (20);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_comment_id NUMBER;
lv_rec_exixsts VARCHAR2 (1) := 'N';
l_last_std_process_date_out DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (200);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_alu_change_warning VARCHAR2 (200);
l_acutal_temination_date_ewgl timestamp;
l_business_group_id number:=101;
l_ser_object_version_number number;
l_last_run_date timestamp ;
l_service_id number;
CURSOR c_emp_cur_hrms(c_last_run_Date date) IS
SELECT * FROM xx_hrms_intf_v@uat_new
where last_update_date>=c_last_run_Date or creation_Date>=c_last_run_Date;
BEGIN
select max(actual_completion_date)
into l_last_run_date from FND_CONCURRENT_REQUESTS fcr, fnd_concurrent_programs fcp where fcr.concurrent_program_id=fcp.concurrent_program_id
and concurrent_program_name='XX_HRMS_INTF'
and fcr.STATUS_CODE='C';
fnd_file.PUT_LINE(fnd_file.log,'last run successful date:'l_last_run_date); --Creating New Employee
FOR emp_rec IN c_emp_cur_hrms(l_last_run_date) LOOP
fnd_file.put_line (fnd_file.LOG, 'HRMS employee NO:' emp_rec.employee_number);
BEGIN
SELECT 'Y' INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = emp_rec.employee_number
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and business_group_id=l_business_group_id;
EXCEPTION
WHEN OTHERS THEN
lv_rec_exixsts := 'N';
END;
IF lv_rec_exixsts = 'N' THEN
hr_employee_api.create_employee
(p_hire_date => emp_rec.start_date ,
p_business_group_id => l_business_group_id,
p_last_name => emp_rec.last_name ,
p_first_name => emp_rec.first_name ,
p_title => emp_rec.title, p_sex => emp_rec.sex ,
p_employee_number => emp_rec.employee_number ,
p_date_of_birth => emp_rec.date_of_birth ,
p_person_id => l_person_id ,
p_assignment_id => l_assignment_id ,
p_per_object_version_number => l_per_object_version_number ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_full_name => l_full_name ,
p_per_comment_id => l_per_comment_id ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_name_combination_warning => l_name_combination_warning ,
p_assign_payroll_warning => l_assign_payroll_warning ,
p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'Creating New employee:' emp_rec.employee_number); ELSIF lv_rec_exixsts = 'Y' THEN
SELECT papf.person_id ,
papf.object_version_number ,
ppos.actual_termination_date ,
ppos.OBJECT_VERSION_NUMBER ,
ppos.PERIOD_OF_SERVICE_ID INTO l_person_id ,
l_per_object_version_number ,
l_acutal_temination_date_ewgl ,
l_ser_object_version_number ,
l_service_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE employee_number = emp_rec.employee_number
AND papf.person_id = ppos.person_id
and papf.business_group_id=l_business_group_id
AND SYSDATE BETWEEN EFFECTIVE_START_dATE AND EFFECTIVE_END_DATE;
l_employee_number := emp_rec.employee_number;
fnd_file.put_line(fnd_file.log,'person_id:'l_person_id);
fnd_file.put_line(fnd_file.log,'Object Version Number:'l_per_object_version_number); fnd_file.put_line(fnd_file.log,'EWGL Termination date:'l_acutal_temination_date_ewgl); fnd_file.put_line(fnd_file.log,'Last Update date:'emp_rec.last_update_date); fnd_file.put_line(fnd_file.log,'HRMS Employee Actual Termination Date:'emp_rec.actual_termination_date); f
nd_file.put_line(fnd_file.log,'Service Object Version:'l_ser_object_version_number);
IF emp_rec.last_update_date>=l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL and emp_rec.actual_termination_date is null THEN
hr_person_api.update_person (p_effective_date => emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_last_name => emp_rec.last_name
,p_first_name => emp_rec.first_name
,p_date_of_birth => emp_rec.date_of_birth
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'UPDATING New employee:' emp_rec.employee_number);
END IF;
IF emp_rec.actual_termination_date IS NOT NULL and emp_rec.last_update_date>=l_last_run_date THEN
l_last_std_process_date_out := emp_rec.final_process_date; hr_ex_employee_api.actual_termination_emp
(p_effective_date => emp_rec.effective_start_date
,p_period_of_service_id => l_service_id
,p_object_version_number => l_ser_object_version_number
,p_actual_termination_date => emp_rec.actual_termination_date
,p_person_type_id=>null
,p_last_std_process_date_out => l_last_std_process_date_out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning ,
p_pay_proposal_warning => l_pay_proposal_warning ,
p_dod_warning => l_dod_warning ,
p_alu_change_warning => l_alu_change_warning);
fnd_file.put_line (fnd_file.LOG, 'Terminating New employee:' emp_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND emp_rec.actual_termination_date IS NULL and emp_rec.last_update_date>=l_last_run_date THEN hr_employee_api.re_hire_ex_employee
(p_hire_date => emp_rec.effective_start_date ,
p_person_id => l_person_id ,
p_per_object_version_number => l_per_object_version_number ,
p_rehire_reason => ' ', p_assignment_id => l_assignment_id ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_assign_payroll_warning => l_assign_payroll_warning);
fnd_file.put_line (fnd_file.LOG, 'RE Hiring New employee:' emp_rec.employee_number); END IF; END IF; END LOOP;
commit;
END;