Bug 2865 - Acquisitions: GST not calculating correctly.
Summary: Acquisitions: GST not calculating correctly.
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: master
Hardware: PC All
: P3 major (vote)
Assignee: Chris Hall
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-12-16 02:12 UTC by Joann Ransom
Modified: 2014-12-07 20:02 UTC (History)
8 users (show)

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


Attachments
Test scenarios for adding orders (26.50 KB, application/xml)
2008-12-30 17:17 UTC, Chris Cormack
Details
And again not as an excel spreadsheet (71.19 KB, text/html)
2008-12-30 17:31 UTC, Chris Cormack
Details
Makes corrections to acq.js and parcel.pl regarding gst and discount calculations (2.11 KB, patch)
2012-02-27 23:55 UTC, Chris Hall
Details | Diff | Splinter Review
parcel table : needs value of tax not tax rate (169.15 KB, application/vnd.openxmlformats-officedocument.wordprocessingml.document)
2012-03-20 17:03 UTC, Koha Team University Lyon 3
Details
Corrected errors in previous patch (3.29 KB, patch)
2012-03-22 04:34 UTC, Chris Hall
Details | Diff | Splinter Review
corrects errors in acquisitions calculations (7.13 KB, patch)
2012-04-15 23:18 UTC, Chris Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Chris Cormack 2010-05-21 00:57:20 UTC


---- Reported by jransom@library.org.nz 2008-12-16 14:12:10 ----

I have just placed an order for a book in my new Koha 3.0 test site.

The supplier profile is set with list/ordering price and invoice price both
inclusive of GST (12.5%) The supplier gives a 15% discount.

I ordered a book with a List price of $75. 

The amount to be deducted from the budget needs to be exclusive of GST and
discount ie the actual net cost to us of purchasing the item.

So RRP $75 less GST (divided by 1.125) =  a GST exclusive price of $66.66.
Then if we deduct the 15% ($9.99) off the ex-GST price the book is actually
$56.67.

What appears to be happening is that Koha 3.0 then adds GST of 12.5% ($7.08)
back on to the $56.67 to arrive at the $63.75 which is then deducted off the
budget.

This is the wrong amount. It should be $56.67 that is committed from the budget.



---- Additional Comments From jransom@library.org.nz 2008-12-30 17:17:39 ----

Created an attachment
Test scenarios for adding orders

This is an xl spreadsheet. I can save it as a pdf if that would be preferred.



---- Additional Comments From jransom@library.org.nz 2008-12-30 17:26:23 ----

I have carried out some testing on adding orders in acquisitions and this bug is not quite as black and white as I first thought. Attached spreadsheet gives the details of my testing, results and conclusions. Summarized below:

METHOD:
I set up 6 vendors with different profile combinations: some with list price including GST and somne without, some with discount and some without, some with list prices entered in $USD and some in $NZD.

I then ordered single copies of books from the suppliers, and 2 copies of a different book from the suppliers. 

CONCLUSIONS:

1. LIST PRICE DOES NOT INCLUDE GST - WITH OR WITHOUT A DISCOUNT
Where a Vendor profile has been set with list (ordering) price as not including GST the budgeted price and replacement costs are calculated perfectly, regardless of whether a discount applies or not, and regardless of whether 1 or multiles copies are ordered.

2.LIST PRICE INCLUDES GST AND A DISCOUNT APPLIES
Where the Vendor profile has been set with list or ordering price as including GST, and a discount applies, then Koha is correctly removing the GST, correctly removing the discount and then INCORRECTLY adding GST back on again. I think this is the scenario that led me to post this bug. Ordering 1 or multiple copies, and various discounts, are handled fine.
 
3. LIST PRICE INCLUDES GST AND NO DISCOUNT APPLIES
Where the Vendor profile has been set with list or ordering price as including GST but NO discount, Koha is adding GST on top of the both the list and the RRP price. Ordering 1 or multiple copies makes no difference.

4. LIST PRICE IN FOREIGN CURRENCY
Foreign currency conversions are a total mess and I havn't yet figured out what happened yet. Will bug this separately.





---- Additional Comments From jransom@library.org.nz 2008-12-30 17:31:55 ----

Created an attachment
And again not as an excel spreadsheet





