iVend Database Database : rptSalesTransactionData User Defined Function

Creation Date

7/30/2019 7:05 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

VarChar

8

@ToDate

In

To Date (Part of Date Range in YYYYMMDD Format)

VarChar

8

@ProductGroup

In

Product Group (Prefixed AND Postfixed with '[^]' character )

VarWChar

4000

@FromProduct

In

From Product (Part of Product Range )

VarWChar

100

@ToProduct

In

To Product (Part of Product Range )

VarWChar

100

@CustomerGroup

In

Customer Group (Prefixed AND Postfixed with '[^]' character)

VarWChar

4000

@FromCustomer

In

From Customer (Part of Customer Range )

VarWChar

40

@ToCustomer

In

To Customer (Part of Customer Range )

VarWChar

40

@Store

In

Prefixed AND Postfixed with '[^]' character

VarWChar

4000

@Subsidiary

In

Prefixed AND Postfixed with '[^]' character

VarWChar

4000

@POS

In

Prefixed AND Postfixed with '[^]' character

VarWChar

4000

@Till

In

Prefixed AND Postfixed with '[^]' character

VarWChar

4000

@SalesPerson

In

Prefixed AND Postfixed with '[^]' character

VarWChar

4000

@IncludeLayAway

In

Specify Whether this is LayAway Report

Boolean

1

@IncludeRefund

In

Specify Whether this is Sales Report (Refund)

Boolean

1

@IncludeSale

In

Specify Whether this is Sales Report

Boolean

1

@IncludeOrder

In

Specify Whether this is Order Report

Boolean

1

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

CfgAddress

Table

Defines the address details for each Address.

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

CusCustomerAddress

Table

Defines the address details for each customer

1

CusCustomerGroup

Table

Defines the groups in which the customers can be catagorised.

1

fnSplitValues

User Defined Function

1

InvProduct

Table

Stores the product related details.

1

InvProductGroup

Table

Defined the various groups under which the products can be categorised.

1

InvUOM

Table

Master table for the Inv UOM

1

InvUOMGroupDetail

Table

Stores the details for the UOM group

1

LbrUser

Table

Defines a list of all employees/ labor users created in the system

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

RtlPOS

Table

Contains details about all POS 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

TilTill

Table

Till is defined at the MC and attached with the cash drawer at POS. At a time only one till can be associated with the POS. It is the virtual entity in which we defined some settings (like setting the mode in which the till works) that will be applicable at the POS (for which the till is attached).

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

----------------------------------------------

---

--DECLARE @FromDate Varchar(8)='20151001', @ToDate Varchar(8)='20160131', @ProductGroup Varchar(200)='', @FromProduct Varchar(200)='', @ToProduct Varchar(200)='', @Subsidiary Varchar(200)='',

--@CustomerGroup Varchar(200)='', @FromCustomer Varchar(200)='', @ToCustomer Varchar(200)='', @SalesPerson Varchar(200)='', @Store Varchar(200)='', @POS Varchar(200)='', @Till Varchar(200)=''

--select * from  dbo.rptSalesTransactionData_07042017('','','','','','','','','','','','','','',1,0,1,1)

CREATE Function [dbo].[rptSalesTransactionData]

     (

      @FromDate   VARCHAR(8),

      @ToDate   VARCHAR(8),

      @ProductGroup NVARCHAR(4000),

      @FromProduct NVARCHAR(100),

      @ToProduct   NVARCHAR(100),

      @CustomerGroup NVARCHAR(4000),

      @FromCustomer NVARCHAR(40),

      @ToCustomer   NVARCHAR(40),

      @Store   NVARCHAR(4000),

      @Subsidiary   NVARCHAR(4000),

      @POS   NVARCHAR(4000),

      @Till   NVARCHAR(4000),

      @SalesPerson NVARCHAR(4000),

      @IncludeLayAway BIT,

      @IncludeRefund BIT,

      @IncludeSale BIT,

      @IncludeOrder BIT

     )

Returns @temp Table

