iVend Database Database : fn_Loy_GetRedemablePointsAndAmount User Defined Function

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@CardId

In

BigInt

8

@TrnsactionPayableAmount

In

Numeric

9

@MembershipSlabId

In

BigInt

8

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

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

-- Author:  <Munish Kumar>

-- Create date: <18th June 2008>

-- Description: <To get the redeemable points and Amount based on TransactionAmount>

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

Create FUNCTION [dbo].[fn_Loy_GetRedemablePointsAndAmount]

(

-- Add the parameters for the function here

@CardId BIGINT,

@TrnsactionPayableAmount DECIMAL(18,2),

@MembershipSlabId BIGINT

)

RETURNS

@RedemablePointsAndAmount TABLE

(

RedemablePoints BIGINT,

RedemableAmount DECIMAL(18,2)

)

AS

BEGIN

DECLARE @RedemptionMethodCode BIGINT

DECLARE @RedemableAmountFromExitingPoint DECIMAL(18, 2)

DECLARE @RedemableAmountFromPayableAmount DECIMAL(18, 2)

DECLARE @ActualRedemableAmount DECIMAL(18, 2)

DECLARE @PointsConversionFactor FLOAT

DECLARE @LocalCurrencyConversion FLOAT

DECLARE @RedemptionPercent FLOAT

DECLARE @RedeemablePoint BIGINT

DECLARE @ExistingRedeemablePoint BIGINT

DECLARE @SlabMinimumPoint BIGINT

DECLARE @LoyaltyslabId BIGINT

SELECT @RedemptionMethodCode = PointsRedemptionMethodId

FROM LoyLoyaltyConfiguration

SELECT @ExistingRedeemablePoint = AvailablePoints

FROM LoyLoyaltyCard

WHERE LoyLoyaltyCard.CardId = @CardId

IF @RedemptionMethodCode = 1 ---Current Slab Only

BEGIN

  /*

   Get all the calculator factor of exiting membership slab

  */

  SELECT @PointsConversionFactor = PointsConversionFactor, @LocalCurrencyConversion = LocalCurrencyConversion,

    @RedemptionPercent = RedemptionPercent

  FROM LoyMembershipSlabDetails

  WHERE LoyMembershipSlabDetails.MembershipSlabId = @MembershipSlabId

  /*

   Calculate the Redemable amount base upon the redemption percentage on Transaction

   payable amount for applicable membership slab on card

  */

  SET @RedemableAmountFromPayableAmount = (@TrnsactionPayableAmount * @RedemptionPercent) / 100

  SET @RedemableAmountFromExitingPoint = (@LocalCurrencyConversion/@PointsConversionFactor) * @ExistingRedeemablePoint

  /* Minimum value will be applicable

  */

  IF @RedemableAmountFromPayableAmount < @RedemableAmountFromExitingPoint

  BEGIN

    SET @ActualRedemableAmount = @RedemableAmountFromPayableAmount

  END

  ELSE

  BEGIN

    SET @ActualRedemableAmount = @RedemableAmountFromExitingPoint

  END

  SET @RedeemablePoint = CONVERT(BIGINT, (@PointsConversionFactor/@LocalCurrencyConversion) * @ActualRedemableAmount)

  INSERT INTO @RedemablePointsAndAmount

    VALUES (@RedeemablePoint, @ActualRedemableAmount)

END

ELSE IF @RedemptionMethodCode = 2   ---Slab Fall Back

