How to Return Receiving Transaction with Receiving Open Interface? [ID 360340.1]

ROI : How to Return Receiving Transaction with Receiving Open Interface? [ID 360340.1]
________________________________________
        修改时间 08-JUL-2011     类型 HOWTO     状态 PUBLISHED         
In this Document

https://support.oracle.com/CSP/m ... WTO&id=360340.1

  Goal
  Solution
     SETUP
     A) RETURN to RECEIVING for PURCHASE ORDER Example
     B) RETURN  to VENDOR Transaction for PURCHASE ORDER Example
  References
________________________________________
Applies to:
Oracle Inventory Management - Version: 11.5.1 to 12.1.2 - Release: 11.5 to 12.1
Information in this document applies to any platform.
RVCTP - Receiving Transaction Processor
Goal
How to Return Receiving Transaction against Standard Purchase Order using ROI?
New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) is ROI supports for Standard Purchase Order
•        RETURN to RECEIVING transactions
•        RETURN to VENDOR  transactions

Solution
Following SAMPLE scripts are intended  for Returning to Vendor a Purchase Order with 'Standard Receipt' Routing:

You will need to perform the return to receiving transaction before you can process the return to vendor for 2 step returns
A) perform a RETURN to RECEIVING for a standard Purchase Order through the Receiving Open Interface (ROI) 
(Receipt routing = Standard Receipt)
TRANSACTION_TYPE = RETURN TO RECEIVING
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)
B) perform a RETURN to VENDOR for a standard Purchase Order through the Receiving Open Interface (ROI) 
(Receipt routing = Standard Receipt)
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for RECEIVE transaction)
In order to be most comprehensive, each script has been tested for a Lot and Serial Controlled Item.

It will insert 1 record into the tables RCV_TRANSACTIONS_INTERFACE,
MTL_TRANSACTION_LOTS_INTERFACE  and MTL_SERIAL_NUMBERS_INTERFACE.

If the item is only a standard item, only the record into RCV_TRANSACTIONS_INTERFACE table has to be created. 

For a RETURN TO VENDOR transaction for a direct delivery receipt, only 1 step return is needed, 
then following fields need to be changed:
TRANSACTION_TYPE = RETURN TO VENDOR
PARENT_TRANSACTION_ID = (Transaction Id for DELIVER transaction)
SETUP
0) Ensure to apply the patches listed in Note 367396.1 Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J)

1) Standard Purchase Order number # 8415 has been entered
for Inventory Item = Lot-Serial-Controlled-1
Shipment Receipt Routing = Standard Receipt (RECEIVING_ROUTING_ID=1)
Destination Type Code = Inventory 

2) Item  Lot-Serial-Controlled-1 is a lot and serial controlled Item2) Run the following scripts to check data have been correctly inserted 
SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code, 
msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),
  '2','Y',
  '1','N') LOT_CONTROL,
decode(to_char(msi.serial_number_control_code),
  '1','None',
  '2','Predefined',
  '5','Dynamic at INV receipt',
  '6','Dynamic at SO issue') SERIAL_CONTROL
  from mtl_system_items_b msi,mtl_parameters mp
where msi.segment1 like '&item' and msi.organization_id=mp.organization_id; 
It returns LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'
inventory_item_id=169845 for item=Lot-Serial-Controlled-1 

3) Receipt number  # 7970 for a quantity=2 has been entered (Receive and Deliver Transactions)
The deliver transaction for a quantity=2 on PO #  8415 for 
item=Lot-Serial-Controlled-1 is entered
on Lot Number=S336
and Serial Number from SN0020 to SN0021 

