Data Migration/Conversion in Oracle EBS
What is Migration & Conversion
Migration :
Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data. Process of Migrating of data: Identify the data to be imported to new system (Business requirement). Extract the data into flat file/Staging table Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Conversion :
Conversion of data means translating the data to suite target system (data should be formatted according to target system ) and then move the translated data using Interface Programs/APIs.
Identify the data to be imported to new system (Business requirement). Extract into flat file/Staging table Translate/Convert/Format the data Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables If using API, fetch the data, validate it and then call API to import the data
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Life Cycle of Migration/Conversion
Analysis
Set Ups
Master Data Conversion
Transactional Data Conversion
Functional Testing
Go Live
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Standard ways to do Migration/Conversion
1) Open Interface Table :
The Open interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
Interface Tables Ex: GL_INTERFACE AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE RA_CUSTOMERS_INTERFACE MTL_SYSTEM_ITEMS_INTERFACE Standard Import Conc Progs GL: Journal Import AP: Payables Open Interface AR: Customer Interface INV : Item Import
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
2) APIs
API Definition which includes purpose, input parameter, output parameter, in/out parameter as well as default values for parameter Package: These are the bundle of logically related types, variable , cursor and subprogram Packages consist of 2 parts: Specification - this is where you declare the types , constant , variables , exception, cursor and subprogram Body: This will define code of the subprogram
Procedure : These are subprograms without a return value ,sometime use 'OUT' parameter to return values
Function : These are subprograms with a return value.
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Standard Conventions of API
ALL public package that expose APIs are named as and follow this convention: <product>_<xxxxx>_PUB ending with 'PUB or API Ex: PA_PROJECT_PUB HR_EMPLOYEE_API Oracle convention File are kept separately each for specification and body as its kept as <Product code>XXXXXXS.pls and <Product code>XXXXXXB.pls for body
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
How to start with API Integration
Identify the API
Identify the API Parameters
Map APIs Rec Table type & Other parameters Handle the Exception & Print the Status
Develop the Code & Initialize Apps Info
Call the API
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Important Tips and Techniques when using APIs
i) Set P_COMMIT to TRUE only when implicit commit is required.
ii) Print X_RETURN_STATUS to the user's output. These are values return once Program get completed. S - Success E - Error U -Unexpected Error iii) Need to initialized API by passing all mandatory parameter. iv) All error message are printed in case of error
v) Debugging should be turned On, there are different level which you can set as (P_DEBUG_LEVEL :=0|1|2|3)
vi) These convention for API's works for all sub application products like finance , Manufacturing , CRM and HRMS.
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
Migration/Conversion Process
Legacy data insert program (SQL * Loader/DB Links/External Tables) Custom Staging Tables
Custom PL/SQL Package for Data Validations Oracle Open Interface Oracle Public APIs to Tables Oracle Base Tables Oracle Open Interface Programs
Oracle Apps Base Tables
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
10
Presenter details Venkata Gireesh P venkatg@kpmg.com gireesh.p.v@gmail.com
2006 KPMG, an Indian partnership and a member firm of the KPMG network of independent member firms affiliated with KPMG International, a Swiss cooperative. All rights reserved.
11
