iVend Database Database > User Defined Functions > rptSalesData User Defined Function

Navigation: iVend Database Database > User Defined Functions >

iVend 6.6 Database Help

rptSalesData User Defined Function

Collapse All Expand All

iVend Database Database : rptSalesData User Defined Function

Properties

Creation Date	2/25/2019 9:49 PM
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
@TABLE_RETURN_VALUE	Return Value	Result table returned by table valued function	Empty	0

Objects that rptSalesData depends on

Database Object	Object Type	Description	Dep Level
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
TrxTransactionSaleItem	Table	Stores sale/refund/delivery items attached to a transaction	1

Procedure Source Code

CREATE Function [dbo].[rptSalesData]

(

@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

)

Returns @SaleData Table

(

SaleType INT

,TransactionType NVARCHAR(100)

,TransactionId NVARCHAR(100)

,TransactionKey NVARCHAR(200)

,TransactionDate VARCHAR(50)

,TransactionTime VARCHAR(50)

,StoreId NVARCHAR(100)

,StoreDesc NVARCHAR(200)

,POSId NVARCHAR(100)

,POSDesc NVARCHAR(200)

,TillId NVARCHAR(100)

,TillDesc NVARCHAR(200)

,ItemGroup NVARCHAR(100)

,ItemGroupDesc NVARCHAR(200)

,Item NVARCHAR(100)

,ItemDescription NVARCHAR(200)

,CustGroup NVARCHAR(100)

,CustGroupDesc NVARCHAR(200)

,Cust NVARCHAR(100)

,CustFirstName NVARCHAR(200)

,CustMiddleName NVARCHAR(100)

,CustLastName NVARCHAR(80)

,Quantity DECIMAL(20, 5)

,UOMQuantity DECIMAL(20, 5)

,UOM VARCHAR(200)

,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

,BusinessDate DateTime

,CultureInfo NVARCHAR(80)

,SellRate DECIMAL(20, 5)

,Subsidiary NVARCHAR(40)

,SubsidiaryDescription NVARCHAR(200)

,PriceEC DECIMAL(38, 7)

,DiscountEC DECIMAL(38, 7)

,SurchargeEC DECIMAL(20, 5)

,TaxEC DECIMAL(38, 7)

,TotalAmountEC DECIMAL(38, 7)

,BaseCultureInfo NVARCHAR(80)

)

Begin

--DECLARE @FromDate nvarchar(8),@ToDate nvarchar(8),@ProductGroup nvarchar(4000), @FromProduct nvarchar(40), @ToProduct nvarchar(40),

-- @CustomerGroup nvarchar(4000), @FromCustomer nvarchar(40),@ToCustomer nvarchar(40),@Store nvarchar(4000),

-- @Pos nvarchar(4000),@Till nvarchar(4000),@SalesPerson nvarchar(4000),@IncludeSale bit, @IncludeRefund bit,@IncludeLayaway bit,@Subsidiary nvarchar(2000),@HoCurrency bit

--SELECT @Fromdate=N'20171201',@todate=N'20171230',@ProductGroup=N'',@FromProduct=N'',@ToProduct=N'', @CustomerGroup=N'',

-- @FromCustomer=N'',@ToCustomer=N'',@Store=N'', @Pos=N'',@Till=N'',@SalesPerson=N'',@IncludeSale=1, @IncludeRefund=1, @IncludeLayAway=1,

-- @Subsidiary=N'',@HOCurrency=0

INSERT INTO @SaleData

Select CASE TransactionType WHEN 'Sale' THEN 1 WHEN 'Refund' THEN 2 WHEN 'Layaway' THEN 3 WHEN 'Layaway Cancel' THEN 4 END AS SaleType,

TransactionType, TransactionId, TransactionKey, TransactionDate, TransactionTime, StoreId, StoreDesc, POSId, POSDesc, TillId, TillDesc, ItemGroup, ItemGroupDesc,

