Перейти к основному содержанию
`Ctrl`+`K`

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

Заполняет или восстанавливает обозначения контрагентов для данных 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();