The challenges of historic data migration in E-Business Suite
The saying goes ‘be careful what you wish for, you may get it’ and this is very relevant to data migration, especially migrating historic data. There are some large technical challenges to overcome when migrating history and once these have been overcome and E-Business Suite is full to bursting with data, there are some large business challenges reconciling it all. This article aims to explore some of those challenges and how you might get around them.
Managing Technical Consultant, Claremont
If you have been involved in (or are about to become involved in) an ERP implementation you can imagine the initial meeting to discuss the scope of the migration:
- Your IT team or systems integrator wants a simple migration, so their opening position is open or active data only should be part of the migration i.e. leave the historic stuff where it is.
- Your users usually want most of what they have now, i.e. the transactional, financial and accounting history relating to all activities surrounding their customers/suppliers/factory/ warehouse/engineers.
The mechanisms used for data migration, i.e. the open interface (table/concurrent program – batch) and the API (PL/SQL – row by row), are designed to apply the same validation as you would get entering the data via the E-Business Suite screens. And the E-Business Suite screens assume that once you have spent time entering the data you might like to do some sort of processing with it, e.g. record some payments if you have just entered a new invoice into AR. E-Business Suite does not expect you to enter transactional data just so you can reporton it and refer to it.
To be clear: we’re talking about transactional data here (invoices, orders, payments etc) rather than reference data (customers, inventory items, suppliers), although they both need each other to exist in E-Business Suite.
From a business perspective it is very difficult to carry out some functions without the data actually being present in E-Business Suite. Advanced Collections is a great example of this. It’s difficult to apply collection strategies without having a view on the invoice vs. payment performance of the customers. CRM is another good example, where your relationship with your customer is framed around the business you have done together and therefore the transactional data.
So how to reconcile these positions? What are the challenges? What are the alternatives? I’ll use some examples from recent Claremont migration projects:
Historic GL Journals or Balances:
Technically one of the easiest migrations to build (slightly more complex if you use multiple currencies but not massively so), but watch out for a few hazards with this one. Firstly you need your historic period(s) open, the various segment value sets need to have all your historic values (don’t forget to take them away afterwards), cross-validation rules may need to be disabled during the load. The challenges are obviously greater if you already have Oracle GL up and running as the period, value set, cross validation rule changes are trickier to make in an already live system.
Closed AR Invoices:
Always a popular request with the clients (my last two projects both asked for it and got it) and can be tricky. As previously mentioned, Oracle does not allow closed transactions to be directly entered into E-Business Suite so in this case you must first load an open invoice and then close it either by applying a receipt against it or crediting the balance with a credit note. Last year we worked on a R12 European roll-out to nine countries and this was a central migration requirement.
If you follow the applying the receipt method then you must also migrate the receipt. There are two common methods for doing this as I’ll describe below. The first is technically straightforward and a nightmare for the business, the second is the reverse case.
- Create individual receipts which match the open invoice amounts and close the invoices in 1:1 fashion. This is the easiest approach for IT to take, but gives you a reconciliation headache as you have just made up a bunch of receipts solely for the purposes of closing some invoices. Those receipts were never paid to you in the way you now see them in E-Business Suite. Our nine country rollout client had used this method when migrating to R11i some years previously and all the European units swore blind they would never use the same method again, as they appeared to have spent the time since going live on R11i sorting out the mess in AR.
- Re-create the receipts as they were paid to you. In reality medium and large sized enterprises get paid for more than one outstanding invoice on one receipt (certainly the case in the European rollout). The challenge with this is that, where you may have set a time limit on your historic migration e.g. we’ll go back to the start of the previous financial year, trying to replicate AR invoices and receipts can soon make this meaningless. Consider the following example:
- Invoices 100, 101 & 102 all occurred in the last FY and are therefore in scope for migration, these are imported via AR Autoinvoice as open items.
- Receipt A200 was received in payment for invoices 100 & 101 in full, but only part of the balance for invoice 102.
- Ther emainder of invoice 102 was paid for by receipt A201, however A201 also paid invoices 99,98,97 & 96 which occurred in the FY 2 years ago – you’ve just had scope- creep because you want an accurate reflection of what the customer paid you so you need both receipts and all invoices related to them.
The farther back you go, the less valuable the data becomes.
One last point on historic AR; watch out for tax codes. Legacy tax codes may not exist in E-Business Suite and may even use rates which are no longer available, but were when the invoice was raised. These will need to be configured in the E-Business Suite tax modules for the duration of the migration.
The key message is, if your migration is going to deliver something of value to the business then it is going to have to go down the more difficult path.
You also have to take into account the GL transactions that all this new sub-ledger data is going to generate. The approach we took was to allow AR, GL, FA and the rest to push their accounting entries into the GL, post the journals and reverse them, so you end up with zero GL movement from your historic transactions. One of our current clients uses Oracle GL as the ledger for a range of non-Oracle applications (these applications being the ones we are now extracting data from to import into Oracle), so we have to be careful not to double count revenue for example when pulling the history into E-Business Suite, as it had already been accounted for in a prior period.
Some related challenges:
- An historic AR data migration means you must load the (possibly) now historic customers into E-Business Suite before the transactions can load. The customer migration can be tricky as it has a lot of separate parts which clients tend to consider part of their customer record, but are in reality separate migrations in their own right, e.g. bank accounts (don’t forget to load the banks and branches first though), notes, contact points etc.
AP Invoices. To migrate a closed AP invoice it needs to get paid in E-Business Suite and the payment has already gone to the supplier from a separate system. Again, you’d be double counting if you pay it again and because of Oracle Payments configuration surrounding payment documents E-Business Suite is going to log a payment document number for a payment which didn’t take place in E-Business Suite (hint: auditors don’t like this).
To address this, a conversion payment document can be set up (or possibly more than one if you’re migrating multiple historic payment formats, e.g. cheques and EFT). This document and its associated sequence numbering is set up solely to simulate a payment being made, the payment document could be linked to your normal and then you can reconcile these transactions in cash management with a reversing journal in GL. This will mean that you can have a line in cash management with a zero value and all the take on payments will be reconciled to this line with the GL Journal. As an alternative, set up a dummy bank account, just remember to deactivate it afterwards.
Some Oracle modules are more helpful in the migration of historic data, for example Service Contracts. We’re involved in a migration using this module currently and there is a very handy feature within the Service Contracts APIs which allows you to create a contract as either fully, partially or completely unbilled at the point of creation. You achieve this via different billing streams attached to the contracts, remembering to set the fully billed flag on the API call.
In terms of effort from the project team, there is one very simple rule: the more data you migrate the longer someone has to spend reconciling it with the source system (hence the quote in the opening sentence of this piece) and there is never enough time in the plan to carry this out. Some pointers from recent projects:
- Get the business (not IT) to identify the reconciliation reports in the legacy system at the design stage (when you’re writing your CV.010 conversion scope document) and make sure they have the right level of detail.
- Don’t use the standard E-Business Suite report for reconciliation, at least not in their current form. It’s much easier to borrow the SQL from these reports and get the data into Excel (you can still easily compare the report totals with the E-Business Suite output) which is wonderful for reconciliation. The standard reports don’t lend themselves well to Excel.
The most obvious: don’t migrate the data unless is actually forms part of a business process you want to implement in E-Business Suite, e.g. Advanced Collections. Organisations rarely throw the old system away (bear in mind there is a legal requirement to retain certain types of data) so you may still have read access to it. And in a lot of cases that’s enough.
Create a custom schema (check your licensing arrangements first) and export/import or SQL*Load the legacy data into tables of the same structure just living in Oracle. This works if you don’t need to use the data in key processes, but want to have it to hand for reporting. If you create synonyms for this data in your APPS schema then E-Business Suite reporting tools can see it and you can report on it through the concurrent manager. If you are a Discoverer user you could also extend your end user layer to encompass the non- E-Business Suite data.
Historic data can be valuable, but less so the further back you go (and don’t forget, the data starts ageing as soon as you load it). There are mechanisms available to get most data into E-Business Suite in their historic form, but this will make your conversion project more difficult. So the key is to strike the balance between necessary functionality and the safety net of having lots of data. One thing we found very useful was to review this balance after the first trial migration, the organisation reduced their scope by about 40% when they saw how long it took to migrate and the total volume of data. In truth, you probably need less data than you think you do.
Technical Managing Consultant
Michael leads the technical delivery of client projects as well as developing tools and methods aimed at removing risk and costs from Oracle ERP upgrades and implementations.