iVend Database Database > User Defined Functions > rptSalesTransactionDataWithSalesPerson User Defined Function

Navigation: iVend Database Database > User Defined Functions >

iVend 6.6 Database Help

rptSalesTransactionDataWithSalesPerson User Defined Function

Collapse All Expand All

iVend Database Database : rptSalesTransactionDataWithSalesPerson User Defined Function

Properties

Creation Date	9/17/2019 9:19 AM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@FromDate	In		VarChar	8
@ToDate	In	To Date (Part of Date Range in YYYYMMDD Format)	VarChar	8
@ProductGroup	In	Product Group (Prefixed AND Postfixed with '[^]' character )	VarWChar	4000
@FromProduct	In	From Product (Part of Product Range )	VarWChar	100
@ToProduct	In	To Product (Part of Product Range )	VarWChar	100
@CustomerGroup	In	Customer Group (Prefixed AND Postfixed with '[^]' character)	VarWChar	4000
@FromCustomer	In	From Customer (Part of Customer Range )	VarWChar	40
@ToCustomer	In	To Customer (Part of Customer Range )	VarWChar	40
@Store	In	Prefixed AND Postfixed with '[^]' character	VarWChar	4000
@Subsidiary	In	Prefixed AND Postfixed with '[^]' character	VarWChar	4000
@POS	In	Prefixed AND Postfixed with '[^]' character	VarWChar	4000
@Till	In	Prefixed AND Postfixed with '[^]' character	VarWChar	4000
@SalesPerson	In	Prefixed AND Postfixed with '[^]' character	VarWChar	4000
@IncludeLayAway	In	Specify Whether this is LayAway Report	Boolean	1
@IncludeRefund	In	Specify Whether this is Sales Report (Refund)	Boolean	1
@IncludeSale	In	Specify Whether this is Sales Report	Boolean	1
@IncludeOrder	In	Specify Whether this is Order Report	Boolean	1
@TABLE_RETURN_VALUE	Return Value	Result table returned by table valued function	Empty	0

Objects that rptSalesTransactionDataWithSalesPerson depends on

Database Object	Object Type	Description	Dep Level
CfgAddress	Table	Defines the address details for each Address.	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
fnSplitValues	User Defined Function		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
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
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
TrxTransactionOrder	Table	Stores information about various order booked in the system.	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

Procedure Source Code

--select * from [dbo].[rptSalesTransactionDataWithSalesPerson_07042017]('','','','','','','','','','','','','','',0,0,1,'')

--(@IncludeSale, @IncludeRefund, @IncludeLayAway, @IncludeOrder) following Reports will be generated at iVend :-

-- Case1 (@IncludeSale = 1) THEN Sales Reports are generated at iVend.

-- Case2 (@IncludeRefund = 1) THEN Sales Reports are generated at iVend.(Refund)

-- Case3 (@IncludeLayAway = 1) THEN LayAway Reports are generated at iVend.

-- Case4 (@IncludeOrder = 1) THEN Order Reports are generated at iVend.

--##SUMMARY_END

CREATE Function [dbo].[rptSalesTransactionDataWithSalesPerson]

(

@FromDate VARCHAR(8),

@ToDate VARCHAR(8),

@ProductGroup NVARCHAR(4000),

@FromProduct NVARCHAR(100),

@ToProduct NVARCHAR(100),

@CustomerGroup NVARCHAR(4000),

@FromCustomer NVARCHAR(40),

@ToCustomer NVARCHAR(40),

@Store NVARCHAR(4000),

@Subsidiary NVARCHAR(4000),

@POS NVARCHAR(4000),

@Till NVARCHAR(4000),

@SalesPerson NVARCHAR(4000),

@IncludeLayAway BIT,

@IncludeRefund BIT,

@IncludeSale BIT,

@IncludeOrder BIT

)

Returns @temp Table

