HRMS Interface for Employee Creation and update
Basic process to sink employee information between two instances
CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTEGRATION_PROC (
retcode VARCHAR2,
errbuff VARCHAR2)
AS
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_hrms (c_last_run_Date DATE)
IS
SELECT *
FROM xx_hrms_integration_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_INTEGRATION'
AND fcr.STATUS_CODE = 'C';
fnd_file.PUT_LINE (fnd_file.LOG,
'Last run successful date:' || l_last_run_date);
--Creating New Employee
FOR c_emp_hrms_rec IN c_emp_hrms (l_last_run_date)
LOOP
fnd_file.put_line (
fnd_file.LOG,
'HRMS employee NO:' || c_emp_hrms_rec.employee_number);
BEGIN
SELECT 'Y'
INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = c_emp_hrms_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 => c_emp_hrms_rec.start_date,
p_business_group_id => l_business_group_id,
p_last_name => c_emp_hrms_rec.last_name,
p_first_name => c_emp_hrms_rec.first_name,
p_title => c_emp_hrms_rec.title,
p_sex => c_emp_hrms_rec.sex,
p_employee_number => c_emp_hrms_rec.employee_number,
p_date_of_birth => c_emp_hrms_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:' || c_emp_hrms_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 = c_emp_hrms_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 := c_emp_hrms_rec.employee_number;
fnd_file.put_line (
fnd_file.LOG,
'person_id:'
|| l_person_id
|| ' - Object Version Number:'
|| l_per_object_version_number
|| ' - EWGL Termination date:'
|| l_acutal_temination_date_ewgl
|| ' - Last Update date:'
|| c_emp_hrms_rec.last_update_date
|| ' - HRMS Employee Actual Termination Date:'
|| c_emp_hrms_rec.actual_termination_date
|| ' - Service Object Version:'
|| l_ser_object_version_number);
IF c_emp_hrms_rec.last_update_date >= l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL
AND c_emp_hrms_rec.actual_termination_date IS NULL
THEN
hr_person_api.update_person (
p_effective_date => c_emp_hrms_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 => c_emp_hrms_rec.last_name,
p_first_name => c_emp_hrms_rec.first_name,
p_date_of_birth => c_emp_hrms_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:'
|| c_emp_hrms_rec.employee_number);
END IF;
IF c_emp_hrms_rec.actual_termination_date IS NOT NULL
AND c_emp_hrms_rec.last_update_date >= l_last_run_date
THEN
l_last_std_process_date_out :=
c_emp_hrms_rec.final_process_date;
hr_ex_employee_api.actual_termination_emp (
p_effective_date => c_emp_hrms_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 =>
c_emp_hrms_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:'
|| c_emp_hrms_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND c_emp_hrms_rec.actual_termination_date IS NULL
AND c_emp_hrms_rec.last_update_date >= l_last_run_date
THEN
hr_employee_api.re_hire_ex_employee (
p_hire_date => c_emp_hrms_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:'
|| c_emp_hrms_rec.employee_number);
END IF;
END IF;
END LOOP;
COMMIT;
END;
No comments:
Post a Comment