Production.BillOfMaterials (table)

wikibot[edit]

Table	Production.BillOfMaterials
Description	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Column	Data Type	Nullable	Default	Description
BillOfMaterialsID	int	not null		Primary key for BillOfMaterials records.
ProductAssemblyID	int	null		Parent product identification number. Foreign key to Product.ProductID.
ComponentID	int	not null		Component identification number. Foreign key to Product.ProductID.
StartDate	datetime	not null	(getdate())	Date the component started being used in the assembly item.
EndDate	datetime	null		Date the component stopped being used in the assembly item.
UnitMeasureCode	nchar(3)	not null		Standard code identifying the unit of measure for the quantity.
BOMLevel	smallint	not null		Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty	decimal(8, 2)	not null	((1.00))	Quantity of the component needed to create the assembly.
ModifiedDate	datetime	not null	(getdate())	Date and time the record was last updated.

Primary Key	Columns
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID

Index	Type	Columns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	Unique	ProductAssemblyID, ComponentID, StartDate
IX_BillOfMaterials_UnitMeasureCode		UnitMeasureCode

Check Constraint	Expression	Description
CK_BillOfMaterials_BOMLevel	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))	Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
CK_BillOfMaterials_EndDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)	Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
CK_BillOfMaterials_PerAssemblyQty	([PerAssemblyQty]>=(1.00))	Check constraint [PerAssemblyQty] >= (1.00)
CK_BillOfMaterials_ProductAssemblyID	([ProductAssemblyID]<>[ComponentID])	Check constraint [ProductAssemblyID] <> [ComponentID]

Dependency Type	Object Type	Referenced Object
Schema	Schema	Production

Reference Type	Object Type	Referencing Object
Select	Procedure	dbo.uspGetBillOfMaterials
Select	Procedure	dbo.uspGetWhereUsedProductID

Table	Production.BillOfMaterials
Description	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Column	Data Type	Nullable	Default	Description / PK / Index
BillOfMaterialsID	int	not null		Primary key for BillOfMaterials records. PK_BillOfMaterials_BillOfMaterialsID
ProductAssemblyID	int	null		Parent product identification number. Foreign key to Product.ProductID. AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ComponentID	int	not null		Component identification number. Foreign key to Product.ProductID. AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
StartDate	datetime	not null	(GETDATE())	Date the component started being used in the assembly item. AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
EndDate	datetime	null		Date the component stopped being used in the assembly item.
UnitMeasureCode	nchar(3)	not null		Standard code identifying the unit of measure for the quantity. IX_BillOfMaterials_UnitMeasureCode
BOMLevel	smallint	not null		Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty	decimal(8, 2)	not null	(1.00)	Quantity of the component needed to create the assembly.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Dependency Type	Object Type	Referenced Object
Schema	Schema	Production

Reference Type	Object Type	Referencing Object
Select	Procedure	dbo.uspGetBillOfMaterials
Select	Procedure	dbo.uspGetWhereUsedProductID