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;
  V_TWO_DAY_SHIFT CHAR(1 BYTE);
  V_OUT_TIME_BEFORE TIMESTAMP;
  V_OUT_TIME_MIN TIMESTAMP;
BEGIN

BEGIN
SELECT TWO_DAY_SHIFT INTO V_TWO_DAY_SHIFT  FROM HRIS_SHIFTS WHERE SHIFT_ID =(
SELECT SHIFT_ID FROM HRIS_ATTENDANCE_DETAIL WHERE ATTENDANCE_DT=TRUNC(P_FROM_ATTENDANCE_TIME) AND EMPLOYEE_ID=P_EMPLOYEE_ID);
END;


begin
select 
case when
out_time is null then in_time else out_time 
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;
  --

    DBMS_OUTPUT.PUT_LINE('start');


  DBMS_OUTPUT.PUT_LINE('from '||P_FROM_ATTENDANCE_TIME);

  DBMS_OUTPUT.PUT_LINE('to '||P_TO_ATTENDANCE_TIME);


  IF(V_TWO_DAY_SHIFT='E')
  THEN
  begin

  select 
  TO_DATE(TO_CHAR(P_TO_ATTENDANCE_TIME,'DD-MON-YYYY')||'08:30 AM','DD-MON-YYYY HH:MI AM')
  ,TO_DATE(TO_CHAR(P_TO_ATTENDANCE_TIME,'DD-MON-YYYY')||'01:00 AM','DD-MON-YYYY HH:MI AM')
  INTO
  V_OUT_TIME_BEFORE
  ,V_OUT_TIME_MIN
  FROM DUAL;



  DBMS_OUTPUT.PUT_LINE('sdfsdfsdfsdf----');
  DBMS_OUTPUT.PUT_LINE(V_OUT_TIME_MIN);



  DBMS_OUTPUT.PUT_LINE('-------sdfsdfsdfsd');


  SELECT 
    MIN(A.ATTENDANCE_TIME)      AS OUT_TIME
  INTO P_OUT_TIME
  FROM HRIS_ATTENDANCE A
  WHERE (A.ATTENDANCE_TIME >= V_OUT_TIME_MIN
  AND A.ATTENDANCE_TIME    <= V_OUT_TIME_BEFORE)
  AND A.EMPLOYEE_ID         = P_EMPLOYEE_ID 
  and A.ATTENDANCE_TIME>V_YESTERDAY_OUT_TIME;

  end;

  END IF;

  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;




  DBMS_OUTPUT.PUT_LINE(P_OUT_TIME);




  DBMS_OUTPUT.PUT_LINE('end');


  --
  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;