iVend Database Database : vwAnalytics_PromotionSales 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

Net Sales

Numeric

9 (22,5)

Promotion Sales

Numeric

9 (38,6)

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

ProPromotionBonusBuysMaster

Table

Stores the master data for the Promotion defined

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

TrxTransactionPromotionDetail

Table

Stores the promotion applied details on the transaction

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE VIEW [dbo].[vwAnalytics_PromotionSales]

as

SELECT convert(datetime,convert(nvarchar(10),T1.BusinessDate, 101), 101) BusinessDate, T2.Description as Store,

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

, CASE WHEN T0.Type = 1 THEN T0.TotalPostSaleDiscount * - 1 ELSE T0.TotalPostSaleDiscount END * T0.PromotionalQuantity / T0.Quantity As [Promotion Sales]

FROM TrxTransactionSaleItem T0 INNER JOIN TrxTransaction T1 ON T0.TransactionKey = T1.TransactionKey

INNER JOIN RtlStore T2 ON T1.StoreKey = T2.StoreKey

LEFT OUTER JOIN TrxTransactionPromotionDetail T3 ON T0.TransactionItemKey = T3.TransactionItemKey

LEFT OUTER JOIN ProPromotionBonusBuysMaster T4 ON T3.PromotionKey = T4.PromotionKey

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

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

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

AND T0.Type IN (0, 1, 3, 5)

AND T1.IsVoided = 0

AND T1.IsSuspended = 0