iVend Database Database : Rpt_BestWorstSellingItem Stored Procedure

Creation Date

12/24/2015 5:42 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

VarChar

8

@ProductGroup

In

VarWChar

4000

@Subsidiary

In

VarWChar

4000

@NosRecord

In

Integer

4

@IncludeLayaway

In

Boolean

1

@IncludeOrder

In

Boolean

1

@SaleQty

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[Rpt_BestWorstSellingItem](

@FromDate VARCHAR(8),

@ToDate VARCHAR(8),

@ProductGroup NVARCHAR(4000),

@Subsidiary NVARCHAR(4000),

@NosRecord INT,

@IncludeLayaway BIT,

@IncludeOrder BIT,

@SaleQty Bit

)

AS

Begin

-----  EXEC Rpt_BestWorstSellingItem '20151111', '20160220', '', 2, 0, 0, 0

----DECLARE @FromDate VARCHAR(8)='20150101', @ToDate VARCHAR(8)='20161118', @ProductGroup NVARCHAR(4000)='', @IncludeLayAway BIT=0, @IncludeOrder BIT=0, @NosRecord INT =4, @SaleQty bit =1

DECLARE @Temp TABLE (

     Type NVARCHAR (50),

     Subsidiary NVARCHAR(200),

     SubsidiaryDesc NVARCHAR(200),

     ItemCode NVARCHAR(100),

     Description NVARCHAR(200),

     UOMQuantity DECIMAL(20,5),

     UOM Varchar(100),

     Price DECIMAL(20,5),

     Discount DECIMAL(20,5),

     Tax DECIMAL(20,5),

     TotalAmount DECIMAL(20,5),

     CultureInfo NVARCHAR(100),

     BaseCultureInfo NVARCHAR(100),

     ExchangeRate DECIMAL(20,5)

    )

INSERT @Temp (Type, Subsidiary, SubsidiaryDesc, ItemCode, Description, UOMQuantity, UOM, Price, Discount, Tax, TotalAmount, CultureInfo, BaseCultureInfo, ExchangeRate)

