GST Rewrite RFC

RFC for GST / VAT rewriting

Development has started, see bug 12825 (http://bugs.koha-community.org/bugzilla3/showdependencytree.cgi?id=12825&hide_resolved=1)

Reasons for wanting to rewrite the GST/VAT management

Bug 5335 has introduced a way to define taxes at order-line level rather than having only one tax rate for all orders.

The implementation is quite bad: the prices calculation is done on each page, nothing has been refactorized.

Since the goal was to change as few changes as possible, the DB have been kept as before.

But the fields are not always obvious and often confuses.

The proposition

We (BibLibre) propose to store all prices in the DB in order not to recalculate them.

This will fix a current problem: if the discount and or VAT rate is changed, the values displayed changed. So an order could have been receipt with an invoice generated. But the prices will continue to change.

A new feature

On the way, an enhancement will be take into account: a vat pro-rata/adjustment (I don't know what is the English term here).

Indeed, a VAT can be different on ordering or receiving: there will be 2 VAT rate

For instance, if a public price is $20, the price excluding taxes will be $18.69 (with vat=7%). But the library will get some VAT back. This should be integrate on receiving.

Note: If the invoice prices don't include tax, we can choose the gst rate to apply for the invoice.

If the invoice prices include tax, we cannot choose the gst rate. The gst rate used on ordering is kept.

Changes to be done

Currently, we have the following DB fields :

• quantity => Quantity ordered
• currency => the currency used for the purchase
• listprice => vendor price
• unitprice => price entered on receiving
• quantityreceived => the quantity received
• rrp => Replacement cost for an item
• ecost => Estimated cost for an item
• gstrate => tax rate (%)
• discount => discount (%)
• uncertainprice => boolean : the price was uncertain
• totalamount => not used
• freight => certainly useless, not used

The fields would become:

• quantity => quantity ordered
• rrp_et => recommended retail price excluding tax
• rrp_it => recommended retail price including tax
• discount => discount (%)
• ecost_et => ecost excluding tax
• ecost_it => ecost including tax
• gstrate_order => gst rate on ordering (%)
• gst_order => gst value on ordering (unit)
• quantity_received => quantity received
• unitprice_et => unit price excluding tax (on receiving)
• unitprice_it => unit price including tax (on receiving)
• gstrate_receiv => gst rate on receiving (invoice) (%)
• gst_receiv => gst value on receiving (unit)
• freight => shipping cost

-- Because in Canada shipping cost are taxed, I would also suggest to replace "freight" by :

• freight_et => shipping cost excluding tax
• freight_it => shipping cost including tax
• freight_gstrate => shipping cost gstrate (%)
• freight_gst => shipping cost gst value (unit)

To get all these information, the vendor page should be enhanced. We could have two new options at the "Ordering information" level :

• Shipping cost: Include tax X Don't include tax X
• Shipping cost tax rate : [value of gist system preference]

What we want (examples)

Configuration 1 : 0 / 0

• List prices don't include tax
• Invoice prices don't include tax

Supplier configuration:

• gstrate_order: 5 %
• gstrate_receiv: 2 %
• discount: 10 %

Order:

• rrp_et = 82
• rrp_it = rrp_et * ( 1 + gstrate_order ) = 82 * ( 1 + 5 % ) = 86.10
• ecost_et = rrp_et * ( 1 -discount ) = 82 * ( 1 -10 % ) = 73.80
• ecost_it = rrp_et * ( 1 + gst_rate_order ) * ( 1 -discount ) = 82 * ( 1 + 5 % ) * ( 1 -10 % ) = 77.49
• quantity = 2
• gst_order = quantity * ecost_et * gstrate_order = 2 * 73.80 * 5 % = 7.38

Ordering

Basket view

rrp tax exc.	ecost tax exc.	rrp tax inc.	ecost tax inc.	quantity	total tax exc.	total tax inc.	VAT %	VAT
82	73.80	86.10	77.49	2	147.60	154.98	5	7.38

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	154.98 (was 147.60)	0	845.02 (was 852.40)

Basket group printing (3 pages)

Total RRP tax exc.	Total RRP tax inc.	VAT %	VAT	Total discount	Total tax exc.	Total tax inc.
164	172.20	5	7.38	16.40	147.60	154.98

Quantity	RRP tax exc.	RRP tax inc.	Discount %	Discount price	VAT rate	Total tax exc.	Total tax inc.
2	82	86.10	10	8.20	5	147.60	154.98

Receiving

If shipping cost is tax excluded, the system will calculate the shipping cost including tax

• freight_it = freight_et + freight_gst

If shipping cost tax included, the system will directly populate "freight_it".

The shipping cost which is added to the total will always be the value including tax (freight_it).

Two options :

If shipping cost is excluding tax and shipping cost tax rate = 10% (freight_gstrate) :

• freight_et = 4.55
• freight_gst = 0.45
• freight_it = 5 (including tax)

If shipping cost is including tax :

• freight_it = 5 (including tax).

2 cases:

The unit price is the same

• quantityreceived = 2
• unitprice_et = 73.80
• unitprice_it : unitprice_et * ( 1 +gstrate_receiv ) = 73.80 * ( 1 + 2 % ) = 75.276
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 73.80 * 2 % = 2.952

Parcel

Already received orders table:

Quantity	ecost exc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	73.80	73.80	75.276	2	2.952	unitprice_it*2 = 150.552

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total exc. tax
	82 (rrp_et)	73.80 (ecost_et)	73.80 (unitprice_et)	2	147.60 (unitprice_et * 2)
Total exc. tax	164 (sum(rrp_et*quantity))	147.60 (sum(ecost_et * quantity))	147.60 (sum(unitprice_et * quantity))		147.60 (sum(unit_price_et * quantity))
VAT order 5% (gst_order)		7.38
VAT receipt 2% (gst_receiv)			2.952		2.952
Total tax inc.		154.98	150.552		150.552
Total + shipping					155.552

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0 (was 5)	155.552 (was 147.60)	844.448 (was 847.40)

The unit price has changed

• quantityreceived = 2
• unitprice_et = 70
• unitprice_it : unitprice_et * ( 1 +gstrate_receiv ) = 70 * ( 1 + 2 % ) = 71.40
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 70 * 2 % = 2.8

Parcel

Already received orders table:

Quantity	ecost exc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	73.80	70	71.40	2	2.8	unitprice_it*2 = 142.80

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total exc. tax
	82 (rrp_et)	73.80 (ecost_et)	70 (unitprice_et)	2	140 (unitprice_et * 2)
Total exc. tax	164 (sum(rrp_et*quantity))	147.60 (sum(ecost_et * quantity))	140 (sum(unitprice_et * quantity))		147.60 (sum(unit_price_et * quantity))
VAT order 5% (gst_order)		7.38
VAT receipt 2% (gst_receiv)			2.8		2.8
Total tax inc.		154.98	142.80		142.80
Total + shipping					147.80

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0	147.80	852.20

Configuration 2 : 1 / 1

• List prices include tax
• Invoice prices include tax

Supplier configuration:

• gstrate_order = 5 %
• gstrate_receiv = gstrate_order = 5% (in this case, there is no difference between both gst rate)
• discount: 10 %

Order:

• rrp_et = rrp_it / ( 1 + gst_rate_order ) = 82 / ( 1 + 5 % ) = 78.095238095 =~ 78.10
• rrp_it = 82
• ecost_et = rrp_et * ( 1 -discount ) = 78.10 * ( 1 -10 % ) =70.29
• ecost_it = rrp_it * ( 1 -discount ) = 82 * ( 1 -10 % ) =73.80
• quantity = 2
• gst_order = quantity * ecost_et * gstrate_order = 2 * 70.29 * 5 % = 7.029 =~ 7.03

Ordering

Basket view

rrp tax exc.	ecost tax exc.	rrp tax inc.	ecost tax inc.	quantity	total tax exc.	total tax inc.	VAT %	VAT
78.10	70.29	82	73.80	2	140.58	147.60	5	7.03

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	147.60	0	852.40

Basket group printing (3 pages)

Total RRP tax exc.	Total RRP tax inc.	VAT %	VAT	Total discount	Total tax exc.	Total tax inc.
156.20	164	5	7.03	15.62	140.58	147.60

Quantity	RRP tax exc.	RRP tax inc.	Discount %	Discount price	VAT rate	Total tax exc.	Total tax inc.
2	78.10	82	10	7.81	5	140.58	147.60

Receiving

If shipping cost is tax excluded, the system will calculate the shipping cost including tax

• freight_it = freight_et + freight_gst

If shipping cost tax included, the system will directly populate "freight_it".

The shipping cost which is added to the total will always be the value including tax (freight_it).

Two options :

If shipping cost is excluding tax and shipping cost tax rate = 10% (freight_gstrate) :

• freight_et = 4.55
• freight_gst = 0.45
• freight_it = 5 (including tax)

If shipping cost is including tax :

• freight_it = 5 (including tax).

2 cases:

The unit price is the same

• quantityreceived = 2
• unitprice_et : unitprice_it / ( 1 +gstrate_receiv ) = 73.80 / ( 1 + 5 % ) = 70.285714286 =~ 70.29
• unitprice_it : 73.80
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 70.29 * 5 % = 7.03

Parcel

Already received orders table:

Quantity	ecost exc. tax	ecost inc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	70.29	73.80	70.29	73.80	5	7.03	unitprice_it*2 = 147.60

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total inc. tax
	82 (rrp_it)	73.80 (ecost_it)	73.80 (unitprice_it)	2	147.60 (unitprice_it * 2)
Total exc. tax	156.19 (sum(rrp_et*quantity))	140.58 sum(ecost_et * quantity)	140.58 sum(unitprice_et * quantity)	sum(quantity)	140.58 (sum(unit_price_et * quantity))
VAT order 5%		7.03	7.03		7.03
Total tax inc.					147.60 (sum(unitprice_it) *quantity)
Total + shipping					152.60

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0	152.60	847.40

The unit price has changed

• quantityreceived = 2
• unitprice_et : unitprice_it / ( 1 +gstrate_receiv ) = 70 / ( 1 + 5 % ) = 66.67
• unitprice_it : 70 (modified on receiving)
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 66.67 * 5 % = 6.67

Parcel

Already received orders table:

Quantity	ecost exc. tax	ecost inc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	70.29	73.80	66.67	70	5	6.67	unitprice_it*2 = 140

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total inc. tax
	82 (rrp_it)	73.80 (ecost_it)	70 (unitprice_it)	2	140 (unitprice_it * 2)
Total exc. tax	156.19(sum(rrp_et*quantity))	140.58 (sum(ecost_et * quantity))	133.34 (sum(unitprice_et * quantity))	2 (sum(quantity))	133.34 (sum(unitprice_et * quantity))
VAT order 5%		7.03	6.67		6.67
Total tax inc.					140 sum(unitprice_it * quantity)
Total + shipping					145

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0	145	855

Configuration 3 : 1 / 0

• List prices include tax
• Invoice prices don't include tax

Supplier configuration:

• gstrate_order: 5 %
• gstrate_receiv: 2 %
• discount: 10 %

Order:

• rrp_et = rrp_it / ( 1 + gst_rate_order ) = 82 / ( 1 + 5 % ) = 78.10
• rrp_it = 82
• ecost_et = rrp_et * ( 1 -discount ) =78.10 * ( 1 -10 % ) =70.29
• ecost_it = rrp_it * ( 1 -discount ) = 82 * ( 1 -10 % ) = 73.80
• quantity = 2
• gst_order = quantity * ecost_et * gstrate_order = 2 * 70.29 * 5 % = 7.03

Ordering

Basket view

rrp tax exc.	ecost tax exc.	rrp tax inc.	ecost tax inc.	quantity	total tax exc.	total tax inc.	VAT %	VAT
78.10	70.29	82	73.80	2	140.58	147.60	5	7.03

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	147.60	0	852.40

Basket group printing (3 pages)

Total RRP tax exc.	Total RRP tax inc.	VAT %	VAT	Total discount	Total tax exc.	Total tax inc.
156.20	164	5	7.03	15.62	140.58	147.60

Quantity	RRP tax exc.	RRP tax inc.	Discount %	Discount price	VAT rate	Total tax exc.	Total tax inc.
2	78.10	82	10	7.81	5	140.58	147.60

Receiving

If shipping cost is tax excluded, the system will calculate the shipping cost including tax

• freight_it = freight_et + freight_gst

If shipping cost tax included, the system will directly populate "freight_it".

The shipping cost which is added to the total will always be the value including tax (freight_it).

Two options :

If shipping cost is excluding tax and shipping cost tax rate = 10% (freight_gstrate) :

• freight_et = 4.55
• freight_gst = 0.45
• freight_it = 5 (including tax)

If shipping cost is including tax :

• freight_it = 5 (including tax).

2 cases:

The unit price is the same

• quantityreceived = 2
• unitprice_et = 70.29
• unitprice_it : unitprice_et * ( 1 +gstrate_receiv ) = 70.29 * ( 1 + 5 % ) = 71.69
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 73.80 * 2 % = 2.8116

Parcel

Already received orders table:

Quantity	ecost exc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	70.29	70.29	71.69	2	2.81	unitprice_it*2 = 143.38

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total exc. tax
	78.10 (rrp_et)	70.29 (ecost_et)	70.29 (unitprice_et)	2	140.58 (unitprice_et * 2)
Total exc. tax	156.20 (sum(rrp_et*quantity))	140.58 (sum(ecost_et * quantity))	140.58 (sum(unitprice_et * quantity))		140.58 (sum(unit_price_et * quantity))
VAT order 5% (gst_order)		7.028
VAT receipt 2% (gst_receiv)			2.81		2.81
Total tax inc.		147.60	143.38		143.38
Total + shipping					148.38

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0	148.38	851.62

The unit price has changed

Same as before.

Configuration 4 : 0 / 1

• List prices don't include tax
• Invoice prices include tax

Supplier configuration:

• gstrate_order = 5 %
• gstrate_receiv = gstrate_order = 5% (in this case, there is no difference between both gst rate)
• discount: 10 %

Order:

• rrp_et = 82
• rrp_it = rrp_et * ( 1 + gstrate_order ) = 82 * ( 1 + 5 % ) = 86.10
• ecost_et = rrp_et * ( 1 -discount ) = 82 * ( 1 - 10 % ) = 73.80
• ecost_it = rrp_et * ( 1 + gstrate_order ) * ( 1 - discount ) = 82 * ( 1 + 5% ) * ( 1 - 10 % ) = 77.49
• quantity = 2
• gst_order = quantity * ecost_et * gstrate_order = 2 * 73.80 * 5 % = 7.38

Ordering

Basket view

rrp tax exc.	ecost tax exc.	rrp tax inc.	ecost tax inc.	quantity	total tax exc.	total tax inc.	VAT %	VAT
82	73.80	86.10	77.49	2	147.6	154.98	5	7.38

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	154.98 (was 147.60)	0	845.02 (was 852.40)

Basket group printing (3 pages)

Total RRP tax exc.	Total RRP tax inc.	VAT %	VAT	Total discount	Total tax exc.	Total tax inc.
164	172.20	5	7.38	16.40	147.60	154.98

Quantity	RRP tax exc.	RRP tax inc.	Discount %	Discount price	VAT rate	Total tax exc.	Total tax inc.
2	82	86.10	10	8.20	5	147.60	154.98

Receiving

If shipping cost is tax excluded, the system will calculate the shipping cost including tax

• freight_it = freight_et + freight_gst

If shipping cost tax included, the system will directly populate "freight_it".

The shipping cost which is added to the total will always be the value including tax (freight_it).

Two options :

If shipping cost is excluding tax and shipping cost tax rate = 10% (freight_gstrate) :

• freight_et = 4.55
• freight_gst = 0.45
• freight_it = 5 (including tax)

If shipping cost is including tax :

• freight_it = 5 (including tax).

2 cases:

The unit price is the same

• quantityreceived = 2
• unitprice_et = unitprice_it / ( 1 + gstrate_receiv ) = 73.80
• unitprice_it : 77.49
• gst_receiv = quantityreceived * unitprice_et * gstrate_receiv = 2 * 73.80 * 5 % = 7.38

Parcel

Already received orders table:

Quantity	ecost exc. tax	ecost inc. tax	unit price exc. tax	unit price inc. tax	VAT rate	VAT	Total
2	73.80	77.49	73.80	77.49	5	7.38	unitprice_it*2 = 154.98

Invoice

	Public Price	Ordered price	Billing price	Quantity	Total inc. tax
	82 (rrp_et)	73.80 (ecost_et)	73.80 (unitprice_et)	2	147.60 (unitprice_et * 2)
Total exc. tax	164 (sum(rrp_et*quantity))	147.60 (sum(ecost_et * quantity))	147.60 (sum(unitprice_et * quantity))	sum(quantity)	147.60 (sum(unit_price_et * quantity))
VAT order 5%		7.38	7.38		7.38
Total tax inc.		154.98	154.98		154.98 (sum(unitprice_it) *quantity)
Total + shipping					159.98

Fund view (acq home)

fund code	fund name	active	owner	library	amount	ordered	spent	available
your fund code	your fund name	1 or 0	your owner	your library name	1000	0	159.98	840.02

The unit price has changed

Same as before.

Possible future enhancements

• Allow to enter the amount of a discount or tax instead of just the percentage. This can help avoid rounding differences between Koha's and the vendor's calculations. It would also work for special offers, where you get a discount of x for an item.
• Make sure the form fields display in the order that the calculation is done and add hints to indicate what is happening (this might already be the case).
• Add a new field for the type of tax, I recently learned that other taxes than GST might apply, especially when ordering in different countries. The type of tax is needed to determine who the money will be paid to and if it needs to be registered for statistics. --Kfischer 11:07, 27 August 2014 (EDT)

Use cases / library requirements

Not all of this can or should be covered with this rewrite, but I think it will be good to start gathering some use cases and try to make it not harder to implement them at a later point in time:

• A book including a DVD: one order line, but book and dvd are listed separately and different tax rates need to be applied. Similar would be true for a print journal with parallel online access. It seems this can happen in the EU and also in India.
• The library licenses an e-book package or a database and the cost is divided between different faculties/departments/funds.
• A library imports a book and customs need to be paid separately from the vendor. As I understand it, this would mean 2 different invoices for 1 order line for different vendors.
• A library orders a book from a distributor, but the invoice needs to be paid to the publisher. This would be: order from one vendor, invoice for another.

Think of this I wonder if all the amount, tax and fund related fields apart from the total will need to be moved to another table (1:n) at some point to allow to divide up the cost for an item into several parts. --Kfischer 09:35, 29 August 2014 (EDT)

Questions / notes

Freight/shipping cost

• Not sure the aqorders.shipmentcost is consistent. Maybe we should only keep the DB field aqinvoice.shipmentcost.
  • As I understand it currently we calculate the freight cost only in aqinvoice per invoice and there is no code for filling the aqorders.freight column. I think there once was and there is interest in having a different approach to the extra costs by dividing them up and add them to the order lines or by funds Bug 9598 - pro-rate shipping] and another bug I can't seem to be able to locate right now) --Kfischer 11:05, 27 August 2014 (EDT)
  • Just done this for Edifact Invoices and Shipment cost is per invoice not per item line ( of course some suppliers will differ ) there are also other per invoice charges possible which I had to subsume under shipment cost. Also these may be subject to a different tax rate to the items shipped --colinc 11:46, 28 August 2014

Migration

• I think for libraries starting fresh with acq this should work ok, but I am worried about how to make this work for libaries already using the acquisition module. Is there a plan yet? Some thoughts:
  • Backup the acq data somewhere before/during update.
  • Make sure all 'old' data is still accessible.

--Kfischer 09:35, 29 August 2014 (EDT)

Terminology

• I feel one of the problems we currently have is inconsistent use of terminology and that it's not so clear what goes in which field and when it is filed and updated. Some suggestions:
  • Don't use VAT or GST, but use tax everywhere maybe? It seems gst is only used in a few countries, tax would be neutral.
  • I think RRP somewhat equals list price? But not totally sure.
  • Is the unit price what we refer to as actual price in the templates?

--Kfischer 11:05, 27 August 2014 (EDT)

• I second Katrin's points on terminology. We should probably avoid truncated names as they add to the confusion [I've seen at least 3 contradictory defnitions of the RRP acronym --colinc 08:68 28 August 2014

Rounding

• I am not sure if that is currently the case, but I think rounding should be consistent. I am worried that we currently have some inconsistencies there, partly because some of the fields in aqorders are decimal(13,2) while others are decimal(28,6). --Kfischer 11:05, 27 August 2014 (EDT)
• A consistent approach should be adopted. Best practice would be to do all calculations in cents (i.e. as integers) and store in a consistent money format (e.g. descimal(x.2)) --colinc 08:68 28 August 2014
• I think having some standard that is not only used in the acq module, but also in accounts would be good. It seems Kyle is proposing decimal(28,6) for the accounts rewrite. --Kfischer 09:35, 29 August 2014 (EDT)

Database structure

• Wondering if storing 2 prices, one inclusive of tax, one exclusive is the best approach why not price (excluding tax) and tax. Just thinking that the most frequent tax related queries are on the lines of whats the total tax on this order/invoice/month's receipts?. And that query is probably probably of much more interest than multiple versions of the line price. --colinc 16:26 28 August 2014
• It's a good question - I think finding a good structure for the fields is the important part. But I am not sure about the advantages/disadvantages of both approaches. --Kfischer 09:35, 29 August 2014 (EDT)