[PATCH] account module quite severe issues with closing fiscal years

[The Loeki]

We have identified two major issues with the wizard_fiscalyear_close in the account module in any 5+ version of OpenERP:

1) As noted in the wizard_fiscalyear_close.py (~line 145):
                #TODO: this query could be improved in order to work if there is more than 2 open FY
                # a.period_id IN ('+fy2_period_set+') is the problematic clause
2) When a reconcile crosses years AND the reconciliation in the new years contains N entries, each reconciled entry in the old year will get reported N times instead of once.

These bugs will quite possibly crap up closing a year, and we consider them therefore to be quite important.

[The Loeki (the-loeki)]

Of course the reverse is also true, where an invoice will be older than the closing year but be paid in a later than the closing year, it'll get booked incorrectly.
Uploading new patches. These are, as of yet, untested, but since appears to be little hope of any of the devs actually responding to this issue I'll upload 'em anyway for now and report when it works, so anyone interested in actually correctly closing a financial year can use them.

[The Loeki (the-loeki)]

verified working against 5.0 for all situations

* one invoice in closing year (or before), multiple payments in new year (or after) now books correctly
* invoices made in closing year (or before) but paid in new year (or after) now book correctly

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

By the way, a note for users from Spain:

A new 'spanish fiscal year closing' wizard that supersede the 'wizard_fiscalyear_close' has been developed on the Spanish localization: http://bazaar.launchpad.net/~openerp-spain-team/openerp-spain/5.0/files/head%3A/l10n_es/l10n_ES_cierre_ejercicio/
We strongly suggest using it for closing fiscal years on companies that use the spanish chart of accounts.

