if exists ( select * from sys.all_objects ao where ao.name = 'sp_MtrSchetLoadD_Bulk_3_2' ) DROP PROCEDURE [dbo].[sp_MtrSchetLoadD_Bulk_3_2]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_MtrSchetLoadD_Bulk_3_2]
  @SchetID uniqueidentifier
 ,@type int
 ,@Worker uniqueidentifier 
AS 
BEGIN

update tempdb..TEMP_ZGLV_MTR set ID = '1'
update tempdb..TEMP_SCHET_MTR set ID = '1'
update tempdb..TEMP_ZAP_MTR set [SchetZapID] = newid()
update tempdb..TEMP_Z_SLUCH_MTR set [SchetSluchAccomplishedID] = newid()
update tempdb..TEMP_SANK_MTR set [SchetSluchSankID] = newid()
update tempdb..TEMP_SANK_SL_ID_MTR set [SchetSluchSankSLID] = newid() 
update tempdb..TEMP_SLUCH_MTR set [SchetSluchID] = newid()
update tempdb..TEMP_ONK_SL_MTR  set SchetSluchOnkID = newid()
update tempdb..TEMP_ONK_USL_MTR set [SchetOnkUslID] = newid() 
update tempdb..TEMP_SANK_MTR set [SchetSluchSankID] = newid() 
update tempdb..TEMP_KSG_KPG_MTR set [KsgID] = newid() 
update tempdb..TEMP_USL_MTR  set [SchetUslID] = newid()

-- ФЛК второго уровня, проверка зависимостей невозможная с помощью XSD
declare @NSCHET varchar(15) = (select top 1 [NSCHET] from tempdb..TEMP_SCHET_MTR)
declare @q019type varchar(1) = 'D'

create table #Errors
(
	OSHIB varchar(36),
	IM_POL varchar(10),
	ZN_POL varchar(100),
	NSCHET varchar(15),
	BAS_EL varchar(10),
	N_ZAP int,	
	IDCASE varchar(11),
	SL_ID varchar(36),
	IDSERV varchar(36),
	COMMENT varchar(250)
)

if ((select COUNT(*) from tempdb..TEMP_SCHET_MTR t1 
	 JOIN tempdb..TEMP_ZGLV_MTR t3 on (t3.ID = t1.ID)
	 JOIN [IES].[T_SCHET] t2 with(nolock) on t2.[type_] = 538 and t2.[DSCHET] = t1.[DSCHET] and t2.[NSCHET] = t1.[NSCHET] and t2.[C_OKATO1] = t3.[C_OKATO1] and t2.[IsDelete] = 0) = 0)
BEGIN
	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'NSCHET', convert(varchar(17), @NSCHET) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Для исправительного счета не найден основной'
END

declare @id uniqueidentifier
set @id = (select t2.SchetID from tempdb..TEMP_SCHET_MTR t1
join tempdb..TEMP_ZGLV_MTR t3 on (t3.ID = t1.ID)
join IES.T_SCHET t2 with(nolock) on t2.type_=538 and t2.DSCHET = t1.DSCHET and t2.NSCHET = t1.NSCHET and t2.C_OKATO1 = t3.C_OKATO1 and t2.IsDelete = 0)

if (@id is not null)
begin

insert into #Errors (IM_POL, BAS_EL, N_ZAP, IDCASE, IDSERV, OSHIB, COMMENT)
select 'IDCASE', 'SLUCH', null, ts.IDCASE, null, '905', 'IDCASE отсутствует в основном счете'
from tempdb..TEMP_SLUCH_MTR ts
outer apply 
(select isnull(cast(ss.IDCASE as varchar(11)), ssa.IDCASE) as IDCASE
from IES.T_SCHET_SLUCH ss with(nolock)
JOIN IES.T_SCHET_ZAP sz with(nolock) on (ss.SchetZap = sz.SchetZapID)
join IES.T_SCHET_SLUCH_ACCOMPLISHED ssa with(nolock) on (ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID)
where sz.Schet = @id and ss.IsDelete = 0 and sz.IsDelete = 0 and ssa.IsDelete = 0 and isnull(cast(ss.IDCASE as varchar(11)), cast(ssa.IDCASE as varchar(11))) = ts.IDCASE) ca
where ca.IDCASE is null

insert into #Errors (IM_POL, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SL_ID', 'SLUCH', null, ts.IDCASE, ts.SL_ID, null, '905', 'IDCASE + SL_ID отсутствует в основном счете'
from tempdb..TEMP_SLUCH_MTR ts
outer apply 
(select isnull(cast(ss.IDCASE as varchar(11)), ssa.IDCASE) as IDCASE
	, isnull(ss.SL_ID, '') as SL_ID
from IES.T_SCHET_SLUCH ss
JOIN IES.T_SCHET_ZAP sz on (ss.SchetZap = sz.SchetZapID)
join IES.T_SCHET_SLUCH_ACCOMPLISHED ssa on (ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID)
where sz.Schet = @id and ss.IsDelete = 0 and sz.IsDelete = 0 and ssa.IsDelete = 0 and isnull(cast(ss.IDCASE as varchar(11)), cast(ssa.IDCASE as varchar(11))) = ts.IDCASE and isnull(ss.SL_ID, '') = isnull(ts.SL_ID, '')) ca
where ca.IDCASE is null or ca.SL_ID is null

--insert into #Errors (IM_POL, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--select 'SL_ID', 'SLUCH', null, ts.IDCASE, ts.SL_ID, null, '905', 'Этот случай был принят в основном счёте'
--from tempdb..TEMP_SLUCH_MTR ts
--outer apply 
--(select isnull(ss.IDCASE, ssa.IDCASE) as IDCASE
--	, isnull(ss.SL_ID, '') as SL_ID
--from IES.T_SCHET_SLUCH ss
--JOIN IES.T_SCHET_ZAP sz on (ss.SchetZap = sz.SchetZapID)
--join IES.T_SCHET_SLUCH_ACCOMPLISHED ssa on (ss.SchetSluchAccomplished = ssa.SchetSluchAccomplishedID)
--join IES.T_SCHET_RESULTS sr on (sr.SchetSluchMain = ss.SchetSluchID)
--where sz.Schet = @id and sr.SchetSluchAccepted = sr.SchetSluchMain and ss.IsDelete = 0 and sz.IsDelete = 0 and ssa.IsDelete = 0 and isnull(ss.IDCASE, ssa.IDCASE) = ts.IDCASE and isnull(ss.SL_ID, '') = isnull(ts.SL_ID, '')
--and ss.SchetSluchID not in ('a5f22b5a-2465-41d4-b75f-12318135db83', '0e876138-3e2f-4e2f-bfb7-d74985203da4')
--) ca
--where ca.IDCASE is not null or ca.SL_ID is not null


insert into #Errors (IM_POL, BAS_EL, N_ZAP, IDCASE, IDSERV, OSHIB, COMMENT)
select 'SUMMAV', 'SCHET', null, null, null, '905', 'Сумма выставленная к оплате меньше в основном счете'
from tempdb..TEMP_SCHET_MTR ts
join IES.T_SCHET s with(nolock) on s.SchetID = @id
where ts.SUMMAV > s.SUMMAV

end

--проверка уникальности идентификаторов
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'N_ZAP', N_ZAP as ZN_POL, @NSCHET, 'ZAP', N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор N_ZAP' from tempdb..TEMP_ZAP_MTR
group by N_ZAP
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'IDCASE', IDCASE as ZN_POL, @NSCHET, 'Z_SL', null as N_ZAP, IDCASE as IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор IDCASE' from tempdb..TEMP_Z_SLUCH_MTR
group by IDCASE
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SL_ID', SL_ID as ZN_POL, @NSCHET, 'SL', null as N_ZAP, IDCASE, SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор SL_ID, в пределах законченного случая' from tempdb..TEMP_SLUCH_MTR
group by IDCASE, SL_ID
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'IDSERV', IDSERV as ZN_POL, @NSCHET, 'USL', null as N_ZAP, IDCASE, SL_ID, IDSERV, '905', 'Дублирующий идентификатор IDSERV в пределах одного случая' from tempdb..TEMP_USL_MTR
where IDSERV is not null
group by IDCASE, SL_ID, IDSERV
having count(*)>1

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_CODE', S_CODE as ZN_POL, @NSCHET, 'SANK', null as N_ZAP, IDCASE, null as SL_ID, null as IDSERV, '905', 'Дублирующий идентификатор S_CODE, в пределах законченного случая' from tempdb..TEMP_SANK_MTR
group by IDCASE, S_CODE
having count(*)>1

--проверка, что суммы сходятся
declare @sumsluch decimal(17,2)
select @sumsluch =  Sum([SUM_M]) from tempdb..TEMP_SLUCH_MTR
declare @sumZsluch decimal(17,2)
select @sumZsluch =  Sum([SUMV]) from tempdb..TEMP_Z_SLUCH_MTR
declare @sumschet decimal(17,2)
select @sumschet = SUM([SUMMAV]) from tempdb..TEMP_SCHET_MTR
if (@sumsluch <> @sumschet) 
begin
	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'SUMMAV', convert(varchar(17), @sumschet) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Сумма на счете не равна сумме случаев'
end
else if (@sumZsluch <> @sumschet) 
begin
	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'SUMMAV', convert(varchar(17), @sumschet) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '905', 'Сумма на счете не равна сумме закочненных случаев'
end
else if (@sumZsluch <> @sumsluch) 
begin
	insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
	select 'SUMV', convert(varchar(17), @sumZsluch) as ZN_POL ,@NSCHET, 'Z_SL', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV,  '905', 'Сумма закочненных случаев не равна сумме случаев'
end

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'USL_OK', convert(varchar(2), t0.USL_OK) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0160', 'Значение не найдено в справочнике V006'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V006_MEDICAL_TERMS t2 with(nolock) on t2.IDUMP = t0.USL_OK
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.USL_OK is not null
and t2.IDUMP is null

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CODE_SH', CODE_SH as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0610', 'Значение не найдено в справочнике V024'
from tempdb..TEMP_LEK_PR_DATE_INJ_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V024_DOP_KR t2 with(nolock) on t0.[CODE_SH] = t2.[IDDKK] and t2.[DATEBEG]<=t1.[DATE_Z_2] and (t2.[DATEEND] is null or t2.[DATEEND]>=t1.[DATE_Z_2])
where
@q019type in ('C', 'D', 'H', 'R')
and t0.CODE_SH is not null
and t2.IDDKK is null


--003F.00.0832 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CONS', null as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0831'
, ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and not exists (select top 1 1 from tempdb..TEMP_CONS_MTR t01 where t01.sl_id = t0.sl_id and t01.idcase = t0.idcase)
and isnull(DISP,0)=0 and (DS_ONK=1 or DS1 like 'C%' or DS1 like 'D0%')
and DATE_Z_2 >= convert(date,'01.04.2020',104)
and DATE_Z_2 <= convert(date,'31.12.2020',104)
--003F.00.0832 end

--003F.00.0834 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CONS', null as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0834'
, ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and not exists (select top 1 1 from tempdb..TEMP_CONS_MTR t01 where t01.sl_id = t0.sl_id and t01.idcase = t0.idcase)
and isnull(DISP,0) = 0 and (DS_ONK=1 or DS1 like 'C%' or DS1 like 'D0%' or (DS1 >= 'D45' and DS1 < 'D48'))
and DATE_Z_2 >= convert(date,'01.01.2021',104)
--003F.00.0834 end

