if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_infoListLoad_karelia]') and xtype = N'P')
drop procedure [dbo].[sp_infoListLoad_karelia]
GO
CREATE procedure [dbo].[sp_infoListLoad_karelia] 
@FileID uniqueidentifier
,@x xml
,@Worker uniqueidentifier 
,@PackageType varchar
AS 
BEGIN 

declare @_isMobBrigade bit = 
    case 
        when @PackageType = 'DISP_DIRECT_INFO_MB' then 1 
        else 0 
    end;

--загрузка только через дирректории!!!!!!!!!!!!!!!!!!!!!!!!!!
SELECT 
   NewId() as [ItemID]
  ,T.c.value('./VERSION[1]', 'varchar(5)') AS [VERSION]
  ,T.c.value('./DATA[1]', 'date') AS [DATA]
  ,T.c.value('./FILENAME[1]', 'varchar(26)') AS [FILENAME]
  ,T.c.value('./YEAR[1]', 'int') AS [YEAR]
  ,T.c.value('./SMO[1]', 'varchar(5)') AS [SMO]
  ,T.c.value('./ZAP[1]', 'int') AS [ZAP]
INTO #ZGLV
FROM @x.nodes('/PERS_LIST/ZGLV') T(c)   

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('./ID_PAC[1]', 'varchar(36)') AS [ID_PAC]
 ,T.c.value('./FAM[1]', 'varchar(30)') AS [FAM]
 ,T.c.value('./IM[1]', 'varchar(30)') AS [IM]
 ,T.c.value('./OT[1]', 'varchar(30)') AS [OT]
 ,T.c.value('./W[1]', 'int') AS [W]
 ,T.c.value('./DR[1]', 'datetime') AS [DR]
 ,T.c.value('./VPOLIS[1]', 'int') AS [VPOLIS]
 ,T.c.value('./SPOLIS[1]', 'varchar(30)') AS [SPOLIS]
 ,T.c.value('./NPOLIS[1]', 'varchar(30)') AS [NPOLIS]
 ,T.c.value('./PHONE1[1]', 'varchar(30)') AS [PHONE1]
 ,T.c.value('./PHONE2[1]', 'varchar(30)') AS [PHONE2]
 ,T.c.value('./QUESTION_DATE[1]', 'date') AS [QUESTION_DATE]
 ,T.c.value('./REJECT_DATE[1]', 'date') AS [REJECT_DATE]
 ,T.c.value('./REJECT_REASON[1]', 'int') AS [REJECT_REASON]
 ,T.c.value('./InsuredID[1]', 'varchar(36)') AS [InsuredID] -- заглушка
 ,T.c.value('./InsuredPolicyRecordID[1]', 'varchar(36)') AS [InsuredPolicyRecordID] -- заглушка
 ,T.c.value('./ActualInsuredPolicyRecordID[1]', 'varchar(36)') AS [ActualInsuredPolicyRecordID] -- заглушка
 ,T.c.value('../ZGLV[1]/DATA[1]', 'datetime') AS [DATA]
 ,T.c.value('../ZGLV[1]/SMO[1]', 'varchar(5)') AS [SMO]
 ,T.c.value('../ZGLV[1]/YEAR[1]', 'int') AS [YEAR]
INTO #PERS
FROM @x.nodes('/PERS_LIST/PERS') T(c)

 SELECT 
 NewId() as [ItemID]
 ,T.c.value('../../ID_PAC[1]', 'varchar(36)') AS ID_PAC
 ,T.c.value('../../REJECT_DATE[1]', 'date') AS [REJECT_DATE]
 ,T.c.value('../../REJECT_REASON[1]', 'int') AS [REJECT_REASON]
 ,T.c.value('../../NPOLIS[1]', 'varchar(30)') AS [NPOLIS] --связка с DISP_DIRECT
 ,T.c.value('./DispDirectId[1]', 'varchar(36)') AS [DispDirectId] -- заглушка
 ,T.c.value('./INFO_FORM[1]', 'int') AS [INFO_FORM]
 ,T.c.value('./INFO_DATE[1]', 'date') AS [INFO_DATE]
 ,T.c.value('./INFO_TYPE[1]', 'int') AS [INFO_TYPE]
 ,T.c.value('./INFO_ABOUT_WHAT[1]', 'varchar(3)') AS [INFO_ABOUT_WHAT]
