Re: Tax Inquiry - Does not show output tax info

@Braat Waate
Great work. All correct now.
Just a small cosmetic ..In the report for Bank Payments and Deposits its showing the Memo field instead of the Ref#. Also the Name field is blank. Can this be corrected?
Its correct for Sales and Supplier Invoices.

Re: Tax Inquiry - Does not show output tax info

@rafat

Sorry, the memo is in the tax table and not the ref# for bank payments, although with some more sql it may be possible to retrieve it.

Note that for suppliers the report prints the supplier reference number, usually the supplier check number.   But FA lacks bank check numbers, so if one enters the bank check number into the memo field, then it appears on the report, which is actually what we do.

Here is the blank name fix:
rep_709.php.

Re: Tax Inquiry - Does not show output tax info

Thanks Braath

Still.. the Name field is blank.. a typo somewhere...

Re: Tax Inquiry - Does not show output tax info

@rafat

Double check if your rep_709.php has the code change:

+++ b/core/reporting/rep709.php
@@ -35,9 +35,10 @@ function getTaxTransactions($from, $to)
 
        $sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
                    taxrec.net_amount*ex_rate AS net_amount,
-                               IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.", 
-                                       IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), 
-                                       IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
+                IF(ISNULL(supp.supp_name),
+                    IF(ISNULL(debt.name),
+                        IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), debt.name),
+                        supp.supp_name) as name,
                                branch.br_name
                FROM ".TB_PREF."trans_tax_details taxrec
                LEFT JOIN ".TB_PREF."tax_types tt

With the new code, if the transaction (including bank payments and deposits) has a supplier name or customer name, it will be used.  Then if it has a miscellaneous name, it will be used.  Finally, it would use the memo.

So I think the name field must be non-blank, unless the payment was a quickentry and the memo was blank.

Re: Tax Inquiry - Does not show output tax info

@Braath Waate,

Cant get it to show the name field. I will test more to find out why.

Re: Tax Inquiry - Does not show output tax info

@Braath Waate,

Are you suggesting a correction in the core? I have some problems following this thread.

Please be a little more specific if I should change something in the core. Thanks in advance.

/Joe

Re: Tax Inquiry - Does not show output tax info

I think it needs more testing.  I was hoping seahawk and perhaps others would try out the code and see if it works on their sites.  rafat isn't able to see the name on payments/deposit.  But yes, if this fixes something that people need, then both the tax report and tax inquiry should be included in the core.

Re: Tax Inquiry - Does not show output tax info

Yes we need more time to test this..
The name field to me is important but more important are the transactions and the totals..
After Braath fork  they are 100% correct. I compare them to the manual spreadsheet I have, as well as the report I have from FA. I am a happy lad.
FA core is very correct in Tax reports that are only based on customer/supplier invoices... after the correction that Braath's  done to supplier invoices thru gl entry..this needs to be accounted for in the core of course.

Re: Tax Inquiry - Does not show output tax info

@Braath Waate

Been testing ..and cant get the Name to show unless the name is a Customer or a Supplier. The Miscellaneous entry name does not show. I remember I had this problem with Bank Statements and I had to use your pull request #26 to get it to show.

Re: Tax Inquiry - Does not show output tax info

@rafat

The tax report fails to show miscellaneous names because they are missing from the gl_trans table.  The gl_trans table neglects to store the person_id for miscellaneous names because the function add_gl_trans in gl_db_trans.inc calls this code:

    if (!is_subledger_account($account) || $account==get_company_pref('grn_clearing_act'))
        $person_id = $person_type_id = null;

The function is_subledger_account returns true only for customer or supplier names, so clears the name for miscellaneous names.

I have run into this before and do not understand why it does this, but the names can be retrieved with an additional sql step.  So this is a bug that I suspect you may see on other reports as well.

I updated the rep_709.php with this change.

Re: Tax Inquiry - Does not show output tax info

Yes Sir,

It works. I now can see the Misc Name field in the report.. also for Bank Deposits/Payments.

Many Thanks

Re: Tax Inquiry - Does not show output tax info

@Seahawk
Are you able to test the above and see if all OK?  I am asking this because the 2.3.25 report is full of bugs if you are still using it..it has double entries and is somehow accounting for void entries when I test it. The above is working ok for me. It makes sense if you test it so we can have another user environment experience.

63 (edited by rafat 02/19/2019 02:30:12 pm)

Re: Tax Inquiry - Does not show output tax info

@Braath Waate

Manual Journal entries are showing correctly in rep709 but not in the Tax Inquiry..The report is correct but Inquiry fails to show the
right totals. I know you told us about it in the above thread. Can this be included. ? Also as you said above getting the Ref# for others than Customer/Supplier Invoices  needs more SQL effort.. Can this be included please?
Thanks

64 (edited by seahawk 02/19/2019 04:24:05 pm)

Re: Tax Inquiry - Does not show output tax info

Sorry Lads, been out of action for a while and forgot to subscribe to the topic. I will test now and report back, Just made a new installation with 2.4.6

Regards

Wynand

Re: Tax Inquiry - Does not show output tax info

OK have checked it and it seems fine to me.

When you process a bank payment or a journal, there are two memo fields. The one is at the end of the line item and the other one is below the transactions.

The report pulls through the bottom memo and not the line item memo. Clients normally use the line item menu, so that is not pulling through it gives a number 4 or 6

Wynand

Re: Tax Inquiry - Does not show output tax info

@seahawk