--003F.00.0852 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CONS', null as ZN_POL ,@NSCHET, 'SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0851'
, ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and exists (select top 1 1 from tempdb..TEMP_CONS_MTR t01 where t01.sl_id = t0.sl_id and t01.idcase = t0.idcase)
and not(isnull(DISP,0)=0 and (DS_ONK=1 or DS1 like 'C%' or DS1 like 'D0%'))
and DATE_Z_2 >= convert(date,'01.04.2020',104)
and DATE_Z_2 <= convert(date,'31.12.2020',104)
--003F.00.0852 end

--003F.00.0854 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CONS', null as ZN_POL ,@NSCHET, 'SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0854'
, ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and exists (select top 1 1 from tempdb..TEMP_CONS_MTR t01 where t01.sl_id = t0.sl_id and t01.idcase = t0.idcase)
and not (isnull(DISP,0)=0 and (DS_ONK=1 or DS1 like 'C%' or DS1 like 'D0%' or (DS1 >= 'D45' and DS1 < 'D48')))
and DATE_Z_2 >= convert(date,'01.01.2021',104)
--003F.00.0854 end

--003F.00.0891 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ONK_SL', null as ZN_POL ,@NSCHET, 'SL', t2.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0891'
, ''
from tempdb..TEMP_ONK_SL_MTR t0
join tempdb..TEMP_SLUCH_MTR t1 with(nolock) on t0.SL_ID = t1.SL_ID and t0.IDCASE = t1.IDCASE
join tempdb..TEMP_Z_SLUCH_MTR t2 with(nolock) on t2.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and t0.DS1_T is null -- мб элемент обязателен
and (DS1 like 'C%' or DS1 like 'D0%')
and USL_OK != 4
and REAB != 1
and DS_ONK = 0
and isnull(DISP,0) != 1
and DATE_Z_2 >= convert(date,'01.04.2020',104)
and DATE_Z_2 <= convert(date,'31.12.2020',104)
--003F.00.0891 end

--003F.00.0892 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ONK_SL', null as ZN_POL ,@NSCHET, 'SL', t2.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0892'
, ''
from tempdb..TEMP_ONK_SL_MTR t0
join tempdb..TEMP_SLUCH_MTR t1 with(nolock) on t0.SL_ID = t1.SL_ID and t0.IDCASE = t1.IDCASE
join tempdb..TEMP_Z_SLUCH_MTR t2 with(nolock) on t2.IDCASE = t1.IDCASE
where
@q019type in ('D', 'R')
and t0.DS1_T is null -- мб элемент обязателен
and (DS1 like 'C%' or DS1 like 'D0%' or (DS1 >= 'D45' and DS1 < 'D48'))
and USL_OK != 4
and REAB != 1
and DS_ONK = 0
and isnull(DISP,0) != 1
and DATE_Z_2 >= convert(date,'01.01.2021',104)
--003F.00.0892 end

--001F.00.0650 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VID_VME', VID_VME as ZN_POL ,@NSCHET, 'USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0650', 'Значение не найдено в справочнике V019'
from tempdb..TEMP_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V019_VMP_METHOD t2 with(nolock) on cast(t2.IDHM as varchar(15)) = t0.VID_VME  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('D', 'R')
and VIDPOM=32 and t0.VID_VME is not null
and t2.IDHM is null

and DATE_Z_2 >= convert(date,'01.11.2019',104)
--001F.00.0650 end





--001F.00.0060 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VPOLIS', convert(varchar(1), t0.VPOLIS) as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '001F.00.0060', 'Значение не найдено в справочнике F008'
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
join tempdb..TEMP_ZAP_MTR t1 with(nolock) on t0.N_ZAP= t1.N_ZAP
join tempdb..TEMP_Z_SLUCH_MTR t3 with(nolock) on t3.n_zap = t0.N_ZAP
left join ies.T_F008_OMS_TYPE t2 with(nolock) on t2.IDDoc = t0.VPOLIS
and t2.DATEBEG<=t3.DATE_Z_2 and (t2.DATEEND is null or t2.DATEEND>=t3.DATE_Z_2) 
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.VPOLIS is not null
and t2.IDDoc is null

--001F.00.0060 end

--001F.00.0070 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ST_OKATO', ST_OKATO as ZN_POL ,@NSCHET, 'PACIENT', t1.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '001F.00.0070', 'Значение не найдено в справочнике F010'
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
join tempdb..TEMP_ZAP_MTR t1 with(nolock) on t0.N_ZAP= t1.N_ZAP
join tempdb..TEMP_Z_SLUCH_MTR t3 with(nolock) on t3.n_zap = t0.N_ZAP
left join ies.T_F010_SUBJECT t2 with(nolock) on t2.KOD_OKATO = t0.ST_OKATO and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.ST_OKATO is not null
and t2.KOD_OKATO is null

--001F.00.0070 end

--001F.00.0170 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VIDPOM', convert(varchar(4), t0.VIDPOM) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0170', 'Значение не найдено в справочнике V008'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V008_MEDICAL_TYPE t2 with(nolock) on t2.IDVMP = t0.VIDPOM  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.VIDPOM is not null
and t2.IDVMP is null

--001F.00.0170 end

--001F.00.0180 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'FOR_POM', convert(varchar(1), t0.FOR_POM) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0180', 'Значение не найдено в справочнике V014'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V014_MEDICAL_FORM t2 with(nolock) on t2.IDFRMMP = t0.FOR_POM  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.FOR_POM is not null
and t2.IDFRMMP is null

--001F.00.0180 end

--001F.00.0190 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPR_MO', NPR_MO as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0190', 'Значение не найдено в справочнике F003'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_F003_MO t2 with(nolock) on t2.mcod = t0.NPR_MO
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.NPR_MO is not null
and t2.mcod is null

--001F.00.0190 end

--001F.00.0200 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LPU', LPU as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0200', 'Значение не найдено в справочнике F003'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_F003_MO t2 with(nolock) on t2.mcod = t0.LPU
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.LPU is not null
and t2.mcod is null

--001F.00.0200 end

--001F.00.0210 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'RSLT', convert(varchar(3), t0.RSLT) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0210', 'Значение не найдено в справочнике V009'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V009_RESULT t2 with(nolock) on t2.IDRMP = t0.RSLT  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.RSLT is not null
and t2.IDRMP is null

--001F.00.0210 end

--001F.00.0220 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ISHOD', convert(varchar(3), t0.ISHOD) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0220', 'Значение не найдено в справочнике V012'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V012_OUTCOME t2 with(nolock) on t2.IDIZ = t0.ISHOD  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.ISHOD is not null
and t2.IDIZ is null

--001F.00.0220 end

--001F.00.0230 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'IDSP', convert(varchar(2), t0.IDSP) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '001F.00.0230', 'Значение не найдено в справочнике V010'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_V010_PAY t2 with(nolock) on t2.IDSP = t0.IDSP  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.IDSP is not null
and t2.IDSP is null

--001F.00.0230 end

--001F.00.0250 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VID_HMP', VID_HMP as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0250', 'Значение не найдено в справочнике V018'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.idcase
left join ies.T_V018_VMP_TYPE t2 with(nolock) on t2.IDHVID = t0.VID_HMP
and t2.DATEBEG<=DATE_Z_2 and (t2.DATEEND is null or t2.DATEEND>=DATE_Z_2) 
left join ies.T_V018_VMP_TYPE_PREV t3 with(nolock) on t3.V018VmpType= t0.VID_HMP
and t3.DATEBEG<=DATE_Z_2 and (t3.DATEEND is null or t3.DATEEND>=DATE_Z_2) 
where
@q019type in ('D', 'R', 'T')
and t0.VID_HMP is not null
and t2.IDHVID is null
and t3.V018VmpType is null

--001F.00.0250 end

--001F.00.0260 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'METOD_HMP', convert(varchar(3), t0.METOD_HMP) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0260', 'Значение не найдено в справочнике V019'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V019_VMP_METHOD t2 with(nolock) on t2.IDHM = t0.METOD_HMP and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('D', 'R', 'T')
and t0.METOD_HMP is not null
and t2.V019VmpMethodID is null
--001F.00.0260 end

--001F.00.0270 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PROFIL', convert(varchar(3), t0.PROFIL) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0270', 'Значение не найдено в справочнике V002'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V002_PROFILE t2 with(nolock) on t2.IDPR = t0.PROFIL and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.PROFIL is not null
and t2.IDPR is null

--001F.00.0270 end

--001F.00.0280 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PROFIL_K', convert(varchar(3), t0.PROFIL_K) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0280', 'Значение не найдено в справочнике V020'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V020_BED_PROFILE t2 with(nolock) on t2.IDK_PR = t0.PROFIL_K and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.PROFIL_K is not null
and t2.IDK_PR is null

--001F.00.0280 end

--001F.00.0290 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'P_CEL', P_CEL as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0290', 'Значение не найдено в справочнике V025'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V025_KPC t2 with(nolock) on t2.IDPC = t0.P_CEL and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R')
and t0.P_CEL is not null
and t2.IDPC is null

--001F.00.0290 end

--001F.00.0300 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'C_ZAB', convert(varchar(1), t0.C_ZAB) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0300', 'Значение не найдено в справочнике V027'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V027_C_ZAB t2 with(nolock) on t2.IDCZ = t0.C_ZAB and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.C_ZAB is not null
and t2.IDCZ is null

--001F.00.0300 end

--001F.00.0310 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PRVS', convert(varchar(4), t0.PRVS) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0310', 'Значение не найдено в справочнике V021'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
left join ies.T_V021_MED_SPEC t2 with(nolock) on t2.CODE_SPEC = t0.PRVS
and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.PRVS is not null
and t2.CODE_SPEC is null

--001F.00.0310 end

--001F.00.0370 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NAPR_MO', NAPR_MO as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0370', 'Значение не найдено в справочнике F003'
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_F003_MO t2 with(nolock) on t2.mcod = t0.NAPR_MO 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.NAPR_MO is not null
and t2.mcod is null

--001F.00.0370 end

--001F.00.0380 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NAPR_V', convert(varchar(2), t0.NAPR_V) as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0380', 'Значение не найдено в справочнике V028'
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V028_NAPR_V t2 with(nolock) on t2.IDVN = t0.NAPR_V  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.NAPR_V is not null
and t2.IDVN is null
--001F.00.0380 end

--001F.00.0390 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'MET_ISSL', convert(varchar(2), t0.MET_ISSL) as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0390', 'Значение не найдено в справочнике V029'
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V029_MET_ISSL t2 with(nolock) on t2.IDMET = t0.MET_ISSL  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.MET_ISSL is not null
and t2.IDMET is null

--001F.00.0390 end

--001F.00.0400 begin

insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PR_CONS', convert(varchar(1), t0.PR_CONS) as ZN_POL ,@NSCHET, 'CONS', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0400', 'Значение не найдено в справочнике N019'
from tempdb..TEMP_CONS_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N019_ONK_CONS t2 with(nolock) on t2.ID_CONS = t0.PR_CONS  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.PR_CONS is not null
and t2.ID_CONS is null

--001F.00.0400 end

--001F.00.0410 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DS1_T', convert(varchar(2), t0.DS1_T) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0410', 'Значение не найдено в справочнике N018'
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N018_ONK_REAS t2 with(nolock) on t2.ID_REAS = t0.DS1_T  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.DS1_T is not null
and t2.ID_REAS is null

