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

TransactionOrderKey

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

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)

HasDiscountOverride

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

Boolean

1

TransactionQuotationKey

Stores the Key of the Quotation from which this item is converted to order item.

VarWChar

50

DeliveryWarehouseKey

Stores the WarehouseKey of the warehouse from where the delivery is requested for this item

VarWChar

50

BookingStoreKey

Stores the StoreKey of the Store from where the transaction is being done

VarWChar

50

BookingWarehouseKey

Stores the WarehouseKey of the warehouse from where the transaction is being done

VarWChar

50

TotalTaxableAmount

Stores the amount on which the tax would be calculated

Numeric

9 (20,5)

((0))

ParentTransactionItemKey

Stores the TransactionItem Key against which these items are added in the transaction.

VarWChar

50

((0))

IsUpsellItem

Specifies if the item is added as an upsell item in the transaction.

Boolean

1

((0))

UpsellProductKey

Stores the product key of the item against which these items are added in the transaction.

VarWChar

50

((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

((0))

ManualDiscountAmount

Stores the manual Discount Amount

Numeric

9 (20,5)

((0))

ManualDiscountPercent

Stores the manual Discount Percent

Numeric

9 (20,5)

((0))

CouponDiscountAmount

Stores the Coupon Discount Amount

Numeric

9 (20,5)

((0))

SystemDiscountAmount

Stores the System Discount Amount

Numeric

9 (20,5)

((0))

PromotionalDiscountAmount

Stores the Promotion Discount Amount

Numeric

9 (20,5)

((0))

SystemDiscountPercent

Stores the 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

Stores the UOM Quantity

Numeric

9 (20,5)

UOMBaseQuantity

Stores the Base UOM Quantity

Numeric

9 (20,5)

UOMGroupDetailKey

Reference Key for the UOM Group Detail

VarWChar

50

ScannedBarcode

Stores the current Scanned Barcode value

VarWChar

50

ScannedBarcodeSource

Value is the source from where the product barcode has been entered. The valid values are:

BarcodeScan = 0,

ManualScan = 1,

Upsell = 2,

Promotion =3,

ProductSearch =4,

AlternateProductView= 5

Integer

4

((0))

AverageCost

Stores the Average cost for the product on the order

Numeric

9 (20,5)

PaymentDiscount

Store the Discount provided during a Sale Transaction

Numeric

9 (20,5)

MobileFulfilledQuantity

Count is the value of the quantity fulfilled through mobile

Numeric

9 (20,5)

HasCostMarginOverride

Value is True if the Cost Margin Overrride has been applied on item for the transaction order

Boolean

1

((0))

ExchangeRate

Stores the Exchange rate for the Currency used in transaction

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 Transaction order

Numeric

9 (20,5)

((0))

OriginalTax

Value is the Original Tax applied on the Transaction Order

Numeric

9 (20,5)

((0))

TotalPostSaleDiscountEC

Stores the Enterprise Currency for the Total Post Sale Discount

Numeric

9 (38,7)

([TotalPostSaleDiscount]*[ExchangeRate])

TotalEC

Stores the Enterprise Currency for the Total of a Transaction Order

Numeric

9 (38,7)

([Total]*[ExchangeRate])

TaxEC

Stores the Enterprise Currency for the Tax on transaction order

Numeric

9 (38,7)

([Tax]*[ExchangeRate])

PriceEC

Stores the Enterprise Currency for the price of an item for a transaction

Numeric

9 (38,7)

([Price]*[ExchangeRate])

SubTotalEC

Stores the Enterprise Currency for the Sub total for a transaction order

Numeric

9 (38,7)

([SubTotal]*[ExchangeRate])

AverageCostEC

Stores the Average Cost for the sale item with respect to Enterprise Currency.

Numeric

9 (38,7)

([AverageCost]*[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_TrxTransactionOrder

IX_TrxTransactionOrder_1

PK_TrxTransactionOrder

Database Object

Object Type

Description

Dep Level

GetSpecialOrderHistory

Stored Procedure

1

Integration_TransactionSurcharge

Stored Procedure

1

RPT_ABCAnalysis

Stored Procedure

1

rpt_LayAway

Stored Procedure

1

RPT_StoreAnalysis

Stored Procedure

1

rptSalesTransactionData

User Defined Function

1

rptSalesTransactionDataWithSalesPerson

User Defined Function

1

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateBatchNumber

Stored Procedure

1

TrxInventoryUpdateOrder

Stored Procedure

Handles updates related to Order item

1

TrxInventoryUpdatePaymentDiscount

Stored Procedure

Handles updates related to sale item

1

TrxInventoryUpdateQuotation

Stored Procedure

Handles updates related to Quotation item

1

TrxInventoryUpdateSaleDelivery

Stored Procedure

Handles updates related to delivery of a Sale item

1

TrxUpdateCustomerBalance

Stored Procedure

1

vw_InventoryMovement

View

1

XTapeReport

Stored Procedure

Generates the XTape report for a particular till

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[TrxTransactionOrder](

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

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

[HasDiscountOverride] [bit] NOT NULL,

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

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

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

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

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

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

[IsUpsellItem] [bit] NOT NULL,

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

[IsTaxInclusive] [bit] NOT NULL,

[IgnoreDiscountItemsForSaleDiscount] [bit] NOT NULL,

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

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

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

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

[MobileFulfilledQuantity] [decimal](20, 5) 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,

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

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

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

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

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

[AverageCostEC] AS ([AverageCost]*[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_TrxTransactionOrder] PRIMARY KEY CLUSTERED

(

[TransactionOrderKey] 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].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_TotalTaxableAmount] DEFAULT ((0)) FOR [TotalTaxableAmount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ParentTransactionItemKey] DEFAULT ((0)) FOR [ParentTransactionItemKey]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_IsUpsellItem] DEFAULT ((0)) FOR [IsUpsellItem]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_UpsellProductKey] DEFAULT ((0)) FOR [UpsellProductKey]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_IsTaxInclusive] DEFAULT ((0)) FOR [IsTaxInclusive]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_IgnoreDiscountItemsForSaleDiscount] DEFAULT ((0)) FOR [IgnoreDiscountItemsForSaleDiscount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ManualDiscountType] DEFAULT ((0)) FOR [ManualDiscountType]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ManualDiscountAmount] DEFAULT ((0)) FOR [ManualDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ManualDiscountPercent] DEFAULT ((0)) FOR [ManualDiscountPercent]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_CouponDiscountAmount] DEFAULT ((0)) FOR [CouponDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_SystemDiscountAmount] DEFAULT ((0)) FOR [SystemDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_PromotionalDiscountAmount] DEFAULT ((0)) FOR [PromotionalDiscountAmount]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_SystemDiscountPercent] DEFAULT ((0)) FOR [SystemDiscountPercent]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_SystemDiscountType] DEFAULT ((0)) FOR [SystemDiscountType]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ScannedBarcodeSource] DEFAULT ((0)) FOR [ScannedBarcodeSource]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_HasCostMarginOverride] DEFAULT ((0)) FOR [HasCostMarginOverride]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_ExchangeRate] DEFAULT ((1)) FOR [ExchangeRate]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_IsTaxFree] DEFAULT ((0)) FOR [IsTaxFree]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_OriginalTaxCodeKey] DEFAULT ((0)) FOR [OriginalTaxCodeKey]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_OriginalTaxRate] DEFAULT ((0)) FOR [OriginalTaxRate]

ALTER TABLE [dbo].[TrxTransactionOrder] ADD CONSTRAINT [DF_TrxTransactionOrder_OriginalTax] DEFAULT ((0)) FOR [OriginalTax]