INTO #INFO_NOTES
FROM @x.nodes('/PERS_LIST/PERS/INFO_LIST/INFO_NOTE') T(c)

--Ищем полис застрахованного

--по идее тут уже должен найти
update t1
set t1.[InsuredID] = (
  select top 1 InsuredID 
  from [ISZL].T_INSURED t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR)   
 )
from #PERS t1
where t1.[InsuredID] is null 

update t1
set t1.[InsuredID] = (
  select top 1 InsuredID 
  from [ISZL].T_INSURED t2
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR)   
 )
from #PERS t1
where t1.[InsuredID] is null 

update t1
set t1.[InsuredID] = (
  select top 1 InsuredID 
  from [ISZL].T_INSURED t2
  where (t2.LastName = t1.FAM and t2.Patronymic = t1.OT  and t2.Birthday = t1.DR)   
 )
from #PERS t1
where t1.[InsuredID] is null 

update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.[InsuredID] is null 

update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.[InsuredID] is null 

update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3 and t1.[InsuredID] is null 

--0. Ищем полис по номеру полиса. Не важно актуальный он или нет
--step 0.
update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and t2.Patronymic = t1.OT and t2.Birthday = t1.DR and p.Number = t1.NPOLIS)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null  and t1.[InsuredID] is null 

update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3 and t1.[InsuredID] is null 

--step 1. без отчества
update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM  and t2.Birthday = t1.DR and p.Number = t1.NPOLIS)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.[InsuredID] is null 
 
update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3 and t1.[InsuredID] is null 

--step 2. без отчества и ДР
update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
  where (t2.LastName = t1.FAM and t2.FirstName = t1.IM and p.Number = t1.NPOLIS)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.[InsuredID] is null 

update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
	join ISZL.T_INSURED ins on ins.InsuredID = t2.Insured
  where (t2.FirstName = t1.IM and t2.Birthday = t1.DR and (p.Number = t1.NPOLIS or ins.ENP = t1.NPOLIS))   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null and t1.VPOLIS = 3 and t1.[InsuredID] is null 
 
--step 3. без отчества и Фамилии
update t1
set t1.[InsuredPolicyRecordID] = (
  select top 1 InsuredPolicyRecordID 
  from [ISZL].T_INSURED_POLICY_RECORD t2
	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
  where (t2.FirstName = t1.IM and t2.Birthday = t1.DR and p.Number = t1.NPOLIS)   
 )
from #PERS t1
where t1.[InsuredPolicyRecordID] is null  and t1.[InsuredID] is null 

--Ищем актуальный полис застрахованного 
--update t1
--set t1.[ActualInsuredPolicyRecordID] = (
--  select top 1 InsuredPolicyRecordID 
--  from [ISZL].T_INSURED_POLICY_RECORD t2
--	join [ISZL].T_POLICY p on p.PolicyID = t2.[Policy]
--	--join [ISZL].T_F008_OMS_TYPE f008 on f008.IDDOC = p.OmsType
--  where t2.Insured = t3.Insured and t2.DateBegin <= t1.[DATA] and (not(t2.DateStop is not null and t2.DateStop <= t1.[DATA]))
--  order by t2.DateBegin, p.OmsType
-- )
--from #PERS t1
--join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = t1.[InsuredPolicyRecordID]

--нужное с disp_direct 
;with dispdircte as
(
	select 
	 dispdir.DispDirectID as DispDirectID
	,dispdir.Insured as Insured
	,dispdir.InsuredPolicyRecord as InsuredPolicyRecord
	,dispdir.YEAR as Year
	,dispt.IDDT as IDDT
	from ISZL.T_DISP_DIRECT dispdir
	join ISZL.T_V016_DISPT dispt on dispt.V016ID = dispdir.V016Dispt
	where dispdir.type_ = (select top 1 e.EntityID from VCLib.T_ENTITIES e where e.EntName = 'DISP_DIRECT')
)
update inf
set inf.DispDirectId = 
(
	select top 1 DispDirectID 
	from dispdircte dispdir
	where t1.YEAR = dispdir.Year and (t1.InsuredPolicyRecordID = dispdir.InsuredPolicyRecord or t1.InsuredID = dispdir.Insured) and inf.INFO_ABOUT_WHAT = dispdir.IDDT
)
from #INFO_NOTES inf 
join #PERS t1 on inf.NPOLIS = t1.NPOLIS

