Datafix Script to clear the RCV stuck pending transactions for Receipts

Scripts used for clearing the RCV stuck pending transactions

Scenario:

While receiving or correcting a PO, some time the transactions get stuck with out Processing due to delay in running receiving transaction processor (This happens sometimes if there is too many concurrent program's running at the back end)

Due to the above reason the PO is not received in the system and on hand quantity is not updated.

Even if we try to clear the stuck pending transactions system does not allow.

System also does not allow us to create receipt again for the PO till the stuck PO pending receiving transactions are cleared.

Solution:

We have written scripts to resolve this issue by way deleting the stuck PO pending transactions from the RCV table.

Step 1:  Query to identify the rcv_transactions_interface_id

Query: Select * from rcv_transactions_interface WHERE PO_HEADER_ID= (Give the PO header id of the diseased PO)

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1=''

Example:

Select * from rcv_transactions_interface WHERE PO_HEADER_ID=1341737

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='550684'

Result: The above query will give the rcv_transactions_interface_ids of the stuck PO lines

Step 2:

Provide this information to the DBA and request the DBA team to follow the steps as provided in Oracle Note: Doc ID 303544.1 (How To Remove Pending and Error Receiving Transactions from Transaction Status Summary)

     a.  Run the script to check the backup tables: table_check23.sql
     b.  Run the appropriate datafix script:
          For 11.5.10 and r12.0: delete_stuck_rti_for_R11510 and R120.sql
          For 12.1 and above: delete_stuck_rti_for_R121_and_above.sql

---- ANIL APPANABOYINA