--001F.00.0410 end

--001F.00.0420 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'STAD', convert(varchar(3), t0.STAD) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0420', 'Значение не найдено в справочнике N002'
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N002_STADIUM t2 with(nolock) on t2.ID_St = t0.STAD  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.STAD is not null
and t2.ID_St is null

--001F.00.0420 end

--001F.00.0430 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ONK_T', convert(varchar(4), t0.ONK_T) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0430', 'Значение не найдено в справочнике N003'
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N003_TUMOR t2 with(nolock) on t2.ID_T = t0.ONK_T  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.ONK_T is not null
and t2.ID_T is null

--001F.00.0430 end

--001F.00.0440 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ONK_N', convert(varchar(4), t0.ONK_N) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0440', 'Значение не найдено в справочнике N004'
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N004_NODUS t2 with(nolock) on t2.ID_N = t0.ONK_N  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.ONK_N is not null
and t2.ID_N is null

--001F.00.0440 end

--001F.00.0450 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'ONK_M', convert(varchar(4), t0.ONK_M) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0450', 'Значение не найдено в справочнике N005'
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N005_METASTASIS t2 with(nolock) on t2.ID_M = t0.ONK_M  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.ONK_M is not null
and t2.ID_M is null

--001F.00.0450 end

--001F.00.0460 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_CODE', convert(varchar(3), t0.DIAG_CODE) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0460', 'Значение не найдено в справочнике N007'
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_N007_MRF t2 with(nolock) on t2.ID_Mrf = t0.DIAG_CODE
and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'R', 'T')
and DIAG_TIP=1
and t2.ID_Mrf is null
--001F.00.0460 end

--001F.00.0470 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_CODE', convert(varchar(3), t0.DIAG_CODE) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0470', 'Значение не найдено в справочнике N010'
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_N010_MARK t2 with(nolock) on t2.ID_Igh = t0.DIAG_CODE
and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'R', 'T')
and DIAG_TIP=2
and t2.ID_Igh is null
--001F.00.0470 end

--001F.00.0480 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_RSLT', convert(varchar(3), t0.DIAG_RSLT) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0480', 'Значение не найдено в справочнике N008'
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N008_MRF_RT t2 with(nolock) on t2.ID_R_M = t0.DIAG_RSLT
and ((DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)) or t0.DIAG_DATE < '01.09.2018')
where
@q019type in ('C', 'D', 'R', 'T')
and DIAG_TIP=1
and t0.DIAG_RSLT is not null
and t2.ID_R_M is null
--001F.00.0480 end

--001F.00.0490 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_RSLT', convert(varchar(3), t0.DIAG_RSLT) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0490', 'Значение не найдено в справочнике N011'
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N011_MARK_VALUE t2 with(nolock) on t2.ID_R_I = t0.DIAG_RSLT
and ((DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)) or t0.DIAG_DATE < '01.09.2018')
where
@q019type in ('C', 'D', 'R', 'T')
and DIAG_TIP=2
and t2.ID_R_I is null
and t0.DIAG_RSLT is not null
--001F.00.0490 end

--001F.00.0500 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PROT', convert(varchar(1), t0.PROT) as ZN_POL ,@NSCHET, 'B_PROT', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0500', 'Значение не найдено в справочнике N001'
from tempdb..TEMP_B_PROT_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N001_PrOt t2 with(nolock) on t2.ID_PrOt = t0.PROT  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.PROT is not null
and t2.ID_PrOt is null

--001F.00.0500 end

--001F.00.0510 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'USL_TIP', convert(varchar(1), t0.USL_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0510', 'Значение не найдено в справочнике N013'
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N013_TREAT_TYPE t2 with(nolock) on t2.ID_TLech = t0.USL_TIP  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.USL_TIP is not null
and t2.ID_TLech is null

--001F.00.0510 end

--001F.00.0520 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'HIR_TIP', convert(varchar(1), t0.HIR_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0520', 'Значение не найдено в справочнике N014'
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N014_SURG_TREAT t2 with(nolock) on t2.ID_THir = t0.HIR_TIP  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.HIR_TIP is not null
and t2.ID_THir is null

--001F.00.0520 end

--001F.00.0530 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_L', convert(varchar(1), t0.LEK_TIP_L) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0530', 'Значение не найдено в справочнике N015'
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N015_DRUG_THERAPY_LINES t2 with(nolock) on t2.ID_TLek_L = t0.LEK_TIP_L  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.LEK_TIP_L is not null
and t2.ID_TLek_L is null
--001F.00.0530 end

--001F.00.0540 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_V', convert(varchar(1), t0.LEK_TIP_V) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0540', 'Значение не найдено в справочнике N016'
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N016_DRUG_THERAPY_CYCLES t2 with(nolock) on t2.ID_TLek_V = t0.LEK_TIP_V  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.LEK_TIP_V is not null
and t2.ID_TLek_V is null
--001F.00.0540 end

--001F.00.0550 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LUCH_TIP', convert(varchar(1), t0.LUCH_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0550', 'Значение не найдено в справочнике N017'
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N017_RADIATION_THERAPY_TYPES t2 with(nolock) on t2.ID_TLuch = t0.LUCH_TIP  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.LUCH_TIP is not null
and t2.ID_TLuch is null

--001F.00.0550 end

--001F.00.0560 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'REGNUM', REGNUM as ZN_POL ,@NSCHET, 'LEK_PR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0560', 'Значение не найдено в справочнике N020'
from tempdb..TEMP_LEK_PR_DATE_INJ_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_N020_ONK_LEKP t2 with(nolock) on t2.ID_LEKP = t0.REGNUM  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'R', 'T')
and t0.REGNUM is not null
and t2.ID_LEKP is null

--001F.00.0560 end

--001F.00.0580 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'N_KSG', t0.N_KSG as ZN_POL ,@NSCHET, 'KSG_KPG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0580', 'Значение не найдено в справочнике V023'
from tempdb..TEMP_KSG_KPG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_V023_KSG t2 with(nolock) on t2.K_KSG = t0.N_KSG
and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R')
and KSG_PG=0
and t0.N_KSG is not null
and t2.K_KSG is null

--001F.00.0580 end

--001F.00.0590 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'N_KPG', t0.N_KPG as ZN_POL ,@NSCHET, 'KSG_KPG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0590', 'Значение не найдено в справочнике V026'
from tempdb..TEMP_KSG_KPG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V026_KPG t2 with(nolock) on t2.K_KPG = t0.N_KPG  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R')
and t0.N_KPG is not null
and t2.K_KPG is null


--001F.00.0590 end

--001F.00.0600 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'KOEF_Z', convert(varchar, cast(t0.KOEF_Z as money)) as ZN_POL ,@NSCHET, 'KSG_KPG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0600', 'Значение KOEF_Z должно совпадать со значением V023.KOEF_Z записи, соответствующей номеру КСГ'
from tempdb..TEMP_KSG_KPG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V023_KSG t2 with(nolock) on t2.KOEF_Z = t0.KOEF_Z
and t0.n_ksg =t2.k_ksg and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R')
and KSG_PG=0
and t2.v023ksgid is null
and t0.n_ksg is not null

--001F.00.0600 end

--001F.00.0610 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CRIT', CRIT as ZN_POL ,@NSCHET, 'KSG_KPG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0610', 'Значение не найдено в справочнике V024'
from tempdb..TEMP_CRIT_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_V024_DOP_KR t2 with(nolock) on t2.IDDKK = t0.CRIT  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
 join tempdb..TEMP_KSG_KPG_MTR t3 on t1.IDCASE = t3.IDCASE
where
@q019type in ('C', 'D', 'H', 'R')
and KSG_PG=0
and t0.CRIT is not null
and t2.IDDKK is null


--001F.00.0610 end

--001F.00.0620 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LPU', t0.LPU as ZN_POL ,@NSCHET, 'USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0620', 'Значение не найдено в справочнике F003'
from tempdb..TEMP_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_F003_MO t2 with(nolock) on t2.mcod = t0.LPU  
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.LPU is not null
and t2.mcod is null

--001F.00.0620 end

--001F.00.0630 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PROFIL', convert(varchar(3), t0.PROFIL) as ZN_POL ,@NSCHET, 'USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0630', 'Значение не найдено в справочнике V002'
from tempdb..TEMP_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_V002_PROFILE t2 with(nolock) on t2.IDPR = t0.PROFIL  and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2) 
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and t0.PROFIL is not null
and t2.IDPR is null

--001F.00.0630 end

--001F.00.0660 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PRVS', convert(varchar(4), t0.PRVS) as ZN_POL ,@NSCHET, 'USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0660', 'Значение не найдено в справочнике V021'
from tempdb..TEMP_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

left join ies.T_V021_MED_SPEC t2 with(nolock) on t2.CODE_SPEC = t0.PRVS
 and DATEBEG<=DATE_Z_2 and (DATEEND is null or DATEEND>=DATE_Z_2)
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.PRVS is not null
and t2.CODE_SPEC is null

--001F.00.0660 end

--001F.00.0670 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_TIP', convert(varchar(2), t0.S_TIP) as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0670', 'Значение не найдено в справочнике F006'
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_F006_CONTROL_TYPE t2 with(nolock) on t2.IDVID = t0.S_TIP  and DATEBEG<=isnull(t0.DATE_ACT,t1.DATE_Z_2) and (DATEEND is null or DATEEND>=isnull(t0.DATE_ACT,t1.DATE_Z_2) ) 
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.S_TIP is not null
and t2.IDVID is null

--001F.00.0670 end

--001F.00.0680 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_OSN', convert(varchar(3), t0.S_OSN) as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0680', 'Значение не найдено в справочнике F014'
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_F014_DENY_REASON t2 with(nolock) on t2.Kod = t0.S_OSN
 and DATEBEG<=isnull(t0.DATE_ACT,t1.DATE_Z_2) and (DATEEND is null or DATEEND>=isnull(t0.DATE_ACT,t1.DATE_Z_2))
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and S_OSN is not null
and t2.Kod is null

--001F.00.0680 end

--002F.00.0130 begin
--insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--select 'NOVOR', t0.NOVOR as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '002F.00.0130', ''
--from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
--join tempdb..TEMP_ZAP_MTR t1 with(nolock) on t0.N_ZAP= t1.N_ZAP
--where
--@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
--and t0.NOVOR != '0'
--and [dbo].[RegExMatch](t0.NOVOR,'^[12](([012][0-9])|(3[01]))((1[0-2])|(0[1-9]))(1[0-9]|[23][0-9])(([1-9])|10)$') != 1

--002F.00.0130 end

--002F.00.0140 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VNOV_D', convert(varchar(4), t0.VNOV_D) as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '002F.00.0140', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
join tempdb..TEMP_ZAP_MTR t1 with(nolock) on t0.N_ZAP= t1.N_ZAP
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and not (300<VNOV_D and VNOV_D<2500)
and VNOV_D is not null



--002F.00.0140 end

--002F.00.0150 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VNOV_M', convert(varchar(4), t0.VNOV_M) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '002F.00.0150', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and not (300<VNOV_M and VNOV_M<2500)
and VNOV_M is not null
--002F.00.0150 end

--002F.00.0160 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'OS_SLUCH', convert(varchar(1), t0.OS_SLUCH) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '002F.00.0160', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.OS_SLUCH not in ('1','2')

