CREATE OR REPLACE PROCEDURE HRIS_ATTENDANCE_AFTER_INSERT(
    P_EMPLOYEE_ID HRIS_ATTENDANCE.EMPLOYEE_ID%TYPE,
    P_ATTENDANCE_DT HRIS_ATTENDANCE.ATTENDANCE_DT%TYPE,
    P_ATTENDANCE_TIME HRIS_ATTENDANCE.ATTENDANCE_TIME%TYPE,
    P_REMARKS HRIS_ATTENDANCE.REMARKS%TYPE,
    P_PURPOSE HRIS_ATTD_DEVICE_MASTER.PURPOSE%TYPE:=NULL)
AS
  V_IN_TIME HRIS_ATTENDANCE_DETAIL.IN_TIME%TYPE;
  V_SHIFT_ID HRIS_SHIFTS.SHIFT_ID%TYPE;
  V_OVERALL_STATUS HRIS_ATTENDANCE_DETAIL.OVERALL_STATUS%TYPE;
  V_LATE_STATUS HRIS_ATTENDANCE_DETAIL.LATE_STATUS%TYPE:='N';
  V_HALFDAY_FLAG HRIS_ATTENDANCE_DETAIL.HALFDAY_FLAG%TYPE;
  V_HALFDAY_PERIOD HRIS_ATTENDANCE_DETAIL.HALFDAY_PERIOD%TYPE;
  V_GRACE_PERIOD HRIS_ATTENDANCE_DETAIL.GRACE_PERIOD%TYPE;
  V_LATE_IN HRIS_SHIFTS.LATE_IN%TYPE;
  V_EARLY_OUT HRIS_SHIFTS.EARLY_OUT%TYPE;
  V_LATE_START_TIME   TIMESTAMP;
  V_EARLY_END_TIME    TIMESTAMP;
  V_TOTAL_WORKING_MIN NUMBER;
  V_LATE_COUNT        NUMBER :=0;
  V_TOTAL_HOUR        NUMBER :=0;
  V_TWO_DAY_SHIFT HRIS_ATTENDANCE_DETAIL.TWO_DAY_SHIFT%TYPE;
  V_IGNORE_TIME HRIS_SHIFTS.IGNORE_TIME%TYPE;
  V_HALF_INTERVAL   DATE;
  V_ATTENDANCE_DT   DATE;
  V_ATTENDANCE_TIME TIMESTAMP;
