Data Conversion / Migration / Interface

Today, as part of the ERP implementation,Data Conversion/Data Migration/Interface plays an important role.

As you all know, data can be entered in different ways:

1. Data can be entered using Oracle Applications screen.

2. Data can also be entered using an Oracle Open System Interface.

3. Also, data can be loaded using 3rd party tools such as Data Loader, etc.

First let me tell me you about,

1. What is Migration/Conversion and Interface?
2. What point of time they are used?

Hope, everybody who has worked in the implementation Project, should have come
across Data Migration / Data Conversion / Migration.

I am citing here definitions and some situations for those who didn’t have the opportunity to work in the implementation project.

What is Data Migration?
Data Migration can be defined as a process of moving very large volumes of data from
our clients existing system to new systems. Existing systems could be anything like IT applications, spreadsheets and standalone databases.

Say suppose, a new customer who uses existing IT application likes to switch over to
Oracle E-Business Suite, considering the performance and volume of data, we would go
in for Data Migration to port the existing data to ERP.

What is Data Conversion?
Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated.
Say suppose, an existing customer who uses older version of Oracle E-Business Suite(11i)likes to switch over to Oracle E-Business Suite(12i), considering the new features, we would go in for Data Conversion.

Few Points:

1. Frequency of Conversions are a one time Event.
2. Conversions are executed before Production.
3. Conversions are executed in Batch


What is Interface?
In common man's language, Interface is a communication channel by which you can
move data into Oracle Applications.
Say suppose, there are situations wherein we need to upload data frequently from an
external system like spreadsheets, etc., we would go in for Interface.

Few Points:

1. Frequency of Interface is On-going
2. Interface are executed during production
3. Interface are executed in Batch / real time


Let us get to know, how many types of Interfaces are there and what are they.
Normally in any system there are two types of Interfaces.

(i) Inbound Interface
(ii) Outbound Interface

Inbound Interface:
Inbound Interface is the one which allows data to get into oracle application from
outside is called inbound interface.

Types of Inbound Interfaces available:

1. Open interfaces

2. API's(Application Program Interface)

3. XML GATEWAY --Mainly used for automation transactions with third party systems

4. WEBADI--Used for uploading data from excel.

5. PLSQL Packages for Reading XML Data--Use this in case of importing non standard transactions

Custom Inbound Interface programs have following steps :

Datafile (Legacy data) => Temporary tables => Validate data => Load into Interface Tables => Run Concurrent Program to load the data into Apps Base tables

Datafile (Legacy data) => Temporary tables => Validate data => Run required API (application program interface) to load the data into Apps Base tables.

Datafile (Legacy data) => Prepare delimited data file => Create Control file with
instructions to load the data into Apps Base tables.

Run SQLLDR from unix or DOS:
sqlldr user/password@instance control=control_file.ctl data=data_file.dat
bad=bad_file.dat

Outbound Interface:

The one which allows data to get data from oracle applications to other systems is
called outbound interface.

Outbound Interface selects and transform an Application data by SQL statements
and creates output files using Oracle Reports, PL/SQL (FND_FILE utility) or
SQL*Plus scripts.

Types of Outbound Interfaces available:

1. Custom programs where we pull data to a csv file using UTL_FILE in the required format.
2. PLSQL Packages for generating XML

Okey, That’s it from me, now. Will continue to post some more on various topics. Please drop your comments if you feel something has to be added or modified. Appreciate your help on this.