iVend Database Database : LoyUpdatePointSummary Stored Procedure

Creation Date

6/14/2019 6:07 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@CustomerKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgLoyaltyConfig

Table

Stores the configuration information for the loyalty

1

LoyLoyaltyCardInformation

Table

Store the Details of Loyalty Customer in the system.

1

LoyMembershipGroup

Table

Stores the Header detail of Loyalty Membership Group in the system.

1

LoyPoints

Table

Stores the Header detail of Loyalty Points awarded in the system.

1

LoyPointsSummary

Table

Stores the Summary of Loyalty Points awarded in the system.

1

LoySlab

Table

Stores Loyalty Slab configured in the system.

1

CREATE PROCEDURE LoyUpdatePointSummary(@CustomerKey nvarchar(50))

AS

BEGIN

          DECLARE @AwardedPoints INT

  DECLARE @AwardedCurrency DECIMAL(38,2)

  DECLARE @RedeemedPoints INT

  DECLARE @RedeemedCurrency DECIMAL(38,2)

  DECLARE @OpeningPoints INT

  DECLARE @OpeningCurrency DECIMAL(38,2)

  DECLARE @ExpiredPoints INT

  DECLARE @ExpiredCurrency DECIMAL(38,2)

  DECLARE @PlanDefinitionBasis INT

          DECLARE @IsInttraStoreReplicationEnable INT

          DECLARE @pointSummaryKey nvarchar(50)

          DECLARE @MemebershipGroupKey nvarchar(50)

          DECLARE @AvalablePoints INT

  DECLARE @AvalableCurrency DECIMAL(38,2)

  DECLARE @MembershipSlabDeescalationMethod INT

          DECLARE @IsNegativeBalanceInAdjustmentJournal INT

            ------ DECLARATION STARTS FOR POINTS EXPIRY ISSUE--------

          DECLARE @Count INT

  DECLARE @Counter INT

    DECLARE @RedeemedPointsTillCurrentDate INT

    DECLARE @RedeemedCurrencyTillCurrentDate DECIMAL(38,2)

    DECLARE @PointExpiryDate DATETIME

    DECLARE @ExpiredPointsTotal INT

    DECLARE @ExpiredCurrencyTotal DECIMAL(38,2)

    DECLARE @CarryForwardReedempoints int

    DECLARE @CarryForwardReedemCurrency DECIMAL(38,2)

    DECLARE @MinimumSlabValueCalculatedOn int

    DECLARE @SlabkeyByPoint nvarchar(50)

    DECLARE @CurrentSlabKey nvarchar(50)

    DECLARE @TransactionKey nvarchar(50)

    DECLARE @CustomerSlabMinValue NUMERIC

    DECLARE @CurrentSlabMinValue NUMERIC

    DECLARE @LoyaltyConfigKey nvarchar(50)

    CREATE TABLE #tmpExpiryDate

  (

   RowNumber INT IDENTITY(1,1),

   PointsKey nvarchar(50),

   ExpiryDate DATETIME,

   AwardedPoints INT,

   AwardedCurrency DECIMAL(38,2)

  )

  DECLARE @tempTransactionKeys TABLE(TransactionKey nvarchar(50))

  SET @ExpiredPoints=0

  SET @ExpiredCurrencyTotal=0

  SET @ExpiredPointsTotal=0

  SET @CarryForwardReedempoints=0

  SET @CarryForwardReedemCurrency='0'

      ---------- DECLARATION ENDS FOR POINTS EXPIRY ISSUE--------

  SELECT @MemebershipGroupKey=ISNULL(MembershipGroupKey,'0') , @CurrentSlabKey=slabkey,@LoyaltyConfigKey=LoyaltyConfigKey FROM LOYLOYALTYCARDINFORMATION WHERE LOYALTYCARDINFORMATIONKEY =@CustomerKey

  select top 1 * into #tempLoyPoints from LoyPoints where 0=1

  ---------------------------------- STATEMENTS STARTS POINTS EXPIRY ISSUE---------------------------------------------------

  if(@MemebershipGroupKey = '0')

          BEGIN

  INSERT INTO #tmpExpiryDate

  SELECT PointsKey,ExpiryDate, ISNULL(AwardedPoints,0) As AwardedPoints, ISNULL(AwardedCurrency,'0') AS AwardedCurrency

  FROM LoyPoints

  WHERE CustomerKey = @CustomerKey and IsExpired = 1

  ORDER BY Created ASC

  insert into #tempLoyPoints

  select * from LoyPoints

  Where CustomerKey = @CustomerKey

  --print '------------------------------------------------'

