iVend Database Database : DailySaleSummary User Defined Function

Creation Date

9/17/2019 9:19 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarWChar

8

@ToDate

In

VarWChar

8

@Subsidiary

In

VarWChar

100

@Store

In

VarWChar

100

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

InvProduct

Table

Stores the product related details.

1

InvProductCost

Table

Store the historical cost of the product.

1

PmtCurrency

Table

Define a list of all currecies 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

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

CREATE FUNCTION [dbo].[DailySaleSummary](

@FromDate NVARCHAR(8)

,@ToDate NVARCHAR(8)

,@Subsidiary NVARCHAR(100)

,@Store NVARCHAR(100)

--,@IncludeLayaway Bit

--,@IncludeRefund Bit

--,@IncludeSale Bit

--,@IncludeOrder Bit

)

--DECLARE @FromDate NVARCHAR(8)='20160508', @ToDate NVARCHAR(8)='20160508', @Subsidiary NVARCHAR(100)='', @Store NVARCHAR(100)=''

--SELECT * FROM DailySaleSummary(@FromDate, @ToDate, @SubSidiary, @Store)

--DECLARE @IncludeSale Bit = 1, @IncludeLayaway Bit = 0, @IncludeRefund Bit = 1, @IncludeOrder Bit = 1

RETURNS @SaleSummary TABLE

(TransactionType NVARCHAR(100), SubsidiaryKey NVARCHAR(200), SubsidiaryId NVARCHAR(100), SubsidiaryDesc NVARCHAR(200), StoreKey NVARCHAR(200), StoreId NVARCHAR(100),

StoreDesc NVARCHAR(200), WarehouseKey NVARCHAR(200), ProductKey NVARCHAR(200), BusinessDate DATETIME, ExchangeRate DECIMAL(20,5), CultureInfo NVARCHAR(100),

BaseCultureInfo NVARCHAR(100), SaleInclusiveTax DECIMAL(20,5), SaleInclusiveTaxEC DECIMAL(20,5), SaleExcludeTax DECIMAL(20,5),

SaleExcludeTaxEC DECIMAL(20,5), Tax DECIMAL(20,5), TaxEC DECIMAL(20,5), UnitSold DECIMAL(20,5), TransactionCount INT,TransactionKey NVARCHAR(200),

Discount DECIMAL(20,5), DiscountEC DECIMAL(20,5), Cost DECIMAL(20,5), CostEC DECIMAL(20,5), CostingMethod INT)

AS

BEGIN

--DECLARE @SaleSummary AS TABLE

--(TransactionType NVARCHAR(100), SubsidiaryKey NVARCHAR(200), SubsidiaryId NVARCHAR(100), SubsidiaryDesc NVARCHAR(200), StoreKey NVARCHAR(200), StoreId NVARCHAR(100),

-- StoreDesc NVARCHAR(200), WarehouseKey NVARCHAR(200), ProductKey NVARCHAR(200), BusinessDate DATETIME, ExchangeRate DECIMAL(20,5), CultureInfo NVARCHAR(100),

-- BaseCultureInfo NVARCHAR(100), SaleInclusiveTax DECIMAL(20,5), SaleInclusiveTaxEC DECIMAL(20,5), SaleExcludeTax DECIMAL(20,5),

-- SaleExcludeTaxEC DECIMAL(20,5), Tax DECIMAL(20,5), TaxEC DECIMAL(20,5), UnitSold DECIMAL(20,5), TransactionCount INT,TransactionKey NVARCHAR(200),

-- Discount DECIMAL(20,5), DiscountEC DECIMAL(20,5), Cost DECIMAL(20,5), CostEC DECIMAL(20,5), CostingMethod INT)

INSERT @SaleSummary

---- SALE

SELECT

'Sale' AS TransactionType, S.SubsidiaryKey, Sub.Id AS SubsidiaryId, Sub.Description AS SubsidiaryDesc, Trx.StoreKey, S.Id AS StoreId, S.Description AS StoreDesc,

TrxItem.WarehouseKey, TrxItem.ProductKey, Trx.BusinessDate, ISNULL(Trx.ExchangeRate,1) AS ExchangeRate,

