Inventory Standard Datafix Instruction #17: Fixing Duplicate
Sales Order Transactions In MTI, MMTT and MMT
In this Document
Goal
Fix
A - Identification Scripts.
Identify
Symptoms
Symptom List
B - Verifying your File Versions.
C -
Root Cause Analysis.
1. Duplicate Transactions After One In
Batch Failed
2. New Root- Causes?
D - Datafix.
1. Identify data.
2. Backup data.
3. Run the datafix script.
4- Check the data.
References
Applies to:
Oracle Inventory Management - Version 11.5.10.2 and later
Information in this document applies to any platform.
Error occurred while relieving reservations:
INV_RSV_RLF_FAILED
Goal
This document provides detailed instructions for removing
duplicate sales order transactions between the various inventory transaction
tables: Transaction interface
(MTL_TRANSACTIONS_INTERFACE MTI), pending transactions
(MTL_MATERIAL_TRANSACTIONS_TEMP MMTT) and the transaction history
(MTL_MATERIAL_TRANSACTIONS MMTT). The
scripts focus on sales orders and have no affect on inventory transactions like
miscellaneous receipts or WIP transactions. Also this document will show all
the available information regarding the Root Cause of this data corruption.
Please follow up this document in the following order:
A. Identification Scripts: This will confirm if you are
having this data corruption.
B. Verifying your file versions: This will allow you and
support to confirm if you are already in a known fixed version.
C. Root Cause Analysis. It shows all the relevant
information about this issue Root Cause.
D. Datafix Intructions. It will provide steps for fixing the
data corruption.
Fix
A - Identification Scripts.
Identify
Please run the identification script (INV17-ident.sql)
uploaded into this document.
PROMPT
PROMPT INV17-ident.sql
PROMPT From Note:1472074.1 Inventory Standard Datafix
Instruction #17:
PROMPT ... Fixing Duplicate Sales Order Transactions In MTI,
MMTT and MMT
PROMPT The script is based on details in Bug 4286083.
PROMPT The sql identifies if the transactions are duplicate
transactions
PROMPT in the inventory transactions tables.
PROMPT The sql is ONLY valid for Order Management duplicate
transactions
PROMPT and will NOT identifyother sources like WIP jobs or
inventory aliases.
PROMPT
PROMPT a. MMT vs. MTI
PROMPT Count Duplicates Between Historical transactions
(MMT) vs. Interface transactions (MTI)
SELECT count(*)
FROM mtl_material_transactions b, mtl_transactions_interface
a
WHERE a.picking_line_id = b.picking_line_id
AND a.trx_source_line_id = b.trx_source_line_id
AND a.inventory_item_id = b.inventory_item_id
AND b.transaction_type_id = a.transaction_type_id
AND b.transaction_source_type_id in (2,8)
AND b.picking_line_id is not null ;
PROMPT b. MMTT vs. MTI
PROMPT Count Duplicates Between Pending transactions (MMTT)
vs. Interface transactions (MTI)
SELECT count(*)
FROM mtl_material_transactions_temp b, mtl_transactions_interface a
WHERE a.picking_line_id = b.picking_line_id
AND a.trx_source_line_id = b.trx_source_line_id
AND a.inventory_item_id = b.inventory_item_id
AND b.transaction_type_id = a.transaction_type_id
AND b.transaction_source_type_id in (2,8)
AND b.picking_line_id is not null ;
PROMPT c. MMT vs. MMTT:
PROMPT Count Duplicates Between Historical transactions
(MMT) vs. Pending transactions (MMTT)
SELECT count(*)
FROM mtl_material_transactions b, mtl_material_transactions_temp a
WHERE a.picking_line_id = b.picking_line_id
AND a.trx_source_line_id = b.trx_source_line_id
AND a.inventory_item_id = b.inventory_item_id
AND b.transaction_type_id = a.transaction_type_id
AND b.transaction_source_type_id in ( 2,8)
AND b.picking_line_id is not null;
Symptoms
The symptoms for this issue can be similar to Note:1471606.1
Inventory Standard Datafix Instruction #16: Fixing Stuck transaction with
'Error Occurred While Relieving Reservations'. In addition, you might find
transactions preventing period close when the records are actually duplicates
that can be deleted.
Symptom List
Here are some common observations, symptoms and errors that
identify this issue.
Action Result
Close period The
period cannot close due to pending transactions.
Pick release Pick
release backorders line
Transact or allocate move order
Transact or allocate fails. Receive an error:
The material sourcing process failed to create picking
suggestions for line
View Material Workbench Available Quantity Onhand quantity is MORE than available quantity
but you have no current move orders or reservations.
Ship confirm Onhand
quantity is MORE than available quantity but you have no current move orders or
reservations.
Ship confirm
Ship confirm fails. Receive an error:
An error occurred while relieving reservations
(Code: INV_RSV_RLF_FAILED)
B - Verifying your File Versions.
Execute the following from your server for getting key file
versions of the following files:
TrxProcessor.java
BaseTransaction.java
INVTXMGB.pls
INVTRXWB.pls
You can use SQL like the following:
set serveroutput on
PROMPT : - - - - : TrxProcessor.java : - - - - :
exec
fnd_aolj_util.getClassVersionfromDB('oracle.apps.inv.transaction.server.TrxProcessor');
PROMPT : - - - - : BaseTransaction.java : - - - - :
exec
fnd_aolj_util.getclassversionfromdb('oracle.apps.inv.transaction.server.BaseTransaction');
PROMPT : - - - - : INVTXMGB.pls : - - - - :
SELECT text FROM dba_source
WHERE name =
'INV_TXN_MANAGER_PUB'
AND line < 3;
PROMPT : - - - - : INVTRXWB.pls : - - - - :
SELECT text FROM dba_source
WHERE name =
'INV_LPN_TRX_PUB'
AND line < 3;
Or review the AppsCheck output from Note 276207.1 for
application parameter: Inventory
C - Root Cause
Analysis.
The following Root Causes have been identified at this
moment.
1. Duplicate Transactions After One In Batch Failed
While processing transactions through open interface, if
batch id is populated and if the first row in a batch fails then the previous
batch, which got successfully processed was not getting deleted from pending
transactions table and open interface table.
Release Bug Patch Filename Version
R11.5.8 Bug
3024133 Patch 2640488 BaseTransaction.java 115.111.11580.5+
R11.5.10 Bug
5710072 Patch 5935177 BaseTransaction.java 115.235.115100.36+
R12.0.x Bug 5748351 Patch 6728000:R12.0.6 TrxProcessor.java
BaseTransaction.java
INVTXMGB.pls
INVTRXWB.pls 120.10.12000000.4+
120.39.12000000.2+
120.25.12000000.3+
120.34.12000000.11+
2. New Root- Causes?
We are always looking for additional reasons for data issues.
If you are on a higher version or can replicate the issue for a different
situation, please log a service request.
IMPORTANT: If you are in higher file version please log a SR
with Support Services or update the existing one providing this note number and
the script output from section A and the file versions from section B and we
will add your SR to the Internal Root Cause Analysis (RCA) Bug 8857390 for
Release 12 and RCA Bug 9270328 for release 11i.
Also, if you are in higher file versions and you can
replicate this issue, we would like to hear more on how you do it and then we
can log a new bug focusing on fixing the issue. Please log a SR with Support
Services or update the existing one with replication steps.
D - Datafix.
Follow the steps below to fix the data. The following
temporary views are created to facilitate the backup table creation and the
datafix. You can drop these views later if desired:
View Explanation
mmt_mti_records_v Compare
the historical transactions to the interface transactions.
mmtt_mti_records_v Compare
the pending transactions to the interface transactions.
mmt_mmtt_records_v Compare
the historical transactions to the pending transactions.
1. Identify data.
Please run the identification script (INV17-ident.sql)
uploaded into this document.
2. Backup data.
Run the backup script (INV17-backup.sql). The backup script
creates the following tables: mti_dup_backup, msni_dup_backup, mtli_dup_backup,
mti_dup_backup_mmtt, msni_dup_backup_mmtt, mtli_dup_backup_mmtt,
mmtt_dup_backup, msnt_dup_backup, mtlt_dup_backup
3. Run the datafix script.
Run the datafix script (INV17-datafix.sql) uploaded into the
note. This will fix the data by removing duplicate transactions.
Note: The script DOES commit.
4- Check the data.
Check if your data has been corrected and you are not
experiencing problems. You can rerun the script from the identification
section: (INV17-ident.sql) uploaded into
this document.
Note: You may also need to submit any good, remaining transactions.
For example, you could check for unprocessed records, resubmit any you can via
the form, rerun the inventory manager, rerun trip stop, etc. See Note:1069492.1
Resolving Period Close Pending Transaction R12 that includes SQL to update
transactions as well.
For example, this SQL could resubmit unprocessed pending
transactions that you cannot see via the form because they are transaction mode
8:
update mtl_material_transactions_temp
set process_flag =
'Y',
lock_flag = 'N',
transaction_mode = 3,
error_code = NULL,
error_explanation =
NULL
where process_flag in ('Y','E')
References
NOTE:568012.1 - FAQ: Inventory Standard Datafixes
NOTE:280400.1 - Stuck Transaction Interface Sales Order
Issues: An Error Occured While Relieving Reservations (INV_RSV_RLF_FAILED)
NOTE:294391.1 - What do the transaction mode (like
transaction_mode = 8) numbers mean in the pending transactions table
(MTL_MATERIAL_TRANSACTIONS_TEMP)?
Attachments