(

 StoreId     NVARCHAR(40)

 ,StoreDesc     NVARCHAR(200)

 ,POSId     NVARCHAR(40)

 ,POSDesc     NVARCHAR(200)

 ,TillId     NVARCHAR(100)

 ,TillDesc     NVARCHAR(200)

 ,TransactionType   NVARCHAR(100)

 ,TransactionId   NVARCHAR(100)

 ,TransactionKey   NVARCHAR(200)

 ,TransactionDate   VARCHAR(50)

 ,TransactionTime   VARCHAR(50)

 ,ItemGroup     NVARCHAR(40)

 ,ItemGroupDesc   NVARCHAR(200)

 ,Item     NVARCHAR(100)

 ,ItemDescription   NVARCHAR(200)

 ,CustGroup     NVARCHAR(40)

 ,CustGroupDesc   NVARCHAR(200)

 ,Cust     NVARCHAR(40)

 ,CustFirstName   NVARCHAR(200)

 ,CustMiddleName   NVARCHAR(100)

 ,CustLastName   NVARCHAR(80)

 ,CustAddress1   NVARCHAR(200)

 ,CustAddress2   NVARCHAR(200)

 ,CustAddress3   NVARCHAR(200)

 ,CustCity     NVARCHAR(200)

 ,CustZip     NVARCHAR(60)

 ,CustPhone     NVARCHAR(40)

 ,Quantity     DECIMAL(20, 5)

 ,UOMQuantity   DECIMAL(20, 5)

 ,UOM     VARCHAR(200)

 ,OpenQuantity   DECIMAL(20, 5)

 ,FullfilledQty   DECIMAL(20, 5)

 ,Price     DECIMAL(20, 5)

 ,Discount     DECIMAL(20, 5)

 ,Surcharge     DECIMAL(20, 5)

 ,Tax     DECIMAL(20, 5)

 ,TotalAmount   DECIMAL(20, 5)

 ,SalesPersonId   NVARCHAR(100)

 ,SalesPersonName   NVARCHAR(280)

 ,Created     DATETIME

 ,OriginalDetailKey   NVARCHAR(100)

 ,Status     SMALLINT

 ,LayAwayPlanKey   NVARCHAR(100)

 ,InstallmentCount   SMALLINT

 ,SalesPersonKey   NVARCHAR(100)

 ,SalesPercentage   DECIMAL(20, 5)

 ,BusinessDate   DateTime

 ,CultureInfo   NVARCHAR(80)

 ,SellRate     DECIMAL(20, 5)

 ,Subsidiary     NVARCHAR(40)

 ,SubsidiaryDescription NVARCHAR(200)

 ,CurrencyKey   NVARCHAR(100)

 ,PriceEC     DECIMAL(38, 7)

 ,DiscountEC     DECIMAL(38, 7)

 ,SurchargeEC   DECIMAL(20, 5)

 ,TaxEC     DECIMAL(38, 7)

 ,TotalAmountEC   DECIMAL(38, 7)

 ,BaseCultureInfo   NVARCHAR(80)

)

AS

Begin

If @IncludeSale = 1

Begin

--Transaction Sale Item

Insert @temp

SELECT

  'StoreId' = RtlStore.Id,

  'StoreDesc' = RtlStore.Description,

  'POSId' = RtlPOS.Id,

  'POSDesc' = RtlPOS.Description,

  'TillId' = TilTill.Id,

  'TillDesc' = TilTill.Description,

  'TransactionType' = 'Sale',

  'TransactionId' = Trxn.TransactionId,

  'TransactionKey' = Trxn.TransactionKey,

  'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107),

  'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108),

  '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,

  'CustAddress1' = Addr.Address1,

  'CustAddress2' = Addr.Address2,

  'CustAddress3' = Addr.Address3,

  'CustCity' = Addr.City,

  'CustZip' = Addr.ZipCode,

  'CustPhone' = Cust.PhoneNumber,

  'Quantity' = TrxnSale.Quantity,

  'UOMQuantity' = TrxnSale.Quantity/NULLIF(ISNULL(TrxnSale.UOMBaseQuantity,1),0),

  'UOM' = ISNULL(UOM.Description,''),

  'OpenQuantity' = -1,

  'FullfilledQty' = -1,

  'Price' = TrxnSale.Price,

  'Discount' = (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)),

  'Surcharge' = -1,

  'Tax' = TrxnSale.Tax,

  'TotalAmount' = TrxnSale.TotalPostSaleDiscount,

  'SalesPersonId' = ISNULL(LbrUser.Id,''),

  'SalesPersonName' = ISNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, ''),

  'Created' = Trxn.Created,

  'OriginalDetailKey' = -1,

  'Status' = -1,

  'LayAwayPlanKey' = -1,

  'InstallmentCount' = -1,

  'SalesPersonKey' = 0,

  'SalesPercentage' = 0,

  'BusinessDate' = Trxn.BusinessDate,

  'CultureInfo' = CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END,

  'SellRate' = Trxn.ExchangeRate,

  'Subsidiary' = Sub.Id,

  'SubsidiaryDescription' = Sub.Description,

  'CurrencyKey' = Trxn.CurrencyKey,

  'PriceEC' = TrxnSale.PriceEC,

  'DiscountEC' = (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0),

  'Surcharge' = -1*Trxn.ExchangeRate,

  'TaxEC' = TrxnSale.TaxEC,

  'TotalAmountEC' = TrxnSale.TotalPostSaleDiscountEC,

  'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem AS TrxnSale WITH (NOLOCK) ON Trxn.TransactionKey = TrxnSale.TransactionKey

  Left JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) on TrxnSale.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnSale.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

  LEFT OUTER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  LEFT OUTER JOIN (Select CustAdd.CustomerKey, CustAdd.AddressKey From CusCustomerAddress AS CustAdd With(NOLOCK)

  WHERE CustAdd.AddressType = 0 AND CustAdd.IsDefault = 1) AS CustAdd ON Cust.CustomerKey = CustAdd.CustomerKey

  LEFT OUTER JOIN CfgAddress AS Addr WITH (NOLOCK) ON CustAdd.AddressKey = Addr.AddressKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

  INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

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

