Advanced Retail (UK) Ltd
Company Wiki

These are the steps to follow when VAT has been added to a foreign Customer Order. There is alot of order specific data in these scripts but basically only the order number, order guid and invoice guid will need changing throughout. Also check out tickets #9791 and #53256 where there is a reversal due to the wrong order number having been used.

I WILL WRITE A SPROC TO AUTOMATICALLY DO EVERYTHING AT A LATER DATE.

Check the customer has been marked as non_VATable. This can't be done through the GUI:

select * from transactor t
where company_name like '%petworld%'

begin transaction
update transactor set non_vatable = 1 where company_name like '%petworld%' and non_vatable = 0
commit transaction

Check the product doesn't have any vat on it for the price list concerned. The proce list can be found on the invoice.

select list_no, * from order_hdr where invoice_num = 1109  -- be absolutely sure you have the correct order, check the customer against guid_cust on the order
select * from order_det where guid_hdr = 'A01682CD-544A-45B1-930B-B5DADB220474'
select * from order_invoice where guid_order = 'A01682CD-544A-45B1-930B-B5DADB220474' --3C5993C9-E059-4019-B9CD-9C35424370A0
select * from invoice_hdr where guid = '3C5993C9-E059-4019-B9CD-9C35424370A0'
select * from invoice_det where guid_hdr = '3C5993C9-E059-4019-B9CD-9C35424370A0'

Check there is VAT, check again using this script to prove the VAT has been removed:

select sum(order_line_val) from invoice_det where guid_hdr = '3C5993C9-E059-4019-B9CD-9C35424370A0'
select order_val,sale_val,vat_val, * from invoice_hdr where guid = '3C5993C9-E059-4019-B9CD-9C35424370A0'
select sum(order_line_val) from order_det where guid_hdr = 'A01682CD-544A-45B1-930B-B5DADB220474'

Before the update secure the invoice and order details, just in case

select * into _1109_order_det from order_det where guid_hdr = 'A01682CD-544A-45B1-930B-B5DADB220474'
select * into _1109_order_hdr from order_hdr where guid = 'A01682CD-544A-45B1-930B-B5DADB220474'
select * into _1109_invoice_det from invoice_det where guid_hdr = '3C5993C9-E059-4019-B9CD-9C35424370A0'
select * into _1109_invoice_hdr from invoice_hdr where guid = '3C5993C9-E059-4019-B9CD-9C35424370A0'

Finally the update:

begin transaction
update order_det set vat_code = 2, order_vat_val = 0 where guid_hdr = 'A01682CD-544A-45B1-930B-B5DADB220474' and vat_code = 7

update invoice_det set vat_code = 2, order_line_val = order_line_val - order_vat_val, sale_line_val = sale_line_val - sale_vat_val where guid_hdr = '3C5993C9-E059-4019-B9CD-9C35424370A0' and vat_code = 7
update invoice_det set order_vat_val = 0, sale_vat_val = 0 where guid_hdr = '3C5993C9-E059-4019-B9CD-9C35424370A0'

-- not changing payments which will be the original value
update invoice_hdr set order_val = order_val - vat_val, sale_val = order_val - vat_val where guid = '3C5993C9-E059-4019-B9CD-9C35424370A0'
update invoice_hdr set vat_val = 0 where guid = '3C5993C9-E059-4019-B9CD-9C35424370A0'
-- commit transaction
-- rollback transaction