Bug 10461 - tax not taken in to account in the acq budget summaries
Summary: tax not taken in to account in the acq budget summaries
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: Main
Hardware: All All
: P5 - low major (vote)
Assignee: Koha Team University Lyon 3
QA Contact:
URL: acqui-home.pl
Keywords:
: 7686 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-06-13 12:39 UTC by Nicole C. Engard
Modified: 2017-12-07 22:17 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
patch (1.93 KB, patch)
2013-08-30 15:38 UTC, Koha Team University Lyon 3
Details | Diff | Splinter Review
Calculation of price with the gst in ordereceive (1.58 KB, patch)
2013-10-15 09:17 UTC, Koha Team University Lyon 3
Details | Diff | Splinter Review
correct addorder and orderreceive to store price including gst (2.48 KB, patch)
2013-10-15 13:01 UTC, Koha Team University Lyon 3
Details | Diff | Splinter Review
correct addorder and orderreceive to store price including gst (3.51 KB, patch)
2013-12-17 15:01 UTC, Koha Team University Lyon 3
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nicole C. Engard 2013-06-13 12:39:09 UTC
On acqui-home.pl the budget summary is not taking in to account tax rate at all. The code looks something like this:


SELECT SUM( COALESCE(unitprice, ecost) * quantity ) AS sum FROM aqorders
WHERE budget_id = ? AND
quantityreceived > 0 AND
datecancellationprinted IS NULL

Plus

SELECT SUM(shipmentcost) AS sum
FROM aqinvoices
WHERE shipmentcost_budgetid = ?
AND closedate IS NOT NULL


Tax rate should be included in this to give an accurate count of what has been spent/ordered.
Comment 1 Koha Team University Lyon 3 2013-08-30 15:38:59 UTC Comment hidden (obsolete)
Comment 2 Koha Team University Lyon 3 2013-08-30 16:13:53 UTC
Test plan :
1. Create a new fund
2. Define different tax rate in "gist" in system preferences
3. Edit a vendor and add a tax rate and put List prices "Include tax"
4. Add a new basket and a new order at this vendor and on the new fund
5. Check the budget summary in acqui-home.pl, the total price of the order is add in the amount "ordered" of the new fund
6. Edit the vendor and changed List Prices "Don't include tax"
6. Check the budget summary in acqui-home.pl, the total price more the tax is add in the  amount "ordered" of the new fund
7. Go back to the vendor and put Invoice prices:  "Include tax"
8. Go to the receive shipments, receive your order
9. Check the budget summary in acqui-home.pl, the actual cost of the order is add in the amount "spent" of the new fund
10. Go back to the vendor and put Invoice prices:  "Don't include tax"
11. 9. Check the budget summary in acqui-home.pl, the actual cost more the tax of the order is add in the amount "spent" of the new fund
Comment 3 Mathieu Saby 2013-08-31 17:17:22 UTC
Hello
I made some tests on sandbox 2.
I have a problem with the calculation of received prices.
Ex :
Vendor with 
- list price does not include tax
- invoiced price includes tax
- discount 10%
- vat 5,5%

- order created wuith list price : 10 eur
- in my basket summary, I see a included ecost price of 9.49. That is correct :(10-1)-5.5(10-1)/100 = 9.495
- in acquisition home page, in "ordered" colum for this fund, I see 9.49

The problem appears when I receive the item : 
in reception page, Koha tells me the unit cost is 9.49. That is correct.
but after the reception is done, in the acquisition home page shows "9" and not "9.49"

an other page to check is pro.test2.biblibre.com/cgi-bin/koha/acqui/spent.pl page with the details of the orders received for a fund. I also see "9" instead of 9.49.

M. Saby
Comment 4 Mathieu Saby 2013-09-01 11:36:41 UTC
Previous test based on the assumption that "invoiced price includes tax" means "the price on the invoice printed by the vendor includes tax" (I have never been sure of the meaning of this ;-) )