(Here you have a tutorial about closing fiscal years with the spanish localization wizard: http://www.pexego.es/blog/cierre-del-ejercicio-en-openerp)

---

Maybe the accounting experts want to take a look to that localized wizard to get some ideas for the 5.2/6.0 versions of the fiscal year closing wizard. The current 5.0 wizard is a bit crappy...

[The Loeki (the-loeki)]

dude! You completely rewrote the entire wizard!? Would you care to explain the specific reasons for diverting from the default (though up until now broken) way, and where this one is better?
Is it generally applicable or really only for Spanish charts?

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

Yep Loeki, we rewrote it from scratch. Why? Cause the fiscalyear closing wizard was not really applicable on Spain as it was implemented:

  - The OpenERP default wizard just creates an opening move, where it moves the balances/detail/unreconciled amounts of accounts that need to be closed to a centralized counterpart. In Spain we must create instead:

        + Loss&Profit regularization moves (dated Dec 31th): Moves all the balances of (non-view) profit an losses accounts into a 'total fiscal year profit' account. All the P&L accounts must have balance 0 after this. (This is the only part that the OpenERP fiscalyear closing wizard would cover)
               This is used to calculate taxes based on the yearly profit (the 'total fiscal year profit' account balance).

        + A closing move (dated Dec 31th): Moves all the balances (we don't do 'detailed' nor 'non conciled' closings) of all the (non-view) accounts against each other (just adds them), without using a centralized counterpart. The move is self-reconciled. This move should be balanced, and all the accounts should have balance 0 afterwards.
                This is used mainly for checking that there are not accounting errors.

        + An opening move (dated Jan 1st): After the previous move all the accounts have balance 0 (we just cleared out all the accounts), we must 'reopen' them doing the inverse move of the closing one (reverse it), so in the end we preserve the old balances (but the L&P accounts remain at 0). This move is also self-reconciled.
                This is used to preserve the old amounts.

All this closing moves are usually created in March (but use the given dates), so the opening period may have moves already.

But we also did lots of improvements:

  - Validates your accounting: Checks whether all the moves are balanced, whether all the moves have valid dates (on Spain the period and date must match), checks whether all the moves on the fiscal year have been closed...

  - Its much faster that the OpenERP wizard (just does a "create" per full move VS the default wizard doing a create per move line; remember that each create means constraint checking...).

  - It doesn't time out. A progress screen is shown instead. <== This might be used on the standard wizard, no more timeout messages when you have a lot of accounts and the user waiting while wondering whether is it still running or it did hang.

  - Makes easy to keep track of the end-of-year operations (you can review easily those moves it just created) and guides the user.

  - It closes the fiscal year and periods when you confirm the wizard. No need to use a separate wizard.

  - Its easy to cancel everything. Just press the Cancel button, and the wizard reopens the fiscal year and periods, and unreconciles, cancels and deletes the moves. Easy and clean.

So yes, this wizard was designed for Spain, but it has some interesting ideas that might be used on the base wizard :)

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hello Experts,

Since Jan 10, there was a change in the functionality that All the fiscal years older than current one will be considered as "TO be CLOSED" fiscal years without regard what fiscal year you asked to close.

The patch I provided here, tends to close the selected fiscal year entries only.

Please share your views.

Thanks.

[The Loeki (the-loeki)]

The assumption that only move.lines from the year to be closed would need to be closed seems a right one.

However, as specified in the initial report, that's not what this patch or bug is about, and your patch would not /seem/ to prevent the bug from happening.

The first bug relates to the fact that an invoice could be made in a previous year, and/or be paid in the/a next year. Neither will reconcile correctly.
The second bug relates to invoices that are made in the current (or, after my patch, previous years) and be paid in several parts in the next year(s).

The current code would generate a move.line for each payment, resulting in a horrible mess.

[qdp (OpenERP) (qdp)]

Hello The Loeki,

actually i checked and cannot found any bug related to the fiscalyear closing. I tested:
* unpaid invoices of 2009 are correctly passed to 2010 and even to 2011 if still unpaid
* unpaid invoices of 2009 that are partially paid (in several payments) in 2010 are correctly passed to 2011
* unpaid invoices of 2009 that are totally paid (in several payments) in 2010 are correctly NOT passed to 2011

So please can you provide us a more complete test case, with comparison between expected result and result computed by openERP?

Thanks in advance,
Quentin

[The Loeki (the-loeki)]

It's been over two months by now, so my memory is a bit vague, but IIRC, the two scenario's we encountered were:

* invoices are all paid & reconciled, all fiscal years are open.

* invoice in 2002, with several payments in 2003. Close 2002 and each invoice account_move_line in 2002 will appear N payments in the closing account_move. This is simply, though not very elegantly, fixed by adding the DISTINCT-clause, as seen in the patch.
Try the SQL against a db in these conditions and you will see this happening for yourself without actually closing anything.

* invoice in 2002, payment in 2004. When closing 2002 this won't work because payment won't be picked up, when subsequently closing 2003, it still won't work because the invoice won't be picked up.
This is fixed by including /all/ next, previous periods respectively in the associated period sets, as seen in the patch.

[invitu (invitu)]

Hello

The process that Borja describes on comment #8 is exactly the same as in France.

Regards

[Olivier Dony (Odoo) (odo-openerp)]

Jay, Quentin,

We have checked this issue and patch provided by 'The Loeki', and they seem valid (according to our quick review with fp). The SELECT DISTINCT one seems obvious if you look carefully at the query.
Could you check this patch and the described testcase thoroughly and implement the appropriate fix in both 5.0 and trunk?

Thanks!

[Jay Vora (Serpent Consulting Services) (jayvora)]

Olivier,

I agree with Loeki's patch, but I had problems with what has been done here http://bazaar.launchpad.net/~openerp/openobject-addons/5.0/revision/2534.

After this revision, it will never consider the supplied fiscal year, and will consider all the PAST fiscal years.

Check it please. Read Comment #9 too.

Thanks.

[Jan Verlaan (jan-verlaan)]

Please, Jay and The Loekie, can you have a virtual meeting, call with each other to discus towards a solution.
Right now year-end closing isn't working with payments that fall in two booking years and it will change/mangle data in a already closed year!!

Resulting in getting a mess in already closed years, and proberly nobody will encounter this error anymore. It is set to high priority but this bugreport looks like it doesn't. It is open from 03-03-2010.

[qdp (OpenERP) (qdp)]

Hello all,

the fix is commited in revision http://bazaar.launchpad.net/~openerp/openobject-addons/5.0/revision/2758 (i attach the patch)

After some investigation, it appears that the patch proposed by the Loeki wasn't deffering the invoice of 2002 in 2004 if the payment was still not made in 2003. This should work fine now

Thanks for your participation

[The Loeki (the-loeki)]

qdp,

That's probably because you took my first patch. The second one I published solved that exact problem as well, as is noted in the comment.

But no matter. Thanks for finally getting it in.