Quantity Tree - Inventory Onhand Quantity Calculation Logic

Why Quantity Tree?

Why go for such a complex structure just to get the quantity of an Item?
Why not just query the tables and get it every time?
Why not Just do the computation at each and every code where it is required?
This is why:

• Centralized structure to do anything will reduce much of the hassles.

• Not only Quantity Computation, but also anything done at a single place will lead to lesser number of bugs and easier maintenance.

E.g. where Quantity Tree goes handy:
Misc. Receipt Form when you tab out of each of the fields that are required to be filled the bottom two fields namely Available Quantity and OnHand Quantity changes its values. This is because the Quantity that has to be determined is getting more and more restricted. If we are going for a Query based approach then we need to query the Database for each and every restriction. Is this going to be good?

What's Quantity Tree

The Quantity Tree is a PL/SQL code to build a tree-like memory structure that holds information about different type of quantities. This information will be stored in nodes at different levels that represents the storage locations of the item. The node levels would consist of the following:

1. Item level nodes
2. Revision level nodes(Optional)
3. Lot level nodes(Optional)
4. Subinventory level nodes
5. Locator level nodes
6. LPN level nodes

A Demo Qty Tree(No LPN Level in picture):

Terminologies of Quantities

Quantity On Hand

represents the amount of items at the current node and all child nodes that are physically available at the moment. Pending Transactions are treated as source of onhand.

Reservable Quantity On Hand

represents the amount of items at the current node and all child nodes that are physically available and are reservable at the moment.

Quantity Reserved

represents the amount of items reserved at the current node or reserved at any child node at the moment.

Quantity Suggested

represents the amount of items sugested by the system to be moved from this location to a different location.

Available To Transact:

QOH – (QR + net(QS))

represents the amount of items available to be transacted to a specific source at the moment.

Available To Reserve

RQOH – (QR + net(QS))

represents the amount of items available to be reserved from this storage level at the moment.

Three Quantity Related Key Tables

MTL_ONHAND_QUANTITIES_DETAIL(MOQD)
MTL_ONHAND_QUANTITIES stores quantity on hand information by control level and location.
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 our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.

MTL_RESERVATIONS(MR)
This table stores reservation information. Each record is a reservation that ties an item/organization combination with a demand source and a supply source. Demand source information comprises demand source type (Sales Order, Account, Account Alias, Inventory), demand source header, demand source line and demand source name. Supply source information comprises supply source type (Inventory, WIP jobs), supply source header, supply source line, supply source name and inventory controls (revision, lot, subinventory, locator).

MTL_MATERIAL_TRANSACTIONS_TEMP(MMTT)
MTL_MATERIAL_TRANSACTIONS_TEMP is the gateway for all material transactions.
Records are processed from this table into Inventory through the transaction processor. All Inventory transaction forms write directly to this table. Outside applications must write transaction records to MTL_TRANSACTIONS_INTERFACE to be processed through MTL_MATERIAL_TRANSACTIONS_TEMP and the transaction processor by the Transaction Worker concurrent program.

Data Structures

Quantity Tree Code Processes

Create a Qty Tree Code Process

Data Flow in CREATE_TREE
If the Tree already exists just returns the Tree ID.
If not Database is Queried and the Tree is built.

* find_rootinfo()
Finds whether the Tree already Exists

* new_tree()
Creates a Tree Node and a Item Node

* build_tree()
Builds the SQL and Queries the DB
Qty Types: MOQ – 1, MTR – 3, MMTT – 1/5
For each record calls add_quantities where everything is done.

* add_quantities()
find_tree_node returns the node for which the record fetched from DB is valid.
The quantity is added to QOH, RQOH, QR, ATR, ATT depending on the Qty Type. (BUILD_TIME)

* find_tree_node()
Which builds the actual link from the node till the Item Node.
First Locator Level Node is created and then it proceeds to Sub Level, Lot Level and then to Revision Level if the corresponding values are not null.
The appropriate Parent Child relationship is established.
Always returns the first Node created in this link till Item Node.

Query Tree Code Process

* find_tree_node()
For the given Revision, Locator, Lot, Locator the node is found.

* If the SubInventory is passed then
ATR = min(ATR of current node, ATR of parent Nodes)
ATT = min(ATR of current node, ATT of parent Nodes)
This goes on till Item Level node is reached. (QUERY_TIME)

