Serial Numbers Have Incorrect Status After transaction Failed / RVCTP Incorrectly Updates Serial Number Current Status To '3' Failing / Does Not Rollback in Case of Error (Doc ID 1591848.1)

Last updated on MAY 01, 2023

Applies to:

Symptoms

Actual Behavior
When trying to perform a receipt (on a PO or RMA or other document) for a serialized item and Receiving Transaction Processor (RVCTP) fails,
it's noticed that the current status of the serial number is being updated incorrectly (from 4 - Issued out of stores to 3 - Resides in stores) whereas the material cannot be received due to the error.
 
Issue occurs for both Receipts done through Standard Oracle Forms or imported through the ROI Receiving Open Interface program,  as long as the
receipt record is failed due to various reason through RTP.

Record remains in Error in rcv_transactions_interface table.

There is no material transaction records for the receiving transaction in MTL_MATERIAL_TRANSACTIONS table
and there is no onhand records for the item with the serial numbers.  
 

Expected Behavior
. If RVCTP fails when receiving serial number, status in MTL_SERIAL_NUMBERS table should not be updated.
. For existing corruption, a Datafix should update the serial number status to 'issue out of store' in order to be able to enter the Receipt again.


Steps to Reproduce the issue

1) Item is serial controlled at receipt
In MTL_SYSTEM_ITEMS table
Inventory_item_id 255957
Serial_Number_Control_Code  5

2) Serial number "SN1" has been generated, shipped (internally or externally) and issued from store
SN Status = 4 = Issued out of stores

In MTL_SERIAL_NUMBERS table
Serial Number    SN1
Previous_status  3
Current_Status   4 -- Issued out of stores

3) Perform RMA or PO receipt / delivery via Purchasing Form with 'RCV: Processing Mode' profile option set to Batch
Navigate to Receiving Receipts form RCVRCERC or Receiving Transactions RCVTXERT form
Click on Lot - Serial button
In Serial Entry form
indicate serial number SN1  
Save
A record is created in RCV_TRANSACTIONS_INTERFACE and RCV_SERIALS_INTERFACE tables.

Or create receipt / deliver transaction via ROI (record in RCV_HEADERS_INTERFACE, RCV_TRANSACTIONS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE tables).

4) Ensure that RVCTP Receiving Transaction Processor fails (for instance close the period)

5) Transaction Statuses form shows stuck record in RCV_TRANSACTIONS_INTERFACE table and RCV_SERIALS_INTERFACE:

. Select * from RCV_TRANSACTIONS_INTERFACE
where item_id = &item_id;
Interface_Transaction_Id 4075911
Transaction_Type RECEIVE
Transaction_Status_Code ERROR
Last_update_date 24/09/13
Quantity 1
Item_Id 255957
Auto_Transact_Code DELIVER
To_Organization_Id 204
Source_Document_Code PO
Destination_Type_Code INVENTORY  
Use_Mtl_Lot 1
Use_Mtl_Serial 5

. Select * from RCV_SERIALS_INTERFACE
where item_id = &item_id;
Interface_Transaction_Id 4075911  <-- RTI.Interface_Transaction_Id
Fm_Serial_Num SN1
To_Serial_Num SN1
Item_Id 255957
Organization_Id 204

(If created via ROI
Select * from MTL_SERIAL_NUMBERS_INTERFACE
returns a record with
Transaction_interface_id  
Fm_Serial_Number SN1
To_Serial_Number SN1
Product_code RCV
Product_Transaction_id 4075911 <-- RTI.Interface_Transaction_Id
)

6) There No onhand at the subinventory which is the subinventory that the serial number resides at in the serial number screen.
There is no new material transaction.

Select* from MTL_ONHAND_QUANTITIES_DETAIL
where inventory_item_id = &item_id;
There are no records

Select * from MTL_MATERIAL_TRANSACTIONS
where transaction_id in
(select transaction_id from mtl_unit_transactions
where inventory_item_id = &item_id
and serial_number = '&SN') ;
Last transaction is issue transaction with negative quantity

7) Check the Serial status and the current status is now updated to Resides in Stores although transaction failed to be processed
SN record from MTL_SERIAL_NUMBERS table

Select * from MTL_SERIAL_NUMBERS
where inventory_item_id = &item_id
and serial_number = '&SN' ;
 
Inventory_item_id 255957
Serial_number    SN1
Current_Organization_id    204
Last_update_date 24/09/13 <-- RVCTP date
Previous_status  3
Current_Status   3   <-- incorrect, should be still 4

Changes

 The problem may surface after applying Logistics rollup patches RUP5 (Patch 14611722:R12.WMS.B), RUP6 (Patch 16272776:R12.WMS.B) and RUP7 (Patch 16812342:R12.WMS.B).

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.