M. Saby
Comment 5 Koha Team University Lyon 3 2013-09-02 08:13:35 UTC
Hello Mathieu,
I glance your test in http://pro.test2.biblibre.com/, for the budget spent in the summary home page, it's normal you see 9 because it take the actual price for the invoice. So with the preference "invoiced price includes tax", it means "the atcual price on the invoice printed by the vendor includes tax", the price spent is really 9.

So for me, the patch is working as it's excepted.
Can you check it ?
Comment 6 Mathieu Saby 2013-09-02 10:53:25 UTC
Well, well, I fear I am still lost...

In reception Koha tells me the unit cost is "9.49" (so, I suppose price including tax). But I don't find again this 9.49 in Acq home page.
For me, "Budget spent" must not take the raw price of the invoice, if this raw price does not include tax. In this case, budget spent must add tax to this raw price.

Or maybe there is a bug in reception page?
What is the meaning of the "unit cost" when we receive item : does it mean 
1/ "cost actually written by the vendor on his invoice (with or without tax, depending of invoiceincgst value) ?
or
2/ "price actually paid by the library (so always WITH tax), based on the cost written by the vendor on the invoice" ?

Maybe Nicole could help me on this point?

Mathieu
Comment 7 Mathieu Saby 2013-09-17 21:54:14 UTC
In fact, your patch is probably right ;-)

Our library has a very specific situation, and I have just realized Koha is probably not able at all to deal with it : for some vendors, we pay the price without tax to the vendor AND the tax in a separate operation to the state (for vendors specialized in foreign books).
So for me "tax = 5.5%", "list price does not include tax" and "invoice price does not include tax" meant in literal way "the price printed on the invoice is without tax", but that did not preclude our library to pay tax (not to the vendor, but directly to the state). So we must write off from our funds the price including tax. But not sure Koha is made for that.