---- Additional Comments From jransom@library.org.nz 2008-12-30 17:37:43 ----

Happy to supply a login and password for someone to access my Koha test site if required. Just email me.



---- Additional Comments From nengard@gmail.com 2009-12-23 17:59:54 ----

This bug hasn't been touched in nearly a year, please revisit and test and close if appropriate.



---- Additional Comments From colin.campbell@ptfs-europe.com 2010-02-19 14:25:18 ----

Problem still outstanding on current HEAD



---- Additional Comments From chris@bigballofwax.co.nz 2010-03-30 03:37:29 ----

Jo is currently testing a fix for this, if it works we will send the patch upstream



--- Bug imported by chris@bigballofwax.co.nz 2010-05-21 00:57 UTC  ---

This bug was previously known as _bug_ 2865 at http://bugs.koha.org/cgi-bin/bugzilla3/show_bug.cgi?id=2865
Imported an attachment (id=898)
Imported an attachment (id=899)

Actual time not defined. Setting to 0.0
The original submitter of attachment 898 [details] is unknown.
   Reassigning to the person who moved it here: chris@bigballofwax.co.nz.
The original submitter of attachment 899 [details] is unknown.
   Reassigning to the person who moved it here: chris@bigballofwax.co.nz.

Comment 1 Chris Hall 2012-02-27 23:55:48 UTC Comment hidden (obsolete)
Comment 2 Koha Team University Lyon 3 2012-03-20 17:00:10 UTC
Results in the basket table are corrects whatever you choose List prices=Include tax or List prices=Don't include tax. 
Results in the parcel table are ok if you choose List prices=Don't include tax but not if you choose List prices=Include tax (Tax is added at the prices including already the tax).
 
In parcel table, it will be nice to have the sum of the tax but not only the tax rate. (cf. attachement)
Comment 3 Koha Team University Lyon 3 2012-03-20 17:03:49 UTC
Created attachment 8375 [details]
parcel table : needs value of tax not tax rate
Comment 4 Chris Hall 2012-03-22 04:34:09 UTC Comment hidden (obsolete)
Comment 5 Chris Hall 2012-03-22 04:45:07 UTC
Cheers for pointing out the error in parcel.pl regarding it re-adding st back on if the list price is gst exclusive, this will be fixed shortly.
Comment 6 Katrin Fischer 2012-03-22 06:20:13 UTC
>Budgeted cost (ecost in the code) is gst exclusive and takes the discount into >account BEFORE gst.

Hi Chris, 

I am not sure I understand this correctly, so please ignore me if I don't ;)

I think budgeted cost needs to be inclusive GST, because libraries in Germany have to pay that. So they need it to be in the budgeted cost. I am not sure if there are differences between different types of libraries and countries, but if that is the case we perhaps need a switch or something. (What is tax registered in the vendor supposed to do? Perhaps that? I always wondered.)
Comment 7 Chris Hall 2012-04-15 23:18:10 UTC
Created attachment 9212 [details] [review]
corrects errors in acquisitions calculations

Attached patch that meets clients specs, not ready to be up streamed yet.


Hey Katrin

You understood correctly, it assumes budgeted cost is GST exclusive so this can't be pushed to master yet.


Just for documentation purposes, here is what I used the various variables to mean:
list price = may or may not include gst depending on vendor settings, if there if a discount then this price hasnt been adjusted to reflect the discounted price.
unit price = actual cost, user supplied, not used in calculations
rrp = gst exclusive, also doesnt reflect discounted price
ecost = budgeted cost = list price minus any discount and excluding gst
gstrate = rate of gst as a percentage
gst = rrp * gstrate

The previous code used gst to refer to both the rate and the actual amount in different places, I have corrected this where I came across it.


Work to be done:
Setting for whether or not the budgeted cost is gst inclusive/exclusive.
This is the previous issue Katrin pointed out still exists, see line 323 or acqui/parcel.pl, I have labeled this with a FIXME as this assumes tototal is gst exclusive and will always add it back in.
Comment 8 Jonathan Druart 2013-07-19 08:41:20 UTC
This issue is certainly fixed by bug 5335.
Comment 9 Jonathan Druart 2014-03-06 09:16:27 UTC
This one is too old.