EBS HISTORICAL DATA CONVERSION

Technical Approach - Holistic View

Synopsis

One of the customers has decided to upgrade/re-implement Oracle eBusiness Suite ERP from present 12.0.6 to 12.1.3. As part of this upgrade/re-implementation customer wants to convert Open, Partially closed and Closed transactions from old system to new system in order to meet the following requirements:

• Reporting &
• Auditing

It has also been mentioned that new COA and Item structure will be created to meet the existing business requirements.

Before we discuss about the different approaches to fulfill these, we need to understand that Oracle ERP is an integrated system of multiple modular applications. All new implementation projects need to address the aspect of data conversions.

Oracle EBS

With EBS, it is highly recommended that closed transactions never be converted in the sub-modules, though it is technically feasible. The APIs, oracle has written were designed for open transactions and do not build all the links between transactional data, such as opens invoices, and their resolution, the payment of the invoices. If an organization wants to record the past two years of historical purchasing data and is running Purchasing, Receiving and Payables, this will require creating purchase orders that are matched correctly to receipts and invoices, which will have to record exact payment history. These transactions will all need to reflect the same integration as the legacy system to be meaningful. This precise replication and integration is difficult to achieve and time consuming to convert. So you can see how quickly this snowballs into a complicated web of transactions, often leaving the systems with bad data or the organization with large programming bill that was probably not necessary.

Approaches

As new COA and item flex field structures are going to be designed and used, the corresponding mapping has to be created. Whether the historical data are converted or not this mapping table or logic will be there. A considerable amount of effort will be required to map and then reconcile the ledger accounts and items will be present for all possible ways. Keeping this aside we can attack the problem through different tools, techniques and methodologies.

After doing thorough research, analysis and internal discussion IBM thinks that this complex requirement can be handled if we follow any one or combination of the approaches mentioned below.

Old & New System Up & Running

Keeping the reporting and auditing requirement in mind we can have a solution where old (12.0.3) system will be kept up & running for read-only mode. Open transactions will be brought into new system and users will do business as usual in new environment. Partially closed transactions will be converted, and through DFF or custom tables the links of the unique ids for the corresponding closed transactions will be stored and provided to users/group of users to query them in old system.

            Business along with functional analysts needs to find out the gaps for each individual entity (Purchase Orders, Receipts, Sales Orders, Shipments, Payables Invoices, Receivables Invoices, Sub Ledgers, Fixed Assets etc). The information captured can then be used to design DFFs or Custom tables. Ideally DFFs would be the best to use as it requires minimum effort and will be as par AIM Methodologies. Each entity is having limitations of number of DFFs segments that can be used. If unique ids corresponding to partially closed transactions are found to be huge, then custom table approach will be used. In order to view these unique ids custom form will have to be developed for each entity and will be called from corresponding menus from the application. In order to meet some critical audit reports data will be fetched from both the systems and presented together using this DFF/custom table links.

Advantages

1.     Audit requirements are addressed through custom reports from based on two separate databases

2.     Details of closed and partially transactions are not lost

3.     Less time for iterative testing, reconciliation & refresh activities

4.     Improved system performance as online transaction processing load is reduced

5.     Reduced Programming effort

6.     Availability of Oracle support

Disadvantages

1.     Limited number of reports till the old system is decommissioned

2.     Retain and maintain the old system

3.     User Training to be familiar with both systems

4.     Multiple to & from navigation between old and new system

Estimation

For each of the process DFF/custom tables need to be setup and corresponding data need to be mapped and brought in from old system. Assuming the above activities are within scope the rough estimate will be approximately 200 PH per process areas. So the rough estimate for this approach will be 200*12=2400 PH

Additionally, there will be 5 audit reports budgeted where data from both the system need to be fetched. The rough estimate of the same will be 1000 hours assuming these to be complex reports.

Redefine the access and security layer for the old system – estimated at 200 PH to design and test.

New System with Data Warehouse (DWH)

With this a new DWH instance will be created. To provide ad-hoc and MIS reporting capability for each of the process areas, materialized views will be created and data from old system will be brought through these views. Once data are refreshed the old system will be decommissioned. Assuming that customer wants to drill down to the lowest level of granularity, views are going to be created for following areas

Ø       Requisitions – Headers, lines & distributions

Ø       Purchase Orders – Headers, Lines, Distributions

Ø       Material Transactions – Transactions (Lot & Serials) and transaction Accounts

Ø       Purchase Order Receipts

Ø       Payables Invoices – Headers, Lines & Distributions

Ø       Payments - Batches, Schedules, Checks, Bank & Bank Accounts

Ø       Suppliers (Active & Inactive) – Suppliers, Supplier Sites, Usages

Ø       Customers (Active & Inactive) – Customers, Accounts, Usages, Profiles

Ø       Sales Orders – Headers, Lines, holds

Ø       Shipping – Deliveries & Details

Ø       Receivables Invoice – Headers, Lines, receipts & applications

Ø       Sub-ledgers

Ø       General Ledger – Batches, Lines, Balances

Ø       Fixed Assets

Each for each entity a top level view will be created which will then be used in BI Publisher to provide the drill down ad-hoc and audit reports. To meet some complex reporting requirements where in data from both systems will be pulled and presented, will developed in line with previous approach. If BI Publisher is used with OBIEE then the same can be accessed from transaction processing system itself through standard/custom responsibility.

Advantages

1.     Single source of truth with the help of OBIEE

2.     Seamless  integrations

3.     Details of closed and partial transactions are not lost

4.     Can be done in parallel or after go-live

5.     Maintenance cost is less as only DWH has be to up & running 

6.     Availability of Oracle support

7.     On-demand reporting would be easy

Disadvantages

1.     Separate Database installation and maintenance cost

