CREATE OR REPLACE FUNCTION "public"."dwh_contract_kgntv_func"()
  RETURNS TABLE("contractid" int4, "requestid" int4, "lotid" int4, "requestyear" int4, "first_fin_year" int4, "first_fin_cont_year" int4, "first_fin_cont_year_fact" int4, "contractrnk" varchar, "contractchangenumber" varchar, "modification" varchar, "contractversion" int4, "ordertypename" text, "ordertypecode" int4, "stagetitle" varchar, "stageid" int4, "stagenumber" numeric, "contractnoticenumber" varchar, "datelastchange" timestamptz, "customerid" int4, "customername" varchar, "customerinn" varchar, "customerkpp" varchar, "suppliername" varchar, "supplierinn" varchar, "supplierkpp" varchar, "suppliersmp" int4, "suppliersmpsmp" int4, "suppliersmpsonco" int4, "supplierinvalid" int4, "suppliercriminal" int4, "supplierstatus" varchar, "supplierphone" varchar, "supplierokopf" varchar, "supplierokpo" varchar, "supplierfio" varchar, "supplieremail" varchar, "supplieradress" varchar, "supplieraddress_fact" varchar, "suppliertype" varchar, "grbsname" varchar, "grbsid" int4, "grbscode" varchar, "perc_size" numeric, "perc_rub_size" numeric, "modification_docnum" varchar, "penaltiesamount" varchar, "contractcreatedate" timestamptz, "contractsigndate" timestamptz, "contractnumber" varchar, "contractsubject" varchar, "contractfullprice" numeric, "contract_price_signing" numeric, "contractactualpaid" numeric, "contractplaneexecdate" timestamptz, "contractactualexecdate" timestamptz, "contractrejectdate" timestamptz, "first_notice_date" timestamp, "first_try_notice_date" timestamp, "last_notice_date" timestamp, "last_try_notice_date" timestamp, "contractrejectreason" varchar, "contractrejectbase" varchar, "contractoneexecreason" varchar, "resultprotocol" varchar, "paidfromregister" numeric, "protocoldate" timestamptz, "finsource" varchar, "warranty_requirements" varchar, "warranty_case" varchar, "foreignpref" int4, "penalties" int4, "ispublic" int4, "import_uuid" varchar, "detail_parent_id" int4, "cancel_document_params" varchar, "is_concluded_in_e_shop" varchar, "special_contract_type" int4, "contract_execution_start_date" timestamptz, "price_type" varchar, "document_date" timestamptz, "contract_modification_reasons" varchar, "modif_reason_documents" varchar, "drugs_purchase" int4, "fulfilment_sum_rur" numeric, "violation_deadlines" int4, "name" varchar, "violation_reasons_not_21" varchar, "amount_of_penalty" varchar, "penalty_write_off_information" int4, "penalty_sum" numeric, "fulfilment_sum" numeric, "preference_smp_sonco" int4, "preference_invalid" int4, "preference_ugolov" int4, "oktmo" varchar, "supply_impossible" int4, "rtc_price" numeric, "acceptance_doc" int4) AS $BODY$
#variable_conflict use_column
begin

-- создаем временную таблицу first_fin
Create Temp Table first_fin
(
	lot_id int4 PRIMARY KEY,
	year_ int4
)
;

-- заполняем времнную таблицу first_fin
Insert into first_fin
Select
	l.id lot_id,
	min(pvtp.name) year_
 From gpospb.lots l
 Inner Join gpospb.po_finances pf On (pf.lot_id = l.id)
 Inner Join gpospb.po_account pa On (pa.id = pf.po_account_id)
 Inner Join gpospb.po_budget pb On (pb.id = pa.po_budget_id)
 Inner Join gpospb.po_vocab_time_periods pvtp On pvtp.id = pb.po_period_id
 Group by l.id
;

-- создаем временную таблицу first_fin_cont
Create Temp Table first_fin_cont
(
	lot_id int4 PRIMARY KEY,
	year_plan int4,
	year_fact int4
)
;

