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_Ctrn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Ctrn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-12-22
-- Description: Выгрузка/загрузка справочника Торговое наименование препарата (ТРН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_Ctrn]
     @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'

IF @PATH IS NOT NULL
	BEGIN
CREATE TABLE #T_C_TRN
	(
		 CTrnID					UNIQUEIDENTIFIER
		,OtherID				INT
		,NameRus				VARCHAR(4000)
		,NameEng				VARCHAR(4000)
		,Active					BIT
		,Characteristic			VARCHAR(4000)
		,PharmacologyAction		VARCHAR(4000)
		,Practice				VARCHAR(max)
		,Contraindications		VARCHAR(5000)
		,Restrictions			VARCHAR(max)
		,UseOfPregnancy			VARCHAR(max)
		,SideEffects			VARCHAR(max)
		,Interaction			VARCHAR(max)
		,Overdose				VARCHAR(max)
		,InjectionMethod		VARCHAR(255)
		,Caution				VARCHAR(max)
		,Composition			VARCHAR(3000)
		,PharmDescription		VARCHAR(max)
		,Pharmacodynamics		VARCHAR(max)
		,Indications			VARCHAR(8000)
		,ExpirationDate			VARCHAR(300)
		,MNNName				VARCHAR(8000)
		,Manufacturer			VARCHAR(3000)
		,CMnn					UNIQUEIDENTIFIER
		,ProductType			INT
		,NameLatR				VARCHAR(4000)
		,FedCode				VARCHAR(50)
		,RegCode				VARCHAR(50)
		,EhrRusP08TSH954N		VARCHAR(52)
		,EhrRusP08TSH954NVer	VARCHAR(8)
		,EhrRus575VSF88OG		VARCHAR(52)
		,EhrRus575VSF88OGVer	VARCHAR(8)
		,Atc					int
		,AtcVer					VARCHAR(8)
	)
SET @SQL = 'INSERT INTO tempdb..#T_C_TRN
			SELECT
				 [CTrnID]
				,[OtherID]
				,[NameRus]
				,[NameEng]
				,[Active]
				,[Characteristic]
				,[PharmacologyAction]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[Overdose]
				,[InjectionMethod]
				,[Caution]
				,[Composition]
				,[PharmDescription]
				,[Pharmacodynamics]
				,[Indications]
				,[ExpirationDate]
				,[MNNName]
				,[Manufacturer]
				,[CMnn]
				,[ProductType]
				,[NameLatR]
				,[FedCode]
				,[RegCode]
				,ehr1.[CodeEHR]
				,RRB.CurrVersion
				,ehr2.[CodeEHR]
				,RRB1.CurrVersion
				,Atc.[IdEHR]
				,RRBAtc.CurrVersion
			FROM ' + @PATH + '.[PHARM].T_C_TRN nc
			left join ' + @PATH + '.EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = nc.EhrRusP08TSH954N
			    LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = nc.EhrRus575VSF88OG
			    LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
			left join ' + @PATH + '.EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = nc.Atc
			    LEFT JOIN ' + @PATH + '.EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks'
