Database Error - Voided Customer Payment

I found a problem with error message after I voided a customer payment.  This problem has been encountered by other users:

Ref: https://frontaccounting.com/punbb/viewtopic.php?id=4892

When attempt to open a voided customer payment by clicking on the Customer Payment #, the following error message appeared:

DATABASE ERROR : no debtor trans found for given params
sql that failed was : SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, com.memo_,bank_act,0_bank_accounts.bank_name, 0_bank_accounts.bank_account_name, 0_bank_accounts.account_type AS BankTransType, 0_bank_accounts.bank_curr_code, 0_bank_trans.amount as bank_amount FROM 0_debtor_trans trans LEFT JOIN 0_comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN 0_shippers ON 0_shippers.shipper_id=trans.ship_via, 0_debtors_master cust, 0_bank_trans, 0_bank_accounts WHERE trans.trans_no='22' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND 0_bank_trans.trans_no ='22' AND 0_bank_trans.type=12 AND 0_bank_trans.amount != 0 AND 0_bank_accounts.id=0_bank_trans.bank_act

I managed to find a line in  sales/includes/db/cust_trans_db.inc that somehow caused the database error.

    if ($trans_type == ST_CUSTPAYMENT || $trans_type == ST_BANKDEPOSIT) {
        // it's a payment so also get the bank account
        $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_id)."
            AND ".TB_PREF."bank_trans.type=$trans_type
            AND ".TB_PREF."bank_trans.amount != 0  <<== the "!=" is causing the issue
            AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act ";

As stated above, this != 0 is causing the problem.  If I remove "!", then the voided payment will appear normal.  But the normal customer payment will have the same error.

Due to my limited php knowledge, I am not able to fix it. 

I hope this can be fixed as soon as possible or it looks ugly when the user click on the transaction.

Thanks!

/Mark

The one that I encountered with the error message was version 2.3.22.  And I have made a clean installation of version 2.3.25. 

I created a direct invoice and enter the customer payment transaction for this invoice.  Then I void this payment transaction. I went back to list Customer Transactions, and click on the number (#) of this voided payment transaction:

1.  On version 2.3.22:  I got the error as mentioned in my previous post.
2.  On version 2.3.25:  I got a new browser windows totally blank. (If there is no issue, I would expect to see the transaction and with the void note below)

I found the "!" in the file "cust_trans_db.inc" causing this issue as mentioned in my previous post.

I hope that this can be duplicated by other users and it can be rectified.  Otherwise, either a blank screen or error message made it look a little unappealing although I did not see this error caused other issues to the other transactions.

Thank you.

/Mark

This is the error in the log file after I clicked on the voided payment #:

[19-Jun-2016 22:22:28 Asia/Singapore] <b>DATABASE ERROR :</b> no debtor trans found for given params<br>sql that failed was : SELECT trans.*,ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total,cust.name AS DebtorName, cust.address, cust.curr_code, cust.tax_id, com.memo_,bank_act,0_bank_accounts.bank_name, 0_bank_accounts.bank_account_name,
            0_bank_accounts.account_type AS BankTransType,
            0_bank_accounts.bank_curr_code,
            0_bank_trans.amount as bank_amount FROM 0_debtor_trans trans
                    LEFT JOIN 0_comments com ON trans.type=com.type AND trans.trans_no=com.id
                    LEFT JOIN 0_shippers ON 0_shippers.shipper_id=trans.ship_via,
                    0_debtors_master cust, 0_bank_trans, 0_bank_accounts WHERE trans.trans_no='3'
        AND trans.type='12'
        AND trans.debtor_no=cust.debtor_no AND 0_bank_trans.trans_no ='3'
            AND 0_bank_trans.type=12
            AND 0_bank_trans.amount != 0
            AND 0_bank_accounts.id=0_bank_trans.bank_act <br><br><br>

/Mark

@apmuthu,

Thanks for your quick reply.  When I void a payment, the corresponding invoice became outstanding.  And I verified and confirmed this.   I did not void an invoice prior to the payment.  That is, I voided the payment first.  Even though I did not void the invoice, this error will still appear.

I checked the ledger, when the customer payment is voided, it disappeared from the GL entry.

/Mark