Query to fetch Major Head, Minor Head, Sub Head, Budget Head wise ==> Allocation and Utilization against a specified Budget ID.

Budget Allocation and Utilization.sql

SELECT
	
	concat(o.value, ' - ', o.name) AS Org,
	concat(vwc.majorheadvalue, ' - ', vwc.majorhead) AS MajorHead,
	concat(vwc.minorheadvalue, ' - ', vwc.minerhead) AS MinorHead,
	concat(vwc.subheadvalue, ' - ', vwc.subhead) AS SubHead,
	concat(vwc.value, ' - ', vwc.name) AS BudgetHead,
	SUM(f.amt) AS Allocation,
	SUM(i.grandtotal) AS Utilization
FROM
	
	c_invoice i
	INNER JOIN c_invoiceline il ON (( il.c_invoice_id = i.c_invoice_id ))
	INNER JOIN ad_org o ON (( o.ad_org_id = il.ad_org_id ))
	INNER JOIN vwcharge vc ON (( vc.c_charge_id = il.c_charge_id ))
	INNER JOIN vwchart vwc ON (( vwc.c_elementvalue_id = vc.account_id ))
	INNER JOIN gl_budget b ON (( b.gl_budget_id = i.gl_budget_id ))
	INNER JOIN gl_fund f ON (( f.ad_org_id = il.ad_org_id AND f.c_elementvalue_id = vc.account_id ))

WHERE
	i.issotrx = 'N'
	AND
	i.ad_client_id = 1000000
	AND
	b.gl_budget_id = 1000000
	

GROUP BY
	
	o.value,
	o.name,
	vwc.majorheadvalue,
	vwc.majorhead,
	vwc.minorheadvalue,
	vwc.minerhead,
	vwc.subheadvalue,
	vwc.subhead,
	vwc.value,
	vwc.name

ORDER BY
	o.value,
	o.name,
	vwc.majorheadvalue,
	vwc.majorhead,
	vwc.minorheadvalue,
	vwc.minerhead,
	vwc.subheadvalue,
	vwc.subhead,
	vwc.value,
	vwc.name
ASC