Transaction Open Interface (TOI) and Pending Transactions in Oracle Apps

ORACLE SCM FUNCTIONAL, ORACLE SCM TECHNICAL, TECHNOLOGY
Spread the love..!
  • 1
    Share

Transaction Open Interface – This form queries the MTL_TRANSACTIONS_INTERFACE table.
Nav: Inventory Responsibility -> Transactions -> Transaction Open Interface.

Pending Transactions – This form queries the MTL_MATERIAL_TRANSACTIONS_TEMP table.
Nav: Inventory Responsibility -> Transactions -> Pending Transactions.

oracle-inventory-transaction-flow

Use of MTL_TRANSACTIONS_INTERFACE table and TOI window.

MTL_TRANSACTIONS_INTERFACE (MTI) table is the open interface provided by Oracle to bridge between non-Inventory applications and the Inventory Transactions module.

For example, If any transaction happens in OM then tranasaction data is passes to this open interfface for validation.
The Inventory Transactions Manager (Process Transaction interface – INCTCM) reads records from the MTI table, validates them and moves the successful transactions into the MTL_MATERIAL_TRANSACTIONS_TEMP table, and submits Inventory Transaction workers (sub-processes – INCTCW) which then process these records through inventory. So in concise we can say that this process consists of data derivation, validation, and the transfer of records from MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE into their associated TEMP (temporary) tables from where the transactions processor worker processes them.

Use of Transaction Open Interface (TOI) window

If any record get stuck while being processed by Process Transaction Interface then those records can be seen through this form. With STUCK I mean records remain in MTI and not able to move to its corresponding TEMP table. In that case we check the error message through this form and once error reason is solved(like inadequate on-hand, period not open etc) then we can re-submit this record from TOI form. Then Process transaction Interface will re-process this record and if validated then record will move to its corresponding TEMP table.

Using the Transaction Open Interface window, we can view stuck transactions and correct and resubmit those transactions.

To resubmit Stuck transactions (in TOI) for processing:

    1. Check the Submit option next to the transactions you want to resubmit or choose Resubmit All.
    2. Click on Save to submit the transactions for processing.

Use of MTL_MATERIAL_TRANSACTIONS_TEMP table

After initial validation by the Transactions Manager (Process Transaction Interface – INCTCM) from the MTL_TRANSACTIONS_INTERFACE the transactions are then passed to the MTL_MATERIAL_TRANSACTONS_TEMP table. To process the records and move to MTL_MATERIAL_TRANSACTONS table a job id is attached and a Transactions Worker (INCTCW) is submitted by the INCTCM process. MTL_MATERIAL_TRANSACTIONS_TEMP hold data only till transaction is in process, after transaction is complete data from MTL_MATERIAL_TRANSACTIONS_TEMP gets deleted and posted to MTL_MATERIAL_TRANSACTIONS.
This Temp table is also used by the Inventory and Purchasing modules which write transactions directly into this table after validation.

Miscellaneous transactions(Inventory module form) writes directly into MMTT (temp) table. The transactions which are created through this Misc. Transaction form are via on-line processing.

A useful select query SQL to find the specific source number and link it to TRANSACTION_SOURCE_ID is:
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.

For further details on how to solve Pending Transaction stuck, please refer to Note Below.


Spread the love..!
  • 1
    Share

One thought on “Transaction Open Interface (TOI) and Pending Transactions in Oracle Apps

Your Comments