iVend Database Database : vw_ProductCost View

Creation Date

9/12/2019 6:40 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

ProductCost

Numeric

9 (20,5)

ProductKey

VarWChar

50

WarehouseKey

VarWChar

50

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

1

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvProductCost

Table

Store the historical cost of the product.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

CREATE VIEW [dbo].[vw_ProductCost]

AS

SELECT CASE A.CostingMethod WHEN 1 THEN ISNULL(AverageCost, 0)

      WHEN 2 THEN ISNULL

                       ((SELECT       TOP 1 Cost

                          FROM         InvProductCost WITH (NOLOCK)

                          WHERE        ProductKey = B.ProductKey AND WarehouseKey IN (C.WarehouseKey, '0') AND SubsidiaryKey IN (C.SubsidiaryKey, '0')

      AND IsDeleted = 0 AND CONVERT(VARCHAR, EffectiveDate, 112)

                                                    <= dbo.GetCompanyDateTime()

                          ORDER BY EffectiveDate DESC), 0)

      ELSE 0 END

      AS ProductCost,

       A.ProductKey, C.WarehouseKey

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