Purchasing

1 AdventureWorks.Purchasing © Matthias Wolf; wolf online training Hover columns to read comments. Purchasing Fk FK_ProductVendor_Vendor_BusinessEntityID ProductVendor ref Vendor ( BusinessEntityID ) Fk FK_ProductVendor_Vendor_BusinessEntityID ProductVendor ref Vendor ( BusinessEntityID ) Fk FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID PurchaseOrderDetail ref PurchaseOrderHeader ( PurchaseOrderID ) Fk FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID PurchaseOrderDetail ref PurchaseOrderHeader ( PurchaseOrderID ) Fk FK_PurchaseOrderHeader_ShipMethod_ShipMethodID PurchaseOrderHeader ref ShipMethod ( ShipMethodID ) Fk FK_PurchaseOrderHeader_ShipMethod_ShipMethodID PurchaseOrderHeader ref ShipMethod ( ShipMethodID ) Fk FK_PurchaseOrderHeader_Vendor_VendorID PurchaseOrderHeader ref Vendor ( VendorID -> BusinessEntityID ) Fk FK_PurchaseOrderHeader_Vendor_VendorID PurchaseOrderHeader ref Vendor ( VendorID -> BusinessEntityID ) ProductVendorTable Purchasing.ProductVendor Cross-reference table mapping vendors with the products they supply. Pk PK_ProductVendor_ProductID_BusinessEntityID ( ProductID, BusinessEntityID ) Clustered index created by a primary key constraint.ProductIDProductID * int Primary key. Foreign key to Product.ProductID. intReferences Product ( ProductID ) Pk PK_ProductVendor_ProductID_BusinessEntityID ( ProductID, BusinessEntityID ) Clustered index created by a primary key constraint.IX_ProductVendor_BusinessEntityID ( BusinessEntityID ) Nonclustered index.BusinessEntityIDBusinessEntityID * int Primary key. Foreign key to Vendor.BusinessEntityID. intReferences Vendor ( BusinessEntityID ) AverageLeadTimeAverageLeadTime * int The average span of time (in days) between placing an order with the vendor and receiving the purchased product. int StandardPriceStandardPrice * money The vendor's usual selling price. money LastReceiptCostLastReceiptCost money The selling price when last purchased. money LastReceiptDateLastReceiptDate datetime Date the product was last received by the vendor. datetime MinOrderQtyMinOrderQty * int The maximum quantity that should be ordered. int MaxOrderQtyMaxOrderQty * int The minimum quantity that should be ordered. int OnOrderQtyOnOrderQty int The quantity currently on order. int IX_ProductVendor_UnitMeasureCode ( UnitMeasureCode ) Nonclustered index.UnitMeasureCodeUnitMeasureCode * nchar(3) The product's unit of measure. nchar(3)References UnitMeasure ( UnitMeasureCode ) ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime PurchaseOrderDetailTable Purchasing.PurchaseOrderDetail Individual products associated with a specific purchase order. See PurchaseOrderHeader. Pk PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID ( PurchaseOrderID, PurchaseOrderDetailID ) Clustered index created by a primary key constraint.PurchaseOrderIDPurchaseOrderID * int Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. intReferences PurchaseOrderHeader ( PurchaseOrderID ) Pk PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID ( PurchaseOrderID, PurchaseOrderDetailID ) Clustered index created by a primary key constraint.PurchaseOrderDetailIDPurchaseOrderDetailID * int Primary key. One line number per purchased product. int DueDateDueDate * datetime Date the product is expected to be received. datetime OrderQtyOrderQty * smallint Quantity ordered. smallint IX_PurchaseOrderDetail_ProductID ( ProductID ) Nonclustered index.ProductIDProductID * int Product identification number. Foreign key to Product.ProductID. intReferences Product ( ProductID ) UnitPriceUnitPrice * money Vendor's selling price of a single product. money LineTotalLineTotal * money Per product subtotal. Computed as OrderQty * UnitPrice. money ReceivedQtyReceivedQty * decimal(8,2) Quantity actually received from the vendor. decimal(8,2) RejectedQtyRejectedQty * decimal(8,2) Quantity rejected during inspection. decimal(8,2) StockedQtyStockedQty * decimal(9,2) Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty. decimal(9,2) ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime PurchaseOrderHeaderTable Purchasing.PurchaseOrderHeader General purchase order information. See PurchaseOrderDetail. Pk PK_PurchaseOrderHeader_PurchaseOrderID ( PurchaseOrderID ) Clustered index created by a primary key constraint.PurchaseOrderIDPurchaseOrderID * int Primary key. intReferred by PurchaseOrderDetail ( PurchaseOrderID ) RevisionNumberRevisionNumber * tinyint default 0 Incremental number to track changes to the purchase order over time. tinyint StatusStatus * tinyint default 1 Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete tinyint IX_PurchaseOrderHeader_EmployeeID ( EmployeeID ) Nonclustered index.EmployeeIDEmployeeID * int Employee who created the purchase order. Foreign key to Employee.BusinessEntityID. intReferences Employee ( EmployeeID -> BusinessEntityID ) IX_PurchaseOrderHeader_VendorID ( VendorID ) Nonclustered index.VendorIDVendorID * int Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID. intReferences Vendor ( VendorID -> BusinessEntityID ) ShipMethodIDShipMethodID * int Shipping method. Foreign key to ShipMethod.ShipMethodID. intReferences ShipMethod ( ShipMethodID ) OrderDateOrderDate * datetime default getdate() Purchase order creation date. datetime ShipDateShipDate datetime Estimated shipment date from the vendor. datetime SubTotalSubTotal * money default 0.00 Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID. money TaxAmtTaxAmt * money default 0.00 Tax amount. money FreightFreight * money default 0.00 Shipping cost. money TotalDueTotalDue * money Total due to vendor. Computed as Subtotal + TaxAmt + Freight. money ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime ShipMethodTable Purchasing.ShipMethod Shipping company lookup table. Pk PK_ShipMethod_ShipMethodID ( ShipMethodID ) Clustered index created by a primary key constraint.ShipMethodIDShipMethodID * int Primary key for ShipMethod records. intReferred by PurchaseOrderHeader ( ShipMethodID ) Referred by SalesOrderHeader ( ShipMethodID ) Unq AK_ShipMethod_Name ( Name ) Unique nonclustered index.NameName * name Shipping company name. name ShipBaseShipBase * money default 0.00 Minimum shipping charge. money ShipRateShipRate * money default 0.00 Shipping charge per pound. money Unq AK_ShipMethod_rowguid ( rowguid ) Unique nonclustered index. Used to support replication samples.rowguidrowguid * uniqueidentifier default newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. uniqueidentifier ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime VendorTable Purchasing.Vendor Companies from whom Adventure Works Cycles purchases parts or other goods. Pk PK_Vendor_BusinessEntityID ( BusinessEntityID ) Clustered index created by a primary key constraint.BusinessEntityIDBusinessEntityID * int Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID intReferences BusinessEntity ( BusinessEntityID ) Referred by ProductVendor ( BusinessEntityID ) Referred by PurchaseOrderHeader ( VendorID -> BusinessEntityID ) Unq AK_Vendor_AccountNumber ( AccountNumber ) Unique nonclustered index.AccountNumberAccountNumber * accountnumber Vendor account (identification) number. accountnumber NameName * name Company name. name CreditRatingCreditRating * tinyint 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average tinyint PreferredVendorStatusPreferredVendorStatus * flag default 1 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product. flag ActiveFlagActiveFlag * flag default 1 0 = Vendor no longer used. 1 = Vendor is actively used. flag PurchasingWebServiceURLPurchasingWebServiceURL nvarchar(1024) Vendor URL. nvarchar(1024) ModifiedDateModifiedDate * datetime default getdate() Date and time the record was last updated. datetime
Tables



