SELECT DISTINCT ATTENDANCE_DT
FROM HRIS_ATTENDANCE_TEMP
ORDER BY ATTENDANCE_DT DESC;
SELECT * FROM HRIS_ATTENDANCE ORDER BY ATTENDANCE_DT DESC;
SELECT *
FROM HRIS_ATTENDANCE_DETAIL
WHERE IN_TIME IS NOT NULL
ORDER BY ATTENDANCE_DT DESC;
CREATE TABLE HRIS_ATTENDANCE_DETAIL_BK AS
SELECT * FROM HRIS_ATTENDANCE_DETAIL;
SELECT * FROM HRIS_ATTD_DEVICE_MASTER;
SELECT * FROM HRIS_ATTENDANCE WHERE ATTENDANCE_DT=TRUNC(SYSDATE);
CREATE TABLE HRIS_ATTD_TMP
  (THUMB_ID NUMBER, ATTENDANCE_TIME VARCHAR2(50)
  );
SELECT * FROM HRIS_ATTD_TMP;
CREATE TABLE HRIS_TEST
  (TEST_COL VARCHAR2(100 BYTE)
  );
--
SELECT *
FROM HRIS_ATTD_TMP;
--
DECLARE
  V_MONTH       VARCHAR2(10 BYTE);
  V_DAY         VARCHAR2(10 BYTE);
  V_YEAR        VARCHAR2(10 BYTE);
  V_HOUR        VARCHAR2(10 BYTE);
  V_MIN         VARCHAR2(10 BYTE);
  V_DATE        VARCHAR2(20 BYTE);
  V_DATE_TIME   VARCHAR2(20 BYTE);
  V_EMPLOYEE_ID NUMBER;
BEGIN
  FOR ATTD IN
  (SELECT THUMB_ID,
    REGEXP_SUBSTR (ATTENDANCE_TIME, '[^ ]+', 1, 1) AS V_DATE,
    REGEXP_SUBSTR (ATTENDANCE_TIME, '[^ ]+', 1, 2) AS V_TIME
  FROM HRIS_ATTD_TMP
  )
  LOOP
    BEGIN
      SELECT EMPLOYEE_ID
      INTO V_EMPLOYEE_ID
      FROM HRIS_EMPLOYEES
      WHERE ID_THUMB_ID= ATTD.THUMB_ID;
      --
      V_MONTH     := REGEXP_SUBSTR (ATTD.V_DATE, '[^/]+', 1, 1);
      V_DAY       := REGEXP_SUBSTR (ATTD.V_DATE, '[^/]+', 1, 2);
      V_YEAR      := REGEXP_SUBSTR (ATTD.V_DATE, '[^/]+', 1, 3);
      V_DATE      := V_DAY||'/'||TO_CHAR(V_MONTH, 'fm09')||'/'||V_YEAR;
      V_HOUR      := REGEXP_SUBSTR (ATTD.V_TIME, '[^:]+', 1, 1);
      V_MIN       := REGEXP_SUBSTR (ATTD.V_TIME, '[^:]+', 1, 2);
      V_DATE_TIME := V_DATE||' '||V_HOUR||':'||V_MIN;
      --
      INSERT
      INTO HRIS_ATTENDANCE
        (
          EMPLOYEE_ID,
          ATTENDANCE_DT,
          ATTENDANCE_TIME
        )
        VALUES
        (
          V_EMPLOYEE_ID,
          TO_DATE(V_DATE,'DD/MM/YYYY'),
          TO_DATE(V_DATE_TIME,'DD/MM/YYYY HH24:MI')
        );
    EXCEPTION
    WHEN no_data_found THEN
      NULL;
    END;
  END LOOP;
END;
--
TRUNCATE TABLE HRIS_TEST;
SELECT * FROM HRIS_TEST;
SELECT TO_DATE(TEST_COL,'DD/MM/YYYY HH24:MI') FROM HRIS_TEST;
SELECT TO_CHAR(6, 'fm09') FROM dual;
DESC HRIS_ATTENDANCE;


SELECT * FROM HRIS_ATTENDANCE ORDER BY ATTENDANCE_DT DESC;

BEGIN
  HRIS_REATTENDANCE('16-JUL-17');
END;