MTI (MTL_TRANSACTIONS_INTERFACE) Has Error Code 'Oracle Error' / Duplicates (Doc ID 332211.1)

SYMPTOMS

Find transactions in the transaction open interface table (MTL_TRANSACTIONS_INTERFACE) with the following error.
Also find the following error in the log file.
Resubmitting the transactions does not allow them to process.

Error: In Table

oracle error

Error: In Log

Set and get global variables for date,time,user,encoding,profile values
Error in get_profile_values
ERROR CODE = -6502
ERROR MESSAGE = ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Steps
1. Goto Inventory > Transactions > Transaction Open Interface
2. The find window appears
3. Enter the query criteria.
4. Press Find.
5. The transactions appear showing the error.

Business Impact
Due to this issue, users cannot process transactions.

Verification
The following SQL verified the issue exists:

select error_code from MTL_TRANSACTIONS_INTERFACE
/
oracle error

CAUSE

You maybe experiencing the same situation if one of the following SQLs identifies the issue:

1. Generic Duplicates
The following generic script may return rows rows indicating that there are duplicate transactions. This does not look at a specific type of transaction:

select mmtt.* from mtl_material_transactions_temp mmtt,
mtl_transactions_interface mti
where mmtt.TRANSACTION_TEMP_ID = mti.transaction_interface_id
and mmtt.transaction_type_id = mti.transaction_type_id
and mmtt.organization_id = mti.organization_id
and mmtt.inventory_item_id = mti.inventory_item_id
and mmtt.subinventory_code = mti.subinventory_code
and mmtt.primary_quantity = mti.primary_quantity
and mmtt.transaction_source_type_id = mti.transaction_source_type_id
and mmtt.transaction_action_id = mti.transaction_action_id
and nvl(mmtt.locator_id,-9999) = nvl(mti.locator_id,-9999)
and nvl(mmtt.revision,'@@@') = nvl(mti.revision,'@@@')
and mmtt.transaction_mode = 8
/
> 0 rows

-OR-

2. Duplicate Sales OrdersDuplicate Sales Order records maybe found in the interface table using the scripts from Note.268973.1.  (These scripts only work for sales orders and internal orders.)

select count(*) from MMT_MTI_RECORDS_V
> 0 rows

select count(*) from MMTT_MTI_RECORDS_V
> 0 rows

select count(*) from MMT_MMTT_RECORDS_V
> 0 rows

-OR-

3. Duplicate Work OrdersDuplicate Work Order records maybe found in the interface table that are already in the pending table.

select count(*)
from apps.mtl_material_transactions_temp mmtt, apps.mtl_transactions_interface mti
where mmtt.transaction_source_type_id = 5
and mti.transaction_interface_id = mmtt.transaction_temp_id
and mti.organization_id = mmtt.organization_id
and mti.inventory_item_id = mmtt.inventory_item_id
and mti.subinventory_code = mmtt.subinventory_code
and nvl(mti.locator_id,-9999) = nvl(mmtt.locator_id,-9999)
and nvl(mti.revision,'@@@') = nvl(mmtt.revision,'@@@')
and mti.transaction_source_type_id = 5
and mti.transaction_source_id = mmtt.transaction_source_id
and mti.completion_transaction_id = mmtt.completion_transaction_id
and mti.transaction_action_id = mmtt.transaction_action_id
/
> 0 rows

SOLUTION

For duplicate sales orders, see Note 568012.1 - FAQ: Inventory Standard Datafixes, section B-17. This lists an identification script and possible root-cause patches. A standard datafix to remove the duplicate interface (MTI) record is a good starting point, but then an additional script must be run to resubmit the stuck pending (MMTT) transaction.

If you have this situation, you will need a datafix from Oracle Support:

1. Please gather the following details and upload them to a new SR requesting a datafix for this issue.  Please also reference this note number in your new SR.

2. Please provide the results from the scripts in Note.268973.1 as well as the following SQL results in MS Excel format with column headings:

select mmtt.* from mtl_material_transactions_temp mmtt,
mtl_transactions_interface mti
where mmtt.TRANSACTION_TEMP_ID = mti.transaction_interface_id
and mmtt.transaction_type_id = mti.transaction_type_id
and mmtt.organization_id = mti.organization_id
and mmtt.inventory_item_id = mti.inventory_item_id
and mmtt.subinventory_code = mti.subinventory_code
and mmtt.primary_quantity = mti.primary_quantity
and mmtt.transaction_source_type_id = mti.transaction_source_type_id
and mmtt.transaction_action_id = mti.transaction_action_id
and nvl(mmtt.locator_id,-9999) = nvl(mti.locator_id,-9999)
and nvl(mmtt.revision,'@@@') = nvl(mti.revision,'@@@')
and mmtt.transaction_mode = 8
/

 

3. Please provide the following output in MS Excel format with column headings:

select from MTL_TRANSACTIONS_INTERFACE