Inventory Stock details of an item

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Inventory Stock details of an item by Subinventory / Oranizations
-- Date      : 01.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

SELECT
msi.SEGMENT1 Item_name,
ood.ORGANIZATION_NAME org_name,
msinv.SECONDARY_INVENTORY_NAME subinv_name,
moq.TRANSACTION_QUANTITY
from
mtl_system_items_b msi,
mtl_onhand_quantities moq,
mtl_secondary_inventories msinv,
org_organization_definitions ood
where msi.INVENTORY_ITEM_ID = moq.INVENTORY_ITEM_ID
and msinv.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msinv.SECONDARY_INVENTORY_NAME = moq.SUBINVENTORY_CODE
and ood.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.segment1 = :item_name;

-- --------------------------------------------------------