11.5.10 / R12 ROI How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

ROI How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

Goal
 New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher)  supports for Inter-Org Transfers:
         . RECEIVE transaction
         . DELIVER to Inventory Transaction

How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
 Solution

Following SAMPLE script is intended

To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done between 2 inventory organizations with Direct
Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)


In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.

The script will load records into the tables
          RCV_HEADERS_INTERFACE,
          RCV_TRANSACTIONS_INTERFACE,
          MTL_TRANSACTION_LOTS_INTERFACE
          MTL_SERIAL_NUMBERS_INTERFACE

If the item is only a standard item, only the records into
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables have to be created.


          ------------------------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) and For Release 12 see Note 423541.1


1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct

2) Item  'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and Destination Organization

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;

For both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'


3) Inventory  / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1' 
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010


4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:

Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_NUM= ROI-Lot-Serial-1
        RECEIPT_SOURCE_CODE=INVENTORY
        RECEIPT_NUM=null
        SHIP_TO_ORG_ID=210

Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_LINE_ID=246486
        UNIT_OF_MEASURE=Each
        ITEM_ID=169845
        QUANTITY_SHIPPED=10
        QUANTITY_RECEIVED=0
        SHIPMENT_LINE_STATUS_CODE=EXPECTED
        SOURCE_DOCUMENT_CODE=INVENTORY
        ROUTING_HEADER_ID=3
        FROM_ORGANIZATION=207
        TO_ORGANIZATION_ID=210
        TO_SUBINVENTORY=Staging1


5)  Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534

Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         LOT_NUM=S00226
         QUANTITY=10

Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
         LOT_NUM=S00226



RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example

1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002

Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'

. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'

. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226

. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_00002'


INSERT INTO RCV_HEADERS_INTERFACE
            (HEADER_INTERFACE_ID,
             GROUP_ID,
             PROCESSING_STATUS_CODE,
             RECEIPT_SOURCE_CODE,
             TRANSACTION_TYPE,
             AUTO_TRANSACT_CODE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATION_DATE,
             CREATED_BY,
             SHIPMENT_NUM,
             SHIP_TO_ORGANIZATION_ID,
             EXPECTED_RECEIPT_DATE,
             VALIDATION_FLAG
              )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'INVENTORY', --Receipt_Source_Code
             'NEW', --Transaction_Type
             'DELIVER',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'ROI-Lot-Serial-1',   --Shipment_Num
             210,            --Ship_To_Organization_Id,
             SYSDATE,        --Expected_Receipt_Date
             'Y'             --Validation_Flag
             );
   
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,
             INTERFACE_SOURCE_CODE,
             ITEM_ID,
             EMPLOYEE_ID,
             AUTO_TRANSACT_CODE,
             SHIPMENT_HEADER_ID,
             SHIPMENT_LINE_ID,
             RECEIPT_SOURCE_CODE,
             TO_ORGANIZATION_ID,
             SOURCE_DOCUMENT_CODE,
             DESTINATION_TYPE_CODE,
             SUBINVENTORY,
             SHIPMENT_NUM,
             EXPECTED_RECEIPT_DATE,
             HEADER_INTERFACE_ID,
             VALIDATION_FLAG
             )
             VALUES
             ( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID             
             rcv_interface_groups_s.currval,        --GROUP_ID
             SYSDATE,               --LAST_UPDATE_DATE
             0,                              --LAST_UPDATED_BY
             SYSDATE,               --CREATION_DATE
             0,                              --CREATED_BY
             0,                              --LAST_UPDATE_LOGIN
             'RECEIVE',               --TRANSACTION_TYPE
             SYSDATE,               --TRANSACTION_DATE
             'PENDING',              --PROCESSING_STATUS_CODE
             'BATCH',                  --PROCESSING_MODE_CODE
             'PENDING',              --TRANSACTION_STATUS_CODE
             2,                              --QUANTITY
             'Each',                       --UNIT_OF_MEASURE
             'RCV',                       --INTERFACE_SOURCE_CODE
             169845,                    --ITEM_ID
             13706,                      --EMPLOYEE_ID
             'DELIVER',              --AUTO_TRANSACT_CODE
             233534,                    --SHIPMENT_HEADER_ID
             246486,                    --SHIPMENT_LINE_ID
             'INVENTORY',        --RECEIPT_SOURCE_CODE
             210,                          --TO_ORGANIZATION_ID
             'INVENTORY',        --SOURCE_DOCUMENT_CODE
             'INVENTORY',        --DESTINATION_TYPE_CODE
             'Staging1',                 --SUBINVENTORY
             'ROI-Lot-Serial-1',    --SHIPMENT_NUM
             SYSDATE,               --EXPECTED_RECEIPT_DATE,
             rcv_headers_interface_s.currval,  --HEADER_INTERFACE_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
            'S00226',    --LOT_NUMBER
            2,                --TRANSACTION_QUANTITY
            2,                --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
            'SM1_00001', --FM_SERIAL_NUMBER
            'SM1_00002', --TO_SERIAL_NUMBER
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
Commit;

Note: On R12 instance, RHI.org_id or RTI.org_id should also be populated.


2) Run the following scripts to check data have been correctly inserted

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839

SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839


3) In Purchasing Responsibility, Change to receiving organization and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=25168)


4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 has Receive and Deliver transactions.


5) Check how the following application tables have been populated/updated

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
             
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012

SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED

SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records

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

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=307970
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=307969
    QUANTITY=2