Production.BillOfMaterials

Object type: Table

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

Columns

Name Data type Nullable Description

BillOfMaterialsID int No Column has identity seed value of 1 with a step value of 1.
Primary key for BillOfMaterials records.

ProductAssemblyID int Yes Parent product identification number. Foreign key to Product.ProductID.

ComponentID int No Component identification number. Foreign key to Product.ProductID.

StartDate datetime No Default value: GETDATE()
Date the component started being used in the assembly item.

EndDate datetime Yes Date the component stopped being used in the assembly item.

UnitMeasureCode nchar(3) No Standard code identifying the unit of measure for the quantity.

BOMLevel smallint No Indicates the depth the component is from its parent (AssemblyID).

PerAssemblyQty decimal(8, 2) No Default value: 1.00
Quantity of the component needed to create the assembly.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_BillOfMaterials_StartDate DEFAULT StartDate Value: GETDATE()
Default constraint value of GETDATE()

DF_BillOfMaterials_PerAssemblyQty DEFAULT PerAssemblyQty Value: 1.00
Default constraint value of 1.0

DF_BillOfMaterials_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_BillOfMaterials_EndDate CHECK N/A Value: (([EndDate] > [StartDate]) OR ([EndDate] IS NULL) )
Check constraint EndDate]>[StartDate] OR [EndDate] IS NULL

CK_BillOfMaterials_ProductAssemblyID CHECK N/A Value: ([ProductAssemblyID] <> [ComponentID])
Check constraint [ProductAssemblyID]<>[ComponentID]

CK_BillOfMaterials_BOMLevel CHECK N/A Value: ((([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_PerAssemblyQty CHECK N/A Value: ([PerAssemblyQty] >= 1.00)
Check constraint [PerAssemblyQty]>=(1.00)

PK_BillOfMaterials_BillOfMaterialsID PRIMARY KEY BillOfMaterialsID Nonclustered index created by a primary key constraint.

FK_BillOfMaterials_Product_ProductAssemblyID FOREIGN KEY ProductAssemblyID Reference table: Production.Product
Foreign key constraint referencing Product.ProductAssemblyID.

FK_BillOfMaterials_Product_ComponentID FOREIGN KEY ComponentID Reference table: Production.Product
Foreign key constraint referencing Product.ComponentID.

FK_BillOfMaterials_UnitMeasure_UnitMeasureCode FOREIGN KEY UnitMeasureCode Reference table: Production.UnitMeasure
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

Indices

Name Description

AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate Clustered index.

IX_BillOfMaterials_UnitMeasureCode Nonclustered index.

Triggers

Name Description

Production.uBillOfMaterials AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date.

Accessed by

Name Description

dbo.uspGetBillOfMaterials Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.

dbo.uspGetWhereUsedProductID Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.

Modified by

Name Description

Production.uBillOfMaterials AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date.

Applies to

Tables | User

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

Name	Type	Columns	Description
DF_BillOfMaterials_StartDate	DEFAULT	StartDate	Value: GETDATE() Default constraint value of GETDATE()
DF_BillOfMaterials_PerAssemblyQty	DEFAULT	PerAssemblyQty	Value: 1.00 Default constraint value of 1.0
DF_BillOfMaterials_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_BillOfMaterials_EndDate	CHECK	N/A	Value: (([EndDate] > [StartDate]) OR ([EndDate] IS NULL) ) Check constraint EndDate]>[StartDate] OR [EndDate] IS NULL
CK_BillOfMaterials_ProductAssemblyID	CHECK	N/A	Value: ([ProductAssemblyID] <> [ComponentID]) Check constraint [ProductAssemblyID]<>[ComponentID]
CK_BillOfMaterials_BOMLevel	CHECK	N/A	Value: ((([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_PerAssemblyQty	CHECK	N/A	Value: ([PerAssemblyQty] >= 1.00) Check constraint [PerAssemblyQty]>=(1.00)
PK_BillOfMaterials_BillOfMaterialsID	PRIMARY KEY	BillOfMaterialsID	Nonclustered index created by a primary key constraint.
FK_BillOfMaterials_Product_ProductAssemblyID	FOREIGN KEY	ProductAssemblyID	Reference table: Production.Product Foreign key constraint referencing Product.ProductAssemblyID.
FK_BillOfMaterials_Product_ComponentID	FOREIGN KEY	ComponentID	Reference table: Production.Product Foreign key constraint referencing Product.ComponentID.
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	FOREIGN KEY	UnitMeasureCode	Reference table: Production.UnitMeasure Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

Name	Description
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	Clustered index.
IX_BillOfMaterials_UnitMeasureCode	Nonclustered index.

Name	Description
Production.uBillOfMaterials	AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date.

Name	Description
dbo.uspGetBillOfMaterials	Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
dbo.uspGetWhereUsedProductID	Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.