WHERE Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND (TrxnSale.Type = 0 Or TrxnSale.Type = 3)           --0 - Sale, 3-Delivery Against Special Order

  AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE Convert(DateTime, @FromDate, 112) END

  AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE Convert(DateTime, @ToDate, 112) END                                                                         --Date Filter

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

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

  AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

            OR

   (Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END))   --Product Filter

  --AND CHARINDEX(CustGrp.Id, @CustomerGroup) > CASE WHEN @CustomerGroup <> '' THEN  0 ELSE -1 END                             --Customer Group Filter

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

  AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

            OR

   (Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END))   --Customer Filter

  --AND CHARINDEX(LbrUser.Id, @SalesPerson) > CASE WHEN @SalesPerson <> '' THEN  0 ELSE -1 END                    --Sales Person Filter

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

  --AND CHARINDEX(RtlStore.Id,  @Store) > CASE WHEN @Store <> '' THEN  0 ELSE -1 END                      --Store Filter

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

  --AND CHARINDEX(RtlPOS.Id, @POS) > CASE WHEN @POS <> '' THEN  0 ELSE -1 END                        --POS Filter

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

  --AND CHARINDEX(TilTill.Id, @Till) > CASE WHEN @Till <> '' THEN  0 ELSE -1 END                       --Till Filter

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

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

END

----Transaction Refund Item

If @IncludeRefund = 1

Begin

Insert @temp

SELECT 'StoreId'   = RtlStore.Id

   ,'StoreDesc' = RtlStore.Description

   ,'POSId'   = RtlPOS.Id

   ,'POSDesc'   = RtlPOS.Description

   ,'TillId'   = TilTill.Id

   ,'TillDesc'   = TilTill.Description

   ,'TransactionType' = 'Refund'

   ,'TransactionId' = Trxn.TransactionId

   ,'TransactionKey' = Trxn.TransactionKey

   ,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

   ,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

   ,'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

   ,'CustAddress1' = Addr.Address1

   ,'CustAddress2' = Addr.Address2

   ,'CustAddress3' = Addr.Address3

   ,'CustCity'   = Addr.City

   ,'CustZip'   = Addr.ZipCode

   ,'CustPhone' = Cust.PhoneNumber

   ,'Quantity'   = TrxnSale.Quantity * -1

   ,'UOMQuantity' = TrxnSale.Quantity * -1/NULLIF(ISNULL(TrxnSale.UOMBaseQuantity,1),0)

   ,'UOM'   = ISNULL(UOM.Description,'')

   ,'OpenQuantity' =   -1

   ,'FullfilledQty' =   -1

   ,'Price'   = TrxnSale.Price * -1

   ,'Discount'   = (ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0)) * -1

   ,'Surcharge' = -1

   ,'Tax'   = TrxnSale.Tax * -1

   ,'TotalAmount' = TrxnSale.TotalPostSaleDiscount * -1

   ,'SalesPersonId' = ISNULL(LbrUser.Id,'')

   ,'SalesPersonName' = isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '')

   ,'Created' = Trxn.Created

   ,'OriginalDetailKey'= -1

   ,'Status'   = -1

   ,'LayAwayPlanKey' = -1

   ,'InstallmentCount' = -1

   ,'SalesPersonKey' = 0

   ,'SalesPercentage' = 0

   ,'BusinessDate' = Trxn.BusinessDate

   ,'CultureInfo' = CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END

   ,'SellRate' = Trxn.ExchangeRate

   ,'Subsidiary' = Sub.Id

   ,'SubsidiaryDescription' = Sub.Description

   ,'CurrencyKey' = Trxn.CurrencyKey

   ,'PriceEC' = TrxnSale.PriceEC

   ,'DiscountEC' = -1*(ISNULL(TrxnSale.ManualDiscountAmount,0)+ISNULL(TrxnSale.CouponDiscountAmount,0)+ISNULL(TrxnSale.PromotionalDiscountAmount,0)+ISNULL(TrxnSale.SaleDiscountAmount,0)+ISNULL(TrxnSale.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)

   ,'Surcharge' = -1*Trxn.ExchangeRate

   ,'TaxEC' = -1*TrxnSale.TaxEC

   ,'TotalAmountEC' = -1*TrxnSale.TotalPostSaleDiscountEC

   ,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionSaleItem AS TrxnSale WITH (NOLOCK) ON Trxn.TransactionKey = TrxnSale.TransactionKey

  Left JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) ON TrxnSale.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) ON UOMD.UOMKey=UOM.UOMKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnSale.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

  INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  LEFT OUTER JOIN (Select CustAdd.CustomerKey, CustAdd.AddressKey From CusCustomerAddress AS CustAdd With(NOLOCK)

  where CustAdd.AddressType = 0 AND CustAdd.IsDefault = 1) AS CustAdd ON Cust.CustomerKey = CustAdd.CustomerKey

  LEFT OUTER JOIN CfgAddress AS Addr WITH (NOLOCK) ON CustAdd.AddressKey = Addr.AddressKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

  INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

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

