Projects:APR Migration Tool/User Manual
 | This document is still a work in progress. It may contain inaccuracies or errors. |
Contents |
About this user manual
This manual explains how to use the migration tool for a smooth migration of the old financial data to be consistent with the new flow implemented by the Advanced Payables and Receivables module (APRM). It will also cover other aspects like how to manage the detailed log information generated during the process as well as how to identify all the migrated data and verify the process. Please read this document from the beginning to the end before running the migration tool. As the name of the module suggests, this process is designed for the initial migration of data which means the process must be run as first step before start operating with APRM flows.
|
| IMPORTANT
The module is already published in Controlled Release maturity status. Early adopters / pioneers are able to install it and run the migration process in a testing environment. At the same time we can take benefit collecting valuable feedback from them. The process has been successfully completed in a productive environment coming from one of our business partners. As the migration is a very complex and critical process which must take in account all the different casuistic, we wanted to mature the module before moving it to General Availability. |
Terminology
- Old flow: current 2.50 Core's financial flow, includes payment rules, debt payments, settlements,...
- New flow: new financial flow implemented by the APRM, this is incompatible with the old flow and when the APR module is installed it is not possible to use the old one.
- Old payment: payments done in old flow.
- New payment: the term used for referring payments in the new flow. They can be accessed from Financial Management || Receivables & Payables || Transactions || Payment In/Out
Introduction
This module provides an straightforward and robust tool for migrating the financial data from the old flow making it suitable for the new flow implemented through Advanced Payables and Receivables (APRM) module.
Getting started
Installing the Migration Tool module
 | This step is not needed if you are asked to run Migration Tool during the upgrade process from Openbravo 2.50 to Openbravo 3 because Migration Tool is automatically installed. |
Log into the system using the System Administrator role and go to General Setup || Application || Module Management. Click the Add Modules tab and find the Migration Tool module within the list of all modules available in the central repository.
Note for Spanish speakers: If you want to use the Migration Tool module in Spanish, find within the list of all modules available in the central repository the Migration Tool Spanish (Spain) module and install it.
A detailed guide on how to install a new module can be found in the Install Module video.
Change to the role used to execute the migration process
Only the System Administrator role will have privileges to run the process. Log in as System Administrator and go to Application || General Setup || Client || Migration Tool.
Prerequisites
Document Types with document base AP Payment and AP Receipt have to be created and actived.
Migration Process
- Atomic process: database operations either all occur, or nothing occurs. Any problem during the process will force a rollback of the transactions performed until this event, keeping the database as it was before launching the process. It prevents updates to the database occurring only partially, which can cause an inconsistency in the system.
- It will be executed once and it won't be possible to undo it. It will neither provide a process to migrate data from the new flow to the old flow, it is a one way operation.
- The module cannot be uninstalled after a successful migration.
- As a prerequisite to the migration Advanced Payables and Receivables Management module should be installed.
IMPORTANT!
The migration at first should be done in a staging environment. Procedure is the following:
- Testing environment: create a clone of the production environment (here is the tool to help).
- Do the migration in the Testing environment.
- Verify the migration.
- Repeat the process in the Production environment if previous steps are successful. Before running this steps is highly recommended to create a backup of the database.
Interpretation of Messages
At the end of the migration process a message is shown with the following information:
Failure
- Entity: name of the entity in which the process has failed trying to migrate.
- Client: name of the client
- Record info: identifier of the faulty record.
- Error Cause: extended human readable explanation (if possible) about the cause of the error. If the information is not detailed enough the stack trace of the error is visible in Migration Tool Log window.
Success
Summary of the process.
Entities
Payment Method
Each payment rule of All_Payment Rule reference list will be migrated as a new Payment Method. These are the default payment rules provided in Openbravo ERP master data. If you have modified or added any record to the list it will be also migrated.
Note: the migration process will skip NOT ACTIVE payment rules.
Equivalent payment methods will be available on Financial Management || Receivables & Payables || Setup || Payment Method || Payment Method
 |  |
Default configuration
|  |
Cash type payment method configuration
|
Cash payment rules will have a bit different configuration, having an automatic receipt / deposit and payment / withdrawn enabled. It will generate the payment and the transaction at one shot when processing an invoice using this payment method. What does this means?
How the process identifies which are the Cash payment rules? Looking in the payment rule name, if it contains the cash string (no case sensitive) the process will consider them as cash type. |  |
Linked entities
In the following windows the Form of Payment value has been migrated to the corresponding new Payment Method.
- Project Proposal
- Table: C_ProjectProposal
- Window: Project & Service Management || Transactions || Service Project || Service Project >> Proposal
- Service Project
- Table: C_Project
- Window: Project & Service Management || Transactions || Service Project || Service Project
- Payment Term Line
- Table: C_Paymenttermline
- Window: Master Data Management || Business Partner Setup || Payment Term || Header >> Lines
Verification
New column em_aprmt_payment_rule in fin_payment_method table will contain the reference to the corresponding payment rule.
Note: the field is not visible through the application.
Financial Account
Bank
Each Bank Account defined in the application in Financial Management || Receivables & Payables || Setup || Bank || Bank >> Bank Account will result on a new Financial Account of type Bank.
Default mapping for Bank type Financial Accounts:
| Old Flow | New Flow | ||
| Table | Column | Table | Column |
| C_Bank | name | FIN_Financial_Account | name |
| C_Bank | -- | FIN_Financial_Account | Description ('Generated by Migration Tool') |
| C_Bank | -- | FIN_Financial_Account | Type: Bank (B) |
| C_Bank | c_location_id | FIN_Financial_Account | c_location_id |
| C_Bank | swiftcode | FIN_Financial_Account | swiftcode |
| C_Bank | codebank | FIN_Financial_Account | codebank |
| C_Bank | routingno | FIN_Financial_Account | routingno |
| C_Bank | codebranch | FIN_Financial_Account | codebranch |
| C_Bank | digitcontrol | FIN_Financial_Account | bank_digitcontrol |
| C_Bank | ine_number | FIN_Financial_Account | ine_number |
| C_Bank | c_bpartner_id | FIN_Financial_Account | c_bpartner_id |
| C_BankAccount | accountno | FIN_Financial_Account | accountno |
| C_BankAccount | iban | FIN_Financial_Account | iban |
| C_BankAccount | codeaccount | FIN_Financial_Account | codeaccount |
| C_BankAccount | digitcontrol | FIN_Financial_Account | account_digitcontrol |
| C_BankAccount | isdefault | FIN_Financial_Account | isdefault |
| C_BankAccount | creditlimit | FIN_Financial_Account | creditlimit |
| C_BankAccount | c_currency_id | FIN_Financial_Account | c_currency_id |
| C_BankAccount | -- | FIN_Financial_Account | currentbalance: sum of all bank statements of the the C_BankAccount. |
| C_BankAccount | -- | FIN_Financial_Account | fin_matching_algorithm_id: null |
Cash
Each CashBook defined Financial Management || Receivables & Payables || Setup || Cashbook || Cashbook will create a new Financial Account of Cash type.
Default mapping for Cash type Financial Accounts:
| Old Flow | New Flow | ||
| Table | Column | Table | Column |
| C_CashBook | name | FIN_Financial_Account | name |
| C_CashBook | Description | FIN_Financial_Account | Description + 'Generated by Migration Tool' |
| C_CashBook | -- | FIN_Financial_Account | Type: Cash (C) |
| C_CashBook | c_currency_id | FIN_Financial_Account | c_currency_id |
| C_CashBook | -- | FIN_Financial_Account | currentbalance: 0 |
| C_CashBook | -- | FIN_Financial_Account | creditlimit: 0 |
| The rest of columns are left as null | |||
Financial Account and Payment Method association
All the Payment Methods created in the payment method migration process will be associated to each Financial Account using as default the configuration of the payment method definition.
The affected table is fin_finacc_paymentmethod
Verification
As each bank account and cashbook is mapped with a new financial account, the migration tool will add new field which will allow the user to navigate to the corresponding Financial Account from the bank account or cashbook.
 |  |
Looking in the database
New column em_aprmt_financial_account_id in c_bankaccount and c_cashbook tables will contain the reference to the corresponding Financial Account.
Business Partner defaults
This process will migrate the defaults values defined for Business Partner playing the role of Customer and Vendor/Creditor.
Payment Method
The default Payment Method (Customer and Vendor/Creditor) will be filled with the previously created Payment Method for the corresponding Form of Payment defined in the following windows:
|  | |||||||||||||||||
Financial Account
The default Financial Account (Customer and Vendor/Creditor) will be filled with the previously created Financial Account for the corresponding Bank Account defined in the following windows:
|  | |||||||||||||||||
Accounting
All the accounting configuration defined for Customer and Vendor/Creditor
- Master Data Management || Business Partner || Business Partner >> Customer >> Customer Accounting (C_BP_Customer_Acct table)
- Master Data Management || Business Partner || Business Partner >> Vendor/Creditor >> Vendor Accounting (C_BP_Vendor_Acct table)
will be deactivated following this rule:
All the records will be DEACTIVATED if the status is not neither empty (null) nor default (--).
Order
How do I know if an order will be migrated?
It needs to satisfy the following conditions:
- It must be processed.
- It must not been invoiced.
- It must not been voided.
- Document type distinct to Proposal and Quotation.
No payments
If the order hasn't any payments a new Payment Plan will be created for the total of the order.
With Payments
As a general rule, all the pending payments create a new payment plan with the total amount of the payment. Another payment plan is created with the outstanding amount (Total gross amount of the order - sum[amount of payments]).
In the following example a sales order of 200€ has been completed.
In Payment tab there are two records:
- Processed payment of 75€.
- Pending payment of 25€
In Payment Plan tab we can see what is the result:
- Payment Plan for the pending payment of 25€.
- Payment Plan for the outstanding amount: 200 - (75 + 25) = 100€.
Invoice
Direct migration
Invoices of this group will be fully migrated. The records of Payment tab will be removed and will be replaced by the corresponding entries in the Payment Plan tab.
How do I know if an invoice will be fully migrated?
It needs to satisfy the following conditions:
- All the payments must be not canceled.
- All the payments must be not related to a payment status movement.
- It must be not related to any withholding.
- Any payment must be in default or null status. Other final status means that the payment has been managed or it can has configured other account (no the default for default status).
Let's describe the process in pseudo-code
for each invoice in this group of invoices do
for each old payment in invoice do
create the equivalent payment plan
end for
end for
In the following example we have an invoice which fulfills previous conditions. It has only one payment of 60€, which is not canceled and does not belong to any payment status management.
Partial migration
In this case the migration process will not generate any new record in Payment Plan tab and the records in Payment tab will remain.
The process will search for NON CANCELED old payments in the rest of invoices (invoices which have been not covered in the previous group) and for each old payment it will generate a Payment of new flow (Payment In/Out) with the following information:
- Payment date: inherited the due date.
- Business Partner: inherited the business partner.
- Payment Method: uses the equivalent payment method for the form of payment defined in the old payment. Remember that in the previous steps the migration process has created a equivalent payment method for each form of payment.
- Deposit To: Remember that in the previous steps the migration process has created a equivalent financial account for each bank account and cash book.
- Takes (if exists) the financial account that matches with the bank account used in the old payment.
- Takes (if exists) the financial account that matches with the cash book used in the old payment.
- Takes the default financial account defined for the business partner as Customer or Vendor (depending of the role which is playing).
- Financial Account defined as default at client level.
- If in the previous steps hasn't been possible to select a Financial Account the process will show an error.
- Amount: inherited the amount.
- Write Off Amount: inherited the amount.
- Status: awaiting execution. At this status the payments need to be manually executed.
It can be done from the Payment In/Out window, clicking on Execute Payment button Financial Management || Receivables & Payables || Transactions || Payment In / Out
or
it is possible to execute payments in batch mode using Financial Management || Receivables & Payables || Transactions || Payment Execution || Payment Execution window.
When executing the application will show a popup where the user can enter the date when the payment takes place.
This payment will have assigned a G/L Item as follows:
- Name: <status of the payment> - <business partner search key> - <business partner name>
- Description: <status of the payment> - <business partner search key> - <business partner name>
- Debit Account
- It will check if for the status of the payment already exists a Customer Receivables No account defined for the Business Partner. Master Data Management || Business Partner || Business Partner >> Customer >> Customer Accounting
- If does not exist, the process will search for Customer Receivables No account defined for default status (--).
- If does not exist, the process will search for Customer Receivables No account defined for empty status.
- Credit Account
- It will check if for the status of the payment already exists a Vendor Liability Account defined for the Business Partner. Master Data Management || Business Partner || Business Partner >> Vendor/Creditor >> Vendor Accounting
- If does not exist, the process will search for Vendor Liability Account defined for default status (DE).
- If does not exist, the process will search for Vendor Liability Account defined for empty status.
Let's explain it more visually:
The screenshot bellow shows in edit view the payment that will be migrated. The status of the payment is -- (default status) and it is related to McGiver Supplies business partner.
McGiver does not have accounting defined for default status (--) but it has for empty status:
The G/L Item is created using the previous accounts:
Settlements
This process will cover all the non canceled payments which are not related to any invoice/order.
The migration will consist in the same steps described for partial migration of Invoices.
In this case if the payment already has a G/L Item the process will reuse it when creating the new Payment In/Out. If not, the process will take charge of creating the corresponding G/L Item as described before.
Migration Log
Each execution of the migration process will produce an extensive log trace accessible from the application.
You should just go to: General Setup || Client || Migration Log || Migration Log
The following information is stored:
- Execution date: date (including time) when the process was executed.
- Result:
- Success: the process has finished successfully and your instance is ready to start working with the new flow.
- Error: a problem occurred during the process.
- Summary log for each entity: the log will show the identifiers of the records inserted or modified during the process. If the process fails, the error stacktrace is captured and added together with the log. Not only that, the process will identify the faulty record and you will be able to navigate to it from the this window.
Verification
Before running the migration process use 'old' Payment Report in order to get a picture of all the outstanding payments grouped by business partner. You should first query for receivables and then for payables. Save both in pdf format, it will allow to compare the result after the migration.
Once the migration has been completed successfully we should do the same exercise using the new Payment Report and compare the result. They should match.
The following example is taken from the SmallBazaar client avaiable in Openbravo ERP.
