
/****** Object:  StoredProcedure [dbo].[sp_TFOMS_MotionInHospitalXml]    Script Date: 1/12/2020 8:50:33 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_TFOMS_MotionInHospitalXml]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].sp_TFOMS_MotionInHospitalXml
GO
-- =============================================
-- Author:	Ilya Zhuykov	Vitacore (с)
-- Create date: 2020-10-02
-- Description:	Выгрузка информации о завершении госпитализации в ТФОМС
-- =============================================
CREATE PROCEDURE [dbo].[sp_TFOMS_MotionInHospitalXml]
	@ID uniqueidentifier = null,
	@QueueName varchar(255) = null,	
	@Sender varchar(255) = null
AS
BEGIN
declare @Result varchar(max)
		,@refNumber varchar(225)
		,@hospType int
		,@mainID uniqueidentifier = null
		,@resultDate datetime = null
		,@PolicyDataNotDiffers bit = 0 
		,@PolicyExists bit = 0 
		,@IsNewborn bit = 0 
		,@ChildRegistrID uniqueidentifier = null
SET NOCOUNT ON;
	select @hospType = mdc.HospitalizationType
	,@mainID = mov.MovementID
	,@resultDate = mov_res.DatOut
	from [AKUZ].T_MOVEMENT_RESULT mov_res 
		join [AKUZ].T_MOVEMENT mov on mov_res.MedicalcardRegistration = mov.MovementID
		left join [AKUZ].T_MEDICALCARD_REGISTRATION  mdc on mov_res.MedicalcardRegistration = mdc.MovementID
		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
		left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
		left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
		left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
	where mov_res.MedicalRecordID = @ID

	if @mainID is null
	begin	
		select @hospType = mdc.HospitalizationType
		,@mainID = mov.MovementID
		,@resultDate = mov.MovementDate
		from  [AKUZ].T_MOVEMENT mov 
			join [AKUZ].T_MEDICALCARD_REGISTRATION  mdc on mov.MovementID = mdc.MovementID
			join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
			left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
			left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
			left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
		where mov.MovementID = @ID
	end
--___проверка случая новорожденного

	select @ChildRegistrID = (select top 1 MovementID  from [AKUZ].T_CHILD_REGISTRATION where MovementID = @mainID)

	if @ChildRegistrID is not null 
	select @mainID = @ChildRegistrID
--___проверяем ситуацию несовпадения ФИО полиса и карты для случая несовершеннолетних ________________	

	
	select @IsNewborn = 
		case when @resultDate - pat.Birthday <=31 then 1
			else 0 
		end
	from  [AKUZ].T_MOVEMENT mov 
		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
	where mov.MovementID = @mainID

	select  @PolicyDataNotDiffers =
		case when pol.LastName = pat.LastName and pol.FirstName = pat.FirstName and pol.FatherName = pat.FatherName and pol.Birthday = pat.Birthday then  1
		else 0
	end
	,@PolicyExists = 
		case when pol.PolicyID is not null then 1 
		else 0
	end
	from  [AKUZ].T_MOVEMENT mov 
		join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
		join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
		join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
		left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,@resultDate)
	where mov.MovementID = @mainID

	if(@PolicyExists = 0)
	begin
		RETURN
	end;

	if @hospType  <>  13002
	begin
		select @refNumber = lpDir.LpuCode + Cast(YEAR(GETDATE()) as varchar(4))+right('0000'+ db.DirectionNumber, 5)
		from [AKUZ].T_MOVEMENT_RESULT mov_res 
			join [AKUZ].T_MOVEMENT mov on mov_res.MedicalcardRegistration = mov.MovementID
			join [AKUZ].T_MEDICALCARD_REGISTRATION  mdc on mov_res.MedicalcardRegistration = mdc.MovementID
			join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
			left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
			left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
			left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
		where mov_res.MedicalRecordID = @ID and db.DirectionNumber is not null

		if @refNumber is null
		begin
			select @refNumber = lpDir.LpuCode + Cast(YEAR(GETDATE()) as varchar(4))+right('0000'+ db.DirectionNumber, 5)
			from  [AKUZ].T_MOVEMENT mov
				join [AKUZ].T_MEDICALCARD_REGISTRATION  mdc on mov.MovementID = mdc.MovementID
				join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
				left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
				left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
				left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
			where  mov.MovementID = @ID and db.DirectionNumber is not null
		end
	end

--__________xml______________________________________________________________________________________________________________________________--
	if @ChildRegistrID is null
	begin
 	
		if @hospType <>  13002
		begin
			;WITH xmlnamespaces ('https://services.rosminzdrav.ru/' as ser)
				SELECT @Result = 
				(
						SELECT 
						 '1.0'				                                   							as 'ser:HEAD/ser:VERSION'				 --версия
						,@Sender	                                             						as 'ser:HEAD/ser:SENDER'			     --МО-отправитель сведений
						,convert(varchar(10),CURRENT_TIMESTAMP, 20)            							as 'ser:HEAD/ser:D_EVENT'				 --Дата отправления сведений
						--,''												   							as 'ser:HEAD/ser:GUID_QUERY'			 --Идентификатор для №4			
						,
						(
							select 
							lm.LpuCode                                                					as 'ser:CODE_MO'				 --Принимающая направление МО
							,
							(
								select 
								IsNull(tt.MedicalRecordID,mov.MovementID )								as 'ser:GUID'		 --Уникальный код записи 
								,lpDir.LpuCode											   				as 'ser:NPR_MO'		 --Реестровый номер МО, направившей пациента на госпитализацию
								,CASE 
									WHEN brDir.TFomsCode like '%.1' THEN null 
									ELSE brDir.TFomsCode
								 END 													   				as 'ser:NPR_LPU'		 --Региональный код подразделения МО, направившего пациента на госпитализацию			                                              		    
								,lm.LpuCode 										   					as 'ser:DEST_MO'		 --Реестровый номер МО, в которую направлен/госпитализирован пациент
								,CASE 
									WHEN br.TFomsCode like '%.1' THEN null 
									ELSE br.TFomsCode
								 END												   					as 'ser:DEST_LPU'		 --Региональный код подразделения МО, в которое направлен/госпитализирован пациент
								,CASE mdc.HospitalizationType
									WHEN 13001 THEN 3
									WHEN 13002 THEN 1
									WHEN 13003 THEN 2
								 END																	as 'ser:FOR_POM'	         --Форма оказания медицинской помощи F003
								 ,
									(
										select	
											CASE
												WHEN  @IsNewborn = 1 and @PolicyDataNotDiffers = 0 THEN 1
												else null
											END																as 'ser:OS_SLUCH' --Признак новорожденного
											, 								
											 pat.LastName													as 'ser:FAM'	 --Фамилия пациента
											,pat.FirstName													as 'ser:IM'	 --Имя пациента
											,pat.FatherName													as 'ser:OT'	 --Отчество пациента
											,(case when pat.Sex = 10301 then 1 else 2 end)					as 'ser:W'	     --Пол
											,convert(varchar(10), pat.Birthday, 20)							as 'ser:DR'	 --День рождения пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.LastName
												else null
											END																as 'ser:FAM_P'	 --Фамилия представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.FirstName
												else null
											END																as 'ser:IM_P'	 --Имя представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.FatherName
												else null
											END																as 'ser:OT_P'	 --Отчество представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN convert(varchar(10),pol.Birthday, 20)	
												else null
											END																as 'ser:DR_P'	 --День рождения представителя пациента

											,CASE pol.PolicyForm
												WHEN 4 THEN 2
												WHEN 1 THEN 1
												else 3
											END																as 'ser:VPOLIS'	 --Тип ДПФС
											,pol.Serial     												as 'ser:SPOLIS'	 --Серия ДПФС
											,pol.Number    									    			as 'ser:NPOLIS'	 --Номер ДПФС
											,ISNULL(i1.SmoCod, i2.SmoCod)					    			as 'ser:SMO'		 --Реестровый номер СМО
											--,''															as 'ser:SMO_NAM'	 --Наименование СМО - не заполнять если номер есть
											,substring (ISNULL(ISNULL(r1.Okato, r2.Okato),''),1,5)			as 'ser:ST_OKATO'	 --Регион страхования

											,
											CASE 
												WHEN  @IsNewborn = 1 and @PolicyDataNotDiffers = 0 THEN 1
												else null
											END																as 'ser:NOVOR_NUM' --Признак новорожденного
									
											,case 
											when tt.Phone is not null and LTRIM(tt.Phone)<>''
												then tt.Phone
											else isnull((select top 1 Number  
											from akuz.t_phone_number ph_n
											where ph_n.Patient = pat.PatientID), '0')
											end                                                             as 'ser:CONTACT'	 --Контактный номер
										FOR XML PATH('ser:PACIENT'),
										TYPE
									)
			--___________________NPR_____________________________________________________________________________________________________________________________________
								,
								(
									select
									IsNull(convert(varchar(10), tt.ReferralDate, 20),
									convert(varchar(10), db.DirectionDate, 20))						as 'ser:NPR_DATE'	 --Дата направления
									,@refNumber														as 'ser:NPR_NUM'	 --Номер направления
									,IsNull(tt.MkbCode,db.Mkb )										as 'ser:DS'			 --Диагноз направления
									,CASE 
									-- исходного направления нет, но номер направления установлен
										when tt.DivisionType is null and @refnumber is not null then
											CASE d.DivisionType
												WHEN 12808 THEN 2
												WHEN 12809 THEN 2
												WHEN 12810 THEN 2
											ELSE 1
											end
										when tt.DivisionType is null then null
										WHEN tt.DivisionType  = 12808 THEN 2
										WHEN tt.DivisionType  =  12809 THEN 2
										WHEN tt.DivisionType  = 12810 THEN 2				
										ELSE 1
										END															as 'ser:USL_OK'     --Условия оказания МП V006 
									,IsNull(tt.sbCodeMz,sb.CodeMz)									as 'ser:PROFK'		--Профиль койки
									,isnull(tt.divCodeMz,d.CodeMz )										as 'ser:PODR'		--Код отделения
									,tt.Snils														as 'ser:CODE_MD'	--СНИЛС медработника, направившего больного
									,isNull(isNull(convert(varchar(10), tt.PreHospitalDate, 20)
									,convert(varchar(10), db.PreHospitalDate, 20)),convert(varchar(10), mov.MovementTime, 20))					as 'ser:DATE_P'		--Предварительная дата госпитализации
									FOR XML PATH('ser:NPR'),
									TYPE
								)					
			--___________________HOSP[]______________________________________________________________________________________________________________________________________
														,
								(
									select isnull(isnull(isnull(isnull(rdrd.Mkb ,mov.Mkb), db.Mkb),last_ooi.MKBCode),ooi.MKBCode)
																									as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
									,convert(varchar(10),isnull(last_res.DatOut,db.PreHospitalDate)	, 20)					as 'ser:DATE_P'		--Плановая дата госпитализации
									,convert(varchar(10), mov.ExpectedDateOfDischarge, 20)			as 'ser:DATE_V'		--Плановая дата выбытия
									,CASE res_d.DivisionType
										WHEN 12808 THEN 2
										WHEN 12809 THEN 2
										WHEN 12810 THEN 2
									ELSE 1
									END																as 'ser:USL_OK'		--Условия оказания МП
									,sb.CodeMz														as 'ser:PROFK'		--Код профиля койки
									,res_d.CodeMz													as 'ser:PODR'		--Код отделения
									,mov.MedCardNumber												as 'ser:N_MKSB'		--Номер карты стационарного больного
									,replace(convert(varchar(20), mov_res.TimeIn, 20),' ','T')		as 'ser:DATE_1'		--Дата и время фактической госпитализации пациента
									,convert(varchar(10), mov_res.DatOut, 20)						as 'ser:DATE_2'		--Дата выбытия
									,ooi.MKBCode													as 'ser:DS'			--Диагноз при выбытии

									from [AKUZ].T_MOVEMENT_RESULT mov_res 
									join [AKUZ].T_OUTCOME_OF_INSPECTION ooi on mov_res.MedicalRecordID = ooi.MedicalRecordID
									left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov_res.StructureBed
									left join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
									left join akuz.t_division res_d on mov_res.Division = res_d.DivisionID
									left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  mov.MovementID
										and last_res.DatOut = (select max(msrs.DatOut)
											from akuz.t_movement_result msrs where msrs.MedicalcardRegistration =  mov.MovementID and msrs.DatOut < mov_res.DatIn )
									left join  [AKUZ].T_OUTCOME_OF_INSPECTION last_ooi on last_res.MedicalRecordID = last_ooi.MedicalRecordID

									where mov_res.MedicalcardRegistration = mov.MovementID
									FOR XML PATH('ser:HOSP'),
									TYPE
								)
			--_____________________________________________________________________________________________________________________________________________________________
						,
								(
									select isnull(isnull(rdrd.Mkb ,modc.Mkb), db.Mkb) 				as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
									,isnull(convert(varchar(10),last_res.DatOut	, 20),
									isnull(	convert(varchar(10),tt.ReferralDate	, 20),
									convert(varchar(10),db.PreHospitalDate	, 20)))				as 'ser:DATE_P'	--Плановая дата госпитализации
									,convert(varchar(10), modc.ExpectedDateOfDischarge, 20)			as 'ser:DATE_V'	--Плановая дата выбытия
									,CASE last_res_d.DivisionType
										WHEN 12808 THEN 2
										WHEN 12809 THEN 2
										WHEN 12810 THEN 2
									ELSE 1
									END																as 'ser:USL_OK'	--Условия оказания МП
									,sb.CodeMz														as 'ser:PROFK'	--Код профиля койки
									,isNull(last_res_d.CodeMz,dc.CodeMz)							as 'ser:PODR'		--Код отделения
									,modc.MedCardNumber												as 'ser:N_MKSB'	--Номер карты стационарного больного
									,replace(convert(varchar(20), 
										isnull(
										isnull(last_res.TimeIn,last_res.DatIn),
										modc.MovementTime)
										, 20),' ','T')												as 'ser:DATE_1'	--Дата и время фактической госпитализации пациента
									--,''															as 'ser:DATE_2'	--Предполагаемая дата выбытия
									--,''															as 'ser:DS'		--Диагноз при выбытии

									from [AKUZ].T_MOVEMENT modc
									join akuz.T_EVENT_BASE modceb on modceb.EventID = modc.EmergencyEvent
									left join AKUZ.T_DIVISION dc on dc.DivisionID = modc.Division
									left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  modc.MovementID
										and last_res.DatOut = (select max(msrs.DatOut)
											from akuz.t_movement_result msrs where msrs.MedicalcardRegistration =  modc.MovementID)
									left join akuz.t_division last_res_d on last_res.Division = last_res_d.DivisionID
									where modc.MovementID = mov.MovementID 
									--Последняя актуальная госпитализация
									and modceb.CloseDate is null
									FOR XML PATH('ser:HOSP'),
									TYPE
								)
			--_____________________________________________________________________________________________________________________________________________________________

								FOR XML PATH('ser:REC'),
								TYPE
							)
							FOR XML PATH('ser:DATA'),
							TYPE
						)			
			
				from [AKUZ].T_MOVEMENT mov 
					join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
					join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
					join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = mov.MovementID
					--LPU
					join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
					join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
					join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
					--patient
					join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
					join [AKUZ].T_EMERGENCY_EVENT ee on ee.EventID = eb.EventID
					join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
					join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
					--diagnosis
					left join [AKUZ].T_RECEPTION_DIAGNOSIS rd on rd.EmergencyEvent = eb.EventID 
					left join [AKUZ].T_DIAGNOSIS rdrd on rdrd.MedicalRecordID =  rd.MedicalRecordID 
					--polis
					left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,@resultDate)
					left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
					left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
					left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
					left join [AKUZ].V_INSURER_REGION ir1 on ir1.InsurerRegionID = i1.OKATO		
					left join [AKUZ].T_REGION r1 on r1.RegionID = ir1.Region
					left join [AKUZ].V_INSURER_REGION ir2 on ir2.InsurerRegionID = i2.OKATO		
					left join [AKUZ].T_REGION r2 on r2.RegionID = ir2.Region
					--referral
					left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
					left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
					left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
					left join [AKUZ].T_DIVISION dDir on  dDir.DivisionID= dir.DirectorDivision
					left join [AKUZ].T_BRANCH brDir on brDir.LpuID = dDir.Adress
		
					--original referral
					outer apply
					(
						select hr.MedicalRecordID, hr.Phone,hrr.ReferralDate,hrr.MkbCode, dT.DivisionType, hrsb.CodeMz as sbCodeMz,hr.PreHospitalDate,dT.CodeMz as divCodeMz,hrw.Snils
						from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
						join [AKUZ].T_REFERRAL hrr on hrr.MedicalRecordID = hr.MedicalRecordID
						join [AKUZ].T_MEDICAL_RECORD hrmr on hrmr.MedicalRecordID = hrr.MedicalRecordID
						join [AKUZ].T_EVENT_BASE hrae on  hrae.EventID = hrmr.AmbulanceEvent
						join [AKUZ].T_AMBULANCE_CARD hrac on  hrac.AmbulanceCardID = hrae.AmbulanceCard
						join [AKUZ].T_PATIENT hrp on  hrp.PatientID = hrac.Patient
						join [AKUZ].T_STRUCTURE_BED hrsb on hrsb.StructureBedID = hr.StructureBed
						left join [AKUZ].T_DIVISION dT on dT.DivisionID = hrr.Division			
						join [AKUZ].T_POST hrpst on hrpst.ResourceID = hrmr.Post
						join [AKUZ].T_RESOURCE hrrs on hrrs.ResourceID = hrpst.ResourceID
						join [AKUZ].T_WORKER hrw on hrpst.Worker = hrw.WorkerID
						where hr.ReferralNumber = db.DirectionNumber
						and hrr.Lpu = db.Lpu
						and hrp.PatientID = pat.PatientID
						and hrr.ReferralDate <= eb.EventDate
						and DATEADD(M,2,hrr.ReferralDate)>= eb.EventDate
					) tt
					where mov.MovementID = @mainID

					FOR XML PATH(''),
					ROOT('ser:SendRouteRqst')
				) 	
		
				select @mainID = IsNull(tt.MedicalRecordID,mov.MovementID )
				from [AKUZ].T_MOVEMENT_RESULT mov_res 
					join [AKUZ].T_MOVEMENT mov on mov_res.MedicalcardRegistration = mov.MovementID
					join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
					join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
					join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
					left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = eb.Direction
					outer apply
					(
						select hr.MedicalRecordID
						from [AKUZ].T_HOSPITALIZATION_REFERRAL hr
						join [AKUZ].T_REFERRAL hrr on hrr.MedicalRecordID = hr.MedicalRecordID
						join [AKUZ].T_MEDICAL_RECORD hrar on  hrar.MedicalRecordID = hr.MedicalRecordID
						join [AKUZ].T_EVENT_BASE hrae on  hrae.EventID = hrar.AmbulanceEvent
						join [AKUZ].T_AMBULANCE_CARD hrac on  hrac.AmbulanceCardID = hrae.AmbulanceCard
						join [AKUZ].T_PATIENT hrp on  hrp.PatientID = hrac.Patient

						where hr.ReferralNumber = db.DirectionNumber
						and hrr.Lpu = db.Lpu
						and hrp.PatientID = pat.PatientID
						and hrr.ReferralDate <= eb.EventDate
						and DATEADD(M,2,hrr.ReferralDate)>= eb.EventDate
					) tt
					where mov_res.MedicalRecordID = @ID
		end
		else
		begin

			;WITH xmlnamespaces ('https://services.rosminzdrav.ru/' as ser)
				SELECT @Result = 
				(
						SELECT 
						 '1.0'				                                   							as 'ser:HEAD/ser:VERSION'				 --версия
						,@Sender	                                             						as 'ser:HEAD/ser:SENDER'			     --МО-отправитель сведений
						,convert(varchar(10),CURRENT_TIMESTAMP, 20)            							as 'ser:HEAD/ser:D_EVENT'				 --Дата отправления сведений
						--,''												   							as 'ser:HEAD/ser:GUID_QUERY'			 --Идентификатор для №4			
						,
						(
							select 
							lm.LpuCode                                                					as 'ser:CODE_MO'				 --Принимающая направление МО
							,
							(
								select 
								mov.MovementID															as 'ser:GUID'		 --Уникальный код записи 						
								,lm.LpuCode 										   					as 'ser:DEST_MO'		 --Реестровый номер МО, в которую направлен/госпитализирован пациент
								,CASE 
									WHEN br.TFomsCode like '%.1' THEN null 
									ELSE br.TFomsCode
								 END												   					as 'ser:DEST_LPU'		 --Региональный код подразделения МО, в которое направлен/госпитализирован пациент
								,CASE mdc.HospitalizationType
									WHEN 13001 THEN 3
									WHEN 13002 THEN 1
									WHEN 13003 THEN 2
								 END																	as 'ser:FOR_POM'	         --Форма оказания медицинской помощи F003
								 ,
									(
										select	
											CASE 
												WHEN  @IsNewborn = 1  and @PolicyDataNotDiffers = 0  THEN 1
												else null
											END																as 'ser:OS_SLUCH' --Признак новорожденного
											, 
											 pat.LastName													as 'ser:FAM'	 --Фамилия пациента
											,pat.FirstName													as 'ser:IM'	 --Имя пациента
											,pat.FatherName													as 'ser:OT'	 --Отчество пациента
											,(case when pat.Sex = 10301 then 1 else 2 end)					as 'ser:W'	     --Пол
											,convert(varchar(10), pat.Birthday, 20)							as 'ser:DR'	 --День рождения пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.LastName
												else null
											END																as 'ser:FAM_P'	 --Фамилия представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.FirstName
												else null
											END																as 'ser:IM_P'	 --Имя представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN pol.FatherName
												else null
											END																as 'ser:OT_P'	 --Отчество представителя пациента
											, 
											CASE @PolicyDataNotDiffers
												WHEN 0  THEN convert(varchar(10),pol.Birthday, 20)	
												else null
											END																as 'ser:DR_P'	 --День рождения представителя пациента															as 'ser:DR_P'	 --День рождения представителя пациента

											,CASE pol.PolicyForm
												WHEN 4 THEN 2
												WHEN 1 THEN 1
												else 3
											END																as 'ser:VPOLIS'	 --Тип ДПФС
											,pol.Serial     												as 'ser:SPOLIS'	 --Серия ДПФС
											,pol.Number    									    			as 'ser:NPOLIS'	 --Номер ДПФС
											,ISNULL(i1.SmoCod, i2.SmoCod)					    			as 'ser:SMO'		 --Реестровый номер СМО
											--,''															as 'ser:SMO_NAM'	 --Наименование СМО - не заполнять если номер есть
											,substring (ISNULL(ISNULL(r1.Okato, r2.Okato),''),1,5)			as 'ser:ST_OKATO'	 --Регион страхования,									
											,CASE
												WHEN  @IsNewborn = 1  and @PolicyDataNotDiffers = 0  THEN 1
												else null
											END																as 'ser:NOVOR_NUM' --Признак новорожденного									
											,isnull(
											(select top 1 Number  
											from akuz.t_phone_number ph_n
											where ph_n.Patient = pat.PatientID) , '0')						as 'ser:CONTACT'	 --Контактный номер
										FOR XML PATH('ser:PACIENT'),
										TYPE
									)
				
			--___________________HOSP[]______________________________________________________________________________________________________________________________________
								,
								(
										select isnull(last_ooi.MKBCode,ooi.MKBCode)
																											as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
											--,convert(varchar(10),db.PreHospitalDate	, 20)					as 'ser:DATE_P'		--Плановая дата госпитализации
											,convert(varchar(10), mov_res.DatOut, 20)			as 'ser:DATE_V'		--Плановая дата выбытия
											,CASE resDiv.DivisionType
												WHEN 12808 THEN 2
												WHEN 12809 THEN 2
												WHEN 12810 THEN 2
											ELSE 1
											END																as 'ser:USL_OK'		--Условия оказания МП
											,sb.CodeMz														as 'ser:PROFK'		--Код профиля койки
											,resDiv.CodeMz													as 'ser:PODR'		--Код отделения
											,res_mov.MedCardNumber												as 'ser:N_MKSB'		--Номер карты стационарного больного
											,replace(convert(varchar(20), mov_res.TimeIn, 20),' ','T')		
																											as 'ser:DATE_1'      --Дата и время фактической госпитализации пациента
											,convert(varchar(10), mov_res.DatOut, 20)						as 'ser:DATE_2'		--Дата выбытия
											,ooi.MKBCode													as 'ser:DS'			--Диагноз при выбытии

											from [AKUZ].T_MOVEMENT_RESULT mov_res 
											join [AKUZ].T_OUTCOME_OF_INSPECTION ooi on mov_res.MedicalRecordID = ooi.MedicalRecordID
											left join [AKUZ].T_DIVISION resDiv on mov_res.Division = resDiv.DivisionID
											left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov_res.StructureBed
											left join.[AKUZ].T_MOVEMENT res_mov on mov_res.MedicalcardRegistration = res_mov.MovementID
											left join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
											left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  mov_res.MedicalcardRegistration
												and last_res.DatOut = (select max(msrs.DatOut)
													from akuz.t_movement_result msrs where 
													msrs.MedicalcardRegistration =  mov_res.MedicalcardRegistration 
													and msrs.DatOut < mov_res.DatIn )
											left join  [AKUZ].T_OUTCOME_OF_INSPECTION last_ooi on last_res.MedicalRecordID = last_ooi.MedicalRecordID

											where mov_res.MedicalcardRegistration = @mainID
										FOR XML PATH('ser:HOSP'),
										TYPE
								)
			--_____________________________________________________________________________________________________________________________________________________________
					,
								(

									select last_ooi.MKBCode												as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
										,convert(varchar(10),last_res.DatOut	, 20)					as 'ser:DATE_P'	--Плановая дата госпитализации
										,convert(varchar(10), modc.ExpectedDateOfDischarge, 20)			as 'ser:DATE_V'	--Плановая дата выбытия
										,

										CASE isnull (last_res_d.DivisionType,dc.DivisionType)
											WHEN 12808 THEN 2
											WHEN 12809 THEN 2
											WHEN 12810 THEN 2
										ELSE 1
										END																as 'ser:USL_OK'	--Условия оказания МП
										,sbc.CodeMz														as 'ser:PROFK'	--Код профиля койки
										,isnull (last_res_d.CodeMz,dc.CodeMz)							as 'ser:PODR'		--Код отделения
										,modc.MedCardNumber												as 'ser:N_MKSB'	--Номер карты стационарного больного
										,replace(convert(varchar(20), 
										isnull(
										isnull(last_res.TimeIn,last_res.DatIn),
										modc.MovementTime)
										, 20),' ','T')	
																										as 'ser:DATE_1'	--Дата и время фактической госпитализации пациента
										--,''															as 'ser:DATE_2'	--Предполагаемая дата выбытия
										--,''															as 'ser:DS'		--Диагноз при выбытии

										from [AKUZ].T_MOVEMENT modc
										left join [AKUZ].T_STRUCTURE_BED sbc on sbc.StructureBedID = modc.StructureBed
										left join AKUZ.T_DIVISION dc on dc.DivisionID = modc.Division
										left join akuz.T_EVENT_BASE modceb on modceb.EventID = modc.EmergencyEvent	
										left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  modc.MovementID
											and last_res.MedicalRecordID = (select top 1 msrs.MedicalRecordID
												from akuz.t_movement_result msrs where msrs.MedicalcardRegistration =  modc.MovementID
												--and msrs.DatOut = max(msrs.DatOut)
												order by msrs.DatIn desc)
										left join  [AKUZ].T_OUTCOME_OF_INSPECTION last_ooi on last_res.MedicalRecordID = last_ooi.MedicalRecordID
										left join AKUZ.T_DIVISION  last_res_d on last_res.Division = last_res_d.DivisionID
										where modc.MovementID = @mainID 
										--Последняя актуальная госпитализация
										and modceb.CloseDate is  null
									FOR XML PATH('ser:HOSP'),
									TYPE
								)
			--_____________________________________________________________________________________________________________________________________________________________

								FOR XML PATH('ser:REC'),
								TYPE
							)
							FOR XML PATH('ser:DATA'),
							TYPE
						)			
			
				from [AKUZ].T_MOVEMENT mov 
					join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
					join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
					join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = mov.MovementID
					--LPU
					join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
					join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
					join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
					--patient
					join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
					join [AKUZ].T_EMERGENCY_EVENT ee on ee.EventID = eb.EventID
					join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
					join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
					--diagnosis
					left join [AKUZ].T_RECEPTION_DIAGNOSIS rd on rd.EmergencyEvent = eb.EventID 
					left join [AKUZ].T_DIAGNOSIS rdrd on rdrd.MedicalRecordID =  rd.MedicalRecordID 
					--polis
					left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,@resultDate)
					left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
					left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
					left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
					left join [AKUZ].V_INSURER_REGION ir1 on ir1.InsurerRegionID = i1.OKATO		
					left join [AKUZ].T_REGION r1 on r1.RegionID = ir1.Region
					left join [AKUZ].V_INSURER_REGION ir2 on ir2.InsurerRegionID = i2.OKATO		
					left join [AKUZ].T_REGION r2 on r2.RegionID = ir2.Region
					--referral
					left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
					left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
					left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
					left join [AKUZ].T_DIVISION dDir on  dDir.DivisionID= dir.DirectorDivision
					left join [AKUZ].T_BRANCH brDir on brDir.LpuID = dDir.Adress
		
					where mov.MovementID = @mainID

					FOR XML PATH(''),
					ROOT('ser:SendRouteRqst')
				) 	
		end

	end
	else
--__разбирается так же как и 13002
	begin
		;WITH xmlnamespaces ('https://services.rosminzdrav.ru/' as ser)
			SELECT @Result = 
			(
					SELECT 
					 '1.0'				                                   							as 'ser:HEAD/ser:VERSION'				 --версия
					,@Sender	                                             						as 'ser:HEAD/ser:SENDER'			     --МО-отправитель сведений
					,convert(varchar(10),CURRENT_TIMESTAMP, 20)            							as 'ser:HEAD/ser:D_EVENT'				 --Дата отправления сведений
					--,''												   							as 'ser:HEAD/ser:GUID_QUERY'			 --Идентификатор для №4			
					,
					(
						select 
						lm.LpuCode                                                					as 'ser:CODE_MO'				 --Принимающая направление МО
						,
						(
							select 
							@ChildRegistrID															as 'ser:GUID'		 --Уникальный код записи 						
							,lm.LpuCode 										   					as 'ser:DEST_MO'		 --Реестровый номер МО, в которую направлен/госпитализирован пациент
							,CASE 
								WHEN br.TFomsCode like '%.1' THEN null 
								ELSE br.TFomsCode
							 END												   					as 'ser:DEST_LPU'		 --Региональный код подразделения МО, в которое направлен/госпитализирован пациент
							
								--всегда экстренная
							,1																		as 'ser:FOR_POM'	         --Форма оказания медицинской помощи F003
							 ,
								(
								--всегда новорожденный
									select	
										1																as 'ser:OS_SLUCH' --Признак новорожденного
										, 
										 ''													as 'ser:FAM'	 --Фамилия пациента
										,''													as 'ser:IM'	 --Имя пациента
										,''													as 'ser:OT'	 --Отчество пациента
										,									
										
										(case when cr.Sex = 10301 then 1 else 2 end)					as 'ser:W'	     --Пол
										,convert(varchar(10), cr.DataBurn, 20)							as 'ser:DR'	 --День рождения пациента
										, 
										pat.LastName													as 'ser:FAM_P'	 --Фамилия представителя пациента
										,
										pat.FirstName													as 'ser:IM_P'	 --Имя представителя пациента
										, 
										pat.FatherName													as 'ser:OT_P'	 --Отчество представителя пациента
										, 
										convert(varchar(10), pat.Birthday, 20)							as 'ser:DR_P'	 --День рождения представителя пациента															as 'ser:DR_P'	 --День рождения представителя пациента

										,CASE pol.PolicyForm
											WHEN 4 THEN 2
											WHEN 1 THEN 1
											else 3
										END																as 'ser:VPOLIS'	 --Тип ДПФС
										,pol.Serial     												as 'ser:SPOLIS'	 --Серия ДПФС
										,pol.Number    									    			as 'ser:NPOLIS'	 --Номер ДПФС
										,ISNULL(i1.SmoCod, i2.SmoCod)					    			as 'ser:SMO'		 --Реестровый номер СМО
										--,''															as 'ser:SMO_NAM'	 --Наименование СМО - не заполнять если номер есть
										,substring (ISNULL(ISNULL(r1.Okato, r2.Okato),''),1,5)			as 'ser:ST_OKATO'	 --Регион страхования,		

										,cr.ChildNumber													as 'ser:NOVOR_NUM' --Признак новорожденного									
										,isnull(
										(select top 1 Number  
										from akuz.t_phone_number ph_n
										where ph_n.Patient = pat.PatientID) , '0')						as 'ser:CONTACT'	 --Контактный номер
									FOR XML PATH('ser:PACIENT'),
									TYPE
								)
				
		--___________________HOSP[]______________________________________________________________________________________________________________________________________
							,
							(
									select isnull(last_ooi.MKBCode,ooi.MKBCode)
																										as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
										--,convert(varchar(10),db.PreHospitalDate	, 20)					as 'ser:DATE_P'		--Плановая дата госпитализации
										,convert(varchar(10), mov_res.DatOut, 20)			as 'ser:DATE_V'		--Плановая дата выбытия
										,CASE resDiv.DivisionType
											WHEN 12808 THEN 2
											WHEN 12809 THEN 2
											WHEN 12810 THEN 2
										ELSE 1
										END																as 'ser:USL_OK'		--Условия оказания МП
										,sb.CodeMz														as 'ser:PROFK'		--Код профиля койки
										,resDiv.CodeMz													as 'ser:PODR'		--Код отделения

										,cast(res_mov.MedCardNumber as varchar(20)) + '-p'+	 cast(cr.ChildNumber as varchar(2))  		
																										as 'ser:N_MKSB'		--Номер карты стационарного больного
										,replace(convert(varchar(20), mov_res.DatIn, 20),' ','T')		as 'ser:DATE_1'		--Дата и время фактической госпитализации пациента
										,convert(varchar(10), mov_res.DatOut, 20)						as 'ser:DATE_2'		--Дата выбытия
										,ooi.MKBCode													as 'ser:DS'			--Диагноз при выбытии

										from [AKUZ].T_MOVEMENT_RESULT mov_res 
										join [AKUZ].T_OUTCOME_OF_INSPECTION ooi on mov_res.MedicalRecordID = ooi.MedicalRecordID
										left join [AKUZ].T_DIVISION resDiv on mov_res.Division = resDiv.DivisionID
										left join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov_res.StructureBed
										left join.[AKUZ].T_MOVEMENT res_mov on mov_res.MedicalcardRegistration = res_mov.MovementID
										left join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
										left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  mov_res.MedicalcardRegistration
											and last_res.DatOut = (select max(msrs.DatOut)
												from akuz.t_movement_result msrs where 
												msrs.MedicalcardRegistration =  mov_res.MedicalcardRegistration 
												and msrs.DatOut < mov_res.DatIn )
										left join  [AKUZ].T_OUTCOME_OF_INSPECTION last_ooi on last_res.MedicalRecordID = last_ooi.MedicalRecordID

										where mov_res.MedicalcardRegistration = @ChildRegistrID
									FOR XML PATH('ser:HOSP'),
									TYPE
							)
		--_____________________________________________________________________________________________________________________________________________________________
				,
							(

								select last_ooi.MKBCode												as 'ser:DS_P'		--Диагноз приемного отделения/диагноз при поступлении в отделение
									,convert(varchar(10),last_res.DatOut	, 20)					as 'ser:DATE_P'	--Плановая дата госпитализации
									,convert(varchar(10), modc.ExpectedDateOfDischarge, 20)			as 'ser:DATE_V'	--Плановая дата выбытия
									,

									CASE isnull (last_res_d.DivisionType,dc.DivisionType)
										WHEN 12808 THEN 2
										WHEN 12809 THEN 2
										WHEN 12810 THEN 2
									ELSE 1
									END																as 'ser:USL_OK'	--Условия оказания МП
									,sbc.CodeMz														as 'ser:PROFK'	--Код профиля койки
									,isnull (last_res_d.CodeMz,dc.CodeMz)							as 'ser:PODR'		--Код отделения
									,cast(modc.MedCardNumber as varchar(20)) + '-p'+	cast(cr.ChildNumber as varchar(2))  	
																									as 'ser:N_MKSB'	--Номер карты стационарного больного
									,replace(convert(varchar(20), 
									isnull(
									last_res.TimeIn,
									modc.MovementTime)
									, 20),' ','T')	
																									as 'ser:DATE_1'	--Дата и время фактической госпитализации пациента
									--,''															as 'ser:DATE_2'	--Предполагаемая дата выбытия
									--,''															as 'ser:DS'		--Диагноз при выбытии

									from [AKUZ].T_MOVEMENT modc
									left join [AKUZ].T_STRUCTURE_BED sbc on sbc.StructureBedID = modc.StructureBed
									left join AKUZ.T_DIVISION dc on dc.DivisionID = modc.Division
									left join akuz.T_EVENT_BASE modceb on modceb.EventID = modc.EmergencyEvent	
									left join akuz.t_movement_result last_res on last_res.MedicalcardRegistration =  modc.MovementID
										and last_res.MedicalRecordID = (select top 1 msrs.MedicalRecordID
											from akuz.t_movement_result msrs where msrs.MedicalcardRegistration =  modc.MovementID
											--and msrs.DatOut = max(msrs.DatOut)
											order by msrs.DatIn desc)
									left join  [AKUZ].T_OUTCOME_OF_INSPECTION last_ooi on last_res.MedicalRecordID = last_ooi.MedicalRecordID
									left join AKUZ.T_DIVISION  last_res_d on last_res.Division = last_res_d.DivisionID
									where modc.MovementID = @ChildRegistrID 
									--Последняя актуальная госпитализация
									and modceb.CloseDate is  null
								FOR XML PATH('ser:HOSP'),
								TYPE
							)
		--_____________________________________________________________________________________________________________________________________________________________

							FOR XML PATH('ser:REC'),
							TYPE
						)
						FOR XML PATH('ser:DATA'),
						TYPE
					)			
			
			from [AKUZ].T_MOVEMENT mov 
				join [AKUZ].T_STRUCTURE_BED sb on sb.StructureBedID = mov.StructureBed
				join [AKUZ].T_V002_PROFILE v0 on v0.V002ProfileID = sb.V002Profile
				join [AKUZ].T_CHILD_REGISTRATION cr on cr.MovementID = mov.MovementID
				join [AKUZ].T_MEDICALCARD_REGISTRATION mdc on mdc.MovementID = cr.MedicalcardRegistration
				--LPU
				join [AKUZ].T_DIVISION d on d.DivisionID = mov.Division
				join [AKUZ].T_BRANCH br on br.LpuID = d.Branch
				join [AKUZ].T_LPU_MAIN lm on br.LpuMain = lm.LpuID
				--patient
				join [AKUZ].T_EVENT_BASE eb on eb.EventID = mov.EmergencyEvent
				join [AKUZ].T_EMERGENCY_EVENT ee on ee.EventID = eb.EventID
				join [AKUZ].T_AMBULANCE_CARD ac on ac.AmbulanceCardID = eb.AmbulanceCard
				join [AKUZ].T_PATIENT pat on pat.PatientID = ac.Patient
				--diagnosis
				left join [AKUZ].T_RECEPTION_DIAGNOSIS rd on rd.EmergencyEvent = eb.EventID 
				left join [AKUZ].T_DIAGNOSIS rdrd on rdrd.MedicalRecordID =  rd.MedicalRecordID 
				--polis
				left join [AKUZ].T_POLICY pol on pol.PolicyID = [dbo].[fn_GetOmsPolicy](pat.PatientID,@resultDate)
				left join [AKUZ].T_INSURANCE_COMPANY ic on ic.OrganizationID = pol.InsuranceCompany
				left join [AKUZ].T_INSURER i1 on i1.InsurerID = ic.Insurer
				left join [AKUZ].T_INSURER i2 on i2.InsurerID = pol.Insurer
				left join [AKUZ].V_INSURER_REGION ir1 on ir1.InsurerRegionID = i1.OKATO		
				left join [AKUZ].T_REGION r1 on r1.RegionID = ir1.Region
				left join [AKUZ].V_INSURER_REGION ir2 on ir2.InsurerRegionID = i2.OKATO		
				left join [AKUZ].T_REGION r2 on r2.RegionID = ir2.Region
				--referral
				left join [AKUZ].T_DIRECTION dir on dir.DirectionID = eb.Direction
				left join [AKUZ].T_DIRECTION_BASE db on db.DirectionID = dir.DirectionID
				left join [AKUZ].T_LPU_MAIN lpDir on lpDir.LpuID = db.DirectorLpu
				left join [AKUZ].T_DIVISION dDir on  dDir.DivisionID= dir.DirectorDivision
				left join [AKUZ].T_BRANCH brDir on brDir.LpuID = dDir.Adress
		
				where mov.MovementID = @ChildRegistrID

				FOR XML PATH(''),
				ROOT('ser:SendRouteRqst')
			) 	
	end





--__________RSO______________________________________________________________________________________________________________________________--
		INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
		   ([RabbitSendObjectID]
		   ,[QueueName]
		   ,[Content]
		   ,[MessageID]
		   ,[Headers]
		   ,[ContentEncoding]
		   ,[Type]
		   ,[UserID]
		   ,[State]
		   ,[CreateDateTime]
		   ,[OwnerID])
		select
		   NewID()--(<RabbitSendObjectID, uniqueidentifier,>
		   ,@QueueName--,<QueueName, varchar(255),>
		   ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
		   ,null--,<MessageID, varchar(255),>
		   ,null--,<Headers, varchar(255),>
		   ,'utf-8'--,<ContentEncoding, varchar(255),>
		   ,'SendRouteRqst'--,<Type, varchar(255),>
		   ,null--,<UserID, varchar(255),>
		   ,0--,<State, int,>)
		   ,GETDATE()
		   ,@mainID	
END

GO

