iVend Database Database : InvBatch Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

PRIMARY

System Object

Published for Replication

Rows

256

Data Space Used

96.00 KB

Index Space Used

64.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

BatchKey

Value is the system generated Primary Key of the table.

VarWChar

50

BatchNumber

Value is the unique batch number of product.

VarWChar

50

WarehouseKey

Value is the reference key of warehouse where the batch exist. See also Warehouse.

VarWChar

50

ProductKey

Value is the reference key of product to which this batch belongs. See also Product.

VarWChar

50

CommentKey

Value is the reference key of comment. See also Comment.

VarWChar

50

AdmissionDate

Value is the date when batch received.

DBTimeStamp

4

ExpirationDate

Value is the date when batch will expired.

DBTimeStamp

4

ManufacturingDate

Value is the manufacturing date of batch.

DBTimeStamp

4

InStockQuantity

Value is the quantity of batch in stock.

Numeric

9 (20,4)

CommittedQuantity

For future use.

Numeric

9 (20,4)

OnOrderQuantity

Value is the quantity of batch which is on order.

Numeric

9 (20,4)

OnRentQuantity

For future use

Numeric

9 (20,4)

((0))

LostQuantity

For future use

Numeric

9 (20,4)

InReturnQuantity

For future use

Numeric

9 (20,4)

AvailableQuantity

Value is the quantity of batch which is available at warehouse.

Numeric

9 (20,4)

OnLayawayQuantity

Value is the quantity of batch which is on layaway.

Numeric

9 (20,4)

ReleasedQuantity

For future use.

Numeric

9 (20,4)

Status

Value which denotes the current status of the batch, in the form of Available or NotAvailable.

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

IsDeleted

Value is True if the record has been deleted from the system. Record is always Soft Deleted

Boolean

1

Price

Stores the Price for the Batch Items.

Numeric

9 (20,5)

((0.00))

Details

Stores the batch details

LongVarWChar

16

AllocatedQuantity

Value is the quantity used to create a delivery package

Numeric

9 (20,4)

ReservedQuantity

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

Numeric

9 (20,4)

((0))

Comments

Stores comments.

VarWChar

4000

Index

Description

Primary

Unique

IX_InvBatch

PK_InvBatch

Database Object

Object Type

Description

Dep Level

BarCodeResolution

Stored Procedure

1

BatchQuantityUpdates

Stored Procedure

1

CancelStockTransfer

Stored Procedure

1

ExecuteInventoryItemLogUpdate

Stored Procedure

2

GetMobileReplicationData

Stored Procedure

1

GoodsIssueReport

Stored Procedure

1

GoodsReceiptPOReport

Stored Procedure

1

GoodsReceiptReport

Stored Procedure

1

Integration_Consolidated_Transaction

Stored Procedure

1

Integration_GoodsIssue_SerialBatch

Stored Procedure

1

Integration_GoodsReceiptSerialBatch

Stored Procedure

1

Integration_GoodsReceiptSerialBatchShortReceived

Stored Procedure

1

Integration_GoodsReturnSerialBatch

Stored Procedure

1

Integration_StockTransferSerialBatch

Stored Procedure

1

InvWarehouseInventoryLocation

Stored Procedure

1

StockTransferReport

Stored Procedure

1

TrgSBOBatchUpdate

Trigger

Handles updates made to Batch table from ERP system (Business One in this case)

1

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateCheckNegativeInventory

Stored Procedure

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvBatch](

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

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

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

[AdmissionDate] [datetime] NULL,

[ExpirationDate] [datetime] NULL,

[ManufacturingDate] [datetime] NULL,

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

[CommittedQuantity] [decimal](20, 4) NOT NULL,

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

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

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

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

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

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

[ReleasedQuantity] [decimal](20, 4) NULL,

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

[IsDeleted] [bit] NOT NULL,

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

[Details] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

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

CONSTRAINT [PK_InvBatch] PRIMARY KEY CLUSTERED

(

[BatchKey] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[InvBatch] ADD CONSTRAINT [DF_InvBatch_OnRentQuantity] DEFAULT ((0)) FOR [OnRentQuantity]

ALTER TABLE [dbo].[InvBatch] ADD CONSTRAINT [DF_InvBatch_Price] DEFAULT ((0.00)) FOR [Price]

ALTER TABLE [dbo].[InvBatch] ADD CONSTRAINT [DF_InvBatch_ReservedQuantity] DEFAULT ((0)) FOR [ReservedQuantity]