View source | View content page | Page history | Printable version   

Projects:Multi Currency Reports Review/Functional Specification

Contents

Multi Currency Reports Review - Functional Specifications

Overview

Purpose

At present, reports in Openbravo ERP are not taking into account multi currency. This means that reports are, for instance, adding transactions in dollars with transactions in euros. This is common to all reports, so all of them (excepting Accounting ones that go in a different project) should be reviewed and fixed.

All non-accounting reports within Openbravo ERP will be reviewed in order to support multi-currency capability. This means:

  1. In the filter window, add the capability to select the currency one wants the report to be shown. This filter will be mandatory. To ensure this, a Javascript check has to be added before launching the report.
  2. In the report itself:
    1. Add the currency symbol ($, €, etc...) to:
      1. The price of each transaction.
      2. The amount of each transaction.
    2. Add a column that will display the amount of each transaction converted to the currency selected. This is the column that will be used to calculate the total amounts. The header of this column will be labeled with the ISO code and the symbol of the converted currency.
    3. A function must ensure that the currency symbol is set in the correct side of the amount, e.g., $ 10 and 25 €.

Scope

The following reports (inside Analysis Tools) have to be reviewed:

Important note: if a bug external to this project is found in any of the reports, see Constraints section.

Design Considerations

Assumptions

This project assumes that all the reports listed inside Scope section are working correctly. If this is not the case, see Constraints section.


Dependencies

This project is related to, but do not depends on, the Accounting report review project, more precisely to feature request 0004274.


Constraints

A bug in any of the reports listed inside Scope section could stop this project. The procedure in this case will be:

  1. Report the bug in Openbravo's issues tracker.
  2. Correct the bug in the development trunk, not in the project branch.
  3. Merge the trunk (that will include the bug fix) to the project branch.
  4. Continue with the project development.


Functional Requirements

User roles & profiles

Managers of any company
A manager of any company purchasing/selling goods/services in countries with different currencies will need to pay/charge with different currencies, hence, report expenses/incomes in different currencies.


Business process definition

Price lists:

  1. Create a sales price list in dollars with its price list version and its product prices (Master Data Management || Pricing || Price List)
  2. Create a sales price list in euros with its price list version and its product prices (Master Data Management || Pricing || Price List)

Business partners:

  1. Assign the price list in dollars to one customer.
  2. Assign the price list in euros to another customer.

Transactions:

  1. Create a transaction, e.g., create and complete a Sale Order with some lines for the customer with dollars price list (Sales Management || Transactions || Sales Order || Header)
  2. Create a transaction, e.g., create and complete a Sale Order with some lines for the customer with euros price list (Sales Management || Transactions || Sales Order || Header)

Report:

  1. Report these transactions, e.g., through Sales Dimensional Report (Sales Management || Analysis Tools || Sales Dimensional Report)


User stories

Kaoruko, the sales manager of a Japanese company, wants to report hers first two sales: one made to an USA customer (in $) and one made to a French customer (in €). Since she works in the Tokyo headquarters, she wants to report these two sales in Japanese Yens (¥). Using Sales Dimensional Report, she selects JPY (Japanese Yen) in currency drop-down list, selects Product dimension and launches the report. Report shows her:


Functional requirements based on business processes

To achieve the desired functionalities the following elements need to be added.

Currency window

Currency window (General Setup || Application || Currency) must have a check that indicates if the currency symbol goes to the right side of the amount, price, price/m2...


Num Requirement Importance Status
1.1 C_Currency table must include a non mandatory column ISSYMBOLRIGHTSIDE to register if the currency symbol goes at the right side. Default value will be no (N). Must have Complete
1.2 Currency window must include a new check-box field named Currency symbol at the right. This check-box will be un-checked by default and will not be mandatory. Must have Complete
1.3 C_Currency table will have ISSYMBOLRIGHTSIDE column value set correctly for the more used currencies. Must have Complete


C_CURRENCY_SYMBOL function

C_CURRENCY_SYMBOL function will give back a string with the amount and the currency symbol placed at the right place (at the right or at the left side of the amount) or only the symbol depending on a parameter.


Num Requirement Importance Status
2.1 Create a C_Currency_Symbol function with three parameters (c_currency_id, amount-price-price/m2 and onlysymbol) that will:
  • Read ISSYMBOLRIGHTSIDE value from C_Currency table for the c_currency_id given.
  • Form and return a string like Price Currency Symbol (if ISSYMBOLRIGHTSIDE value is Y) or Currency Symbol Price (if ISSYMBOLRIGHTSIDE value is N).
  • If onlysymbol parameter is equal to Y, it will only return the Currency Symbol.
Must have Complete


C_CURRENCY_ISOSYM function

C_CURRENCY_ISOSYM function will give back a string made of the combination of currency's ISO code and symbol in the type: (ISOCODE-SYMBOL).


Num Requirement Importance Status
3.1 Create a C_Currency_IsoSym function with one parameter (c_currency_id) that will:
  • Read ISO_CODE and CURSYMBOL values from C_Currency table for the c_currency_id given.
  • Form and return a string like: (ISOCODE-SYMBOL).
Must have Complete


Filter windows

In all the filter windows of the reports, apart from the existing filters (Start Date, Ending Date, Business Partner, etc...) add a new one named Currency.


Num Requirement Importance Status
4.1 A drop-down list displaying all currency ISO codes (see General Setup >> Application >> Currency). Must have Complete
4.2 Client's base currency must appear selected by default. Must have Complete
4.3 Currency drop-down list has to be mandatory. Must have Complete
4.4 When launching the report (by pressing HTML format or PDF format), a Javascript function must check that a currency has been selected. Must have Complete


Reports

In all the reports, add its currency symbol to current amounts, prices, prices/m2, etc... and a column with the amount converted to the selected currency.


Num Requirement Importance Status
5.1 The currency symbol at the correct side of the amount, price, price/m2, etc... using C_Currency_Symbol function (see General Setup >> Application >> Currency). When it is not possible to form a string between the amount and the currency symbol (e.g., the amount is needed as a Big Decimal number for the calculation of totals), currency symbol will be placed at the right side of the amount. Must have Complete
5.2 A column showing the converted amount. The header of this column will be labeled with the ISO code and the symbol of the selected currency in the form Amount (ISO-Symbol). This requirement only applies to Amount field (not to Price, Price/m2, etc... fields). If the report does not have amount field, this requirement does not apply. When there is no place enough for putting an extra column (e.g., in comparative reports), only converted amount column will be shown. Must have Complete
5.3 A cell showing the total converted amount as the summation of converted amounts. Sometimes, the total is made at different levels: Project, Business Partner, Product, etc... If the report does not have amount field, this requirement does not apply. In many cases, this requirement means to change the current total cell by the converted total cell. Must have Complete
5.4 Currency conversions must be done with
  • the currency,
  • the date,
  • the client,
  • and the organization

of the transaction. For instance, if an order line is dated on 27-01-2006, the currency conversion must be done using this date. In the case that the transaction line date is not mandatory, the date of the transaction header has to be taken (for instance, with expense sheet lines). Otherwise, the conversion date can be taken as today (now() function).

Must have Complete
5.5 Currency conversions must respect the price precision of the converted currency (see General Setup >> Application >> Currency). Must have Complete
5.6 If there is no conversion rate for the selected currency in any of the transactions, display a message in the filter window or in the pop-up window. For generated filter windows, display the message directly in the report, if possible.
  • Modify C_CURRENCY_CONVERT in order to display a message if there is no conversion rate for certain transaction.
  • Catch this database error message and display it indicating that for the currency from, the currency to, the transaction date, the client and the organization given there is no conversion rate, e.g., There is no conversion rate defined from EUR to CUP for date '02-02-2007', Client 'BigBazaar' and Organization 'Main'.
Must have Complete
5.7 Function used for the currency conversion must be C_Currency_Convert(p_amount, p_curfrom_id, p_curto_id, p_convdate, p_ratetype, p_client_id, p_org_id) where:
  • p_amount is the amount to convert.
  • p_curfrom_id is the id of the transaction currency (100 = USD, 102 = EUR. etc...).
  • p_curto_id is the id of the currency selected in the report filter (converted currency).
  • p_convdate is the date of the conversion (transaction date). If the transaction date is null, take the date of the transaction header. If the transaction header is null, take now() as date.
  • p_ratetype is the type of conversion that will always be null.
  • p_client_id is the id of the client of the transaction.
  • p_org_id is the id of the organization of the transaction.

To test the function:

  1. Logged as System Administrator, setup a conversion rates from USD to EUR and from EUR to USD (General Setup >> Application >> Conversion Rates).
  2. Execute: SELECT C_Currency_Convert(20000, '100', '102', TO_DATE('01-01-2008'), null, '1000000', '1000000') FROM DUAL;
Must have Complete

User Interface Mockups

Currency window inside General Setup || Application || Currency:

Currency-window.png


Sales Dimensional Report inside Sales Management || Analysis Tools || Sales Dimensional Report:

RepSalesDimFilt.png
RepSalesDimRep.png

Purchase Order Report inside Procurement Management || Analysis Tools || Purchase Order Report:

PurchOrdFilt.png
PurchOrdRep.png


Technical Requirements

Application Dictionary elements as well as all type of files involved in Model - View - Controller (MVC) stucture (*.html, *.xml, *.java and *.xsql files) will have to be modified. Besides, Jasper reports (*.jrxml files) will also be modified.


Non-Functional Requirements

Some conversion rates can be taken from...

... and set into General Setup >> Application >> Conversion Rates.


Open Discussion Items

Closed Discussion Items

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Multi_Currency_Reports_Review/Functional_Specification"

This page has been accessed 10,807 times. This page was last modified on 8 June 2012, at 05:28. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.