How to migrate data into oracle applications

Purpose:

The Purpose of this document is to arrive at a Data migration Strategy for ZTL. Data Migration is the process of transfer of data from the current systems in use to the proposed Oracle Financials System. Currently most Accounting Data is maintained in Tally and Excel and Data from these systems will be converted to Oracle Applications data. Henceforth in this document the terms “Migration” and “Conversion” will be used interchangeably

The Strategy will be covering the following aspects:

1. Cut Off Date
2. Type of data to be migrated
3. Migration Process in General
4. Module wise Migration Strategy













5. Data formats required – Excel format


6. Man-Day estimates of development of interfaces.

The Migration strategy given below is with the following modules in mind General Ledger(GL), Accounts Payable (AP), Accounts Receivable(AR), Oracle Projects (PA) and Oracle Purchasing (PO). Fixed Assets and Cash Management will be undertaken in Phase 2 of the implementation.

1. Cut-off Date :

The selection of cut off date is predicated on the following reasons:

a) Clear Identification of transactions from earlier system
b) Auditability
c) Continuity

The Cut off date is normally taken as the closest date for which Statutory Audit has been completed so that there are no audit complications and is preferably at the beginning of a month. We therefore have two options viz 1st Apr’01 or 30th June’01.

We strongly recommended having cut-off date for data migration as 30th June’01. To enable this two things have to be achieved:

a) Statutory Audit should be complete till the 30th June 2001.
b) All Vendor balances and Customer balances will have to be confirmed for the cut off date.

The Audit should made with a clear understanding with the Auditors that there is no question of revisiting the earlier system as the earlier system will be discontinued. We prefer this system because it reduces the amount of data that needs to be migrated as this would mean taking transaction from 1st of July into Oracle Applications. This reduces the time that will be taken for migration significantly and the objectives underlined above can still be achieved.

Obtaining customer and Vendor confirmations is necessary because it means the balances that are entering the new system through sub-ledgers are correct and there are less reasons for revisiting the earlier systems.


2. Type of data to be migrated

There are essentially two kinds of data that needs to be migrated:

a) All Master data in all the modules.
b) All Open Transactions in various modules.

In the modules that are being implemented the following are the Masters and Transactions that will be migrated:

Masters:

Module
Name of Master
Interface Exists?
Process of Entry
GL
Chart of Accounts
No
Manual or ADI if available
AR,PA
Customers
Yes
Manual as the number of Customers are few
AP,PO
Vendors
No
Manual
PA
Projects
No
Manual
Common
Employees
No
Interface from HRIS
AP,AR
Bank Masters
No
Manual

Transactions:

Module
Name of Master
Interface Exists?
Process of Entry
GL
Trial Balance
Yes
Interface
AR
Open Customer Invoices
Yes
Interface

Customer Receipts for closed Invoices
Yes
Interface if there is volume otherwise manual.
AP
Open Vendor Invoices
Yes
Interface

Invoice Payments
No
Manual
PA
Current Projects Cost
Yes
Interface

Current Projects Revenue
Yes
Interface
PO
Open Purchase orders
No
Manual

3. Migration Process in General

The Migration process is a two step sequential process

Step

Responsibility
1
Data Preparation
Finance and Accounts Dept
2
Data Migration
F & A Department & Implementation Team


Data Preparation:

Data preparation essentially is the process of culling out data from Tally and the current Excel Sheets and giving them in the required formats. Since Oracle Applications requires certain Mandatory data for setting up the application and these data may not be captured in the existing systems there is an element of data preparation that will be required. For Eg. If Vendor Address is not captured in Tally it will have to be obtained for migration to Oracle Apps.

Excel formats will be provided to the F&A department in which the data will have to be provided. These formats will basically contain data that is mandatory for Oracle Apps and some data that though not mandatory will be required because of the way the System will be set-up for Zensar.

Once the Cut-off date has been identified the following process will be followed:

a) A trial balance will be taken for the cut off date. This Trial balance will have to be mapped to the new proposed 8-segment chart of Accounts.
b) Once the mapping is done the mapping will be cleaned for defects that may arise between the mapping and the Set-up data provided.
c) After the correct mapped trial balance is ready individual accounts will be broken down into what will flow from respective sub-ledgers and what will be entered directly into General Ledger.
d) For all those transactions that come from sub-ledgers detailed break-up will have to be provided for the balance in General Ledger. For eg. The Balance of the Vendor Control Account in GL should be the sum total of all the invoices that are entered in AP.
e) Dummy Accounts will be used wherever necessary for smoothening the process of migration.

Data Migration:

This step essentially involves running the interface to get the data migrated into Oracle Apps from the Excel Sheets. Wherever there are no interfaces provided this will have to be done manually. This can be done by hired data entry operators but there is an element of training in the whole step and if the current users are a part of the data entry process they will also get trained.


