iVend Database Database : RptSalesTransactionByItemAndItemGroup Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@Store

In

VarChar

5000

@FromDate

In

VarChar

1

@ToDate

In

VarChar

1

@FromProductGroup

In

BigInt

8

@ToProductGroup

In

BigInt

8

@FromProduct

In

BigInt

8

@ToProduct

In

BigInt

8

@FromCustomerGroup

In

BigInt

8

@ToCustomerGroup

In

BigInt

8

@FromCustomer

In

BigInt

8

@ToCustomer

In

BigInt

8

@POS

In

BigInt

8

@Till

In

BigInt

8

@SalesPerson

In

BigInt

8

@IncludeLayAway

In

VarChar

1

@IncludeRefund

In

VarChar

1

@ViewTopN

In

VarChar

1

@ViewTopNBy

In

VarChar

1

@ViewTopNSubBy

In

VarChar

1

@TopN

In

Integer

4

@Debug

In

VarChar

1

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[RptSalesTransactionByItemAndItemGroup]

     (

      @Store   varchar(5000) = '',

      @FromDate   varchar   = '19000101',

      @ToDate   varchar   = '99991231',

      @FromProductGroup bigint   = 0,

      @ToProductGroup bigint   = 99999999999999,

      @FromProduct bigint   = 0,

      @ToProduct   bigint   = 99999999999999,

      @FromCustomerGroup bigint   = 0,

      @ToCustomerGroup bigint   = 99999999999999,

      @FromCustomer bigint   = 0,

      @ToCustomer   bigint   = 99999999999999,

      @POS   bigint   = -1,

      @Till   bigint   = -1,

      @SalesPerson bigint   = -1,

      @IncludeLayAway char(1)   = 'Y',

      @IncludeRefund char(1)   = 'Y',

      @ViewTopN   char(1)   = 'N',

      @ViewTopNBy   Char(1)   = '', --Pass 'Q' For Top N By Quantity, 'V' For Top N By Value

      @ViewTopNSubBy Char(1)   = '', --Pass 'C' For Top N By Customer

      @TopN   int   = -1,

      @Debug   char(1)   = 'N'

     )

As

Begin

/*

RtlPOS

--LayAway Cancellation

--Item Category

--Top N Reports

--By Value

--By Qty

--Delivery Reports

 Declare @Store    varchar(5000),

   @FromDate   varchar,

   @ToDate    varchar,

   @FromProductGroup bigint,

   @ToProductGroup  bigint,

   @FromProduct  bigint,

   @ToProduct   bigint,

   @FromCustomerGroup bigint,

   @ToCustomerGroup bigint,

   @FromCustomer  bigint,

   @ToCustomer   bigint,

   @POS    bigint,

   @Till    bigint,

   @SalesPerson  bigint,

   @IncludeLayAway  char(1),

   @IncludeRefund  char(1),

   @ViewTopN   char(1),

   @ViewTopNBy   Char(1),

   @ViewTopNSubBy  Char(1)

   @TopN    int,

   @Debug    char(1)

 Exec RptSalesTransactionByItemAndItemGroup

   @Store    = '',

   @FromDate   = '19000101',

   @ToDate    = '99991231',

   @FromProductGroup = 0,

   @ToProductGroup  = 99999999999999,

   @FromProduct  = 0,

   @ToProduct   = 99999999999999,

   @FromCustomerGroup = 0,

   @ToCustomerGroup = 99999999999999,

   @FromCustomer  = 0,

   @ToCustomer   = 99999999999999,

   @POS    = -1,

   @Till    = -1,

   @SalesPerson  = -1,

   @IncludeLayAway  = 'Y',

   @IncludeRefund  = 'Y',

   @ViewTopN   = 'N',

   @ViewTopNBy   = '', --Pass 'Q' For Top N By Quantity, 'V' For Top N By Value

   @ViewTopNSubBy  = '', --Pass 'C' For Top N By Customer

   @TopN    = -1,

   @Debug    = 'N'

*/

Set NoCount On

Declare @SQLStr varchar(8000)

--########################################################################################

--Put stores in a temp table

Create Table #tempStore

(

 StoreKey bigint,

 Id   nvarchar(40)

)

