Projects:Accounting Report Review/Functional Documentation

Contents 1 Accounting Report Review 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.4.1 A) Report names 1.3.4.2 B) Currencies 1.3.4.3 C) Multiple Languages Support 1.3.4.4 D) Organization filters 1.3.4.5 E) Delete conversion rate 1.3.4.6 F) Posting document conversion 1.4 Technical Requirements 1.5 Non-Functional Requirements 1.6 Open Discussion Items 1.7 Closed Discussion Items

Accounting Report Review

Overview

Purpose

Every report in the financial management is going to be revised, checked and fixed, so:

A) Report name is correct (ie: balance sheet has been changed to trial balance. See 1884). This issue is going to be fixed by David Abragimov and Paul Ponomarev.

B) Sums are coherent (ie: currency conversions are correctly done where needed. See 4274).

C) Multiple languages are supported (ie: general accounting reports do not support it. See 2202, 2204, 3397).

D) The available filters are enough (ie: some reports have no Org filter. See 313, 2117, 3247, 3251, 3249 and 3256).

E) When deleting a conversion rate, display a message avoiding it if this conversion rate is in use.

F) When posting a document in a non-system currency, if no conversion is available, identify this error.

Scope

Reports involved in this review:

• Under Financial Management > Receivables & Payables > Analysis Tools
  • Bank Report
  • Cash Report
  • Cashflow Forecast
  • Payment Report
  • Payment Aging Balance
  • Invoice Taxes Report
  • Payment Tracker
• Under Financial Management > Accounting > Analysis Tools
  • Balance sheet and P&L structure
  • Trial Balance Report
  • General Ledger Report
  • General Ledger Journal
  • Not Posted Transaction Report
  • User Defined Accounting Report
  • Tax Report
  • Withholding Report
  • Cash Flow Statement
  • Balance sheet and P&L structure Setup
  • User Defined Accounting Report Setup
  • Tax Report Setup
  • Generate Cash Flow Statement
  • Accounting Transaction Details

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

Parts of this project will make use of the modifications performed in the currency management in the project Multi Currency Reports Review project.

Part of the technical documentation will be taken from Multi Currency Reports Review project.

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

Financial controllers, accountants and managers of companies which have transactions in more than one currency: buying to providers, or selling to customers located in regions with a different currency.

Business process definition

Currency conversion rates:

1. Define the conversion rate between euros and dollars, different from 1:1. Let's say, 2:1 (2€=$1).

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

Transactions:

1. Create a Sales Invoice in Sales Management>Transactions>Sales Invoice>Header, with dollars price list. Add some lines to it with a tax rate different to 0%. Save line, and complete document. '$D'(=[D/2]€) will be the tax amount obtained in the invoice.
2. Create a Sales Invoice in Sales Management>Transactions>Sales Invoice>Header, with euros price list. Add some lines to it with a tax rate different to 0%. Save the line, and complete the document. 'E€'(=$2E) will be the tax amount obtained in the invoice.

Report:

1. Create a Tax Report with one line in it, showing the sales taxes applied in the invoices.
2. Show the tax report, choosing euros as currency: report must show a total of (E+D/2)€, and not a (E+D)€ amount.
3. Show the tax report, choosing dollars as currency: report must show a total of $(D+2E), and not a $(E+D) amount.

User Stories

User Story 1

The multinational company Conservas El Gordo & Co Ltd., located in London and trading in pounds, has been using Openbravo ERP for five years. The Jordan company Amman Ltd. becomes a new provider of this company, and sends purchase invoices in Jordan Dinars currency. This currency is created in the application, conversions created and updated periodically, and when obtaining financial reports, any of the existing currencies could be chosen to show the report.

User Story 2

The Italian company Il Bambino Ltd. is a prosperous family business using Openbravo ERP. The owners are interested in selling the company, and are receiving offers from different Italian competitors. Then a Russian multinational company wants to make an offer for Il Bambino Ltd, and need to have some financial reports in Russian Rubles, so company's administrator is able to understand reports obtained.

Russian translation is imported, and Rubles currency is activated. The actual currency conversion is set, and reports obtained and showed to Russian buyers.

Functional requirements based on business processes

A) Report names

Change the names and re-arrange accounting reports.

Num	Requirement	Importance	Status
A.1	Change the following report names and re-arrange them: Old Name => New Name Report Annual Certification => Withholding Report Report Not Posted => Not Posted Transaction Report General Accounting Report => Balance sheet and P&L structure Balance Sheet => Trial Balance General Ledger Report => General Ledger Report General Ledger Journal => General Ledger Journal Create Accounting Report => User Defined Accounting Report Accounting Report => User Defined Accounting Report Setup General Account Report Setup => Balance sheet and P&L structure Setup Accounting Transaction Details => Accounting Transaction Details Create Tax Report => Tax Report Tax Report => Tax Report Setup Create Cash Flow Statement => Generate Cash Flow Statement Report Cash Flow => Cash Flow Statement Final position in the menu: Balance sheet and P&L structure Trial Balance General Ledger Report General Ledger Journal Not Posted Transaction Report User Defined Accounting Report Tax Report Withholding Report Cash Flow Statement Balance sheet and P&L structure Setup User Defined Accounting Report Setup Tax Report Setup Generate Cash Flow Statement Accounting Transaction Details	Must have	Complete

B) Currencies

Filter windows

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

Num	Requirement	Importance	Status
B.1	A drop-down list displaying all currency ISO codes (see General Setup >> Application >> Currency).	Must have	Not Started
B.2	User's accounting schema currency must appear selected by default.	Must have	Not Started
B.3	Currency drop-down list have to be mandatory.	Must have	Not Started
B.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	Not Started

Reports

In all the financial 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
B.5	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	Not Started
B.6	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	Not Started
B.7	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	Not Started
B.8	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	Not Started
B.9	Currency conversions must respect the price precision of the converted currency (see General Setup >> Application >> Currency).	Must have	Not Started
B.10	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. Make a previous select in the report in order to see if any of the transactions can not be converted properly. If so, display a message 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	Not Started
B.11	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	Not Started

C) Multiple Languages Support

For each table that contains data to be shown in an accounting report, if the _TRL table does not exists, it will be created.

Num	Requirement	Importance	Status
C.1	Create _TRL tables when necesssary. The C_ELEMENTVALUE_TRL table exists, but is not referenced in the accounting reports (see 2377). Use the values within C_ELEMENTVALUE_TRL to translate accounting elements through reports.	Must have	Not Started
C.2	Fulfill the table when importing a translation	Must have	Not Started
C.3	Include the table in the exporting tables when exporting a translation.	Must have	Not Started
C.4	When a string of the translated table is requested, give the translated string, and not the original one.	Must have	Not Started

D) Organization filters

Num	Requirement	Importance	Status
D.1	Add an organization filter to: Bank Report Cash Report Payment Report Not Posted Transaction Report Payment Tracker	Must have	Not Started

E) Delete conversion rate

Num	Requirement	Importance	Status
E.1	Avoid that a conversion rate in use in a posted transaction could be modified.	Must have	Not Started

F) Posting document conversion

Num	Requirement	Importance	Status
F.1	When posting a transaction that does not have a conversion rate defined for the System currency, display a message informing about it and abort the posting.	Must have	Not Started

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

• If a document in a non-system currency has been posted, applying certain currency conversion rate, it will not be possible to modify or even remove that conversion rate (see E) Delete conversion rate section).

• Regarding the point B.10 of the section B) Currencies, if no rate is available to calculate currency conversion, report must display an advise pop-up window saying that for the given currency, date, client and organization there is no conversion rate defined.