create table #Errors
(
	CODE int,
	COMMENT varchar(250)
)

--insert into #Errors
--select 1, t1.ID_PAC, t1.NPOLIS, 'Застрахованный умер'
--from #PERS t1
--join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = t1.[ActualInsuredPolicyRecordID]
--join [ISZL].T_INSURED t3 on t3.InsuredID = t2.Insured
--where t3.DateDeath is not null and t1.DATA>t3.DateDeath

--insert into #Errors
--select 2, t1.ID_PAC, NPOLIS, 'Застрахован другой СМО РК'
--from #PERS t1
--join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = t1.[ActualInsuredPolicyRecordID]
--join iszl.T_ORGANIZATION t3 on t3.OrganizationID = t2.Smo
--join iszl.T_F002_SMO t4 on t4.SMOCOD = t3.Code
--where t3.Code <> t1.SMO and t4.TF_OKATO = 86000

--insert into #Errors
--select 3, t1.ID_PAC, NPOLIS, 'Застрахован вне территории РК'
--from #PERS t1
--join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = t1.[ActualInsuredPolicyRecordID]
--join iszl.T_ORGANIZATION t3 on t3.OrganizationID = t2.Smo
--join iszl.T_F002_SMO t4 on t4.SMOCOD = t3.Code
--where t4.TF_OKATO <> 86000

--insert into #Errors
--select 4, t1.ID_PAC, NPOLIS, 'Выдача ВС в другой СМО'
--from #PERS t1

--insert into #Errors
--select 5, t1.ID_PAC, NPOLIS, 'Полис закрыт в связи с выявлением дубликата'
--from #PERS t1

--insert into #Errors
--select 6, t1.ID_PAC, NPOLIS, 'Прочие причины закрытия полиса (в том числе невозможность определить причину закрытия)'
--from #PERS t1
--join [ISZL].T_INSURED_POLICY_RECORD t2 on t2.[InsuredPolicyRecordID] = t1.[InsuredPolicyRecordID]
--where t2.DateStop > t1.[DATA] and t1.[ActualInsuredPolicyRecordID] is null

insert into #Errors
select 7,  'Застрахованный не найден ни по ФИО и ДР, ни по номеру и серии полиса. ID_PAC ('+isnull(t1.ID_PAC,'')+'). NPOLIS ('+isnull(NPOLIS,'')+')'
from #PERS t1
where t1.InsuredPolicyRecordID is null  and t1.[InsuredID] is null

--;with cte as(
--	select
--		ROW_NUMBER() over (partition by inf.ItemID order by inf.ItemID) as rn
--		,isnull(pers.InsuredID,t3.Insured) as insuredid
--		,pers.ID_PAC
--		,pers.NPOLIS
--	from #PERS pers
--	left join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = pers.[InsuredPolicyRecordID] 
--	join #INFO_NOTES inf on inf.NPOLIS = pers.NPOLIS
--)
--insert into #Errors
--select distinct 7, 'Неверный формат входных данных! Дубль ЗЛ в xml:' + isnull(ins.LastName,'') + ' ' + isnull(ins.FirstName,'') + ' ' +isnull(ins.Patronymic,'') + '. ID_PAC ('+isnull(cte.ID_PAC,'')+'). NPOLIS ('+isnull(cte.NPOLIS,'')+')'
--from cte
--join ISZL.T_INSURED ins on ins.InsuredID = cte.insuredid
--where cte.rn > 1

--insert into #Errors
--select 9, t1.ID_PAC, NPOLIS, 'Полис застрахованного не действителен'
--from #PERS t1
--where t1.[ActualInsuredPolicyRecordID] is not null and t1.[InsuredPolicyRecordID] is not null and t1.[ActualInsuredPolicyRecordID] <> t1.[InsuredPolicyRecordID]