Where TrxnSale.Type = 1 AND Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0

  AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE Convert(DateTime, @FromDate, 112) END AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE Convert(DateTime, @ToDate, 112) END --Date Filter                          --Date Filter

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

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

  AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

            OR

   (Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END))   --Product Filter

  --AND CHARINDEX(CustGrp.Id, @CustomerGroup) > CASE WHEN @CustomerGroup <> '' THEN  0 ELSE -1 END                             --Customer Group Filter

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

  AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

            OR

   (Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END))   --Customer Filter

  --AND CHARINDEX(LbrUser.Id, @SalesPerson) > CASE WHEN @SalesPerson <> '' THEN  0 ELSE -1 END                    --Sales Person Filter

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

  --AND CHARINDEX(RtlStore.Id,  @Store) > CASE WHEN @Store <> '' THEN  0 ELSE -1 END                      --Store Filter

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

  --AND CHARINDEX(RtlPOS.Id, @POS) > CASE WHEN @POS <> '' THEN  0 ELSE -1 END                        --POS Filter

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

  --AND CHARINDEX(TilTill.Id, @Till) > CASE WHEN @Till <> '' THEN  0 ELSE -1 END                       --Till Filter

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

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

END

--Transaction LayAway Item

If @IncludeLayAway = 1

Begin

Insert @temp

