Resolving records stuck in the Receiving Transactions Interface

Resolving records stuck in the Receiving Transactions Interface

Reprocessing Errored Rows
-------------------------
 On occassion it may be necessary to reprocess an errored or stuck record in the interface.  The following steps can be used to reprocess  a stuck record. 
 Navigate to the Transaction Status Summary screen in the application from either Receiving or Inventory responsibility (Nav > Receiving  > Transaction Status Summary).  Use the Find form to query up the transaction in question and then set focus to the transaction.
You then select the Delete function from the menu by pressing on the red x icon button to delete the record.
 You can then reattempt to receive the record.  If it fails again you must engage Oracle Support Services for further disposition.

Technical Details:
-----------------
When a row has been succesfully processed it will be deleted from the table.  When a row has been processed for an inventory item,  the rcv_transactions stable will be updated and a row written to the mtl_transactions_interface table, waiting to be picked up by the Inventory Transaction Processor.  Once this record has been processed succesfully, the on hand quantity level will be updated.
 Receiving statuses and errors can subsequently be viewed in the Receiving  Transaction Status Summary form in Purchasing / Inventory. On occassion the records cannot be viewed in this form and they have to be queried using sqlplus.
 To query stuck rows, run the following sql statement which will yield errored records for the past 7 days (if you want to change the time
 period just change the sysdate - 7 criteria to a larger duration for example for the past 30 days it would be: "and transaction_date >= sysdate - 30")
 Here is the query:
 SQL> select interface_transaction_id, processing_status_code, processing_mode_code, transaction_status_code, transaction_type,
      transaction_date
      from rcv_transactions_interface
      where (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR') and transaction_date >= sysdate - 7;
 The processing_mode_code determines whether the record was received in Online, Immediate or Batch mode.  This is the column that would
 need to be updated to 'BATCH' to reprocess an online record.

Troubleshooting
----------------
 If the receipt has gone through okay but the stock levels have not been updated then it is worth checking if the inventory transaction  manager is running and if there are any records pending or in error in the inventory transactions interface.  These can be queried via  the Inventory Transactions Interface Inquiry screen.
 If the Purchase Order shows that the shipment has been received and delivered and inventory levels show the stock levels updated to  reflect the delivery, then the records can be deleted using the Transaction Status Summary form (Ref Note 303544.1 - How To Remove  Pending and Error Transactions from the Transaction Status Summary) by selecting the record and then picking the Delete red X icon on the  toolbar in the menu. In this case there is no need to reprocess or  re-enter these transactions.
Note: If a transaction is accessible via the Transaction Status Summary form and is in error it can be deleted.
Generic script to delete RTI records which are not appearing in Transaction Status Summary form for deletion. Please ensure the scripts are ran on TEST instance first and tested for data
 correctness thoroughly. After the scripts are ran, please check the data and only the correct records are updated before committing. If all goes well, the script can be promoted to the PRODUCTION instance.
--back up date in rti, rli, mtlt, rsi, msnt
create table rti_bak as select * from rcv_transactions_interface
where interface_transaction_id in (&interface_transaction_ids);
create table rli_bak as select * from rcv_lots_interface
where  interface_transaction_id in (select interface_transaction_id  from rti_bak);
create table mtlt_bak as
select * from mtl_transaction_lots_temp
where  product_code = 'RCV'
and    product_transaction_id in  (select interface_transaction_id   from rti_bak);
create table rsi_bak as
select * from rcv_serials_interface where  interface_transaction_id  in  (select interface_transaction_id from rti_bak);
create table msnt_bak as select * from mtl_serial_numbers_temp
where  product_code = 'RCV' and    product_transaction_id in  (select interface_transaction_id
                                    from rti_bak);
--delete data in rti, rli, mtlt, rsi, msnt
delete rcv_transactions_interface where interface_transaction_id in  (select interface_transaction_id  from rti_bak);
delete rcv_lots_interface
where  interface_transaction_id in (select interface_transaction_id   from rti_bak);
delete mtl_transaction_lots_temp
where  product_code = 'RCV' and    product_transaction_id in  (select interface_transaction_id
                                    from rti_bak);
delete rcv_serials_interface
where  interface_transaction_id  in  (select interface_transaction_id from rti_bak);
delete mtl_serial_numbers_temp
where  product_code = 'RCV'
and    product_transaction_id in  (select interface_transaction_id from rti_bak);

References :
Resolving records stuck in the Receiving Transactions Interface
Doc ID 50903.1
Transaction Status Summary: How To Remove Pending and Error Receiving Transactions [ID 303544.1]