mtl_material_transactions_temp errors : How to Find the errors of Stuck Transactions in mtl_material_transactions_temp

mtl_material_transactions_temp errors : How to Find the errors of Stuck Transactions in mtl_material_transactions_temp

In this post , we will be discuss about mtl_material_transactions_temp errors. We wil be discuss more about how we can find the errors Reasons for the Pending and the error out transactions in the mtl_material_transactions_temp tables. In mtl_material_transactions_temp table , We have different transactions from Different Sources. Like Inventory , WIP , Costing and WIP Resources too. I will share the SQL query for all these different sources to find out in detail about mtl_material_transactions_temp errors.

 

 

Important SQL Queries of mtl_material_transactions_temp errors

For WIP Resource Transactions

Step1:-  To check for pending transactions, run the following SQL;
select transaction_id, request_id, group_id,process_status , wip_entity_name from wip_cost_txn_interface where process_status in ( 1,3); ( process_status= 1 indicates ‘Pending’, 3 indicates ‘Error’ )

 

Step2 :- update wip_cost_txn_interfaceset group_id=NULL,transaction_id = NULL,process_status= 1 ( set it to Pending )where process_status = 3; ( error records )

Step3:- Restart the (Resource) Cost Manager.

For Inventory Transactions

select transaction_source_id , inventory_item_id, process_flag,error_code, error_explanation,transaction_source_type_id, organization_id from mtl_material_transactions_temp where process_flag = ‘E’ and transaction_source_id= ;

 Use this script to resubmit the Transactions:
update mtl_material_transactions_tempset process_flag = ‘Y’,lock_flag = ‘N’,transaction_mode= 3,error_code = NULL,error_explanation = NULL where process_flag = ‘E’

 

For Pending WIP Transactions

 

select transaction_id, wip_entity_id, process_phase, process_status, wip_entity_name from wip_move_txn_interface where process_status in (1,3)
(Process Status = 1 indicates Pending, 3 indicates “Error” )

 

select error_message, error_column from wip_txn_interface_errors where request_id= ( for the two that have a request id )

 Use this script to update the Error Records:

 update wip_move_txn_interface set group_id=null,request_Id = null,process_status=1,transaction_id=null where transaction_id=;
Restart the interface managers (Move and Cost Manager).
Launch the Move Transaction Manager even if the TP:WIP Move Transaction Profile Option = on-line processing. (Navigation -> Inventory/Setup/Transactions/Interface Managers). Use the “Launch Manager” button under the special option on the tool bar.