|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| VAT Tax calculation on INVOICE INSERT |
| Author |
Message |
|
|
| Posted : 2007-01-30 10:17:51 |
Hi,
How do should I specify the TAX for the following transaction.
One line includes tax at 14% and the INClusive amount is GBP 105.00
If is specify "AmountIncludesVAT" as 0
GBP 92.10 results in Invoice total of 104.99
GBP 92.11 results in Invoice total of 105.01
This seems to be a rounding problem.
If I specify amounts Include VAT then the VAT is added again on INSERT, resluting a VAT amount being taxed again.
Our invoicing system stores to 12 digits after the decimal, what is most QODBC can do or is there a way of using the VAT inclusive total, or perhaps a better way?
The line item should be GBP 105.00 inclusive of VAT and the total should be the same.
I must add that we do have invoices that are mixed, and some lines do not include tax.
thanks
Johan
INSERT INTO "InvoiceLine"
("CustomerRefListID", "TemplateRefListID", "RefNumber", "DueDate", "TxnDate","TermsRefListID", "AmountIncludesVAT", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","InvoiceLineClassRefListID","FQSaveToCache")
VALUES
('8590000-1170063160','D0000-893257009','D150', {d'2007-02-28'}, {d'2007-02-01'}, '90000-893256262', 0 , '700000-875726161', 'WS', 92.11, 92.11, '30002-875726162','90001-1128336164',0) |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-30 10:32:03 |
Tax is calculated by QuickBooks, so there's always rounding issues. I don't have a UK copy of QuickBooks, but try:
INSERT INTO "InvoiceLine"
("CustomerRefListID", "TemplateRefListID", "RefNumber", "DueDate", "TxnDate","TermsRefListID", "AmountIncludesVAT", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineClassRefListID","FQSaveToCache")
VALUES
('8590000-1170063160','D0000-893257009','D150', {d'2007-02-28'}, {d'2007-02-01'}, '90000-893256262', 1 , '700000-875726161', 'WS', 105.00, 105.00, '90001-1128336164',0)
otherwise:
INSERT INTO "InvoiceLine"
("CustomerRefListID", "TemplateRefListID", "RefNumber", "DueDate", "TxnDate","TermsRefListID", "AmountIncludesVAT", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","InvoiceLineClassRefListID","FQSaveToCache")
VALUES
('8590000-1170063160','D0000-893257009','D150', {d'2007-02-28'}, {d'2007-02-01'}, '90000-893256262', 0 , '700000-875726161', 'WS', 92.1053, 92.10, '30002-875726162','90001-1128336164',0)
and let me know the results so I can help other UK developers. |
|
|
|
|
|
| Posted : 2007-01-30 10:52:43 |
The 1st
INSERT INTO "InvoiceLine"
("CustomerRefListID", "TemplateRefListID", "RefNumber", "DueDate", "TxnDate","TermsRefListID", "AmountIncludesVAT", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineTaxCodeRefListID","InvoiceLineClassRefListID","FQSaveToCache")
VALUES
('8590000-1170063160','D0000-893257009','t153', {d'2007-02-28'}, {d'2007-02-01'}, '90000-893256262', 1 , '700000-875726161', 'WS', 105.00, 105.00, '30002-875726162','90001-1128336164',0)
Note I have "AmountIncludesVAT" as 1
This results in an invoice with a total of 119.70, it adds VAT again, even though the IncludesVAT flag is true.
It adds VAT to the 2 amount fields too. (rate and amt)
The 2nd results in QODBC DATA truncated error being returned.
This seems to be happening if
"InvoiceLineRate" digits after decimal is more than 5
"InvoiceLineAmount", digits after decimal is more than 2
Looking at this currently it seems only way to do it is to use 2nd method but there is the risk of rounding problems, especially on large invoices with many lines. Ideally VAT lines should be specified with VAT included amounts and then QB can work the amounts back to exvat if needed, but the line totals and the Invoice totals will be accurate.
Am I missing something somewhere?
thanks
Johan
BTW Thank you for fantastic service and the speed of responding to posts so far has blown me away.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-30 11:09:27 |
In my first example, I had removed the "InvoiceLineTaxCodeRefListID" to stop the double tax calculation. Your result still had it in.
Thanks for the decimal settings for "InvoiceLineRate" and "InvoiceLineAmount". You can actually use InvoiceLineQuantity and InvoiceLineRate without InvoiceLineAmount to calculate to 4 decimal places (like I did with 92.1053), so you can get the value correct for QuickBooks to use when calculating VAT. |
|
|
|
|
|
| Posted : 2007-01-30 11:21:37 |
Hi Tom
1st example
If I leave this "InvoiceLineTaxCodeRefListID" out, how do I handle invoicelines on the same invoice where some have tax and other don't.
2nd example
I will code the bacth process now, and once I start with INSERTING real data will check and see if there are enough decimals to handle the VAT calculation.
thanks
Johan
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-30 13:36:03 |
| For mixed tax lines you might need to stick with the 2nd example. |
|
|
|
|