4) Use the script below to get details about the receipt transactions. Data from this
query is used in the insert statement below.
SQL> Select RSH.RECEIPT_NUM ,
        PH.SEGMENT1 PO_NUMBER,
        RT.TRANSACTION_ID ,
        RT.TRANSACTION_TYPE ,
        RT.TRANSACTION_DATE ,
        RT.QUANTITY ,
        RT.UNIT_OF_MEASURE ,
        RT.SHIPMENT_HEADER_ID ,
        RT.SHIPMENT_LINE_ID ,
        RT.SOURCE_DOCUMENT_CODE ,
        RT.DESTINATION_TYPE_CODE ,
        RT.EMPLOYEE_ID ,
        RT.PARENT_TRANSACTION_ID ,
        RT.PO_HEADER_ID ,
        RT.PO_LINE_ID ,
        PL.LINE_NUM ,
        PL.ITEM_ID ,
        PL.ITEM_DESCRIPTION ,
        PL.UNIT_PRICE ,
        RT.PO_LINE_LOCATION_ID ,
        RT.PO_DISTRIBUTION_ID ,
        RT.ROUTING_HEADER_ID,
        RT.ROUTING_STEP_ID ,
        RT.DELIVER_TO_PERSON_ID ,
        RT.DELIVER_TO_LOCATION_ID ,
        RT.VENDOR_ID ,
        RT.VENDOR_SITE_ID ,
        RT.ORGANIZATION_ID ,
        RT.SUBINVENTORY ,
        RT.LOCATOR_ID ,
        RT.LOCATION_ID,
        RSH.SHIP_TO_ORG_ID
From      
        RCV_TRANSACTIONS RT,
        RCV_SHIPMENT_HEADERS RSH,
        PO_HEADERS_ALL PH,
        PO_LINES_ALL PL
Where 
        RSH.RECEIPT_NUM = '&RECEIPT_NUMBER'
        AND PH.SEGMENT1 like '&PO_NUMBER'
        AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
        AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
        AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID; 
It returns 2 records for RECEIPT_NUM=7970 and PO_NUMBER=8415:  

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=524017
    DESTINATION_TYPE_CODE=RECEIVING
    QUANTITY=2
    UNIT_OF_MEASURE=Each
    SHIPMENT_HEADER_ID=477550
    SHIPMENT_LINE_ID=484510
    SOURCE_DOCUMENT_CODE=PO
    EMPLOYEE_ID=13706
    PARENT_TRANSACTION_ID=-1
    PO_HEADER_ID=40924
    PO_LINE_ID=46915
    ITEM_ID=169845
    PO_LINE_LOCATION_ID=81963
    PO_DISTRIBUTION_ID=null
    DELIVER_TO_PERSON_ID=null
    DELIVER_TO_LOCATION_ID=null
    VENDOR_ID=7927
    VENDOR_SITE_ID=4724
    ORGANIZATION_ID=204
    SUBINVENTORY=null
    LOCATOR_ID=null
    LOCATION_ID=204
    SHIP_TO_ORG_ID=204 

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=524018
    DESTINATION_TYPE_CODE=INVENTORY
    QUANTITY=2
    UNIT_OF_MEASURE=Each
    SHIPMENT_HEADER_ID=477550
    SHIPMENT_LINE_ID=484510
    SOURCE_DOCUMENT_CODE=PO
    EMPLOYEE_ID=13706
    PARENT_TRANSACTION_ID=524017
    PO_HEADER_ID=40924
    PO_LINE_ID=46915
    ITEM_ID=169845
    PO_LINE_LOCATION_ID=81963
    PO_DISTRIBUTION_ID=84349
    DELIVER_TO_PERSON_ID=13706
    DELIVER_TO_LOCATION_ID=204
    VENDOR_ID=7927
    VENDOR_SITE_ID=4724
    ORGANIZATION_ID=204
    SUBINVENTORY=Stores
    LOCATOR_ID=null
    LOCATION_ID=204
    SHIP_TO_ORG_ID=204


