SELECT (
  CASE
    WHEN ((S.START_TIME-TRUNC(S.START_TIME))-(A.IN_TIME-TRUNC(A.OUT_TIME))) > (INTERVAL '01:00' HOUR TO MINUTE)
    THEN 'Y'
    ELSE 'N'
  END) AS EARLY_OT_FLAG,
  (
  CASE
    WHEN ((A.OUT_TIME-TRUNC(A.OUT_TIME))-(S.END_TIME-TRUNC(S.END_TIME))) > (INTERVAL '01:00' HOUR TO MINUTE)
    THEN 'Y'
    ELSE 'N'
  END) AS LATE_OT_FLAG,
  A.IN_TIME,
  S.START_TIME,
  S.END_TIME,
  A.OUT_TIME
FROM HRIS_ATTENDANCE_DETAIL A
JOIN HRIS_SHIFTS S
ON (A.SHIFT_ID    =S.SHIFT_ID)
WHERE EMPLOYEE_ID =700218
AND ATTENDANCE_DT =TRUNC(SYSDATE-7);
