Aloz 的 Oracle ERP 天地

當 User 操作 RMA 單時, 選擇了 Reference 後, Book order 會發生 "Quantity cannot be greater than original quantity" 的錯誤訊息. 不過, 標準畫面很難查的出來是哪一張 RMA 單 Reference 了同一張單子, 所以無法處理.

SELECT OOH1.ORDER_NUMBER A1
     , OOL1.ORDERED_ITEM A2
     , OOL1.LINE_NUMBER A3
     , OOL1.ORDERED_QUANTITY A4
     , OOH1.FLOW_STATUS_CODE A5
     , OOH2.ORDER_NUMBER B1
     , OOL2.LINE_NUMBER B1
     , OOL2.ORDERED_QUANTITY B3
     , OOH2.FLOW_STATUS_CODE B4
     , OOH3.ORDER_NUMBER C1
     , OOL3.LINE_NUMBER C2
     , OOL3.ORDERED_QUANTITY C3
     , OOH3.FLOW_STATUS_CODE C4
   FROM ONT.OE_ORDER_HEADERS_ALL OOH1
      , ONT.OE_ORDER_LINES_ALL OOL1
      , ONT.OE_ORDER_LINES_ALL OOL2
      , ONT.OE_ORDER_HEADERS_ALL OOH2
      , ONT.OE_ORDER_LINES_ALL OOL3
      , ONT.OE_ORDER_HEADERS_ALL OOH3
  WHERE OOH1.HEADER_ID = OOL1.HEADER_ID
    AND OOL1.LINE_CATEGORY_CODE = 'RETURN'
    AND OOH1.ORDER_NUMBER = '當前的訂單編號'
    AND OOL1.REFERENCE_LINE_ID = OOL2.LINE_ID
    AND OOH2.HEADER_ID = OOL2.HEADER_ID
    AND OOL1.REFERENCE_LINE_ID = OOL3.REFERENCE_LINE_ID(+)
    AND OOH3.HEADER_ID(+) = OOL3.HEADER_ID
    AND OOH1.ORDER_NUMBER <> OOH3.ORDER_NUMBER