Bug 13324 - [DEPENDS_ON_13321] The fund values must be based on tax included values
Summary: [DEPENDS_ON_13321] The fund values must be based on tax included values
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: claire.hernandez@biblibre.com
QA Contact: Katrin Fischer
URL:
Keywords:
Depends on: 13321
Blocks:
  Show dependency treegraph
 
Reported: 2014-11-21 16:15 UTC by Jonathan Druart
Modified: 2018-12-03 20:03 UTC (History)
10 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Who signed the patch off:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 13324: The fund values should be based on tax included values (3.36 KB, patch)
2014-11-21 16:22 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 13324: The fund values should be based on tax included values (3.36 KB, patch)
2016-02-01 09:36 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 13324: The fund values should be based on tax included values (3.55 KB, patch)
2016-03-09 14:51 UTC, Julian Maurice
Details | Diff | Splinter Review
Bug 13324: The fund values should be based on tax included values (3.59 KB, patch)
2016-10-13 15:48 UTC, Koha Team University Lyon 3
Details | Diff | Splinter Review
Bug 13324: The fund values should be based on tax included values (3.65 KB, patch)
2016-10-27 00:55 UTC, Nick Clemens
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Druart 2014-11-21 16:15:04 UTC
On calculating the fund values (spend, ordered), the calculation must be base on the values included tax, for all supplier configurations.
Comment 1 Jonathan Druart 2014-11-21 16:22:30 UTC Comment hidden (obsolete)
Comment 2 Jonathan Druart 2015-01-07 08:20:49 UTC
To test these patches, have a look at http://lists.koha-community.org/pipermail/koha-devel/2014-December/041024.html
Comment 3 Francois Charbonnier 2015-01-12 18:39:31 UTC
I tested this patch on BibLibre's sanbox 17.

I used 4 different setups for the vendors :

Set up 1 = List prices INCLUDE TAX + Invoice prices INCLUDE TAX
Set up 2 = List prices EXCLUDE TAX + Invoice prices EXCLUDE TAX
Set up 3 = List prices INCLUDE TAX + Invoice prices EXCLUDE TAX
Set up 4 = List prices EXCLUDE TAX + Invoice prices INCLUDE TAX

Then I set up 8 different funds to test the calculation on the fund level and on the child fund level.

Under each vendor, I added two baskets. Each with 1 order which would use one of the 8 funds I created.

Vendor 1 = List prices INCLUDE TAX + Invoice prices INCLUDE TAX
- basket 1 with order on a level 1 fund
- basket 2 with order on a level 2 fund

Vendor 2 = List prices EXCLUDE TAX + Invoice prices EXCLUDE TAX
- basket 1 with order on a level 1 fund
- basket 2 with order on a level 2 fund

Vendor 3 = List prices INCLUDE TAX + Invoice prices EXCLUDE TAX
- basket 1 with order on a level 1 fund
- basket 2 with order on a level 2 fund

Vendor 4 = List prices EXCLUDE TAX + Invoice prices INCLUDE TAX
- basket 1 with order on a level 1 fund
- basket 2 with order on a level 2 fund


