Financial Report Minimal Requierement for 5.0

[Joël Grand-Guillaume @ camptocamp]

Hi,

This is a Bug report on Financial reporting of OpenERP that has to be fixed for 5.0 in order to be "usable" in a real accounting context. All the following comments has been discussed with Fabien Pinckaers, Luc Maurer, Quentin De Paoli, and me.

We decided to fix those minimal requirements (fixes or improvements) in the stable and all "nice-to-have" for the 6.0. This bug report :https://bugs.launchpad.net/openobject-addons/+bug/551630 will be for 6.0.

1. All financial reports
---------------------------

Wizard changes for balance sheet & general ledger & journal printing & partner ledger (qdp)
----------------------------------------------------------------------------------------------------------------------------------------
Filter :
Filters have to be refactorized and period filters isn't working.

We need those filter :
- 'start date end date' and 'start period and end period'.
- Period really based on period and not on dates => actually the filter by period does not consider period, but date of periods…. Select must be done on periode_id IN (ids).

IMPROVE SPEED (al)
-----------------------
100'000 Account move line should be printed out in less than the OpenERP-Client timeout !

Especially in :
- the General Ledger
- the Aged Partner Balance

--> AL is working on this.

For 6.0 version (qdp's team)
-------------------------
Wizard:
- add an option to have a new column “currency balance amount” => amount and currency code => check box
- add comparison with other fiscal year

Presentation:
- Add “currency balance amount” and “currency code” columns if related check box is active

3.General ledger
---------------------
---------------------

Mandatory/bugs => 5.0.11
----------------------------------
Presentation:
- Replace “balance amount” by “cumulative balance amount” and make a cumulative total for each line

For 6.0 version
-------------------
- add “with opening balance" => to have a new line with the sum of all entries that are before the begin date/periode used for the filter
- Use the “Display history” check box => add a new select field (see quentin notes)

4.Partner balance
---------------------------
---------------------------

Mandatory/bugs => 5.0.11
----------------------------------
Wizard:
- Set the default value for “fiscal year” = actual fiscal year (year that contain the actual date).
- add "balance date" => by default = actual date
- add new option for the moves selection : “partner with movement / balance <> 0 / all” => by default = “balance <>0”
- add a new check box “with starting balance” => by default “true” that report the starting balance
- Make it also available on the partner (automatic selection of the partner) => important to have good informations (take care if multi-selection to select all)

Presentation :
- same layout than general ledger and account balance

Calculation:
- selection of client vs supplier accounts based on the internal account type
- add a new computed field “rec_max_date” on account_move_line which is False if not fully reconciled => if fully reconciles = the max date of all the entries with same reconcile number of this entry line => to know when it has been paid because you have to know what was exactly the situation at a given time (!!! Do not use the reconciliation date !!!)

The value are the SUM of move_line that
- account internal type = client and/or supplier (depending the choice made by the user)
- and journal/period <> “End of year entry journal” (see fiscal year)
- and move_line.date = into the selected fiscal year
- and move_line.date >= begin date/period and move_line.date <= end date/period
- if “with starting balance” = true => or move_line.date < begin date/period and (rec_max_date = false or rec_max_date > “balance date”)
- sum of debit, credit, debit-credit => total by account and by partners

5.Partner ledger
---------------------
---------------------

Mandatory/bugs => 5.0.11
---------------------------
Wizard:

- By default the value for “fiscal year” = actual fiscal year (year that contain the actual date).
- add "balance date" (situation at date…)=> by default = actual date
- add new option for the moves selection “partner with movement / balance <> 0 / all => by default = “balance <>0”
- add a new check box “with starting balance” => by default “true”
Make it also available on the partner (automatic selection of the partner) => important to have good informations (take care if multi-selection to select all)

Presentation :
- same layout than general ledger and account balance

Calculation :

The value are the LIST of move_line that
- account internal type = client and/or supplier (depending the choice made by the user)
- and journal/period <> “End of year entry journal” (see fiscal year)
- and move_line.date = into the selected fiscal year
- and move_line.date >= begin date/period and move_line.date <= end date/period
- if “with starting balance” = true => or move_line.date < begin date/period and (rec_max_date = false or rec_max_date > “balance date”)
- if “Include reconcile entries” = false => and (rec_max_date = false or rec_max_date > “balance date”)
- group by partners

For 6.0 version
--------------------

- Add sort option (date, entry number, amount, due date)
- Add choice “open amount only / paid amount only / all entries” to replace the “Include reconcile entries”

6.Aged partner ledger
---------------------------
---------------------------

Mandatory/bugs => 5.0.11
-----------------------------------
Wizard:

- By default the value for “fiscal year” = actual fiscal year (year that contain the actual date).
- Add filter option like all other financial reports
- Add a new check box “with starting balance” => by default “true”
- Rename “Sart of period" with "balance date" to be consistent with other reports.

Calculation:

It should be based on “Maturity date” (not “effective date”). If the maturity date is empty => use the effective date.

The value are SUM of move_line that:

- account internal type = client and/or supplier (depending the choice made by the user)
- and journal/period <> “End of year entry journal” (see fiscal year)
- and move_line.date = into the selected fiscal year
- and move_line.date >= begin date/period and move_line.date <= end date/period
- if “with starting balance” = true => or move_line.date < begin date/period and (rec_max_date = false or rec_max_date > “balance date”)
- group by partners and by group of Maturity date

Presentation:

The last column of the report must show the sum from last period to infinite, do not limit it with another period min or max. Example:

- Today: 30-60, 60-90, 90-120, 120-150

- Correct is : 30-60, 60-90, 90-120, 120+

[Borja López Soilán (NeoPolus) (borjals)]

Just a comment. You may want to take a look to the account_financial_reports and account_balance_reports from the extra-addons.

The first one adds some financial reports with some of the features you mentioned. For example it adds a full balance report “with opening balance" or a cumulative general ledger. Some images:

  Full Balance: http://bit.ly/9DiAYe
  Invoice List: http://bit.ly/b0E91d
  Cumulated General Ledger: http://bit.ly/dbLYtd
  Journal: http://bit.ly/bFmCtM
  Menu: http://bit.ly/dhacEh

  (sorry, some images are in Spanish, but I think they are a give a good preview of the features)

The second one, it's a reporting engine for balance-like reports comparing fiscal concepts between two fiscal years. It works with editable templates, so it's easy to add new reports. Currently we are using it on the Spanish Localization (https://launchpad.net/openerp-spain) to print the official balance and profit&losses reports. Some images:

  Wizard: http://bit.ly/cg2SDh
  Generated data (editable!): http://bit.ly/aUe8Wf
  Printed report: http://bit.ly/aIMDYs

[Ferdinand (office-chricar)]

Hello - you may want to have a look into

*stable_5.0-extra-addons/account_financial_report
- a lot of good and useful reports are there

* https://code.launchpad.net/~openerp-commiter/openobject-addons/chricar_price_unit
account/account.py
- all necessary values for a fiscal years and period are provided as function fields in one central place - and correct initial balances are reported regardless if the fiscal year is closed or not ( returned data are correct - the code is still a bit messy)

I have discussed this already with Borja / Plexago

[Borja López Soilán (NeoPolus) (borjals)]

Yeah, I think some of Ferdinand ideas (like having precomputed debit/credit/balance totals by period for faster reports; or like having a 'initial balance' on the account object) should be implemented in the 6.0 version :)

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

Is there any news around this huge problem !? We approach from the deadline for the next 5.0.11 and I get no feed-back on this. As we'll need to test them, it'll be good to have 1 or 2 days for that purpose at least...

Thank you for keeping us informed.

Best regards,

Joël

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Priority :

1. General Ledger & Account Balance

2. Partner Reports

[Joël Grand-Guillaume @ camptocamp (jgrandguillaume-c2c)]

Hi,

Please, make the backport for the financial report of v6.0 into v5.0.15. Refer to that bug report : https://bugs.launchpad.net/openobject-addons/+bug/551630

This would be so great to have them working for this milestones !

Let us know if we can help,

Regards,

Joël

[Mohammed (gaafer5)]

hi,

It is not out there in V 5.0.15

[Frederic Clementi - Camptocamp (frederic-clementi)]

Please see result of new tests done on Trunk version. Thanks for responding quickly as agreed with Fabien for backport to V5.

Frederic

[Frederic Clementi - Camptocamp (frederic-clementi)]

AGED BALANCE REPORT TEST (in trunk)

I could test the aged balance report today and I must say that the report is not working properly...

1 - I personally think it is a shame that calendar days are not taken into consideration. I explain myself :
- an invoice due on the 31/01/2010 and not paid on the 30/04/2010 is 89 days overdue and appear in column "60-90"
- an invoice due on the 30/06/2010 and not paid on the 30/09/2010 is 92 days overdue and appear in column "90-120"
Both invoices are 3 months overdue and will not be shown as urgent to get paid.

2 - much more important - invoices, credit notes and payment are shown in this report separately. The report does not take into account partial reconcile. In the attached document you see on the 2nd line the partner called "cust2usd" and invoice in column "+120" for 3058.90F and the partial payment in column "0-30" for -436.20F. What is expected : col- "+120" amount : 2622.70F.

3 - A very usefull (and expected) option in wizard is missing (for main for year end legal audit) : possibility to exclude invoices after a date.
i.e: we are in apr-2010 and we need to know all invoices, at the date of the 31/12/09, which are still unpaid today . We need an aged balance report dated to the 31/12/09 excluding all invoices after that date but taking all payment and credit notes between the 31/12/09 and today.

Thank you for making correction ASAP - at least the point 2 for backport to V5.

We know that you are focus on the V6 and we understand but we remind you that we agreed to backport all financial reports into extra-addons V5 BEFORE THE 30-NOV-2010 so we need feedback from you on this comment and my previous one under this same bug report.

[Mohammed (gaafer5)]

Dears,

I am really confused, by "backport all financial reports into extra-addons V5", you mean the P&L and balance sheet reports would be in a separated module in extra-addons branch and this module would be available before the 30th of November?

Please help

[qdp (OpenERP) (qdp)]

Hi Frederic,

thanks for the feedback, i'll give you my comments as soon as possible, thanks for your understanding.

@Mohammed: OpenERP will create a module in the extra-addons repository that will contains the latest accounting reports from v6 (thus we need to fix them all first!), except for P&L and B&S: because those module weren't existing in v5 and because they need a modificaiton within the object model...

Thanks

[qdp (OpenERP) (qdp)]

Frederic, i do not understand the point #2 you made on your last comment... Can you provide me some an example of accounting entries with the expected result and the obtained result?

Thanks

[qdp (OpenERP) (qdp)]

Hi Frederic,

here are my review of your feedback, i replied in red in line of your document.

As a summary, things we will improve:
*your proposal for IB is validated and already commited to trunk
*general rule for debit/credit/balance columns is that they should be able to display 1.000.000.000,00 € on the same line
*cumulative balance have to be done for the remaining reports

Also i remind you that i need more information on the point #2 of the aged partner ledger

[Frederic Clementi - Camptocamp (frederic-clementi)]

Thanks Quentin, we really appreciate your prompt responsiveness (we really do want to loose customers because of these reports)

Regarding the Aged balance report, I have described in the attached document a scenario hoping this is clear enough now..

I will have a look at the improvement you already made and give you my feedback...

Frederic

[qdp (OpenERP) (qdp)]

Hello again Frederic,

i checked your document: very well explained, thanks!

Unfortunately i fear we won't be able to fix easily the problem #1 you raised: it seems to me a little tricky to do it in such a way to not slow the pdf generation. Let us delay that untill the v6 is out.

For the problem #2, i confirm: it's a bug.. i checked in the code and do not yet understand how it's possible that it's excluded from the report, as we are already checking on the reconciliation date > selected date... more investigation is needed.

Thanks

[qdp (OpenERP) (qdp)]

i solved that probelm with the payment and reconciliation that comes later that selected day. Actually, it will now only count the lines with a date less or equal to the selected date, and also check if the reconciliation is made after the selected date. It's commited in branch addons3 and will be soon in main addons branch.

Work is still under progress for
*general rule for debit/credit/balance columns is that they should be able to display 1.000.000.000,00 € on the same line
*cumulative balance have to be done for the remaining reports

Do not hesitate to test more and more those reports in the following days :-)
Thanks

