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;

Invoice Tracking from GL

Invoice back tracking from GL. some important tables are as under:

• GL_JE_HEADERS
• GL_HE_LINES
• GL_IMPORT_REFERENCES
• XLA_AE_LINES
• XLA_AE_HEADERS
• XLA_EVENTS
• XLA_TRASACTION_ENTITIES_UPG
• AP_INVOICES_ALL
• AP_INVOICE_LINES_ALL
• AP_INVOICE_DISTRIBUTIONS_ALL

A graphical relationship view is given below:



I hope diagram is very helpful to understand flow of invoice to GL.

AP Invoices Tables

Purpose of creating this blog is to explain the process of uploading supplier invoices, through Oracle Accounts Payables (AP) module's interface tables.
Interface Tables:

• AP_INVOICES_INTERFACE
• AP_INVOICE_LINES_INTERFACE

Base Tables:

• AP_INVOICES_ALL
• AP_INVOICE_DISTRIBUTIONS_ALL
• AP_PAYMENT_SCHEDULES_ALL

Error Tables:

• AP_INTERFACE_REJECTIONS
• AP_INTERFACE_CONTROLS