create or replace FUNCTION HRIS_CHECK_HOLIDAY_INBETWN(
    P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE,
    P_ATTENDANCE_DT DATE )
  RETURN NUMBER
AS
  V_START_DATE DATE := NULL;
  V_END_DATE   DATE := NULL;
  V_COUNT NUMBER:= NULL;
BEGIN

  SELECT max(ATTENDANCE_DT)
  INTO V_START_DATE
  FROM HRIS_ATTENDANCE_DETAIL
  WHERE OVERALL_STATUS = 'AB'
  AND EMPLOYEE_ID      = P_EMPLOYEE_ID
  AND ATTENDANCE_DT    < P_ATTENDANCE_DT;

  SELECT min(ATTENDANCE_DT)
  INTO V_END_DATE
  FROM HRIS_ATTENDANCE_DETAIL
  WHERE OVERALL_STATUS = 'AB'
  AND EMPLOYEE_ID      = P_EMPLOYEE_ID
  AND ATTENDANCE_DT    > P_ATTENDANCE_DT;

  IF(V_END_DATE is NULL) THEN
  V_END_DATE := trunc(sysdate);
  END IF;

  SELECT count(IN_TIME)
  into V_COUNT
  FROM HRIS_ATTENDANCE_DETAIL
  WHERE ATTENDANCE_DT BETWEEN V_START_DATE AND V_END_DATE
  AND employee_id = P_EMPLOYEE_ID
  AND IN_TIME   IS NOT NULL;

  IF(V_COUNT = 0) then
  return 1;
  else
  RETURN 0;
  end if;
END;