Obtain Balance in CoA using SQL

saidsl · August 7, 2017, 12:38am

Hi,

Is it possible to obtain the balance of an account (similiar to the right of the Chart of Account Tree) using SQL queries only?

I need to pull these balances appearing on the right in the CoA Tree to a Business Intelligence/Reporting App and it only takes SQL queries.

Thanks & Regards
Said

PS. And if it is how would the sql be? and or what tables/fields would be used

umair · August 7, 2017, 6:22am

Trial Balance report?

saidsl · August 7, 2017, 6:35am

Yes, if I was viewing from within ERPNext, however, I’m pulling the data from the DB for use in an BI app.

Regards
Said

nabinhait · August 7, 2017, 9:31am

If you are looking balance only of leaf node accounts, then it will be like:

select
	sum(debit) - sum(credit)
from
	`tabGL Entry`
where
	account = 'your-account-name'

If you want balance for a customer/supplier, then:

select
	sum(debit) - sum(credit)
from
	`tabGL Entry`
where
	party_type = 'Customer'
	and party = 'your-party-name'

If you looking for group accounts, then you need to pass conditions based on lft and rgt. Check following function for more details:

github.com

frappe/erpnext/blob/master/erpnext/accounts/utils.py#L86


      
          	raise FiscalYearError(error_msg)
          
          
def validate_fiscal_year(date, fiscal_year, company, label="Date", doc=None):
          	years = [f[0] for f in get_fiscal_years(date, label=_(label), company=company)]
          	if fiscal_year not in years:
          		if doc:
          			doc.fiscal_year = years[0]
          		else:
          			throw(_("{0} '{1}' not in Fiscal Year {2}").format(label, formatdate(date), fiscal_year))
          
          
@frappe.whitelist()
          def get_balance_on(account=None, date=None, party_type=None, party=None, company=None, in_account_currency=True, cost_center=None):
          	if not account and frappe.form_dict.get("account"):
          		account = frappe.form_dict.get("account")
          	if not date and frappe.form_dict.get("date"):
          		date = frappe.form_dict.get("date")
          	if not party_type and frappe.form_dict.get("party_type"):
          		party_type = frappe.form_dict.get("party_type")
          	if not party and frappe.form_dict.get("party"):
          		party = frappe.form_dict.get("party")
          	if not cost_center and frappe.form_dict.get("cost_center"):

saidsl · August 7, 2017, 12:29pm

@nabinhait

Awesome, that’s exactly what I was looking for.

Thank you soo much

Regards
Said