IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetSetPharmacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetSetPharmacy]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-09-29
-- Description: Выгрузка/загрузка справочника Аптек в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSetPharmacy]
    @Xml xml = null,
    @IsNew bit = 1,
    @ID varchar(255) = null,
    @QueueName varchar(100) = null,
    @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'
								  ,convert(varchar(10), p.LicenseDate, 104)	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.ZIPCode								as 'PHARMACY/PostalCode'
								  ,e.IdEHR									as 'PHARMACY/EhrRusMdr308'
								  ,RRB.CurrVersion							as 'PHARMACY/EhrRusMdr308Ver'
                            from [RDS].T_PHARMACY p
								left join [RDS].T_RAJON r on r.RajonID = p.Rajon
								left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
									LEFT 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
									LEFT 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'
                      ,convert(varchar(10), p.LicenseDate, 104)	as 'body/LicenseDate'
                      ,p.Dlo									as 'body/Dlo'
					  ,p.FiasAddress							as 'body/FiasAddress'
					  ,p.FiasHome								as 'body/FiasHome'
					  ,p.Mail								    as 'body/Mail'
					  ,e1.CodeEHR								as 'body/EhrRusBr9ap1s2qb'
					  ,RRB1.CurrVersion							as 'body/EhrRusBr9ap1s2qbVer'
                      ,p.ZIPCode								as 'body/PostalCode'
					  ,e.IdEHR									as 'body/EhrRusMdr308'
					  ,RRB.CurrVersion							as 'body/EhrRusMdr308Ver'
				from [RDS].T_PHARMACY p
					left join [RDS].T_RAJON r on r.RajonID = p.Rajon
					left join EHR.T_EHR_RUS_MDR308 e on e.EhrRusID = p.EhrRusMdr308
						LEFT 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
						LEFT 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 [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,>)
              ,null--,<OwnerID, varchar(255),>
        from [RDS].T_SYNC_SERVERS s
        where (@QueueName is not null and s.ServerName = @QueueName)
                or
             (@QueueName is null and s.ServerType in (0, 1)) -- Рассылаем пакет всем подписанным серверам AKUZ-а и APS, если не указана очередь
    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(40)')                        AS LicenseNumber,
                   t.c.value('./LicenseIssue[1]', 'varchar(40)')                         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(10)')                           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 [RDS].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 [RDS].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.ZIPCode				    = np.ZIPCode
			   ,p.EhrRusMdr308				= e.EhrRusID
            from [RDS].T_PHARMACY p
                join #PHARMACYS np on np.ID = p.Code
				outer apply (
					select top 1 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 top 1 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 [RDS].[T_PHARMACY]
                       ([Code], [Name], [NameFull], [OGRN], [Terr], [OKATO], [DateBegin], [DateEnd], [RegisterDate], [RecipeServedTypes], [Address], [Rajon], [Number], [UsePharmacyProduct], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, FiasHome, Mail, EhrRusBr9ap1s2qb, ZIPCode, 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 [RDS].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
					   ,np.[Number]--<Number, varchar(50),>
					   ,0
					   ,np.Phone
                       ,np.LicenseNumber
                       ,np.LicenseIssue
                       ,np.LicenseDate
					   ,ISNULL(np.Dlo, 0)
					   ,np.FiasAddress
					   ,np.FiasHome
                       ,np.Mail
                       ,e1.EhrRusID
                       ,np.ZIPCode
					   ,e.EhrRusID
            from #PHARMACYS np
				outer apply (
					select top 1 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 top 1 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 [RDS].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(40)')                        AS LicenseNumber,
                   t.c.value('./body[1]/LicenseIssue[1]', 'varchar(40)')                         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(10)')                           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 [RDS].[T_PHARMACY] p
                    join #PHARMACY np on np.[ID] = p.Code
            END
            else
            BEGIN
                -- update [RDS].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 [RDS].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.ZIPCode   				= np.ZIPCode
                   ,p.EhrRusMdr308				= e.EhrRusID
                from [RDS].T_PHARMACY p
                    join #PHARMACY np on np.ID = p.Code
                    outer apply (
                        select top 1 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 top 1 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 [RDS].T_PHARMACY
                INSERT INTO [RDS].[T_PHARMACY]
                           ([Code] ,[Name] , [NameFull], [OGRN], [Terr], [OKATO], [DateBegin], [DateEnd], [RegisterDate], [RecipeServedTypes], [Address], [Rajon], [Number], [UsePharmacyProduct], Phone, LicenseNumber, LicenseIssue, LicenseDate, Dlo, FiasAddress, FiasHome, Mail, EhrRusBr9ap1s2qb, ZIPCode, 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 [RDS].T_RAJON r where r.[Code] = np.[Rajon])--<Rajon, varchar(255),>
                           ,np.[Number]--<Number, varchar(50),>
                           ,0
                           ,Phone
                           ,np.LicenseNumber
                           ,np.LicenseIssue
                           ,np.LicenseDate
                           ,ISNULL(Dlo, 0)
                           ,np.FiasAddress
                           ,np.FiasHome
                           ,np.Mail
                           ,e1.EhrRusID
                           ,np.ZIPCode
                           ,e.EhrRusID
                from #PHARMACY np
                    outer apply (
                        select top 1 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 top 1 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 [RDS].T_PHARMACY p
                        where p.Code = np.ID
                    )

            END
        END
    END
END
GO