iVend Database Database : InvSerial Table

Creation Date

4/13/2015 12:00 PM

File Group

PRIMARY

Text File Group

PRIMARY

System Object

Published for Replication

Rows

128

Data Space Used

32.00 KB

Index Space Used

48.00 KB

Column Name

Description

Datatype

Length

Allow Nulls

Default

Formula

SerialKey

System-generated primary key.

VarWChar

50

SerialNumber

Value is the unique serial number of product.

VarWChar

50

WarehouseKey

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

VarWChar

50

ProductKey

Value is the reference key of product to which the serial 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 serial received.

DBTimeStamp

4

ExpirationDate

Value is the date when serial will expired.

DBTimeStamp

4

ManufacturingDate

Value is the manufacturing date of serial.

DBTimeStamp

4

Status

Value which denotes the current status of the serial availability.

Valid values are:

NotAvailable = 0

InStore = 1

Sold = 2

OnRent = 3 (For future use)

OnLayaway = 4

Lost = 5 (For future use)

Integer

4

SystemNumber

For future use.

Integer

4

ManufacturerSerialNumber

For future use.

VarWChar

50

LotNumber

For future use.

VarWChar

50

InternalSerialNumber

For future use.

VarWChar

50

CreatedBy

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

VarWChar

50

Created

Date of creation of this record

DBTimeStamp

4

ModifiedBy

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

VarWChar

50

Modified

Date of last modification of this record

DBTimeStamp

4

IsDeleted

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

Boolean

1

OriginalSerialKey

Serial key that was allocated to this serial number at the time of serial creation

VarWChar

50

Price

Stores the Price for the Serial Items.

Numeric

9 (20,5)

((0.00))

Details

For future use

LongVarWChar

16

LocationKey

Reference Key of Location. See also InvLocation.

VarWChar

50

Comments

Stores comments.

VarWChar

4000

Index

Description

Primary

Unique

IX_InvSerial

PK_InvSerial

Database Object

Object Type

Description

Dep Level

BarCodeResolution

Stored Procedure

1

CancelStockTransfer

Stored Procedure

1

CreateTillCount

Stored Procedure

1

DataNotification

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

InventoryItemSerialStatusUpdates

Stored Procedure

1

InvWarehouseInventoryLocation

Stored Procedure

1

StockTransferReport

Stored Procedure

1

TrgSBOSerialUpdate

Trigger

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

1

TrxCompleteTransactionUpdates

Stored Procedure

2

TrxInventoryUpdateSerialNumber

Stored Procedure

1

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[InvSerial](

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

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

[Status] [int] NOT NULL,

[SystemNumber] [int] NULL,

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

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

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

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

[Created] [datetime] NOT NULL,

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

[Modified] [datetime] NOT NULL,

[IsDeleted] [bit] NOT NULL,

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

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

[Details] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

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

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

CONSTRAINT [PK_InvSerial] PRIMARY KEY CLUSTERED

(

[SerialKey] 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].[InvSerial] ADD CONSTRAINT [DF_InvSerial_Price] DEFAULT ((0.00)) FOR [Price]