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 1st 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.
