create or replace PROCEDURE HRIS_WOH_LEAVE_ADDITION(
    P_WOH_ID HRIS_EMPLOYEE_WORK_HOLIDAY.ID%TYPE )
AS
  V_EMPLOYEE_ID HRIS_EMPLOYEE_WORK_HOLIDAY.EMPLOYEE_ID%TYPE;
  V_FROM_DATE HRIS_EMPLOYEE_WORK_HOLIDAY.FROM_DATE%TYPE;
  V_TO_DATE HRIS_EMPLOYEE_WORK_HOLIDAY.TO_DATE%TYPE;
  V_DURATION NUMBER;
  P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE;
  --
  V_TOTAL_HOUR HRIS_ATTENDANCE_DETAIL.TOTAL_HOUR%TYPE;
  --
  V_SUBSTITUTE_LEAVE_ID HRIS_LEAVE_MASTER_SETUP.LEAVE_ID%TYPE;
  V_BALANCE HRIS_EMPLOYEE_LEAVE_ASSIGN.BALANCE%TYPE;
  V_INCREMENT_DAY FLOAT    :=0;
  V_ON_TRAVEL CHAR(1 BYTE) :='N';
  V_SHIFT_ID  NUMBER(7,0);
BEGIN
  SELECT LEAVE_ID
  INTO V_SUBSTITUTE_LEAVE_ID
  FROM HRIS_LEAVE_MASTER_SETUP
  WHERE STATUS='E' AND IS_SUBSTITUTE='Y'
  AND ROWNUM         = 1;
  --
  SELECT FROM_DATE,
    TO_DATE,
    TRUNC(TO_DATE)-TRUNC(FROM_DATE),
    EMPLOYEE_ID,
    APPROVED_BY
  INTO V_FROM_DATE,
    V_TO_DATE,
    V_DURATION,
    V_EMPLOYEE_ID,
    P_EMPLOYEE_ID
  FROM HRIS_EMPLOYEE_WORK_HOLIDAY
  WHERE ID= P_WOH_ID AND STATUS = 'AP' ;
  --
  BEGIN
    SELECT BALANCE
    INTO V_BALANCE
    FROM HRIS_EMPLOYEE_LEAVE_ASSIGN
    WHERE EMPLOYEE_ID=V_EMPLOYEE_ID
    AND LEAVE_ID     = V_SUBSTITUTE_LEAVE_ID;
  EXCEPTION
  WHEN no_data_found THEN
    INSERT
    INTO HRIS_EMPLOYEE_LEAVE_ASSIGN
      (
        EMPLOYEE_ID,
        LEAVE_ID,
        PREVIOUS_YEAR_BAL,
        TOTAL_DAYS,
        BALANCE,
        CREATED_DT,
        CREATED_BY
      )
      VALUES
      (
        V_EMPLOYEE_ID,
        V_SUBSTITUTE_LEAVE_ID,
        0,
        0,
        0,
        TRUNC(SYSDATE),
        P_EMPLOYEE_ID
      );
  END;
  --
  FOR i IN 0..V_DURATION
  LOOP
    BEGIN
      SELECT TOTAL_HOUR,
        (
        CASE
          WHEN TRAVEL_ID IS NOT NULL
          THEN 'Y'
          ELSE 'N'
        END),
        SHIFT_ID
      INTO V_TOTAL_HOUR,
        V_ON_TRAVEL,
        V_SHIFT_ID
      FROM HRIS_ATTENDANCE_DETAIL
      WHERE EMPLOYEE_ID= V_EMPLOYEE_ID
      AND ATTENDANCE_DT= TRUNC(V_FROM_DATE)+i;
    EXCEPTION
    WHEN no_data_found THEN
      CONTINUE;
    END;
    --
    IF V_ON_TRAVEL = 'Y' THEN
      SELECT TOTAL_WORKING_HR
      INTO V_TOTAL_HOUR
      FROM HRIS_SHIFTS
      WHERE SHIFT_ID = V_SHIFT_ID;
    END IF;
    --
    IF((V_TOTAL_HOUR /60) >= 2 AND (V_TOTAL_HOUR /60) < 4) THEN
      V_INCREMENT_DAY         :=(V_INCREMENT_DAY+.5);
    ELSIF ((V_TOTAL_HOUR /60) >=4) THEN
      V_INCREMENT_DAY         :=(V_INCREMENT_DAY+1);
    ELSIF (V_TOTAL_HOUR <= 0 OR V_TOTAL_HOUR is NULL) THEN
      V_INCREMENT_DAY         :=V_INCREMENT_DAY+1;
    END IF;
  END LOOP;
--
  INSERT
  INTO HRIS_EMPLOYEE_LEAVE_ADDITION
    (
      EMPLOYEE_ID,
      LEAVE_ID,
      NO_OF_DAYS,
      REMARKS,
      CREATED_DATE,
      WOD_ID,
      WOH_ID
    )
    VALUES
    (
      V_EMPLOYEE_ID,
      V_SUBSTITUTE_LEAVE_ID,
      V_INCREMENT_DAY,
      'WOH REWARD',
      TRUNC(SYSDATE),
      NULL,
      P_WOH_ID
    );
END;