Were you testing the 2.4.6 tax report or my version?

If you were using my version, and the report showed a bank payment or journal with the bottom memo, was the person a quick entry or a supplier, customer, or miscellaneous name?

Re: Tax Inquiry - Does not show output tax info

@seahawk
@rafat

I updated the rep709.php to show more of the bank payment/deposit fields (account name, bottom memo, line memo).   It uses an additional line rather than trying to pick and choose what to display in the reference number column.  Let me know what you think.

Re: Tax Inquiry - Does not show output tax info

It looks good I like it.. and its correct.
Last request please can Journal Entries be included in the Inquiry..it is shown in the report but not accounted for the Inquiry.
Thanks again.

Re: Tax Inquiry - Does not show output tax info

Another happy user who found this thread useful:

http://frontaccounting.com/punbb/viewtopic.php?id=8003

Re: Tax Inquiry - Does not show output tax info

@rafat
@itronics

I think the reason why tax inquiry and the tax report are not working in 2.4 is because of the changes introduced by adding reg_type to the trans_tax_details table.  This commit added the reg_type and changed how these pages work.  As I said earlier in this thread, it would be nice if reg_type was not NULL in several cases which perhaps has defeated the reason why reg_type was added to 2.4.   Here is the latest gl/includes/db/gl_db_trans.inc which seems to fix the tax inquiry page for journal entries by removing more code that was deliberately introduced in 2.4 and which I do not understand.

Re: Tax Inquiry - Does not show output tax info

@Braath Waate and @notrinos

As usual your modification works perfectly. I tested it with two different environments.
As you mentioned above.. the Tax System of FA 2.4 is broken... and needs serious consideration.. 
I am confident the FA team will be able to fix soon.

Re: Tax Inquiry - Does not show output tax info

@Braath Waate

I'm not sure I understand the problem right, but the change in gl_db_trans.inc effectively excludes from tax summary all journal entries which user explicitly included into input tax register, which is not acceptable. Instead the change proposed includes bank payments and deposits, transactions which are not taxable per se, at least in EU tax system.

I understand that tax rules may differ in other jurisdictions, so I would like to include a fix solving problems you have encountered. But to avoid any misunderstanding, it is much better to maintain more complex issues like this via our Mantis bugtracker. Mantis really helps in bugfixing process, encouraging clear problem description. Please put the issue on bugtracker together with some short step by step description how to reproduce the issue. You can also attach proposed code change for completness (I'm not sure all the changes are in gl_db_trans.inc).

Janusz

Re: Tax Inquiry - Does not show output tax info

@itronics

I would be happy to open a bug report in Mantis, however I also do not claim to understand the issues either which is why this thread is so long and no bug report was ever opened.  This thread is over a year old and I entered the discussion only recently when sledge stated that he did not see any purchases tax and I wondered why.   Then rafat chimed in and stated one way it could arise: by adding g/l tax accounts on a supplier invoice.   I did some testing and found out that these tax entries had reg_type set to NULL and the 2.4 code was ignoring that.   Others have mentioned that the results returned by the 2.3 pages and 2.4 pages differ.

To summarize how I thought it should work:

1. the tax inquiry page and the tax report should have the same totals and no duplicate entries
2. g/l entries to tax accounts on supplier invoices should included as input tax
3. bank payments to tax accounts should be included as input tax
4. g/ journal entries to/from tax accounts where the tax register box is clicked should be included as input or output tax.

With these assumptions,  the tax inquiry page/report would give a picture of how much is owed to a tax authority.  For me, that would be useful, because I can use these pages to see how my collections are tracking with my payments which I could use to discover if my collections or payments are in error, or if I need to use g/j entries to reset these accounts and declare income/loss on the difference (for example, if I was only collecting say 5% but really owed 6% on the tax forms that were paid).

Note that I use different g/l accounts to track taxes paid on actual purchases so that these g/l accounts do not skew the above.

But perhaps this was not the way these reports were intended to work in 2.4 and I respect that.   I only entered this discussion out of curiosity and am satisfied with how my modified pages work on my site, which has a different core anyway.

Re: Tax Inquiry - Does not show output tax info

I have gone through the whole thread today. Unfortunately none of my Sites are using Purchase Taxes so I never hear of any bug from my clients. I will try to test it myself and shall revert.

www.boxygen.pk

Re: Tax Inquiry - Does not show output tax info

@Braath  Waate

Thank you for explanation. Most of your assumptions are right, but I think there is some  misunderstanding about purpose of tax report. Under VAT regulations as introduced in many countries (and I think this apply also to other taxing schemes), every tax payer has to maintain tax registers, for all transactions subject to tax. Both bank payments and bank deposits (due to tax refunds) from/to tax authorities are not taxable transactions, so should not appear in tax report. At any time, current balance on tax account shows current company tax duties.

Basically most amounts in tax report will mirror sales and purchase invoice transactions, but sometimes there are other more specific GL transactions which should be included in either input or output tax registers. For example sometimes company can lawfully withdraw some part of tax related to overdue payments from customers. This can be recorded as journal entry moving tax between input/output and some clearing GL account, and this should be visible in tax report.

So in other words, tax report is official document, which calculates company's tax duties for selected period. Sums from the report are then declared to tax authorities, and company pays  (or receive) tax with Bank Payment/Bank Deposit which is outside scope of tax report.

Summarizing, third of your assumption is not valid. But if there is any scenario when assumptions  1,2, or 4 seems still not work, we have  aug which should be fixed.

J.