An Introduction to Basics of Interfaces in Oracle Apps.

What is Oracle Apps Conversion?

Conversion is one of the program which will be used to upload the data from legacy system in to oracle applications, base tables and it is one time data transfer.

What is Oracle Apps Interface?


Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transaction data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

IMPORTING SALES ORDERS AND/OR RETURNS.

What we can import?
New sales orders,Changed sales orders,Completed sales orders,Returns sales orders..etc.

From Where we can import?
From legacy applications...

1. Through EDI Transactions processed through the Gateway.
2. Internal orders created from Oracle Purchasing’s internal requisitions, or returns.

What are Interfaces?

·         Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
·         The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
·         Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
·         Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
·         Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

·         Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.

·         Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

·         Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.

·         Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

·         First the data from the source application is loaded into a database table (called Interface table).

·         Then the provided validation program logic validates the records whether they are correct or not .

·         If the validation fails, the errors are transferred into another table (called Error Table).

·         If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:
You obtain data from a source application to pass on to a destination application for further processing and/or storage.
b] Source Data Issues:
Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.
c] Destination Application:
You send data to a destination application so that the application can perform further processing and/or storage.
d] Interface Table:
For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.
e] Identifier columns: 
Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.
f] Control Columns:

·         Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.

·         WHO columns are also control columns.

g] Data Columns:

·         Stores the data that is being converted.

·         Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:
Derived columns are created by the destination application from information in the required columns.
i] Optional Columns:
Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.
j] Error Table:

·         For inbound interfaces, the errors table stores all errors found by the validation and processing functions.

·         In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.

·         In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:
Find out if there exists an Open Interface to carry out the functionality.
2] Creation of Pre-Interface table ( staging Table):
A table in the format of the data file which can be pruned to load as clean a data into the Interface table.
3] Load data into Pre-Interface table:
SQL*LOADER can be used to load the flat file into the pre-interface table.
4] Validate data in the Pre-Interface table:
Basic validation of the data loaded into the Pre-Interface table can be carried out like:

·         For checking NULL values in required columns

·         Checking for Foreign Key and Quick Code values.

·         Duplication Validation

·         Business Rule validation

5] Mapping the values:
Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.
6] Load data into Interface table:

·     Once the data is as clean as you can get it, the data can be inserted into the Interface table.

·     At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.     

•         Run the interface program

•         Check for Errors

•         Report on the Interface

ABOUT INTERFACES:

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.

A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are
• Transfer Program
• Interface Table and
• Import Program

A] Transfer Program:
If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.
What they do?

·         It maps the columns of source table with the columns of Interface Tables.

·         It performs Row Level and Column Level validations.

·         It transfers the data from Source to the Interface Table.

B] Interface Tables:
The Interface tables basically have 4 types of columns.

1.     Mandatory Columns.

2.     Conditionally Required Columns.

3.     Optional Columns.

4.     Internal Processing Columns.

Mandatory Columns:
These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.

Conditionally Required Columns:
The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.

Optional Columns:
These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.

Internal Processing Columns:
Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.

C] Import Program:
For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.

What they do?

·         It maps the columns of the Interface Table with one or more columns in the destination table.

·         It performs row level and column level validation.

·         It imports the data from Interface Table to the Destination tables, if the records validated successfully.

·         It deletes all the successfully validated records from Interface Table.

·         If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.