iVend Database Database : Loy_GetTransactionLineItemByDate Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

DBTimeStamp

4

@IsBusinessDate

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[Loy_GetTransactionLineItemByDate]

@FromDate datetime,

@IsBusinessDate bit

AS

BEGIN

DECLARE @SQL nvarchar(4000)

  SET @SQL='SELECT    TrxTransactionSaleItem.TransactionItemKey, TrxTransactionSaleItem.TransactionKey, TrxTransactionSaleItem.Type, TrxTransactionSaleItem.ProductKey,

                     InvProduct.ProductGroupKey, TrxTransactionSaleItem.HasDiscounts, TrxTransactionSaleItem.OriginalPrice, TrxTransactionSaleItem.Price,

                     TrxTransactionSaleItem.PriceSource, Sum(TrxTransactionSaleItem.Quantity) As Quantity, Sum(TrxTransactionSaleItem.DiscountAmount) As DiscountAmount, Sum(TrxTransactionSaleItem.DiscountPercent) AS DiscountPercent,

                     Sum(TrxTransactionSaleItem.TotalBeforeSaleDiscount) As TotalBeforeSaleDiscount, Sum(TrxTransactionSaleItem.TotalPostSaleDiscount) AS TotalPostSaleDiscount, TrxTransactionSaleItem.IsExchange,

                     TrxTransactionSaleItem.IsPromotionApplied, Sum(TrxTransactionSaleItem.PromotionalQuantity) AS PromotionalQuantity, TrxTransactionSaleItem.HasDiscountOverride,

                     TrxTransactionSaleItem.OriginalDocumentKey, TrxTransactionSaleItem.OriginalDetailKey, Sum((CASE (OriginalPrice - Price) WHEN 0 THEN 0 ELSE OriginalPrice - Price END))

                     AS PriceOverrideAmount

  FROM TrxTransactionSaleItem

  INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

  INNER JOIN TrxTransaction ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

  WHERE'

              if(@IsBusinessDate = 1)

    SET @SQL = @SQL + ' TrxTransaction.BusinessDate >='''+ Convert(nvarchar,@FromDate,112) + ''''

  else

    SET @SQL = @SQL + ' TrxTransaction.ActualDate >=''' + Convert(nvarchar,@FromDate,112) + ''''

                    SET @SQL = @SQL+'Group By TrxTransactionSaleItem.TransactionItemKey, TrxTransactionSaleItem.TransactionKey, TrxTransactionSaleItem.Type, TrxTransactionSaleItem.ProductKey,

                     InvProduct.ProductGroupKey, TrxTransactionSaleItem.HasDiscounts, TrxTransactionSaleItem.OriginalPrice, TrxTransactionSaleItem.Price,

                     TrxTransactionSaleItem.PriceSource,

                     TrxTransactionSaleItem.IsExchange,

                     TrxTransactionSaleItem.IsPromotionApplied,TrxTransactionSaleItem.HasDiscountOverride,

                     TrxTransactionSaleItem.OriginalDocumentKey, TrxTransactionSaleItem.OriginalDetailKey'

EXEC(@SQL)

END

--Loy_GetTransactionLineItemByDate '2010-01-01',1