CREATE OR REPLACE PROCEDURE HRIS_UPDATE_JOB_HISTORY(
    P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE)
AS
  V_LATEST_JOB_HISTORY_ID HRIS_JOB_HISTORY.JOB_HISTORY_ID%TYPE;
BEGIN
  SELECT JOB_HISTORY_ID
  INTO V_LATEST_JOB_HISTORY_ID
  FROM
    (SELECT *
    FROM HRIS_JOB_HISTORY
    WHERE EMPLOYEE_ID = P_EMPLOYEE_ID AND STATUS = 'E'
    ORDER BY START_DATE DESC
    )
  WHERE ROWNUM=1;
  FOR employee IN
  (SELECT * FROM HRIS_EMPLOYEES WHERE EMPLOYEE_ID = P_EMPLOYEE_ID
  )
  LOOP
    UPDATE HRIS_JOB_HISTORY
    SET TO_COMPANY_ID   = employee.COMPANY_ID,
      TO_BRANCH_ID      =employee.BRANCH_ID,
      TO_DEPARTMENT_ID  =employee.DEPARTMENT_ID,
      TO_DESIGNATION_ID =employee.DESIGNATION_ID,
      TO_POSITION_ID    =employee.POSITION_ID,
      TO_SERVICE_TYPE_ID=employee.SERVICE_TYPE_ID,
      TO_SALARY         =employee.SALARY,
      RETIRED_FLAG      = employee.RETIRED_FLAG,
      DISABLED_FLAG     = (
      CASE
        WHEN employee.STATUS= 'D'
        THEN 'N'
        ELSE 'Y'
      END)
    WHERE JOB_HISTORY_ID = V_LATEST_JOB_HISTORY_ID;
  END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE ('No job history record found for employeeId : '||P_EMPLOYEE_ID);
END;
