Onhand Quantities

select
p.organization_code,
org.name organization_name,
si.segment1 item_code,
si.description item_description,
si.primary_uom_code item_uom,
sum(mt.primary_quantity) onhand_quantity

from
mtl_material_transactions mt,
mtl_system_items si,
mtl_parameters p,
hr_all_organization_units org

where
mt.inventory_item_id=si.inventory_item_id and
mt.organization_id=si.organization_id and
mt.organization_id=p.organization_id and
p.organization_id=org.organization_id and
p.organization_code=nvl(:org_code,p.organization_code) and
si.segment1 like nvl(:item_code,si.segment1) and
mt.transaction_type_id not in (80,2)

group by
p.organization_code,
org.name,
si.segment1,
si.primary_uom_code,
si.description

order by
p.organization_code,
si.segment1