[Mohammed (gaafer5)]

Dear Quentin,

Thank you for your response, Can you be more specific about those changes so that we can do it ourselves considering the number of customers keep asking from those reports (P&L,BS).

regards

--- On Mon, 11/15/10, qdp (OpenERP) <email address hidden> wrote:

From: qdp (OpenERP) <email address hidden>
Subject: [Openerp-expert-accounting] [Bug 583666] Re: Financial Report Minimal Requierement for 5.0
To: <email address hidden>
Date: Monday, November 15, 2010, 11:36 AM

Hi Frederic,

thanks for the feedback, i'll give you my comments as soon as possible,
thanks for your understanding.

@Mohammed: OpenERP will create a module in the extra-addons repository
that will contains the latest accounting reports from v6 (thus we need
to fix them all first!), except for P&L and B&S: because those module
weren't existing in v5 and because they need a modificaiton within the
object model...

Thanks

--
Financial Report Minimal Requierement for 5.0
https://bugs.launchpad.net/bugs/583666
You received this bug notification because you are a member of OpenERP
Accounting Experts, which is a direct subscriber.

Status in OpenObject Addons Modules: Confirmed

Bug description:
Hi,

This is a Bug report on Financial reporting of OpenERP that has to be fixed for 5.0 in order to be "usable" in a real accounting context. All the following comments has been discussed with Fabien Pinckaers, Luc Maurer, Quentin De Paoli,  and me.

