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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetFlkFiltersBulkMtrSchet538_q015]
 @type int
AS 
BEGIN

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

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)
)

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

--001F.00.0191 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.0191', 'Значение не найдено в справочнике F032'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_F032_TRMO t2 with(nolock) on t2.mcod = t0.NPR_MO and t2.D_BEGIN<=t0.DATE_Z_2 and (t2.D_END is null or t2.D_END>=t0.DATE_Z_2)
where
@q019type in ('C', 'H', 'R', 'T')
and t0.NPR_MO is not null
and t2.mcod is null
and DATE_Z_2 >= convert(date,'01.11.2022',104)
--001F.00.0191 end

--001F.00.0201 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.0201', 'Значение не найдено в справочнике F032'
from tempdb..TEMP_Z_SLUCH_MTR t0 with(nolock)
left join ies.T_F032_TRMO t2 with(nolock) on t2.mcod = t0.LPU and t2.D_BEGIN<=t0.DATE_Z_2 and (t2.D_END is null or t2.D_END>=t0.DATE_Z_2)
where
@q019type in ('C', 'H', 'R', 'T', 'X')
and t0.LPU is not null
and t2.mcod is null
and DATE_Z_2 >= convert(date,'01.11.2022',104)
--001F.00.0201 end

--001F.00.0361 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, 'NAZ', t1.N_ZAP, t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0361', 'Значение не найдено в справочнике F032'
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_F032_TRMO t2 with(nolock) on t2.mcod = t0.NAPR_MO and t2.D_BEGIN<=t1.DATE_Z_2 and (t2.D_END is null or t2.D_END>=t1.DATE_Z_2)
where
@q019type in ('X')
and t0.NAPR_MO is not null
and t2.mcod is null
and DATE_Z_2 >= convert(date,'01.11.2022',104)
--001F.00.0361 end

--001F.00.0371 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.0371', 'Значение не найдено в справочнике F032'
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_F032_TRMO t2 with(nolock) on t2.mcod = t0.NAPR_MO and t2.D_BEGIN<=t1.DATE_Z_2 and (t2.D_END is null or t2.D_END>=t1.DATE_Z_2)
where
@q019type in ('C', 'R', 'T')
and t0.NAPR_MO is not null
and t2.mcod is null

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

--001F.00.0621 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.0621', 'Значение не найдено в справочнике F032'
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_F032_TRMO t2 with(nolock) on t2.mcod = t0.LPU and t2.D_BEGIN<=t1.DATE_Z_2 and (t2.D_END is null or t2.D_END>=t1.DATE_Z_2)
where
@q019type in ('C', 'H', 'R', 'T', 'X')
and t0.LPU is not null
and t2.mcod is null
and DATE_Z_2 >= convert(date,'01.11.2022',104)
--001F.00.0621 end

--001F.00.0691 begin
insert into #Errors (IM_POL, ZN_POL, NSCHET, BAS_EL, N_ZAP, IDCASE, SL_ID, IDSERV, OSHIB, COMMENT)
select 'CODE_EXP', convert(varchar(3), t3.CODE_EXP) as ZN_POL ,@NSCHET, 'SANK', t1.N_ZAP,  t0.IDCASE, t0.SL_ID, null as IDSERV, '001F.00.0691'
, 'Значение не найдено в справочнике F042'
from tempdb..TEMP_SANK_MTR t0 with(nolock)
join tempdb..TEMP_CODE_EXP_MTR t3 with(nolock) on t0.N_ZAP=t3.N_ZAP and t0.IDCASE=t3.IDCASE and t0.S_CODE=t3.S_CODE
join tempdb..TEMP_Z_SLUCH_MTR t1 with(nolock) on t0.IDCASE = t1.IDCASE
left join ies.T_F042_TREKMP t2 with(nolock) on t2.UIDEKMP = t3.CODE_EXP
where
@q019type in ('C', 'H', 'R', 'T', 'X')
and t3.CODE_EXP is not null
and t2.UIDEKMP is null
and DATE_Z_2 >= convert(date,'01.11.2022',104)
--001F.00.0691 end

--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_KSG_KPG_MTR t3 on t0.IDCASE = t3.IDCASE and t0.SL_ID = t3.SL_ID
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) 
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]))(19|[23][0-9])((0{0,1}[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 DATE_2 >= convert(date,'01.11.2019',104)
and DATE_2 <= convert(date,'31.03.2020',104)
	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
					)
				)
			)
		)
--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 
	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
END

END