<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow Miscellaneous Technology arrow Migration program in Apps. Migrate Customers
This website has now moved to http://apps2fusion.com





Migration program in Apps. Migrate Customers | Print |  E-mail
Written by Anil Passi   
Monday, 13 November 2006
This article explains the steps to write programs for data migration in Oracle Apps.

Question : Why this article?
Answer : Because in every Oracle ERP implementation you have some level of data migration activity. I will try to explain the steps, followed by a real life example of data migration.

Question : How to do data migration?
Answer :
A. Prepare the source data
B. Design your staging tables and write code
C. Test Test Test....

Question : What is involved in the preparation stage of data migration in oracle apps?
Answer:  For the Preparation stage, following must be done.
1. Understand the structure of the data being imported and also its business purpose.
2. Fully understand where the data will end up residing into Oracle Apps.
3 Find out if open interfaces or APIs exist in Oracle Apps to facilitate loading the required data.
4. Ensure that lookup codes or setup in Oracle apps support the values that are coming from source system.
5. Think about how the errors will be reported and managed.
6. Also think about how the transactions that fail migration will be re-tried. You may decide to knock of a simple screen if high volume of transactions needs user intervention for cleansing.

Question: And how about the development stage during migration?
Answer: Do the following
1. Design the stages in which data will flow.
Traditionally, your legacy/source data will be loaded into some temporary(staging) tables in a raw(as is) fomat/strucutre . Next you will translate the data into the physical structure that is similar to structure of Oracle Apps table/API. And then you will call the API or populate the Open Interface tables.
2. For doing the above, you will first write one-off scripts to create those staging tables and load data into them using SQL*Loader or some other methodology. If your source system is Oracle too, then I suggest you create a db link to pull the data into your staging tables.
All the steps pertaining the transformation can be done via a pl/sql concurrent Program(unless you are using tools like Warehouse builder)
3. Run the concurrent program that you have would written for transformation of data. This will either populate your Open Interface Tables or it will perform migration if APIs are being used.
(Again all this can be done via tools too, but here I am talking about a small migration activity, hence ignore the tool talk)
4. Ensure that errors are logged into some error logging table.
If the data errors in Oracle's open interface tables, then you could have a database view that does a union on your error table and also oracle's error logging table.

Question: How about testing?
Answer: You will figure out the following at this stage, from the records that error
1. Missing mapping codes
2. Missing setups in oracle apps.
Testing will most likely be repetitive, until you are able to have a migration run which leaves behind the records that are very low in volume or non-existent.

Question : If I get a comma delimited file, how will I load that into tables?
Answer : You can use a Sql*Loader, or a java program with a csv parser or a file based table approach.

Question : In oracle apps during migration, do we usually receive xml data file?
Answer : Keep in mind that you usually migrate data from mainframe or standalone systems which now stand outdated.
Such systems usually produce comma delimited or tab delimited file.

Question : Ok, once the data from source system has been loaded using sql*loader, what next?
Answer: The data model of the source data may or may not comply with the data model in oracle apps. Hence you need a transformation step in most cases. This is explained below with an example of TCA API to migrate customers/parties.

Question : Should I not ask the legacy system people to transform data as per our requirements?
Answer : No, don't bother doing so, for below reasons :-
A. Techies of legacy system will not be happy that their system is now being made redundant. Hence don't expect much value addition from them.
B. There is a possibility that in an attempt to transform data to Oracle's data structure, they might induce faults/bugs.
If the transformation bugs are encountered at your end in apps, you can fix them yourself. However if legacy team does transformation for you, then you become dependent  on them for bug fixes. Eventually tired of waiting on them, you might end up doing transformation yourself anyway.
C. If your design for transformation changes, you should not be dependent upon the legacy system...just fix it yourself at your own end in apps.

Question : If we end up using interface api's in apps for data migration, then where lies the difference between migration and interfaces?
Answer : Migration is a one-off activity, even though it uses the same sets of tables/API's as interfaces do.

Question : Enough explanation, now give me an example.
Answer : Let’s assume you get a task to migrate customers from legacy system into TCA (or call it AR - Receivables for simplicity).
Your file from source is:-
lcust.dat
1000,GE, GE Capital,1000-1
1001,Cisco,Cisco Routers,1001-1
1002,Barclays,Barclays Investments,1001-1
1003,Barclays,Barclays Mortgages,1001-2

The format of data is CustomerId,Cust parent name,customer operating company name, operating company id

Step 1. FTP this file to your database server and run sql*loader to load the file into a table named xx_legacy_cust, this table will have four columns, one column for each file in source.
Step 2. Transform this data...
For this we create two tables...
XX_TRNSFRM_PARTY
   --parent_cust_Id
   --parent_cust_name
XX_TRNSFRM _CUST_ACCOUNT
   --parent_cust_Id
   --operating_cust_Id
   --operating_cust_name
Now, you can write a pl/sql program to split data from table xx_legacy_cust into the two tables listed above.

Step 3. Now write the pl/sql program to migrate this data into Oracle.
Create or replace procedure xx_migrate_parties(errbuff out varchar2,retcode out varchar2) is
Begin
FOR p_party_rec in (select * from XX_TRNSFRM_PARTY )
LOOP
 --call api to create party