Table ProductVendor

Cross-reference table mapping vendors with the products they supply.

IdxField NameData TypeDescription
* ProductID int Primary key. Foreign key to Product.ProductID.
* BusinessEntityID int Primary key. Foreign key to Vendor.BusinessEntityID.
* AverageLeadTime int The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
* StandardPrice money The vendor's usual selling price.
  LastReceiptCost money The selling price when last purchased.
  LastReceiptDate datetime Date the product was last received by the vendor.
* MinOrderQty int The maximum quantity that should be ordered.
* MaxOrderQty int The minimum quantity that should be ordered.
  OnOrderQty int The quantity currently on order.
* UnitMeasureCode nchar(3) The product's unit of measure.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_ProductVendor_ProductID_BusinessEntityID ON ProductID, BusinessEntityID Clustered index created by a primary key constraint.
IX_ProductVendor_BusinessEntityID ON BusinessEntityID Nonclustered index.
IX_ProductVendor_UnitMeasureCode ON UnitMeasureCode Nonclustered index.
Foreign Keys
FK_ProductVendor_Product_ProductID ( ProductID ) ref Product (ProductID) Foreign key constraint referencing Product.ProductID.
FK_ProductVendor_UnitMeasure_UnitMeasureCode ( UnitMeasureCode ) ref UnitMeasure (UnitMeasureCode) Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_ProductVendor_Vendor_BusinessEntityID ( BusinessEntityID ) ref Vendor (BusinessEntityID) Foreign key constraint referencing Vendor.BusinessEntityID.
Constraints
  CK_ProductVendor_AverageLeadTime [AverageLeadTime]>=(1)
  CK_ProductVendor_StandardPrice [StandardPrice]>(0.00)
  CK_ProductVendor_LastReceiptCost [LastReceiptCost]>(0.00)
  CK_ProductVendor_MinOrderQty [MinOrderQty]>=(1)
  CK_ProductVendor_MaxOrderQty [MaxOrderQty]>=(1)
  CK_ProductVendor_OnOrderQty [OnOrderQty]>=(0)


