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_BillOfGoods]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_BillOfGoods]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Форм выпуска препарата(TRN) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_BillOfGoods]
     @Xml xml = null
    ,@IsNew bit = 1
    ,@ID uniqueidentifier = null
    ,@QueueName varchar(100) = 'RDSSync'
    ,@dateTime datetime = null
    ,@User uniqueidentifier = null
	,@PATH varchar(max) = 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)
		   ,@SQL VARCHAR(MAX)

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


	select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
	into #PharmDV
	from PHARM.S_DLO_DoseView dv
	group by REPLACE(dv.Value, ' ', '')

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_BILL_OF_GOODS_DOSE
	(
		 BillOfGoodsID					UNIQUEIDENTIFIER
		,OtherID						INT
		,Active							BIT
		,CTrn							UNIQUEIDENTIFIER
		,DoseView						varchar(1000)
		,DoseViewRep					varchar(1000)
		,Rate							INT
		,PackQuantity					INT
		,IsVital						BIT
		,FederalID						INT
		,ExportCode						INT
		,EAN							VARCHAR(255)
		,ProductInfo					VARCHAR(512)
		,Dlo							BIT
		,RoundToWriteOff				BIT
		,FedCode						VARCHAR(50)
		,RegCode						VARCHAR(50)
		,Gtin							VARCHAR(14)
		,MedicamentsOfRoszdravnadzor	UNIQUEIDENTIFIER
		,EhrRus2m69i03gtd				INT
		,EhrRus2m69i03gtdVer			VARCHAR(8)
		,EhrRus9a9g09e8kl				VARCHAR(52)
		,EhrRus9a9g09e8klVer			VARCHAR(8)
		,MedicationForms				UNIQUEIDENTIFIER
		,MedicamentProducer				UNIQUEIDENTIFIER
		,EhrRusClp14fvirj				INT
		,EhrRusClp14fvirjVer			VARCHAR(8)
		,EhrRusH17F8UTS3N				INT
		,EhrRusH17F8UTS3NVer			VARCHAR(8)
		,EhrRusDsg207					INT
		,EhrRusDsg207Ver				VARCHAR(8)
	)

