iVend Database Database : uspTapeReport Stored Procedure

Creation Date

7/26/2019 6:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTillKey

In

BigInt

8

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

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

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

RtlPOS

Table

Contains details about all POS defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TaxTaxCode

Table

TaxCode is defined at the SBO and it's replicated from SBO to iVend. We can only view the TaxCodes at MC and associate them with the different objects, like Product, Customer, Enterprise, etc.

1

TilSession

Table

Defines all the till sessions from the time a till is assigned to a POS till the time it is finalized.

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

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE PROCEDURE [dbo].[uspTapeReport]

@pTillKey BIGINT

--  EXEC uspTapeReport 1000000000000124

AS

BEGIN

SELECT * FROM

(Select Top 1 C.Id POS, A.BusinessDate, b.Id TillId, D.Description Store, E.FirstName

from TilSession A, TilTill B, RtlPOS C, RTLStore D, Lbruser E

Where A.TillKey = b.TillKey

AND  A.TillKey = @pTillKey

AND  A.POSKey = C.POSKey

--And  A.Status = 1

And  A.StoreKey = D.StoreKey

AND  A.UserKey = E.UserKey

Order By A.EndDate Desc) A,

(SELECT 'Tender Type' Type, C.DESCRIPTION Description, Sum(ISNULL(B.PaidAmount,0)-ISNULL(B.ChangeAmount,0)) Amount, 1 'Sort Order'

FROM TrxTransaction A, TrxTransactionPayment B, PmtPaymentType C

WHERE A.TransactionKey = B.TransactionKey

AND  B.PaymentTypeKey = C.PaymentTypeKey

AND  A.TillKey = @pTillKey

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

group by C.DESCRIPTION

UNION

SELECT 'Tender Type' Type, 'Total', Sum(ISNULL(B.PaidAmount,0)-ISNULL(B.ChangeAmount,0)) Amount, 2

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

AND A.IsVoided=0 AND B.IsVoided=0 AND A.IsSuspended=0

UNION

SELECT 'Tender Type' Type, 'Round Total', Sum(ISNULL(B.RoundingAmount,0)) Amount, 3

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

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

UNION

SELECT 'Foreign Currency' Type, C.DESCRIPTION, Sum(ISNULL(B.PaidAmount,0)-ISNULL(B.ChangeAmount,0)) Amount, 4

FROM TrxTransaction A, TrxTransactionPayment B, PMTCURRENCY C

WHERE A.TransactionKey = B.TransactionKey

AND  B.CurrencyKey = C.CurrencyKey

AND  A.TillKey = @pTillKey

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

group by C.DESCRIPTION

UNION

SELECT 'Foreign Currency' Type, 'Total', Sum(ISNULL(B.PaidAmount,0)-ISNULL(B.ChangeAmount,0)) Amount, 5

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

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

UNION

SELECT 'Transaction Count' Type, CASE B.TYPE WHEN 0 THEN 'Sale' WHEN 1 THEN 'Refund' WHEN 2 THEN 'Exchange' WHEN 3 THEN 'Delivery Against Special Order'

WHEN 4 THEN 'Delivery Against Sale' WHEN 5 THEN'Delivery Against Layaway' End as Type, Count(B.TransactionItemKey) Count, 6

FROM TrxTransaction A, TrxTransactionSaleItem B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

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

group by B.TYPE

UNION

SELECT 'Transaction Count' Type, 'Total',Count(B.TransactionItemKey) Count, 7

FROM TrxTransaction A, TrxTransactionSaleItem B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

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

UNION

Select 'Transaction Amount' Type, CASE B.TYPE WHEN 0 THEN 'Sale' WHEN 1 THEN 'Refund' WHEN 2 THEN 'Exchange' WHEN 3 THEN 'Delivery Against Special Order'

WHEN 4 THEN 'Delivery Against Sale' WHEN 5 THEN'Delivery Against Layaway' End as Type,

Sum(ISNULL(B.TotalPostSaleDiscount,0))+Sum(ISNULL(B.SurchargeTotal,0))+Sum(Q.Surcharge) Amount, 8

From (

Select TransactionKey, TillKey, Sum(SurchargesTotal)Surcharge

From TrxTransaction Where TillKey=@pTillKey AND IsVoided=0 AND IsSuspended=0

Group By TransactionKey, TillKey

) Q JOIN

TrxTransaction A ON Q.TransactionKey=A.TransactionKey AND Q.TillKey=A.TillKey

JOIN TrxTransactionSaleItem B ON A.TransactionKey = B.TransactionKey

Where A.IsVoided=0 AND IsSuspended=0

AND A.TillKey=@pTillKey             /* Add by Jyotsna Tyagi Dated 06th Jan, 2017 client reported Voided and suspended transaction add in Xtape report*/

Group BY Type

UNION

Select 'Transaction Amount' Type, 'Total',

Sum(ISNULL(B.TotalPostSaleDiscount,0))+Sum(ISNULL(B.SurchargeTotal,0))+Sum(Q.Surcharge) Amount, 9

From (

Select TransactionKey, TillKey, Sum(SurchargesTotal)Surcharge From TrxTransaction

Where TillKey=@pTillKey AND IsVoided=0 AND IsSuspended=0

Group By TransactionKey, TillKey

) Q JOIN

TrxTransaction A ON Q.TransactionKey=A.TransactionKey AND Q.TillKey=A.TillKey

JOIN TrxTransactionSaleItem B ON A.TransactionKey = B.TransactionKey

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

AND A.TillKey=@pTillKey

UNION

SELECT 'Tax Breakup' Type, C.Description, Sum(ISNULL(B.Tax,0)) Tax, 10

FROM TrxTransaction A, TrxTransactionSaleItem B, TaxTaxCode C

WHERE A.TransactionKey = B.TransactionKey

AND  B.TaxCodeKey = C.TaxCodeKey

AND  A.TillKey = @pTillKey

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

group by C.Description

UNION

SELECT 'Tax Breakup' Type,'Total', Sum(ISNULL(B.Tax,0)) Tax, 11

FROM TrxTransaction A, TrxTransactionSaleItem B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

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

UNION

SELECT 'Card Details' Type, B.CardNumber, Isnull(Sum(ISNULL(B.PaidAmount,0)),0) Amount, 12

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

AND A.IsVoided=0 AND B.IsVoided=0 AND A.IsSuspended=0 AND B.TenderType IN (1,2,14)                 /* Add by Jyotsna Tyagi Dated 06th Jan, 2017 client reported Voided and suspended transaction add in Xtape report*/

group by b.CardNumber

UNION

SELECT 'Card Details' Type, 'Total', Isnull(Sum(ISNULL(B.PaidAmount,0)),0) Amount, 13

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

AND A.IsVoided=0 AND B.IsVoided=0 AND A.IsSuspended=0 AND B.TenderType IN (1,2,14)       /* Add by Jyotsna Tyagi Dated 06th Jan, 2017 client reported Voided and suspended transaction add in Xtape report*/

)B

order by b.[Sort Order]

END