Purchasing.PurchaseOrderDetail

Object type: Table

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

Columns

Name Data type Nullable Description

PurchaseOrderID int No Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.

PurchaseOrderDetailID int No Column has identity seed value of 1 with a step value of 1.
Primary key. One line number per purchased product.

DueDate datetime No Date the product is expected to be received.

OrderQty smallint No Quantity ordered.

ProductID int No Product identification number. Foreign key to Product.ProductID.

UnitPrice money No Vendor's selling price of a single product.

LineTotal ISNULL([OrderQty] * [UnitPrice], 0.00) Yes Per product subtotal. Computed as OrderQty * UnitPrice.

ReceivedQty decimal(8, 2) No Quantity actually received from the vendor.

RejectedQty decimal(8, 2) No Quantity rejected during inspection.

StockedQty ISNULL([ReceivedQty] - [RejectedQty], 0.00) Yes Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.

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

Constraints

Name Type Columns Description

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

CK_PurchaseOrderDetail_OrderQty CHECK N/A Value: ([OrderQty] > 0)
Check constraint [OrderQty]>(0)

CK_PurchaseOrderDetail_UnitPrice CHECK N/A Value: ([UnitPrice] >= 0.00)
Check constraint [UnitPrice]>=(0.00)

CK_PurchaseOrderDetail_ReceivedQty CHECK N/A Value: ([ReceivedQty] >= 0.00)
Check constraint [ReceivedQty]>=(0.00)

CK_PurchaseOrderDetail_RejectedQty CHECK N/A Value: ([RejectedQty] >= 0.00)
Check constraint [RejectedQty]>=(0.00)

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

FK_PurchaseOrderDetail_Product_ProductID FOREIGN KEY ProductID Reference table: Production.Product
Foreign key constraint referencing Product.ProductID.

FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID FOREIGN KEY PurchaseOrderID Reference table: Purchasing.PurchaseOrderHeader
Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.

Indices

Name Description

IX_PurchaseOrderDetail_ProductID Nonclustered index.

Triggers

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.

Accessed 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.

Modified by

Name Description

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

Applies to

Tables | User

Name	Data type	Nullable	Description
PurchaseOrderID	int	No	Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailID	int	No	Column has identity seed value of 1 with a step value of 1. Primary key. One line number per purchased product.
DueDate	datetime	No	Date the product is expected to be received.
OrderQty	smallint	No	Quantity ordered.
ProductID	int	No	Product identification number. Foreign key to Product.ProductID.
UnitPrice	money	No	Vendor's selling price of a single product.
LineTotal	ISNULL([OrderQty] * [UnitPrice], 0.00)	Yes	Per product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQty	decimal(8, 2)	No	Quantity actually received from the vendor.
RejectedQty	decimal(8, 2)	No	Quantity rejected during inspection.
StockedQty	ISNULL([ReceivedQty] - [RejectedQty], 0.00)	Yes	Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_PurchaseOrderDetail_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_PurchaseOrderDetail_OrderQty	CHECK	N/A	Value: ([OrderQty] > 0) Check constraint [OrderQty]>(0)
CK_PurchaseOrderDetail_UnitPrice	CHECK	N/A	Value: ([UnitPrice] >= 0.00) Check constraint [UnitPrice]>=(0.00)
CK_PurchaseOrderDetail_ReceivedQty	CHECK	N/A	Value: ([ReceivedQty] >= 0.00) Check constraint [ReceivedQty]>=(0.00)
CK_PurchaseOrderDetail_RejectedQty	CHECK	N/A	Value: ([RejectedQty] >= 0.00) Check constraint [RejectedQty]>=(0.00)
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PRIMARY KEY	PurchaseOrderID PurchaseOrderDetailID	Clustered index created by a primary key constraint.
FK_PurchaseOrderDetail_Product_ProductID	FOREIGN KEY	ProductID	Reference table: Production.Product Foreign key constraint referencing Product.ProductID.
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	FOREIGN KEY	PurchaseOrderID	Reference table: Purchasing.PurchaseOrderHeader Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.

Name	Description
IX_PurchaseOrderDetail_ProductID	Nonclustered index.

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.