Item, ItemDescription, CustGroup, CustGroupDesc, Cust, CustFirstName, CustMiddleName, CustLastName, Quantity, UOMQuantity, UOM, Price, Discount, Surcharge,

Tax, TotalAmount, SalesPersonId, SalesPersonName, Created, BusinessDate, CultureInfo, SellRate, Subsidiary, SubsidiaryDescription,

PriceEC, DiscountEC, SurchargeEC, TaxEC, TotalAmountEC, BaseCultureInfo

From (

SELECT

CASE WHEN (TrxnSale.Type = 0 Or TrxnSale.Type = 3) THEN 'Sale' WHEN TrxnSale.Type=1 THEN 'Refund' END AS TransactionType

, Trxn.TransactionId AS TransactionId, Trxn.TransactionKey AS TransactionKey, CONVERT(VARCHAR, Trxn.Created, 107) AS TransactionDate

, CONVERT(VARCHAR(5), Trxn.Created, 108) AS TransactionTime, RtlStore.Id AS StoreId, RtlStore.Description AS StoreDesc, RtlPOS.Id AS POSId

, RtlPOS.Description AS POSDesc, TilTill.Id AS TillId, TilTill.Description AS TillDesc, ProdGrp.Id AS ItemGroup, ProdGrp.Description AS ItemGroupDesc

, Prod.Id AS Item, TrxnSale.Description AS ItemDescription, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc

, Cust.Id AS Cust, Cust.FirstName AS CustFirstName, Cust.MiddleName AS CustMiddleName, Cust.LastName AS CustLastName

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.Quantity ELSE TrxnSale.Quantity END AS Quantity

, CASE WHEN TrxnSale.Type=1 THEN -1*(TrxnSale.Quantity/ISNULL(TrxnSale.UOMBaseQuantity,1)) ELSE (TrxnSale.Quantity/ISNULL(TrxnSale.UOMBaseQuantity,1)) END AS UOMQuantity

, ISNULL(UOM.Description,'') AS UOM, TrxnSale.Price AS Price, CASE WHEN TrxnSale.Type=1 THEN -1*(ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)+ISNULL(TrxnSale.TaxableDiscountAmount,0)) ELSE (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)+ISNULL(TrxnSale.TaxableDiscountAmount,0)) END AS Discount

, -1 AS Surcharge, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.Tax ELSE TrxnSale.Tax END AS Tax

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TotalPostSaleDiscount ELSE TrxnSale.TotalPostSaleDiscount END AS TotalAmount

, ISNULL(LbrUser.Id,'') AS SalesPersonId, ISNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName

, Trxn.Created AS Created, Trxn.BusinessDate AS BusinessDate

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

, Trxn.ExchangeRate AS SellRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, TrxnSale.PriceEC AS PriceEC

, CASE WHEN TrxnSale.Type=1 THEN -1*(ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)END AS DiscountEC

, -1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TaxEC ELSE Trxn.TaxEC END AS TaxEC

, CASE WHEN TrxnSale.Type=1 THEN -1*TrxnSale.TotalPostSaleDiscountEC ELSE TrxnSale.TotalPostSaleDiscountEC END AS TotalAmountEC

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

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 --AND Prod.IsDeleted = 0

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

LEFT OUTER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

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 RtlSubsidiary Sub WITH (NOLOCK) ON RtlStore.SubsidiaryKey = Sub.SubsidiaryKey

