iVend Database Database : GetNextDocumentNumberForPOS Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@DocumentNumberSeriesKey

In

VarWChar

50

@CommitResult

In

Boolean

1

@Result

Out

Next transaction number generated by the procedure

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

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

1

GetCompanyDateTime

User Defined Function

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

1

RtlPOSDocumentNumberSeries

Table

Stores the Document Number series details for a POS

1

/*

begin tran

Declare @abc varchar(50)

Exec GetNextDocumentNumber 10000000000002, @abc output

select @abc

rollback

*/

CREATE PROCEDURE [dbo].[GetNextDocumentNumberForPOS]

(

  @DocumentNumberSeriesKey     nvarchar(50),

@CommitResult BIT,

  @Result NVARCHAR(50) OUT

)

AS

BEGIN

SET NOCOUNT ON

  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @NextNumber     nvarchar(50),

  @ErrorDesc     NVARCHAR(255),

  @TransactionId   NVARCHAR(50),

  @Length     INT,

  @Prefix     NVARCHAR(50),

  @FinalNumberWithFormating NVARCHAR(50),

  @Suffix     NVARCHAR(50),

  @CurrentNumber   NVARCHAR(50),

          @LastNumber     NVARCHAR(50),

  @lSiteId     INT,

  @ReplicationKey   nvarchar(50),

  @StartNumber   NVARCHAR(50),

  @lPOSSiteId     INT

SELECT @lSiteId = SiteId, @lPOSSiteId = ISNULL(POSSiteId, 0) From CfgSiteInformation

Update RtlPOSDocumentNumberSeries

SET IsDeleted = IsDeleted

Where POSDocumentNumberSeriesKey = @DocumentNumberSeriesKey

Select @CurrentNumber = CurrentNumber, @Prefix = Prefix, @Suffix = Suffix, @LastNumber = EndNumber, @StartNumber = StartNumber

From RtlPOSDocumentNumberSeries

Where POSDocumentNumberSeriesKey = @DocumentNumberSeriesKey

-- Set StartNumber as CurrentNumber if first transaction being commited using this DocumentNumberSeries.

IF @CurrentNumber IS NULL OR @CurrentNumber = ''

  begin

  select @FinalNumberWithFormating = StartNumber from RtlPOSDocumentNumberSeries

  where POSDocumentNumberSeriesKey = @DocumentNumberSeriesKey

  end

else

  begin

  Set @NextNumber = CONVERT(nvarchar(50), @CurrentNumber) + 1 ---2

  Set @Length = LEN(@CurrentNumber)

  SET @FinalNumberWithFormating = LEFT(@StartNumber, LEN(@StartNumber) - LEN(@NextNumber)) + convert(nvarchar, @NextNumber) ---0000002

  end

IF Convert(bigint,@NextNumber) > Convert(bigint, @LastNumber)

BEGIN

SET @ErrorDesc = 'TransactionLogic.LastSeriesNumberExceeded'

GOTO ERRORHANDLER

END

IF @CommitResult = 'True'

BEGIN

Update RtlPOSDocumentNumberSeries

Set  CurrentNumber = @FinalNumberWithFormating,

 IsActive = CASE WHEN @FinalNumberWithFormating = @LastNumber THEN 0 ELSE IsActive END

Where POSDocumentNumberSeriesKey = @DocumentNumberSeriesKey

INSERT INTO RepReplicationTransaction

 (ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType, Flag, StoreId, FromSBO, Sender, Receiver, ProcessDate)

SELECT NEWID() , 251, @DocumentNumberSeriesKey, 0, 1, 0, NULL,NULL,NULL,NULL, dbo.GetCompanyDateTime()

END

Select @TransactionId = @Prefix+ @FinalNumberWithFormating + @Suffix

print @transactionId

SELECT @Result = @TransactionId

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

RETURN

ERRORHANDLER:

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

RETURN(1)

END