Oracle Interface Tables

Data can feeded in 3 ways

1. The Data can be entered using the application Screens.

2.The data can be entered using Oracle’s Open System Interface.

3. The data can be stored in the database table directly

Conversion An overview of its process

(Inbound Process)

1. Get data from legacy system Called Flat file.

2. Load the flat file data into temporary table called staging table using sqlloader

(control files are used .ctl extensions)

3. Validate the data in staging table using pl/sql procedures to define rules of validation.

4. Successful Validation rakes up the data to the interface tables.

5. These Validate data can be directed to base tables using predefined Seeded programs.

(Outbound process)

outbound Interface Process:

=============================

1)Develop the PL/SQL Program (Either Procedure or Package)

2)Write the Cursor to retrieve the data from database tables.

3)Create file or Open the File by using UTL_File.fopen().

4)Open the Cursor

5)If any validations are there write the validations

6)Transfer the Data into File by using UTL_File.Put_Line().

7)Close the Cursor.

8)Close the File by using UTL_File.fclose();

9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.

We will use following three functions to generate the file.

1)Utl_File.fopen     = To open (or) Create the file

2)Utl_File.Put_line  = To Transfer the data into the File.

3)Utl_File.fclose    = To close the File after Data transfer.

Difference Between Conversion and Interface

1. Conversion is one-time process 1. Interface is the post production

which is performed once before process. production go on live.(Pre-production process)

2. Data comes in to Oracle Applications 2. Interface is the integration of

only (One way process) two systems.

3. Interative Process 3. Scheduled and repetive process.

Open Interface Tables are standard Oracle tables.

-Oracle uses OITs to provide a simple interface to Oracle base tables.

-Oracle has list of all the open interface that oracle offered in there product.

Interface Program

- It is an inbuilt program in most of the modules to pull up the data from the interface table

Under validations sends data to base tables,if validation fails Displays data to Error a error message

Important Interface Tables

GL INTERFACE TABLES　

GL_BUDGET_INTERFACE TABLE

GL_DAILY_RATES_INTERFACE TABLE

GL_IEA_INTERFACE TABLE

GL_INTERFACE TABLE

GL_INTERFACE_CONTROL TABLE

GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES

AP_INTERFACE_CONTROLS TABLE

AP_INTERFACE_REJECTIONS TABLE

AP_INVOICES_INTERFACE TABLE

AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES

　

AR_PAYMENTS_INTERFACE_ALL TABLE

AR_TAX_INTERFACE TABLE

HZ_DQM_SYNC_INTERFACE TABLE

HZ_PARTY_INTERFACE TABLE

HZ_PARTY_INTERFACE_ERRORS TABLE

RA_CUSTOMERS_INTERFACE_ALL TABLE

RA_INTERFACE_DISTRIBUTIONS_ALL TABLE

RA_INTERFACE_ERRORS_ALL TABLE

RA_INTERFACE_LINES_ALL TABLE

RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES

　

FA_BUDGET_INTERFACE TABLE

FA_INV_INTERFACE TABLE

FA_PRODUCTION_INTERFACE TABLE

FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES

MTL_CC_ENTRIES_INTERFACE TABLE

MTL_CC_INTERFACE_ERRORS TABLE

MTL_CI_INTERFACE TABLE

MTL_CI_XREFS_INTERFACE TABLE

MTL_COPY_ORG_INTERFACE TABLE

MTL_CROSS_REFERENCES_INTERFACE TABLE

MTL_DEMAND_INTERFACE TABLE

MTL_DESC_ELEM_VAL_INTERFACE TABLE

MTL_EAM_ASSET_NUM_INTERFACE TABLE

MTL_EAM_ATTR_VAL_INTERFACE TABLE

MTL_INTERFACE_ERRORS TABLE

MTL_INTERFACE_PROC_CONTROLS TABLE

MTL_ITEM_CATEGORIES_INTERFACE TABLE

MTL_ITEM_CHILD_INFO_INTERFACE TABLE

MTL_ITEM_REVISIONS_INTERFACE TABLE

MTL_ITEM_SUB_INVS_INTERFACE TABLE

MTL_OBJECT_GENEALOGY_INTERFACE TABLE

MTL_RELATED_ITEMS_INTERFACE TABLE

MTL_RESERVATIONS_INTERFACE TABLE

MTL_RTG_ITEM_REVS_INTERFACE TABLE

MTL_SECONDARY_LOCS_INTERFACE TABLE

MTL_SERIAL_NUMBERS_INTERFACE TABLE

MTL_SO_RMA_INTERFACE TABLE

MTL_SYSTEM_ITEMS_INTERFACE TABLE

MTL_TRANSACTIONS_INTERFACE TABLE

MTL_TRANSACTION_LOTS_INTERFACE TABLE

MTL_TXN_COST_DET_INTERFACE TABLE

PO INTERFACE TABLES

PO_DISTRIBUTIONS_INTERFACE TABLE

PO_HEADERS_INTERFACE TABLE

PO_INTERFACE_ERRORS TABLE

PO_LINES_INTERFACE TABLE

PO_REQUISITIONS_INTERFACE_ALL TABLE

PO_REQ_DIST_INTERFACE_ALL TABLE

PO_RESCHEDULE_INTERFACE TABLE

RCV_HEADERS_INTERFACE TABLE

RCV_LOTS_INTERFACE TABLE

RCV_SERIALS_INTERFACE TABLE

RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

BOM_BILL_OF_MTLS_INTERFACE TABLE

BOM_INTERFACE_DELETE_GROUPS TABLE

BOM_INVENTORY_COMPS_INTERFACE TABLE

BOM_OP_RESOURCES_INTERFACE TABLE

BOM_OP_ROUTINGS_INTERFACE TABLE

BOM_OP_SEQUENCES_INTERFACE TABLE

BOM_REF_DESGS_INTERFACE TABLE

BOM_SUB_COMPS_INTERFACE TABLE

CST_COMP_SNAP_INTERFACE TABLE

CST_INTERFACE_ERRORS TABLE

CST_ITEM_COSTS_INTERFACE TABLE

CST_ITEM_CST_DTLS_INTERFACE TABLE

CST_PC_COST_DET_INTERFACE TABLE

CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

WIP_COST_TXN_INTERFACE TABLE

WIP_INTERFACE_ERRORS TABLE

WIP_JOB_DTLS_INTERFACE TABLE

WIP_JOB_SCHEDULE_INTERFACE TABLE

WIP_MOVE_TXN_INTERFACE TABLE

WIP_SCHEDULING_INTERFACE TABLE

WIP_TXN_INTERFACE_ERRORS TABLE