EXECUTE (@SQL)

	-- МНН не из справочника RDS переводим в неактивные
	UPDATE c
	SET c.Active = 0
		FROM [PHARM].T_C_TRN c
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_C_TRN nc
	        WHERE nc.CTrnID = c.CTrnID
	    )

	-- Обновляем имеющиеся TRN
	update c
	set c.NameEng = nc.NameEng
	   ,c.NameRus = nc.NameRus
	   ,c.NameLatR = nc.NameLatR
	   ,c.Active = nc.Active
	   ,c.OtherID = nc.OtherID
	   ,c.Characteristic = nc.Characteristic
	   ,c.PharmacologyAction = nc.PharmacologyAction
	   ,c.Practice = nc.Practice
	   ,c.Contraindications = nc.Contraindications
	   ,c.Restrictions = nc.Restrictions
	   ,c.UseOfPregnancy = nc.UseOfPregnancy
	   ,c.SideEffects = nc.SideEffects
	   ,c.Interaction = nc.Interaction
	   ,c.Overdose = nc.Overdose
	   ,c.InjectionMethod = nc.InjectionMethod
	   ,c.Caution = nc.Caution
	   ,c.Composition = nc.Composition
	   ,c.PharmDescription = nc.PharmDescription
	   ,c.Pharmacodynamics = nc.Pharmacodynamics
	   ,c.Indications = nc.Indications
	   ,c.ExpirationDate = nc.ExpirationDate
	   ,c.MNNName = nc.MNNName
	   ,c.Manufacturer = nc.Manufacturer
	   ,c.CMnn = nc.CMnn
	   ,c.Atc = atc.EhrRusID
	   ,c.ProductType = nc.ProductType
	   ,c.RegCode = nc.RegCode
	   ,c.FedCode = nc.FedCode
	   ,c.EhrRusP08TSH954N = eh1.EhrRusID
	   ,c.EhrRus575VSF88OG = eh2.EhrRusID
	from [PHARM].T_C_TRN c
	    join #T_C_TRN nc on nc.CTrnID = c.CTrnID
		outer apply (
			select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_P08TSH954N eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CurrVersion = nc.EhrRusP08TSH954NVer
		)eh1
		outer apply (
			select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_575VSF88OG eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CurrVersion = nc.EhrRus575VSF88OGVer
		)eh2
		outer apply (
			select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_M00002 eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
		) atc

	-- Загружаем новые
	INSERT INTO [PHARM].[T_C_TRN]
			(
				 [CTrnID]
				,[NameRus]
				,[NameEng]
				,[Active]
				,[OtherID]
				,[Characteristic]
				,[PharmacologyAction]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[Overdose]
				,[InjectionMethod]
				,[Caution]
				,[Composition]
				,[PharmDescription]
				,[Pharmacodynamics]
				,[Indications]
				,[ExpirationDate]
				,[MNNName]
				,[Manufacturer]
				,[CMnn]
				,[Atc]
				,[ProductType]
				,[RegCode]
				,[FedCode]
				,[EhrRusP08TSH954N]
				,[EhrRus575VSF88OG]
				,[NameLatR]
			)

	SELECT
				 nc.[CTrnID]--<CMnnID, uniqueidentifier,>
				,nc.NameRus--<NameRus, varchar(4000),>
				,nc.NameEng--<NameEng, varchar(4000),>
				,nc.Active--<Active, bit,>
				,nc.OtherID--<OtherID, int,>
				,nc.Characteristic
				,nc.PharmacologyAction
				,nc.Practice
				,nc.Contraindications
				,nc.Restrictions
				,nc.UseOfPregnancy
				,nc.SideEffects
				,nc.Interaction
				,nc.Overdose
				,nc.InjectionMethod
				,nc.Caution
				,nc.Composition
				,nc.PharmDescription
				,nc.Pharmacodynamics
				,nc.Indications
				,nc.ExpirationDate
				,nc.MNNName
				,nc.Manufacturer
				,nc.CMnn
				,atc.EhrRusID
				,nc.ProductType
				,RegCode
				,FedCode
				,eh1.EhrRusID
				,eh2.EhrRusID
				,nc.NameLatR
	from #T_C_TRN nc
		outer apply (
			select top 1 eh.EhrRusID
			from EHR.T_EHR_RUS_P08TSH954N eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CurrVersion = nc.EhrRusP08TSH954NVer
		)eh1
		outer apply (
			select top 1 eh.EhrRusID
			from EHR.T_EHR_RUS_575VSF88OG eh
				LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CurrVersion = nc.EhrRus575VSF88OGVer
		)eh2
		outer apply (
			select top 1 eh.EhrRusID
				from EHR.T_EHR_RUS_M00002 eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
			where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
		) atc
	where not exists
	    (
	        select *
	        from [PHARM].T_C_TRN c
	        where c.CTrnID = nc.[CTrnID]
	    )

	END
	ELSE
	BEGIN

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result =
            (
                select @FTLOAD               as 'header/TYPE'
                      ,(
                            select c.CTrnID             as 'C_TRN/ID'
                                  ,c.Active             as 'C_TRN/Active'
                                  ,c.NameRus            as 'C_TRN/NameRus'
                                  ,c.NameEng            as 'C_TRN/NameEng'
                                  ,c.NameLatR           as 'C_TRN/NameLatR'
                                  ,c.OtherID            as 'C_TRN/OtherID'
								  ,c.Characteristic		as 'C_TRN/Characteristic'
								  ,c.PharmacologyAction as 'C_TRN/PharmacologyAction'
								  ,c.Practice			as 'C_TRN/Practice'
								  ,c.Contraindications  as 'C_TRN/Contraindications'
								  ,c.Restrictions		as 'C_TRN/Restrictions'
								  ,c.UseOfPregnancy		as 'C_TRN/UseOfPregnancy'
								  ,c.SideEffects		as 'C_TRN/SideEffects'
								  ,c.Interaction		as 'C_TRN/Interaction'
								  ,c.Overdose			as 'C_TRN/Overdose'
								  ,c.InjectionMethod	as 'C_TRN/InjectionMethod'
								  ,c.Caution			as 'C_TRN/Caution'
								  ,c.Composition		as 'C_TRN/Composition'
								  ,c.PharmDescription	as 'C_TRN/PharmDescription'
								  ,c.Pharmacodynamics	as 'C_TRN/Pharmacodynamics'
								  ,c.Indications		as 'C_TRN/Indications'
								  ,c.ExpirationDate		as 'C_TRN/ExpirationDate'
								  ,c.MNNName			as 'C_TRN/MNNName'
								  ,c.Manufacturer		as 'C_TRN/Manufacturer'
								  ,c.CMnn				as 'C_TRN/CMnn'
								  ,c.ProductType		as 'C_TRN/ProductType'
								  ,RegCode				as 'C_TRN/RegCode'
								  ,FedCode				as 'C_TRN/FedCode'
								  ,ehr1.CodeEHR			as 'C_TRN/EhrRusP08TSH954N'
								  ,RRB.CurrVersion		as 'C_TRN/EhrRusP08TSH954NVer'
								  ,ehr2.CodeEHR			as 'C_TRN/EhrRus575VSF88OG'
								  ,RRB1.CurrVersion		as 'C_TRN/EhrRus575VSF88OGVer'
								  ,atc.IdEHR			as 'C_TRN/Atc'
								  ,RRBAtc.CurrVersion	as 'C_TRN/AtcVer'
                            from [PHARM].T_C_TRN c
								left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
								left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
								left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
								LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
                            for xml path(''),
                            type
                      ) as 'body/C_TRNS'
                for xml path(''),
                root('C_TRN')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем C_TRN
            SELECT @Result =
            (
                select @OperationType			AS 'header/TYPE'
                      ,@ID						AS 'header/ID'
                      ,c.Active					AS 'body/Active'
                      ,c.NameRus				AS 'body/NameRus'
                      ,c.NameEng				AS 'body/NameEng'
                      ,c.NameLatR				AS 'body/NameLatR'
                      ,c.OtherID				AS 'body/OtherID'
					  ,c.Characteristic			AS 'body/Characteristic'
					  ,c.PharmacologyAction		AS 'body/PharmacologyAction'
					  ,c.Practice				AS 'body/Practice'
					  ,c.Contraindications		AS 'body/Contraindications'
					  ,c.Restrictions			AS 'body/Restrictions'
					  ,c.UseOfPregnancy			AS 'body/UseOfPregnancy'
					  ,c.SideEffects			AS 'body/SideEffects'
					  ,c.Interaction			AS 'body/Interaction'
					  ,c.Overdose				AS 'body/Overdose'
					  ,c.InjectionMethod		AS 'body/InjectionMethod'
					  ,c.Caution				AS 'body/Caution'
					  ,c.Composition			AS 'body/Composition'
					  ,c.PharmDescription		AS 'body/PharmDescription'
					  ,c.Pharmacodynamics		AS 'body/Pharmacodynamics'
					  ,c.Indications			AS 'body/Indications'
					  ,c.ExpirationDate			AS 'body/ExpirationDate'
					  ,c.MNNName				AS 'body/MNNName'
					  ,c.Manufacturer			AS 'body/Manufacturer'
					  ,c.CMnn					AS 'body/CMnn'
					  ,c.ProductType			AS 'body/ProductType'
					  ,RegCode					AS 'body/RegCode'
					  ,FedCode					AS 'body/FedCode'
					  ,ehr1.CodeEHR				AS 'body/EhrRusP08TSH954N'
					  ,RRB.CurrVersion			AS 'body/EhrRusP08TSH954NVer'
					  ,ehr2.CodeEHR				AS 'body/EhrRus575VSF88OG'
					  ,RRB1.CurrVersion			AS 'body/EhrRus575VSF88OGVer'
					  ,atc.IdEHR				as 'body/Atc'
					  ,RRBAtc.CurrVersion		as 'body/AtcVer'
                from [PHARM].T_C_TRN c
					left join EHR.T_EHR_RUS_P08TSH954N ehr1 on ehr1.EhrRusID = c.EhrRusP08TSH954N
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = ehr1.EhrRusRefBooks
					left join EHR.T_EHR_RUS_575VSF88OG ehr2 on ehr2.EhrRusID = c.EhrRus575VSF88OG
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = ehr2.EhrRusRefBooks
					left join EHR.T_EHR_RUS_M00002 atc on atc.EhrRusID = c.Atc
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = atc.EhrRusRefBooks
                where c.CTrnID = @ID
                for xml path(''),
                root('C_TRN')
            )
        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('C_TRN') T(c)

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Generate table #C_TRNS
            SELECT t.c.value('./ID[1]', 'varchar(255)')                  AS ID,
                   t.c.value('./Active[1]', 'bit')                       AS Active,
                   t.c.value('./NameRus[1]', 'varchar(4000)')            AS NameRus,
                   t.c.value('./NameEng[1]', 'varchar(4000)')            AS NameEng,
                   t.c.value('./NameLatR[1]', 'varchar(4000)')           AS NameLatR,
                   t.c.value('./OtherID[1]', 'int')                      AS OtherID,
                   t.c.value('./Characteristic[1]', 'varchar(4000)')     AS Characteristic,
                   t.c.value('./PharmacologyAction[1]', 'varchar(4000)') AS PharmacologyAction,
                   t.c.value('./Practice[1]', 'varchar(max)')            AS Practice,
                   t.c.value('./Contraindications[1]', 'varchar(4000)')  AS Contraindications,
                   t.c.value('./Restrictions[1]', 'varchar(max)')        AS Restrictions,
                   t.c.value('./UseOfPregnancy[1]', 'varchar(max)')      AS UseOfPregnancy,
                   t.c.value('./SideEffects[1]', 'varchar(max)')         AS SideEffects,
                   t.c.value('./Interaction[1]', 'varchar(max)')         AS Interaction,
                   t.c.value('./Overdose[1]', 'varchar(1000)')           AS Overdose,
                   t.c.value('./InjectionMethod[1]', 'varchar(255)')     AS InjectionMethod,
                   t.c.value('./Caution[1]', 'varchar(max)')             AS Caution,
                   t.c.value('./Composition[1]', 'varchar(255)')         AS Composition,
                   t.c.value('./PharmDescription[1]', 'varchar(max)')    AS PharmDescription,
                   t.c.value('./Pharmacodynamics[1]', 'varchar(max)')    AS Pharmacodynamics,
                   t.c.value('./Indications[1]', 'varchar(8000)')        AS Indications,
                   t.c.value('./ExpirationDate[1]', 'varchar(300)')      AS ExpirationDate,
                   t.c.value('./MNNName[1]', 'varchar(8000)')            AS MNNName,
                   t.c.value('./Manufacturer[1]', 'varchar(3000)')       AS Manufacturer,
                   t.c.value('./CMnn[1]', 'uniqueidentifier')            AS CMnn,
                   t.c.value('./Atc[1]', 'int')                          AS Atc,
                   t.c.value('./AtcVer[1]', 'varchar(52)')               AS AtcVer,
                   t.c.value('./ProductType[1]', 'int')                  AS ProductType,
                   t.c.value('./RegCode[1]', 'varchar(50)')              AS RegCode,
                   t.c.value('./FedCode[1]', 'varchar(50)')              AS FedCode,
                   t.c.value('./EhrRusP08TSH954N[1]', 'varchar(52)')     AS EhrRusP08TSH954N,
                   t.c.value('./EhrRusP08TSH954NVer[1]', 'varchar(8)')   AS EhrRusP08TSH954NVer,
                   t.c.value('./EhrRus575VSF88OG[1]', 'varchar(52)')     AS EhrRus575VSF88OG,
                   t.c.value('./EhrRus575VSF88OGVer[1]', 'varchar(8)')   AS EhrRus575VSF88OGVer
            INTO #C_TRNS
            FROM @Xml.nodes('C_TRN/body/C_TRNS/C_TRN') T(c)

            -- Первичная загрузка
            -- МНН не из справочника RDS переводим в неактивные
            update c
            set c.Active = 0
            from [PHARM].T_C_TRN c
            where not exists
                (
                    select *
                    from #C_TRNS nc
                    where nc.ID = c.CTrnID
                )

            -- Обновляем имеющиеся TRN
            update c
            set c.NameEng = nc.NameEng
               ,c.NameRus = nc.NameRus
               ,c.NameLatR = nc.NameLatR
               ,c.Active = nc.Active
               ,c.OtherID = nc.OtherID
			   ,c.Characteristic = nc.Characteristic
			   ,c.PharmacologyAction = nc.PharmacologyAction
			   ,c.Practice = nc.Practice
			   ,c.Contraindications = nc.Contraindications
			   ,c.Restrictions = nc.Restrictions
			   ,c.UseOfPregnancy = nc.UseOfPregnancy
			   ,c.SideEffects = nc.SideEffects
			   ,c.Interaction = nc.Interaction
			   ,c.Overdose = nc.Overdose
			   ,c.InjectionMethod = nc.InjectionMethod
			   ,c.Caution = nc.Caution
			   ,c.Composition = nc.Composition
			   ,c.PharmDescription = nc.PharmDescription
			   ,c.Pharmacodynamics = nc.Pharmacodynamics
			   ,c.Indications = nc.Indications
			   ,c.ExpirationDate = nc.ExpirationDate
			   ,c.MNNName = nc.MNNName
			   ,c.Manufacturer = nc.Manufacturer
			   ,c.CMnn = nc.CMnn
			   ,c.Atc = atc.EhrRusID
			   ,c.ProductType = nc.ProductType
			   ,c.RegCode = nc.RegCode
			   ,c.FedCode = nc.FedCode
			   ,c.EhrRusP08TSH954N = eh1.EhrRusID
			   ,c.EhrRus575VSF88OG = eh2.EhrRusID
            from [PHARM].T_C_TRN c
                join #C_TRNS nc on nc.ID = c.CTrnID
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CurrVersion = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_575VSF88OG eh
					LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CurrVersion = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				) atc

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_TRN]
                       ([CTrnID] ,[NameRus] ,[NameEng] ,[Active], [OtherID]
					   ,Characteristic,PharmacologyAction,Practice,Contraindications,Restrictions,
                       UseOfPregnancy,SideEffects,Interaction,Overdose,InjectionMethod,Caution,Composition,PharmDescription,Pharmacodynamics,Indications,
                       ExpirationDate,MNNName,Manufacturer,CMnn,Atc,ProductType,RegCode,FedCode,EhrRusP08TSH954N,EhrRus575VSF88OG,NameLatR)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(4000),>
                       ,nc.NameEng--<NameEng, varchar(4000),>
                       ,nc.Active--<Active, bit,>
                       ,nc.OtherID--<OtherID, int,>
					   ,nc.Characteristic
					   ,nc.PharmacologyAction
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.Overdose
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Composition
					   ,nc.PharmDescription
					   ,nc.Pharmacodynamics
					   ,nc.Indications
					   ,nc.ExpirationDate
					   ,nc.MNNName
					   ,nc.Manufacturer
					   ,nc.CMnn
					   ,atc.EhrRusID
					   ,nc.ProductType
					   ,RegCode
					   ,FedCode
					   ,eh1.EhrRusID
					   ,eh2.EhrRusID
                       ,nc.NameLatR
            from #C_TRNS nc
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_P08TSH954N eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CurrVersion = nc.EhrRusP08TSH954NVer
				)eh1
				outer apply (
					select top 1 eh.EhrRusID
					from EHR.T_EHR_RUS_575VSF88OG eh
						LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CurrVersion = nc.EhrRus575VSF88OGVer
				)eh2
				outer apply (
					select top 1 eh.EhrRusID
						from EHR.T_EHR_RUS_M00002 eh
							LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
					where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
				) atc
            where not exists
                (
                    select *
                    from [PHARM].T_C_TRN c
                    where c.CTrnID = nc.ID
                )
        END
        ELSE
        BEGIN
            -- Generate table #C_TRN
            SELECT T.c.value('./header[1]/ID[1]', 'uniqueidentifier')            AS ID,
                   T.c.value('./body[1]/Active[1]', 'bit')                       AS Active,
                   t.c.value('./body[1]/NameRus[1]', 'varchar(4000)')            AS NameRus,
                   t.c.value('./body[1]/NameEng[1]', 'varchar(4000)')            AS NameEng,
                   t.c.value('./body[1]/NameLatR[1]', 'varchar(4000)')           AS NameLatR,
                   T.c.value('./body[1]/OtherID[1]', 'int')                      AS OtherID,
                   t.c.value('./body[1]/Characteristic[1]', 'varchar(4000)')     AS Characteristic,
                   t.c.value('./body[1]/PharmacologyAction[1]', 'varchar(4000)') AS PharmacologyAction,
                   t.c.value('./body[1]/Practice[1]', 'varchar(max)')            AS Practice,
                   t.c.value('./body[1]/Contraindications[1]', 'varchar(4000)')  AS Contraindications,
                   t.c.value('./body[1]/Restrictions[1]', 'varchar(max)')        AS Restrictions,
                   t.c.value('./body[1]/UseOfPregnancy[1]', 'varchar(max)')      AS UseOfPregnancy,
                   t.c.value('./body[1]/SideEffects[1]', 'varchar(max)')         AS SideEffects,
                   t.c.value('./body[1]/Interaction[1]', 'varchar(max)')         AS Interaction,
                   t.c.value('./body[1]/Overdose[1]', 'varchar(1000)')           AS Overdose,
                   t.c.value('./body[1]/InjectionMethod[1]', 'varchar(255)')     AS InjectionMethod,
                   t.c.value('./body[1]/Caution[1]', 'varchar(max)')             AS Caution,
                   t.c.value('./body[1]/Composition[1]', 'varchar(255)')         AS Composition,
                   t.c.value('./body[1]/PharmDescription[1]', 'varchar(max)')    AS PharmDescription,
                   t.c.value('./body[1]/Pharmacodynamics[1]', 'varchar(max)')    AS Pharmacodynamics,
                   t.c.value('./body[1]/Indications[1]', 'varchar(8000)')        AS Indications,
                   t.c.value('./body[1]/ExpirationDate[1]', 'varchar(300)')      AS ExpirationDate,
                   t.c.value('./body[1]/MNNName[1]', 'varchar(8000)')            AS MNNName,
                   t.c.value('./body[1]/Manufacturer[1]', 'varchar(3000)')       AS Manufacturer,
                   t.c.value('./body[1]/CMnn[1]', 'uniqueidentifier')            AS CMnn,
                   t.c.value('./body[1]/Atc[1]', 'int')                          AS Atc,
                   t.c.value('./body[1]/AtcVer[1]', 'varchar(52)')               AS AtcVer,
                   t.c.value('./body[1]/ProductType[1]', 'int')                  AS ProductType,
                   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]/EhrRusP08TSH954N[1]', 'varchar(52)')     AS EhrRusP08TSH954N,
                   t.c.value('./body[1]/EhrRusP08TSH954NVer[1]', 'varchar(8)')   AS EhrRusP08TSH954NVer,
                   t.c.value('./body[1]/EhrRus575VSF88OG[1]', 'varchar(52)')     AS EhrRus575VSF88OG,
                   t.c.value('./body[1]/EhrRus575VSF88OGVer[1]', 'varchar(8)')   AS EhrRus575VSF88OGVer
            INTO #C_TRN
            FROM @Xml.nodes('C_TRN') T(c)

            IF(@OperationType = @DELETE)
            BEGIN
                -- Удаление
                update c
                set c.Active = 0
                from [PHARM].[T_C_TRN] c
                    join #C_TRN nc on nc.[ID] = c.CTrnID
            END
            else
            BEGIN
                -- update [PHARM].T_C_TRN
                update c
                set c.NameEng = nc.NameEng
                   ,c.NameRus = nc.NameRus
                   ,c.NameLatR = nc.NameLatR
                   ,c.OtherID = nc.OtherID
                   ,c.Characteristic = nc.Characteristic
                   ,c.PharmacologyAction = nc.PharmacologyAction
                   ,c.Practice = nc.Practice
                   ,c.Contraindications = nc.Contraindications
                   ,c.Restrictions = nc.Restrictions
                   ,c.UseOfPregnancy = nc.UseOfPregnancy
                   ,c.SideEffects = nc.SideEffects
                   ,c.Interaction = nc.Interaction
                   ,c.Overdose = nc.Overdose
                   ,c.InjectionMethod = nc.InjectionMethod
                   ,c.Caution = nc.Caution
                   ,c.Composition = nc.Composition
                   ,c.PharmDescription = nc.PharmDescription
                   ,c.Pharmacodynamics = nc.Pharmacodynamics
                   ,c.Indications = nc.Indications
                   ,c.ExpirationDate = nc.ExpirationDate
                   ,c.MNNName = nc.MNNName
                   ,c.Manufacturer = nc.Manufacturer
                   ,c.CMnn = nc.CMnn
                   ,c.Atc = atc.EhrRusID
                   ,c.ProductType = nc.ProductType
                   ,c.RegCode = nc.RegCode
                   ,c.FedCode = nc.FedCode
                   ,c.EhrRusP08TSH954N = eh1.EhrRusID
                   ,c.EhrRus575VSF88OG = eh2.EhrRusID
                from [PHARM].T_C_TRN c
                    join #C_TRN nc on nc.ID = c.CTrnID
                    outer apply (
                        select top 1 eh.EhrRusID
                        from EHR.T_EHR_RUS_P08TSH954N eh
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
                    )eh1
                    outer apply (
                        select top 1 eh.EhrRusID
                        from EHR.T_EHR_RUS_575VSF88OG eh
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CURRVERSION = nc.EhrRus575VSF88OGVer
                    )eh2
                    outer apply (
                        select top 1 eh.EhrRusID
                            from EHR.T_EHR_RUS_M00002 eh
                                LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
                    ) atc
                where @OperationType = @UPDATE

                -- insert [PHARM].T_C_TRN
                INSERT INTO [PHARM].[T_C_TRN]
                    ([CTrnID] ,[NameRus] ,[NameEng], [Active], [OtherID]
                    ,Characteristic,PharmacologyAction,Practice,Contraindications,Restrictions,UseOfPregnancy,SideEffects,Interaction,Overdose,InjectionMethod,Caution,
                    Composition,PharmDescription,Pharmacodynamics,Indications,ExpirationDate,MNNName,Manufacturer,CMnn,Atc,ProductType,RegCode,FedCode,EhrRusP08TSH954N,EhrRus575VSF88OG,NameLatR)
                SELECT
                           nc.ID--<CMnnID, uniqueidentifier,>
                           ,nc.NameRus--<NameRus, varchar(4000),>
                           ,nc.NameEng--<NameEng, varchar(4000),>
                           ,nc.Active--<Active, bit,>
                           ,nc.OtherID--<OtherID, int,>
                           ,nc.Characteristic
                           ,nc.PharmacologyAction
                           ,nc.Practice
                           ,nc.Contraindications
                           ,nc.Restrictions
                           ,nc.UseOfPregnancy
                           ,nc.SideEffects
                           ,nc.Interaction
                           ,nc.Overdose
                           ,nc.InjectionMethod
                           ,nc.Caution
                           ,nc.Composition
                           ,nc.PharmDescription
                           ,nc.Pharmacodynamics
                           ,nc.Indications
                           ,nc.ExpirationDate
                           ,nc.MNNName
                           ,nc.Manufacturer
                           ,nc.CMnn
                           ,Atc.EhrRusID
                           ,nc.ProductType
                           ,RegCode
                           ,FedCode
                           ,eh1.EhrRusID
                           ,eh2.EhrRusID
                           ,nc.NameLatR
                from #C_TRN nc
                    outer apply (
                        select top 1 eh.EhrRusID
                        from EHR.T_EHR_RUS_P08TSH954N eh
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB ON RRB.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.CodeEHR = nc.EhrRusP08TSH954N and RRB.CURRVERSION = nc.EhrRusP08TSH954NVer
                    )eh1
                    outer apply (
                        select top 1 eh.EhrRusID
                        from EHR.T_EHR_RUS_575VSF88OG eh
                            LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRB1 ON RRB1.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.CodeEHR = nc.EhrRus575VSF88OG and RRB1.CURRVERSION = nc.EhrRus575VSF88OGVer
                    )eh2
                    outer apply (
                        select top 1 eh.EhrRusID
                            from EHR.T_EHR_RUS_M00002 eh
                                LEFT JOIN EHR.T_EHR_RUS_REF_BOOKS RRBAtc ON RRBAtc.EhrRusRefBooksID = eh.EhrRusRefBooks
                        where eh.IdEHR = nc.Atc and RRBAtc.CurrVersion = nc.AtcVer
                    ) atc
                where not exists
                    (
                        select *
                        from [PHARM].T_C_TRN c
                        where c.CTrnID = nc.ID
                    )
                        and
                    @OperationType = @INSERT
            END
        END


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

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