Hz_Party_Site_V2pub.create_party();

    For p_party_rec in (select * from XX_TRNSFRM _CUST_ACCOUNT where parent_cust_Id = p_party_rec.parent_cust-id)
    loop
    --now create a customer account
         hz_cust_account_v2pub.create_cust_account(..parameters…
                                             l_return_status,
                                             l_msg_count,
                                             l_msg_data
        );

      IF l_msg_count > 1 THEN
         FOR i IN 1 .. l_msg_count
         LOOP
            Fnd_File.put_line (Fnd_File.LOG, i || '. ' || SUBSTR (Fnd_Msg_Pub.get (p_encoded      => Fnd_Api.g_false ), 1, 255 ) );
         END LOOP;
     END IF;

    END LOOP ;
END LOOP ;
END  ;

Now some notes:-
1. No two data migrations are exactly the same, hence I have given a pseudo code, and not th actual code.
2. In some cases the quality of the source data can be so bad that you may need a third party to cleanse the data before you run migration.
3. The API's used for data migration and for developing open interfaces are the same.


Comments (17)add
...
written by jaihind , November 14, 2006
Hi
This is really good stuff for bigeners. I have one question. Iam a bit confused with migration, and interface and where to use APIs. Like I know migration is one time activity in which legacy data is put into staging tabels then the data are transferred to interface tables after validation( I dont know whether this step is right or wrong) then once the data is there in interface tanles we use open interfaces to transfer the data from interface to base tables Is it right or the data is transferred to directly base tables without populating data in interface tables and I also want to know do we really make all this into single pl/sql and assign it to some concurrent request ...And in the code can you please explain some clearly what errbuf will do and also where we record errors during pl/sql , where to see the errors. It will be very helpful for me if send me clarification for this...Thanking you
Jaihind
...
written by nasir , November 14, 2006
good for even starters...
...
written by nasir , November 14, 2006
good for even starters...
...
written by jaihind , November 14, 2006
Hi
This is really good stuff for bigeners. I have one question. Iam a bit confused with migration, and interface and where to use APIs. Like I know migration is one time activity in which legacy data is put into staging tabels then the data are transferred to interface tables after validation( I dont know whether this step is right or wrong) then once the data is there in interface tanles we use open interfaces to transfer the data from interface to base tables Is it right or the data is transferred to directly base tables without populating data in interface tables and I also want to know do we really make all this into single pl/sql and assign it to some concurrent request ...And in the code can you please explain some clearly what errbuf will do and also where we record errors during pl/sql , where to see the errors. It will be very helpful for me if send me clarification for this...Thanking you
Jaihind
...
written by nasir , November 14, 2006
good for even starters...
...
written by nasir , November 14, 2006
good for even starters...
...
written by Vamsi , December 03, 2006
Hi

could you please tell me what are extensions in oracle apps and what is the difference between the extensions and enhancements
...
written by Vamsi , December 03, 2006
Hi

could you please tell me what are extensions in oracle apps and what is the difference between the extensions and enhancements
...
written by BALAJI , December 26, 2006
Anil,
ur article is realy good,but u need to clarify one doubt
on how to add flex field data in report ,it raising an error while creating a query with bind variable in query for eg.
SELECT &P_FLEXDATA C_FLEXDATA,
CHART_OF_ACCOUNTS_ID C_NUM
FROM CODE_COMBINATIONS_TABLE

error ora-00936
...
written by BALAJI , December 26, 2006
Anil,
ur article is realy good,but u need to clarify one doubt
on how to add flex field data in report ,it raising an error while creating a query with bind variable in query for eg.
SELECT &P_FLEXDATA C_FLEXDATA,
CHART_OF_ACCOUNTS_ID C_NUM
FROM CODE_COMBINATIONS_TABLE

error ora-00936
...
written by raj , January 25, 2007
Hi Anil..

Can u give us some common validations we do on all modules.also how can we find Mandatory columns in oracle applications to migrate data from legacy system to oracle apps

Thanks
raj
...
written by Anil Passi , January 27, 2007
.
.
.
Hi Raj,

I suggest you look at this from case by case basis . There is no common validation accross all migrations

thanks
anil
...
written by sachin suvarna , March 21, 2007
Hi anil!
What is the difference between open interface and standard interface. Is it possible for you to list the steps that you use during open interfaces.

Regards
Sachin Suvarna
...
written by Prakash Jid , June 25, 2007
Hi Anil,

This is nice and exellent Guides.
Plz tell me which API is used for the Supliers
PO data conversion from Legasy.

Thanks,

Regards Prakash Jid
...
written by Prashant , September 24, 2007
Hello Anil,
The stuff on Data Migration is really good. But at the moment i am stuck up with one basic problem - Identifying the API's that will do the Data Migration for Oracle PLM package. Need your urgent response on where the documentation can be found?

Regards,
Prashant
on-hand quantity conversion
written by nag daka , December 18, 2007
I have a requirement regarding data conversion.I am pretty much new to data conversion,could any body let me know how to start it.They provided me one script which is fetching data from database.I need to update org i.d.They have some newly defined org I.ds.I came to know the api what i need to consider is 'inventory transaction open interface'.could any body get back me quickly as it is very urgent requirement....

How can I know which are the mandatory fields and what are the default values in the customer interface using API in R12.
written by Bhaumik Bhatt , February 25, 2008
Hi all,

I would like to know the mandatory and non mandatory fields while doing customer interface using API's. Also would like to know the default values for the parameter which are non mandatory.

Please let me know if there is any document which provide me teh details of the above.

If i would like to do this using Package then what will be the sequence for creating customers.

Thanks & Regards

You must be logged in to a comment. Please register if you do not have an account yet.

busy