iVend Database Database : Loy_GetPointsSummaryForPortalIntegration Stored Procedure

Creation Date

2/23/2016 6:02 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@LoyaltyConfigKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgCountry

Table

The country details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

1

CfgState

Table

The state details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

1

LoyLoyaltyCardInformation

Table

Store the Details of Loyalty Customer 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 [dbo].[Loy_GetPointsSummaryForPortalIntegration](@LoyaltyConfigKey nvarchar(50))

AS

BEGIN

    SELECT AVAILABLEPOINTS,AVAILABLECURRENCY,AWARDEDPOINTS,AWARDEDCURRENCY,REDEEMEDPOINTS,REDEEMEDCURRENCY,EXPIREDPOINTS,EXPIREDCURRENCY,

                            LOYALTYID,CUSTOMERKEY=LOYALTYCARDINFORMATIONKEY,POINTSSUMMARYKEY,FIRSTNAME,LASTNAME,ADDRESS1,ANNIVERSARYDATE,BIRTHDATE,CITY,COUNTRY=ISNULL(D.ID,'')

                            ,[STATE] =ISNULL(E.ID,''),EMAIL,GENDER,

                            MOBILEPHONE,PHONENUMBER,ZIPCODE,SlabName= C.ID FROM LOYPOINTSSUMMARY A

                            INNER JOIN LOYLOYALTYCARDINFORMATION B ON A.CUSTOMERKEY=B.LOYALTYCARDINFORMATIONKEY

                            INNER JOIN LOYSLAB C ON B.SLABKEY= C.SLABKEY

                            LEFT OUTER JOIN CFGCOUNTRY D ON D.COUNTRYKEY= B.COUNTRYKEY

                            LEFT OUTER JOIN CFGSTATE E ON E.STATEKEY= B.STATEKEY

                            WHERE A.ISINTEGRATEDWITHPORTAL=0   and B.LoyaltyConfigKey = @LoyaltyConfigKey

END