Oracle ERP R12. View for RECEIPT_NUM info.

因為常需要查詢某一收料單的最終數量狀態(例: RECEIVE又RETURN TO VENDOR..)
一般的做法就是去查Receiving Transactions Summary,看收多少退多少,再計算取餘量.
但IT在寫程式時若每次都要將該邏輯重覆寫入不同程式,則是一個不明智的做法,就在這想法下,Create了一個Function (用來取得shipment line id最終的qty),及view (用來快速取得最終數量)
=====================================================================
FUNCTION GET_SHIPMENT_LINE_TTL_QTY(I_SHIPMENT_HEADER_ID IN NUMBER,
                                     I_SHIPMENT_LINE      IN NUMBER,
                                     I_TXNTYPE            IN VARCHAR2)
    RETURN NUMBER IS
    /*
    When      Who      What
    ====================================
    20110803  WenShen  Created for 取得收料單最後數量
    */
    T_RESULT NUMBER;
  BEGIN
    SELECT SUM(DECODE(RT.TRANSACTION_TYPE,
                      'RETURN TO RECEIVING',
                      RT.QUANTITY * -1, --<<<<key point
                      'RETURN TO VENDOR',
                      RT.QUANTITY * -1,  --<<<<key point
                      RT.QUANTITY))
      INTO T_RESULT
      FROM RCV_TRANSACTIONS RT
     WHERE RT.SHIPMENT_HEADER_ID = I_SHIPMENT_HEADER_ID
       AND RT.SHIPMENT_LINE_ID = I_SHIPMENT_LINE
       AND DECODE(RT.TRANSACTION_TYPE,
                  'RETURN TO RECEIVING','DELIVER',  --<<<<key point
                  'RETURN TO VENDOR','RECEIVE',  --<<<<key point
                  RT.TRANSACTION_TYPE) = DECODE(I_TXNTYPE,
                                                'RETURN TO RECEIVING','DELIVER',
                                                'RETURN TO VENDOR','RECEIVE',
                                                I_TXNTYPE);
    --
    RETURN T_RESULT;
  END;

==============================================
CREATE OR REPLACE VIEW Z_RCV_RT_INFO_V AS
SELECT /*
       When     Who         What
       ==============================
       20110803 WenShen     Created for get RT No. finial qty
       */
       RT.SHIPMENT_LINE_ID,
       PHA.SEGMENT1 PO_NUMBER,
       PLA.LINE_NUM PO_LINE_NUMBER,
       RSH.RECEIPT_NUM,
       RT.TRANSACTION_TYPE,
       RT.ATTRIBUTE1 INTERNAL_LOT_NUMBER,
       RT.ORGANIZATION_ID,
       PLA.LINE_REFERENCE_NUM,
       MSI.SEGMENT1 ITEM_NUMBER,
       RT.TRANSACTION_ID,
       GET_SHIPMENT_LINE_TTL_QTY(RT.SHIPMENT_HEADER_ID,
                                             RT.SHIPMENT_LINE_ID,
                                             RT.TRANSACTION_TYPE) QUANTITY, --<<Key point
       RT.UNIT_OF_MEASURE,
       RT.SUBINVENTORY,
       RT.LOCATOR_ID,
       MSI.INVENTORY_ITEM_ID,
       RT.CREATION_DATE,
       RSH.ATTRIBUTE1 HEADER_ATTRIBUTE1,
       RSH.ATTRIBUTE2 HEADER_ATTRIBUTE2,
       RT.ATTRIBUTE1 TXN_ATTRIBUTE1,
       RT.ATTRIBUTE2 TXN_ATTRIBUTE2
  FROM RCV_SHIPMENT_HEADERS RSH
  JOIN RCV_TRANSACTIONS RT
    ON RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
   AND RT.ORGANIZATION_ID = NVL(RSH.ORGANIZATION_ID,RT.ORGANIZATION_ID)
  JOIN PO_HEADERS_ALL PHA
    ON PHA.PO_HEADER_ID = RT.PO_HEADER_ID
  JOIN PO_LINES_ALL PLA
    ON PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = RT.PO_LINE_ID
  JOIN MTL_SYSTEM_ITEMS_B MSI
    ON MSI.ORGANIZATION_ID = RT.ORGANIZATION_ID
   AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID
 WHERE 1=1
 ORDER BY RSH.RECEIPT_NUM DESC;