(CASE WHEN (S.SubsidiaryKey=0 OR S.SubsidiaryKey IS NULL) THEN (SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) ELSE Sub.CultureInfo END)AS CultureInfo,

(SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) AS BaseCultureInfo,

ISNULL(TrxItem.TotalPostSaleDiscount,0) AS SaleInclusiveTax, ISNULL(TrxItem.TotalPostSaleDiscountEC,0) AS SaleInclusiveTaxEC,

(ISNULL(TrxItem.TotalPostSaleDiscount,0)-ISNULL(TrxItem.Tax,0)) AS SaleExcludeTax,

(ISNULL(TrxItem.TotalPostSaleDiscountEC,0)-ISNULL(TrxItem.TaxEC,0)) AS SaleExcludeTaxEC,

ISNULL(TrxItem.Tax,0)AS Tax, ISNULL(TrxItem.TaxEC,0) AS TaxEC,

ISNULL(TrxItem.Quantity,0) AS UnitSold,

ISNULL(Row_NUmber()OVER(Partition BY Trx.TransactionKey ORDER BY Trx.TransactionKey),0) TransactionCount, Trx.TransactionKey,

(ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0)) AS Discount,

((ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0))/NULLIF(ISNULL(Trx.ExchangeRate,1),1)) AS DiscountEC,

((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)*(ISNULL(TrxItem.Quantity,0))) AS Cost,

((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0)

AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0) AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)/NULLIF(ISNULL(Trx.ExchangeRate,1),1)*(ISNULL(TrxItem.Quantity,0))) AS CostEC, P.CostingMethod

FROM dbo.TrxTransaction Trx WITH (NOLOCK)

INNER JOIN dbo.TrxTransactionSaleItem TrxItem WITH (NOLOCK) ON Trx.TransactionKey = TrxItem.TransactionKey

INNER JOIN dbo.RtlStore S WITH (NOLOCK) ON Trx.StoreKey = S.StoreKey

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

INNER JOIN InvProduct P WITH (NOLOCK) ON TrxItem.ProductKey = P.ProductKey

WHERE Trx.IsSuspended = 0 AND Trx.IsVoided = 0 AND (TrxItem.Type = 0 OR TrxItem.Type = 3)

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

AND CASE WHEN @ToDate = '' THEN CONVERT(VARCHAR, Trx.BusinessDate, 112) ELSE @ToDate END

AND S.Id = CASE WHEN @Store <> '' THEN @Store ELSE S.Id END

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

UNION ALL

------REFUND

SELECT 'Refund' AS TransactionType, S.SubsidiaryKey, Sub.Id AS SubsidiaryId, Sub.Description AS SubsidiaryDesc, Trx.StoreKey, S.Id AS StoreId, S.Description AS StoreDesc,

TrxItem.WarehouseKey, TrxItem.ProductKey, Trx.BusinessDate, ISNULL(Trx.ExchangeRate,1) AS ExchangeRate,

(CASE WHEN (S.SubsidiaryKey=0 OR S.SubsidiaryKey IS NULL) THEN (SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) ELSE Sub.CultureInfo END)AS CultureInfo,

(SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) AS BaseCultureInfo,

-1*ISNULL(TrxItem.TotalPostSaleDiscount,0) AS SaleInclusiveTax, -1*ISNULL(TrxItem.TotalPostSaleDiscountEC,0) AS SaleInclusiveTaxEC,

-1*(ISNULL(TrxItem.TotalPostSaleDiscount,0)-ISNULL(TrxItem.Tax,0)) AS SaleExcludeTax,

-1*(ISNULL(TrxItem.TotalPostSaleDiscountEC,0)-ISNULL(TrxItem.TaxEC,0)) AS SaleExcludeTaxEC,

-1*ISNULL(TrxItem.Tax,0)AS Tax, -1*ISNULL(TrxItem.TaxEC,0) AS TaxEC,

-1*ISNULL(TrxItem.Quantity,0) AS UnitSold,

ISNULL(Row_NUmber()OVER(Partition BY Trx.TransactionKey ORDER BY Trx.TransactionKey),0) TransactionCount, Trx.TransactionKey,

-1*(ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0)) AS Discount,

