Data Migration System


Data Migration System


Presently for data migration in oracle apps, raw data is migrated from flat files to oracle apps. The flat file may be an excel sheet provided by the client or any other text file. The existing process is tedious, complicated and not really user friendly.
To overcome these problems I have tried to develop a tool. With the help of this tool data migration does not require maintenance of scripts and the user has an opportunity wherein he may mention the source of the flat file and the destination where it needs to be migrated. This tool is called as ‘Data Migration System’. It is unlike other migration tools which has monolithic design and requires much more technical intervention.

INTRODUCTION
      Background
Conversion and loading process in oracle apps
Includes:-
1)   Migrating raw data to staging table by registering concurrent programs.
2)   Validating the staging table data and loading to the interface tables.
3)  Calling Oracle open interface program to load interface table data to base table.
This migration process is quite cumbersome and stringent. The migration systems are available in the market as separate utilities or these features are embedded with some application program. The migration system could be thought of as a user friendly system that allows the transfer of raw data from any flat file to oracle apps without any dependency.
Rationale behind building such system is to explore the oracle application framework.
The framework design is to build part of service oriented architecture. The technology chosen is JAVA because of its wide acceptance and flexibility on server side and with the help of this technology the design may be easily implemented.

      Relevance            
   The idea of implementation of data migration system is derived from traditional migration process in oracle apps.     
  Unlike the other migration tools which require maintaining of comma separated files or text file and additional scripting for migration, the data migration system provides a way for user to specify the spreadsheet i.e. excel sheet to migrate the data to oracle apps tables.
   The migration system will not affect the working of existing migration process in apps however the user is allowed to specify the package and/or procedure in order to validate the data in table. Migration system follows the same procedure as conventional migration process in apps by reducing task of maintaining separate data file and writing of control file scripts to read raw data from the file.     

technical Overview:
The main goal of data migration system is to provide user interface to upload spreadsheet rather than doing the same through backend using linux utilities or through oracle apps custom concurrent program.
The migration system follows the same process of conversion and loading. It inherits some features of application desktop integrator. The data migration system is another utility tool for migration. Even a novice user may migrate data using its simple user interface. It mainly includes following processes.

Initial process:
The migration system is similar to the SQL loader with certain additional facilities. At initial stage, the user is supposed to ask for an instance. This could be test instance, UAT instance, DEV instance, CRP instance or production. User can easily switch to another instance if migration process in the earlier has completed successfully. After specifying the instance, user will be required to enter database credentials for the same. Once the credentials are validated then user will be asked to select/mention the table name. The specified table name would be considered as the destination for migration. If table mentioned does not exist in database then the migration system provides option to the user for creating the table.

Moving data from flat file to oracle staging table:
Here user will be asked to select the columns of the staging table. User may shuttle the required number of columns in trailing list. Skipper may be mentioned, if the user wants to skip certain rows from spreadsheet and specify the file to upload the data to staging table. Possible file types supported by migration system are dat(data file), xls(excel spreadsheet), csv(comma separated file), txt(text file). As of now the delimiter for the text file is considered as comma. Idly any valid delimiter character may be specified before uploading the txt/dat file. Comma separated file containing commas, single and double quotes will be successfully uploaded. Workbook containing multiple spreadsheets will also be successfully uploaded. For e.g. If there is a requirement of PO data migration and client has provided the data in 3 different excel sheets indicating header, lines and distributions information. User can mention staging table information and specify the file straightway to upload. If user has specified correct source and destination information then message will be populated indicating file has been uploaded, error log table is maintained for recording any error occurred during/after uploading. Advantage of this migration system is that there is no need of querying the database to ensure insertion in table as the migrated data in the table may be viewed in the same page after successful file upload.

Moving from staging to Interface table(S) OIT:
To validate staging table data, the migration system allows user to specify package and/or procedure to carry out the same. The specified package must exist in oracle apps. Here the Benefit of data migration system is reusability of existing package(s) like conventional migration process. No need of restructuring the existing package and/or procedure.

Finally to Oracle apps base table:
User will ask for Oracle open interface program. This functionality is alike webADI. User will specify the parameters needed for running concurrent program to move data from interface tables to apps base tables.


FLow diagram:




Architecture



BENEFITS:
Ø  Simple and efficient system for transferring data from flat file to oracle database.
Ø  No need of maintaining control file and CSV file.
Ø  No need of manually transfer data files to server location. (No need of running the FTP utility.)
Ø  No need of remembering huge commands.
Ø  Multiple spreadsheets under single workbook can be successfully uploaded.
Ø  Time saving process.
Ø  Directly mention excel file to copy data to oracle database.
Ø  Allows user to create tables dynamically.
Ø  On successful migration, it also has the facility of viewing uploaded data rather than querying the database.


