iVend Database Database : vwAnalytics_GiftCertificates View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

BusinessDate

DBTimeStamp

4

Store

VarWChar

100

GC Sales

Numeric

9 (20,5)

GC Recharge

Integer

4

GC CashBack

Integer

4

Redemption

Numeric

9 (22,5)

Net Sales

Numeric

9 (22,5)

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

PmtGiftCertificateLog

Table

Stores the log for every Gift certificate used

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionGiftCertificate

Table

Stores the values for the Gift certificate for the transaction

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE VIEW [dbo].[vwAnalytics_GiftCertificates]

as

SELECT convert(datetime,convert(nvarchar(10),T.BusinessDate, 101), 101) BusinessDate, S.Description as Store,

[GC Sales], 0 as [GC Recharge],0 as [GC CashBack], GC.Redemption, GC.[Net Sales]

FROM (

select 'A' UniqueKey, GCL.GiftCertificateLogKey as SubKey, GCL.SourceKey as TransactionKey

 , 0 as [GC Sales], 0 as [GC Recharge],0 as [GC CashBack]

 , GCL.Amount * -1 AS [Redemption], 0 as [Net Sales]

From  PmtGiftCertificateLog GCL

INNER JOIN TrxTransaction T ON GCL.SourceKey = T.TransactionKey

where GCL.SourceType = 2

UNION

select 'B' UniqueKey, TransactionGiftCertificateKey as SubKey, TransactionKey,

CASE WHEN Type = 0 THEN Total ELSE 0 END AS [GC Sales],

CASE WHEN Type = 1 THEN Total ELSE 0 END AS [GC Recharge],

CASE WHEN Type = 2 THEN Total * -1 ELSE 0 END AS [GC CashBack]

, 0 , 0 as [Net Sales]

FROM

TrxTransactionGiftCertificate

UNION

select 'C' UniqueKey, TransactionItemKey SubKey, TSI.TransactionKey

 , 0 as [GC Sales], 0 as [GC Recharge],0 as [GC CashBack], 0,

CASE WHEN TSI.Type = 1 THEN TSI.TotalPostSaleDiscount * - 1 ELSE TSI.TotalPostSaleDiscount END AS [Net Sales]

FROM TrxTransactionSaleItem TSI

) GC

INNER JOIN TrxTransaction T ON GC.TransactionKey = T.TransactionKey

AND T.IsSuspended = 0

AND T.IsVoided = 0

INNER JOIN RtlStore S ON T.StoreKey = S.StoreKey

INNER JOIN (SELECT TOP 1 SiteId from CfgSiteInformation) As T5 on 1=1

LEFT OUTER JOIN RtlStore T6 ON T5.SiteId = t6.SiteId

WHERE T.StoreKey = CASE WHEN T5.SiteId = 1 THEN T.StoreKey ELSE T6.StoreKey END