--002F.00.0160 end

--002F.00.0180 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'OPLATA', convert(varchar(1), t0.OPLATA) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '002F.00.0180', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and 
(
	(isnull(t0.OPLATA, -1) not in (0, 1, 2, 3) and @q019type in ('A'))
	or 
	(isnull(t0.OPLATA, 0) not in (0, 1, 2, 3) and @q019type in ('C', 'D', 'H', 'R', 'T', 'X'))
)


--002F.00.0180 end

--002F.00.0220 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DET', convert(varchar(1), t0.DET) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0220', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and not (DET in (0,1))

--002F.00.0220 end

--002F.00.0250 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DS_ONK', convert(varchar(1), t0.DS_ONK) as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0250', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'R', 'T', 'X')
and not ( t0.DS_ONK in (0,1) )

--002F.00.0250 end

--002F.00.0320 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VERS_SPEC', VERS_SPEC as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0320', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and VERS_SPEC != 'V021'

--002F.00.0320 end

--002F.00.0360 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'MTSTZ', convert(varchar(1), t0.MTSTZ) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0360', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and MTSTZ is not null
and MTSTZ != 1

--002F.00.0360 end

--002F.00.0370 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'WEI', convert(varchar, cast(t0.WEI as money)) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0370', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and WEI is not null
and WEI >= 500

--002F.00.0370 end

--002F.00.0380 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'HEI', convert(varchar(3), t0.HEI) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0380', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and HEI is not null
and HEI >= 260

--002F.00.0380 end

--002F.00.0390 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'BSA', convert(varchar, cast(t0.BSA as money)) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0390', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and BSA is not null
and BSA >= 6

--002F.00.0390 end

--002F.00.0400 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_TIP', convert(varchar(1), t0.DIAG_TIP) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0400', ''
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and not (DIAG_TIP in (1,2))


--002F.00.0400 end

--002F.00.0410 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'REC_RSLT', convert(varchar(1), t0.REC_RSLT) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0410', ''
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and REC_RSLT is not null
and REC_RSLT != 1
--002F.00.0410 end

--002F.00.0420 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'PPTR', convert(varchar(1), t0.PPTR) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0420', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and PPTR is not null
and PPTR != 1
--002F.00.0420 end

--002F.00.0510 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DET', convert(varchar(1), t0.DET) as ZN_POL ,@NSCHET, 'USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '002F.00.0510', ''
from tempdb..TEMP_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'H', 'R', 'T')
and DET is not null
and not (DET in (0,1))

--002F.00.0510 end

--003F.00.0030 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SANK_MEK', convert(varchar, cast(t0.SANK_MEK as money)) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '003F.00.0030', ''
from tempdb..TEMP_SCHET_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and isnull(t0.SANK_MEK,0) <=0
and exists (select top 1 1 from tempdb..TEMP_SANK_MTR where S_TIP in (1, 10,11,12) and isnull(S_SUM,0)<>0 and S_IST=1)


--003F.00.0030 end

--003F.00.0040 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SANK_MEE', convert(varchar, cast(t0.SANK_MEE as money)) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '003F.00.0040', ''
from tempdb..TEMP_SCHET_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and isnull(t0.SANK_MEE,0) <=0
and exists (select top 1 1 from tempdb..TEMP_SANK_MTR where S_TIP>19 and S_TIP<30 and isnull(S_SUM,0)<>0 and S_IST=1)

--003F.00.0040 end

--003F.00.0050 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SANK_EKMP', convert(varchar, cast(t0.SANK_EKMP as money)) as ZN_POL ,@NSCHET, 'SCHET', null as N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '003F.00.0050', ''
from tempdb..TEMP_SCHET_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and isnull(t0.SANK_EKMP,0) <=0
and exists (select top 1 1 from tempdb..TEMP_SANK_MTR where S_TIP>29 and S_TIP<49 and isnull(S_SUM,0)<>0 and S_IST=1)

--003F.00.0050 end

--003F.00.0080 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SPOLIS', t0.SPOLIS as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '003F.00.0080', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
join tempdb..TEMP_ZAP_MTR t1 with(nolock) on t0.N_ZAP= t1.N_ZAP
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and SPOLIS is not null
and VPOLIS<>1

--003F.00.0080 end

--003F.00.0390 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VNOV_D', convert(varchar(4), t0.VNOV_D) as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '003F.00.0390', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.N_ZAP = t1.N_ZAP
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and VNOV_D is not null
and VNOV_M is not null

--003F.00.0390 end

--003F.00.0400 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPR_MO', NPR_MO as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0400', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and ((FOR_POM=3 and USL_OK=1) or USL_OK=2)
and NPR_MO is null

--003F.00.0400 end

--003F.00.0410 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPR_DATE', convert(varchar(10),t0.NPR_DATE,104) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0410', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and ((FOR_POM=3 and USL_OK=1) or USL_OK=2)
and NPR_DATE is null


--003F.00.0410 end

--003F.00.0430 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'KD_Z', convert(varchar(3), t0.KD_Z) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0430', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R')
and USL_OK in (1, 2)
and kd_z is null

--003F.00.0430 end

--003F.00.0440 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'KD_Z', convert(varchar(3), t0.KD_Z) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0440', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('C', 'D', 'H', 'R')
and KD_Z is not null
and not (USL_OK in (1, 2))

--003F.00.0440 end

--003F.00.0450 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'VNOV_M', convert(varchar(4), t0.VNOV_M) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0450', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_PACIENT_MTR t1 with(nolock) on t1.n_zap = t0.n_zap
where
@q019type in ('C', 'D', 'H', 'R', 'T')
and VNOV_D is not null
and VNOV_M is not null

--003F.00.0450 end

--003F.00.0580 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SANK',null  as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0580', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.SANK_IT is not null
and not exists (select top 1 1 from tempdb..TEMP_SANK_MTR t1 where t1.IDCASE = t0.IDCASE)

--003F.00.0580 end

--003F.00.0590 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SANK_IT', convert(varchar, cast(t0.SANK_IT as money)) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0590', ''
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.SANK_IT is null
and exists (select top 1 1 from tempdb..TEMP_SANK_MTR t1 with(nolock) where t0.IDCASE = t1.IDCASE and isnull(s_sum,0) >0 )


--003F.00.0590 end

--003F.00.0660 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'P_CEL', convert(varchar(3), t0.P_CEL) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID as SL_ID, null as IDSERV,  '003F.00.0660', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
where
@q019type in ('C', 'D', 'H', 'R')
and (isnull(USL_OK,0) = 3)
and t0.P_CEL is null
--003F.00.0660 end

--003F.00.0670 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'P_CEL', convert(varchar(3), t0.P_CEL) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID as SL_ID, null as IDSERV,  '003F.00.0670', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
where
@q019type in ('C', 'D', 'H', 'R')
and (isnull(USL_OK, 0) != 3)
and t0.P_CEL is not null
--003F.00.0670 end

--003F.00.0740 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'KD', convert(varchar(3), t0.KD) as ZN_POL ,@NSCHET, 'Z_SL', t0.N_ZAP, t0.IDCASE, null as SL_ID, null as IDSERV,  '003F.00.0740', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.idcase = t1.idcase
where
@q019type in ('C', 'D', 'H', 'R')
and KD is null
and (USL_OK in (1, 2))
--003F.00.0740 end

--003F.00.0830 begin
INSERT INTO #Errors ( IM_POL ,ZN_POL ,NSCHET ,BAS_EL ,N_ZAP ,IDCASE ,SL_ID ,IDSERV ,OSHIB ,COMMENT )
SELECT 'CONS' ,NULL AS ZN_POL ,@NSCHET ,'SL' ,t0.N_ZAP ,t0.IDCASE ,t0.SL_ID ,NULL AS IDSERV ,'003F.00.0830' ,''
FROM tempdb..TEMP_SLUCH_MTR t0 WITH (NOLOCK)
WHERE @q019type IN ('D', 'R', 'T')
	AND NOT EXISTS (
		SELECT TOP 1 1
		FROM tempdb..TEMP_CONS_MTR t1
		WHERE t1.SL_ID = t0.SL_ID
			AND t1.IDCASE = t0.IDCASE
		)
	AND (
		DS_ONK = 1
		OR (
			DS1 LIKE 'C%'
			OR DS1 LIKE 'D0%'
			OR (
				DS1 = 'D70'
				AND EXISTS (
					SELECT TOP 1 1
					FROM tempdb..TEMP_DS2_MTR t1
					WHERE (
								(
								DS2 >= 'C00'
								AND DS2 < 'C81'
								)
								OR DS2 LIKE 'C97%'
							)
						AND t1.SL_ID = t0.SL_ID
						AND t1.IDCASE = t0.IDCASE
					)
				)
			)
		)
and DATE_2 <= convert(date,'31.03.2020',104)
--003F.00.0830 end

-- --003F.00.0850 begin

-- INSERT INTO #Errors (
	-- IM_POL
	-- ,ZN_POL
	-- ,NSCHET
	-- ,BAS_EL
	-- ,N_ZAP
	-- ,IDCASE
	-- ,SL_ID
	-- ,IDSERV
	-- ,OSHIB
	-- ,COMMENT
	-- )
-- SELECT 'CONS'
	-- ,NULL AS ZN_POL
	-- ,@NSCHET
	-- ,'SL'
	-- ,t0.N_ZAP
	-- ,t0.IDCASE
	-- ,t0.SL_ID
	-- ,NULL AS IDSERV
	-- ,'003F.00.0850'
	-- ,''
-- FROM tempdb..TEMP_SLUCH_MTR t0 WITH (NOLOCK)
-- WHERE @q019type IN ('D', 'R', 'T')
	-- AND EXISTS (
		-- SELECT TOP 1 1
		-- FROM tempdb..TEMP_CONS_MTR t1
		-- WHERE t1.SL_ID = t0.SL_ID
			-- AND t1.IDCASE = t0.IDCASE
		-- )
	-- AND NOT (
		-- DS_ONK = 1
		-- OR (
			-- DS1 LIKE 'C%'
			-- OR DS1 LIKE 'D0%'
			-- OR (
				-- DS1 = 'D70'
				-- AND EXISTS (
					-- SELECT TOP 1 1
					-- FROM tempdb..TEMP_DS2_MTR t1
					-- WHERE ((DS2 >= 'C00' AND DS2 < 'C81') or ds2 like 'C97%')
						-- AND t1.SL_ID = t0.SL_ID
						-- AND t1.IDCASE = t0.IDCASE
					-- )
				-- )
			-- )
		-- )
-- and DATE_2 <= convert(date,'31.03.2020',104)
-- --003F.00.0850 end

--003F.00.0960 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'USL', null as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0960', ''
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1  with(nolock) on t0.idcase = t1.idcase
left join tempdb..TEMP_ONK_USL_MTR t2 with(nolock) on t0.SL_ID = t2.SL_ID and t0.IDCASE = t2.IDCASE
where
@q019type in ('C', 'D', 'R')
and (
DS1 like 'C%'
or DS1 like 'D0%'
or (
         DS1 = 'D70'
         and exists ( select top 1 1 from tempdb..TEMP_DS2_MTR t3 where ((DS2>='C00' and DS2<'C81') or DS2 like 'C97%') and  t3.SL_ID = t0.SL_ID and t3.IDCASE = t0.IDCASE)
    )

)
and isnull(t2.USL_TIP,0) in (1,3,4,6)
and not exists (select top 1 1 from tempdb..TEMP_USL_MTR t4 where t4.sl_id = t0.sl_id and t4.idcase = t0.idcase)
--003F.00.0960 end

