iVend Database Database : vw_InvProductInventoryInfo View

Creation Date

9/22/2015 12:01 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

ProductKey

BigInt

8

ParentMartixProductKey

BigInt

8

Description

VarWChar

100

WarehouseKey

BigInt

8

AvailableQuantity

Numeric

9 (38,5)

InStockQuantity

Numeric

9 (20,5)

OnFulFillmentQuantity

Numeric

9 (20,5)

UnitPrice

Numeric

9 (20,5)

UnitCost

Numeric

9 (20,5)

UnitMargin

Numeric

9 (21,5)

TotalAvailableToSellPrice

Numeric

9 (38,6)

TotalInStockPrice

Numeric

9 (38,7)

TotalInStockCost

Numeric

9 (38,7)

TotalAvailableToSellMargin

Numeric

9 (38,6)

TotalInStockMargin

Numeric

9 (38,6)

Coeficient

Numeric

9 (38,18)

Database Object

Object Type

Description

Dep Level

InvGetAllSelectedAttributeItemsList

Stored Procedure

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

2

InvLocation

Table

Save the location infomation.

2

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

vw_InvInventoryItem

View

1

CREATE VIEW [dbo].[vw_InvProductInventoryInfo]

AS

SELECT     A.ProductKey,

          A.ParentMartixProductKey,

          A.Description,

          B.WarehouseKey,

          E.AvailableQuantity,

          B.InStockQuantity,

          B.OnFulFillmentQuantity,

                    CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END AS [UnitPrice],

                     D.ProductCost AS [UnitCost],

                    CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END - D.ProductCost AS [UnitMargin],

                     E.AvailableQuantity * CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END AS [TotalAvailableToSellPrice],

                     B.InStockQuantity * CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END AS [TotalInStockPrice],

                     B.InStockQuantity * D.ProductCost AS [TotalInStockCost],

                     E.AvailableQuantity * (CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END - D.ProductCost) AS [TotalAvailableToSellMargin],

                     B.InStockQuantity * (CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END - D.ProductCost) AS TotalInStockMargin,

                    CASE WHEN D.ProductCost > 0 THEN CASE WHEN B.Price > 0 THEN B.Price ELSE A.BasePrice END / D.ProductCost ELSE 0 END AS Coeficient

FROM         dbo.InvProduct AS A INNER JOIN

                     dbo.InvInventoryItem AS B ON A.ProductKey = B.ProductKey INNER JOIN

                     dbo.InvWarehouse AS C ON C.WarehouseKey = B.WarehouseKey INNER JOIN

                     dbo.vw_ProductCost AS D ON D.ProductKey = A.ProductKey AND B.WarehouseKey = D.WarehouseKey

                    INNER JOIN dbo.vw_InvInventoryItem E ON E.InventoryItemKey = B.InventoryItemKey