iVend Database Database : RPT_ABCAnalysis Stored Procedure

Creation Date

1/5/2016 6:40 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

VarChar

8

@IncludeLayaway

In

Boolean

1

@IncludeOrder

In

Boolean

1

@A_Per

In

Numeric

9

@B_Per

In

Numeric

9

@C_Per

In

Numeric

9

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

InvProduct

Table

Stores the product related details.

1

PmtCurrency

Table

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

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

/****** Object:  StoredProcedure [dbo].[RPT_ABCAnalysis]    Script Date: 10/9/2015 2:32:04 PM ******/

--SET ANSI_NULLS ON

--GO

--SET QUOTED_IDENTIFIER ON

--GO

CREATE PROCEDURE [dbo].[RPT_ABCAnalysis]

(

@FromDate VARCHAR(8),

@ToDate VARCHAR(8),

@IncludeLayaway BIT,

@IncludeOrder BIT,

@A_Per Decimal(10,2),

@B_Per Decimal(10,2),

@C_Per Decimal(10,2)

)

AS

BEGIN

-----    EXEC RPT_ABCAnalysis '20160801', '20161013', 0, 0, 80, 15, 5

DECLARE @A DECIMAL(10,2), @B DECIMAL(10,2), @C DECIMAL(10,2)

--,@FromDate VARCHAR(8)='20150101', @ToDate VARCHAR(8)='20150930', @IncludeLayaway BIT=1, @IncludeOrder BIT=1, @A_Per DECIMAL(10,2) = 80,

--@B_Per DECIMAL(10,2) =15, @C_Per DECIMAL(10,2) =5

SET @A = @A_Per/100

SET @B = @A+@B_Per/100

SET @C = @C_Per/100

DECLARE @Temp TABLE (

  Type NVARCHAR(50), Item NVARCHAR(100), Description NVARCHAR(200), Quantity DECIMAL(20,5), SalePrice DECIMAL(20,5), CultureInfo NVARCHAR(100),

  BaseCultureInfo NVARCHAR(100), ExchangeRate Decimal(10,5)

    )

INSERT @Temp (Type, Item, Description, Quantity, SalePrice, CultureInfo, BaseCultureInfo, ExchangeRate)

SELECT 'Sale' AS Type, P.Id AS Item, P.Description AS ItemDesc, Sum(T1.Quantity) AS Inventory, SUM(T1.TotalPostSaleDiscount) AS Sales,

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

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

FROM TrxTransaction T

INNER JOIN TrxTransactionSaleItem T1 ON T.TransactionKey = T1.TransactionKey

INNER JOIN InvProduct P ON P.ProductKey = T1.ProductKey

LEFT OUTER JOIN PmtCurrency Cur ON T.CurrencyKey = Cur.CurrencyKey

WHERE

 T.IsSuspended =0 AND T.IsVoided =0 AND T1.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

GROUP BY P.Id, P.Description, CultureInfo, T.SubsidiaryKey, T.ExchangeRate

UNION ALL

SELECT 'Refund' AS Type, P.Id AS Item, P.Description AS ItemDesc, Sum(-1*T1.Quantity) AS Inventory, SUM(-1*T1.TotalPostSaleDiscount) AS Sales,

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

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

FROM TrxTransaction T

INNER JOIN TrxTransactionSaleItem T1 ON T.TransactionKey = T1.TransactionKey

INNER JOIN InvProduct P ON P.ProductKey = T1.ProductKey

LEFT OUTER JOIN PmtCurrency Cur ON T.CurrencyKey = Cur.CurrencyKey

WHERE

 T.IsSuspended =0 AND T.IsVoided =0 AND T1.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

GROUP BY P.Id, P.Description, CultureInfo, T.SubsidiaryKey, T.ExchangeRate

-----***********Layaway*********

IF @IncludeLayaway = 1

BEGIN

INSERT @Temp (Type, Item, Description, Quantity, SalePrice, CultureInfo, BaseCultureInfo, ExchangeRate)

SELECT 'Layaway' AS Type, P.Id AS Item, P.Description AS ItemDesc, Sum(T1.Quantity) AS Inventory, SUM(T1.TotalPostSaleDiscount) AS Sales,

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

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

FROM TrxTransaction T

INNER JOIN TrxTransactionLayaway T1 ON T.TransactionKey = T1.TransactionKey

INNER JOIN InvProduct P ON P.ProductKey = T1.ProductKey

LEFT OUTER JOIN PmtCurrency Cur ON T.CurrencyKey = Cur.CurrencyKey

WHERE

 T.IsSuspended =0 AND T.IsVoided =0 AND T1.Status<>2

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

GROUP BY P.Id, P.Description, CultureInfo, T.SubsidiaryKey, T.ExchangeRate

END

---***********Order*********

IF @IncludeOrder = 1

BEGIN

INSERT @Temp (Type, Item, Description, Quantity, SalePrice, CultureInfo, BaseCultureInfo, ExchangeRate)

SELECT 'Order' AS Type, P.Id AS Item, P.Description AS ItemDesc, Sum(T1.Quantity) AS Inventory, SUM(T1.TotalPostSaleDiscount) AS Sales,

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

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

FROM TrxTransaction T

INNER JOIN TrxTransactionOrder T1 ON T.TransactionKey = T1.TransactionKey

INNER JOIN InvProduct P ON P.ProductKey = T1.ProductKey

LEFT OUTER JOIN PmtCurrency Cur ON T.CurrencyKey = Cur.CurrencyKey

WHERE

 T.IsSuspended =0 AND T.IsVoided =0 AND T1.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

GROUP BY P.Id, P.Description, CultureInfo, T.SubsidiaryKey, T.ExchangeRate

END

; WITH CTE AS (

  SELECT Item, Description, SUM(Quantity) AS Quantity, Sum(SalePrice) AS SalePrice,

    ROW_NUMBER() Over(ORDER BY Sum(SalePrice) DESC)AS Rn, CultureInfo, BaseCultureInfo, ExchangeRate

  FROM @Temp

  Group BY Item, Description, CultureInfo, BaseCultureInfo, ExchangeRate

   )

SELECT

 CTE.Item, CTE.Description, CTE.Quantity, CTE.SalePrice, Sum(C.SalePrice)/NullIF(Sum(CTE.SalePrice)OVER(),0) AS Cum,

 CTE.SalePrice/NULLIF(SUM(CTE.SalePrice)OVER(),0)*100 AS AmountPer,

CASE WHEN (Sum(C.SalePrice)/NullIF(Sum(CTE.SalePrice)OVER(),0))<= @A THEN 'A'

  WHEN (Sum(C.SalePrice)/NullIF(Sum(CTE.SalePrice)OVER(),0))<= @B THEN 'B'

  ELSE 'C' END AS Category,

  CTE.RN, CTE.CultureInfo, CTE.BaseCultureInfo, CTE.ExchangeRate

FROM CTE JOIN CTE C on CTE.RN>=C.RN

Group BY

CTE.Item, CTE.Description, CTE.Quantity, CTE.SalePrice, CTE.RN, CTE.CultureInfo, CTE.BaseCultureInfo, CTE.ExchangeRate

ORDER BY SalePrice Desc

END