[Oracle]Quantity cannot be greater than original ordered quantity.

今天User建立一筆RMA訂單出現錯誤訊息，Quantity cannot be greater than original ordered quantity. 但是原始訂單數量與RMA訂單數量相同，卻出現錯誤 後來經由同事提醒，有可能該訂單已經被重覆做過RMA造成，經過查詢後果然沒錯 所以當出現Quantity cannot be greater than original ordered quantity. 可能需要排除的問題

1. RMA數量大於原始訂單數量
2. RMA已經被沖銷過造成數量不足

DECLARE
  P_COUNT        NUMBER;
  P_LINE_ID      VARCHAR2(50);
  P_ORDER_NUMBER VARCHAR2(50);
BEGIN
  P_LINE_ID := 1959631; --原始訂單
  SELECT COUNT(*)
    INTO P_COUNT
    FROM OE_ORDER_HEADERS_ALL OOHA, OE_ORDER_LINES_ALL OOLA
   WHERE 1 = 1
     AND OOHA.HEADER_ID = OOLA.HEADER_ID
     AND OOLA.REFERENCE_LINE_ID = P_LINE_ID;

  SELECT OOHA.ORDER_NUMBER
    INTO P_ORDER_NUMBER
    FROM OE_ORDER_HEADERS_ALL OOHA, OE_ORDER_LINES_ALL OOLA
   WHERE 1 = 1
     AND OOHA.HEADER_ID = OOLA.HEADER_ID
     AND OOLA.REFERENCE_LINE_ID = P_LINE_ID
   ORDER BY OOHA.ORDERED_DATE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
  
    IF P_COUNT > 1 THEN
      DBMS_OUTPUT.PUT_LINE('RMA已經被||P_ORDER_NUMBER||沖銷');
    ELSE
      DBMS_OUTPUT.PUT_LINE('正常');
    END IF;
END;