* If the SubInventory is not passed then
ATR = min (ATR of current node, ATR of parent Nodes)
ATT = min (ATT of current node, ATT of parent Nodes)
This goes on till Item Level Node is reached. (QUERY_TIME)

Quantity Tree APIs

Here's some general instructions and guidelines on using the quantity tree.

1. INV_QUANTITY_TREE_PVT.CREATE_TREE :

Call create_tree to build the tree for a given organization and item. The tree can be built in two modes:

reservation mode and transaction mode.

Reservation mode is used to determine the available to reserve (atr) quantity (for reservations).

Transaction mode is used to determine the availabe to transact (att) quantity, used in transactions.

The onhand_source passed to the create tree function help define which subs and locators will be used to determine onhand quantity.

If Onhand_source is 1, then only the quantity in ATPable subs will be used to determine quantity. If onhand_source is 2, then only the quantity in nettable subs is considered. If onhand_source is 3, the subs are not limited based on the nettable and ATPable flags.

Pick_release should be 0 except if called from the inventory or wms detailing engines.

The create_tree procedure returns tree_id, which must be used to query or update the tree.The equivalent function in the C code is CreateTree.

2. INV_QUANTITY_TREE_PVT.QUERY_TREE :

This procedure is used to find the values for quantity onhand, reservable quantity on hand, quantity reserved, quantity suggested, availabe to transact, and available to reserve.

This procedure takes the place of 2 C functions: QtyQuery and SubXQuery. If tree is being queried in transaction mode, and the transaction is a subinventory transfer, then pass the subinventory code of the destination sub in the p_transfer_subinventory_code parameter.

In all other cases, set the p_transfer_subinventory_code parameter to NULL.ATT and ATR are calculated differently depending on whether the transaction is a subinventory transfer or some other transaction.

3. INV_QUANTITY_TREE_PVT.UPDATE_QUANTITIES :

The update procedure changes the quantity in the quantity tree for a given item/org/revision/lot/sub/locator.

The quantity updated depends on the quantity type parameter.

If the quantity type is g_qoh, then the p_primary_quantity value is added to the quantity onhand.

If the quantity type is g_qs_txn,then the quantity suggested value is updated.

Reservations work the same way.

Update_quantities does not update the database - it only updates the local version of the qty tree. The database must be updated separately.

There are a couple of important things to keep in mind.
First, the quantity passed in to update_quantities is important.

The quantity is always added to the appropriate node qty. So, for a receipt txn, the quantity passed in should be positive. For an issue txn, the quantity passed in should have a negative sign (to decrement on hand quantity).

For reserving items or suggesting an issue, the value passed in should be positive (incrementing quantity reserved or quantity suggested). Do not update the tree with suggested receipts; including suggested receipts could lead to missing inventory if the suggestion is not

transacted.

Second, this function is the same as the C function QtyAvail. There is no pl/sql equivalent of the C function SubXFer. For a subinventory transfer transaction which updates both the destination location and the source location, update_quantities must be called twice. First, add the quantity to the destination sub/locator. Then decrement the quantity from the source sub/locator. Order is important - this ordering assures that higher level att/atr are not made negative. The updates to both the destination and source should only happen for actual
transactions, not suggested transfers.

4. INV_QUANTITY_TREE_PVT.DO_CHECK :

Since Quantity Tree is a PL/SQL structure (a memory structure that stores cached values), sometimes values on Quantity Tree are not in synch with
corresponding database table. This happens mainly due to concurrrency issues. For Example, let say we have an organization that does not allow negative balances
and we have onhand quantity of 10 Ea. User-1 opens sessions and constructs Quantity Tree. User-2 opens sessions and constructs Quantity Tree, too.
User-2 performs issue transaction of 10 Ea and commits, resulting in total onhand quantity as 0 Ea.However, User-1's Quantity Tree has no idea about
any such sue transaction by User-2. So if User-1 tries to perform issue transaction of 10 Ea, it will happily allow it.
This situation is to be avoided as if this transaction is successful, it would drive onhand quantity to -10 Ea.

To avoid such situations, we call INV_QUANTITY_TREE_PVT.DO_CHECK just before performing the transaction commit. (ensure that MMTTs for current
transactions are inserted). This API destroys the existing Quantity Tree in the session and constructs new one. Then it ensures that while performing the current
transaction, we are not violating negative balances settings for current organization (it also check if ATR at any node is not driven negative). If violation is detected,
it throws error message, else it continues to transaction processing.