Table PurchaseOrderDetail

Individual products associated with a specific purchase order. See PurchaseOrderHeader.

IdxField NameData TypeDescription
* PurchaseOrderID int Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
* PurchaseOrderDetailID int AUTOINCREMENT Primary key. One line number per purchased product.
* DueDate datetime Date the product is expected to be received.
* OrderQty smallint Quantity ordered.
* ProductID int Product identification number. Foreign key to Product.ProductID.
* UnitPrice money Vendor's selling price of a single product.
* LineTotal money Per product subtotal. Computed as OrderQty * UnitPrice.
* ReceivedQty decimal(8,2) Quantity actually received from the vendor.
* RejectedQty decimal(8,2) Quantity rejected during inspection.
* StockedQty decimal(9,2) Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID ON PurchaseOrderID, PurchaseOrderDetailID Clustered index created by a primary key constraint.
IX_PurchaseOrderDetail_ProductID ON ProductID Nonclustered index.
Foreign Keys
FK_PurchaseOrderDetail_Product_ProductID ( ProductID ) ref Product (ProductID) Foreign key constraint referencing Product.ProductID.
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID ( PurchaseOrderID ) ref PurchaseOrderHeader (PurchaseOrderID) Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
Constraints
  CK_PurchaseOrderDetail_OrderQty [OrderQty]>(0)
  CK_PurchaseOrderDetail_UnitPrice [UnitPrice]>=(0.00)
  CK_PurchaseOrderDetail_ReceivedQty [ReceivedQty]>=(0.00)
  CK_PurchaseOrderDetail_RejectedQty [RejectedQty]>=(0.00)
Triggers
  iPurchaseOrderDetail
  uPurchaseOrderDetail


Table PurchaseOrderHeader

General purchase order information. See PurchaseOrderDetail.

