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

在 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);
天聖經:神的心不僅在神的聖言當中,也在神所創造的萬物之中,天涯海角每一處都有神的心
Related Posts Plugin for WordPress, Blogger...