with a as
(
Select distinct
	pf.fulfilment_sum,
	pf.fulfilment_sum_rur,
	pff.acceptance_finance_id
From cm_contract_finances pf
LEFT join cm_contract_acceptance_docs_to_payments pff on pf.id=acceptance_finance_id
--WHERE 24090772
--GROUP BY pff.acceptance_finance_id
),
pf as(
	SELECT
		pf.lot_id,
		pf.amount,
		pf.TYPE,
		pf.doc_type,
		NULL "YEAR",
		pf.po_account_id,
		pf.stage_id,
		pf.supplier_id,
		pf.id,
		pf.MONTH,
		pf.doc_name,
		Substring(pf.doc_number FROM 0 FOR 150) doc_number,
		pf.doc_date,
		pf.pay_doc_name, -- Вид документа
		pf.published::text , -- IS 'Опубликован на ООС'
		pf.improper_execution_text, -- 'Информация о ненадлежащем исполнении или неисполнении контракта, в том числе в части оплаты'
		pf.fulfilment_sum_rur, -- 'Стоимость исполненных поставщиком обязательств, руб'
		pf.delivery_accept_date, -- 'Дата подписания заказчиком документа о приемке ТРУ'
		pf.product,
		NULL payment_description,
		NULL sub_type_code,
		NULL description,
		NULL kosgu,
		NULL cvr,
		NULL targetexpenseitemcode,
		NULL section_code,
		NULL fund_code,
		NULL req_code,
		NULL aip_code,
		NULL inn,
		NULL aip_name,
		NULL receiver_inn,
		NULL cvr_name,
		NULL kosgu_name,
		NULL code,
		NULL kosgu_rs
	from
	cm_contract_finances pf
		WHERE 1=1 And pf.type In (1) and pf.actual is TRUE AND COALESCE(doc_type,'0') != 'PAY'
		--and  lot_id = '4804034'
			--AND pf.lot_id=5031694

	UNION

	SELECT
		cc.lot_id,
		pf.amount,
		2 AS TYPE,
		'PAY' AS doc_type,
		pf.YEAR,
		NULL po_account_id,
		null stage_id,
		NULL supplier_id,
		NULL id,
		NULL "MONTH",
		NULL doc_name,
		Substring(pf.number FROM 0 FOR 150) doc_number,
		pf.DATE::DATE doc_date,
		pf.pay_doc_name,
		NULL published,
		NULL improper_execution_text,
		NULL fulfilment_sum_rur,
		NULL delivery_accept_date,
		NULL product,
		SUBSTRING(pf.payment_description,0,100)payment_description,
		pf.sub_type_code,
		vbc.description,
		coalesce(gea.kosgu, pf.kosgu_rs)  kosgu,
		pf.e_code cvr,
		CASE WHEN pf.article_code IS NULL THEN '0000000000'
		ELSE pf.article_code end targetexpenseitemcode,
		pf.section_code,
		pf.fund_code,
		pf.req_code,
		pf.aip_code,
		c.inn,
		l_aip."NAME" aip_name,
		pf.receiver_inn,
		gl.name AS cvr_name,
		glk.name AS kosgu_name,
		ggc.code,
		pf.kosgu_rs
	FROM cm_contracts cc
	INNER JOIN lots l ON (l.id = cc.lot_id AND l.actual = TRUE)
	INNER JOIN cm_contract_payments pf ON (pf.cm_contract_id = cc.id)
	left join gpo_exp_account gea ON(pf.kosgu_rs = substring(gea.exp_account, 1, 6) and pf.year::varchar = substring(gea.exp_account, 7, 4) and pf.sub_type_code='BUD' and length(gea.exp_account)=10)
	LEFT JOIN po_vocab_budget_types vbc ON (vbc.code=pf.sub_type_code)
	LEFT JOIN (SELECT aip_code,max(NAME) "NAME",YEAR from  gpo_list_aip GROUP BY aip_code,YEAR)l_aip ON (l_aip.aip_code= pf.aip_code AND l_aip.YEAR=pf.YEAR)
	LEFT JOIN contragents c ON (c.id = cc.contract_customer)
	LEFT join public.po_list_org_hierarchy ploh	on ploh.contragent_id = c.id and ploh.actual = true and c.actual= true and ploh.parent_type = 1
	left join public.contragents c_grbs 	on c_grbs.id = ploh.parent_contragent_id
	left join public.gpospb_grbs_codes ggc 	on ggc.inn = c_grbs.inn
	LEFT JOIN gpo_list_expense_type gl ON pf.e_code=gl.e_code
	LEFT JOIN gpo_list_kosgu glk ON  glk.kosgu_code=pf.kosgu_rs
	--where  cc.lot_id = '4804034'
		--WHERE lot_id=5031694
	ORDER BY TYPE,doc_type,"YEAR")

