- OracleApps Epicenter - http://www.oracleappshub.com -

Data Conversion,Migration and interface ..Why important

Do you know how many ways; we can enter the data into oracle application. Most of us can guess three different ways as:

But those who works in some complex business environment may figure out some of more like:

And there are many more, but most of these are used for master data, and few cases for transaction data via Open interface if available.

Importance of data conversion/migration and interfaces within any ERP implementation project can't be ignored. Since ERP mostly deals with data which finally leads into Information , thus it is equally important to understand the aspect how "data" is important in any ERP system specailly in implementation phase, no matter how simple and unified operation is. Since I been involved in some big transformation oracle apps project thus It is a absolute a good cause to share some information about integration touch point, conversion/migration and interface development to someone who is very new to ERP world as well as Oracle application.

Let's start with some common situation, we have three cases,

  1. The Customer is running there some home grown IT application which cater most of the company need. Now management has decided to go for any ERP solutions, then the question what will happen for data which is already in the existing application?
  2. Another situation is already using ERP
    a. They want to upgrade to higher version…presuming the structure of some table get changed? Lets say 10.7 to 11i
    b. The company is acquired or merged with some other company, and the whole data need to move into the parent or child company .
    c. They want to enable some additional modules within existing application.
  3. There are few data interacting with both the cases irrespective of database technology for where data is coming and going based out of need.

The answer of the 1 is data migration and 2 is more pronounced as data conversion where as thirds is popularly known as Interface. The ways these are working haven't much difference but it is more important to understand definition and need. I never found any big difference between migration/conversion unless there is huge transformation of data, the only things figure out is conversion might required some less steps to perform, as assumption is that set up related stuff been already been taken care before execution of activity.

Let's understand like this: Data Migration as a process of moving required (and most often very large) volumes of data from our clients' existing systems to new systems. Existing systems can be anything from custom-built IT infrastructures to spreadsheets and standalone databases. 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.

Lets take a deep drive to understand better:

Why conversion/Migration is more important in ERP?

Before Go-Live in the production environment the required master data, the open transaction data and the historical transaction data need to be imported from the old legacy applications to Oracle Applications. Since data structure and data design in legacy systems are different from those of Oracle Applications, data need to be messaged/ converted satisfying the business rules to suite the Oracle requirement. Initial data can be migrated by any other means as discussed above depending upon cetain paramater like Volumn, usage, complexity , business rule etc..

How we Define Data Conversion

What Is An Interface then

Does the conversion/migration/interface have Life Cycle

Yes, they have, because they have a significant efforts required in development and design and implementation

Conversion is assumed as one time activity but never looks like small activity which can be performed with couple of days.

How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

  • An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
  • A typical inbound interface would follow these steps:
    1. Extract data from legacy system into a flat file.
    2. Use SQL*Loader or equivalent tool to upload information into a temporary table.
    3. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
    4. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.

Outbound Interfaces

o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
- Write a PL/SQL program to extract data from Oracle base tables into a flat file.
- Use a custom program to read that data and post it into the legacy system

Do we have some other standard way to do interface?

What Is An Open Interface Table (OIT)?

Oracle Interface Program

Examples of standard import programs:

  • GL: Journal Import
  • AP: Payables Open Interface
  • AR: Customer Interface
  • INV : Item Import
  • AR - Autoinvoice

Ok, thats is all about Conversion and Interfaces briefing. I will write some more for Tools used for Conversion/Interface and will discuss some granular details about a conversion/migration project and share some information about how and where AIM's documents fits into conversion and Migration projects. So watch out this space for some more stuff for conversions.. Till than..your comment and requset you to share some information relaeted to these areas.


66 Comments (Open | Close)

66 Comments To "Data Conversion,Migration and interface ..Why important"

#1 Comment By jayakrishna On June 12, 2009 @ 6:51 am

hi there is an any API for journal import in R12 applications
please tell me any of the api to import

#2 Comment By sam On August 9, 2009 @ 6:06 pm

Hello Anand,

Many thanks for posting such useful notes on the website. I am a regular visitor.

Data conversion in an implementation can face 2 scenarios:

1) Fresh instance available for implementation and a business is going to go-live on the instance

2) An instance which is already used (other Operating Units are setup etc). In such a case, probably we might need to look at what are the fields already used (for example attributes in master items table for inventory)

