Re: Tax Inquiry - Does not show output tax info

Applying CoA differences between the official one and mine is trivial as it mostly just has schema changes in field ordering. The real issue comes when using the new pattern of document numbering from FA 2.3.x to FA 2.4.x. If your FA 2.4.x has errors on using my CoA but no errors on using the official CoA, then your official CoA schema will need to be compared with mine in a diff tool like WinMerge or directly in a MySQL client like SQLyog.

The FA userbase would be sad to see a longtime user like you go if you do! We are here to do any hand holding as required.

27 (edited by Braath Waate 01/27/2019 02:54:08 pm)

Re: Tax Inquiry - Does not show output tax info

@sledge

Before you go, it would be nice to learn why the tax inquiry report is showing zeroes for purchases in your database.  The tax inquiry report and sql is simple so it should not be difficult to find out why.  Because you already are familiar with phpmyadmin, you could use that to examine your database.

1. Click on your trans_tax_details file.
2. edit inline the select statement:

SELECT * FROM `trans_tax_details` where reg_type=1 and tax_type_id != 0

This shows you all the purchase taxes.   In order for the tax_inquiry report to show non-zero data, the trans_type field must be valid (20 for purchase invoices), the dates must be valid, the amount/rate fields must be non-zero, and most importantly the tax_type_id must point to a valid tax type.

Look it up by clicking on the tax_types table.  Is it a valid tax type id?

If all the data is valid, then the tax inquiry report will show non-zero purchased amounts.

Feel free to post your results.

Re: Tax Inquiry - Does not show output tax info

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.

Re: Tax Inquiry - Does not show output tax info

Sledge..just to add.. dont leave my friend.. you will not find neater, more friendly and rich of functionality as well as good performance than FA. Also the team around FA are always ..always very helpful... They will solve your problem...

Re: Tax Inquiry - Does not show output tax info

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?

Re: Tax Inquiry - Does not show output tax info

@joe: In the light of discussions here and in the other thread, please set this issue to rest.

32 (edited by sledge 01/27/2019 08:02:26 pm)

Re: Tax Inquiry - Does not show output tax info

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)

Re: Tax Inquiry - Does not show output tax info

@Braath Waate
Cant get it to work. If I implement the above I get a blank screen on all FA and a DB error in the log.

34 (edited by Braath Waate 01/29/2019 01:28:51 pm)

Re: Tax Inquiry - Does not show output tax info

@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.

Re: Tax Inquiry - Does not show output tax info

Official Dutch CoA has some infirmities is excess/missing fields as also some other CoAs. In my unofficial FA24extensions repo, I have corrected all of them thus far:
There was no difference between the official nl_NL-default.sql and the one in my unofficial FA24extensions repo except that the lone fiscal year start date has been advanced from 2017 to 2018 and the presence of a missing sys_prefs flag:

('ref_no_auto_increase','setup.company', 'tinyint', 1, '0'),

It has since been updated in my repo to have the corrections for missing fields, not needed fields as well.

Furthermore, there are some differences between the official chart and the one auto-generated from my script.

Official:
INSERT INTO `0_bank_accounts` VALUES
('1010', '2', 'XYZ Bank betaalrekening', '12345678', 'XYZ Bank', 'Adres bank 1\n1234 AB PLAATS BANK', 'EUR', '1', '1', '9040', '0000-00-00 00:00:00', '0', '0'),
('1000', '3', 'Kas', 'N/A', 'N/A', 'N/A', 'EUR', '0', '2', '9040', '0000-00-00 00:00:00', '0', '0'),
('1020', '0', 'XYZ Bank spaarrekening', '12345678', 'XYZ Bank', 'Adres bank 1 \n1234 AB PLAATS BANK', 'EUR', '0', '4', '9040', '0000-00-00 00:00:00', '0', '0');

Generated:
INSERT INTO `0_bank_accounts` (`account_code`,`account_type`,`bank_account_name`,`bank_account_number`,`bank_name`,`bank_address`,`bank_curr_code`,`dflt_curr_act`,`id`,`bank_charge_act`,`last_reconciled_date`,`ending_reconcile_balance`,`inactive`) VALUES
 ('1060', '0', 'Current account', 'N/A', 'N/A', NULL, 'EUR', '1', '1', '5690', '0000-00-00 00:00:00', '0', '0')
