iVend Database Database : vwAnalytics_OpenPO_Snapshot View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

StoreName

VarWChar

100

StoreCode

VarWChar

20

VendorName

VarWChar

100

OrderDate

DBTimeStamp

4

DeliveryDate

DBTimeStamp

4

PurchaseOrderId

VarWChar

20

Total

Numeric

9 (20,5)

ItemTotal

Numeric

9 (20,5)

OrderQty

Numeric

9 (20,4)

QuantityReceived

Numeric

9 (20,4)

ProductName

VarWChar

100

ProductCode

VarWChar

20

UOMQuantity

Numeric

9 (20,5)

UOMQuantityReceived

Numeric

9 (20,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

InvUOM

Table

Master table for the Inv UOM

1

PurPurchaseOrder

Table

Stores information related to Purchase order transactions done from Console or done from ERP on behalf of this store.

1

PurPurchaseOrderDetail

Table

Stores the line details of the purchase order.

1

PurVendor

Table

Defines the vendor details of an enterprise from which the products are purchased.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

CREATE VIEW [dbo].[vwAnalytics_OpenPO_Snapshot]

as

SELECT RtlStore.Description as StoreName, RtlStore.Id as StoreCode, PurPurchaseOrder.VendorName, PurPurchaseOrder.DeliveryDate as OrderDate,

 PurPurchaseOrder.DeliveryDate, PurPurchaseOrder.PurchaseOrderId, PurPurchaseOrder.Total,

 PurPurchaseOrderDetail.Total as ItemTotal, PurPurchaseOrderDetail.Quantity as OrderQty, PurPurchaseOrderDetail.QuantityReceived,

 InvProduct.Description as ProductName, InvProduct.Id as ProductCode , PurPurchaseOrderDetail.UOMQuantity,  PurPurchaseOrderDetail.UOMQuantityReceived,

 InvUOM.Description as UOM

FROM PurPurchaseOrder

INNER JOIN PurPurchaseOrderDetail ON PurPurchaseOrder.PurchaseOrderKey = PurPurchaseOrderDetail.PurchaseOrderKey

INNER JOIN RtlStore ON PurPurchaseOrder.SiteId = RtlStore.SiteId

AND RtlStore.StoreKey IN (

SELECT StoreKey From RtlStore S

WHERE S.SiteId = CASE WHEN (SELECT TOP 1 SiteId from CfgSiteInformation) <= 1 THEN S.SiteId ELSE (SELECT TOP 1 SiteId from CfgSiteInformation) END

)

INNER JOIN PurVendor ON PurPurchaseOrder.VendorKey = PurVendor.VendorKey

INNER JOIN InvProduct ON PurPurchaseOrderDetail.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN InvUOM ON PurPurchaseOrderDetail.UOMKey = InvUOM.UOMKey

WHERE PurPurchaseOrderDetail.Status = 0