CREATE TABLE [dbo].[ICPhysicalInventory]
(
[PhysicalInventoryNo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DetailID] [int] NOT NULL,
[WarehouseID] [int] NOT NULL,
[BinID] [int] NOT NULL,
[ItemID] [int] NULL,
[Serialized] [bit] NULL,
[SerialNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tag] [int] NOT NULL,
[QuantityOnHand] [decimal] (18, 6) NOT NULL,
[Cost] [money] NOT NULL,
[SheetNo] [int] NOT NULL,
[QuantityCounted] [decimal] (18, 6) NULL,
[InventoriedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Posted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[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_ICPhysicalInventory_CreateDate] DEFAULT (getdate()),
[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_LastUpdate] DEFAULT (getdate()),
[timestamp] [timestamp] NOT NULL,
[PhysicalInventoryListID] [int] NOT NULL,
[PhysicalInventoryID] [int] NOT NULL IDENTITY(1, 1),
[AddedLater] [bit] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_AddedLater] DEFAULT ((0)),
[EquipmentID] [int] NULL,
[IsAttachedAccessory] [bit] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_IsAttachedAccessory] DEFAULT ((0)),
[AssembledBinID] [int] NULL,
[ParentEquipmentID] [int] NULL,
[MasterEquipmentID] [int] NULL,
[SubComponentQuantity] [decimal] (18, 6) NOT NULL CONSTRAINT [DF_ICPhysicalInventory_SubComponentQuantity] DEFAULT ((0)),
[SubComponentIsConsumed] [bit] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_SubComponentIsConsumed] DEFAULT ((0)),
[SubComponentDepth] [int] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_SubComponentDepth] DEFAULT ((0)),
[SubComponentID] [int] NULL,
[QuantityAllocated] [bit] NOT NULL CONSTRAINT [DF_ICPhysicalInventory_QuantityAllocated] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [PK_ICPhysicalInventory] PRIMARY KEY CLUSTERED ([PhysicalInventoryID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_BinID_ICBins] FOREIGN KEY ([BinID]) REFERENCES [dbo].[ICBins] ([BinID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_EquipmentID_SCEquipments] FOREIGN KEY ([EquipmentID]) REFERENCES [dbo].[SCEquipments] ([EquipmentID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_ItemID_ICItems] FOREIGN KEY ([ItemID]) REFERENCES [dbo].[ICItems] ([ItemID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_MasterEquipmentID_SCEquipments] FOREIGN KEY ([MasterEquipmentID]) REFERENCES [dbo].[SCEquipments] ([EquipmentID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_ParentEquipmentID_SCEquipments] FOREIGN KEY ([ParentEquipmentID]) REFERENCES [dbo].[SCEquipments] ([EquipmentID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_PhysicalInventoryListID_ICPhysicalInventoryLists] FOREIGN KEY ([PhysicalInventoryListID]) REFERENCES [dbo].[ICPhysicalInventoryLists] ([PhysicalInventoryListID])
GO
ALTER TABLE [dbo].[ICPhysicalInventory] ADD CONSTRAINT [FK_ICPhysicalInventory_WarehouseID_ICWarehouses] FOREIGN KEY ([WarehouseID]) REFERENCES [dbo].[ICWarehouses] ([WarehouseID])
GO