Tax Inquiry - Does not show output tax info (Page 2 of 5)

Hi Sledge

If you are entering your purchase invoice via GL Items (Supplier Invoice) and not Direct Supplier Invoices then you will face this. We have discussed this in this thread and did not come to any conclusion. FA 2.4.1 and above have a problem with the TAX Inquiry an TAX Report and don't know why no one identified it.

rafat is correct.  If tax g/l accounts are used on the supplier invoice, either by quick entry or g/l line items, the reg_type is NULL.

That is unfortunate because the tax inquiry report is not expecting that.   Without reg_type set correctly in the database, the tax inquiry or tax reports have to guess.

So the fix for Tax Inquiry has to be something like:

+++ b/core/gl/includes/db/gl_db_trans.inc
@@ -532,12 +532,12 @@ function get_tax_summary($from, $to, $also_zero_purchases=false)
                                        ), amount*ex_rate,0)) payable,
 
                                SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)*
-                               IF(reg_type=".TR_INPUT
+                               IF((reg_type=".TR_INPUT." OR trans_type =".ST_SUPPINVOICE.")"
                                        . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate")
                                        .", net_amount*ex_rate, 0)) net_input,
 
                                SUM(IF(trans_type IN(".ST_SUPPCREDIT."),-1,1)*
-                               IF(reg_type=".TR_INPUT
+                               IF((reg_type=".TR_INPUT." OR trans_type =".ST_SUPPINVOICE.")"
                                        . ($also_zero_purchases ? '': " AND tax_type_id AND taxrec.rate ") 
                                        .", amount*ex_rate, 0)) collectible,
                                taxrec.rate,

But what about G/J entries, deposits and payments?

Woow, lots of comments! Thank you for that and your encouraging words. I would love to stay, I prefer open source, I love FA. In generally I try to contribute to OS communities. I've spent quite some time with the FA DB when I started 8 years ago, mainly to try to import exports from my bank account. From that time I know the FA DB is complex for an 'outsider', because of the nature of proper accounting and accounting rules. Even more as I have to trust it for my company, it has to be correct, it is not a kindergarten.

I know you guys are willing to help me, but I don't have enough faith in myself, I can not oversee the implications of what I'm doing in the database. As the error is easily re-produced (take current FA, load the 'official' dutch COA from the repository, do some accounting and check if the tax report matches) I was hoping for some support (read: fix) from the core community who knows what they're talking about. I fully understand if this is too much from me to expect, there is no blaming in here. But I've tried and drowning (I hate to admit, but it's true).

As said in my previous post, I'm not gone yet, but I do realise I can't stick with old version forever (with risk of not being able to update at all in a couple major versions from now). In the upcoming days I will work my way trough recent comments.

Much appreciated guys, really!

(edited typo's)

@rafat

must be a typo somewhere.   I put the changes temporarily in my fork:
Tax Inquiry gl/includes/db/gl_db_trans.inc
Tax Report reporting/rep709.php

caveat: FA creates a bogus "net amount" (based on the tax amount and tax rate) for manually created tax g/l account transactions, so that will look strange.

caveat: this is only for g/l tax accounts on the supplier invoice.  It does not handle g/j, deposits, or payments to tax g/l accounts.

It is obvious to me that FA is not expecting users to create manual tax g/l accounts transactions.  Rather, it expects that the supplier be assigned a tax group and taxes be assigned through the tax system.  Then all seems to work fine.

@Braath Waate.. Your Tax Inquiry works as I expected it, it shows the right figures. The Tax Report rep709 also includes the tax in the auto generated Delivery Note (DN) which is not correct. If I remove the DN transactions from the report then the figures tally with the Tax Inquiry. Where shall I look into the report to remove the DN Transactions? Thanks for your help.

@Braath Waate.. Thanks a lot ..this is perfect.. both Inquiry and Report tally.. even with my manual spreadsheet. This is what I was looking for. Thanks again.

The official Dutch (nl_NL) Chart has been updated to match the fields and field orders in the FA24extensions repo.

I implemented the changes from @Braath Waate but no difference.

Still only show the Customer invoices and credit notes and supllier invoices and credit notes. Now bank payments and receipts VAT shows.

Will try reports fro 2.3.25 and see what happens.

FA 2.3.26 release version has the same rep709.php as the v2.3.25 has. Ignoring whitespace changes, the effective diff between the FA 2.3.26 version and FA 2.4.6+ version is attached to experiment with.

Interesting.

The sql change in the report for 2.4.6 is this:

-            AND taxrec.trans_type <> ".ST_CUSTDELIVERY."
+            AND !ISNULL(taxrec.reg_type)

This makes all the difference because NULL reg_types occur in supplier invoices, general journal, payments and deposits if one uses a tax g/l account.  The 2.4.6 code filters them out.

I modified the 2.4.6 report for rafat:

            AND (!ISNULL(taxrec.reg_type) OR taxrec.trans_type=".ST_SUPPINVOICE.")

to handle the NULL  reg_type for supplier invoices, but I mentioned that I knew it did not handle g/j, payments or deposits.

I would suggest next reverting the 2.4.6 sql change while retaining the GROUP BY taxrec.id that I added.  Here is the link rep709.php that perhaps seahawk could test.

Does anyone know who made this change and why?

@Braath Waate
Although the report shows the Bank Payments and Deposits..etc  unfortunately it does not account for them in the totals. They are just dropped.  It is the same result with the Inquiry.