iVend Database Database : InvInventoryItem Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

System Object

Published for Replication

Rows

5115

Data Space Used

2,280.00 KB

Index Space Used

1,192.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

InventoryItemKey

System generated Primary key of the table.

VarWChar

50

ProductKey

Refers to the product. See also Product

VarWChar

50

WarehouseKey

Refers to the warehouse. See also Warehouse

VarWChar

50

PurchaseTaxKey

Refers to the purchase tax code key.See also Tax

VarWChar

50

((0))

SalesTaxKey

Refers to the sales tax code key.See also Tax

VarWChar

50

((0))

InStockQuantity

Refers to the quntity in stock

Numeric

9 (20,5)

((0))

InReturnQuantity

For future use

Numeric

9 (20,5)

((0))

OnRentQuantity

For future use

Numeric

9 (20,5)

((0))

LostQuantity

For future use

Numeric

9 (20,5)

((0))

OnLayawayQuantity

Refers to the quantity that has been put on layaways

Numeric

9 (20,5)

((0))

OnOrderQuantity

Refers to the quantity that has been put on special order

Numeric

9 (20,5)

((0))

OnFulFillmentQuantity

Refers to the quantity that needs to be fulfilled

Numeric

9 (20,5)

((0))

AvailableQuantity

Refers to the total available quantity at the current warehouse

Numeric

9 (20,5)

((0))

KitsAvailableToBuild

Refers to the total quantity of kits that are available to build

Numeric

9 (15,0)

((0))

AssembliesAvailableToBuild

Refers to the total quantity of assemblies that are available to build

Numeric

9 (15,0)

((0))

Price

Refers to the price of the product

Numeric

9 (20,5)

LeadTime

Minimum time in which the product can be made available.

Integer

4

Created

Date of creation of this record

DBTimeStamp

4

Modified

Date of last modification of this record

DBTimeStamp

4

CreatedBy

Reference of user who has created this record. See also Security User

VarWChar

50

ModifiedBy

Reference of user who has modified this record. See also Security User

VarWChar

50

IsLocked

Value is true is Inventory item is locked (not available for Sale)

Boolean

1

((0))

CountedQuantity

Stores the Counted Quantity of the Inventory product

Numeric

9 (20,5)

((0))

WasCounted

Value is true if the Counting for the Inventory Product is completed

Boolean

1

((0))

InventoryCycleSetupKey

Reference Key of Inventory Cycle Setup. See also Inventory Cycle Setup

VarWChar

50

((0))

NextCountDate

Stores the Next scheduled Count Date based on the chosen frequency.

The values are populated when frequency chosen as

OneTime

Annually

HalfYearly

Quarterly

DBTimeStamp

4

NextCountTime

Stores the Next scheduled Count time.

DBTimeStamp

4

InTransitQuantity

Specifies the inventroy item quantity that is in-transit from warehouse to a particular warehouse.

Numeric

9 (20,5)

((0))

MinimumStockLevel

Specifies the value as Minimum Stock quantity of the Inventroy Item

Numeric

9 (20,5)

((0))

MaximumStockLevel

Specifies the value as Maximum Stock quantity of the Inventroy Item

Numeric

9 (20,5)

((0))

ReplenishmentMethod

Stores the Replenishment methofs for the product

Integer

4

ConsiderInMRP

Value is true if the product can be a part of MRP

Boolean

1

MinimumOrderQuantity

Specifies the value as Minimum ordered quantity

Numeric

9 (20,5)

FulfillmentWarehouseKey

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

VarWChar

50

TransactionType

Value is the transactio type for the inventory. Valid values are:

GoodsIssue = 0,

InventoryReconciliation = 1

Integer

4

((0))

AverageCost

Stores the Average cost for the product

Numeric

9 (20,5)

LastLabelPrintingDate

Stores the Date when the lasttime the label was printed for the item

DBTimeStamp

4

AllocatedQuantity

Value is the allocated quantity for inventory batch

Numeric

9 (20,5)

((0))

CostProtectionMarginType

Valid values are:

None = 0,

Amount = 1,

Percent = 2

Integer

4

((0))

CostProtectionMarginValue

Stores the value based on the type chosen for the Cost Protection margin type

Numeric

9 (20,5)

((0))

PriceListKey

Value is the Pricelist Key used at the store for commodities for the Subsidiary. See also Pricelist

VarWChar

50

PreferedVendorKey

Refers to the Vendor. See also Vendor

VarWChar

50

IsTaxExempt

value is True if the product is tax exempted for the Subsidiary Warehouse

Boolean

1

IsInclusiveTaxed

Flag to check whether the product is tax inclusive or not for the Subsidiary Warehouse

Boolean

1

MaximumOpenPrice

Maximum limit for the Open price for the product for the Subsidiary Warehouse

Numeric

9 (20,5)

ReservedQuantity

Hold the reserved quantity for sale with delivery fulfillment and Layaway with delivery fulfillment

Numeric

9 (20,5)

((0))

IsOnHold

Flag to check whether the product is on hold or not for the Subsidiary Warehouse

Boolean

1

((0))

Index

Description

Primary

Unique

IX_InvInventoryItem

IX_InvInventoryItem_1

PK_InvInventoryItem

Database Object

Object Type

Description

Dep Level

BarCodeResolution

Stored Procedure

1

