CREATE OR REPLACE PROCEDURE HRIS_LATE_LEAVE_DEDUCTION(
    P_COMPANY_ID HRIS_PENALIZED_MONTHS.COMPANY_ID%TYPE,
    P_FISCAL_YEAR_ID HRIS_PENALIZED_MONTHS.FISCAL_YEAR_ID%TYPE,
    P_FISCAL_YEAR_MONTH_NO HRIS_PENALIZED_MONTHS.FISCAL_YEAR_MONTH_NO%TYPE,
    P_DEDUCTION_DAY FLOAT,
    P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE,
    P_ACTION CHAR)
AS
  V_MONTH_START_DATE HRIS_MONTH_CODE.FROM_DATE%TYPE;
  V_MONTH_END_DATE HRIS_MONTH_CODE.TO_DATE %TYPE;
  V_LEAVE_ID HRIS_LEAVE_MASTER_SETUP.LEAVE_ID%TYPE;
  V_ALREADY_DEDUCTED_FLAG CHAR(1 BYTE);
BEGIN
  SELECT FROM_DATE,
    TO_DATE
  INTO V_MONTH_START_DATE,
    V_MONTH_END_DATE
  FROM HRIS_MONTH_CODE
  WHERE FISCAL_YEAR_ID    = P_FISCAL_YEAR_ID
  AND FISCAL_YEAR_MONTH_NO=P_FISCAL_YEAR_MONTH_NO;
  --
  DELETE
  FROM HRIS_EMPLOYEE_PENALTY_DAYS
  WHERE (TRUNC(ATTENDANCE_DT) BETWEEN V_MONTH_START_DATE AND V_MONTH_END_DATE)
  AND EMPLOYEE_ID IN
    (SELECT EMPLOYEE_ID
    FROM HRIS_EMPLOYEES
    WHERE HRIS_EMPLOYEES.COMPANY_ID=P_COMPANY_ID
    );
  IF(P_ACTION ='D') THEN
    DELETE
    FROM HRIS_PENALIZED_MONTHS
    WHERE FISCAL_YEAR_ID     =P_FISCAL_YEAR_ID
    AND FISCAL_YEAR_MONTH_NO =P_FISCAL_YEAR_MONTH_NO
    AND COMPANY_ID           =P_COMPANY_ID;
    RETURN;
  END IF;
  --
  SELECT (
    CASE
      WHEN COUNT(*) >0
      THEN 'Y'
      ELSE 'N'
    END)
  INTO V_ALREADY_DEDUCTED_FLAG
  FROM HRIS_PENALIZED_MONTHS
  WHERE FISCAL_YEAR_ID     =P_FISCAL_YEAR_ID
  AND FISCAL_YEAR_MONTH_NO =P_FISCAL_YEAR_MONTH_NO
  AND COMPANY_ID           =P_COMPANY_ID;
  --
  IF V_ALREADY_DEDUCTED_FLAG ='Y' THEN
    UPDATE HRIS_PENALIZED_MONTHS
    SET NO_OF_DAYS           =P_DEDUCTION_DAY,
      MODIFIED_DATE          =TRUNC(SYSDATE),
      MODIFIED_BY            = P_EMPLOYEE_ID
    WHERE FISCAL_YEAR_ID     =P_FISCAL_YEAR_ID
    AND FISCAL_YEAR_MONTH_NO =P_FISCAL_YEAR_MONTH_NO
    AND COMPANY_ID           =P_COMPANY_ID;
  ELSE
    INSERT
    INTO HRIS_PENALIZED_MONTHS
      (
        FISCAL_YEAR_ID,
        FISCAL_YEAR_MONTH_NO,
        COMPANY_ID,
        NO_OF_DAYS,
        CREATED_DATE,
        CREATED_BY
      )
      VALUES
      (
        P_FISCAL_YEAR_ID,
        P_FISCAL_YEAR_MONTH_NO,
        P_COMPANY_ID,
        P_DEDUCTION_DAY,
        TRUNC(SYSDATE),
        P_EMPLOYEE_ID
      );
  END IF;
  --
  FOR attendance IN
  (SELECT         *
    FROM HRIS_ATTENDANCE_DETAIL
    WHERE (TRUNC(ATTENDANCE_DT) BETWEEN V_MONTH_START_DATE AND V_MONTH_END_DATE)
    AND OVERALL_STATUS IN ('BA','LA')
    AND EMPLOYEE_ID    IN
      (SELECT EMPLOYEE_ID
      FROM HRIS_EMPLOYEES
      WHERE HRIS_EMPLOYEES.COMPANY_ID=P_COMPANY_ID
      )
  )
  LOOP
    BEGIN
      SELECT LEAVE_ID
      INTO V_LEAVE_ID
      FROM
        (SELECT L.LEAVE_ID
        FROM HRIS_EMPLOYEE_LEAVE_ASSIGN LA
        JOIN HRIS_LEAVE_MASTER_SETUP L
        ON (LA.LEAVE_ID      =L.LEAVE_ID)
        WHERE LA.EMPLOYEE_ID =attendance.EMPLOYEE_ID
        AND LA.BALANCE      >=P_DEDUCTION_DAY
        ORDER BY L.DEDUCTION_PRIORITY_NO
        )
      WHERE ROWNUM=1;
    EXCEPTION
    WHEN no_data_found THEN
      SELECT LEAVE_ID
      INTO V_LEAVE_ID
      FROM
        (SELECT L.LEAVE_ID
        FROM HRIS_EMPLOYEE_LEAVE_ASSIGN LA
        JOIN HRIS_LEAVE_MASTER_SETUP L
        ON (LA.LEAVE_ID      =L.LEAVE_ID)
        WHERE LA.EMPLOYEE_ID =attendance.EMPLOYEE_ID
        ORDER BY L.DEDUCTION_PRIORITY_NO
        )
      WHERE ROWNUM=1;
    END;
    --
    INSERT
    INTO HRIS_EMPLOYEE_PENALTY_DAYS
      (
        EMPLOYEE_ID,
        ATTENDANCE_DT,
        LEAVE_ID,
        NO_OF_DAYS,
        REMARKS,
        CREATED_DATE
      )
      VALUES
      (
        attendance.EMPLOYEE_ID,
        attendance.ATTENDANCE_DT,
        V_LEAVE_ID,
        P_DEDUCTION_DAY,
        '4th day penalty',
        TRUNC(SYSDATE)
      );
  END LOOP;
END;