Purchasing.PurchaseOrderDetail Table

Description

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

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	8845
Data Space Used	512.00 KB
Index Space Used	192.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
PurchaseOrderID	Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.	Integer	4
PurchaseOrderDetailID	Primary key. One line number per purchased product.	Integer	4
DueDate	Date the product is expected to be received.	DBTimeStamp	4
OrderQty	Quantity ordered.	SmallInt	2
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
UnitPrice	Vendor's selling price of a single product.	Currency	8
LineTotal	Per product subtotal. Computed as OrderQty * UnitPrice.	Currency	8		(isnull([OrderQty]*[UnitPrice],(0.00)))
ReceivedQty	Quantity actually received from the vendor.	Numeric	9 (8,2)
RejectedQty	Quantity rejected during inspection.	Numeric	9 (8,2)
StockedQty	Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.	Numeric	9 (9,2)		(isnull([ReceivedQty]-[RejectedQty],(0.00)))
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

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

Check Constraints

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

Triggers

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

Relationships

Relationship	Description
FK_PurchaseOrderDetail_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.

Objects that depend on Purchasing.PurchaseOrderDetail

	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.	1
	uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	1

Objects that Purchasing.PurchaseOrderDetail depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		3
HumanResources.Employee	Table	Employee information such as salary, department, and title.	2
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.Name	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		4
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	3
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
Purchasing.ShipMethod	Table	Shipping company lookup table.	2
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	1
Production.UnitMeasure	Table	Unit of measure lookup table.	2
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.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[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 [PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID] PRIMARY KEY CLUSTERED 
(
    [PurchaseOrderID] ASC,
    [PurchaseOrderDetailID] 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].[PurchaseOrderDetail] ADD  CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderDetail_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [FK_PurchaseOrderDetail_Product_ProductID]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID] FOREIGN KEY([PurchaseOrderID])
REFERENCES [Purchasing].[PurchaseOrderHeader] ([PurchaseOrderID])
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderDetail_OrderQty] CHECK  (([OrderQty]>(0)))
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [CK_PurchaseOrderDetail_OrderQty]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderDetail_ReceivedQty] CHECK  (([ReceivedQty]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [CK_PurchaseOrderDetail_ReceivedQty]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderDetail_RejectedQty] CHECK  (([RejectedQty]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [CK_PurchaseOrderDetail_RejectedQty]
ALTER TABLE [Purchasing].[PurchaseOrderDetail]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderDetail_UnitPrice] CHECK  (([UnitPrice]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderDetail] CHECK CONSTRAINT [CK_PurchaseOrderDetail_UnitPrice]

Related Objects