iVend Database Database : SalesAnalysisbySalesPersons Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@StoreId

In

VarWChar

100

@Salesperson

In

VarWChar

50

@FromDate

In

DBTimeStamp

4

@ToDate

In

DBTimeStamp

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CusCustomer

Table

Defines the customer details that interact with the enterprise.

2

InvProduct

Table

Stores the product related details.

2

InvProductGroup

Table

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

2

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.

2

RtlStore

Table

Contains the details of Stores defined in the system.

2

SalesDetail

User Defined Function

1

TrxTransaction

Table

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

2

TrxTransactionLayaway

Table

Store layway type of transactions.

2

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

2

TrxTransactionSalesPerson

Table

Defines a list of all the users to whom a certain commission has been given as a part of a transaction.

1

-- =============================================

-- Author:  <Riyanka Jain>

-- Create date: <>

-- Description: <Sales Analysis by Sales Persons>

-- =============================================

CREATE PROCEDURE [dbo].[SalesAnalysisbySalesPersons]

@StoreId nvarchar(100),

@Salesperson nvarchar(50),

@FromDate DateTime,

@ToDate DateTime

AS

BEGIN

SELECT     SalesDetail.StoreKey, SalesDetail.StoreDescription AS StoreDescription, SalesDetail.StoreID AS StoreID, SalesDetail.POSKey,

          SalesDetail.ActualDate,SalesDetail.TranType,SalesDetail.TransactionKey,SalesDetail.ProductID AS ProductID,

    SalesDetail.ProductDescription AS ProductDesc, SalesDetail.Quantity, SalesDetail.Price, SalesDetail.DiscountAmount,

          SalesDetail.Total, SalesDetail.ProductGroupID,LbrUser.UserKey, LbrUser.FirstName, LbrUser.LastName,

    TrxTransactionSalesPerson.Percentage AS CommissionPercent,SalesDetail.BaseCurrencyId

FROM      SalesDetail (null,null,null,@StoreId,null,@FromDate,@ToDate) as SalesDetail

  INNER JOIN TrxTransactionSalesPerson

  ON TrxTransactionSalesPerson.TransactionKey = SalesDetail.TransactionKey

  INNER JOIN LbrUser

              ON TrxTransactionSalesPerson.UserKey = LbrUser.UserKey

WHERE     LbrUser.UserKey = (CASE WHEN isnull(@Salesperson,'') = '' THEN LbrUser.UserKey ELSE @Salesperson END)

END

/****** Object:  StoredProcedure [dbo].[uspGetItemPrice]    Script Date: 01/11/2008 17:28:48 ******/

SET ANSI_NULLS ON