select * from MTL_TXN_REQUEST_HEADERS_V a where a.REQUEST_NUMBER=‘19311288‘
 
  select * from MTL_TXN_REQUEST_LINES_V a where a.header_id=19311288

In this Document
  Goal
  Solution      1. What is quantity detailed in the move order table mtl_txn_request_lines?      2. When might the quantity detailed be more than the quantity delivered?      3. EXAMPLE QUANTITIES      4. Find a Closed Move Order, but No Staged Inventory?      5. What is code for move order status?      6. What is TRANSACTION_STATUS in MTL_MATERIAL_TRANSACTIONS_TEMP?      7. How does one get rid of cancelled move orders?      8. Removing Pending Transactions for canceled move orders?   References

This document is being delivered to you via Oracle Support‘s Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to:

Oracle Inventory Management - Version: 11.5.10.1
Information in this document applies to any platform.
FORM:INVTOTRX.FMB - Transact Move Orders
Goal

The following outlines some common questions and answers about the move order lines table -- Specifically about the quantity delivered and detailed.

This is an extension of the first Move Order FAQ Note 114156.1 that is also helpful in describing the basics of the move order flow, types, and definitions. The two notes maybe combined in the future.
Note 114156.1 that is also helpful in describing the basics of the move order flow, types, and definitions. The two notes maybe combined in the future.
 



Solution

1. What is quantity detailed in the move order table mtl_txn_request_lines?
The quantity detailed reflects the allocated quantity for a move order line. 



2. When might the quantity detailed be more than the quantity delivered?
The quantity detailed might be greater than the quantity delivered if a move order is allocated
but not transacted.

3. EXAMPLE QUANTITIES
Here is an example of the way the quantity value changes as the move order progresses:

SQL USED: select 
QUANTITY, 
QUANTITY_DELIVERED, 
QUANTITY_DETAILED, 
REQUIRED_QUANTITY, 
LINE_STATUS 
from MTL_TXN_REQUEST_LINES 
where LINE_ID = &EnterLineID; 

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).
 

4. Find a Closed Move Order, but No Staged Inventory?
There are times when a move order will close, but the quantity is not moved from finished goods to a staging subinventory.  The most common case of this is a move order for a subinventory 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 occassions 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.  If no inventory actually was staged, the move order might need to be reopened via a datafix.  This would require assistance from Oracle Support similar to Bug 4582052 or Bug 6045553.

When logging an SR related to a sales order be sure to provide the results of the HTMOMSE11i from Note 133464.1.

The following patches were identified to avoid this issue (though Patch 3720404 does not need to be applied as it is already included in Patch 4582052):
Patch 4582052 
Patch 3855692 
Patch 3720404 - included in above 2 patches
 

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 Canceled 
7 Pre Approved 
8 Partially Approved 
9 Canceled by Source 

You can look up the status codes with SQL like the following:
select lookup_code, substr(meaning, 1, 60) "Meaning" 
from mfg_lookups 
where lookup_type = ‘MTL_TXN_REQUEST_STATUS‘ 
order by lookup_code 
/ 



6. What is TRANSACTION_STATUS in MTL_MATERIAL_TRANSACTIONS_TEMP?
As soon as a move order is allocated, a record is inserted into the MTL_MATERIAL_TRANSACTIONS_TEMP table.  The column TRANSACTION_STATUS keeps track of the progress of the move order

These are the current values for TRANSACTION_STATUS:
STATUS 1 = PENDING
STATUS 2 = ALLOCATED 
STATUS 3 = TRANSACTED / This is where went into internal processing and waiting for transaction manager to run if transaction_mode is 3 / background.

7. How does one get rid of cancelled move orders? 
We have multiple ways to cleanup move orders: 

a. INVCLRMOv115.5.sql from Note 169677.1. 
This note focuses on move orders related to closed/cancelled sales orders. 
If you findNote 169677.1. 
This note focuses on move orders related to closed/cancelled sales orders. 
If you find pending move order transactions reserving inventory and these pending transactions are related to closed move orders for Sales Orders, the INVCLRMOv115.5.sql would be a good choice.

b. ‘Move Order Purge‘ concurrent program (INV_MO_PURGE.PURGE_LINES from INVMOPGB.pls). 
This removes any move orders in status (5, 6, 9) 
5 Closed 
6 Canceled 
9 Canceled by Source 

If you are trying to get rid of cancelled move orders especially manual move orders not related to sales orders, your best bet is the Move Order Purge Concurrent program. This should be available in the standard Inventory responsibility. You might want to review this Note 406182.1 if you plan to run it. Make sure you have a recent version of the program to avoid common errors.


8. Removing Pending Transactions for canceled move orders?
Move orders are sometimes allocated but not yet transacted when the related sales order or order line is cancelled.  When this happens, the move order goes to status Canceled by Source. If the move order was already allocated, then allocation records would exist in the pending transaction table (MTL_MATERIAL_TRANSACTIONS_TEMP).  These pending transactions act like reservations on the onhand quantity.  Based on your setup, allocations can be automatically removed in 11.5.10 and higher.  See Note 338740.1 for details. If you find pending transactions related to a canceled move order, you might not require a datafix, but could potentially clear up the issue yourself via the Inventory >Transactions > Pending Transactions form by querying the transaction that was using the inventory, pressing the Lot/Serial button, deleting the serial number, then deleting the transaction. The following outlines an example when this could be helpful.

