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
Create Stage table
Load the data into Stage table using SQL * Loader or Dataloader utility
Create a PL/SQL program to validate the data in stage table
Map data from source stage table to target interface table
Transfer data from stage table to interface table
Finally, run Concurrent Program / API to move the data from interface tables to actual Oracle Applications base tables
Analyze and correct if any error occurs during Concurrent Program execution
Here is the list of Open Interface Tables in Oracle EBS
|
Application
|
Table Name
|
|
AP
|
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE AP_SUPPLIERS_INT AP_SUPPLIER_SITES_INT AP_SUP_SITE_CONTACT_INT |
|
AR
|
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
|
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
|
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
|
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
|
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 | |
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
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.
© Interface Computers All Rights Reserved