IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.sp_CreateVZNServiceMessage') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.sp_CreateVZNServiceMessage
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Создания пакета в сервис ВЗН
-- =============================================
CREATE PROCEDURE dbo.sp_CreateVZNServiceMessage
    @MessageType varchar(100),
    @ObjId uniqueidentifier,
	@Snils varchar(14),
	@oid varchar(max),
	@DopId uniqueidentifier = null
AS
BEGIN
	declare @searchPreviousMessage bit 
	set @searchPreviousMessage = 0
declare @xml xml
 if (@MessageType = 'patient.create')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
				Replace(Replace(nosology.SNILS, '-',''), ' ', '')		as 'snils',	
				nosology.FirstName										as 'firstName',								
				nosology.LastName										as 'lastName',								
				nosology.FatherName										as 'patronymic',
				eSex.Code												as 'gender',								
				convert(varchar(10), nosology.Birthday, 20)				as 'birthDate',								
				eh.IdEHR												as 'citizenShipId/@id',
				nosology.BirthdayLastName								as 'birthLastName',
				nosology.WorkPlace										as 'workPlace',		
				nosology.BirthPlace										as 'birthPlace',
				--nosology.Adress										as 'address',
				--nosology.FiasAddress									as 'address/aoidArea',
				--nosology.FiasStreet									as 'address/aoidStreet', 
				--nosology.FiasHome										as 'address/houseid', 
				--nosology.City											as 'address/areaName', 
				--nosology.CityPrefix									as 'address/prefixArea',
				--nosology.Street										as 'address/streetName',
				--nosology.StreetPrefix									as 'address/prefixStreet',
				--nosology.House										as 'address/house', 
				Replace(Replace(nosology.DocSeries, '-',''), ' ', '')	as 'documents/document/serial',
				nosology.DocNumber										as 'documents/document/number',
				nosology.DocIssueOrg									as 'documents/document/passOrg', 
				ehr.IdEhr												as 'documents/document/documentId/@id',
				convert(varchar(10), nosology.DocIssueDate, 20)			as 'documents/document/passDate' 
			from RDS.T_NOSOLOGY nosology
				left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
				left join EHR.T_EHR_RUS_O00015 eh on eh.EhrRusID = nosology.CitizenShip 
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
				left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
				where nosology.NosologyID = @ObjId
		FOR XML PATH(''),   
		ROOT ('patient')
		)
	end
	else if (@MessageType = 'patient.update')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
				Replace(Replace(nosology.SNILS, '-',''), ' ', '')		as 'key/snils',	
				nosology.FirstName										as 'patient/firstName',								
				nosology.LastName										as 'patient/lastName',								
				nosology.FatherName										as 'patient/patronymic',
				eSex.Code												as 'patient/gender',								
				convert(varchar(10), nosology.Birthday, 20)				as 'patient/birthDate',								
				eh.IdEHR												as 'patient/citizenShipId/@id',
				nosology.BirthdayLastName								as 'patient/birthLastName',
				nosology.WorkPlace										as 'patient/workPlace',		
				nosology.BirthPlace										as 'patient/birthPlace',
				--nosology.Adress											as 'address',
				--nosology.FiasAddress									as 'address/aoidArea',
				--nosology.FiasStreet										as 'address/aoidStreet', 
				--nosology.FiasHome										as 'address/houseid', 
				--nosology.City											as 'address/areaName', 
				--nosology.CityPrefix										as 'address/prefixArea',
				--nosology.Street											as 'address/streetName',
				--nosology.StreetPrefix									as 'address/prefixStreet',
				--nosology.House											as 'address/house', 
				Replace(Replace(nosology.DocSeries, '-',''), ' ', '')   as 'patient/documents/document/serial',
				nosology.DocNumber										as 'patient/documents/document/number',
				nosology.DocIssueOrg									as 'patient/documents/document/passOrg', 
				ehr.IdEhr												as 'patient/documents/document/documentId/@id',
				convert(varchar(10), nosology.DocIssueDate, 20)			as 'patient/documents/document/passDate' 
			from RDS.T_NOSOLOGY nosology
				left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
				left join EHR.T_EHR_RUS_O00015 eh on eh.EhrRusID = nosology.CitizenShip 
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
				left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
				where nosology.NosologyID = @ObjId
		FOR XML PATH(''),   
		ROOT ('updatePatient')
		)
	end
    else if (@MessageType = 'patient_document.create')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
			Replace(Replace(nosology.SNILS, '-',''), ' ', '')		as 'key/snils',	
			ehr.IdEhr												as 'documents/document/documentId/@id',								
			Replace(Replace(nosology.DocSeries, '-',''), ' ', '')	as 'documents/document/serial',								
			nosology.DocNumber										as 'documents/document/number',	
			convert(varchar(10), nosology.DocIssueDate, 20)			as 'documents/document/passDate',
			nosology.DocIssueOrg									as 'documents/document/passOrg'
			from RDS.T_NOSOLOGY nosology
				left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
				left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
				where nosology.NosologyID = @ObjId
	FOR XML PATH(''),   
	ROOT ('createDocuments')
	)
	end 
	else if (@MessageType = 'patient_document.update')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
			Replace(Replace(nosology.SNILS, '-',''), ' ', '')		as 'documentKey/snils',	
			ehr.IdEHR												as 'documentId/@id',								
			Replace(Replace(nosology.DocSeries, '-',''), ' ', '')	as 'document/serial',								
			nosology.DocNumber										as 'document/number',								
			convert(varchar(10), nosology.DocIssueDate, 20)			as 'document/passDate',
			nosology.DocIssueOrg									as 'document/passOrg'
			from RDS.T_NOSOLOGY nosology
				left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
				left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
				where nosology.NosologyID = @ObjId
	FOR XML PATH(''),   
	ROOT ('updateDocument')
	)
	end 
	else if (@MessageType = 'vzn.create')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
			Replace(Replace(nosology.SNILS, '-',''), ' ', '')		as 'key/snils',
			mdrInc.oid												as 'moId',
			convert(varchar(10), nosology.DateOfInclusion, 20)		as 'includeDate',
			nosologyBenefits.Head									as 'registry/signedPerson',
			(case when nosology.PolicyForm = 1 then 1 
			when nosology.PolicyForm = 2 then 3 
			when nosology.PolicyForm = 3 then 3 
			when nosology.PolicyForm = 4 then 2
			end )													as 'registry/policTypeId/@id',	
			nosology.OmsSerial										as 'registry/policSerial',
			nosology.OmsNumber										as 'registry/policNumber', 
			nosology.F002Smo										as 'registry/imcCode',
			nosologyBenefits.UniqueNumber							as 'registry/registryNumber',	
			nosology.ExemptionBy178									as 'registry/isInclRegistry',
			nosology.ExemptionBy890									as 'registry/isDrugSupply', 
			nosologyBenefits.Mkb									as 'registry/deseases/desease/code',
			convert(varchar(10), nosologyBenefits.DateBegin, 20)	as 'registry/deseases/desease/beginDate', 
			convert(varchar(10), nosologyBenefits.EndDate, 20)		as 'registry/deseases/desease/endDate', 
			lower(nosology.FiasAddress)								as 'registry/addresses/patientAddress/address/aoidArea', 
			nosology.FiasStreet										as 'registry/addresses/patientAddress/address/aoidStreet',
			nosology.FiasHome										as 'registry/addresses/patientAddress/address/houseid',
			substring(region.Code, 0, 2)							as 'registry/addresses/patientAddress/address/region/@id', 
			nosology.City											as 'registry/addresses/patientAddress/address/areaName', 
			nosology.CityPrefix										as 'registry/addresses/patientAddress/address/prefixArea',
			nosology.Street											as 'registry/addresses/patientAddress/address/streetName',
			nosology.StreetPrefix									as 'registry/addresses/patientAddress/address/prefixStreet',
			nosology.House											as 'registry/addresses/patientAddress/address/house',
			2														as 'registry/addresses/patientAddress/addressTypeId/@id',
			nosology.OperationCode									as 'registry/registryOperationId/@id',
			nosology.ExecutiveAuthority								as 'registry/territoryId/@id',
			--
			convert(varchar(10), nosology.DateOfDeclusion, 20)		as 'registry/excludeDate',
			--nosology.PAC_ID											as 'registry',		      
			convert(varchar(10), nosology.DeathDate, 20)			as 'registry/deathDate', 
			ehrP360.CodeEHR											as 'registry/disabilityGroupId', 
			nosology.ExcludeReason									as 'registry/excludeReasonId',
			mdrDec.oid												as 'registry/excludeMoId', 
			nosology.Comment										as 'registry/note'
		from RDS.T_NOSOLOGY_BENEFITS nosologyBenefits
			left join RDS.T_NOSOLOGY nosology on nosology.NosologyID = nosologyBenefits.Nosology
			left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
			left join EHR.T_EHR_RUS_O00015 eh on eh.EhrRusID = nosology.CitizenShip
			left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
			left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
			left join RDS.T_LPU_MAIN lpuDec on nosology.DeclusionLPU = lpuDec.MCOD
			left join EHR.T_EHR_RUS_MDR308 mdrDec on lpuDec.EhrRusMdr308Dop = mdrDec.EhrRusID
			left join RDS.T_LPU_MAIN lpuInc on nosology.InclusionLPU = lpuInc.MCOD
			left join EHR.T_EHR_RUS_MDR308 mdrInc on lpuInc.EhrRusMdr308Dop = mdrInc.EhrRusID
			left join EHR.T_EHR_RUS_FR7366 ehrFr7366 on nosology.EhrRusFr7366 = ehrFr7366.EhrRusID 
			left join RDS.T_REGION region on nosology.Region = region.RegionID
			left join EHR.T_EHR_RUS_P360COA3OB ehrP360 on nosologyBenefits.EhrRusP360COA3OB = ehrP360.EhrRusID 
		where nosologyBenefits.NosologyBenefitsID = @ObjId
		for xml path(''),
        root('createVzn')
		)
	end 
	else if (@MessageType = 'vzn.update')
	begin
	set @searchPreviousMessage = 1
	select @xml = (
		select  
			mdrInc.oid												as 'moId',
			convert(varchar(10), nosology.DateOfInclusion, 20)		as 'includeDate',
			convert(varchar(10), nosology.DateOfDeclusion, 20)		as 'excludeDate',
			nosologyBenefits.UniqueNumber							as 'registry/registryNumber',	
						(case when nosology.PolicyForm = 1 then 1 
			when nosology.PolicyForm = 2 then 3 
			when nosology.PolicyForm = 3 then 3 
			when nosology.PolicyForm = 4 then 2
			end )													as 'registry/policTypeId/@id',	
			nosology.OmsSerial										as 'registry/policSerial',
			nosology.OmsNumber										as 'registry/policNumber', 
			nosology.F002Smo										as 'registry/imcCode',
			nosology.ExemptionBy178									as 'registry/isInclRegistry',
			nosology.ExemptionBy890									as 'registry/isDrugSupply', 
			nosologyBenefits.Mkb									as 'registry/deseases/desease/code',
			convert(varchar(10), nosologyBenefits.DateBegin, 20)	as 'registry/deseases/desease/beginDate', 
			convert(varchar(10), nosologyBenefits.EndDate, 20)		as 'registry/deseases/desease/endDate', 
			lower(nosology.FiasAddress)								as 'registry/addresses/patientAddress/address/aoidArea', 
			nosology.FiasStreet										as 'registry/addresses/patientAddress/address/aoidStreet',
			nosology.FiasHome										as 'registry/addresses/patientAddress/address/houseid',
			substring(region.Code, 0, 2)							as 'registry/addresses/patientAddress/address/region/@id', 
			nosology.City											as 'registry/addresses/patientAddress/address/areaName', 
			nosology.CityPrefix										as 'registry/addresses/patientAddress/address/prefixArea',
			nosology.Street											as 'registry/addresses/patientAddress/address/streetName',
			nosology.StreetPrefix									as 'registry/addresses/patientAddress/address/prefixStreet',
			nosology.House											as 'registry/addresses/patientAddress/address/house',
			2														as 'registry/addresses/patientAddress/addressTypeId/@id',
			nosology.OperationCode									as 'registry/registryOperationId/@id',
			nosology.ExecutiveAuthority								as 'registry/territoryId/@id',
			nosologyBenefits.Head									as 'registry/signedPerson',
			--nosology.PAC_ID											as 'registry',		      
			convert(varchar(10), nosology.DeathDate, 20)			as 'registry/deathDate', 
			ehrP360.CodeEHR											as 'registry/disabilityGroupId', 
			nosology.ExcludeReason									as 'registry/excludeReasonId/@id',
			mdrDec.oid												as 'registry/excludeMoId', 
			nosology.Comment										as 'registry/note'
		from RDS.T_NOSOLOGY_BENEFITS nosologyBenefits
			left join RDS.T_NOSOLOGY nosology on nosology.NosologyID = nosologyBenefits.Nosology
			left join VCLib.T_ENUMS eSex on eSex.EnumID = nosology.Sex
			left join EHR.T_EHR_RUS_O00015 eh on eh.EhrRusID = nosology.CitizenShip
			left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = nosology.DocumentType
			left join EHR.T_EHR_RUS_NK0469 ehr on dul.EhrRusNk0469 = ehr.EhrRusID
			left join RDS.T_LPU_MAIN lpuDec on nosology.DeclusionLPU = lpuDec.MCOD
			left join EHR.T_EHR_RUS_MDR308 mdrDec on lpuDec.EhrRusMdr308Dop = mdrDec.EhrRusID
			left join RDS.T_LPU_MAIN lpuInc on nosology.InclusionLPU = lpuInc.MCOD
			left join EHR.T_EHR_RUS_MDR308 mdrInc on lpuInc.EhrRusMdr308Dop = mdrInc.EhrRusID
			left join EHR.T_EHR_RUS_FR7366 ehrFr7366 on nosology.EhrRusFr7366 = ehrFr7366.EhrRusID 
			left join RDS.T_REGION region on nosology.Region = region.RegionID
			left join EHR.T_EHR_RUS_P360COA3OB ehrP360 on nosologyBenefits.EhrRusP360COA3OB = ehrP360.EhrRusID 
		where nosologyBenefits.NosologyBenefitsID = @ObjId
		for xml path(''),
        root('updateVzn')
		)
	end 
	else if(@MessageType = 'vzn_need.create')
	begin
	set @searchPreviousMessage = 0
	delete RDS.T_MESSAGE_TO_NOSOLOGY_SERVICE where ObjId = @ObjId
	select @xml = (
				select
			 nos.UniqueNumber							as 'registryNumber'
			,eh.IdEHR									as 'appEvent/@id'
			,ehBen.IdEHR								as 'nrVznNeedPers/nosGroupId/@id'
			,ehMnn.IdEHR								as 'nrVznNeedPers/specs/spec/mnnId/@id'
			,ehMf.IdEHR									as 'nrVznNeedPers/specs/spec/drugFormId/@id'
			,ehBen.IdEHR								as 'nrVznNeedPers/specs/spec/dosageId/@id'
			,(pdd.DemandOnPeriod * dali.DoseCount)/12	as 'nrVznNeedPers/specs/spec/needPerMonth'
			,pdd.DemandOnPeriod * dali.DoseCount		as 'nrVznNeedPers/specs/spec/needPerYear'
			--,4										as 'nrVznNeedPers/specs/spec/statusId'
			--,5										as 'nrVznNeedPers/specs/spec/needStatus'
			--,1										as 'nrVznNeedPers/specs/spec/standardId'
			--,1										as 'nrVznNeedPers/specs/spec/rejectionReason'
			--,1										as 'nrVznNeedPers/specs/spec/measureId'
			,pdd.Signa									as 'nrVznNeedPers/specs/spec/singleDose'
		from RDS.T_PATIENT_DRUG_DEMAND pdd
			join RDS.T_PHARMACY_DRUG_DEMAND phdd on phdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
			join RDS.T_DRUG_DEMAND dd on dd.DrugDemandID = phdd.DrugDemand
			join RDS.T_DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
			left join EHR.T_EHR_RUS_VZN17 eh on eh.EhrRusID = dr.EhrRusVzn17
			join RDS.T_BENEFITS b on b.BenefitsID = pdd.Benefits
			outer apply(
				select top 1 nb.UniqueNumber
				from RDS.T_NOSOLOGY_BENEFITS nb
				join RDS.T_NOSOLOGY n on n.NosologyID = nb.Nosology
				where n.SNILS = pdd.Snils and nb.Benefits = pdd.Benefits
				and nb.DateBegin <= dr.BeginPeriod and (nb.EndDate is null or nb.EndDate >= dr.EndPeriod)
				and (n.DateOfDeclusion is null or n.DateOfDeclusion >= dr.BeginPeriod)
				--добавить фильтр на даты
			) nos
			join RDS.T_DRUG_APPLICATION_LIST_ITEM dali on dali.DrugApplicationListItemID = dd.DrugApplicationListItem
			left join PHARM.T_C_MNN mnn on mnn.CMnnID = dali.CMnn
			left join EHR.T_EHR_RUS_NSV101 ehMnn on ehMnn.EhrRusID = mnn.EhrRusNsv101
			left join PHARM.T_MEDICATION_FORMS mf on mf.MedicationFormsID = dali.MedicationForms
			left join EHR.T_EHR_RUS_VZN431 ehMf on ehMf .EhrRusID = mf.EhrRusVzn431
			left join EHR.T_EHR_RUS_H17F8UTS3N ehBen on ehBen.EhrRusID = dali.EhrRusH17F8UTS3N
		where pdd.PatientDrugDemandID = @ObjId
		for xml path(''),
        root('createNeed')
		)
	end 
	else if(@MessageType = 'vzn_recipe.create')
	begin
	set @searchPreviousMessage = 0
	delete RDS.T_MESSAGE_TO_NOSOLOGY_SERVICE where ObjId = @ObjId
	select @xml = (
		select  
			nos.UniqueNumber										as 'registryNumber',
			recipe.Serial											as 'recipe/recipeSerial',
			recipe.Number											as 'recipe/recipeNumber',								
			convert(varchar(10), recipe.RecipeDate, 20)				as 'recipe/issueDate',								
			convert(varchar(10), recipe.IssueDate	, 20)			as 'recipe/deliveryDate',	
			--recipe.IssueDate										as 'regionId',
			recipe.DoctorSnils										as 'recipe/personId',
			recipe.MKBCode											as 'recipe/desease',
			ehrMnn.IdEHR											as 'recipe/mnnId/@id',
			lpu.oid													as 'recipe/moId',
			mdr308.oid												as 'recipe/pharmacyId',
			vzn431.IdEHR											as 'recipe/drugFormId/@id', --VZN431
			pharmacy.FIO_CHIEF										as 'recipe/signedPerson',
			nos.TherapyResistance									as 'recipe/isTheraphyResistence',
			( select 
				ehrDv.IdEHR												as 'released/dosageId/@id',
				ehrDvCount.IdEHR										as 'released/dosageCountId/@id',
				re.Amount												as 'released/packCount',
				ehrBg.IdEHR												as 'released/vznDrugId/@id'
			 from RDS.T_RECIPE_ELEMENT re
				left join PHARM.T_BILL_OF_GOODS bog on re.BillOfGoods = bog.BillOfGoodsID
				left join EHR.T_EHR_RUS_CLP14FVIRJ ehrBg on ehrBg.EhrRusID = bog.EhrRusClp14fvirj
				left join EHR.T_EHR_RUS_H17F8UTS3N ehrDv on ehrBg.EhrRusID = bog.EhrRusH17F8UTS3N
				left join EHR.T_EHR_RUS_DSG207 ehrDvCount on ehrBg.EhrRusID = bog.EhrRusDsg207
			 where re.Recipe = recipe.RecipeID 
			 for xml path('rels'),
			TYPE
			)
		from RDS.T_RECIPE recipe
		left join PHARM.T_MEDICATION_FORMS mf on recipe.MedicationForms = mf.MedicationFormsID
		left join EHR.T_EHR_RUS_VZN431 vzn431 on mf.EhrRusVzn431 = vzn431.EhrRusID
		left join PHARM.T_C_MNN mnn on recipe.CMnn = mnn.CMnnID
		left join EHR.T_EHR_RUS_NSV101 ehrMnn on ehrMnn.EhrRusID = mnn.EhrRusNsv101
		left join RDS.T_PHARMACY pharmacy on pharmacy.Code = recipe.Pharmacy
		left join EHR.T_EHR_RUS_MDR308 mdr308 on pharmacy.EhrRusMdr308 = mdr308.EhrRusID
		outer apply(
			select top 1 eh.oid
			from RDS.T_LPU_MAIN lpu 
			left join EHR.T_EHR_RUS_MDR308 eh on eh.EhrRusID = lpu.EhrRusMdr308Dop
			where recipe.LpuCode = lpu.MCOD or eh.oid = recipe.LpuCode
			) lpu
		outer apply
		(
			select top 1 nb.TherapyResistance, nb.UniqueNumber 
			from RDS.T_NOSOLOGY_BENEFITS nb
			join RDS.T_NOSOLOGY nos on nos.NosologyID = nb.Nosology
			where nos.SNILS = recipe.Snils and nb.Benefits = recipe.Benefit
			and nb.DateBegin <= recipe.RecipeDate and (nb.EndDate is null or nb.EndDate >= recipe.RecipeDate)
			and (nos.DateOfDeclusion is null or nos.DateOfDeclusion >= recipe.RecipeDate)
		) nos
		where recipe.RecipeID = @ObjId
		for xml path(''),
        root('createVznRecipe')
		)
	end

	
	INSERT INTO [RDS].[T_MESSAGE_TO_NOSOLOGY_SERVICE]
           ([MessageToNosologyServiceID]
           ,[Status]
           ,[CreateDate]
           ,[SendDate]
           ,[Xml]
           ,[IdFromService]
           ,[Error]
           ,[ObjId]
           ,[Users]
           ,[Type]
           ,[MessageToNosologyService]
		   ,[oid]
		   ,[Snils]
		   ,[ExportRecipeVznService])
     VALUES
           (newid()
           ,0
           ,SYSDATETIME()
           ,null
           ,dbo.ConvertTextToBinaryZip(cast(@Xml as varchar(max)), '')
           ,null
           ,null
           ,@ObjId
           ,null
           ,(case
                when @MessageType = 'patient.create' then 1
                when @MessageType = 'patient.update' then 2
                when @MessageType = 'patient_document.create' then 4
                when @MessageType = 'patient_document.update' then 5
                when @MessageType = 'vzn.create' then 14
                when @MessageType = 'vzn.update' then 15
                when @MessageType = 'vzn_need.create' then 20
                when @MessageType = 'vzn_recipe.create' then 18
                end)
           ,case when @searchPreviousMessage = 1 then (select top 1 mtns.MessageToNosologyServiceID 
				from RDS.T_MESSAGE_TO_NOSOLOGY_SERVICE mtns
				where mtns.Snils = @Snils
				order by mtns.CreateDate desc) else null end
		   ,@oid
		   ,@Snils
		   ,@DopId) 
 end;

