Oracle Apps DBA stuff: Fixing Duplicate Sales Order Transactions In MTI, MMTT and MMT

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