iVend Database Database : vwAnalytics_OpenStockTransferRequest View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

Product

VarWChar

100

ProductCode

VarWChar

20

FromWarehouse

VarWChar

100

FromWarehouseCode

VarWChar

20

ToWarehouse

VarWChar

100

ToWarehouseCode

VarWChar

20

RequestNumber

VarWChar

20

BusinessDate

DBTimeStamp

4

RequestedDate

DBTimeStamp

4

Quantity

Numeric

9 (20,5)

UOMQuantity

Numeric

9 (20,5)

BaseUOMQuantity

Numeric

9 (20,5)

UOMQuantityOpen

Numeric

9 (20,5)

QuantityOpen

Numeric

9 (20,5)

UOM

VarWChar

100

UOMKey

BigInt

8

FromWarehouseKey

BigInt

8

ToWarehouseKey

BigInt

8

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

InvStockTransferRequest

Table

Defines the information for the generation of the Stock Transfer request

1

InvStockTransferRequestDetail

Table

Stores the detailed information for a generated Stock Transfer request

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

as

SELECT InvProduct.Description as Product,  InvProduct.Id as ProductCode, FromWarehouse.Description as FromWarehouse, FromWarehouse.Id as FromWarehouseCode,

ToWarehouse.Description as ToWarehouse, ToWarehouse.Id as ToWarehouseCode, InvStockTransferRequest.RequestNumber, InvStockTransferRequest.BusinessDate,

InvStockTransferRequest.RequestedDate, InvStockTransferRequestDetail.Quantity, InvStockTransferRequestDetail.UOMQuantity, InvStockTransferRequestDetail.BaseUOMQuantity, InvStockTransferRequestDetail.UOMQuantityOpen,

InvStockTransferRequestDetail.QuantityOpen, InvUOM.Description as UOM,

InvStockTransferRequestDetail.UOMKey, InvStockTransferRequest.FromWarehouseKey, InvStockTransferRequest.ToWarehouseKey

FROM   InvStockTransferRequest InvStockTransferRequest

INNER JOIN InvStockTransferRequestDetail InvStockTransferRequestDetail ON InvStockTransferRequest.StockTransferRequestKey=InvStockTransferRequestDetail.StockTransferRequestKey

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

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

INNER JOIN InvProduct InvProduct ON InvStockTransferRequestDetail.ProductKey=InvProduct.ProductKey

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

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

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