create or replace PROCEDURE HRIS_WOH_REWARD
  (
    P_ID HRIS_EMPLOYEE_WORK_HOLIDAY.ID%TYPE
  )
AS
  V_EMPLOYEE_ID HRIS_EMPLOYEE_WORK_HOLIDAY.EMPLOYEE_ID%TYPE;
  V_WOH_FLAG HRIS_POSITIONS.WOH_FLAG%TYPE;
  V_FROM_DATE HRIS_EMPLOYEE_WORK_HOLIDAY.FROM_DATE%TYPE;
  V_TO_DATE HRIS_EMPLOYEE_WORK_HOLIDAY.TO_DATE%TYPE;
BEGIN
  SELECT EMPLOYEE_ID,
    TRUNC( FROM_DATE),
    TRUNC(TO_DATE)
  INTO V_EMPLOYEE_ID,
    V_FROM_DATE,
    V_TO_DATE
  FROM HRIS_EMPLOYEE_WORK_HOLIDAY
  WHERE ID    = P_ID;
  IF(V_TO_DATE>TRUNC(SYSDATE)) THEN
    RETURN;
  END IF;
  --
  
   -- check if employeewise reward type set in  start
  BEGIN
  SELECT WOH_FLAG INTO V_WOH_FLAG FROM HRIS_EMPLOYEES WHERE EMPLOYEE_ID=V_EMPLOYEE_ID;
   EXCEPTION
  WHEN no_data_found THEN
    NULL;
  END;
  
  -- check if employeewise reward type set in  end
  
  
  -- select employee reward type in  position and set in variable  if not found terminate
  IF(V_WOH_FLAG IS NULL OR (V_WOH_FLAG!='O' AND V_WOH_FLAG!='L'))
  THEN
  BEGIN
    SELECT P.WOH_FLAG
    INTO V_WOH_FLAG
    FROM HRIS_EMPLOYEES E
    JOIN HRIS_POSITIONS P
    ON (E.POSITION_ID   = P.POSITION_ID)
    WHERE E.EMPLOYEE_ID =V_EMPLOYEE_ID;
  EXCEPTION
  WHEN no_data_found THEN
    HRIS_RAISE_ERR(V_EMPLOYEE_ID,'Work on dayoff reward could not be given.','Employee position is not set');
  END;
  END IF;
  --
DELETE FROM HRIS_EMPLOYEE_LEAVE_ADDITION WHERE WOH_ID=P_ID;
       DELETE FROM HRIS_OVERTIME_DETAIL WHERE WOH_ID= P_ID;
      DELETE FROM HRIS_OVERTIME WHERE WOH_ID = P_ID;
  IF V_WOH_FLAG ='L' THEN
    HRIS_WOH_LEAVE_ADDITION(P_ID);
  ELSIF V_WOH_FLAG ='O' THEN
    HRIS_WOH_OT_ADDITION(P_ID);
  END IF;
END;