REFERENCES:
Books:
1. Anil passi, Vladimir Ajvaz, Oracle E-Business suite development and  extensibility handbook
2. Data Migration in Oracle E-Business Suite – presentation by Accenture.
3. Conversions, Interfaces and Oracle Applications: Data Movement throughout the Oracle Applications Life Cycle
4. Oracle application framework – developer guide version 12.1.1
5. Oracle_ Applications Developer’s Guide
6. Austin Davis, Accelerate oracle configurator data migration for E-business suite R12

Sites:
  1. http://erpschools.com/articles/interfaces-and-conversions
  2. http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21675/appmgr_overview.htm
  3. http://apps2fusion.com/at/50-pb/255-data-conversion-strategies-in-oracle-e-business-suite-implementation
  4. http://apps2fusion.com/at/pb/255-data-conversion-strategies-in-oracle-e-business-suite-implementation?showall=1
  5. oracle.anilpassi.com/oa-framework-tutorial-01.html
  6. oracle.anilpassi.com/oa-framework-tutorials-training-2.html
  7. mukx.blogspot.com/oa-framework

Screen shots:









Comments

  1. Unknown3 March 2017 at 02:14

    Hi
    Very good article on data migration,I am new to this site but the way you described the article was impressive .Keep posting the articles like this .
    Thank you.
    oracle R12 training

    ReplyDelete
  2. oraclefusion4 March 2017 at 01:59

    Hi
    This is good article about the data migration and good points were stated in the blog
    Oracle Fusion

    ReplyDelete
  3. oracleSCM6 March 2017 at 21:34

    HI,

    This is good article .please give me this type of articles.Use full for
    all users.

    ReplyDelete
  4. oracle procurement6 March 2017 at 23:19

    Thank you for sharing good facts. it is a great informative post. Your article is too properly.
    Hold posting these articles continuously.
    for more information please check the site
    online trainings

    ReplyDelete
  5. ORACLE FUSION PROCUREMENT 14 March 2017 at 21:32

    Hi,

    This is very good blog.Very interesting and valuable.
    oracle fusion SCM on line training

    ReplyDelete
  6. Oracle Fusion18 April 2017 at 03:31

    This post is very informative for everybody. I would like to appreciate your work for good accuracy and got informative knowledge from here.
    Oracle Financials Online Training

    ReplyDelete

Post a Comment

Popular posts from this blog

Search on Master-detail table in OAF

Search on Master-detail table in OAF Hello friends, Oracle application framework provides me another opportunity to work on and to get more acquainted with. So here I am eager to share another short article on OAF.  Just finished a project on OAF and came across various fact and findings about the framework. You guys may have come across master – detail relationship in OAF and ‘how-to’ behind that as well.  I have recently read articles on how to insert a row into child table. Well this article is quite a different one. While developing an application, there might be a requirement wherein we need to work on master detail relationship.  In my case, the requirement was to develop the search page having result table including master and detail table records.  The user may enter any search criteria that may fall under master table and/or detail table. Depending upon the search performed by the user, the master and detail table records must be populated. For...
Read more

Implement 3-level master detail hierarchy using table region in OA framework.

Image
Implement 3-level master detail hierarchy using table region in OA framework. After depth searching on various oracle forums, I came to know that multi-level hierarchy in advance table is not achievable by easy means. There could be at the most one detail region inside an advance table.  If the business requirement is to display records in master->detail->detail format then advance table won’t be effective. For e.g. If the requirement is to show item transaction details year wise and when user clicks on show-hide region against any record will navigate to the month wise details and when user clicks on show-hide region against any record will navigate to the day wise details such hierarchy is possible, The OA framework provides the table region to achieve the same functionality. Procedure: Create view object for corresponding master – detail – detail regions. Define view link for 1 st level of master-detail hierarchy. Define view link for 2 nd leve...
Read more

Shuttle Control in OA Framework

Image
Shuttle Control in OA Framework While working on various business scenarios, we may come across multiple situations wherein we need to select specific values from the list and require moving to another list which will be considered as final list.  OA framework provides shuttle control to fulfill the same. For e.g.  In Jdeveloper, while attaching view object to the application module we need to select correct view object from view object definition list in left side panel and move it to right side panel/list so as to include view object definition into the  application module. Well, to achieve such operations Oracle application framework provides such option by means of shuttle control.  Shuttle Control allows user to move values from one list region to other. Design:  Select page region and right click -> new region set region style property of this region to shuttle.  After setting region style you will get shuttle region with default leading l...
Read more