5)  Run the following scripts to identify the Lot/Serial Information related to the DELIVER transaction RCV_TRANSACTIONS.TRANSACTION_ID=524018  
SQL> Select LOT_TRANSACTION_TYPE,LOT_NUM,QUANTITY 
from RCV_LOT_TRANSACTIONS
where source_transaction_id=&RTtransaction_id; 
This script returns 1 record with LOT_TRANSACTION_TYPE=TRANSACTION and LOT_NUM=S366
SQL> Select SERIAL_TRANSACTION_TYPE,SERIAL_NUM,SHIPMENT_LINE_ID,TRANSACTION_ID,
LOT_NUM
from RCV_SERIAL_TRANSACTIONS 
where source_transaction_id=&RTtransaction_id; 
This script returns 2 records,one for each serial number between SN0020 and SN0021
For SERIAL_NUM=SN0020 and SERIAL_NUM=SN0021
SERIAL_TRANSACTION_TYPE=TRANSACTION
SHIPMENT_LINE_ID=484510
SOURCE_TRANSACTION_ID=524018
TRANSACTION_ID=524018
LOT_NUM=S366
A) RETURN to RECEIVING for PURCHASE ORDER Example
1) Insert via ROI a RETURN TO RECEIVING  record on the DELIVER to INVENTORY Transaction 
(RCV_TRANSACTIONS.TRANSACTION_ID=524018)
so to correct the quantity delivered from 2 to a quantity delivered of 1,
returning SERIAL_NUM=SN0021 for LOT_NUM=S366

Insert 
. 1 record in RCV_TRANSACTIONS_INTERFACE table with 
TRANSACTION_TYPE='RETURN TO RECEIVING'
PARENT_TRANSACTION_ID=524018 (Transaction Id for DELIVER transaction)
for a positive quantity =1 
and DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='VENDOR' and SOURCE_DOCUMENT_CODE='PO'
. 1 record in MTL_TRANSACTIONS_LOTS_INTERFACE for a positive quantity=1
on lot number S366
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating
FM_SERIAL_NUMBER='SN0021'and TO_SERIAL_NUMBER='SN0021'
SQL> 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
      (INTERFACE_TRANSACTION_ID,
       GROUP_ID,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       TRANSACTION_TYPE,
       TRANSACTION_DATE,
       PROCESSING_STATUS_CODE,
       PROCESSING_MODE_CODE,
       TRANSACTION_STATUS_CODE,
       QUANTITY,
       UNIT_OF_MEASURE,
       ITEM_ID,
       EMPLOYEE_ID,
       SHIPMENT_HEADER_ID,
       SHIPMENT_LINE_ID,
       RECEIPT_SOURCE_CODE,
       VENDOR_ID,
       FROM_ORGANIZATION_ID,
       FROM_SUBINVENTORY,
       FROM_LOCATOR_ID,
       SOURCE_DOCUMENT_CODE,
       PARENT_TRANSACTION_ID,
       PO_HEADER_ID,
       PO_LINE_ID,
       PO_LINE_LOCATION_ID,
       PO_DISTRIBUTION_ID,
       DESTINATION_TYPE_CODE,
       DELIVER_TO_PERSON_ID,
       LOCATION_ID,
       DELIVER_TO_LOCATION_ID,
       VALIDATION_FLAG
      )
      VALUES
      (rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
       rcv_interface_groups_s.nextval,       --GROUP_ID
       SYSDATE,                   --LAST_UPDATE_DATE
       0,                         --LAST_UPDATE_BY
       SYSDATE,                   --CREATION_DATE
       0,                         --CREATED_BY
       0,                         --LAST_UPDATE_LOGIN
       'RETURN TO RECEIVING',     --TRANSACTION_TYPE
       SYSDATE,                   --TRANSACTION_DATE
       'PENDING',                 --PROCESSING_STATUS_CODE
       'BATCH',                   --PROCESSING_MODE_CODE
       'PENDING',                 --TRANSACTION_STATUS_CODE
       1,                         --QUANTITY
       'Each',                    --UNIT_OF_MEASURE
       169845,                    --ITEM_ID
       13706,                     --EMPLOYEE_ID
       477550,                    --SHIPMENT_HEADER_ID
       484510,                    --SHIPMENT_LINE_ID
       'VENDOR',                  --RECEIPT_SOURCE_CODE
       7927,                      --VENDOR_ID
       204,                       --FROM_ORGANIZATION_ID
       'Stores',                  --FROM_SUBINVENTORY
       null,                      --FROM_LOCATOR_ID
       'PO',                      --SOURCE_DOCUMENT_CODE
       524018,                    --PARENT_TRANSACTION_ID
       40924,                     --PO_HEADER_ID
       46915,                     --PO_LINE_ID
       81963,                     --PO_LINE_LOCATION_ID
       84349,                     --PO_DISTRIBUTION_ID
       'INVENTORY',               --DESTINATION_TYPE_CODE
       null,                      --DELIVER_TO_PERSON_ID
       null,                      --LOCATION_ID
       null,                      --DELIVER_TO_LOCATION_ID
       'Y'                        --VALIDATION_FLAG
      ); 

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            LOT_NUMBER,
            TRANSACTION_QUANTITY,
            PRIMARY_QUANTITY,
            SERIAL_TRANSACTION_TEMP_ID,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE, --LAST_UPDATE_DATE
            0,       --LAST_UPDATED_BY
            SYSDATE, --CREATION_DATE
            0,       --CREATED_BY
            0,       --LAST_UPDATE_LOGIN
            'S366',  --LOT_NUMBER
            1,       --TRANSACTION_QUANTITY
            1,       --PRIMARY_QUANTITY
            MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
            'RCV',   --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            ); 

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE 
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            FM_SERIAL_NUMBER,
            TO_SERIAL_NUMBER,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID) 
            VALUES 
            (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE,  --LAST_UPDATE_DATE
            0,        --LAST_UPDATED_BY
            SYSDATE,  --CREATION_DATE
            0,        --CREATED_BY
            0,        --LAST_UPDATE_LOGIN
            'SN0021', --FM_SERIAL_NUMBER
            'SN0021', --TO_SERIAL_NUMBER
            'RCV',    --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
Commit; 

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where PO_HEADER_ID=40924
INTERFACE_TRANSACTION_ID=463894
GROUP_ID=29806
TRANSACTION_TYPE=RETURN TO RECEIVING
QUANTITY=1
PARENT_TRANSACTION_ID=524018
DESTINATION_TYPE_CODE=INVENTORY
RECEIPT_SOURCE_CODE=VENDOR
SOURCE_DOCUMENT_CODE=PO

SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE 
where PRODUCT_TRANSACTION_ID=&interface_txn_id;
TRANSACTION_INTERFACE_ID=11554802
LOT_NUMBER=S366
TRANSACTION_QUANTITY=1
PRIMARY_QUANTITY=1
SERIAL_TRANSACTION_TEMP_ID=11554802
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=463894

SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE 
where PRODUCT_TRANSACTION_ID=&interface_txn_id;
TRANSACTION_INTERFACE_ID=11554802
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=463894
FM_SERIAL_NUMBER=SN0021
TO_SERIAL_NUMBER=SN0021
3) In Purchasing Responsibility, Run the Receiving Transaction Processor 
for the specific Group ID (GROUP_ID=29806) 