If @Store = ''

Begin

Insert Into #tempStore Select StoreKey, Id From RtlStore

End

Else

Begin

Set @Store = @Store + ',';

Insert Into #tempStore Select StoreKey, Id From RtlStore Where CharIndex(Cast(StoreKey As Varchar) + ',', @Store) > 0

End

If @Debug = 'Y'

Begin

Select 'Store List'

Select * From #tempStore

--Return

End

--Put POS in a temp table

Create Table #tempPOS

(

 POSKey bigint

)

If (@POS = -1)

Begin

Insert Into #tempPOS Select POSKey From RtlPOS

End

Else

Begin

Insert Into #tempPOS Values(@POS)

End

If @Debug = 'Y'

Begin

Select 'POS List'

Select * From #tempPOS

--Return

End

--Put TILL in a temp table

Create Table #tempTill

(

 TillKey bigint

)

If (@Till = -1)

Begin

Insert Into #tempTill Select TillKey From TilTill

End

Else

Begin

Insert Into #tempTill Values(@Till)

End

If @Debug = 'Y'

Begin

Select 'TILL List'

Select * From #tempTill

--Return

End

--Put Users in a temp table

Create Table #tempSalesPerson

(

 UserKey bigint

)

If (@SalesPerson = -1)

Begin

Insert Into #tempSalesPerson Select UserKey From LbrUser

End

Else

Begin

Insert Into #tempSalesPerson Values(@SalesPerson)

End

If @Debug = 'Y'

Begin

Select 'Sales Person List'

Select * From #tempSalesPerson

--Return

End

--########################################################################################

--Transaction Sale Item

Select 'Store'   = Store.Id

  ,'TransactionType' = 'Sale   '

  ,'TransactionNo' = Trxn.TransactionId

  ,'TransactionDate' = Convert(Varchar, Trxn.Created, 107)

  ,'ItemGroup' = ProdGrp.Id

  ,'ItemGroupDesc' = ProdGrp.Description

  ,'Item'   = Prod.Id

  ,'ItemDescription' = TrxnSale.Description

  ,'CustGroup' = CustGrp.Id

  ,'CustGroupDesc' = CustGrp.Description

  ,'Cust'   = Cust.Id

  ,'CustFirstName' = Cust.FirstName

  ,'CustMiddleName' = Cust.MiddleName

  ,'CustLastName' = Cust.LastName

  ,'Quantity'   = TrxnSale.Quantity

  ,'Price'   = TrxnSale.Price

  ,'Discount'   = TrxnSale.DiscountAmount

  ,'Tax'   = TrxnSale.Tax

  ,'TotalAmount' = TrxnSale.TotalPostSaleDiscount

  ,'CreatedBy' = Trxn.CreatedBy

Into #temp

From TrxTransaction As Trxn Inner Join TrxTransactionSaleItem As TrxnSale On Trxn.TransactionKey = TrxnSale.TransactionKey

        Inner Join InvProduct As Prod On TrxnSale.ProductKey = Prod.ProductKey

        Inner Join InvProductGroup As ProdGrp On Prod.ProductGroupKey = ProdGrp.ProductGroupKey

        Inner Join CusCustomer As Cust On Trxn.CustomerKey = Cust.CustomerKey

        Inner Join CusCustomerGroup As CustGrp On Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

        Inner Join #tempStore As Store On Trxn.StoreKey = Store.StoreKey

        Inner Join #tempSalesPerson As SalesPerson On Trxn.UserKey = SalesPerson.UserKey

        Inner Join #tempPOS As POS On Trxn.POSKey = POS.POSKey

        --Inner Join #tempTill As Till On Trxn.TillKey = Till.TillKey

Where Convert(Varchar, Trxn.Created, 112) >= @FromDate

And Convert(Varchar, Trxn.Created, 112) <= @ToDate

And  ProdGrp.ProductGroupKey >= @FromProductGroup

And  ProdGrp.ProductGroupKey <= @ToProductGroup

And  Prod.ProductKey >= @FromProduct

And  Prod.ProductKey <= @ToProduct

And  CustGrp.CustomerGroupKey >= @FromCustomerGroup

