Bank Balance Management in ADempiere, Open Source ERP

Hi Everyone,

The purpose of this article is to share a best practice implementation for managing daily bank balances. When you use asset based lending, you only want to borrow the minimum amount on a daily basis to help reduce interest expenses. If you know what is expected to clear in the next few days, you can best manage your cash.

The article shows you how to accomplish this goal using ADempiere and iDempiere, free and open source ERP. For those of you who are not familiar, ADempiere is well-suited for wholesale\web distribution and light-manufacturing companies with between 20 to 300 concurrent users. If you implement the below queries, the result will help you monitor balances in all accounts in all currencies even when the payments are in mixed currencies.

The below queries help you know your to-the-day cash positions. If you are not familiar with modifying ADempiere or iDempiere for new windows, let me know, and I can help.

This article assumes the following:

1. You create a drafted bank statement at the beginning of each month.
2. You update the drafted bank statement each day with payments and receipts that have cleared the bank.

Drafted Bank Statement Lines

The first task is to create a list of payments on drafted bank statement lines. Replace ‘yourco’ in the view name with your company’s name or abbreviation:

--create view yourco_rv_daily_bank_drafted as
select xbsl.c_payment_id 
from C_BankStatementLine xbsl
 inner join c_bankstatement xbs 
 on (xbsl.c_bankstatement_id = xbs.c_bankstatement_id)
where xbs.docstatus in ('DR','IP');

Get the Balances in the Bank Account Currency

The second task is to get your balances. Do not be intimidated by the size of the query.

--create or replace view yourco_rv_daily_bank_base as
select ba.ad_client_id, ba.ad_org_id,
 ba.created, ba.createdby,
 ba.updated, ba.updatedby,
 ba.accountno, round(ba.currentbalance,2) as currentbank_bal, 
 ba.c_currency_id as c_currency_id, 
 coalesce(
 (select 
 round(sum(
 currencyConvert(xp.payamt, xp.C_Currency_ID, 
 ba.C_Currency_ID, xp.DateTrx, 
 null, xp.AD_Client_ID, xp.AD_Org_ID)
 ),2) 
 from c_payment xp 
 where isreconciled = 'N' 
 and xp.docstatus = 'CO'
 and ba.c_bankaccount_id = xp.c_bankaccount_id
 and xp.isreceipt = 'N'
 and not exists
 (select * from yourco_rv_daily_bank_drafted dr 
 where xp.c_payment_id = dr.c_payment_id)
 ),0
 ) as ap_noclear_norecon_bal,
 coalesce(
 (select 
 round(sum(
 currencyConvert(xp.payamt, xp.C_Currency_ID, 
 ba.C_Currency_ID, xp.DateTrx, 
 null, xp.AD_Client_ID, xp.AD_Org_ID)
 ),2) 
 from c_payment xp 
 where isreconciled = 'N' 
 and xp.docstatus = 'CO'
 and ba.c_bankaccount_id = xp.c_bankaccount_id
 and xp.isreceipt = 'N'
 and exists
 (select * from yourco_rv_daily_bank_drafted dr 
 where xp.c_payment_id = dr.c_payment_id)
 ),0
 ) as ap_yesclear_norecon_bal,
 coalesce(
 (select 
 round(sum(
 currencyConvert(xp.payamt, xp.C_Currency_ID, 
 ba.C_Currency_ID, xp.DateTrx, 
 null, xp.AD_Client_ID, xp.AD_Org_ID)
 ),2) 
 from c_payment xp 
 where isreconciled = 'N' 
 and xp.docstatus = 'CO'
 and ba.c_bankaccount_id = xp.c_bankaccount_id
 and xp.isreceipt = 'Y'
 and not exists
 (select * from yourco_rv_daily_bank_drafted dr 
 where xp.c_payment_id = dr.c_payment_id)
 ),0
 ) as ar_noclear_norecon_bal,
 coalesce(
 (select 
 round(sum(
 currencyConvert(xp.payamt, xp.C_Currency_ID, 
 ba.C_Currency_ID, xp.DateTrx, 
 null, xp.AD_Client_ID, xp.AD_Org_ID)
 ),2) 
 from c_payment xp 
 where isreconciled = 'N' 
 and xp.docstatus = 'CO'
 and ba.c_bankaccount_id = xp.c_bankaccount_id
 and xp.isreceipt = 'Y'
 and exists
 (select * from yourco_rv_daily_bank_drafted dr 
 where xp.c_payment_id = dr.c_payment_id)
 ),0
 ) as ar_yesclear_norecon_bal
from c_bankaccount ba
;

Perform Simple Math

The purpose of this query is to perform some math and present the calculated balances:

--create view yourco_rv_daily_bank as
select ad_client_id, ad_org_id, created, createdby, updated, updatedby, c_currency_id,
 currentbank_bal 
 - ap_noclear_norecon_bal 
 + ar_noclear_norecon_bal 
 - ap_yesclear_norecon_bal 
 + ar_yesclear_norecon_bal 
 as total_bal,
 ap_noclear_norecon_bal,
 ar_noclear_norecon_bal,
 ap_yesclear_norecon_bal,
 ar_yesclear_norecon_bal
from yourco_rv_daily_bank_base
;

Update in ADempiere

ADempiere only needs to know about the yourco_rv_daily_bank view. Import this view as a table, and present as a window. If you want assistance, let me know.