--003F.00.0990 begin
--insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--select 'ONK_T', convert(varchar(4), t0.ONK_T) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0990', 'Обязателен при DS1_T=0 и возраст пациента на DATE_Z_1 больше или равен 18 лет'
--from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
--join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
--join tempdb..TEMP_PACIENT_MTR t2 with(nolock) on t1.N_ZAP = t2.N_ZAP
--join tempdb..TEMP_SLUCH_MTR t3  with(nolock) on t3.sl_id = t0.sl_id and t3.idcase = t0.idcase
--where
--@q019type in ('C', 'D', 'R', 'T')
--and t0.ONK_T is null
--and t0.ds1_t = 0
--and datediff(year,t2.dr,t3.date_1)>=18
--003F.00.0990 end

--003F.00.1000 begin
--insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--select 'ONK_N', convert(varchar(4), t0.ONK_T) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0990', 'Обязателен при DS1_T=0 и возраст пациента на DATE_Z_1 больше или равен 18 лет'
--from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
--join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
--join tempdb..TEMP_PACIENT_MTR t2 with(nolock) on t1.N_ZAP = t2.N_ZAP
--join tempdb..TEMP_SLUCH_MTR t3  with(nolock) on t3.sl_id = t0.sl_id and t3.idcase = t0.idcase
--where
--@q019type in ('C', 'D', 'R', 'T')
--and t0.ONK_N is null
--and t0.ds1_t = 0
--and datediff(year,t2.dr,t3.date_1)>=18
--003F.00.1000 end

--003F.00.1010 begin
--insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
--select 'ONK_M', convert(varchar(4), t0.ONK_T) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.0990', 'Обязателен при DS1_T=0 и возраст пациента на DATE_Z_1 больше или равен 18 лет'
--from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
--join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
--join tempdb..TEMP_PACIENT_MTR t2 with(nolock) on t1.N_ZAP = t2.N_ZAP
--join tempdb..TEMP_SLUCH_MTR t3  with(nolock) on t3.sl_id = t0.sl_id and t3.idcase = t0.idcase
--where
--@q019type in ('C', 'D', 'R', 'T')
--and t0.ONK_M is null
--and t0.ds1_t = 0
--and datediff(year,t2.dr,t3.date_1)>=18
--003F.00.1010 end

--003F.00.1020 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'MTSTZ', convert(varchar(1), t0.MTSTZ) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1020', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and t0.MTSTZ is not null
and not (t0.DS1_T in (1,2))


--003F.00.1020 end

--003F.00.1030 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SOD', convert(varchar, cast(t0.SOD as money)) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1030', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

join tempdb..TEMP_ONK_USL_MTR t2 with(nolock) on t2.SL_ID = t0.SL_ID and t2.IDCASE = t0.IDCASE
where
@q019type in ('C', 'D', 'R', 'T')
and t0.SOD is null
and isnull(t2.USL_TIP,0) in (3,4)

--003F.00.1030 end

--003F.00.1040 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SOD', convert(varchar, cast(t0.SOD as money)) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1040', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and t0.SOD is not null
and not exists (select top 1 1 from tempdb..TEMP_ONK_USL_MTR t2 with(nolock) where t2.SL_ID = t0.SL_ID and t2.IDCASE = t0.IDCASE and isnull(t2.USL_TIP,0) in (3,4))

--003F.00.1040 end

--003F.00.1050 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'K_FR', convert(varchar(2), t0.K_FR) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1050', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

join tempdb..TEMP_ONK_USL_MTR t2 with(nolock) on t2.SL_ID = t0.SL_ID and t2.IDCASE = t0.IDCASE
where
@q019type in ('C', 'D', 'R', 'T')
and t0.K_FR is null
and isnull(t2.USL_TIP,0) in (3,4)

--003F.00.1050 end

--003F.00.1060 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'K_FR', convert(varchar(2), t0.K_FR) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1060', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and t0.K_FR is not null
and not exists (select top 1 1 from tempdb..TEMP_ONK_USL_MTR t2 with(nolock) where t2.SL_ID = t0.SL_ID and t2.IDCASE = t0.IDCASE and isnull(t2.USL_TIP,0) in (3,4))


--003F.00.1060 end

--003F.00.1070 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'WEI', convert(varchar, cast(t0.WEI as money)) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1070', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and BSA is not null
and WEI is null

--003F.00.1070 end

--003F.00.1080 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'HEI', convert(varchar(3), t0.HEI) as ZN_POL ,@NSCHET, 'ONK_SL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1080', ''
from tempdb..TEMP_ONK_SL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and BSA is not null
and HEI is null

--003F.00.1080 end

--003F.00.1190 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'MET_ISSL', convert(varchar(2), t0.MET_ISSL) as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1190', ''
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and  MET_ISSL is null
and NAPR_V=3

--003F.00.1190 end

--003F.00.1200 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'MET_ISSL', convert(varchar(2), t0.MET_ISSL) as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1200', ''
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and MET_ISSL is not null
and NAPR_V != 3

--003F.00.1200 end

--003F.00.1210 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NAPR_USL', NAPR_USL as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1210', ''
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and NAPR_USL is null
and MET_ISSL is not null

--003F.00.1210 end

--003F.00.1220 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NAPR_USL', NAPR_USL as ZN_POL ,@NSCHET, 'NAPR', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1220', ''
from tempdb..TEMP_NAPR_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and NAPR_USL is not null
and MET_ISSL is null

--003F.00.1220 end

--003F.00.1230 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DT_CONS', convert(varchar(10),t0.DT_CONS,104) as ZN_POL ,@NSCHET, 'CONS', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1230', ''
from tempdb..TEMP_CONS_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and PR_CONS in (1,2,3)
and DT_CONS is null

--003F.00.1230 end

--003F.00.1240 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DT_CONS', convert(varchar(10),t0.DT_CONS,104) as ZN_POL ,@NSCHET, 'CONS', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1240', ''
from tempdb..TEMP_CONS_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and not (PR_CONS in (1,2,3))
and DT_CONS is not null


--003F.00.1240 end

--003F.00.1250 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DIAG_RSLT', convert(varchar(3), t0.DIAG_RSLT) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1250', ''
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and REC_RSLT =1
and DIAG_RSLT is null

--003F.00.1250 end

--003F.00.1260 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'REC_RSLT', convert(varchar(1), t0.REC_RSLT) as ZN_POL ,@NSCHET, 'B_DIAG', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1260', ''
from tempdb..TEMP_B_DIAG_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and REC_RSLT is null
and DIAG_RSLT is not null

--003F.00.1260 end

