SELECT SUM (target_qty)
FROM (SELECT segment1, description, mtl.primary_uom_code,
moqv.subinventory_code subinv,
moqv.inventory_item_id item_id,
SUM (transaction_quantity) target_qty
FROM apps.mtl_onhand_qty_cost_v moqv,
apps.mtl_system_items_vl mtl
WHERE moqv.organization_id = :p_organization_id
AND moqv.inventory_item_id = :p_inventory_item_id
AND moqv.inventory_item_id = mtl.inventory_item_id
AND moqv.organization_id = mtl.organization_id
GROUP BY moqv.subinventory_code,
mtl.primary_uom_code,
moqv.inventory_item_id,
segment1,
description
UNION
SELECT mtl.segment1, mtl.description, mtl.primary_uom_code,
mmt.subinventory_code subinv, mmt.inventory_item_id item_id,
-SUM (primary_quantity) target_qty
FROM apps.mtl_material_transactions mmt,
apps.mtl_txn_source_types mtst,
apps.mtl_system_items_vl mtl
WHERE mmt.organization_id = :p_organization_id
AND transaction_date >= TO_DATE (:p_historical_date, ‘DD-MON-YYYY’) + 1
AND mmt.transaction_source_type_id = mtst.transaction_source_type_id
AND mmt.organization_id = mtl.organization_id
AND mmt.inventory_item_id = mtl.inventory_item_id
AND mmt.inventory_item_id = :p_inventory_item_id
AND mmt.subinventory_code IS NOT NULL
GROUP BY mmt.subinventory_code,
mmt.inventory_item_id,
mtl.segment1,
mtl.description,
mtl.primary_uom_code);
