Scope and Application
This document will be used to explain the flow of transactions from the MTL_TRANSACTIONS_INTERFACE to the GL_INTERFACE.
The aim of this document is to help and assist in the resolution of transaction Interface related issues. Unprocessed Transactions / Closing INV Accounting Period FAQ
Q1. What is the MTL_TRANSACTIONS_INTERFACE table used for?
A1. This is the Interface between non-Inventory applications and the Inventory
Transactions module.
In other words any other module other than Inventory that wants to update Inventory has to come through this table. Modules such as WIP (Work In Progress) and OE (Order Entry) first pass their records to the
MTL_TRANSACTIONS_INTERFACE (MTI) for validation.
The Transactions Manager (INCTCM) reads records from the MTI table, validates them and moves the successful transactions into the MTL_MATERIAL_TRANSACTIONS_TEMP table, and submits Transaction workers (sub-processes - INCTCW) which then process these records through inventory. 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 processes them.
Both the Lots and Serial number tables above are used when items being updated are under 'Lot' or 'Serial' number control. For example :- In the case of a Sales Order, if the item is being shipped to a client and the lot and serial number are being used, these tables need to be updated to show that the lot or serial number is no longer available in the Inventory stock.
It is important to note that in general the processors will not move the transactions from this table if the following fields are not set as follows.
LOCK_FLAG = 2
PROCESS_FLAG = 1
transaction_mode = 3
Q2. What is the MTL_MATERIAL_TRANSACTIONS_TEMP table used for?
A2. The transactions are passed to the MTL_MATERIAL_TRANSACTONS_TEMP table after initial validation by the Transactions Manager (INCTCM) from the MTL_TRANSACTIONS_INTERFACE. A job id is attached and a Transactions Worker (INCTCW) is submitted by the INCTCM process to get the records processed and moved to the MTL_MATERIAL_TRANSACTONS table.
This table is also used by the Inventory and Purchasing modules which write transactions directly into this table after validation.
Inventory Module Forms like the Miscellaneous transactions form write directly into this table. The transactions which are created through these form are via on-line processing. It is from this table that the inventory quantities finally get updated and serial/lot numbers get updated.
Q3. What type of issues will you see with the MTL__TRANSACTIONS_INTERFACE and MTL_MATERIAL_TRANSACTIONS_TEMP tables?
A3. The issues can be related to stuck transactions in these tables.
The following is a list of errors that you may see in the error_code, error_explanation columns in the above tables:
NOTE: In Release 11 the APP-XXX errors do not show any more.
ERROR_CODE ERROR EXPLANATION
APP-05367 Invalid Item
APP-05341 Invalid sub-inventory
APP-05343 Invalid transaction date
APP-05345 Invalid transaction UOM
APP-05354 Invalid locator segments
APP-05365 Invalid distribution account segments
APP-05372 Transaction processor error
APP-05526 Invalid locator
APP-05518 Negative balances not allowed
APP-05064 Quantity must be less than or equal to available to transact for that item at its specific control level
APP-05520 No open period found for date entered
Account period
Lot records
Serial records
APP-05367 Invalid Item
Check the attributes against the set required for the transaction type.
i.e. Order entry, PO etc.
APP-05341 Invalid Subinventory
Check and make sure that this record does not already exist on the MTL_MATERIAL_TRANSACTIONS_TEMP table as sometimes the process does not clear some of the records out of MTL_TRANSACTIONS_INTERFACE correctly.
If this is the case it is a matter of deleting the records from the MTL_TRANSACTIONS_INTERFACE.
Check the subinventory against the available MTL_SECONDARY_INVENTORIES on the system for the given ORGANIZATION_ID against this transaction.
If the subinventory exists, check the TRANSACTION_TYPE_ID, for example:
If it is a Purchase Order then you need to check the MTL_SUPPLY to ensure that the supply exists for this transaction.
APP-05343 Invalid transaction date
In certain circumstances, and depending on the transaction type, the accounting period is not open. There is no issue in changing the date to the current date and resubmitting it through the system.
APP-05345 Invalid transaction UOM
Check the UOM measure being used and find out if it is valid for the organization source. If not then the UOM needs to be set up. This error can also be due to the conversion classes having not been set-up.
APP-05354 Invalid locator segments
APP-05355 The Locator segments are invalid for the given sub inventory, organization Combination.
Check under Inventory Responsibility -> set-up -> flexfields -> key -> segments 'stock locators'. Ensure each segment has been enabled.
APP-05365 Invalid distribution account segments
The distribution account segments has its base in the Account Key Flexfield set-up. It is this Flexfield structure that determines how many segments should be populated into the segments within the MTL_TRANSACTION_INTERFACE table.
In order to check if the distribution account is correct we need to join the details back to the GL_CODE_COMBINATION table to validate the account code being used for the transaction. Check the error_explanation field as this may identify what segment is actually incorrect for example:
APP-1756 Value 'XXXX' is not defined.
Ensure that the segments point to a valid row in the GL_CODE_COMBINATIONS table.
APP-05372 Transaction processor error
For this error check the ERROR_EXPLANATION to find out the exact message as to why it is possibly failing.
These error'd transactions can usually have the three processing flags reset and can be re-submitted as the error is usually attached to records in a group where one of the records has errored with a more severe error.
APP-05526 Invalid locator
This is a list of checks to perform:
1.Extract the Organization_id and cross check tables to see if the org is under locator control.
2.Are the locator details filled in correctly on the transaction
3.Has the Organisation set-up been modified.
APP-05518 Negative balances not allowed.
This message will be given if there is not enough on hand inventory to satisfy all the transactions within a batch. Therefore, ensure that the transaction quantity is enough to satisfy the sum of all the transaction quantities for a particular batch.
APP-05064 Quantity must be less than or equal to Available to transact for that item at its specific control level. (For Example: subinventory,locator,lot,serial)
1. If you specify a subinventory/locator while entering the order, the system will attempt to ship the items from that subinventory/locator. If there is not enough available stock to reserve/transact in that particular subinventory/locator the program will error out with the above error. However, if there are no subinventories/locators specified while entering the order, the system will pick the stock from where it finds available stock in that Organization.
2. Check onhand and available quantity for the errored items.
3. The records would have errored out due to some other row in the batch not having enough stock, so the entire batch would fail. Find the row that has the error explanation populated. Resubmit all the other rows except the one which has error explanation , if the same error occurs for other rows, do the same process once again excluding that row.
4. Please note that Available Quantity = On Hand Quantity - Reservations (Soft and Hard Reservations).
APP-05520 No open period found for date entered
The Accounting Period is not currently open for the transaction.
1.Check that the period is open for the transaction date
Q4. What type of issues will you see with the MTL_MATERIAL_TRANSACTIONS table?
A4. Transaction is valued (unit cost*quantity = $ amount)
Once the data gets here the transaction is almost complete except for the fact that it needs to have the costing details finally updated to the MTT row and the accounting details written to the MTL_TRANSACTION_ACCOUNTS table for transfer to the General Ledger. This is a transactional historical table and is the holding point for transactions to be costed.
The Cost Manager is a concurrent job that runs in the background. (CMCTCM)
When the COSTED_FLAG = 'N' (Not Processed) - this means the record is awaiting costing.
If the COSTED_FLAG = NULL then the transaction has already been processed.
If the COSTED_FLAG = 'E' then the transaction has errored during costing and the ERROR_CODE and ERROR_EXPLANATION fields need to be checked.
Q5. What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table?
A5. The details of the transactions are written into this table as Debits and Credits. This data is picked up when running the General Ledger Transfer routine:
Navigation Path -> Inventory Responsibility -> Accounting Close Cycle -> General Ledger Transfers)
This concurrent request takes the transactions with a GL_BATCH_ID of -1 and passes them to the
GL_INTERFACE for processing and the batch is given it's proper batch number.
When the transfer to GL is completed, a column called GROUP_ID gets populated in the GL_INTERFACE table with a gl_batch_id. The GL BATCH identifier is used in the GROUP ID field,
so only the batch which is in error is prevented from being imported and posted. Also, adding a GROUP ID allows for a more definitive level of control not only across locations but within a location.
Q6. Where do you find Pending/Errored Inventory Transactions?
A6.1. Navigate to Inventory Responsibility -> Accounting Close Cycle -> Inventory Accounting Periods
-- Select Pending Button related to an OPEN period.
-- In the top region -- Resolution Required
-- If it shows pending records, These must be fixed prior to the system allowing the period to be closed.
-- The form only shows a count of pending records against the period.
A6.2. Navigate Inventory Responsibility -> Transactions -> Transaction Open Interface
-- This form queries the MTL_TRANSACTIONS_INTERFACE table
-- Records can be resubmitted from this form.
A6.3. Navigate Inventory Responsibility -> Transactions -> Pending Transactions
-- This form queries the MTL_MATERIAL_TRANSACTIONS_TEMP table
-- Records can only be resubmitted via the form.
-- Records must be processed out of this form in order to close the inventory accounting periods.
A6.4. Navigate Inventory Responsibility -> Transactions -> Material Transactions
-- Records can be viewed from this form to see whether the transactions have the costed flag set to 'YES' or 'No', also other related columns under Alternate Region -> 'Reason/Reference'.
-- We re-submit the records only through SQL.
Q7. Where do you find Pending/Errored WIP Move and WIP Resource Transactions?
A7.1. Navigate Work in Process Responsibility -> Move Transactions -> Pending Move Transactions
-- This form queries the WIP_MOVE_TXN_INTERFACE table
-- Records can be updated, deleted, and resubmitted via the form.
A7.2. Navigate Work in Process Responsibility -> Resource Transactions -> Pending Resource Transactions
-- This form queries the WIP_COST_TXN_INTERFACE table
-- Records can be updated, deleted, and resubmitted via the form.
Q8. What are the major tables involved in Material Movement for Inventory and WIP Material, Move, and Resource Transactions?
A8. See below:
MTL_TRANSACTIONS_INTERFACE
-- INCTCM is the Transaction Manager for this Table.
MTL_MATERIAL_TRANSACTIONS_TEMP
-- INCTCM is the Transaction Manager for this Table
MTL_MATERIAL_TRANSACTIONS
-- CMCTCM is the Cost Manager for the records to be costed in this table.
-- CMCCCM is the Cost Collection Manager for the records to be imported to Project Mfg.
WIP_MOVE_TXN_INTERFACE
-- WIP Move Transaction Worker processes records in this table (WICTCM)
WIP_MOVE_TRANSACTIONS
-- Must be viewed and resubmitted via SQL with the scripts below
WIP_COST_TXN_INTERFACE
-- Resource Cost Worker processes records in this table.
Q9. How do you resolve Pending Material Transactions?
A9. Resolving Pending Material Transactions is a process of determining and fixing what is preventing a record from being processed through the MTL_TRANSACTIONS_INTERFACE table. Details of pending transactions can be viewed through the application by navigating to the Transaction
Open Interface form.
Using the Transaction Open Interface window, you can view, edit, correct and resubmit transactions received through the open interface.
There are five selections in the alternate region list of values: Error, Location, Source, Intransit and Other. These provide detailed information to help you resolve pending transactions. In the Error alternate region:
a. The error code describes the error on the last attempt to process the line item.
b. The error explanation gives a reason for the error.
c. The process flag indicates whether the row has been processed by the concurrent manager.
The process flag status codes are:
1 = Pending
2 = Running
3 = Error
Resubmitting Transactions for Processing:
In the Transaction Open Interface window, check the Resubmit [] box next to the transaction you want to resubmit or choose Resubmit All from the Special menu. If you have many transactions to resubmit,
use the Resubmit All button to select all transactions for processing and then selectively deselect individual transactions you do not want to resubmit. Save your work to submit the transactions for processing.
Q10. How do you resolve Pending WIP Cost Transactions?
A10. Resolving Pending WIP Cost transactions is a process of determining and fixing what is preventing a record from being processed through the WIP_COST_TXN_INTERFACE table. Pending transactions can be viewed through the application by navigating to the WIP Responsibility -> pending resource transaction form.
In the Pending Resource Transaction window you can view, update, delete, and resubmit resource transactions that have failed validation and remain in the WIP_COST_TXN_INTERFACE table. You can also resubmit transactions whose concurrent process has failed and have a processing phase of Complete and process status of Error. There are eight selections in the alternative region list of values: Processing,
Source, Concurrent Request, Job or Schedule Name, Operation, Resource, Transaction and Comments.
Errored transactions will have the Transaction ID and Group ID populated and the Status will be error. Pending transactions will have the Transaction Id and Group ID fields blank and the Status will be pending.
To view error details for failed resource transactions, select the errored transaction and click on the [Error] button. The Pending Resource Transaction error window appears. Column indicates the name of the column in the resource transaction interface table (WIP_COST_TXN_INTERFACE) that failed validation. Message indicates why the transaction failed.
To resubmit failed resource transactions:
In the Pending Resource Transactions widow either check the transaction's Resubmit check box to resubmit one record or choose select All for Resubmit from the Special Menu then save your work.
Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE table, into what column is the PO number input?
A11. There is not a direct column for PO number.In the Oracle Manufacturing Implementation Manual, locate the section on Open Transactions Interfaces. This section describes the transaction type id's and
there is not a PO number. The transaction_reference column can be used to hold the PO number. This column is optional and shows up on reports and transaction displays. Since a column for the PO number does not directly exist, a transaction_reference field can be used to input this information.
In Release 10.7, we do a Miscellaneous Receipt (PO Receipts are not supported).
In Release 11, Purchasing Module has a Receiving Interface and we do not see any data coming to this table anymore.
Customers that currently have release 10.7 and do not have this functionality will need to upgrade to release 11.0 or higher, which contains the production version of this functionality.
Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE?
A12. All issues and receipts of asset items to an asset subinventory and sales order shipments require the distribution account id that originates from the GL code combinations. This account id is required to
create a complete audit trail of the transaction.
