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

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[APS].[sp_RDS_Pharmacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [APS].[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 [APS].[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 [RDS].T_PHARMACY p where p.Code = @ID))
        BEGIN
            select @OperationType = @UPDATE
        END
        ELSE IF (@IsNew = 0 and not exists (select * from [RDS].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.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'
								  ,p.Dlo								  as 'PHARMACY/Dlo'
                            from [APS].T_PHARMACY p
								left join [APS].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.FiasAddress						  as 'body/FiasAddress'
					  ,p.FiasHome							  as 'body/FiasHome'
					  ,p.Mail							      as 'body/Mail'
					  ,e1.CodeEHR							  as 'body/EhrRusBr9ap1s2qb'
					  ,RRB.CurrVersion						  as 'body/EhrRusBr9ap1s2qbVer'
                      ,p.PostalCode						      as 'body/PostalCode'
					  ,e.IdEHR								  as 'body/EhrRusMdr308'
					  ,RRB.CurrVersion						  as 'body/EhrRusMdr308Ver'
					  ,p.Dlo								  as 'body/Dlo'
				from [APS].T_PHARMACY p
					left join [APS].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
				where p.Code = @ID
				for xml path(''),
				root('PHARMACY')
            )
        END

        -- Отправка
        INSERT INTO [APS].[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,>)
              ,null--,<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 [APS].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 [APS].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 [APS].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 [APS].[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 [APS].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 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 [APS].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 [APS].[T_PHARMACY] p
                                 join #PHARMACY np on np.[ID] = p.Code
                    END
                else
                    BEGIN
                        -- update [APS].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 [APS].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 [APS].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 [APS].T_PHARMACY
                        INSERT INTO [APS].[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 [APS].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 @OperationType = @INSERT
                          and not exists
                            (select *
                             from [APS].T_PHARMACY p
                             where p.Code = np.ID)

                    END
            END


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

    END
END
GO