FUNCTION PERIOD_NEXT(p_period NUMBER, p_date DATE := trunc(sysdate), p_duration NUMBER := 1) RETURN DATE IS
  v_res DATE;
  v_period BM_CNT_PERIOD%ROWTYPE;
BEGIN
  BEGIN
    select * into v_period from BM_CNT_PERIOD where CNT_PERIOD_ID = p_period;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  END;
  v_res := trunc(p_date);
  IF (v_period.ADJUST IS NULL) THEN v_period.ADJUST := 0; END IF;
  IF (v_period.ADJUST > 0) THEN
    v_res :=
    CASE v_period.UNIT
      WHEN 'D' THEN v_res + p_duration*v_period.VALUE
      WHEN 'W' THEN trunc(v_res,'IW') + 7*p_duration*v_period.VALUE + v_period.ADJUST - 1
      WHEN 'M' THEN ADD_MONTHS(trunc(v_res,'MM'), p_duration*v_period.VALUE) + v_period.ADJUST - 1
      WHEN 'Y' THEN ADD_MONTHS(trunc(v_res,'YYYY'), 12*p_duration*v_period.VALUE) + v_period.ADJUST - 1
    END;
  ELSE
    v_res :=
    CASE v_period.UNIT
      WHEN 'D' THEN v_res + p_duration*v_period.VALUE
      WHEN 'W' THEN v_res + 7*p_duration*v_period.VALUE
      WHEN 'M' THEN ADD_MONTHS(v_res, p_duration*v_period.VALUE)
      WHEN 'Y' THEN ADD_MONTHS(v_res, 12*p_duration*v_period.VALUE)
    END;
  END IF;
  RETURN v_res;
END;
FUNCTION PERIOD_NEXT(p_unit BM_CNT_PERIOD.UNIT%TYPE, p_value BM_CNT_PERIOD.VALUE%TYPE, p_adjust BM_CNT_PERIOD.ADJUST%TYPE, p_date DATE := trunc(sysdate), p_duration NUMBER := 1) RETURN DATE IS
  v_res DATE;
BEGIN
  v_res := trunc(p_date);
  IF ((p_adjust IS NULL) OR (p_adjust = 0)) THEN
    v_res :=
    CASE p_unit
      WHEN 'D' THEN v_res + p_duration*p_value
      WHEN 'W' THEN v_res + 7*p_duration*p_value
      WHEN 'M' THEN ADD_MONTHS(v_res, p_duration*p_value)
      WHEN 'Y' THEN ADD_MONTHS(v_res, 12*p_duration*p_value)
    END;
  ELSE
    v_res :=
    CASE p_unit
      WHEN 'D' THEN v_res + p_duration*p_value
      WHEN 'W' THEN trunc(v_res,'IW') + 7*p_duration*p_value + p_adjust - 1
      WHEN 'M' THEN ADD_MONTHS(trunc(v_res,'MM'), p_duration*p_value) + p_adjust - 1
      WHEN 'Y' THEN ADD_MONTHS(trunc(v_res,'YYYY'), 12*p_duration*p_value) + p_adjust - 1
    END;
  END IF;
  RETURN v_res;
END;

select T.TARIFF_ID, T.TARIFF
--, PERIOD_NEXT(T.PERIOD, sysdate, nvl(T.DURATION,1)) as N1
--, PERIOD_NEXT(P.UNIT, P.VALUE, P.ADJUST, sysdate, nvl(T.DURATION,1)) as N2
from V_TARIFF T
join BM_CNT_PERIOD P on (P.CNT_PERIOD_ID = T.FEE_PERIOD)