iVend Database Database : vw_InvInventoryItem View

Creation Date

9/18/2015 10:50 AM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

InventoryItemKey

BigInt

8

WarehouseKey

BigInt

8

AvailableQuantity

Numeric

9 (38,5)

Database Object

Object Type

Description

Dep Level

InvGetAllSelectedAttributeItemsList

Stored Procedure

2

vw_InvProductInventoryInfo

View

1

Database Object

Object Type

Description

Dep Level

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvInventoryLocation

Table

Stores the location information for the Inventory available at different locations

1

InvLocation

Table

Save the location infomation.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

CREATE VIEW [dbo].[vw_InvInventoryItem]

AS

SELECT A.InventoryItemKey,A.WarehouseKey, CASE WHEN I.IsLocationEnabled = 1 THEN ISNULL(SUM(ISNULL(B.AvailableQuantity,0)),0) ELSE A.AvailableQuantity END 'AvailableQuantity'

FROM InvInventoryItem A

INNER JOIN InvWarehouse I ON I.WarehouseKey = A.WarehouseKey

LEFT OUTER JOIN InvInventoryLocation B ON A.WarehouseKey = B.WarehouseKey AND A.ProductKey = B.ProductKey

LEFT OUTER JOIN InvLocation C ON B.LocationKey = C.LocationKey AND C.WarehouseKey = A.WarehouseKey

WHERE ISNULL(C.Sales, 1) = 1

GROUP BY A.InventoryItemKey,A.WarehouseKey, I.IsLocationEnabled, A.AvailableQuantity