Tuesday, 27 May 2014
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment