Tables [dbo].[ICItems]
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)0
Created12:55:38 AM Thursday, March 21, 2019
Last Modified1:15:56 AM Thursday, March 21, 2019
KeyNameData TypeMax Length (Bytes)Allow NullsDefault
Cluster Primary Key PK_ICItems: ItemIDItemIDint4
False
Itemvarchar(255)255
False
Descriptionvarchar(255)255
False
BarCodevarchar(255)255
False
Serializedbit1
False
WebEnabledbit1
False
Foreign Keys FK_ICItems_UMID_ICUnits: [dbo].[ICUnits].UMIDUMIDint4
False
Foreign Keys FK_ICItems_CategoryID_ICCategories: [dbo].[ICCategories].CategoryIDCategoryIDint4
False
Foreign Keys FK_ICItems_InventoryCodeID_ICInventoryCodes: [dbo].[ICInventoryCodes].InventoryCodeIDInventoryCodeIDint4
True
Costmoney8
False
Foreign Keys FK_ICItems_ExpenseCodeID_ICExpenseCodes: [dbo].[ICExpenseCodes].ExpenseCodeIDExpenseCodeIDint4
True
Foreign Keys FK_ICItems_SalesCodeID_ICSalesCodes: [dbo].[ICSalesCodes].SalesCodeIDSalesCodeIDint4
True
BackOrderbit1
False
Taxbit1
False
RoundPricechar(2)2
False
PriceAdjustmentmoney8
False
Foreign Keys FK_ICItems_ServiceCodeID_ICServiceCodes: [dbo].[ICServiceCodes].ServiceCodeIDServiceCodeIDint4
True
Yieldint4
False
Foreign Keys FK_ICItems_EquipmentCodeID_ICEquipmentCodes: [dbo].[ICEquipmentCodes].EquipmentCodeIDEquipmentCodeIDint4
True
StdOverheadmoney8
False
MfgLaborHoursdecimal(16,6)9
False
MfgLaborRatemoney8
False
MfgTypechar(2)2
False
SourceCodevarchar(255)255
False
Weightdecimal(16,6)9
False
Foreign Keys FK_ICItems_WeightUMID_ICUnits: [dbo].[ICUnits].WeightUMIDWeightUMIDint4
True
OnHandQtydecimal(18,6)9
False
((0))
OnHandCostmoney8
False
((0))
AvgCostmoney8
False
((0))
Ordereddecimal(18,6)9
False
((0))
Allocateddecimal(18,6)9
False
((0))
Activebit1
False
Locksint4
False
((0))
CreatorIDchar(8)8
False
UpdatorIDchar(8)8
False
CreateDatedatetime8
False
(getdate())
LastUpdatedatetime8
False
(getdate())
timestamptimestamp8
True
Foreign Keys FK_ICItems_ModelID_ICModels: [dbo].[ICModels].ModelIDModelIDint4
True
TaxFlagint4
False
((1))
EnableDefaultPricebit1
False
((0))
DefaultPricemoney8
False
((0))
LastCostmoney8
False
((0))
LastCostDatedatetime8
True
Remarksvarchar(1024)1024
False
('')
StandardQtydecimal(18,6)9
False
((0))
DefectiveQtydecimal(18,6)9
False
((0))
UnavailableQtydecimal(18,6)9
False
((0))
BaseChargeOutCostmoney8
False
((0))
CommissionClassIDint4
True
Foreign Keys FK_ICItems_DBFileHeaderID_DBFileHeader: [dbo].[DBFileHeader].DBFileHeaderIDDBFileHeaderIDint4
False
((1))
EnableContractUsageLimitsbit1
False
((0))
Foreign Keys FK_ICItems_UsageLimitMeterTypeID_MTMeterTypes: [dbo].[MTMeterTypes].UsageLimitMeterTypeIDUsageLimitMeterTypeIDint4
True
IsReturnablebit1
False
((0))
WarrantyEffectiveStartDatedatetime8
True
WarrantyEffectiveEndDatedatetime8
True
Foreign Keys FK_ICItems_StockingCodeID_ICStockingCodes: [dbo].[ICStockingCodes].StockingCodeIDStockingCodeIDint4
True
BackOrdereddecimal(18,6)9
False
((0))
EffectiveOnHandQtydecimal(18,6)9
False
((0))
EffectiveOnHandCostmoney8
False
((0))
Foreign Keys FK_ICItems_NoteID_ICItemNotes: [dbo].[ICItemNotes].NoteIDNoteIDint4
True
NoteFlagint4
False
((0))
UseBaseOutCostbit1
False
((0))
BaseOutCostmoney8
False
((0))
Foreign Keys FK_ICItems_OutCostGroupID_ICOutCostGroups: [dbo].[ICOutCostGroups].OutCostGroupIDOutCostGroupIDint4
False
((1))
AutoSerializebit1
False
((0))
NextSerialNumbervarchar(32)32
False
('')
Foreign Keys FK_ICItems_ShTrackingConfigID_ShTrackingConfigs: [dbo].[ShTrackingConfigs].ShTrackingConfigIDShTrackingConfigIDint4
True
Foreign Keys FK_ICItems_PrefVendorID_APVendors: [dbo].[APVendors].PrefVendorIDPrefVendorIDint4
True
PrefMfgNumbervarchar(32)32
False
('')
cuTypeCodevarchar(16)16
True
cuUsedByvarchar(16)16
True
IsOEMCompatibleItembit1
False
((0))
OEMNumbervarchar(33)33
False
('')
NoteCountint4
False
((0))
ContractRatemoney8
False
((0))
Foreign Keys FK_ICItems_ContractRateCycleID_SCBillingCycles: [dbo].[SCBillingCycles].ContractRateCycleIDContractRateCycleIDint4
True
ContractRateRoundNoDigitsint4
False
((0))
ContractRateFlagint4
False
((0))
Foreign Keys FK_ICItems_CoreChargeItemID_ICItems: [dbo].[ICItems].CoreChargeItemIDCoreChargeItemIDint4
True
ServiceableAsItembit1
False
((0))
Foreign Keys FK_ICItems_BaseDistributionCodeID_SCContractBaseDistCodes: [dbo].[SCContractBaseDistCodes].BaseDistributionCodeIDBaseDistributionCodeIDint4
True
Foreign Keys FK_ICItems_TaxFlagID_ShTaxFlags: [dbo].[ShTaxFlags].TaxFlagIDTaxFlagIDint4
False
((2))
StockingSetSizeint4
False
((1))
KeyNameKey ColumnsUniqueFill Factor
Cluster Primary Key PK_ICItems: ItemIDPK_ICItemsItemID
True
80
NameColumns
FK_ICItems_BaseDistributionCodeID_SCContractBaseDistCodesBaseDistributionCodeID->[dbo].[SCContractBaseDistCodes].[BaseDistributionCodeID]
FK_ICItems_CategoryID_ICCategoriesCategoryID->[dbo].[ICCategories].[CategoryID]
FK_ICItems_ContractRateCycleID_SCBillingCyclesContractRateCycleID->[dbo].[SCBillingCycles].[BillingCycleID]
FK_ICItems_CoreChargeItemID_ICItemsCoreChargeItemID->[dbo].[ICItems].[ItemID]
FK_ICItems_DBFileHeaderID_DBFileHeaderDBFileHeaderID->[dbo].[DBFileHeader].[DBFileHeaderID]
FK_ICItems_EquipmentCodeID_ICEquipmentCodesEquipmentCodeID->[dbo].[ICEquipmentCodes].[EquipmentCodeID]
FK_ICItems_ExpenseCodeID_ICExpenseCodesExpenseCodeID->[dbo].[ICExpenseCodes].[ExpenseCodeID]
FK_ICItems_InventoryCodeID_ICInventoryCodesInventoryCodeID->[dbo].[ICInventoryCodes].[InventoryCodeID]
FK_ICItems_ModelID_ICModelsModelID->[dbo].[ICModels].[ModelID]
FK_ICItems_NoteID_ICItemNotesNoteID->[dbo].[ICItemNotes].[NoteID]
FK_ICItems_OutCostGroupID_ICOutCostGroupsOutCostGroupID->[dbo].[ICOutCostGroups].[OutCostGroupID]
FK_ICItems_PrefVendorID_APVendorsPrefVendorID->[dbo].[APVendors].[VendorID]
FK_ICItems_SalesCodeID_ICSalesCodesSalesCodeID->[dbo].[ICSalesCodes].[SalesCodeID]
FK_ICItems_ServiceCodeID_ICServiceCodesServiceCodeID->[dbo].[ICServiceCodes].[ServiceCodeID]
FK_ICItems_ShTrackingConfigID_ShTrackingConfigsShTrackingConfigID->[dbo].[ShTrackingConfigs].[ShTrackingConfigID]
FK_ICItems_StockingCodeID_ICStockingCodesStockingCodeID->[dbo].[ICStockingCodes].[StockingCodeID]
FK_ICItems_TaxFlagID_ShTaxFlagsTaxFlagID->[dbo].[ShTaxFlags].[TaxFlagID]
FK_ICItems_UMID_ICUnitsUMID->[dbo].[ICUnits].[ID]
FK_ICItems_UsageLimitMeterTypeID_MTMeterTypesUsageLimitMeterTypeID->[dbo].[MTMeterTypes].[MeterTypeID]
FK_ICItems_WeightUMID_ICUnitsWeightUMID->[dbo].[ICUnits].[ID]
CREATE TABLE [dbo].[ICItems]
(
[ItemID] [int] NOT NULL,
[Item] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BarCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Serialized] [bit] NOT NULL,
[WebEnabled] [bit] NOT NULL,
[UMID] [int] NOT NULL,
[CategoryID] [int] NOT NULL,
[InventoryCodeID] [int] NULL,
[Cost] [money] NOT NULL,
[ExpenseCodeID] [int] NULL,
[SalesCodeID] [int] NULL,
[BackOrder] [bit] NOT NULL,
[Tax] [bit] NOT NULL,
[RoundPrice] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PriceAdjustment] [money] NOT NULL,
[ServiceCodeID] [int] NULL,
[Yield] [int] NOT NULL,
[EquipmentCodeID] [int] NULL,
[StdOverhead] [money] NOT NULL,
[MfgLaborHours] [decimal] (16, 6) NOT NULL,
[MfgLaborRate] [money] NOT NULL,
[MfgType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceCode] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Weight] [decimal] (16, 6) NOT NULL,
[WeightUMID] [int] NULL,
[OnHandQty] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_OnHandQty] DEFAULT ((0)),
[OnHandCost] [money] NOT NULL CONSTRAINT [DF_ICItems_OnHandCost] DEFAULT ((0)),
[AvgCost] [money] NOT NULL CONSTRAINT [DF_ICItems_AvgCost] DEFAULT ((0)),
[Ordered] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_Ordered] DEFAULT ((0)),
[Allocated] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_Allocated] DEFAULT ((0)),
[Active] [bit] NOT NULL,
[Locks] [int] NOT NULL CONSTRAINT [DF_ICItems_Locks] DEFAULT ((0)),
[CreatorID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UpdatorID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ICItems_CreateDate] DEFAULT (getdate()),
[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_ICItems_LastUpdate] DEFAULT (getdate()),
[timestamp] [timestamp] NULL,
[ModelID] [int] NULL,
[TaxFlag] [int] NOT NULL CONSTRAINT [DF_ICItems_TaxFlag] DEFAULT ((1)),
[EnableDefaultPrice] [bit] NOT NULL CONSTRAINT [DF_ICItems_EnableDefaultPrice] DEFAULT ((0)),
[DefaultPrice] [money] NOT NULL CONSTRAINT [DF_ICItems_DefaultPrice] DEFAULT ((0)),
[LastCost] [money] NOT NULL CONSTRAINT [DF_ICItems_LastCost] DEFAULT ((0)),
[LastCostDate] [datetime] NULL,
[Remarks] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ICItems_Remarks] DEFAULT (''),
[StandardQty] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_StandardQty] DEFAULT ((0)),
[DefectiveQty] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_DefectiveQty] DEFAULT ((0)),
[UnavailableQty] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_UnavailableQty] DEFAULT ((0)),
[BaseChargeOutCost] [money] NOT NULL CONSTRAINT [DF_ICItems_BaseChargeOutCost] DEFAULT ((0)),
[CommissionClassID] [int] NULL,
[DBFileHeaderID] [int] NOT NULL CONSTRAINT [DF_ICItems_DBFileHeaderID] DEFAULT ((1)),
[EnableContractUsageLimits] [bit] NOT NULL CONSTRAINT [DF_ICItems_EnableContractUsageLimits] DEFAULT ((0)),
[UsageLimitMeterTypeID] [int] NULL,
[IsReturnable] [bit] NOT NULL CONSTRAINT [DF_ICItems_IsReturnable] DEFAULT ((0)),
[WarrantyEffectiveStartDate] [datetime] NULL,
[WarrantyEffectiveEndDate] [datetime] NULL,
[StockingCodeID] [int] NULL,
[BackOrdered] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_BackOrdered] DEFAULT ((0)),
[EffectiveOnHandQty] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICItems_EffectiveOnHandQty] DEFAULT ((0)),
[EffectiveOnHandCost] [money] NOT NULL CONSTRAINT [DF_ICItems_EffectiveOnHandCost] DEFAULT ((0)),
[NoteID] [int] NULL,
[NoteFlag] [int] NOT NULL CONSTRAINT [DF_ICItems_NoteFlag] DEFAULT ((0)),
[UseBaseOutCost] [bit] NOT NULL CONSTRAINT [DF_ICItems_UseBaseOutCost] DEFAULT ((0)),
[BaseOutCost] [money] NOT NULL CONSTRAINT [DF_ICItems_BaseOutCost] DEFAULT ((0)),
[OutCostGroupID] [int] NOT NULL CONSTRAINT [DF_ICItems_OutCostGroupID] DEFAULT ((1)),
[AutoSerialize] [bit] NOT NULL CONSTRAINT [DF_ICItems_AutoSerialize] DEFAULT ((0)),
[NextSerialNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ICItems_NextSerialNumber] DEFAULT (''),
[ShTrackingConfigID] [int] NULL,
[PrefVendorID] [int] NULL,
[PrefMfgNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ICItems_PrefMfgNumber] DEFAULT (''),
[cuTypeCode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cuUsedBy] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsOEMCompatibleItem] [bit] NOT NULL CONSTRAINT [DF_ICItems_IsOEMCompatibleItem] DEFAULT ((0)),
[OEMNumber] [varchar] (33) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ICItems_OEMNumber] DEFAULT (''),
[NoteCount] [int] NOT NULL CONSTRAINT [DF_ICItems_NoteCount] DEFAULT ((0)),
[ContractRate] [money] NOT NULL CONSTRAINT [DF_ICItems_ContractRate] DEFAULT ((0)),
[ContractRateCycleID] [int] NULL,
[ContractRateRoundNoDigits] [int] NOT NULL CONSTRAINT [DF_ICItems_ContractRateRoundNoDigits] DEFAULT ((0)),
[ContractRateFlag] [int] NOT NULL CONSTRAINT [DF_ICItems_ContractRateFlag] DEFAULT ((0)),
[CoreChargeItemID] [int] NULL,
[ServiceableAsItem] [bit] NOT NULL CONSTRAINT [DF_ICItems_ServiceableAsItem] DEFAULT ((0)),
[BaseDistributionCodeID] [int] NULL,
[TaxFlagID] [int] NOT NULL CONSTRAINT [DF_ICItems_TaxFlagID] DEFAULT ((2)),
[StockingSetSize] [int] NOT NULL CONSTRAINT [DF_ICItems_StockingSetSize] DEFAULT ((1))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [PK_ICItems] PRIMARY KEY CLUSTERED  ([ItemID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_BaseDistributionCodeID_SCContractBaseDistCodes] FOREIGN KEY ([BaseDistributionCodeID]) REFERENCES [dbo].[SCContractBaseDistCodes] ([BaseDistributionCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_CategoryID_ICCategories] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[ICCategories] ([CategoryID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_ContractRateCycleID_SCBillingCycles] FOREIGN KEY ([ContractRateCycleID]) REFERENCES [dbo].[SCBillingCycles] ([BillingCycleID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_CoreChargeItemID_ICItems] FOREIGN KEY ([CoreChargeItemID]) REFERENCES [dbo].[ICItems] ([ItemID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_DBFileHeaderID_DBFileHeader] FOREIGN KEY ([DBFileHeaderID]) REFERENCES [dbo].[DBFileHeader] ([DBFileHeaderID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_EquipmentCodeID_ICEquipmentCodes] FOREIGN KEY ([EquipmentCodeID]) REFERENCES [dbo].[ICEquipmentCodes] ([EquipmentCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_ExpenseCodeID_ICExpenseCodes] FOREIGN KEY ([ExpenseCodeID]) REFERENCES [dbo].[ICExpenseCodes] ([ExpenseCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_InventoryCodeID_ICInventoryCodes] FOREIGN KEY ([InventoryCodeID]) REFERENCES [dbo].[ICInventoryCodes] ([InventoryCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_ModelID_ICModels] FOREIGN KEY ([ModelID]) REFERENCES [dbo].[ICModels] ([ModelID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_NoteID_ICItemNotes] FOREIGN KEY ([NoteID]) REFERENCES [dbo].[ICItemNotes] ([NoteID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_OutCostGroupID_ICOutCostGroups] FOREIGN KEY ([OutCostGroupID]) REFERENCES [dbo].[ICOutCostGroups] ([OutCostGroupID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_PrefVendorID_APVendors] FOREIGN KEY ([PrefVendorID]) REFERENCES [dbo].[APVendors] ([VendorID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_SalesCodeID_ICSalesCodes] FOREIGN KEY ([SalesCodeID]) REFERENCES [dbo].[ICSalesCodes] ([SalesCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_ServiceCodeID_ICServiceCodes] FOREIGN KEY ([ServiceCodeID]) REFERENCES [dbo].[ICServiceCodes] ([ServiceCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_ShTrackingConfigID_ShTrackingConfigs] FOREIGN KEY ([ShTrackingConfigID]) REFERENCES [dbo].[ShTrackingConfigs] ([ShTrackingConfigID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_StockingCodeID_ICStockingCodes] FOREIGN KEY ([StockingCodeID]) REFERENCES [dbo].[ICStockingCodes] ([StockingCodeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_TaxFlagID_ShTaxFlags] FOREIGN KEY ([TaxFlagID]) REFERENCES [dbo].[ShTaxFlags] ([TaxFlagID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_UMID_ICUnits] FOREIGN KEY ([UMID]) REFERENCES [dbo].[ICUnits] ([ID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_UsageLimitMeterTypeID_MTMeterTypes] FOREIGN KEY ([UsageLimitMeterTypeID]) REFERENCES [dbo].[MTMeterTypes] ([MeterTypeID])
GO
ALTER TABLE [dbo].[ICItems] ADD CONSTRAINT [FK_ICItems_WeightUMID_ICUnits] FOREIGN KEY ([WeightUMID]) REFERENCES [dbo].[ICUnits] ([ID])
GO