错误信息1：mtl_transactions_interface,货位段无效
原因：mtl_transactions_interface.locator_id值不正确或为空，或其他异常情况
错误信息2：mtl_transactions_interface,|物料挑库发放和挑库确认流程完成时出现错误，|程序包 INV_Trolin_Util 过程 Query_Rows 中出现错误
Pending Transactions Stuck With Error The Material Pick Release and Pick Confirm Process Completed With Errors
原因：mtl_transactions_interface.source_line_id的值应该为物料搬运单的行ID。
错误信息2：mtl_transactions_interface,|物料挑库发放和挑库确认流程完成时出现错误
原因：接口表子库存与搬运单行的子库存不一致。
搬运单的子库存和库位需要一致。
v_misc_trans_tbl mtl_transactions_interface%ROWTYPE;
v_misc_lot_tbl mtl_transaction_lots_interface%ROWTYPE;
v_misc_trans_tbl.transaction_interface_id := v_trx_interface_id;
v_misc_trans_tbl.transaction_type_id := rec_misc_in2.transaction_type_id; --事务处理类型 小类
v_misc_trans_tbl.source_code := 'MISC';
v_misc_trans_tbl.source_header_id := rec_misc_in2.header_id;
v_misc_trans_tbl.source_line_id := 55060;--rec_misc_in2.line_id;
v_misc_trans_tbl.process_flag := 1; --ready
v_misc_trans_tbl.transaction_mode := 3; --后台处理
v_misc_trans_tbl.inventory_item_id := rec_misc_in2.inventory_item_id;
--v_misc_trans_tbl.item_segment1 := rec_misc_in2.epi_item;
v_misc_trans_tbl.organization_id := rec_misc_in2.org_id;
v_misc_trans_tbl.subinventory_code := rec_misc_in2.subinventory;
v_misc_trans_tbl.locator_id := v_loc_id;
v_misc_trans_tbl.transaction_quantity := rec_misc_in2.epi_qty;
v_misc_trans_tbl.transaction_uom := rec_misc_in2.item_uom;
v_misc_trans_tbl.transaction_date := SYSDATE;
--v_misc_trans_tbl.transaction_source_type_id := 4; --Move Order
v_misc_trans_tbl.transaction_source_id := rec_misc_in2.header_id; --事务处理类型 小类
v_misc_trans_tbl.transaction_source_name := rec_misc_in2.order_number;
v_misc_trans_tbl.wip_entity_type := NULL;
v_misc_trans_tbl.operation_seq_num := NULL;
v_misc_trans_tbl.last_update_date := SYSDATE;
v_misc_trans_tbl.last_updated_by := fnd_global.user_id;
v_misc_trans_tbl.creation_date := SYSDATE;
v_misc_trans_tbl.created_by := fnd_global.user_id;
入库：
mtl_transactions_interface
事务处理来源 -- GL 帐户无效。请重新输入 事务处理来源无效
v_misc_trans_tbl.transaction_source_id :=
v_misc_trans_tbl.transaction_source_name :=
这两个字段搞错了。
子库存转移
减少的一边才会生成会计科目。
会计科目是从 设置->组织->子库存->物料账户 取得。
一般子库存转移，接口表transaction_source_id不需要赋值。
因为TRANSACTION_SOURCE_TYPE_ID指定为13（即Subinventory Transfer），那么这时候他的会计科目就确定了。
以下資料可供參考：
Provide basic understanding about Oracle Inventory Transactions and Cost
processing procedure.
SCOPE & APPLICATION
-------------------
Oracle manufacturing customers, support, and consulting.
Transactions - Unprocessed Transactions/Closing INV Accounting Period
(TRXN-STK)
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 resolution of
Interface related issues.
CONTENTS
--------
Q1. What is the MTL_TRANSACTIONS_INTERFACE table used for?
Q2. What is the MTL_MATERIAL_TRANSACTIONS_TEMP table used for?
Q3. What type of issues will you see with the MTL__TRANSACTIONS_INTERFACE and
MTL_MATERIAL_TRANSACTIONS_TEMP tables?
Q4. What type of issues will you see with the MTL_MATERIAL_TRANSACTIONS table?
Q5. What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table?
Q6. Where do you find Pending/Errored Inventory Transactions?
Q7. Where do you find Pending/Errored WIP Move and WIP Resource Transactions?
Q8. What are the major tables involved in Material Movement for Inventory and
WIP Material, Move, and Resource Transactions?
Q9. How do you resolve Pending Material Transactions?
Q10. How do you resolve Pending WIP Cost Transactions?
Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE
table, into what column is the PO number input?
Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE?
FAQ: Frequent Asked Questions
=============================
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.
There is an Interface Manager called the Transactions Manager (INCTCM) which
reads records from this table, validates them and moves the successful
transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Transaction
workers (sub-processes - INCTCW) which then processes these records through
inventory. This process consists of data derivation, validation,
and transfer of records from MTL_TRANSCTIONS_INTERFACE,
MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE
into their respective 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. Example :- In the case
of an Sales Order, the item is being shipped to a client so the lot and serial
number if being used, this needs to be updated to show that it is no
longer available in 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. Once the transactions have been passed to this 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 in order tothe
get the records processed and moved to the MTL_MATERIAL_TRANSACTONS
table.
This table is also used by Inventory Module and Purchasing module which
writes directly onto this table for any transactions entered within
itself and each transaction in turn through a process of strict
validation.
Inventory Module Forms like Miscellaneous transactions writes directly
into this table. The transactions which are done through these form. are
on-line processing. It is from here that the inventory quantities finally
get updated,serial/lot numbers get marked as being used.
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 the list of errors that you may see in 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 simple 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
that the future 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 valid for the
organization source. If not set up the UOM. It may also be a case
where the conversion classes have 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 -> flexfeilds -> key ->
segments 'stock locators',check 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 are MTL_TRANSACTION_INTERFACE table.
Basically in order to check if the distribution account correct we need to
join the details back to the GL_CODE_COMBINATION table to validate the
account code being used for the transaction.Inquiry on the error_explanation
field as this may identify what segment is actually incorrect.This might
return result APP-1756 Value 'XXXX' is not defined.
Need to check that the segments point to a valid row in
GL_CODE_COMBINATIONS.
APP-05372 Transaction processor error
For this error you really need to get the ERROR_EXPLANATION to
find out the exact message as to why it is possibly failing.
These can usually have their three flags reset and re-submitted
as this error is usually attached to those records in a group
where one of the records has been erred with one of the more
severe errors.
APP-05526 Invalid locator
There is a list of checks that you need to make to ensure that
the above error is correct.
1.Extract Organization_id and cross check tables to see if the
org is under locator control.
2.Are the locator details filled incorrectly on the transaction
3.Has the Organisation set-up been modified.
APP-05518 Negative balances not allowed.
This message will be prompted if there is not enouhg on hand quantity
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,
it means we just wants to ship it out from that
subinventory/locator and if there is not enough available to
reserve/transact in that particular subinventory/locator the
inventory program will error out with the above errors. However, if
there are no subinventories/locators specified while
entering the order, we will pick up from where its available
in that Organization.
2. Check onhand and availabe 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 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
Period is not currently open for the transaction to be costed
against later.
1.Check that the period is open
2.Check that the date is a date between the open and close period.
Q4. What type of issues will you see with the MTL_MATERIAL_TRANSACTIONS table?
A4. Transaction is valued (unit cost*quantity = $ amount)
Once data gets here the transaction is almost complete except for
the fact that it needs to have the costing details finally updated
and written to the Transaction Accounts table for transfer to
General Ledger. This is a transactional historical table. However,
it is the holding point for transactions to be cost.
The Cost Manager is a concurrent job that runs in the background.
(CMCTCM)
COSTED_FLAG = 'N'(Not Processed) This means record is pending.
If it is NULL then the transaction has already been
processed.
If it is set to 'E' then it has erred for some
reason.
Q5. What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table?
A5. The details of the transactions are usually written into this table
as Debits and Credits and from here ct can kick off Transfer to GL
Navigation Path -> Inventory Responsibility -> Accounting Close Cycle
-> General Ledger Transfers) as a concurrent request takes the
transactions with a GL_BATCH_ID of -1 and passes them to the
GL_INTERFACE for processing. When it passes the transactions to the
GL_INTERFACE it is at this time that the batch is given it's proper
batch number.
Now there is a new functionality introduced. When transfer to GL is done,
there is a column called GROUP_ID which gets populated in gl_interface
table with gl_batch_id. GL BATCH identifier is used in the GROUP ID field,
only the one batch which is in error would be prevented from being imported
and posted. Also, adding a GROUP ID would allow for a more definitive
level of control not only across locations but within a location.
Patches which gives this functionality:
REL 10.7: 853450
REL 11.0: 1114961
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 show pending records, These must be fixed prior to
trying to close the period.
-- 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
inventory accounting periods.
A6.4. Navigate Inventory Responsibility -> Transactions ->
Material Transactions
-- Records can be viewed from this form. to know whether the
transactions has costed flag as '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?
A8.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.
A8.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.
Error 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
erred 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. Refer to the column of transaction_reference
and this column will allow the po number. The 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 GL code combinations. This account id is required to
create a complete audit trail of the transaction.
