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