iVend Database Database : vwAnalytics_SalesPerson View

Creation Date

4/13/2015 12:00 PM

Is Schema Bound

Encrypted

Ansi Nulls

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

SalesPerson

VarWChar

50

SalesPersonTotal

Numeric

9 (38,6)

POS

VarWChar

100

BusinessDate

DBTimeStamp

4

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

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

CREATE VIEW [dbo].[vwAnalytics_SalesPerson]

as

SELECT SP.FirstName as SalesPerson,

    CASE WHEN TSI.Type = 1 THEN TSI.TotalPostSaleDiscount * - 1 ELSE TSI.TotalPostSaleDiscount END * TSP.Percentage/100 as SalesPersonTotal, Pos.Description as POS

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

FROM         dbo.TrxTransactionSaleItem AS TSI

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

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

INNER JOIN dbo.TrxTransactionSalesPerson TSP ON TSI.TransactionItemKey = TSP.SourceKey

INNER 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 TSP.Type = 1

AND T.IsVoided = 0

AND T.IsSuspended = 0

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

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