Purchasing.PurchaseOrderHeader

Object type: Table

General purchase order information. See PurchaseOrderDetail.

Columns

Name Data type Nullable Description

PurchaseOrderID int No Column has identity seed value of 1 with a step value of 1.
Primary key.

RevisionNumber tinyint No Default value: 0
Incremental number to track changes to the purchase order over time.

Status tinyint No Default value: 1
Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete

EmployeeID int No Employee who created the purchase order. Foreign key to Employee.EmployeeID.

VendorID int No Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.

ShipMethodID int No Shipping method. Foreign key to ShipMethod.ShipMethodID.

OrderDate datetime No Default value: GETDATE()
Purchase order creation date.

ShipDate datetime Yes Estimated shipment date from the vendor.

SubTotal money No Default value: 0.00
Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.

TaxAmt money No Default value: 0.00
Tax amount.

Freight money No Default value: 0.00
Shipping cost.

TotalDue ISNULL([SubTotal] + [TaxAmt] + [Freight], 0) PERSISTED No Total due to vendor. Computed as Subtotal + TaxAmt + Freight.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_PurchaseOrderHeader_RevisionNumber DEFAULT RevisionNumber Value: 0
Default constraint value of 0

DF_PurchaseOrderHeader_Status DEFAULT Status Value: 1
Default constraint value of 1

DF_PurchaseOrderHeader_OrderDate DEFAULT OrderDate Value: GETDATE()
Default constraint value of GETDATE()

DF_PurchaseOrderHeader_SubTotal DEFAULT SubTotal Value: 0.00
Default constraint value of 0.0

DF_PurchaseOrderHeader_TaxAmt DEFAULT TaxAmt Value: 0.00
Default constraint value of 0.0

DF_PurchaseOrderHeader_Freight DEFAULT Freight Value: 0.00
Default constraint value of 0.0

DF_PurchaseOrderHeader_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_PurchaseOrderHeader_Status CHECK N/A Value: ([Status] BETWEEN 1 AND 4)
Check constraint [Status] BETWEEN (1) AND (4)

CK_PurchaseOrderHeader_ShipDate CHECK N/A Value: (([ShipDate] >= [OrderDate]) OR ([ShipDate] IS NULL) )
Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL

CK_PurchaseOrderHeader_SubTotal CHECK N/A Value: ([SubTotal] >= 0.00)
Check constraint [SubTotal]>=(0.00)

CK_PurchaseOrderHeader_TaxAmt CHECK N/A Value: ([TaxAmt] >= 0.00)
Check constraint [TaxAmt]>=(0.00)

CK_PurchaseOrderHeader_Freight CHECK N/A Value: ([Freight] >= 0.00)
Check constraint [Freight]>=(0.00)

PK_PurchaseOrderHeader_PurchaseOrderID PRIMARY KEY PurchaseOrderID Clustered index created by a primary key constraint.

FK_PurchaseOrderHeader_Employee_EmployeeID FOREIGN KEY EmployeeID Reference table: HumanResources.Employee
Foreign key constraint referencing Employee.EmployeeID.

FK_PurchaseOrderHeader_Vendor_VendorID FOREIGN KEY VendorID Reference table: Purchasing.Vendor
Foreign key constraint referencing Vendor.VendorID.

FK_PurchaseOrderHeader_ShipMethod_ShipMethodID FOREIGN KEY ShipMethodID Reference table: Purchasing.ShipMethod
Foreign key constraint referencing ShipMethod.ShipMethodID.

Indices

Name Description

IX_PurchaseOrderHeader_EmployeeID Nonclustered index.

IX_PurchaseOrderHeader_VendorID Nonclustered index.

Triggers

Name Description

Purchasing.uPurchaseOrderHeader AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Linked tables

Name Description

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

Accessed by

Name Description

Purchasing.iPurchaseOrderDetail AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.

Modified by

Name Description

Purchasing.iPurchaseOrderDetail AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.

Purchasing.uPurchaseOrderDetail AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.

Purchasing.uPurchaseOrderHeader AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Applies to

Tables | User

Name	Data type	Nullable	Description
PurchaseOrderID	int	No	Column has identity seed value of 1 with a step value of 1. Primary key.
RevisionNumber	tinyint	No	Default value: 0 Incremental number to track changes to the purchase order over time.
Status	tinyint	No	Default value: 1 Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID	int	No	Employee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorID	int	No	Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodID	int	No	Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate	datetime	No	Default value: GETDATE() Purchase order creation date.
ShipDate	datetime	Yes	Estimated shipment date from the vendor.
SubTotal	money	No	Default value: 0.00 Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt	money	No	Default value: 0.00 Tax amount.
Freight	money	No	Default value: 0.00 Shipping cost.
TotalDue	ISNULL([SubTotal] + [TaxAmt] + [Freight], 0) PERSISTED	No	Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_PurchaseOrderHeader_RevisionNumber	DEFAULT	RevisionNumber	Value: 0 Default constraint value of 0
DF_PurchaseOrderHeader_Status	DEFAULT	Status	Value: 1 Default constraint value of 1
DF_PurchaseOrderHeader_OrderDate	DEFAULT	OrderDate	Value: GETDATE() Default constraint value of GETDATE()
DF_PurchaseOrderHeader_SubTotal	DEFAULT	SubTotal	Value: 0.00 Default constraint value of 0.0
DF_PurchaseOrderHeader_TaxAmt	DEFAULT	TaxAmt	Value: 0.00 Default constraint value of 0.0
DF_PurchaseOrderHeader_Freight	DEFAULT	Freight	Value: 0.00 Default constraint value of 0.0
DF_PurchaseOrderHeader_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_PurchaseOrderHeader_Status	CHECK	N/A	Value: ([Status] BETWEEN 1 AND 4) Check constraint [Status] BETWEEN (1) AND (4)
CK_PurchaseOrderHeader_ShipDate	CHECK	N/A	Value: (([ShipDate] >= [OrderDate]) OR ([ShipDate] IS NULL) ) Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_PurchaseOrderHeader_SubTotal	CHECK	N/A	Value: ([SubTotal] >= 0.00) Check constraint [SubTotal]>=(0.00)
CK_PurchaseOrderHeader_TaxAmt	CHECK	N/A	Value: ([TaxAmt] >= 0.00) Check constraint [TaxAmt]>=(0.00)
CK_PurchaseOrderHeader_Freight	CHECK	N/A	Value: ([Freight] >= 0.00) Check constraint [Freight]>=(0.00)
PK_PurchaseOrderHeader_PurchaseOrderID	PRIMARY KEY	PurchaseOrderID	Clustered index created by a primary key constraint.
FK_PurchaseOrderHeader_Employee_EmployeeID	FOREIGN KEY	EmployeeID	Reference table: HumanResources.Employee Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_Vendor_VendorID	FOREIGN KEY	VendorID	Reference table: Purchasing.Vendor Foreign key constraint referencing Vendor.VendorID.
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	FOREIGN KEY	ShipMethodID	Reference table: Purchasing.ShipMethod Foreign key constraint referencing ShipMethod.ShipMethodID.

Name	Description
IX_PurchaseOrderHeader_EmployeeID	Nonclustered index.
IX_PurchaseOrderHeader_VendorID	Nonclustered index.

Name	Description
Purchasing.uPurchaseOrderHeader	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Name	Description
Purchasing.PurchaseOrderDetail	Individual products associated with a specific purchase order. See PurchaseOrderHeader.

Name	Description
Purchasing.iPurchaseOrderDetail	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.