iVend Database Database : GetNextSiteNumber Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@SiteId

In

Reference key of the Site

Integer

4

@NextnoId

In

Id for which the accounting id has to be generated

VarChar

50

@Result

Out

Next Number generated by the procedure

VarWChar

50

@StartNumber

In

Integer

4

@POSSiteId

In

Reference key of the POS

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

DataNotification

Stored Procedure

2

GetNextSiteNumberForCurrentSite

Stored Procedure

1

LogAuditInformation

Stored Procedure

1

Trg_RepNotification

Trigger

1

CREATE PROCEDURE [dbo].[GetNextSiteNumber]

(

@SiteId   INT = 1,

  @NextnoId VARCHAR(50) ='',

  @Result   nvarchar(50) OUTPUT,

  @StartNumber INT = 0,

@POSSiteId INT = 0

)

AS

BEGIN

Select @Result = NEWID()

--SET NOCOUNT ON

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

--if @SiteId is null

-- RAISERROR('%s',16,-1,'Site information not found')

--   DECLARE @NextNumber  BIGINT,

--  @DBPOSSiteId INT

--Update SysNextNumber

--SET NextNumberID = NextNumberID

--WHERE NextNumberID = @NextnoId

--SELECT @NextNumber = NextNumber

--FROM SysNextNumber

--WHERE NextNumberID = @NextnoId

--IF @@ROWCOUNT = 0

--   BEGIN

-- SELECT @NextNumber = 1

-- IF @NextNumber < @StartNumber

--  SET @NextNumber = @StartNumber

-- INSERT SysNextNumber ( NextNumberID, NextNumber )

--       VALUES( @NextnoId, @NextNumber )

--END

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

--IF @NextNumber < @StartNumber

-- SET @NextNumber = @StartNumber

--  SELECT @Result = CONVERT(BIGINT, RIGHT('0000' + CONVERT(VARCHAR(4), @SiteId) , 4) + RIGHT('000' + CONVERT(VARCHAR(3), @DBPOSSiteId) , 3) + RIGHT('000000000000' + CONVERT(VARCHAR(12), @NextNumber), 12))

--  --SELECT @Result = CONVERT(BIGINT, RIGHT('00000' + CONVERT(VARCHAR(5), @SiteId) , 5) + RIGHT('00000000000000' + CONVERT(VARCHAR(13), @NextNumber), 13))

--IF @NextnoId = 'Table.ReplicationTransaction' AND EXISTS(SELECT 1 FROM RepReplicationTransaction With(nolock) Where ReplicationTransactionKey = @Result)

-- BEGIN

--  SELECT @Result = CONVERT(BIGINT, RIGHT('0000' + CONVERT(VARCHAR(4), @SiteId) , 4) + RIGHT('000' + CONVERT(VARCHAR(3), @DBPOSSiteId) , 3) + RIGHT('000000000000' + CONVERT(VARCHAR(12), @NextNumber + 500), 12))

--  --SELECT @Result = CONVERT(BIGINT, RIGHT('00000' + CONVERT(VARCHAR(5), @SiteId) , 5) + RIGHT('00000000000000' + CONVERT(VARCHAR(13), @NextNumber + 500), 13))

--  UPDATE SysNextNumber

--  SET  NextNumber = NextNumber  + 501

--  WHERE NextNumberID = @NextnoId

-- END

--ELSE IF @NextnoId = 'Table.InventoryItemLog' AND EXISTS(SELECT 1 FROM InvInventoryItemLog With(nolock) Where InventoryItemLogKey = @Result)

-- BEGIN

--  SELECT @Result = CONVERT(BIGINT, RIGHT('0000' + CONVERT(VARCHAR(4), @SiteId) , 4) + RIGHT('000' + CONVERT(VARCHAR(3), @DBPOSSiteId) , 3) + RIGHT('000000000000' + CONVERT(VARCHAR(12), @NextNumber + 500), 12))

--  UPDATE SysNextNumber

--  SET  NextNumber = NextNumber  + 501

--  WHERE NextNumberID = @NextnoId

-- END

--ELSE IF @NextnoId = 'Table.AuditLog' AND EXISTS(SELECT 1 FROM CfgAuditLog With(nolock) Where AuditKey = @Result)

-- BEGIN

--  SELECT @Result = CONVERT(BIGINT, RIGHT('0000' + CONVERT(VARCHAR(4), @SiteId) , 4) + RIGHT('000' + CONVERT(VARCHAR(3), @DBPOSSiteId) , 3) + RIGHT('000000000000' + CONVERT(VARCHAR(12), @NextNumber + 500), 12))

--  --SELECT @Result = CONVERT(BIGINT, RIGHT('00000' + CONVERT(VARCHAR(5), @SiteId) , 5) + RIGHT('00000000000000' + CONVERT(VARCHAR(13), @NextNumber + 500), 13))

--  UPDATE SysNextNumber

--  SET  NextNumber = NextNumber  + 501

--  WHERE NextNumberID = @NextnoId

-- END

--ELSE

-- BEGIN

--  UPDATE SysNextNumber

--  SET  NextNumber = NextNumber  + 1

--  WHERE NextNumberID = @NextnoId

--  END

--SET TRANSACTION ISOLATION LEVEL READ COMMITTED

--RETURN

END