1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Inventory Query

Discussion in 'Oracle SCM & Manufacturing' started by tamicka79, Nov 30, 2010.

  1. tamicka79

    tamicka79 Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    75
    Hi I have the following query where I am having problems. If i run the query for I subinventory my quanties are fine but if i run the query for multiple subinventories my quantities are multipled by the number of subinventories. Can someone please help! I will need to be able to get this information for as many as one subinventory or more.

    Thanking you in advance,

    Code (SQL):
    SELECT msi.segment1 AS item_code
    , mp.organization_code AS org_code
    , decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinv_code
    , SUM(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0))AS ohq
    FROM
    apps.mtl_system_items_b msi
    , apps.mtl_onhand_quantities moq
    , apps.mtl_parameters mp
    , (SELECT msec.secondary_inventory_name,
    msec.organization_id
    FROM mtl_secondary_inventories msec
    WHERE msec.organization_id = '&&org_id'
    AND msec.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')) sinv
    WHERE 1=1
    AND msi.organization_id = &&org_id
    AND msi.inventory_item_status_code = 'Active'
    AND msi.inventory_item_id = moq.inventory_item_id(+)
    AND msi.organization_id = moq.organization_id(+)
    AND (moq.subinventory_code IN ('Sub_INVA','Sub_INVB') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')))
    AND msi.organization_id = mp.organization_id
    AND msi.organization_id = sinv.organization_id
    GROUP BY msi.segment1,
    mp.organization_code,
    decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name)
    ORDER BY msi.segment1,
    decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
    msi.segment1;