(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

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

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.

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,

Who has written this plug-in Tax Inquiry?

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

Joe

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?

Is there a fix for this problem?

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?