Reference Generator

refgenerator.sql

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	BEGIN TRANSACTION;
	DECLARE @now_year CHAR(4)
	DECLARE @num_year CHAR(4)
	DECLARE @run_num INT

	-- get system year
	SELECT @now_year=LEFT(CONVERT(VARCHAR(10),GETDATE(),120),4)
	
	-- select a run num for the current year from T_RUN_NUM
	SELECT @num_year= RUN_YR_NUM, @run_num=CUR_NUM FROM T_RUN_NUM WITH(UPDLOCK) 
	WHERE  RUN_YR_NUM=@now_year And
		   ((@p_NumberType IS NULL AND NumberType is NULL) OR NumberType = @p_NumberType) And
		   ((@p_NumberCategory IS NULL AND NumberCategory is NULL) OR NumberCategory=@p_NumberCategory) And
		   ((@p_SenderGO IS NULL AND SenderGO is NULL) OR SenderGO=@p_SenderGO) And
		   ((@p_RecipientGO IS NULL AND RecipientGO is NULL) OR RecipientGO=@p_RecipientGO ) And
		   ((@p_RecipientType IS NULL AND RecipientType is NULL) OR RecipientType=@p_RecipientType ) And
		   ((@p_MailType IS NULL AND MailType is NULL) OR MailType=@p_MailType ) And	
		   ((@p_SubType IS NULL AND SubType is NULL) OR SubType=@p_SubType ) And	
		   ((@p_SenderType IS NULL AND SenderType is NULL) OR SenderType=@p_SenderType)

	-- if running number for system year was not found
	IF @num_year IS NULL 
		BEGIN
			-- set the year to the new year 
			SET @num_year=@now_year
			-- and check the T_START_NUM table to see if there is a preconfigured starting number for this new year
			SELECT @run_num=StartingNumber From T_START_NUM
			WHERE   ((@p_NumberType IS NULL AND NumberType is NULL) OR NumberType = @p_NumberType) And
			((@p_NumberCategory IS NULL AND NumberCategory is NULL) OR NumberCategory=@p_NumberCategory) And
			((@p_SenderGO IS NULL AND SenderGO is NULL) OR SenderGO=@p_SenderGO) And
			((@p_RecipientGO IS NULL AND RecipientGO is NULL) OR RecipientGO=@p_RecipientGO ) And
			((@p_RecipientType IS NULL AND RecipientType is NULL) OR RecipientType=@p_RecipientType ) And
			((@p_MailType IS NULL AND MailType is NULL) OR MailType=@p_MailType ) And	
			((@p_SubType IS NULL AND SubType is NULL) OR SubType=@p_SubType ) And	
			((@p_SenderType IS NULL AND SenderType is NULL) OR SenderType=@p_SenderType) AND
			( DATEPART(yyyy, UPD_DTTM) = @num_year    )
			
		    -- if there is no starting number set
			if @run_num IS NULL
			BEGIN
			--	start from 1
			 SET @run_num = 1
			END
			
			-- insert this new starting number into the T_RUN_NUM table for this new year
			INSERT INTO T_RUN_NUM ( [RUN_YR_NUM]
								   ,[NumberType]
								   ,[NumberCategory]
								   ,[SenderGO]
								   ,[RecipientGO]
								   ,[RecipientType]
								   ,[MailType]
								   ,[SubType]
								   ,[SenderType]
								   ,[CUR_NUM]) 
							VALUES(
									@num_year,
									@p_NumberType,
									@p_NumberCategory,
									@p_SenderGO,
									@p_RecipientGO,
									@p_RecipientType,
									@p_MailType,
									@p_SubType,
									@p_SenderType,
									Convert(int,@run_num))
		END
	ELSE
		BEGIN
			SET @run_num=@run_num+1
			UPDATE T_RUN_NUM SET CUR_NUM=@run_num,RUN_YR_NUM=@num_year
			WHERE  RUN_YR_NUM=@now_year And
		   ((@p_NumberType IS NULL AND NumberType is NULL) OR NumberType = @p_NumberType) And
		   ((@p_NumberCategory IS NULL AND NumberCategory is NULL) OR NumberCategory=@p_NumberCategory) And
		   ((@p_SenderGO IS NULL AND SenderGO is NULL) OR SenderGO=@p_SenderGO) And
		   ((@p_RecipientGO IS NULL AND RecipientGO is NULL) OR RecipientGO=@p_RecipientGO ) And
		   ((@p_RecipientType IS NULL AND RecipientType is NULL) OR RecipientType=@p_RecipientType ) And
		   ((@p_MailType IS NULL AND MailType is NULL) OR MailType=@p_MailType ) And	
		   ((@p_SubType IS NULL AND SubType is NULL) OR SubType=@p_SubType ) And	
		   ((@p_SenderType IS NULL AND SenderType is NULL) OR SenderType=@p_SenderType)
		END 

	IF (@p_yearAsSuffix = 0)
		SELECT @o_new_rnum = @now_year+'/'+CONVERT( VARCHAR(6),@run_num)
	ELSE
		SELECT @o_new_rnum = CONVERT( VARCHAR(6),@run_num)+'/'+@now_year

	COMMIT;
END