We decided to fix those minimal requirements (fixes or improvements) in the stable and all "nice-to-have" for the 6.0. This bug report :https://bugs.launchpad.net/openobject-addons/+bug/551630 will be for 6.0.

1. All financial reports
---------------------------

Wizard changes for balance sheet & general ledger & journal printing & partner ledger (qdp)
----------------------------------------------------------------------------------------------------------------------------------------
Filter :
Filters have to be refactorized and period filters isn't working.

We need those filter :
- 'start date end date' and 'start period and end period'.
- Period really based on period and not on dates => actually the filter by period does not consider period, but date of periods…. Select must be done on periode_id IN (ids).

IMPROVE SPEED (al)
-----------------------
100'000 Account move line should be printed out in less than the OpenERP-Client timeout !

Especially in :
- the General Ledger
- the Aged Partner Balance

--> AL is working on this.

For 6.0 version (qdp's team)
-------------------------
Wizard:
- add an option to have a new column “currency balance amount” => amount and currency code => check box
- add comparison with other fiscal year

Presentation:
- Add “currency balance amount” and “currency code” columns if related check box is active

3.General ledger
---------------------
---------------------

Mandatory/bugs  => 5.0.11
----------------------------------
Presentation:
- Replace “balance amount” by “cumulative balance amount” and make a cumulative total for each line

For 6.0 version
-------------------
- add “with opening balance" => to have a new line with the sum of all entries tha...

[Frederic Clementi - Camptocamp (frederic-clementi)]

Quentin,

addons3 is this branch? : lp:~openerp-dev/openobject-addons/trunk-dev-addons3

Can I re-test here?

[qdp (OpenERP) (qdp)]

it's that branch, but now it's already in the trunk ;-)

