iVend Database Database : vwAnalytics_MerchandiseInventoryCount View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

Hierarchy

VarWChar

100

HierarchyNode

VarWChar

50

Product

VarWChar

100

Warehouse

VarWChar

100

CountingDate

DBTimeStamp

4

StartingCount

Numeric

9 (20,5)

InStoreQuantity

Numeric

9 (20,5)

CountedQuantity

Numeric

9 (20,5)

DifferenceQuantity

Numeric

9 (20,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

InvInventoryCount

Table

Stores the master data for the Inventory counting

1

InvInventoryCountDetail

Table

Stores the details counting data for the inventory

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

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

CREATE VIEW [dbo].[vwAnalytics_MerchandiseInventoryCount]

AS

SELECT     HM.Description AS Hierarchy, HD.Name AS HierarchyNode, Prod.Description AS Product, W.Description AS Warehouse, IIC.CountingDate, IICD.StartingCount,

                     IICD.InStoreQuantity, IICD.CountedQuantity, IICD.DifferenceQuantity

FROM         dbo.InvInventoryCountDetail AS IICD INNER JOIN

                     dbo.InvInventoryCount AS IIC ON IICD.InventoryCountKey = IIC.InventoryCountKey INNER JOIN

                     dbo.InvProductMerchandiseHierarchy AS IPMH ON IICD.ProductKey = IPMH.ProductKey INNER JOIN

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

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

                     dbo.InvProduct AS Prod ON IICD.ProductKey = Prod.ProductKey INNER JOIN

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

                     dbo.RtlStore AS Store ON W.WarehouseKey = Store.WarehouseKey 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     (Store.StoreKey = CASE WHEN T5.SiteId = 1 THEN Store.StoreKey ELSE T6.StoreKey END)