iVend Database Database : TrxTransactionPayment Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

System Object

Published for Replication

Rows

0

Data Space Used

0.00 KB

Index Space Used

0.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

TransactionPaymentKey

Value is the system generated key of the transaction payment.

VarWChar

50

((0))

TransactionKey

Value is key of the transaction to which this payment has been applied. See also Transaction

VarWChar

50

PaymentTypeKey

Value is the payment type key.See also Payment Type

VarWChar

50

TenderType

Value is the tender type of the payment.

Valid values are:

Cash = 0

CreditCard = 1

DebitCard = 2

OnAccount = 4

Check = 5

TravellersCheck = 6

Voucher = 8

Integer

4

DetailKey

For future use

VarWChar

50

DetailType

For future use

Integer

4

Amount

Total amount paid using this payment type

Numeric

9 (20,5)

ChangeAmount

Value is the change amount

Numeric

9 (20,5)

CashBackAmount

Value is the cash back amount to be given back to customer.

Numeric

9 (20,5)

Name

Value is the name of the customer.

VarWChar

50

ExpirationDate

Value is the expiry date of the credit card or debit card or voucher depending on the tender type used

DBTimeStamp

4

ZipCode

Value is the zipcode of customer

VarWChar

50

AccountType

Value is the card type .

Valid values are:

Others = 0

MasterCard = 1

Visa = 2

Amex = 3

DinersClub = 4

Discover = 5

JCB = 6

CartBlance = 7

Integer

4

Address

Value is the customer address in case of the credit card.

VarWChar

50

City

Value is the customer address city in case of the credit card.

VarWChar

50

State

Value is the customer address state in case of the credit card.

VarWChar

50

CurrencyKey

For future use

VarWChar

50

ExchangeRateKey

For future use

VarWChar

50

IsVoided

Value is true if the payment has been voided.

Boolean

1

HasAuthorization

Value is true if the payment has a third party authorization attached to it.

Boolean

1

HasCardSwipe

Value is true if the customer card has been swiped and the card number has not been entered manually by pos user.

Boolean

1

AuthorizationCode

Value is the code returned from authorization.

VarWChar

50

PaymentReferenceNumber

Payment reference number provided by the third party aurhorizer, used in case of Authorized type of payments

VarWChar

50

RoundingAmount

Value is the amount by which the payment amount has been rounded.

Numeric

9 (20,5)

OriginalReferenceNumber

Original Payment reference number provided by the third party aurhorizer, used in case of Authorized type of payments for refunds

VarWChar

100

Refunded

For future use

Boolean

1

CardNumber

Last 4 digits of credit / debit cards or any other voucher etc.

VarWChar

50

ForeignCurrencyAmount

Amount which is taken in foreign currency

Numeric

9 (20,5)

ExchangeRate

Specifies the current exchange rate for the base currency

Numeric

9 (20,5)

HasOfflineAuthorization

Future use

Boolean

1

((0))

UniqueIdentifier

UniqueIdentifier

VarWChar

50

((0))

AdditionalInformation

Stores the additional information while doing the transaction payment

VarWChar

2000

CountryKey

Reference Key of Country Key. See also cfgCountry

VarWChar

50

BankKey

Reference Key of Bank Key. See also cfgBank

VarWChar

50

ValidationCode

Stores the validation code for the Payment

VarWChar

50

DiscountAmount

Specified the Discount Amount for the transaction payment

Numeric

9 (20,5)

((0))

SurchargeAmount

Value is the Surcharge Amount for the transaction

Numeric

9 (20,5)

((0))

NetPayableAmount

Value is the Net amount payble for the transaction

Numeric

9 (20,5)

((0))

HasSurcharge

Value is True if the transaction has Surcharge

Boolean

1

((0))

PaidAmount

Value is the amount paid for the transaction

Numeric

9 (20,5)

CheckDueDate

Captures the due date of the payments made by check

DBTimeStamp

4

HasSignature

Value is true if the Transaction has signature attached with it

Boolean

1

((0))

Index

Description

Primary

Unique

IX_TrxTransactionPayment

IX_TrxTransactionPayment_UniqueIdentifier

PK_TrxTransactionPayment

Database Object

Object Type

Description

Dep Level

CreateTillCount

Stored Procedure

1

Integration_ARPayment

Stored Procedure

1

Integration_TransactionPayment

Stored Procedure

1

Loy_GetTransactionPayment

Stored Procedure

1

Loyalty_ActiveBillValue

View

1

Loyalty_GenderInfo

View

1

TrxCompleteTransactionUpdates

Stored Procedure

1

TrxInventoryUpdatePaymentDiscount

Stored Procedure

Handles updates related to sale item

1

TrxUpdateCustomerBalance

Stored Procedure

1

UpdateTillAmounts

Stored Procedure

Handles updates to Till once payment against a transaction has been taken

1

uspTapeReport

Stored Procedure

1

XTapeReport

Stored Procedure

Generates the XTape report for a particular till

1

ZTapeReport

Stored Procedure

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[TrxTransactionPayment](

[TransactionPaymentKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[TransactionKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PaymentTypeKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TenderType] [int] NOT NULL,

[DetailKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DetailType] [int] NOT NULL,

[Amount] [numeric](20, 5) NOT NULL,

[ChangeAmount] [numeric](20, 5) NOT NULL,

[CashBackAmount] [numeric](20, 5) NOT NULL,

[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ExpirationDate] [datetime] NULL,

[ZipCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AccountType] [int] NOT NULL,

[Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CurrencyKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ExchangeRateKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IsVoided] [bit] NOT NULL,

[HasAuthorization] [bit] NOT NULL,

[HasCardSwipe] [bit] NOT NULL,

[AuthorizationCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[PaymentReferenceNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[RoundingAmount] [numeric](20, 5) NOT NULL,

[OriginalReferenceNumber] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Refunded] [bit] NOT NULL,

[CardNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ForeignCurrencyAmount] [numeric](20, 5) NOT NULL,

[ExchangeRate] [numeric](20, 5) NOT NULL,

[HasOfflineAuthorization] [bit] NOT NULL,

[UniqueIdentifier] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[AdditionalInformation] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CountryKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BankKey] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ValidationCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[DiscountAmount] [numeric](20, 5) NOT NULL,

[SurchargeAmount] [numeric](20, 5) NOT NULL,

[NetPayableAmount] [numeric](20, 5) NOT NULL,

[HasSurcharge] [bit] NOT NULL,

[PaidAmount] [numeric](20, 5) NULL,

[CheckDueDate] [datetime] NULL,

[HasSignature] [bit] NOT NULL,

CONSTRAINT [PK_TrxTransactionPayment] PRIMARY KEY CLUSTERED

(

[TransactionPaymentKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_TransactionPaymentKey] DEFAULT ((0)) FOR [TransactionPaymentKey]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_HasOfflineAuthorization] DEFAULT ((0)) FOR [HasOfflineAuthorization]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_UniqueIdentifier] DEFAULT ((0)) FOR [UniqueIdentifier]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_Discount] DEFAULT ((0)) FOR [DiscountAmount]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_Surcharge] DEFAULT ((0)) FOR [SurchargeAmount]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_NetPayableAmount] DEFAULT ((0)) FOR [NetPayableAmount]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF_TrxTransactionPayment_HasSurcharge] DEFAULT ((0)) FOR [HasSurcharge]

ALTER TABLE [dbo].[TrxTransactionPayment] ADD CONSTRAINT [DF__TrxTransa__HasSi__7ED7A8CB] DEFAULT ((0)) FOR [HasSignature]