[Trunk] Tax/Base amount on account move line must be calculated

[Frederic Clementi - Camptocamp]

Hello,

Just a bit of context before explaning the issue :

Just before the v7 release we have added few financial constraints to prevent user errors which was leading to a general lack of confidence.
Thanks to these constraints, users have a much better feeling today ( I have just finished 2 projects 'OpenERP finance' v7).

Bug description :

When I post manually an journal entry I can enter the tax amount.
This can lead to severe errors in the tax report since the tax amount can be different from the debit or credit (except if there is a special tax coeficient)

Solution I propose :
This field must be read only, hidden from the move view and calculated this way :
tax amount = absolute value of debit or credit * coef (defined in the tax) .... IF there is a tax code on the move line otherwise is null.

Thanks

Frederic
Camptocamp

[qdp (OpenERP) (qdp)]

i don't think a constraint would be a good idea as they may be use cases where you want a different tax amount than the debit/credit * coeff (python code, fixed amount, child on children...).

on the other hand, maybe we should restrict the tax_amount field use in the manual encoding so that the user always use the tax field instead of filling by the hand tax amount and tax code. I'm not sure, actually... but in all case, this is not a bug, more a enhancement proposal ;-)

[Frederic Clementi - Camptocamp (frederic-clementi)]

Open question to community :

Do you guys, in your country, see any use cases where the tax amount can be different from the debit credit amount of the move line ?

At least, for France or Switzerland I do not see any.

Many thanks

[Normunds (Alistek) (3pm)]

We suffered, the same problem.

But this field sould be allowed to enter manually - in case when OpenERP is not configured to perform it automatically or manual entries should be made. I agree, that at least in EU I do not see the case when it should be different either from debit or credit.
So probably it can be automatically filled from debit or credit and leaving option to change if it is necessary in some other countries.

At the same time I see very useful this field separated for discovering errors, when users enter wrong taxes while creating invoices.

For now it is broken for foreign trade anyway:

At the moment there are situation when it is not possible to calculate this field automatically at all.
For example: when you do an import operations from third countries to EU, it is not possible to calculate this field, because VAT base can be calculated on customs only - e.g. after Import Taxes has been aplied.

In my mentioned case manual entry is not sollution either, there sould be another "amount_untaxed" field implemented - we did it in our localisation.

There are situations, at least in ver. 6.0 (and I believe it isn't ot fixed even o ver. 7.0), when this hits very painfulfy especially dealing with foregn currencies and rates.

Standard situation:
- User created Suplier Invoice (USD) from Purchase Order on lets say February 21th and did not set invoice date, and the currency rate (Feb 21, USD/EUR) and left it in draft state.
- On March 1 user received goods and real invoice, entered missing data (set real invoice date NOTE! Feb 23th!!! Different from invoice creation date!) and accepted it (state Open). In this situation Invoice will generate accounting entries based on currency rate of Feb 23th - thus leading of significant difference of tax_amount and accounting (debit/credit) entries.

We fixed it in our localisation by adding constraint while confirming an invoice when accounting entries doesn't match tax amounts unless manual is set to true along with backport of Akretions's decimal fixes.

Normunds
Alistek Ltd

[qdp (OpenERP) (qdp)]

yes there exists use case where the tax amount is different than the debit or credit
* in Belgium, because we may have taxes that impact several (more than one) tax codes (for base amount or for tax amount), so that it ends in openerp with a line with debit=credit=0 and a tax amount. (and it's not dedicated to Belgium: if i remind correctly in Luxembourg they have that kind of taxes too)
* as i said, if your tax is a fixed amount (not a percentage) (e.g taxe Récupel)

[Frederic Clementi - Camptocamp (frederic-clementi)]

Thanks Normunds for your explainations.

About this 2nd field untaxed amount... I am not familiar with this problematic at all. Can't you simply create a parent taxe with 2 children taxes (one for untaxed vat / one for taxed one ) ?
Because my point is , whenever tax amount is different from Debit or credit... it makes checkings on the taxes report really complex...and risky to validate.

About the wrong tax amount with various exchage rate, I must say that I do not understand the problem
Tax amount is only calculated when invoice is posted. When created by PO, the state is draft (so no accounting entries...and then no tax amount). Unless we work with the module 'account_cancel' which (dangerously and illegally) allows you to re-open a validated invoice. In this case, the problem is account_cancel... not the way the tax amount is calculated. the date of creation is not relevant to compute the vat.
...but maybe I missed something in your explanation

Anyway, does that mean that you could not survive with tax_amount = debit/credit ?

Frederic

[conexus - s.petersen (s.petersen)]

Hello,

I think qdp's approach "always use the tax field instead of filling by the hand tax amount and tax code." would make things easier and "safer" for many new users.

At least until now, I never had a tax where the tax_amount was different from credit/debit.
Couldn't the taxe Récupel also be solved with a parent/children tax configuration?

I understand Normunds case, but I think customs/tolls are another issue, or not?

Btw. how do you manually post Journal Entries in v7 with these kind of bugs being around: https://bugs.launchpad.net/openobject-addons/+bug/1135493

with kind regards,
Sven

[Normunds (Alistek) (3pm)]

Hello!

I checked once more, I agree with Quentin (OpenERP). This is typical situation for us as well for VAT reporting, when credit/debit=0 and tax_amount has value e.g. they do not have the same value.
BUT WE HAVE ONLY cases when credit and debit=0 and if tax_amount is negative for Refund invoices.

What I pointed out there are situations when OpenERP cannot handle taxing and accounting right an cause what you mentioned.

I was not may be clear, so will try to explain in detail, cause this is generic thing:

- First issue is not directly related, but OpenERP cannot handle correct VAT report, when we do not know exact VAT base amount at invoice creation time and it doesn't match to invoice totals, because for import operations VAT base
is calculated not from invoice itself, but unknown value determined by toll procedures.

- Second issue is an example how to get wrong results in taxing and accounting.

Your company currenccy is EUR, you use automatic currency rates import into OpenERP from legitimate authority.

1. Purchase Manager issue Purchase Order 100GBP and Supplier Invoice is generated automatically on 1th March. Supplier Invoice is generated in currency with floating currency rate (GBP, USD, JPY etc.)

Note, that invoice is generated automatically and is in DRAFT state (NO INVOICE DATE SET)
In this case:

* Invoice is 100GPB and VAT should be accounted (cause GB is EU member)
* account.invoice.tax object is generated and currency conversion is performed on 1th March (date of invoice creation!!!)
* field base_amount is 115.36 EUR (based on 1th March currency rate!!!)

Total invoice sum in foreign currency (GBP) is correct.

2. After one week Purchase Manager receives goods and finish encoding the invoice.
Note, that supplier shipped goods NOT on 1th March, but let's say on 4th March and issued invoice dated 4th March

In this case:
* Purchase manager enters valid invoice date. 4th March
* Purchase manager enters invoice number in description
* Purchase manager confirms Supplier Invoice by pressing "Validate" Button
* account.invoice.tax object remains INTACT, because it is already calculated !!!
* acount.move.lines are created (currency conversion for accounting move lines performed by invoice date 4th March!!!)
* field base_amount is 115.36 EUR (REMAINS THE SAME AS ON MARCH 1th)
* account.move.lines debit or credit entries are calculated from base_amount 116.05 (AS ON MARCH 4th)

The result is difference 0.69 EUR in tax base_amount between tax reports and accunting on 100GPB invoice.

We solved this situation in OpenERP ver.6.0 by adding constraints of catching such differences while validating the invoice, but allowing if specific circumstances are met for correct VAT output.
When you press "Compute Taxes" base_amount and related tax fields are recalculated and become correct.

Normunds
Alistek Ltd

[Stéphane Bidoul (Acsone) (sbi)]

Hello Frederic,

In our case (Belgium), we have situations where we have a tax_amount on moves with debit=credit=0. This is required to declare the tax base in two different tax codes.

Other than that, we have here and there a one cent difference on some supplier invoices.

-sbi

[Carlos Liebana (carlos-liebana)]

+1 Stéphane Bidoul (Acsone) (sbi)

In Spain we also have this kind of situation so no automatic calculation can be made in manual entries.

[Frederic Clementi - Camptocamp (frederic-clementi)]

Ok, we will do that in a module then

Many thanks for your explainations, guys

Frederic

[Goran Kliska (gkliska)]

Hello:

For Storno accounting Tax/Base amount is always == (debit + credit)
           Still trying to find the case where it is not.
           Maybe for contra check is abs(tax_amount) = abs(debit + credit) ???

As extreme measure I'am using trigger on some databases:
"""
        cr.execute('''
                CREATE OR REPLACE FUNCTION debit_credit2tax_amount() RETURNS trigger AS
                $debit_credit2tax_amount$
                BEGIN
                   NEW.tax_amount := CASE when NEW.tax_code_id is not null
                                           then coalesce(NEW.credit, 0.00)+coalesce(NEW.debit, 0.00)
                                           else 0.00
                                      END;
                   RETURN NEW;
                END;
                $debit_credit2tax_amount$ LANGUAGE plpgsql;

                ALTER FUNCTION debit_credit2tax_amount() OWNER TO %s;

                DROP TRIGGER IF EXISTS move_line_tax_amount ON account_move_line;
                CREATE TRIGGER move_line_tax_amount BEFORE INSERT OR UPDATE ON account_move_line
                    FOR EACH ROW EXECUTE PROCEDURE debit_credit2tax_amount();
        '''%(tools.config['db_user'],))

"""

From account_storno module :

class account_move_line(osv.osv):
    _inherit = "account.move.line"
    #Original constraints
    #_sql_constraints = [
    #('credit_debit1', 'CHECK (credit*debit=0)', 'Wrong credit or debit value in accounting entry !'),
    #('credit_debit2', 'CHECK (credit+debit>=0)', 'Wrong credit or debit value in accounting entry !'),
    #]

    # credit_debit1 is valid constraint. Clear message
    # credit_debit2 is replaced with dummy constraint that is always true.

    _sql_constraints = [
        ('credit_debit1', 'CHECK (credit*debit=0)', 'Wrong credit or debit value in accounting entry! Either credit or debit must be 0.00.'),
        ('credit_debit2', 'CHECK (abs(credit+debit)>=0)', 'Wrong credit or debit value in accounting entry !'),
    ]

    def _check_contra_minus(self, cr, uid, ids, context=None):
        """ This is to restore credit_debit2 check functionality, for contra journals
        """
        for l in self.browse(cr, uid, ids, context=context):
            if l.journal_id.posting_policy == 'contra':
                if not (l.debit * l.credit) >= 0.0:
                    return False
        return True

    def _check_storno_tax(self, cr, uid, ids, context=None):
        """For Storno accounting Tax/Base amount is always == (debit + credit)
           Still trying to find the case where it is not.
           Maybe for contra check is abs(tax_amount) = abs(debit + credit) ???
        """
        for l in self.browse(cr, uid, ids, context=context):
            if l.journal_id.posting_policy == 'storno' and l.tax_code_id:
                if float_compare((l.debit + l.credit), l.tax_amount, precision_digits = 2) != 0: #precision_digits=dp.get_precision('Account')[1])
                    return False
        return True

    _constraints = [
        (_check_contra_minus, _('Negative credit or debit amount is not allowed for "contra" journal policy.'), ['journal_id']),
        (_check_storno_tax,...

[Goran Kliska (gkliska)]

Hello,

I am sorry for previous copy-paste comment.

tax_amount is fine example of data duplication.
As always, data duplication anomalies leads to strange "cure the symptoms" solutions like:
 - read only, hidden, calculated,
 - hidden,trigger
 - divergent constraints in Vauxoo and other localization modules

Solution I propose :
This field must be depreciated.
Accounting practice have to be improved.
Tax reports based on credit/debit (+tax code properties for sign).

But, before all that, posting of taxes needs improvement.
Specifically:
  Partly/fully nondeductible taxes (Vat and/or Profit tax):
     Extracted from Croatian localization. Far from Community modules standards yet!
      http://bazaar.launchpad.net/~gkliska/addons-sp/slobodni_addons_61/files/head:/account_tax_nondeductable/
     Italian localization
  Vat deductible on Payment:
      Again mix of Italian and Cratian localization as reference
      http://bazaar.launchpad.net/~gkliska/addons-sp/slobodni_addons_61/files/head:/account_tax_payment/
  VAT on Import
      There was video on c2c web explaining the technique.
  Invoice with 0.0€ or Gifts
      Here, (by law) we have to post +-market value and pay the VAT on it.
      AFAIK it is certainly not bad practice to book in 2 lines (100€ market value, 25% VAT):

Thanks,
PS Just say NO to data duplications in OpenERP! :)

[Normunds (Alistek) (3pm)]

This is an example when debit != tax_amount. This is valid entry for Latvian VAT accounting.

See attached screenshot.

I suggest not to hurry for making changes to existing solution, because it provides necessary functionality.