• vezz
  • New member
  • Offline
  • Registered: 02/24/2015
  • Posts: 4

Topic: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

The plug-in "tax inquiry (cash basis) is not calculating input tax correctly.  Bank Payments are negative, which is fine, but Supplier Payments are positive, meaning these amounts are being deducted from the input tax total. Please see below:


Type    Ref    #    Date    Payer/Payee    Tax Amount    Gross Output/Input

Supplier Payment     131     133     04.11.2014     Energy     44.50     267.01        
Supplier Payment     132     134     04.11.2014     Rent     70.00     430.00        
Bank Payment     123     125     06.11.2014     Motor     -13.88     -83.26        
Bank Payment     115     117     07.11.2014     Shop expenses     -283.32     -1,699.90        
Supplier Payment     133     135     10.11.2014     Purchases     21.67     42.69        

Supplier payments are entered via Payments to Suppliers in the Purchases tab.
Bank payments are made directly into bank via Payments in the Banking & General Ledger.

How can this be rectified?

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

@joe: this needs to be corrected.

Post's attachments

Wrong_Tax_Details_Report_Total.png 31.4 kb, file has never been downloaded. 

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

apmuthu's Website

  • vezz
  • New member
  • Offline
  • Registered: 02/24/2015
  • Posts: 4

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

Hello,

Is there a fix for this problem?

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

The devs are on holiday....... not yet fixed. Easter is drawing near....

apmuthu's Website

  • vezz
  • New member
  • Offline
  • Registered: 02/24/2015
  • Posts: 4

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

Hello,

Easter has now passed and I have to file a new VAT return. Is there a fix or do I have to do it manually?

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,089

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

Hello guys.

Who has written this plug-in Tax Inquiry?

As I can see this is not part of the core.

Joe

7 Reply by apmuthu (edited by apmuthu 05/06/2015 05:23:09 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

Extension: rep_tax_cash_basis
Author: Alastair Robertson (KwikPay, NZ)

Lines 166 and 193 in the file modules/rep_tax_cash_basis/includes/tax_cash_db.inc :

            ttd.amount*ex_rate collectible,

should be:

            -1*ttd.amount*ex_rate collectible,

Kindly verify and revert.

Patch:

--- modules/rep_tax_cash_basis/includes/tax_cash_db.inc    Mon Oct 14 01:44:10 2013
+++ modules/rep_tax_cash_basis/includes/tax_cash_db.inc    Wed May 06 10:49:31 2015
@@ -163,7 +163,7 @@
             0 payable,
             bt.amount*ex_rate gross_input,
             ttd.net_amount*ex_rate net_input,
-            ttd.amount*ex_rate collectible,
+            -1*ttd.amount*ex_rate collectible,
             ttd.rate,
             bt.type,
             bt.person_type_id,
@@ -190,7 +190,7 @@
             0 payable,
             bt.amount*ex_rate gross_input,
             ttd.net_amount*ex_rate net_input,
-            ttd.amount*ex_rate collectible,
+            -1*ttd.amount*ex_rate collectible,
             ttd.rate,
             bt.type,
             bt.person_type_id,
Post's attachments

Patch.zip 7.8 kb, 3 downloads since 2015-05-06 

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

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

@elax's 0VAT fix is not part of the core. Where is elax's 0VAT fix and when and where is it used?

apmuthu's Website

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

apologies... I did not realise elax_tax_support_files was not so available. I can definitely recommend it for UK accounting, indeed with Box 7 requirements and 0VAT in returns etc I would find FA  not so appealing without, which would be a shame as I honestly think it is fantastic in general.

I believe something on the same lines should be considered for the core. With a combination of Elax's files and the Cash Basis module I have a system that works really well for me - Thank you so much.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,094

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

If the said elax_tax_support_files are not proprietary / commercial you can post it here for peer review and possible inclusion into the core.

apmuthu's Website

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

I have deleted my previous post as it was not the correct reason for the problem. I will explain the problem and see if anyone else has this issue.

I process invoices with multiple lines with different VATs associated with different entries. So some entries may be 0VAT and some may be 20% VAT entries. These all appear in the trans_tax_details table for the particular invoice(debt) as multiple rows all with the correct respective net_amount and amount entries.

The Select statement

        SELECT
            'Output',
            ca.amt gross_output,
            (ca.amt/(ttd.net_amount+ttd.amount)) * ttd.net_amount * ex_rate net_output,
            (ca.amt/(ttd.net_amount+ttd.amount)) * ttd.amount*ex_rate payable,

is performed to determine the correct amount if the invoice is not settled (allocated) in full. However, as I see it this only works if the invoice is summarised in one row with one VAT. That is, ca.amt is compared with each row and if this doesn't relate directly to the ttd.net_amount and ttd.amount in one row it doesn't work?

Am I reading this wrongly?

As I noted before I deleted my earlier post using

  SELECT
            'Output',
            ca.amt gross_output,
            ttd.net_amount * ex_rate net_output,
            ttd.amount*ex_rate payable,

will work but technically only if the allocation is in full.

Can anyone please confirm if it's an error or if I am seeing this wrongly??

Many thanks

12 Reply by dls (edited by dls 05/08/2015 04:09:24 pm)

  • dls
  • Senior Member
  • Offline
  • From: UK
  • Registered: 02/23/2010
  • Posts: 63

Re: Plug-in Tax Inquiry - Cash Basis reports incorrect input tax

I believe that the following code replacing the first section of the SELECT statement in the tax_cash_db.inc file sorts the problem discussed in the previous post - could someone please try this and compare the results with the original and confirm?

(Please note I am tracking 0%VAT transactions and 20%VAT transactions - but this should effect any multi VAT listings in any single invoice)

    $sql = "SELECT
            'Output',
            ca.amt gross_output,       
           (ca.amt/(
                         SELECT sum(ttd1.net_amount) + sum(ttd1.amount)
                          FROM ".TB_PREF."trans_tax_details ttd1
                          WHERE ttd1.trans_type = ttd.trans_type AND ttd1.trans_no = ttd.trans_no
                         )) * ttd.net_amount * ex_rate net_output,
           (ca.amt/(
                        SELECT sum(ttd1.net_amount) + sum(ttd1.amount)
                        FROM ".TB_PREF."trans_tax_details ttd1
                        WHERE ttd1.trans_type = ttd.trans_type AND ttd1.trans_no = ttd.trans_no
                        )) * ttd.amount*ex_rate payable,
            0 gross_input,
            0 net_input,
            0 collectible,
            ttd.rate,
            bt.type,
            bt.person_type_id,
            bt.person_id,
            ttd.trans_no,
            tt.id,
            tt.name,
            bt.trans_date
        FROM ".TB_PREF."bank_trans bt
        INNER JOIN ".TB_PREF."cust_allocations ca
            ON bt.type = ca.trans_type_from
            AND bt.trans_no = ca.trans_no_from
        INNER JOIN ".TB_PREF."debtor_trans dt
            ON dt.type = ca.trans_type_from
            AND dt.trans_no = ca.trans_no_from
        INNER JOIN ".TB_PREF."trans_tax_details ttd
            ON ttd.trans_type = ca.trans_type_to
            AND ttd.trans_no = ca.trans_no_to
        INNER JOIN ".TB_PREF."tax_types tt
            ON tt.id = ttd.tax_type_id
        WHERE bt.trans_date >= '$fromdate'
            AND bt.trans_date <= '$todate'
    UNION ALL


Many thanks