4) Navigate to Receiving / Receiving Transactions Summary form
For PO 8415 , Receipt Number 7970 has Receive, Deliver and Return to Receiving transactions.  
SQL> Select * from RCV_TRANSACTIONS where PO_HEADER_ID=40924
It returns 3 records 

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=524017
    DESTINATION_TYPE_CODE=RECEIVING
    PARENT_TRANSACTION_ID=-1
    QUANTITY=2

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=524018
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=524017
    QUANTITY=2 

For TRANSACTION_TYPE=RETURN TO RECEIVING
    TRANSACTION_ID=524025
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=524018
    QUANTITY=1
    FROM_SUBINVENTORY=Stores
SQL> Select * from MTL_SERIAL_NUMBERS where INVENTORY_ITEM_ID=&Item_id
and lot_number like 'S366';
The script returns 2 records
For SERIAL_NUMBER=SN0020 
    CURRENT_STATUS=3 (Resides in stores)
    CURRENT_SUBINVENTORY_CODE=Stores
For SERIAL_NUMBER=SN0021 
    CURRENT_STATUS=7 
    CURRENT_SUBINVENTORY_CODE=null
5) Navigate to Purchase Orders/Purchase Orders Summary
At shipment and distribution level, for the PO Number 8415
Quantity Received=2
Quantity Delivered=1
B) RETURN  to VENDOR Transaction for PURCHASE ORDER Example
6) Insert via ROI a RETURN TO VENDOR record on the RECEIVE Transaction 
(RCV_TRANSACTIONS.TRANSACTION_ID=524017) so to correct the quantity received from 2 to a quantity received of 1 

