Manufacturing Scenario

Manufacturing Scenario

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (June 2009)

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
Production.BillOfMaterials	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.
Production.Document	Engineering specifications and other technical documentation.	The DocumentSummary column uses the varchar(max) data type. The Document column uses the varbinary(max) data type.
Production.Illustration	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.
Production.Location	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.
Production.Product	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.
Production.ProductInventory	The inventory level of products by their location. See Production.Location previously mentioned.
Production.ProductModel	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
Production.ScrapReason	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.
Production.WorkOrder	Defines the products and quantity that must be manufactured to meet current and forecasted sales.
Production.WorkOrderRouting	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

A. Viewing a multilevel bill-of-materials list for a parent product

B. Viewing Product Inventory

C. Viewing work orders by product

Concepts

Other Resources

Help and Information