IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_Pharmacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Pharmacy]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-09-29
-- Description: Выгрузка/загрузка справочника Аптек в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_Pharmacy]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID varchar(255) = 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 varchar(255)
            -- Operation types
           ,@INSERT varchar(6)
           ,@UPDATE varchar(6)
           ,@DELETE varchar(6)
           ,@FTLOAD varchar(6)

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

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD                                            as 'header/TYPE'
                      ,(
                            select p.Code                                 as 'PHARMACY/ID'
                                  ,p.Name                                 as 'PHARMACY/Name'
                                  ,p.NameFull                             as 'PHARMACY/NameFull'
                                  ,p.OGRN                                 as 'PHARMACY/OGRN'
                                  ,p.Terr                                 as 'PHARMACY/Terr'
                                  ,p.OKATO                                as 'PHARMACY/OKATO'
                                  ,convert(varchar(10), p.DateBegin, 104) as 'PHARMACY/DateBegin'
                                  ,convert(varchar(10), p.DateEnd, 104)   as 'PHARMACY/DateEnd'
                                  ,convert(varchar(10), @dateTime, 104)   as 'PHARMACY/RegisterDate'
                                  ,p.RecipeServedTypes                    as 'PHARMACY/RecipeServedTypes'
								  ,p.[Address]						      as 'PHARMACY/Address'
								  ,r.[Code]								  as 'PHARMACY/Rajon'
								  ,p.[Number]							  as 'PHARMACY/Number'
								  ,p.Phone								  as 'PHARMACY/Phone'
                                  ,p.LicenseNumber						  as 'PHARMACY/LicenseNumber'
								  ,p.LicenseIssue						  as 'PHARMACY/LicenseIssue'
								  ,p.LicenseDate						  as 'PHARMACY/LicenseDate'
								  ,p.Dlo								  as 'PHARMACY/Dlo'
								  ,p.FiasAddress						  as 'PHARMACY/FiasAddress'
								  ,p.FiasHome							  as 'PHARMACY/FiasHome'
								  ,p.Mail   							  as 'PHARMACY/Mail'
								  ,e1.CodeEHR							  as 'PHARMACY/EhrRusBr9ap1s2qb'
								  ,RRB1.CurrVersion						  as 'PHARMACY/EhrRusBr9ap1s2qbVer'
								  ,p.PostalCode						      as 'PHARMACY/PostalCode'
								  ,e.IdEHR								  as 'PHARMACY/EhrRusMdr308'
								  ,RRB.CurrVersion						  as 'PHARMACY/EhrRusMdr308Ver'
                            from [AKUZ].T_PHARMACY p
								left join [AKUZ].T_RAJON r on r.RajonID = p.Rajon
								left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
								join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
								left join EHR.T_EHR_RUS_BR9AP1S2QB e1 on e1.EhrRusID = p.EhrRusBr9ap1s2qb
								join EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
                			for xml path(''),
                            type
                      ) as 'body/PHARMACYS'
                for xml path(''),
                root('PHARMACY')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем PHARMACY
            SELECT @Result =
            (
                select @OperationType                         as 'header/TYPE'
                      ,@ID                                    as 'header/ID'
                      ,p.Name                                 as 'body/Name'
                      ,p.NameFull                             as 'body/NameFull'
                      ,p.OGRN                                 as 'body/OGRN'
                      ,p.Terr                                 as 'body/Terr'
                      ,p.OKATO                                as 'body/OKATO'
                      ,convert(varchar(10), p.DateBegin, 104) as 'body/DateBegin'
                      ,convert(varchar(10), p.DateEnd, 104)   as 'body/DateEnd'
                      ,convert(varchar(10), @dateTime, 104)   as 'body/RegisterDate'
                      ,p.RecipeServedTypes                    as 'body/RecipeServedTypes'
					  ,p.[Address]						      as 'body/Address'
					  ,r.[Code]								  as 'body/Rajon'
					  ,p.[Number]							  as 'body/Number'
					  ,p.Phone								  as 'body/Phone'
                      ,p.LicenseNumber						  as 'body/LicenseNumber'
                      ,p.LicenseIssue						  as 'body/LicenseIssue'
                      ,p.LicenseDate						  as 'body/LicenseDate'
					  ,p.Dlo								  as 'body/Dlo'
					  ,p.FiasAddress						  as 'body/FiasAddress'
                      ,p.PostalCode						      as 'body/PostalCode'
					  ,p.FiasHome							  as 'body/FiasHome'
					  ,e.IdEHR								  as 'body/EhrRusMdr308'
					  ,RRB.CurrVersion						  as 'body/EhrRusMdr308Ver'
				from [AKUZ].T_PHARMACY p
					left join [AKUZ].T_RAJON r on r.RajonID = p.Rajon
					left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
					join EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
				where p.Code = @ID
				for xml path(''),
				root('PHARMACY')
            )
        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('PHARMACY') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #PHARMACYS
            SELECT t.c.value('./ID[1]', 'varchar(255)')                                  AS ID,
                   t.c.value('./Name[1]', 'varchar(255)')                                AS Name,
                   t.c.value('./NameFull[1]', 'varchar(255)')                            AS NameFull,
                   t.c.value('./OGRN[1]', 'varchar(255)')                                AS OGRN,
                   t.c.value('./Terr[1]', 'varchar(255)')                                AS Terr,
                   t.c.value('./OKATO[1]', 'varchar(255)')                               AS OKATO,
                   CONVERT(DATETIME, t.c.value('./DateBegin[1]', 'varchar(10)'), 104)    AS DateBegin,
                   CONVERT(DATETIME, t.c.value('./DateEnd[1]', 'varchar(10)'), 104)      AS DateEnd,
                   CONVERT(DATETIME, t.c.value('./RegisterDate[1]', 'varchar(10)'), 104) AS RegisterDate,
                   t.c.value('./RecipeServedTypes[1]', 'int')                            AS RecipeServedTypes,
                   t.c.value('./Address[1]', 'varchar(255)')                             AS Address,
                   t.c.value('./Rajon[1]', 'varchar(13)')                                AS Rajon,
                   t.c.value('./Number[1]', 'varchar(50)')                               AS Number,
                   t.c.value('./Phone[1]', 'varchar(40)')                                AS Phone,
                   t.c.value('./LicenseNumber[1]', 'varchar(255)')                       AS LicenseNumber,
                   t.c.value('./LicenseIssue[1]', 'varchar(255)')                        AS LicenseIssue,
                   CONVERT(DATETIME, t.c.value('./LicenseDate[1]', 'varchar(10)'), 104)  AS LicenseDate,
                   t.c.value('./Dlo[1]', 'int')                                          AS Dlo,
                   t.c.value('./FiasAddress[1]', 'varchar(255)')                         AS FiasAddress,
                   t.c.value('./FiasHome[1]', 'varchar(255)')                            AS FiasHome,
                   t.c.value('./Mail[1]', 'varchar(200)')                                AS Mail,
                   t.c.value('./EhrRusBr9ap1s2qb[1]', 'varchar(52)')                     AS EhrRusBr9ap1s2qb,
                   t.c.value('./EhrRusBr9ap1s2qbVer[1]', 'varchar(8)')                   AS EhrRusBr9ap1s2qbVer,
                   t.c.value('./PostalCode[1]', 'varchar(255)')                          AS PostalCode,
                   t.c.value('./EhrRusMdr308[1]', 'int')                                 AS EhrRusMdr308,
                   t.c.value('./EhrRusMdr308Ver[1]', 'varchar(8)')                       AS EhrRusMdr308Ver
            INTO #PHARMACYS
            FROM @Xml.nodes('PHARMACY/body/PHARMACYS/PHARMACY') T(c)

            -- Первичная загрузка
            -- Аптеки не из справочника RDS переводим в неактивные
            update p
            set p.DateEnd = @dateTime
                ,p.RegisterDate = @dateTime
            from [AKUZ].T_PHARMACY p
            where not exists
            (
                select *
                from #PHARMACYS np
                where np.ID = p.Code
            )

            -- Обновляем имеющиеся Аптеки
            update p
            set p.[Name] = np.[Name]
               ,p.[NameFull] = np.[NameFull]
               ,p.[OGRN] = np.[OGRN]
               ,p.[Terr] = np.[Terr]
               ,p.[OKATO] = np.[OKATO]
               ,p.[DateBegin] = np.[DateBegin]
               ,p.[DateEnd] = np.[DateEnd]
               ,p.[RegisterDate] = np.[RegisterDate]
               ,p.[RecipeServedTypes] = np.[RecipeServedTypes]
			   ,p.[Address] = np.[Address]
			   ,p.[Rajon] = (select top 1 r.RajonID from [AKUZ].T_RAJON r where r.[Code] = np.[Rajon])
			   ,p.[Number] = np.[Number]
			   ,p.Phone = np.Phone
			   ,p.LicenseNumber = np.LicenseNumber
			   ,p.LicenseIssue = np.LicenseIssue
			   ,p.LicenseDate = np.LicenseDate
			   ,p.Dlo = ISNULL(np.Dlo, 0)
			   ,p.FiasAddress = np.FiasAddress
			   ,p.FiasHome = np.FiasHome
               ,p.Mail = np.Mail
               ,p.EhrRusBr9ap1s2qb = e1.EhrRusID
			   ,p.PostalCode = np.PostalCode
			   ,p.EhrRusMdr308 = e.EhrRusID
            from [AKUZ].T_PHARMACY p
                join #PHARMACYS np on np.ID = p.Code
				outer apply (
					select e.EhrRusID
					from EHR.T_EHR_RUS_MDR308 e
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and RRB.CURRVERSION = np.EhrRusMdr308Ver
				) e
				outer apply (
					select e1.EhrRusID
					from EHR.T_EHR_RUS_BR9AP1S2QB e1
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
					where e1.CodeEHR = np.EhrRusBr9ap1s2qb and RRB1.CURRVERSION = np.EhrRusBr9ap1s2qbVer
				) e1

            -- Загружаем новые
            INSERT INTO [AKUZ].[T_PHARMACY]
                       ([Code] ,[Name] ,[NameFull] , [OGRN], [Terr], [OKATO], [DateBegin], [DateEnd], [RegisterDate], [RecipeServedTypes], [Address], [Rajon], [Number], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, FiasHome, Mail, EhrRusBr9ap1s2qb, PostalCode, EhrRusMdr308)
            SELECT
                       np.ID--<Code, int,>
                       ,np.Name--<Name, varchar(255),>
                       ,np.NameFull--<NameFull, varchar(255),>
                       ,np.OGRN--<OGRN, varchar(255),>
                       ,np.Terr--<Terr, varchar(255),>
                       ,np.OKATO--<OKATO, varchar(255),>
                       ,np.DateBegin--<DateBegin, datetime,>
                       ,np.DateEnd--<DateEnd, datetime,>
                       ,np.RegisterDate--<RegisterDate, datetime,>
                       ,np.RecipeServedTypes--<RecipeServedTypes, int,>)
					   ,np.[Address]--<Address, varchar(255),>
					   ,(select top 1 r.RajonID from [AKUZ].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
					   ,np.[Number]--<Number, varchar(50),>
					   ,Phone
					   ,np.LicenseNumber
					   ,np.LicenseIssue
					   ,np.LicenseDate
					   ,ISNULL(Dlo, 0)
					   ,np.FiasAddress
					   ,np.FiasHome
                       ,np.Mail
                       ,e1.EhrRusID
					   ,np.PostalCode
					   ,e.EhrRusID
            from #PHARMACYS np
				outer apply (
					select top 1 e.EhrRusID
					from EHR.T_EHR_RUS_MDR308 e
						join EHR.T_EHR_RUS_REF_BOOKS eb on eb.EhrRusRefBooksID = e.EhrRusRefBooks
					where e.IdEHR = np.EhrRusMdr308 and eb.CurrVersion = np.EhrRusMdr308Ver
				) e
				outer apply (
					select top 1 e1.EhrRusID
					from EHR.T_EHR_RUS_BR9AP1S2QB e1
						join EHR.T_EHR_RUS_REF_BOOKS eb1 on eb1.EhrRusRefBooksID = e1.EhrRusRefBooks
					where e1.CodeEHR = np.EhrRusBr9ap1s2qb and eb1.CurrVersion = np.EhrRusBr9ap1s2qbVer
				) e1
            where not exists
            (
                select *
                from [AKUZ].T_PHARMACY p
                where p.Code = np.ID
            )
        END
        ELSE
        BEGIN
            -- Generate table #PHARMACY
            SELECT T.c.value('./header[1]/ID[1]', 'varchar(255)')                                AS ID,
                   t.c.value('./body[1]/Name[1]', 'varchar(255)')                                AS Name,
                   t.c.value('./body[1]/NameFull[1]', 'varchar(255)')                            AS NameFull,
                   t.c.value('./body[1]/OGRN[1]', 'varchar(255)')                                AS OGRN,
                   t.c.value('./body[1]/Terr[1]', 'varchar(255)')                                AS Terr,
                   t.c.value('./body[1]/OKATO[1]', 'varchar(255)')                               AS OKATO,
                   CONVERT(DATETIME, t.c.value('./body[1]/DateBegin[1]', 'varchar(10)'), 104)    AS DateBegin,
                   CONVERT(DATETIME, t.c.value('./body[1]/DateEnd[1]', 'varchar(10)'), 104)      AS DateEnd,
                   CONVERT(DATETIME, t.c.value('./body[1]/RegisterDate[1]', 'varchar(10)'), 104) AS RegisterDate,
                   t.c.value('./body[1]/RecipeServedTypes[1]', 'int')                            AS RecipeServedTypes,
                   t.c.value('./body[1]/Address[1]', 'varchar(255)')                             AS Address,
                   t.c.value('./body[1]/Rajon[1]', 'varchar(13)')                                AS Rajon,
                   t.c.value('./body[1]/Number[1]', 'varchar(50)')                               AS Number,
                   t.c.value('./body[1]/Phone[1]', 'varchar(40)')                                AS Phone,
                   t.c.value('./body[1]/LicenseNumber[1]', 'varchar(255)')                       AS LicenseNumber,
                   t.c.value('./body[1]/LicenseIssue[1]', 'varchar(255)')                        AS LicenseIssue,
                   CONVERT(DATETIME, t.c.value('./body[1]/LicenseDate[1]', 'varchar(10)'), 104)  AS LicenseDate,
                   t.c.value('./body[1]/Dlo[1]', 'int')                                          AS Dlo,
                   t.c.value('./body[1]/FiasAddress[1]', 'varchar(255)')                         AS FiasAddress,
                   t.c.value('./body[1]/FiasHome[1]', 'varchar(255)')                            AS FiasHome,
                   t.c.value('./body[1]/Mail[1]', 'varchar(200)')                                AS Mail,
                   t.c.value('./body[1]/EhrRusBr9ap1s2qb[1]', 'varchar(52)')                     AS EhrRusBr9ap1s2qb,
                   t.c.value('./body[1]/EhrRusBr9ap1s2qbVer[1]', 'varchar(8)')                   AS EhrRusBr9ap1s2qbVer,
                   t.c.value('./body[1]/PostalCode[1]', 'varchar(255)')                          AS PostalCode,
                   t.c.value('./body[1]/EhrRusMdr308[1]', 'int')                                 AS EhrRusMdr308,
                   t.c.value('./body[1]/EhrRusMdr308Ver[1]', 'varchar(8)')                       AS EhrRusMdr308Ver
            INTO #PHARMACY
            FROM @Xml.nodes('PHARMACY') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                update p
                set p.DateEnd = @dateTime
                    ,p.RegisterDate = @dateTime
                from [AKUZ].[T_PHARMACY] p
                    join #PHARMACY np on np.[ID] = p.Code
            END
            else
            BEGIN
                -- update [AKUZ].T_PHARMACY
                update p
                set p.[Name] = np.[Name]
                   ,p.[NameFull] = np.[NameFull]
                   ,p.[OGRN] = np.[OGRN]
                   ,p.[Terr] = np.[Terr]
                   ,p.[OKATO] = np.[OKATO]
                   ,p.[DateBegin] = np.[DateBegin]
                   ,p.[DateEnd] = np.[DateEnd]
                   ,p.[RegisterDate] = np.[RegisterDate]
                   ,p.[RecipeServedTypes] = np.[RecipeServedTypes]
                   ,p.[Address] = np.[Address]
                   ,p.[Rajon] = (select top 1 r.RajonID from [AKUZ].T_RAJON r where r.[Code] = np.[Rajon])
                   ,p.[Number] = np.[Number]
                   ,p.Phone = np.Phone
                   ,p.LicenseNumber = np.LicenseNumber
                   ,p.LicenseIssue = np.LicenseIssue
                   ,p.LicenseDate = np.LicenseDate
                   ,p.Dlo = ISNULL(np.Dlo, 0)
                   ,p.FiasAddress = np.FiasAddress
                   ,p.FiasHome = np.FiasHome
                   ,p.Mail = np.Mail
                   ,p.EhrRusBr9ap1s2qb = e1.EhrRusID
                   ,p.PostalCode = np.PostalCode
                   ,p.EhrRusMdr308 = e.EhrRusID
                from [AKUZ].T_PHARMACY p
                    join #PHARMACY np on np.ID = p.Code
                    outer apply (
                        select e.EhrRusID
                        from EHR.T_EHR_RUS_MDR308 e
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
                        where e.IdEHR = np.EhrRusMdr308 and RRB.CurrVersion = np.EhrRusMdr308Ver
                    ) e
                    outer apply (
                        select e1.EhrRusID
                        from EHR.T_EHR_RUS_BR9AP1S2QB e1
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
                        where e1.CodeEHR = np.EhrRusBr9ap1s2qb and RRB1.CurrVersion = np.EhrRusBr9ap1s2qbVer
                    ) e1
                where @OperationType = @UPDATE

                -- insert [AKUZ].T_PHARMACY
                INSERT INTO [AKUZ].[T_PHARMACY]
                           ([Code], [Name], [NameFull], [OGRN], [Terr], [OKATO], [DateBegin], [DateEnd], [RegisterDate], [RecipeServedTypes], [Address], [Rajon], [Number], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, FiasHome, Mail, EhrRusBr9ap1s2qb, PostalCode, EhrRusMdr308)
                SELECT
                           np.ID--<Code, int,>
                           ,np.Name--<Name, varchar(255),>
                           ,np.NameFull--<NameFull, varchar(255),>
                           ,np.OGRN--<OGRN, varchar(255),>
                           ,np.Terr--<Terr, varchar(255),>
                           ,np.OKATO--<OKATO, varchar(255),>
                           ,np.DateBegin--<DateBegin, datetime,>
                           ,np.DateEnd--<DateEnd, datetime,>
                           ,np.RegisterDate--<RegisterDate, datetime,>
                           ,np.RecipeServedTypes--<RecipeServedTypes, int,>)
                           ,np.[Address]--<Address, varchar(255),>
                           ,(select top 1 r.RajonID from [AKUZ].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
                           ,np.[Number]--<Number, varchar(50),>
                           ,Phone
                           ,np.LicenseNumber
                           ,np.LicenseIssue
                           ,np.LicenseDate
                           ,ISNULL(Dlo, 0)
                           ,np.FiasAddress
                           ,np.FiasHome
                           ,np.Mail
                           ,e1.EhrRusID
                           ,np.PostalCode
                           ,e.EhrRusID
                from #PHARMACY np
                    outer apply (
                        select e.EhrRusID
                        from EHR.T_EHR_RUS_MDR308 e
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = e.EhrRusRefBooks
                        where e.IdEHR = np.EhrRusMdr308 and RRB.CurrVersion = np.EhrRusMdr308Ver
                    ) e
                    outer apply (
                        select e1.EhrRusID
                        from EHR.T_EHR_RUS_BR9AP1S2QB e1
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = e1.EhrRusRefBooks
                        where e1.CodeEHR = np.EhrRusBr9ap1s2qb and RRB1.CurrVersion = np.EhrRusBr9ap1s2qbVer
                    ) e1
                where not exists
                    (
                        select *
                        from [AKUZ].T_PHARMACY p
                        where p.Code = np.ID
                    )
                        and
                    @OperationType = @INSERT
            END
        END

        -- генерируем след. номер
        --exec [dbo].sp_GenerateIdForEntity 'PHARMACY'
        update ei
        set ei.id = (select MAX(Code) from [AKUZ].T_PHARMACY) + 1
        from T_ENTITY_IDS ei
        where ei.EntName = 'PHARMACY'
                and
            ei.FieldName = 'PrimaryKey'

        -- Drop tables
        DROP TABLE #PHARMACY
        DROP TABLE #PHARMACYS

    END
END
GO