With the above, we might need to work accordingly to prepare the data conversion templates. Do you have any help document on how to go about preparing a data conversion template for various entities (for example, items, open sales orders)

You can write to me on [1]

many thanks again…

#3 Comment By Triveni On August 21, 2009 @ 12:51 am

Hi Sanjith ,

please let me know about the rules (Specified below) while preparing the CV 40 -Functional Document as part of Coversions (Migration).

processing Rules
Translation Rules
Filter Rules
Foreign Key Rules
Derivation Rules
Default value Rules

In detail the meaning of that Rules & existance ,For more understanding take an ex: Ar Open Invoices conversion , in this perspect how to map this task (Ar Open Invoices) to the above mentioned Rules.

Please forward any document available on conversions & Rules.

Hope iam clear , Appreciate in advance all your Responses .
what is quick codes ?
How to identify the quick codes please map in perspective of AR Open Invoices conversion?
How to identify the Foreign key validations for a specific conversion take any other Example & map .

Please let us know more about the quick code validation .

Please replu to [2]


#4 Comment By Kavita On March 10, 2010 @ 9:53 am


The information provided here is really helpfull for professionals who are new in this field.
It really helps me to understand the basics of Data migration & conversion process in Oracle apps.
Its just what I was looking for.

Thanks a lot for your efforts.

#5 Comment By Salma On June 15, 2010 @ 4:06 am

Dear Sanjith,

Do you have a complete scope of work for ERP Data preparation/cleansing towards ORACLE full suite?

Many thanks. Salma

#6 Comment By Sanjit Anand On June 15, 2010 @ 4:14 am


CV.010 : Data migration Stretegy Document is good to have in the begining, thats have details for entire data prep as well as cleaning activity.

If you are looking for a sample i can share with you.


#7 Comment By Salma On June 15, 2010 @ 11:44 pm

Yes please,

I would like to have a look at the sample.

Many thanks, Salma

#8 Comment By maruthireddy On July 22, 2010 @ 7:13 am

Plz send me a sample query of supplier conversion

#9 Comment By Brenden On October 20, 2010 @ 11:25 pm


Great work!
Can you share CV.010 : Data migration Strategy Document with me too please.
Much appreciated.


#10 Comment By Srini On October 27, 2010 @ 10:13 am


I would really appreciate your work, thanks for sharing info.

i have one question

Suppose i want to load the flat file information to header level and line level, in this case will i get one flat file or 2 flat files

i am very new this oracle apps so pls dont hesitate to answer small questions

#11 Comment By Srini On October 27, 2010 @ 10:15 am

One more Question is

how to do validation before inserting into interface table.

please tell with one example (with column validation)

thanks in advance


#12 Comment By Sudhakar On November 9, 2010 @ 3:32 am


Article is very good for begginers like me. Keep it continue with the same tempo for us.


#13 Comment By srinu babu On November 11, 2010 @ 10:21 am

hi thanx for the exclusive information…plz send is there any workflow documets with full information


#14 Comment By sujith On November 17, 2011 @ 3:11 am

Article is very good for begginers like me. I wish to know more about different types of conversions like gl conversion, item conversion etc. please add more topics on conversion

#15 Comment By Kashif Rasheed On January 18, 2012 @ 2:36 am


I need your help if any one can.

Actually we wants to migrate data in Oracle eBs:

1- GL Opening Balances
2- Customer Master with opening balances
3- Supplier Master with opening balances
4 – Fixed Asstes Master with opening balances
5- Employee Master with Salary Details

Can anyone provide me the excel templetes for those files, I am really appreciated and thankful to you.

My email address is [4]

Best regards,
Kashif Rasheed

#16 Comment By Javed khan On October 13, 2012 @ 7:44 am

Thanks Sanjit for providing such an important information related to interfaces.

I have one question can you please help me to clarify my doubt…

What i have understood from this article that interfaces are meant to transfer or synchronizing the data from one system to other system..

But when we synchronize the data within oracle apps from one module to other (say OM to install base or OM to AR) then this kind of data synchronization can also be considered as interfacing …


when data transfermation done between oracle apps with some other legacy system (say Geneva billing) then only it can be said as interfacing .

Thanks and Regards,
Javed Khan