Data Loading into Oracle E-Business Suite through Open Interface Tables

 

There are many times an Oracle Apps professional needs to insert or update data into the Oracle EBS system from an external source. This can be legacy or test data. The source could be either plain text file such as CSV or it could be third party databases like MySQL, MS SQL Server or similar. To insert or to update the data most of the professionals will think of SQL * Loader or Oracle Export or Import tool. But in Oracle EBS applications we cannot directly load the data into Oracle Applications tables. Since Oracle Applications tables are interrelated, if you directly insert / update data into one table, then these changes will not reflect in related tables and thus break the logic.

 

To load the data Oracle has provided open interface tables in each module. The Open Interface Tables are intermediate tables into which we have to load the legacy or test data using any tool such as SQL * Loader or Data Loader etc. After loading, the data is validated and then transferred to the base tables. The base tables are the real application tables. To transfer data from interface tables, we have to run a concurrent program to move the data into actual Oracle Applications base tables. These concurrent programs maintain the logic.

Most of the times, we create stage tables first to load raw data and then do necessary validations and checks and then move the validated data into interface tables.

A concurrent program is just like an execution file. Concurrent programs are executed in Oracle E-Business Suite to move data from interface tables to base tables. These programs perform the application-level checks and run validation before inserting data into base tables.

Similarly, Oracle has also provided Interface Views to extract data from Oracle EBS suite. Views allow only simple definition. By using views, you can get concurrent data access to Oracle E-Business Suite. Only SELECT operations are allowed on these Views.

 

Steps to load data into Oracle E-Business suite

 

  1. Create Stage table

  2. Load the data into Stage table using SQL * Loader or Dataloader utility

  3. Create a PL/SQL program to validate the data in stage table

  4. Map data from  source stage table to target interface table

  5. Transfer data from stage table to interface table

  6. Finally, run Concurrent Program / API to move the data from interface tables to actual Oracle Applications base tables

  7. Analyze and correct if any error occurs during Concurrent Program execution

 

dataload in oracle apps using interface tables

 

 

 

Here is the list of Open Interface Tables in Oracle EBS

 

Application
Table Name
AP Interface Tables
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
AR INTERFACE TABLES
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL
AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL  AR_INTERIM_CASH_RECEIPT_LINES_ALL
RA_CUSTOMERS_INTERFACE
RA_CUSTOMER_PROFILES_INTERFACE
RA_CONTACT_PHONES_INTERFACE
RA_CUSTOMER_BANKS_INTERFACE
RA_CUST_PAY_METHOD_INTERFACE
BOM INTERFACE TABLES BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE
CM INTERFACE TABLES
CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE
FA INTERFACE TABLES FA_BUDGET_INTERFACE
FA_INV_INTERFACE
FA_PRODUCTION_INTERFACE
FA_TAX_INTERFACE
GL INTERFACE TABLES
GL_INTERFACE
GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY
INV INTERFACE TABLES
MTL_SYSTEM_ITEMS_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_RESOURCE_COSTS_INTERFACE
CST_RES_OVERHEADS_INTERFACE
CST_DEPT_OVERHEADS_INTERFACE
OM INTERFACE TABLES
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL
PO INTERFACE TABLES
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE

 

 

 

Before you begin the loading process, you will first need to check the documentation of Open Interface Table for that particular module. In each Oracle Applications Module,  Oracle documentation mentions the names of the open interface tables and its structure. It also mentions which fields in the open interface table are mandatory and which fields are optional. So before you start creating the stage table you will first read and analyze the structure of the open interface tables and then map your source data to the interface tables.

 

 

Oracle will also give description for each column in the open interface table. After reading the description you should decide which source column data should go into each column in open interface table.

 

Here is an example from Oracle Documentation for Payable Interface Tables

 

