Query to calculate the Utilization of Developmental Budget Heads

developmental_utilization.sql

SELECT
	SUM(i.grandtotal) AS total_utilization
FROM
	adempiere.c_invoice i
	INNER JOIN adempiere.gl_budget b ON (( b.gl_budget_id = i.gl_budget_id ))
	INNER JOIN adempiere.c_invoiceline il ON (( il.c_invoice_id = i.c_invoice_id ))
	INNER JOIN adempiere.vwcharge vc ON (( vc.c_charge_id = il.c_charge_id ))
	INNER JOIN adempiere.vwchart vwc ON (( vwc.c_elementvalue_id = vc.account_id ))
WHERE
	i.isactive = 'Y'
	AND
	i.ad_client_id = 1000000
	AND
	i.issotrx = 'N'
	AND
	b.isactive = 'Y'
	AND
	b.ad_client_id = 1000000
	AND
	b.isprimary = 'Y'
	AND
	b.budgetstatus = 'A'
	AND
	vwc.minorheadvalue = 'A09' -- Expenditure on Acquiring of Physical Assets
	OR
	vwc.minorheadvalue = 'A12' -- Civil Works
	OR
	vwc.minorheadvalue = 'A13' -- Repair and Maintenance