What is Interface and Interface process

Interface is program used to transfer the data from flat file to database table or from data base table to flat file. Two types of interfaces:

·      Outbound Interface

·      Inbound Interface

Outbound Interface:

Outbound interface is used to transfer the data from oracle apps base table to legacy system flat file. We will develop a concurrent program, which is plsql procedure. We will use UTL_FILE package to generate the flat file.

Inbound Interface:

Inbound interface is used to upload the data from legacy system flat file to oracle apps base table. For this we will use SQL LOADER program and plsql program.

Interface is post implementation loads which will be helpful to import the data after go live.

The object that we have developed for this interface will be registered in the oracle applications.

Types of Interfaces: There are two types of interfaces.

1.     Open Interface.
2.     Custom Interface.

Open Interface: Open interface is nothing but it provides interface tables and import programs to import data into base tables.

Customer Interface: Whenever oracle has not provided any import programs or API to import data into base tables we call it as a custom interface.

Interface Process:

1.     Based on the flat file we will develop the control file and insert the data into staging table.

Note: Place the control file in the bin directory of your custom application directory.

2.     Register that in oracle 11i applications by taking execution method as SQL * LOADER we develop the packages which will be having procedures like validate the staging table, moving the validated data to the interface tables, run import program after that register that package in oracle 11i applications

Note: While transferring the data into base tables we pass three extra cols those are given below.

Ø  Process_flag (Number)

Ø  Transaction_type (Varchar2)

Ø  Set_process_id

Process_flag: To check the status of the row which we fetched into the table. We pass different values that indicate the status of the row those are given below.

Ø  1 à To be process

Ø  2 à Import Success

Ø  3 à Error

Ø  4 à Validation Under Process

Ø  7 à Validation Successful.

Transaction_type: This is used to identify which transaction was done those are CREATE, UDATE.

Set_process_id: It is used to split the no. of records to use memory correctly without getting any SGA MEMORY error.