SET @SQL = 'INSERT INTO tempdb..#T_BILL_OF_GOODS_DOSE
			SELECT
				 bgl.BillOfGoodsID
				,bgl.OtherID
				,bgl.Active
				,bgl.CTrn
				,dv.Value
				,REPLACE(dv.Value, '' '', '''')
				,bgl.Rate
				,bgl.PackQuantity
				,bgl.IsVital
				,bgl.FederalID
				,bgl.ExportCode
				,bgl.EAN
				,bgl.ProductInfo
				,bgl.Dlo
				,bgl.RoundToWriteOff
				,bgl.FedCode
				,bgl.RegCode
				,bgl.Gtin
				,bgl.MedicamentsOfRoszdravnadzor
				,er.IdEHR
				,RRB.CurrVersion
				,er1.CodeEHR
				,RRB1.CurrVersion
				,bgl.MedicationForms
				,bgl.MedicamentProducer
				,er2.IdEHR
				,RRB2.CurrVersion
				,er3.IdEHR
				,RRB3.CurrVersion
				,er4.IdEHR
				,RRB4.CurrVersion
			FROM ' + @PATH + '.PHARM.T_BILL_OF_GOODS bgl
			left join ' + @PATH + '.PHARM.S_DLO_DoseView dv on dv.id = bgl.DoseView
			left join ' + @PATH + '.EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bgl.EhrRus2m69i03gtd
				LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bgl.EhrRus9a9g09e8kl
				LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bgl.EhrRusClp14fvirj
				LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bgl.EhrRusH17F8UTS3N
				LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bgl.EhrRusDsg207
				LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks'
EXECUTE (@SQL)

-- Первичная загрузка
-- Загружаем новые DoseView
	INSERT INTO [PHARM].[S_DLO_DoseView] ([Value])
	SELECT Max(bgl.DoseView)
		FROM #T_BILL_OF_GOODS_DOSE bgl
	WHERE bgl.DoseViewRep IS NOT NULL AND NOT EXISTS
	    (
	        SELECT *
				FROM #PharmDV dv
	        where dv.Value = bgl.DoseViewRep
	    )
	GROUP BY bgl.DoseViewRep

	--пересоздаем #PharmDV
	truncate table #PharmDV
	insert into #PharmDV(Value, Id)
	select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
	from PHARM.S_DLO_DoseView dv
	group by REPLACE(dv.Value, ' ', '')
	-- TRN не из справочника RDS переводим в неактивные
	UPDATE bf
	set bf.Active = 0
		from [PHARM].T_BILL_OF_GOODS bf
	where not exists
	(
	    select *
	    from #T_BILL_OF_GOODS_DOSE bgl
	    where bgl.BillOfGoodsID = bf.BillOfGoodsID
	)

	-- Обновляем имеющиеся TRN
	update bf
	set
	    bf.DoseView						= dv.Id
	   ,bf.OtherID						= bgl.OtherID
	   ,bf.IsVital						= ISNULL(bgl.IsVital,0)
	   ,bf.Active						= bgl.Active
	   ,bf.Rate							= ISNULL(bgl.Rate, 1)
	   ,bf.ExportCode					= bgl.ExportCode
	   ,bf.PackQuantity					= bgl.PackQuantity
	   ,bf.FederalID					= bgl.FederalID
	   ,bf.MedicamentProducer			= bgl.MedicamentProducer
	   ,bf.CTrn							= bgl.CTrn
	   ,bf.MedicationForms				= bgl.MedicationForms
	   ,bf.ProductInfo					= bgl.ProductInfo
	   ,bf.Dlo							= ISNULL(bgl.Dlo,0)
	   ,bf.RoundToWriteOff				= ISNULL(bgl.RoundToWriteOff,0)
	   ,bf.Gtin							= bgl.Gtin
	   ,bf.EAN							= bgl.EAN
	   ,bf.MedicamentsOfRoszdravnadzor	= bgl.MedicamentsOfRoszdravnadzor
	   ,bf.RegCode						= bgl.RegCode
	   ,bf.FedCode						= bgl.FedCode
	   ,bf.EhrRus2m69i03gtd				= ehr.EhrRusID
	   ,bf.EhrRus9a9g09e8kl				= ehr1.EhrRusID
	   ,bf.EhrRusClp14fvirj				= ehr2.EhrRusID
	   ,bf.EhrRusH17F8UTS3N				= ehr3.EhrRusID
	   ,bf.EhrRusDsg207					= ehr4.EhrRusID
	from [PHARM].T_BILL_OF_GOODS bf
	    join #T_BILL_OF_GOODS_DOSE bgl on bgl.BillOfGoodsID = bf.BillOfGoodsID
		left join #PharmDV dv on dv.Value = bgl.DoseViewRep
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_2M69I03GTD er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
		) ehr
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_9A9G09E8KL er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.CodeEHR = bgl.EhrRus9a9g09e8kl AND rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
		) ehr1
		outer apply (
			select top 1 prep.EhrRusID
			from EHR.T_EHR_RUS_CLP14FVIRJ prep
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
			where prep.IdEHR = bgl.EhrRusClp14fvirj AND rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
		) ehr2
		outer apply (
			select top 1 prep.EhrRusID
			from EHR.T_EHR_RUS_H17F8UTS3N prep
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
			where prep.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
		) ehr3
		outer apply (
			select top 1 prep.EhrRusID
			from EHR.T_EHR_RUS_DSG207 prep
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = prep.EhrRusRefBooks
			where prep.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
		) ehr4

	-- Загружаем новые TRN
	INSERT INTO [PHARM].[T_BILL_OF_GOODS]
			(
				 [BillOfGoodsID]
				,[CTrn]
				,[Active]
				,[MedicamentProducer]
				,[OtherID]
				,[MedicationForms]
				,[DoseView]
				,[Rate]
				,[IsVital]
				,[PackQuantity]
				,[ExportCode]
				,[FederalID]
				,[ProductInfo]
				,[Dlo]
				,[RoundToWriteOff]
				,[Gtin]
				,[EAN]
				,[MedicamentsOfRoszdravnadzor]
				,[RegCode]
				,[FedCode]
				,[EhrRus2m69i03gtd]
				,[EhrRus9a9g09e8kl]
				,[EhrRusClp14fvirj]
				,EhrRusH17F8UTS3N
				,EhrRusDsg207
			)
	SELECT
				 bgl.[BillOfGoodsID]
				,bgl.CTrn
				,bgl.Active
				,bgl.MedicamentProducer
				,bgl.OtherID
				,bgl.MedicationForms
				,dv.Id
				,ISNULL(bgl.Rate, 1)
				,ISNULL(bgl.IsVital,0)
				,bgl.PackQuantity
				,bgl.ExportCode
				,bgl.FederalID
				,bgl.ProductInfo
				,ISNULL(bgl.Dlo,0)
				,ISNULL(bgl.RoundToWriteOff,0)
				,bgl.Gtin
				,bgl.EAN
				,bgl.MedicamentsOfRoszdravnadzor
				,RegCode
				,FedCode
				,ehr.EhrRusID
				,ehr1.EhrRusID
				,ehr2.EhrRusID
				,ehr3.EhrRusID
				,ehr4.EhrRusID
	FROM #T_BILL_OF_GOODS_DOSE bgl
		outer apply (
			select top 1 dv.Id from [PHARM].S_DLO_DoseView dv where REPLACE(dv.Value, ' ', '') = REPLACE(bgl.DoseView, ' ', '')
		) dv
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_2M69I03GTD er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
		) ehr
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_9A9G09E8KL er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
		) ehr1
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_CLP14FVIRJ er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
		) ehr2
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_H17F8UTS3N er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
		) ehr3
		outer apply (
			select top 1 er.EhrRusID
			from EHR.T_EHR_RUS_DSG207 er
				join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
			where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
		) ehr4
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_BILL_OF_GOODS bg
	        WHERE bg.BillOfGoodsID = bgl.[BillOfGoodsID]
	    )

	END
	ELSE
	BEGIN
    IF (@Xml is null)
    BEGIN
        -- Инициализация
        select @ContentEncoding = 'utf-8'
              ,@Type = 'BILL_OF_GOODS'
              ,@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 [PHARM].T_BILL_OF_GOODS bg where bg.BillOfGoodsID = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [PHARM].T_BILL_OF_GOODS bg where bg.BillOfGoodsID = @ID))
        BEGIN
            select @OperationType = @DELETE
            select @Result =
            (
                select @OperationType as 'header/TYPE'
                      ,@ID            as 'header/ID'
                FOR XML PATH(''),
                ROOT('BILL_OF_GOODS')
            )
        END

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD											as 'header/TYPE'
                      ,(
                            select bg.BillOfGoodsID						as 'BILL_OF_GOODS/ID'
                                  ,dv.Value								as 'BILL_OF_GOODS/DoseView'
                                  ,bg.OtherID							as 'BILL_OF_GOODS/OtherID'
								  ,bg.IsVital							as 'BILL_OF_GOODS/IsVital'
                                  ,bg.Active							as 'BILL_OF_GOODS/Active'
								  ,bg.Rate								as 'BILL_OF_GOODS/Rate'
								  ,bg.ExportCode						as 'BILL_OF_GOODS/ExportCode'
								  ,bg.PackQuantity						as 'BILL_OF_GOODS/PackQuantity'
                                  ,bg.FederalID							as 'BILL_OF_GOODS/FederalID'
                                  ,bg.MedicamentProducer				as 'BILL_OF_GOODS/MedicamentProducer'
                                  ,bg.CTrn								as 'BILL_OF_GOODS/CTrn'
                                  ,bg.MedicationForms					as 'BILL_OF_GOODS/MedicationForms'
								  ,bg.ProductInfo						as 'BILL_OF_GOODS/ProductInfo'
								  ,bg.Dlo								as 'BILL_OF_GOODS/Dlo'
								  ,bg.RoundToWriteOff					as 'BILL_OF_GOODS/RoundToWriteOff'
								  ,bg.Gtin								as 'BILL_OF_GOODS/Gtin'
								  ,bg.EAN								as 'BILL_OF_GOODS/EAN'
								  ,bg.MedicamentsOfRoszdravnadzor		as 'BILL_OF_GOODS/MedicamentsOfRoszdravnadzor'
								  ,RegCode								as 'BILL_OF_GOODS/RegCode'
								  ,FedCode								as 'BILL_OF_GOODS/FedCode'
								  ,er.IdEHR								as 'BILL_OF_GOODS/EhrRus2m69i03gtd'
								  ,RRB.CurrVersion						as 'BILL_OF_GOODS/EhrRus2m69i03gtdVer'
								  ,er1.CodeEHR							as 'BILL_OF_GOODS/EhrRus9a9g09e8kl'
								  ,RRB1.CurrVersion						as 'BILL_OF_GOODS/EhrRus9a9g09e8klVer'
								  ,er2.IdEHR							as 'BILL_OF_GOODS/EhrRusClp14fvirj'
								  ,RRB2.CurrVersion						as 'BILL_OF_GOODS/EhrRusClp14fvirjVer'
								  ,er3.IdEHR							as 'BILL_OF_GOODS/EhrRusH17F8UTS3N'
								  ,RRB3.CurrVersion						as 'BILL_OF_GOODS/EhrRusH17F8UTS3NVer'
								  ,er4.IdEHR							as 'BILL_OF_GOODS/EhrRusDsg207'
								  ,RRB4.CurrVersion						as 'BILL_OF_GOODS/EhrRusDsg207Ver'
                            from [PHARM].T_BILL_OF_GOODS bg
                                left join [PHARM].S_DLO_DoseView dv on dv.Id = bg.DoseView
								left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
								left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
								left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
								left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
								left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
									LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
                            for xml path(''),
                            type
                      ) as 'body/BILL_OF_GOODS_LIST'
                for xml path(''),
                root('BILL_OF_GOODS')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем BILL_OF_GOODS
            SELECT @Result =
            (
                select @OperationType					as 'header/TYPE'
                      ,@ID								as 'header/ID'

                      ,dv.Value							as 'body/DoseView'
                      ,bg.OtherID						as 'body/OtherID'
					  ,bg.IsVital						as 'body/IsVital'
                      ,bg.Active						as 'body/Active'
					  ,bg.Rate							as 'body/Rate'
					  ,bg.ExportCode					as 'body/ExportCode'
					  ,bg.PackQuantity					as 'body/PackQuantity'
                      ,bg.FederalID						as 'body/FederalID'
                      ,bg.MedicamentProducer			as 'body/MedicamentProducer'
                      ,bg.CTrn							as 'body/CTrn'
                      ,bg.MedicationForms				as 'body/MedicationForms'
					  ,bg.ProductInfo					as 'body/ProductInfo'
					  ,bg.Dlo							as 'body/Dlo'
					  ,bg.RoundToWriteOff				as 'body/RoundToWriteOff'
					  ,bg.Gtin							as 'body/Gtin'
					  ,bg.EAN							as 'body/EAN'
					  ,bg.MedicamentsOfRoszdravnadzor	as 'body/MedicamentsOfRoszdravnadzor'
					  ,RegCode							as 'body/RegCode'
					  ,FedCode							as 'body/FedCode'
					  ,er.IdEHR							as 'body/EhrRus2m69i03gtd'
					  ,RRB.CurrVersion					as 'body/EhrRus2m69i03gtdVer'
					  ,er1.CodeEhr						as 'body/EhrRus9a9g09e8kl'
					  ,RRB1.CurrVersion					as 'body/EhrRus9a9g09e8klVer'
					  ,er2.IdEHR						as 'body/EhrRusClp14fvirj'
					  ,RRB2.CurrVersion					as 'body/EhrRusClp14fvirjVer'
					  ,er3.IdEHR						as 'body/EhrRusH17F8UTS3N'
					  ,RRB3.CurrVersion					as 'body/EhrRusH17F8UTS3NVer'
					  ,er4.IdEHR						as 'body/EhrRusDsg207'
					  ,RRB4.CurrVersion					as 'body/EhrRusDsg207Ver'
                from [PHARM].T_BILL_OF_GOODS bg
                    left join [PHARM].S_DLO_DoseView dv on dv.Id = bg.DoseView
					left join EHR.T_EHR_RUS_2M69I03GTD er on er.EhrRusID = bg.EhrRus2m69i03gtd
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					left join EHR.T_EHR_RUS_9A9G09E8KL er1 on er1.EhrRusID = bg.EhrRus9a9g09e8kl
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = er1.EhrRusRefBooks
					left join EHR.T_EHR_RUS_CLP14FVIRJ er2 on er2.EhrRusID = bg.EhrRusClp14fvirj
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB2 ON RRB2.EhrRusRefBooksID = er2.EhrRusRefBooks
					left join EHR.T_EHR_RUS_H17F8UTS3N er3 on er3.EhrRusID = bg.EhrRusH17F8UTS3N
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB3 ON RRB3.EhrRusRefBooksID = er3.EhrRusRefBooks
					left join EHR.T_EHR_RUS_DSG207 er4 on er4.EhrRusID = bg.EhrRusDsg207
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB4 ON RRB4.EhrRusRefBooksID = er4.EhrRusRefBooks
                where bg.BillOfGoodsID = @ID
                for xml path(''),
                root('BILL_OF_GOODS')
            )
        END

        -- Отправка
        INSERT INTO [AKUZ].[T_RABBIT_SEND_OBJECT]
            ([RabbitSendObjectID] ,[QueueName] ,[Content] ,[MessageID] ,[Headers] ,[ContentEncoding] ,[Type] ,[UserID] ,[State] ,[CreateDateTime] ,[OwnerID])
        select NewID()--(<RabbitSendObjectID, uniqueidentifier,>
              ,@QueueName--,<QueueName, varchar(255),>
              ,dbo.ConvertTextToBinaryZip(@Result, '')--,<Content, text,>
              ,null--,<MessageID, varchar(255),>
              ,null--,<Headers, varchar(255),>
              ,@ContentEncoding--,<ContentEncoding, varchar(255),>
              ,@Type--,<Type, varchar(255),>
              ,@User--,<UserID, varchar(255),>
              ,0--,<State, int,>)
              ,@dateTime--,<CreateDateTime, datetime,>)
              ,@OwnerID--,<OwnerID, varchar(255),>
    END
    ELSE
    IF (@Xml is not null)
    BEGIN

        -- Получаем тип операции
        select @OperationType = t.c.value('./header[1]/TYPE[1]', 'varchar(36)')
        from @Xml.nodes('BILL_OF_GOODS') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #BILL_OF_GOODS_LIST
            SELECT t.c.value('./ID[1]', 'uniqueidentifier')                          AS ID,
                   t.c.value('./DoseView[1]', 'varchar(1000)')                       AS DoseView,
                   REPLACE(t.c.value('./DoseView[1]', 'varchar(1000)'), ' ', '')     AS DoseViewRep,
                   t.c.value('./OtherID[1]', 'int')                                  AS OtherID,
                   t.c.value('./IsVital[1]', 'bit')                                  AS IsVital,
                   t.c.value('./Active[1]', 'bit')                                   AS Active,
                   t.c.value('./Rate[1]', 'int')                                     AS Rate,
                   t.c.value('./ExportCode[1]', 'int')                               AS ExportCode,
                   t.c.value('./PackQuantity[1]', 'int')                             AS PackQuantity,
                   t.c.value('./FederalID[1]', 'int')                                AS FederalID,
                   t.c.value('./MedicamentProducer[1]', 'uniqueidentifier')          AS MedicamentProducer,
                   t.c.value('./CTrn[1]', 'uniqueidentifier')                        AS CTrn,
                   t.c.value('./MedicationForms[1]', 'uniqueidentifier')             AS MedicationForms,
                   t.c.value('./ProductInfo[1]', 'varchar(max)')                     AS ProductInfo,
                   t.c.value('./Dlo[1]', 'bit')                                      AS Dlo,
                   t.c.value('./RoundToWriteOff[1]', 'bit')                          AS RoundToWriteOff,
                   t.c.value('./Gtin[1]', 'varchar(14)')                             AS Gtin,
                   t.c.value('./EAN[1]', 'varchar(255)')                             AS EAN,
                   t.c.value('./MedicamentsOfRoszdravnadzor[1]', 'uniqueidentifier') AS MedicamentsOfRoszdravnadzor,
                   t.c.value('./RegCode[1]', 'varchar(50)')                          AS RegCode,
                   t.c.value('./FedCode[1]', 'varchar(50)')                          AS FedCode,
                   t.c.value('./EhrRus2m69i03gtd[1]', 'int')                         AS EhrRus2m69i03gtd,
                   t.c.value('./EhrRus2m69i03gtdVer[1]', 'varchar(8)')               AS EhrRus2m69i03gtdVer,
                   t.c.value('./EhrRus9a9g09e8kl[1]', 'varchar(52)')                 AS EhrRus9a9g09e8kl,
                   t.c.value('./EhrRus9a9g09e8klVer[1]', 'varchar(8)')               AS EhrRus9a9g09e8klVer,
                   t.c.value('./EhrRusClp14fvirj[1]', 'int')                         AS EhrRusClp14fvirj,
                   t.c.value('./EhrRusClp14fvirjVer[1]', 'varchar(8)')               AS EhrRusClp14fvirjVer,
                   t.c.value('./EhrRusH17F8UTS3N[1]', 'int')                         AS EhrRusH17F8UTS3N,
                   t.c.value('./EhrRusH17F8UTS3NVer[1]', 'varchar(8)')               AS EhrRusH17F8UTS3NVer,
                   t.c.value('./EhrRusDsg207[1]', 'int')                             AS EhrRusDsg207,
                   t.c.value('./EhrRusDsg207Ver[1]', 'varchar(8)')                   AS EhrRusDsg207Ver
            INTO #BILL_OF_GOODS_LIST
            FROM @Xml.nodes('BILL_OF_GOODS/body/BILL_OF_GOODS_LIST/BILL_OF_GOODS') T(c)

            -- Первичная загрузка
            -- Загружаем новые DoseView
            INSERT INTO [PHARM].[S_DLO_DoseView] ([Value])
            SELECT Max(bgl.DoseView)
            FROM #BILL_OF_GOODS_LIST bgl
            where bgl.DoseView is not null and not exists
                (
                    select *
                    from #PharmDV dv
                    where dv.Value = bgl.DoseViewRep
                )
            group by bgl.DoseViewRep

			truncate table #PharmDV
			insert into #PharmDV(Value, Id)
			select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
			from PHARM.S_DLO_DoseView dv
			group by REPLACE(dv.Value, ' ', '')

            -- T_BILL_OF_GOODS не из справочника RDS переводим в неактивные
            update bf
            set bf.Active = 0
            from [PHARM].T_BILL_OF_GOODS bf
            where not exists
            (
                select *
                from #BILL_OF_GOODS_LIST bgl
                where bgl.ID = bf.BillOfGoodsID
            )

            -- Обновляем имеющиеся TRN
            update bf
            set
                bf.DoseView = dv.Id
               ,bf.OtherID = bgl.OtherID
               ,bf.IsVital = ISNULL(bgl.IsVital,0)
               ,bf.Active = bgl.Active
			   ,bf.Rate = ISNULL(bgl.Rate, 1)
               ,bf.ExportCode = bgl.ExportCode
               ,bf.PackQuantity = bgl.PackQuantity
               ,bf.FederalID = bgl.FederalID
               ,bf.MedicamentProducer = bgl.MedicamentProducer
			   ,bf.CTrn = bgl.CTrn
               ,bf.MedicationForms = bgl.MedicationForms
               ,bf.ProductInfo = bgl.ProductInfo
			   ,bf.Dlo = ISNULL(bgl.Dlo,0)
			   ,bf.RoundToWriteOff = ISNULL(bgl.RoundToWriteOff,0)
			   ,bf.Gtin = bgl.Gtin
			   ,bf.EAN = bgl.EAN
			   ,bf.MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
			   ,bf.RegCode = bgl.RegCode
			   ,bf.FedCode = bgl.FedCode
			   ,bf.EhrRus2m69i03gtd		= ehr.EhrRusID
			   ,bf.EhrRus9a9g09e8kl		= ehr1.EhrRusID
			   ,bf.EhrRusClp14fvirj		= ehr2.EhrRusID
			   ,bf.EhrRusH17F8UTS3N		= ehr3.EhrRusID
			   ,bf.EhrRusDsg207			= ehr4.EhrRusID
            from [PHARM].T_BILL_OF_GOODS bf
                join #BILL_OF_GOODS_LIST bgl on bgl.ID = bf.BillOfGoodsID
				left join #PharmDV dv on dv.Value = bgl.DoseViewRep
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_2M69I03GTD er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_9A9G09E8KL er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
				) ehr1
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_CLP14FVIRJ er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
				) ehr2
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_H17F8UTS3N er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				) ehr3
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DSG207 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				) ehr4

            -- Загружаем новые TRN
            INSERT INTO [PHARM].[T_BILL_OF_GOODS]
                       (
					    [BillOfGoodsID]
					   ,[CTrn]
					   ,[Active]
					   ,[MedicamentProducer]
					   ,[OtherID]
					   ,[MedicationForms]
					   ,[DoseView]
					   ,[Rate]
					   ,[IsVital]
					   ,[PackQuantity]
					   ,[ExportCode]
					   ,[FederalID]
					   ,[ProductInfo]
					   ,[Dlo]
					   ,[RoundToWriteOff]
					   ,[Gtin]
					   ,[EAN]
					   ,[MedicamentsOfRoszdravnadzor]
					   ,[RegCode]
					   ,[FedCode]
					   ,[EhrRus2m69i03gtd]
					   ,[EhrRus9a9g09e8kl]
					   ,[EhrRusClp14fvirj]
						,EhrRusH17F8UTS3N
						,EhrRusDsg207
					   )
            SELECT
					 bgl.ID
					,bgl.CTrn
					,bgl.Active
					,bgl.MedicamentProducer
					,bgl.OtherID
					,bgl.MedicationForms
					,dv.Id
					,ISNULL(bgl.Rate, 1)
					,ISNULL(bgl.IsVital,0)
					,bgl.PackQuantity
					,bgl.ExportCode
					,bgl.FederalID
					,bgl.ProductInfo
					,ISNULL(bgl.Dlo,0)
					,ISNULL(bgl.RoundToWriteOff,0)
					,bgl.Gtin
					,bgl.EAN
					,bgl.MedicamentsOfRoszdravnadzor
					,RegCode
					,FedCode
					,ehr.EhrRusID
					,ehr1.EhrRusID
					,ehr2.EhrRusID
					,ehr3.EhrRusID
					,ehr4.EhrRusID
            from #BILL_OF_GOODS_LIST bgl
				left join #PharmDV dv on dv.Value = bgl.DoseViewRep
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_2M69I03GTD er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
				) ehr
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_9A9G09E8KL er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
				) ehr1
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_CLP14FVIRJ er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
				) ehr2
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_H17F8UTS3N er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
				) ehr3
				outer apply (
					select top 1 er.EhrRusID
					from EHR.T_EHR_RUS_DSG207 er
						join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
					where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
				) ehr4

            where not exists
                (
                    select *
                    from [PHARM].T_BILL_OF_GOODS bg
                    where bg.BillOfGoodsID = bgl.ID
                )
        END
        ELSE
        BEGIN
            -- Generate table #BILL_OF_GOODS
            SELECT t.c.value('./header[1]/ID[1]', 'uniqueidentifier')                        AS ID,
                   t.c.value('./body[1]/DoseView[1]', 'varchar(1000)')                       AS DoseView,
                   REPLACE(t.c.value('./body[1]/DoseView[1]', 'varchar(1000)'), ' ', '')     AS DoseViewRep,
                   t.c.value('./body[1]/OtherID[1]', 'int')                                  AS OtherID,
                   t.c.value('./body[1]/IsVital[1]', 'bit')                                  AS IsVital,
                   t.c.value('./body[1]/Active[1]', 'bit')                                   AS Active,
                   t.c.value('./body[1]/Rate[1]', 'int')                                     AS Rate,
                   t.c.value('./body[1]/ExportCode[1]', 'int')                               AS ExportCode,
                   t.c.value('./body[1]/PackQuantity[1]', 'int')                             AS PackQuantity,
                   t.c.value('./body[1]/FederalID[1]', 'int')                                AS FederalID,
                   t.c.value('./body[1]/MedicamentProducer[1]', 'uniqueidentifier')          AS MedicamentProducer,
                   t.c.value('./body[1]/CTrn[1]', 'uniqueidentifier')                        AS CTrn,
                   t.c.value('./body[1]/MedicationForms[1]', 'uniqueidentifier')             AS MedicationForms,
                   t.c.value('./body[1]/ProductInfo[1]', 'varchar(max)')                     AS ProductInfo,
                   t.c.value('./body[1]/Dlo[1]', 'bit')                                      AS Dlo,
                   t.c.value('./body[1]/RoundToWriteOff[1]', 'bit')                          AS RoundToWriteOff,
                   t.c.value('./body[1]/Gtin[1]', 'varchar(14)')                             AS Gtin,
                   t.c.value('./body[1]/EAN[1]', 'varchar(255)')                             AS EAN,
                   t.c.value('./body[1]/MedicamentsOfRoszdravnadzor[1]',
                             'uniqueidentifier')                                             AS MedicamentsOfRoszdravnadzor,
                   t.c.value('./body[1]/RegCode[1]', 'varchar(50)')                          AS RegCode,
                   t.c.value('./body[1]/FedCode[1]', 'varchar(50)')                          AS FedCode,
                   t.c.value('./body[1]/EhrRus2m69i03gtd[1]', 'int')                         AS EhrRus2m69i03gtd,
                   t.c.value('./body[1]/EhrRus2m69i03gtdVer[1]', 'varchar(8)')               AS EhrRus2m69i03gtdVer,
                   t.c.value('./body[1]/EhrRus9a9g09e8kl[1]', 'varchar(52)')                 AS EhrRus9a9g09e8kl,
                   t.c.value('./body[1]/EhrRus9a9g09e8klVer[1]', 'varchar(8)')               AS EhrRus9a9g09e8klVer,
                   t.c.value('./body[1]/EhrRusClp14fvirj[1]', 'int')                         AS EhrRusClp14fvirj,
                   t.c.value('./body[1]/EhrRusClp14fvirjVer[1]', 'varchar(8)')               AS EhrRusClp14fvirjVer,
                   t.c.value('./body[1]/EhrRusH17F8UTS3N[1]', 'int')                         AS EhrRusH17F8UTS3N,
                   t.c.value('./body[1]/EhrRusH17F8UTS3NVer[1]', 'varchar(8)')               AS EhrRusH17F8UTS3NVer,
                   t.c.value('./body[1]/EhrRusDsg207[1]', 'int')                             AS EhrRusDsg207,
                   t.c.value('./body[1]/EhrRusDsg207Ver[1]', 'varchar(8)')                   AS EhrRusDsg207Ver
            INTO #BILL_OF_GOODS
            FROM @Xml.nodes('BILL_OF_GOODS') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                update bf
                set bf.Active = 0
                from [PHARM].[T_BILL_OF_GOODS] bf
                    join #BILL_OF_GOODS nbg on nbg.ID = bf.BillOfGoodsID
            END
            else
            BEGIN
                -- Первичная загрузка
                -- Загружаем новые DoseView
                INSERT INTO [PHARM].[S_DLO_DoseView] ([Value])
                SELECT Max(bgl.DoseView)
                FROM #BILL_OF_GOODS bgl
                where bgl.DoseView is not null and not exists
                    (
                        select *
                        from #PharmDV dv
                        where dv.Value = bgl.DoseViewRep
                    )
                group by bgl.DoseViewRep

                truncate table #PharmDV
                insert into #PharmDV(Value, Id)
                select REPLACE(dv.Value, ' ', '') as [value], MAX(dv.Id) as Id
                from PHARM.S_DLO_DoseView dv
                group by REPLACE(dv.Value, ' ', '')

                -- Обновляем TRN
                update bf
                set bf.DoseView = dv.Id
                   ,bf.OtherID = bgl.OtherID
                   ,bf.IsVital = ISNULL(bgl.IsVital,0)
                   ,bf.Active = bgl.Active
                   ,bf.Rate = ISNULL(bgl.Rate, 1)
                   ,bf.ExportCode = bgl.ExportCode
                   ,bf.PackQuantity = bgl.PackQuantity
                   ,bf.FederalID = bgl.FederalID
                   ,bf.MedicamentProducer = bgl.MedicamentProducer
                   ,bf.CTrn = bgl.CTrn
                   ,bf.MedicationForms = bgl.MedicationForms
                   ,bf.ProductInfo = bgl.ProductInfo
                   ,bf.Dlo = ISNULL(bgl.Dlo,0)
                   ,bf.RoundToWriteOff = ISNULL(bgl.RoundToWriteOff,0)
                   ,bf.Gtin = bgl.Gtin
                   ,bf.EAN = bgl.EAN
                   ,bf.MedicamentsOfRoszdravnadzor = bgl.MedicamentsOfRoszdravnadzor
                   ,bf.RegCode = bgl.RegCode
                   ,bf.FedCode = bgl.FedCode
                   ,bf.EhrRus2m69i03gtd = ehr.EhrRusID
                   ,bf.EhrRus9a9g09e8kl = ehr1.EhrRusID
                   ,bf.EhrRusClp14fvirj = ehr2.EhrRusID
                   ,bf.EhrRusH17F8UTS3N				= ehr3.EhrRusID
                   ,bf.EhrRusDsg207					= ehr4.EhrRusID
                from [PHARM].T_BILL_OF_GOODS bf
                    join #BILL_OF_GOODS bgl on bgl.ID = bf.BillOfGoodsID
                    left join #PharmDV dv on dv.Value = bgl.DoseViewRep
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_2M69I03GTD er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
                    ) ehr
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_9A9G09E8KL er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
                    ) ehr1
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_CLP14FVIRJ er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
                    ) ehr2
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_H17F8UTS3N er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
                    ) ehr3
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_DSG207 er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
                    ) ehr4
                where @OperationType = @UPDATE

                -- Загружаем новые TRN
                INSERT INTO PHARM.T_BILL_OF_GOODS
                        (
                         BillOfGoodsID
                        ,CTrn
                        ,Active
                        ,MedicamentProducer
                        ,OtherID
                        ,MedicationForms
                        ,DoseView
                        ,Rate
                        ,IsVital
                        ,PackQuantity
                        ,ExportCode
                        ,FederalID
                        ,ProductInfo
                        ,Dlo
                        ,RoundToWriteOff
                        ,Gtin
                        ,EAN
                        ,MedicamentsOfRoszdravnadzor
                        ,RegCode
                        ,FedCode
                        ,EhrRus2m69i03gtd
                        ,EhrRus9a9g09e8kl
                        ,EhrRusClp14fvirj
                            ,EhrRusH17F8UTS3N
                            ,EhrRusDsg207
                        )
                SELECT
                         bgl.ID
                        ,bgl.CTrn
                        ,bgl.Active
                        ,bgl.MedicamentProducer
                        ,bgl.OtherID
                        ,bgl.MedicationForms
                        ,dv.Id
                        ,ISNULL(bgl.Rate, 1)
                        ,ISNULL(bgl.IsVital,0)
                        ,bgl.PackQuantity
                        ,bgl.ExportCode
                        ,bgl.FederalID
                        ,bgl.ProductInfo
                        ,ISNULL(bgl.Dlo,0)
                        ,ISNULL(bgl.RoundToWriteOff,0)
                        ,bgl.Gtin
                        ,bgl.EAN
                        ,MedicamentsOfRoszdravnadzor
                        ,RegCode
                        ,FedCode
                        ,ehr.EhrRusID
                        ,ehr1.EhrRusID
                        ,ehr2.EhrRusID
                            ,ehr3.EhrRusID
                            ,ehr4.EhrRusID
                from #BILL_OF_GOODS bgl
                    left join #PharmDV dv on dv.Value = bgl.DoseViewRep
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_2M69I03GTD er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRus2m69i03gtd and rrb.CurrVersion = bgl.EhrRus2m69i03gtdVer
                    ) ehr
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_9A9G09E8KL er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.CodeEHR = bgl.EhrRus9a9g09e8kl and rrb.CurrVersion = bgl.EhrRus9a9g09e8klVer
                    ) ehr1
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_CLP14FVIRJ er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusClp14fvirj and rrb.CurrVersion = bgl.EhrRusClp14fvirjVer
                    ) ehr2
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_H17F8UTS3N er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusH17F8UTS3N and RRB.CURRVERSION = bgl.EhrRusH17F8UTS3NVer
                    ) ehr3
                    outer apply (
                        select top 1 er.EhrRusID
                        from EHR.T_EHR_RUS_DSG207 er
                            join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = er.EhrRusRefBooks
                        where er.IdEHR = bgl.EhrRusDsg207 and RRB.CURRVERSION = bgl.EhrRusDsg207Ver
                    ) ehr4
                where not exists
                    (
                        select *
                        from [PHARM].T_BILL_OF_GOODS bg
                        where bg.BillOfGoodsID = bgl.ID
                    )
                        and
                    @OperationType = @INSERT

            END
        END

		declare @EntId int
        select top 1 @EntId = EntityID from VCLib.T_ENTITIES where EntName = 'BILL_OF_GOODS'
        IF(exists (select top 1 * from [VCLib].T_GEN_NUMBERS where  EntityID = @EntId and NumberName = 'PHARM.BILL_OF_GOODS'))
		BEGIN
			-- генерируем след. номер
			--exec [dbo].sp_GenerateNumber @EntId, 'PHARM.BILL_OF_GOODS'
			update gn
			set gn.NextNumber = (select MAX(OtherID) from [PHARM].T_BILL_OF_GOODS)
			from [VCLib].T_GEN_NUMBERS gn
			where gn.EntityID = @EntId
					and
				gn.NumberName = 'PHARM.BILL_OF_GOODS'
		END
		ELSE
		BEGIN

			INSERT INTO [VCLib].[T_GEN_NUMBERS]
			           ([GenNumbersID]
			           ,[EntityID]
			           ,[NumberName]
			           ,[NextNumber])
			     VALUES
			           (newID()
			           ,@EntId
			           ,'PHARM.BILL_OF_GOODS'
			           ,ISNULL((select MAX(OtherID) from [PHARM].T_BILL_OF_GOODS), 1))

		END
    END
END
END
GO