BreakBuildKit

Stored Procedure

1

CancelStockTransfer

Stored Procedure

1

CustomeLabelReport

Stored Procedure

2

Forecast

Stored Procedure

1

GenerateLabelData

Stored Procedure

Generates label data depending on the parameter specified

1

GetBOMQuantity

Stored Procedure

1

GetInventoryItemForStockCount

Stored Procedure

1

Integration_InventoryItem

Stored Procedure

1

Integration_InventoryItemPrice

Stored Procedure

1

InventoryCount_Save

Stored Procedure

1

InventoryItemQuantityUpdates

Stored Procedure

1

InvGetAllSelectedAttributeItemsList

Stored Procedure

2

InvGetMatrixItems

Stored Procedure

Applies Buy Some Get Some promotion on the transaction

1

Rpt_ItemWithoutSale

User Defined Function

1

TrgInvStockTransferDetail

Trigger

1

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateLaybyCancellationItem

Stored Procedure

1

TrxInventoryUpdateLaybyItem

Stored Procedure

1

TrxInventoryUpdateOrder

Stored Procedure

Handles updates related to Order item

1

TrxInventoryUpdateSaleDelivery

Stored Procedure

Handles updates related to delivery of a Sale item

1

TrxInventoryUpdateSaleItem

Stored Procedure

Handles updates related to sale item

1

TrxInventoryUpdatesSaleRefund

Stored Procedure

Handles updates related to On Account payments

1

UpdateInventoryCost

Stored Procedure

Used in replication to update the cost of an item if the costing method is set to enterprise

1

uspGetItemPrice

Stored Procedure

1

vw_InvInventoryItem

View

1

vw_InvProductInventoryInfo

View

1

vw_ProductCost

View

1

vw_SalesReportData

View

1

vwAnalytics_InventorySnapshot

View

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvInventoryItem](

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

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

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

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

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

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

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

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

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

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

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

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

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

[KitsAvailableToBuild] [decimal](15, 0) NOT NULL,

[AssembliesAvailableToBuild] [decimal](15, 0) NOT NULL,

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

[LeadTime] [int] NOT NULL,

[Created] [datetime] NOT NULL,

[Modified] [datetime] NOT NULL,

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

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

[IsLocked] [bit] NOT NULL,

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

[WasCounted] [bit] NOT NULL,

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

[NextCountDate] [datetime] NULL,

[NextCountTime] [datetime] NULL,

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

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

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

[ReplenishmentMethod] [int] NULL,

[ConsiderInMRP] [bit] NULL,

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

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

[TransactionType] [int] NULL,

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

[LastLabelPrintingDate] [datetime] NULL,

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

[CostProtectionMarginType] [int] NULL,

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

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

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

[IsTaxExempt] [bit] NULL,

[IsInclusiveTaxed] [bit] NULL,

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

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

[IsOnHold] [bit] NULL,

CONSTRAINT [PK_InvInventoryItem] PRIMARY KEY CLUSTERED

(

[InventoryItemKey] 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].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_PurchaseTaxKey] DEFAULT ((0)) FOR [PurchaseTaxKey]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_SalesTaxKey] DEFAULT ((0)) FOR [SalesTaxKey]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_InStockQuantity] DEFAULT ((0)) FOR [InStockQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_InReturnedQuantity] DEFAULT ((0)) FOR [InReturnQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_OnRentQuantity] DEFAULT ((0)) FOR [OnRentQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_LostQuantity] DEFAULT ((0)) FOR [LostQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_OnLayawayQuantity] DEFAULT ((0)) FOR [OnLayawayQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_OnOrderQuantity] DEFAULT ((0)) FOR [OnOrderQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_OnFulFillmentQuantity] DEFAULT ((0)) FOR [OnFulFillmentQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_AvailableQuantity] DEFAULT ((0)) FOR [AvailableQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_KitsAvailableToBuild] DEFAULT ((0)) FOR [KitsAvailableToBuild]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_AssembliesAvailableToBuild] DEFAULT ((0)) FOR [AssembliesAvailableToBuild]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_IsLocked] DEFAULT ((0)) FOR [IsLocked]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_CountedQuantity] DEFAULT ((0)) FOR [CountedQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_WasCounted] DEFAULT ((0)) FOR [WasCounted]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_InventoryCycleSetupKey] DEFAULT ((0)) FOR [InventoryCycleSetupKey]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_InTransitQuantity] DEFAULT ((0)) FOR [InTransitQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_MinimumStockLevel] DEFAULT ((0)) FOR [MinimumStockLevel]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_MaximumStockLevel] DEFAULT ((0)) FOR [MaximumStockLevel]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_TransactionType] DEFAULT ((0)) FOR [TransactionType]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_AllocatedQuantity] DEFAULT ((0)) FOR [AllocatedQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_CostProtectionMarginType] DEFAULT ((0)) FOR [CostProtectionMarginType]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_CostProtectionMarginValue] DEFAULT ((0)) FOR [CostProtectionMarginValue]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF_InvInventoryItem_ReservedQuantity] DEFAULT ((0)) FOR [ReservedQuantity]

ALTER TABLE [dbo].[InvInventoryItem] ADD CONSTRAINT [DF__InvInvent__IsOnH__7F76C749] DEFAULT ((0)) FOR [IsOnHold]