Insert 
. 1 record in RCV_TRANSACTIONS_INTERFACE table with 
TRANSACTION_TYPE='RETURN TO VENDOR' 
PARENT_TRANSACTION_ID=524017 (Transaction Id for RECEIVE transaction) 
for a positive quantity = 1 indicating 
DESTINATION_TYPE_CODE='RECEIVING' 
RECEIPT_SOURCE_CODE='VENDOR' and SOURCE_DOCUMENT_CODE='PO' 
. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a positive quantity=1 
on lot number S366 
. 1 record in MTL_SERIAL_NUMBERS_INTERFACE, indicating 
FM_SERIAL_NUMBER='SN0021'and TO_SERIAL_NUMBER='SN0021'
SQL> 
INSERT INTO RCV_TRANSACTIONS_INTERFACE
      (INTERFACE_TRANSACTION_ID,
       GROUP_ID,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       TRANSACTION_TYPE,
       TRANSACTION_DATE,
       PROCESSING_STATUS_CODE,
       PROCESSING_MODE_CODE,
       TRANSACTION_STATUS_CODE,
       QUANTITY,
       UNIT_OF_MEASURE,
       ITEM_ID,
       EMPLOYEE_ID,
       SHIPMENT_HEADER_ID,
       SHIPMENT_LINE_ID,
       RECEIPT_SOURCE_CODE,
       VENDOR_ID,
       FROM_ORGANIZATION_ID,
       FROM_SUBINVENTORY,
       FROM_LOCATOR_ID,
       SOURCE_DOCUMENT_CODE,
       PARENT_TRANSACTION_ID,
       PO_HEADER_ID,
       PO_LINE_ID,
       PO_LINE_LOCATION_ID,
       PO_DISTRIBUTION_ID,
       DESTINATION_TYPE_CODE,
       DELIVER_TO_PERSON_ID,
       LOCATION_ID,
       DELIVER_TO_LOCATION_ID,
       VALIDATION_FLAG
      )
      VALUES
      (rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
       rcv_interface_groups_s.nextval,       --GROUP_ID
       SYSDATE,                   --LAST_UPDATE_DATE
       0,                         --LAST_UPDATE_BY
       SYSDATE,                   --CREATION_DATE
       0,                         --CREATED_BY
       0,                         --LAST_UPDATE_LOGIN
       'RETURN TO VENDOR',        --TRANSACTION_TYPE
       SYSDATE,                   --TRANSACTION_DATE
       'PENDING',                 --PROCESSING_STATUS_CODE
       'BATCH',                   --PROCESSING_MODE_CODE
       'PENDING',                 --TRANSACTION_STATUS_CODE
       1,                         --QUANTITY
       'Each',                    --UNIT_OF_MEASURE
       169845,                    --ITEM_ID
       13706,                     --EMPLOYEE_ID
       477550,                    --SHIPMENT_HEADER_ID
       484510,                    --SHIPMENT_LINE_ID
       'VENDOR',                  --RECEIPT_SOURCE_CODE
       7927,                      --VENDOR_ID
       204,                       --FROM_ORGANIZATION_ID
       'Stores',                  --FROM_SUBINVENTORY
       null,                      --FROM_LOCATOR_ID
       'PO',                      --SOURCE_DOCUMENT_CODE
       524017,                    --PARENT_TRANSACTION_ID
       40924,                     --PO_HEADER_ID
       46915,                     --PO_LINE_ID
       81963,                     --PO_LINE_LOCATION_ID
       84349,                     --PO_DISTRIBUTION_ID
       'RECEIVING',                      --DESTINATION_TYPE_CODE
       null,                      --DELIVER_TO_PERSON_ID
       null,                      --LOCATION_ID
       null,                      --DELIVER_TO_LOCATION_ID
       'Y'                        --VALIDATION_FLAG
      ); 

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            LOT_NUMBER,
            TRANSACTION_QUANTITY,
            PRIMARY_QUANTITY,
            SERIAL_TRANSACTION_TEMP_ID,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE, --LAST_UPDATE_DATE
            0,       --LAST_UPDATED_BY
            SYSDATE, --CREATION_DATE
            0,       --CREATED_BY
            0,       --LAST_UPDATE_LOGIN
            'S366',  --LOT_NUMBER
            1,       --TRANSACTION_QUANTITY
            1,       --PRIMARY_QUANTITY
            MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
            'RCV',   --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE 
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            FM_SERIAL_NUMBER,
            TO_SERIAL_NUMBER,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID) 
            VALUES 
            (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE,  --LAST_UPDATE_DATE
            0,        --LAST_UPDATED_BY
            SYSDATE,  --CREATION_DATE
            0,        --CREATED_BY
            0,        --LAST_UPDATE_LOGIN
            'SN0021', --FM_SERIAL_NUMBER
            'SN0021', --TO_SERIAL_NUMBER
            'RCV',    --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
Commit;
7) Run the following scripts to check data have been correctly inserted
SQL> Select * from RCV_TRANSACTIONS_INTERFACE where PO_HEADER_ID=40924;
INTERFACE_TRANSACTION_ID=474896
GROUP_ID=30053
TRANSACTION_TYPE=RETURN TO VENDOR
QUANTITY=1
PARENT_TRANSACTION_ID=524017
DESTINATION_TYPE_CODE=RECEIVING
RECEIPT_SOURCE_CODE='VENDOR' 
SOURCE_DOCUMENT_CODE='PO'
SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE 
where PRODUCT_TRANSACTION_ID=&interface_txn_id;
TRANSACTION_INTERFACE_ID=11561639
LOT_NUMBER=S366
TRANSACTION_QUANTITY=1
PRIMARY_QUANTITY=1
SERIAL_TRANSACTION_TEMP_ID=11561639
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=474896
SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE 
where PRODUCT_TRANSACTION_ID=&interface_txn_id;
TRANSACTION_INTERFACE_ID=11561639
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=474896
FM_SERIAL_NUMBER=SN0021
TO_SERIAL_NUMBER=SN0021
8) In Purchasing Responsibility, Run the Receiving Transaction Processor for the specific Group ID (GROUP_ID=30053)
9) Navigate to Receiving / Receiving Transactions Summary form For PO 8415 , Receipt Number 7970 has Receive, Deliver , Return to Receiving and Return to Supplier transactions
SQL> Select * from RCV_TRANSACTIONS where PO_HEADER_ID=40924
It returns 4 records
New record has
TRANSACTION_TYPE=RETURN TO VENDOR
TRANSACTION_ID=536017
DESTINATION_TYPE_CODE=RECEIVING
PARENT_TRANSACTION_ID=302978
QUANTITY=1
SQL> Select * from MTL_SERIAL_NUMBERS where INVENTORY_ITEM_ID=&Item_id
and lot_number like 'S366';

The script returns 2 records

For SERIAL_NUMBER=SN0020 
    CURRENT_STATUS=3 (Resides in stores)
    CURRENT_SUBINVENTORY_CODE=Stores

For SERIAL_NUMBER=SN0021 
    CURRENT_STATUS=4 (serial number issued from stores)
    CURRENT_SUBINVENTORY_CODE=null
10) Navigate to Purchase Orders/Purchase Orders Summary
At shipment and distribution level, for the PO Number 8415
Quantity Received=1
Quantity Delivered=1

References
NOTE:290489.1 - What are the New Receiving Open Interface (ROI) Functionality in Procurement Family Pack J/11.5.10?
NOTE:301281.1 - ROI: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items?
NOTE:367396.1 - Required Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J)