iVend Database Database : vw_GrossProfit View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

TransactionKey

BigInt

8

TransactionId

VarWChar

50

BusinessDate

DBTimeStamp

4

ProductKey

BigInt

8

Id

VarWChar

50

ProductName

VarWChar

100

ProductGroupId

VarWChar

20

ProductGroupDescription

VarWChar

100

WarehouseKey

BigInt

8

WarehouseID

VarWChar

20

WarehouseName

VarWChar

100

Type

Integer

4

StoreId

VarWChar

20

StoreName

VarWChar

100

CustomerId

VarWChar

20

CustomerName

VarWChar

100

LastName

VarWChar

40

CustomerGroupId

VarWChar

20

CustomerGroupName

VarWChar

100

SubsidiaryKey

BigInt

8

Subsidiary

VarWChar

20

SubsidiaryDesc

VarWChar

100

Quantity

Numeric

9 (20,5)

Price

Numeric

9 (20,5)

PriceEC

Numeric

9 (38,7)

BasePrice

Numeric

9 (20,5)

OriginalPrice

Numeric

9 (20,5)

Tax

Numeric

9 (20,5)

TaxEC

Numeric

9 (38,7)

TotalPostSaleDiscount

Numeric

9 (20,5)

TotalPostSaleDiscountEC

Numeric

9 (38,7)

CultureInfo

VarWChar

40

BaseCultureInfo

VarWChar

20

ExchangeRate

Numeric

9 (20,5)

CostingMethod

Integer

4

Rate

Numeric

9 (20,5)

Database Object

Object Type

Description

Dep Level

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

CusCustomerGroup

Table

Defines the groups in which the customers can be catagorised.

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

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

RtlStore

Table

Contains the details of Stores defined in the system.

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

as

SELECT A.TransactionKey, A.TransactionId, A.BusinessDate, B.ProductKey, C.Id, C.Description AS ProductName, H.Id AS ProductGroupId, H.Description AS ProductGroupDescription,

G.WarehouseKey, G.Id AS WarehouseID, G.Description AS WarehouseName, B.Type, D.Id AS StoreId, D.Description AS StoreName, E.Id As CustomerId, E.FirstName AS CustomerName,

E.LastName, F.Id AS CustomerGroupId, F.Description AS CustomerGroupName, Sub.SubsidiaryKey, Sub.Id Subsidiary, Sub.Description SubsidiaryDesc, B.Quantity , B.Price, B.PriceEC,

B.BasePrice, B.OriginalPrice, B.Tax, B.TaxEC, B.TotalPostSaleDiscount, B.TotalPostSaleDiscountEC,

(CASE WHEN (G.SubsidiaryKey IS NULL OR G.SubsidiaryKey=0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE SUB.CultureInfo END) AS CultureInfo,

(Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, A.ExchangeRate,

ISNULL(C.CostingMethod,0) AS CostingMethod,

CASE WHEN C.CostingMethod = 2 THEN

ISNULL((

    SELECT TOP 1 Cost FROM InvProductCost

    WHERE InvProductCost.ProductKey = B.ProductKey

    AND InvProductCost.WarehouseKey IN (G.WarehouseKey,0)

AND ISNULL(InvProductCost.SubsidiaryKey,0) = ISNULL(Sub.SubsidiaryKey,0)

    AND IsDeleted = 0

    AND Convert(VARCHAR,EffectiveDate,112) <= A.BusinessDate

    ORDER BY EffectiveDate DESC

),0)

WHEN C.CostingMethod = 1 THEN

ISNULL((

   B.AverageCost

),0) ELSE 0 END AS Rate

FROM TrxTransaction A

INNER JOIN TrxTransactionSaleItem B ON A.TransactionKey = B.TransactionKey

INNER JOIN InvProduct C ON B.ProductKey = C.ProductKey

INNER JOIN InvProductGroup H ON C.ProductGroupKey = H.ProductGroupKey

INNER JOIN RtlStore D ON A.StoreKey = D.StoreKey

INNER JOIN CusCustomer E ON A.CustomerKey = E.CustomerKey

INNER JOIN CusCustomerGroup F ON E.CustomerGroupKey = F.CustomerGroupKey

INNER JOIN InvWarehouse G ON D.WarehouseKey = G.WarehouseKey

LEFT OUTER JOIN RtlSubsidiary Sub ON G.SubsidiaryKey = Sub.SubsidiaryKey

WHERE

A.IsSuspended = 0 AND A.IsVoided = 0 AND  B.TYPE != 4 --THIS IS DELIVERY AGAINST SALE

AND B.Quantity > 0