SELECT 'StoreId'   = RtlStore.Id

   ,'StoreDesc' = RtlStore.Description

   ,'POSId'   = RtlPOS.Id

   ,'POSDesc'   = RtlPOS.Description

   ,'TillId'   = TilTill.Id

   ,'TillDesc'   = TilTill.Description

   ,'TransactionType' = 'Layaway'

   ,'TransactionId' = Trxn.TransactionId

   ,'TransactionKey' = Trxn.TransactionKey

   ,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

   ,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

   ,'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

   ,'CustAddress1' = Addr.Address1

   ,'CustAddress2' = Addr.Address2

   ,'CustAddress3' = Addr.Address3

   ,'CustCity'   = Addr.City

   ,'CustZip'   = Addr.ZipCode

   ,'CustPhone' = Cust.PhoneNumber

   ,'Quantity'   = LayAway.Quantity

   ,'UOMQuantity' = LayAway.Quantity / NULLIF(ISNULL(Layaway.UomBaseQuantity,0),0)

   ,'UOM'   = ISNULL(UOM.Description,'')

   ,'OpenQuantity' =   -1

   ,'FullfilledQty' =   -1

   ,'Price'   = LayAway.Price

   ,'Discount'   = (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))

   ,'Surcharge' = -1

   ,'Tax'   = LayAway.Tax

   ,'TotalAmount' = LayAway.TotalPostSaleDiscount

   ,'SalesPersonId' = ISNULL(LbrUser.Id,'')

   ,'SalesPersonName' = isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '')

   ,'Created'   = Trxn.Created

   ,'OriginalDetailKey'= LayAway.OriginalDetailKey

   ,'Status'   = LayAway.Status

   ,'LayAwayPlanKey' = LayAway.LayawayPlanKey

   ,'InstallmentCount' = LayAway.InstallmentCount

   ,'SalesPersonKey' = 0

   ,'SalesPercentage' = 0

   ,'BusinessDate' = Trxn.BusinessDate

   ,'CultureInfo' = CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END

   ,'ExchangeRate' = Trxn.ExchangeRate

   ,'Subsidiary' = Sub.Id

   ,'SubsidiaryDescription' = Sub.Description

   ,'CurrencyKey' = Trxn.CurrencyKey

   ,'PriceEC' = LayAway.PriceEC

   ,'DiscountEC' = (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)

   ,'Surcharge' = -1*Trxn.ExchangeRate

   ,'TaxEC' = LayAway.TaxEC

   ,'TotalAmountEC' = LayAway.TotalPostSaleDiscountEC

   ,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionLayAway AS LayAway WITH (NOLOCK) ON Trxn.TransactionKey = LayAway.TransactionKey

  Left JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) on LayAway.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON LayAway.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

  INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  LEFT OUTER JOIN (Select CustAdd.CustomerKey, CustAdd.AddressKey From CusCustomerAddress AS CustAdd With(NOLOCK)

  where CustAdd.AddressType = 0 AND CustAdd.IsDefault = 1) AS CustAdd ON Cust.CustomerKey = CustAdd.CustomerKey

  Left Outer Join CfgAddress AS Addr WITH (NOLOCK) ON CustAdd.AddressKey = Addr.AddressKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

  INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

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

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND LayAway.[Type] = 0 AND LayAway.Status<>2

  AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @FromDate,112) END AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @ToDate, 112) END --Date Filter                          --Date Filter

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

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

  AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

            OR

   (Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END))   --Product Filter

  --AND CHARINDEX(CustGrp.Id, @CustomerGroup) > CASE WHEN @CustomerGroup <> '' THEN  0 ELSE -1 END                             --Customer Group Filter

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

  AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

            OR

   (Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END))   --Customer Filter

  --AND CHARINDEX(LbrUser.Id, @SalesPerson) > CASE WHEN @SalesPerson <> '' THEN  0 ELSE -1 END                    --Sales Person Filter

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

  --AND CHARINDEX(RtlStore.Id,  @Store) > CASE WHEN @Store <> '' THEN  0 ELSE -1 END                      --Store Filter

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

  --AND CHARINDEX(RtlPOS.Id, @POS) > CASE WHEN @POS <> '' THEN  0 ELSE -1 END                        --POS Filter

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

  --AND CHARINDEX(TilTill.Id, @Till) > CASE WHEN @Till <> '' THEN  0 ELSE -1 END                       --Till Filter

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

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

UNION ALL

