Inter-Org Transfer has Zero Quantity on Receiving end!

Once I encountered a situation where in a Shipment Line of Inter-Org  Transfer has Zero Quantity on Receiving end.

Here is the screenshot of the problem I have encountered.

--To Findout Shipmet_Header_ID
SELECT *
  FROM rcv_shipment_headers
 WHERE 1 = 1 AND receipt_num = '&Recipt_Num'
  AND ship_to_org_id = '&ship_to_org_id';

--To See Shipment_Lines
SELECT *
  FROM rcv_shipment_lines
 WHERE 1 = 1 AND shipment_header_id = '&shipment_header_id';

In Shipment lines I could see that the shipment line is in ‘EXPECTED‘ state but Items couldn’t be received.

Then I have focused to see if there is any problem with ITEMS and I found that these Items are serial controlled and they are locked ( Marks IDs are populated for these Serial Controlled Items).

Generally Serial Controlled Items are not available to transact when markIds (Columns ‘group_mark_id’, ‘line_mark_id’ and ‘lot_line_mark_id’)are populated.

Usually Items get locked when multiple persons try to transact the same item at a time or if there is any abrupt closure of a transaction.

You can run the following query to check if the items have mark IDs populated

SELECT inventory_item_id "Item Id", serial_number "Serial Number",
       current_organization_id "Organization Id", lot_number "Lot Number",
       DECODE (current_status,
               1, 'Defined but not used',
               3, 'Resides in Stores',
               4, 'Out of Stores',
               5, 'Intransit',
               6, 'Invalid',
               NULL, 'Verify Serial Number',
               current_status
              ) "Status",
       current_subinventory_code "Subinv", current_locator_id "Locator",
       group_mark_id "Group Mark Id", line_mark_id "Line Mark Id",
       lot_line_mark_id "Lot Line Mark Id", last_update_date
  FROM mtl_serial_numbers
 WHERE inventory_item_id = '&Item_ID'
   AND serial_number IN ('&List_of_Serial_numbers')
   AND current_organization_id = '&Org_ID';

If you find the items with mark IDs populated, then you need to clear those mark IDs with the following UPDATE statement

UPDATE mtl_serial_numbers
   SET group_mark_id = NULL,
       line_mark_id = NULL,
       lot_line_mark_id = NULL
 WHERE inventory_item_id = '&Item_ID'
   AND serial_number IN ('&List_of_Serial_numbers')
   AND current_organization_id = '&Org_ID';

Please take backup before you execute update statement on mtl_serial_numbers table.
Also don’t forget to COMMIT after running the UPDATE script.

After resolving the mark IDs issue we need to clear the records in rcv_transactions_interface

SELECT *
  FROM rcv_transactions_interface
 WHERE 1 = 1
  AND shipment_header_id = '&shipment_header_id';

You can find that your records are strucked here.

When receiving goods in Oracle Inventory INV the lines will be stored in the RCV_TRANSACTIONS_INTERFACE table so a concurrent process can act upon the data in the table and make the needed changes. In some cases it might happen that the transaction status stays on “Pending” when the Processing Mode is “Immediate”. In this kind of situations you can move forwarded in updating the interface directly by a UPDATE script like the one below.

UPDATE rcv_transactions_interface
   SET processing_mode_code = 'BATCH'
 WHERE interface_transaction_id = '&interface_transaction_id'
   AND GROUP_ID = '&GROUP_ID'
   AND transaction_type = 'RECEIVE'
   AND processing_status_code = 'PENDING'
   AND processing_mode_code = 'IMMEDIATE'
   AND transaction_status_code = 'PENDING'
   AND to_organization_id = '&to_organization_id';

After executing UPDATE script on rcv_transactions_interface you need to run ‘Receiving Transaction Processor‘ from Purchasing Responsibility with parameter as GROUP_ID.

This will resolve the issue and Items will already be received.
You can find that Shipmet_Line_Status_Code will change to ‘FULLY RECEIVED’

NOTE:
1) Take Backup of tables before you execute update script.
2) Preferably do this in DEV or TEST instance and then move the fix to PROD