iVend Database Database : vw_InventoryCost View

Creation Date

9/12/2019 6:40 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

DocumentType

VarChar

28

WarehouseKey

VarWChar

50

WarehouseId

VarWChar

20

WarehouseName

VarWChar

100

ProductKey

VarWChar

50

Id

VarWChar

50

Description

VarWChar

100

ProductGroupKey

VarWChar

50

ProductGroupId

VarWChar

20

QuantityReceived

Numeric

9 (22,5)

BusinessDate

DBTimeStamp

4

TotalPostSaleDiscount

Numeric

9 (38,6)

CostingMethod

Integer

4

SubsidiaryKey

VarWChar

50

Subsidiary

VarWChar

20

SubsidiaryDesc

VarWChar

100

ExchangeRate

Numeric

9 (20,5)

CultureInfo

VarWChar

40

BaseCultureInfo

VarWChar

20

Rate

Numeric

9 (38,6)

Database Object

Object Type

Description

Dep Level

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvGoodsReturn

Table

Stores the data related to Inventory Goods Return

1

InvGoodsReturnDetail

Table

Stores the details of inventory goods return

1

InvInventoryItemLog

Table

Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated.

1

InvInventoryTransaction

Table

This gets affected when the inventory of a particular item is consumed at the store itself for any reason.

1

InvInventoryTransactionDetail

Table

Defines the details of the product on the goods issue note

1

InvProduct

Table

Stores the product related details.

1

InvProductCost

Table

Store the historical cost of the product.

1

InvProductGroup

Table

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

1

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PmtExchangeRate

Table

Stores the Exchange Rate values for the currencies defined in application

1

RtlSubsidiary

Table

Defines all the Subsidiaries defined in the application

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE VIEW [dbo].[vw_InventoryCost]

AS

SELECT CASE WHEN T1.SourceType = 5 THEN 'Goods Receipt Reconciliation' WHEN T1.SourceType = 2 THEN 'Goods Receipt ST' ELSE 'Goods Receipt' END AS 'DocumentType',

 T1.ToWarehouseKey AS WarehouseKey, T5.Id AS WarehouseId, T5.Description AS WarehouseName, T3.ProductKey, T3.Id, T3.Description, T4.ProductGroupKey,

 T4.Id as ProductGroupId, T2.QuantityReceived, T1.Created As BusinessDate, T2.UOMQuantityReceived * T2.Price 'TotalPostSaleDiscount',

ISNULL(T3.CostingMethod,0) AS CostingMethod, Sub.SubsidiaryKey, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, ISNULL(ER.SellRate,1) AS ExchangeRate,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, T2.QuantityReceived * IL.ItemCost As Rate

FROM

 InvGoodReceipt T1 WITH (NOLOCK)

INNER JOIN InvGoodReceiptDetail T2 WITH (NOLOCK) ON T1.GoodsReceiptKey=T2.GoodsReceiptKey

INNER JOIN InvProduct T3 WITH (NOLOCK) ON T2.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T4 WITH (NOLOCK) ON T3.ProductGroupKey=T4.ProductGroupKey

INNER JOIN InvWarehouse T5 WITH (NOLOCK) ON T5.WarehouseKey = T1.ToWarehouseKey

INNER JOIN InvInventoryItemLog IL WITH (NOLOCK) ON T3.ProductKey = IL.ProductKey AND T5.WarehouseKey = IL.WarehouseKey AND T2.GoodsReceiptKey=IL.SourceKey AND T2.GoodsReceiptDetailKey=IL.SourceDetailKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON T5.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER WITH (NOLOCK) ON Sub.LocalCurrencyKey= Er.CurrencyKey AND CONVERT(Nvarchar(8),T1.Created,112) = CONVERT(NVARCHAR(8), Er.EffectiveDate,112)

WHERE T3.IsGiftCertificate = 0

UNION ALL

SELECT 'Goods Return' AS 'DocumentType', T1.WarehouseKey AS WarehouseKey, T5.Id AS WarehouseId, T5.Description AS WarehouseName, T3.ProductKey, T3.Id,  T3.Description,

 T4.ProductGroupKey, T4.Id as ProductGroupId, -1 * T2.Quantity, T1.Created, -1 * T2.UOMQuantity * T2.Price 'TotalPostSaleDiscount', ISNULL(T3.CostingMethod,0) AS CostingMethod,

 Sub.SubsidiaryKey, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, ISNULL(ER.SellRate,1) AS ExchangeRate,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, -1 * T2.Quantity * IL.ItemCost As Rate

FROM

 InvGoodsReturn T1 WITH (NOLOCK)

INNER JOIN InvGoodsReturnDetail T2 WITH (NOLOCK) ON T1.GoodsReturnKey=T2.GoodsReturnKey

INNER JOIN InvProduct T3 WITH (NOLOCK) ON T2.ProductKey=T3.ProductKey

INNER JOIN InvProductGroup T4 WITH (NOLOCK) ON T3.ProductGroupKey=T4.ProductGroupKey

INNER JOIN InvWarehouse T5 WITH (NOLOCK) ON T5.WarehouseKey = T1.WarehouseKey

INNER JOIN InvInventoryItemLog IL WITH (NOLOCK) ON T3.ProductKey = IL.ProductKey AND T5.WarehouseKey = IL.WarehouseKey AND T2.GoodsReturnKey=IL.SourceKey AND T2.GoodsReturnDetailKey=IL.SourceDetailKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON T5.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER WITH (NOLOCK) ON Sub.LocalCurrencyKey= Er.CurrencyKey AND CONVERT(Nvarchar(8),T1.Created,112) = CONVERT(NVARCHAR(8), Er.EffectiveDate,112)

WHERE T3.IsGiftCertificate = 0

UNION ALL

SELECT 'Stock Transfer', T1.FromWarehouseKey AS WarehouseKey, T5.Id AS WarehouseId, T5.Description AS WarehouseName, T3.ProductKey, T3.Id, T3.Description,

 T4.ProductGroupKey, T4.Id as ProductGroup, -1 * T2.QuantityShipped, T1.Created, -1 * T2.UOMQuantity * T2.Price 'TotalPostSaleDiscount',

ISNULL(T3.CostingMethod,0) AS CostingMethod, Sub.SubsidiaryKey, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN 1 ELSE ISNULL(ER.SellRate,1) END)AS ExchangeRate,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, T2.UOMQuantityShipped * IL.ItemCost AS Rate

From

 InvStockTransfer T1 WITH (NOLOCK)

INNER JOIN InvStockTransferDetail T2 WITH (NOLOCK) ON T1.StockTransferKey=T2.StockTransferKey

INNER JOIN InvProduct T3 WITH (NOLOCK) ON T2.ProductKey = T3.ProductKey

INNER JOIN InvProductGroup T4 WITH (NOLOCK) ON T3.ProductGroupKey=T4.ProductGroupKey

INNER JOIN InvWarehouse T5 WITH (NOLOCK) ON T5.WarehouseKey = T1.FromWarehouseKey

INNER JOIN InvInventoryItemLog IL WITH (NOLOCK) ON T3.ProductKey = IL.ProductKey AND T5.WarehouseKey = IL.WarehouseKey AND T2.StockTransferKey=IL.SourceKey AND T2.StockTransferDetailKey=IL.SourceDetailKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON T5.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER WITH (NOLOCK) ON Sub.LocalCurrencyKey= Er.CurrencyKey AND CONVERT(Nvarchar(8),T1.Created,112) = CONVERT(NVARCHAR(8), Er.EffectiveDate,112)

UNION ALL

SELECT CASE WHEN T1.SourceType = 1 THEN 'Goods Issue Reconciliation' ELSE 'Goods Issue' END, T1.WarehouseKey, T5.Id AS WarehouseId, T5.Description AS WarehouseName,

 T3.ProductKey, T3.Id, T3.Description, T4.ProductGroupKey, T4.Id as ProductGroup, T2.Quantity * -1, T1.Created, 0 'TotalPostSaleDiscount', ISNULL(T3.CostingMethod,0) AS CostingMethod,

 Sub.SubsidiaryKey, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN 1 ELSE ISNULL(ER.SellRate,1) END)AS ExchangeRate,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, -1* T2.Quantity * IL.ItemCost AS Rate

