Projects:Multi Currency Reports Review/Functional Specification
Multi Currency Reports Review - Functional Specifications
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:
- In the report itself:
- Add the currency symbol ($, €, etc...) to:
- The price of each transaction.
- The amount of each transaction.
- 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.
- A function must ensure that the currency symbol is set in the correct side of the amount, e.g., $ 10 and 25 €.
- Add the currency symbol ($, €, etc...) to:
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.
This project assumes that all the reports listed inside Scope section are working correctly. If this is not the case, see Constraints section.
This project is related to, but do not depends on, the Accounting report review project, more precisely to feature request 0004274.
A bug in any of the reports listed inside Scope section could stop this project. The procedure in this case will be:
- Report the bug in Openbravo's issues tracker.
- Correct the bug in the development trunk, not in the project branch.
- Merge the trunk (that will include the bug fix) to the project branch.
- Continue with the project development.
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
- Create a sales price list in dollars with its price list version and its product prices (Master Data Management || Pricing || Price List)
- Create a sales price list in euros with its price list version and its product prices (Master Data Management || Pricing || Price List)
- Assign the price list in dollars to one customer.
- Assign the price list in euros to another customer.
- 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)
- 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 these transactions, e.g., through Sales Dimensional Report (Sales Management || Analysis Tools || Sales Dimensional Report)
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 (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...
|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 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.
|2.1|| Create a C_Currency_Symbol function with three parameters (c_currency_id, amount-price-price/m2 and onlysymbol) that will:
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).
|3.1|| Create a C_Currency_IsoSym function with one parameter (c_currency_id) that will:
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.
|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|
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.
|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
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).
|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.
|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:
To test the function:
User Interface Mockups
Currency window inside General Setup || Application || Currency:
Sales Dimensional Report inside Sales Management || Analysis Tools || Sales Dimensional Report:
- Filter window
Purchase Order Report inside Procurement Management || Analysis Tools || Purchase Order Report:
- Filter window
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.
Some conversion rates can be taken from...
... and set into General Setup >> Application >> Conversion Rates.