Search options (Page 8 of 11)

@itronics

Thanks for your explanation which really helps to define what is and what isn't a bug in these reports.

I think that if only the third assumption is mistaken, then perhaps I could add a check box that could add in deposits or payments.    With this box checked, then it is easy to reconcile the report/inquiry with G/L inquiry of a given tax account, since the balances should then match (except if tax register is not checked in case 4). 

I do believe that the current 2.4.4 reports have bugs based on assumptions 1,2, an 4.   I will wait until boxygen reports the results of his investigation and then open a bug report and offer my code as a possible solution.

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

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

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

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

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

There is no support for restricting access to a given dimension.

If the sides of the business (brokerage/property management) are indeed completely separate and do not share any transactions in any bank account or credit card, then separate companies are in order.

However, if any banking is shared, it is better to use one company with two dimensions.   You cannot restrict your bookkeeper from viewing both dimensions.  However, you can limit the ability to edit transactions (Access Setup->Edit other users transactions), so if you enter all the transactions for the brokerage side, the bookkeeper cannot change them.

Quickbooks classes are the same as FA dimensions.

Tags were introduced for filtering G/L accounts on reports. They could work for you by using separate G/L accounts (or sub accounts) where transactions are posted and tagging each G/L account .   Every property transaction would need the correct G/L account.   Support for tags is limited.  Tagging would be useful if you need to examine profitability of groups of properties.

Simpler would be to assign a dimension to each property.  Every property transaction would need a dimension.

FA supports two levels of dimensions.   You can have a single company with two dimensions (brokerage and property management).  The property management side can have property dimensions.  Then you can generate separate P/L statements for the brokerage and property management side, as well as separate P/L for each property.

You are better off with using dimensions than creating two sets of books in most cases.  That is because bank accounts and expenses can be shared within a single company.  For example, if you have a single credit card and pay for expenses for both sides of the company, reconciliation is simple if both sides are part of the same company but impossible with two sets of books.

You should experiment by creating a company and trying out your ideas and look at the P/L statements.  Run those by your accountant or bookkeeper and see if they make sense.   You will save yourself and others time if you set up a system that everyone is comfortable with.

Try to keep the number of G/L accounts/dimensions to a bare minimum.   The more you have, the more complex is the transaction data entry.   For example, if you pay for some item and it is not clear which property it is for or is shared between multiple properties, that bogs down data entry.   You may find that keeping data entry separate for each property is a nightmare and not worth the effort in tracking profitability.

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

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

You are right that my suggestion does not work, it caused sales order entry to completely fail.

The problem is not just limited to tax exempt suppliers.  STRICT_ALL_TABLES for also caused sales order entry for an order with a tax exempt item to fail as well:

Cannot save trans tax details
error code : 1366
error message : Incorrect integer value: '' for column 'tax_type_id' at row 1
sql that failed was : INSERT INTO 1_trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type) VALUES ('13','6083','2019-01-24','','','1',1,'3.15','0','auto',NULL)

The sql failure is the same as the tax exempt supplier problem:

DATABASE ERROR : Cannot save trans tax details
error code : 1366
error message : Incorrect integer value: '' for column 'tax_type_id' at row 1
sql that failed was : INSERT INTO 0_trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type) VALUES ('20','5','2018-12-31','','','1',0,'10','0','rr8','1')

On my site, these errors are easy to reproduce.  On the demo database, I changed DinoSaurius to Tax Exempt.  Then I tried to create a direct supplier invoice with a single item.