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

Sales Amount

Numeric

9 (20,5)

Refund Amount

Numeric

9 (20,5)

Net Sales

Numeric

9 (22,5)

Previous Year Net Sales

Numeric

9 (22,5)

Current Year Net Sales

Numeric

9 (22,5)

Previous Month Net Sales

Numeric

9 (22,5)

Current Month Net Sales

Numeric

9 (22,5)

Net Quantity

Numeric

9 (22,5)

Sales Quantity

Numeric

9 (20,5)

Transaction Type

Integer

4

Line Item Flag

VarChar

6

Tax

Numeric

9 (20,5)

BusinessDate

DBTimeStamp

4

TransactionId

VarWChar

50

Store

VarWChar

100

POS

VarWChar

100

POSId

VarWChar

20

ProductGroup

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

InvProductGroup

Table

Defined the various groups under which the products can be categorised.

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

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RtlPOS

Table

Contains details about all POS defined in the system.

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

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

vw_ProductMerchandiseHierarchy

View

1

CREATE VIEW [dbo].[vwAnalytics_NetSales]

AS

SELECT     IMHD.HierarchyKey, IMHD.HierarchyDetailKey, HM.Description AS Hierarchy, HD.Name AS HierarchyNode, P.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, CASE WHEN TSI.Type <> 1 THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Sales Amount],

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

                    CASE WHEN TSI.Type = 1 AND (year(T .BusinessDate) = (year(getdate()) - 1)) THEN TSI.TotalPostSaleDiscount * - 1 WHEN (year(T .BusinessDate) = (year(getdate()) - 1))

                    THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Previous Year Net Sales], CASE WHEN TSI.Type = 1 AND (year(T .BusinessDate) = year(getdate()))

                    THEN TSI.TotalPostSaleDiscount * - 1 WHEN year(T .BusinessDate) = year(getdate()) THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Current Year Net Sales], CASE WHEN TSI.Type = 1 AND

                    year(T .BusinessDate) = year(dateadd(month, - 1, getdate())) AND month(T .BusinessDate) = month(dateadd(month, - 1, getdate()))

                    THEN TSI.TotalPostSaleDiscount * - 1 WHEN year(T .BusinessDate) = year(dateadd(month, - 1, getdate())) AND month(T .BusinessDate) = month(dateadd(month, - 1, getdate()))

                    THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Previous Month Net Sales], CASE WHEN TSI.Type = 1 AND year(T .BusinessDate) = year(getdate()) AND month(T .BusinessDate)

                    = month(getdate()) THEN TSI.TotalPostSaleDiscount * - 1 WHEN year(T .BusinessDate) = year(getdate()) AND month(T .BusinessDate) = month(getdate())

                    THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Current Month Net Sales], CASE WHEN TSI.Type = 1 THEN TSI.Quantity * - 1 ELSE TSI.Quantity END AS [Net Quantity],

                    CASE WHEN TSI.Type <> 1 THEN TSI.Quantity ELSE 0 END AS [Sales Quantity], CASE WHEN TSI.Type = 3 THEN 0 ELSE TSI.Type END AS [Transaction Type],

                    CASE WHEN TSI.Type IN (0, 3, 5) THEN 'Sale' ELSE 'Refund' END AS [Line Item Flag], TSI.Tax, T.BusinessDate, T.TransactionId, S.Description AS Store,

                     Pos.Description AS POS, P.Id AS POSId, PG.Description AS ProductGroup

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 TSI ON IMHD.DetailKey = TSI.TransactionItemKey AND IMHD.DocumentKey = TSI.TransactionKey INNER JOIN

                     dbo.TrxTransaction AS T ON TSI.TransactionKey = T.TransactionKey INNER JOIN

                     dbo.InvWarehouse AS W ON TSI.WarehouseKey = W.WarehouseKey INNER JOIN

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

                     dbo.RtlPOS AS Pos ON T.POSKey = Pos.POSKey INNER JOIN

                     dbo.InvProduct AS P ON TSI.ProductKey = P.ProductKey INNER JOIN

                     dbo.InvProductGroup AS PG ON P.ProductGroupKey = PG.ProductGroupKey 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 TSI.ProductKey = VW_1.ProductKey AND IMHD.HierarchyKey = VW_1.HierarchyKey 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

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