,('1065', '3', 'Petty Cash account', 'N/A', 'N/A', NULL, 'EUR', '0', '2', '5690', '0000-00-00 00:00:00', '0', '0');

Similar data changes in chart class, chart_master, etc.

Official audit_trail:   `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
Generated audit_trail:  `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Official bank_trans:    `bank_trans_type_id` int(10) unsigned DEFAULT NULL,
Generated bank_trans: No such field in FA 2.4

Official bom:  fields parent, component, loc_code are all CHAR fields.
   Redundant:   KEY `parent` (`parent`,`loc_code`),
   PRIMARY KEY (`parent`,`component`,`workcentre_added`,`loc_code`),

Generated bom: The above fields are all VARCHAR
       No KEY:   `parent`
   PRIMARY KEY (`parent`,`loc_code`,`component`,`workcentre_added`),

Official  budget_trans: No memo_ field after `account` field
Generated budget_trans:   `memo_` tinytext NOT NULL,

Re: Tax Inquiry - Does not show output tax info

@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.

Re: Tax Inquiry - Does not show output tax info

@rafat   I fixed the tax report and updated the link to the new code in my previous comment.

Re: Tax Inquiry - Does not show output tax info

@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.

Re: Tax Inquiry - Does not show output tax info

Looks like Rafat & Braath have some history on a related/the subject. Apmuthu has found some additional information.

Seen some happy faces. But I'm not sure where this leaves me at the moment #confused

Re: Tax Inquiry - Does not show output tax info

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

Re: Tax Inquiry - Does not show output tax info

What is the official verdict now. I see that when I draw a reports in my GL reports/ GL accounts for my Input and Output Vat all the transactions that was done through the bank payments or Cash payments shows correctly as llocated in the line items when processing the transactions.

When I draw the Tax Report only transactions that was done via the Sales and Purchase invoices show in the report. This means that all my bank costs does not show in the tax report and since they are small amounts I did not realise it until today that I had to submit the supporting documents to the Receiver of revenue. Is there now a Tax report that actually works that will include Bank payments and Deposits?

Urgently needed. This is now a serious flaw (BUG)

Wynand

Re: Tax Inquiry - Does not show output tax info

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.

Wynand

Re: Tax Inquiry - Does not show output tax info

Report 709 from version 2.3.25 works and give the bank payments and bank deposits VAT correctly.

Do not know what have changes since. Will use the 2.3.25 report until further correct updates.

Wynand

Re: Tax Inquiry - Does not show output tax info

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.

Post's attachments

FA23_to24_rep709_diff.diff 3.5 kb, 8 downloads since 2019-02-04 

You don't have the permssions to download the attachments of this post.

Re: Tax Inquiry - Does not show output tax info

apmuthu wrote:

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

Does this mean, I can do a new upgrade of the software and apply updated COA from the repository? That is awesome Apmuthu, thanks!
Now, in my understanding there is still something going on with rep709 which might or might not be part of my initially issue. I will keep a close eye on this topic.

46 (edited by Braath Waate 02/07/2019 12:05:26 pm)

Re: Tax Inquiry - Does not show output tax info

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?

Re: Tax Inquiry - Does not show output tax info

@joe: any commit needed?

@sledge: The existing CoA you are using must be compared with the one in my repo and suitable changes done to synch it with the latter among possibly others.

Re: Tax Inquiry - Does not show output tax info

@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.

Re: Tax Inquiry - Does not show output tax info

Been reading about this issue before and came to this thread:
http://frontaccounting.com/punbb/viewtopic.php?id=7087
apmuthu's recommendation still the safest option as I think FA's tax system design is as is.

Re: Tax Inquiry - Does not show output tax info

@rafat

Thanks for testing this.  I updated gl_db_trans.inc (tax inquiry) and rep_709.php.  The inquiry should match the report and include payments and deposits as well as supplier invoices.