Tuesday, April 22, 2008

Report On Inv Balances(xml)

select msi.segment1,mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',-primary_quantity,null)),0) issue,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',null,primary_quantity)),0) receipt
from mtl_material_transactions mmt, mtl_system_items msi, org_organization_definitions ood
where
mmt.organization_id=msi.organization_id and
mmt.inventory_item_id=msi.inventory_item_id and
mmt.organization_id=ood.organization_id and
mmt.organization_id=:org and
mmt.transaction_date between :d1 and :d2
group by msi.segment1, mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id
order by msi.segment1

No comments: