On calculating the fund values (spend, ordered), the calculation must be base on the values included tax, for all supplier configurations.
Created attachment 33795 [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.
To test these patches, have a look at http://lists.koha-community.org/pipermail/koha-devel/2014-December/041024.html
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?
(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.
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
(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 :)
Please continue the discussion on bug 13321 comment 11.
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!
(In reply to Francois Charbonnier from comment #8) > Jonathan, could you sign this patch off on my behalf? Thanks! Added on the remote branch :)
Created attachment 47497 [details] [review] Bug 13324: The fund values should be based on tax included values Rebased on master
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
Sadly, blocked as well by Failed QA on dependent bug 13321.
bz 13321 rebased, so status resetted/
Blocked by bug 13321 which is failed QA.
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>
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.
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>
Pushed to master for 16.11, thanks Jonathan!
(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
Blocked by deps, skipping for 16.05.x
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.