-- заполняем времнную таблицу first_fin_cont
Insert into first_fin_cont
Select
	pf.lot_id,
	Min(Case
			When pf.type = 1 Then pvtp.name
			Else null End) year_plan,
	Min(Case
			When pf.type = 2 Then pvtp.name
			Else Null End) year_fact
 From gpospb.cm_contract_finances pf
 Inner Join gpospb.po_account pa On (pa.id = pf.po_account_id and pf.actual is true)
 Inner Join gpospb.po_exp_account pea On (pea.id = pa.po_exp_account_id and pea.actual = true)
 Inner Join gpospb.gpo_vocab_budget_links gvbl	On ((gvbl.budget_type)::text = (pea.budget_type)::text)
 Inner Join gpospb.gpo_list_budget glb On ((glb.code)::text = (gvbl.gpo_budget_type_code)::text)
 Inner Join gpospb.po_budget pb On (pb.id = pa.po_budget_id and pb.actual = true)
 Inner Join gpospb.po_vocab_time_periods pvtp On (pvtp.id = pb.po_period_id)
 Inner Join gpospb.cm_contract_stages ccs On (ccs.id = pf.stage_id and ccs.actual = true)
 Group By pf.lot_id
;

-- создаем временную таблицу supplieradress
Create Temp Table supplieradress
(
	contragent_id int4 PRIMARY KEY,
	address_nahozh varchar(4000),
	adress varchar(4000)
)
;

-- заполняем времнную таблицу supplieradress
Insert into supplieradress
 Select
	contragent_id,
	max(Case
			When address_type = 1
				Then Coalesce(index::text, '') ||' ' ||Coalesce(region, '') ||' ' || Coalesce(settlement, '') ||' ' || Coalesce(city, '') || ' ' || Coalesce(street, '') || ' ' || Coalesce(house, '')
			When address_type = 3
				Then Coalesce(foreign_region, '') ||' ' || Coalesce(foreign_settlement, '') ||' ' || Coalesce(foreign_city, '') || ' ' || Coalesce(foreign_street, '') || ' ' || Coalesce(foreign_house, '')
				Else Null
		end) address_nahozh,
	max(Case
			When address_type =2
				Then Coalesce(index::text, '') ||' ' ||Coalesce(region, '') ||' ' || Coalesce(settlement, '') ||' ' || Coalesce(city, '') || ' ' || Coalesce(street, '') || ' ' || Coalesce(house, '')
				Else Null
		End) adress
 From gpospb.addresses
 Where actual = true --And region Is Not Null And coalesce(Index::text,city) Is Not NULL 20/11/23
 Group by contragent_id
;

-- создаем временную таблицу fins
Create Temp Table fins
(
	lot_id int4 PRIMARY KEY,
	sources varchar(10000)
)
;

-- заполняем времнную таблицу fins
Insert into fins
 Select
	pf.lot_id,
	string_agg(Distinct glb.name, ', ') sources
 From gpospb.cm_contract_finances pf
 Inner Join gpospb.po_account pa On (pa.id = pf.po_account_id And pf.type = 1 and pf.actual is true)
 Inner Join gpospb.po_exp_account pea On (pea.id = pa.po_exp_account_id)
 Inner Join
	(Select
		Min(gpo_budget_type_code) gpo_budget_type_code,
		budget_type
	From gpospb.gpo_vocab_budget_links
	Where actual Is true
	Group by budget_type
	)gvbl On ((gvbl.budget_type)::text = (pea.budget_type)::text)
 Inner Join gpospb.gpo_list_budget glb On ((glb.code) =(gvbl.gpo_budget_type_code))
 Group by pf.lot_id
;

-- создаем временную таблицу ccf_p
Create Temp Table ccf_p
(
  amount numeric(15,2),
	fulfilment_sum_rur numeric(21,2),
	fulfilment_sum numeric(21,2),
	lot_id int4 PRIMARY KEY,
	acceptance_doc int4
)
;

