iVend Database Database : Loyalty_GenderInfo View

Creation Date

6/3/2015 7:07 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

LoyaltyId

VarWChar

50

BirthDate

DBTimeStamp

4

Gender

VarWChar

50

ActualDate

DBTimeStamp

4

TransactionKey

Integer

4

PayableAmount

Numeric

9 (38,5)

LoyaltyProgram

VarWChar

40

LoyaltyProgramDesc

VarWChar

200

SubsidiaryKey

BigInt

8

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

PmtCurrency

Table

Define a list of all currecies 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

CREATE view [dbo].[Loyalty_GenderInfo]

AS

Select

Distinct LCI.LoyaltyId,

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.Gender WHEN LCI.RegistrationMethod=2 THEN ISNULL(LCI.Gender,'') END AS Gender,

LCI.RegistrationDate AS ActualDate,Count(Trx.TransactionKey)TransactionKey, Sum(TP.PaidAmount)PayableAmount, Cg.Id AS LoyaltyProgram,

Cg.Description AS LoyaltyProgramDesc, Sub.SubsidiaryKey,

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

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

From

LoyLoyaltyCardInformation LCI

LEFT OUTER JOIN CusCustomer Cust WITH (NOLOCK) ON LCI.CustomerKey = Cust.CustomerKey   and LCI.IsDeleted =0-- AND CUSt.IsDeleted=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

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

Group BY LCI.LoyaltyId, LCI.RegistrationMethod, Cust.BirthDate, LCI.BirthDate, Cust.Gender, LCI.Gender, LCI.RegistrationDate,

Cg.Id, Cg.Description, Sub.SubsidiaryKey, Sub.CultureInfo, ISNULL(Trx.ExchangeRate,1)