Purchasing.PurchaseOrderHeader Table

Description

General purchase order information. See PurchaseOrderDetail.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	4012
Data Space Used	336.00 KB
Index Space Used	144.00 KB

Columns

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

Indexes

Index	Description	Primary	Unique
IX_PurchaseOrderHeader_EmployeeID	Nonclustered index.
IX_PurchaseOrderHeader_VendorID	Nonclustered index.
PK_PurchaseOrderHeader_PurchaseOrderID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_PurchaseOrderHeader_Freight	Check constraint [Freight] >= (0.00)	([Freight]>=(0.00))
CK_PurchaseOrderHeader_ShipDate	Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_PurchaseOrderHeader_Status	Check constraint [Status] BETWEEN (1) AND (4)	([Status]>=(1) AND [Status]<=(4))
CK_PurchaseOrderHeader_SubTotal	Check constraint [SubTotal] >= (0.00)	([SubTotal]>=(0.00))
CK_PurchaseOrderHeader_TaxAmt	Check constraint [TaxAmt] >= (0.00)	([TaxAmt]>=(0.00))

Triggers

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

Relationships

Relationship	Description
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
FK_PurchaseOrderHeader_Employee_EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	Foreign key constraint referencing ShipMethod.ShipMethodID.
FK_PurchaseOrderHeader_Vendor_VendorID	Foreign key constraint referencing Vendor.VendorID.

Objects that depend on Purchasing.PurchaseOrderHeader

Database Object	Object Type	Description	Dep Level
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	2
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	1
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	2
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	1

Objects that Purchasing.PurchaseOrderHeader depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	2
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		3
dbo.Phone	User Defined Data Type		3
Purchasing.ShipMethod	Table	Shipping company lookup table.	1
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	1
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	1
Purchasing.Vendor	Table	Companies from whom Adventure Works Cycles purchases parts or other goods.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[PurchaseOrderHeader](
	[PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
	[RevisionNumber] [tinyint] NOT NULL,
	[Status] [tinyint] NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[VendorID] [int] NOT NULL,
	[ShipMethodID] [int] NOT NULL,
	[OrderDate] [datetime] NOT NULL,
	[ShipDate] [datetime] NULL,
	[SubTotal] [money] NOT NULL,
	[TaxAmt] [money] NOT NULL,
	[Freight] [money] NOT NULL,
	[TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PurchaseOrderHeader_PurchaseOrderID] PRIMARY KEY CLUSTERED 
(
	[PurchaseOrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [Employee] ([EmployeeID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY([ShipMethodID])
REFERENCES [ShipMethod] ([ShipMethodID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_ShipMethod_ShipMethodID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_Vendor_VendorID] FOREIGN KEY([VendorID])
REFERENCES [Vendor] ([VendorID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_Vendor_VendorID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_Freight] CHECK  (([Freight]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_Freight]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_ShipDate] CHECK  (([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_ShipDate]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_Status] CHECK  (([Status]>=(1) AND [Status]<=(4)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_Status]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_SubTotal] CHECK  (([SubTotal]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_SubTotal]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt] CHECK  (([TaxAmt]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_RevisionNumber]  DEFAULT ((0)) FOR [RevisionNumber]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_Status]  DEFAULT ((1)) FOR [Status]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_SubTotal]  DEFAULT ((0.00)) FOR [SubTotal]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_TaxAmt]  DEFAULT ((0.00)) FOR [TaxAmt]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_Freight]  DEFAULT ((0.00)) FOR [Freight]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects