Dashboards, Applications & Business Intelligence

Question: How to find the current quantity in Oracle Inventory. What is the table and column details?

The short answer is, you can get the current inventory balance by querying the mtl_onhand_quantities view. To get the current balance you would have to write a query like

select sum(transaction_quantity)
from mtl_onhand_quantities
where inventory_item_id=1234
and organization_id=201

Why do we need to do a sum?
Here are some explanations..

MTL_ONHAND_QUANTITIES is maintained as a stack of receipt records,
which are consumed by issue transactions in FIFO order. 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. Note that any transactions which are committed to the table
MTL_MATERIAL_TRANSACTIONS_TEMP are considered to be played out as far
as quantity on hand is concerned in Inventory transaction forms. All
Inquiry forms and ABC compile are only based on
MTL_ONHAND_QUANTITIES.
.
.
.
MTL_ONHAND_QUANTITIES stores quantity on hand information by control
level and location.
.
.
.

MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID
and UPDATE_TRANSACTION_IDs to join to
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the transactions that
created the row and the transaction that last updated a row.
For e.g, mtl_onhand_quantities maintains the amount of transacted quantity.
If the item goes out of inventory then it creates a row with negative quantity. If you
manufacture an item and place it in the inventory then it creates a row with positive number. Similarly if you buy an item then it creates a row with positive quantity.

Items going out => -ve
Items coming in => +ve

So to get the exact count you would have to sum all the transactions.