iVend Database Database : InvInventoryItemLog Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

System Object

Published for Replication

Rows

2013

Data Space Used

416.00 KB

Index Space Used

80.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

InventoryItemLogKey

Primary key

VarWChar

50

WarehouseKey

Refers to the warehouse from which the quantity is being affected. See also Warehouse

VarWChar

50

ProductKey

Refers to the product. See also Product

VarWChar

50

InQty

Quantity Received

Numeric

9 (20,5)

OutQty

Quantity Consumed

Numeric

9 (20,5)

SourceType

Refers to the source document.

Valid values are:

External = -1

Unknown = 0

GoodsReceipt = 1

GoodsReceiptPO = 2

GoodsReceiptSTN = 3 StockTransfer = 4

KitBuildBreak = 5

StoreUseInventory = 6

SaleTransaction = 7

LayawayTransaction = 8

SpecialOrderTransaction = 9

Integer

4

SourceKey

Refers to the source document key

VarWChar

50

SourceDetailKey

Refers to the source document detail key

VarWChar

50

UpdateType

Referes to the type of information that needs to be updated on the other store database.

Valid values are:

All = 0

AvaliableQuantity = 1

InStockQuantity = 2

Integer

4

Created

Date of creation of this record

DBTimeStamp

4

([dbo].[GetCompanyDateTime]())

OnFulfillmentQuanity

Stores the quantity of the product which is to be fulfilled from the warehouse.

Numeric

9 (20,5)

ItemCost

Value is the cost for an item

Numeric

9 (20,5)

AllocatedQuantity

Count is the Quantity allocated from the warehouse

Numeric

9 (20,5)

((0))

UpdatedAverageCost

Stores the updated average cost of an item which has costing method setup as Average

Numeric

9 (20,5)

LocationKey

Reference Key of Location. See also InvLocation.

VarWChar

50

((0))

IsProcessed

Value is true if the Inventory Items are processed

Boolean

1

((0))

ReservedQuantity

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

Numeric

9 (20,5)

((0))

Index

Description

Primary

Unique

IX_InvInventoryItemLog

PK_InvInventoryItemLog

Database Object

Object Type

Description

Dep Level

CancelStockTransfer

Stored Procedure

1

Daily_ProductCost

User Defined Function

1

ExecuteInventoryItemLogUpdate

Stored Procedure

1

InventoryItemQuantityUpdates

Stored Procedure

1

Rpt_ItemWithoutSale

User Defined Function

2

rptClosingBalanceQuantity

User Defined Function

1

rptOpeningBalance

User Defined Function

1

rptOpeningBalance_dailyClosingBal

User Defined Function

1

rptOpeningBalance_Location

User Defined Function

1

rptOpeningBalance_Stock

User Defined Function

1

TrgSBOInventoryUpdate

Trigger

Handles updates made to item inventory in the ERP system (Business One in this case)

1

TrxCompleteTransactionUpdates

Stored Procedure

1

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

vw_InventoryCost

View

1

vw_InventoryMovement

View

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvInventoryItemLog](

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

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

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

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

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

[SourceType] [int] NOT NULL,

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

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

[UpdateType] [int] NOT NULL,

[Created] [datetime] NOT NULL,

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

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

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

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

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

[IsProcessed] [bit] NOT NULL,

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

CONSTRAINT [PK_InvInventoryItemLog] PRIMARY KEY CLUSTERED

(

[InventoryItemLogKey] 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].[InvInventoryItemLog] ADD CONSTRAINT [DF_InvInventoryItemLog_Created] DEFAULT ([dbo].[GetCompanyDateTime]()) FOR [Created]

ALTER TABLE [dbo].[InvInventoryItemLog] ADD CONSTRAINT [DF_InvInventoryItemLog_AllocatedQuantity] DEFAULT ((0)) FOR [AllocatedQuantity]

ALTER TABLE [dbo].[InvInventoryItemLog] ADD CONSTRAINT [DF_InvInventoryItemLog_LocationKey] DEFAULT ((0)) FOR [LocationKey]

ALTER TABLE [dbo].[InvInventoryItemLog] ADD CONSTRAINT [DF__InvInvent__IsPro__45E94432] DEFAULT ((0)) FOR [IsProcessed]

ALTER TABLE [dbo].[InvInventoryItemLog] ADD CONSTRAINT [DF_InvInventoryItemLog_ReservedQuantity] DEFAULT ((0)) FOR [ReservedQuantity]