CREATE OR REPLACE FUNCTION HRIS_GET_SERVICE_STATUS(
    P_EMPLOYEE_ID NUMBER,
    V_DATE_ON     DATE )
  RETURN NUMBER
AS
  V_JOB_HISTORY_ID NUMBER:=NULL;
BEGIN
  BEGIN
    SELECT JOB_HISTORY_ID
    INTO V_JOB_HISTORY_ID
    FROM
      (SELECT H.JOB_HISTORY_ID
      FROM HRIS_JOB_HISTORY H
      WHERE H.START_DATE  <=TRUNC(V_DATE_ON) AND H.STATUS = 'E'
      AND TRUNC(V_DATE_ON)<= (
        CASE
          WHEN H.END_DATE IS NOT NULL
          THEN H.END_DATE
          ELSE V_DATE_ON
        END )
      AND H.EMPLOYEE_ID   = P_EMPLOYEE_ID
      AND H.DISABLED_FLAG ='N'
      ORDER BY H.START_DATE DESC,
        H.END_DATE ASC
      )
    WHERE ROWNUM =1;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    V_JOB_HISTORY_ID:=NULL;
  END;
RETURN V_JOB_HISTORY_ID;
END;