create or replace FUNCTION HRIS_AVAILABLE_LEAVE_DAYS(
    P_START_DATE DATE,
    P_END_DATE   DATE,
    P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE,
    P_LEAVE_ID HRIS_LEAVE_MASTER_SETUP.LEAVE_ID%TYPE,    
    P_HALF_DAY HRIS_EMPLOYEE_LEAVE_REQUEST.HALF_DAY%TYPE:=NULL
    )
  RETURN NUMBER
AS
  V_WEEKDAY1 HRIS_SHIFTS.WEEKDAY1%TYPE;
  V_WEEKDAY2 HRIS_SHIFTS.WEEKDAY2%TYPE;
  V_WEEKDAY3 HRIS_SHIFTS.WEEKDAY3%TYPE;
  V_WEEKDAY4 HRIS_SHIFTS.WEEKDAY4%TYPE;
  V_WEEKDAY5 HRIS_SHIFTS.WEEKDAY5%TYPE;
  V_WEEKDAY6 HRIS_SHIFTS.WEEKDAY6%TYPE;
  V_WEEKDAY7 HRIS_SHIFTS.WEEKDAY7%TYPE;
  V_DATE_DIFF     NUMBER:= TRUNC(P_END_DATE)-TRUNC(P_START_DATE);
  V_WEEKDAY       NUMBER;
  V_DAYOFF_COUNT  NUMBER:=0;
  V_HOLIDAY_COUNT NUMBER:=0;
  V_HOLIDAY_ID    NUMBER:=NULL;
  V_INCLUDE_DAYOFF_AS_LEAVE HRIS_LEAVE_MASTER_SETUP.DAY_OFF_AS_LEAVE%TYPE;
  V_INCLUDE_HOLIDAY_AS_LEAVE HRIS_LEAVE_MASTER_SETUP.HOLIDAY_AS_LEAVE%TYPE;
BEGIN

  BEGIN
  SELECT DAY_OFF_AS_LEAVE,HOLIDAY_AS_LEAVE INTO
  V_INCLUDE_DAYOFF_AS_LEAVE,V_INCLUDE_HOLIDAY_AS_LEAVE
FROM HRIS_LEAVE_MASTER_SETUP 
WHERE LEAVE_ID=P_LEAVE_ID;

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    V_INCLUDE_DAYOFF_AS_LEAVE:='N';
    V_INCLUDE_HOLIDAY_AS_LEAVE:='N';
  END;
--
BEGIN
  SELECT S.WEEKDAY1,
    S.WEEKDAY2,
    S.WEEKDAY3,
    S.WEEKDAY4,
    S.WEEKDAY5,
    S.WEEKDAY6,
    S.WEEKDAY7
  INTO V_WEEKDAY1,
    V_WEEKDAY2,
    V_WEEKDAY3,
    V_WEEKDAY4,
    V_WEEKDAY5,
    V_WEEKDAY6,
    V_WEEKDAY7
  FROM HRIS_SHIFTS S
  JOIN HRIS_EMPLOYEE_SHIFT_ASSIGN SA
  ON (S.SHIFT_ID     =SA.SHIFT_ID)
  WHERE S.STATUS     ='E'
  AND SA.STATUS      ='E'
  AND SA.EMPLOYEE_ID = P_EMPLOYEE_ID
  AND ROWNUM         =1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  SELECT S.WEEKDAY1,
    S.WEEKDAY2,
    S.WEEKDAY3,
    S.WEEKDAY4,
    S.WEEKDAY5,
    S.WEEKDAY6,
    S.WEEKDAY7
  INTO V_WEEKDAY1,
    V_WEEKDAY2,
    V_WEEKDAY3,
    V_WEEKDAY4,
    V_WEEKDAY5,
    V_WEEKDAY6,
    V_WEEKDAY7
  FROM HRIS_SHIFTS S
  WHERE S.DEFAULT_SHIFT ='Y'
  AND STATUS            = 'E';
END;


FOR i IN 0..V_DATE_DIFF
LOOP
  SELECT TO_CHAR(TRUNC(P_START_DATE+i),'d') INTO V_WEEKDAY FROM DUAL;
  IF (V_WEEKDAY     = 1 AND V_WEEKDAY1 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 2 AND V_WEEKDAY2 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 3 AND V_WEEKDAY4 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 4 AND V_WEEKDAY4 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 5 AND V_WEEKDAY5 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 6 AND V_WEEKDAY6 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  IF (V_WEEKDAY     = 7 AND V_WEEKDAY7 = 'DAY_OFF') THEN
    V_DAYOFF_COUNT :=V_DAYOFF_COUNT+1;
    CONTINUE;
  END IF;
  BEGIN
    SELECT H.HOLIDAY_ID
    INTO V_HOLIDAY_ID
    FROM HRIS_HOLIDAY_MASTER_SETUP H
    JOIN HRIS_EMPLOYEE_HOLIDAY EH
    ON (H.HOLIDAY_ID  = EH.HOLIDAY_ID )
    WHERE H.STATUS    ='E'
    AND EH.EMPLOYEE_ID= P_EMPLOYEE_ID
    AND ((P_START_DATE               +i) BETWEEN H.START_DATE AND H.END_DATE);
    V_HOLIDAY_COUNT :=V_HOLIDAY_COUNT+1;
  EXCEPTION
  WHEN no_data_found THEN
    NULL;
  WHEN TOO_MANY_ROWS THEN
    V_HOLIDAY_COUNT :=V_HOLIDAY_COUNT+1;
  END;
END LOOP;
RETURN ((V_DATE_DIFF+1)-
(
  CASE
  WHEN V_INCLUDE_DAYOFF_AS_LEAVE ='N' THEN
    V_DAYOFF_COUNT
  ELSE
    0
  END)-
(
  CASE
  WHEN V_INCLUDE_HOLIDAY_AS_LEAVE='N' THEN
    V_HOLIDAY_COUNT
  ELSE
    0
  END))
--  /
--(
--  CASE
--  WHEN P_HALF_DAY IS NULL OR P_HALF_DAY ='N' THEN
--    1
--  ELSE
--    2
--  END)
;
END;