ROI: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items

Goal
 Which interface tables are used to populate Serial and Lot information for Receiving Open Interface (ROI)?

Executable:RVCTP - Receiving Transaction Processor

Solution

In addition to rcv_headers_interface and rcv_transactions_interface, the mtl_transaction_lots_interface and mtl_serial_numbers_interface tables must be populated for the Receiving Open Interface (ROI) to process Delivery, Correction and Return of Serial and Lot Controlled Items.

Significant software changes were made in 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) to accommodate new ROI functionality.  Due to dependencies, the new ROI functionality will not be backported.

Example:
1) If Item is Serial Controlled only:


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)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


2) If Item is Lot Controlled only:

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,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


3) If Item is both Serial and Lot Controlled:


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)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 

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, --mtl_serial_numbers_interface.transaction_interface_id
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL);  --rcv_transactions_interface.interface_transaction_id



When transacting Items that are both Lot and Serial Controlled, if Quantity is greater than 1, populate mtl_serial_numbers_interface and mtl_transaction_lots_interface as follows:
1.  one record in mtl_serial_numbers_interface table for each Serial Number (or Serial Number range)
2.  for each of the mtl_serial_numbers_interface records, populate a corresponding record in mtl_transaction_lots_interface with the Lot Number associated with each mtl_serial_numbers_interface record (mtl_transaction_lots_interface.serial_transaction_temp_id=mtl_serial_numbers_interface.transaction_interface_id)