API - Return Material Authorization (RMA)

CONTENT
-------
Sample data and script for performing a DELIVER transaction against RMA with lot
and serial controlled item using Receiving Open Interface (ROI)

1) Setup Item:
Item = CM31556, Org = V1
Lot control = Full Control
Starting Prefix = L
Starting Number = 1000
Serial Control = At Receipt
Starting Prefix = S
Starting Number = 1000

2) Create RMA:
RMA = 56705
Order Type = Return only
Customer = AT&T Universal Card
Ship to = Jacksonville (OPS)
Bill to = Jacksonville (OPS)

Item = CM31556, qty = -10

3) Book RMA.
(you can use Help/Diagnostics/Examine to find out the following values)
OE_ORDER_HEADER_ID = 94034
OE_ORDER_LINE_ID = 195898


4) create RECEIVE transaction against RMA.
(you can use Help/Diagnostics/Examine to find out the following values)
SHIPMENT_HEADER_ID = 70436
SHIPMENT_LINE_ID = 79378
PARENT_TRANSACTION_ID = 121729 (transaction_id for RECEIVE transaction)

5) Insert records into interface tables to create DELIVER transaction:

INSERT INTO RCV_HEADERS_INTERFACE
(
 HEADER_INTERFACE_ID            ,
 GROUP_ID  ,
 PROCESSING_STATUS_CODE          ,
 RECEIPT_SOURCE_CODE ,
 TRANSACTION_TYPE                ,
 LAST_UPDATE_DATE                ,
 LAST_UPDATED_BY        ,
 LAST_UPDATE_LOGIN               ,
 CREATION_DATE    ,
 CREATED_BY                  ,
 SHIPPED_DATE                    ,
 CUSTOMER_ID        ,
 EMPLOYEE_ID                   ,
 VALIDATION_FLAG                 ,
 SHIP_TO_ORGANIZATION_CODE  ,
 EXPECTED_RECEIPT_DATE,
 RECEIPT_HEADER_ID
)
SELECT
 RCV_HEADERS_INTERFACE_S.NEXTVAL,
 RCV_INTERFACE_GROUPS_S.NEXTVAL,
 'PENDING',
 'CUSTOMER',
 'NEW',
 sysdate,
 1,
 1,
 sysdate,
 1,
 sysdate,
 1005,
 29,
 'Y',
 'V1',
 sysdate+5,
 70436 -- SHIPMENT_HEADER_ID
FROM DUAL;


insert into rcv_transactions_interface (
interface_transaction_id,
group_id,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
PARENT_TRANSACTION_ID,
last_update_date,
last_updated_by,
created_by,
creation_date,
transaction_date,
transaction_type,
to_organization_id,
interface_source_code,
receipt_source_code,
source_document_code,
processing_status_code,
processing_mode_code,
validation_flag,
transaction_status_code,
quantity,
uom_code,
item_id,
oe_order_header_id,
oe_order_line_id,
inspection_status_code, --  'ACCEPTED', 'REJECTED', 'NOT INSPECTED'
destination_type_code, -- 'INVENTORY' , 'RECEIVING'
location_id,
deliver_to_location_id,
subinventory,
locator_id,
use_mtl_lot,
use_mtl_serial,
transfer_lpn_id )
values (
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL,
RCV_INTERFACE_GROUPS_S.CURRVAL,
70436, -- SHIPMENT_HEADER_ID
79378, -- SHIPMENT_LINE_ID
121729,  -- PARENT_TRANSACTION_ID
sysdate,
1, -- last_updated_by
1, -- created_by
sysdate,
sysdate, -- transaction_date
'DELIVER', -- transaction_type -- 'DELIVER', 'ACCEPT'
204, -- to_organization_id -- V1
'RCV', -- interface_source_code -- 'RCV'
'CUSTOMER', -- receipt_source_code -- 'VENDOR'
'RMA', -- source_document_code -- 'PO'
'PENDING', -- processing_status_code
'BATCH', -- processing_mode_code
'Y', -- validation_flag
'PENDING', -- transaction_status_code
1, -- quantity
'Ea', -- uom_code
199, -- item_id
94034, -- oe_order_header_id
195898, -- oe_order_line_id
'NOT INSPECTED', -- inspection_status_code
'INVENTORY', -- destination_type_code -- 'INVENTORY' , 'RECEIVING'
204, -- location_id  -- V1 - New York City
NULL, -- deliver_to_location_id
'Stores',  -- subinventory
NULL, -- locator_id 
NULL, -- use_mtl_lot
NULL, -- use_mtl_serial
NULL -- transfer_lpn_id
);

INSERT INTO mtl_transaction_lots_interface
(
TRANSACTION_interface_ID      
,LAST_UPDATE_DATE         
,LAST_UPDATED_BY          
,CREATION_DATE            
,CREATED_BY               
,LAST_UPDATE_LOGIN        
,TRANSACTION_QUANTITY     
,PRIMARY_QUANTITY         
,LOT_NUMBER               
,LOT_EXPIRATION_DATE      
,SERIAL_TRANSACTION_TEMP_ID
,PRODUCT_CODE             
,PRODUCT_TRANSACTION_ID   
) values (
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
,SYSDATE
,1
,SYSDATE
,1
,1
,1
,1
,'L1001'
,NULL
,MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--SERIAL_TRANSACTION_TEMP_ID
,'RCV'
,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL
);


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       
,SERIAL_ATTRIBUTE_CATEGORY
,C_ATTRIBUTE1
,C_ATTRIBUTE3
,PRODUCT_CODE            
,PRODUCT_TRANSACTION_ID  
)
VALUES
(
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL
,sysdate
,1
,sysdate
,1
,1
,'S1001'
,'S1001'
,NULL
,NULL
,NULL
,'RCV'
,RCV_TRANSACTIONS_INTERFACE_S.CURRVAL
);

commit;

Run Receiving Transaction Processor Concurrent Request

6)
Query for RMA on Receiving Transactions Summary to confirm the Deliver
transaction was performed.