-- заполняем времнную таблицу ccf_p
Insert into ccf_p
 Select
	Sum(cm_contract_finances.amount) As amount,
	Sum(cm_contract_finances.fulfilment_sum_rur) As fulfilment_sum_rur,
	Sum(cm_contract_finances.fulfilment_sum) As fulfilment_sum,
	cm_contract_finances.lot_id,
	max(CASE
		WHEN acceptance_doc IS TRUE THEN 1
		ELSE 0 END
	) AS acceptance_doc
 From gpospb.cm_contract_finances
 Where cm_contract_finances.type = 2 and cm_contract_finances.actual is true
 Group By cm_contract_finances.lot_id
;

-- создаем временную таблицу firstnotice
Create Temp Table firstnotice
(
  id int4 PRIMARY KEY,
	date_ timestamp(6),
	successdate timestamp(6),
	date_max timestamp(6),
	successdate_max timestamp(6)
)
;

-- заполняем времнную таблицу firstnotice
Insert into firstnotice
 Select
	cc.id,
	Min(o.date_created) date_,
	Min(Case
			When o.success = true
				Then o.date_created
		End) successdate,
	Max(o.date_created) date_max,
	Max(Case
			When o.success = true
				Then o.date_created
		End) successdate_max
 From gpospb.oos_messages o
 Inner Join gpospb.cm_contracts cc On cc.lot_id = o.lot_id and cc.id = o.contract_id And o.type = 'contract' And cc.import_uuid Is Null -- and o.success = true
 Group By cc.id
;

-- создаем временную таблицу Join_lots
Create Temp Table Join_lots
(
  lot_id int4 PRIMARY KEY,
	procedure_id int4,
	registry_number varchar(32)
)
;

-- заполняем времнную таблицу Join_lots
Insert into Join_lots
 Select
	pol.lot_id,
	l.procedure_id,
	p.registry_number registry_number
 From gpospb.po_lots pol
 Inner Join gpospb.lots l On (l."id" = pol.consolidate_lot_id And pol.consolidate_lot_id Is Not Null)
 Inner Join gpospb.procedures p On (p."id" = l.procedure_id)
;

-- создаем временную таблицу ccpen
Create Temp Table ccpen
(
	doc_number text,
	doc_date varchar(100),
	amount int4,
	amount_of_penalty varchar(1000),
	penalty_write_off_information int4,
	penalty_sum numeric(15,2),
	cm_contract_id int4 PRIMARY KEY
)
;

-- заполняем времнную таблицу ccpen
Insert into ccpen
Select doc_number,
        doc_date,
        amount,
        amount_of_penalty,
        penalty_write_off_information,
        penalty_sum,
        cm_contract_id
From (
Select  string_agg(translate(ltrim(to_char(cpwoi.amount_of_penalty,'999G999G999G990D00')),',.',' ,'),'; ' order by cp.cm_contract_id) as amount_of_penalty --размер неустойки (штрафа, пени), по которой осуществлено списание штрафных санкций (если несколько списаний, то указано через ;)
   ,sum(cpwoi.amount_of_penalty) penalty_sum --сумма всех списанных штрафных санкций по контракту
	,cm_contract_id
	,max(f.penalty_write_off_information)penalty_write_off_information
	,Coalesce(min(amount *(Case
							When currency_name = 'RUB'
								Then 1
							Else currency_rate
						  End)),0
			 )amount,
    Coalesce(to_char(min(doc_date), 'DD.MM.YYYY'), '') doc_date,
    Coalesce(min(doc_number), '') doc_number
 From gpospb.cm_contract_penalties cp
 Left join (select penalty_id,case when penalty_write_off_information='t' then 1 else 0  end penalty_write_off_information From gpospb.cm_contract_penalty_write_off_informations) f ON f.penalty_id = cp.id
 LEFT Join gpospb.cm_contract_penalty_write_off_informations AS cpwoi ON cpwoi.penalty_id = cp.id
 Group By cm_contract_id
 )a
;

-- создаем временную таблицу lc
Create Temp Table lc
(
	lot_id int4 PRIMARY KEY
)
;

-- заполняем времнную таблицу lc
Insert into lc
 Select
	lot_id
 From gpospb.lot_customers
 Where actual = true
 Group By lot_id
;

