INTER ORG INVENTORY TRANSFER

INTER ORG INVENTORY TRANSFER

In this document I am trying provide some basic steps for Inter-Org Inventory Transfer. One sub inventory issue items to another sub inventory. In step – I source sub inventory issue inventory to destination sub inventory. In step – II destination sub inventory receives inventory and take on charge.

Important Tables:

• MTL_SYSTEM_ITEMS_B
• MTL_MATERIAL_TRANSACTIONS
• MTL_TRANSACTION_INTERFACE
• MTL_TRANSACTION_TYPES
• RCV_SHIPMENT_HEADERS
• RCV_SHIPMENT_LINES
• RCV_HEADERS_INTERFACE
• RCV_TRANSACTIONS
• RCV_TRANSACTIONS_INTERFACE

STEP – I

First of all insert required data in MTL_TRANSACTIONS_INTERFACE table and run Process Transactions Interface Request from Oracle Applications (Inventory). It will generate records in following tables:

• MTL_MATERIAL_TRANSACTIONS
• RCV_SHIPMENT_HEADERS
• RCV_SHIPMENT_LINES

STEP – II

Now insert data in RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables and run Receiving Transaction Processor Request from Oracle Applications (Purchasing), it will create records in RCV_TRANSACTIONS and MTL_MATERIAL_TRANSACTIONS tables. In case of any error run Receiving Interface Error Report to view errors. Check error against your interface transaction ID.

Sample Script is as follows:

INSERT INTO MTL_TRANSACTIONS_INTERFACE (

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATE_BY,

SOURCE_CODE

SOURCE_LINE_ID,

SOURCE_HEADER_ID

PROCESS_FLAG,

INVENTORY_ITEM_ID,

ORGANIZATION_ID,

SUBINVENTORY_CODE,

TRANSACTION_TYPE_ID,

TRANSACTION_QUANTITY,

TRANSACTION_UOM,

TRANSACTION_DATE,

TRANSFER_ORGANIZATION,

TRANSFER_SUBINVENTORY,

TRANSACTION_MODE,

REVISION,

SHIPMENT_NUMBER)

VALUES (

SYSDATE, --- CREATION_DATE,

1318, --- CREATED_BY,

SYSDATE, --- LAST_UPDATE_DATE,

1318, --- LAST_UPDATE_BY,

‘KHALID001’, --- SOURCE_CODE

1, --- SOURCE_LINE_ID,

1, --- SOURCE_HEADER_ID

1, --- PROCESS_FLAG,

8063, --- INVENTORY_ITEM_ID,

204, --- ORGANIZATION_ID,

‘Stores’, --- SUBINVENTORY_CODE,

354, --- TRANSACTION_TYPE_ID,

-35, --- TRANSACTION_QUANTITY,

‘QT’, --- TRANSACTION_UOM,

SYSDATE, --- TRANSACTION_DATE,

3159, --- TRANSFER_ORGANIZATION,

‘Fleet’, --- TRANSFER_SUBINVENTORY,

3, --- TRANSACTION_MODE,

‘A’, --- REVISION,

‘KHALID001’ --- SHIPMENT_NUMBER

);

--- RECEIVE & DELIVER

DECLARE

V_HEADER_ID NUMBER;

V_GROUP_ID NUMBER;

BEGIN

select RCV_INTERFACE_GROUPS_S.NEXTVAL into V_GROUP_ID from dual ;

select RCV_HEADERS_INTERFACE_S.NEXTVAL into V_HEADER_ID from dual ;

--- Receiving Shipment

INSERT INTO RCV_HEADERS_INTERFACE

(

HEADER_INTERFACE_ID,

GROUP_ID,

PROCESSING_STATUS_CODE,

RECEIPT_SOURCE_CODE,

TRANSACTION_TYPE,

AUT_TRANSACT_CODE,

LAST_UPDATE_DATE,

LAST_UPDATE_BY,

CREATION_DATE,

CREATED_BY,

VALIDATION_FLAG,

COMMENTS,

SHIPMENT_NUM,

FROM_ORGANIZATION_ID,

SHIP_TO_ORGANIZATION_ID

)

VALUES

(

V_HEADER_ID, --- HEADER_INTERFACE_ID,

V_GROUP_ID, --- GROUP_ID,

‘PENDING’, --- PROCESSING_STATUS_CODE,

‘INVENTORY’, --- RECEIPT_SOURCE_CODE,

‘NEW’, --- TRANSACTION_TYPE,

‘DELIVER’, --- AUT_TRANSACT_CODE,

SYSDATE, --- LAST_UPDATE_DATE,

1318, --- LAST_UPDATE_BY,

SYSDATE, --- CREATION_DATE,

1318, --- CREATED_BY,

‘Y’, --- VALIDATION_FLAG,

‘Receiving Through Interface’, --- COMMENTS,

‘KHALID001’, --- SHIPMENT_NUM,

204, --- FROM_ORGANIZATION_ID,

3159 --- SHIP_TO_ORGANIZATION_ID

);

--- Deliver Inventory

INSERT INTO RCV_TRANSACTIONS_INTERFACE (

INTERFACE_TRANSACTION_ID,

HEADER_INTERFACE_ID,

GROUP_ID,

TRANSACTION_TYPE,

TRANSACTION_DATE,

PROCESSING_STATUS_CODE,

PROCESSING_MODE_CODE,

TRANSACTION_STATUS_CODE,

QUANTITY,

LAST_UPDATE_DATE,

LAST_UPDATE_BY,

CREATION_DATE,

CREATED_BY,

RECEIPT_SOURCE_CODE,

DESTINATION_TYPE_CODE,

AUT_TRANSACT_CODE,

SOURCE_DOCUMENT_CODE,

UNIT_OF_MEASURE,

ITEM_ID,

UOM_CODE,

EMPLOYEE_ID,

SHIPMENT_HEADER_ID,

SHIPMENT_LINE_ID,

TO_ORGANIZATION_ID,

SUBINVENTORY_ID,

FROM_ORGANIZATION_ID,

FROM_SUBINVENTORY

)

VALUES

(

RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_TRANSACTION_ID,

V_HEADER_ID, --- HEADER_INTERFACE_ID,

V_GROUP_ID, --- GROUP_ID,

‘RECEIVE’, --- TRANSACTION_TYPE,

SYSDATE, --- TRANSACTION_DATE,

‘PENDING’, --- PROCESSING_STATUS_CODE,

‘BATCH’, --- PROCESSING_MODE_CODE,

‘PENDING’, --- TRANSACTION_STATUS_CODE,

35, --- QUANTITY,

SYSDATE, --- LAST_UPDATE_DATE,

1318, --- LAST_UPDATE_BY,

SYSDATE, --- CREATION_DATE,

1318, --- CREATED_BY,

‘INVENTORY’, --- RECEIPT_SOURCE_CODE,

‘INVENTORY’, --- DESTINATION_TYPE_CODE,

‘DELIVER’, --- AUT_TRANSACT_CODE,

‘INVENTORY’, --- SOURCE_DOCUMENT_CODE,

‘Quart’, --- UNIT_OF_MEASURE,

8063, --- ITEM_ID,

‘QT’, --- UOM_CODE,

25, --- EMPLOYEE_ID,

4815004, --- SHIPMENT_HEADER_ID,

4805540, --- SHIPMENT_LINE_ID,

3159, --- TO_ORGANIZATION_ID,

‘Fleet’, --- SUBINVENTORY_ID,

204, --- FROM_ORGANIZATION_ID,

‘Stores’ --- FROM_SUBINVENTORY

);

COMMIT;

END;