iVend Database Database > User Defined Functions > Rpt

Navigation: iVend Database Database > User Defined Functions >

iVend 6.6 Database Help

Rpt_ActiveLoyalty User Defined Function

Collapse All Expand All

iVend Database Database : Rpt_ActiveLoyalty User Defined Function

Properties

Creation Date	6/2/2015 6:56 PM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@Store	In		Boolean	1
@FromDate	In		VarChar	8
@ToDate	In		VarChar	8
@LoyaltyProgram	In		VarWChar	80
@TABLE_RETURN_VALUE	Return Value	Result table returned by table valued function	Empty	0

Objects that Rpt_ActiveLoyalty depends on

Database Object	Object Type	Description	Dep Level
CfgLoyaltyConfig	Table	Stores the configuration information for the loyalty	1
CusCustomer	Table	Defines the customer details that interact with the enterprise.	1
CusCustomerGroup	Table	Defines the groups in which the customers can be catagorised.	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
LoyPointsSummary	Table	Stores the Summary of Loyalty Points awarded in the system.	1
LoySlab	Table	Stores Loyalty Slab configured in the system.	1
PmtCurrency	Table	Define a list of all currecies defined in the system.	1
PmtExchangeRate	Table	Stores the Exchange Rate values for the currencies defined in application	1
RtlStore	Table	Contains the details of Stores defined in the system.	1
RtlSubsidiary	Table	Defines all the Subsidiaries defined in the application	1
SubSubsidiaryItem	Table	Stores Subsidiary information for each master, which can have multiple Subsidiaries assigned	1
TrxTransaction	Table	The main table which defined the primary details concerned with every type of transaction.	1

Procedure Source Code

CREATE FUNCTION [dbo].[Rpt_ActiveLoyalty]

(

@Store BIT,

@FromDate Varchar(8),

@ToDate Varchar(8),

@LoyaltyProgram NVARCHAR(80)

)

--- Select * From dbo.Rpt_ActiveLoyalty (0,'20140501', '20161103','Programme02')

RETURNs @Active TABLE

(

LoyaltyId nvarchar(100),

MemberOrder int,

SlabID nvarchar(40),

SlabDescription nvarchar(200),

StoreId nvarchar(40),

StoreDesc nvarchar(200),

CustomerGroupID nvarchar(40),

CustomerGroupDesc nvarchar(200),

CustomerID nvarchar(40),

RegistrationDate datetime,

RegistrationMethod int,

LoyaltyProgram nvarchar(80),

LoyaltyProgramDesc nvarchar(400),

LoyaltyMemberID nvarchar(200),

LoyaltyMemberDesc nvarchar(200),

FirstName nvarchar(200),

LastName nvarchar(80),

Gender nvarchar(100),

BirthDate Datetime,

AnniversaryDate Datetime,

MobilePhone nvarchar(100),

PhoneNumber nvarchar(100),

Email nvarchar(200),

WebPage nvarchar(200),

AvailablePoint Int,

AvailableCurrency Int,

CultureInfo nvarchar(200),

BaseCultureInfo nvarchar(200),

ExchangeRate Decimal(20,5)

)

BEGIN

-- Declare @Store NVarchar(400) = '', @FromDate Varchar(8) = '20150201', @ToDate Varchar(8) = '20150531'

Declare @SQLQry NVarchar(Max)

IF @Store = 1

BEGIN

INSERT INTo @Active

Select

Distinct LCI.LoyaltyId, 1 AS MemberOrder,LS.Id AS SlabID, LS.Description AS SlabDescription,

ISNULL(Store.Id, 'HO') AS StoreId, ISNULL(Store.Description, 'Enterprise') AS StoreDesc,

CustGrp.Id AS CustomerGroupID, CustGrp.Description AS CustomerGroupDesc,

Cust.Id AS CustomerID, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id AS LoyaltyProgram, Cg.Description AS LoyaltyProgramDesc,

'No Member Group' AS LoyaltyMemberID, 'No Member Group' AS LoyaltyMemberDesc,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.FirstName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.FirstName,'') END AS FirstName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.LastName,'')

WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.LastName,'') END AS LastName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Gender WHEN LCI.RegistrationMethod=2 THEN LCI.Gender END AS Gender,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.BirthDate WHEN LCI.RegistrationMethod=2 THEN LCI.BirthDate END AS BirthDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.AnniversaryDate WHEN LCI.RegistrationMethod=2 THEN LCI.AnniversaryDate END AS AnniversaryDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.MobilePhone WHEN LCI.RegistrationMethod=2 THEN LCI.MobilePhone END AS MobilePhone,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.PhoneNumber WHEN LCI.RegistrationMethod=2 THEN LCI.PhoneNumber END AS PhoneNumber,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.WebPage WHEN LCI.RegistrationMethod=2 THEN LCI.WebPage END AS WebPage,

LP.AvailablePoints, LP.AvailableCurrency,

(Case WHEN (Sub.SubsidiaryKey IS NULL OR ISNULL(Sub.SubsidiaryKey,0)=0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)ELSE Sub.CultureInfo END)AS CultureInfo,

(Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, ISNULL(ER.SellRate,1) AS ExchangeRate

From

LoySlab LS WITH (NOLOCK)

LEFT OUTER JOIN LoyLoyaltyCardInformation LCI WITH (NOLOCK) ON LS.SlabKey = LCI.SlabKey AND ISNULL(LCI.IsDeleted,0) =0

INNER JOIN LoyPointsSummary LP ON LCI.LoyaltyCardInformationKey = LP.CustomerKey

LEFT OUTER JOIN TrxTransaction Trx WITH (NOLOCK) ON isnull(Trx.LoyaltyId,'') = LCI.LoyaltyId AND Trx.IsVoided =0 AND Trx.IsSuspended=0

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON Cust.CustomerKey = Trx.CustomerKey

LEFT OUTER JOIN CusCustomerGroup CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON LCI.StoreKey = Store.StoreKey

INNER JOIN CfgLoyaltyConfig Cg ON LCI.LoyaltyConfigKey = Cg.LoyaltyConfigKey

LEFT OUTER JOIN SubSubsidiaryItem SSI ON Cg.LoyaltyConfigKey = SSI.SourceKey AND SourceType=328

LEFT OUTER JOIN RtlSubsidiary Sub ON SSI.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER ON Sub.LocalCurrencyKey = ER.CurrencyKey AND Convert(Varchar,ER.EffectiveDate,112) = Convert(Varchar,getDate(),112)

Where

--LCI.RegistrationMethod = (Select RegistrationMethod From CfgLoyaltyConfig) AND

LCI.HasMembershipGroup = 0

AND Cg.Id = CASE WHEN @LoyaltyProgram<>'' THEN @LoyaltyProgram ELSE Cg.Id END

AND LCI.LoyaltyId IN (Select LoyaltyId From TrxTransaction

Where Convert(varchar,ActualDate,112) >= Case When @FromDate <> '' Then Convert(varchar, @FromDate,112) Else Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) END

AND Convert(varchar,ActualDate,112) <= Case When @ToDate <> '' Then Convert(Varchar, @ToDate,112) Else Convert(Varchar,DATEADD(yy,0,Getdate()),112) END)

AND Convert(Varchar,Trx.ActualDate,112) >= Case When @FromDate <> '' Then Convert(varchar, @FromDate,112) Else Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) END

AND Convert(Varchar,Trx.ActualDate,112) <= Case When @ToDate <> '' Then Convert(Varchar, @ToDate,112) Else Convert(Varchar,DATEADD(yy,0,Getdate()),112) END

--ORDER BY MinimumValue ASC'

UNION ALL

Select

Distinct LCI.LoyaltyId, 2 AS MemberOrder, LS.Id AS SlabID, LS.Description AS SlabDescription,

ISNULL(Store.Id, 'HO') AS StoreId, ISNULL(Store.Description, 'Enterprise') AS StoreDesc,

CustGrp.Id AS CustomerGroupID, CustGrp.Description AS CustomerGroupDesc, Cust.Id AS CustomerID,

LM.Created AS RegistrationDate, LCI.RegistrationMethod, Cg.Id AS LoyaltyProgram, Cg.Description AS LoyaltyProgramDesc,

