Sunday, March 1, 2009

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;

3 comments:

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.