Column Name Null Type Comments
INVOICE_ID NOT NULL NUMBER(15) Required, Primary key
INVOICE_NUM NOT NULL VARCHAR2(50) Required
INVOICE_TYPE_ LOOKUP_CODE   VARCHAR2(25) Optional
INVOICE_DATE   DATE Optional
PO_HEADER_ID   NUMBER(15) Optional
PO_NUMBER_ID   VARCHAR2(20) Foreign key: PO_HEADERS. SEGMENT1
VENDOR_ID   NUMBER(15) Internal ID Foreign key: PO_VENDORS. VENDOR_ID
VENDOR_NUM   VARCHAR2(30) Optional
VENDOR_NAME   VARCHAR2(80) Optional
VENDOR_SITE_ID   NUMBER(15) Internal ID Foreign key: PO_ VENDOR_SITES. VENDOR_SITE_ID
VENDOR_SITE_CODE   VARCHAR2(15) Optional
INVOICE_AMOUNT   NUMBER Required
INVOICE_ CURRENCY_CODE   VARCHAR2(15) Optional Foreign key: FND_CURRENCIES. CURRENCY_CODE
EXCHANGE RATE   NUMBER Foreign key: GL_DAILY_ CONVERSION_TYPES. CONVERSION_TYPE
EXCHANGE RATE TYPE   VARCHAR2(30)  
EXCHANGE_DATE   DATE  
PAYMENT_ CURRENCY_CODE   VARCHAR2(15) Optional Foreign key: FND_CURRENCIES. CURRENCY_CODE
PAYMENT_ CROSS_RATE_TYPE   VARCHAR2(30) Optional
PAYMENT_ CROSS_RATE   NUMBER Optional
PAYMENT_CROSS_ RATE_DATE   DATE Optional
TERMS_ID   NUMBER(15) Internal ID Foreign key: AP_TERMS.TERMS_ID
TERMS_NAME   VARCHAR2(50) Optional
DESCRIPTION   VARCHAR2(240) Optional
AWT_GROUP_ID   NUMBER(15) Internal ID Foreign key: AP_AWT_GROUPS. AWT_GROUP_ID
AWT_GROUP_NAME   VARCHAR2(25) Optional
LAST_UPDATE_DATE   DATE Optional
LAST_UPDATED_BY   NUMBER(15) Optional
LAST_UPDATE_LOGIN   NUMBER(15) Optional
CREATION_DATE   DATE Optional
CREATED_BY   NUMBER(15) Optional
ATTRIBUTE CATEGORY   VARCHAR2(150) Optional
ATTRIBUTE1   VARCHAR2(150) Optional
ATTRIBUTE2   VARCHAR2(150) Optional
ATTRIBUTE3   VARCHAR2(150) Optional
ATTRIBUTE4   VARCHAR2(150) Optional
ATTRIBUTE5   VARCHAR2(150) Optional
ATTRIBUTE6   VARCHAR2(150) Optional
ATTRIBUTE7   VARCHAR2(150) Optional
ATTRIBUTE8   VARCHAR2(150) Optional
ATTRIBUTE9   VARCHAR2(150) Optional
ATTRIBUTE10   VARCHAR2(150) Optional
ATTRIBUTE11   VARCHAR2(150) Optional
ATTRIBUTE12   VARCHAR2(150) Optional
ATTRIBUTE13   VARCHAR2(150) Optional
ATTRIBUTE14   VARCHAR2(150) Optional
ATTRIBUTE15   VARCHAR2(150) Optional
GLOBAL_ ATTRIBUTE_CATEGORY   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE1   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE2   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE3   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE4   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE5   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE6   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE7   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE8   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE9   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE10   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE11   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE12   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE13   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE14   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE15   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE16   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE17   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE18   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE19   VARCHAR2(150) Optional
GLOBAL_ATTRIBUTE20   VARCHAR2(150) Optional
WORKFLOW_FLAG   VARCHAR2(1) Optional
SOURCE   VARCHAR2(80) Required
GROUP_ID   NUMBER Optional
STATUS NULL VARCHAR2(25)  
REQUEST_ID NULL NUMBER  

 

 

INVOICE_ID

Unique identifier for the invoice in this batch. You assign the same value to the invoice's distributions in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.

Validation: None
Destination: None

INVOICE_NUM

Enter the invoice number that you want to assign to the invoice created in Payables from this record.

Validation: You must assign a unique number for the supplier to the invoice. If you assign a duplicate number for the supplier, Payables Open Interface Import does not create an invoice from this record and lists this record on the Payables Open Interface Rejections Report.

 Destination: AP_INVOICES.INVOICE_NUM

 

Please view Oracle Documentation for complete descriptions

 

 

after reading the descriptions you can directly load the data into open interface tables if you are sure that your source data is 100% compatible with oracle requirements, which is a rare case. That's why most of the time we need to create a Stage table first and then load external data into it and then validate the data in stage tables and later transfer it to the open interface table.

 

Because of the complexity and time consuming nature if you load the data through Interface tables, most of the Oracle Apps DBA's rely on Dataloader, which loads the data through front end screen.

 

 

 

Contact Us

Products
Support


© Interface Computers  All Rights Reserved