SELECT 'StoreId'   = RtlStore.Id

   ,'StoreDesc' = RtlStore.Description

   ,'POSId'   = RtlPOS.Id

   ,'POSDesc'   = RtlPOS.Description

   ,'TillId'   = TilTill.Id

   ,'TillDesc'   = TilTill.Description

   ,'TransactionType' = 'Layaway Cancel'

   ,'TransactionId' = Trxn.TransactionId

   ,'TransactionKey' = Trxn.TransactionKey

   ,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

   ,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

   ,'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

   ,'CustAddress1' = Addr.Address1

   ,'CustAddress2' = Addr.Address2

   ,'CustAddress3' = Addr.Address3

   ,'CustCity'   = Addr.City

   ,'CustZip'   = Addr.ZipCode

   ,'CustPhone' = Cust.PhoneNumber

   ,'Quantity'   = -1*LayAway.Quantity

   ,'UOMQuantity' = -1*LayAway.Quantity/ NULLIF(ISNULL(Layaway.UomBaseQuantity,0),0)

   ,'UOM'   = ISNULL(UOM.Description,'')

   ,'OpenQuantity' =   -1

   ,'FullfilledQty' =   -1

   ,'Price'   = LayAway.Price

   ,'Discount'   = (ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))

   ,'Surcharge' = -1*1

   ,'Tax'   = -1*LayAway.Tax

   ,'TotalAmount' = -1*LayAway.TotalPostSaleDiscount

   ,'SalesPersonId' = ISNULL(LbrUser.Id,'')

   ,'SalesPersonName' = isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '')

   ,'Created'   = Trxn.Created

   ,'OriginalDetailKey'= LayAway.OriginalDetailKey

   ,'Status'   = LayAway.Status

   ,'LayAwayPlanKey' = LayAway.LayawayPlanKey

   ,'InstallmentCount' = LayAway.InstallmentCount

   ,'SalesPersonKey' = 0

   ,'SalesPercentage' = 0

   ,'BusinessDate' = Trxn.BusinessDate

   ,'CultureInfo' = CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END

   ,'ExchangeRate' = Trxn.ExchangeRate

   ,'Subsidiary' = Sub.Id

   ,'SubsidiaryDescription' = Sub.Description

   ,'CurrencyKey' = Trxn.CurrencyKey

   ,'PriceEC' = LayAway.PriceEC

   ,'DiscountEC' = -1*(ISNULL(LayAway.ManualDiscountAmount,0)+ISNULL(LayAway.CouponDiscountAmount,0)+ISNULL(LayAway.PromotionalDiscountAmount,0)+ISNULL(LayAway.SaleDiscountAmount,0)+ISNULL(LayAway.SystemDiscountAmount,0))*NULLIF(ISNULL(Trxn.ExchangeRate,1),0)

   ,'Surcharge' = -1*Trxn.ExchangeRate

   ,'TaxEC' = -1*LayAway.TaxEC

   ,'TotalAmountEC' = -1*LayAway.TotalPostSaleDiscountEC

   ,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionLayAway AS LayAway WITH (NOLOCK) ON Trxn.TransactionKey = LayAway.TransactionKey

  Left JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) on LayAway.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON LayAway.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

  INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  LEFT OUTER JOIN (Select CustAdd.CustomerKey, CustAdd.AddressKey From CusCustomerAddress AS CustAdd With(NOLOCK)

  where CustAdd.AddressType = 0 AND CustAdd.IsDefault = 1) AS CustAdd ON Cust.CustomerKey = CustAdd.CustomerKey

  Left Outer Join CfgAddress AS Addr WITH (NOLOCK) ON CustAdd.AddressKey = Addr.AddressKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

  INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

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

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND LayAway.[Type] = 0 AND LayAway.Status=2

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

  AND CASE WHEN @ToDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime, @ToDate, 112) END --Date Filter                          --Date Filter

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

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

  AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

            OR

   (Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END))   --Product Filter

  --AND CHARINDEX(CustGrp.Id, @CustomerGroup) > CASE WHEN @CustomerGroup <> '' THEN  0 ELSE -1 END                             --Customer Group Filter

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

  AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

            OR

   (Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END))   --Customer Filter

  --AND CHARINDEX(LbrUser.Id, @SalesPerson) > CASE WHEN @SalesPerson <> '' THEN  0 ELSE -1 END                    --Sales Person Filter

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

  --AND CHARINDEX(RtlStore.Id,  @Store) > CASE WHEN @Store <> '' THEN  0 ELSE -1 END                      --Store Filter

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

  --AND CHARINDEX(RtlPOS.Id, @POS) > CASE WHEN @POS <> '' THEN  0 ELSE -1 END                        --POS Filter

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

  --AND CHARINDEX(TilTill.Id, @Till) > CASE WHEN @Till <> '' THEN  0 ELSE -1 END                       --Till Filter

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

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

END

If @IncludeOrder = 1

Begin

Insert @temp

