create or replace FUNCTION "HRIS_VALIDATE_LEAVE_REQUEST"(
  P_START_DATE HRIS_EMPLOYEE_LEAVE_REQUEST.START_DATE%TYPE,
  P_END_DATE HRIS_EMPLOYEE_LEAVE_REQUEST.END_DATE%TYPE,
  P_EMPLOYEE_ID HRIS_EMPLOYEE_LEAVE_REQUEST.EMPLOYEE_ID%TYPE)
RETURN VARCHAR2
AS
  V_MONTH_FROM_DATE      DATE;
  V_MONTH_TO_DATE        DATE;
  V_OVERLAPPING_LEAVE_NO NUMBER:=0;
  V_ENABLE_PREV_MTH_LEAVE_REQ HRIS_PREFERENCES.VALUE%TYPE;
  V_LEAVE_YEAR_END DATE;
BEGIN
  BEGIN
    SELECT VALUE
    INTO V_ENABLE_PREV_MTH_LEAVE_REQ
    FROM HRIS_PREFERENCES
    WHERE KEY = 'ENABLE_PREV_MTH_LEAVE_REQ';
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    V_ENABLE_PREV_MTH_LEAVE_REQ:='N';
  END;
  --
  IF V_ENABLE_PREV_MTH_LEAVE_REQ ='N' THEN
    SELECT FROM_DATE,
      TO_DATE
    INTO V_MONTH_FROM_DATE,
      V_MONTH_TO_DATE
    FROM HRIS_LEAVE_MONTH_CODE
    WHERE TRUNC(SYSDATE) BETWEEN FROM_DATE AND TO_DATE;
    --
    IF(TRUNC(P_START_DATE)<TRUNC(V_MONTH_FROM_DATE)) THEN
      RETURN 'Leave Request to previous month is not allowed.';
    END IF;
  END IF;
  --
  SELECT COUNT(*)
  INTO V_OVERLAPPING_LEAVE_NO
  FROM HRIS_EMPLOYEE_LEAVE_REQUEST
  WHERE (( START_DATE between P_START_DATE and P_END_DATE )
  OR ( END_DATE between P_START_DATE and P_END_DATE ))
  AND STATUS IN  ('RQ','RC','AP','CP','CR')
  AND EMPLOYEE_ID = P_EMPLOYEE_ID ;
  --
  IF(V_OVERLAPPING_LEAVE_NO >0) THEN
    RETURN 'Leave Request is overlapping other leave request.';
  END IF;
  
  BEGIN
  SELECT END_DATE INTO V_LEAVE_YEAR_END FROM HRIS_LEAVE_YEARS WHERE 
           P_START_DATE  BETWEEN START_DATE AND END_DATE;
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
      SELECT
        MAX(END_DATE)
        INTO
        V_LEAVE_YEAR_END
        FROM
        HRIS_LEAVE_YEARS;
    END;

           IF(P_END_DATE>V_LEAVE_YEAR_END) THEN
           RETURN 'You Are Requesting leave for next leave Year';
           END IF;

  
  RETURN NULL;
END;