BEGIN

  /* *************************************Declare Varibales*************************************************** */

  DECLARE @DataFromExitingPoints TABLE(LoyaltySlabId BIGINT, RedemablePoints BIGINT, RedemableAmount DECIMAL(18,2),

            PointsConversionFactor FLOAT, LocalCurrencyConversion FLOAT, MinimumPoints DECIMAL(18, 2))

  DECLARE @DataFromPayableAmount TABLE(LoyaltySlabId BIGINT, RedemablePoints BIGINT, RedemableAmount DECIMAL(18,2))

  DECLARE @CurrentMembershipMasterId BIGINT

  DECLARE @CurrentMembershipMinimumPoints BIGINT

  DECLARE @PointsTobeCalcualted BIGINT

  DECLARE @AmountAfterCalculation DECIMAL (18,2)

  /* ******************************************************************************************************** */

  /*Get all slabs those fall in same membership slab of card's current slab*/

  SELECT @RedemptionPercent = RedemptionPercent

  FROM LoyMembershipSlabDetails

  WHERE MembershipSlabid = @MembershipSlabId

  SET @RedemableAmountFromPayableAmount = (@TrnsactionPayableAmount * @RedemptionPercent) / 100

  SELECT @CurrentMembershipMasterId = MembershipMasterId, @CurrentMembershipMinimumPoints = LoyMembershipSlabDetails.MinimumPoints

  FROM LoyMembershipSlabDetails

  WHERE MembershipSlabid = @MembershipSlabId

  DECLARE CalculatePoint CURSOR FOR

  SELECT LoyMembershipSlabDetails.LoyaltySlabId, LoyMembershipSlabDetails.MinimumPoints, LoyMembershipSlabDetails.PointsConversionFactor,

     LoyMembershipSlabDetails.LocalCurrencyConversion

  FROM LoyMembershipSlabDetails

  WHERE LoyMembershipSlabDetails.MembershipMasterId = @CurrentMembershipMasterId

    AND LoyMembershipSlabDetails.MinimumPoints <= @CurrentMembershipMinimumPoints

  ORDER BY MinimumPoints DESC

  OPEN CalculatePoint

  FETCH NEXT FROM CalculatePoint

  INTO @LoyaltyslabId, @SlabMinimumPoint, @PointsConversionFactor, @LocalCurrencyConversion

  WHILE @@FETCH_STATUS = 0

  BEGIN

    SET @PointsTobeCalcualted = 0

    IF @SlabMinimumPoint < @ExistingRedeemablePoint

    BEGIN

      SET @PointsTobeCalcualted = @ExistingRedeemablePoint - @SlabMinimumPoint

      SET @AmountAfterCalculation = (@LocalCurrencyConversion / @PointsConversionFactor) * @PointsTobeCalcualted

      INSERT INTO @DataFromExitingPoints

        VALUES(@LoyaltyslabId, @PointsTobeCalcualted, @AmountAfterCalculation, @PointsConversionFactor , @LocalCurrencyConversion, @SlabMinimumPoint)

      SET @ExistingRedeemablePoint = @ExistingRedeemablePoint - @PointsTobeCalcualted

    END

    FETCH NEXT FROM CalculatePoint

    INTO @LoyaltyslabId, @SlabMinimumPoint, @PointsConversionFactor, @LocalCurrencyConversion, @RedemptionPercent

  END

  CLOSE CalculatePoint

  DEALLOCATE CalcualtePoint

  SELECT @RedeemablePoint = SUM(RedemablePoints), @ActualRedemableAmount = SUM(RedemableAmount) FROM @DataFromExitingPoints

  IF @RedemableAmountFromPayableAmount < @ActualRedemableAmount

  BEGIN

    DECLARE @PayableAmountPoitnsPerSlab BIGINT

    DECLARE @PayableAmountPerSlab DECIMAL(18, 2)

    DECLARE @RestAmountToBeCalculated DECIMAL(18, 2)

    DECLARE @PointAfterCalculation BIGINT

    DECLARE CalculateAmount CURSOR FOR

    SELECT LoyaltySlabId, RedemablePoints, RedemableAmount, PointsConversionFactor , LocalCurrencyConversion

    FROM @DataFromExitingPoints

    ORDER BY MinimumPoints DESC

    OPEN CalculateAmount

    FETCH NEXT FROM CalculateAmount

    INTO @LoyaltyslabId, @PayableAmountPoitnsPerSlab, @PayableAmountPerSlab, @PointsConversionFactor, @LocalCurrencyConversion

    WHILE @@FETCH_STATUS = 0

    BEGIN

      SET @RestAmountToBeCalculated = @RedemableAmountFromPayableAmount - @PayableAmountPerSlab

      IF @RestAmountToBeCalculated <= 0

      BEGIN

        SET @PointAfterCalculation = (@PointsConversionFactor/@LocalCurrencyConversion) * @RedemableAmountFromPayableAmount

        INSERT INTO @DataFromPayableAmount

          VALUES(@LoyaltyslabId, @PointAfterCalculation, @RedemableAmountFromPayableAmount)

      END

      ELSE

      BEGIN

        INSERT INTO @DataFromPayableAmount

          VALUES(@LoyaltyslabId, @PayableAmountPoitnsPerSlab, @PayableAmountPerSlab)

        SET @RedemableAmountFromPayableAmount = @RedemableAmountFromPayableAmount - @PayableAmountPerSlab

        FETCH NEXT FROM CalculateAmount

        INTO @LoyaltyslabId, @PayableAmountPoitnsPerSlab, @PayableAmountPerSlab, @PointsConversionFactor, @LocalCurrencyConversion

      END

    END

    CLOSE CalculatePoint

    DEALLOCATE CalcualtePoint

    SELECT @RedeemablePoint = SUM(RedemablePoints), @ActualRedemableAmount = SUM(RedemableAmount) FROM @DataFromPayableAmount

    INSERT INTO @RedemablePointsAndAmount

      VALUES (@RedeemablePoint, @ActualRedemableAmount)

  END

  ELSE

  BEGIN

    INSERT INTO @RedemablePointsAndAmount

      VALUES (@RedeemablePoint, @ActualRedemableAmount)

  END

END

RETURN

END