iVend Database Database : Loyalty_ActiveBillValue View

Creation Date

6/5/2015 6:37 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

LoyaltyId

VarWChar

50

MemberOrder

Integer

4

LoyaltyMemberID

VarWChar

20

LoyaltyMemberDesc

VarWChar

100

SlabID

VarWChar

20

SlabDescription

VarWChar

100

StoreId

VarWChar

20

StoreDesc

VarWChar

100

CustomerID

VarWChar

20

RegistrationDate

DBTimeStamp

4

RegistrationMethod

Integer

4

LoyaltyProgram

VarWChar

40

LoyaltyProgramDesc

VarWChar

200

FirstName

VarWChar

100

LastName

VarWChar

40

Gender

VarWChar

50

BirthDate

DBTimeStamp

4

AnniversaryDate

DBTimeStamp

4

MobilePhone

VarWChar

50

Email

VarWChar

100

ActualDate

DBTimeStamp

4

WeekDay

VarWChar

30

Total

Numeric

9 (38,5)

CultureInfo

VarWChar

40

BaseCultureInfo

VarWChar

20

ExchangeRate

Numeric

9 (20,5)

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

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

LoySlab

Table

Stores Loyalty Slab configured in the system.

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

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

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

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

CREATE View [dbo].[Loyalty_ActiveBillValue]

AS

With R(N)

AS(

Select N=1

UNION ALL

Select N+1

From R

Where N<7

)

, Q AS

(

Select

Distinct LCI.LoyaltyId, 1 AS MemberOrder, 'No Member Group' AS LoyaltyMemberID, 'No Member Group' AS LoyaltyMemberDesc,

 LS.Id AS SlabID, LS.Description AS SlabDescription,

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

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

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.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

 Trx.ActualDate, DateName(dw,Trx.ActualDate)[WeekDay], Sum(TP.PaidAmount)Total,

 (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, Trx.ExchangeRate

From

LoyLoyaltyCardInformation LCI WITH (NOLOCK)

LEFT OUTER JOIN LoySlab LS WITH (NOLOCK) ON LCI.SlabKey = LS.SlabKey

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON LCI.CustomerKey = Cust.CustomerKey AND ISNULL(LCI.IsDeleted,0) =0

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

INNER JOIN TrxTransactionPayment TP ON Trx.TransactionKey = Tp.TransactionKey AND TP.TenderType<>9

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON Trx.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

Where

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

LCI.LoyaltyId IN (Select LoyaltyId From LoyLoyaltyCardInformation )

Group BY

  LCI.LoyaltyID,LS.Id, LS.Description, Store.Id, Store.Description, Cust.Id, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id, Cg.Description,

  LCI.FirstName, LCI.LastName,LCI.Gender,LCI.BirthDate, LCI.AnniversaryDate, LCI.MobilePhone,LCI.Email, Cust.FirstName, Cust.LastName, Cust.Gender, Cust.BirthDate, Cust.AnniversaryDate,

  Cust.MobilePhone, Cust.Email, Trx.ActualDate, DATENAME(Dw, Trx.ActualDate), Sub.SubsidiaryKey, Sub.CultureInfo, Trx.ExchangeRate

UNION ALL

Select

Distinct LCI.LoyaltyId, 2 AS MemberOrder, LM.Id AS LoyaltyMemberID, LM.Description AS LoyaltyMemberDesc,

 LS.Id AS SlabID, LS.Description AS SlabDescription,

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

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

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.Email WHEN LCI.RegistrationMethod=2 THEN LCI.Email END AS Email,

 Trx.ActualDate, DateName(dw,Trx.ActualDate)[WeekDay], Sum(TP.PaidAmount)Total,

 (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, Trx.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

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON LCI.CustomerKey = Cust.CustomerKey AND ISNULL(LCI.IsDeleted,0) =0

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

INNER JOIN TrxTransactionPayment TP ON Trx.TransactionKey = Tp.TransactionKey AND TP.TenderType<>9

LEFT OUTER JOIN RtlStore Store WITH (NOLOCK) ON Trx.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

Where

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

LCI.HasMembershipGroup=1

AND LCI.LoyaltyId IN (Select LoyaltyId From LoyLoyaltyCardInformation )

Group BY

  LCI.LoyaltyID,  LM.Id, LM.Description, LS.Id, LS.Description, Store.Id, Store.Description, Cust.Id, LCI.RegistrationDate, LCI.RegistrationMethod, Cg.Id, Cg.Description,

  LCI.FirstName, LCI.LastName, LCI.Gender, LCI.BirthDate, LCI.AnniversaryDate, LCI.MobilePhone,LCI.Email, Cust.FirstName, Cust.LastName, Cust.Gender, Cust.BirthDate, Cust.AnniversaryDate,

  Cust.MobilePhone, Cust.Email, Trx.ActualDate, DATENAME(Dw, Trx.ActualDate),Sub.SubsidiaryKey, Sub.CultureInfo, Trx.ExchangeRate

)

Select

 LoyaltyId, MemberOrder, LoyaltyMemberID, LoyaltyMemberDesc, SlabID, SlabDescription, StoreId, StoreDesc, CustomerID, RegistrationDate, RegistrationMethod,

 LoyaltyProgram, LoyaltyProgramDesc, FirstName, LastName, Gender, BirthDate, AnniversaryDate, MobilePhone, Email, ActualDate, DATENAME(Dw, R.N) AS 'WeekDay',

 Total, CultureInfo, BaseCultureInfo, ExchangeRate

From R LEFT OUTER JOIN Q ON Q.[WeekDay] = DATENAME(DW, N)