iVend Database Database : SalesDetail User Defined Function

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@POSId

In

VarWChar

20

@CustomerId

In

VarWChar

20

@ProductId

In

VarWChar

20

@StoreId

In

VarWChar

20

@ProductGroupId

In

VarWChar

20

@FromDate

In

DBTimeStamp

4

@ToDate

In

DBTimeStamp

4

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

SalesAnalysisbySalesPersons

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvProduct

Table

Stores the product related details.

1

InvProductGroup

Table

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

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

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

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

-- Author:    Ritu,Riyanka,Sachin

-- Create date:   7 Sep 2007

-- Last Modified On: 13 Sep 2007

-- Description: Common function to get Retail Sales

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

CREATE FUNCTION [dbo].[SalesDetail]

(

@POSId nvarchar(20),

@CustomerId nvarchar(20),

@ProductId nvarchar(20),

@StoreId nvarchar(20),

@ProductGroupId nvarchar(20),

@FromDate DateTime,

@ToDate DateTime

)

RETURNS

@SalesDetail TABLE

(

-- Add the column definitions for the TABLE variable here

StoreKey bigint,

StoreID nvarchar(20),

StoreDescription nvarchar(100),

POSKey bigint,

POSId nvarchar(20),

POSDescription nvarchar(100),

SalesPersonKey bigint,

TransactionKey bigint,

TransactionId nvarchar(20),

ActualDate DateTime,

UserKey bigint,

TranType nvarchar(10),

ProductID nvarchar(20),

ProductKey bigint,

ProductDescription nvarchar(200),

ProductAccountingID nvarchar(50),

ProductGroupID nvarchar(20),

ProductGroupDescription nvarchar(100),

Price decimal(20,5),

Quantity decimal(20,4),

DiscountType int,

DiscountAmount decimal(20,5),

DiscountPercent decimal(20,5),

SubTotal decimal(20,5),

Total decimal(20,5),

Tax decimal(20,5),

CustomerKey bigint,

CustomerId nvarchar(20),

FirstName nvarchar(100),

MiddleName nvarchar(50),

LastName nvarchar(40),

CustomerAccountingID nvarchar(50),

AddressKey bigint,

Email nvarchar(100),

PhoneNumber nvarchar(50),

MobilePhone nvarchar(50),

FaxNumber nvarchar(50),

BaseCurrencyId nvarchar(20)

)

AS

BEGIN

Select @FromDate=isnull(@FromDate,'1/1/1900'),

@ToDate=isnull(@ToDate,getDate())

DECLARE @BaseCurrencyId nvarchar(20)

SELECT @BaseCurrencyId = Id FROM PmtCurrency WHERE IsBaseCurrency = 'true' and IsDeleted = 'false'

INSERT @SalesDetail

SELECT

  RtlStore.StoreKey,

  RtlStore.Id AS StoreID,

  RtlStore.Description AS StoreDescription,

  RtlPOS.POSKey,

  RtlPOS.Id AS POSId,

  RtlPOS.Description AS POSDescription,

  TrxTransaction.UserKey AS SalesPersonKey,

  TrxTransaction.TransactionKey,

  TrxTransaction.TransactionId,

  TrxTransaction.ActualDate,

  TrxTransaction.UserKey,

  TEMPSALESTBL.TranType,

  InvProduct.Id AS ProductID,

  InvProduct.ProductKey,

  InvProduct.Description AS ProductDescription,

  InvProduct.AccountingID AS ProductAccountingID,

  InvProductGroup.Id AS ProductGroupID,

  InvProductGroup.Description AS ProductGroupDescription,

  TEMPSALESTBL.Price,

  TEMPSALESTBL.Quantity,

  TEMPSALESTBL.DiscountType,

  TEMPSALESTBL.DiscountAmount,

  TEMPSALESTBL.DiscountPercent,

  TEMPSALESTBL.SubTotal,

  TEMPSALESTBL.Total,

  TEMPSALESTBL.Tax,

  CusCustomer.CustomerKey,

  CusCustomer.Id AS CustomerId,

  CusCustomer.FirstName,

  CusCustomer.MiddleName,

  CusCustomer.LastName,

  CusCustomer.AccountingID AS CustomerAccountingID,

  CusCustomer.AddressKey,

  CusCustomer.Email,

  CusCustomer.PhoneNumber,

  CusCustomer.MobilePhone,

  CusCustomer.FaxNumber,

  @BaseCurrencyId

FROM         (SELECT     TransactionKey, ProductKey, Description, Price, DiscountType, DiscountAmount, DiscountPercent, SubTotal, Total, Tax, Quantity,

                                            'Sales' AS TranType

                      FROM          TrxTransactionSaleItem

                      UNION

                      SELECT     TransactionKey, ProductKey, Description, Price, DiscountType, DiscountAmount, DiscountPercent, SubTotal, Total, Tax, 1 AS Quantity,

                                            'Rental' AS TranType

                      FROM         TrxTransactionRental

                      UNION

                      SELECT     TransactionKey, ProductKey, Description, Price, DiscountType, DiscountAmount, DiscountPercent, SubTotal, Total, Tax, Quantity,

                                            'Layaway' AS TranType

                      FROM         TrxTransactionLayaway) AS TEMPSALESTBL INNER JOIN

                     InvProduct ON TEMPSALESTBL.ProductKey = InvProduct.ProductKey INNER JOIN

                     TrxTransaction INNER JOIN

                     RtlStore ON TrxTransaction.StoreKey = RtlStore.StoreKey ON TEMPSALESTBL.TransactionKey = TrxTransaction.TransactionKey INNER JOIN

                     RtlPOS ON TrxTransaction.POSKey = RtlPOS.POSKey LEFT OUTER JOIN

                     CusCustomer ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey LEFT OUTER JOIN

                     InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey WHERE

 InvProduct.Id=(CASE WHEN isnull(@ProductId,'')='' THEN InvProduct.Id ELSE @ProductId END)

AND RtlStore.Id=(CASE WHEN isnull(@StoreId,'')='' THEN RtlStore.Id ELSE @StoreId END)

AND InvProductGroup.Id=(CASE WHEN isnull(@ProductGroupId,'')='' THEN InvProductGroup.Id ELSE @ProductGroupId END)

AND CAST(FLOOR(CAST(ActualDate AS FLOAT)) AS DATETIME) BETWEEN @FromDate AND @ToDate

AND CusCustomer.Id=(CASE WHEN isnull(@CustomerId,'')='' THEN CusCustomer.Id ELSE @CustomerId END)

AND RtlPOS.Id =(CASE WHEN isnull(@POSId,'')='' THEN RtlPOS.Id ELSE @POSId END)

RETURN

END