Number Ranger CREATE TABLE [dbo].[M_NUMBER_RANGE]( [GTRK_NAME1] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [GTRK_SUBNAME] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_PREFIX_LETTER] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_STARTING_NUMNER] [numeric](18, 0) NULL, [GTRK_ENDING_NUMBER] [numeric](18, 0) NULL, [GTRK_CURRENT_NUMBER] [numeric](18, 0) NULL, [GTRK_INCREMENT] [numeric](18, 0) NULL, [GTRK_PERCENTAGE] [numeric](18, 0) NULL, [GTRK_NUMBER_LENGTH] [numeric](18, 0) NULL, [GTRK_GROUP_TABLE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_FLAG] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_REMARKS] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_ENTEREDDT] [datetime] NULL, [GTRK_ENTEREDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [GTRK_MODIFIEDDT] [datetime] NULL, [GTRK_MODIFIEDBY] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, PRIMARY KEY CLUSTERED ( [GTRK_NAME1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GTRACK-V2 GTRACK-V2 10000000 99999999 543 1 0 8 GTRK_TRUCK_DETAILS NULL NULL 2014-01-29 11:15:46.857 ADMIN NULL NULL Ranger Procedure : /************************************************************* CREATED BY : NAGARAJAN.R CREATED DATE : 29/01/2014 PURPOSE : Get the Maximum Doc Number EXECUTION : USP_GET_DOCNO 'GTRACK-V2','','' *************************************************************/ CREATE PROCEDURE USP_GET_DOCNO ( @ID VARCHAR(25) = NULL, @DOCNO VARCHAR(250) OUTPUT, @INDEXNO VARCHAR(9) OUTPUT ) AS BEGIN SELECT @DOCNO = GTRK_PREFIX_LETTER + '' + convert(VARCHAR, GTRK_STARTING_NUMNER + (GTRK_CURRENT_NUMBER + GTRK_INCREMENT)) , @INDEXNO = (GTRK_CURRENT_NUMBER + GTRK_INCREMENT) FROM M_NUMBER_RANGE WHERE GTRK_NAME1 = @ID SELECT @DOCNO AS DOCNO , @INDEXNO AS INDEXNO END Automation Generated Number --EXEC GTRK_CREATETRANSACTIONINFO'TNK4385784','839','HPL','RNJ','TEST','Nagarajan','TN4356','9842946063','Nagu','IN' CREATE PROC [dbo].[GTRK_CREATETRANSACTIONINFO] ( @GTRK_TRUCK_NO VARCHAR(15), @GTRK_PLANT_CODE VARCHAR(15), @GTRK_TRUCK_TYPE_CODE VARCHAR(100), @GTRK_TRANSPORTER_CODE VARCHAR(100), @GRRK_REMARKS VARCHAR(250), @GTRK_DRIVER_NAME VARCHAR(50), @GTRK_LICENCE_NO VARCHAR(15), @GTRK_MOBILE_NO VARCHAR(10), @GTRK_CREATE_BY VARCHAR(50), @FLAG VARCHAR(10), @referenceNumber VARCHAR(15)=NULL OUTPUT ) AS BEGIN --DECLARE @AUTOMATION_DOCNO VARCHAR(10) --SELECT @AUTOMATION_DOCNO=isnull(MAX(ISNULL(GTRK_DOC_NO,00000000001)+1),0000000001) FROM GTRK_TRUCK_DETAILS --Find Maximum Order Number --DECLARE @OrderNumber Varchar(50) --SELECT @OrderNumber =CAST(ISNULL(Max(GTRK_DOC_NO),0) + 1 AS VARCHAR(40)) FROM GTRK_TRUCK_DETAILS --SELECT @OrderNumber=STUFF('00000000', 8-LEN(@orderNumber)+1, LEN(@orderNumber),@orderNumber) -- SELECT @OrderNumber DECLARE @OrderNumber VARCHAR(10) DECLARE @CURRENTNO VARCHAR(9) EXEC USP_GET_DOCNO 'GTRACK-V2', @OrderNumber OUTPUT ,@CURRENTNO OUTPUT BEGIN TRY BEGIN TRANSACTION INSERT INTO GTRK_TRUCK_DETAILS(GTRK_DOC_NO,GTRK_TRUCK_NO,GTRK_PLANT_CODE,GTRK_TRUCK_TYPE_CODE,GTRK_TRANSPORTER_CODE,GTRK_MATL_MVMNT_CODE,GTRK_MATL_CODE,GTRK_REMARKS,GTRK_TRUCK_IN,GTRK_DRIVER_NAME, GTRK_LICENCE_NO,GTRK_MOBILE_NO,GTRK_CREATE_BY,GTRK_CREATED_DATE,GTRK_MODIFIED_BY,GTRK_MODIFIED_DATE,GTRK_FLAG_TYPE) VALUES(@OrderNumber,@GTRK_TRUCK_NO,@GTRK_PLANT_CODE,@GTRK_TRUCK_TYPE_CODE,@GTRK_TRANSPORTER_CODE,'10','10000001',@GRRK_REMARKS,getdate(),@GTRK_DRIVER_NAME, @GTRK_LICENCE_NO,@GTRK_MOBILE_NO,@GTRK_CREATE_BY,getdate(),NULL,NULL,@FLAG) set @referenceNumber=@OrderNumber ----------------UPDATE Current Number range table--------------- UPDATE M_NUMBER_RANGE SET GTRK_CURRENT_NUMBER = @CURRENTNO WHERE GTRK_NAME1 ='GTRACK-V2' ---------------------------------------------------------------- COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage END CATCH END --select *From GTRK_TRUCK_DETAILS --0010953525