Move Order Tables in Oracle Apps 11i and R12

Move orders are basically requests for the movement of material within a single organization. You create a move order, approve and then transact it. Data flows from different tables during the overall process. Here we are going to see the minimum basic information of the Move Order Table in Oracle Apps.

The purpose here is not to reproduce the erm provided by Oracle where you can see the structure of all the database objects for Oracle Apps 11i and R12. We are mainly going to see the base table and some important columns which as a developer you should be aware of.

A move order has header and line information.

MTL_TXN_REQUEST_HEADERS and  MTL_TXN_REQUEST_LINES are the main move order tables in Oracle Apps 11i and R12 that store move order header information and line information respectively. There is a primary and foreign key relationship between those.

MTL_TXN_REQUEST_HEADERS Table

This is the master table that stores move order header information. Header_id is the primary key and request_number holds the move order number. There are other key columns which denotes the order type and transaction type.

  • HEADER_ID – The primary key that has a foreign key relationship with the lines table.
  • REQUEST_NUMBER – Move order number
  • TRANSACTION_TYPE_ID – Transaction Type
  • MOVE_ORDER_TYPE – Move Order Type
  • ORGANIZATION_ID – Organization
  • HEADER_STATUS – Move order header status

MTL_TXN_REQUEST_LINES Table

Table stores move order line level details. Line_id is the primary key and header_id is a foreign key that maps to the header table. There are columns to show quantity information, line status and transaction type, item, etc

  • LINE_ID
  • HEADER_ID
  • LINE_NUMBER
  • ORGANIZATION_ID
  • INVENTORY_ITEM_ID
  • QUANTITY
  • QUANTITY_DELIVERED
  • QUANTITY_DETAILED
  • LINE_STATUS
  • TRANSACTION_TYPE_ID
  • TRANSACTION_SOURCE_TYPE_ID

MTL_MATERIAL_TRANSACTIONS_TEMP Table

Move Instructions in Omron PLC - Tu...

When the move order is allocated, data moves to the mtl_material_transactions_temp table. Here, you will find information like item, quantity, transaction, etc. You can link the mtl_material_transactions_temp table with mtl_txn_request_lines table with column move_order_line_id.

  • TRANSACTION_HEADER_ID
  • TRANSACTION_TEMP_ID
  • INVENTORY_ITEM_ID
  • Quantity information
  • Transaction type, action, source
  • MOVE_ORDER_LINE_ID – These columns hold the move order line id using which you can join with the move order line id columns

MTL_MATERIAL_TRANSACTIONS Table

Finally, data go here when the move order is transacted. Here, you will find information like item, quantity, transaction, etc. You can link the mtl_material_transactions table with mtl_txn_request_lines table with column move_order_line_id.

  • TRANSACTION_ID
  • INVENTORY_ITEM_ID
  • MOVE_ORDER_LINE_ID

Inventor information, quantity, UOM, Transaction type, source, action, Pick slip, putaway, and other information.

Other objects

Below are some of the sequences, views, dfvs which are used by Move order functionality.

  • MTL_TXN_REQUEST_HEADERS_S
  • MTL_TXN_REQUEST_LINES_S
  • MTL_TXN_REQUEST_LINES_SN
  • MTL_TXN_SOURCE_TYPES
  • MTL_TXN_SOURCE_TYPES_S
  • MTL_TXN_REQUEST_LINES_DFV
  • MTL_TXN_REQUEST_LINES_V
  • MTL_TXN_REQUEST_HEADERS_DFV
  • MTL_TXN_REQUEST_HEADERS_V

Summary

We have covered basic tables and related information for Move Orders in Oracle apps. Please share and subscribe if you find this article useful.