SELECT 'Sale' AS Type, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, InvProduct.Id AS Item, TrxTransactionSaleItem.Description ItemDesc, UOMQuantity, ISNULL(UOM.Id, '') AS UOM,

 TrxTransactionSaleItem.Price,

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

 TrxTransactionSaleItem.Tax, TrxTransactionSaleItem.TotalPostSaleDiscount AS TotalAmount,

 (CASE WHEN (TrxTransaction.SubsidiaryKey IS NULL OR TrxTransaction.SubsidiaryKey =0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

ELSE Sub.CultureInfo END) AS CultureInfo,

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

FROM

 TrxTransaction INNER JOIN

 TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey=TrxTransaction.TransactionKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

LEFT JOIN InvUOMGroupDetail UOMD ON TrxTransactionSaleItem.UOMGroupDetailKey = UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM ON UOMD.UOMKey=UOM.UOMKey

LEFT OUTER JOIN RtlSubsidiary Sub ON TrxTransaction.SubsidiaryKey = Sub.SubsidiaryKey

WHERE TrxTransaction.IsSuspended = 0 AND TrxTransaction.IsVoided = 0 AND Type=0

AND CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END

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

--AND CHARINDEX(InvProductGroup.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

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

UNION ALL

SELECT 'Refund' AS Type, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, InvProduct.Id AS Item, TrxTransactionSaleItem.Description ItemDesc, -1*UOMQuantity, ISNULL(UOM.Id, '') AS UOM,

-1*TrxTransactionSaleItem.Price,

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

-1* TrxTransactionSaleItem.Tax, -1*TotalPostSaleDiscount AS TotalAmount,

 (CASE WHEN (TrxTransaction.SubsidiaryKey IS NULL OR TrxTransaction.SubsidiaryKey =0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

ELSE Sub.CultureInfo END) AS CultureInfo,

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

FROM

 TrxTransaction

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

LEFT JOIN InvUOMGroupDetail UOMD ON TrxTransactionSaleItem.UOMGroupDetailKey = UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM ON UOMD.UOMKey = UOM.UOMKey

LEFT OUTER JOIN RtlSubsidiary Sub ON TrxTransaction.SubsidiaryKey = Sub.SubsidiaryKey

WHERE TrxTransaction.IsSuspended = 0 AND TrxTransaction.IsVoided = 0 AND Type=1

AND CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END

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

--AND CHARINDEX(InvProductGroup.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

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

--Transaction ORDER

IF @IncludeOrder = 1

BEGIN

INSERT @Temp (Type, Subsidiary, SubsidiaryDesc, ItemCode, Description, UOMQuantity, UOM, Price, Discount, Tax, TotalAmount, CultureInfo, BaseCultureInfo, ExchangeRate)

SELECT 'Order' AS Type, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, InvProduct.Id Item, TrxTransactionOrder.Description ItemDesc, UOMQuantity, ISNULL(UOM.Id, '') AS UOM,

 TrxTransactionOrder.Price,

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

 TrxTransactionOrder.Tax, TrxTransactionOrder.TotalPostSaleDiscount AS TotalAmount,

 (CASE WHEN (TrxTransaction.SubsidiaryKey IS NULL OR TrxTransaction.SubsidiaryKey =0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

ELSE Sub.CultureInfo END) AS CultureInfo,

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

FROM

 TrxTransaction INNER JOIN

 TrxTransactionOrder on TrxTransaction.TransactionKey=TrxTransactionOrder.TransactionKey

INNER JOIN InvProduct ON TrxTransactionOrder.ProductKey = InvProduct.ProductKey

INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

LEFT JOIN InvUOMGroupDetail UOMD ON trxTransactionOrder.UOMGroupDetailKey = UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM ON UOMD.UOMKey = UOM.UOMKey

LEFT OUTER JOIN RtlSubsidiary Sub ON TrxTransaction.SubsidiaryKey = Sub.SubsidiaryKey

Where TrxTransaction.IsSuspended = 0 AND TrxTransaction.IsVoided = 0 AND Status <> 1

AND CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END

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

--AND CHARINDEX(InvProductGroup.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

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

END

--Transaction LAYAWAY

IF @IncludeLayAway = 1

BEGIN

INSERT @Temp (Type, Subsidiary, SubsidiaryDesc, ItemCode, Description, UOMQuantity, UOM, Price, Discount, Tax, TotalAmount, CultureInfo, BaseCultureInfo, ExchangeRate)

Select 'Layaway' AS Type, Sub.Id AS Subsidiary, Sub.Description AS SubsidiaryDesc, InvProduct.Id Item, TrxTransactionLayaway.Description ItemDesc, UOMQuantity, ISNULL(UOM.Id, '') AS UOM,

 TrxTransactionLayaway.Price,

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

 TrxTransactionLayaway.Tax, TrxTransactionLayaway.TotalPostSaleDiscount AS TotalAmount,

 (CASE WHEN (TrxTransaction.SubsidiaryKey IS NULL OR TrxTransaction.SubsidiaryKey =0) THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

ELSE Sub.CultureInfo END) AS CultureInfo,

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

From

 TrxTransaction INNER JOIN

 TrxTransactionLayaway ON TrxTransaction.TransactionKey=TrxTransactionLayaway.TransactionKey

INNER JOIN InvProduct ON TrxTransactionLayaway.ProductKey = InvProduct.ProductKey

INNER JOIN InvProductGroup ON InvProduct.ProductGroupKey = InvProductGroup.ProductGroupKey

LEFT JOIN InvUOMGroupDetail UOMD ON TrxTransactionLayaway.UOMGroupDetailKey = UOMD.UOMGroupDetailKey

LEFT JOIN InvUOM UOM on UOMD.UOMKey = UOM.UOMKey

LEFT OUTER JOIN RtlSubsidiary Sub ON TrxTransaction.SubsidiaryKey = Sub.SubsidiaryKey

Where TrxTransaction.IsSuspended = 0 AND TrxTransaction.IsVoided = 0 AND Status<>2 AND TrxTransactionLayaway.Type=0

AND CONVERT(NVARCHAR, BusinessDate, 112) >= CASE WHEN @FromDate <> '' THEN @FromDate ELSE Convert(nVarchar, GETDATE(),112) END

AND CONVERT(NVARCHAR, BusinessDate, 112) <= CASE WHEN @ToDate <> '' THEN @ToDate ELSE Convert(nVarchar, GETDATE(),112) END

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

--AND CHARINDEX(InvProductGroup.Id, @ProductGroup) > CASE WHEN @ProductGroup <> '' THEN 0 ELSE -1 END

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

END

; WITH Q AS

(

SELECT

  ROW_NUMBER() OVER(Order By CASE WHEN @SaleQty=1 then SUM(TotalAmount) END DESC, CASE WHEN @SaleQty=0 THEN SUM(TotalAmount) END ASC ) As RN,

  Subsidiary, SubsidiaryDesc, ItemCode as Item, Description AS ItemDesc, UOM, SUM(UOMQuantity)AS Quantity,

  (SUM(TotalAmount)-SUM(Tax))/NULLIF(Sum(UOMQuantity),0) as Price, Sum(Discount) AS Discount, SUM(Tax) AS Tax,

  SUM(TotalAmount) AS TotalAmount, CultureInfo, BaseCultureInfo, ExchangeRate

FROM @Temp

GROUP by Subsidiary, SubsidiaryDesc, [ItemCode], Description, UOM, CultureInfo, BaseCultureInfo, ExchangeRate

HAVING Sum(UOMQuantity)>0

)

SELECT *

FROM Q

WHERE Quantity>0 AND RN<=@NosRecord

ORDER BY CASE WHEN @SaleQty = 1 THEN TotalAmount END DESC, CASE WHEN @SaleQty = 0 THEN TotalAmount END ASC

END