Select
	cc.id contract,
	cc.lot_id,
	pf.stage_id,
	pf.amount finsum, --сумма финансирования
	Case
		When glb.code = Cast ('72020112' As text)
			Then (coalesce(pf.amount, 0))
		Else 0
	End bsum, --сумма бюджета
	Case
		When (glb.code)::text != '72020112'
			Then (Coalesce(pf.amount, 0))
	Else 0 End obsum,  --сумма внебюджета,
	glb.name finsource,
	pf.month,
	COALESCE (pvtp.name,pf."YEAR") as year,
	pvtp_a.name year_accept,
	pb.id budget_id,
	c.inn,
	pf.supplier_id,
	COALESCE (supp.inn,pf.receiver_inn) supplier_inn,
	pf.id,
	pf.doc_name,
	pf.doc_number,
	pf.doc_date,
	pf.pay_doc_name, -- Вид документа
	pf.type,
	a.fulfilment_sum, -- Стоимость исполненных поставщиком (подрядчиком, исполнителем) обязательств в валюте исполнения'
	Substring(pf.product From 0 For 2000) product,
	COALESCE(dea.kosgu,pf.kosgu_rs) economiccode,
	COALESCE(dea.article_code,pf.targetexpenseitemcode) targetexpenseitemcode,
	Case
		When pea.budget_type = 'BUD'
			Then pea.po_exp_code
		Else Null
	End expensesnumeration,
	dea.exp_account,
	COALESCE(dea.section_code,pf.section_code) subsection,
	COALESCE(dea.e_code,pf.cvr) expensetypecode,
	COALESCE(glet.name,pf.cvr_name) expensetypename,
	COALESCE(dea.fund_code,pf.fund_code) fund_code,
	COALESCE(dea.grbs_code,pf.code) grbs_code,
    COALESCE(dea.req_code,pf.req_code) req_code, -- код запроса ( добавлено по просьбе Крицкой Л.А. 14.07.2023)
  	COALESCE(gsa.kosgu,pf.kosgu, dea.kosgu) kosgucode,
	COALESCE(glk.name,kosgu_name) kosgutitle,
	COALESCE(pea.budget_type,pf.sub_type_code) budget_type,
	COALESCE(pvbt.description,pf.description) description,
	pf.published::text , -- IS 'Опубликован на ООС'
	pf.improper_execution_text, -- 'Информация о ненадлежащем исполнении или неисполнении контракта, в том числе в части оплаты'
	a.fulfilment_sum_rur, -- 'Стоимость исполненных поставщиком обязательств, руб'
	pf.delivery_accept_date, -- 'Дата подписания заказчиком документа о приемке ТРУ'
	ccs.is_done::text -- 'Признак исполненности этапа'
	,cfd.eis_date --отправлен в ЕИС
	,case
		When pf.doc_type='ACCEPT' then 'Документ о приёмке'
		When pf.doc_type='PAY' then 'Платежный документ'
		When pf.doc_type='EXEC' then 'Документ об исполнении'
	else null end doc_type,
	COALESCE(l_aip.aip_code,pf.aip_code) aip_code,
	COALESCE(l_aip.name,pf.aip_name) aip_name
From cm_contracts cc
Inner Join lots l On (l.id = cc.lot_id And l.actual = true)
LEFT Join pf On pf.lot_id = l.id
LEFT Join po_account pa On pa.id = pf.po_account_id
LEFT Join po_exp_account pea On (pea.id = pa.po_exp_account_id And pea.actual = true)
LEFT Join
	(Select
		Min(gpo_budget_type_code) gpo_budget_type_code,
		budget_type
	From gpo_vocab_budget_links
	Where actual Is true
	Group by budget_type
	)gvbl On ((gvbl.budget_type)::text = (pea.budget_type)::text) OR pf.sub_type_code=gvbl.budget_type
Left Join gpo_list_budget glb	On (glb.code)::text = (gvbl.gpo_budget_type_code)::text
Left Join po_budget pb	On (pb.id = pa.po_budget_id And pb.actual = true)
Left Join po_vocab_time_periods pvtp On pvtp.id = pb.po_period_id
Left Join cm_contract_stages ccs On ccs.id = pf.stage_id And ccs.actual = true
Left Join po_vocab_time_periods pvtp_a On (pvtp_a.id = pb.po_accept_period_id)
Left Join gpo_po_exp_account gpea On gpea.po_exp_code_id = pea.id
Left Join gpo_exp_account dea On (dea.id = gpea.gpo_exp_account_id)
Left Join gpo_sub_account gsa On (gsa.id = pea.id)
Left Join gpo_list_kosgu glk On (glk.kosgu_code = gsa.kosgu)
Left Join gpo_list_expense_type glet On (glet.e_code = dea.e_code)
Left Join po_vocab_budget_types pvbt On (pvbt.code::text = gvbl.budget_type::text)
Left Join bp_ek.kf_kf_vocab_fundstype vf On (vf.process_name::text = pvbt.description::text)
Left Join contragents supp On (supp."id" = pf.supplier_id)
Left Join contragents c	On (c.id = cc.contract_customer)
Left Join (Select
				max(date_file) eis_date,
				finance_id
			From cm_contract_finances_documents
			Group By finance_id
		  )cfd on cfd.finance_id=pf.id
Left Join cm_contract_aips aip On (aip.lot_id = l.id and aip.cm_contract_finances_id = pf.id and aip.actual is true and pa.id=aip.po_account_id)
Left Join gpo_list_aip l_aip On (l_aip.id = aip.aip_code)
left join a on a.acceptance_finance_id = pf.id
--where  cc.lot_id = '4804034'
--WHERE cc.lot_id=5031694