物料在 "某個日期以前" 的交易量

在 Oracle ERP 中, 要計算某物料在某個日期以前的交易量, 有兩種方式 :

方式 1 : (反推法 : QOH - 某個日期以後的交易量) (速度快很多)

 語法

SELECT A.QTY - B.QTY
  FROM (SELECT NVL(SUM(MOQ.TRANSACTION_QUANTITY), 0) QTY
          FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
             , MTL_SECONDARY_INVENTORIES    MSI
         WHERE MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
           AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
           AND MOQ.ORGANIZATION_ID = &ORGANIZATION_ID
           AND MOQ.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
       ) A
     , (SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0) QTY
          FROM MTL_MATERIAL_TRANSACTIONS MMT
             , MTL_SECONDARY_INVENTORIES MSI
         WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
           AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
           AND MMT.ORGANIZATION_ID = &ORGANIZATION_ID
           AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
           AND MMT.TRANSACTION_DATE >= TRUNC(TO_DATE('&CHECK_DATE','YYYYMMDD')+1)
       ) B;

方式 2 : (速度會較慢)

 語法

SELECT NVL(SUM(MMT.PRIMARY_QUANTITY), 0) QTY
  FROM MTL_MATERIAL_TRANSACTIONS MMT
     , MTL_SECONDARY_INVENTORIES MSI
 WHERE MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
   AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
   AND MMT.ORGANIZATION_ID = &ORGANIZATION_ID
   AND MMT.INVENTORY_ITEM_ID = &INVENTORY_ITEM_ID
   AND MMT.TRANSACTION_DATE < TRUNC(TO_DATE('&CHECK_DATE','YYYYMMDD')+1);