Wednesday, June 22, 2011

On Hand Quantity on any historical date Query

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

4 comments:

Anonymous said...

Thanks This really helped me

Govind said...

what is the use of MTL_TXN_SOURCE_TYPE table in the second query?

Anonymous said...

Thanks...good one

Anu said...

Thanks, very useful query indeed

Post a Comment

Best Blogger TipsGet Flower Effect