Joe

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

======================
Hi @apmuthu,

I am novice in programming and I encountered the same problem in version 2.3.25.

When the said line mentioned by Mark0300 is removed, the error disappeared.  But I couldn't figure out why that line would cause the error as it is just a “if” condition.

/KH

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

Actually any payments received from the customer for that invoice should be either voided or re-assigned to be advances received on account before voiding any associated direct invoice.

Check the customer ledger account to see if the customer payment is still there and void it there if necessary.

@joe: What is the policy on treating customer payments received on voiding associated direct invoice?

[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

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

If there is a customer payment or bank deposit involved the amount should not be 0 (!=0). So where is the problem?

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

This issue is fixed in:
https://frontaccounting.com/punbb/viewtopic.php?id=4528

Upgrade to FA v2.3.20 and apply the fixes. See posts:
https://frontaccounting.com/punbb/viewtopic.php?pid=19496#p19496
and
https://frontaccounting.com/punbb/viewtopic.php?pid=19720#p19720

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,1_bank_accounts.bank_name, 1_bank_accounts.bank_account_name, 1_bank_accounts.account_type AS BankTransType, 1_bank_accounts.bank_curr_code, 1_bank_trans.amount as bank_amount FROM 1_debtor_trans trans LEFT JOIN 1_comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN 1_shippers ON 1_shippers.shipper_id=trans.ship_via, 1_debtors_master cust, 1_bank_trans, 1_bank_accounts WHERE trans.trans_no='11' AND trans.type='12' AND trans.debtor_no=cust.debtor_no AND 1_bank_trans.trans_no ='11' AND 1_bank_trans.type=12 AND 1_bank_trans.amount != 0 AND 1_bank_accounts.id=1_bank_trans.bank_act

I voided this payment after voiding a sales invoice sucessfully. I was able to void the referenced customer payment and it now shows Debit of 0.00. It is just that if I try to look at the transaction that I get the DATABASE ERROR message. I am able to open the voided Sales Invoice and associated Delivery Note just fine.

Ray