CREATE OR REPLACE PROCEDURE HRIS_COMPULSORY_OT_CANCEL(
    P_COMPULSORY_OT_ID HRIS_COMPULSORY_OVERTIME.COMPULSORY_OVERTIME_ID%TYPE)
AS
  V_START_DATE DATE;
  V_END_DATE   DATE;
  V_DATE_DIFF  NUMBER;
BEGIN
  SELECT START_DATE,
    END_DATE,
    (TRUNC(END_DATE)-TRUNC(START_DATE))
  INTO V_START_DATE,
    V_END_DATE,
    V_DATE_DIFF
  FROM HRIS_COMPULSORY_OVERTIME
  WHERE COMPULSORY_OVERTIME_ID = P_COMPULSORY_OT_ID;
  FOR i IN 0..V_DATE_DIFF
  LOOP
    FOR ot IN
    (SELECT *
    FROM HRIS_OVERTIME
    WHERE DESCRIPTION = 'THIS IS AUTOGENERATED OT REQUEST FROM COMPULSORY OT.'
    AND OVERTIME_DATE = TRUNC(V_START_DATE+ i)
    AND EMPLOYEE_ID                      IN
      (SELECT EMPLOYEE_ID
      FROM HRIS_EMPLOYEE_COMPULSORY_OT
      WHERE COMPULSORY_OVERTIME_ID = P_COMPULSORY_OT_ID
      )
    )
    LOOP
      DELETE FROM HRIS_OVERTIME_DETAIL WHERE OVERTIME_ID = ot.OVERTIME_ID;
      --
      DELETE FROM HRIS_OVERTIME WHERE OVERTIME_ID = ot.OVERTIME_ID;
    END LOOP;
  END LOOP;
  UPDATE HRIS_COMPULSORY_OVERTIME
  SET STATUS                   ='D'
  WHERE COMPULSORY_OVERTIME_ID = P_COMPULSORY_OT_ID;
END;