--003F.00.1410 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'HIR_TIP', convert(varchar(1), t0.HIR_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1410', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP=1
and HIR_TIP is null

--003F.00.1410 end

--003F.00.1420 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'HIR_TIP', convert(varchar(1), t0.HIR_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1420', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP != 1
and HIR_TIP is not null

--003F.00.1420 end

--003F.00.1430 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_L', convert(varchar(1), t0.LEK_TIP_L) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1430', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP=2
and LEK_TIP_L is null

--003F.00.1430 end

--003F.00.1440 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_L', convert(varchar(1), t0.LEK_TIP_L) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1440', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP != 2
and LEK_TIP_L is not null

--003F.00.1440 end

--003F.00.1450 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_V', convert(varchar(1), t0.LEK_TIP_V) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1450', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP=2
and LEK_TIP_V is null


--003F.00.1450 end

--003F.00.1460 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_TIP_V', convert(varchar(1), t0.LEK_TIP_V) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1460', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP != 2
and LEK_TIP_V is not null

--003F.00.1460 end

--003F.00.1470 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LUCH_TIP', convert(varchar(1), t0.LUCH_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1470', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP in (3, 4)
and LUCH_TIP is null

--003F.00.1470 end

--003F.00.1480 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LUCH_TIP', convert(varchar(1), t0.LUCH_TIP) as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1480', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and not (USL_TIP in (3, 4))
and LUCH_TIP is not null

--003F.00.1480 end

--003F.00.1490 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'LEK_PR', null as ZN_POL ,@NSCHET, 'ONK_USL', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1490', ''
from tempdb..TEMP_ONK_USL_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('C', 'D', 'R', 'T')
and USL_TIP in (2, 4)
and not exists (select top 1 1 from tempdb..TEMP_LEK_PR_DATE_INJ_MTR t2 with(nolock) where t2.SL_ID = t0.SL_ID and t2.IDCASE = t0.IDCASE)

--003F.00.1490 end

--003F.00.1770 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'SL_ID', SL_ID as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1770', ''
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and isnull(t0.s_sum,0) != 0
and t0.sl_id is null

--003F.00.1770 end

--003F.00.1780 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_OSN', convert(varchar(3), t0.S_OSN) as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.1780', ''
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and isnull(t0.s_sum,0) != 0
and t0.S_OSN is null

--003F.00.1780 end

--003F.00.2800 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'S_SUM', convert(varchar, cast(t0.S_SUM as money)) as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '003F.00.2800', ''
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE

where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.S_SUM is null

--003F.00.2800 end

--004F.00.0230 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPOLIS', NPOLIS as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '004F.00.0230', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.VPOLIS=1
and [dbo].[RegExMatch](t0.NPOLIS,'^\d{1,20}$') != 1


--004F.00.0230 end

--004F.00.0240 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPOLIS', NPOLIS as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '004F.00.0240', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.VPOLIS=2
and [dbo].[RegExMatch](t0.NPOLIS,'^\d{9}$') != 1

--004F.00.0240 end

--004F.00.0250 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'NPOLIS', NPOLIS as ZN_POL ,@NSCHET, 'PACIENT', t0.N_ZAP, null as IDCASE, null as SL_ID, null as IDSERV, '004F.00.0250', ''
from tempdb..TEMP_PACIENT_MTR t0 with(nolock)
where
@q019type in ('A', 'C', 'D', 'H', 'R', 'T', 'X')
and t0.VPOLIS=3
and [dbo].[RegExMatch](t0.NPOLIS,'^\d{16}$') != 1
and t0.NPOLIS is not null
--004F.00.0250 end

--005F.00.0040 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'DS1', DS1 as ZN_POL ,@NSCHET, 'SL', t0.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '005F.00.0040', 'Значение не найдено в справочнике M001'
from tempdb..TEMP_SLUCH_MTR t0 with(nolock)
left join ies.T_MKB t2 with(nolock) on t2.MCBCode = t0.DS1

where
@q019type in ('C', 'D', 'H', 'R', 'T', 'X')
and t0.DS1 is not null
and t2.MCBCode is null
and t2.treelevel >=3

--005F.00.0040 end

IF (SELECT COUNT(*) FROM #Errors)> 0
BEGIN
SELECT 
'<?xml version="1.0" encoding="Windows-1251"?>
' + (
SELECT 
	OSHIB AS 'OSHIB',
	IM_POL AS 'IM_POL',
	ZN_POL AS 'ZN_POL',
	@NSCHET AS 'NSCHET',
	BAS_EL AS 'BAS_EL',
	N_ZAP AS 'N_ZAP',
	IDCASE AS 'IDCASE',
	SL_ID AS 'SL_ID',
	IDSERV AS 'IDSERV',
	COMMENT AS 'COMMENT'
FROM #Errors
FOR XML PATH('PR'),
ROOT('FLK_P')
)
END
ELSE
BEGIN

if ((select COUNT(*) from tempdb..TEMP_SCHET_MTR t1 
	 JOIN tempdb..TEMP_ZGLV_MTR t3 on (t3.ID = t1.ID)
	 JOIN [IES].[T_SCHET] t2 with(nolock) on t2.[type_] = 538 and t2.[DSCHET] = t1.[DSCHET] and t2.[NSCHET] = t1.[NSCHET] and t2.[C_OKATO1] = t3.[C_OKATO1] and t2.[IsDelete] = 0) = 0)
BEGIN
	 raiserror('ОШИБКА: Для исправительного счета не найден основной', 18,1)
END

ELSE
BEGIN

SELECT '[IES].[T_SCHET]' as tableName, ' информации о счете' as operationName, (select count(*) from tempdb..TEMP_SCHET_MTR) as tableCount
	 
SELECT 
 zglv.[VERSION] AS [VERSION]
,zglv.[DATA] AS [DATA]
,zglv.[C_OKATO1] AS [C_OKATO1]
,zglv.[OKATO_OMS] AS [OKATO_OMS]
,schet.[CODE] AS [CODE]
,schet.[YEAR] AS [YEAR]
,schet.[MONTH] AS [MONTH]
,schet.[NSCHET] AS [NSCHET]
,schet.[DSCHET] AS [DSCHET]
,schet.[SUMMAV] AS [SUMMAV]
,schet.[COMENTS] AS [COMENTS]
,schet.[SUMMAP] AS [SUMMAP]
,schet.[SANK_MEK] AS [SANK_MEK]
,schet.[SANK_MEE] AS [SANK_MEE]
,schet.[SANK_EKMP] AS [SANK_EKMP]
,CAST(CAST(CAST(GETDATE() as float) as INT) as datetime) AS [ReceivedDate]
,GETDATE() AS [ReceivedTime]
,@Worker AS [Worker]
,@SchetID AS [SchetID]
,555 AS [type_]
,0 AS [Status]
,1 AS [SchetKind]
,schetmain.SchetID AS [SchetMain]
FROM tempdb..TEMP_SCHET_MTR schet  
JOIN tempdb..TEMP_ZGLV_MTR zglv ON (zglv.ID = schet.ID)
JOIN [IES].[T_SCHET] schetmain ON (schetmain.[type_] = 538 and schetmain.[DSCHET] = schet.[DSCHET] and schetmain.[NSCHET] = schet.[NSCHET] and schetmain.[C_OKATO1] = zglv.[C_OKATO1] and schetmain.[IsDelete] = 0)

SELECT '[IES].[T_SCHET_ZAP]' as tableName, ' информации о людях' as operationName, (select count(*) from tempdb..TEMP_ZAP_MTR) as tableCount

select t0.[N_ZAP], Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost
	From
    (
        Select distinct cast([N_ZAP] as varchar(36)) as [N_ZAP], 
            (
                Select cast(t1.[N_ZAP] as varchar(1)) + ',' AS [text()]
                From tempdb..TEMP_DOST_MTR t1
                Where cast(t1.[N_ZAP] as varchar(36)) = cast(ST2.[N_ZAP] as varchar(36))
                ORDER BY cast(t1.[N_ZAP] as varchar(36))
                For XML PATH ('')
            ) rezult
        From tempdb..TEMP_DOST_MTR ST2
    ) t0 

select t0.[N_ZAP], Left(t0.rezult,Len(t0.rezult)-1) As rezult into #temp_dost_p
	From
    (
        Select distinct cast([N_ZAP] as varchar(36)) as [N_ZAP], 
            (
                Select cast(t1.[N_ZAP] as varchar(1)) + ',' AS [text()]
                From tempdb..TEMP_DOST_P_MTR t1
                Where cast(t1.[N_ZAP] as varchar(36)) = cast(ST2.[N_ZAP] as varchar(36))
                ORDER BY cast(t1.[N_ZAP] as varchar(36))
                For XML PATH ('')
            ) rezult
        From tempdb..TEMP_DOST_P_MTR ST2
    ) t0 	

SELECT 
 zap.[SchetZapID] AS [SchetZapID]
,zap.[N_ZAP] AS [N_ZAP]
,@SchetID AS [Schet]
,505 AS [type_]
,pacient.[VPOLIS] AS [VPOLIS]
,pacient.[SPOLIS] AS [SPOLIS]
,pacient.[NPOLIS] AS [NPOLIS]
,pacient.[ENP] AS [ENP]
,pacient.[ST_OKATO] AS [ST_OKATO]
,pacient.[FAM] AS [FAM]
,pacient.[IM] AS [IM]
,pacient.[OT] AS [OT]
,pacient.[W] AS [W]
,pacient.[DR] AS [DR]
,dost.[rezult] AS [DOST]
,pacient.[FAM_P] AS [FAM_P]
,pacient.[IM_P] AS [IM_P]
,pacient.[OT_P] AS [OT_P]
,pacient.[W_P] AS [W_P]
,pacient.[DR_P] AS [DR_P]
,dost_p.[rezult] AS [DOST_P]
,pacient.[MR] AS [MR]
,pacient.[DOCTYPE] AS [DOCTYPE]
,pacient.[DOCSER] AS [DOCSER]
,pacient.[DOCNUM] AS [DOCNUM]
,pacient.[DOCDATE] AS [DOCDATE]
,pacient.[DOCORG] AS [DOCORG]
,pacient.[SNILS] AS [SNILS]
,pacient.[OKATOG] AS [OKATOG]
,pacient.[OKATOP] AS [OKATOP]
,pacient.[NOVOR] AS [NOVOR]
,pacient.[VNOV_D] AS [VNOV_D]
,pacient.[COMENTP] AS [COMENTP]
FROM tempdb..TEMP_ZAP_MTR zap
JOIN tempdb..TEMP_PACIENT_MTR pacient ON (pacient.[N_ZAP] = zap.[N_ZAP])
LEFT JOIN #temp_dost   dost   ON (pacient.[N_ZAP] = dost.[N_ZAP])
LEFT JOIN #temp_dost_p dost_p ON (pacient.[N_ZAP] = dost_p.[N_ZAP])

SELECT '[IES].[T_SCHET_SLUCH_ACCOMPLISHED]' as tableName, ' информации о законченных случаях лечения' as operationName, (select count(*) from tempdb..TEMP_Z_SLUCH_MTR) as tableCount
  
SELECT 
 zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplishedID]
,zap.[SchetZapID] AS [SchetZap]
,zSL.[IDCASE] AS [IDCASE]
,zSL.[USL_OK] AS [USL_OK]
,zSL.[VIDPOM] AS [VIDPOM]
,zSL.[FOR_POM] AS [FOR_POM]
,zSL.[NPR_MO] AS [NPR_MO]
,zSL.[NPR_DATE] AS [NPR_DATE]
,zSL.[P_DISP2] AS [P_DISP2]
,zSL.[LPU] AS [LPU]
,zSL.[DATE_Z_1] AS [DATE_Z_1]
,zSL.[DATE_Z_2] AS [DATE_Z_2]
,zSL.[KD_Z] AS [KD_Z]
,zSL.[VNOV_M] AS [VNOV_M]
,zSL.[RSLT] AS [RSLT]
,zSL.[ISHOD] AS [ISHOD]
,zSL.[OS_SLUCH] AS [OS_SLUCH]
,zSL.[VB_P] AS [VB_P]
,zSL.[IDSP] AS [IDSP]
,zSL.[SUMV] AS [SUMV]
,zSL.[OPLATA] AS [OPLATA]
,zSL.[SUMP] AS [SUMP]
,zSL.[SANK_IT] AS [SANK_IT]
,0 as [MedicalExpertiseStatus]
FROM tempdb..TEMP_Z_SLUCH_MTR zSL 
JOIN tempdb..TEMP_ZAP_MTR zap ON (zap.[N_ZAP] = zSL.[N_ZAP])

SELECT '[IES].[T_SCHET_SLUCH]' as tableName, ' информации о случаях лечения' as operationName, (select count(*) from tempdb..TEMP_SLUCH_MTR) as tableCount

SELECT 
 sl.[SchetSluchID] AS [SchetSluchID]
,zap.[SchetZapID] AS [SchetZap]
,zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplished]
,506 AS [type_]
,sl.[SL_ID] AS [SL_ID]
,sl.[VID_HMP] AS [VID_HMP]
,(select top 1 v019.V019VmpMethodID from IES.T_V019_VMP_METHOD v019 where sl.METOD_HMP = v019.IDHM and v019.DATEBEG<=zSL.DATE_Z_2 and (v019.DATEEND is null or v019.DATEEND>=zSL.DATE_Z_2)) AS [METOD_HMP]
,sl.[PROFIL] AS [PROFIL]
,(select top 1 v020.[V020BedProfileId] from [IES].[T_V020_BED_PROFILE] v020 where sl.[PROFIL_K] = v020.[IDK_PR] and v020.[DATEBEG]<=zSL.[DATE_Z_2] and (v020.[DATEEND] is null or v020.[DATEEND] >= zSL.[DATE_Z_2])) AS [PROFIL_K]
,sl.[DET] AS [DET]
,(select top 1 v025.[V025KpcID] from [IES].[T_V025_KPC] v025 where v025.[IDPC] = sl.[P_CEL] and v025.[DATEBEG]<=zSL.[DATE_Z_2] and (v025.[DATEEND] is null or v025.[DATEEND] >= zSL.[DATE_Z_2])) AS [P_CEL]
,sl.[DISP] AS [DISP]
,sl.[TAL_D] AS [TAL_D]
,sl.[NHISTORY] AS [NHISTORY]
,sl.[DATE_1] AS [DATE_1]
,sl.[DATE_2] AS [DATE_2]
,sl.[KD] AS [KD]
,sl.[DS0] AS [DS0]
,sl.[DS1] AS [DS1]
,(select top 1 v027.[V027CZabID] from [IES].[T_V027_C_ZAB] v027 where v027.[IDCZ] = sl.[C_ZAB] and v027.[DATEBEG]<=zSL.[DATE_Z_2] and (v027.[DATEEND] is null or v027.[DATEEND] >= zSL.[DATE_Z_2])) AS [C_ZAB]
,sl.[DS_ONK] AS [DS_ONK]
,sl.[DN] AS [DN]
,sl.[CODE_MES1] AS [CODE_MES1]
,sl.[CODE_MES2] AS [CODE_MES2]
,sl.[REAB] AS [REAB]
,(SELECT TOP 1 v021.[IDSPEC] FROM [IES].[T_V021_MED_SPEC] v021 WHERE v021.[CODE_SPEC]=sl.[PRVS] AND v021.[DATEBEG]<=zSL.[DATE_Z_2] AND (v021.[DATEEND] IS NULL OR v021.[DATEEND]>=zSL.[DATE_Z_2])) AS [PRVS3]
,sl.[VERS_SPEC] AS [VERS_SPEC]
,sl.[ED_COL] AS [ED_COL]
,sl.[TARIF] AS [TARIF]
,sl.[SUM_M] AS [SUMV]
,sl.[SUM_M] AS [SUMP]
,sl.[COMENTSL] AS [COMENTSL]
FROM tempdb..TEMP_SLUCH_MTR sl    
JOIN tempdb..TEMP_ZAP_MTR zap ON (zap.[N_ZAP] = sl.[N_ZAP])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

declare @cnt int

select @cnt = count(*)
from
(
	SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType]
	FROM 
	tempdb..TEMP_DS2_MTR  t1
	JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE])
	union
	SELECT NEWID() as [SchetSluchDsID], t1.[DS3] as [MKB], t2.[SchetSluchID] as [SchetSluch],1 as [MKBType]
	FROM 
	tempdb..TEMP_DS3_MTR  t1
	JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE])
)aa

SELECT '[IES].[T_SCHET_SLUCH_DS]' as tableName, ' информации об диагнозах' as operationName, @cnt as tableCount

SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType], null as [DS2_PR], null as [PR_DS2_N]
FROM 
tempdb..TEMP_DS2_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])
union
SELECT NEWID() as [SchetSluchDsID], t1.[DS3] as [MKB], t2.[SchetSluchID] as [SchetSluch],1 as [MKBType], null as [DS2_PR], null as [PR_DS2_N]
FROM 
tempdb..TEMP_DS3_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])
union
SELECT NEWID() as [SchetSluchDsID], t1.[DS2] as [MKB], t2.[SchetSluchID] as [SchetSluch],0 as [MKBType], [DS2_PR] as [DS2_PR], [PR_DS2_N] as [PR_DS2_N]
FROM 
tempdb..TEMP_DS2_N_MTR  t1
JOIN tempdb..TEMP_SLUCH_MTR t2 on (t2.[IDCASE] = t1.[IDCASE] and t1.[SL_ID] = t2.[SL_ID])

SELECT '[IES].[T_SCHET_USL_NAPR]' as tableName, ' направлений' as operationName, (select count(*) from tempdb..TEMP_NAPR_MTR) as tableCount

SELECT 
 NEWID() AS [SchetUslNaprID]
,sl.[SchetSluchID] AS [SchetSluch]
,napr.[NAPR_DATE] AS [NAPR_DATE]
,napr.[NAPR_MO] AS [NAPR_MO]
,napr.[NAPR_V] AS [NAPR_V]
,napr.[MET_ISSL] AS [MET_ISSL]
,napr.[NAPR_USL] AS [NAPR_USL]
FROM tempdb..TEMP_NAPR_MTR napr 
JOIN tempdb..TEMP_SLUCH_MTR sl ON (napr.[IDCASE] = sl.[IDCASE] and napr.[SL_ID] = sl.[SL_ID]) 
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_CONS]' as tableName, ' сведений о проведении консилиума' as operationName, (select count(*) from tempdb..TEMP_CONS_MTR ) as tableCount

SELECT 
 NEWID() AS [SchetSluchConsID]
,sl.[SchetSluchID] AS [SchetSluch]
,(select top 1 N019.[N019OnkConsID] from [IES].[T_N019_ONK_CONS] N019 where N019.[ID_CONS] = cons.[PR_CONS] and N019.[DATEBEG]<=zSL.[DATE_Z_2] and (N019.[DATEEND] is null or N019.[DATEEND]>=zSL.[DATE_Z_2])) AS [N019OnkCons]
,cons.[DT_CONS] AS [DT_CONS]
FROM tempdb..TEMP_CONS_MTR cons
JOIN tempdb..TEMP_SLUCH_MTR sl ON (cons.[IDCASE] = sl.[IDCASE] and cons.[SL_ID] = sl.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK]' as tableName, ' онкологических заболеваний' as operationName, (select count(*) from tempdb..TEMP_ONK_SL_MTR) as tableCount

SELECT 
 onkSL.[SchetSluchOnkID] AS [SchetSluchOnkID]
,sl.[SchetSluchID] AS [SchetSluch]
,(select top 1 N018.[N018OnkReasID] from [IES].[T_N018_ONK_REAS] N018 where n018.[ID_REAS] = onkSL.[DS1_T] and n018.[DATEBEG]<=zSL.[DATE_Z_2] and (n018.[DATEEND] is null or n018.[DATEEND]>=zSL.[DATE_Z_2])) AS [DS1_T]
,(select top 1 N002.[N002StadiumID] from [IES].[T_N002_STADIUM] N002 where N002.[ID_St] = onkSL.[STAD] and N002.[DATEBEG]<=zSL.[DATE_Z_2] and (N002.[DATEEND] is null or N002.[DATEEND]>=zSL.[DATE_Z_2])) AS [STAD]
,(select top 1 N003.[N003TumorID] from [IES].[T_N003_TUMOR] N003 where N003.[ID_T] = onkSL.[ONK_T] and N003.[DATEBEG]<=zSL.[DATE_Z_2] and (N003.[DATEEND] is null or N003.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_T]
,(select top 1 N004.[N004NodusID] from [IES].[T_N004_NODUS] N004 where N004.[ID_N] = onkSL.[ONK_N] and N004.[DATEBEG]<=zSL.[DATE_Z_2] and (N004.[DATEEND] is null or N004.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_N]
,(select top 1 N005.[N005MetastasisID] from [IES].[T_N005_METASTASIS] N005 where N005.[ID_M] = onkSL.[ONK_M] and N005.[DATEBEG]<=zSL.[DATE_Z_2] and (N005.[DATEEND] is null or N005.[DATEEND]>=zSL.[DATE_Z_2])) AS [ONK_M]
,onkSL.[MTSTZ]
,onkSL.[SOD]
,onkSL.[K_FR]
,onkSL.[WEI]
,onkSL.[HEI]
,onkSL.[BSA]
FROM tempdb..TEMP_ONK_SL_MTR onkSL   
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK_DIAG]' as tableName, ' диагностического блока' as operationName, (select count(*) from tempdb..TEMP_B_DIAG_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchOnkDiagID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,diag.[DIAG_TIP] AS [DIAG_TIP]
,diag.[DIAG_CODE] AS [DIAG_CODE]
,diag.[DIAG_RSLT] AS [DIAG_RSLT]
,diag.[REC_RSLT] AS [REC_RSLT]
,diag.[DIAG_DATE] AS [DIAG_DATE]
FROM tempdb..TEMP_B_DIAG_MTR diag 
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (diag.[IDCASE] = onkSL.[IDCASE] and diag.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_ONK_PROT]' as tableName, ' сведения об имеющихся противопоказаниях' as operationName, (select count(*) from tempdb..TEMP_B_PROT_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchOnkProtID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,prot.[D_PROT] AS [D_PROT]
,(select top 1 N001.[N001PrOtID] from [IES].[T_N001_PrOt] N001 where N001.[ID_PrOt] = prot.[PROT] and N001.[DATEBEG]<=zSL.[DATE_Z_2] and (N001.[DATEEND] is null or N001.[DATEEND]>=zSL.[DATE_Z_2])) AS [PROT]
FROM tempdb..TEMP_B_PROT_MTR prot
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (prot.[IDCASE] = onkSL.[IDCASE] and prot.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

SELECT '[IES].[T_SCHET_USL_ONK]' as tableName, ' сведений об услуге при лечении онкологического заболевания' as operationName, (select count(*) from tempdb..TEMP_ONK_USL_MTR) as tableCount

SELECT 
 onkusl.[SchetOnkUslID] AS [SchetUslOnkID]
,onkSL.[SchetSluchOnkID] AS [SchetSluchOnk]
,(select top 1 N013.[N013TreatTypeID] from [IES].[T_N013_TREAT_TYPE] N013 where N013.[ID_TLech] = onkusl.[USL_TIP] and N013.[DATEBEG]<=zSL.[DATE_Z_2] and (N013.[DATEEND] is null or N013.[DATEEND]>=zSL.[DATE_Z_2])) AS [USL_TIP]
,(select top 1 N014.[N014SurgTreatID] from [IES].[T_N014_SURG_TREAT] N014 where N014.[ID_THir] = onkusl.[HIR_TIP] and N014.[DATEBEG]<=zSL.[DATE_Z_2] and (N014.[DATEEND] is null or N014.[DATEEND]>=zSL.[DATE_Z_2])) AS [HIR_TIP]
,(select top 1 N015.[N015DrugTherapyLinesID] from [IES].[T_N015_DRUG_THERAPY_LINES] N015 where N015.[ID_TLek_L] = onkusl.[LEK_TIP_L] and N015.[DATEBEG]<=zSL.[DATE_Z_2] and (N015.[DATEEND] is null or N015.[DATEEND]>=zSL.[DATE_Z_2])) AS [LEK_TIP_L]
,(select top 1 N016.[N016DrugTherapyCyclesID] from [IES].[T_N016_DRUG_THERAPY_CYCLES] N016 where N016.ID_TLek_V = onkusl.[LEK_TIP_V] and N016.[DATEBEG]<=zSL.[DATE_Z_2] and (N016.[DATEEND] is null or N016.[DATEEND]>=zSL.[DATE_Z_2])) AS [LEK_TIP_V]
,(select top 1 N017.[N017RadiationTherapyTypesID] from [IES].[T_N017_RADIATION_THERAPY_TYPES] N017 where N017.[ID_TLuch] = onkusl.[LUCH_TIP] and N017.[DATEBEG]<=zSL.[DATE_Z_2] and (N017.[DATEEND] is null or N017.[DATEEND]>=zSL.[DATE_Z_2])) AS [LUCH_TIP]
,onkusl.[PPTR] AS [PPTR]
FROM tempdb..TEMP_ONK_USL_MTR onkusl
JOIN tempdb..TEMP_ONK_SL_MTR onkSL ON (onkSL.[IDCASE] = onkusl.[IDCASE] and onkSL.[SL_ID] = onkusl.[SL_ID])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = onkSL.[IDCASE] and sl.[SL_ID] = onkSL.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])

select NEWID() as ID, t0.[IDCASE], t0.[SL_ID], t0.[REGNUM], t0.[CODE_SH], Left(t0.rezult,Len(t0.rezult)-1) As rezult 
into #temp_lek_pr
	From
	(
		Select distinct cast(t2.[IDCASE] as varchar(36)) as [IDCASE], cast(t2.[SL_ID] as varchar(36)) as [SL_ID], cast(t2.[REGNUM] as varchar(6)) as [REGNUM], cast(t2.[CODE_SH] as varchar(10)) as [CODE_SH],
			(
				Select cast(t1.[DATE_INJ] as varchar(40)) + ',' AS [text()]
				From tempdb..TEMP_LEK_PR_DATE_INJ_MTR t1
				Where (t1.[IDCASE] = t2.[IDCASE] and t1.[SL_ID] = t2.[SL_ID] and t1.[REGNUM] = t2.[REGNUM] and t1.[CODE_SH] = t2.[CODE_SH])
				ORDER BY t1.[REGNUM], t1.[CODE_SH]
				For XML PATH ('')
			) rezult
		From tempdb..TEMP_LEK_PR_DATE_INJ_MTR t2
	) t0 	

SELECT '[IES].[T_SCHET_USL_ONK_LEK_PR]' as tableName, ' сведений о лекарственных средствах' as operationName, (select count(*) from tempdb..TEMP_LEK_PR_DATE_INJ_MTR) as tableCount

SELECT 
 ID AS [SchetUslOnkLekPrID]
