SELECT dtl.organization_id,
dtl.inventory_item_id,
dtl.segment1,
dtl.revision,
dtl.subinventory_code,
dtl.locator_id,
dtl.lot_number,
dtl.primary_uom_code,
SUM(dtl.primary_transaction_quantity) primary_transaction_quantity
FROM (
--Onhand Quantity
SELECT moq.organization_id,
moq.inventory_item_id,
mst.segment1,
moq.revision,
moq.subinventory_code,
moq.locator_id,
moq.lot_number,
mst.primary_uom_code,
moq.primary_transaction_quantity
FROM inv.mtl_onhand_quantities_detail moq,
inv.mtl_system_items_b mst
WHERE moq.organization_id = mst.organization_id
AND moq.inventory_item_id = mst.inventory_item_id
AND moq.is_consigned = 2
--Filter
AND moq.organization_id = 33
AND mst.segment1 = '00088035800'
UNION ALL
SELECT mmt.organization_id,
mmt.inventory_item_id,
mst.segment1,
mmt.revision,
mmt.subinventory_code,
mmt.locator_id,
lot.lot_number,
mst.primary_uom_code,
-nvl(lot.primary_quantity, mmt.primary_quantity) primary_transaction_quantity
FROM inv.mtl_material_transactions mmt,
inv.mtl_transaction_lot_numbers lot,
inv.mtl_system_items_b mst
WHERE mmt.transaction_id = lot.transaction_id(+)
AND mmt.organization_id = mst.organization_id
AND mmt.inventory_item_id = mst.inventory_item_id
--Filter
AND mmt.organization_id = 33
AND mst.segment1 = '00088035800'
AND mmt.transaction_date >=
to_date('2006-12-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) dtl
GROUP BY dtl.organization_id,
dtl.inventory_item_id,
dtl.segment1,
dtl.revision,
dtl.subinventory_code,
dtl.locator_id,
dtl.lot_number,
dtl.primary_uom_code
HAVING SUM(dtl.primary_transaction_quantity) <> 0;