• Article

Purchasing and Vendor Scenario

At Adventure Works Cycles, the purchasing department buys raw materials and parts used in the manufacture of Adventure Works Cycles bicycles. Adventure Works Cycles also purchases products for resale, such as bicycle apparel and bicycle add-ons like water bottles and pumps. The information about these products and the vendors from whom they are obtained is stored in the AdventureWorks sample database.

This topic provides details about the vendors represented in the sample database, a schema diagram of the major vendor-related tables and sample queries that demonstrate common table relationships.

Vendor and Purchasing Tables

The following table contains a brief description of the data stored in these tables.

Schema.Table

Contains this kind of content

Comments

Person.Address

Street address information for all customers.

Customers may have more than one address. For example, a customer may have a billing address and a different address for shipping.

The associative table VendorAddress maps vendors to their addresses.

The Address table also contains address information for Adventure Works Cycles employees and customers.

Person.Contact

Names of vendor employees with whom Adventure Works Cycles purchasing agents order products.

A vendor may have more than one contact. For example, a sales agent and a sales manager. The Adventure Works Cycles purchasing agent may have the sales agent as a primary customer contact and the sales manager as a secondary contact.

The associative table VendorContact maps contacts to vendors.

The column AdditionalContactInfo contains data such as additional telephone numbers (cell telephone, fax, and so on) specific to the contact. The column is an xml data type. For more information, see About the Contact.AdditionalContactInfo xml Column.

Production.ProductVendor

Maps vendors to the products they supply.

A product may be supplied by more than one vendor, and a vendor may supply more than one product.

 

Purchasing.PurchaseOrderDetail

Details of the purchase order, such as products ordered, quantity, and unit price.

 

Purchasing.PurchaseOrderHeader

Purchase order summary information, such as total due, order date, and order status.

The PurchaseOrderHeader and PurchaseOrderDetail tables together create a master-detail relationship.

Purchasing.ShipMethod

A lookup table that is used to maintain standard ways of shipping products.

The ShipMethodID column is included in the PurchaseOrderHeader table.

Purchasing.Vendor

Details about vendors, such as the vendor name and account number.

 

Purchasing.VendorAddress

Links customers to address information in the Address table.

Addresses are categorized by type, such as billing, home, shipping, and so on). The AddressTypeID column maps to the AddressType table.

Purchasing.VendorContact

Street address information for all customers.

Customers may have more than one address. For example, a customer may have a billing address and a different address for shipping.

This is an associative table. See the Contact and Vendor tables.

Examples

You can use the following queries to view purchasing and vendor data and to become familiar with the purchasing and vendor table relationships.

A. Viewing vendors by location

The following example lists the vendors and their address.

USE AdventureWorks;
GO
SELECT V.VendorID, V.Name AS Vendor, A.AddressLine1, A.AddressLine2, A.City, SP.Name AS State, CR.Name AS Country
FROM Purchasing.Vendor AS V 
    JOIN Purchasing.VendorAddress AS VA ON VA.VendorID = V.VendorID
    JOIN Person.Address AS A on A.AddressID = VA.AddressID
    JOIN Person.StateProvince AS SP on SP.StateProvinceID =         A.StateProvinceID
    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
GROUP BY V.VendorID, V.Name, A.AddressLine1, A.AddressLine2, A.City, SP.Name, CR.Name
ORDER BY V.VendorID;
GO

B. Viewing products supplied by vendors

The following example lists the products that the vendors supply to Adventure Works Cycles.

USE AdventureWorks;
GO
SELECT P.ProductNumber, P.Name AS Product, V.Name AS Vendor, PV.LastReceiptCost
FROM Production.Product AS P
    JOIN Purchasing.ProductVendor AS PV ON P.ProductID = PV.ProductID
    JOIN Purchasing.Vendor AS V ON V.VendorID = PV.VendorID
ORDER BY P.Name ;
GO

C. Viewing vendor contacts by vendor

The following example lists vendor contacts. Vendor contacts are employees of the vendor with whom employees of the Adventure Works Cycles purchasing department interact to order parts and products.

GO
SELECT V.Name as Vendor, C.FirstName, C.LastName, CT.Name AS Title 
FROM Person.Contact AS C 
    JOIN Purchasing.VendorContact VC ON C.ContactID = VC.ContactID
    JOIN Person.ContactType CT ON CT.ContactTypeID = VC.ContactTypeID
    JOIN Purchasing.Vendor V ON V.VendorID = VC.VendorID
ORDER BY V.Name;
GO

D. Viewing purchases by vendor

The following example displays the vendors and their associated purchase orders.

USE AdventureWorks;
GO
SELECT V.Name AS Vendor, SUM(PH.TotalDue)AS [Total Purchase],
    AVG(PH.TotalDue)AS [Average Purchase], MIN(PH.TotalDue) 
    AS [Minimum Purchase], MAX(PH.TotalDue)AS [Maximum Purchase] 
FROM Purchasing.Vendor AS V
    JOIN Purchasing.PurchaseOrderHeader AS PH ON V.VendorID = PH.VendorID
GROUP BY V.Name
ORDER BY V.Name;
GO