LM.Id AS LoyaltyMemberID, LM.Description AS LoyaltyMemberDesc,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.FirstName,'') WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.FirstName,'') END AS FirstName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN ISNULL(Cust.LastName,'') WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.LastName,'') END AS LastName,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Gender WHEN LCI.RegistrationMethod=2 THEN LCI.Gender END AS Gender,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.BirthDate WHEN LCI.RegistrationMethod=2 THEN LCI.BirthDate END AS BirthDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.AnniversaryDate WHEN LCI.RegistrationMethod=2 THEN LCI.AnniversaryDate END AS AnniversaryDate,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.MobilePhone WHEN LCI.RegistrationMethod=2 THEN LCI.MobilePhone END AS MobilePhone,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.PhoneNumber WHEN LCI.RegistrationMethod=2 THEN LCI.PhoneNumber END AS PhoneNumber,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

CASE WHEN LCI.RegistrationMethod IN (1,3) THEN Cust.WebPage WHEN LCI.RegistrationMethod=2 THEN LCI.WebPage END AS WebPage,

LM.AvailablePoints, LM.AvailableCurrency,

(Case WHEN (Sub.SubsidiaryKey IS NULL OR ISNULL(Sub.SubsidiaryKey,0)=0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)ELSE Sub.CultureInfo END)AS CultureInfo,

(Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, ISNULL(ER.SellRate,1) AS ExchangeRate

From

LoySlab LS WITH (NOLOCK)

LEFT OUTER JOIN LoyMembershipGroup LM WITH (NOLOCK) ON LS.SlabKey=LM.SlabKey

LEFT OUTER JOIN LoyLoyaltyCardInformation LCI WITH (NOLOCK) ON LM.MembershipGroupKey = LCI.MembershipGroupKey AND ISNULL(LCI.IsDeleted,0) =0

INNER JOIN LoyPointsSummary LP ON LCI.LoyaltyCardInformationKey = LP.CustomerKey

LEFT OUTER JOIN TrxTransaction Trx WITH (NOLOCK) ON ISNULL(Trx.LoyaltyId,'') = LCI.LoyaltyId AND Trx.IsVoided =0 AND Trx.IsSuspended=0

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON Cust.CustomerKey = Trx.CustomerKey

LEFT OUTER JOIN CusCustomerGroup CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON LCI.StoreKey = Store.StoreKey

INNER JOIN CfgLoyaltyConfig Cg ON LCI.LoyaltyConfigKey = Cg.LoyaltyConfigKey

LEFT OUTER JOIN SubSubsidiaryItem SSI ON Cg.LoyaltyConfigKey = SSI.SourceKey AND SourceType=328

LEFT OUTER JOIN RtlSubsidiary Sub ON SSI.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER ON Sub.LocalCurrencyKey = ER.CurrencyKey AND Convert(Varchar,ER.EffectiveDate,112) = Convert(Varchar,getDate(),112)

Where

--LCI.RegistrationMethod = (Select RegistrationMethod From CfgLoyaltyConfig) AND

LCI.HasMembershipGroup=1

AND Cg.Id = CASE WHEN @LoyaltyProgram<>'' THEN @LoyaltyProgram ELSE Cg.Id END

AND LCI.LoyaltyId IN (Select LoyaltyId From TrxTransaction

Where Convert(varchar,ActualDate,112) >= Case When @FromDate <> '' Then Convert(varchar, @FromDate,112) Else Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) END

AND Convert(varchar,ActualDate,112) <= Case When @ToDate <> '' Then Convert(Varchar, @ToDate,112) Else Convert(Varchar,DATEADD(yy,0,Getdate()),112) END)

AND Convert(Varchar,Trx.ActualDate,112) >= Case When @FromDate <> '' Then Convert(varchar, @FromDate,112) Else Convert(Varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) END

AND Convert(Varchar,Trx.ActualDate,112) <= Case When @ToDate <> '' Then Convert(Varchar, @ToDate,112) Else Convert(Varchar,DATEADD(yy,0,Getdate()),112) END

END

IF @Store =0

BEGIN

INSERT INTo @Active

Select

Distinct LCI.LoyaltyId, 1 AS MemberOrder, LS.Id AS SlabID, LS.Description AS SlabDescription, 'SID' AS StoreId, 'SDESC' AS StoreDesc,