Move Order Transactions – Oracle Inventory R12

Oracle R12.2.4 was the Oracle EBS which i was implementing until last year of 2015. In that project, i’ve become technical consultant for Inventory and Warehouse Management (WMS) module. This post had some knowledge about move order transactions based on technical side.

There were some tables for verificate move order transactions.
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.

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.

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.
Field move_order_header_id and move_order_line_id were provided in this table, which related to MTL_TXN_REQUEST_HEADERS and MTL_TXN_REQUEST_LINES.

MTL_MATERIAL_TRANSACTIONS
This is the store of transact move order transactions. And there were some queries related with it.

For some additional notes:

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

Some illustration for investigate activity:

  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)
/* Purpose : show move order transactions relate with pending transactions   *
*            this query can be used at Oracle R12                            */

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;
/* Purpose : 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;
/* Formatted on 6/9/2015 6:15:51 PM (QP5 v5.163.1008.3004) 
* Purpose : query for investigate transaction_source at MTL_TRANSACTIONS_TEMP     *
*                    based on transaction_source_id                                                          */

SELECT MMTT.TRANSACTION_SOURCE_ID,
       CASE
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 1
          THEN
             (SELECT 'RSH_NUMBER: ' || RCV.SHIPMENT_NUM
                FROM RCV_SHIPMENT_HEADERS RCV, RCV_TRANSACTIONS RCVT
               WHERE RCV.SHIPMENT_HEADER_ID = RCVT.SHIPMENT_HEADER_ID
                     AND RCVT.TRANSACTION_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 2
          THEN
             (SELECT 'SO_NUMBER: ' || SEGMENT1
                FROM MTL_SALES_ORDERS
               WHERE SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 4
          THEN
             (SELECT 'MO_NUMBER: ' || REQUEST_NUMBER
                FROM MTL_TXN_REQUEST_HEADERS
               WHERE REQUEST_NUMBER = TO_CHAR (MMTT.TRANSACTION_SOURCE_ID))
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 5
          THEN
             (SELECT 'WIP_NUMBER: ' || WIP_ENTITY_NAME
                FROM WIP_ENTITIES
               WHERE WIP_ENTITY_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 7
          THEN
             (SELECT 'INT_REQ_NUMBER: ' || RCV.SHIPMENT_NUM
                FROM RCV_SHIPMENT_HEADERS RCV, RCV_TRANSACTIONS RCVT
               WHERE RCV.SHIPMENT_HEADER_ID = RCVT.SHIPMENT_HEADER_ID
                     AND RCVT.TRANSACTION_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 8
          THEN
             (SELECT 'INT_SO_NUMBER: ' || SEGMENT1
                FROM MTL_SALES_ORDERS
               WHERE SALES_ORDER_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 10
          THEN
             (SELECT 'PHY_NAME: ' || PHYSICAL_INVENTORY_NAME
                FROM MTL_PHYSICAL_INVENTORIES
               WHERE PHYSICAL_INVENTORY_ID = MMTT.TRANSACTION_SOURCE_ID)
          WHEN MMTT.TRANSACTION_SOURCE_TYPE_ID = 13
          THEN
             (SELECT 'WIP_NUMBER: ' || WIP_ENTITY_NAME
                FROM WIP_ENTITIES
               WHERE WIP_ENTITY_ID = MMTT.TRANSACTION_SOURCE_ID)
          ELSE
             'NULL'
       END
          SRC_NUMBER
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT;

Source :

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s