Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0037010Openbravo ERP09. Financial managementpublic2017-10-05 13:062018-01-08 18:21
maite 
AtulOpenbravo 
urgentmajoralways
closedfixed 
5
 
3.0PR18Q1 
dmiguelez
Core
No
0037010: Wrong invoice's accounting entry when several "not deductible" taxes are used
"Not deductible" taxes means that product's expense account will be used instead of tax's account. In case several "not deductible" taxes are defined in same invoice document, balance is not properly calculated causing wrong accounting entry details
1. Create Purchase Invoice for Bebidas Alegres
2. Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 21%
3. Add line for "Cerveza Ale" product, unit=1, price=193.25, tax=Adquisiciones IVA 10%
4. Add line for "Cerveza Ale" product, unit=1, price=37.67, tax=Adquisiciones IVA 4%
5. Navigate to these 3 tax rates and set them as "Tax Not Deductible"
6. Process Invoice and post it. Realize that 2 lines for Product Expense account have been generated when only once should be done. Moreover if you access to entry's Detail you can verify that several lines have been calculated
No tags attached.
Issue History
2017-10-05 13:06maiteNew Issue
2017-10-05 13:06maiteAssigned To => Triage Finance
2017-10-05 13:06maiteModules => Core
2017-10-05 13:06maiteResolution time => 1508968800
2017-10-05 13:06maiteTriggers an Emergency Pack => No
2017-10-05 13:07maiteIssue Monitored: networkb
2017-10-06 13:11dmiguelezAssigned ToTriage Finance => AtulOpenbravo
2017-10-09 12:59AtulOpenbravoStatusnew => scheduled
2017-10-09 13:02AtulOpenbravoNote Added: 0099826
2017-10-09 13:02AtulOpenbravoNote Edited: 0099826bug_revision_view_page.php?bugnote_id=0099826#r16046
2017-10-09 13:09AtulOpenbravoNote Added: 0099827
2017-10-09 19:25AtulOpenbravoNote Added: 0099834
2017-10-09 19:30AtulOpenbravoNote Added: 0099835
2017-10-11 19:13AtulOpenbravoNote Added: 0099873
2017-10-13 12:49psanjuanNote Added: 0099902
2017-10-13 12:50psanjuanNote Edited: 0099902bug_revision_view_page.php?bugnote_id=0099902#r16077
2017-10-13 12:51psanjuanNote Edited: 0099902bug_revision_view_page.php?bugnote_id=0099902#r16078
2017-10-13 12:53psanjuanNote Added: 0099903
2017-10-13 12:54psanjuanNote Edited: 0099903bug_revision_view_page.php?bugnote_id=0099903#r16080
2017-10-13 12:55psanjuanNote Added: 0099904
2017-10-13 12:55psanjuanNote Edited: 0099903bug_revision_view_page.php?bugnote_id=0099903#r16081
2017-10-13 12:58psanjuanNote Edited: 0099904bug_revision_view_page.php?bugnote_id=0099904#r16083
2017-10-13 13:04psanjuanNote Added: 0099905
2017-10-13 13:17psanjuanNote Edited: 0099902bug_revision_view_page.php?bugnote_id=0099902#r16084
2017-10-13 13:23psanjuanNote Edited: 0099902bug_revision_view_page.php?bugnote_id=0099902#r16085
2017-10-13 13:29psanjuanNote Edited: 0099903bug_revision_view_page.php?bugnote_id=0099903#r16086
2017-10-13 13:34psanjuanNote Edited: 0099903bug_revision_view_page.php?bugnote_id=0099903#r16087
2017-10-13 13:44psanjuanNote Edited: 0099904bug_revision_view_page.php?bugnote_id=0099904#r16088
2017-10-13 14:02psanjuanNote Edited: 0099904bug_revision_view_page.php?bugnote_id=0099904#r16089
2017-10-13 14:02psanjuanNote Edited: 0099903bug_revision_view_page.php?bugnote_id=0099903#r16090
2017-10-13 14:02psanjuanNote Edited: 0099904bug_revision_view_page.php?bugnote_id=0099904#r16091
2017-10-13 14:11psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16093
2017-10-13 14:16psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16094
2017-10-13 14:26psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16095
2017-10-13 14:27psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16096
2017-10-13 14:28psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16097
2017-10-13 14:33psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16098
2017-10-13 14:37psanjuanNote Edited: 0099905bug_revision_view_page.php?bugnote_id=0099905#r16099
2017-10-13 14:50psanjuanNote Added: 0099908
2017-10-23 11:19hgbotCheckin
2017-10-23 11:19hgbotNote Added: 0099987
2017-10-23 11:19hgbotStatusscheduled => resolved
2017-10-23 11:19hgbotResolutionopen => fixed
2017-10-23 11:19hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/75838b0fd78755bd53a1abe9b26d659592a12c0a [^]
2017-10-23 11:20dmiguelezReview Assigned To => dmiguelez
2017-10-23 11:20dmiguelezNote Added: 0099988
2017-10-23 11:20dmiguelezStatusresolved => closed
2018-01-03 12:38hudsonbotCheckin
2018-01-03 12:38hudsonbotNote Added: 0101340
2018-01-08 18:21dmiguelezFixed in Version => 3.0PR18Q1

Notes
(0099826)
AtulOpenbravo   
2017-10-09 13:02   
Test Plan
- Login as F&B International Group Admin
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org.
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 21%
- Add line for "Cerveza Ale" product, unit=1, price=193.25, tax=Adquisiciones IVA 10%
- Add line for "Cerveza Ale" product, unit=1, price=37.67, tax=Adquisiciones IVA 4%
- Navigate to these 3 tax rates and set them as "Tax Not Deductible".
- Process Invoice and post it. Realize that only 1 line is generated for Product Expense account.

(0099827)
AtulOpenbravo   
2017-10-09 13:09   
Test Plan
- Login as F&B International Group Admin
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org.
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 21% (make sure that Not Deductible flag is set as Yes for this tax rate).
- Process Invoice and post it. Realize that only one line is generated for Product Expense account with Line Net Amount + Tax Amount. No line is created for tax account.

Test Plan
- Login as F&B International Group Admin
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org.
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 16% (make sure that Not Deductible flag is set as No for this tax rate).
- Process Invoice and post it. Realize that one line is generated for Product Expense account and one line is created for tax account.
(0099834)
AtulOpenbravo   
2017-10-09 19:25   
Test Plan
- Login as F&B International Group Admin
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org, with document type as AP Credit Memo
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 21%
- Add line for "Cerveza Ale" product, unit=1, price=193.25, tax=Adquisiciones IVA 10%
- Add line for "Cerveza Ale" product, unit=1, price=37.67, tax=Adquisiciones IVA 4%
- Navigate to these 3 tax rates and set them as "Tax Not Deductible".
- Process Invoice and post it. Realize that only 1 line is generated for Product Expense account.
- Realize that in Journal Entries Detail Report, there are 6 lines for product expense account.
  one each for invoice line line net amount, one each for invoice tax tax amount.

Similar test performed OK for document with type Reversed Purchase Invoice.
(0099835)
AtulOpenbravo   
2017-10-09 19:30   
Test Plan
- Login as F&B International Group Admin
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org, with document type as AP Credit Memo
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Adquisiciones IVA 21%
- Add line for "Cerveza Ale" product, unit=1, price=193.25, tax=Adquisiciones IVA 16%
- Add line for "Cerveza Ale" product, unit=1, price=37.67, tax=Adquisiciones IVA 4%
- Navigate to these 2 tax rates (21% and 4%) and set them as "Tax Not Deductible". For 16% tax rate "Tax Not Deductible" = No
- Process Invoice and post it. Realize that 2 line are generated for Product Expense account.
- In details realize that tax account line is only for tax amount for tax rate 16% and
  2 Product Expenses account line for Tax amount for 21% and 4%,
  3 Product Expenses account lines for invoice lines line net amount
(0099873)
AtulOpenbravo   
2017-10-11 19:13   
Test Plan
- Login as F&B International Group Admin
- Create a summary level tax rate as "Test Tax Group" in F&B Espana Org.
- Create a tax rate "Test Tax A" with rate 10%, parent = Test Tax Group, tax not deductible = Yes
- Create another tax rate "Test Tax B" with rate 5%, parent = Test Tax Group, tax not deductible = Yes
- Create Purchase Invoice for Bebidas Alegres in F&B Espana - Region Norte Org, with document type as AP Credit Memo
- Add line for "Cerveza Ale" product, unit=1, price=64, tax=Test Tax Group
- Add line for "Cerveza Ale" product, unit=1, price=193.25, tax=Test Tax Group
- Add line for "Cerveza Ale" product, unit=1, price=37.67, tax=Test Tax Group
- Process Invoice and post it. Realize that 9 line are generated for Product Expense account.
- In details, realize there are 6 Product Expenses account line for Tax amount, 3 each for Test Tax A and Test Tax B for respective invoice line taxes and 3 Product Expenses account lines for invoice lines line net amount
Total 9 Lines.
(0099902)
psanjuan   
2017-10-13 12:49   
(edited on: 2017-10-13 13:23)
Test Case 1 - all tax rate deductible

Without making any configuration change in purchase tax rates, go to Purchase Invoice window and create a new one. Select Bebidas Alegres BP.
Go to lines and create on line with below information:
Product = Cerveza Ale 0.5L
Invoiced Quantity = 100
Tax = Adquisiciones IVA 21%
Enter another line with the tax rate Adquisiciones IVA 10% and Adquisiciones IVA 4%
Save.
Go to Tax tab and sum Tax Amount = 111.81 and Taxable Amount = 1418.50
Complete and post the invoice.
Verify below accounting entries are shown:
1418.50 (60000) expense account - DEBIT
111.81 (47200) Tax account - DEBIT
1530.31 (40000) Account payables - CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 2 - all tax rate not deductible

Repeat same test case by setting below tax rates as "Tax Not Deductible" = Yes
Adquisiciones IVA 21%
Adquisiciones IVA 10%
Adquisiciones IVA 4%
Verity that the total gross amount of the invoice is for instance 1284.64.
Post the invoice. Verify below posting is shown:
1284.64 (60000) Expense Account DEBIT
1284.64 (40000) Account Payable CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 3 - some tax rate not deductible

Repeat same test case by setting below tax rates as "Tax Not Deductible" = Yes
Adquisiciones IVA 21%
Adquisiciones IVA 10%

Set Adquisiciones IVA 4% as "Tax Not Deductible = No"

Verity that the total gross amount of the invoice is for instance 1202.36.
Complete the invoice.
Go to tax tab and check that Aquisiciones IVA 4% tax amount = 21.30

Post the invoice. Verify below posting is shown:

1181.06(60000) Expense Account DEBIT
21.30 (47200) Tax Account DEBIT
1202.63 (40000) Account Payable CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 4 - some tax rate not deductible

Repeat same test case by setting below tax rates as "Tax Not Deductible" = Yes
Adquisiciones IVA 21%

Set Adquisiciones IVA 4% and Adquisiciones IVA 10% as "Tax Not Deductible = No"

Verity that the total gross amount of the invoice is for instance 1105.56
Complete the invoice.
Go to tax tab and check that Aquisiciones IVA 4% tax amount = 21.30
Go to tax tab and check that Aquisiciones IVA 10% tax amount = 35.20

Post the invoice. Verify below posting is shown:

1049.06(60000) Expense Account DEBIT
56.50 (47200) Tax Account DEBIT
1105.56(40000) Account Payable CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

(0099903)
psanjuan   
2017-10-13 12:53   
(edited on: 2017-10-13 14:02)
Test Case 5 - all tax rate deductible, including an exempt tax rate

Without making any configuration change in purchase tax rates, go to Purchase Invoice window and create a new one. Select Bebidas Alegres BP.
Go to lines and create on line with below information:
Product = Cerveza Ale 0.5L
Invoiced Quantity = 100
Tax = Adquisiciones IVA 21%
Enter another line with the tax rate Adquisiciones IVA 10% and Compras exentas
Save.
Go to Tax tab and sum Tax Amount = 81.81 and Taxable Amount = 703.70
Complete and post the invoice.
Verify below accounting entries are shown:
703.70 (60000) expense account - DEBIT
81.81 (47200) Tax account - DEBIT
785.51(40000) Account payables - CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the counting entries are the same but opposite.

Test Case 6 - some tax rate deductible, including an exempt tax rate

Repeat some test case as above by setting:
Aquisiciones IVA 21% as Tax Not Deductible = No and Adquisiciones IVA 10% as Tax Not Deductible = Yes.

Go to Tax tab and check that tax amount related to "Adquisiciones IVA 21" = 28.56 and tax amount related to "Aquisiciones IVA 10" = 26.40 while total gross amount of the invoice = 1317.46

Post the invoice and verify below posting entries are shown:

1288.90 (60000) expense account - DEBIT
28.56 (47200) Tax account - DEBIT
1317.46(40000) Account payables - CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

(0099904)
psanjuan   
2017-10-13 12:55   
(edited on: 2017-10-13 14:02)
Test Case 7 - all tax rate deductible (intracommunity tax rate)

Without making any configuration change in purchase tax rates, go to Purchase Invoice window and create a new one. Select Bebidas Alegres BP.
Go to lines and create on line with below information:
Product = Cerveza Ale 0.5L
Invoiced Quantity = 100
Tax = Adquisiciones Intracomunitarias 21%
Enter another line with the tax rate Adquisiciones intracomunitarias 10% and Adquisiciones intracomunitarias 4%
Save.
Go to Tax tab and sum Tax Amount for each posive/negative tax rate = 74.32 and Taxable Amount = 752
Complete and post the invoice.
Verify below accounting entries are shown:
752.00 (60000) expense account - DEBIT
74.32 (47200) Tax account - DEBIT
74.32 (47700) Tax account - CREDIT
752.00 (40000) Account payables - CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 8 - all tax rate deductible (intracommunity tax rate) including a non deductible tax rate.

Repeat same test case as above by adding a new line related to the tax rate "Prestación servicios nacional 21" set as Tax Not Deductible = Yes.

Go to lines and create on line with below information:
Product = Cerveza Ale 0.5L
Invoiced Quantity = 100
Tax = Adquisiciones Intracomunitarias 21%
Enter another line
Product = Cerveza Larger 0.5L
Invoice Quantity = 150
Tax = Adquisiciones Intracomunitarias 10%
Enter another line
Product = Vino Blanco 0.75L
Invoice Quantity = 250
Tax = Adquisiciones Intracomunitarias 4%
Enter another line
Product = Vino Rosado 0.75L
Invoice Quantity = 100
Tax = Prestación servicios nacional 21% (no deductible)

