oracle裁员hr,Oracle EBS HR解除员工雇佣

–说明：以下代码已经经过本人测试,建议在实际应用时再次测试

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;