CREATE RPL PL/SQL STORED PROCEDURE

gistfile1.sql

CREATE OR REPLACE
PROCEDURE CREATE_RPL2(
    p_order_item_id IN ORDER_ITEM_PRICES.ORDER_ITEM_PRICE_ID%TYPE,
    p_rpl           IN ORDER_ITEM_PRICES.PRICE%TYPE,
    p_start_day     IN NUMBER,
    p_created_by    IN ORDER_ITEM_PRICES.CREATED_BY%TYPE)
IS
  V_ORDER_MONTH NUMBER;
  V_ORDER_YEAR  NUMBER;
  V_MONTH_END   NUMBER;
  V_START_DAY   NUMBER := 1;
  V_START_DATE DATE;
  V_END_DATE DATE;
  V_TMP VARCHAR2(100);
  V_RPL_REC ORDER_ITEM_PRICES%ROWTYPE;
  CURSOR RPL_CURSOR
  IS
    SELECT * FROM ORDER_ITEM_PRICES WHERE ORDER_ITEM_ID = p_order_item_id;
  START_DAY_OUT_OF_RANGE EXCEPTION;
  RPL_OUT_OF_RANGE       EXCEPTION;
BEGIN
  -- SETUP
  SAVEPOINT START_RPL;
  IF P_START_DAY IS NOT NULL THEN
    V_START_DAY  := P_START_DAY;
  END IF;
  -- GET ORDER MONTH, YEAR AND MONTH END
  SELECT C.MONTH_NUM_OF_YEAR,
    C.YEAR_ID,
    C.MONTH_TIME_SPAN
  INTO V_ORDER_MONTH,
    V_ORDER_YEAR,
    V_MONTH_END
  FROM DIM_CALENDAR C,
    ORDERS O,
    ORDER_ITEMS OI
  WHERE C.CALENDAR_DATE = O.START_DATE
  AND O.ORDER_ID        = OI.ORDER_ID
  AND OI.ORDER_ITEM_ID  = P_ORDER_ITEM_ID;
  -- VALIDATE INPUTS
  -- NOTE: EXISTENCE OF p_order_item_id VALIDATED IN SETUP
  IF p_start_day < 1 OR p_start_day > v_month_end THEN
    RAISE start_day_out_of_range;
  END IF;
  IF p_rpl IS NULL OR p_rpl < 0 THEN
    RAISE rpl_out_of_range;
  END IF;
  -- SET START AND END DATE
  SELECT TO_DATE(lpad(v_start_day,2,'0')
    || lpad(v_order_month,2,'0')
    || v_order_year, 'ddmmyyyy')
  INTO V_START_DATE
  FROM DUAL;
  SELECT TO_DATE(lpad(v_month_end,2,'0')
    || lpad(v_order_month,2,'0')
    || v_order_year, 'ddmmyyyy')
  INTO V_END_DATE
  FROM DUAL;
  -- HANDLE EXITING RPLS
  FOR V_RPL_REC IN rpl_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE(V_RPL_REC.START_DATE || ' ' || V_RPL_REC.END_DATE);
    IF TO_CHAR(v_rpl_rec.start_date, 'dd') >= v_start_day THEN
      DELETE
      FROM ORDER_ITEM_PRICES
      WHERE ORDER_ITEM_PRICE_ID = v_rpl_rec.ORDER_ITEM_PRICE_ID;
    ELSE
      IF TO_CHAR(v_rpl_rec.end_date, 'dd') >= v_start_day THEN
        UPDATE ORDER_ITEM_PRICES
        SET END_DATE              = (V_START_DATE - 1)
        WHERE ORDER_ITEM_PRICE_ID = V_RPL_REC.ORDER_ITEM_PRICE_ID;
      END IF;
    END IF;
  END LOOP;
  -- CREATE NEW RPL
  INSERT
  INTO ORDER_ITEM_PRICES
    (
      ORDER_ITEM_ID,
      PRICE,
      START_DATE,
      END_DATE,
      CREATED_BY
    )
    VALUES
    (
      P_ORDER_ITEM_ID,
      P_RPL,
      V_START_DATE,
      V_END_DATE,
      P_CREATED_BY
    );
EXCEPTION
WHEN NO_DATA_FOUND THEN
  ROLLBACK TO start_rpl;
  DBMS_OUTPUT.PUT_LINE('No such order item: ' || p_order_item_id);
WHEN start_day_out_of_range THEN
  ROLLBACK TO start_rpl;
  DBMS_OUTPUT.PUT_LINE('p_start_day is out of range 1 - ' || v_month_end);
WHEN rpl_out_of_range THEN
  ROLLBACK TO start_rpl;
  DBMS_OUTPUT.PUT_LINE('p_rpl is null or less than 0');
WHEN OTHERS THEN
  ROLLBACK TO start_rpl;
  RAISE;
END CREATE_RPL2;