2.      Huge design and programming effort

3.      Go-live date can not be met

4.      Has to be handled separately

5.      Users need to be trained

6.      DB Link exposes security

Estimation

Developing and testing one materialized view will take approximately 16PH. Assuming on an average three levels for each entity total effort on creating entity level views will be 16X20=320PH. To build the top level single complex view might need 40X20=800PH. Building BI Publisher reports based out of these top level views will take 80X20=1600PH. If complex audit reports are needed to be built using both old and new systems then that might take another 1000PH.So roughly this approach will take 4000PH.

Convert Risk Free or Low Risk Areas

Here the goal is to convert those transactions which are easy, risk free or less risky and requires minimum effort to test and reconcile. Due to the fact that all transactions ultimately flow to General Ledger, due diligence need to be given while choosing transactions chosen for conversion. After doing research and analysis it has been found that following two areas can be converted with minimum risk:

·         General Ledger balances and

·         Closed sales orders

General Ledger Balances       

We can convert balances as far back as you need to go, but the first date needs to be decided early in the project, as you cannot add prior months once the calendar is set up and the first period is opened, and this is one of the very first things will be set up in EBS. A mapping from the old chart of accounts to the new one will be needed no matter how far back you convert. This can be loaded into temporary table in Oracle database or in Excel, depending on whether API or Web ADI is used to convert the balances.

In addition to determining how far back you want to import data, we need to make two additional decisions: First, how to handle the transactions that will be generated with sub-ledger conversions, and second how to handle foreign currency balances. When data is converted for the subledgers, such as Assets or Open Payables transactions they will result in General Ledger entries. These balances presumably exist in the General Ledger and will result in duplicate entries and incorrect balances in these accounts. We have few options as to how to handle this. Following are two of them:

Ø       Trick the system by debiting and creating the same account combination for each transaction. In other words, when a Payables invoice is converted, the expense distribution will have the exact same account number as the liability account, having no impact on the balances when the journal entries are created. The problem with this process is there is no record in EBS of where the transactions were originally coded, and it also results in incorrect entries if the transactions are voided or cancelled

Ø       A second option is to reduce the conversion amounts from the General Ledger balances being converted, and allow the subledgers to create the entries for these transactions. Finally the journal entries created from subledgers can be imported, posted and reversed in the same period, leaving zero impact on the general.

No matter which option is selected it is important to reconcile the subledger and General Ledger in the old system, track the imported journal entries to ensure key accounts create the same balances as the Ledger and old system.

Sales Orders

Oracle has provided the option to bring closed sales orders from the legacy system for historical purpose. Data will be extracted and imported to open interface tables with closed status and then oracle API will be used to import them in the new system. There will be no corresponding shipping transactions created out of these and hence no Receivables invoices will be created. These are purely used for tracking purpose

In short this approach can be used with approach 1, where in Closed Sales Orders and past Ledger balances will available from new system and all for all other closed transactions users need to go back to old system and query them accordingly.

Advantages

1.     Less dependency on old system

2.     Same system for closed and open transactions for Sales Orders and Ledger balances

3.     Details of closed and partially transactions are not lost

4.     Complex auditing requirements are met

Disadvantages

1.     Old historical data will not be there for all entities in new system

2.     Old system has to be up and running for reporting and auditing

3.     Extra programming effort for Sales Orders and Old balances conversion

4.     User training and overhead for using two systems

5.     Maintenance cost for old system

Estimation

As this will be used in addition with approach 1, the only addition will be the effort to convert closed sales orders and past balances. Assuming the moderate volume of transactions this will take 500PH. This will make the total rough estimate 4000PH.

New System with Custom tables

There can be a solution where data will be consolidated up to 1^st level and will be stored in custom tables for each entity and sub entities – like Invoices, POs & SOs etc. Custom reports/pages will be developed on top these custom tables and will be linked to the partially closed transactions in the new system. For example if a partially closed Invoice screen is opened, the custom form will display the receipts which were created for the partial payments and the POs for which this Invoice has been created. In order to view the details of the receipts and POs users need to go back to old system and query.

This will also be an add-on to the approach 1 and make the first level of auditing fast, quick and accurate.

     

Advantages

1.     Users and auditors will have one level of visibility to past transactions

2.     Ad-hoc report can be developed

3.     BI Publisher can be leveraged to build standard and audit reports

4.     Reduced transaction processing load

5.     Reduced down time during upgrade

6.      Availability of Oracle support

Disadvantages

1.     Not the exact replica of old system

2.     Level one drill down may not meet audit requirements

3.     Extra programming effort for extracting level 1 data, designing corresponding custom tables, forms and reports

4.     Users need to be trained to use custom forms

Estimation

Designing and developing custom tables and forms for each of the 12 pain areas will take 3000PH. As this will be add-on to first approach, so the overall rough effort will be 7000PH.

POC Approach

If customers insist on converting all/limited historical data, then the best way to handle this it to do it through POC. With this we will take one functional area like - Procure to Pay, where we can check the feasibility. In this approach we will check for the history and volume of data to be converted, data sources, technical feasibility including APIs and recommend a timeline along with associated risks at the end of the exercise.

Advantages

·         Almost exact replication of legacy system

·         No hardware and maintenance cost for legacy system

·         Single source of truth

·         Reduced or less effort on reporting and auditing

Disadvantages

·         Huge programming bill

·         Considerable effort on testing and reconciliation

·         Risk of losing data integrity

·         Might have to go for out of the box custom APIs

·         As a side effect we might be the risk of losing oracle support

·         Similar POCs for different functional areas – O2C, FIN etc

Estimation

Considering the integrated referential links between Oracle EBusiness framework the POC will take an effort of 4 resources for 2 months - 1 functional and 3 technical.