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 'body/DrugRequestID'
					  ,dr.RequestDate								as 'body/RequestDate'
					  ,dr.Number									as 'body/Number'
					  ,dr.Active									as 'body/Active'
					  ,dr.BeginPeriod								as 'body/BeginPeriod'
					  ,dr.EndPeriod									as 'body/EndPeriod'
					  ,dr.Name										as 'body/Name'
					  ,dr.ImportFileName							as 'body/ImportFileName'
					  ,dr.Comment									as 'body/Comment'
					  ,lp.MCOD										as 'body/LpuMain'
					  ,drM.DrugRequestID							as 'body/DrugRequestID'
					  ,dr.RequestType								as 'body/RequestType'
					  ,dr.[Status]									as 'body/Status'
					  ,dr.[Limit]									as 'body/Limit'
					  ,br.BranchCode								as 'body/BranchCode'
					  ,dr.Additional								as 'body/Additional'
					  ,convert(varchar(10), dr.LastEditDate, 20)	as 'body/LastEditDate'
					  ,er.CodeEHR									as 'body/EhrRusEj36pmtjqk'
					  ,rb.CurrVersion								as 'body/EhrRusEj36pmtjqkVer'
					  ,erVzn.IdEHR								    as 'body/EhrRusVzn17'
					  ,rbVzn.CurrVersion							as 'body/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
					   ) as  'body'
                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)


		-- Обработка операции
        if (@OperationType = @FTSEND)
        BEGIN
            -- 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)

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

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

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

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

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

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

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

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

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

                        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
            END

	END
END
GO