Example of How Migration will be carried out:

Existing Trial Balance:


Account
Mapped Account
Module
Debit
Credit
Share Capital
01.00.000.31101
General Ledger

100000
Secured Loans
01.00.000.21101
Accounts Payable

150000
Vendor A
01.00.000.22101
Accounts Payable

25000
Vendor B
01.00.000.22101
Accounts Payable

40000
Vendor C
01.00.000.22101
Accounts Payable
5000

Customer A
01.00.000.12101
Account Receivable
15000

Customer B
01.00.000.12101
Account Receivable
35000

Fixed Assets
01.00.000.11101
General Ledger
240000

Travelling
01.00.000.71101
General Ledger
34000

Cost of Goods Sold
01.00.000.51101
General Ledger
55000

Entertainment
01.00.000.72101
General Ledger
16000

Revenue
01.00.000.41101
General Ledger

85000
Total


400000
400000

A dummy Account will be defined for Migration : 01.00.000.99999

General Ledger:

The Following Entry JV will be passed


Description
GL Account
Debit
Credit
Share Capital
01.00.000.31101

100000
Fixed Assets
01.00.000.11101
240000

Travelling
01.00.000.71101
34000

Cost of Goods Sold
01.00.000.51101
55000

Entertainment
01.00.000.72101
16000

Revenue
01.00.000.41101

85000
Migration Clearing Account
01.00.000.99999

160000


Accounts Payable:

The Following Invoices will be entered:

Vendor Name
Invoice No
Debit
Credit Amount
Invoice Amount
Vendor A
I23
01.00.000.99999
01.00.000.22101
(25000)
Vendor B
4546
01.00.000.99999
01.00.000.22101
(40000)
Vendor C
568 (Debit Memo)
01.00.000.99999
01.00.000.22101
5000
Institution A
111
01.00.000.99999
01.00.000.21101
150000

On posting to General Ledger the following entry will be passed in GL:

Debit 01.00.000.99999 210000
Credit 01.00.000.22101 60000
Credit 01.00.000.21101 150000

Accounts Receivable:

The Following Invoices will be entered:

Customer Name
Invoice No
Debit
Credit Amount
Invoice Amount
Customer A
I23
01.00.000.12101
01.00.000.99999
15000
Customer B
4546
01.00.000.12101
01.00.000.99999
35000

On posting to General Ledger the following entry will be passed in GL:

Debit 01.00.000.12101 50000
Credit 01.00.000.99999 50000

The New Migrated Trial Balance from Oracle Applications will therefore look as follows:

Account
Mapped Account
Module
Debit
Credit
Share Capital
01.00.000.31101
General Ledger

100000
Secured Loans
01.00.000.21101
Accounts Payable

150000
AP Control Account
01.00.000.22101
Accounts Payable

60000
AR Control Account
01.00.000.12101
Account Receivable
50000

Fixed Assets
01.00.000.11101
General Ledger
240000

Travelling
01.00.000.71101
General Ledger
34000

Cost of Goods Sold
01.00.000.51101
General Ledger
55000

Entertainment
01.00.000.72101
General Ledger
16000

Revenue
01.00.000.41101
General Ledger

85000
Migration Clearing Account


210000
210000
Total


605000
605000


4. Module wise Migration Strategy


The migration of data for individual modules could be different from the overall plan for migration. Individual modules are covered as given below:

General Ledger:

As explained in the example earlier the Trial Balance will be broken down into entries that will flow into General Ledger. This will be brought into the system either manually or by running an interface.

For the purpose of Monthly comparisons the GL module migration is proposed as follows if the cut off date is chosen as 30th June 2001:

a) The Trial balance for the month of April and May will be uploaded directly in General Ledger. There wont be any sub-ledger break up for these transactions. This is being done only for the purpose of getting monthly reports from General Ledger.
b) Depending upon the open transactions that are flowing from sub-ledgers necessary adjustments will be made for those transactions that are entered in General Ledger directly if these transactions are impacting April and May balances.

If the Cut off is 1st April then all transactions will be brought into the system so no such break up will be required.

Budgets are not considered for upload in this phase.

Accounts Payable:

Based on the Trial Balance Mapping all the AP transactions will be routed through AP as invoices and payments. There are two options that are available for migrating invoices:

a) With Actual accounting: In order to do this the F&A dept will have to provide the actual debit accounts for each and every invoice that will be entered in the system.
b) With Dummy accounting: This method helps us to save time as we will bring in all the invoices with dummy accounts and all the actual accounting will be taken directly to General Ledger but sub-ledger break-up of GL balance is lost and becomes available only from the date transactions are entered live. This method is recommended only for audited transactions.

