iVend Database Database : vwAnalytics_OpenStockTransferReceipt View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

RequestNumber

VarWChar

20

FromWarehouse

VarWChar

100

ToWarehouse

VarWChar

100

ProductCode

VarWChar

20

Product

VarWChar

100

UOMQuantity

Numeric

9 (20,5)

UOMQuantityOpen

Numeric

9 (20,5)

UOMQuantityReceived

Numeric

9 (20,5)

UOMQuantityShipped

Numeric

9 (20,5)

ReceiptDate

DBTimeStamp

4

UOM

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

InvProduct

Table

Stores the product related details.

1

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvUOM

Table

Master table for the Inv UOM

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

as

Select InvStockTransfer.RequestNumber,

FromWarehouse.Description as FromWarehouse,

ToWarehouse.Description as ToWarehouse,

   InvProduct.Id As ProductCode,

   InvStockTransferDetail.Description As Product,

   InvStockTransferDetail.UOMQuantity,

   InvStockTransferDetail.UOMQuantityOpen,

   InvStockTransferDetail.UOMQuantityReceived,

   InvStockTransferDetail.UOMQuantityShipped,

   InvStockTransfer.RequestedDate as ReceiptDate,

   InvUOM.Description as UOM

From InvStockTransfer

Inner Join InvStockTransferDetail On InvStockTransfer.StockTransferKey = InvStockTransferDetail.StockTransferKey

Inner Join InvProduct On InvStockTransferDetail.ProductKey = InvProduct.ProductKey

INNER JOIN InvWarehouse FromWarehouse ON InvStockTransfer.FromWarehouseKey = FromWarehouse.WarehouseKey

INNER JOIN InvWarehouse ToWarehouse ON InvStockTransfer.ToWarehouseKey = ToWarehouse.WarehouseKey

Left Outer JOIN InvUOM on InvStockTransferDetail.UOMKey=InvUOM.UOMKey

Inner JOin RtlStore As Store On FromWarehouse.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  InvStockTransfer.Status = 0

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