Saturday, July 21, 2012

How to Resubmit Unprocessed Transactions Stuck in the MTL_MATERIAL_TRANSACTION_TEMP Table


Pending Transactions may be resubmitted through the Pending Transactions screen or via sqlplus.

For screen resubmission:
Navigation Inventory>Transactions>Pending Transactions
 Click the checkbox under the "Submit" column to select specific rows.
 Click the Save icon


To resubmit all Pending Transactions from the Applications:
Navigation Inventory>Transactions>Pending Transactions>Tools>Submit All
           This will choose all records for resubmission.
           Click the Save icon.


For resubmission via SQL*Plus use one of the following scripts. Please note that Oracle requires that a backup of the tables involved be completed prior to modifying any data at the table level in any way.

Script 1 will resubmit all transacations.
Script 2 will resubmit tranasactions with the same TRANSACTION_HEADER_ID in the MTL_MATERIAL_TRANSACTIONS_TEMP (MMTT) table.

Script 1:

SQL> 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');

Script 2:

Script 2.a Resubmit all records with the same MMTT.TRANSACTION_HEADER_ID:

SQL> Update MTL_MATERIAL_TRANSACTIONS_TEMP
        Set PROCESS_FLAG = 'Y',
            LOCK_FLAG = 'N' ,
            TRANSACTION_MODE = 3,
            ERROR_CODE = NULL,
            ERROR_EXPLANATION = NULL
       Where TRANSACTION_HEADER_ID = <transaction_header_id>;


Script 2.b Resubmit all records within several MMTT.TRANSACTION_HEADER_ID's:

SQL> Update MTL_MATERIAL_TRANSACTIONS_TEMP
       Set  PROCESS_FLAG = 'Y',
            LOCK_FLAG = 'N' ,
            TRANSACTION_MODE = 3,
            ERROR_CODE = NULL,
            ERROR_EXPLANATION = NULL
       Where TRANSACTION_HEADER_ID in (<transaction_header_id> , <transaction_header_id> )