iVend Database Database : GiftCertificateUpdate Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pGiftCertificateLogKey

In

Reference key of the Gift Certificate Log

VarWChar

50

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

VarChar

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ExecuteInventoryItemLogUpdate

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

PmtGiftCertificate

Table

Used to store the values for the Gift Certificate issued

1

PmtGiftCertificateLog

Table

Stores the log for every Gift certificate used

1

--=======================================================

CREATE PROCEDURE [dbo].[GiftCertificateUpdate]

(

@pGiftCertificateLogKey nvarchar(50),

@pDebug   CHAR(1) = 'N'

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255)

If @pDebug = 'Y'

SELECT 'Update The Gift Certificate'

DECLARE @lBalanceAmount decimal(20, 5)

Update B

SET @lBalanceAmount = B.BalanceAmount + A.Amount

  ,B.BalanceAmount = @lBalanceAmount

FROM PmtGiftCertificateLog A,  PmtGiftCertificate B

WHERE A.GiftCertificateKey = B.GiftCertificateKey

AND  A.GiftCertificateLogKey = @pGiftCertificateLogKey

AND  A.SourceType != 1

--THIS UPDATED THE IsProcessed FLAG OG LOG TABLE

Update PmtGiftCertificateLog SET IsProcessed = 'true' Where GiftCertificateLogKey = @pGiftCertificateLogKey

IF(@@ERROR <>0)

BEGIN

SET @ErrorDesc = 'Error while updating gift certificate value'

GOTO ERRORHANDLER

END

RETURN 0

ERRORHANDLER:

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

RETURN 1

END