add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, June 22

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

No comments: