Query to Get Current Onhand Qty & Cost of Inventory Items

On-hand quantity represents the actual quantity of an item available in inventory at a specific location and point in time.

SELECT

    ood.organization_id,

    ood.organization_code,

    msi.inventory_item_id,

    msi.segment1 item_code,

    msi.description,

    msi.primary_uom_code,

    msi.creation_date item_creation_date,

    nvl(SUM(moq.primary_transaction_quantity),0) onhand_qty,

    (

        SELECT

            item_cost

        FROM

            cst_item_costs cst

        WHERE

                cst.cost_type_id = 2

            AND msi.inventory_item_id = cst.inventory_item_id

            AND ood.organization_id = cst.organization_id

    )                                     item_cost

FROM

    mtl_system_items_b           msi,

    mtl_onhand_quantities_detail moq,

    org_organization_definitions ood

WHERE

        msi.inventory_item_id = moq.inventory_item_id(+)

    AND msi.organization_id = moq.organization_id(+)

    AND msi.organization_id = ood.organization_id

    AND msi.organization_id != 105 --(master inventory Org)

GROUP BY

    ood.organization_id,

    ood.organization_code,

    msi.inventory_item_id,

    msi.segment1,

    msi.description,

    msi.primary_uom_code,

    msi.creation_date;