The following kinds of transactions will be imported into AP:

a) Invoices: All credit balances will be migrated as Invoices. The F&A department will have to provide Invoice-wise break up of credit balances in the specified formats.
b) Debit balances: All debit balances will be brought into the system as credit memos. The F&A department will have to decide whether the debit balances against each vendor are to be treated as Advances or as Debit memos. We prefer to treat them as advances as the system prompts the user that such a prepayment exists when the user is entering a fresh invoice against that vendor but they have different accounting impacts. Hence this decision will have to be taken by the F&A department.
c) All deposits: All deposits in the TB, which are of refundable nature, will have to be brought in as Prepayments in AP.
d) All Loans: All loans with a payment schedule will have to be brought in through AP as invoices.

Oracle Applications does not provide for an interface for Payments in AP hence all invoice payments will have to be made manually. F&A department will have to keep record of check numbers for the cut-off date chosen.


Accounts Receivable:

Based on the Trial Balance Mapping all the AR transactions will be routed through AR as Invoices and receipts. There are two options that are available for migrating invoices:

c) With Actual accounting: In order to do this the F&A dept will have to provide the actual credit accounts for each and every invoice that will be entered in the system.
d) With Dummy accounting: This method helps us to save time as we will bring in all the invoices with dummy accounts and all the actual accounting will be taken directly to General Ledger but sub-ledger break-up of GL balance is lost and becomes available only from the date transactions are entered live. This method is recommended only for audited transactions.

The following kinds of transactions will be imported into AR:

e) Invoices: All debit balances will be migrated as Invoices. The F&A department will have to provide Invoice-wise break up of debit balances in the specified formats. This format will include details of the salesperson for each invoice and the Customer PO number. This invoice will be entered directly in AR and will not be routed through the Projects Module.
f) Debit balances: All credit balances will be brought into the system as credit memos. The F&A department will have to decide whether the credit balances against each customer are to be treated as deposits or as credit memos.
g) All deposits: All customer deposits in the TB, which are of refundable nature, will have to be brought in as deposits in AR.

Purchasing:

Purchase Orders:
Purchasing is a non critical function in Zensar and therefore we do not see the merit in bringing closed PO's into the system. In the case of open PO’s the following options are available:

1. Bring only new PO’s into Oracle Apps
2. Bring existing PO’s into Oracle Apps.

Option 1 requires no migration.


In option 2 only open Purchase Orders as on the 10th of August 2001(As close to the go live date as possible) will be migrated into the new system. For this the following can be considered as open Purchase orders:

a) PO’s open for receiving
b) PO’s open for invoicing

Only PO’s open for receiving will be considered for Migration. In the case of partially received PO’s, both the PO and its receipt will be recorded in the system. All the invoices, which are received for PO’s not created in Oracle Apps, will be matched outside the system. Since in the creation of new PO’s there will be new PO numbers generated by the system it is necessary that the new PO’s are sent to the various vendors informing them about the change in PO numbers.

Requisitions and other documents:

For transactions as on 10/08/2001, where the Standard PO has not yet been created the following will have to be entered manually:

a) Approved Purchase Requisitions
b) Requests for Quotations if any
c) Received Quotations if any

All types of valid contracts, agreements and AMC’s as on 10/08/2001 have to be entered as PO’s, of type Blanket Purchase Agreement, Contract Purchase Agreement or Planned Purchase Order whichever applicable.

All the releases made against Blanket Purchase Agreement and Planned Purchase Order for which material or service has not been received or has been received partially will be treated similar to an open PO.

Projects:






Conversion Projects :








Few issues to be considered on migration of projects data :

· Should we migrate only New Projects or the Old Projects also needs to be entered ?

· We can enter the only open projects, which are supported by project’s wise time sheets

· Costing of Projects – Standard or Average Costing ?

Conversion Purchasing :








All Standard Purchase Orders as on 10/08/2001, against which material or service has not been received or has been received partially.



Below is the way the data will be migrated:

· All the Purchasing related transactions will be transferred to the system manually.



Reports:


Purchase Order Detail Report – Report will list all the details of the P.O entered during the conversion process.

Conversion Payables :








All Vendors and Employees would be defined into Oracle Payables along with the relevant information, prior to the entry of transaction related data.

The vendor balances in the Legacy System as on 30/6/2001 to be 100% reconciled with each customer – acceptance of account balance.

The check book stock should be prepared as on 30th June’01 – we have to enter it into the system to generate the checks from Oracle Payables.

All outstanding transactions i.e. Invoices, Credit Memo, Debit Memo etc and unadjusted Deposits/Advances as on cut-off-date will be transferred in detail i.e. on transaction by transaction basis.




