PAC库存量与实际不符

sudn · 发表于 2009-8-1 08:28

要先执行SQL，才會有這個臨時表。

[ 本帖最后由 sudn 于 2009-9-1 10:12 编辑 ]

sudn · 发表于 2009-9-1 10:16

Oracle工程师提供的方案和秀才给的差不多，只是还要上Patch。
本以为问题解决了，可是莫名奇妙的还是会有一些差异存在，
又得和Oralce工程师互动了。。。

sudn · 发表于 2009-9-1 10:56

用这段SQL检查，没有问题，
SELECT
TXN.organization_id org_id,
TXN.inventory_item_id item_id,
TXN.subinventory_code sub,
TXN.mmt_qty mmt_qty,
ONHAND.qty onhand_qty, (TXN.mmt_qty - NVL(ONHAND.qty,0)) qty_diff

FROM
   (SELECT mmt.organization_id, mmt.inventory_item_id,
   mmt.subinventory_code, sum(mmt.primary_quantity) mmt_qty
   FROM inv.mtl_material_transactions mmt,
         inv.mtl_secondary_inventories mse
   WHERE mse.organization_id=mmt.organization_id
   AND mse.secondary_inventory_name=mmt.subinventory_code
   AND mse.quantity_tracked=1
   AND mmt.transaction_action_id not in (5,6,24,30,50,51,52,55,26,7,11,17,10,9,13,14)
   AND mmt.lpn_id is null AND mmt.transfer_lpn_id is null
   AND mmt.content_lpn_id is null
  GROUP BY mmt.organization_id, mmt.inventory_item_id,
                  mmt.subinventory_code) TXN,

   (SELECT moq.organization_id,
         moq.inventory_item_id,
         moq.subinventory_code,
         sum(moq.primary_transaction_quantity) qty
   FROM inv.mtl_onhand_quantities_detail moq,
         inv.mtl_secondary_inventories mse
   WHERE moq.organization_id=mse.organization_id
   AND mse.secondary_inventory_name=moq.subinventory_code
   AND mse.quantity_tracked=1
   AND nvl(moq.containerized_flag,2) = 2
   GROUP BY moq.organization_id, moq.inventory_item_id,
            moq.subinventory_code) ONHAND

WHERE TXN.inventory_item_id = ONHAND.inventory_item_id (+)
  AND TXN.organization_id = ONHAND.organization_id (+)
  AND TXN.subinventory_code = ONHAND.subinventory_code (+)
  AND abs(TXN.mmt_qty - NVL(ONHAND.qty,0)) <> 0
  AND exists (select 1
            from inv.mtl_parameters
            where organization_id = TXN.organization_id
            and wms_enabled_flag = 'N')
-----
其它的交易还是有问题