iVend Database Database : vwAnalytics_MerchandisePromotionSales View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

HierarchyKey

BigInt

8

HierarchyDetailKey

BigInt

8

Hierarchy

VarWChar

100

HierarchyNode

VarWChar

50

Product

VarWChar

100

Level1

VarWChar

100

Level2

VarWChar

100

Level3

VarWChar

100

Level4

VarWChar

100

Level5

VarWChar

100

Level6

VarWChar

100

Level7

VarWChar

100

Level8

VarWChar

100

Level9

VarWChar

100

Level10

VarWChar

100

Level11

VarWChar

100

Level12

VarWChar

100

Level13

VarWChar

100

Level14

VarWChar

100

Level15

VarWChar

100

BusinessDate

DBTimeStamp

4

Store

VarWChar

100

Net Sales

Numeric

9 (22,5)

Net Sales without Tax

Numeric

9 (23,5)

Promotion Sales

Numeric

9 (38,6)

Promotional Quantity

Numeric

9 (20,5)

Promotion

VarWChar

100

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

InvMerchandiseHierarchy

Table

Header table for saving the merchandise Hierarch details.

1

InvMerchandiseHierarchyDetail

Table

Detail table for saving the merchandise Hierarch tree details.

1

InvProduct

Table

Stores the product related details.

1

InvProductMerchandiseHierarchy

Table

Stores the values related to a defined Merchandise hirarchy for a product

2

InvProductMerchandiseHierarchyLog

Table

Used to log the transactions releated to the merchandise Hierarchy

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

vw_ProductMerchandiseHierarchy

View

1

CREATE VIEW [dbo].[vwAnalytics_MerchandisePromotionSales]

AS

SELECT     IMHD.HierarchyKey, IMHD.HierarchyDetailKey, HM.Description AS Hierarchy, HD.Name AS HierarchyNode, T0.Description AS Product, VW_1.Level1, VW_1.Level2,

                     VW_1.Level3, VW_1.Level4, VW_1.Level5, VW_1.Level6, VW_1.Level7, VW_1.Level8, VW_1.Level9, VW_1.Level10, VW_1.Level11, VW_1.Level12, VW_1.Level13,

                     VW_1.Level14, VW_1.Level15, T1.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 - T0.Tax) * - 1 ELSE (T0.TotalPostSaleDiscount - T0.Tax) END AS [Net Sales without Tax],

                     T0.TotalPostSaleDiscount * T0.PromotionalQuantity / T0.Quantity AS [Promotion Sales], T0.PromotionalQuantity AS [Promotional Quantity], T4.Description AS Promotion

FROM         dbo.InvProductMerchandiseHierarchyLog AS IMHD INNER JOIN

                     dbo.InvMerchandiseHierarchyDetail AS HD ON IMHD.HierarchyDetailKey = HD.HierarchyDetailKey AND IMHD.HierarchyKey = HD.HierarchyKey INNER JOIN

                     dbo.InvMerchandiseHierarchy AS HM ON HD.HierarchyKey = HM.HierarchyKey INNER JOIN

                     dbo.TrxTransactionSaleItem AS T0 ON IMHD.DetailKey = T0.TransactionItemKey AND IMHD.DocumentKey = T0.TransactionKey INNER JOIN

                     dbo.TrxTransaction AS T1 ON T0.TransactionKey = T1.TransactionKey INNER JOIN

                     dbo.RtlStore AS T2 ON T1.StoreKey = T2.StoreKey LEFT OUTER JOIN

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

                     dbo.ProPromotionBonusBuysMaster AS T4 ON T3.PromotionKey = T4.PromotionKey INNER JOIN

                         (SELECT     TOP (1) SiteId

                          FROM          dbo.CfgSiteInformation) AS T5 ON 1 = 1 LEFT OUTER JOIN

                     dbo.RtlStore AS T6 ON T5.SiteId = T6.SiteId INNER JOIN

                         (SELECT     VW.ProductKey, VW.HierarchyKey, CASE WHEN Level1 IS NULL THEN Prod.Description ELSE Level1 END AS Level1, CASE WHEN Level2 IS NULL

                                                  THEN Prod.Description ELSE Level2 END AS Level2, CASE WHEN Level3 IS NULL THEN Prod.Description ELSE Level3 END AS Level3,

                                                  CASE WHEN Level4 IS NULL THEN Prod.Description ELSE Level4 END AS Level4, CASE WHEN Level5 IS NULL

                                                  THEN Prod.Description ELSE Level5 END AS Level5, CASE WHEN Level6 IS NULL THEN Prod.Description ELSE Level6 END AS Level6,

                                                  CASE WHEN Level7 IS NULL THEN Prod.Description ELSE Level7 END AS Level7, CASE WHEN Level8 IS NULL

                                                  THEN Prod.Description ELSE Level8 END AS Level8, CASE WHEN Level9 IS NULL THEN Prod.Description ELSE Level9 END AS Level9,

                                                  CASE WHEN Level10 IS NULL THEN Prod.Description ELSE Level10 END AS Level10, CASE WHEN Level11 IS NULL

                                                  THEN Prod.Description ELSE Level11 END AS Level11, CASE WHEN Level12 IS NULL THEN Prod.Description ELSE Level12 END AS Level12,

                                                  CASE WHEN Level13 IS NULL THEN Prod.Description ELSE Level13 END AS Level13, CASE WHEN Level14 IS NULL

                                                  THEN Prod.Description ELSE Level14 END AS Level14, CASE WHEN Level15 IS NULL THEN Prod.Description ELSE Level15 END AS Level15

                          FROM          dbo.vw_ProductMerchandiseHierarchy AS VW INNER JOIN

                                                  dbo.InvProduct AS Prod ON Prod.ProductKey = VW.ProductKey

                          WHERE      (VW.HierarchyKey IS NOT NULL)) AS VW_1 ON T0.ProductKey = VW_1.ProductKey AND IMHD.HierarchyKey = VW_1.HierarchyKey

WHERE     (T1.StoreKey = CASE WHEN T5.SiteId = 1 THEN T1.StoreKey ELSE T6.StoreKey END) AND (T1.IsSuspended = 0) AND (T0.Type IN (0, 1, 3, 5)) AND (T1.IsVoided = 0)