iVend Database Database : GetNextAutoGeneratedCode Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@SourceType

In

Integer

4

@UpdateNextNumber

In

Specifies whether the next number has to be updated or not

Boolean

1

@pSiteId

In

to identify the store

Integer

4

@Result

Out

New code generated depending on the Source Type

VarWChar

20

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgCustomSeries

Table

Stores the custom numbering details for a Store/Enterprise.

1

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvProduct

Table

Stores the product related details.

1

PurVendor

Table

Defines the vendor details of an enterprise from which the products are purchased.

1

RtlPOS

Table

Contains details about all POS defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

SysNextNumber

Table

System table

1

CREATE PROCEDURE [dbo].[GetNextAutoGeneratedCode]

(

@SourceType   INT,

      --

      --Valid values are:

      --Customer = 0

      --Vendor = 1

      --Product = 2

      --##PARAM_END

@UpdateNextNumber BIT,

@pSiteId   INT,

@Result   NVARCHAR(20) OUT

)

AS

BEGIN

SET NOCOUNT ON

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @NextNumber BIGINT,

  @ErrorDesc NVARCHAR(255),

  @Code   NVARCHAR(20),

  @PrefixCode NVARCHAR(20),

  @ObjectId NVARCHAR(20),

  @ResultCount INT,

  @ValidCode INT ,

  @Prefix   NVARCHAR(8),

  @Length   INT,

  @StartNumber INT,

  @Object   NVARCHAR(50),

  @POSSiteId INT,

  @POSId   NVARCHAR(20)

SELECT @Prefix = Prefix,

  @StartNumber = StartNumber,

  @Length = Length

FROM CfgCustomSeries

where SourceType = @SourceType

AND  SiteId = @pSiteId

IF @UpdateNextNumber = 'FALSE' AND @Length IS NULL

  BEGIN

  SET @Result = ''

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED

  RETURN

  END

IF @SourceType = 0

SELECT @ObjectId = 'Customer' +'.' + @Prefix

ELSE IF @SourceType = 1

SELECT @ObjectId = 'Vendor' +'.' + @Prefix

ELSE IF @SourceType = 2

SELECT @ObjectId = 'Product' +'.' + @Prefix

SELECT @NextNumber = NextNumber

FROM SysNextNumber

WHERE NextNumberID = @ObjectId

IF @@RowCount = 0

BEGIN

INSERT INTO SysNextNumber(NextNumberID, NextNumber)

VALUES (@ObjectId, @StartNumber )

IF(@@ERROR <>0)

    BEGIN

  SET @ErrorDesc = 'Error in inserting into table Next Number.'

        GOTO ERRORHANDLER

END

SELECT @NextNumber = @StartNumber

END

IF (@StartNumber > @NextNumber)

BEGIN

SELECT @NextNumber = @StartNumber

END

IF (@UpdateNextNumber = 'TRUE')

BEGIN

UPDATE SysNextNumber

SET  NextNumber = @NextNumber + 1

WHERE NextNumberID = @ObjectId

END

SELECT @Code = CONVERT(VARCHAR(20), @NextNumber)

IF (@Length != 0)

BEGIN

SET @PrefixCode = RTRIM(LTRIM(@Prefix))+ @Code

SET @Code = @Prefix + (IsNull(REPLICATE('0', @Length - LEN(LTRIM(RTRIM((@PrefixCode))))), '')) + @Code

END

SET @ValidCode = 0

WHILE @ValidCode <> 1

BEGIN

IF(@SourceType = 0)

  BEGIN

  SELECT CustomerKey FROM CusCustomer WHERE Id = @Code

  SELECT @ResultCount = @@RowCount

  END

IF(@SourceType = 1)

  BEGIN

  SELECT VendorKey FROM PurVendor WHERE Id = @Code

  SELECT @ResultCount = @@RowCount

  END

IF(@SourceType = 2)

  BEGIN

  SELECT ProductKey FROM InvProduct WHERE Id = @Code

  SELECT @ResultCount = @@RowCount

  END

IF @ResultCount > 0

  BEGIN

  UPDATE SysNextNumber

  SET  NextNumber = NextNumber + 1

  WHERE NextNumberID = @ObjectId

  SET @NextNumber = @NextNumber + 1

  SELECT @Code = Convert(VARCHAR(20), @NextNumber)

  IF (@Length != 0)

    BEGIN

    SET @PrefixCode = RTRIM(LTRIM(@Prefix))+ @Code

    SET @Code = @Prefix + (IsNull(REPLICATE('0', @Length - LEN(LTRIM(RTRIM((@PrefixCode))))), '')) + @Code

  END

  CONTINUE

  END

ELSE

  BEGIN

  SET @ValidCode = 1

  BREAK

  END

END

-- This code is for generating customer code for offline POS.

IF(@SourceType = 0)

BEGIN

SELECT @POSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

IF @POSSiteId <> 0

BEGIN

  SELECT @Prefix = Prefix, @StartNumber = StartNumber, @Length = Length

  FROM CfgCustomSeries

  where SourceType = @SourceType

  AND  POSKey = (SELECT POSKey FROM RtlPOS P, RtlStore S WHERE P.POSSiteId = @POSSiteId

  AND  S.SiteId = @pSiteId AND P.StoreKey = S.StoreKey)

  IF @SourceType = 0

  SELECT @ObjectId = 'Customer' +'.' + @Prefix

  SELECT @NextNumber = NextNumber

  FROM SysNextNumber

  WHERE NextNumberID = @ObjectId

  IF @@RowCount = 0

  BEGIN

  INSERT INTO SysNextNumber(NextNumberID, NextNumber)

  VALUES (@ObjectId, @StartNumber )

  IF(@@ERROR <>0)

  BEGIN

    SET @ErrorDesc = 'Error in inserting into table Next Number.'

    GOTO ERRORHANDLER

  END

  SELECT @NextNumber = @StartNumber

  END

  IF (@StartNumber > @NextNumber)

  BEGIN

  SELECT @NextNumber = @StartNumber

  END

  IF (@UpdateNextNumber = 'TRUE')

  BEGIN

  UPDATE SysNextNumber

  SET  NextNumber = @NextNumber + 1

  WHERE NextNumberID = @ObjectId

  END

  SELECT @Code = CONVERT(VARCHAR(20), @NextNumber)

  IF (@Length != 0)

  BEGIN

  SET @PrefixCode = RTRIM(LTRIM(@Prefix))+ @Code

  SET @Code = @Prefix + (IsNull(REPLICATE('0', @Length - LEN(LTRIM(RTRIM((@PrefixCode))))), '')) + @Code

  END

  SET @ValidCode = 0

  WHILE @ValidCode <> 1

  BEGIN

  IF(@SourceType = 0)

    BEGIN

    SELECT CustomerKey FROM CusCustomer WHERE Id = @Code

    SELECT @ResultCount = @@RowCount

    END

  IF @ResultCount > 0

    BEGIN

    UPDATE SysNextNumber

    SET  NextNumber = NextNumber + 1

    WHERE NextNumberID = @ObjectId

    SET @NextNumber = @NextNumber + 1

    SELECT @Code = Convert(VARCHAR(20), @NextNumber)

    IF (@Length != 0)

      BEGIN

      SET @PrefixCode = RTRIM(LTRIM(@Prefix))+ @Code

      SET @Code = @Prefix + (IsNull(REPLICATE('0', @Length - LEN(LTRIM(RTRIM((@PrefixCode))))), '')) + @Code

    END

    CONTINUE

    END

  ELSE

    BEGIN

    SET @ValidCode = 1

    BREAK

    END

  END

END

END

SELECT @Result = @Code

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN(1)

END