iVend Database Database : XTapeReport Stored Procedure

Creation Date

7/26/2019 6:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTillKey

In

Reference key of the Till

VarWChar

200

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

PmtPaymentType

Table

Defines a list of payment types specified in the system. These payment types are used to take payment against a transaction at POS.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TilSessionTransaction

Table

Defines the list of all the transactions that occur through the particular till along with the store , POS n user details.

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

TrxTransactionGiftCertificate

Table

Stores the values for the Gift certificate for the transaction

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

TrxTransactionQuotation

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

/*

Select * from TilTill

EXEC XTapeReport 1000000000000124

Select * from TrxTRansactionPayment

*/

CREATE PROCEDURE [dbo].[XTapeReport]

(

@pTillKey nVarchar(200)

)

AS

BEGIN

  SET NOCOUNT ON

Declare @lSubsidiaryKey nvarchar(50)

--, @pTillKey nVarchar(200)='1000000000000124'

SELECT @lSubsidiaryKey = S.SubsidiaryKey FROM TilTill T with (nolock)

INNER JOIN RtlStore S with (nolock) On T.StoreKey = S.StoreKey

WHERE TillKey = @pTillKey

SET @lSubsidiaryKey = ISNULL(@lSubsidiaryKey, '0')

SELECT p.Id, p.Type, 'PaidIn' = SUM(CASE WHEN (d.Amount - d.ChangeAmount) > 0 THEN d.Amount - d.ChangeAmount

                                            ELSE 0

                                        END),

          'PaidOut' = ABS(SUM(CASE WHEN (d.Amount - d.ChangeAmount < 0) THEN d.Amount - d.ChangeAmount

                                    ELSE 0

                              END)),

  'NetAmount' = SUM(d.Amount - d.ChangeAmount)

  FROM    TrxTransaction h with (nolock),

           TrxTransactionPayment d with (nolock),

           PmtPaymentType p with (nolock)

  WHERE   h.TransactionKey = d.TransactionKey

          AND d.PaymentTypeKey = p.PaymentTypeKey

          AND h.TillKey = @pTillKey

  And h.IsDeleted = 0 AND d.IsVoided = 0 AND H.IsVoided=0 AND H.IsSuspended=0         /* Add by Jyotsna Tyagi Dated 06th Jan, 2017 client reported Voided and suspended transaction add in Xtape report*/

  AND p.SubsidiaryKey = @lSubsidiaryKey

  GROUP BY p.Id, p.Type

UNION ALL

SELECT 'Round Total' AS Id, 0 AS Type, 'PaidIn' = SUM(CASE WHEN ISNULL(D.RoundingAmount,0)<0 THEN Isnull(D.RoundingAmount,0) ELSE 0 END),

          'PaidOut' = ABS(SUM(CASE WHEN ISNULL(D.RoundingAmount,0)>0 THEN Isnull(D.RoundingAmount,0) ELSE 0 END)),

  'NetAmount' = SUM(ISNULL(D.RoundingAmount,0))

  FROM    TrxTransaction h with (nolock),

           TrxTransactionPayment d with (nolock),

           PmtPaymentType p with (nolock)

  WHERE   h.TransactionKey = d.TransactionKey

          AND d.PaymentTypeKey = p.PaymentTypeKey

          AND h.TillKey = @pTillKey

  And h.IsDeleted = 0 AND d.IsVoided = 0 AND H.IsVoided=0 AND H.IsSuspended=0         /* Add by Jyotsna Tyagi Dated 06th Jan, 2017 client reported Voided and suspended transaction add in Xtape report*/

  AND p.SubsidiaryKey = @lSubsidiaryKey

Select p.Id, p.Type,

  'PaidIn' = Sum(Case TransactionType When 0 Then Amount Else 0 End),

  'PaidOut' = Abs(Sum(Case When TransactionType In (1, 3) Then Amount Else 0 End)),

  'NetAmount' = Sum(Case When TransactionType In (0, 1, 3) Then Amount Else 0 End),

  @pTillKey TillKey

from TilSessionTransaction t with (nolock), PmtPaymentType p with (nolock)

Where TillKey = @pTillKey

And  p.Type = 0

And  p.SubsidiaryKey = @lSubsidiaryKey

GROUP BY p.Id, p.Type

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

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'SALES' 'TransactionType' , 0 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionSaleItem T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 0

  AND T1.IsExchange = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

  And T0.IsDeleted = 0

  UNION

  SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'REFUNDS', 1 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionSaleItem T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 1

  AND T1.IsExchange = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

  SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'EXCHANGES', 2 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionSaleItem T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 0

  AND T1.IsExchange = 1

  And T0.IsDeleted = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'DELIVERY', 3 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionSaleItem T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type IN (3,4,5)

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.Total),0) NetAmount , 0 TaxAmount, 'GIFT CARDS', 4 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionGiftCertificate T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.Total),0) NetAmount , 0 TaxAmount, 'GIFT CARDS RECHARGE', 5 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionGiftCertificate T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 1

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.Total),0) NetAmount , 0 TaxAmount, 'GIFT CARDS CASHBACK', 6 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionGiftCertificate T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 2

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'ORDERS', 7 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionOrder T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T1.Status = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'QUOTATIONS', 8 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionQuotation T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T1.Status != 1

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'LAYAWAY', 9 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionLayaway T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 0

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T1.Status = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

UNION

SELECT ISNULL(SUM(T1.TotalPostSaleDiscount),0) NetAmount , ISNULL(SUM(T1.Tax),0) TaxAmount, 'LAYAWAY CANCELLATIONS', 10 'Order'

  FROM    TrxTransaction T0 with (nolock), TrxTransactionLayaway T1 with (nolock)

  WHERE   T0.TransactionKey = T1.TransactionKey

  AND T1.Type = 1

  AND T0.TillKey = @pTillKey

  AND T0.IsVoided = 0

  And T0.IsDeleted = 0

  AND T0.IsSuspended = 0

  AND T1.Status = 0

  AND T0.SubsidiaryKey = @lSubsidiaryKey

  order by 4

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

--    RETURN

--    ERRORHANDLER:

-- --RAISERROR('%s',16,-1,@lErrorDesc)

--    RETURN

END