--,lekpr.[rezult] AS [DATE_INJ]
,aa.[SchetOnkUslID] AS [SchetUslOnk]
,(select top 1 N020.[N020OnkLekpID] from [IES].[T_N020_ONK_LEKP] N020 where lekpr.[REGNUM] = N020.[ID_LEKP] and N020.[DATEBEG]<=zSL.[DATE_Z_2] and (N020.[DATEEND] is null or N020.[DATEEND]>=zSL.[DATE_Z_2])) AS [REGNUM]
,(select top 1 v024.[V024DopKrID] from [IES].[T_V024_DOP_KR] v024 where lekpr.[CODE_SH] = v024.[IDDKK] and v024.[DATEBEG]<=zSL.[DATE_Z_2] and (v024.[DATEEND] is null or v024.[DATEEND]>=zSL.[DATE_Z_2])) AS [V024DopKr]
,lekpr.[REGNUM] AS [N020_R]
FROM #temp_lek_pr lekpr
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = lekpr.[IDCASE] and sl.[SL_ID] = lekpr.[SL_ID])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (sl.[IDCASE] = zSL.[IDCASE])
cross apply (select top 1 * from tempdb..TEMP_ONK_USL_MTR onkusl where lekpr.[IDCASE] = onkusl.[IDCASE] and lekpr.[SL_ID] = onkusl.[SL_ID] and (onkusl.[USL_TIP] = 2 or onkusl.[USL_TIP] = 4)) aa

SELECT '[IES].[T_SCHET_USL_ONK_LEK_PR_DATE]' as tableName, ' сведений о датах лекарственных средств' as operationName, (select count(*) from tempdb..TEMP_LEK_PR_DATE_INJ_MTR) as tableCount

SELECT 
 NEWID() AS [SchetUslOnkLekPrDateID]
,lekpr.ID AS [SchetUslOnkLekPr]
,dateLek.[DATE_INJ] AS [DATE_INJ]
FROM tempdb..TEMP_LEK_PR_DATE_INJ_MTR dateLek
JOIN #temp_lek_pr lekpr ON (dateLek.[IDCASE] = lekpr.[IDCASE] and dateLek.[SL_ID] = lekpr.[SL_ID] and dateLek.[REGNUM] = lekpr.[REGNUM] and dateLek.[CODE_SH] = lekpr.[CODE_SH])

SELECT '[IES].[T_KSG]' as tableName, ' информации о КСГ в случаях' as operationName, (select count(*) from tempdb..TEMP_KSG_KPG_MTR) as tableCount

SELECT 
 ksg.[KsgID] AS [KsgID]
,sl.[SchetSluchID] as [SchetSluch]
,ksg.[N_KSG] AS [N_KSG]
,ksg.[VER_KSG] AS [VER_KSG]
,ksg.[KSG_PG] AS [KSG_PG]
,(select top 1 v026.[V026KpgID] from IES.T_V026_KPG v026 where v026.[K_KPG] = ksg.[N_KPG] and v026.[DATEBEG]<=zSL.[DATE_Z_2] and (v026.[DATEEND] is null or v026.[DATEEND]>=zSL.[DATE_Z_2])) AS [N_KPG]
,ksg.[KOEF_Z] AS [KOEF_Z]
,ksg.[KOEF_UP] AS [KOEF_UP]
,ksg.[BZTSZ] AS [BZTSZ]
,ksg.[KOEF_D] AS [KOEF_D]
,ksg.[KOEF_U] AS [KOEF_U]
,ksg.[SL_K] AS [SL_K]
,ksg.[IT_SL] AS [IT_SL]
FROM tempdb..TEMP_KSG_KPG_MTR ksg 
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and ksg.[IDCASE] = sl.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_KSG_CRIT]' as tableName, ' классификационного критерия CRIT(V024)' as operationName, (select count(*) from tempdb..TEMP_CRIT_MTR) as tableCount

SELECT 
 NEWID() AS [KsgCritID]
,ksg.[KsgID] AS [Ksg]
,(select top 1 v024.[V024DopKrID] from [IES].[T_V024_DOP_KR] v024 where v024.[IDDKK] = crit.[CRIT] and v024.[DATEBEG]<=zSL.[DATE_Z_2] and (v024.[DATEEND] is null or v024.[DATEEND]>=zSL.[DATE_Z_2])) AS [V024DopKr]
FROM tempdb..TEMP_CRIT_MTR crit
JOIN tempdb..TEMP_KSG_KPG_MTR ksg ON (ksg.[SL_ID] = crit.[SL_ID] and ksg.[IDCASE] = crit.[IDCASE])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and sl.[IDCASE] = ksg.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_KSLP]' as tableName, ' информации о КСЛП в случаях' as operationName, (select count(*) from tempdb..TEMP_SL_KOEF_MTR ) as tableCount

SELECT 
 NEWID() AS [KslpID]
,koef.[Z_SL] AS [KOEF]
,koef.[IDSL] AS [KOEF_TYPE]
,ksg.[KsgID] AS [Ksg]
--,sl.[SchetSluchID] AS [OmsSchetSluch]
FROM tempdb..TEMP_SL_KOEF_MTR koef
JOIN tempdb..TEMP_KSG_KPG_MTR ksg ON (ksg.[SL_ID] = koef.[SL_ID] and ksg.[IDCASE] = koef.[IDCASE])
JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[SL_ID] = ksg.[SL_ID] and sl.[IDCASE] = ksg.[IDCASE])

SELECT '[IES].[T_SCHET_USL]' as tableName, ' информации об услугах' as operationName, (select count(*) from tempdb..TEMP_USL_MTR) as tableCount

SELECT 
 usl.[SchetUslID] AS [SchetUslID]
,507 AS [type_]
,sl.[SchetSluchID] AS [SchetSluch]
,usl.[IDSERV] AS [IDSERV]
,usl.[LPU] AS [LPU]
,usl.[PROFIL] AS [PROFIL]
,usl.[VID_VME] AS [VID_VME]
,usl.[DET] AS [DET]
,usl.[DATE_IN] AS [DATE_IN]
,usl.[DATE_OUT] AS [DATE_OUT]
,usl.[DS] AS [DS]
,usl.[CODE_USL] AS [CODE_USL]
,usl.[USL] AS [USL]
,usl.[KOL_USL] AS [KOL_USL]
,usl.[TARIF] AS [TARIF]
,usl.[SUMV_USL] AS [SUMV_USL]
,(SELECT TOP 1 v021.[IDSPEC] FROM [IES].[T_V021_MED_SPEC] v021 WHERE v021.[CODE_SPEC]=usl.[PRVS] AND v021.[DATEBEG]<=zSL.[DATE_Z_2] AND (v021.[DATEEND] IS NULL OR v021.[DATEEND]>=zSL.[DATE_Z_2])) AS [PRVS3]
,usl.[COMENTU] AS [COMENTU]
FROM tempdb..TEMP_USL_MTR usl
JOIN tempdb..TEMP_SLUCH_MTR sl ON (usl.[SL_ID] = sl.[SL_ID] and usl.[IDCASE] = sl.[IDCASE])
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sl.[IDCASE])

SELECT '[IES].[T_SCHET_SLUCH_SANK]' as tableName, ' информации об удержаниях' as operationName, (select count(*) from tempdb..TEMP_SANK_MTR) as tableCount

SELECT 
 ISNULL(sankSL.[SchetSluchSankSLID], sank.SchetSluchSankID) AS [SchetSluchSankID]
,zSL.[SchetSluchAccomplishedID] AS [SchetSluchAccomplished]
,sank.[S_CODE] AS [S_CODE]
,sank.[S_SUM] AS [S_SUM]
,sank.[S_TIP] AS [S_TIP]
,(select top 1 f014.[F014DenyReasonID] from [IES].[T_F014_DENY_REASON] f014 where f014.[kod] = sank.[S_OSN] and f014.DATEBEG<=isnull(sank.DATE_ACT,zSL.[DATE_Z_2]) and (f014.[DATEEND] is null or f014.[DATEEND] >= isnull(sank.DATE_ACT,zSL.[DATE_Z_2]))) AS [S_OSN]
,sank.[S_COM] AS [S_COM]
,sank.[S_IST] AS [S_IST]
,sank.[DATE_ACT] AS [DATE_ACT]
,sank.[NUM_ACT] AS [NUM_ACT]
,sl.SchetSluchID AS [SchetSluch]
FROM tempdb..TEMP_SANK_MTR sank
JOIN tempdb..TEMP_Z_SLUCH_MTR zSL ON (zSL.[IDCASE] = sank.[IDCASE])
LEFT JOIN tempdb..TEMP_SANK_SL_ID_MTR sankSL ON (sank.[S_CODE] = sankSL.[S_CODE] and sank.[IDCASE] = sankSL.[IDCASE])
LEFT JOIN tempdb..TEMP_SLUCH_MTR sl ON (sl.[IDCASE] = sank.[IDCASE] and sankSL.[SL_ID] = sl.[SL_ID])

SELECT '[IES].[T_SCHET_SLUCH_SANK_EXP]' as tableName, ' информации об экспертах' as operationName, (select count(*) from tempdb..TEMP_CODE_EXP_MTR) as tableCount

SELECT 
 NEWID() AS [SchetSluchSankExpID]
,ISNULL(sankSL.[SchetSluchSankSLID], sank.SchetSluchSankID) AS [SchetSluchSank]
,codeexp.[CODE_EXP] AS [F004Expert]
FROM tempdb..TEMP_CODE_EXP_MTR codeexp  
JOIN tempdb..TEMP_SANK_MTR sank ON (sank.[IDCASE] = codeexp.[IDCASE] and sank.[S_CODE] = codeexp.[S_CODE])
LEFT JOIN tempdb..TEMP_SANK_SL_ID_MTR sankSL ON (sank.[S_CODE] = sankSL.[S_CODE] and sank.[IDCASE] = sankSL.[IDCASE])

DROP TABLE #temp_dost   
DROP TABLE #temp_dost_p
DROP TABLE #temp_lek_pr
DROP TABLE #Errors

END

DROP TABLE tempdb..TEMP_SCHET_MTR
DROP TABLE tempdb..TEMP_ZGLV_MTR
DROP TABLE tempdb..TEMP_ZAP_MTR
DROP TABLE tempdb..TEMP_PACIENT_MTR
DROP TABLE tempdb..TEMP_Z_SLUCH_MTR
DROP TABLE tempdb..TEMP_SLUCH_MTR
DROP TABLE tempdb..TEMP_USL_MTR 
DROP TABLE tempdb..TEMP_SANK_MTR
DROP TABLE tempdb..TEMP_SANK_SL_ID_MTR
DROP TABLE tempdb..TEMP_CODE_EXP_MTR
DROP TABLE tempdb..TEMP_KSG_KPG_MTR
DROP TABLE tempdb..TEMP_CRIT_MTR
DROP TABLE tempdb..TEMP_SL_KOEF_MTR 
DROP TABLE tempdb..TEMP_DS2_N_MTR 
DROP TABLE tempdb..TEMP_DS2_MTR 
DROP TABLE tempdb..TEMP_DS3_MTR 
DROP TABLE tempdb..TEMP_CONS_MTR 
DROP TABLE tempdb..TEMP_NAPR_MTR
DROP TABLE tempdb..TEMP_ONK_SL_MTR 
DROP TABLE tempdb..TEMP_B_DIAG_MTR 
DROP TABLE tempdb..TEMP_B_PROT_MTR
DROP TABLE tempdb..TEMP_ONK_USL_MTR
DROP TABLE tempdb..TEMP_LEK_PR_DATE_INJ_MTR

END
END