MTL_ONHAND_QUANTITIES Table - An Overview



PURPOSE
-------
The purpose of this document is to understand how and when the table
MTL_ONHAND_QUANTITIES_DETAIL table is populated and what processes cause records to be
inserted, updated, deleted and maintained.

TEXT
----
The inltuq module populates the MTL_ONHAND_QUANTITIES_DETAIL  table - any process that
calls this module affects the onhand quantity.  The table itself is maintained
in what is termed "stack logic."  This means that a row is written to the table
for each receipt into Inventory (Misc. Receipt, Purchase Order Receipt, or
Work In Process) and is maintained at the level of control you have established
on the system. For instance, subinventory/locator/lot/serial/revision controls.  When an issue
out of Inventory occurs, instead of writing a row out to MTL_ONHAND_QUANTITIES
table, the amount is deducted from the rows of receipts available for that item
at that level of control.

An example of this would explain the calculation better.  You have part/item
number 123 in subinventory "Finished Goods."  You do two receipts for this
part - 50 & 100 - for 150 total quantity of onhand.

At this point, you have two rows in MTL_ONHAND_QUANTITIES_DETAIL:

  Item 123 "Finished Goods" Receipt #1 50
  Item 123 "Finished Goods" Receipt #2 100
  Total 150

Now you receive a Sales Order for a total quantity of 75 and need to deplete
Inventory by this amount.

You bring the Receipt #1 to zero, and the system deletes it (50), and still
need to use 25 from Receipt #2.  So now, your data in MTL_ONHAND_QUANTITIES_DETAIL
looks like this:

  Item 123 "Finished Goods" Receipt #2 75

Zero quantity rows are deleted from this table.  Note that issues do not have
their own row unless you allow negative balances in your organization and all
onhand quantities are depleted.

Basically, transactions that update Inventory onhand quantity must pass through
the MTL_MATERIAL_TRANSACTIONS_TEMP table whether they originate inside Inventory,
Purchasing, or WIP, or are passed through the MTL_TRANSACTIONS_INTERFACE from
Oracle Order Management, Oracle Work in Process, 3rd party software, or legacy
data.  It is at the point of validation in the MTL_MATERIAL_TRANSACTIONS_TEMP
that the inltuq module is called and the MTL_ONHAND_QUANTITIES_DETAIL table is
then populated.

The on hand quantity updates only after records are successfully processed
through the MTL_MATERIAL_TRANSACTIONS_TEMP table in Inventory.  Records populate
into the MTL_MATERIAL_TRANSACTIONS table using the application.  For example,
conducting miscellaneous transactions populates MTL_MATERIAL_TRANSACTIONS_TEMP.
Once in this table the transaction manager picks up records to process.  If
records pass validation and all required data is correct, the record is deleted
from the MTL_MATERIAL_TRANSACTIONS_TEMP table and a corresponding record is
inserted in both the MTL_ONHAND_QUANTITIES_DETAIL table and MTL_MATERIAL_TRANSACTIONS
table.  The module responsible for updating on hand quantity is inltuq.ppc.

This follows the stack process.  If you deplete a particular stack or the
quantity goes to zero, the record is deleted from the MTL_ONHAND_QUANTITIES_DETAIL table