iVend Database Database : vwAnalytics_InventorySnapshot View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

ProductId

VarWChar

20

ProductName

VarWChar

100

WarehouseId

VarWChar

20

WarehouseName

VarWChar

100

Price

Numeric

9 (20,5)

InStockQty

Numeric

9 (20,5)

AvailableQty

Numeric

9 (20,5)

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

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvProductGroup

Table

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

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_InventorySnapshot]

as

Select Prod.Id as ProductId

 , Prod.Description as ProductName

 , Warehouse.Id as WarehouseId

 , Warehouse.Description as WarehouseName

 , InvItem.Price as Price

 , InvItem.InStockQuantity as InStockQty

 , InvItem.AvailableQuantity as AvailableQty

 , ProdGrp.Description as ProductGroup

From InvInventoryItem As InvItem

Inner Join InvProduct As Prod On InvItem.ProductKey = Prod.ProductKey

Inner Join InvProductGroup As ProdGrp On Prod.ProductGroupKey = ProdGrp.ProductGroupKey

Inner Join InvWarehouse As Warehouse On InvItem.WarehouseKey = Warehouse.WarehouseKey

INNER JOIN RtlStore Store ON Warehouse.WarehouseKey = Store.WarehouseKey

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

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

WHERE  InvItem.AvailableQuantity <> 0

AND Store.StoreKey = CASE WHEN T5.SiteId = 1 THEN Store.StoreKey ELSE T6.StoreKey END