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;