After ordering and receiving each order, I checked the funds levels on two pages :
* aqbudgets.pl (admnistration)
* acqui-home.pl (acquisition's home page)

Each time, the calculation were good. Except for one, which is not linked to the calculation itself but on the approxination.

For the vendor 3 (List prices INCLUDE TAX + Invoice prices EXCLUDE TAX) :
I ordered two 10euros book. The price is taxe included. So I should paid 20euros

When I received the books, the price is taxe excluded. The approximation is on two numbers which, in the end, lead the total cost to be 19.99 euros tax included where I expected 20 euros.

For sure, it's not much, probably acceptable but I would like to know if there is an easy fix. What about managing more digits after the coma in the database and display just two?
Comment 4 Jonathan Druart 2015-01-13 08:53:49 UTC
(In reply to Francois Charbonnier from comment #3)

Bonjour François,

> For sure, it's not much, probably acceptable but I would like to know if
> there is an easy fix. What about managing more digits after the coma in the
> database and display just two?

Actually it's what it's done :)
Have a look at the data in the aqorders table for your order:
            ordernumber: 75
           biblionumber: 4996
              entrydate: 2015-01-12
               quantity: 2
              listprice: 10.000000
              unitprice: 9.520000
 unitprice_tax_excluded: 9.520000
 unitprice_tax_included: 9.996000
       quantityreceived: 2
               basketno: 35
              timestamp: 2015-01-12 18:21:52
                    rrp: 10.00
       rrp_tax_excluded: 9.523810
       rrp_tax_included: 10.000000
                  ecost: 10.00
     ecost_tax_excluded: 9.523810
     ecost_tax_included: 10.000000
   tax_rate_on_ordering: 0.0500
  tax_rate_on_receiving: 0.0500
  tax_value_on_ordering: 0.476190
 tax_value_on_receiving: 0.476000

The values are correct for the ordering.
But on receiving, the inserted value is the one displayed on the interface (the rounded value, 9.52).
So the fund total spend (calculated on the unit price included tax) is correct: 9.996 * 2 = 19.992

I really don't know how we could get rid of this problem, I am open to all ideas.
Comment 5 Marc Véron 2015-01-13 10:03:49 UTC
I think the vagueness occurs somewhere else, i.e. when calculating the unitprice for the database.

In the example it is:
unitprice_tax_excluded: 9.520000

however, it should be: 9.523809523809524
(10 / 1.05)
or rounded: 9.523810

which is the same as
ecost_tax_excluded: 9.523810
Comment 6 Jonathan Druart 2015-01-13 11:47:10 UTC
(In reply to Marc Véron from comment #5)
> I think the vagueness occurs somewhere else, i.e. when calculating the
> unitprice for the database.
> 
> In the example it is:
> unitprice_tax_excluded: 9.520000
> 
> however, it should be: 9.523809523809524

Yes, but the problem is that the unitprice tax excluded is displayed on the interface (on receiving the items). And the value is rounded to 9.52 (because we don't want to display 9.523809523809524 :)
Comment 7 Jonathan Druart 2015-01-13 11:58:02 UTC
Please continue the discussion on bug 13321 comment 11.
Comment 8 Francois Charbonnier 2015-02-25 00:15:40 UTC
Jonathan submited a patch to fix the issue I mentionned in comment3. Since, the discussion spread through two tickets (13324 and 13321), the patch I'm talking about is here : http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=13321#c15

I tested again the feature from this ticket and everyting works like a charm.

I did the testing on biblibre's sanbox 17.

Jonathan, could you sign this patch off on my behalf? Thanks!
Comment 9 Jonathan Druart 2015-02-26 08:36:49 UTC
(In reply to Francois Charbonnier from comment #8)
> Jonathan, could you sign this patch off on my behalf? Thanks!

Added on the remote branch :)
Comment 10 Julian Maurice 2016-02-01 09:36:04 UTC Comment hidden (obsolete)
Comment 11 Julian Maurice 2016-03-09 14:51:44 UTC
Created attachment 48873 [details] [review]
Bug 13324: The fund values should be based on tax included values

Rebased on master and added Signed-off-by lines that were lost in the process
Comment 12 Katrin Fischer 2016-03-29 22:21:57 UTC
Sadly, blocked as well by Failed QA on dependent bug 13321.
Comment 13 claire.hernandez@biblibre.com 2016-05-12 13:32:16 UTC
bz 13321 rebased, so status resetted/
Comment 14 Jonathan Druart 2016-07-04 09:01:39 UTC
Blocked by bug 13321 which is failed QA.
Comment 15 Koha Team University Lyon 3 2016-10-13 15:48:13 UTC
Created attachment 56437 [details] [review]
Bug 13324: The fund values should be based on tax included values

Now we have a column in DB to easily retrieve the tax included values.
So the sum must be done this the _tax_included DB field and not on the
"old" field.
The old field can be tax excluded or tax included depending the supplier
configuration.

Test plan:
Verify that the values in the acqui home and budgets page are the tax
included values.

Signed-off-by: Laurence Rault <laurence.rault@biblibre.com>

Signed-off-by: Francois Charbonnier <francois.charbonnier@inlibro.com>

Signed-off-by: Sonia Bouis <sonia.bouis@univ-lyon3.fr>
Signed-off-by: Sonia Bouis <koha@univ-lyon3.fr>
Comment 16 Koha Team University Lyon 3 2016-10-13 15:56:55 UTC
tested with all the configuration of vendor possible (GST include/exclude). 
I have tried to change the price / GST on receiving. 
I have tried partial receipt.
All prices are consistent.
Comment 17 Nick Clemens 2016-10-27 00:55:11 UTC
Created attachment 56893 [details] [review]
Bug 13324: The fund values should be based on tax included values

Now we have a column in DB to easily retrieve the tax included values.
So the sum must be done this the _tax_included DB field and not on the
"old" field.
The old field can be tax excluded or tax included depending the supplier
configuration.

Test plan:
Verify that the values in the acqui home and budgets page are the tax
included values.

Signed-off-by: Laurence Rault <laurence.rault@biblibre.com>

Signed-off-by: Francois Charbonnier <francois.charbonnier@inlibro.com>

Signed-off-by: Sonia Bouis <sonia.bouis@univ-lyon3.fr>
Signed-off-by: Sonia Bouis <koha@univ-lyon3.fr>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Comment 18 Kyle M Hall 2016-10-28 14:41:14 UTC
Pushed to master for 16.11, thanks Jonathan!
Comment 19 Mason James 2016-11-30 07:54:27 UTC
(In reply to Kyle M Hall from comment #18)
> Pushed to master for 16.11, thanks Jonathan!

Contains DB changes, passing for 16.05.x series
Comment 20 Mason James 2016-12-14 12:04:18 UTC
Blocked by deps, skipping for 16.05.x
Comment 21 David Cook 2018-04-04 06:53:19 UTC
Maybe I'm missing something but for an upgraded Koha... the only place I'm seeing prices include GST is on acqui-home.pl. Everywhere else is excluding GST, which makes for some rather inconsistent displays.

Still pulling the string on this one, but in 17.05.03 the ordered.pl and spent.pl pages (which are reached via acqui-home.pl) don't include GST. 

The order page doesn't show GST... although I think that is perhaps based on vendor settings.

The basket doesn't show GST unlss "Show all details" is selected...

Of course, regardless of vendor configuration, you'd need to have a consistent display at a high level, since you'd be aggregating many vendors with different settings...

But it would be good to have a budgeted cost because GST included/excluded arbitrarily seems to throw out the budgets...

Anyway, looking at this more, but not really seeing this as an improvement.