That's not completely clear for me. The manual does not give a lot of details.
What I could find is "Note if you list prices and/or invoice prices include tax"
(http://manual.koha-community.org/3.12/en/acqvendors.html#addacqvendor) 

Mathieu
Comment 8 Mathieu Saby 2013-09-17 21:56:26 UTC
Nicole, could you tell me if the specific situation of my library is taken into account by Koha's vendor preferences? If not, I will try write a patch for creating a new kind of setting.

Mathieu
Comment 9 Mathieu Saby 2013-09-20 08:52:03 UTC
I have made a complete test
I was taking "invoice price include tax" as a synonymous of "the libray actually pay the raw price + the tax to the vendor". So I was expected actual cost to be used in Spent column.

It is not the case, so I failed QA, until we have more explanations of the expected behavior of Koha.


all vendors with gst = 5.5%
vendor 1 : 
list price include tax : NO
invoice price include tax : NO
list price entered : 10 eur

order column : gst added (10.55)
spent column : gst added (10.55)
=> OK

vendor 2 : 
list price include tax : YES
invoice price include tax : YES
list price entered : 10 eur

order column : gst not added (10 eur)
spent column : gst not added (10 eur)
=> OK

vendor 3 : 
list price include tax : YES
invoice price include tax : NO
list price entered : 10 eur
At receipt : actual cost : 9.48

order column : gst not added (10 eur)
order column : gst added (10,55 eur)
=> KO
If actual cost is 9.48 and if "invoice price include tax" means "the libray actually does not pay tax", gst must not be added in spent column, but substracted.

vendor 4 : 
list price include tax : NO
invoice price include tax : YES
list price entered : 10 eur
calculated ecost with tax : 10.55 eur
At receipt : actual cost = 10.55

order column : gst added (10,55 eur)
spent column : gst not added (10 eur)
=> KO
If actual cost is 10.55, and if "invoice price include tax" means "the libray actually pay PRICE+TAX", gst must be added in spent column.



Mathieu
Comment 10 Mathieu Saby 2013-09-30 20:07:19 UTC
Hello Lyon3
do you still disagree with me, especially on the last test?

Mathieu
Comment 11 Mathieu Saby 2013-10-02 13:12:15 UTC
Hi
Jonathan wrote a patch about GST on invoices.
I made 4 long tests (from basket to invoice, with 4 vendors), and found a lot of weird things about prices in general. Maybe it could be helpfull for this one.
http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=10613

Mathieu
Comment 12 Mathieu Saby 2013-10-02 13:16:13 UTC
(Nicole, in comment for Bug 10613 I say documentation about prices is poor. Don't take it for you! I was just meaning if there were somewhere a detailed specification page with all different "prices" and their expected behavior with different vendor settings, it would be easier to agree on bugs to fix.)

Mathieu
Comment 13 Katrin Fischer 2013-10-02 18:22:19 UTC
Hi Mathieu - maybe we need to start a wiki page for that. Without a clear specification, those bugs will be hard to fix :(
Comment 14 Mathieu Saby 2013-10-02 21:19:04 UTC
(In reply to Katrin Fischer from comment #13)
> Hi Mathieu - maybe we need to start a wiki page for that. Without a clear
> specification, those bugs will be hard to fix :(

I can try to write something, but I don't know if I have rights to create a page.
Some ideas it may be usefull to list :
- the different columns for prices in aqorders and their meaning (listprice, ecost, unitprice) + discount + tax rate
(maybe also prices in records in a 2d time)
- the different settings for vendors and their meaning
- the different settings for currencies
- the pages (not forgetting pdf and csv export) displaying prices (not to forget some of them is a patch is made for altering general price management)
- maybe the subs in C4 handling prices
- Which prices stored in database Koha must calculate, when and how
- Which prices must be visible in staff interface, when and how to calculate them
- arithmetic details : how much decimals are taken into consideration in the calculations? what kind of "rounding" is made in the final price displayed or store in db (15.005 => 15 or 15.01 ?)

For example : in a detailed order create by hand, 
-> the user enter a price (could be with or without tax)
10 EUR
-> this input is saved in DB as listprice (is it usefull?)
(I think a correction is made depending on currency at this step. not sure)
-> rrp tax exc. and tax. inc are calculated depending on listprice and the preference "list prices include tax" of the vendor
for ex : rrp tax inc = 10.55 EUR
-> rrp (which one : tax inc., or tax exl, or depending on vendor preference?) is saved in database in rrp column
-> rrp tax INC (or also tax exc. but it must be clear) is displayed on screen
-> ecost (tax inc.? tax exl?) is calculated depending on rrp tax inc., the discount and the preference "invoiced prices include tax"
etc

It would be long and tedious, but I am sure it could be usefull for fixing bugs, avoiding future regressions, and make easier future enh.

Mathieu
Comment 15 Mathieu Saby 2013-10-07 14:25:12 UTC
1st draft on the wiki
wiki.koha-community.org/wiki/Handling_Prices

Mathieu
Comment 16 Koha Team University Lyon 3 2013-10-07 14:52:30 UTC
Thank you Mathieu for your wiki page.
I have just tested the comportement in master for the calculation of the budget summary in acqui-home.pl.
If you choose for the vendor :
* list price include tax : YES / invoice price include tax : YES
or 
* list price include tax : YES / invoice price include tax : NO

The spending are tax included 

but if you choose :
* list price include tax : NO / invoice price include tax : YES
or
* list price include tax : NO / invoice price include tax : NO

The spending are without the tax

I think there's a bug here.

In any case, my patch isn't appropriate anymore.

The unitprice in acqorders are re-calculted and I don't take this into account in this patch.

I try to find out an other way to have the right spending in the summary of acqui-home.pl

Sonia
Comment 17 Koha Team University Lyon 3 2013-10-14 16:36:03 UTC
The principle is that the price including the tax has to be deduct from the budget.
In fact, there's a bad calculation of the unit in orderreceive.pl.
If we are in the case :
list price include tax : YES
invoice price include tax : NO
So to have price including the tax, you have to add the tax to the unit price (unitprice+unitprice*gstrate)

If we are in the case : 
list price include tax : NO
invoice price include tax : YES
you have to add the tax to the estimated price 
ecost =ecost+ecost*gstrate 

this is not the case in orderreceive.pl

I prepare a patch for this if everybody is ok
Comment 18 Mathieu Saby 2013-10-15 07:30:21 UTC
(In reply to Koha Team Lyon 3 from comment #17)
> The principle is that the price including the tax has to be deduct from the
> budget.
> In fact, there's a bad calculation of the unit in orderreceive.pl.
> If we are in the case :
> list price include tax : YES
> invoice price include tax : NO
> So to have price including the tax, you have to add the tax to the unit
> price (unitprice+unitprice*gstrate)
> 
> If we are in the case : 
> list price include tax : NO
> invoice price include tax : YES
> you have to add the tax to the estimated price 
> ecost =ecost+ecost*gstrate 
> 
> this is not the case in orderreceive.pl
> 
> I prepare a patch for this if everybody is ok

Hi
For me the question is : are we supposed to store a corrected price (with/without gst) in "unitprice" column, or to calculate dynamicly the right price at display?
If we store the "gst corrected" unitprice, it will mabye be more precise for rounding, and it will mean if we change the vendor settings AFTER having received a book, the remaining available amount for the fund will stay unchanged. I suppose it will be a good behavior, but I am not completely sure.

Mathieu
Comment 19 Koha Team University Lyon 3 2013-10-15 07:51:43 UTC
Hi
yes, I understand your point of vue but I'm not sure. But, if the settings change in the middle of year because the vendor really change his behaviour, the invoices which have been already paid won't change. If the calculation is dynamic, it will change the spent amount and the available, but it isn't right.
Comment 20 Mathieu Saby 2013-10-15 08:04:52 UTC
(In reply to Koha Team Lyon 3 from comment #19)
> Hi
> yes, I understand your point of vue but I'm not sure. But, if the settings
> change in the middle of year because the vendor really change his behaviour,
> the invoices which have been already paid won't change. If the calculation
> is dynamic, it will change the spent amount and the available, but it isn't
> right.

We made mistakes here for the settings of some vendors, and took profit of the current behavior ;-), BUT I agree with you, the best solution would be to have "gst corrected price" stored in database.

And Koha's documentation should maybe be updated to make clear that changing a vendor setting does not change the prices of received parcels, BUT will change the price calculation of parcels not yet receveived.

Mathieu
Comment 21 Koha Team University Lyon 3 2013-10-15 09:17:03 UTC Comment hidden (obsolete)
Comment 22 Mathieu Saby 2013-10-15 09:39:24 UTC
Your patch is marked as "obsolete". A mistake?

Also, could you provide a description of what it does, and a test plan, in the commit message ?

Mathieu
Comment 23 Koha Team University Lyon 3 2013-10-15 09:57:49 UTC
I just realize that my patch just corrected unitprice, thus the budget spent. But I want to check if estimated cost is ok (for budget ordered) before to propose it.
Comment 24 Koha Team University Lyon 3 2013-10-15 13:01:26 UTC Comment hidden (obsolete)
Comment 25 Koha Team University Lyon 3 2013-10-15 13:09:42 UTC
This patch correct addorder.pl and orderreceive.pl to store prices including gst in ecost and unitprice, according to the vendor preference (listinggst and invoicegst).
The acqui-home.pl the budget summary should take into account the price including gst.
But invoice table and basket table are not right anymore. I propose to do others bugzilla ticket to correct this (or to complete bz10313)

Test plan is the same.
Comment 26 Mathieu Saby 2013-10-15 13:18:56 UTC
(In reply to Koha Team Lyon 3 from comment #25)
> Test plan is the same.

You mean the same as comment 2?
I did not check, but as you made changes in new places (orderreceived.pl...), does not it need needs to be updated?
And could you include a description of the patch, and the test plan within commit message? In a general way, it makes patches more easy to test, especially if the patch is rewritten after discussion like this one ;-)

I will take a look this evening.

Mathieu
Comment 27 I'm just a bot 2013-10-30 07:14:45 UTC
Patch applied cleanly, go forth and signoff
Comment 28 Mathieu Saby 2013-12-12 10:06:04 UTC
Please Lyon 3 and Christophe, could you confirm that this bug has been sign off, and restore the Signed-off line in the commit msg?

Mathieu
Comment 29 Mathieu Saby 2013-12-12 10:06:50 UTC
Oups, sorry, the message was for an other bug !
Too many tabs open...

Mathieu
Comment 30 Koha Team University Lyon 3 2013-12-17 15:01:55 UTC Comment hidden (obsolete)
Comment 31 Koha Team University Lyon 3 2013-12-17 15:41:19 UTC
This patch check the tax rate in the order and if the price are filled tax included or not. With this information, it calculated a price tax included for the estimated cost. It corrected the calculation of the unitprice at the receive.
So the price are recorded tax included in database. So the budget summary in the acqui-home.pl is correct (taking account of tax rate).
Test plan :
1. Create a new fund
2. Define different tax rate in "gist" in system preferences
3. Edit a vendor and add a tax rate and put List prices "Include tax"
4. Add a new basket and a new order at this vendor and on the new fund
5. Check the budget summary in acqui-home.pl, the total price of the order is add in the amount "ordered" of the new fund
6. Edit the vendor and changed List Prices "Don't include tax"
6. Check the budget summary in acqui-home.pl, the total price more the tax is add in the  amount "ordered" of the new fund
7. Go back to the vendor and put Invoice prices:  "Include tax"
8. Go to the receive shipments, receive your order
9. Check the budget summary in acqui-home.pl, the actual cost of the order is add in the amount "spent" of the new fund
10. Go back to the vendor and put Invoice prices:  "Don't include tax"
11. Check the budget summary in acqui-home.pl, the actual cost more the tax of the order is add in the amount "spent" of the new fund
Comment 32 Katrin Fischer 2014-04-09 18:50:12 UTC
I am glad someone is tackling this, but I see some problems with this patch that I think we need to give some thought:

a) Can we safely assume all libraries have to pay the tax? I seem to remember that I have been told that some libraries don't have to - so for those the estimated price and actual price shouldn't include the tax. I have always wondered if the 'tax number registered' setting in the vendor was supposed to do something like that. Maybe something we could ask on the mailing list?

b) If I am reading the patch correctly this changes how the values are stored in the database. So there is the question - what happens to old received orders? Because once this patch is in effect, you will have a mix for old and new orders. Do we need to provide a script, that changes the existing orders according to the settings in aqorders?
Comment 33 Katrin Fischer 2014-04-09 22:22:31 UTC
I checked with Chris Cormack, because I thought it was NZ - and it is. In NZ public libraries can claim back the taxes so they don't want them to be included in the estimated and actual price. It also seems like my suspicion about the 'tax registered' switch were right. Not sure how we can reintroduce this behaviour safely, but I think it will be needed.
Comment 34 Koha Team University Lyon 3 2014-04-10 08:21:14 UTC
*** Bug 7686 has been marked as a duplicate of this bug. ***
Comment 35 Koha Team University Lyon 3 2014-04-10 08:39:16 UTC
Katrin,
the last patch i propose is obsolete. A lot of things have changed in master since I have attached this patch.
If some institutions don't count the tax into their fund, perhaps 2 new information in aqbooksellers : "tax of listprice included in fund" and "tax of unit price included in fund" (with better wording)

b.I will change of method. But this is already true in master. Prices have been changed in the storage since last year I think.
Comment 36 Koha Team University Lyon 3 2017-04-07 12:20:26 UTC
Resolved by all the patchs dealing with GST in acquisitions
Comment 37 Katrin Fischer 2017-04-07 12:38:23 UTC
Yay!