-1*((ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0))/NULLIF(ISNULL(Trx.ExchangeRate,1),1)) AS DiscountEC,

-1*((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)*(ISNULL(TrxItem.Quantity,0))) AS Cost,

-1*((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0)

AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0) AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)/NULLIF(ISNULL(Trx.ExchangeRate,1),1)*(ISNULL(TrxItem.Quantity,0))) AS CostEC, P.CostingMethod

FROM dbo.TrxTransaction Trx WITH (NOLOCK)

INNER JOIN dbo.TrxTransactionSaleItem TrxItem WITH (NOLOCK) ON  Trx.TransactionKey = TrxItem.TransactionKey

INNER JOIN dbo.RtlStore S WITH (NOLOCK) ON  Trx.StoreKey = S.StoreKey

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

INNER JOIN InvProduct P WITH (NOLOCK) ON  TrxItem.ProductKey = P.ProductKey

WHERE Trx.IsSuspended = 0 AND Trx.IsVoided = 0 AND TrxItem.Type = 1

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

AND CASE WHEN @ToDate = '' THEN CONVERT(VARCHAR, Trx.BusinessDate, 112) ELSE @ToDate END

AND S.Id = CASE WHEN @Store <> '' THEN @Store ELSE S.Id END

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

UNION ALL

----LAYAWAY

SELECT 'Layaway' AS TransactionType, S.SubsidiaryKey, Sub.Id AS SubsidiaryId, Sub.Description AS SubsidiaryDesc, Trx.StoreKey, S.Id AS StoreId, S.Description AS StoreDesc,

TrxItem.WarehouseKey, TrxItem.ProductKey, Trx.BusinessDate, ISNULL(Trx.ExchangeRate,1) AS ExchangeRate,

(CASE WHEN (S.SubsidiaryKey=0 OR S.SubsidiaryKey IS NULL) THEN (SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) ELSE Sub.CultureInfo END)AS CultureInfo,

(SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) AS BaseCultureInfo,

ISNULL(TrxItem.TotalPostSaleDiscount,0) AS SaleInclusiveTax, ISNULL(TrxItem.TotalPostSaleDiscountEC,0) AS SaleInclusiveTaxEC,

(ISNULL(TrxItem.TotalPostSaleDiscount,0)-ISNULL(TrxItem.Tax,0)) AS SaleExcludeTax,

(ISNULL(TrxItem.TotalPostSaleDiscountEC,0)-ISNULL(TrxItem.TaxEC,0)) AS SaleExcludeTaxEC,

ISNULL(TrxItem.Tax,0)AS Tax, ISNULL(TrxItem.TaxEC,0) AS TaxEC,

ISNULL(TrxItem.Quantity,0) AS UnitSold,

ISNULL(Row_NUmber()OVER(Partition BY Trx.TransactionKey ORDER BY Trx.TransactionKey),0) TransactionCount, Trx.TransactionKey,

(ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0)) AS Discount,

((ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0))/NULLIF(ISNULL(Trx.ExchangeRate,1),1)) AS DiscountEC,

((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

  AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)*(ISNULL(TrxItem.Quantity,0))) AS Cost,

((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

  AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)/NULLIF(ISNULL(Trx.ExchangeRate,1),1)*(ISNULL(TrxItem.Quantity,0))) AS CostEC, P.CostingMethod

FROM dbo.TrxTransaction Trx WITH (NOLOCK)

INNER JOIN dbo.TrxTransactionLayaway TrxItem WITH (NOLOCK) ON  Trx.TransactionKey = TrxItem.TransactionKey

INNER JOIN dbo.RtlStore S WITH (NOLOCK) ON  Trx.StoreKey = S.StoreKey

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

INNER JOIN InvProduct P WITH (NOLOCK) ON  TrxItem.ProductKey = P.ProductKey

WHERE Trx.IsSuspended = 0 AND Trx.IsVoided = 0 AND TrxItem.[Type] = 0 AND TrxItem.Status<>2

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

AND CASE WHEN @ToDate = '' THEN CONVERT(VARCHAR, Trx.BusinessDate, 112) ELSE @ToDate END

AND S.Id = CASE WHEN @Store <> '' THEN @Store ELSE S.Id END

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

