SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_DrugRequest]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_DrugRequest]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2016-03-14
-- Description: Выгрузка/загрузка заявок по ДЛО в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_DrugRequest]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID uniqueidentifier = null,
    @QueueName varchar(100) = 'RDSSync',
    @dateTime datetime = null,
    @User uniqueidentifier = null
AS
BEGIN

SET NOCOUNT ON;

declare @Result varchar(max)
           ,@ContentEncoding varchar(255)
           ,@Type varchar(255)
           ,@OperationType varchar(255)
           ,@OwnerID uniqueidentifier
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)
           ,@FTSEND varchar(6)

    select @dateTime = ISNULL(@dateTime, GETDATE())
            -- Operation types
          ,@INSERT = 'Insert'
          ,@UPDATE = 'Update'
          ,@DELETE = 'Delete'
          ,@FTLOAD = 'FTLoad'
          ,@FTSEND = 'FTSend'

	IF (@Xml is null)
    BEGIN
	-- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'DRUG_REQUEST'
              ,@OwnerID = @ID

	--выясняем тип операции
        IF (@ID is null)
        BEGIN
            select @OperationType = @FTLOAD
        END
        ELSE IF(@IsNew = 1)
        BEGIN
            select @OperationType = @INSERT
        END
        ELSE IF (@IsNew = 0 and exists (select * from [RDS].T_DRUG_REQUEST dr where dr.DrugRequestID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [RDS].T_DRUG_REQUEST dr where dr.DrugRequestID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result = 
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'DrugRequestID'
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            ) 
        END

		-- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN

		-- собираем все DRUG_REQUEST
            SELECT @Result = 
            ( 
				select @FTLOAD                      as 'header/TYPE'
                      ,(
						select dr.DrugRequestID								as 'DrugRequestID'
							  ,convert(varchar(10), dr.RequestDate, 20)		as 'RequestDate'
							  ,dr.Number									as 'Number'
							  ,dr.Active									as 'Active'
							  ,convert(varchar(10), dr.BeginPeriod, 20)		as 'BeginPeriod'
							  ,convert(varchar(10), dr.EndPeriod, 20)		as 'EndPeriod'
							  ,dr.Name										as 'Name'
							  ,dr.ImportFileName							as 'ImportFileName'
							  ,dr.Comment									as 'Comment'
							  ,lp.MCOD										as 'LpuMain'
							  ,drM.DrugRequestID							as 'DrugRequestID'
							  ,dr.RequestType								as 'RequestType'
							  ,dr.[Status]									as 'Status'
							  ,dr.[Limit]									as 'Limit'
							  ,br.BranchCode								as 'BranchCode'
							  ,dr.Additional								as 'Additional'
							  ,convert(varchar(10), dr.LastEditDate, 20)	as 'LastEditDate'
							  ,er.CodeEHR								    as 'EhrRusEj36pmtjqk'
							  ,rb.CurrVersion							    as 'EhrRusEj36pmtjqkVer'
							  ,erVzn.IdEHR								as 'EhrRusVzn17'
							  ,rbVzn.CurrVersion							as 'EhrRusVzn17Ver'
							  --собираем DRUG_REQUEST_COMMENTS
							  ,(	select 
										drc.DrugRequestCommentsID			as 'DrugRequestCommentsID'
										,drc.Comment						as 'Comment'
										,drc.RequestCode					as 'RequestCode'
										,drc.Processed						as 'Processed'
										,drc.UserProcessed					as 'UserProcessed'
										,idd.IdentityName					as 'Worker'
										,drc.DrugRequest					as 'DrugRequest'
									from [RDS].T_DRUG_REQUEST_COMMENTS drc
										join RDS.T_USERS u on u.UserID = drc.Worker
										join VCLib.T_USERS vu on vu.UserId = u.UserId
										join VCLib.T_IDENTITY idd on vu.[Identity] = idd.IdentityID
									where drc.DrugRequest = dr.DrugRequestID
									FOR XML PATH('DRUG_REQUEST_COMMENTS'),
									TYPE)
							  --собираем DRUG_DEMAND
							  ,(
									select	 dd.DrugDemandID				as 'DrugDemandID'
											,dd.DemandOnPeriod				as 'DemandOnPeriod'
											,dd.RequestQuantity				as 'RequestQuantity'
											,dd.RequestCode					as 'RequestCode'
											,dd.LSFO						as 'LSFO'
											,dd.Comment						as 'Comment'
											,dd.DrugRequest					as 'DrugRequest'
											,dd.SectionCode					as 'SectionCode'
											,dd.PersRequest					as 'PersRequest'
											,dd.DrugApplicationListItem		as 'RequestCatalog'
											--собираем PHARMACY_DRUG_DEMAND
											,(
												select pdd.PharmacyDrugDemandID		as 'PharmacyDrugDemandID'
													  ,pdd.DemandOnPeriod			as 'DemandOnPeriod'
													  ,pdd.RequestQuantity			as 'RequestQuantity'
													  ,pdd.Mont1					as 'Mont1'
													  ,pdd.Mont2					as 'Mont2'
													  ,pdd.Mont3					as 'Mont3'
													  ,pdd.Mont4					as 'Mont4'
													  ,pdd.Mont5					as 'Mont5'
													  ,pdd.Mont6					as 'Mont6'
													  ,pdd.Mont7					as 'Mont7'
													  ,pdd.Mont8					as 'Mont8'
													  ,pdd.Mont9					as 'Mont9'
													  ,pdd.Mont10					as 'Mont10'
													  ,pdd.Mont11					as 'Mont11'
													  ,pdd.Mont12					as 'Mont12'
													  ,pdd.DrugDemand				as 'DrugDemand'
													  ,pdd.Pharmacy					as 'Pharmacy'
													  --собираем PATIENT_DRUG_DEMAND
													  ,(
														select pat.PatientDrugDemandID		as 'PatientDrugDemandID'
															  ,pat.PharmacyDrugDemand		as 'PharmacyDrugDemand'
															  ,pat.DemandOnPeriod			as 'DemandOnPeriod'
															  ,pat.RequestQuantity			as 'RequestQuantity'
															  ,pat.Mont1					as 'Mont1'
															  ,pat.Mont2					as 'Mont2'
															  ,pat.Mont3					as 'Mont3'
															  ,pat.Mont4					as 'Mont4'
															  ,pat.Mont5					as 'Mont5'
															  ,pat.Mont6					as 'Mont6'
															  ,pat.Mont7					as 'Mont7'
															  ,pat.Mont8					as 'Mont8'
															  ,pat.Mont9					as 'Mont9'
															  ,pat.Mont10					as 'Mont10'
															  ,pat.Mont11					as 'Mont11'
															  ,pat.Mont12					as 'Mont12'
															  ,pat.Birthday					as 'Birthday'
															  ,pat.FirstName				as 'FirstName'
															  ,pat.FatherName				as 'FatherName'
															  ,pat.LastName					as 'LastName'
															  ,eSex.Code					as 'Sex'
															  ,b.BenefitCode				as 'BenefitCode'
															  ,b.BenefitViews				as 'BenefitsType'
															  ,pat.MKB						as 'MKB'
															  ,pat.Comment					as 'Comment'
															  ,pat.Signa					as 'Signa'
															  ,pat.Post						as 'Post'
															  ,pat.Snils					as 'Snils'
															  ,case when pat.DocSeries = '0' then null
															  		else pat.DocSeries 
															   end							as 'DocSeries'
															  ,case when pat.DocNumber = '0' then null
															  		else pat.DocNumber 
															   end							as 'DocNumber'
															  ,convert(varchar(10), pat.DocIssueDate, 20)             as 'DocIssueDate'
															  ,pat.DocIssueOrg				as 'DocIssueOrg'
															  ,eDt.Code						as 'DocType'
															  ,pat.OmsNumber				as 'PolicyNumber'
															  ,pat.OmsSerial				as 'PolicySerial'
														from [RDS].T_PATIENT_DRUG_DEMAND pat
															left join [VCLib].T_ENUMS eDt on eDt.EnumID = pat.DocType
															left join [RDS].T_BENEFITS b on b.BenefitsID = pat.Benefits
															left join [VCLib].T_ENUMS eSex on eSex.EnumID = pat.Sex
														where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
														FOR XML PATH('PATIENT_DRUG_DEMAND'),
														TYPE
													   )
												from [RDS].T_PHARMACY_DRUG_DEMAND pdd
												where dd.DrugDemandID = pdd.DrugDemand
												FOR XML PATH('PHARMACY_DRUG_DEMAND'),
												TYPE
											 )
									from [RDS].T_DRUG_DEMAND dd
									where dd.DrugRequest = dr.DrugRequestID
									FOR XML PATH('DRUG_DEMAND'),
									TYPE
							   )
						from [RDS].T_DRUG_REQUEST dr
							join [RDS].T_LPU_MAIN lp on lp.MCOD = dr.LpuMain
							left join [RDS].T_BRANCH br on br.BranchID = dr.Branch
							left join [RDS].T_DRUG_REQUEST drM on drM.DrugRequestID = dr.DrugRequestMain
							left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = dr.EhrRusEj36pmtjqk
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rb ON rb.EhrRusRefBooksID = er.EhrRusRefBooks
							left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = dr.EhrRusVzn17
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
						FOR XML PATH('DRUG_REQUEST'),
						TYPE
						) as 'body/DRUG_REQUEST_LIST'
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            )

		END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

		-- собираем DRUG_REQUEST
            SELECT @Result = 
            ( 
                select @OperationType								as 'header/TYPE'
                      ,@ID											as 'DrugRequestID'
					  ,dr.RequestDate								as 'RequestDate'
					  ,dr.Number									as 'Number'
					  ,dr.Active									as 'Active'
					  ,dr.BeginPeriod								as 'BeginPeriod'
					  ,dr.EndPeriod									as 'EndPeriod'
					  ,dr.Name										as 'Name'
					  ,dr.ImportFileName							as 'ImportFileName'
					  ,dr.Comment									as 'Comment'
					  ,lp.MCOD										as 'LpuMain'
					  ,drM.DrugRequestID							as 'DrugRequestID'
					  ,dr.RequestType								as 'RequestType'
					  ,dr.[Status]									as 'Status'
					  ,dr.[Limit]									as 'Limit'
					  ,br.BranchCode								as 'BranchCode'
					  ,dr.Additional								as 'Additional'
					  ,convert(varchar(10), dr.LastEditDate, 20)	as 'LastEditDate'
					  ,er.CodeEHR									as 'EhrRusEj36pmtjqk'
					  ,rb.CurrVersion								as 'EhrRusEj36pmtjqkVer'
					  ,erVzn.IdEHR								as 'EhrRusVzn17'
					  ,rbVzn.CurrVersion							as 'EhrRusVzn17Ver'
					   --собираем DRUG_REQUEST_COMMENTS
					   ,(select 
							drc.DrugRequestCommentsID			as 'DrugRequestCommentsID'
							,drc.Comment						as 'Comment'
							,drc.RequestCode					as 'RequestCode'
							,drc.Processed						as 'Processed'
							,drc.UserProcessed					as 'UserProcessed'
							,idd.IdentityName					as 'Worker'
							,drc.DrugRequest					as 'DrugRequest'
						from [RDS].T_DRUG_REQUEST_COMMENTS drc
							join RDS.T_USERS u on u.UserID = drc.[Users]
							join VCLib.T_USERS vu on vu.UserId = u.UserId
							join VCLib.T_IDENTITY idd on idd.IdentityID = vu.[Identity]
						where drc.DrugRequest = dr.DrugRequestID
						FOR XML PATH('DRUG_REQUEST_COMMENTS'),
						TYPE)
					  --собираем DRUG_DEMAND
					  ,(
							select	 dd.DrugDemandID				as 'DrugDemandID'
									,dd.DemandOnPeriod				as 'DemandOnPeriod'
									,dd.RequestQuantity				as 'RequestQuantity'
									,dd.RequestCode					as 'RequestCode'
									,dd.LSFO						as 'LSFO'
									,dd.Comment						as 'Comment'
									,dd.DrugRequest					as 'DrugRequest'
									,dd.SectionCode					as 'SectionCode'
									,dd.PersRequest					as 'PersRequest'
									,dd.DrugApplicationListItem		as 'RequestCatalog'
									--собираем PHARMACY_DRUG_DEMAND
											,(
												select pdd.PharmacyDrugDemandID		as 'PharmacyDrugDemandID'
													  ,pdd.DemandOnPeriod			as 'DemandOnPeriod'
													  ,pdd.RequestQuantity			as 'RequestQuantity'
													  ,pdd.Mont1					as 'Mont1'
													  ,pdd.Mont2					as 'Mont2'
													  ,pdd.Mont3					as 'Mont3'
													  ,pdd.Mont4					as 'Mont4'
													  ,pdd.Mont5					as 'Mont5'
													  ,pdd.Mont6					as 'Mont6'
													  ,pdd.Mont7					as 'Mont7'
													  ,pdd.Mont8					as 'Mont8'
													  ,pdd.Mont9					as 'Mont9'
													  ,pdd.Mont10					as 'Mont10'
													  ,pdd.Mont11					as 'Mont11'
													  ,pdd.Mont12					as 'Mont12'
													  ,pdd.DrugDemand				as 'DrugDemand'
													  ,pdd.Pharmacy					as 'Pharmacy'
													  --собираем PATIENT_DRUG_DEMAND
													  ,(
														select pat.PatientDrugDemandID		as 'PatientDrugDemandID'
															  ,pat.PharmacyDrugDemand		as 'PharmacyDrugDemand'
															  ,pat.DemandOnPeriod			as 'DemandOnPeriod'
															  ,pat.RequestQuantity			as 'RequestQuantity'
															  ,pat.Mont1					as 'Mont1'
															  ,pat.Mont2					as 'Mont2'
															  ,pat.Mont3					as 'Mont3'
															  ,pat.Mont4					as 'Mont4'
															  ,pat.Mont5					as 'Mont5'
															  ,pat.Mont6					as 'Mont6'
															  ,pat.Mont7					as 'Mont7'
															  ,pat.Mont8					as 'Mont8'
															  ,pat.Mont9					as 'Mont9'
															  ,pat.Mont10					as 'Mont10'
															  ,pat.Mont11					as 'Mont11'
															  ,pat.Mont12					as 'Mont12'
															  ,pat.Birthday					as 'Birthday'
															  ,pat.FirstName				as 'FirstName'
															  ,pat.FatherName				as 'FatherName'
															  ,pat.LastName					as 'LastName'
															  ,eSex.Code					as 'Sex'
															  ,b.BenefitCode				as 'BenefitCode'
															  ,b.BenefitViews				as 'BenefitsType'
															  ,pat.MKB						as 'MKB'
															  ,pat.Comment					as 'Comment'
															  ,pat.Signa					as 'Signa'
															  ,pat.Post						as 'Post'
															  ,pat.Snils					as 'Snils'
															  ,case when pat.DocSeries = '0' then null
															  		else pat.DocSeries 
															   end							as 'DocSeries'
															  ,case when pat.DocNumber = '0' then null
															  		else pat.DocNumber 
															   end							as 'DocNumber'
															  ,convert(varchar(10), pat.DocIssueDate, 20)              as 'DocIssueDate'
															  ,pat.DocIssueOrg				as 'DocIssueOrg'
															  ,eDt.Code						as 'DocType'
															  ,pat.OmsNumber				as 'PolicyNumber'
															  ,pat.OmsSerial				as 'PolicySerial'
														from [RDS].T_PATIENT_DRUG_DEMAND pat
															left join [VCLib].T_ENUMS eDt on eDt.EnumID = pat.DocType
															left join [RDS].T_BENEFITS b on b.BenefitsID = pat.Benefits
															left join [VCLib].T_ENUMS eSex on eSex.EnumID = pat.Sex
														where pat.PharmacyDrugDemand = pdd.PharmacyDrugDemandID
														FOR XML PATH('PATIENT_DRUG_DEMAND'),
														TYPE
													   )
												from [RDS].T_PHARMACY_DRUG_DEMAND pdd
												where dd.DrugDemandID = pdd.DrugDemand
												FOR XML PATH('PHARMACY_DRUG_DEMAND'),
												TYPE
											 )
							from [RDS].T_DRUG_DEMAND dd
							where dd.DrugRequest = dr.DrugRequestID  and dd.Comment is not null
							FOR XML PATH('DRUG_DEMAND'),
							TYPE
					   )
                from [RDS].T_DRUG_REQUEST dr
					join [RDS].T_LPU_MAIN lp on lp.MCOD = dr.LpuMain
					left join [RDS].T_BRANCH br on br.BranchID = dr.Branch
					left join [RDS].T_DRUG_REQUEST drM on drM.DrugRequestID = dr.DrugRequestMain
					left join EHR.T_EHR_RUS_EJ36PMTJQK er on er.EhrRusID = dr.EhrRusEj36pmtjqk
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rb ON rb.EhrRusRefBooksID = er.EhrRusRefBooks
					left join EHR.T_EHR_RUS_VZN17 erVzn on erVzn.EhrRusID = dr.EhrRusVzn17
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS rbVzn ON rbVzn.EhrRusRefBooksID = erVzn.EhrRusRefBooks
                where dr.DrugRequestID = @ID
                FOR XML PATH(''),
                ROOT('DRUG_REQUEST')
            ) 

		END

		declare @LpuCode varchar(6)
		select @LpuCode = LpuMain
		from [RDS].T_DRUG_REQUEST 
		where DrugRequestID = @ID

		-- Отправка
        INSERT INTO [RDS].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,s.ServerName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
		   from [RDS].T_SYNC_SERVERS s
			where s.ServerType <> 1 and  
			exists (select * 
						from [RDS].T_SYNC_SERVERS s1
							join [RDS].T_SERVERS_LINK sl on sl.SyncServers = s1.SyncServersID
							join [RDS].T_SERVERS_LINK_LPU ll on ll.ServersLinkID = sl.ServersLinkID
							join [RDS].T_LPU_MAIN lp on lp.MCOD = ll.LpuMain
						where lp.MCOD = @LpuCode and s1.SyncServersID = s.SyncServersID)
	END
	ELSE 
    IF (@Xml is not null)
    BEGIN
		
		-- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
        from @Xml.nodes('DRUG_REQUEST') T(c)

		-- Generate table #DRUG_REQUEST_LIST
        select t.c.value('./DrugRequestID[1]', 'uniqueidentifier')			as [DrugRequestID]
			  ,convert(datetime, t.c.value('./RequestDate[1]', 'varchar(10)'), 20)					as [RequestDate]
              ,t.c.value('./Active[1]', 'bit')								as [Active]
			  ,t.c.value('./Number[1]', 'int')								as [Number]
              ,convert(datetime, t.c.value('./BeginPeriod[1]', 'varchar(10)'), 20)					as [BeginPeriod]
              ,convert(datetime, t.c.value('./EndPeriod[1]', 'varchar(10)'), 20)						as [EndPeriod]
              ,t.c.value('./Name[1]', 'varchar(255)')						as [Name]
              ,t.c.value('./ImportFileName[1]', 'varchar(255)')				as [ImportFileName]
              ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
              ,t.c.value('./LpuMain[1]', 'varchar(6)')						as [LpuMain]
              ,t.c.value('./DrugRequestMain[1]', 'uniqueidentifier')		as [DrugRequestMain]
              ,t.c.value('./RequestType[1]', 'int')							as [RequestType]
			  ,t.c.value('./Status[1]', 'int')								as [Status]
			  ,t.c.value('./Limit[1]', 'decimal(31, 10)')					as [Limit]
			  ,t.c.value('./BranchCode[1]', 'varchar(16)')					as [BranchCode]
			  ,convert(datetime, t.c.value('./LastEditDate[1]', 'varchar(10)'), 20)			as [LastEditDate]
              ,t.c.value('./Additional[1]', 'bit')							as [Additional]
              ,t.c.value('./EhrRusEj36pmtjqk[1]', 'varchar(50)')			as [EhrRusEj36pmtjqk]
              ,t.c.value('./EhrRusEj36pmtjqkVer[1]', 'varchar(50)')			as [EhrRusEj36pmtjqkVer]
              ,t.c.value('./EhrRusVzn17[1]', 'varchar(50)')					as [EhrRusVzn17]
              ,t.c.value('./EhrRusVzn17Ver[1]', 'varchar(50)')				as [EhrRusVzn17Ver]
            INTO #DRUG_REQUEST_LIST
        from @Xml.nodes('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST') T(c)
        where @OperationType = @FTSEND

		-- Generate table #DRUG_REQUEST
        select t.c.value('./DrugRequestID[1]', 'uniqueidentifier')			as [DrugRequestID]
			  ,convert(datetime, t.c.value('./RequestDate[1]', 'varchar(10)'), 20)					as [RequestDate]
              ,t.c.value('./Active[1]', 'bit')								as [Active]
			  ,t.c.value('./Number[1]', 'int')								as [Number]
              ,convert(datetime, t.c.value('./BeginPeriod[1]', 'varchar(10)'), 20)					as [BeginPeriod]
              ,convert(datetime, t.c.value('./EndPeriod[1]', 'varchar(10)'), 20)						as [EndPeriod]
              ,t.c.value('./Name[1]', 'varchar(255)')						as [Name]
              ,t.c.value('./ImportFileName[1]', 'varchar(255)')				as [ImportFileName]
              ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
              ,t.c.value('./LpuMain[1]', 'varchar(6)')						as [LpuMain]
              ,t.c.value('./DrugRequestMain[1]', 'uniqueidentifier')		as [DrugRequestMain]
              ,t.c.value('./RequestType[1]', 'int')							as [RequestType]
			  ,t.c.value('./Status[1]', 'int')								as [Status]
			  ,t.c.value('./Limit[1]', 'decimal(31, 10)')					as [Limit]
			  ,t.c.value('./BranchCode[1]', 'varchar(16)')					as [BranchCode]
			  ,convert(datetime, t.c.value('./LastEditDate[1]', 'varchar(16)'), 20)				as [LastEditDate]
              ,t.c.value('./Additional[1]', 'bit')							as [Additional]
              ,t.c.value('./EhrRusEj36pmtjqk[1]', 'varchar(50)')			as [EhrRusEj36pmtjqk]
              ,t.c.value('./EhrRusEj36pmtjqkVer[1]', 'varchar(50)')			as [EhrRusEj36pmtjqkVer]
              ,t.c.value('./EhrRusVzn17[1]', 'varchar(50)')					as [EhrRusVzn17]
              ,t.c.value('./EhrRusVzn17Ver[1]', 'varchar(50)')				as [EhrRusVzn17Ver]
            INTO #DRUG_REQUEST
        from @Xml.nodes('DRUG_REQUEST') T(c)
        where @OperationType <> @FTSEND

		-- Generate table #DRUG_DEMAND_LIST
        select t.c.value('./DrugDemandID[1]', 'uniqueidentifier')			as [DrugDemandID]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./RequestCode[1]', 'int')							as [RequestCode]
			  ,t.c.value('./LSFO[1]', 'int')								as [LSFO]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')			as [DrugRequest]
			  ,t.c.value('./SectionCode[1]', 'varchar(255)')				as [SectionCode]
              ,t.c.value('./PersRequest[1]', 'bit')							as [PersRequest]
              ,t.c.value('./RequestCatalog[1]', 'uniqueidentifier')			as [RequestCatalog]
		INTO #DRUG_DEMAND_LIST
        from @Xml.nodes('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND') T(c)
        where @OperationType = @FTSEND

		-- Generate table #DRUG_DEMAND
        select t.c.value('./DrugDemandID[1]', 'uniqueidentifier')			as [DrugDemandID]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./RequestCode[1]', 'int')							as [RequestCode]
			  ,t.c.value('./LSFO[1]', 'int')								as [LSFO]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')			as [DrugRequest]
			  ,t.c.value('./SectionCode[1]', 'varchar(255)')				as [SectionCode]
              ,t.c.value('./PersRequest[1]', 'bit')							as [PersRequest]
              ,t.c.value('./RequestCatalog[1]', 'uniqueidentifier')			as [RequestCatalog]
		INTO #DRUG_DEMAND
        from @Xml.nodes('DRUG_REQUEST/DRUG_DEMAND') T(c)
        where @OperationType <> @FTSEND

		-- Generate table #PHARMACY_DRUG_DEMAND_LIST
        select t.c.value('./PharmacyDrugDemandID[1]', 'uniqueidentifier')	as [PharmacyDrugDemandID]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./Mont1[1]', 'decimal(12, 2)')					as [Mont1]
			  ,t.c.value('./Mont2[1]', 'decimal(12, 2)')					as [Mont2]
			  ,t.c.value('./Mont3[1]', 'decimal(12, 2)')					as [Mont3]
			  ,t.c.value('./Mont4[1]', 'decimal(12, 2)')					as [Mont4]
			  ,t.c.value('./Mont5[1]', 'decimal(12, 2)')					as [Mont5]
			  ,t.c.value('./Mont6[1]', 'decimal(12, 2)')					as [Mont6]
			  ,t.c.value('./Mont7[1]', 'decimal(12, 2)')					as [Mont7]
			  ,t.c.value('./Mont8[1]', 'decimal(12, 2)')					as [Mont8]
			  ,t.c.value('./Mont9[1]', 'decimal(12, 2)')					as [Mont9]
			  ,t.c.value('./Mont10[1]', 'decimal(12, 2)')					as [Mont10]
			  ,t.c.value('./Mont11[1]', 'decimal(12, 2)')					as [Mont11]
			  ,t.c.value('./Mont12[1]', 'decimal(12, 2)')					as [Mont12]
			  ,t.c.value('./Pharmacy[1]', 'int')							as [Pharmacy]
			  ,t.c.value('./DrugDemand[1]', 'uniqueidentifier')				as [DrugDemand]
		INTO #PHARMACY_DRUG_DEMAND_LIST
        from @Xml.nodes('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND') T(c)
        where @OperationType = @FTSEND

		-- Generate table #PHARMACY_DRUG_DEMAND
        select t.c.value('./PharmacyDrugDemandID[1]', 'uniqueidentifier')	as [PharmacyDrugDemandID]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./Mont1[1]', 'decimal(12, 2)')					as [Mont1]
			  ,t.c.value('./Mont2[1]', 'decimal(12, 2)')					as [Mont2]
			  ,t.c.value('./Mont3[1]', 'decimal(12, 2)')					as [Mont3]
			  ,t.c.value('./Mont4[1]', 'decimal(12, 2)')					as [Mont4]
			  ,t.c.value('./Mont5[1]', 'decimal(12, 2)')					as [Mont5]
			  ,t.c.value('./Mont6[1]', 'decimal(12, 2)')					as [Mont6]
			  ,t.c.value('./Mont7[1]', 'decimal(12, 2)')					as [Mont7]
			  ,t.c.value('./Mont8[1]', 'decimal(12, 2)')					as [Mont8]
			  ,t.c.value('./Mont9[1]', 'decimal(12, 2)')					as [Mont9]
			  ,t.c.value('./Mont10[1]', 'decimal(12, 2)')					as [Mont10]
			  ,t.c.value('./Mont11[1]', 'decimal(12, 2)')					as [Mont11]
			  ,t.c.value('./Mont12[1]', 'decimal(12, 2)')					as [Mont12]
			  ,t.c.value('./Pharmacy[1]', 'int')							as [Pharmacy]
			  ,t.c.value('./DrugDemand[1]', 'uniqueidentifier')				as [DrugDemand]
		INTO #PHARMACY_DRUG_DEMAND
        from @Xml.nodes('DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND') T(c)
        where @OperationType <> @FTSEND
		
		-- Generate table #PATIENT_DRUG_DEMAND_LIST
		select t.c.value('./PatientDrugDemandID[1]', 'uniqueidentifier')	as PatientDrugDemandID
			  ,t.c.value('./PharmacyDrugDemand[1]', 'uniqueidentifier')		as [PharmacyDrugDemand]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./Mont1[1]', 'decimal(12, 2)')					as [Mont1]
			  ,t.c.value('./Mont2[1]', 'decimal(12, 2)')					as [Mont2]
			  ,t.c.value('./Mont3[1]', 'decimal(12, 2)')					as [Mont3]
			  ,t.c.value('./Mont4[1]', 'decimal(12, 2)')					as [Mont4]
			  ,t.c.value('./Mont5[1]', 'decimal(12, 2)')					as [Mont5]
			  ,t.c.value('./Mont6[1]', 'decimal(12, 2)')					as [Mont6]
			  ,t.c.value('./Mont7[1]', 'decimal(12, 2)')					as [Mont7]
			  ,t.c.value('./Mont8[1]', 'decimal(12, 2)')					as [Mont8]
			  ,t.c.value('./Mont9[1]', 'decimal(12, 2)')					as [Mont9]
			  ,t.c.value('./Mont10[1]', 'decimal(12, 2)')					as [Mont10]
			  ,t.c.value('./Mont11[1]', 'decimal(12, 2)')					as [Mont11]
			  ,t.c.value('./Mont12[1]', 'decimal(12, 2)')					as [Mont12]
			  ,convert(datetime, T.c.value('./Birthday[1]', 'varchar(10)'), 20)	as Birthday
			  ,t.c.value('./FirstName[1]', 'varchar(40)')					as [FirstName]
			  ,t.c.value('./FatherName[1]', 'varchar(40)')					as [FatherName]
			  ,t.c.value('./LastName[1]', 'varchar(40)')					as [LastName]
			  ,t.c.value('./BenefitCode[1]', 'int')							as [BenefitCode]
			  ,t.c.value('./BenefitsType[1]', 'int')						as [BenefitType]
			  ,t.c.value('./MKB[1]', 'varchar(10)')							as [MKB]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
			  ,t.c.value('./Signa[1]', 'varchar(256)')						as [Signa]
			  ,t.c.value('./Post[1]', 'varchar(256)')						as [Post]
			  ,t.c.value('./Snils[1]', 'varchar(14)')						as [Snils]
			  ,t.c.value('./DocSeries[1]', 'varchar(16)')					as [DocSeries]
			  ,t.c.value('./DocNumber[1]', 'varchar(16)')					as [DocNumber]
			  ,convert(datetime, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 20)	as DocIssueDate
			  ,t.c.value('./DocIssueOrg[1]', 'varchar(14)')						as [DocIssueOrg]
			  ,t.c.value('./DocType[1]', 'int')								as [DocType]
			  ,t.c.value('./PolicyNumber[1]', 'varchar(32)')				as [OmsNumber]
			  ,t.c.value('./PolicySerial[1]', 'varchar(12)')				as [OmsSerial]
			  ,T.c.value('./Sex[1]', 'varchar(255)')						as [Sex]
		INTO #PATIENT_DRUG_DEMAND_LIST
        from @Xml.nodes('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND') T(c)
        where @OperationType = @FTSEND
		
		-- Generate table #PATIENT_DRUG_DEMAND
		select t.c.value('./PatientDrugDemandID[1]', 'uniqueidentifier')	as PatientDrugDemandID
			  ,t.c.value('./PharmacyDrugDemand[1]', 'uniqueidentifier')		as [PharmacyDrugDemand]
			  ,t.c.value('./DemandOnPeriod[1]', 'decimal(12, 2)')			as [DemandOnPeriod]
			  ,t.c.value('./RequestQuantity[1]', 'decimal(12, 2)')			as [RequestQuantity]
			  ,t.c.value('./Mont1[1]', 'decimal(12, 2)')					as [Mont1]
			  ,t.c.value('./Mont2[1]', 'decimal(12, 2)')					as [Mont2]
			  ,t.c.value('./Mont3[1]', 'decimal(12, 2)')					as [Mont3]
			  ,t.c.value('./Mont4[1]', 'decimal(12, 2)')					as [Mont4]
			  ,t.c.value('./Mont5[1]', 'decimal(12, 2)')					as [Mont5]
			  ,t.c.value('./Mont6[1]', 'decimal(12, 2)')					as [Mont6]
			  ,t.c.value('./Mont7[1]', 'decimal(12, 2)')					as [Mont7]
			  ,t.c.value('./Mont8[1]', 'decimal(12, 2)')					as [Mont8]
			  ,t.c.value('./Mont9[1]', 'decimal(12, 2)')					as [Mont9]
			  ,t.c.value('./Mont10[1]', 'decimal(12, 2)')					as [Mont10]
			  ,t.c.value('./Mont11[1]', 'decimal(12, 2)')					as [Mont11]
			  ,t.c.value('./Mont12[1]', 'decimal(12, 2)')					as [Mont12]
			  ,convert(datetime, t.c.value('./Birthday[1]', 'varchar(10)'), 20)						as [Birthday]
			  ,t.c.value('./FirstName[1]', 'varchar(40)')					as [FirstName]
			  ,t.c.value('./FatherName[1]', 'varchar(40)')					as [FatherName]
			  ,t.c.value('./LastName[1]', 'varchar(40)')					as [LastName]
			  ,t.c.value('./BenefitCode[1]', 'int')							as [BenefitCode]
			  ,t.c.value('./BenefitsType[1]', 'int')						as [BenefitType]
			  ,t.c.value('./MKB[1]', 'varchar(10)')							as [MKB]
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as [Comment]
			  ,t.c.value('./Signa[1]', 'varchar(256)')						as [Signa]
			  ,t.c.value('./Post[1]', 'varchar(256)')						as [Post]
			  ,t.c.value('./Snils[1]', 'varchar(14)')						as [Snils]
			  ,t.c.value('./DocSeries[1]', 'varchar(16)')					as [DocSeries]
			  ,t.c.value('./DocNumber[1]', 'varchar(16)')					as [DocNumber]
			  ,convert(datetime, T.c.value('./DocIssueDate[1]', 'varchar(10)'), 20)	as DocIssueDate
			  ,t.c.value('./DocIssueOrg[1]', 'varchar(14)')						as [DocIssueOrg]
			  ,t.c.value('./DocType[1]', 'int')								as [DocType]
			  ,t.c.value('./PolicyNumber[1]', 'varchar(32)')				as [OmsNumber]
			  ,t.c.value('./PolicySerial[1]', 'varchar(12)')				as [OmsSerial]
			  ,T.c.value('./Sex[1]', 'varchar(255)')						as [Sex]
		INTO #PATIENT_DRUG_DEMAND
        from @Xml.nodes('DRUG_REQUEST/DRUG_DEMAND/PHARMACY_DRUG_DEMAND/PATIENT_DRUG_DEMAND') T(c)
        where @OperationType <> @FTSEND

		
		-- Generate table #DRUG_REQUEST_COMMENTS_LIST
		select t.c.value('./DrugRequestCommentsID[1]', 'uniqueidentifier')	as DrugRequestCommentsID
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as Comment
			  ,t.c.value('./RequestCode[1]', 'int')							as RequestCode
			  ,t.c.value('./Processed[1]', 'int')							as Processed
			  ,t.c.value('./UserProcessed[1]', 'int')						as UserProcessed
			  ,t.c.value('./Worker[1]', 'varchar(500)')						as Worker
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')			as DrugRequest
		INTO #DRUG_REQUEST_COMMENTS_LIST
        from @Xml.nodes('DRUG_REQUEST/body/DRUG_REQUEST_LIST/DRUG_REQUEST/DRUG_REQUEST_COMMENTS') T(c)
        where @OperationType = @FTSEND
		
		-- Generate table #DRUG_REQUEST_COMMENTS
		select t.c.value('./DrugRequestCommentsID[1]', 'uniqueidentifier')	as DrugRequestCommentsID
			  ,t.c.value('./Comment[1]', 'varchar(8000)')					as Comment
			  ,t.c.value('./RequestCode[1]', 'int')							as RequestCode
			  ,t.c.value('./Processed[1]', 'int')							as Processed
			  ,t.c.value('./UserProcessed[1]', 'int')						as UserProcessed
			  ,t.c.value('./Worker[1]', 'varchar(500)')						as Worker
			  ,t.c.value('./DrugRequest[1]', 'uniqueidentifier')			as DrugRequest
		INTO #DRUG_REQUEST_COMMENTS
        from @Xml.nodes('DRUG_REQUEST/DRUG_REQUEST_COMMENTS') T(c)
        where @OperationType <> @FTSEND

		-- Обработка операции
        if (@OperationType = @FTSEND)
        BEGIN

			update dr
			        set dr.[RequestDate]		= drl.[RequestDate]
			           ,dr.[LpuMain]			= ISNULL(lp.MCOD, tt.LpuMain)
			           ,dr.[Active]				= drl.[Active]
			           ,dr.[DrugRequestMain]	= drl.[DrugRequestMain]
			           ,dr.[Number]				= drl.[Number]
			           ,dr.[RequestType]		= drl.[RequestType]
			           ,dr.[ImportFileName]		= drl.[ImportFileName]
			           ,dr.[Name]				= drl.[Name]
			           ,dr.[EndPeriod]			= drl.[EndPeriod]
			           ,dr.[BeginPeriod]		= drl.[BeginPeriod]
			           ,dr.[Status]				= drl.[Status]
			           ,dr.[Comment]			= drl.[Comment]
			           ,dr.[Limit]				= drl.[Limit]
			           ,dr.[Branch]				= tt.BranchID
					   ,dr.LastEditDate			= ISNULL(drl.LastEditDate, getdate())
			           ,dr.Additional			= ISNULL(drl.Additional,0)
			           ,dr.[EhrRusEj36pmtjqk]	= ehr.EhrRusID
			           ,dr.[EhrRusVzn17]		= ehrVzn.EhrRusID
			from [RDS].T_DRUG_REQUEST dr
				join #DRUG_REQUEST_LIST drl on drl.[DrugRequestID] = dr.DrugRequestID
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				outer apply (
						select top 1 br.BranchID, br.LpuMain 
						from RDS.T_BRANCH br 
							join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
						where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain)
				) tt
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver
				) ehrVzn

			INSERT INTO [RDS].[T_DRUG_REQUEST]
			           ([DrugRequestID]
			           ,[RequestDate]
			           ,[LpuMain]
			           ,[Active]
			           ,[DrugRequestMain]
			           ,[Number]
			           ,[RequestType]
			           ,[ImportFileName]
			           ,[Name]
			           ,[EndPeriod]
			           ,[BeginPeriod]
			           ,[Status]
			           ,[Comment]
			           ,[Limit]
			           ,[Branch]
					   ,[LastEditDate]
					   ,Additional
					   ,EhrRusEj36pmtjqk
					   ,EhrRusVzn17
					   )
			SELECT 
						drl.[DrugRequestID]
			           ,drl.[RequestDate]
			           ,ISNULL(lp.MCOD, tt.LpuMain)
			           ,drl.[Active]
			           ,drl.[DrugRequestMain]
			           ,drl.[Number]
			           ,drl.[RequestType]
			           ,drl.[ImportFileName]
			           ,drl.[Name]
			           ,drl.[EndPeriod]
			           ,drl.[BeginPeriod]
			           ,drl.[Status]
			           ,drl.[Comment]
			           ,drl.[Limit]
			           ,tt.BranchID
					   ,ISNULL(drl.LastEditDate, getdate())
					   ,ISNULL(drl.Additional,0)
					   ,ehr.EhrRusID
					   ,ehrVzn.EhrRusID
			FROM #DRUG_REQUEST_LIST drl
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				outer apply (
					select top 1 br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain)
				) tt
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver
				) ehrVzn
			where not exists (select * from [RDS].T_DRUG_REQUEST dr where dr.DrugRequestID = drl.[DrugRequestID])
			
			update dd 
			        set dd.[DrugRequest] = ddl.[DrugRequest]
			           ,dd.[DemandOnPeriod] = ddl.[DemandOnPeriod]
			           ,dd.[RequestQuantity] = ddl.[RequestQuantity]
			           ,dd.[RequestCode] = ddl.[RequestCode]
			           ,dd.[LSFO] = ddl.[LSFO]
			           ,dd.[Comment] = ddl.[Comment]
					   ,dd.SectionCode = ddl.SectionCode
					   ,dd.PersRequest = ISNULL(ddl.PersRequest, 0)
			           ,dd.DrugApplicationListItem = ddl.RequestCatalog
			from [RDS].T_DRUG_DEMAND dd
			join #DRUG_DEMAND_LIST ddl on ddl.DrugDemandID = dd.DrugDemandID

			INSERT INTO [RDS].[T_DRUG_DEMAND]
			           ([DrugDemandID]
			           ,[DrugRequest]
			           ,[DemandOnPeriod]
			           ,[RequestQuantity]
			           ,[RequestCode]
			           ,[LSFO]
			           ,[Comment]
					   ,[SectionCode]
					   ,[PersRequest]
					   ,[DrugApplicationListItem])
			SELECT
						ddl.[DrugDemandID]
			           ,ddl.[DrugRequest]
			           ,ddl.[DemandOnPeriod]
			           ,ddl.[RequestQuantity]
			           ,ddl.[RequestCode]
			           ,ddl.[LSFO]
			           ,ddl.[Comment]
					   ,ddl.SectionCode
					   ,ISNULL(ddl.PersRequest, 0)
					   ,ddl.RequestCatalog
			FROM #DRUG_DEMAND_LIST ddl
			where not exists (select * from [RDS].T_DRUG_DEMAND dd where dd.DrugDemandID = ddl.[DrugDemandID])
			
			update pdd
			        set [DrugDemand]		= pddl.[DrugDemand]
			           ,[DemandOnPeriod]	= pddl.[DemandOnPeriod]
			           ,[Pharmacy]			= pddl.[Pharmacy]
			           ,[RequestQuantity]	= pddl.[RequestQuantity]
			           ,[Mont1]				= pddl.[Mont1]
			           ,[Mont2]				= pddl.[Mont2]
			           ,[Mont3]				= pddl.[Mont3]
			           ,[Mont4]				= pddl.[Mont4]
			           ,[Mont5]				= pddl.[Mont5]
			           ,[Mont6]				= pddl.[Mont6]
			           ,[Mont7]				= pddl.[Mont7]
			           ,[Mont8]				= pddl.[Mont8]
			           ,[Mont9]				= pddl.[Mont9]
			           ,[Mont10]			= pddl.[Mont10]
			           ,[Mont11]			= pddl.[Mont11]
			           ,[Mont12]			= pddl.[Mont12]
			from [RDS].T_PHARMACY_DRUG_DEMAND pdd
			join #PHARMACY_DRUG_DEMAND_LIST pddl on pddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID

			INSERT INTO [RDS].[T_PHARMACY_DRUG_DEMAND]
			           ([PharmacyDrugDemandID]
			           ,[DrugDemand]
			           ,[DemandOnPeriod]
			           ,[Pharmacy]
			           ,[RequestQuantity]
			           ,[Mont1]
			           ,[Mont2]
			           ,[Mont3]
			           ,[Mont4]
			           ,[Mont5]
			           ,[Mont6]
			           ,[Mont7]
			           ,[Mont8]
			           ,[Mont9]
			           ,[Mont10]
			           ,[Mont11]
			           ,[Mont12])
			SELECT
			            pddl.[PharmacyDrugDemandID]
			           ,pddl.[DrugDemand]
			           ,pddl.[DemandOnPeriod]
			           ,pddl.[Pharmacy]
			           ,pddl.[RequestQuantity]
			           ,pddl.[Mont1]
			           ,pddl.[Mont2]
			           ,pddl.[Mont3]
			           ,pddl.[Mont4]
			           ,pddl.[Mont5]
			           ,pddl.[Mont6]
			           ,pddl.[Mont7]
			           ,pddl.[Mont8]
			           ,pddl.[Mont9]
			           ,pddl.[Mont10]
			           ,pddl.[Mont11]
			           ,pddl.[Mont12]
			FROM #PHARMACY_DRUG_DEMAND_LIST pddl
			where not exists (select * from [RDS].T_PHARMACY_DRUG_DEMAND pdd where pdd.PharmacyDrugDemandID = pddl.PharmacyDrugDemandID)

					
			update pdd
			set
						 pdd.[DemandOnPeriod]			= pddl.[DemandOnPeriod]
						,pdd.[PharmacyDrugDemand]		= pddl.[PharmacyDrugDemand]
						,pdd.[Comment]					= pddl.[Comment]
						,pdd.[Benefits]					= (Select top 1 b.BenefitsID from [RDS].T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and b.BenefitViews = pddl.BenefitType)
						,pdd.[MKB]						= pddl.[MKB]
						,pdd.[FirstName]				= pddl.[FirstName]
						,pdd.[FatherName]				= pddl.[FatherName]
						,pdd.[LastName]					= pddl.[LastName]
						,pdd.[Birthday]					= pddl.[Birthday]
						,pdd.[RequestQuantity]			=	pddl.[RequestQuantity]
						,pdd.[Mont1]					=	pddl.[Mont1]
						,pdd.[Mont2]					=	pddl.[Mont2]
						,pdd.[Mont3]					=	pddl.[Mont3]
						,pdd.[Mont4]					=	pddl.[Mont4]
						,pdd.[Mont5]					=	pddl.[Mont5]
						,pdd.[Mont6]					=	pddl.[Mont6]
						,pdd.[Mont7]					=	pddl.[Mont7]
						,pdd.[Mont8]					=	pddl.[Mont8]
						,pdd.[Mont9]					=	pddl.[Mont9]
						,pdd.[Mont10]					=	pddl.[Mont10]
						,pdd.[Mont11]					=	pddl.[Mont11]
						,pdd.[Mont12]					=	pddl.[Mont12]
						,pdd.[Signa]					=	pddl.[Signa]
						,pdd.[Post]						=	pddl.[Post]
						,pdd.[Snils]					=	pddl.[Snils]
						,pdd.DocSeries					=	pddl.DocSeries
						,pdd.DocNumber					=	pddl.DocNumber
						,pdd.DocIssueDate				=	pddl.DocIssueDate
						,pdd.DocIssueOrg				=	pddl.DocIssueOrg
						,pdd.DocType					=	eDoc.EnumID
						,pdd.OmsNumber					=	pddl.OmsNumber
						,pdd.OmsSerial					=	pddl.OmsSerial
						,pdd.EhrRus5o5e88vhc6			=   dul.EhrRus5o5e88vhc6
						,pdd.Sex						=   eSex.EnumID
			FROM [RDS].[T_PATIENT_DRUG_DEMAND] pdd
				join #PATIENT_DRUG_DEMAND_LIST pddl on pddl.[PatientDrugDemandID] = pdd.[PatientDrugDemandID]
				left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex

			INSERT INTO [RDS].[T_PATIENT_DRUG_DEMAND]
						(
						 [PatientDrugDemandID]
						,[DemandOnPeriod]
						,[PharmacyDrugDemand]
						,[Comment]
						,[Benefits]
						,[MKB]
						,[FirstName]
						,[FatherName]
						,[LastName]
						,[Birthday]
			            ,[RequestQuantity]
			            ,[Mont1]
			            ,[Mont2]
			            ,[Mont3]
			            ,[Mont4]
			            ,[Mont5]
			            ,[Mont6]
			            ,[Mont7]
			            ,[Mont8]
			            ,[Mont9]
			            ,[Mont10]
			            ,[Mont11]
			            ,[Mont12]
					    ,[Signa]
					    ,[Post]
						,[Snils]
						,DocSeries		
						,DocNumber		
						,DocIssueDate	
						,DocIssueOrg	
						,DocType		
						,OmsNumber		
						,OmsSerial		
						,EhrRus5o5e88vhc6
						,Sex
						)
			
			SELECT		[PatientDrugDemandID]
						,[DemandOnPeriod]
						,[PharmacyDrugDemand]
						,[Comment]
						,(Select top 1 b.BenefitsID from [RDS].T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and b.BenefitViews = pddl.BenefitType)
						,[MKB]
						,[FirstName]
						,[FatherName]
						,[LastName]
						,[Birthday]
			            ,[RequestQuantity]
			            ,[Mont1]
			            ,[Mont2]
			            ,[Mont3]
			            ,[Mont4]
			            ,[Mont5]
			            ,[Mont6]
			            ,[Mont7]
			            ,[Mont8]
			            ,[Mont9]
			            ,[Mont10]
			            ,[Mont11]
			            ,[Mont12]
					    ,[Signa]
					    ,[Post]
						,[Snils]
						,pddl.DocSeries
						,pddl.DocNumber
						,pddl.DocIssueDate
						,pddl.DocIssueOrg
						,eDoc.EnumID
						,pddl.OmsNumber
						,pddl.OmsSerial
						,dul.EhrRus5o5e88vhc6
						,eSex.EnumID
			FROM #PATIENT_DRUG_DEMAND_LIST pddl
				left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
			where not exists (select * from [RDS].T_PATIENT_DRUG_DEMAND pdd where pdd.[PatientDrugDemandID] = pddl.[PatientDrugDemandID])

			update drc
			set drc.[UserProcessed] = rc.[UserProcessed]
			FROM [RDS].[T_DRUG_REQUEST_COMMENTS] drc
				join #DRUG_REQUEST_COMMENTS_LIST rc on drc.DrugRequestCommentsID = rc.DrugRequestCommentsID

			--удаление лишних
			delete pdd
			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 #DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugDemandID
			where not exists (
					select * from #PATIENT_DRUG_DEMAND_LIST pddl 
					join #PHARMACY_DRUG_DEMAND_LIST phddl on phddl.PharmacyDrugDemandID = pddl.PharmacyDrugDemand
					join #DRUG_DEMAND_LIST ddl on ddl.DrugDemandID = phddl.DrugDemand
					where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID and dd.DrugRequest = ddl.DrugRequest)

			delete pdd
			from [RDS].[T_PHARMACY_DRUG_DEMAND] pdd
				join [RDS].T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
				join #DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugRequest
			where not exists (
					select * from #PHARMACY_DRUG_DEMAND_LIST phddl 
					join #DRUG_DEMAND_LIST ddl on ddl.DrugDemandID = phddl.DrugDemand
					where phddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID and dd.DrugRequest = ddl.DrugRequest)

			delete dd
			from [RDS].T_DRUG_DEMAND dd
				join #DRUG_REQUEST_LIST dr on dr.DrugRequestID = dd.DrugRequest
			where not exists (
					select * from #DRUG_DEMAND_LIST ddl 
					where ddl.DrugDemandID = dd.DrugDemandID and dd.DrugRequest = ddl.DrugRequest)

		END
		ELSE IF(@OperationType = @DELETE)
		BEGIN
		
			delete pdd
			from [RDS].[T_PATIENT_DRUG_DEMAND] pdd
				join [RDS].[T_PHARMACY_DRUG_DEMAND] ppdd on ppdd.PharmacyDrugDemandID = pdd.PharmacyDrugDemand
				join [RDS].T_DRUG_DEMAND dd on dd.DrugDemandID = ppdd.DrugDemand
				join #DRUG_REQUEST drl on drl.[DrugRequestID] = dd.DrugRequest

			delete pdd
			from [RDS].[T_PHARMACY_DRUG_DEMAND] pdd
				join [RDS].T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
				join #DRUG_REQUEST drl on drl.[DrugRequestID] = dd.DrugRequest

			delete dd
			from [RDS].T_DRUG_DEMAND dd
				join #DRUG_REQUEST drl on drl.[DrugRequestID] = dd.DrugRequest

			delete dr
			from [RDS].T_DRUG_REQUEST dr
				join #DRUG_REQUEST drl on drl.[DrugRequestID] = dr.DrugRequestID

		END
		ELSE
		BEGIN

			update dr
			        set dr.[RequestDate] = drl.[RequestDate]
			           ,dr.[LpuMain] = ISNULL(lp.MCOD, tt.LpuMain)
			           ,dr.[Active] = drl.[Active]
			           ,dr.[DrugRequestMain] = drl.[DrugRequestMain]
			           ,dr.[Number] = drl.[Number]
			           ,dr.[RequestType] = drl.[RequestType]
			           ,dr.[ImportFileName] = drl.[ImportFileName]
			           ,dr.[Name] = drl.[Name]
			           ,dr.[EndPeriod] = drl.[EndPeriod]
			           ,dr.[BeginPeriod] = drl.[BeginPeriod]
			           ,dr.[Status] = drl.[Status]
			           ,dr.[Comment] = drl.[Comment]
			           ,dr.[Limit] = drl.[Limit]
			           ,dr.[Branch] = tt.BranchID
					   ,dr.LastEditDate = ISNULL(drl.LastEditDate, getdate())
			           ,dr.Additional = ISNULL(drl.Additional,0)
			           ,dr.[EhrRusEj36pmtjqk]	= ehr.EhrRusID
			           ,dr.[EhrRusVzn17]		= ehrVzn.EhrRusID
			from [RDS].T_DRUG_REQUEST dr
				join #DRUG_REQUEST drl on drl.DrugRequestID = dr.DrugRequestID
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				outer apply (
					select top 1 br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain)
				) tt
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver
				) ehrVzn

			INSERT INTO [RDS].[T_DRUG_REQUEST]
			           ([DrugRequestID]
			           ,[RequestDate]
			           ,[LpuMain]
			           ,[Active]
			           ,[DrugRequestMain]
			           ,[Number]
			           ,[RequestType]
			           ,[ImportFileName]
			           ,[Name]
			           ,[EndPeriod]
			           ,[BeginPeriod]
			           ,[Status]
			           ,[Comment]
			           ,[Limit]
					   ,[Branch]
					   ,LastEditDate
					   ,Additional
					   ,[EhrRusEj36pmtjqk]
					   ,EhrRusVzn17
					   )
			SELECT 
			            drl.[DrugRequestID]
			           ,drl.[RequestDate]
			           ,ISNULL(lp.MCOD, tt.LpuMain)
			           ,drl.[Active]
			           ,drl.[DrugRequestMain]
			           ,drl.[Number]
			           ,drl.[RequestType]
			           ,drl.[ImportFileName]
			           ,drl.[Name]
			           ,drl.[EndPeriod]
			           ,drl.[BeginPeriod]
			           ,drl.[Status]
			           ,drl.[Comment]
			           ,drl.[Limit]
			           ,tt.BranchID
					   ,ISNULL(drl.LastEditDate, getdate())
					   ,ISNULL(drl.Additional,0)
			           ,ehr.EhrRusID
			           ,ehrVzn.EhrRusID	
			FROM #DRUG_REQUEST drl
				left join RDS.T_LPU_MAIN lp on lp.MCOD = drl.LpuMain and (lp.DloAddDate <= drl.RequestDate and (lp.DloEndDate is null or lp.DloEndDate >= drl.RequestDate))
				outer apply (
					select top 1 br.BranchID, br.LpuMain 
					from RDS.T_BRANCH br 
						join RDS.T_LPU_MAIN lpp on lpp.MCOD = br.LpuMain and (lpp.DloAddDate <= drl.RequestDate and (lpp.DloEndDate is null or lpp.DloEndDate >= drl.RequestDate))
					where (lp.MCOD is not null and br.LpuMain = lp.MCOD and br.BranchCode = drl.BranchCode) or (lp.MCOD is null and br.BranchCode = drl.LpuMain)
				) tt
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_EJ36PMTJQK er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = drl.EhrRusEj36pmtjqk and RRB.CURRVERSION = drl.EhrRusEj36pmtjqkVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID 
					from EHR.T_EHR_RUS_VZN17 er
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = drl.EhrRusVzn17 and RRB.CURRVERSION = drl.EhrRusVzn17Ver
				) ehrVzn
			where not exists (select * from [RDS].T_DRUG_REQUEST dr where dr.DrugRequestID = drl.[DrugRequestID])

			update dd 
			        set dd.[DrugRequest] = ddl.[DrugRequest]
			           ,dd.[DemandOnPeriod] = ddl.[DemandOnPeriod]
			           ,dd.[RequestQuantity] = ddl.[RequestQuantity]
			           ,dd.[RequestCode] = ddl.[RequestCode]
			           ,dd.[LSFO] = ddl.[LSFO]
			           ,dd.[Comment] = ddl.[Comment]
					   ,dd.SectionCode = ddl.SectionCode
					   ,dd.PersRequest = ISNULL(ddl.PersRequest, 0)
					   ,dd.DrugApplicationListItem = ddl.RequestCatalog
			from [RDS].T_DRUG_DEMAND dd
				join #DRUG_DEMAND ddl on ddl.DrugDemandID = dd.DrugDemandID

			INSERT INTO [RDS].[T_DRUG_DEMAND]
			           ([DrugDemandID]
			           ,[DrugRequest]
			           ,[DemandOnPeriod]
			           ,[RequestQuantity]
			           ,[RequestCode]
			           ,[LSFO]
			           ,[Comment]
					   ,[SectionCode]
					   ,[DrugApplicationListItem]
					   ,[PersRequest])
			SELECT
			            ddl.[DrugDemandID]
			           ,ddl.[DrugRequest]
			           ,ddl.[DemandOnPeriod]
			           ,ddl.[RequestQuantity]
			           ,ddl.[RequestCode]
			           ,ddl.[LSFO]
			           ,ddl.[Comment]
					   ,ddl.SectionCode
					   ,ddl.RequestCatalog
					   ,ISNULL(ddl.PersRequest, 0)
			FROM #DRUG_DEMAND ddl
			where not exists (select * from [RDS].T_DRUG_DEMAND dd where dd.DrugDemandID = ddl.[DrugDemandID])
			
			update pdd
			        set [DrugDemand]			= pddl.[DrugDemand]
			           ,[DemandOnPeriod]		= pddl.[DemandOnPeriod]
			           ,[Pharmacy]				= pddl.[Pharmacy]
			           ,[RequestQuantity]		= pddl.[RequestQuantity]
			           ,[Mont1]					= pddl.[Mont1]
			           ,[Mont2]					= pddl.[Mont2]
			           ,[Mont3]					= pddl.[Mont3]
			           ,[Mont4]					= pddl.[Mont4]
			           ,[Mont5]					= pddl.[Mont5]
			           ,[Mont6]					= pddl.[Mont6]
			           ,[Mont7]					= pddl.[Mont7]
			           ,[Mont8]					= pddl.[Mont8]
			           ,[Mont9]					= pddl.[Mont9]
			           ,[Mont10]				= pddl.[Mont10]
			           ,[Mont11]				= pddl.[Mont11]
			           ,[Mont12]				= pddl.[Mont12]
			from [RDS].T_PHARMACY_DRUG_DEMAND pdd
				join #PHARMACY_DRUG_DEMAND pddl on pddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID

			INSERT INTO [RDS].[T_PHARMACY_DRUG_DEMAND]
			           ([PharmacyDrugDemandID]
			           ,[DrugDemand]
			           ,[DemandOnPeriod]
			           ,[Pharmacy]
			           ,[RequestQuantity]
			           ,[Mont1]
			           ,[Mont2]
			           ,[Mont3]
			           ,[Mont4]
			           ,[Mont5]
			           ,[Mont6]
			           ,[Mont7]
			           ,[Mont8]
			           ,[Mont9]
			           ,[Mont10]
			           ,[Mont11]
			           ,[Mont12])
			SELECT
			            pddl.[PharmacyDrugDemandID]
			           ,pddl.[DrugDemand]
			           ,pddl.[DemandOnPeriod]
			           ,pddl.[Pharmacy]
			           ,pddl.[RequestQuantity]
			           ,pddl.[Mont1]
			           ,pddl.[Mont2]
			           ,pddl.[Mont3]
			           ,pddl.[Mont4]
			           ,pddl.[Mont5]
			           ,pddl.[Mont6]
			           ,pddl.[Mont7]
			           ,pddl.[Mont8]
			           ,pddl.[Mont9]
			           ,pddl.[Mont10]
			           ,pddl.[Mont11]
			           ,pddl.[Mont12]
			FROM #PHARMACY_DRUG_DEMAND pddl
			where not exists (select * from [RDS].T_PHARMACY_DRUG_DEMAND pdd where pdd.PharmacyDrugDemandID = pddl.PharmacyDrugDemandID)
		

			update pdd
			set
						 pdd.[DemandOnPeriod]		= pddl.[DemandOnPeriod]
						,pdd.[PharmacyDrugDemand]	= pddl.[PharmacyDrugDemand]
						,pdd.[Comment]				= pddl.[Comment]
						,pdd.[Benefits]				= (Select top 1 b.BenefitsID from [RDS].T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and b.BenefitViews = pddl.BenefitType)
						,pdd.[MKB]					= pddl.[MKB]
						,pdd.[FirstName]			= pddl.[FirstName]
						,pdd.[FatherName]			= pddl.[FatherName]
						,pdd.[LastName]				= pddl.[LastName]
						,pdd.[Birthday]				= pddl.[Birthday]
						,pdd.[RequestQuantity]		= pddl.[RequestQuantity]
						,pdd.[Mont1]				= pddl.[Mont1]
						,pdd.[Mont2]				= pddl.[Mont2]
						,pdd.[Mont3]				= pddl.[Mont3]
						,pdd.[Mont4]				= pddl.[Mont4]
						,pdd.[Mont5]				= pddl.[Mont5]
						,pdd.[Mont6]				= pddl.[Mont6]
						,pdd.[Mont7]				= pddl.[Mont7]
						,pdd.[Mont8]				= pddl.[Mont8]
						,pdd.[Mont9]				= pddl.[Mont9]
						,pdd.[Mont10]				= pddl.[Mont10]
						,pdd.[Mont11]				= pddl.[Mont11]
						,pdd.[Mont12]				= pddl.[Mont12]
						,pdd.[Signa]				= pddl.[Signa]
						,pdd.[Post]					= pddl.[Post]
						,pdd.[Snils]				= pddl.[Snils]
						,pdd.DocSeries				= pddl.DocSeries
						,pdd.DocNumber				= pddl.DocNumber
						,pdd.DocIssueDate			= pddl.DocIssueDate
						,pdd.DocIssueOrg			= pddl.DocIssueOrg
						,pdd.DocType				= eDoc.EnumID
						,pdd.OmsNumber				= pddl.OmsNumber
						,pdd.OmsSerial				= pddl.OmsSerial
						,pdd.EhrRus5o5e88vhc6		= dul.EhrRus5o5e88vhc6
						,pdd.Sex					= eSex.EnumID
			FROM [RDS].[T_PATIENT_DRUG_DEMAND] pdd
				join #PATIENT_DRUG_DEMAND pddl on pddl.[PatientDrugDemandID] = pdd.[PatientDrugDemandID]
				left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
			
			INSERT INTO [RDS].[T_PATIENT_DRUG_DEMAND]
						(
						 [PatientDrugDemandID]
						,[DemandOnPeriod]
						,[PharmacyDrugDemand]
						,[Comment]
						,[Benefits]
						,[MKB]
						,[FirstName]
						,[FatherName]
						,[LastName]
						,[Birthday]
			            ,[RequestQuantity]
			            ,[Mont1]
			            ,[Mont2]
			            ,[Mont3]
			            ,[Mont4]
			            ,[Mont5]
			            ,[Mont6]
			            ,[Mont7]
			            ,[Mont8]
			            ,[Mont9]
			            ,[Mont10]
			            ,[Mont11]
			            ,[Mont12]
					    ,[Signa]
					    ,[Post]
						,[Snils]
						,DocSeries		
						,DocNumber		
						,DocIssueDate	
						,DocIssueOrg	
						,DocType		
						,OmsNumber		
						,OmsSerial		
						,EhrRus5o5e88vhc6
						,Sex
						)
			SELECT		[PatientDrugDemandID]
						,[DemandOnPeriod]
						,[PharmacyDrugDemand]
						,[Comment]
						,(Select top 1 b.BenefitsID from [RDS].T_BENEFITS b where b.BenefitCode = pddl.BenefitCode and b.BenefitViews = pddl.BenefitType)
						,[MKB]
						,[FirstName]
						,[FatherName]
						,[LastName]
						,[Birthday]
			            ,[RequestQuantity]
			            ,[Mont1]
			            ,[Mont2]
			            ,[Mont3]
			            ,[Mont4]
			            ,[Mont5]
			            ,[Mont6]
			            ,[Mont7]
			            ,[Mont8]
			            ,[Mont9]
			            ,[Mont10]
			            ,[Mont11]
			            ,[Mont12]
					    ,[Signa]
					    ,[Post]
						,[Snils]
						,pddl.DocSeries
						,pddl.DocNumber
						,pddl.DocIssueDate
						,pddl.DocIssueOrg
						,eDoc.EnumID
						,pddl.OmsNumber
						,pddl.OmsSerial
						,dul.EhrRus5o5e88vhc6
						,eSex.EnumID
			FROM #PATIENT_DRUG_DEMAND pddl
				left join [VCLib].T_ENUMS eDoc on eDoc.EnumTypeID = 105 and eDoc.Code = pddl.DocType
				left join EHR.T_EHR_TYPE_MATCHING_DUL dul on dul.DocType = eDoc.EnumID
				left join [VCLib].T_ENUMS eSex on eSex.EnumTypeID = 103 and eSex.Code = pddl.Sex
			where not exists (select * from [RDS].T_PATIENT_DRUG_DEMAND pdd where pdd.[PatientDrugDemandID] = pddl.[PatientDrugDemandID])


			update drc
			set
						 drc.UserProcessed = rc.UserProcessed
			FROM [RDS].[T_DRUG_REQUEST_COMMENTS] drc
				join #DRUG_REQUEST_COMMENTS rc on drc.DrugRequestCommentsID = rc.DrugRequestCommentsID



			--удаление лишних
			delete pdd
			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 #DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
			where not exists (
					select * from #PATIENT_DRUG_DEMAND pddl 
						join #PHARMACY_DRUG_DEMAND phddl on phddl.PharmacyDrugDemandID = pddl.PharmacyDrugDemand
						join #DRUG_DEMAND ddl on ddl.DrugDemandID = phddl.DrugDemand
					where pddl.PatientDrugDemandID = pdd.PatientDrugDemandID and dd.DrugRequest = ddl.DrugRequest)

			delete pdd
			from [RDS].[T_PHARMACY_DRUG_DEMAND] pdd
				join [RDS].T_DRUG_DEMAND dd on dd.DrugDemandID = pdd.DrugDemand
				join #DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
			where not exists (
					select * from #PHARMACY_DRUG_DEMAND phddl 
						join #DRUG_DEMAND ddl on ddl.DrugDemandID = phddl.DrugDemand
					where phddl.PharmacyDrugDemandID = pdd.PharmacyDrugDemandID and dd.DrugRequest = ddl.DrugRequest)

			delete dd
			from [RDS].T_DRUG_DEMAND dd
				join #DRUG_REQUEST dr on dr.DrugRequestID = dd.DrugRequest
			where not exists (
					select * from #DRUG_DEMAND ddl 
					where ddl.DrugDemandID = dd.DrugDemandID and dd.DrugRequest = ddl.DrugRequest)

		END

		drop table #DRUG_REQUEST_LIST
		drop table #DRUG_REQUEST
		drop table #DRUG_DEMAND_LIST
		drop table #DRUG_DEMAND
		drop table #PHARMACY_DRUG_DEMAND_LIST
		drop table #PHARMACY_DRUG_DEMAND
		drop table #PATIENT_DRUG_DEMAND_LIST
		drop table #PATIENT_DRUG_DEMAND
		drop table #DRUG_REQUEST_COMMENTS_LIST
		drop table #DRUG_REQUEST_COMMENTS
	END
END
GO