IdxField NameData TypeDescription
* PurchaseOrderID int AUTOINCREMENT Primary key.
* RevisionNumber tinyint DEFAULT 0 Incremental number to track changes to the purchase order over time.
* Status tinyint DEFAULT 1 Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
* EmployeeID int Employee who created the purchase order. Foreign key to Employee.BusinessEntityID.
* VendorID int Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID.
* ShipMethodID int Shipping method. Foreign key to ShipMethod.ShipMethodID.
* OrderDate datetime DEFAULT getdate() Purchase order creation date.
  ShipDate datetime Estimated shipment date from the vendor.
* SubTotal money DEFAULT 0.00 Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
* TaxAmt money DEFAULT 0.00 Tax amount.
* Freight money DEFAULT 0.00 Shipping cost.
* TotalDue money Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_PurchaseOrderHeader_PurchaseOrderID ON PurchaseOrderID Clustered index created by a primary key constraint.
IX_PurchaseOrderHeader_EmployeeID ON EmployeeID Nonclustered index.
IX_PurchaseOrderHeader_VendorID ON VendorID Nonclustered index.
Foreign Keys
FK_PurchaseOrderHeader_Employee_EmployeeID ( EmployeeID ) ref Employee (BusinessEntityID) Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID ( ShipMethodID ) ref ShipMethod (ShipMethodID) Foreign key constraint referencing ShipMethod.ShipMethodID.
FK_PurchaseOrderHeader_Vendor_VendorID ( VendorID ) ref Vendor (BusinessEntityID) Foreign key constraint referencing Vendor.VendorID.
Constraints
  CK_PurchaseOrderHeader_Status [Status]>=(1) AND [Status]<=(4)
  CK_PurchaseOrderHeader_ShipDate [ShipDate]>=[OrderDate] OR [ShipDate] IS NULL
  CK_PurchaseOrderHeader_ShipDate [ShipDate]>=[OrderDate] OR [ShipDate] IS NULL
  CK_PurchaseOrderHeader_SubTotal [SubTotal]>=(0.00)
  CK_PurchaseOrderHeader_TaxAmt [TaxAmt]>=(0.00)
  CK_PurchaseOrderHeader_Freight [Freight]>=(0.00)
Triggers
  uPurchaseOrderHeader


Table ShipMethod

Shipping company lookup table.

IdxField NameData TypeDescription
* ShipMethodID int AUTOINCREMENT Primary key for ShipMethod records.
* Name name Shipping company name.
* ShipBase money DEFAULT 0.00 Minimum shipping charge.
* ShipRate money DEFAULT 0.00 Shipping charge per pound.
* rowguid uniqueidentifier DEFAULT newid() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_ShipMethod_ShipMethodID ON ShipMethodID Clustered index created by a primary key constraint.
AK_ShipMethod_Name ON Name Unique nonclustered index.
AK_ShipMethod_rowguid ON rowguid Unique nonclustered index. Used to support replication samples.
Constraints
  CK_ShipMethod_ShipBase [ShipBase]>(0.00)
  CK_ShipMethod_ShipRate [ShipRate]>(0.00)


Table Vendor

Companies from whom Adventure Works Cycles purchases parts or other goods.

IdxField NameData TypeDescription
* BusinessEntityID int Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID
* AccountNumber accountnumber Vendor account (identification) number.
* Name name Company name.
* CreditRating tinyint 1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
* PreferredVendorStatus flag DEFAULT 1 0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
* ActiveFlag flag DEFAULT 1 0 = Vendor no longer used. 1 = Vendor is actively used.
  PurchasingWebServiceURL nvarchar(1024) Vendor URL.
* ModifiedDate datetime DEFAULT getdate() Date and time the record was last updated.
Indexes
PK_Vendor_BusinessEntityID ON BusinessEntityID Clustered index created by a primary key constraint.
AK_Vendor_AccountNumber ON AccountNumber Unique nonclustered index.
Foreign Keys
FK_Vendor_BusinessEntity_BusinessEntityID ( BusinessEntityID ) ref BusinessEntity (BusinessEntityID) Foreign key constraint referencing BusinessEntity.BusinessEntityID
Constraints
  CK_Vendor_CreditRating [CreditRating]>=(1) AND [CreditRating]<=(5)
Triggers
  dVendor