end

IF(@MemebershipGroupKey != '0')

  BEGIN

    INSERT INTO #tmpExpiryDate

    SELECT PointsKey,ExpiryDate, ISNULL(AwardedPoints,0) As AwardedPoints, ISNULL(AwardedCurrency,'0') AS AwardedCurrency

    FROM LoyPoints

    WHERE MembershipGroupKey = @MemebershipGroupKey and IsExpired = 1

    ORDER BY Created ASC

    Select @CurrentSlabKey=slabkey from loymembershipgroup where membershipgroupkey=@MemebershipGroupKey

    Insert into #tempLoyPoints

    Select * from LoyPoints

    Where MembershipGroupKey = @MemebershipGroupKey

END

    SELECT @Count=COUNT(*) FROM #tmpExpiryDate

  SET @Counter = 1

  WHILE @Counter <= @Count

  BEGIN

  SELECT @PointExpiryDate=ExpiryDate,

    @ExpiredPoints = AwardedPoints,

    @ExpiredCurrency = AwardedCurrency

  FROM #tmpExpiryDate WHERE RowNumber = @Counter

  IF(@MemebershipGroupKey = '0')

                BEGIN

    SELECT @RedeemedPointsTillCurrentDate = SUM(ISNULL(p.RedeemedPoints ,0)),

      @RedeemedCurrencyTillCurrentDate = SUM(ISNULL(p.RedeemedCurrency  ,'0'))

    FROM #tempLoyPoints p

    WHERE p.CustomerKey = @CustomerKey AND p.Created Is Not Null AND

      CONVERT(VARCHAR(20),  p.Created, 112) <= CONVERT(VARCHAR(20), @PointExpiryDate, 112) AND

       ((ISNULL(p.RedeemedPoints,0) !=0 OR ISNULL(p.RedeemedCurrency,'0') !='0'))

        AND p.TransactionKey NOT IN(SELECT TransactionKey FROM @tempTransactionKeys)

    INSERT INTO @tempTransactionKeys

    SELECT P.TransactionKey

    FROM #tempLoyPoints p

    WHERE p.CustomerKey = @CustomerKey and p.Created Is Not Null

      AND CONVERT(VARCHAR(20),  p.Created, 112) <= CONVERT(VARCHAR(20), @PointExpiryDate, 112)

    AND ((ISNULL(p.RedeemedPoints,0) !=0 OR ISNULL(p.RedeemedCurrency,0) !=0))

    AND p.TransactionKey NOT IN(SELECT TransactionKey FROM @tempTransactionKeys)

  END

  IF(@MemebershipGroupKey != '0')

                BEGIN

    SELECT @RedeemedPointsTillCurrentDate = SUM(ISNULL(p.RedeemedPoints ,0)),

      @RedeemedCurrencyTillCurrentDate = SUM(ISNULL(p.RedeemedCurrency  ,0))

    FROM #tempLoyPoints p

    WHERE p.MembershipGroupKey = @MemebershipGroupKey AND p.Created Is Not Null AND

      CONVERT(VARCHAR(20),  p.Created, 112) <= CONVERT(VARCHAR(20), @PointExpiryDate, 112) AND

       ((ISNULL(p.RedeemedPoints,0) !=0 OR ISNULL(p.RedeemedCurrency,0) !=0))

        AND p.TransactionKey NOT IN(SELECT TransactionKey FROM @tempTransactionKeys)

    INSERT INTO @tempTransactionKeys

    SELECT P.TransactionKey

    FROM #tempLoyPoints p

    WHERE p.MembershipGroupKey = @MemebershipGroupKey and p.Created Is Not Null

      AND CONVERT(VARCHAR(20),  p.Created, 112) <= CONVERT(VARCHAR(20), @PointExpiryDate, 112)

    AND ((ISNULL(p.RedeemedPoints,0) !=0 OR ISNULL(p.RedeemedCurrency, 0) !=0))

    AND p.TransactionKey NOT IN(select TransactionKey from @tempTransactionKeys)

  END

  SET @RedeemedPointsTillCurrentDate =ISNULL(@RedeemedPointsTillCurrentDate,0)

      SET @RedeemedCurrencyTillCurrentDate =ISNULL(@RedeemedCurrencyTillCurrentDate,0)

  SET @RedeemedPointsTillCurrentDate=@RedeemedPointsTillCurrentDate + @CarryForwardReedempoints

  SET @RedeemedCurrencyTillCurrentDate=@RedeemedCurrencyTillCurrentDate + @CarryForwardReedemCurrency

  IF(@ExpiredPoints != 0 and @RedeemedPointsTillCurrentDate !=0)

  BEGIN

    IF(@RedeemedPointsTillCurrentDate >= @ExpiredPoints)

    BEGIN

    SET @CarryForwardReedempoints =@RedeemedPointsTillCurrentDate - @ExpiredPoints

        SET @ExpiredPoints = 0

    END

    ELSE

    BEGIN

      SET @ExpiredPoints =(@ExpiredPoints-@RedeemedPointsTillCurrentDate)

    SET @CarryForwardReedempoints = 0

    END

  END

  IF(@ExpiredCurrency != 0 and @RedeemedCurrencyTillCurrentDate !=0)

  BEGIN

    IF(@RedeemedCurrencyTillCurrentDate >= @ExpiredCurrency)

    BEGIN

    SET @CarryForwardReedemCurrency = @RedeemedCurrencyTillCurrentDate - @ExpiredCurrency

    SET @ExpiredCurrency = 0

    END

    ELSE

    BEGIN

    SET @ExpiredCurrency = @ExpiredCurrency - @RedeemedCurrencyTillCurrentDate

    SET @CarryForwardReedemCurrency = 0

    End

    End

  SET @ExpiredPointsTotal=@ExpiredPointsTotal+ @ExpiredPoints

  SET @ExpiredCurrencyTotal=@ExpiredCurrencyTotal+@ExpiredCurrency

  SET @Counter = @Counter + 1

  END

  ----------------------------------STATEMENTS ENDS POINTS EXPIRY ISSUE---------------------------------------------------

          SET @IsInttraStoreReplicationEnable =1

          SET @PlanDefinitionBasis =1

            SELECT @IsNegativeBalanceInAdjustmentJournal=ISNULL(IsNegativeBalanceInAdjustmentJournal,0)

    , @IsInttraStoreReplicationEnable=ExchangeLoyaltyDataBetweenStore

    , @MinimumSlabValueCalculatedOn=MinimumSlabValueCalculatedOn

    , @MembershipSlabDeescalationMethod=MembershipSlabDeescalationMethod

  FROM CfgLoyaltyConfig where LoyaltyConfigKey=@LoyaltyConfigKey and IsDeleted=0

          IF(@MemebershipGroupKey = '0'  )

          BEGIN

    SELECT @AwardedPoints=SUM(ISNULL(AwardedPoints,0)) ,@AwardedCurrency=SUM(ISNULL(AwardedCurrency,0))

    ,@RedeemedPoints=SUM(isnull(RedeemedPoints,0)) ,@RedeemedCurrency=SUM(isnull(RedeemedCurrency,0))

    FROM #tempLoyPoints WHERE CustomerKey =@CustomerKey And (MembershipGroupKey=0 Or MembershipGroupKey Is null) --and IsExpired =0

    SET @ExpiredPointsTotal =ISNULL(@ExpiredPointsTotal,0)

          SET @ExpiredCurrencyTotal =ISNULL(@ExpiredCurrencyTotal,0)

    SET @AwardedPoints =ISNULL(@AwardedPoints,0)

    SET @OpeningPoints =ISNULL(@OpeningPoints,0)

    SET @RedeemedPoints =ISNULL(@RedeemedPoints,0)

    SET @AwardedCurrency =ISNULL(@AwardedCurrency,0)

    SET @OpeningCurrency =ISNULL(@OpeningCurrency,0)

    SET @RedeemedCurrency =ISNULL(@RedeemedCurrency,0)

          END

  IF(@MemebershipGroupKey != '0'  )

  BEGIN

    SELECT @AwardedPoints=SUM(ISNULL(AwardedPoints,0))

      ,@AwardedCurrency=SUM(ISNULL(AwardedCurrency,'0')) ,

      @RedeemedPoints=SUM(ISNULL(RedeemedPoints,0))

      ,@RedeemedCurrency=SUM(ISNULL(RedeemedCurrency,'0'))

    FROM #tempLoyPoints

    WHERE MembershipGroupKey =@MemebershipGroupKey

        SET @ExpiredPointsTotal =ISNULL(@ExpiredPointsTotal,0)

          SET @ExpiredCurrencyTotal =ISNULL(@ExpiredCurrencyTotal,0)

    SET @AwardedPoints =ISNULL(@AwardedPoints,0)

    SET @OpeningPoints =ISNULL(@OpeningPoints,0)

    SET @RedeemedPoints =ISNULL(@RedeemedPoints,0)

    SET @AwardedCurrency =ISNULL(@AwardedCurrency,0)

    SET @OpeningCurrency =ISNULL(@OpeningCurrency,0)

    SET @RedeemedCurrency =ISNULL(@RedeemedCurrency,0)

  END

          IF(@IsInttraStoreReplicationEnable =1)

          BEGIN

    IF(@MemebershipGroupKey = '0'  )

      BEGIN

                        SET @AvalablePoints= ((@AwardedPoints +@OpeningPoints)-(@RedeemedPoints+@ExpiredPointsTotal))

                        SET @AvalableCurrency =((@AwardedCurrency +@OpeningCurrency )-(@RedeemedCurrency+@ExpiredCurrencyTotal))

      IF(@IsNegativeBalanceInAdjustmentJournal=0)

          BEGIN

        IF(@AvalablePoints < 0)

        BEGIN

      SET @AvalablePoints=0

        END

        IF(@AvalableCurrency < 0)

        BEGIN

        SET @AvalableCurrency=0

        END

      END

      UPDATE LoyPointsSummary SET

      AvailablePoints=@AvalablePoints,

      AvailableCurrency =@AvalableCurrency,

      AwardedPoints =@AwardedPoints,

      AwardedCurrency=@AwardedCurrency ,

      RedeemedPoints =@RedeemedPoints ,

      RedeemedCurrency =@RedeemedCurrency ,

      ExpiredPoints =@ExpiredPointsTotal,

      ExpiredCurrency=@ExpiredCurrencyTotal,

      IsIntegratedWithPortal=0

      WHERE CustomerKey =@CustomerKey

    END

    IF(@MemebershipGroupKey!= '0')

      BEGIN

    SET @AvalablePoints=((@AwardedPoints +@OpeningPoints)-(@RedeemedPoints+@ExpiredPointsTotal))

    SET @AvalableCurrency =((@AwardedCurrency +@OpeningCurrency )-(@RedeemedCurrency+@ExpiredCurrencyTotal))

    IF(@IsNegativeBalanceInAdjustmentJournal=0)

      BEGIN

        IF(@AvalablePoints < 0)

        BEGIN

        SET @AvalablePoints=0

        END

        IF(@AvalableCurrency < 0)

        BEGIN

        SET @AvalableCurrency=0

        END

      END

    UPDATE loymembershipGroup SET

     AvailablePoints =@AvalablePoints,

     AvailableCurrency=@AvalableCurrency,

     AwardedPoints=@AwardedPoints,

     AwardedCurrency=@AwardedCurrency ,

     RedeemedPoints=@RedeemedPoints ,

     RedeemedCurrency =@RedeemedCurrency ,

     ExpiredPoints =@ExpiredPointsTotal,

     ExpiredCurrency =@ExpiredCurrencyTotal

    WHERE MembershipGroupKey =@MemebershipGroupKey

    END

    END

  IF(@IsInttraStoreReplicationEnable =0)

          Begin

    IF(@MemebershipGroupKey ='0'  )

      BEGIN

            SET @AvalablePoints=((@AwardedPoints +@OpeningPoints)-(@RedeemedPoints+@ExpiredPointsTotal))

          SET @AvalableCurrency =((@AwardedCurrency +@OpeningCurrency )-(@RedeemedCurrency+@ExpiredCurrencyTotal))

        IF(@IsNegativeBalanceInAdjustmentJournal=0)

        BEGIN

          IF(@AvalablePoints < 0)

          BEGIN

          SET @AvalablePoints=0

          END

          IF(@AvalableCurrency < 0)

          BEGIN

          SET @AvalableCurrency=0

          END

        END

        UPDATE LoyPointsSummary SET

        AvailablePoints=@AvalablePoints,

        AvailableCurrency =@AvalableCurrency,

        AwardedPoints =@AwardedPoints,

        AwardedCurrency=@AwardedCurrency ,

        RedeemedPoints =@RedeemedPoints ,

        RedeemedCurrency =@RedeemedCurrency ,

        ExpiredPoints =@ExpiredPointsTotal,

        ExpiredCurrency=@ExpiredCurrencyTotal,

        IsIntegratedWithPortal=0

        WHERE CustomerKey =@CustomerKey

        SELECT TOP 1 @SlabkeyByPoint=slabkey,@CurrentSlabMinValue=MinimumValue FROM loyslab WHERE IsDeleted=0 AND LoyaltyConfigKey=@LoyaltyConfigKey AND minimumvalue <= CASE WHEN @MinimumSlabValueCalculatedOn = 1 THEN @AvalablePoints

        WHEN @MinimumSlabValueCalculatedOn = 2 THEN @AwardedPoints

        WHEN @MinimumSlabValueCalculatedOn = 3 THEN @AvalableCurrency

        WHEN @MinimumSlabValueCalculatedOn = 4 THEN @AwardedCurrency

        END

        ORDER BY minimumvalue DESC

        SELECT TOP 1 @CustomerSlabMinValue=MinimumValue FROM loyslab WHERE SlabKey=@CurrentSlabKey AND LoyaltyConfigKey=@LoyaltyConfigKey

      --IF(@CurrentSlabKey <> @SlabkeyByPoint AND @CustomerSlabMinValue < @CurrentSlabMinValue AND @MembershipSlabDeescalationMethod !=1)

      --BEGIN

      --   UPDATE LOYLOYALTYCARDINFORMATION  SET   SLABKEY = @SlabkeyByPoint WHERE LOYALTYCARDINFORMATIONKEY=  @CustomerKey

      -- EXEC DataNotIFication @pSourceType = 338, @pSourceKey = @CustomerKey, @pBatchKey = '-1', @pOperationType = 0, @pUserKey = '-1', @pDebug= 'false'

      --END

      IF(@CurrentSlabKey <> @SlabkeyByPoint AND @MembershipSlabDeescalationMethod =2)

      BEGIN

          UPDATE LOYLOYALTYCARDINFORMATION SET   SLABKEY = @SlabkeyByPoint WHERE LOYALTYCARDINFORMATIONKEY= @CustomerKey

        EXEC DataNotIFication @pSourceType = 338, @pSourceKey = @CustomerKey, @pBatchKey = '-1', @pOperationType = 0, @pUserKey = '-1', @pDebug= 'false'

      END

            -- Generate the replication event for  points summary

        SELECT @pointSummaryKey=PointsSummarykey FROM LoyPointsSummary WHERE CustomerKey =@CustomerKey

        EXEC DataNotIFication @pSourceType = 337, @pSourceKey = @pointSummaryKey, @pBatchKey = '-1', @pOperationType = 0, @pUserKey = '-1', @pDebug= 'false'

    END

    IF(@MemebershipGroupKey != '0')

      BEGIN

        SET @AvalablePoints=((@AwardedPoints + @OpeningPoints)-(@RedeemedPoints + @ExpiredPointsTotal))

        SET @AvalableCurrency =((@AwardedCurrency + @OpeningCurrency )-(@RedeemedCurrency + @ExpiredCurrencyTotal))

        IF(@IsNegativeBalanceInAdjustmentJournal = 0)

        BEGIN

        IF(@AvalablePoints < 0)

          BEGIN

          SET @AvalablePoints=0

          END

          IF(@AvalableCurrency < 0)

          BEGIN

          SET @AvalableCurrency=0

          END

        END

        SELECT TOP 1 @SlabkeyByPoint = slabkey

          ,@CurrentSlabMinValue=MinimumValue

        FROM loyslab

        WHERE IsDeleted=0

        AND LoyaltyConfigKey=@LoyaltyConfigKey

        AND minimumvalue <= CASE WHEN @MinimumSlabValueCalculatedOn = 1 THEN @AvalablePoints

          WHEN @MinimumSlabValueCalculatedOn = 2 THEN @AwardedPoints

          WHEN @MinimumSlabValueCalculatedOn = 3 THEN @AvalableCurrency

          WHEN @MinimumSlabValueCalculatedOn = 4 THEN @AwardedCurrency

        END

          ORDER BY minimumvalue DESC

          SELECT TOP 1 @CustomerSlabMinValue=MinimumValue FROM loyslab WHERE SlabKey=@CurrentSlabKey AND LoyaltyConfigKey=@LoyaltyConfigKey

            IF(@CurrentSlabKey <> @SlabkeyByPoint AND @CustomerSlabMinValue <@CurrentSlabMinValue AND @MembershipSlabDeescalationMethod !=1)

        BEGIN

          SET @CurrentSlabKey = @SlabkeyByPoint

        END

        IF(@CurrentSlabKey <> @SlabkeyByPoint AND @MembershipSlabDeescalationMethod !=1)

          BEGIN

            SET @CurrentSlabKey = @SlabkeyByPoint

          END

          UPdate loymembershipGroup SET

        AvailablePoints =@AvalablePoints,

        AvailableCurrency=@AvalableCurrency,

        AwardedPoints=@AwardedPoints,

        AwardedCurrency=@AwardedCurrency ,

        RedeemedPoints=@RedeemedPoints ,

        RedeemedCurrency =@RedeemedCurrency ,

        ExpiredPoints =@ExpiredPointsTotal,

        ExpiredCurrency =@ExpiredCurrencyTotal,

        SlabKey = @CurrentSlabKey

        WHERE MembershipGroupKey =@MemebershipGroupKey

        -- Generate the replication event for  membership group

          EXEC DataNotIFication @pSourceType = 356, @pSourceKey = @MemebershipGroupKey, @pBatchKey = '-1', @pOperationType = 0, @pUserKey = '-1', @pDebug= 'false'

      END

          SELECT TOP 1 @SlabkeyByPoint=slabkey,@CurrentSlabMinValue=MinimumValue FROM loyslab WHERE IsDeleted=0 AND LoyaltyConfigKey=@LoyaltyConfigKey AND minimumvalue <= CASE WHEN @MinimumSlabValueCalculatedOn = 1 THEN @AvalablePoints

        WHEN @MinimumSlabValueCalculatedOn = 2 THEN @AwardedPoints

        WHEN @MinimumSlabValueCalculatedOn = 3 THEN @AvalableCurrency

        WHEN @MinimumSlabValueCalculatedOn = 4 THEN @AwardedCurrency

        END

        ORDER BY minimumvalue DESC

        SELECT TOP 1 @CustomerSlabMinValue=MinimumValue FROM loyslab WHERE SlabKey=@CurrentSlabKey AND LoyaltyConfigKey=@LoyaltyConfigKey

      --IF(@CurrentSlabKey <> @SlabkeyByPoint AND @CustomerSlabMinValue < @CurrentSlabMinValue AND @MembershipSlabDeescalationMethod !=1)

      --BEGIN

      --   UPDATE LOYLOYALTYCARDINFORMATION  SET   SLABKEY = @SlabkeyByPoint WHERE LOYALTYCARDINFORMATIONKEY=  @CustomerKey

      --END

      IF(@CurrentSlabKey <> @SlabkeyByPoint AND @MembershipSlabDeescalationMethod =2)

      BEGIN

          UPDATE LOYLOYALTYCARDINFORMATION SET   SLABKEY = @SlabkeyByPoint WHERE LOYALTYCARDINFORMATIONKEY= @CustomerKey

      END

  END

    DROP TABLE #tmpExpiryDate

DROP TABLE #tempLoyPoints

END