--insert into #Errors
--select 10, t2.ID_PAC, t2.NPOLIS, 'Не было найдено соответствующего профмероприятия'
--from #INFO_NOTES t1
--join #PERS t2 on t1.NPOLIS = t2.NPOLIS
--where t1.DispDirectId is null  and t2.InsuredPolicyRecordID is not null 

--Карелия 3:54 PM НАЗАРОВ В СКАЙПЕ ПОПРОСИЛ УБРАТЬ
--Привет. в формате файла по информированию профов обнаружили косяк.  надо убрать ФЛК обязательности заполнения полей REJECT_REASON и REJECT_DATE при INFO_FORM=3 или INFO_FORM=6
--insert into #Errors
--select 7, t1.ID_PAC, NPOLIS, 'Поля REJECT_REASON и REJECT_DATE обязательны к заполнению при INFO_FORM = 3 или INFO_FORM = 6'
--from #INFO_NOTES t1
--where (t1.INFO_FORM = 3 or t1.INFO_FORM = 6)  and (t1.REJECT_REASON is null or t1.REJECT_DATE is null)

--новая проверка
insert into #Errors (CODE, COMMENT)
select 4, 'Уже существует информирование с указанными параметрами. NPOLIS(' + t1.NPOLIS + ')'
from #ZGLV z, #PERS t1
join #INFO_NOTES inn on inn.NPOLIS = t1.NPOLIS
left join iszl.T_ORGANIZATION orgsmo on orgsmo.Code = t1.SMO
join iszl.T_DISP_DIRECT_INFO ddi on ddi.Insured = cast(t1.InsuredID as uniqueidentifier)
where
	ddi.V016Dispt = (select top 1 dispt.V016ID from ISZL.T_V016_DISPT dispt where dispt.IDDT = inn.INFO_ABOUT_WHAT) and
	ddi.TIP_INFO = (case
		    when inn.INFO_ABOUT_WHAT = 'ДВ4' then 
			                                     case 
												 when inn.INFO_TYPE = 1 then 1
												 when inn.INFO_TYPE = 2 then 2
												 end
   		    when inn.INFO_ABOUT_WHAT = 'ДВ1' then 
			                                     case 
												 when inn.INFO_TYPE = 1 then 1
												 when inn.INFO_TYPE = 2 then 2
												 end 
		    when inn.INFO_ABOUT_WHAT = 'УД1' then 
			                                     case 
												 when inn.INFO_TYPE = 1 then 1
												 when inn.INFO_TYPE = 2 then 2
												 end        
			when inn.INFO_ABOUT_WHAT = 'ДВ2' then 3
		    when inn.INFO_ABOUT_WHAT = 'ОПВ' then 4
			else inn.INFO_ABOUT_WHAT
		            end) and
	ddi.Smo = orgsmo.OrganizationID and
	DATEPART(year,ddi.[Date]) = z.YEAR and
	cast(cast(cast(ddi.[Date] as float) as int) as datetime) = cast(inn.INFO_DATE as datetime) and
    t1.InsuredID is not null and ddi.Insured is not null

insert into #Errors (CODE, COMMENT)
select 5, 'Найдены дубликаты записей в XML с NPOLIS(' + pers.NPOLIS + ') в количестве (' + cast(count(*) as varchar) + ')'
from #PERS pers
where not exists (
	select *
	from #INFO_NOTES info
	where info.NPOLIS = pers.NPOLIS
		and info.INFO_ABOUT_WHAT = 'УД1'
) group by pers.NPOLIS
having count(*) > 1