WHERE Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (TrxnSale.Type = 0 Or TrxnSale.Type = 3 OR TrxnSale.Type = 1) --0 - Sale, 3-Delivery Against Special Order

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(LbrUser.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@SalesPerson)) OR ISNULL(LbrUser.Id,'') IN (CASE WHEN @SalesPerson='' THEN ISNULL(LbrUser.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 (ISNULL(RtlPOS.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@POS)) OR ISNULL(RtlPOS.Id,'') IN (CASE WHEN @POS='' THEN ISNULL(RtlPOS.Id,'') END)) -------------Filter Change to avoid Charindex as like operator

AND (ISNULL(TilTill.Id,'') IN (SELECT DataString FROM [dbo].[fnSplitValues](@Till)) OR ISNULL(TilTill.Id,'') 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 --Subsidiary Filter

UNION ALL

SELECT

CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status<>2) THEN 'Layaway' WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN 'Layaway Cancel' END AS TransactionType

, Trxn.TransactionId AS TransactionId, Trxn.TransactionKey AS TransactionKey, CONVERT(VARCHAR, Trxn.Created, 107) AS TransactionDate

, CONVERT(VARCHAR(5), Trxn.Created, 108) AS TransactionTime, RtlStore.Id AS StoreId, RtlStore.Description AS StoreDesc, RtlPOS.Id AS POSId

, RtlPOS.Description AS POSDesc, TilTill.Id AS TillId, TilTill.Description AS TillDesc, ProdGrp.Id AS ItemGroup, ProdGrp.Description AS ItemGroupDesc

, Prod.Id AS Item, LayAway.Description AS ItemDescription, CustGrp.Id AS CustGroup, CustGrp.Description AS CustGroupDesc, Cust.Id AS Cust

, Cust.FirstName AS CustFirstName, Cust.MiddleName AS CustMiddleName, Cust.LastName AS CustLastName

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Quantity ELSE LayAway.Quantity END AS Quantity

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(LayAway.Quantity / NULLIF(ISNULL(Layaway.UomBaseQuantity,1),0)) ELSE (LayAway.Quantity / ISNULL(Layaway.UomBaseQuantity,0)) END AS UOMQuantity

, ISNULL(UOM.Description,'') AS UOM, LayAway.Price AS Price

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0)) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0)) END AS Discount

, -1 AS Surcharge, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.Tax ELSE LayAway.Tax END AS Tax

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscount ELSE LayAway.TotalPostSaleDiscount END AS TotalAmount

, ISNULL(LbrUser.Id,'') AS SalesPersonId, isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '') AS SalesPersonName

, Trxn.Created AS Created, Trxn.BusinessDate AS BusinessDate

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

, Trxn.ExchangeRate AS ExchangeRate, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDescription, LayAway.PriceEC AS PriceEC

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) ELSE (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0)+ISNULL(LayAway.TaxableDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0) END AS DiscountEC

, -1*Trxn.ExchangeRate AS SurchargeEC, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TaxEC ELSE LayAway.TaxEC END AS TaxEC

, CASE WHEN (LayAway.[Type] = 0 AND LayAway.Status=2) THEN -1*LayAway.TotalPostSaleDiscountEC ELSE LayAway.TotalPostSaleDiscountEC END AS TotalAmountEC

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

From TrxTransaction AS Trxn WITH (NOLOCK)

INNER JOIN TrxTransactionLayAway AS LayAway WITH (NOLOCK) ON Trxn.TransactionKey = LayAway.TransactionKey

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

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

INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON LayAway.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

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 RtlSubsidiary Sub WITH (NOLOCK) ON RtlStore.SubsidiaryKey = Sub.SubsidiaryKey

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (LayAway.[Type] = 0 OR LayAway.Status=2)

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 --Date Filter

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 ((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 (LbrUser.Id IN (SELECT DataString FROM [dbo].[fnSplitValues](@SalesPerson)) OR LbrUser.Id IN (CASE WHEN @SalesPerson='' THEN LbrUser.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 ISNULL(Sub.Id,'')= CASE WHEN @Subsidiary <> '' THEN @Subsidiary ELSE ISNULL(Sub.Id,'') END --Subsidiary Filter

)

AS SalesData

Where (TransactionType = CASE WHEN @IncludeSale=1 THEN 'Sale' END OR TransactionType=CASE WHEN @IncludeRefund=1 THEN 'Refund' END OR TransactionType=CASE WHEN @IncludeLayaway=1 THEN 'Layaway' END OR TransactionType=CASE WHEN @IncludeLayaway=1 THEN 'Layaway Cancel' END)

Return

END

Related Objects

iVend Database Database

Send comments on this topic.