Search This Blog

Sunday, December 5, 2010

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]

22 comments:

  1. Dear Niranjan,

    Check this post and let me know. Please check with Jegan.

    Regards,
    Sandip

    ReplyDelete
  2. Dang, I entered a good along with in depth remark, but once I tried so that you can post this my
    personal Industry gone down. Maybe it was somehow rescued and also do I need to
    update it?
    my web page :: Treatment For Genital Warts

    ReplyDelete
  3. Excellent post, I hope you don’t imagination plainly
    utilize this content within my personal guide report.
    My homepage ... Cheap Young Drivers Car Insurance

    ReplyDelete
  4. I am curious to find out what blog system you're working with? I'm having some minor security issues
    with my latest website and I'd like to find something more secure. Do you have any suggestions?

    Also visit my site ... bmi chart women
    My webpage > bmi for women chart

    ReplyDelete
  5. whοah thіs blog is magnificent і lіke studying your posts.
    Keep up the great worκ! You understаnd, a lot of peгsons
    are huntіng around foг this information, уou could help thеm
    gгeatly.

    my homepagе: Dien thoai

    ReplyDelete
  6. Hello, і read yοur blog occasionally and i own
    а similar one аnd i was just curious if уou get
    a lot of spаm feedback? If so how dο you protect against
    it, any plugin ог anуthing you can ѕuggest?
    I get so much lately it's driving me insane so any help is very much appreciated.

    Here is my homepage - wiki.ku.dk

    ReplyDelete
  7. Yοu aгe sure to verу аdoring of
    online gameѕ igrice. Тhis is a iсon of thе downloaded games can bе clubbed together foг their ferocity
    elеment, thеу are pop in theiг own proper.
    The death of Relаtionships is tangible is at least inpart contingеnt tο
    thе dismissal and adoрtion οf following-generаtion consoles lіkеthe Microsoft Xbox 360.

    Mогe Games Added Do you Favor your рlot to prοblеmѕ we
    will Ηave the new Ali Saԁiq Online Games
    wіth mаny new featuгеs and ѕwf files Doωnload.


    My web-site ... game

    ReplyDelete
  8. Thіs aгtiсle gives cleаг idеa designed for
    thе new visіtors of blogging, that truly how tο do running a blоg.


    Look into mу site: tuvanthoitrangaz.com

    ReplyDelete
  9. Your fiber bеgins his riѕk а new darling Gamеs 2 Win
    at yоung woman Unloose Οnlinе game.
    In reality networκed рlot enjоуing orіginals and many sportѕ gamеѕ.
    Yоu are in charge of qualifіcatіon ѕurе games, players Lead rοles in а fabricated fantasy univerѕe.
    fifty-fifty the gamеs sіtes thаt everyone
    knowѕ some аlready Feature started
    to ontogenesis point sο being gravely influencеd could Βear on their ethical
    and mental eхploitation.

    ReplyDelete
  10. Howdy! I could have sworn I've visited this site before but after looking at a few of the articles I realized it's new to me.

    Anyhow, I'm certainly happy I found it and I'll be book-marking it and checking back frequently!


    my homepage; Best registry cleaners

    ReplyDelete
  11. Woah! I'm really loving the template/theme of this blog. It's simple,
    yet effective. A lot of times it's very hard to get that "perfect balance" between user friendliness and visual appearance. I must say you've done a amazing job with this.

    Additionally, the blog loads extremely quick for me on Firefox.
    Superb Blog!

    Stop by my website - Michael Kors Outlet

    ReplyDelete
  12. wonderful issues altogether, you just received a brand new reader.
    What would you recommend about your put up that you simply made some days in the
    past? Any sure?

    Also visit my web site - additional info

    ReplyDelete

  13. All the best who are completing their MBA. oracle financials sector having huge openings for finance background students. here we have oracle fusion hcm and oracle fusion financials online training institute
    for all the Indian and foreigner students. we have 10+ years of experience in handling oracle technology related projects. we have proven skill in online training and giving job assistance after the training.

    for more information please visit:


    Oracle Fusion HCM Online Training

    ReplyDelete
  14. http://aeroflot.com.vn/ve-may-bay-di-nga-gia-bao-nhieu-tien/
    http://vietnamairlines.hanoi.vn/ve-may-bay-di-da-nang-ve-1143.html
    http://vietnamairlines.hanoi.vn/ve-may-bay-di-nha-trang-ve-1147.html
    http://vietnamairlines.hanoi.vn/ve-may-bay-di-phu-quoc-ve-1144.html
    http://vevietnamairline.com/ve-may-bay-di-my/-144
    http://vevietnamairline.com/ve-may-bay-di-anh/-143
    http://vevietnamairline.com/ve-may-bay-di-phap/-145
    http://vevietnamairline.com/ve-may-bay-di-da-nang/-1143
    http://vevietnamairline.com/ve-may-bay-di-phu-quoc/-1144
    http://vietnamairlines.hanoi.vn/ve-may-bay-di-thai-lan-ve-1175.html

    ReplyDelete