if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GenerateSchetOutNumber_68]') and xtype = N'P')
drop procedure [dbo].[sp_GenerateSchetOutNumber_68]
GO

-- =============================================
-- Author:		(C) Vitacard System 2009
-- Create date: 2014-04-03
-- Description:	Генерирует номер исходящего счета
-- =============================================
CREATE PROCEDURE [dbo].[sp_GenerateSchetOutNumber_68]
	@xml xml, @Next varchar(64) out
AS 
BEGIN 
declare @trancount int 
	declare @NextNumber int 
	declare @ReturnValue varchar(64)

	declare @msg as varchar(64) 

	set @NextNumber = null 
	set @Next = null
	set nocount on  
	select @trancount = @@trancount 
	if @trancount = 0 begin tran 
	 
	declare @NumberName varchar(64)
	set @NumberName =( select 'SchetNumber-' 
		+ (t.c.value('./NSCHET[1]', 'varchar(50)')) 
		+ '-'
		+ (t.c.value('./YEAR[1]', 'varchar(5)'))
	from   @xml.nodes('/root') t(c) )
	
	select @NextNumber = NextNumber from [IES].T_GEN_NUMBERS with(rowlock,xlock) where EntityID = 539 and NumberName like @NumberName
	 
	if @NextNumber is null 
		begin
		 set @NextNumber = 1
		 insert into [IES].T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber) values (newid(), 539, @NumberName, @NextNumber)
		 if @@error <> 0 goto err1
		end
	else
		begin 
		 set @NextNumber = @NextNumber + 1
		 update [IES].T_GEN_NUMBERS set NextNumber = @NextNumber where EntityID = 539 and NumberName = @NumberName 
		 if @@error <> 0 goto err1 
		end 

	if @trancount = 0 commit tran 

	set @Next = cast(@NextNumber as varchar(64)) 

	return @NextNumber
	 
	err1: 
	 if @trancount = 0 rollback tran 
	 set @msg = 'Ошибка генерации номера.' 
	 raiserror (@msg, 9, 1)  
	return -1

END
GO

if exists (
	select 1 from IES.T_SP_REPLACE
	where OriginalSPName = 'sp_GenerateSchetOutNumber'
)
begin
	update IES.T_SP_REPLACE
	set CurrentSPName = 'sp_GenerateSchetOutNumber_68'
	where OriginalSPName = 'sp_GenerateSchetOutNumber';
end
else 
begin
	insert into IES.T_SP_REPLACE
	(SpReplaceID, OriginalSPName, CurrentSPName)
	values 
	(newid(), 'sp_GenerateSchetOutNumber', 'sp_GenerateSchetOutNumber_68'); 
end