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_Cmnn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_Cmnn]
GO
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2015-07-29
-- Description: Выгрузка/загрузка справочника Международных непатентованных наименований (МНН) в(из) RDS
-- =============================================
CREATE PROCEDURE [dbo].[sp_RDS_Cmnn]
	 @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(6)
           ,@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
IF (SELECT OBJECT_ID('tempdb..#T_C_MNN')) IS NOT NULL
	DROP TABLE #T_C_MNN
CREATE TABLE #T_C_MNN
	(
		 CMnnID				UNIQUEIDENTIFIER
		,NameRus			VARCHAR(1000)
		,NameEng			VARCHAR(1000)
		,OtherID			INT
		,Active				BIT
		,UsedIn				INT
		,ChemicalName		VARCHAR(600)
		,BruttoFormula		VARCHAR(100)
		,Characteristic		VARCHAR(4000)
		,Pharmacology		VARCHAR(MAX)
		,Practice			VARCHAR(6000)
		,Contraindications	VARCHAR(4000)
		,Restrictions		VARCHAR(MAX)
		,UseOfPregnancy		VARCHAR(MAX)
		,SideEffects		VARCHAR(MAX)
		,Interaction		VARCHAR(MAX)
		,InjectionMethod	VARCHAR(2000)
		,Caution			VARCHAR(MAX)
		,Overdose			VARCHAR(4000)
		,CASCode			VARCHAR(50)
		,Indications		VARCHAR(8000)
		,SingleDose			DECIMAL(17)
		,MaxDose			DECIMAL(17)
		,SingleDoseView		INT
		,MaxDoseView		INT
		,NameLatR			VARCHAR(4000)
		,FedCode			VARCHAR(50)
		,RegCode			VARCHAR(50)
	)
SET @SQL = 'INSERT INTO tempdb..#T_C_MNN
			SELECT 
				 [CMnnID]
				,[NameRus]
				,[NameEng]
				,[OtherID]
				,[Active]
				,[UsedIn]
				,[ChemicalName]
				,[BruttoFormula]
				,[Characteristic]
				,[Pharmacology]
				,[Practice]
				,[Contraindications]
				,[Restrictions]
				,[UseOfPregnancy]
				,[SideEffects]
				,[Interaction]
				,[InjectionMethod]
				,[Caution]
				,[Overdose]
				,[CASCode]
				,[Indications]
				,[SingleDose]
				,[MaxDose]
				,[SingleDoseView]
				,[MaxDoseView]
				,[NameLatR]
				,[FedCode]
				,[RegCode]
				FROM ' + @PATH + '.[PHARM].T_C_MNN'
EXECUTE (@SQL)

