–说明：以下代码已经经过本人测试,建议在实际应用时再次测试
declare
–常量定义
l_terminate_emp_flag varchar2(1) := 'N';
l_terminate_msg varchar2(600);
–select t.person_id, t.last_name from per_people_f t;
l_person_id number := 1383;
l_le_terminate_emp_exception exception;
–DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.actual_termination_emp
–IN variables
l_effective_date date;
–离职原因,需根据系统定义的参数输入 select distinct t.leaving_reason from hr.PER_PERIODS_OF_SERVICE t;
l_termination_reason per_periods_of_service.leaving_reason%type := 'EMP_TRANS';
–当执行时显示：Error validating API: hr_ex_employee_api.actual_termination_emp : ORA-20001: 必须指定具有系统人员类型 EX_EMP 的人员类型。
–运行下面命令找出 select t.person_type_id from hr.per_person_types t where t.system_person_type = 'EX_EMP';得出'9'
l_person_type_id per_person_types.person_type_id%type := 9;
l_period_of_service_id per_periods_of_service.period_of_service_id%type;
l_actual_termination_date per_periods_of_service.actual_termination_date%type := trunc(sysdate);
l_last_standard_process_date per_periods_of_service.last_standard_process_date%type := trunc(sysdate + 10);
l_object_version_number per_periods_of_service.object_version_number%type;
l_start_date per_periods_of_service.date_start%type;
l_notif_term_date date;
–OUT variables
l_supervisor_warning boolean := false;
l_event_warning boolean := false;
l_interview_warning boolean := false;
l_review_warning boolean := false;
l_recruiter_warning boolean := false;
l_asg_future_changes_warning boolean := false;
l_entries_changed_warning varchar2(300);
l_pay_proposal_warning boolean := false;
l_dod_warning boolean := false;
l_alu_change_warning varchar2(300);
–DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.final_process_emp
–IN variables
l_final_process_date per_periods_of_service.final_process_date%type;
–OUT variables
l_org_now_no_manager_warning boolean := false;
l_f_asg_future_changes_warning boolean := false;
l_f_entries_changed_warning varchar2(300);
—
begin
begin
select pos.period_of_service_id, pos.object_version_number, date_start
into l_period_of_service_id, l_object_version_number, l_start_date
from per_periods_of_service pos
where pos.person_id = l_person_id;
exception
when others then
l_terminate_msg := 'Error while selecting employee details : ' ||
substr(sqlerrm, 1, 150);
raise l_le_terminate_emp_exception;
end;
–保存点
savepoint terminate_employee_s1;
begin
/*
* This API terminates an employee.
* This API converts a person of type Employee >to a person of type
* Ex-Employee. The person's period of service and any employee assignments are ended.
*/
hr_ex_employee_api.actual_termination_emp(p_validate => false –l_validate
,
p_effective_date => trunc(sysdate),
p_period_of_service_id => l_period_of_service_id,
p_object_version_number => l_object_version_number,
p_actual_termination_date => l_actual_termination_date,
p_last_standard_process_date => l_last_standard_process_date,
p_person_type_id => l_person_type_id,
p_leaving_reason => l_termination_reason
–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);
if l_object_version_number is null then
l_terminate_emp_flag := 'N';
l_terminate_msg := 'Warning validating API: hr_ex_employee_api.actual_termination_emp';
raise l_le_terminate_emp_exception;
end if;
l_terminate_emp_flag := 'Y';
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.actual_termination_emp : ' ||
substr(sqlerrm, 1, 150);
raise l_le_terminate_emp_exception;
end;
— hr_ex_employee_api.actual_termination_emp
if l_terminate_emp_flag = 'Y' then
begin
if l_start_date > trunc(sysdate) then
l_notif_term_date := l_start_date + 1;
else
l_notif_term_date := trunc(sysdate);
end if;
/*
* This API updates employee termination information.
* The ex-employee must exist in the relevant business group
*/
apps.hr_ex_employee_api.update_term_details_emp(p_validate => false –l_validate
,
p_effective_date => trunc(sysdate),
p_period_of_service_id => l_period_of_service_id,
p_notified_termination_date => l_notif_term_date,
p_projected_termination_date => l_notif_term_date
–In/Out
,
p_object_version_number => l_object_version_number);
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.update_term_details_emp : ' ||
substr(sqlerrm, 1, 150);
l_terminate_emp_flag := 'N';
raise l_le_terminate_emp_exception;
end; –hr_ex_employee_api.update_term_details_emp
begin
/*
* This API set the final process date for a terminated employee.
* This API covers the second step in terminating a period of service and all
* current assignments for an employee. It updates the period of service
* details and date-effectively deletes all employee assignments as of the final process date.
*/
apps.hr_ex_employee_api.final_process_emp(p_validate => false –l_validate
,
p_period_of_service_id => l_period_of_service_id
–Out
,
p_object_version_number => l_object_version_number,
p_final_process_date => l_final_process_date,
p_org_now_no_manager_warning => l_org_now_no_manager_warning,
p_asg_future_changes_warning => l_f_asg_future_changes_warning,
p_entries_changed_warning => l_f_entries_changed_warning);
exception
when others then
l_terminate_msg := 'Error validating API: hr_ex_employee_api.final_process_emp : ' ||
substr(sqlerrm, 1, 150);
raise l_le_terminate_emp_exception;
end; –hr_ex_employee_api.final_process_emp
end if;
commit;
exception
when l_le_terminate_emp_exception then
dbms_output.put_line(l_terminate_msg);
rollback to terminate_employee_s1;
when others then
dbms_output.put_line('Terminate Employee. Error OTHERS while validating: ' ||
sqlerrm);
rollback to terminate_employee_s1;
end;