And  CustGrp.CustomerGroupKey <= @ToCustomerGroup

And  Cust.CustomerKey >= @FromCustomer

And  Cust.CustomerKey <= @ToCustomer

And  (Type = 0 Or Type = 3)   --0 - Sale, 3-Delivery Against Special Order

--########################################################################################

--########################################################################################

--Transaction Refund Item

If @IncludeRefund = 'Y'

Begin

Insert Into #temp

Select 'Store'   = Store.Id

   ,'TransactionType' = 'Sale   '

   ,'TransactionNo' = Trxn.TransactionId

   ,'TransactionDate' = Convert(Varchar, Trxn.Created, 107)

   ,'ItemGroup' = ProdGrp.Id

   ,'ItemGroupDesc' = ProdGrp.Description

   ,'Item'   = Prod.Id

   ,'ItemDescription' = TrxnSale.Description

   ,'CustGroup' = CustGrp.Id

   ,'CustGroupDesc' = CustGrp.Description

   ,'Cust'   = Cust.Id

   ,'CustFirstName' = Cust.FirstName

   ,'CustMiddleName' = Cust.MiddleName

   ,'CustLastName' = Cust.LastName

   ,'Quantity'   = TrxnSale.Quantity

   ,'Price'   = TrxnSale.Price

   ,'Discount'   = TrxnSale.DiscountAmount

   ,'Tax'   = TrxnSale.Tax

   ,'TotalAmount' = TrxnSale.TotalPostSaleDiscount

   ,'CreatedBy' = Trxn.CreatedBy

From TrxTransaction As Trxn Inner Join TrxTransactionSaleItem As TrxnSale On Trxn.TransactionKey = TrxnSale.TransactionKey

        Inner Join InvProduct As Prod On TrxnSale.ProductKey = Prod.ProductKey

        Inner Join InvProductGroup As ProdGrp On Prod.ProductGroupKey = ProdGrp.ProductGroupKey

        Inner Join CusCustomer As Cust On Trxn.CustomerKey = Cust.CustomerKey

        Inner Join CusCustomerGroup As CustGrp On Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

        Inner Join #tempStore As Store On Trxn.StoreKey = Store.StoreKey

        Inner Join #tempSalesPerson As SalesPerson On Trxn.UserKey = SalesPerson.UserKey

        Inner Join #tempPOS As POS On Trxn.POSKey = POS.POSKey

        --Inner Join #tempTill As Till On Trxn.TillKey = Till.TillKey

Where Convert(Varchar, Trxn.Created, 112) >= @FromDate

And Convert(Varchar, Trxn.Created, 112) <= @ToDate

And  ProdGrp.ProductGroupKey >= @FromProductGroup

And  ProdGrp.ProductGroupKey <= @ToProductGroup

And  Prod.ProductKey >= @FromProduct

And  Prod.ProductKey <= @ToProduct

And  CustGrp.CustomerGroupKey >= @FromCustomerGroup

And  CustGrp.CustomerGroupKey <= @ToCustomerGroup

And  Cust.CustomerKey >= @FromCustomer

And  Cust.CustomerKey <= @ToCustomer

And  Type = 1   --1 - Refund

End

--########################################################################################

--########################################################################################

--Transaction LayAway Item

If @IncludeLayAway = 'Y'

Begin

Insert Into #temp

Select 'Store'   = Store.Id

   ,'TransactionType' = 'LayAway'

   ,'TransactionNo' = Trxn.TransactionId

   ,'TransactionDate' = Convert(Varchar, Trxn.Created, 107)

   ,'ItemGroup' = ProdGrp.Id

   ,'ItemGroupDesc' = ProdGrp.Description

   ,'Item'   = Prod.Id

   ,'ItemDescription' = LayAway.Description

   ,'CustGroup' = CustGrp.Id

   ,'CustGroupDesc' = CustGrp.Description

   ,'Cust'   = Cust.Id

   ,'CustFirstName' = Cust.FirstName

   ,'CustMiddleName' = Cust.MiddleName

   ,'CustLastName' = Cust.LastName

   ,'Quantity'   = LayAway.Quantity

   ,'Price'   = LayAway.Price

   ,'Discount'   = LayAway.DiscountAmount

   ,'Tax'   = LayAway.Tax

   ,'TotalAmount' = LayAway.TotalPostSaleDiscount

   ,'CreatedBy' = Trxn.CreatedBy