-- сохранение
if ((select count(*) from #Errors) > 0)
begin
	select cast(0 as bit),
	'<?xml version="1.0" encoding="utf-8"?>
	' + (
	select 
		CODE AS 'CODE',
		COMMENT AS 'COMMENT'
	FROM #Errors
	FOR XML PATH('ERR'),
	ROOT('ROOT')
	)
end
else
begin

	--------------------
	--Сheck File Exist--
	--------------------
	declare @ExistFile uniqueidentifier
	set @ExistFile = (
				select top 1 ddif.[FileID] 
				from [ISZL].T_DISP_DIRECT_INFO_FILE ddif 
					join  [VCLib].T_FILE f on f.[FileID] = ddif.[FileID]
				where f.[Name] = (select  z.[FILENAME] from #ZGLV z)
			)

	if @ExistFile is not null
	begin 
		exec [dbo].sp_DispInfoDel @ExistFile
	end

	insert into VCLib.T_FILE ([FileID],[Name],[CreateDateTime],[FileNameForm],[SaveFileLocation],[UseOle],[type_])
	select @FileID, [FILENAME],GetDate(), 1, 1, 0, (select EntityID from VCLib.T_ENTITIES where EntName = 'DISP_DIRECT_INFO_FILE') 
	from #ZGLV 

	insert into [ISZL].[T_DISP_DIRECT_INFO_FILE] ([FileID],[VERSION],ReceivedDate,ReceivedTime,Worker)
	select @FileID, [VERSION],GetDate(),GetDate(),@Worker
	from #ZGLV 


	insert into ISZL.T_DISP_DIRECT_INFO
	(
		 [DispDirectInfoID]
		,Mo
		,[Date]
		,[DispDirect]
		,[DispDirectInfoType]
		,[V016Dispt]
		,[TIP_INFO]
		,[Insured]
		,Smo
		,[DispDirectInfoFile]
		,CreationDate
		,REJECT_DATE
		,REJECT_REASON
		,IsMobileFlag
	)
	select
		-- inf.ItemID
		NewId()
		--,(select org.Code from ISZL.T_MO_ATTACHMENT attachment
		--  join ISZL.T_ORGANIZATION org on org.OrganizationID = attachment.Mo
		--  where attachment.AttachDate <= getdate() 
		--  and attachment.Status = 1
		--  and not exists
		--  (
		--	select * from ISZL.T_MO_ATTACHMENT t1
		--	where t1.AttachDate < getdate()
		--	and t1.AttachDate >= attachment.AttachDate
		--	and attachment.Status = 2
		--  )
		-- )--фигня какая-то
		,null
		,inf.INFO_DATE
		,inf.DispDirectId
		,(select top 1 inftype.Code from ISZL.T_DISP_DIRECT_INFO_TYPE inftype where inftype.Code = inf.INFO_FORM)
		,(select top 1 dispt.V016ID from ISZL.T_V016_DISPT dispt where dispt.IDDT = inf.INFO_ABOUT_WHAT)
		,case
		    when inf.INFO_ABOUT_WHAT = 'ДВ4' then 
			                                     case 
												 when inf.INFO_TYPE = 1 then 1
												 when inf.INFO_TYPE = 2 then 2
												 end
   		    when inf.INFO_ABOUT_WHAT = 'ДВ1' then 
			                                     case 
												 when inf.INFO_TYPE = 1 then 1
												 when inf.INFO_TYPE = 2 then 2
												 end 
		    when inf.INFO_ABOUT_WHAT = 'УД1' then 
			                                     case 
												 when inf.INFO_TYPE = 1 then 1
												 when inf.INFO_TYPE = 2 then 2
												 end        
			when inf.INFO_ABOUT_WHAT = 'ДВ2' then 3
		    when inf.INFO_ABOUT_WHAT = 'ОПВ' then 4
			else inf.INFO_ABOUT_WHAT
		            end
		,isnull(pers.InsuredID,t3.Insured)
		,(select top 1 OrganizationID from iszl.T_ORGANIZATION where Code =  pers.SMO)
		,@FileID
		,getdate()
		,inf.REJECT_DATE
		,inf.REJECT_REASON
		,@_isMobBrigade
	from #PERS pers
	left join [ISZL].T_INSURED_POLICY_RECORD t3 on t3.[InsuredPolicyRecordID] = pers.[InsuredPolicyRecordID] 
	join #INFO_NOTES inf on inf.NPOLIS = pers.NPOLIS
-----------------
end

DROP TABLE #INFO_NOTES
DROP TABLE #PERS
DROP TABLE #ZGLV
END
