Properties

Owner:	Purchasing
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	336
Index Size KB:	144
Rows:	4012
Description:	General purchase order information. See PurchaseOrderDetail.

Columns

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

Total: 13 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	PurchaseOrderID	1	1

Indexes

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

Total: 3 index(es)

Triggers

	Name	Owner	Instead Of	Disabled	Table/View	Description
	uPurchaseOrderHeader	Purchasing			Purchasing.PurchaseOrderHeader	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Total: 1 trigger(s)

Check Constraints

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

Total: 5 constraint(s)

Referencing Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Purchasing.PurchaseOrderDetail	FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	PK_PurchaseOrderHeader_PurchaseOrderID

Total: 1 table(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	HumanResources.Employee	FK_PurchaseOrderHeader_Employee_EmployeeID	PK_Employee_EmployeeID
	Purchasing.ShipMethod	FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	PK_ShipMethod_ShipMethodID
	Purchasing.Vendor	FK_PurchaseOrderHeader_Vendor_VendorID	PK_Vendor_VendorID

Total: 3 table(s)

Objects that [Purchasing].[PurchaseOrderHeader] depends on

	Object Name	Owner	Object Type	Dep Level
	AccountNumber	dbo	User Defined type	1
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	NameStyle	dbo	User Defined type	1
	Phone	dbo	User Defined type	1
	Contact	Person	Table	2
	ShipMethod	Purchasing	Table	2
	Vendor	Purchasing	Table	2
	Employee	HumanResources	Table	3

Total: 9 object(s)

Objects that depend on [Purchasing].[PurchaseOrderHeader]

	Object Name	Owner	Object Type	Dep Level
	PurchaseOrderDetail	Purchasing	Table	1
	uPurchaseOrderHeader	Purchasing	Trigger	1
	iPurchaseOrderDetail	Purchasing	Trigger	2
	uPurchaseOrderDetail	Purchasing	Trigger	2

Total: 4 object(s)

SQL

CREATE TABLE [PurchaseOrderHeader] (
    [PurchaseOrderID] [int] IDENTITY (1, 1) NOT NULL ,
    [RevisionNumber] [tinyint] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_RevisionNumber] DEFAULT ((0)),
    [Status] [tinyint] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_Status] DEFAULT ((1)),
    [EmployeeID] [int] NOT NULL ,
    [VendorID] [int] NOT NULL ,
    [ShipMethodID] [int] NOT NULL ,
    [OrderDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_OrderDate] DEFAULT (getdate()),
    [ShipDate] [datetime] NULL ,
    [SubTotal] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_SubTotal] DEFAULT ((0.00)),
    [TaxAmt] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_TaxAmt] DEFAULT ((0.00)),
    [Freight] [money] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_Freight] DEFAULT ((0.00)),
    [TotalDue] AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) ,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseOrderHeader_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_PurchaseOrderHeader_PurchaseOrderID] PRIMARY KEY  CLUSTERED
    (
        [PurchaseOrderID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID] FOREIGN KEY
    (
        [EmployeeID]
    ) REFERENCES [Employee] (
        [EmployeeID]
    ),
    CONSTRAINT [FK_PurchaseOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY
    (
        [ShipMethodID]
    ) REFERENCES [ShipMethod] (
        [ShipMethodID]
    ),
    CONSTRAINT [FK_PurchaseOrderHeader_Vendor_VendorID] FOREIGN KEY
    (
        [VendorID]
    ) REFERENCES [Vendor] (
        [VendorID]
    ),
    CONSTRAINT [CK_PurchaseOrderHeader_Freight] CHECK ([Freight]>=(0.00)),
    CONSTRAINT [CK_PurchaseOrderHeader_ShipDate] CHECK ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL),
    CONSTRAINT [CK_PurchaseOrderHeader_Status] CHECK ([Status]>=(1) AND [Status]<=(4)),
    CONSTRAINT [CK_PurchaseOrderHeader_SubTotal] CHECK ([SubTotal]>=(0.00)),
    CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt] CHECK ([TaxAmt]>=(0.00))
) ON [PRIMARY]
GO

See Also

List of tables