This can be done by running a standard report Transaction historical summary from Inventory responsibility. Alternately, run the below query .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 = 
rg_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 = rg_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
Courtesy from Suresh Vaishya. Thanks Mate.
/* On hand Quantity With Sub inventory , Locator and Lot Number*/
SELECT (mmt.inventory_item_id) “item id”, msi.description item_description,
msi.primary_unit_of_measure primary_uom,
mmt.transaction_uom transaction_uom,
DECODE (mtln.lot_number,
NULL, SUM (mmt.primary_quantity),
SUM (mtln.primary_quantity)
) primary_qty,
DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) transaction_qty,
mtln.lot_number, mil.inventory_location_id loc_seg,
secondary_inventory_name, ood.organization_name
FROM mtl_material_transactions mmt,
org_organization_definitions ood,
mtl_transaction_lot_numbers mtln,
mtl_system_items msi,
mtl_item_locations mil,
mtl_secondary_inventories mseci
WHERE mmt.organization_id = ood.organization_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.organization_id = mil.organization_id(+)
AND mmt.subinventory_code = mseci.secondary_inventory_name
AND mmt.organization_id = mseci.organization_id
AND mmt.organization_id = :p_org_id
AND TRUNC (mmt.transaction_date) <=
TO_CHAR (TO_DATE (‘2008/01/14′, ‘YYYY/MM/DD HH24:MI:SS’),
‘DD-MON-YYYY’
)
GROUP BY mmt.inventory_item_id,
ood.organization_name,
mtln.lot_number,
msi.description,
mil.inventory_location_id,
secondary_inventory_name,
msi.attribute14,
msi.primary_unit_of_measure,
mmt.transaction_uom
HAVING DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)
) != 0
ORDER BY ood.organization_name, (mmt.inventory_item_id)
By: Ahmad on April 22, 2008
at 7:07 am