Onhand Quantity at given date
Below is the query that can help in getting 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