This API is typically coded into POST-FORMS-COMMIT in the forms.

5. INV_QUANTITY_TREE_PVT.CLEAR_QUANTITY_CACHE

This API destroys all the quantity trees in the session.
Delete all quantity trees in the memory. Should be called when you call rollback.
Otherwise the trees in memory may not be in sync with the data in the corresponding database tables.

Onhand Qty(Tree) Diagnostics

1.Collect quantity tree debug log

INV: Debug Trace: Yes
INV:Debug Level: 15
INV: Debug file: /usr/tmp/invdebug.log

Qty Tree Log Looks Like:

(Marked means: Its used in do check. If any node was modified to reduce its original qty, then do_check will check only those nodes for node violations.)

2.Following Queries Output

SELECT * FROM MTL_ONHAND_QUANTITIES_DETAIL WHERE INVENTORY_ITEM_ID = &ITEM_ID AND ORGANIZATION_ID = &ORG_ID;
SELECT * FROM MTL_MATERIAL_TRANSACTIONS_TEMP WHERE INVENTORY_ITEM_ID = &ITEM_ID AND ORGANIZATION_ID = &ORG_ID;
SELECT * FROM MTL_RESERVATIONS WHERE INVENTORY_ITEM_ID = &ITEM_ID AND ORGANIZATION_ID = &ORG_ID;

3.Onhand Qty(Tree) Diagnostics Scripts
refer: http://blog.youkuaiyun.com/pan_tian/article/details/8508122

Examples

Profiles Used for Quantity Tree

INV:Quantity tree timeout for lock

Timeout for the Quantity Tree to wait for the Lock.

(PROFILE_OPTION_NAME=INV_QTY_TREE_TIMEOUT)

INV: Maximum Number of Quantity Trees

Maximum number of Trees that can exists in a session.

(PROFILE_OPTION_NAME=INV_MAXIMUM_NUM_TREES)

Quantity Tree Dictionary

Abbr.

qoh quantity on hand
rqoh reservable quantity on hand
qr quantity reserved
qs quantity suggested
att available to transact
atr available to reserve

sqoh secondary quantity on hand
srqoh secondary reservable quantity on hand
sqr secondary quantity reserved
sqs secondare quantity suggested
satt secondary available to transact

satr secondary available to reserve

Node levels

1.Item level nodes
2.Revision level nodes(Optional)
3.Lot level nodes(Optional)
4.Subinventory level nodes
5.Locator level nodes
6.LPN level nodes

Tree Mode

g_reservation_mode CONSTANT INTEGER := 1;

Doesn't bother about Demand Info

g_transaction_mode CONSTANT INTEGER := 2;

Requires Demand Info

g_loose_only_mode CONSTANT INTEGER := 3;

Only Loose Quantities are Considered.

g_no_lpn_rsvs_mode CONSTANT INTEGER := 4;

(Users can call create_tree() in three modes, reservation mode, transaction mode, and loose items only mode. In loose items only mode, only quantity not in containers(LPN) is considered)

Quantity Type

g_qoh CONSTANT INTEGER := 1; -- quantity on hand(MOQD)
g_qr_same_demand CONSTANT INTEGER := 2; -- quantity reserved for the same demand source(MR)
g_qr_other_demand CONSTANT INTEGER := 3; -- quantity reserved for other demand source(MR)
g_qs_rsv CONSTANT INTEGER := 4; -- quantity for suggested reservation(MMTT)
g_qs_txn CONSTANT INTEGER := 5; -- quantity for suggested transaction(MMTT)

(User can call update_quantities to change quantities at a given level.Quantity type constans should be used to specify which quantity the user intents to change: quantity onhand, or quantity reserved.)

Quantity Calculation Related Files

INV_QUANTITY_TREE_UE - INVQTUEB.pls
INV_QUANTITY_TREE_PVT - INVVQTTB.pls
INV_QUANTITY_TREE_GRP - INVGQTTB.pls
INV_QUANTITY_TREE_PUB - INVPQTTB.pls
INVMTXIT.pld(It's Quantity Calculation API Wrapper at Forms Level)
inldqc.ppc
UtilFns.java