create or replace PROCEDURE HRIS_ATTD_IN_OUT(
    P_EMPLOYEE_ID          IN HRIS_ATTENDANCE_DETAIL.EMPLOYEE_ID%TYPE,
    P_FROM_ATTENDANCE_TIME IN TIMESTAMP,
    P_TO_ATTENDANCE_TIME   IN TIMESTAMP,
    P_IN_TIME OUT HRIS_ATTENDANCE_DETAIL.IN_TIME%TYPE,
    P_OUT_TIME OUT HRIS_ATTENDANCE_DETAIL.OUT_TIME%TYPE,
    P_IS_TWO_DAY_SHIFT OUT CHAR)
AS
  V_IN_TIME          TIMESTAMP;
  V_OUT_TIME         TIMESTAMP;
  V_IS_TWO_DAY_SHIFT CHAR:='N';
  V_YESTERDAY_OUT_TIME TIMESTAMP;
BEGIN


begin
select 
case when
out_time is null then in_time else out_time + interval '3' minute
end 
INTO V_YESTERDAY_OUT_TIME
from Hris_Attendance_Detail
where Attendance_Dt=trunc(P_FROM_ATTENDANCE_TIME)-1 and employee_id=P_EMPLOYEE_ID;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;

if(V_YESTERDAY_OUT_TIME is null)
then
V_YESTERDAY_OUT_TIME:=trunc(P_FROM_ATTENDANCE_TIME)-1;
end if;

  SELECT MIN(A.ATTENDANCE_TIME) AS IN_TIME,
    MAX(A.ATTENDANCE_TIME)      AS OUT_TIME
  INTO P_IN_TIME,
    P_OUT_TIME
  FROM HRIS_ATTENDANCE A
  WHERE (A.ATTENDANCE_TIME >= P_FROM_ATTENDANCE_TIME
  AND A.ATTENDANCE_TIME    <= P_TO_ATTENDANCE_TIME)
  AND A.EMPLOYEE_ID         = P_EMPLOYEE_ID 
  and A.ATTENDANCE_TIME>V_YESTERDAY_OUT_TIME;
  --
  SELECT MIN(TO_DATE(TO_CHAR(A.ATTENDANCE_DT,'DD-MON-YYYY')
    ||' '
    ||TO_CHAR(A.ATTENDANCE_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM')) AS IN_TIME
  INTO V_IN_TIME
  FROM HRIS_ATTENDANCE A
  LEFT JOIN HRIS_ATTD_DEVICE_MASTER ADM
  ON (A.IP_ADDRESS          =ADM.DEVICE_IP)
  WHERE (A.ATTENDANCE_TIME >= P_FROM_ATTENDANCE_TIME
  AND A.ATTENDANCE_TIME    <= P_TO_ATTENDANCE_TIME)
  AND A.EMPLOYEE_ID         = P_EMPLOYEE_ID
  AND (ADM.PURPOSE           ='IN') 
  and A.ATTENDANCE_TIME>V_YESTERDAY_OUT_TIME;
  --
  SELECT MAX(TO_DATE(TO_CHAR(A.ATTENDANCE_DT,'DD-MON-YYYY')
    ||' '
    || TO_CHAR(A.ATTENDANCE_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM')) AS IN_TIME
  INTO V_OUT_TIME
  FROM HRIS_ATTENDANCE A
  LEFT JOIN HRIS_ATTD_DEVICE_MASTER ADM
  ON (A.IP_ADDRESS          =ADM.DEVICE_IP)
  WHERE (A.ATTENDANCE_TIME >= P_FROM_ATTENDANCE_TIME
  AND A.ATTENDANCE_TIME    <= P_TO_ATTENDANCE_TIME)
  AND A.EMPLOYEE_ID         = P_EMPLOYEE_ID
  AND (ADM.PURPOSE           ='OUT') 
  and A.ATTENDANCE_TIME>V_YESTERDAY_OUT_TIME;
  --
  IF V_IN_TIME        IS NOT NULL THEN
    P_IN_TIME         :=V_IN_TIME;
    V_IS_TWO_DAY_SHIFT:='Y';
  END IF;
  --
  IF V_OUT_TIME       IS NOT NULL THEN
    P_OUT_TIME        :=V_OUT_TIME;
    V_IS_TWO_DAY_SHIFT:='Y';
  END IF;
  P_IS_TWO_DAY_SHIFT:=V_IS_TWO_DAY_SHIFT;
END;