• From: Dutch Caribbean
  • Registered: 12/02/2013
  • Posts: 39

Topic: Supplier payment fails

Version 2.3.19

I am not sure whether this is the same bugs as mentioned here: https://frontaccounting.com/punbb/viewtopic.php?id=4539. It looks different on details.

The problem is this:
Banking -> General Ledger -> Payments
Select Pay to: Supplier
Select a supplier
Enter an amount
Press "Add item"
Process Payment
This error message appears in a red banner:
"The settled amount has to be greater than 0"
(Translated from Dutch, apologies if not literally correct)
http://www.linkels.net/~jlinkels/links/eds3.jpg

Now when I pay to a supplier which is configured for a different currency, an additional entry appears, showing the amount in the foreign currency. Then the transaction does go through.
http://www.linkels.net/~jlinkels/links/eds4.jpg

However, when I then go to Purchases -> Supplier Transactions and I try to retrieve the payment just made, I get a pop-up with this error message:

Databasefout : query for bank transaction
error code : 1305
error message : FUNCTION frontacc.IFNULL does not exist
sql that failed was : SELECT bt.*, act.*, IFNULL(abs(dt.ov_amount), 
IFNULL (abs(st.ov_amount), bt.amount)) settled_amount, IFNULL(abs(dt.ov_amount/bt.amount), 
IFNULL (abs(st.ov_amount/bt.amount), 1)) settle_rate, 
IFNULL(debtor.curr_code, IFNULL (supplier.curr_code, act.bank_curr_code)) settle_curr FROM
1_bank_trans bt LEFT JOIN 1_debtor_trans dt ON dt.type=bt.type AND
dt.trans_no=bt.trans_no LEFT JOIN 
1_debtors_master debtor ON debtor.debtor_no = dt.debtor_no 
LEFT JOIN 1_supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no 
LEFT JOIN 1_suppliers supplier ON 
supplier.supplier_id = st.supplier_id, 1_bank_accounts act 
WHERE act.id=bt.bank_act AND bt.type='1' AND bt.trans_no = '7' 
ORDER BY trans_date, bt.id

However, in the second case (with the foreign currency) the bank account is credited, so I assume the transaction has been completed.

To be sure it did not have anything to do with the state of the supplier, I created two new suppliers. One in standard currency, one in foreign currency. Except for the currency I left everything default.

I tried it in the on-line training database (2.3.18), which was succesful.

I also downloaded the stable repository hg clone https://frontaccounting.hg.sourceforge.net:8000/hgroot/frontaccounting/default. Then I copied the directory /var/lib/vz/repos/mercurial/frontac/default/purchasing/includes/db to the production directory. No changes.

I was a bit reluctant to sync the complete cloned repository with the production directory, although that should be safe I assume. Right? Would it make a difference?

jlinkels

  • From: Dutch Caribbean
  • Registered: 12/02/2013
  • Posts: 39

Re: Supplier payment fails

Update:

When I went to Purchases -> Payments for Suppliers and tried to make a payment for an invoice I just received, this failed. No error message, just nothing happened.

When I restored the directory /var/www/account/purchasing/includes/db to the state it had after installing the stable version the same action was successful.

If I complicated thing by updating only the directory /var/www/account/purchasing/includes/db to the repository clone, my apologies. On the other hand it might indicate a regression error.

The original problem as posted in the first post remains the same, regardless of the state of the /var/www/account/purchasing/includes/db directory.

jlinkels

3 Reply by apmuthu (edited by apmuthu 01/03/2014 07:48:52 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Supplier payment fails

In the purchasing/includes/db folder, the two files:
    grn_db.inc
and
    suppalloc_db.inc
have changed.

Also some small changes in sql cleanup have not been (fully) included in the official hg repo as yet like https://frontaccounting.com/punbb/viewtopic.php?pid=18517#p18517

You can try the FA core from my unofficial github repo and let me know if it gets clarified.

Verify that the function get_bank_trans() at lines 66 to 91 in gl/includes/db/gl_db_bank_trans.inc is like:

function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id=null)
{
    $sql = "SELECT bt.*, act.*,
        IFNULL(abs(dt.ov_amount), IFNULL(ABS(st.ov_amount), bt.amount)) AS settled_amount,
        IFNULL(abs(dt.ov_amount/bt.amount), IFNULL(ABS(st.ov_amount/bt.amount), 1)) AS settle_rate,
        IFNULL(debtor.curr_code, IFNULL(supplier.curr_code, act.bank_curr_code)) AS settle_curr

        FROM ".TB_PREF."bank_trans bt
                LEFT JOIN ".TB_PREF."debtor_trans dt ON dt.type=bt.type AND dt.trans_no=bt.trans_no
                LEFT JOIN ".TB_PREF."debtors_master debtor ON debtor.debtor_no = dt.debtor_no
                LEFT JOIN ".TB_PREF."supp_trans st ON st.type=bt.type AND st.trans_no=bt.trans_no
                LEFT JOIN ".TB_PREF."suppliers supplier ON supplier.supplier_id = st.supplier_id
                LEFT JOIN ".TB_PREF."bank_accounts act ON act.id = bt.bank_act
        WHERE  1 ";
    if ($type != null)
        $sql .= " AND bt.type=".db_escape($type);
    if ($trans_no != null)
        $sql .= " AND bt.trans_no = ".db_escape($trans_no);
    if ($person_type_id != null)
        $sql .= " AND bt.person_type_id = ".db_escape($person_type_id);
    if ($person_id != null)
        $sql .= " AND bt.person_id = ".db_escape($person_id);
    $sql .= " ORDER BY bt.trans_date, bt.id";

    return db_query($sql, "query for bank transaction");
}

apmuthu's Website

  • From: Dutch Caribbean
  • Registered: 12/02/2013
  • Posts: 39

Re: Supplier payment fails

Hi Apmuthu,

I downloaded and copied the complete core into my test directory.

The 2 issues in the first post are solved.
The one issue in the second post as well.

I can also confirm that the file contents of gl_db_bank_trans.inc is as listed in your post.

Thanks a lot!

jlinkels

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Supplier payment fails

Joe/Janusz, now the rest of the changes in my commits can be incorporated into the FA hg repo now that confirmation is available.

https://github.com/apmuthu/frontaccounting/commit/854c8067faa29399b655ac317d0d1ace48c3c622
https://github.com/apmuthu/frontaccounting/commit/1a2834b600e83e50372667d365cc737835d1b990
https://github.com/apmuthu/frontaccounting/commit/5d3a64a1880df01af314ff8d33de01ecfad44250

apmuthu's Website