create table dwh_budget_kgntv as
with filter_                  as
		(select  l.id lid,
				cc.id cid
			from lots l
			left join cm_contracts cc
			on l.id = cc.lot_id
			join procedure_steps ps
			on ps.id = l.current_step and ps.actual = true
			join vocab_procedure_steps vps
			on vps.pseudo::text = ps.step_id::text and vps.actual = true
			join site.naumen_kgntv_stages nks
			on nks.kgntv_id = vps.id
			where vps.id != 93 and nks.naumen_id in (5, 6, 11, 12)
		)
	select  pa.id      as accountid,
			ca.id            as contragentsid,
			pvtp.name        as budgetyear,
			gea.exp_account  as exp_account,
			gea.section_code as section_code,
			(gea.grbs_code
			||gea.section_code
			||gea.article_code
			||gea.e_code
			||gea.kosgu)     as kbk,
			gea.article_code as article_code,
			gla.name         as article_name,
			gea.kosgu        as kosgu,
			glk.name         as kosgu_name,
			pvbt.description as budget_name,
			gea.e_code       as e_code,
			gea.fund_code    as fund_code,
			pby.sum          as allocated_amount,
			planlot.planlot_amount,
			pby.sum - coalesce(planlot.planlot_amount, 0) restlot,
			pby.sum - coalesce(plancont.plancont_amount, 0) restcontract
		from contragents as ca
		join po_budget   as pb
		on pb.contragent_id = ca.id
		join po_account as pa
		on pa.po_budget_id = pb.id
		join po_exp_account as pea
		on pea.id = pa.po_exp_account_id
		join gpo_po_exp_account as gpea
		on pea.id = gpea.po_exp_code_id
		join gpo_exp_account as gea
		on gea.id = gpea.gpo_exp_account_id
		join gpo_list_article as gla
		on gla.article_code = gea.article_code
		join po_vocab_budget_types as pvbt
		on pvbt.code = pea.budget_type
		join po_vocab_time_periods as pvtp
		on pvtp.id = pb.po_period_id
		join gpo_list_kosgu as glk
		on glk.kosgu_code = gea.kosgu
		join po_budget_year as pby
		on pby.id = pa.id
		left join
			(select  sum(pf.amount) as planlot_amount,
					pf.po_account_id
				from po_finances pf
				where pf.lot_id in
					(select  lid
						from filter_
					)
				group by po_account_id
			)planlot
		on planlot.po_account_id = pa.id
		left join
			(select  sum(ccf.amount) as plancont_amount,
					ccf.po_account_id
				from cm_contract_finances ccf
				where amount is not null and type = 1 and contract_id in
					(select  cid
						from filter_
					)
				group by po_account_id
			)plancont
		on plancont.po_account_id = pa.id
		where pb.actual = true and pby.actual = true and pea.actual = true and
			gla.actual = true and glk.actual = true and pvbt.actual = true and ca.actual
			is true;