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;
  V_CURRENT_SHIFT_ID NUMBER;
  V_BETN_TWOPM CHAR(5 BYTE);
  V_BETN_TWOPM_OUT TIMESTAMP;
BEGIN

SELECT SHIFT_ID INTO V_CURRENT_SHIFT_ID FROM HRIS_ATTENDANCE_DETAIL WHERE
ATTENDANCE_DT=TRUNC(P_FROM_ATTENDANCE_TIME) AND EMPLOYEE_ID=P_EMPLOYEE_ID;

BEGIN
SELECT TWO_DAY_SHIFT INTO V_TWO_DAY_SHIFT  FROM HRIS_SHIFTS WHERE SHIFT_ID =V_CURRENT_SHIFT_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
V_YESTERDAY_OUT_TIME:=trunc(P_FROM_ATTENDANCE_TIME-1);
--raise_application_error(-20344,'no attendance in that date');
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
  V_IS_TWO_DAY_SHIFT:='Y';

  select 
  CASE WHEN V_CURRENT_SHIFT_ID =14
  THEN
  TO_DATE(TO_CHAR(P_TO_ATTENDANCE_TIME,'DD-MON-YYYY')|| '01:00 PM','DD-MON-YYYY HH:MI AM')
  ELSE
  TO_DATE(TO_CHAR(P_TO_ATTENDANCE_TIME,'DD-MON-YYYY')|| '10:00 AM','DD-MON-YYYY HH:MI AM')
  END
  ,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
  and A.ATTENDANCE_TIME<V_OUT_TIME_BEFORE;




  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;
  --
  
    -- check to overide out time if punch time is   between 1:30 to 2:30 start
select 
case when 
P_IN_TIME
between 
TO_DATE(TO_CHAR(P_FROM_ATTENDANCE_TIME,'DD-MON-YYYY')||'01:45 PM','DD-MON-YYYY HH:MI AM')
and
TO_DATE(TO_CHAR(P_FROM_ATTENDANCE_TIME,'DD-MON-YYYY')||'02:30 PM','DD-MON-YYYY HH:MI AM')
then
'TRUE'
else
'FALSE'
end  into V_BETN_TWOPM
from dual;


IF(V_BETN_TWOPM = 'TRUE')
THEN


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_BETN_TWOPM_OUT
  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
  --AND V_TWO_DAY_SHIFT='E'
  AND (A.ATTENDANCE_TIME BETWEEN
  TO_DATE(TO_CHAR(P_FROM_ATTENDANCE_TIME+1,'DD-MON-YYYY')||' 4:00 AM','DD-MON-YYYY HH:MI AM') 
  AND
  TO_DATE(TO_CHAR(P_FROM_ATTENDANCE_TIME+1,'DD-MON-YYYY')||' 6:30 AM','DD-MON-YYYY HH:MI AM'));

  
  IF(V_BETN_TWOPM_OUT IS NOT NULL )
  THEN
  V_OUT_TIME:=V_BETN_TWOPM_OUT;
  END IF;


END IF;
-- check to overide out time if punch time is   between 1:30 to 2:30 end
  
  
  
  
  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;