change in applicable tax rate (vat) from one year to another

This is just to log my own thoughts and actions upon a change in national vat (tax) rate in Switzerland from 8% to 7.7% starting 2018. This happens from time to time…
How best to apply the changes to SuiteCRM invoices? (we’re currently only using the AOS_invoices module, similar would apply to quotes)

Please let me know if you have any better, simpler or more efficient ideas!

  1. add new vat rate (7.7) to dropdown “vat_list”
  2. (when the time is right) change the default vat rate to the new value in module Line Items (via Studio). All newly created records will automatically use the new vat-rate 7.7.
  3. change vat value in all applicable invoices that are already saved in the system but have not yet been reviewed, confirmed and sent to the customer. See my sql-statement for this below.
    The Filter setting (WHERE statements) must be adapted to your needs! Make sure you don’t overwrite the vat rates for the wrong records.

Because calculations of all invoice amounts (vat, totals) are done in the browser via JS code, either:
4.a) turn off inline-edit for some invoice field that MUST be edited in your company’s workflow before the invoice get’s printed and sent out. That way you’re sure that JS-code will be run before sending out the invoice to the customer.
OR
4.b) cycle through all respective invoices manually in edit mode and save them, to make sure that new vat rate is correctly applied to the calculated invoice amounts.

In my case 4.b) takes just over a few minutes so that’s what I chose to do.


Here’s my SQL code for step 3:

UPDATE `aos_products_quotes` as l
JOIN `aos_invoices` as i ON l.`parent_id` = i.`id`
SET l.`vat` = '7.7'
WHERE l.`parent_type` = 'AOS_Invoices' AND i.`status` = 'neu' AND date(i.`invoice_date`) > '2018-01-15';

Again, your WHERE statement will have to be fitted to your specific invoice module architecture and workflow.


Anybody have a better idea? naturally it’d be nice to be able to recalculate all applicable invoices in the background, instead of via JS, but that would require recreating the same logic in PHP and that’s just not worth it to me. Any other tricks?

1 Like

Forgot something important: need to filter for a specific vat value to replace. We have many line items with vat = 0 e.g., those should remain 0 of course. So here’s an updated query:

UPDATE `aos_products_quotes` as l
JOIN `aos_invoices` as i ON l.`parent_id` = i.`id`
SET l.`vat` = '7.7'
WHERE l.`vat` = '8.0' AND l.`parent_type` = 'AOS_Invoices' AND i.`status` = 'neu' AND date(i.`invoice_date`) > '2018-01-15';
1 Like