UNION ALL

----LAYAWAY CANCEL

SELECT 'Layaway Cancel' AS TransactionType, S.SubsidiaryKey, Sub.Id AS SubsidiaryId, Sub.Description AS SubsidiaryDesc, Trx.StoreKey, S.Id AS StoreId, S.Description AS StoreDesc,

TrxItem.WarehouseKey, TrxItem.ProductKey, Trx.BusinessDate, ISNULL(Trx.ExchangeRate,1) AS ExchangeRate,

(CASE WHEN (S.SubsidiaryKey=0 OR S.SubsidiaryKey IS NULL) THEN (SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) ELSE Sub.CultureInfo END)AS CultureInfo,

(SELECT CultureInfo FROM dbo.PmtCurrency WHERE IsBaseCurrency=1) AS BaseCultureInfo,

-1*ISNULL(TrxItem.TotalPostSaleDiscount,0) AS SaleInclusiveTax, -1*ISNULL(TrxItem.TotalPostSaleDiscountEC,0) AS SaleInclusiveTaxEC,

-1*(ISNULL(TrxItem.TotalPostSaleDiscount,0)-ISNULL(TrxItem.Tax,0)) AS SaleExcludeTax,

-1*(ISNULL(TrxItem.TotalPostSaleDiscountEC,0)-ISNULL(TrxItem.TaxEC,0)) AS SaleExcludeTaxEC,

-1*ISNULL(TrxItem.Tax,0)AS Tax, -1*ISNULL(TrxItem.TaxEC,0) AS TaxEC,

-1*ISNULL(TrxItem.Quantity,0) AS UnitSold,

ISNULL(Row_NUmber()OVER(Partition BY Trx.TransactionKey ORDER BY Trx.TransactionKey),0) TransactionCount, Trx.TransactionKey,

-1*(ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0)) AS Discount,

-1*((ISNULL(TrxItem.ManualDiscountAmount,0)+ISNULL(TrxItem.PromotionalDiscountAmount,0)+ISNULL(TrxItem.SaleDiscountAmount,0)+ISNULL(TrxItem.SystemDiscountAmount,0)+ISNULL(TrxItem.TaxableDiscountAmount,0))/NULLIF(ISNULL(Trx.ExchangeRate,1),1)) AS DiscountEC,

-1*((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

  AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)*(ISNULL(TrxItem.Quantity,0))) AS Cost,

-1*((CASE WHEN P.CostingMethod=1 THEN ISNULL(TrxItem.AverageCost,0)

WHEN P.CostingMethod=2 THEN ISNULL((

SELECT TOP 1 Cost FROM dbo.InvProductCost PC

WHERE PC.ProductKey=TrxItem.ProductKey AND ISNULL(PC.WarehouseKey,0) IN (ISNULL(TrxItem.WarehouseKey,0),0) AND ISNULL(Trx.SubsidiaryKey,0)=ISNULL(PC.SubsidiaryKey,0)

  AND PC.EffectiveDate<=Trx.BusinessDate ORDER BY PC.EffectiveDate DESC)

  ,0) END)/NULLIF(ISNULL(Trx.ExchangeRate,1),1)*(ISNULL(TrxItem.Quantity,0))) AS CostEC, P.CostingMethod

FROM dbo.TrxTransaction Trx WITH (NOLOCK)

INNER JOIN dbo.TrxTransactionLayaway TrxItem WITH (NOLOCK) ON  Trx.TransactionKey = TrxItem.TransactionKey

INNER JOIN dbo.RtlStore S WITH (NOLOCK) ON  Trx.StoreKey = S.StoreKey

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

INNER JOIN InvProduct P WITH (NOLOCK) ON  TrxItem.ProductKey = P.ProductKey

WHERE Trx.IsSuspended = 0 AND Trx.IsVoided = 0 AND TrxItem.[Type] = 0 AND TrxItem.Status=2

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

AND CASE WHEN @ToDate = '' THEN CONVERT(VARCHAR, Trx.BusinessDate, 112) ELSE @ToDate END

AND S.Id = CASE WHEN @Store <> '' THEN @Store ELSE S.Id END

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

---- SELECT * FROM @SaleSummary

RETURN

END