• Registered: 05/24/2011
  • Posts: 2

Topic: Tax Inquiry does not pick up Supplier Credit Notes?

Firstly, thanks for a great application! I'm happily using version 2.3.4 after years with SQL-ledger.

I may be incorrect, but I think there is a bug with the 'Tax Inquiry' report, in that it does not seem to include GST/VAT inputs from Supplier Credit Notes.
Specifically, it seems to ignore transaction types ST_SUPPCREDIT  from the trans_tax_details table in the get_tax_summary function in gl/includes/db/gl_db_trans.inc. ST_CUSTCREDIT is included correctly, but I don't see ST_SUPPCREDIT.

Regards,

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

Re: Tax Inquiry does not pick up Supplier Credit Notes?

I cannot reproduce this. ST_SUPPCREDIT (21) types are included correctly here.

Please try this SQL statement directly in your phpMyAdmin.

SELECT 
    IF(trans_type=11 || trans_type=20 || trans_type=0,-1,1)*
    IF(trans_type=2 || trans_type=10 || (trans_type=0 AND amount<0)
         || trans_type=11, net_amount*ex_rate,0) net_output,

    IF(trans_type=11 || trans_type=20 || trans_type=0,-1,1)*
    IF(trans_type=2 || trans_type=10 || (trans_type=0 AND amount<0)
         || trans_type=11, amount*ex_rate,0) payable,

    IF(trans_type=11 || trans_type=20,-1,1)*
    IF(trans_type=2 || trans_type=10 || (trans_type=0 AND amount<0)
         || trans_type=11, 0, net_amount*ex_rate) net_input,

    IF(trans_type=11 || trans_type=20,-1,1)*
    IF(trans_type=2 || trans_type=10 || (trans_type=0 AND amount<0)
         || trans_type=11, 0, amount*ex_rate) collectible,
    taxrec.trans_type,
    taxrec.trans_no,
    taxrec.tran_date,
    taxrec.rate,
    ttype.id,
    ttype.name
        FROM 0_tax_types ttype,
             0_trans_tax_details taxrec
        WHERE taxrec.tax_type_id=ttype.id
            AND taxrec.trans_type != 13
            AND taxrec.tran_date >= '2011-05-01'
            AND taxrec.tran_date <= '2011-05-31'

You may need to change the tran_date period. Here you should see the trans_type 21 (ST_SUPPCREDIT).

/Joe

  • Registered: 05/24/2011
  • Posts: 2

Re: Tax Inquiry does not pick up Supplier Credit Notes?

Hi Joe,

Thanks for the quick response and for taking the time to investigate my question.

Your correct, the SUPPCREDIT transactions ARE being picked up in your query, and  also in my tax query. Sorry for wasting your time.

I had been comparing the Tax Inquiry report against a GL Inquiry of my VAT account, and saw differences. In the Tax Query, the Credit Note VAT amounts show in the Collectable column, whereas in the GL Inquiry, they show in the Credit column. So I was getting different summation figures. I had a quick look at the gl_db_trans.inc file, and as I did'nt see the SUPPCREDIT constant, I assumed that was the problem. Looking in detail at your supplied query, I see that these constants are only used to get the sign correct for the calculations, not to restrict the search.

Thanks again for your help.