How to find INV Onhand Quantity at given date?
–The query inputs the Item ID, organization ID and date.
SELECT SUM (target_qty), item_id
FROM ( SELECT moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code,
moqv.inventory_item_id,
moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv,
mmt.inventory_item_id item_id,
-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id
SELECT SUM (target_qty), item_id
FROM ( SELECT moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code,
moqv.inventory_item_id,
moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv,
mmt.inventory_item_id item_id,
-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id
Leave a Reply
Want to join the discussion?Feel free to contribute!