Manufacturing Scenario
This topic provides details about the Adventure Works Cycles manufacturing information that is represented in the AdventureWorks sample database, a list of manufacturing-related tables, and sample queries that demonstrate common table relationships.
Manufacturing Overview
In the AdventureWorks sample database, tables are provided that support the following typical manufacturing areas:
- Manufacturing processes:
- Bill of materials: Lists the products that are used or contained in another product.
- Work orders: Manufacturing orders by work center.
- Locations: Defines the major manufacturing and inventory areas, such as frame forming, paint, subassembly, and so on.
- Manufacturing and product assembly instructions by work center.
- Product inventory: The physical location of a product in the warehouse or manufacturing area, and the quantity available in that area.
- Engineering documentation: Technical specifications and maintenance documentation for bicycles or bicycle components.
Manufacturing Tables
The following table contains a brief description of the data that is stored in the manufacturing tables.
| Schema.Table | Contains this kind of content | Comment |
|---|---|---|
A list of all the components used to manufacture bicycles and bicycle subassemblies. |
There is an intrinsic recursive relationship in the bill of material structure that indicates the relationship between a parent product and the components that make up that product. For example, if the parent product is a bicycle, the first-level component might be a wheel assembly. The wheel assembly has its own components, such as reflectors, rims, spokes, tires, and tire tubes. The ProductAssemblyID column represents the parent, or primary, product and ComponentID represents the child, or individual, parts used to build the parent assembly. The BOM_Level column indicates the level of the ComponentID relative to the ProductAssemblyID. In the previous example, the wheel assembly would have a BOM_Level of 1, the components of the wheel assembly would have a BOM_Level of 2, and so on. |
|
Engineering specifications and other technical documentation. |
The DocumentSummary column uses the varchar(max) data type. The Document column uses the varbinary(max) data type. |
|
Bicycle manufacturing illustrations. |
The illustrations are rendered in the manufacturing instructions that are contained in the ProductModel table. This column uses the xml data type. |
|
A list of inventory and manufacturing areas within Adventure Works Cycles in which the products and parts are stored as inventory or built. For example, paint is stored in both the Paint Storage location in the warehouse and in the manufacturing work center, Paint Shop, where the bicycle frames are painted. |
|
|
Information about each product sold by Adventure Works Cycles or used to manufacture Adventure Works Cycles bicycles and bicycle components. |
The FinishedGoodsFlag column indicates whether a product is sold. Products that are not sold are components of a product that is sold. For example, a bicycle would be sold, but the sheet of metal used to create the bicycle frame would not. |
|
The inventory level of products by their location. See Production.Location previously mentioned. |
|
|
The product models associated with products. For example, Mountain-100 or LL Touring Frame. |
The CatalogDescription column contains additional product information by using the xml data type. The Instructions column contains product manufacturing instructions by using the xml data type |
|
A list of common reasons why bicycles or bicycles parts are rejected during the manufacturing process. For example, the scrap reason 'Paint failed' is used in the Paint work center to reject a bicycle frame for which the paint did not cure correctly. |
The WorkOrderRouting table tracks the quantity scrapped and the reason for scrapping by product. Depending on the severity of the problem, the product must be fixed or replaced before the product can move to the next work center. |
|
Defines the products and quantity that must be manufactured to meet current and forecasted sales. |
|
|
The details for each work order. This includes the sequence of work centers the product travels through in the manufacturing or assembly process. For example, bicycle handlebars are manufactured in the Frame Forming work center. They are moved to the Frame Welding work center for additional work, and then moved to the Subassembly work center, where they are added to the bicycle frame. |
|
Examples
You can use the following queries to view manufacturing and product data and to become familiar with the manufacturing table relationships.
A. Viewing a multilevel bill-of-materials list for a parent product
The following example displays all the components that are used to create a specific parent product: ProductAssemblyID.
USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
b.EndDate, 0 AS ComponentLevel
FROM Production.BillOfMaterials AS b
WHERE b.ProductAssemblyID = 800
AND b.EndDate IS NULL
UNION ALL
SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
bom.EndDate, ComponentLevel + 1
FROM Production.BillOfMaterials AS bom
INNER JOIN Parts AS p
ON bom.ProductAssemblyID = p.ComponentID
AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
ComponentLevel
FROM Parts AS p
INNER JOIN Production.Product AS pr
ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO
B. Viewing Product Inventory
In the following example, the quantity that is available for each product is listed by its location in inventory. Products can be located in multiple locations.
USE AdventureWorks;
GO
SELECT P.Name AS Product, L.Name AS [Inventory Location],
SUM(PI.Quantity)AS [Qty Available]
FROM Production.Product AS P
JOIN Production.ProductInventory AS PI ON P.ProductID = PI.ProductID
JOIN Production.Location AS L ON PI.LocationID = L.LocationID
GROUP BY P.Name, L.Name
ORDER BY P.Name ;
GO
C. Viewing work orders by product
In the following example, all work orders are listed for products in the subcategories Mountain Bike (1), Road Bike (2), and Touring Bike (3).
USE AdventureWorks;
GO
SELECT WorkOrderID, P.Name AS Product, OrderQty, DueDate
FROM Production.WorkOrder W
JOIN Production.Product P ON W.ProductID = P.ProductID
WHERE P.ProductSubcategoryID IN (1, 2, 3)
ORDER BY P.Name, DueDate ;
GO
See Also
Concepts
Product Scenario
Purchasing and Vendor Scenario
Sales and Marketing Scenario
Other Resources
Adventure Works Cycles Business Scenarios