Data Fix Query For Error Out Work Confirmation - Orapoint

Overview: Please perform below in TEST instance where issue exists, Running this script for PO 500549. For PO: 500549, header_interface_id= 23740, interface_transaction_id = 23945.

--(1)Execute backup script
create table rhi_bkup04022020 as
select * from rcv_headers_interface where header_interface_id= 23740;  

select * from rhi_bkup04022020;

---Query to get  interface_transaction_id from interface table
 select * from rcv_transactions_interface
   WHERE TRANSACTION_TYPE IN ('SHIP', 'RECEIVE')
   AND interface_source_code = 'ISP'
   AND ( (processing_status_code = 'COMPLETED' AND transaction_status_code = 'ERROR' ) OR
           (processing_status_code = 'ERROR' AND transaction_status_code = 'PENDING')
           ) and shipment_num = '&shipment_num';  

create table rti_bkup04022020 as
select * from rcv_transactions_interface where interface_transaction_id = 23945;

select * from rti_bkup04022020;
 
--(2) Execute the following sql to correct
 
--Update rcv_headers_interface table
 Update rcv_headers_interface set
 processing_request_id=null,
 receipt_header_id=null,
 processing_status_code = 'PENDING',
 group_id = 111111
 where header_interface_id= 23740;
 COMMIT;
 
--Update rcv_transactions_interface Table
 Update rcv_transactions_interface set
 request_id=null,
 processing_request_id=null,
 order_transaction_id=null,
 Po_Unit_Price =null,
 processing_status_code = 'PENDING',
 transaction_status_code = 'PENDING',
 item_revision=null,
 job_id=null,
 primary_quantity=null,
 primary_unit_of_measure=null,
 quantity=null,
 quantity_invoiced=null,
 quantity_shipped=null,
 secondary_quantity=null,
 secondary_unit_of_measure=null,
 secondary_uom_code=null,
 source_doc_quantity=null,
 source_doc_unit_of_measure=null,
 substitute_item_id=null,
 substitute_item_num=null,
 timecard_id=null,
 timecard_ovn=null,
 unit_of_measure=null,
 uom_code=null,
 vendor_cum_shipped_qty=null,
 group_id = 111111
 where interface_transaction_id = 23945;
 COMMIT;
 
 --(3) Checking updated table
 select * from rcv_headers_interface where header_interface_id= 23740;
 select * from rcv_transactions_interface where interface_transaction_id = 23945;
 
 --(4)Run Receiving Transaction Processor for group_id 111111

 --(5)Verify that shipment header and line has been created