Quantity Delivered vs Detailed in Move Order Lines

Quantity Delivered vs Detailed in Move Order Lines (MTL_TXN_REQUEST_LINES) [ID 431479.1]

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

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

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 = '&EnterMONumber'
/

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.

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
/

7. How to Remove Cancelled and Closed 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 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). The move order purge was introduced in 11.5.10. In 11.5.8 and 11.5.9, users would use the Move order form. (INVTOMAI) via Tools > Purge order. See Note 468322.1.

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 - HTMOMSE Sales Order Diagnostic Script
NOTE:169677.1 - How to Remove 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 Lines Stuck In Release To Warehouse Status with Error "The material pick release and pick confirm process completed with errors"
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:309710.1 - Cannot Transact Serial Number with Mark ID Populated / Orphan Serial Numbers
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:338740.1 - Sales Order Cancelled After Pick Release Leaves Pending Move Transactions (Allocations)
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 - Orphan 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
NOTE:417875.1 - Serial Number FAQ
NOTE:456127.1 - DUPLICATE MOVE ORDER ISSUES TRANSACTIONS IN MMT
NOTE:468322.1 - Why In Move Order Form. (INVTOMAI) Purge Order Option Is Not Available In 11.5.10
NOTE:568012.1 - FAQ: Inventory Standard Datafixes
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: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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-687796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15225049/viewspace-687796/