-- Первичная загрузка
	-- МНН не из справочника RDS переводим в неактивные
	UPDATE c
		SET c.Active = 0
	FROM [PHARM].T_C_MNN c
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM #T_C_MNN nc
	        WHERE nc.CMnnID = c.CMnnID
	    )
	        and c.UsedIn in (1,3)

	-- Обновляем имеющиеся МНН
	update c
	set c.NameEng = nc.NameEng
	   ,c.NameRus = nc.NameRus
	   ,c.NameLatR = nc.NameLatR
	   ,c.Active = nc.Active
	   ,c.OtherID = nc.OtherID
	   ,c.ChemicalName = nc.ChemicalName
	   ,c.BruttoFormula = nc.BruttoFormula
	   ,c.Characteristic = nc.Characteristic
	   ,c.Pharmacology = nc.Pharmacology
	   ,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.InjectionMethod = nc.InjectionMethod
	   ,c.Caution = nc.Caution
	   ,c.Overdose = nc.Overdose
	   ,c.CASCode = nc.CASCode
	   ,c.UsedIn = nc.UsedIn
	   ,c.RegCode = nc.RegCode
	   ,c.FedCode = nc.FedCode
	from [PHARM].T_C_MNN c
	    join #T_C_MNN nc on nc.CMnnID = c.CMnnID
	--where c.UsedIn in (1,3)
	
	-- Загружаем новые
	INSERT INTO [PHARM].[T_C_MNN]
	(
		 [CMnnID] 
		,[NameRus] 
		,[NameEng] 
		,[Active]
		,[OtherID]
		,[UsedIn]
		,[ChemicalName]
		,[BruttoFormula]
		,[Characteristic]
		,[Pharmacology]
		,[Practice]
		,[Contraindications]
		,[Restrictions]
		,[UseOfPregnancy]
		,[SideEffects]
		,[Interaction]
		,[InjectionMethod]
		,[Caution]
		,[Overdose]
		,[CASCode]
		,[RegCode]
		,[FedCode]
		,[NameLatR]
	)
	SELECT
		 nc.CMnnID--<CMnnID, uniqueidentifier,>
		,nc.NameRus--<NameRus, varchar(255),>
		,nc.NameEng--<NameEng, varchar(255),>
		,nc.Active--<Active, bit,>
		,nc.OtherID--<OtherID, int,>
		,nc.UsedIn
		,nc.ChemicalName
		,nc.BruttoFormula
		,nc.Characteristic
		,nc.Pharmacology
		,nc.Practice
		,nc.Contraindications
		,nc.Restrictions
		,nc.UseOfPregnancy
		,nc.SideEffects
		,nc.Interaction
		,nc.InjectionMethod
		,nc.Caution
		,nc.Overdose
		,nc.CASCode
		,nc.RegCode
		,nc.FedCode
		,nc.NameLatR
	FROM #T_C_MNN nc
	WHERE NOT EXISTS
	    (
	        SELECT *
				FROM [PHARM].T_C_MNN c
	        WHERE c.CMnnID = nc.CMnnID
	            --    and
	            --c.UsedIn in (1,3)
	    )

	END
	ELSE
	BEGIN

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

        -- Обработка операции
        IF (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            select @Result = 
            (
                select @FTLOAD               as 'header/TYPE'
                      ,(
                            select c.CMnnID				as 'C_MNN/ID'
                                  ,c.Active				as 'C_MNN/Active'
                                  ,c.NameRus			as 'C_MNN/NameRus'
                                  ,c.NameEng			as 'C_MNN/NameEng'
                                  ,c.NameLatR			as 'C_MNN/NameLatR'
                                  ,c.OtherID			as 'C_MNN/OtherID'
								  ,c.ChemicalName		as 'C_MNN/ChemicalName'
								  ,c.BruttoFormula		as 'C_MNN/BruttoFormula'
								  ,c.Characteristic		as 'C_MNN/Characteristic'
								  ,c.Pharmacology		as 'C_MNN/Pharmacology'
								  ,c.Practice			as 'C_MNN/Practice'
								  ,c.Contraindications	as 'C_MNN/Contraindications'
								  ,c.Restrictions		as 'C_MNN/Restrictions'
								  ,c.UseOfPregnancy		as 'C_MNN/UseOfPregnancy'
								  ,c.SideEffects		as 'C_MNN/SideEffects'
								  ,c.Interaction		as 'C_MNN/Interaction'
								  ,c.InjectionMethod	as 'C_MNN/InjectionMethod'
								  ,c.Caution			as 'C_MNN/Caution'
								  ,c.Overdose			as 'C_MNN/Overdose'
								  ,c.CASCode			as 'C_MNN/CASCode'
								  ,c.UsedIn				as 'C_MNN/UsedIn'
								  ,c.RegCode			as 'C_MNN/RegCode'
								  ,c.FedCode			as 'C_MNN/FedCode'
                            from [PHARM].T_C_MNN c
                            where c.UsedIn in (1,3)-- RDS 
                            for xml path(''),
                            type
                      ) as 'body/C_MNNS'
                for xml path(''),
                root('C_MNN')
            )
        END
        ELSE IF(@OperationType <> @DELETE)
        BEGIN

            -- собираем C_MNN
            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.ChemicalName		as 'body/ChemicalName'
					  ,c.BruttoFormula		as 'body/BruttoFormula'
					  ,c.Characteristic		as 'body/Characteristic'
					  ,c.Pharmacology		as 'body/Pharmacology'
					  ,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.InjectionMethod	as 'body/InjectionMethod'
					  ,c.Caution			as 'body/Caution'
					  ,c.Overdose			as 'body/Overdose'
					  ,c.CASCode			as 'body/CASCode'
					  ,c.UsedIn				as 'body/UsedIn'
					  ,c.RegCode			as 'body/RegCode'
					  ,c.FedCode			as 'body/FedCode'
                from [PHARM].T_C_MNN c
                where c.CMnnID = @ID
                        and
                    c.UsedIn in (1,3)
                for xml path(''),
                root('C_MNN')
            ) 
        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(6)')
        from @Xml.nodes('C_MNN') T(c)

        -- Generate table #C_MNNS
        select t.c.value('./ID[1]', 'uniqueidentifier')  as [ID]
              ,t.c.value('./Active[1]', 'bit')           as [Active]
              ,t.c.value('./NameRus[1]', 'varchar(1000)')			as [NameRus]
              ,t.c.value('./NameEng[1]', 'varchar(1000)')			as [NameEng]
              ,t.c.value('./NameLatR[1]', 'varchar(4000)')			as [NameLatR]
              ,t.c.value('./OtherID[1]', 'int')			            as [OtherID]
			  ,t.c.value('./ChemicalName[1]', 'varchar(600)')		as ChemicalName
			  ,t.c.value('./BruttoFormula[1]', 'varchar(100)')		as BruttoFormula
			  ,t.c.value('./Characteristic[1]', 'varchar(4000)')	as Characteristic
			  ,t.c.value('./Pharmacology[1]', 'varchar(max)')		as Pharmacology
			  ,t.c.value('./Practice[1]', 'varchar(6000)')			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('./InjectionMethod[1]', 'varchar(4000)')	as InjectionMethod
			  ,t.c.value('./Caution[1]', 'varchar(max)')			as Caution
			  ,t.c.value('./Overdose[1]', 'varchar(4000)')			as Overdose
			  ,t.c.value('./CASCode[1]', 'varchar(50)')				as CASCode
			  ,t.c.value('./UsedIn[1]', 'int')						as UsedIn
			  ,t.c.value('./RegCode[1]', 'varchar(50)')				as RegCode
			  ,t.c.value('./FedCode[1]', 'varchar(50)')				as FedCode
            INTO #C_MNNS
        from @Xml.nodes('C_MNN/body/C_MNNS/C_MNN') T(c)
        where @OperationType = @FTLOAD

        -- Generate table #C_MNN
        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(1000)')			as [NameRus]
              ,t.c.value('./body[1]/NameEng[1]', 'varchar(1000)')			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]/ChemicalName[1]', 'varchar(600)')		as ChemicalName
			  ,t.c.value('./body[1]/BruttoFormula[1]', 'varchar(100)')		as BruttoFormula
			  ,t.c.value('./body[1]/Characteristic[1]', 'varchar(4000)')	as Characteristic
			  ,t.c.value('./body[1]/Pharmacology[1]', 'varchar(max)')		as Pharmacology
			  ,t.c.value('./body[1]/Practice[1]', 'varchar(6000)')			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]/InjectionMethod[1]', 'varchar(4000)')	as InjectionMethod
			  ,t.c.value('./body[1]/Caution[1]', 'varchar(max)')			as Caution
			  ,t.c.value('./body[1]/Overdose[1]', 'varchar(4000)')			as Overdose
			  ,t.c.value('./body[1]/CASCode[1]', 'varchar(50)')				as CASCode
			  ,t.c.value('./body[1]/UsedIn[1]', 'int')						as UsedIn
			  ,t.c.value('./body[1]/RegCode[1]', 'varchar(50)')				as RegCode
			  ,t.c.value('./body[1]/FedCode[1]', 'varchar(50)')				as FedCode
            INTO #C_MNN
        from @Xml.nodes('C_MNN') T(c)
        where @OperationType <> @FTLOAD

        -- Обработка операции
        if (@OperationType = @FTLOAD)
        BEGIN
            -- Первичная загрузка
            -- МНН не из справочника RDS переводим в неактивные
            update c
            set c.Active = 0
            from [PHARM].T_C_MNN c
            where not exists
                (
                    select *
                    from #C_MNNS nc
                    where nc.ID = c.CMnnID
                )
                    and
                c.UsedIn in (1,3)

            -- Обновляем имеющиеся МНН
            update c
            set c.NameEng = nc.NameEng
               ,c.NameRus = nc.NameRus
               ,c.NameLatR = nc.NameLatR
               ,c.Active = nc.Active
               ,c.OtherID = nc.OtherID
			   ,c.ChemicalName = nc.ChemicalName
			   ,c.BruttoFormula = nc.BruttoFormula
			   ,c.Characteristic = nc.Characteristic
			   ,c.Pharmacology = nc.Pharmacology
			   ,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.InjectionMethod = nc.InjectionMethod
			   ,c.Caution = nc.Caution
			   ,c.Overdose = nc.Overdose
			   ,c.CASCode = nc.CASCode
			   ,c.UsedIn = nc.UsedIn
			   ,c.RegCode = nc.RegCode
			   ,c.FedCode = nc.FedCode
            from [PHARM].T_C_MNN c
                join #C_MNNS nc on nc.ID = c.CMnnID
            --where c.UsedIn in (1,3)

            -- Загружаем новые
            INSERT INTO [PHARM].[T_C_MNN]
                       ([CMnnID] ,[NameRus] ,[NameEng] ,[Active], [OtherID], [UsedIn]
					   ,ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
					   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, Caution, Overdose, CASCode,RegCode,FedCode,NameLatR)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(255),>
                       ,nc.NameEng--<NameEng, varchar(255),>
                       ,nc.Active--<Active, bit,>
                       ,nc.OtherID--<OtherID, int,>
                       ,nc.UsedIn
					   ,nc.ChemicalName
					   ,nc.BruttoFormula
					   ,nc.Characteristic
					   ,nc.Pharmacology
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Overdose
					   ,nc.CASCode
					   ,nc.RegCode
					   ,nc.FedCode
                       ,nc.NameLatR
            from #C_MNNS nc
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN c
                    where c.CMnnID = nc.ID
                        --    and
                        --c.UsedIn in (1,3)
                )
        END
        else if(@OperationType = @DELETE)
        BEGIN
            -- Удаление
            update c
            set c.Active = 0
            from [PHARM].[T_C_MNN] c
                join #C_MNN nc on nc.[ID] = c.CMnnID
            --where c.UsedIn in (1,3)
        END
        else
        BEGIN
            -- update [PHARM].T_C_MNN
            update c
            set c.NameEng = nc.NameEng
               ,c.NameRus = nc.NameRus
               ,c.NameLatR = nc.NameLatR
               ,c.Active = nc.Active
               ,c.OtherID = nc.OtherID
			   ,c.ChemicalName = nc.ChemicalName
			   ,c.BruttoFormula = nc.BruttoFormula
			   ,c.Characteristic = nc.Characteristic
			   ,c.Pharmacology = nc.Pharmacology
			   ,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.InjectionMethod = nc.InjectionMethod
			   ,c.Caution = nc.Caution
			   ,c.Overdose = nc.Overdose
			   ,c.CASCode = nc.CASCode
			   ,c.UsedIn = nc.UsedIn
			   ,c.RegCode = nc.RegCode
			   ,c.FedCode = nc.FedCode
            from [PHARM].T_C_MNN c
                join #C_MNN nc on nc.ID = c.CMnnID
            where @OperationType = @UPDATE
                --    and
                --c.UsedIn in (1,3)

            -- insert [PHARM].T_C_MNN
            INSERT INTO [PHARM].[T_C_MNN]
                ([CMnnID] ,[NameRus] ,[NameEng] ,[Active], [OtherID], [UsedIn]
					   ,ChemicalName, BruttoFormula, Characteristic, Pharmacology, Practice, Contraindications, 
					   Restrictions, UseOfPregnancy, SideEffects, Interaction, InjectionMethod, 
                       Caution, Overdose, CASCode, RegCode, FedCode,NameLatR)
            SELECT
                       nc.ID--<CMnnID, uniqueidentifier,>
                       ,nc.NameRus--<NameRus, varchar(255),>
                       ,nc.NameEng--<NameEng, varchar(255),>
                       ,nc.Active--<Active, bit,>
                       ,nc.OtherID--<OtherID, int,>
                       ,[UsedIn]--<UsedIn, bit,>
					   ,nc.ChemicalName
					   ,nc.BruttoFormula
					   ,nc.Characteristic
					   ,nc.Pharmacology
					   ,nc.Practice
					   ,nc.Contraindications
					   ,nc.Restrictions
					   ,nc.UseOfPregnancy
					   ,nc.SideEffects
					   ,nc.Interaction
					   ,nc.InjectionMethod
					   ,nc.Caution
					   ,nc.Overdose
					   ,nc.CASCode
					   ,nc.RegCode
					   ,nc.FedCode
					   ,nc.NameLatR
            from #C_MNN nc
            where not exists
                (
                    select *
                    from [PHARM].T_C_MNN c
                    where c.CMnnID = nc.ID
                        --    and
                        --c.UsedIn in (1,3)
                )
                    and
                @OperationType = @INSERT
        END

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

        -- Drop tables
        DROP TABLE #C_MNN
        DROP TABLE #C_MNNS

    END
END
END
GO