Topic: Error when trying to edit Journal Entry

Under 'Banking and General Ledger/Journal Inquiry' I have a list of journal entries. For most of the entries, when I select the edit icon a new window opens that allows me to edit the entry.

However, I have a couple of entries for which the windows that comes up shows the following error:

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,bank_accounts.bank_name, bank_accounts.bank_account_name, bank_accounts.account_type AS BankTransType, bank_accounts.bank_curr_code, bank_trans.amount as bank_amount FROM debtor_trans trans LEFT JOIN comments com ON trans.type=com.type AND trans.trans_no=com.id LEFT JOIN shippers ON shippers.shipper_id=trans.ship_via, debtors_master cust, bank_trans, bank_accounts WHERE trans.trans_no='360' AND trans.type='2' AND trans.debtor_no=cust.debtor_no AND bank_trans.trans_no ='360' AND bank_trans.type=2 AND bank_trans.amount != 0 AND bank_accounts.id=bank_trans.bank_act

I checked the debtor_trans table and it's empty.
Any suggestion on how to fix this?

At least one of the transactions was entered with the import transaction module (I don't know if this makes a difference).
I am using FA 2.3.22.
Any input would be appreciated.
Thanks,
Ion

2 (edited by apmuthu 04/08/2015 11:18:24 pm)

Re: Error when trying to edit Journal Entry

The following is the main fix for the journal inquiry issue since we need to remove NULLs in the SQLs:

--- gl/includes/db/gl_db_trans.inc    Fri Jan 16 08:56:28 1970
+++ gl/includes/db/gl_db_trans.inc    Fri Jan 16 08:56:28 1970
@@ -605,14 +605,14 @@
 function get_sql_for_journal_inquiry($filter, $from, $to, $ref='', $memo='', $alsoclosed=false)
 {
 
-    $sql = "SELECT    IF(ISNULL(a.gl_seq),0,a.gl_seq) as gl_seq,
+    $sql = "SELECT    COALESCE(a.gl_seq,0) as gl_seq,
         gl.tran_date,
         gl.type,
         gl.type_no,
-        refs.reference,
+        COALESCE(refs.reference,'') AS reference,
         SUM(IF(gl.amount>0, gl.amount,0)) as amount,
-        com.memo_,
-        IF(ISNULL(u.user_id),'',u.user_id) as user_id
+        COALESCE(com.memo_,'') AS memo_,
+        COALESCE(u.user_id,'') as user_id
         FROM ".TB_PREF."gl_trans as gl
          LEFT JOIN ".TB_PREF."audit_trail as a ON
             (gl.type=a.type AND gl.type_no=a.trans_no)
@@ -625,11 +625,11 @@
         WHERE gl.tran_date >= '" . date2sql($from) . "'
         AND gl.tran_date <= '" . date2sql($to) . "'
         AND gl.amount!=0";
-    if ($ref) {
-        $sql .= " AND reference LIKE ". db_escape("%$ref%");
+    if (!empty($ref)) {
+        $sql .= " AND refs.reference LIKE '%". db_escape($ref) . "%'";
     }
-    if ($memo) {
-        $sql .= " AND com.memo_ LIKE ". db_escape("%$memo%");
+    if (!empty($memo)) {
+        $sql .= " AND com.memo_ LIKE '%". db_escape($memo) . "%'";
     }
     if ($filter != -1) {
         $sql .= " AND gl.type=".db_escape($filter);

Now we also need to tolerate NULLs in fields in GROUP BY statements which otherwise evaluate to a count of 0 records in the db_pager.inc and make sure that no negative LIMIT is part of the SELECT clause since the first value of the page is 0 and on deducting 1 for previous page will return a negative number causing the sql to end in LIMIT -10, 10:

--- includes/db_pager.inc    Fri Jan 16 08:56:28 1970
+++ includes/db_pager.inc    Fri Jan 16 08:56:28 1970
@@ -325,6 +325,7 @@
 
         $page_len = $this->page_len;
         $offset = ($this->curr_page - 1) * $page_len;
+        $offset = ($offset < 0 ? 0 : $offset);
 
         $sql .= " LIMIT $offset, $page_len";
 
@@ -345,11 +346,19 @@
                 return false;
             $row = db_fetch_row($result);
             $this->rec_count = $row[0];
+             // 2015-04-09 apmuthu: try original sql since count = 0 if group by clause has NULLs
+            if (!$row[0] && !empty($this->group)) {
+                $sql = $this->_sql_gen(false);
+                $result = db_query($sql, 'Error reading record set');
+                if ($result == false) 
+                    return false;
+                $this->rec_count = db_num_rows($result);
+            }
               $this->max_page = $this->page_len ?
                 ceil($this->rec_count/$this->page_len) : 0;
         
             if ($go_debug) { // FIX - need column name parsing, but for now:
-                // check if field names are set explicite in col def
+                // check if field names are set explicitly in col def
                 // for all initially ordered columns
                 foreach ($this->columns as $col) {
                        if (isset($col['ord']) && $col['ord'] != '' 
Post's attachments

Journal_Inquiry_patch.zip 56 kb, 1 downloads since 2015-04-08 

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

3 (edited by apmuthu 04/09/2015 02:42:54 pm)

Re: Error when trying to edit Journal Entry

It is possible that you imported your customer without an entry in the debtors_master table or more likely your debtor_trans table's import did not go well.

Your SQL has been reformatted for execution in the en_US-demo CoA as Company 1:

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
      , bank_accounts.bank_name
      , bank_accounts.bank_account_name
      , bank_accounts.account_type AS BankTransType
      , bank_accounts.bank_curr_code
      , 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 shippers ON shippers.shipper_id=trans.ship_via
    , 1_debtors_master cust
    , 1_bank_trans bank_trans
    , 1_bank_accounts bank_accounts
WHERE trans.trans_no='360' 
  AND trans.type='2' 
  AND trans.debtor_no=cust.debtor_no 
  AND bank_trans.trans_no ='360' 
  AND bank_trans.type=2 
  AND bank_trans.amount != 0 
  AND bank_accounts.id=bank_trans.bank_act;

The source of this SQL is in the file core/sales/includes/db/cust_trans_db.inc in function get_customer_trans($trans_id, $trans_type). The said error comes up only when $go_debug = 1 is set in config.php.

Re: Error when trying to edit Journal Entry

Hi apmuthu,

I've applied the patch you suggested, still no luck.
My config.php has $go_debug = 0 however the error still shows up.
You're right, the import failed to create entries in the debtor_trans table so that's why when I click the edit button it fails to bring the edit screen up.

In the mean time I have upgraded to FA 2.3.24 and now the window that pops up instead of showing the error shows a blank page! I can only get the error from the error.log file.

What I ended up doing is update the entries in the bank_trans table and changed the person_type_id from 2 to 0 for these transactions.
Ion

Re: Error when trying to edit Journal Entry

Changes in config file values to reflect in FA may need a logout and login again if session variables are depended upon.

Your workaround could be useful for others till the dev fixes it. Thanks.