BEGIN
  V_ATTENDANCE_DT  :=TRUNC(P_ATTENDANCE_DT);
  V_ATTENDANCE_TIME:=TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY') ||' ' ||TO_CHAR(P_ATTENDANCE_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM');
  --
  BEGIN
    SELECT SHIFT_ID,
      OVERALL_STATUS,
      LATE_STATUS,
      HALFDAY_FLAG,
      HALFDAY_PERIOD,
      GRACE_PERIOD,
      IN_TIME,
      HALFDAY_PERIOD,
      TWO_DAY_SHIFT,
      IGNORE_TIME
    INTO V_SHIFT_ID,
      V_OVERALL_STATUS,
      V_LATE_STATUS,
      V_HALFDAY_FLAG,
      V_HALFDAY_PERIOD,
      V_GRACE_PERIOD,
      V_IN_TIME,
      V_HALFDAY_PERIOD,
      V_TWO_DAY_SHIFT,
      V_IGNORE_TIME
    FROM HRIS_ATTENDANCE_DETAIL
    WHERE ATTENDANCE_DT       = TRUNC(V_ATTENDANCE_DT)
    AND EMPLOYEE_ID           = P_EMPLOYEE_ID;
    IF V_IGNORE_TIME          ='Y' THEN
      IF(V_OVERALL_STATUS     ='DO') THEN
        V_OVERALL_STATUS     :='WD';
      ELSIF (V_OVERALL_STATUS ='HD') THEN
        V_OVERALL_STATUS     :='WH';
      ELSIF (V_OVERALL_STATUS ='LV') THEN
        NULL;
      ELSIF (V_OVERALL_STATUS ='TV') THEN
        NULL;
      ELSIF (V_OVERALL_STATUS ='TN') THEN
        NULL;
      ELSE
        V_OVERALL_STATUS :='PR';
      END IF;
      IF P_PURPOSE='IN' THEN
        UPDATE HRIS_ATTENDANCE_DETAIL
        SET IN_TIME         = TO_DATE ( TO_CHAR (V_ATTENDANCE_TIME, 'DD-MON-YY HH:MI AM'), 'DD-MON-YY HH:MI AM'),
          OVERALL_STATUS    = V_OVERALL_STATUS,
          IN_REMARKS        = P_REMARKS
        WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
        AND EMPLOYEE_ID     = P_EMPLOYEE_ID
        AND IN_TIME        IS NULL;
        RETURN;
      END IF;
      IF P_PURPOSE      ='OUT' THEN
        V_ATTENDANCE_DT:=V_ATTENDANCE_DT-1;
        UPDATE HRIS_ATTENDANCE_DETAIL
        SET OUT_TIME        = V_ATTENDANCE_TIME,
          OVERALL_STATUS    = V_OVERALL_STATUS,
          OUT_REMARKS       = P_REMARKS
        WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
        AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
        RETURN;
      END IF;
      IF (V_IN_TIME IS NULL) THEN
        UPDATE HRIS_ATTENDANCE_DETAIL
        SET IN_TIME         = TO_DATE ( TO_CHAR (V_ATTENDANCE_TIME, 'DD-MON-YY HH:MI AM'), 'DD-MON-YY HH:MI AM'),
          OVERALL_STATUS    = V_OVERALL_STATUS,
          LATE_STATUS       = V_LATE_STATUS,
          IN_REMARKS        = P_REMARKS
        WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
        AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
        RETURN;
      END IF;
      SELECT SUM(ABS(EXTRACT( HOUR FROM DIFF ))*60 + ABS(EXTRACT( MINUTE FROM DIFF )))
      INTO V_TOTAL_HOUR
      FROM
        (SELECT (V_ATTENDANCE_TIME-V_IN_TIME) AS DIFF FROM DUAL
        ) ;
      UPDATE HRIS_ATTENDANCE_DETAIL
      SET OUT_TIME        = V_ATTENDANCE_TIME,
        LATE_STATUS       =V_LATE_STATUS,
        OUT_REMARKS       = P_REMARKS,
        TOTAL_HOUR        =V_TOTAL_HOUR,
        OT_MINUTES        =(V_TOTAL_HOUR- V_TOTAL_WORKING_MIN)
      WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
      AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
      RETURN;
    END IF;
    IF V_TWO_DAY_SHIFT ='E' THEN
      --
      SELECT LATE_IN,
        EARLY_OUT,
        LATE_START_TIME,
        EARLY_END_TIME,
        EARLY_END_TIME + (LATE_START_TIME -EARLY_END_TIME)/2,
        TOTAL_WORKING_HR
      INTO V_LATE_IN,
        V_EARLY_OUT,
        V_LATE_START_TIME,
        V_EARLY_END_TIME,
        V_HALF_INTERVAL,
        V_TOTAL_WORKING_MIN
      FROM
        (SELECT S.LATE_IN,
          S.EARLY_OUT,
          TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
          ||' '
          ||TO_CHAR(S.START_TIME+((1/1440)*NVL(S.LATE_IN,0)),'HH:MI AM'),'DD-MON-YYYY HH:MI AM') AS LATE_START_TIME,
          TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
          ||' '
          || TO_CHAR(S.END_TIME -((1/1440)*NVL(S.EARLY_OUT,0)),'HH:MI AM'),'DD-MON-YYYY HH:MI AM') AS EARLY_END_TIME,
          S.TOTAL_WORKING_HR
        FROM HRIS_SHIFTS S
        WHERE S.SHIFT_ID=V_SHIFT_ID
        );
      --
      IF V_ATTENDANCE_TIME < V_HALF_INTERVAL THEN
        V_LATE_START_TIME :=V_LATE_START_TIME-1;
        V_ATTENDANCE_DT   :=V_ATTENDANCE_DT  -1;
        SELECT OVERALL_STATUS,
          LATE_STATUS,
          HALFDAY_FLAG,
          HALFDAY_PERIOD,
          GRACE_PERIOD,
          IN_TIME,
          HALFDAY_PERIOD,
          GRACE_PERIOD
        INTO V_OVERALL_STATUS,
          V_LATE_STATUS,
          V_HALFDAY_FLAG,
          V_HALFDAY_PERIOD,
          V_GRACE_PERIOD,
          V_IN_TIME,
          V_HALFDAY_PERIOD,
          V_GRACE_PERIOD
        FROM HRIS_ATTENDANCE_DETAIL
        WHERE ATTENDANCE_DT = TRUNC(V_ATTENDANCE_DT)
        AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
      ELSE
        V_EARLY_END_TIME:=V_EARLY_END_TIME+1;
      END IF;
      --
    END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Attendance Job for '||V_ATTENDANCE_DT||' not excecuted');
    RETURN;
  END;
  --
  BEGIN
    IF V_HALFDAY_PERIOD IS NOT NULL THEN
      SELECT LATE_IN,
        EARLY_OUT,
        TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
        ||' '
        ||TO_CHAR(LATE_START_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM'),
        TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
        ||' '
        ||TO_CHAR(EARLY_END_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM'),
        TOTAL_WORKING_HR
      INTO V_LATE_IN,
        V_EARLY_OUT,
        V_LATE_START_TIME,
        V_EARLY_END_TIME,
        V_TOTAL_WORKING_MIN
      FROM
        (SELECT S.LATE_IN,
          S.EARLY_OUT,
          (
          CASE
            WHEN V_HALFDAY_PERIOD ='F'
            THEN S.HALF_DAY_IN_TIME
            ELSE S.START_TIME
          END ) +((1/1440)*NVL(S.LATE_IN,0)) AS LATE_START_TIME,
          (
          CASE
            WHEN V_HALFDAY_PERIOD ='F'
            THEN S.END_TIME
            ELSE S.HALF_DAY_OUT_TIME
          END ) -((1/1440)*NVL(S.EARLY_OUT,0)) AS EARLY_END_TIME,
          S.TOTAL_WORKING_HR
        FROM HRIS_SHIFTS S
        WHERE S.SHIFT_ID =V_SHIFT_ID
        );
    ELSIF V_GRACE_PERIOD IS NOT NULL THEN
      SELECT LATE_IN,
        EARLY_OUT,
        TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
        ||' '
        ||TO_CHAR(LATE_START_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM'),
        TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
        ||' '
        ||TO_CHAR(EARLY_END_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM'),
        TOTAL_WORKING_HR
      INTO V_LATE_IN,
        V_EARLY_OUT,
        V_LATE_START_TIME,
        V_EARLY_END_TIME,
        V_TOTAL_WORKING_MIN
      FROM
        (SELECT S.LATE_IN,
          S.EARLY_OUT,
          (
          CASE
            WHEN V_GRACE_PERIOD ='E'
            THEN S.GRACE_START_TIME
            ELSE S.START_TIME
          END) +((1/1440)*NVL(S.LATE_IN,0)) AS LATE_START_TIME,
          (
          CASE
            WHEN V_GRACE_PERIOD ='E'
            THEN S.END_TIME
            ELSE S.GRACE_END_TIME
          END) -((1/1440)*NVL(S.EARLY_OUT,0)) AS EARLY_END_TIME,
          S.TOTAL_WORKING_HR
        FROM HRIS_SHIFTS S
        WHERE S.SHIFT_ID=V_SHIFT_ID
        );
    END IF;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20344, 'SHIFT WITH SHIFT_ID => '|| V_SHIFT_ID ||' NOT FOUND.');
  END;
  --   CHECK FOR ADJUSTED SHIFT
  BEGIN
    SELECT TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
      ||' '
      ||TO_CHAR(LATE_START_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM'),
      TO_DATE(TO_CHAR(V_ATTENDANCE_DT,'DD-MON-YYYY')
      ||' '
      ||TO_CHAR(EARLY_END_TIME,'HH:MI AM'),'DD-MON-YYYY HH:MI AM')
    INTO V_LATE_START_TIME,
      V_EARLY_END_TIME
    FROM
      (SELECT SA.START_TIME + ((1/1440)*NVL(V_LATE_IN,0))  AS LATE_START_TIME,
        SA.END_TIME         -((1/1440)*NVL(V_EARLY_OUT,0)) AS EARLY_END_TIME
      FROM HRIS_SHIFT_ADJUSTMENT SA
      JOIN HRIS_EMPLOYEE_SHIFT_ADJUSTMENT ESA
      ON (SA.ADJUSTMENT_ID=ESA.ADJUSTMENT_ID)
      WHERE (TRUNC(V_ATTENDANCE_DT) BETWEEN TRUNC(SA.ADJUSTMENT_START_DATE) AND TRUNC(SA.ADJUSTMENT_END_DATE) )
      AND ESA.EMPLOYEE_ID =P_EMPLOYEE_ID
      );
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NO ADJUSTMENT FOUND FOR EMPLOYEE =>'|| P_EMPLOYEE_ID || 'ON THE DATE'||V_ATTENDANCE_DT);
  END;
  --      END FOR CHECK FOR ADJUSTED_SHIFT
  IF (V_IN_TIME            IS NULL) THEN
    IF(V_OVERALL_STATUS     ='DO') THEN
      V_OVERALL_STATUS     :='WD';
    ELSIF (V_OVERALL_STATUS ='HD') THEN
      V_OVERALL_STATUS     :='WH';
    ELSIF (V_OVERALL_STATUS ='LV') THEN
      IF(V_HALFDAY_FLAG    !='Y' AND V_HALFDAY_PERIOD IS NOT NULL) OR V_GRACE_PERIOD IS NOT NULL THEN
        V_OVERALL_STATUS   :='LP';
      END IF;
    ELSIF (V_OVERALL_STATUS ='TV') THEN
      NULL;
    ELSIF (V_OVERALL_STATUS ='TN') THEN
      NULL;
    ELSE
      V_OVERALL_STATUS :='PR';
    END IF;
    IF(V_ATTENDANCE_DT < TRUNC(SYSDATE)) THEN
      V_LATE_STATUS   :='X';
    END IF;
    IF V_OVERALL_STATUS  = 'PR' AND V_LATE_START_TIME<V_ATTENDANCE_TIME THEN
      IF(V_ATTENDANCE_DT < TRUNC(SYSDATE)) THEN
        V_LATE_STATUS   :='Y';
      ELSE
        V_LATE_STATUS :='L';
      END IF;
    END IF;
    --
    UPDATE HRIS_ATTENDANCE_DETAIL
    SET IN_TIME         = TO_DATE ( TO_CHAR (V_ATTENDANCE_TIME, 'DD-MON-YY HH:MI AM'), 'DD-MON-YY HH:MI AM'),
      OVERALL_STATUS    = V_OVERALL_STATUS,
      LATE_STATUS       = V_LATE_STATUS,
      IN_REMARKS        = P_REMARKS
    WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
    AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
    RETURN;
  END IF;
  --
  IF V_OVERALL_STATUS ='PR' AND V_EARLY_END_TIME>V_ATTENDANCE_TIME THEN
    IF (V_LATE_STATUS IN ('L','Y')) THEN
      V_LATE_STATUS :='B';
    ELSE
      V_LATE_STATUS :='E';
    END IF;
  ELSE
    IF (V_LATE_STATUS     ='B') THEN
      V_LATE_STATUS      :='L';
    ELSIF ( V_LATE_STATUS ='E') THEN
      V_LATE_STATUS      := 'N';
    ELSIF ( V_LATE_STATUS ='X') THEN
      V_LATE_STATUS      := 'N';
    ELSIF ( V_LATE_STATUS ='Y') THEN
      V_LATE_STATUS      := 'L';
    END IF;
  END IF;
  --
  SELECT SUM(ABS(EXTRACT( HOUR FROM DIFF ))*60 + ABS(EXTRACT( MINUTE FROM DIFF )))
  INTO V_TOTAL_HOUR
  FROM
    (SELECT (V_ATTENDANCE_TIME-V_IN_TIME) AS DIFF FROM DUAL
    ) ;
  IF(V_TOTAL_HOUR<=5) THEN
    RETURN;
  END IF;
  UPDATE HRIS_ATTENDANCE_DETAIL
  SET OUT_TIME        = V_ATTENDANCE_TIME,
    LATE_STATUS       =V_LATE_STATUS,
    OUT_REMARKS       = P_REMARKS,
    TOTAL_HOUR        =V_TOTAL_HOUR,
    OT_MINUTES        =(V_TOTAL_HOUR- V_TOTAL_WORKING_MIN)
  WHERE ATTENDANCE_DT = V_ATTENDANCE_DT
  AND EMPLOYEE_ID     = P_EMPLOYEE_ID;
END;