Query to get on-hand quantity

   
The quantity on hand of an item at any particular control level and location can be
found by summing TRANSACTION_QUANTITY for all records that match the
criteria

 SELECT SUM (primary_transaction_quantity)
        FROM mtl_onhand_quantities_detail
       WHERE     inventory_item_id = p_inventory_item_id
             AND organization_id = p_organization_id
             AND subinventory_code = p_subinventory_code
             AND NVL (lot_number, '$') = NVL (p_lot_number, '$')
             AND revision = p_item_revision;