create or replace PROCEDURE HRIS_OT_MANUAL_CR_OR_UP(
    V_MONTH_ID HRIS_MONTH_CODE.MONTH_ID%TYPE ,
    V_MONTH_DAY NUMBER ,
    V_EMPLOYEE_ID HRIS_OVERTIME_MANUAL.EMPLOYEE_ID%TYPE ,
    V_OVERTIME_HOUR HRIS_OVERTIME_MANUAL.OVERTIME_HOUR%TYPE)
AS
  V_ATTENDANCE_DATE HRIS_OVERTIME_MANUAL.ATTENDANCE_DATE%TYPE;
  V_ROW_COUNT NUMBER;
  V_ATTENDANCE_DETAIL_COUNT NUMBER;
  V_FOOD_ALLOWANCE NUMBER;

BEGIN
  SELECT FROM_DATE+V_MONTH_DAY -1
  INTO V_ATTENDANCE_DATE
  FROM HRIS_MONTH_CODE
  WHERE MONTH_ID =V_MONTH_ID;
  --
  SELECT COUNT(*)
  INTO V_ROW_COUNT
  FROM HRIS_OVERTIME_MANUAL
  WHERE ATTENDANCE_DATE =V_ATTENDANCE_DATE
  AND EMPLOYEE_ID       = V_EMPLOYEE_ID;
  IF (V_ROW_COUNT       >0 ) THEN
    UPDATE HRIS_OVERTIME_MANUAL
    SET OVERTIME_HOUR     =V_OVERTIME_HOUR
    WHERE ATTENDANCE_DATE =V_ATTENDANCE_DATE
    AND EMPLOYEE_ID       = V_EMPLOYEE_ID ;
  ELSE
    INSERT
    INTO HRIS_OVERTIME_MANUAL
      (
        ATTENDANCE_DATE,
        EMPLOYEE_ID,
        OVERTIME_HOUR
      )
      VALUES
      (
        V_ATTENDANCE_DATE,
        V_EMPLOYEE_ID,
        V_OVERTIME_HOUR
      );
  END IF;

  -- TO AFFECT FOOD_ALLOWANCE START
BEGIN
SELECT COUNT(*) INTO V_ATTENDANCE_DETAIL_COUNT  FROM HRIS_ATTENDANCE_DETAIL WHERE EMPLOYEE_ID=V_EMPLOYEE_ID AND ATTENDANCE_DT=V_ATTENDANCE_DATE 
AND IN_TIME IS NOT NULL AND OUT_TIME IS NOT NULL;


IF(V_ATTENDANCE_DETAIL_COUNT>0 AND V_OVERTIME_HOUR>=0)
THEN 
BEGIN
SELECT CASE 
             WHEN V_OVERTIME_HOUR>= 2 AND V_OVERTIME_HOUR < 4
            THEN 1
            WHEN V_OVERTIME_HOUR>=4
            THEN 2
            ELSE
            0
            END INTO V_FOOD_ALLOWANCE FROM DUAL;

UPDATE HRIS_ATTENDANCE_DETAIL SET FOOD_ALLOWANCE=V_FOOD_ALLOWANCE WHERE EMPLOYEE_ID=V_EMPLOYEE_ID AND ATTENDANCE_DT=V_ATTENDANCE_DATE ;
END;
END IF;
END;
  -- TO AFFECT FOOD_ALLOWANCE END
END;