Invoices will be entered for the outstanding amount.

All the outstanding credit balance as on the cut-off-date against individual employees to be entered as a standard invoice at detail level. The various types of outstanding credit balance against the employees could be one and all among the following:


Travel Claims
Medical Reimbursements
LTA Reimbursements etc.

Document sequences would be created to reflect the transactions entered in the various Batches.


Below is the way the data will be migrated :

· All the outstanding Invoices, Credit Memos and Debit Memos will be uploaded.

· All the outstanding Prepayments will then be manually entered.

· All the clearing Bank Payments as discussed earlier will be then manually entered.

Posting in GL :

GL Post will be run after every batch is uploaded/manually entered and approved. The respective accounting entries would be generated only on GL post.

Reports

Invoice Register Report – On Approving and running the GL post ‘Invoice Register’ report for individual Batches as mentioned above will be generated which will detail all the transactions entered during the conversion process.

Payment Register Report – On making the Payment a ‘Payment Register’ report would be generated to reflect the payment made to during the conversion process.



Conversion Fixed Assets :








All the capitalised Assets as on 30/6/2001 would be entered in Oracle Assets along with the Accumulated Depreciation Reserve Balance. This creation of Assets in the Fixed Assets module would be done using the Mass Additions functionality.

Whether assets retired before 30th June’01 should we take / upload it into the system? – to be confirmed with Vaijayanti.

Below is the way the data will be migrated :

· All the capitalised Assets would be uploaded into Fixed Assets with the Capitalised and Post as the flag.

· In one upload the entire Fixed Assets register would be uploaded into the module for further processing. On the completion of the upload process, all the Assets uploaded would be Posted.

·

· On posting the system will assign New Asset Nos. to all the Assets lying in MassAdditions.


· All the Assets would be uploaded with the Current Gross Value of the Asset along with the Life-to-date Depreciation Reserve balance and the Year-to-date Depreciation Reserve Balance.

For conversion purpose, the Depreciation Reserve should be segregated as follows:


Life-to-date Depreciation – Depreciation Reserve from the date of capitalisation till 30/6/2001.

Year-to-date Depreciation – Depreciation Reserve from 01-04-2001 till 30-06-2001.

The System will not generate any Accounting entry for Depreciation Reserve as the same has been manually entered. For future depreciation run, the system will generate the Depreciation Reserve after taking into cognizance already entered Depreciation Reserve and post the same to GL.


Report:

Conversion Assets Report : This report will list all the Assets entered during the conversion process along with the Depreciation reserve.

Conversion General Ledger :






All month wise Trial Balances from April’01 to June’01 to be entered in GL – this is basically with a view to facilitate MIS reporting.

All budget balances as on the cut-off-date.

On posting the actual balances in GL, the system will compare the Actual Balance against the Budgeted Balance and will reflect the Funds available. Encumbrance Budget balance from the Purchasing module will be transferred to GL where the available funds will be compared after deducting the actual balance and the encumbered amount from the budgeted balance.



All the account balances entered in GL will be compared with the various reports available in GL. On comparison of the various account balances, a TB from Oracle General Ledger will be generated and compared with the existing Tally TB and on reconciling the same the conversion exercise will get completed.

Reports :

Account Analysis Report – This report will reflect the balance against any account code. This report will be used to compare the Tally TB account balance with the GL Account Balance.

Trial Balance – A detailed TB will be generated to do the final comparison with the TB from the existing system.

6. Data formats required – Excel format

All the data to be uploaded in the system should be in MS-Excel formats only. The data for individual modules should be in conformity with Interface Tables in respective modules.

7. Man-Day estimates of development of interfaces

We may have to develop some of the interfaces in-house as it may not be available in the Apps or may be we have to develop some the customized interfaces. We have to estimate the man-days required for this purpose – the respective persons responsible for developing the interfaces to give input in this regard.

8. Module wise list of standard interface tables in various modules.

General Ledger:

GL_INTERFACE

Receivables:

LIST OF CUSTOMER INTERFACES:

RA_CUSTOMERS_INTERFACE
RA_CUSTOMER_PROFILES_INTERFACE
RA_CONTACT_PHONES_INTERFACE
RA_CUSTOMER_BANKS_INTERFACE
RA_CUST_PAY_METHOD_INTERFACE
AR_PAYMENTS_INTERFACE_ALL

LIST OF AUTO INVOICES INTERFACE:

RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL


Payables:

LIST OF INVOICES INTERFACE:

AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

LIST OF EXPENSE REPORTS INTERFACE:

AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL

Fixed Assets:

FA_MASS_ADDITIONS

Purchasing:

There are no standard interfaces available in this module.

Related Articles to Read