Interfaces In Oracle Apps

Interfaces in Oracle applications

Interfaces:-

In Oracle apps Interfaces are generally tables, which act as a medium to transfer data from one module to another module (or) either transfer the data from oracle applications to flat files (or) from legacy systems to oracle applications.

There are totally 352 tables provided by Oracle package. Each module has its own Interface tables.

Types of Interfaces:-

There are two major types of interfaces.

1.     Inbound Interface: - These interfaces are used to transfer data from external systems to Oracle applications.

2.     Outbound Interface: - These interfaces are used to transfer data from Oracle applications to external systems.

There are two other distinctions of Interfaces

1.     Open Interface: - If the interface logic is provided by oracle applications, it is called as Open Interface.

2.     Custom Interface: - If the interface logic needs to be developed by the implementation team, then it is called as Custom Interface.

What is Inter facing?

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

1.     Transfer Program

2.     Interface table and

3.     Import Program

Transfer Program: - If the source modules are developed in the 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.

·        It maps the columns of source table to the columns of interface table.

·        It performs Row level and Column level validations.

·        It transfers data from Source to Interface tables.

Interface Table:-

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. With the help of mandatory columns only the Import program will convert the records from source to destination.

Conditionally Required Columns:  The values of these columns are based on the values of Mandatory columns. For example, if you are converting foreign currency transactions to INR then it is 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 the client requirement.

Internal Processing Columns: - Status and Error message columns are called as Internal Processing columns. These are specific only to the Interface table. These columns are going to be used by the Import program to update the status and error message, it record fails its validation while importing from Interface table to destination table.

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 Interface table with one or more columns in the destination table.

·        It performs Row level and Column level validations.

·        It imports data from Interface tables to destination tables, it the records validated successfully. If it is validated successfully it will delete the records from interface table.

·        If the validation fails, the Import program will update the status and error message columns of interface table.

Interfaces v/s API

Interfaces are used to transfer the data from Legacy system to Oracle applications.

API’s are used to convert the data from one form to another form with in the oracle application module.

Interfaces and Conversion Process:-

In interfaces and conversions data can be feed in three ways.

1.     Data can be entered using the application screens.

2.     The data can be entered using the oracle’s open system interface.

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

Conversion 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 sql loader.

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

4.     Successful validation takes up the data into the interface tables.

5.     These validated data can be directed to base tables using 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 the UTL_file.fopen ().

4.     Open the Cursor.

5.     If any validations are there write the validations.

6.     Transfer the data into file using UTL_File.put_line ().

7.     Close the cursor.

8.     Close the file by using the UTL_file.fclose ().

9.     Register the package or procedure as concurrent program and submit from SRS window.

Differences between Conversion and Interface?

Conversion	Interface
1.It is a One-time process.	1.It is a post-production which is performed once before process. (Pre-production Process)
2. Data comes into Oracle applications	2. It is a one way process in which the integration of two systems.
3.Iterative Process	3 Scheduled and repetitive process.