(

StoreId NVARCHAR(40)

,StoreDesc NVARCHAR(200)

,POSId NVARCHAR(40)

,POSDesc NVARCHAR(200)

,TillId NVARCHAR(100)

,TillDesc NVARCHAR(200)

,Type NVARCHAR(100)

,TransactionType NVARCHAR(100)

,TransactionId NVARCHAR(100)

,TransactionKey NVARCHAR(200)

,TransactionDate VARCHAR(50)

,TransactionTime VARCHAR(50)

,ItemGroup NVARCHAR(40)

,ItemGroupDesc NVARCHAR(200)

,Item NVARCHAR(100)

,ItemDescription NVARCHAR(200)

,CustGroup NVARCHAR(40)

,CustGroupDesc NVARCHAR(200)

,Cust NVARCHAR(40)

,CustFirstName NVARCHAR(200)

,CustMiddleName NVARCHAR(100)

,CustLastName NVARCHAR(80)

,CustAddress1 NVARCHAR(200)

,CustAddress2 NVARCHAR(200)

,CustAddress3 NVARCHAR(200)

,CustCity NVARCHAR(200)

,CustZip NVARCHAR(60)

,CustPhone NVARCHAR(40)

,Quantity DECIMAL(20, 5)

,UOMQuantity DECIMAL(20, 5)

,UOM VARCHAR(200)

,OpenQuantity DECIMAL(20, 5)

,FullfilledQty DECIMAL(20, 5)

,Price DECIMAL(20, 5)

,Discount DECIMAL(20, 5)

,Surcharge DECIMAL(20, 5)

,Tax DECIMAL(20, 5)

,TotalAmount DECIMAL(20, 5)

,SalesPersonId NVARCHAR(100)

,SalesPersonName NVARCHAR(280)

,Created DATETIME

,OriginalDetailKey NVARCHAR(200)

,Status SMALLINT

,LayAwayPlanKey NVARCHAR(200)

,InstallmentCount SMALLINT

,SalesPersonKey NVARCHAR(200)

,SalesPercentage DECIMAL(20, 5)

,BusinessDate Datetime

,Subsidiary NVARCHAR(40)

,SubsidiaryDescription NVARCHAR(200)

,CurrencyKey NVARCHAR(200)

,CultureInfo NVARCHAR(40)

,ExchangeRate DECIMAL(20, 5)

,PriceEC DECIMAL(38, 7)

,DiscountEC DECIMAL(38, 7)

,SurchargeEC DECIMAL(38, 7)

,TaxEC DECIMAL(38, 7)

,TotalAmountEC DECIMAL(38, 7)

,BaseCultureInfo NVARCHAR(80)

)

Begin

If @IncludeSale = 1

Begin

--Transaction Sale Item

Insert @temp

SELECT

'StoreId' = RtlStore.Id

,'StoreDesc' = RtlStore.Description

,'POSId' = RtlPOS.Id

,'POSDesc' = RtlPOS.Description

,'TillId' = TilTill.Id

,'TillDesc' = TilTill.Description

,'Type' = TrxnSale.Type

,'TransactionType' = 'Sale'

,'TransactionId' = Trxn.TransactionId

,'TransactionKey' = Trxn.TransactionKey

,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

,'ItemGroup' = ProdGrp.Id

,'ItemGroupDesc' = ProdGrp.Description

,'Item' = Prod.Id

,'ItemDescription' = TrxnSale.Description

,'CustGroup' = CustGrp.Id

,'CustGroupDesc' = CustGrp.Description

,'Cust' = Cust.Id

,'CustFirstName' = Cust.FirstName

,'CustMiddleName' = Cust.MiddleName

,'CustLastName' = Cust.LastName

,'CustAddress1' = Addr.Address1

,'CustAddress2' = Addr.Address2

,'CustAddress3' = Addr.Address3

,'CustCity' = Addr.City

,'CustZip' = Addr.ZipCode

,'CustPhone' = Addr.PhoneNumber

,'Quantity' = TrxnSale.Quantity

,'UOMQuantity' = TrxnSale.Quantity/ISNULL(NULLIF(TrxnSale.UOMBaseQuantity,0),1)

,'UOM' = ISNULL(UOM.Description,'')

,'OpenQuantity' = -1

,'FullfilledQty' = -1

,'Price' = TrxnSale.Price

,'Discount' = (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))

,'Surcharge' = -1

,'Tax' = TrxnSale.Tax

,'TotalAmount' = TrxnSale.TotalPostSaleDiscount

,'SalesPersonId' = ISNULL(SalesPersonUser.Id,'')

,'SalesPersonName' = isNull(SalesPersonUser.FirstName, '') + ' ' + isNull(SalesPersonUser.MiddleName, '') + ' ' + isNull(SalesPersonUser.LastName, '')

,'Created' = Trxn.Created

,'OriginalDetailKey'= -1

,'Status' = -1

,'LayAwayPlanKey' = -1

,'InstallmentCount' = -1

,'SalesPersonKey' = SalesPerson.UserKey

,'SalesPercentage' = SalesPerson.Percentage

,'BusinessDate' = Trxn.BusinessDate

,'Subsidiary' = Sub.Id

,'SubsidiaryDescription' = Sub.Description

,'CurrencyKey' = Trxn.CurrencyKey

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

,'ExchangeRate' = Trxn.ExchangeRate

,'PriceEC' = TrxnSale.PriceEC

,'DiscountEC' = (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)

,'SurchargeEC' = -1

,'TaxEC' = TrxnSale.TaxEC

,'TotalAmountEC' = TrxnSale.TotalPostSaleDiscountEC

,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From

TrxTransaction AS Trxn WITH (NOLOCK)

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

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

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

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

LEFT OUTER JOIN CfgAddress AS Addr WITH (NOLOCK) ON Cust.AddressKey = Addr.AddressKey

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 TrxTransactionSalesPerson SalesPerson WITH (NOLOCK) ON TrxnSale.TransactionItemKey = SalesPerson.SourceKey AND SalesPerson.Type = 1

LEFT OUTER JOIN LbrUser SalesPersonUser WITH (NOLOCK) ON SalesPerson.UserKey = SalesPersonUser.UserKey

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

Where

(TrxnSale.Type = 0 Or TrxnSale.Type = 3) AND Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 --Actual Sales Person Filter

AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @FromDate, 112) END

AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @ToDate, 112) END --Date Filter

AND (ProdGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@ProductGroup)) OR ProdGrp.Id IN (CASE WHEN @ProductGroup='' THEN ProdGrp.Id END)) -------------Filter Change to avoid Charindex as like operator

AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

(Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END)) --Product Filter

AND (CustGrp.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@CustomerGroup)) OR CustGrp.Id IN (CASE WHEN @CustomerGroup='' THEN CustGrp.Id END)) -------------Filter Change to avoid Charindex as like operator

AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

(Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END)) --Customer Filter

AND (Isnull(SalesPersonUser.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@SalesPerson)) OR Isnull(SalesPersonUser.Id,'') IN (CASE WHEN @SalesPerson='' THEN Isnull(SalesPersonUser.Id,'') END)) -------------Filter Change to avoid Charindex as like operator

AND (RtlStore.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@Store)) OR RtlStore.Id IN (CASE WHEN @Store='' THEN RtlStore.Id END)) -------------Filter Change to avoid Charindex as like operator

AND (RtlPOS.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@POS)) OR RtlPOS.Id IN (CASE WHEN @POS='' THEN RtlPOS.Id END)) -------------Filter Change to avoid Charindex as like operator

AND (Isnull(TilTill.Id,0) IN (SELECT DataString FROM [dbo].[fnSplitValues](@Till)) OR Isnull(TilTill.Id,0) IN (CASE WHEN @Till='' THEN Isnull(TilTill.Id,'') END)) -------------Filter Change to avoid Charindex as like operator

AND ISNULL(Sub.Id,'')= CASE WHEN @Subsidiary <> '' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END

END

--Transaction Refund Item

If @IncludeRefund = 1

Begin

Insert @temp

SELECT 'StoreId' = RtlStore.Id

,'StoreDesc' = RtlStore.Description

,'POSId' = RtlPOS.Id

,'POSDesc' = RtlPOS.Description

,'TillId' = TilTill.Id

,'TillDesc' = TilTill.Description

,'Type' = TrxnSale.Type

,'TransactionType' = 'Refund'

,'TransactionId' = Trxn.TransactionId

,'TransactionKey' = Trxn.TransactionKey

,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

,'ItemGroup' = ProdGrp.Id

,'ItemGroupDesc' = ProdGrp.Description

,'Item' = Prod.Id

,'ItemDescription' = TrxnSale.Description

,'CustGroup' = CustGrp.Id

,'CustGroupDesc' = CustGrp.Description

,'Cust' = Cust.Id

,'CustFirstName' = Cust.FirstName

,'CustMiddleName' = Cust.MiddleName

,'CustLastName' = Cust.LastName

,'CustAddress1' = Addr.Address1

,'CustAddress2' = Addr.Address2

,'CustAddress3' = Addr.Address3

,'CustCity' = Addr.City

,'CustZip' = Addr.ZipCode

,'CustPhone' = Addr.PhoneNumber

,'Quantity' = TrxnSale.Quantity * -1

,'UOMQuantity' = TrxnSale.Quantity * -1/ISNULL(NULLIF(TrxnSale.UOMBaseQuantity,0),1)

,'UOM' = ISNULL(UOM.Description,'')

,'OpenQuantity' = -1

,'FullfilledQty' = -1

,'Price' = TrxnSale.Price * -1

,'Surcharge' = -1

,'Tax' = TrxnSale.Tax * -1

,'TotalAmount' = TrxnSale.TotalPostSaleDiscount * -1

,'SalesPersonId' = ISNULL(SalesPersonUser.Id,'')

,'SalesPersonName' = isNull(SalesPersonUser.FirstName, '') + ' ' + isNull(SalesPersonUser.MiddleName, '') + ' ' + isNull(SalesPersonUser.LastName, '')

,'Created' = Trxn.Created

,'OriginalDetailKey'= -1

,'Status' = -1

,'LayAwayPlanKey' = -1

,'InstallmentCount' = -1

,'SalesPersonKey' = SalesPerson.UserKey

,'SalesPercentage' = SalesPerson.Percentage

,'BusinessDate' = Trxn.BusinessDate

,'Subsidiary' = Sub.Id

,'SubsidiaryDescription' = Sub.Description

,'CurrencyKey' = Trxn.CurrencyKey

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

,'ExchangeRate' = Trxn.ExchangeRate

,'PriceEC' = -1*TrxnSale.PriceEC

,'DiscountEC' = -1*((ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0))

,'SurchargeEC' = -1

,'TaxEC' = -1*TrxnSale.TaxEC

,'TotalAmountEC' = -1*TrxnSale.TotalPostSaleDiscountEC

,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)