Get on-hand, reserved & available to transact quantity of item for given order in Oracle Apps R12

 

    SELECT

        --oola.subinventory,

        ooha.header_id,

        oola.line_id,

        oola.line_number,

        ooha.org_id header_org,

        oola.org_id line_org,

        ooha.ship_from_org_id header_ship_org,

        oola.ship_from_org_id line_ship_org,

        (select organization_code from mtl_parameters where organization_id=ooha.ship_from_org_id) header_ship_org_code,

        (select organization_code from mtl_parameters where organization_id=oola.ship_from_org_id) line_ship_org_code,

        ooha.order_number,

        msi.inventory_item_id,

        oola.ordered_item,

        (SELECT SUM(oola.ordered_quantity)    from apps.oe_order_lines_all  oola1 where oola1.inventory_item_id = oola.inventory_item_id AND oola1.ship_from_org_id = oola.ship_from_org_id and oola.header_id=oola1.header_id) ordered_quantity,

        (

            (select nvl(SUM(moq.transaction_quantity),0) from apps.mtl_onhand_quantities_detail  moq where oola.inventory_item_id = moq.inventory_item_id AND msi.organization_id = moq.organization_id )

            -    

            (select nvl(SUM(mr.reservation_quantity),0)  from apps.mtl_reservations  mr where oola.inventory_item_id = mr.inventory_item_id AND msi.organization_id = mr.organization_id )

        ) available_to_transact,

        (select nvl(SUM(moq.transaction_quantity),0) from apps.mtl_onhand_quantities_detail  moq where oola.inventory_item_id = moq.inventory_item_id AND msi.organization_id = moq.organization_id )  onhand_quantity,

        (select nvl(SUM(mr.reservation_quantity),0)  from apps.mtl_reservations  mr where oola.inventory_item_id = mr.inventory_item_id AND msi.organization_id = mr.organization_id )                 reserved_quantity

        

    FROM

        apps.oe_order_headers_all          ooha,

        apps.oe_order_lines_all            oola,

        apps.mtl_system_items_b            msi

    WHERE

            1 = 1

        AND ooha.header_id = oola.header_id

        AND oola.ship_from_org_id = msi.organization_id

        AND oola.inventory_item_id = msi.inventory_item_id

        AND ooha.order_number in (

        '1409693'

        );