[Mohammed (gaafer5)]

Dear qdp

Can you please give me a brief description about those modification in order to be able to implement them in V5 because I need those two reports desperately in V5.

Thanks,

--- On Mon, 11/15/10, qdp (OpenERP) <email address hidden> wrote:

From: qdp (OpenERP) <email address hidden>
Subject: [Openerp-expert-accounting] [Bug 583666] Re: Financial Report Minimal Requierement for 5.0
To: <email address hidden>
Date: Monday, November 15, 2010, 11:36 AM

Hi Frederic,

thanks for the feedback, i'll give you my comments as soon as possible,
thanks for your understanding.

@Mohammed: OpenERP will create a module in the extra-addons repository
that will contains the latest accounting reports from v6 (thus we need
to fix them all first!), except for P&L and B&S: because those module
weren't existing in v5 and because they need a modificaiton within the
object model...

Thanks

--
Financial Report Minimal Requierement for 5.0
https://bugs.launchpad.net/bugs/583666
You received this bug notification because you are a member of OpenERP
Accounting Experts, which is a direct subscriber.

Status in OpenObject Addons Modules: Confirmed

Bug description:
Hi,

This is a Bug report on Financial reporting of OpenERP that has to be fixed for 5.0 in order to be "usable" in a real accounting context. All the following comments has been discussed with Fabien Pinckaers, Luc Maurer, Quentin De Paoli,  and me.

We decided to fix those minimal requirements (fixes or improvements) in the stable and all "nice-to-have" for the 6.0. This bug report :https://bugs.launchpad.net/openobject-addons/+bug/551630 will be for 6.0.

1. All financial reports
---------------------------

Wizard changes for balance sheet & general ledger & journal printing & partner ledger (qdp)
----------------------------------------------------------------------------------------------------------------------------------------
Filter :
Filters have to be refactorized and period filters isn't working.

We need those filter :
- 'start date end date' and 'start period and end period'.
- Period really based on period and not on dates => actually the filter by period does not consider period, but date of periods…. Select must be done on periode_id IN (ids).

IMPROVE SPEED (al)
-----------------------
100'000 Account move line should be printed out in less than the OpenERP-Client timeout !