The allocations are documented in the Oracle Applications via records in the move order lines table (MTL_TXN_REQUEST_LINES) and the pending transactions table (MTL_MATERIAL_TRANSACTIONS_TEMP) as well as other tables if the item is lot and serial controlled. (See Note 309710.1 for a discussion on serial numbers and mark ids.) When a sales order is cancelled, the move order line is changed to a status of canceled bysource (status = 9). See Note 338740.1 for details on automatically cleaning up pending transactions. The note outlines a new setting in 11.5.10 called uto Delete Allocations. If this was not enabled or not available, the move order is canceled, but the pending transactions will remain. This keeps the serial number locked from use, but now there is no active order. The pending transactions / allocations can often be deleted manually in this case by querying them in the Inventory >Transactions > Pending Transactions form. If the item is lot/serial controlled, you would press the Lot/Serial button, delete the serial number or lot numbers, then delete the transaction. This should unmark the serial number as well as remove the demand for the onhand quantity caused by the allocation.

References

Bug 6045553 - DATAFIX: ITEM CANNOT BE TRANSACTED- MOVE ORDER CLOSED / DELIVERY OPEN
Bug 6045622 - DATAFIX: MOVE ORDER ALLOCATION NOT TRANSACTING FOR ALLOCATED QUANTITIES
Note 114156.1 - 11i Move Order Transaction - FAQ
Note 133464.1 - OMSE11i.SQL release 11i script
Note 169677.1 - How to Clean Corrupt (Orphan) Move Order Records From the Database
Note 261795.1 - Transact Move Order Fails for Changed Subinventory - Negative Balance Not Allowed
Note 267376.1 - View/Update Allocations Show No Allocation Details - Move Order - Process Flag = W
Note 280129.1 - How Can One Enter Missing Quanitity in the Transact Move Order Lines Screen
Note 280131.1 - Unable To Search For Move Orders Created By Other User On Find Move Orders
Note 284937.1 - Order line stuck in release to warehouse status and move order line is closed
Note 286649.1 - Order Line Stuck In Release To Warehouse Status And Move Order Line Is Closed
Note 294841.1 - Component Picking Move Order Is Failing For 2nd Line of Move Order When Source Locator Is Overridden
Note 300918.1 - Cannot Backorder Move Order
Note 306394.1 - Process Stuck Wip Component Issue Move Order Transactions -- Stuck with No Error
Note 306930.1 - Transact Move Order Results In App-Inv-05205 After Patch.4035367
Note 307000.1 - Cannot Cancel or Close Move Order Lines For Component Pick Release / Enable MO Backorder
Note 308753.1 - INVTOMAI.fmb: FRM-40112: After Creating A Move Order and Approving It
Note 313327.1 - Cannot Skip Allocations / Cannot Navigate To Next Allocation Without Entering Previous In Move Order Line Using MSCA
Note 315838.1 - Move Order Pick Slip Not Auto-Allocating For Replenishment Move Orders.
Note 330553.1 - TRANSACT MOVE ORDER GIVES APP-INV-05407
Note 333919.1 - Move Order Issue Increasing Inventory Rather Than Reducing
Note 334678.1 - What is INV_MOVEORDER_CANCEL? Transact Move Order form (INVTOTRX) Issues Error INV_MOVEORDER_CANCEL
Note 344900.1 - Destination Locator is getting Cleared when Creating and Saving New Move Order Transaction
Note 351658.1 - Move Order Form (INVTOTRX) Hangs When Transacting
Note 357360.1 - Orphaned Move Order Lines (MTL_TXN_REQUEST_LINES)
Note 364306.1 - Cannot Transact Move Orders After 11.5.10 Upgrade
Note 396523.1 - Stuck Pending Move Order Transactions (MMTT) / Available Shows Zero Because Records Are Stuck In MMTT, Move Order Cancelled By Source in Transact Move Order (INVTOTRX) Form
Note 406182.1 - Move Order Purge Program Failing INV_MO_PURGE.PURGE_LINES
Patch 3720404 - This patch fixes the issue of orphan allocations for a closed move order .This fix is recommended for Customers who are using Inventory Move order functionality
Patch 3855692 - This patch fixes the issue of Allocations not visible on Transact Move order form lines window when performing multiselect and also fix Delivery stuck in release to warehouse status MO closed.This patch recommended for Customers using TMO.
Patch 4582052 - This patch fixes errors thrown and data corruption occurs on Transact Move order form when Transaction quantity is updated from View update allocations.This patch is recommended for customers using Transact Move orders form.
Note 417875.1 - Serial Number FAQ
Note 309710.1 - Cannot Transact Serial Number with Mark ID Populated / Orphan Serial Numbers
Note 338740.1 - Sales Order Cancelled After Pick Release Leaves Pending Move Transactions (Allocations)