Properties

Owner:	Purchasing
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	512
Index Size KB:	168
Rows:	8845
Description:	Individual products associated with a specific purchase order. See PurchaseOrderHeader.

Columns

		Name	Data Type	Length	NULL	Default	IsIdentity	IsGUID	Description
		PurchaseOrderID	int	4					Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
		PurchaseOrderDetailID	int	4					Primary key. One line number per purchased product.
		DueDate	datetime	8					Date the product is expected to be received.
		OrderQty	smallint	2					Quantity ordered.
		ProductID	int	4					Product identification number. Foreign key to Product.ProductID.
		UnitPrice	money	8					Vendor's selling price of a single product.
		LineTotal	money	8					Per product subtotal. Computed as OrderQty * UnitPrice.
		ReceivedQty	decimal	5					Quantity actually received from the vendor.
		RejectedQty	decimal	5					Quantity rejected during inspection.
		StockedQty	decimal	5					Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
		ModifiedDate	datetime	8		(getdate())			Date and time the record was last updated.

Total: 11 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	PurchaseOrderDetailID	1	1

Indexes

	Index	Primary	Unique	Description
	PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID			Primary key (clustered) constraint
	IX_PurchaseOrderDetail_ProductID			Nonclustered index.

Total: 2 index(es)

Triggers

	Name	Owner	Instead Of	Disabled	Table/View	Description
	iPurchaseOrderDetail	Purchasing			Purchasing.PurchaseOrderDetail	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
	uPurchaseOrderDetail	Purchasing			Purchasing.PurchaseOrderDetail	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.

Total: 2 trigger(s)

Check Constraints

	Name	Expression
	CK_PurchaseOrderDetail_OrderQty	([OrderQty]>(0))
	CK_PurchaseOrderDetail_ReceivedQty	([ReceivedQty]>=(0.00))
	CK_PurchaseOrderDetail_RejectedQty	([RejectedQty]>=(0.00))
	CK_PurchaseOrderDetail_UnitPrice	([UnitPrice]>=(0.00))

Total: 4 constraint(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.Product	FK_PurchaseOrderDetail_Product_ProductID	PK_Product_ProductID
	Purchasing.PurchaseOrderHeader	FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	PK_PurchaseOrderHeader_PurchaseOrderID

Total: 2 table(s)

Objects that [Purchasing].[PurchaseOrderDetail] 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
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	ShipMethod	Purchasing	Table	2
	UnitMeasure	Production	Table	2
	Vendor	Purchasing	Table	2
	Employee	HumanResources	Table	3
	ProductSubcategory	Production	Table	3
	Product	Production	Table	4
	PurchaseOrderHeader	Purchasing	Table	4

Total: 15 object(s)

Objects that depend on [Purchasing].[PurchaseOrderDetail]

	Object Name	Owner	Object Type	Dep Level
	iPurchaseOrderDetail	Purchasing	Trigger	1
	uPurchaseOrderDetail	Purchasing	Trigger	1

Total: 2 object(s)

SQL

CREATE TABLE [PurchaseOrderDetail] (
    [PurchaseOrderID] [int] NOT NULL ,
    [PurchaseOrderDetailID] [int] IDENTITY (1, 1) NOT NULL ,
    [DueDate] [datetime] NOT NULL ,
    [OrderQty] [smallint] NOT NULL ,
    [ProductID] [int] NOT NULL ,
    [UnitPrice] [money] NOT NULL ,
    [LineTotal] AS (isnull([OrderQty]*[UnitPrice],(0.00))) ,
    [ReceivedQty] [decimal](8, 2) NOT NULL ,
    [RejectedQty] [decimal](8, 2) NOT NULL ,
    [StockedQty] AS (isnull([ReceivedQty]-[RejectedQty],(0.00))) ,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID] PRIMARY KEY  CLUSTERED
    (
        [PurchaseOrderID],
        [PurchaseOrderDetailID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_PurchaseOrderDetail_Product_ProductID] FOREIGN KEY
    (
        [ProductID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID] FOREIGN KEY
    (
        [PurchaseOrderID]
    ) REFERENCES [PurchaseOrderHeader] (
        [PurchaseOrderID]
    ),
    CONSTRAINT [CK_PurchaseOrderDetail_OrderQty] CHECK ([OrderQty]>(0)),
    CONSTRAINT [CK_PurchaseOrderDetail_ReceivedQty] CHECK ([ReceivedQty]>=(0.00)),
    CONSTRAINT [CK_PurchaseOrderDetail_RejectedQty] CHECK ([RejectedQty]>=(0.00)),
    CONSTRAINT [CK_PurchaseOrderDetail_UnitPrice] CHECK ([UnitPrice]>=(0.00))
) ON [PRIMARY]
GO

See Also

List of tables