-- создаем временную таблицу ccss_pvss
Create Temp Table ccss_pvss
(
	contract_id int4 PRIMARY KEY,
	status int4
)
;

-- заполняем времнную таблицу ccss_pvss
Insert into ccss_pvss
Select ccss.contract_id, max(ccss.status)status
From gpospb.cm_contract_supplier_status ccss -- On (/*ccss.supplier_id=cc.supplier_id and */ccss.contract_id=cc.id)
group by ccss.contract_id
;

-- создаем временную таблицу grbs
Create Temp Table grbs
(
	id int4 PRIMARY KEY,
	full_name varchar(2000),
	inn varchar(20),
	kpp varchar(20),
	code varchar(255)
)
;

-- заполняем времнную таблицу grbs
Insert into grbs
Select c_grbs.id,c_grbs.full_name,c_grbs.inn,c_grbs.kpp,gcgrbs.code
From gpospb.contragents c_grbs
Left Join gpospb.gpospb_contragents gcgrbs On (gcgrbs.contragent_id = c_grbs.id)
;

-- создаем временную таблицу vps_nks
Create Temp Table vps_nks
(
	ps_id int4 PRIMARY KEY,
	full_name varchar(255),
	id int4,
	naumen_id numeric
)
;

/*Select ps.id As ps_id, vps.full_name, vps.id, nks.naumen_id
From gpospb.procedure_steps ps
Inner Join gpospb.vocab_procedure_steps vps On (vps.pseudo::text = ps.step_id::text and vps.actual = true)
Left Join gpospb.site.naumen_kgntv_stages nks On (nks.kgntv_id = vps.id )
*/

-- заполняем времнную таблицу vps_nks
Insert into vps_nks
Select
 ps.id As ps_id, vps.full_name, vps.id, nks.naumen_id
From gpospb.lots l
Inner Join gpospb.procedure_steps ps On (ps.id = l.current_step)
Inner Join gpospb.vocab_procedure_steps vps On (vps.pseudo::text = ps.step_id::text and vps.actual = true)
Left Join site.naumen_kgntv_stages nks On (nks.kgntv_id = vps.id )
;

-- создаем временную таблицу vpt_nkp
Create Temp Table vpt_nkp
(
	id int4 PRIMARY KEY,
	name varchar(55),
	naumen_name text,
	naumen_code int4
)
;

-- заполняем времнную таблицу vpt_nkp
Insert into vpt_nkp
Select vpt.id, vpt.name, nkp.naumen_name, nkp.naumen_code
From gpospb.vocab_procedure_types vpt
Left Join site.naumen_kgntv_placingtypes nkp on (nkp.kgntv_id = vpt.id )
;

-- создаем временную таблицу ppr
Create Temp Table ppr
(
	lot_id int4,
	preference_short_name varchar(200),
	preference_id int4,
	actual bool
)
;

-- заполняем времнную таблицу ppr
Insert into ppr
Select  pr.lot_id, pr.preference_short_name, pr.preference_id,pr.actual
From gpospb.po_preferences pr
Join gpospb.po_list_purchase_preferences plpp on plpp.id=pr.preference_id and plpp.actual='true'
;

-- Очищаем данные основной таблицы
Truncate table public.dwh_contract_kgntv_func
;