SELECT 'StoreId'   = RtlStore.Id

   ,'StoreDesc' = RtlStore.Description

   ,'POSId'   = RtlPOS.Id

   ,'POSDesc'   = RtlPOS.Description

   ,'TillId'   = TilTill.Id

   ,'TillDesc'   = TilTill.Description

   ,'TransactionType' = 'Order'

   ,'TransactionId' = Trxn.TransactionId

   ,'TransactionKey' = Trxn.TransactionKey

   ,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

   ,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

   ,'ItemGroup' = ProdGrp.Id

   ,'ItemGroupDesc' = ProdGrp.Description

   ,'Item'   = Prod.Id

   ,'ItemDescription' = TrxnOrder.Description

   ,'CustGroup' = CustGrp.Id

   ,'CustGroupDesc' = CustGrp.Description

   ,'Cust'   = Cust.Id

   ,'CustFirstName' = Cust.FirstName

   ,'CustMiddleName' = Cust.MiddleName

   ,'CustLastName' = Cust.LastName

   ,'CustAddress1' = Addr.Address1

   ,'CustAddress2' = Addr.Address2

   ,'CustAddress3' = Addr.Address3

   ,'CustCity'   = Addr.City

   ,'CustZip'   = Addr.ZipCode

   ,'CustPhone' = Cust.PhoneNumber

   ,'Quantity'   = TrxnOrder.Quantity

   ,'UOMQuantity' = TrxnOrder.Quantity/NULLIF(ISNULL(TrxnOrder.UOMBaseQuantity,1),0)

   ,'UOM'   = ISNULL(UOM.Description,'')

   ,'OpenQuantity' =   TrxnOrder.OpenQuantity

   ,'FullfilledQty' =   TrxnOrder.FullfilledQuantity

   ,'Price'   = TrxnOrder.Price

   ,'Discount'   = (ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0))

   ,'Surcharge' = TrxnOrder.SurchargeTotal

   ,'Tax'   = TrxnOrder.Tax

   ,'TotalAmount' = TrxnOrder.TotalPostSaleDiscount

   ,'SalesPersonId' = ISNULL(LbrUser.Id,'')

   ,'SalesPersonName' = isNull(LbrUser.FirstName, '') + ' ' + isNull(LbrUser.MiddleName, '') + ' ' + isNull(LbrUser.LastName, '')

   ,'Created'   = Trxn.Created

   ,'OriginalDetailKey'= -1

   ,'Status'   = TrxnOrder.Status

   ,'LayAwayPlanKey' = -1

   ,'InstallmentCount' = -1

   ,'SalesPersonKey' = 0

   ,'SalesPercentage' = 0

   ,'BusinessDate' = Trxn.BusinessDate

   ,'CultureInfo' = CASE WHEN (Convert(nvarchar(100),RtlStore.SubsidiaryKey) IS NULL OR Convert(nvarchar(100),RtlStore.SubsidiaryKey)='') THEN (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1) ELSE Sub.CultureInfo END

   ,'ExchangeRate' = Trxn.ExchangeRate

   ,'Subsidiary' = Sub.Id

   ,'SubsidiaryDescription' = Sub.Description

   ,'CurrencyKey' = Trxn.CurrencyKey

   ,'PriceEC'   = TrxnOrder.PriceEC

   ,'DiscountEC' = (ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0))* NULLIF(ISNULL(Trxn.ExchangeRate,1),0)

   ,'Surcharge' = TrxnOrder.SubTotalEC

   ,'TaxEC'   = TrxnOrder.TaxEC

   ,'TotalAmountEC' = TrxnOrder.TotalPostSaleDiscountEC

   ,'BaseCultureInfo' = (Select CultureInfo From PmtCurrency Where IsBaseCurrency=1)

From TrxTransaction AS Trxn WITH (NOLOCK)

  INNER JOIN TrxTransactionOrder AS TrxnOrder WITH (NOLOCK) ON Trxn.TransactionKey = TrxnOrder.TransactionKey

  Left JOIN InvUOMGroupDetail UOMD WITH (NOLOCK) on TrxnOrder.UOMGroupDetailKey=UOMD.UOMGroupDetailKey

  LEFT JOIN InvUOM UOM WITH (NOLOCK) on UOMD.UOMKey=UOM.UOMKey

  INNER JOIN InvProduct AS Prod WITH (NOLOCK) ON TrxnOrder.ProductKey = Prod.ProductKey --AND Prod.IsDeleted = 0

  INNER JOIN InvProductGroup AS ProdGrp WITH (NOLOCK) ON Prod.ProductGroupKey = ProdGrp.ProductGroupKey

  INNER JOIN CusCustomer AS Cust WITH (NOLOCK) ON Trxn.CustomerKey = Cust.CustomerKey

  LEFT OUTER JOIN (Select CustAdd.CustomerKey, CustAdd.AddressKey From CusCustomerAddress AS CustAdd With(NOLOCK)

  where CustAdd.AddressType = 0 AND CustAdd.IsDefault = 1) AS CustAdd ON Cust.CustomerKey = CustAdd.CustomerKey

  Left Outer Join CfgAddress AS Addr WITH (NOLOCK) ON CustAdd.AddressKey = Addr.AddressKey

  INNER JOIN CusCustomerGroup AS CustGrp WITH (NOLOCK) ON Cust.CustomerGroupKey = CustGrp.CustomerGroupKey

  INNER JOIN RtlStore WITH (NOLOCK) ON Trxn.StoreKey = RtlStore.StoreKey

  INNER JOIN LbrUser WITH (NOLOCK) ON Trxn.UserKey = LbrUser.UserKey

  INNER JOIN RtlPOS WITH (NOLOCK) ON Trxn.POSKey = RtlPOS.POSKey

  LEFT OUTER JOIN TilTill WITH (NOLOCK) ON Trxn.TillKey = TilTill.TillKey

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

