Oracle Apps Knowledge Sharing

SET SERVEROUTPUT ON;
DECLARE
  l_obj                        NUMBER;
  l_period_of_service_id       NUMBER;
  l_dod_warning                BOOLEAN;
  l_supervisor_warning         BOOLEAN;
  l_event_warning              BOOLEAN;
  l_interview_warning          BOOLEAN;
  l_review_warning             BOOLEAN;
  l_recruiter_warning          BOOLEAN;
  l_pay_proposal_warning       BOOLEAN;
  l_entries_changed_warning    VARCHAR2(30);
  l_asg_future_changes_warning BOOLEAN;
  l_org_now_no_manager_warning BOOLEAN;
  l_termination_date           DATE := SYSDATE-5;
  l_last_std_process_date      DATE := SYSDATE-5;
  l_final_process_date         DATE := SYSDATE-5;
  ln_person_id                 NUMBER DEFAULT 39010; -- To be end dated
BEGIN
  SELECT period_of_service_id
        ,object_version_number
    INTO l_period_of_service_id
        ,l_obj
    FROM per_periods_of_service
   WHERE person_id = ln_person_id
     AND actual_termination_date IS NULL;
  /*
  -- HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP populates the Leaving_Reason,
  -- Actual_Termination_Date and Last_Standard_Process_Date
  */
  hr_ex_employee_api.actual_termination_emp
          (p_validate                   => FALSE
          ,p_effective_date             => SYSDATE
          ,p_period_of_service_id       => l_period_of_service_id
          ,p_object_version_number      => l_obj
          ,p_actual_termination_date    => l_termination_date
          ,p_last_standard_process_date => l_last_std_process_date
          -- OUT Variables
          ,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
          );
  /*
  -- HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP populates Accepted_Termination_Date,
  -- Notified_Termination_Date and Projected_Termination_Date
  */
  hr_ex_employee_api.update_term_details_emp
       (p_validate                   => FALSE
       ,p_effective_date             => SYSDATE
       ,p_period_of_service_id       => l_period_of_service_id
       ,p_object_version_number      => l_obj
       ,p_accepted_termination_date  => SYSDATE-5
       ,p_notified_termination_date  => SYSDATE-5
       ,p_projected_termination_date => SYSDATE-5
       );

  COMMIT;
  
  DBMS_OUTPUT.PUT_LINE('Employee is terminated…');
  
  /*
  -- Update Final Process Date for Terminated Employee --
  -- To Be executed only after the employee is terminated
  -- and has an become EX-EMP  
  */
  SELECT period_of_service_id,
         object_version_number,
         last_standard_process_date
    INTO l_period_of_service_id,
         l_obj,
         l_final_process_date
    FROM per_periods_of_service ppos
   WHERE period_of_service_id = ( SELECT MAX(period_of_service_id)
                                    FROM per_periods_of_service
                                   WHERE person_id = ppos.person_id
                                     AND actual_termination_date IS NOT NULL)
     AND person_id = ln_person_id
     AND final_process_date IS NULL;

  hr_ex_employee_api.final_process_emp
       (p_validate                   => FALSE
       ,p_period_of_service_id       => l_period_of_service_id
       ,p_object_version_number      => l_obj
       ,p_final_process_date         => l_final_process_date
       -- OUT Variables
       ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
       ,p_asg_future_changes_warning => l_asg_future_changes_warning
       ,p_entries_changed_warning    => l_entries_changed_warning
       );
  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Final Process Date is updated for Terminated Employee…');  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error while terminating Employee : '||SQLERRM);
END;
/

Testing: