Projects:Exchangerateatdoclevel/Functional Documentation

Contents 1 Document sign-off 2 Document Status 3 Objective 4 Process Overview and Assumptions 5 User Stories 5.1 Current behaviour 5.2 Proposed behaviour 5.2.1 Reversal Documents 6 User Interface changes 7 Design considerations 8 Issues

Document sign-off

Role	Name	Sign-off date
Client	SC Soft
GPS	Salvador Zapata
PM	Richard Morley

Document Status

Date	Author or editor	Description of change	Document status	Document version
June 6th, 2011	Patricia San Juan	Document creation	Draft	1.0
June 8th, 2011	Patricia San Juan	Document ready for review and sign-off	Final	1.0
June 10th, 2011	Patricia San Juan	Document updated - Richard M. feedback	Final	1.0

Objective

There are certain business scenarios which require to use a conversion rate because the document/s currency is not the same as the accounting schema currency/currencies.

There is currently a limitation in Openbravo that foreign currency documents can be entered and posted only using the central conversion rates defined at a system level.

However, there are certain business scenarios where an user of Openbravo may wish (or need) to use a different exchange rate (without changing the central conversion rate). Business scenarios include (but are not limited to):

• Where there is a requirement that the exchange rate implicit in a supplier's purchase invoice document must be used
• Where there is an agreement with a customer that a specific exchange rate will be used in sales invoices (and that rate is different from the system exchange rate prevailing at the date of the sales invoice).

The objective of this document is to describe a solution that will remove the existing limitation by allowing exchange rates to also be defined at a document level. This will enhance the process of entering and posting documents that are in a currency other than the system/accounting currency, allowing the use of an alternative exchange rate at a document leve.

This new feature is going to be developed as part of the Openbravo 3 Core and will be delivered through the corresponding MP.

Process Overview and Assumptions

There are several documents which are/can be involved in the accounting workflow:

1. Invoices (sales/purchase)
2. Payments (Payment In/Out)
3. Financial Account transactions (Withdrawal/Deposit/Reconciliations)

The scope of this design assumes that a payment is made or received in the same currency as the invoice, however invoices&payments could be in a currency other than that of the accounting schema/s.

Having said that, a company could have:

• one currency defined as "base currency" at client level (e.g EUR)
• and as many accounting schemas as required in different currencies (e.g EUR, USD, DKK...)

At any point in time there may be a difference in the Openbravo system exchange rate and the exchange rate used in a document created by a third party on that same date,
therefore it is required to provide the ability (optional) to define exchange rates (document currency -> accounting schema currency exchange rates), for the documents listed below:

• Purchase Invoice
• Sales Invoice
• Payment In
• Payment Out
• Deposit into a Financial Account
• Withdrawal from a Financial Account

It should be possible to define for each document listed above as many exchange rates as accounting schemas.
In practice:

• A company working in a EUR schema (default accounting schema in EUR) needs to enter a USD Purchase Invoice, in this situation the exchange rate USD -> EUR should be entered in the Purchase Invoice, otherwise the system will take the global one setup at System Level.
• If the same company has also an Accounting Schema in USD, in that case the transactional currency is the same as the accounting currency of the schema.
  Then an exchange rate of 1:1 is deduced.
• If the same company has also a third accounting schema in GBP by example, then the system could either use the exchange rate entered in the purchase invoice, if any, otherwise it will take into account the global one setup at system level.

When processing/accounting a document, the system will look:

• first for the applicable exchange rate entered at document level and
• then, and just in case there is no exchange rate at document level, it will look for the coversion rate setup at global/system level in the application path:
  "General Setup // Application // Conversion Rates "
• The system must warn the user in case there is no exchange rate setup at all.

When reversing a document, the system must take into account:

• the exchange rate used while posting the document being reversed.

Finally, it should be possible to either:

1. entering the transactional currency amount (document currency amount) and the domestic currency amount (accounting schema currency amount), so that the exchange rate is deduced
2. or entering the transactional currency amount and the exchange rate, so the domestic currency amount (accounting schema currency amount) is deduced.

Note:
Finally, it is important to remark that:

• withdrawals and deposits are besides recorder in the currency of the Financial Account, which could be in a different currency (e.g USD) than the payment currency(e.g EUR).

  This feature is already covered as it is possible to receive/make payments in a financial account, in a different currency than the financial account currency.

• In the case of not posting either Withdrawals or Deposits but Reconciliations, Reconciliation posting will take into account the corresponding exchange rates setup in the Financial Account.

User Stories

Current behaviour

This section describes the current behaviour of the system where there is no need to vary the exchange rate, that means that the global exchange rate setup at system level is enough.

• Base currency is EUR
• Accounting Schema is in EUR
• Bank account is in EUR
• Purchase Invoice for USD 2.675,00
• Global exchange rate setup at the application path:
  "General Setup // Application // Conversion rates".
  • USD to EUR as (1 USD = 1.31 EUR).
    
  • Valid From = 01-June-2011 - Valid To= 30-June-2011

(This scenario would apply same way to sales).

A purchase invoice is received for USD 2675,00 on June 10th, 2011:

• Purchase Invoice Header:
  • Business Partner = Vendor A
• Purchase Invoice header - "Exchange rates" tab:
  • (Document) Currency = USD
  • Accounting Schema Currency = EUR
  • Exchange rate = 1.31
• Purchase Invoice Line/s:
  • line = 10
  • product = item 1
  • invoiced quantity = 5
  • unit price = 500.00 (USD)
  • net invoice amount = 2.500,00 (USD)
  • tax rate = 7%
  • tax amount = 175 (USD)
  • total invoice amount = 2.675,00 (USD)
• When this purchase invoice is posted dated on June 12nd, 2011 the coversion rate (1 USD = 1.31 EUR) is used because it has no changed.
• User post the invoice. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
Expense	3.275,00		2.500,00 * 1.31
Taxes	229.25		175,00 *1.31
Account payable		3.504,25	2.675,00 * 1.31

• The payment for the invoice is made on June 30th, 2011.
  • User adds payment to the invoice, specifies USD 2.675,00 as payment amount
  • User selects the EUR bank account as the source to make the payment
  • System should show the current exchange rate setup at system level (1 USD = 1.31 EUR) as the payment currency is different than the financial account currency.
  • The expected USD amount using that conversion rate is 3.504,25 EUR
    • User could change the given exchange rate and therefore the system will have to properly adjust the expected USD amount, but there is no need.
  • User process the payment and chooses to automatically withdrawn the amount.
• A new payment is created in the "Payment Out" window.

  As the exchange rate has not changed, there is no need to change it in the "Payment Out" window, "Exchange Rates" tab.

• User post the payment. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
Account Payable	3.504,25		2.675,00 * 1.31
In Transit Payment Account		3.504,25	2.675,00 * 1.31

• System generates a financial transaction for 3.504,25 EUR (2.675,00 USD) to withdraw from the bank account.

  As the exchange rate has not changed, there is no need to change it in the "Financial Account" window, "Exchange Rates" tab.

• User post the withdrawal. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
In Transit Payment Account	3.504,25		2.675,00 * 1.31
Withdrawn Payment Account		3.504,25	2.675,00 * 1.31

This is the simplest scenario as the currency conversion rate is fixed, therefore there are no currency gains/losses to deal with.

Proposed behaviour

This section describe how the system should behave for those cases where there is a need to vary the exchange rate, that means that the global exchange rate setup at system level is not enough.

• Base currency is EUR
• Accounting Schema is in EUR
• Bank account is in EUR
• Purchase Invoice for USD 2.675,00
• Gloval exchange rate at the application path:
  "General Setup // Application // Conversion rates".
  • USD to EUR as (1 USD = 1.31 EUR).
    
  • Valid From = 01-June-2011 - Valid To= 30-June-2011
    

(This scenario would apply same way to sales).

A purchase invoice is received for USD 2675,00 on June 10th, 2011:

• Purchase Invoice Header:
  • Business Partner = Vendor A
• Purchase Invoice header - "Exchange rates" tab:
  • (Document) Currency = USD
  • Accounting Schema Currency = EUR
  • Exchange rate = 1.31
  • Amount (document currency) = 2.675,00
• Purchase Invoice Line/s:
  • line = 10
  • product = item 1
  • invoiced quantity = 5
  • unit price = 500.00 (USD)
  • net invoice amount = 2.500,00 (USD)
  • tax rate = 7%
  • tax amount = 175 (USD)
  • total invoice amount = 2.675,00 (USD)
• When this purchase invoice is posted dated on June 12nd, 2011 the coversion rate has changed to (1 USD = 1.34 EUR).
• User is able to change the exchange rate at the "Exchange rate" tab, as described below:
  • (Document) Currency = USD
  • Accounting Schema Currency = EUR
  • Exchange rate = 1.34
  • Amount (document currency) = 2.675,00
• User post the invoice. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
Expense	3.350,00		2.500,00 * 1.34
Taxes	234,50		175,00 *1.34
Account payable		3.584,50	2.675,00 * 1.34

• The payment for the invoice is made on June 30th, 2011.
  • User adds payment to the invoice, specifies USD 2.675,00 as payment amount
  • User selects the EUR bank account as the source to make the payment
  • System should show the current exchange rate setup at system level (1 USD = 1.31 EUR), as the payment currency is different than the financial account currency.
  • The expected USD amount using that conversion rate is 3.504,25 EUR
  • User changes the give exchange rate to (1 USD = 1.36 EUR)
  • The expected USD amount using the new conversion rate is 3.638,00 EUR
  • User process the payment and chooses to automatically withdrawn the amount.
• A new payment is created in the "Payment Out" window.
• When this payment is posted dated on June 30th, 2011, the conversion rate has changed once more to (1 USD = 1,38 EUR).
• User is able to change the exchange rate at the "Exchange rate" tab, once more, as described below:
  • (Document) Currency = USD
  • Accounting Schema Currency = EUR
  • Exchange rate = 1.38
  • Amount (document currency) = 2.675,00
• User posts the payment. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
Account Payable	3.584,50		2.675,00 * 1.34
Currency Loss	107,00		3.691,50 -3 .584,50
In Transit Payment Account		3.691,50	2.675,00 * 1.38

• System generates a financial transaction for 3.691,50 EUR (2.675,00 USD) to withdraw from the bank account.
• When this withdrawal is posted dated on July 5th, 2011, the conversion rate has changed to (1 USD = 1,30 EUR).
• User is able to change the exchange rate at the "Exchange rate" tab once more as described below:
  • (Document) Currency = USD
  • Accounting Schema Currency = EUR
  • Exchange rate = 1.30
  • Amount (document currency) = 2.675,00.
• User post the withdrawal. The system will show below posting in EUR:

Account	Debit	Credit	Calculation
In Transit Payment Account	3.691,50		2.675,00 * 1.38
Withdrawn Payment Account		3.477,50	2.675,00 * 1.30
Currency Gain		214,00	3.691,50 - 3.477,50

This is the more complex scenario as the currency conversion rate is not fixed, therefore there are currency gains/losses to deal with.

System must take currency gain/loss accounts from the corresponding Accounting Schema, Defaults.
Therefore below "default" ledger accounts under the section "Bank" must be used:

• Bank Revaluation Gain (income account) - must be credited in case of an exchange rate decrease,
• Bank Revaluation Loss (expense account) - must be debited in case of an exchange rate increase.

Reversal Documents

In the case of voiding/reverting an invoice or a payment document, the system should behave as explained below:

Invoice reversal

An invoice already posted can be voided/reversed:

1. by clicking on the "Reactivate" button and the choosing the action "Void". This process is going to create either a Reversed Purchase Invoice", in case of reversing an AP Invoice; or a "Reversed Sales Invoice", in case of reversing an AR Invoice.

   The exchange rate to be used while posting the "Reversed Purchase/Sales Invoice" must be exactly the same one as the one used while posting the AP/AR invoice.

2. by creating either an "AP Credit Memo" or an "AR Credit Memo" and selecting the invoice being reversed in the "Reversed Invoice" tab.

   The exchange rate to be used while posting the "AP/AR Credit Memo" must be exactly the same one as the one used while posting the AP/AR invoice.

   It is out of the scope to reverse two invoices by the same AP/AR Credit Memo, in the case of a diferent exchange rate used while posting each invoice.

   Besides, there should be an invoice to be reversed selected in the "Reversed Invoice" tab, otherwise the exchange rate could be diferent and that should not be the case.

3. by creating either a negative "AP Invoice" or a negative "AR Invoice" and selecting the invoice being reversed in the "Reversed Invoice" tab.

   The exchange rate to be used while posting the negative "AP/AR Invoice" must be exactly the same one as the one used while posting the AP/AR invoice.

   Besides, there should be an invoice to be reversed selected in the "Reversed Invoice" tab, otherwise the exchange rate could be diferent and that should not be the case..

Payment/Withdrawal/Deposit reversal

Above documents can be reversed once posted by cliking on the "Unpost" button.
The exchange rate to be used while un-posting any of above the documents must be exactly the same one as the one used while posting them.
Once reactivated, the process starts again.

User Interface changes

This project implies below user interface changes:

1. Purchase Invoice: A new tab named "Exchange rates" is required, including below three new fields:

   Currency (Invoice currency)

   Accounting Currency (Accounting Schema currency)

   Exchange rate (Document currency -> Accounting Schema currency exchange rate)

   Total Amount (Document currency)

2. Sales Invoice: A new tab named "Exchange rates" is required, including below three new fields:

   Currency (Invoice currency)

   Accounting Currency (Accounting Schema currency)

   Exchange rate (Document currency -> Accounting Schema currency exchange rate)

   Total Amount (Document currency)

3. Payment In: A new tab named "Exchange rates" is required, including below three new fields:

   Currency (Payment currency)

   Accounting Currency (Accounting Schema currency)

   Exchange rate (Document currency -> Accounting Schema currency exchange rate)

   Total Amount (Document currency)

4. Payment Out: A new tab named "Exchange rates" is required, including below three new fields:

   Currency (Payment currency)

   Accounting Currency (Accounting Schema currency)

   Exchange rate (Document currency -> Accounting Schema currency exchange rate)

   Total Amount (Document currency)

5. Financial Account: A new tab named "Exchange rates" is required, including below three new fields:

   Currency (Deposit/Withdrawal currency)

   Accounting Currency (Accounting Schema currency)

   Exchange rate (Document currency -> Accounting Schema currency exchange rate)

   Total Amount (Document currency)

Design considerations

Issues

Please initiate a new discussion thread in the Forum for this project.

Remember that you will need to be logged in to the Openbravo Forge to do this.

All new discussion threads will be added to the Open Items list below by the Project Owner.

When it is agree in the Discussion Forum that it is closed (by the Functional Specification being updated, or the issue being deferred or rejected) then the link will be moved to Closed Items.

Open Items:

Closed Items: