To find out Onhand quantity based on organization, and Subinventory Code:

To find out Onhand quantity based on organization, and Subinventory Code:


SELECT msib.segment1 ITEM_NAME,
       moq.inventory_item_id,
       moq.subinventory_code subinventory_code,
       mp.organization_code, 
       moq.organization_id,
       sum(moq.transaction_quantity)
FROM   MTL_ONHAND_QUANTITIES MOQ,
       MTL_SYSTEM_ITEMS_B MSIB,
       MTL_PARAMETERS MP
WHERE  msib.inventory_item_id = moq.inventory_item_id
AND    msib.organization_id = mp.organization_id
AND    msib.organization_id = moq.organization_id
AND    moq.organization_id = :p_organization_id 
GROUP BY msib.segment1,moq.inventory_item_id, moq.organization_id,moq.subinventory_code,mp.organization_code
ORDER BY msib.segment1, moq.organization_id,sum(moq.transaction_quantity);