-- Вставляем данные в основную таблицу
Insert into public.dwh_contract_kgntv_func (contractid, requestid, lotid, requestyear, first_fin_year, first_fin_cont_year, first_fin_cont_year_fact, contractrnk, contractchangenumber, modification, contractversion, ordertypename,
ordertypecode, stagetitle, stageid, stagenumber, contractnoticenumber,
datelastchange, customerid, customername, customerinn, customerkpp, suppliername, supplierinn, supplierkpp, suppliersmp, suppliersmpsmp, suppliersmpsonco, supplierinvalid, suppliercriminal, supplierstatus,
supplierphone, supplierokopf, supplierokpo, supplierfio,
supplieremail,
supplieradress, supplieraddress_fact, suppliertype, grbsname, grbsid, grbscode, perc_size, perc_rub_size, modification_docnum, penaltiesamount, contractcreatedate, contractsigndate, contractnumber, contractsubject, contractfullprice, contract_price_signing,
contractactualpaid, contractplaneexecdate, contractactualexecdate, contractrejectdate, first_notice_date, first_try_notice_date, last_notice_date, last_try_notice_date, contractrejectreason,
contractrejectbase,
contractoneexecreason, resultprotocol, paidfromregister, protocoldate, finsource, warranty_requirements, warranty_case, foreignpref, penalties,
ispublic,
import_uuid, detail_parent_id,
cancel_document_params, is_concluded_in_e_shop, special_contract_type, contract_execution_start_date, price_type, document_date,
contract_modification_reasons,
modif_reason_documents,
drugs_purchase,
fulfilment_sum_rur,
violation_deadlines, name,
violation_reasons_not_21,
amount_of_penalty,
penalty_write_off_information,
penalty_sum, fulfilment_sum,
preference_smp_sonco,
preference_invalid,
preference_ugolov
,oktmo
,supply_impossible,
rtc_price,
acceptance_doc)
Select
	cc.id As contractid,
	pr.id As requestid,
	l.id As lotid,
	Case
		When f_pvtp.name Is Not Null
			Then f_pvtp.name
		Else first_fin.year_
	End As requestyear,
	first_fin.year_ first_fin_year,
	first_fin_cont.year_plan first_fin_cont_year,
	first_fin_cont.year_fact first_fin_cont_year_fact,
	cc.contract_rnk As contractrnk,
	(((cc.contract_number) ::text || '/'::text) ||
	Case
		When(cc.version_number Is Null)
			Then 0
		Else cc.version_number
	End) As contractchangenumber,
	substr(Case
			When modification_type = 1
				Then 'Изменение контракта \ '
			When modification_type = 2
				Then 'Корректировка ошибок \ '
		   End || case when l.id =3460911 then null else modification_description end, 0, 1000) ::text modification,
	cc.version_number contractversion,
	Case
		When vpt_nkp.naumen_name Is Null
			Then vpt_nkp.name
		Else vpt_nkp.naumen_name
		End ordertypename,
	Case
		When vpt_nkp.naumen_code Is Null
			Then vpt_nkp.id+100
		Else vpt_nkp.naumen_code End ordertypecode,
	--nks.kgntv_name As stagetitle,
	substr(vps_nks.full_name, 0, 128) stagetitle,
	vps_nks.id /*nks.kgntv_id*/ As stageid,
	vps_nks.naumen_id As stagenumber,
	Case
		When Length(Coalesce(pr.registry_number,Join_lots.registry_number)) <> 19
			Then Null
		Else (Coalesce(pr.registry_number,Join_lots.registry_number))
	End contractnoticenumber,
	cc.date_last_edit As datelastchange,
	Case When pp.delegated is true Then grbs.id ELse c.id End As customerid,
	Case When pp.delegated is true Then upper(grbs.full_name) Else upper(c.full_name) End As customername,
	Case When pp.delegated is true Then grbs.inn Else c.inn End As customerinn,
	Case When pp.delegated is true Then grbs.kpp Else c.kpp End As customerkpp,
	Case
		When s.full_name Is Not Null
			Then s.full_name
		Else cmscn.supplier_full_name
	End As suppliername,
	s.inn As supplierinn,
	s.kpp As supplierkpp,
	Case
		When s.small_biz = true Or s.is_social = true or ccss_pvss.status In ('30','31') Or ccss_pvss.status In ('40','41')
			Then 1
		Else 0
	End As suppliersmp,
	Case
		When s.small_biz = true Or ccss_pvss.status In ('30','31')
			Then 1
		Else 0
	End As suppliersmpsmp,
	Case
		When s.is_social = true Or ccss_pvss.status In ('40','41')
			Then 1
		Else 0
	End As suppliersmpsonco,
	Case
		When s.is_disabled_people = true Or ccss_pvss.status =  '20'
			Then 1
		Else 0
	End As supplierinvalid,
	Case
		When s.is_penal_system = true Or ccss_pvss.status =  '10'
			Then 1
		Else 0
	End suppliercriminal,
    Case
		When ccss_pvss.status = '10' Then 'Учреждение и предприятие уголовно-исполнительной системы'
		When ccss_pvss.status = '20' Then 'Организация инвалидов'
		When ccss_pvss.status = '30' Then 'Субъект малого предпринимательства'
		When ccss_pvss.status = '31' Then 'Поставщик (подрядчик, исполнитель), который в соответствии с контрактом обязан привлечь к исполнению контракта субподрядчиков,соисполнителей из числа субъектов малого предпринимательства'
		When ccss_pvss.status = '40' Then 'Социально-ориентированная некоммерческая организация'
		When ccss_pvss.status = '41' Then 'Поставщик (подрядчик, исполнитель), который в соответствии с контрактом обязан привлечь к исполнению контракта субподрядчиков,соисполнителей из числа социально ориентированных некоммерческих организаций'	Else '0'
		End As supplierstatus, --статус поставщика
	s.phone As supplierphone,
	s.okopf As supplierokopf,-- окопф поставщика от 31.05.2021
	s.okpo As supplierokpo,-- окпо поставщика от 31.05.2021
	s.contact_fio supplierfio, -- фио поставщика
	s.email supplieremail, -- э-почта поставщика
	supplieradress.adress As supplieradress,
	substr(supplieradress.address_nahozh, 0, 2000) As supplieraddress_fact,-- адрес местонахождения (фактический) поставщика от 01.06.2021
	sp.name As suppliertype,
	upper(grbs.full_name) As grbsname,
	grbs.id As grbsid,
	"substring"((grbs.code) ::text, 1, 2) As grbscode,
	cc.perc_size,
	cc.perc_rub_size,
	substr(cc.modification_docnum, 0, 1000) modification_docnum,
	Case
		When ccpen.amount > 0
			Then(ccpen.amount::text || ' от ' || ccpen.doc_date || ' № ' || ccpen.doc_number)
		Else '0'
	End penaltiesamount,
	cc.date_add As contractcreatedate,
	cc.date_sign As contractsigndate,
	cc.contract_number As contractnumber,
	substr(l.subject, 0, 2500) As contractsubject,
	cc.contract_amount As contractfullprice,
    -- cc.contract_price_signing,                                                      -- поле цена контракта, заполняется при наличии РНК
	coalesce(cc.contract_price_signing, cc.contract_amount) as contract_price_signing, -- 25.03.2022 в случае отсутствия РНК (но контракт заключен в ЭМ), берем цену контракта из contract_amount
	ccf_p.amount As contractactualpaid,
	cc.date_execute As contractplaneexecdate,
	cc.date_close_fact As contractactualexecdate,
	cc.date_cancel As contractrejectdate,
	firstnotice.successdate first_notice_date,
	firstnotice.date_ first_try_notice_date,
	firstnotice.successdate_max last_notice_date,
	firstnotice.date_max last_try_notice_date,
	-- clctr.name As contractrejectreason,
	"substring"(cc.cancel_document_params, 1, 128) as contractrejectreason, --  10.03.22 поле причина расторжения контракта
	clctr.name as contractrejectbase,                                     --  10.03.22 поле основание расторжения контракта
	-- "substring"(coalesce(cc.cancel_document_params ,clctr.name ), 1, 128) as contractrejectreason, --  28.01.21 поле причина расторжения контракта в случае пустого значения заполняется основанием расторжения контракта
	substr(plcssr.name, 0, 1000) contractoneexecreason,
	substr(cc.contractdoc_name, 0, 2000) As resultprotocol,
	cc.exec_paid_from_product_list paidfromregister,
	cc.protocol_date protocoldate,
	fins.sources As finsource,
	Case
		When ccqgi.warranty_requirements_text Is Not Null
			Then 'да'
		Else 'нет'
	End warranty_requirements,--признак наличия гарантийных обязательств от 31.05.2021
	Case
		When ccqgi.warranty_requirements_text Is Not Null And ccqgi.published Is True
			Then  'да'
		Else 'нет'
	End warranty_case,--признак наступления гарантийного случая от 31.05.2021
	Case
		When ppr_inostr.preference_short_name ='RBK44'
		Then 1
		Else 0
	End foreignpref,
	Case
		When ccpen.cm_contract_id Is Not Null
			Then 1
		Else 0 End penalties,
	Case
		When length(cc.contract_rnk)=13
			Then 1
		Else 0 End ispublic,
	cc.import_uuid,
	pp.detail_parent_id, --id родительской процецедуры
	substr(cc.cancel_document_params, 0, 2000)cancel_document_params,--Реквизиты документа по расторжению контракта
	Case
		When cc.external_id Is Not Null Or Coalesce(is_concluded_in_e_shop,false)=true
			Then True
		Else Null
	End::char is_concluded_in_e_shop,--Признак заключения контракта в ЭМ
	--cc.is_concluded_in_e_shop::char is_concluded_in_e_shop--Признак заключения контракта в ЭМ
	--pl.purchase_code pg_ikz,    --  ИКЗ Плана-графика
	--pl.purchase_code_in_plan pz_ikz,    --  ИКЗ Плана закупок
	--l.plan_position_number pg_rn -- РН позиции Плана-графика
	cc.special_contract_type,
	pl.contract_execution_start_date,
	Case
		When cc.price_type ='P'
			Then 'Цена контракта'
		When cc.price_type ='OP'
			Then 'Ориентировочное значение цены контракта'
		When cc.price_type ='MP'
			Then 'Максимальное значение цены контракта' --Способ указания цены контракта
	End price_type,
	cc.document_date document_date, --Дата документа, являющегося основанием от 25.01.2021
	creason.name contract_modification_reasons, --Причина изменения условий контракта/договора от 25.01.2021
	cdocuments.name modif_reason_documents, --Наименование документа основания внесения изменений от 25.01.2021
	Case
		When pl.drugs_purchase Is True
			Then 1
		Else 0
	End drugs_purchase, --лекарственные препараты 31.05.2021
	ccf_p.fulfilment_sum_rur --Стоимость исполненных поставщиком обязательств, руб 31.05.2021
	,cc.violation_deadlines--Причина принятия бюджетных обязательств в нарушение срока
	,substr(dd.name, 0, 1000) as name  --Причина принятия бюджетных обязательств в нарушение срока (описание)
	,substr(cc.violation_deadlines_description, 0, 1000) as violation_reasons_not_21 --Причины, не установленные пунктом 21 постановления Правительства №42
    ,ccpen.amount_of_penalty  --размер неустойки (штрафа, пени), по которой осуществлено списание штрафных санкций
	,ccpen.penalty_write_off_information --Флаг актуальности по информации об осуществлении списания штрафных санкций
	,ccpen.penalty_sum --сумма всех списанных штрафных санкций по контракту
	,ccf_p.fulfilment_sum --Стоимость исполненных обязательств 29.08.2022
	,Case
		When ppr_smp_sonco.preference_short_name in ('PVS33044','MP44')
		Then 1
		Else 0
	End As preference_smp_sonco -- признак наличия преимуществ для СМП и  социально ориентированных некоммерческих организаций
	,Case
		When ppr_inv.preference_short_name ='IN44'
		Then 1
		Else 0
	End As preference_invalid -- признак наличия преимуществ для организаций инвалидов
	,Case
		When ppr_ug.preference_short_name ='UG44'
		Then 1
		Else 0
	End As preference_ugolov -- признак наличия преимуществ для учреждений уголовно-исполнительной системы
	,s.oktmo -- октмо
	,Case
		When pl.supply_impossible is true
		Then 1
		Else 0
	End As supply_impossible, -- Признак заключения контракта с неопределенным объемом товаров, работ, услуг
	cc.rtc_price, -- Цена за право заключения контракта
	ccf_p.acceptance_doc --флаг документа о приемке
