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;

《Oracle EBS HR解除员工雇佣》上的一个想法

发表回复

您的电子邮箱地址不会被公开。