iVend Database Database : TrxTransactionGiftCertificate Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

PRIMARY

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

TransactionGiftCertificateKey

System generated Primary key of the table.

VarWChar

50

TransactionKey

Refers to the key of the transaction. See also Transaction

VarWChar

50

WarehouseKey

Refers to the key of the warehouse from where the product has been selected.See also Warehouse.

VarWChar

50

ProductKey

Refers to the key of the product.See also Product

VarWChar

50

InventoryItemKey

Refers to the key the inventory item. See also Inventory Item

VarWChar

50

SerialNumberKey

Stores the SerialKey of the Serial Number of the Gift Certificate

VarWChar

50

Description

Refers to the item description.

VarWChar

100

Type

Specifies the type of the transaction

Valid values are:

Sale = 0

ReCharge = 1

Cash Back = 2

Integer

4

OriginalPrice

Stores the price of the Gift Certificate

Numeric

9 (20,5)

BasePrice

Stores the price of the Gift Certificate

Numeric

9 (20,5)

Price

Stores the price of the Gift Certificate

Numeric

9 (20,5)

PriceSource

Refers to the price source.

Valid values are:

None =0

System = 1

Manual = 2

Integer

4

Quantity

Stores the Quantity of the Gift Certificates. This would always be 1.

Numeric

9 (20,5)

SubTotal

Subtotal

Numeric

9 (20,5)

Total

Is the sum of SubTotal + Tax

Numeric

9 (20,5)

TotalBeforeSaleDiscount

Refers to the total before the sale level discount has been applied.

Numeric

9 (20,5)

TotalPostSaleDiscount

Value is the total amount after any sale discount applied.

Numeric

9 (20,5)

CommentKey

Refers to the key of the added comments.See also Comments

VarWChar

50

OriginalDetailKey

In case of recharge and cash back, the field stores the TransactionKey of the original gift certificate

VarWChar

50

OriginalDocumentKey

In case of recharge and cash back, the field stores the TransactionKey of the original gift certificate

VarWChar

50

ValidDate

Expiry date of the Gift Certificate

DBTimeStamp

4

OriginalGiftCertificateKey

In case of recharge and cash back, the field stores the GiftCertificateKey of the giftcertificate against which these transactions are carried out.

VarWChar

50

HasDiscounts

Specifies whether any discounts have been applied on the line item

Boolean

1

((0))

ManualDiscountAmount

Specifies the Applied Manual Discount Amount

Numeric

9 (20,5)

((0))

ManualDiscountPercent

Specifies the Applied Manual Discount percent

Numeric

9 (20,5)

((0))

CouponDiscountAmount

Specifies the Applied Coupon Discount Amount

Numeric

9 (20,5)

((0))

SystemDiscountAmount

Specifies the Applied System Discount Amount

Numeric

9 (20,5)

((0))

PromotionalDiscountAmount

Specifies the Applied promotional Discount Amount

Numeric

9 (20,5)

((0))

DiscountType

Discount type applicable

Valid values are:

None = 0

Amount = 1

Percent = 2

Integer

4

((0))

DiscountAmount

Specifies the Applied Discount Amount

Numeric

9 (20,5)

((0))

DiscountPercent

Specifies the Applied Discount Percent

Numeric

9 (20,5)

((0))

SystemDiscountPercent

Specifies the Applied System Discount Amount

Numeric

9 (20,5)

((0))

SystemDiscountType

Specifies the Applied System Discount percent

Numeric

9 (20,5)

((0))

Comments

Stores comments

VarWChar

4000

Integration1

Additional Field for ERP Integration - Alpha numeric type

VarWChar

100

Integration2

Additional Field for ERP Integration - Alpha numeric type

VarWChar

100

Integration3

Additional Field for ERP Integration - Alpha numeric type

VarWChar

100

Integration4

Additional Field for ERP Integration - Alpha numeric type

VarWChar

100

Integration5

Additional Field for ERP Integration - Alpha numeric type

VarWChar

100

Integration6

Additional Field for ERP Integration - Numeric type

Numeric

9 (20,5)

Integration7

Additional Field for ERP Integration - Numeric type

Numeric

9 (20,5)

Integration8

Additional Field for ERP Integration - Numeric type

Numeric

9 (20,5)

Integration9

Additional Field for ERP Integration - Numeric type

Numeric

9 (20,5)

Integration10

Additional Field for ERP Integration - Numeric type

Numeric

9 (20,5)

Index

Description

Primary

Unique

IX_TrxTransactionGiftCertificate

PK_TrxTransactionGiftCertificate

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateSerialNumber

Stored Procedure

1

vwAnalytics_GiftCertificates

View

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].[TrxTransactionGiftCertificate](

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

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

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

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

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

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

[Description] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Type] [int] NOT NULL,

[OriginalPrice] [decimal](20, 5) NOT NULL,

[BasePrice] [decimal](20, 5) NOT NULL,

[Price] [decimal](20, 5) NOT NULL,

[PriceSource] [int] NOT NULL,

[Quantity] [decimal](20, 5) NOT NULL,

[SubTotal] [decimal](20, 5) NOT NULL,

[Total] [decimal](20, 5) NOT NULL,

[TotalBeforeSaleDiscount] [decimal](20, 5) NOT NULL,

[TotalPostSaleDiscount] [decimal](20, 5) NOT NULL,

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

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

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

[ValidDate] [datetime] NOT NULL,

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

[HasDiscounts] [bit] NOT NULL,

[ManualDiscountAmount] [decimal](20, 5) NOT NULL,

[ManualDiscountPercent] [decimal](20, 5) NOT NULL,

[CouponDiscountAmount] [decimal](20, 5) NOT NULL,

[SystemDiscountAmount] [decimal](20, 5) NOT NULL,

[PromotionalDiscountAmount] [decimal](20, 5) NOT NULL,

[DiscountType] [int] NOT NULL,

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

[DiscountPercent] [decimal](20, 5) NOT NULL,

[SystemDiscountPercent] [decimal](20, 5) NOT NULL,

[SystemDiscountType] [decimal](20, 5) NOT NULL,

[Comments] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

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

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

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

[Integration6] [decimal](20, 5) NULL,

[Integration7] [decimal](20, 5) NULL,

[Integration8] [decimal](20, 5) NULL,

[Integration9] [decimal](20, 5) NULL,

[Integration10] [decimal](20, 5) NULL,

CONSTRAINT [PK_TrxTransactionGiftCertificate] PRIMARY KEY CLUSTERED

(

[TransactionGiftCertificateKey] 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].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_HasDiscounts] DEFAULT ((0)) FOR [HasDiscounts]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_ManualDiscountAmount] DEFAULT ((0)) FOR [ManualDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_ManualDiscountPercent] DEFAULT ((0)) FOR [ManualDiscountPercent]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_CouponDiscountAmount] DEFAULT ((0)) FOR [CouponDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_SystemDiscountAmount] DEFAULT ((0)) FOR [SystemDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_PromotionalDiscountAmount] DEFAULT ((0)) FOR [PromotionalDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_DiscountType] DEFAULT ((0)) FOR [DiscountType]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_DiscountAmount] DEFAULT ((0)) FOR [DiscountAmount]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_DiscountPercent] DEFAULT ((0)) FOR [DiscountPercent]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_SystemDiscountPercent] DEFAULT ((0)) FOR [SystemDiscountPercent]

ALTER TABLE [dbo].[TrxTransactionGiftCertificate] ADD CONSTRAINT [DF_TrxTransactionGiftCertificate_SystemDiscountType] DEFAULT ((0)) FOR [SystemDiscountType]