Oracle ERP User Network Collaborate with Professionals working on Oracle ERP(Oracle Apps) including EBusiness Suite (R12, 11i), Fusion, Peoplesoft, JDE, Siebel, Hyperion, OBIEE, SOA etc.

In this article I will be explaining the general steps involved in any Conversion/Data Migration of Oracle Projects module.

At the end of this article, you would have learned:

* Stages in Oracle Projects Conversion.
* How to setup the Oracle Projects module for the conversion/Data Migration.
* Options for the Load (flat file, csv, or direct Loads).
* Oracle Projects AMG APIs needed to perform the Conversion.
* Testing the Conversion Process.
* Verifying the Conversion Process.

Scenario:

Company 'XYZ' is using a Project Management and Accounting Software for years long. The Management has decided to move from their existing system to Oracle Projects module because of its vast functionality and integration with Other financial modules.

How to deal with it ?

Now the question arises: What data to migrate from the legacy system to Oracle Projects?. Well, it depends upon the type of projects.

If the Projects are used for Internal Administration and tracking of costs, you may want to migrate the existing projects, tasks(the work break down structure), Cost Budgets, Cost (Timecards, Employee Expenses, Miscellaneous Expenses) etc.

If the Projects are used for billing the Clients for the work done (Typical Contract Projects), then you may want to Revenue, Agreements(Contracts), Revenue budgets and Invoices in addition to the above data.

Once the decision is made to which data to migrate, then the next step would be setting up the Oracle Projects for the conversion purpose, which we will see in detail sooner. Once the System has been setup, the technical elements(programs, concurrent processes etc) have to be created in order to migrate the data from Legacy System to Oracle Projects.

Stages in Oracle Projects Conversion

1. The First Stage is to obtain the data from the Legacy System which needs to be migrated to Oracle Projects.
2. The Second Stage will be most crucial step in the process which is to massage the data according to the Oracle Projects Conversion Interface(Programs built using AMG APIs). This Step is indeed time consuming, manual labor intensive to massage and rectify the errors etc. But completing this step successfully pays dividends in the consecutive processes / Stages.
3. The Third Stage is uploading the data obtained from legacy systems into the Staging Area(Staging Tables created to hold the data temporarily till it gets migrated into Oracle Projects). Once the data is uploaded to the Staging tables, the programs built for migration(We will see how to build these programs in detail) will validate the Staging Table data to confirm that it is in compliance with the Projects Conversion Program( The AMG APIs used in the programs indeed needs data in certain format, also the data should be validated against the Oracle Projects Setup. For instance, when migrating the cost or hours from legacy to Projects, we might need to validate if the expenditure type is already setup in Oracle Projects, if the expenditure type is not setup, the program/APIs will throw an error. So it is always better to capture these kind of scenarios in the Validation Step of the Migration.

The Second Stage and Third Stages are repetitive until you get the Valid data from the legacy system which can be migrated into Oracle Projects without any errors or issues.

1. The Fourth Stage is the actual migration process which will migrate the data from the Staging Tables to the Oracle Projects Base tables. Once this step is done, the projects, tasks and other data are available in Oracle Projects for use.

Before going through the stages, we will look at some of the basic setups that needs to be done in Oracle Projects.

Oracle Projects Setup For Conversion

· Product Code:

The Product Code needs to be setup in Oracle Projects in the AMG Gateway - Source Products Form in the Oracle Projects Implementation Super User Responsibility. This setup is mandatory since this product code needs to be passed when using the Oracle Projects AMG APIs.

· Project Types and Project Templates:

The project types and project templates for conversion projects need to be setup up. This is a mandatory setup since while migrating projects we need to tell the APIs which project template/type the projects use.

For Contract Projects, setup the Contract Project Type Template. For administrative or internal projects, setup the Indirect Project Type templates.

If you are migrating Cost and Revenue Budgets, then the Plan Types need to be attached to the templates in order to create the budgets for the migrated projects.

· Implementation Option Setup:

Project Numbering: This implementation option is by default set to 'Automatic' which means when creating projects in Oracle Projects, the project number is automatically derived and users are not required to provide any project numbers. This option is best suitable when creating projects in Oracle Projects. But when migrating the projects from the third party systems, there is an option to migrate the projects with the same project number as in the legacy system. This is not mandatory but is recommended since it will be easy to refer back the projects in the source system using the project numbers.

In order to pass the project number to the Migration program, this implementation option needs to be setup to 'Manual'. Once the migration is done, this setup can be reverted back to 'Automatic'.

· Setup Transaction Source:

The Transaction Source needs to be setup in Oracle Projects in the Transaction Sources form in Oracle Projects Implementation Super User Responsibility. This is a mandatory setup for the Costs/hours migration from the legacy system to Oracle Projects. We need to tell the migration API's what is the source system and how the data is handled when it is imported to Oracle Projects.

· Setup Expenditure Types:

Expenditure Types are needed to categorize the cost/hours when it is imported to Oracle Projects. This is a mandatory setup for Cost/hours migration. We need to tell the system which expenditure type the cost/hour belongs to.

· Setup Employee Cost Rates:

Setting up cost rates for employees is not mandatory. But if you need to cost the hours that are migrated in the system, the labor cost distribution process in Oracle Projects do need the rates setup in order to calculate the costs.

But if you are migrating the costs directly from the legacy instead of hours then this step is not needed. But ideally the cost rates are required in a general production scenario wherein the employees/contractors enter their timecards.

You can setup job rate schedule, employee level rate schedule or employee level overrides. Alternatively, the costing client extension can be setup to calculate the cost according to the business scenario.

Refer to the Oracle Projects User Guide for how to setup the employee cost rates.

First Stage: Obtain Data from Legacy System

The first stage deals with obtaining the data from the legacy system in the desired format. The data can be obtained in the form of flat text file or comma separated file csv, tab delimited file or file with any delimiters. Generally tab delimited files are recommended since comma separated files behave strange when there is a comma in the data itself.

If there is a database link created between the Source Legacy database and the Oracle Projects Database then the data can be obtained directly using the select statements against the Source DB from within the Oracle Projects DB. But this method is not preferred as it is more performance intensive when it comes to selecting large data over the network.

For Projects Migration, generally 2 files are obtained. One file for Projects Data and the other file for Tasks Data.

For Transaction Migration, single file is enough with all the cost/hours data.

For Cost/Revenue Budget migration, single file is enough with all the Budgets Data.

Create SQL Loader concurrent program which will upload the obtained data into the Oracle Staging Tables.

Read more here

Download the full article here.Oracle_Projects_Conversion.pdf

Sathish Raju

For more oracle projects articles: www.projectsaccounting.com