iVend Database Database : TrxTransactionQuotation 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

TransactionQuotationKey

Value is system generated primary key.

VarWChar

50

TransactionKey

Value is the transaction key for which this order item has been added. See also Transaction

VarWChar

50

WarehouseKey

Value is the warehouse key from which the pos is associated / the selling warehouse of store.See also Warehouse

VarWChar

50

ProductKey

Value is the key of the order item.See also Product

VarWChar

50

InventoryItemKey

Value is the InventoryItem key. See also Inventory Item

VarWChar

50

Description

Value is the description of the order item.

VarWChar

100

HasFulfillment

Value is true if the order item has fulfillment attached.

Boolean

1

HasSurcharges

Value is true if the order item has surcharges defined.

Boolean

1

HasDiscounts

Value is true if the order item has any discounts defined.

Boolean

1

HasDiscountOverride

This flag checks if the discounts has been overrided by the user

Boolean

1

TaxCodeKey

Value is Tax Code key of the order item. See also Tax

VarWChar

50

TaxRate

Value is the tax rate applicable to the order item.

Numeric

9 (20,5)

HasTaxCodeOverride

Value is true if the taxcode of order item has been overriden.

Boolean

1

OriginalPrice

Value is the original price of the order item.

Numeric

9 (20,5)

BasePrice

Value is the overriden price of the order item.

Numeric

9 (20,5)

Price

Value is the overriden price of the order item.

Numeric

9 (20,5)

PriceSource

Value is the price source of the order item.

Valid values are:

None = 0

System = 1

Manual = 2

Integer

4

Quantity

Value is the quantity of the order item added to fulfillment.

Numeric

9 (20,5)

OpenQuantity

Value is the quantity of order item that is still remaining for the fulfillment delivery.

Numeric

9 (20,5)

FullfilledQuantity

Value is the quantity of order items which have been fullfilled/ delivered out of original transaction open quantities.

Numeric

9 (20,5)

RefundedQuantity

The quantity of order item refunded after fulfillment.

Numeric

9 (20,5)

SubTotal

Value is the order item sub total amount.

Numeric

9 (20,5)

DiscountType

Discount type applicable.

Valid values are:

None = 0

Amount = 1

Percent = 2

Integer

4

DiscountAmount

Value is the discount amount applied to the order item.

Numeric

9 (20,5)

DiscountPercent

Value is percentage of discount applied on order item.

Numeric

9 (20,5)

SaleDiscountAmount

Value is the discount amount applied on the transaction.

Numeric

9 (20,5)

ExclusiveDiscountPercent

Discount percentage applied on the Line item on exclusive price

Numeric

9 (20,5)

TaxableDiscountAmount

For future use.

Numeric

9 (20,5)

StoreTaxableDiscountAmount

For future use.

Numeric

9 (20,5)

Tax

Value is the tax amount of the order item.

Numeric

9 (20,5)

SurchargeTotal

Value is the surcharge amount of order item.

Numeric

9 (20,5)

PostTaxDiscount

For future use.

Numeric

9 (20,5)

PostTaxDiscountLiability

For future use.

Numeric

9 (20,5)

PostTaxDiscountStoreLiability

For future use.

Numeric

9 (20,5)

Total

Value is the total amount of the order line item.

Numeric

9 (20,5)

TotalBeforeSaleDiscount

Value is the total amount before any sale discount had been applied.

Numeric

9 (20,5)

TotalPostSaleDiscount

Value is the total amount after any sale discount applied.

Numeric

9 (20,5)

CommentKey

Value is the comment key for the order item.See also Comment

VarWChar

50

RequiredDeliveryDate

Expected delivery date of the order

DBTimeStamp

4

Type

Value is the order item type.

Valid values are:

Order = 0

Cancel = 1

Edit = 2

Refund = 3

Integer

4

OriginalDetailKey

Used in case of editing of an existing order. Contains the original order key for reference.

VarWChar

50

OriginalDocumentKey

Used in case of editing of an existing order. Contains the original transaction key for reference.

VarWChar

50

Status

Value is the current status of the special order.

Valid values are:

Open = 0

Closed = 1

Cancelled = 2

Integer

4

IsPromotionApplied

Flag to check whether the promotion is applied or not

Boolean

1

PromotionalQuantity

Refers to the promotional quantity

Numeric

9 (20,5)

TotalTaxableAmount

Stores the Total Taxble Amount

Numeric

9 (20,5)

((0))

IsTaxInclusive

This setting is copied from product setup while doing the transaction. Setting specifies if the item is marked as TaxInclusive item.

Boolean

1

((0))

IgnoreDiscountItemsForSaleDiscount

Flag to mark if the sale discount is to be ignored for this product when line discount is applied at the time of transaction

Boolean

1

((0))

ManualDiscountType

Manual Discount type applicable

Valid values are:

None = 0

Amount = 1

Percent = 2

Integer

4

ManualDiscountAmount

Stores the Manual Discount amount when the Manual Discount Type chosen as Amount

Numeric

9 (20,5)

ManualDiscountPercent

Stores the Manual Discount percent when the Manual Discount Type chosen as percent

Numeric

9 (20,5)

CouponDiscountAmount

Stores the Coupon Discount amount

Numeric

9 (20,5)

SystemDiscountAmount

Stores System Discount Amount

Numeric

9 (20,5)

PromotionalDiscountAmount

