Interfaces

Interfaces are the tables which acts as a medium to transfer data from one module to another.or transfer data from legacy system to oracle applications. There are 352 tables provided by the oracle package. Each module having its own interface tables.

Interfacing
It is the process of converting the records from one format to another. The main components are:

1.Transfer Program: Transfer programs are generally developed using JAVA, PLSQL or SQL LOADER. When source modules are implemented in Oracle applications, then Transfer program comes by default with the package. Otherwise, we have to developer it.

Logic of interface programs:
It maps the columns of the source table with the columns of the interface tables.
It performs row level and column level validations.
It transfer data from the source to the interface tables.

2.Interface tables: These tables are used to store data coming form the staging table before moving to Oracle base tables.

3.Import program:  These programs are designed using PLSQL, Java, C, C++, etc. They are generally registered with destination modules.For all the interface tables, Oracle application package is going to provide Import programs..

Flexfields : A brief note

A flexfield is made up of sub-fields or segments. There are two types of flexfields:

1. Key Flexfield (KFF) :  It appears on our apps screen as a normal text field with an appropriate prompt. They are used to capture mandatory an key business information of the organization. Each KFF has its own base table.

There are 31 KFF supplied with the oracle package.

2. Descriptive Flex Field (DFF): They are used to capture additional or extra business information of the organization. They are used for adding extra accounts depends as per business requirements.

DFF has two different segments, Global DFF and context sensitive DFF.

Classification of tables in Oracle Applications

1. Transaction Data table : They are normal tables, they are used to stored the data in all the modules in which any DML queries can be performed.

2.Seed data tables: The data in these tables are created at the time of installation. The records in these tables are read only.

3.Interim data table: These tables are used for validations purpose. For instance, Staging tables. These tables are used when we are transferring the data from the external application. to the system tables.

Data flow in oracle apps from flat file to base table- A brief overview

Flat File

The Structure of the flat file must match the structure of the Oracle standard interface tables. If in some cases, they do not match, then we need to get the values from the base tables using the interface programs (validations). These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

Upload Program

Once the data has been extracted to a flat file, it is then moved / loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities.

Interface Program

Then PLSQL interface programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables. If the staging table columns are not matching with the Interface tables, the validations are carried out. For instance,

BEGIN

SELECT USER_JE_CATEGORY_NAME  --column name

INTO i_CATEGORY  --cursor variable name

FROM GL_JE_CATEGORIES --standard interface table name

WHERE USER_JE_CATEGORY_NAME = REC_CUR.CATEGORY;                                                                         


Suppose we have 'Category' column in the staging table, but we need the values of 'USER_JE_CATEGORY_NAME'. We need to get the values of 'USER_JE_CATEGORY_NAME' from the 'GL_JE_CATEGORIES' interface table and temporary store the values in 'l_CATEGORY' which is a cursor.

Later we can store the value of 'l_CATEGORY' to the interface table.

Standard Interface Program
Once the interface tables rae populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. the Interface programs validate the data, derive and assign the default values and ultimately populated the production base tables.