Save and complete the invoice. Check that total gross amount of the invoice is 1741.25 (1287.50 (Intracommunity tax base) + 375.00 (services tax base) + 78.75 (tax amount no deductible)

Go to Tax tab and sum Tax Amount for each posive/negative tax rate but Prestaciones Servicios Nacional 21% = 90.46 and Taxable Amount = 1287.50

Check that the tax amount related to "Prestaciones Servicios Nacional 21%" = 78.75

Post the invoice.

Verify below accounting entries are shown:
1741.25 (60000) expense account - DEBIT
90.46 (47200) Tax account - DEBIT
90.46 (47700) Tax account - CREDIT
1741.25(40000) Account payables - CREDIT

(0099905)
psanjuan   
2017-10-13 13:04   
(edited on: 2017-10-13 14:37)
Test Case 9 - all tax rate deductible, including withholding tax rate

Without making any configuration change in purchase tax rates, go to Purchase Invoice window and create a new one. Select Bebidas Alegres BP.
Go to lines and create on line with below information:
Product = Cerveza Ale 0.5L
Invoiced Quantity = 100
Tax = Prestación servicios nacional 21% -21%R
Enter another line with the tax rate Adquisiciones 10% and Adquisiciones 4%
Complete the invoice.
Go to Tax tab and sum Tax Amount for each posive/negative tax rate = positive = 85.06 and negative = -28.56 (witholding). Sum of tax amount = 136.00+532.50+352= 1020.50
Post the invoice
Verify below accounting entries are shown:
1020.50(60000) expense account - DEBIT
85.06(47200) Tax account - DEBIT
28.56 (47700) Tax account - CREDIT
1077.00 (40000) Account payables - CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 10 - all tax rate non deductible, including a withholding tax rate
Set tax rates below as Tax Not Deductible = Yes
Adquisiciones IVA 21%
Adquisiciones IVA 10%
Adquisiciones IVA 4%
Prestación servicios nacional 21% -21%R (+21%)

Go to purchase invoice and create a new invoice for Bebidas Alegres BP. Enter below lines:
Line 1 - Product = Cerveza Ale 0.5L, Invoiced quantity = 100 - Tax Rate = Adquisiciones IVA 21%
Line 2 - Product = Cerveza Larger 0.5L, Invoiced quantity = 150 - Tax Rate = Adquisiciones IVA 10%
Line 3 - Product = Vino Blanco 0.75L, Invoiced quantity = 250 - Tax Rate = Adquisiciones IVA 10%
Line 4 - Product = Vino Rosado 0.75L, Invoiced quantity = 100 - Tax Rate = Prestación servicios nacional 21% -21%R

Save and complete the invoice. Total Gross Amount = 1752.96 (264.00+136.00+887.50+375.00+26.40+28.56+35.50+78.75-78.78)

Go to tax tab and check below info:

Taxable Amount (excluding withholding related one ) = 1662.50
Tax amount (excluding withholding relates one ) = 169.21

1662.50 + 169.21 = 1831.71 (TOTAL EXPENSE)

Verify below posting is shown:
1831.71 (60000) expense account DEBIT
1752.96 (40000) account payables CREDIT
78.75 (47500) withholding tax amount CREDIT

Repeat same test case for a negative purchase invoice by voiding current invoice. Verify that the accounting entries are the same but opposite.

Test Case 11 - some tax rate non deductible, including a withholding tax rate
Set tax rates below as Tax Not Deductible = Yes
Adquisiciones IVA 21%
Adquisiciones IVA 10%

Go to purchase invoice and create a new invoice for Bebidas Alegres BP. Enter below lines:
Line 1 - Product = Cerveza Ale 0.5L, Invoiced quantity = 100 - Tax Rate = Adquisiciones IVA 21%
Line 2 - Product = Cerveza Larger 0.5L, Invoiced quantity = 150 - Tax Rate = Adquisiciones IVA 10%
Line 3 - Product = Vino Blanco 0.75L, Invoiced quantity = 250 - Tax Rate = Adquisiciones IVA 10%
Line 4 - Product = Vino Rosado 0.75L, Invoiced quantity = 100 - Tax Rate = Prestación servicios nacional 21% -21%R

Save and complete the invoice. Total Gross Amount = 1752.96 (264.00+136.00+887.50+375.00+26.40+28.56+35.50+78.75-78.78)

Go to tax tab and check below info:

Tax amount related to Adquisiciones IVA 4% = 35.50
Tax amount related to Prestación servicios nacional 21% -21%R (+21%) = 78.75
total tax deductible = 114.25

Verify below posting is shown:
1717.46 (60000) expense account DEBIT
114.25 (47200) Tax account DEBIT
1752.96 (40000) account payables CREDIT
78.75 (47500) withholding tax amount CREDIT

(0099908)
psanjuan   
2017-10-13 14:50   
Test plan above verified.
Issue can be code reviewed and closed.
(0099987)
hgbot   
2017-10-23 11:19   
Repository: erp/devel/pi
Changeset: 75838b0fd78755bd53a1abe9b26d659592a12c0a
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Fri Oct 13 09:34:21 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/75838b0fd78755bd53a1abe9b26d659592a12c0a [^]

Fixes Issue 37010. In the loop that goes through each
Non Deductuable Tax, the Orderlines are retrieved.

However, this lines must be filtered to retrieve only the ones
that use the Tax that is being looped. If not, all the lines
will be used for all the taxes and then duplicated data will appear

---
M src/org/openbravo/erpCommon/ad_forms/DocInvoice.java
M src/org/openbravo/erpCommon/ad_forms/DocLineInvoice_data.xsql
---
(0099988)
dmiguelez   
2017-10-23 11:20   
Code Review + Testing Ok
(0101340)
hudsonbot   
2018-01-03 12:38   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/c81e0d3cbab5 [^]
Maturity status: Test