Purchasing.PurchaseOrderHeader

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Purchasing
Name	PurchaseOrderHeader
Module	Purchasing

General purchase order information. See PurchaseOrderDetail.

Columns

		Name	Data type	Attributes	References	Description
	1	PurchaseOrderID	int	Identity		Primary key.
Primary key. Identity
	2	RevisionNumber	tinyint	Default: 0		Incremental number to track changes to the purchase order over time.
Incremental number to track changes to the purchase order over time. Default: 0
	3	Status	tinyint	Default: 1		Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete Default: 1
	4	EmployeeID	int		HumanResources.Employee	Employee who created the purchase order. Foreign key to Employee.BusinessEntityID.
Employee who created the purchase order. Foreign key to Employee.BusinessEntityID. References: HumanResources.Employee
	5	VendorID	int		Purchasing.Vendor	Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID.
Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID. References: Purchasing.Vendor
	6	ShipMethodID	int		Purchasing.ShipMethod	Shipping method. Foreign key to ShipMethod.ShipMethodID.
Shipping method. Foreign key to ShipMethod.ShipMethodID. References: Purchasing.ShipMethod
	7	OrderDate	datetime	Default: getdate()		Purchase order creation date.
Purchase order creation date. Default: getdate()
	8	ShipDate	datetime			Estimated shipment date from the vendor.
Estimated shipment date from the vendor.
	9	SubTotal	money	Default: 0.00		Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID. Default: 0.00
	10	TaxAmt	money	Default: 0.00		Tax amount.
Tax amount. Default: 0.00
	11	Freight	money	Default: 0.00		Shipping cost.
Shipping cost. Default: 0.00
	12	TotalDue	money	Computed: isnull(([SubTotal]+[TaxAmt])+[Freight],(0))		Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
Total due to vendor. Computed as Subtotal + TaxAmt + Freight. Computed: isnull(([SubTotal]+[TaxAmt])+[Freight],(0))
	13	ModifiedDate	datetime	Default: getdate()		Date and time the record was last updated.
Date and time the record was last updated. Default: getdate()

Relations

Foreign table	Primary table	Join	Title / Name / Description
Purchasing.PurchaseOrderHeader	HumanResources.Employee	Purchasing.PurchaseOrderHeader.EmployeeID = HumanResources.Employee.BusinessEntityID	FK_PurchaseOrderHeader_Employee_EmployeeID Foreign key constraint referencing Employee.EmployeeID.
Purchasing.PurchaseOrderHeader.EmployeeID = HumanResources.Employee.BusinessEntityID Name: FK_PurchaseOrderHeader_Employee_EmployeeID Foreign key constraint referencing Employee.EmployeeID.
Purchasing.PurchaseOrderHeader	Purchasing.ShipMethod	Purchasing.PurchaseOrderHeader.ShipMethodID = Purchasing.ShipMethod.ShipMethodID	FK_PurchaseOrderHeader_ShipMethod_ShipMethodID Foreign key constraint referencing ShipMethod.ShipMethodID.
Purchasing.PurchaseOrderHeader.ShipMethodID = Purchasing.ShipMethod.ShipMethodID Name: FK_PurchaseOrderHeader_ShipMethod_ShipMethodID Foreign key constraint referencing ShipMethod.ShipMethodID.
Purchasing.PurchaseOrderHeader	Purchasing.Vendor	Purchasing.PurchaseOrderHeader.VendorID = Purchasing.Vendor.BusinessEntityID	FK_PurchaseOrderHeader_Vendor_VendorID Foreign key constraint referencing Vendor.VendorID.
Purchasing.PurchaseOrderHeader.VendorID = Purchasing.Vendor.BusinessEntityID Name: FK_PurchaseOrderHeader_Vendor_VendorID Foreign key constraint referencing Vendor.VendorID.
Purchasing.PurchaseOrderDetail	Purchasing.PurchaseOrderHeader	Purchasing.PurchaseOrderDetail.PurchaseOrderID = Purchasing.PurchaseOrderHeader.PurchaseOrderID	FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
Purchasing.PurchaseOrderDetail.PurchaseOrderID = Purchasing.PurchaseOrderHeader.PurchaseOrderID Name: FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.

Unique keys

	Key name	Columns	Description
	PK_PurchaseOrderHeader_PurchaseOrderID	PurchaseOrderID	Primary key (clustered) constraint
Primary key (clustered) constraint

Triggers

	Key name	When	Description
	uPurchaseOrderHeader	After Update	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Update RevisionNumber for modification of any field EXCEPT the Status. IF NOT UPDATE([Status]) BEGIN UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1 WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Update RevisionNumber for modification of any field EXCEPT the Status. IF NOT UPDATE([Status]) BEGIN UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1 WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;

Uses

Name
Purchasing.PurchaseOrderHeader
Purchasing.PurchaseOrderHeader
HumanResources.Employee
Purchasing.ShipMethod
Purchasing.Vendor
Purchasing.uPurchaseOrderHeader
Purchasing.PurchaseOrderHeader
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError
dbo.uspPrintError

Used by

Name
Purchasing.PurchaseOrderHeader
Purchasing.PurchaseOrderHeader
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Purchasing.uPurchaseOrderHeader
Purchasing.PurchaseOrderDetail

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56