Projects:Multi Currency Reports Review/Functional Specification

Contents 1 Multi Currency Reports Review - Functional Specifications 1.1 Overview 1.1.1 Purpose 1.1.2 Scope 1.2 Design Considerations 1.2.1 Assumptions 1.2.2 Dependencies 1.2.3 Constraints 1.3 Functional Requirements 1.3.1 User roles & profiles 1.3.2 Business process definition 1.3.3 User stories 1.3.4 Functional requirements based on business processes 1.3.5 User Interface Mockups 1.4 Technical Requirements 1.5 Non-Functional Requirements 1.6 Open Discussion Items 1.7 Closed Discussion Items

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:

• Inside Procurement Management:
  • Purchase Dimensional Report
  • Material Receipts Dimensional Report
  • Purchase Invoice Dimensional Report
  • Purchase Order Report
  • Vendor Invoice Report
• Inside Warehouse Management:
  • Shipper Report
  • Valued Stock Report
  • Invoice Consignment Report
  • Report Pareto Product
• Inside Production Management:
  • Standard Costs Report
  • Production Cost Report
• Inside Sales Management:
  • Sales Dimensional Report
  • Shipments Dimensional Report
  • Discount Invoice Report
  • Sales Order Returns Dimensional Report
  • Sales Order Report
  • Invoiced Sales Order Report
  • Orders Awaiting Invoice Report
  • Delivered Sales Order Report
  • Invoice Detail - Jasper
  • Customer Invoice Report
  • Sales Invoice Dimensional Report
  • Sales Report by Partner and Product
• In Project & Service Management:
  • Project Profitability
  • Service Project Report
  • Expense Report

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:

• The product line amounts in dollars and euros of the sales order lines.
• The converted amounts in yens.
• The total amount in yens.

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: Logged as System Administrator, setup a conversion rates from USD to EUR and from EUR to USD (General Setup >> Application >> Conversion Rates). 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:

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

• Filter window

• Report

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

• Filter window

• Report

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...

• http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
• http://currencysource.ez-cdn.com/USD.xml

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

Open Discussion Items

Closed Discussion Items