Especially in :
- the General Ledger
- the Aged Partner Balance

--> AL is working on this.

For 6.0 version (qdp's team)
-------------------------
Wizard:
- add an option to have a new column “currency balance amount” => amount and currency code => check box
- add comparison with other fiscal year

Presentation:
- Add “currency balance amount” and “currency code” columns if related check box is active

3.General ledger
---------------------
---------------------

Mandatory/bugs  => 5.0.11
----------------------------------
Presentation:
- Replace “balance amount” by “cumulative balance amount” and make a cumulative total for each line

For 6.0 version
-------------------
- add “with opening balance" => to have a new line with the sum of all entries that are before the begin ...

[qdp (OpenERP) (qdp)]

Hello Mohammed,

you need to add the field report_type in the class account_account, and after that make sure that your chart of account is properly configured with good value for each account. Not sure it will be enough....

Regards,
Quentin

[Frederic Clementi - Camptocamp (frederic-clementi)]

Quentin,

1 - Bad news, we have generated massively 30000 invoices (not paid) in a database in order to test reports performance and we have launched the GL report...after 1 hour waiting , we gave up and stop the process....I think GL and partner ledger report will not be usable for some customers (and 30000 invoices is not that big).

Don't you think we should rather recreate GL report and partner ledger reports (other reports are fine performance wise) using webkit. Nicolas Bessi, here, told me it is relatively quick to do - if the problem come from XML only.

What do you think?

2 - About Aged balance report.... any progress about our problem #1 (see comment #14)

Thanks

[qdp (OpenERP) (qdp)]

Hello again Frederic,

1. yes i checked the speed performance last week and sadely seen that the speed improvement we lately made was missing (has been rollebacked) but it's already back in the report_dev branch and will be soon merged into trunk (i'll try to do it by today so that you can check again). So i don't think it's necessary to change every reports right now. My concerns are more about the correct computation and figures..

2. mmh sorry if i was not clear: we do not plan to fix it for v6. It seems really tricky to do and we don't have enough time to do it correctly.

Thanks

[qdp (OpenERP) (qdp)]

The backport of v6 financial reports on the v5 is now available here http://bazaar.launchpad.net/~openerp-commiter/openobject-addons/trunk-extra-addons/files/head%3A/account_v6_reports_backport.
Installing this module will replace the existing version of the main accounting reports with more accurate and more user friendly ones.

So i'm closing this bug which is related to v5 only. If you found new bugs in the report of v6, please create a new bug report.

Thank you,
Quentin

[Sebastien LANGE - http://www.Syleam.fr (alnslang)]

Hello,

Thanks for this work. I propose to push this module in
lp:openobject-addons/extra-5.0 with access rules.

What do you think ?

Best regards,
--
Sébastien LANGE
Dirigeant associé
SYLEAM Info Services
Gold Partner Open ERP
27 Avenue Jean Mantelet
61000 Alençon
Tél : [phone]

Le 16 décembre 2010 17:14, qdp (OpenERP) <email address hidden> a écrit :
> The backport of v6 financial reports on the v5 is now available here http://bazaar.launchpad.net/~openerp-commiter/openobject-addons/trunk-extra-addons/files/head%3A/account_v6_reports_backport.
> Installing this module will replace the existing version of the main accounting reports with more accurate and more user friendly ones.
>
> So i'm closing this bug which is related to v5 only. If you found new
> bugs in the report of v6, please create a new bug report.
>
> Thank you,
> Quentin
>
> ** Changed in: openobject-addons
>       Status: Confirmed => Fix Released

********************************************************************************
Ce message et toutes les pièces jointes (ci-après le "message") sont établis à
l'intention exclusive de ses destinataires et sont confidentiels. Si vous
recevez ce message par erreur, merci de le détruire et d'en avertir
immédiatement l'expéditeur. Toute utilisation de ce message non conforme à sa
destination, toute diffusion ou toute publication, totale ou partielle, est
interdite, sauf autorisation expresse. L'internet ne permettant pas d'assurer
l'intégrité de ce message, SYLEAM décline toute responsabilité au titre de ce
message, dans l'hypothèse où il aurait été modifié.

This message and any attachments (the "message") is intended solely for the
addressees and is confidential. If you receive this message in error, please
delete it and immediately notify the sender. Any use not in accord with its
purpose, any dissemination or disclosure, either whole or partial, is prohibited
except formal approval. The internet can not guarantee the integrity of this
message. SYLEAM shall not therefore be liable for the message if modified.
********************************************************************************