ON HAND QUENTITY QUERY

SELECT SUM (target_qty), item_id, subinv
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
AND moqv.subinventory_code =
NVL (:subinventory_code, moqv.subinventory_code)
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 >=
NVL (TO_DATE (:hist_date), TRUNC (SYSDATE))
+ 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
AND mmt.subinventory_code =
NVL (:subinventory_code, mmt.subinventory_code)
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id,subinv