From

 InvInventoryTransaction T1 WITH (NOLOCK)

INNER JOIN InvInventoryTransactionDetail T2 WITH (NOLOCK) ON T1.InventoryTransactionKey = T2.InventoryTransactionKey

INNER JOIN InvProduct T3 WITH (NOLOCK) ON T2.ProductKey = T3.ProductKey

INNER JOIN InvProductGroup T4 WITH (NOLOCK) ON T3.ProductGroupKey=T4.ProductGroupKey

INNER JOIN InvWarehouse T5 WITH (NOLOCK) ON T5.WarehouseKey = T1.WarehouseKey

INNER JOIN InvInventoryItemLog IL WITH (NOLOCK) ON T3.ProductKey = IL.ProductKey AND T5.WarehouseKey = IL.WarehouseKey AND T2.InventoryTransactionKey=IL.SourceKey AND T2.InvInventoryTransactionDetailKey=IL.SourceDetailKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON T5.SubsidiaryKey = Sub.SubsidiaryKey

LEFT OUTER JOIN PmtExchangeRate ER WITH (NOLOCK) ON Sub.LocalCurrencyKey= Er.CurrencyKey AND CONVERT(Nvarchar(8),T1.Created,112) = CONVERT(NVARCHAR(8), Er.EffectiveDate,112)

UNION ALL

SELECT 'Sales', T2.WarehouseKey, T5.Id AS WarehouseId, T5.Description AS WarehouseName, T3.ProductKey, T3.Id, T3.Description, T4.ProductGroupKey,

 T4.Id as ProductGroup, CASE WHEN T2.Type = 1 THEN 1* T2.Quantity ELSE -1*T2.Quantity END, T1.Created, (T2.TotalPostSaleDiscount - T2.Tax) * (CASE WHEN T2.Type = 1 THEN -1 ELSE 1 END),

ISNULL(T3.CostingMethod,0) AS CostingMethod, Sub.SubsidiaryKey, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, ISNULL(T1.ExchangeRate,1) AS ExchangeRate,

 (CASE WHEN (T5.SubsidiaryKey='0' OR T5.SubsidiaryKey IS NULL OR ISNULL(CONVERT(Nvarchar(200), T5.SubsidiaryKey),'')='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END) AS CultureInfo,

 (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, T2.Quantity * CASE WHEN T3.CostingMethod = 2 THEN

ISNULL(( SELECT TOP 1 Cost

    FROM InvProductCost WITH (NOLOCK)

    WHERE ProductKey = T2.ProductKey

    AND WarehouseKey IN (T2.WarehouseKey, '')

--AND ISNULL(CONVERT(Nvarchar(200),InvProductCost.SubsidiaryKey),'') =ISNULL(Convert(Nvarchar(200),Sub.SubsidiaryKey),'')

    AND IsDeleted = 0

    AND CONVERT(VARCHAR,EffectiveDate,112) <= Convert(Varchar(8),T1.BusinessDate,112)

    ORDER BY EffectiveDate DESC

),0) * (CASE WHEN T2.Type = 1 THEN -1 ELSE 1 END)

WHEN T3.CostingMethod = 1 THEN

ISNULL(T2.AverageCost,0) END AS Rate

From

 TrxTransaction T1 WITH (NOLOCK)

INNER JOIN TrxTransactionSaleItem T2 WITH (NOLOCK) ON T1.TransactionKey = T2.TransactionKey

INNER JOIN InvProduct T3 WITH (NOLOCK) ON T2.ProductKey = T3.ProductKey

INNER JOIN InvProductGroup T4 WITH (NOLOCK) ON T3.ProductGroupKey = T4.ProductGroupKey

INNER JOIN InvWarehouse T5 WITH (NOLOCK) ON T2.WarehouseKey = T5.WarehouseKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON T5.SubsidiaryKey = Sub.SubsidiaryKey

WHERE T2.Type !=4 AND T1.IsSuspended = 0 AND T1.IsVoided = 0