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.
- Mandatory Columns.
- Conditionally Required Columns.
- Optional Columns.
- Internal Processing 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.
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.
Interface Vs. Application Program Interface (API):
Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.