From TrxTransaction As Trxn Inner Join TrxTransactionLayAway As LayAway On Trxn.TransactionKey = LayAway.TransactionKey

        Inner Join InvProduct As Prod On LayAway.ProductKey = Prod.ProductKey

        Inner Join InvProductGroup As ProdGrp On Prod.ProductGroupKey = ProdGrp.ProductGroupKey

        Inner Join CusCustomer As Cust On Trxn.CustomerKey = Cust.CustomerKey

        Inner Join CusCustomerGroup As CustGrp On Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

        Inner Join #tempStore As Store On Trxn.StoreKey = Store.StoreKey

        Inner Join #tempSalesPerson As SalesPerson On Trxn.UserKey = SalesPerson.UserKey

        Inner Join #tempPOS As POS On Trxn.POSKey = POS.POSKey

        --Inner Join #tempTill As Till On Trxn.TillKey = Till.TillKey

Where Convert(Varchar, Trxn.Created, 112) >= @FromDate

And Convert(Varchar, Trxn.Created, 112) <= @ToDate

And  ProdGrp.ProductGroupKey >= @FromProductGroup

And  ProdGrp.ProductGroupKey <= @ToProductGroup

And  Prod.ProductKey >= @FromProduct

And  Prod.ProductKey <= @ToProduct

And  CustGrp.CustomerGroupKey >= @FromCustomerGroup

And  CustGrp.CustomerGroupKey <= @ToCustomerGroup

And  Cust.CustomerKey >= @FromCustomer

And  Cust.CustomerKey <= @ToCustomer

End

--########################################################################################

If @ViewTopN = 'N'

Begin

Select * From #temp Order By Store, TransactionNo, TransactionType

End

Else If @ViewTopN = 'Y'

Begin

If @ViewTopNBy = 'Q'

Begin

  If (@ViewTopNSubBy = '')

  Begin

  Select @SQLStr ='Select Top ' + Cast(@TopN As varchar) + ' Store, ItemGroup, Item, ItemDescription, Sum(Quantity) As Quantity, Sum(Price) As Price, Sum(Discount) As Discount, Sum(Tax) As Tax, Sum(TotalAmount) As TotalAmount From #temp Group By Store, ItemGroup, Item, ItemDescription  Order By  Quantity Desc'

  End

  Else If (@ViewTopNSubBy = 'C')

  Begin

  Select @SQLStr ='Select Top ' + Cast(@TopN As varchar) + ' Store, Cust, CustFirstName, CustMiddleName, CustLastName, Sum(Quantity) As Quantity, Sum(Price) As Price, Sum(Discount) As Discount, Sum(Tax) As Tax, Sum(TotalAmount) As TotalAmount From #temp Group By Store, Cust, CustFirstName, CustMiddleName, CustLastName  Order By  Quantity Desc'

  End

End

If @ViewTopNBy = 'V'

Begin

  If (@ViewTopNSubBy = '')

  Begin

  Select @SQLStr ='Select Top ' + Cast(@TopN As varchar) + ' Store, ItemGroup, Item, ItemDescription, Sum(Quantity) As Quantity, Sum(Price) As Price, Sum(Discount) As Discount, Sum(Tax) As Tax, Sum(TotalAmount) As TotalAmount From #temp Group By Store, ItemGroup, Item, ItemDescription Order By  TotalAmount Desc'

  End

  Else If (@ViewTopNSubBy = 'C')

  Begin

  Select @SQLStr ='Select Top ' + Cast(@TopN As varchar) + ' Store, Cust, CustFirstName, CustMiddleName, CustLastName, Sum(Quantity) As Quantity, Sum(Price) As Price, Sum(Discount) As Discount, Sum(Tax) As Tax, Sum(TotalAmount) As TotalAmount From #temp Group By Store, Cust, CustFirstName, CustMiddleName, CustLastName Order By  TotalAmount Desc'

  End

End

Exec (@SQLStr)

End

Set NoCount Off

End