Quantity Delivered vs Detailed in Move Order Lines
(MTL_TXN_REQUEST_LINES)
A. What tables are used in the move order process?
1) MTL_TXN_REQUEST_HEADERS:Move order headers, this stores
the move order number in column (REQUEST_NUMBER). It has a status, but this is
not used as much as the lines status to drive functionality.
2) MTL_TXN_REQUEST_LINES: Move order lines, this is the one that drives most queries and status checks for the move order as each line can be transacted individually.
3) MTL_MATERIAL_TRANSACTIONS_TEMP:Pending material transactions table also called the transaction temporary table, this holds allocations that act like reservations on inventory. An allocation is where you pick a specific item in inventory down to the lot, locator, serial, revision to move, but you do not actually perform. the move yet.
1. Create move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 1 (Incomplete)
2. Approve move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 3 (Approved)
3. Allocate move order for full quantity:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 3 (Approved)
4. Transact move order:
Quantity: 10
Quantity Delivered: 10
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 5 (Closed)
2) MTL_TXN_REQUEST_LINES: Move order lines, this is the one that drives most queries and status checks for the move order as each line can be transacted individually.
3) MTL_MATERIAL_TRANSACTIONS_TEMP:Pending material transactions table also called the transaction temporary table, this holds allocations that act like reservations on inventory. An allocation is where you pick a specific item in inventory down to the lot, locator, serial, revision to move, but you do not actually perform. the move yet.
1. Create move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 1 (Incomplete)
2. Approve move order:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: NULL
Quantity Required: NULL
Line Status: 3 (Approved)
3. Allocate move order for full quantity:
Quantity: 10
Quantity Delivered: NULL
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 3 (Approved)
4. Transact move order:
Quantity: 10
Quantity Delivered: 10
Quantity Detailed: 10
Quantity Required: NULL
Line Status: 5 (Closed)
NOTE:
When a move order is allocated, a corresponding record is inserted into the
pending table (MTL_MATERIAL_TRANSACTIONS_TEMP as well as lot/serial tables if
required).
When the move order is transacted, the record moves from the pending table to the history table (MTL_MATERIAL_TRANSACTIONS).
When the move order is transacted, the record moves from the pending table to the history table (MTL_MATERIAL_TRANSACTIONS).
a) Example
query for linking move orders with the pending table:
SELECT mmtt.transaction_temp_id,
tol.organization_id,
toh.request_number,
toh.header_id,
tol.line_number,
tol.line_id,
tol.inventory_item_id,
toh.description,
toh.move_order_type,
tol.line_status,
tol.quantity,
tol.quantity_delivered,
tol.quantity_detailed
FROM mtl_txn_request_headers toh,
mtl_txn_request_lines tol,
mtl_material_transactions_temp mmtt
WHERE toh.header_id = tol.header_id
AND toh.organization_id = tol.organization_id
AND tol.line_id = mmtt.move_order_line_id;
b)
Example query linking MTL_MATERIAL_TRANSACTIONS to the move order:
SELECT mmt.transaction_id,
tol.organization_id,
toh.request_number,
toh.header_id,
tol.line_number,
tol.line_id,
tol.inventory_item_id,
toh.description,
toh.move_order_type,
tol.line_status,
tol.quantity,
tol.quantity_delivered,
tol.quantity_detailed
FROM mtl_txn_request_headers toh,
mtl_txn_request_lines tol,
mtl_material_transactions mmt
WHERE toh.header_id = tol.header_id
AND toh.organization_id = tol.organization_id
AND tol.line_id = mmt.move_order_line_id
AND toh.request_number = '&MONumber';
/
tol.organization_id,
toh.request_number,
toh.header_id,
tol.line_number,
tol.line_id,
tol.inventory_item_id,
toh.description,
toh.move_order_type,
tol.line_status,
tol.quantity,
tol.quantity_delivered,
tol.quantity_detailed
FROM mtl_txn_request_headers toh,
mtl_txn_request_lines tol,
mtl_material_transactions mmt
WHERE toh.header_id = tol.header_id
AND toh.organization_id = tol.organization_id
AND tol.line_id = mmt.move_order_line_id
AND toh.request_number = '&MONumber';
/
There are times
when a move order will close, but the quantity is not moved from finished goods
to a staging sub inventory. The most common case of this is a move order
for a sub inventory or item that is NOT reservable. If the item is not
reservable, no inventory is staged and the move order simply closes without
doing anything. However, there are rare occasions when a move order
closes without moving quantity to staging but should have. In the case of
a move order related to a sales order, the sales order delivery detail shows
picked and Released to Warehouse after the move order is transacted.
5. What
is code for move order status?
The move
order table (MTL_TXN_REQUEST_LINES) provides the status of the move order
in the LINE_STATUS column as a numeric. The following lists the
various statuses as well as provides a SQL to look them up.
At the time this question was entered, here are
a list of the statuses and codes:1 Incomplete
2 Pending Approval
3 Approved
4 Not Approved
5 Closed
6 Cancelled
7 Pre-Approved
8 Partially Approved
9 Cancelled by Source
