Заполнение обозначений контрагентов

Заполняет или восстанавливает обозначения контрагентов для данных GDS. Используется для служебной корректировки справочных данных модуля.

Место применения: Сервис > Инструменты > Выполнить JEXL-скрипт

Внимание

Скрипт привязан к объектам конкретного модуля и требует проверки на целевой базе. Перед запуском проверьте идентификаторы, SQL-запросы, API-классы и условия обработки.

Тип: JEXL-скрипт

	var ropList =  sql(`
		select distinct cd.id
from(
	with obezl as (
	           	select distinct nch21 as sDesignation from sormovo_obezl
	           )
	           ,spko as (
	           	select distinct nsp as sDesignation from sormovo.sormovo_spko
	           )
	           ,sprkrep as (
	           	select distinct nch as sDesignation from sormovo_sprkrep
	           )
	           select
	           	sp.id as idSrcSpec
	            ,sp.idConstructor
	           	,sp1.id as idCurSpec
	           	,sv.id as idCurVer
	           	,sv.idstate as idCurVerState
	           	,case
	           		when tt.sDesignationDraftOrig != tt.sDesignation
	           			and (coalesce(t2.sDesignation, t3.sDesignation, t4.sDesignation) is not null
	           				or g2.id is not null)
	           		then case when tt.sCaption like '%  05' or  tt.sCaption like '%  5'
	           			then 'СД'
	           			else case
	           				when tt.sRoutePath like '67%' or tt.sRoutePath like '68%'
	           				then 'ТМЦ МСЧ'
	           				else 'МСЧ'
	           				end
	           			end
	              when  (tt.sRoutePath like '67%' or tt.sRoutePath like '68%')
	                  and tt.sMaterialCode = '00000000000'
	                then
	                  'ТМЦ NEW'
	           		else case when tt.sRoutePath like '21%'
	           			or tt.sRoutePath like '24%'
	           			or tt.sRoutePath like '26%'
	           			or tt.sMaterialCode = '00000000000'
	                or tt.sLocatedPlace like 'П%'
	           			then 'ИНФ'
	           			else case when tt.sCaption like '%  05' or  tt.sCaption like '%  5'
	           				then case when tt.bIsShip = 1
	           				          or (tt.nQty = 1
	                              and tt.sDesignationDraftOrig = tt.sDesignation
	                              and tt.bIsWeight = 1)
	           					then 'ТМЦ СД'
	           					else 'Д СД'
	           					end
	           				else case
	           					when tt.sRoutePath like '67%' or tt.sRoutePath like '68%'
	           					then 'ТМЦ'
	           					else 'Д'
	           					end
	           				end
	           			end
	           		end as sPosType
	           	,g1.id as idGds
	           	,g2.id as idUnitProd
	           	,g3.id as idGdsNew
	           	,tt.*
	           from (
	           	select
	           		t.mashnom as sLocatedPlace
	           		,t.nsp as sDesignationDraftOrig
	           		,regexp_replace(t.nsp, '(.+\-\d{6})(\-)(\d*\-\d*)', '\1.\3') as sDesignationDraft
	           		,t.naim as sCaption
	           		,case
	           		when t.nsp like '%3621%' then 1
	           		else 0 end as bIsShip
	           		,t.poz as sPosition
	           		,t.nch as sDesignation
	           		,cast(t.kol as numeric) as nQty
	           		,cast(t.ves as numeric) as nMassFull
	           		,nullif(t.marka, '') as sMatMarkProj
	           		,t.marshr as sRoutePath
	           		,t.trud as sTechComplect
	           		,regexp_replace(t.zakaz, '(\d)(\d){1}(\d*)', '\1\3') as sPrjVers
	           		,t.pokr as sCoverType
	           		,t.shifr as sMaterialCode
	           		,cast(lpad(t.eiv, 1, '0') as numeric) as bIsWeight
	           		,m.id as idMsrNormItem
	           		,cast(t.norma as numeric) as nNorm
	           		,nullif(t.razmer, '') as sSize
	           		,cast(case when t.razmer != '' and t.razmer SIMILAR TO '([0-9]*)' and t.razmer not similar to '([0]*)' and t.razmer is not null
	           			then  substring(lpad(t.razmer, 9, '0'),1,5) || '.' || substring(lpad(t.razmer, 9, '0'),6)
	           			else cast (null as varchar(254))
	           		end as numeric) as nNormInSize
	           	from sormovo.sormovo_spko t
	           	left join sormovo_eizm ei on lpad(t.einv, 3, '0') = ei.ed
	           	left join msr_measureitem m on upper(ei.ted) = upper(m.sshortname)
	           	--where t.nsp = 'RSD59-362313-04-001'
	           ) tt
	           left join (
	             select distinct on (regexp_replace(scode, 'СБ', ''))
	               id
	               ,regexp_replace(scode, 'СБ', '') as sCode
	               ,idConstructor
	             from mct_specification
	             ) sp on tt.sDesignationDraft = sp.scode
	           left join mct_specification sp1 on tt.sDesignationDraft||'(ИВЦ)' = sp1.scode
	           left join mct_specificationver sv on (sp1.id = sv.idmctdocument and sv.bislast = 1)
	           left join obezl t2 on tt.sDesignation = t2.sDesignation
	           left join spko t3 on tt.sDesignation = t3.sDesignation
	           left join sprkrep t4 on tt.sDesignation = t4.sDesignation
	           left join bs_goods g1 on tt.sMaterialCode = g1.sarticle
	           left join bs_goods g2 on tt.sDesignation = g2.sarticle
	           left join bs_goods g3 on (tt.sDesignation = g3.sDesignation and tt.sCaption = g3.sname)
	           order by tt.sDesignationDraftOrig, tt.sLocatedPlace
) t 
join gds_contrasdesignation cd on cd.sdesignation = t.sDesignation and cd.idGds != t.idUnitProd and cd.idContras = 99206
join bs_goods g on cd.idGds = g.id 
where t.sPosType = 'МСЧ'
		`).batchObjLoad(Gds_ContrasDesignationApi, "id");
	for (rop : ropList){
		Gds_ContrasDesignationApi.delete(rop);
	}
	commit();