From gpospb.cm_contracts cc
Inner Join gpospb.lots l On (l.id = cc.lot_id And l.actual = true And (cc.contract_rnk Is Not Null Or cc.external_id Is Not Null) and cc.id not in (150039,81681,70830,91739,104632,215242,216796,217707,217712,217795,219368,240051,624804))
Inner Join gpospb.procedures pr On (l.procedure_id = pr.id And pr.actual = true)
Inner Join gpospb.po_procedures pp On (pp.procedure_id = pr.id)
Inner Join vps_nks On (vps_nks.ps_id = l.current_step)
Inner Join lc On (lc.lot_id = l.id)
Inner Join gpospb.contragents c On (c.id = cc.contract_customer)
Inner Join gpospb.contragents s On (s.id = cc.supplier_id)
Left join ccss_pvss on ccss_pvss.contract_id=cc.id
Left Join gpospb.po_lots pl	on pl.lot_id = l.id
Left Join gpospb.po_vocab_time_periods f_pvtp On (f_pvtp.id = pp.finance_time_period_id)
Left Join first_fin On (first_fin.lot_id = l.id)
Left Join first_fin_cont On (first_fin_cont.lot_id = l.id)
Left Join vpt_nkp On (vpt_nkp.id = pr.procedure_type)
Left Join gpospb.cm_contract_supplier_names cmscn On (cc.id=cmscn.contract_id)
Left Join supplieradress On (supplieradress.contragent_id = s.id)
Left Join gpospb.po_list_org_hierarchy ploh On (ploh.contragent_id = cc.contract_customer and ploh.parent_type = 1 and ploh.actual = true)
Left join grbs On (grbs.id = ploh.parent_contragent_id)
Left Join fins On (fins.lot_id = l.id)
Left Join ccf_p On (ccf_p.lot_id = l.id)
Left Join  ppr as ppr_smp_sonco On ppr_smp_sonco.lot_id = l.id and ppr_smp_sonco.preference_short_name in ('PVS33044','MP44') and ppr_smp_sonco.actual is true
Left Join  ppr as ppr_inv On ppr_inv.lot_id = l.id and ppr_inv.preference_short_name ='IN44' and ppr_inv.actual is true
Left Join  ppr as ppr_ug On ppr_ug.lot_id = l.id and ppr_ug.preference_short_name ='UG44' and ppr_ug.actual is true
Left Join  ppr as ppr_inostr On ppr_inostr.lot_id = l.id and ppr_inostr.preference_short_name ='RBK44' and ppr_inostr.actual is true
Left Join firstnotice On (firstnotice.id = cc.id)
Left Join gpospb.cm_list_contract_termination_reasons clctr On (clctr.id = cc.cancel_reason and clctr.actual = true)
Left Join gpospb.po_list_contract_single_supplier_reasons plcssr On (pp.basis_for_single_supplier = plcssr.code)
Left Join gpospb.supplier_profiles sp On (sp."id" = s.supplier_profile_id)
Left Join Join_lots On Join_lots.lot_id = l.id
Left Join ccpen On (ccpen.cm_contract_id = cc.id)
Left Join gpospb.cm_list_contract_modification_reasons creason On (creason.code=cc.modification_base)
Left Join gpospb.cm_list_contract_modif_reason_documents cdocuments On (cdocuments.code=cc.modification_base_doc)
Left Join gpospb.cm_contract_quality_guarantee_info ccqgi on c.id=ccqgi.contract_id
left Join gpospb.cm_vocab_conclusion_violation_of_deadlines dd  on dd.id=cc.violation_deadlines and dd.actual is TRUE
/*WHERE (Case
		When f_pvtp.name Is Not Null
			Then f_pvtp.name
		Else first_fin.year_
	END) in ('2017','2018','2019','2020','2021','2022','2023')*/


-- Order By l.id a
;

--Удаляем промежуточные тб.
Drop table first_fin;
Drop table first_fin_cont;
Drop table supplieradress;
Drop table fins;
Drop table ccf_p;
Drop table firstnotice;
Drop table Join_lots;
Drop table ccpen;
Drop table lc;
Drop table ccss_pvss;
Drop table grbs;
Drop table vps_nks;
Drop table vpt_nkp;
Drop table ppr;

--Возвращаем данные
return query select * From public.dwh_contract_kgntv_func;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1e+06