iVend Database Database : ZTapeReport Stored Procedure

Creation Date

8/27/2019 10:04 AM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTillKey

In

VarWChar

200

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

GetCompanyDateTime

User Defined Function

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

RptZReport

Table

Not in use

1

RptZReportDetail

Table

1

RtlPOS

Table

Contains details about all POS defined in the system.

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

TrxARPayment

Table

Defines the details of the on account payments or any other settlements being done by the customer and the amount.

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

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].[ZTapeReport]

@pTillKey nvarchar(200)

AS

BEGIN

DECLARE @pSiteId INT,

@ZReportKey nvarchar(50),

@lPOSId NVARCHAR(50),

@lBusinessDate DATETIME,

@lTillId NVARCHAR(50),

@lAdvances DECIMAL(20, 5),

@lGiftCardSales DECIMAL(20, 5),

@lPreviousPayments DECIMAL(20, 5),

@lCreditSales DECIMAL(20, 5),

@lPaidByGiftCard DECIMAL(20, 5),

@lPaidByStoreCredit DECIMAL(20,5),

@lSalesReturns DECIMAL(20,5),

@lSalesReturnsTax DECIMAL(20,5),

@lTaxableInvoices DECIMAL(20,5),

@lSalesTax DECIMAL(20,5),

@lSalesNonTaxable DECIMAL(20,5),

@lTotalSales DECIMAL(20,5),

@lSalesReturnsNonTaxable DECIMAL(20,5),

@lTotalTaxable DECIMAL(20,5),

@lTotalSalesTax DECIMAL(20,5),

@lTotalNonTaxable DECIMAL(20,5),

@pPOSSiteId INT

delete FROM RptZReportDetail where ReportKey = (Select ReportKey From RptZReport where TillKey = @pTillKey)

delete FROM RptZReport where TillKey = @pTillKey

Select @pSiteId = SiteId, @pPOSSiteId = ISNULL(POSSiteId, 0) from CfgSiteInformation

SELECT @ZReportKey = NEWID()

INSERT INTO RptZReportDetail

(

ZReportDetailKey,

ReportKey,

PaymentId,

Type,

PaidIn,

PaidOut,

NetAmount

)

SELECT NEWID(),@ZReportKey, p.Id, p.Type, 'PaidIn' = SUM(CASE WHEN (Round(d.Amount - d.ChangeAmount + d.RoundingAmount,2)) > 0 THEN Round(d.Amount - d.ChangeAmount + d.RoundingAmount,2)

                                            ELSE 0

                                        END),

          'PaidOut' = ABS(SUM(CASE WHEN (Round(d.Amount - d.ChangeAmount + d.RoundingAmount,2) < 0) THEN Round(d.Amount - d.ChangeAmount + d.RoundingAmount,2)

                                    ELSE 0

                              END)),

  'NetAmount' = SUM(Round(d.Amount - d.ChangeAmount + d.RoundingAmount,2))

FROM    TrxTransaction h,

           TrxTransactionPayment d,

           PmtPaymentType p

WHERE   h.TransactionKey = d.TransactionKey

          AND d.PaymentTypeKey = p.PaymentTypeKey

          AND h.TillKey = @pTillKey

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

  And  H.IsDeleted = 0

GROUP BY p.Id, p.Type

SELECT @lAdvances = ISNULL(SUM(Round(A.Amount,2)),0)

FROM TrxARPayment A, TrxTransactionPayment B, TrxTransaction C

WHERE A.TransactionKey = B.TransactionKey

AND  A.TransactionKey = C.TransactionKey

And  C.IsDeleted = 0

AND  C.TillKey = @pTillKey

AND  A.PaymentType IN ( 0, --ARPAYMENT

      1, --SPECIALORDER

      3) --LAYAWAY

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

--gift card sales

SELECT @lGiftCardSales = ISNULL(SUM(A.Price),0)

FROM TrxTransactionGiftCertificate A, TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

AND  B.TillKey = @pTillKey

AND  B.IsVoided=0

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

SELECT @lPreviousPayments = ISNULL(SUM(Round(A.Amount,2)),0)

FROM TrxARPayment A, TrxTransactionPayment B, TrxTransaction C

WHERE A.TransactionKey = B.TransactionKey

AND  A.TransactionKey = C.TransactionKey

AND  C.TillKey = @pTillKey

AND  A.PaymentType IN (2) --AR PAYMENT AGAINST SALES

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

And  C.IsDeleted = 0

--this reflects store credits issued in the current till

SELECT @lCreditSales = ISNULL(SUM(Round(B.Amount,2)),0)

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  A.TillKey = @pTillKey

AND  B.TenderType = 11

And  A.IsDeleted = 0

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

--this reflects total amount collected by the gift card

SELECT @lPaidByGiftCard = ISNULL(SUM(Round(B.Amount,2)), 0)

FROM TrxTransaction A, TrxTransactionPayment B

WHERE A.TransactionKey = B.TransactionKey

AND  B.TenderType = 3

AND  A.TillKey = @pTillKey

AND  A.IsDeleted = 0

AND  A.IsVoided=0

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

--this reflects the total amount paid by the store cred

