iVend Database Database : vw_SalesReportData View

Creation Date

7/20/2019 12:11 AM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

SaleType

Integer

4

TransactionType

VarChar

14

TransactionId

VarWChar

50

TransactionKey

VarWChar

50

TransactionDate

VarChar

30

TransactionTime

VarChar

5

StoreId

VarWChar

20

StoreDesc

VarWChar

100

POSId

VarWChar

20

POSDesc

VarWChar

100

TillId

VarWChar

50

TillDesc

VarWChar

100

TilTillMaster

VarWChar

20

ItemGroup

VarWChar

20

ItemGroupDesc

VarWChar

100

Item

VarWChar

50

ItemDescription

VarWChar

100

CustGroup

VarWChar

20

CustGroupDesc

VarWChar

100

Cust

VarWChar

20

CustomerName

VarWChar

192

Quantity

Numeric

9 (22,5)

UOMQuantity

Numeric

9 (38,16)

UOM

VarWChar

100

Price

Numeric

9 (20,5)

Discount

Numeric

9 (27,5)

Surcharge

Integer

4

Tax

Numeric

9 (22,5)

TotalAmount

Numeric

9 (22,5)

SalesPersonId

VarWChar

50

SalesPersonName

VarWChar

142

Created

DBTimeStamp

4

BusinessDate

DBTimeStamp

4

CultureInfo

VarWChar

40

SellRate

Numeric

9 (20,5)

Subsidiary

VarWChar

20

SubsidiaryDescription

VarWChar

100

PriceEC

Numeric

9 (38,7)

DiscountEC

Numeric

9 (38,6)

SurchargeEC

Numeric

9 (22,5)

TaxEC

Numeric

9 (38,6)

TotalAmountEC

Numeric

9 (38,6)

BaseCultureInfo

VarWChar

20

Vendor

VarWChar

20

VendorName

VarWChar

100

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

InvInventoryItem

Table

Stores inventory details for each Product for each Warehouse

1

InvProduct

Table

Stores the product related details.

1

InvProductGroup

Table

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

1

InvUOM

Table

Master table for the Inv UOM

1

InvUOMGroupDetail

Table

Stores the details for the UOM group

1

LbrUser

Table

Defines a list of all employees/ labor users created in the system

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PurVendor

Table

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

1

RtlPOS

Table

Contains details about all POS 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

TilTill

Table

Till is defined at the MC and attached with the cash drawer at POS. At a time only one till can be associated with the POS. It is the virtual entity in which we defined some settings (like setting the mode in which the till works) that will be applicable at the POS (for which the till is attached).

1

TilTillMaster

Table

Stores the master till information

1

TrxTransaction

Table

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

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE View [dbo].[vw_SalesReportData]

AS

Select CASE TransactionType WHEN 'Sale' THEN 1 WHEN 'Refund' THEN 2 WHEN 'Layaway' THEN 3 WHEN 'Layaway Cancel' THEN 4 END AS SaleType,

TransactionType, TransactionId, TransactionKey, TransactionDate, TransactionTime, StoreId, StoreDesc, POSId, POSDesc, TillId, TillDesc,

TilTillMaster, ItemGroup, ItemGroupDesc, Item, ItemDescription, CustGroup, CustGroupDesc, Cust, CustomerName, Quantity, UOMQuantity,

UOM, Price, Discount, Surcharge, Tax, TotalAmount, SalesPersonId, SalesPersonName, Created, BusinessDate, CultureInfo, SellRate,

Subsidiary, SubsidiaryDescription, PriceEC, DiscountEC, SurchargeEC, TaxEC, TotalAmountEC, BaseCultureInfo, Vendor, VendorName

From (

SELECT

CASE WHEN (TrxnSale.Type = 0 Or TrxnSale.Type = 3) THEN 'Sale' WHEN TrxnSale.Type=1 THEN 'Refund' END AS TransactionType

, Trxn.TransactionId AS TransactionId, Trxn.TransactionKey AS TransactionKey, TrxnSale.OriginalDocumentKey, TrxnSale.OriginalDetailKey

, CONVERT(VARCHAR, Trxn.Created, 107) AS TransactionDate

, CONVERT(VARCHAR(5), Trxn.Created, 108) AS TransactionTime, RtlStore.Id AS StoreId, RtlStore.Description AS StoreDesc, RtlPOS.Id AS POSId

, RtlPOS.Description AS POSDesc, TilTill.Id AS TillId, TilTill.Description AS TillDesc, TilTillMaster.Id AS TilTillMaster, ProdGrp.Id AS ItemGroup, ProdGrp.Description AS ItemGroupDesc

, Prod.Id AS Item, TrxnSale.Description AS ItemDescription, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc

, Cust.Id AS Cust, ISNULL(Cust.FirstName,'')+' '+ISNULL(Cust.MiddleName,'')+' '+ISNULL(Cust.LastName,'') AS CustomerName

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.Quantity ELSE TrxnSale.Quantity END AS Quantity

, CASE WHEN TrxnSale.Type=1 THEN -1*(TrxnSale.Quantity/ISNULL(TrxnSale.UOMBaseQuantity,1)) ELSE (TrxnSale.Quantity/ISNULL(TrxnSale.UOMBaseQuantity,1)) END AS UOMQuantity

, ISNULL(UOM.Description,'') AS UOM, TrxnSale.Price AS Price

, CASE WHEN TrxnSale.Type=1 THEN -1*(ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)+ISNULL(TrxnSale.TaxableDiscountAmount,0)) ELSE (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)+ISNULL(TrxnSale.TaxableDiscountAmount,0)) END AS Discount