Stores the System promotion Discount amount

Numeric

9 (20,5)

SystemDiscountPercent

Stores System Discount Percent

Numeric

9 (20,5)

((0))

SystemDiscountType

System Discount type applicable

Valid values are:

None = 0

Amount = 1

Percent = 2

Numeric

9 (20,5)

((0))

UOMQuantity

Reference Key for the UOM Group Detail

Numeric

9 (20,5)

UOMBaseQuantity

Stores the UOM quantity

Numeric

9 (20,5)

UOMGroupDetailKey

Stores the Base UOM quantity

VarWChar

50

ScannedBarcode

Stores the Scanned BarCode

VarWChar

50

ScannedBarcodeSource

Stores the BarCode Source

Integer

4

((0))

HasCostMarginOverride

Value is True if the Cost Margin Overrride has been done on a quotation

Boolean

1

((0))

ExchangeRate

Stores the Exchange rate for the Currency used in Quotation

Numeric

9 (20,5)

((1))

IsTaxFree

Value is True if the added item is Tax Free

Boolean

1

((0))

OriginalTaxCodeKey

Value is the Reference for the Tax code key. See Tax code

VarWChar

50

((0))

OriginalTaxRate

Value is the Original Tax rate for the Quotation

Numeric

9 (20,5)

((0))

OriginalTax

Value is the Original Tax applied on Quotationj

Numeric

9 (20,5)

((0))

PriceEC

Stores the Enterprise Currency for the Price of an item in Quotation

Numeric

9 (38,7)

([Price]*[ExchangeRate])

TotalEC

Stores the Enterprise Currency for the Total of quotation

Numeric

9 (38,7)

([Total]*[ExchangeRate])

SubTotalEC

Stores the Enterprise Currency for the Sub total of a Quotation

Numeric

9 (38,7)

([SubTotal]*[ExchangeRate])

TaxEC

Stores the Enterprise Currency for the Tax on quotation

Numeric

9 (38,7)

([Tax]*[ExchangeRate])

TotalPostSaleDiscountEC

Store the Enterprise Currency for the Post Sale Discount for a Quotation

Numeric

9 (38,7)

([TotalPostSaleDiscount]*[ExchangeRate])

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_TrxTransactionQuotation

PK_TrxTransactionQuotation

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateQuotation

Stored Procedure

Handles updates related to Quotation item

1

XTapeReport

Stored Procedure

Generates the XTape report for a particular till

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[TrxTransactionQuotation](

[TransactionQuotationKey] [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,

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

[HasFulfillment] [bit] NOT NULL,

[HasSurcharges] [bit] NOT NULL,

[HasDiscounts] [bit] NOT NULL,

[HasDiscountOverride] [bit] NOT NULL,

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

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

[HasTaxCodeOverride] [bit] 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,

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

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

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

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

[DiscountType] [int] NOT NULL,

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

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

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

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

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

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

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

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

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

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

[PostTaxDiscountStoreLiability] [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,

[RequiredDeliveryDate] [datetime] NULL,

[Type] [int] NOT 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,

[Status] [int] NOT NULL,

[IsPromotionApplied] [bit] NOT NULL,

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

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

[IsTaxInclusive] [bit] NOT NULL,

[IgnoreDiscountItemsForSaleDiscount] [bit] NOT NULL,

[ManualDiscountType] [int] NULL,

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

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

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

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

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

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

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

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

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

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

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

[ScannedBarcodeSource] [int] NULL,

[HasCostMarginOverride] [bit] NULL,

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

[IsTaxFree] [bit] NOT NULL,

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

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

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

[PriceEC] AS ([Price]*[ExchangeRate]),

[TotalEC] AS ([Total]*[ExchangeRate]),

[SubTotalEC] AS ([SubTotal]*[ExchangeRate]),

[TaxEC] AS ([Tax]*[ExchangeRate]),

[TotalPostSaleDiscountEC] AS ([TotalPostSaleDiscount]*[ExchangeRate]),

[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_TrxTransactionQuotation] PRIMARY KEY CLUSTERED

(

[TransactionQuotationKey] 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].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_TotalTaxableAmount] DEFAULT ((0)) FOR [TotalTaxableAmount]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_IsTaxInclusive] DEFAULT ((0)) FOR [IsTaxInclusive]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_IgnoreDiscountItemsForSaleDiscount] DEFAULT ((0)) FOR [IgnoreDiscountItemsForSaleDiscount]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_SystemDiscountPercent] DEFAULT ((0)) FOR [SystemDiscountPercent]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_SystemDiscountType] DEFAULT ((0)) FOR [SystemDiscountType]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_ScannedBarcodeSource] DEFAULT ((0)) FOR [ScannedBarcodeSource]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_HasCostMarginOverride] DEFAULT ((0)) FOR [HasCostMarginOverride]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_ExchangeRate] DEFAULT ((1)) FOR [ExchangeRate]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_IsTaxFree] DEFAULT ((0)) FOR [IsTaxFree]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_OriginalTaxCodeKey] DEFAULT ((0)) FOR [OriginalTaxCodeKey]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_OriginalTaxRate] DEFAULT ((0)) FOR [OriginalTaxRate]

ALTER TABLE [dbo].[TrxTransactionQuotation] ADD CONSTRAINT [DF_TrxTransactionQuotation_OriginalTax] DEFAULT ((0)) FOR [OriginalTax]