SELECT @lPaidByStoreCredit = 0

--ISNULL(SUM(Round(B.Amount,2)),0)

--FROM TrxTransaction A, TrxTransactionPayment B, TrxTransactionGiftCertificate C, InvProduct D

--WHERE A.TransactionKey = B.TransactionKey

--AND  B.TenderType = 3

--AND  B.DetailKey  = C.TransactionGiftCertificateKey

--AND  C.ProductKey = D.ProductKey

--AND  D.IsStoreCredit = 'TRUE'

--AND  D.IsGiftCertificate = 'TRUE'

--AND  A.TillKey = @pTillKey

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

SELECT @lSalesReturns = ISNULL(SUM(CASE WHEN B.Tax != 0 THEN Round(B.TotalPostSaleDiscount,2) - Round(B.Tax,2) ELSE 0 END),0),

@lSalesReturnsTax = ISNULL(SUM(B.Tax),0),

@lSalesReturnsNonTaxable = ISNULL(SUM(CASE WHEN B.Tax = 0 THEN Round(B.TotalPostSaleDiscount,2) ELSE 0 END),0)

FROM TrxTransaction A, TrxTransactionSaleItem B

WHERE A.TransactionKey = B.TransactionKey

AND  B.Type = 1   --INDICATES THAT THESE ARE RETURNS

AND  A.TillKey = @pTillKey

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

SELECT @lTaxableInvoices = ISNULL(SUM(CASE WHEN B.Tax != 0 THEN Round(B.TotalPostSaleDiscount,2) - Round(B.Tax,2) ELSE 0 END),0),

@lSalesTax = ISNULL(SUM(B.Tax),0),

@lSalesNonTaxable = ISNULL(SUM(CASE WHEN B.Tax = 0 THEN Round(B.TotalPostSaleDiscount,2) ELSE 0 END),0)

FROM TrxTransaction A, TrxTransactionSaleItem B

WHERE A.TransactionKey = B.TransactionKey

AND  B.Type != 1   --INDICATES THAT THESE ARE SALES

AND  A.TillKey = @pTillKey

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

And  A.IsDeleted = 0

SELECT   @lTotalTaxable = ISNULL(@lTaxableInvoices, 0) - ISNULL(@lSalesReturns, 0),

  @lTotalSalesTax = ISNULL(@lSalesTax, 0) - ISNULL(@lSalesReturnsTax, 0),

  @lTotalNonTaxable = ISNULL(@lSalesNonTaxable,0) - ISNULL(@lSalesReturnsNonTaxable, 0)

SELECT @lTotalSales = ISNULL(@lTotalTaxable, 0) + ISNULL(@lTotalSalesTax,0) + ISNULL(@lTotalNonTaxable,0)

Select Top 1

@lPOSId = C.Id,

@lBusinessDate = A.BusinessDate,

@lTillId = b.Id

from TilSession A, TilTill B, RtlPOS C

Where A.TillKey = b.TillKey

AND A.TillKey = @pTillKey

AND A.POSKey = C.POSKey

And A.Status = 1

Order By A.EndDate Desc

SELECT @ZReportKey As ReportKey,

@pTillKey  TillKey,

 dbo.GetCompanyDateTime() TillDate,

@lPOSId POSId,

@lTillId TillId,

@lBusinessDate BusinessDate,

CONVERT(NUMERIC (20,2), ROUND(@lAdvances,2)) AdvancePayments,

CONVERT(NUMERIC (20,2), ROUND(@lPreviousPayments,2)) PreviousPayments,

CONVERT(NUMERIC (20,2), ROUND(@lGiftCardSales,2)) GiftCardSales,

CONVERT(NUMERIC (20,2), ROUND(@lPaidByGiftCard,2)) PaidByGiftCard,

CONVERT(NUMERIC (20,2), ROUND(@lCreditSales,2)) StoreCreditIssued,

CONVERT(NUMERIC (20,2), ROUND(@lPaidByStoreCredit,2)) PaidByStoreCredit,

CONVERT(NUMERIC (20,2), ROUND(@lSalesReturns,2)) SalesReturns,

CONVERT(NUMERIC (20,2), ROUND(@lSalesReturnsTax,2)) SalesReturnsTax,

CONVERT(NUMERIC (20,2), ROUND(@lSalesReturnsNonTaxable,2)) SalesReturnsNonTaxable,

CONVERT(NUMERIC (20,2), ROUND(@lTaxableInvoices,2)) TotalTaxableSales,

CONVERT(NUMERIC (20,2), ROUND(@lSalesTax,2)) SalesTax,

CONVERT(NUMERIC (20,2), ROUND(@lSalesNonTaxable,2)) SalesNonTaxable,

CONVERT(NUMERIC (20,2), ROUND(@lTotalSales,2)) NetSalesAmount,

CONVERT(NUMERIC (20,2), ROUND(@lTotalTaxable,2)) NetTaxableAmount,

CONVERT(NUMERIC (20,2), ROUND(@lTotalSalesTax,2)) NetSalesTaxAmount,

CONVERT(NUMERIC (20,2), ROUND(@lTotalNonTaxable,2)) NetNonTaxableAmount

END