, -1 AS Surcharge, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.Tax ELSE TrxnSale.Tax END AS Tax

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TotalPostSaleDiscount ELSE TrxnSale.TotalPostSaleDiscount END AS TotalAmount

, ISNULL(LbrUser.Id,'') AS SalesPersonId, ISNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName

, Trxn.Created AS Created, Trxn.BusinessDate AS BusinessDate

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

, Trxn.ExchangeRate AS SellRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, TrxnSale.PriceEC AS PriceEC

, CASE WHEN TrxnSale.Type=1 THEN -1*(ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)END AS DiscountEC

, -1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TaxEC ELSE Trxn.TaxEC END AS TaxEC

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TotalPostSaleDiscountEC ELSE TrxnSale.TotalPostSaleDiscountEC END AS TotalAmountEC

, (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, ISNULL(V.Id,'No Vendor') AS Vendor, ISNULL(V.CompanyName,'No Vendor') AS VendorName

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionSaleItem AS TrxnSale WITH (NOLOCK) ON Trxn.TransactionKey = TrxnSale.TransactionKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnSale.ProductKey = Prod.ProductKey

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

LEFT OUTER JOIN TilTill WITH (NOLOCK)ON Trxn.TillKey = TilTill.TillKey

LEFT OUTER JOIN TilTillMaster WITH (NOLOCK)ON TilTill.MasterTillKey= TilTillMaster.MasterTillKey

LEFT JOIN InvUOMGroupDetail  UOMD WITH (NOLOCK) on TrxnSale.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

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

INNER JOIN InvInventoryItem IV WITH (NOLOCK) ON TrxnSale.InventoryItemKey=IV.InventoryItemKey

LEFT OUTER JOIN PurVendor V WITH (NOLOCK) ON V.VendorKey = (CASE WHEN (RtlStore.SubsidiaryKey =0 OR RtlStore.SubsidiaryKey IS NULL) THEN Prod.PreferedVendorKey ELSE IV.PreferedVendorKey END)

WHERE Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (TrxnSale.Type = 0 Or TrxnSale.Type = 3 OR TrxnSale.Type = 1) --0 - Sale, 3-Delivery Against Special Order

UNION ALL

SELECT

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status<>2) THEN 'Layaway' WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN 'Layaway Cancel' END AS TransactionType

, Trxn.TransactionId AS TransactionId, Trxn.TransactionKey AS TransactionKey, LayAway.OriginalDocumentKey, LayAway.OriginalDetailKey

, CONVERT(VARCHAR, Trxn.Created, 107) AS TransactionDate, CONVERT(VARCHAR(5), Trxn.Created, 108) AS TransactionTime, RtlStore.Id AS StoreId

, RtlStore.Description AS StoreDesc, RtlPOS.Id AS POSId, RtlPOS.Description AS POSDesc, TilTill.Id AS TillId, TilTill.Description AS TillDesc

, TilTillMaster.Id AS TilTillMaster, ProdGrp.Id AS ItemGroup, ProdGrp.Description AS ItemGroupDesc, Prod.Id AS Item, LayAway.Description AS ItemDescription

, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc, Cust.Id AS Cust, ISNULL(Cust.FirstName,'')+' '+ISNULL(Cust.MiddleName,'')+' '+ISNULL(Cust.LastName,'') AS CustomerName

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Quantity ELSE LayAway.Quantity END AS Quantity

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(LayAway.Quantity / NULLIF(ISNULL(Layaway.UomBaseQuantity,1),0)) ELSE (LayAway.Quantity / ISNULL(Layaway.UomBaseQuantity,0)) END AS UOMQuantity

, ISNULL(UOM.Description,'') AS UOM, LayAway.Price AS Price

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0)) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0)) END AS Discount

, -1 AS Surcharge, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Tax ELSE LayAway.Tax END AS Tax

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscount ELSE LayAway.TotalPostSaleDiscount END AS TotalAmount

, ISNULL(LbrUser.Id,'') AS SalesPersonId, isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName

, Trxn.Created AS Created, Trxn.BusinessDate AS BusinessDate

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

, Trxn.ExchangeRate AS ExchangeRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, LayAway.PriceEC AS PriceEC

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) END AS DiscountEC

, -1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TaxEC ELSE LayAway.TaxEC END AS TaxEC

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscountEC ELSE LayAway.TotalPostSaleDiscountEC END AS TotalAmountEC

, (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) AS BaseCultureInfo, ISNULL(V.Id,'No Vendor') AS Vendor, ISNULL(V.CompanyName,'No Vendor') AS VendorName

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionLayAway AS LayAway WITH (NOLOCK) ON Trxn.TransactionKey = LayAway.TransactionKey

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON LayAway.ProductKey = Prod.ProductKey

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

LEFT OUTER JOIN TilTillMaster WITH (NOLOCK)ON TilTill.MasterTillKey= TilTillMaster.MasterTillKey

LEFT OUTER JOIN InvUOMGroupDetail  UOMD WITH (NOLOCK) on LayAway.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

LEFT OUTER JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

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

INNER JOIN InvInventoryItem IV WITH (NOLOCK) ON LayAway.InventoryItemKey=IV.InventoryItemKey

LEFT OUTER JOIN PurVendor V WITH (NOLOCK) ON V.VendorKey = (CASE WHEN (RtlStore.SubsidiaryKey =0 OR RtlStore.SubsidiaryKey IS NULL) THEN Prod.PreferedVendorKey ELSE IV.PreferedVendorKey END)

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0

) AS SalesData

--Order BY Item