iVend Database Database > Views > vwAnalytics

iVend 6.6 Database Help

vwAnalytics_Sales View

Collapse All Expand All

iVend Database Database : vwAnalytics_Sales View

Properties

Columns

Objects that vwAnalytics_Sales depends on

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
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
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
LbrUser	Table	Defines a list of all employees/ labor users created in the system	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
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
TrxTransactionSalesPerson	Table	Defines a list of all the users to whom a certain commission has been given as a part of a transaction.	1

View Definition

CREATE VIEW [dbo].[vwAnalytics_Sales]

SELECT TSI.Description AS Product, CASE WHEN TSI.Type <> 1 THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Sales Amount],

CASE WHEN TSI.Type = 1 THEN TSI.TotalPostSaleDiscount ELSE 0 END AS [Refund Amount],

CASE WHEN TSI.Type = 1 THEN TSI.TotalPostSaleDiscount * - 1 ELSE TSI.TotalPostSaleDiscount END AS [Net Sales],

CASE WHEN TSI.Type = 1 THEN TSI.Quantity * - 1 ELSE TSI.Quantity END AS [Net Quantity],

CASE WHEN TSI.Type <> 1 THEN TSI.Quantity ELSE 0 END AS [Sales Quantity], CASE WHEN TSI.Type = 3 THEN 0 ELSE TSI.Type END AS [Transaction Type],

CASE WHEN TSI.Type IN (0, 3, 5) THEN 'Sale' ELSE 'Refund' END AS [Line Item Flag],

convert(datetime,convert(nvarchar(10),T.BusinessDate, 101), 101) BusinessDate,

T.TransactionId, S.Description as Store, Pos.Description as POS, P.Id as POSId,

U.FirstName as 'User', C.FirstName as Customer,

PG.Description ProductGroup

, 'Others' as ProductCategory

, CG.Description as CustomerGroup, ISNULL(SP.FirstName, '') as SalesPerson,

CASE WHEN TSI.Type = 1 THEN TSI.TotalPostSaleDiscount * - 1 ELSE TSI.TotalPostSaleDiscount END * ISNULL(TSP.Percentage, 0)/100 as SalesPersonTotal

FROM dbo.TrxTransactionSaleItem AS TSI

INNER JOIN dbo.TrxTransaction AS T ON TSI.TransactionKey = T.TransactionKey

INNER JOIN dbo.InvWarehouse AS W ON TSI.WarehouseKey = W.WarehouseKey

INNER JOIN dbo.RtlStore AS S ON T.StoreKey = S.StoreKey

INNER JOIN dbo.RtlPOS AS Pos ON T.POSKey = Pos.POSKey

INNER JOIN dbo.LbrUser AS U ON T.UserKey = U.UserKey

INNER JOIN dbo.CusCustomer AS C ON T.CustomerKey = C.CustomerKey

INNER JOIN dbo.InvProduct P ON TSI.ProductKey = p.ProductKey

INNER JOIN dbo.InvProductGroup PG ON P.ProductGroupKey = PG.ProductGroupKey

INNER JOIN dbo.CusCustomerGroup CG ON C.CustomerGroupKey = CG.CustomerGroupKey

LEFT OUTER JOIN dbo.TrxTransactionSalesPerson TSP ON TSI.TransactionItemKey = TSP.SourceKey

AND TSP.Type = 1

LEFT OUTER JOIN dbo.LbrUser SP ON TSP.UserKey = SP.UserKey

INNER JOIN (SELECT TOP 1 SiteId from CfgSiteInformation) As T5 on 1=1

LEFT OUTER JOIN RtlStore T6 ON T5.SiteId = t6.SiteId

WHERE T.IsSuspended = 0

AND T.IsVoided = 0

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

AND TSI.Type IN (0, 1, 3, 5)

Send comments on this topic.