Verify if there is any mismatch between transactions and on-hand?

Purchasing & Inventory forum can be used to discuss all features/issues related to Oracle Purchasing & Inventory.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Verify if there is any mismatch between transactions and on-hand?

Post by admin »

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 mtl_material_transactions mmt,
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,15,22)
AND Nvl(mmt.logical_transaction, -1) <> 1
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 mtl_onhand_quantities_detail moq,
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 mtl_parameters
where organization_id = TXN.organization_id
and wms_enabled_flag = 'N');
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests