Difference between revisions of "Production.BillOfMaterials (table)"

From dbscript Online Help
Jump to: navigation, search
 
(10 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
 
| '''Table
 
| '''Table
 
| Production.BillOfMaterials
 
| Production.BillOfMaterials
−
|-
+
|- valign="top"
 +
| '''Description
 +
| Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
 
|}
 
|}
  
−
 
+
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| BillOfMaterialsID
 
| BillOfMaterialsID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_BillOfMaterials_BillOfMaterialsID
+
| Primary key for BillOfMaterials records.
−
|
+
|- valign="top"
−
|-
 
 
| ProductAssemblyID
 
| ProductAssemblyID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Parent product identification number. Foreign key to Product.ProductID.
−
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
|- valign="top"
−
|-
 
 
| ComponentID
 
| ComponentID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Component identification number. Foreign key to Product.ProductID.
−
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
|- valign="top"
−
|-
 
 
| StartDate
 
| StartDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
|  
+
| Date the component started being used in the assembly item.
−
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
|- valign="top"
−
|-
 
 
| EndDate
 
| EndDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Date the component stopped being used in the assembly item.
−
|
+
|- valign="top"
−
|-
 
 
| UnitMeasureCode
 
| UnitMeasureCode
 
| nchar(3)
 
| nchar(3)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Standard code identifying the unit of measure for the quantity.
−
| IX_BillOfMaterials_UnitMeasureCode
+
|- valign="top"
−
|-
 
 
| BOMLevel
 
| BOMLevel
 
| smallint
 
| smallint
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Indicates the depth the component is from its parent (AssemblyID).
−
|
+
|- valign="top"
−
|-
 
 
| PerAssemblyQty
 
| PerAssemblyQty
 
| decimal(8, 2)
 
| decimal(8, 2)
 
| not null
 
| not null
 
| ((1.00))
 
| ((1.00))
 +
| Quantity of the component needed to create the assembly.
 +
|- valign="top"
 +
| ModifiedDate
 +
| datetime
 +
| not null
 +
| (getdate())
 +
| Date and time the record was last updated.
 +
|}
 +
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|- valign="top"
 +
| PK_BillOfMaterials_BillOfMaterialsID
 +
| BillOfMaterialsID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|- valign="top"
 +
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
 +
| Unique
 +
| ProductAssemblyID, ComponentID, StartDate
 +
|- valign="top"
 +
| IX_BillOfMaterials_UnitMeasureCode
 +
|
 +
| UnitMeasureCode
 +
|}
 +
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
 +
 +
|- valign="top"
 +
| 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)
 +
|- valign="top"
 +
| CK_BillOfMaterials_EndDate
 +
| ([EndDate]>[StartDate] OR [EndDate] IS NULL)
 +
| Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
 +
|- valign="top"
 +
| CK_BillOfMaterials_PerAssemblyQty
 +
| ([PerAssemblyQty]>=(1.00))
 +
| Check constraint [PerAssemblyQty] >= (1.00)
 +
|- valign="top"
 +
| CK_BillOfMaterials_ProductAssemblyID
 +
| ([ProductAssemblyID]<>[ComponentID])
 +
| Check constraint [ProductAssemblyID] <> [ComponentID]
 +
|}
 +
 +
=== Foreign Keys ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Relation
 +
| '''Column
 +
| '''Referenced Column
 +
 +
|- valign="top"
 +
| [[Production.Product_(table)|Production.Product]]
 +
| ComponentID
 +
| ProductID
 +
|- valign="top"
 +
| [[Production.Product_(table)|Production.Product]]
 +
| ProductAssemblyID
 +
| ProductID
 +
|- valign="top"
 +
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 +
| UnitMeasureCode
 +
| UnitMeasureCode
 +
|}
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}
 +
 +
=== Dependencies ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
|- valign="top"
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
 +
 +
|- valign="top"
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetWhereUsedProductID_(procedure)|dbo.uspGetWhereUsedProductID]]
 +
 +
|}
 +
 +
 +
== automatically generated ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|-
 +
| '''Table
 +
| Production.BillOfMaterials
 +
|- valign="top"
 +
| '''Description
 +
| Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
 +
|-
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Column
 +
| '''Data Type
 +
| '''Nullable
 +
| '''Default
 +
| '''Description / PK / Index
 +
 +
|- valign="top"
 +
| BillOfMaterialsID
 +
| int
 +
| not null
 
|  
 
|  
 +
| Primary key for BillOfMaterials records.<br />PK_BillOfMaterials_BillOfMaterialsID
 +
|- valign="top"
 +
| ProductAssemblyID
 +
| int
 +
| null
 
|  
 
|  
−
|-
+
| Parent product identification number. Foreign key to Product.ProductID.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
−
| ModifiedDate
+
|- valign="top"
 +
| ComponentID
 +
| int
 +
| not null
 +
|
 +
| Component identification number. Foreign key to Product.ProductID.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
 +
|- valign="top"
 +
| StartDate
 
| datetime
 
| datetime
 
| not null
 
| not null
−
| (getdate())
+
| (GETDATE())
 +
| Date the component started being used in the assembly item.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
 +
|- valign="top"
 +
| EndDate
 +
| datetime
 +
| null
 
|  
 
|  
 +
| Date the component stopped being used in the assembly item.<br />
 +
|- valign="top"
 +
| UnitMeasureCode
 +
| nchar(3)
 +
| not null
 
|  
 
|  
 +
| Standard code identifying the unit of measure for the quantity.<br />IX_BillOfMaterials_UnitMeasureCode
 +
|- valign="top"
 +
| BOMLevel
 +
| smallint
 +
| not null
 +
|
 +
| Indicates the depth the component is from its parent (AssemblyID).<br />
 +
|- valign="top"
 +
| PerAssemblyQty
 +
| decimal(8, 2)
 +
| not null
 +
| (1.00)
 +
| Quantity of the component needed to create the assembly.<br />
 +
|- valign="top"
 +
| ModifiedDate
 +
| datetime
 +
| not null
 +
| (GETDATE())
 +
| Date and time the record was last updated.<br />
 
|}
 
|}
  
Line 103: Line 279:
 
| UnitMeasureCode
 
| UnitMeasureCode
 
| UnitMeasureCode
 
| UnitMeasureCode
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
|-
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
 +
 +
|-
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetWhereUsedProductID_(procedure)|dbo.uspGetWhereUsedProductID]]
 +
 
|}
 
|}

Latest revision as of 23:16, 23 June 2010

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.

Columns[edit]

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[edit]

Primary Key Columns
PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID

Indexes[edit]

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

Check Constraints[edit]

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]

Foreign Keys[edit]

Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode

References[edit]

Dependency Type Object Type Referenced Object
Schema Schema Production

Dependencies[edit]

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


automatically generated[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 / 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.


Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode


Dependency Type Object Type Referenced Object
Schema Schema Production


Reference Type Object Type Referencing Object
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID
Retrieved from "http://dbshelp.devio.at/index.php?title=Production.BillOfMaterials_(table)&oldid=2621"