Where Trxn.IsSuspended = 0 AND Trxn.IsVoided = 0 AND TrxnOrder.Type=0 AND TrxnOrder.Status=0                                           --0 - Sale, 3-Delivery Against Special Order

  AND Trxn.BusinessDate BETWEEN CASE WHEN @FromDate = '' THEN Trxn.BusinessDate ELSE CONVERT(DateTime,@FromDate, 112) END AND CASE WHEN @ToDate = '' THEN  Trxn.BusinessDate ELSE CONVERT(DateTime, @ToDate, 112) END --Date Filter

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

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

  AND ((Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @FromProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @ToProduct END)

            OR

   (Prod.Id BETWEEN CASE WHEN @FromProduct = '' THEN Prod.Id ELSE @ToProduct END AND CASE WHEN @ToProduct = '' THEN Prod.Id ELSE @FromProduct END))   --Product Filter

  --AND CHARINDEX(CustGrp.Id, @CustomerGroup) > CASE WHEN @CustomerGroup <> '' THEN  0 ELSE -1 END                             --Customer Group Filter

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

  AND ((Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @FromCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @ToCustomer END)

            OR

   (Cust.Id BETWEEN CASE WHEN @FromCustomer = '' THEN Cust.Id ELSE @ToCustomer END AND CASE WHEN @ToCustomer = '' THEN Cust.Id ELSE @FromCustomer END))   --Customer Filter

  --AND CHARINDEX(LbrUser.Id, @SalesPerson) > CASE WHEN @SalesPerson <> '' THEN  0 ELSE -1 END                    --Sales Person Filter

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

  --AND CHARINDEX(RtlStore.Id,  @Store) > CASE WHEN @Store <> '' THEN  0 ELSE -1 END                      --Store Filter

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

  --AND CHARINDEX(RtlPOS.Id, @POS) > CASE WHEN @POS <> '' THEN  0 ELSE -1 END                        --POS Filter

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

  --AND CHARINDEX(TilTill.Id, @Till) > CASE WHEN @Till <> '' THEN  0 ELSE -1 END                       --Till Filter

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

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

UNION ALL

SELECT 'StoreId'   = RtlStore.Id

   ,'StoreDesc' = RtlStore.Description

   ,'POSId'   = RtlPOS.Id

   ,'POSDesc'   = RtlPOS.Description

   ,'TillId'   = TilTill.Id

   ,'TillDesc'   = TilTill.Description

   ,'TransactionType' = 'Order'

   ,'TransactionId' = Trxn.TransactionId

   ,'TransactionKey' = Trxn.TransactionKey

   ,'TransactionDate' = CONVERT(VARCHAR, Trxn.Created, 107)

   ,'TransactionTime' = CONVERT(VARCHAR(5), Trxn.Created, 108)

   ,'ItemGroup' = ProdGrp.Id

   ,'ItemGroupDesc' = ProdGrp.Description

   ,'Item'   = Prod.Id

   ,'ItemDescription' = TrxnOrder.Description

   ,'CustGroup' = CustGrp.Id

   ,'CustGroupDesc' = CustGrp.Description

   ,'Cust'   = Cust.Id

   ,'CustFirstName' = Cust.FirstName

   ,'CustMiddleName' = Cust.MiddleName

   ,'CustLastName' = Cust.LastName

   ,'CustAddress1' = Addr.Address1

   ,'CustAddress2' = Addr.Address2

   ,'CustAddress3' = Addr.Address3

   ,'CustCity'   = Addr.City

   ,'CustZip'   = Addr.ZipCode

   ,'CustPhone' = Cust.PhoneNumber

   ,'Quantity'   = -1*TrxnOrder.Quantity

   ,'UOMQuantity' = -1*TrxnOrder.Quantity/NULLIF(ISNULL(TrxnOrder.UOMBaseQuantity,1),0)

   ,'UOM'   = ISNULL(UOM.Description,'')

   ,'OpenQuantity' =   -1*TrxnOrder.OpenQuantity

   ,'FullfilledQty' =   -1*TrxnOrder.FullfilledQuantity

   ,'Price'   = TrxnOrder.Price

   ,'Discount'   = -1*(ISNULL(TrxnOrder.ManualDiscountAmount,0)+ISNULL(TrxnOrder.CouponDiscountAmount,0)+ISNULL(TrxnOrder.PromotionalDiscountAmount,0)+ISNULL(TrxnOrder.SaleDiscountAmount,0)+ISNULL(TrxnOrder.SystemDiscountAmount,0))