Purchasing.PurchaseOrderDetail

AdventureWorks

- AdventureWorks

Documentation	AdventureWorks
Schema	Purchasing
Name	PurchaseOrderDetail
Module	Purchasing

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

Columns

		Name	Data type	Attributes	References	Description
	1	PurchaseOrderID	int		Purchasing.PurchaseOrderHeader	Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. References: Purchasing.PurchaseOrderHeader
	2	PurchaseOrderDetailID	int	Identity		Primary key. One line number per purchased product.
Primary key. One line number per purchased product. Identity
	3	DueDate	datetime			Date the product is expected to be received.
Date the product is expected to be received.
	4	OrderQty	smallint			Quantity ordered.
Quantity ordered.
	5	ProductID	int		Production.Product	Product identification number. Foreign key to Product.ProductID.
Product identification number. Foreign key to Product.ProductID. References: Production.Product
	6	UnitPrice	money			Vendor's selling price of a single product.
Vendor's selling price of a single product.
	7	LineTotal	money	Computed: isnull([OrderQty]*[UnitPrice],(0.00))		Per product subtotal. Computed as OrderQty * UnitPrice.
Per product subtotal. Computed as OrderQty * UnitPrice. Computed: isnull([OrderQty]*[UnitPrice],(0.00))
	8	ReceivedQty	decimal(8, 2)			Quantity actually received from the vendor.
Quantity actually received from the vendor.
	9	RejectedQty	decimal(8, 2)			Quantity rejected during inspection.
Quantity rejected during inspection.
	10	StockedQty	decimal(9, 2)	Computed: isnull([ReceivedQty]-[RejectedQty],(0.00))		Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty. Computed: isnull([ReceivedQty]-[RejectedQty],(0.00))
	11	ModifiedDate	datetime	Default: getdate()		Date and time the record was last updated.
Date and time the record was last updated. Default: getdate()

Relations

Foreign table	Primary table	Join	Title / Name / Description
Purchasing.PurchaseOrderDetail	Production.Product	Purchasing.PurchaseOrderDetail.ProductID = Production.Product.ProductID	FK_PurchaseOrderDetail_Product_ProductID Foreign key constraint referencing Product.ProductID.
Purchasing.PurchaseOrderDetail.ProductID = Production.Product.ProductID Name: FK_PurchaseOrderDetail_Product_ProductID Foreign key constraint referencing Product.ProductID.
Purchasing.PurchaseOrderDetail	Purchasing.PurchaseOrderHeader	Purchasing.PurchaseOrderDetail.PurchaseOrderID = Purchasing.PurchaseOrderHeader.PurchaseOrderID	FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
Purchasing.PurchaseOrderDetail.PurchaseOrderID = Purchasing.PurchaseOrderHeader.PurchaseOrderID Name: FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.

Unique keys

	Key name	Columns	Description
	PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderID, PurchaseOrderDetailID	Primary key (clustered) constraint
Primary key (clustered) constraint

Triggers

	Key name	When	Description
	iPurchaseOrderDetail	After Insert	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderHeader] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderHeader] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
	uPurchaseOrderDetail	After Update	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) -- Insert record into TransactionHistory BEGIN INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderDetail] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); UPDATE [Purchasing].[PurchaseOrderDetail] SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID] AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID]; END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;
AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.
CREATE TRIGGER [Purchasing].[uPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] AFTER UPDATE AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY IF UPDATE([ProductID]) OR UPDATE([OrderQty]) OR UPDATE([UnitPrice]) -- Insert record into TransactionHistory BEGIN INSERT INTO [Production].[TransactionHistory] ([ProductID] ,[ReferenceOrderID] ,[ReferenceOrderLineID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,[ActualCost]) SELECT inserted.[ProductID] ,inserted.[PurchaseOrderID] ,inserted.[PurchaseOrderDetailID] ,'P' ,GETDATE() ,inserted.[OrderQty] ,inserted.[UnitPrice] FROM inserted INNER JOIN [Purchasing].[PurchaseOrderDetail] ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]; -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber. UPDATE [Purchasing].[PurchaseOrderHeader] SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal]) FROM [Purchasing].[PurchaseOrderDetail] WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID]) WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted); UPDATE [Purchasing].[PurchaseOrderDetail] SET [Purchasing].[PurchaseOrderDetail].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID] AND inserted.[PurchaseOrderDetailID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderDetailID]; END; END TRY BEGIN CATCH EXECUTE [uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [uspLogError]; END CATCH; END;

Uses

Name
Purchasing.PurchaseOrderDetail
Purchasing.PurchaseOrderDetail
Production.Product
Purchasing.PurchaseOrderHeader
Purchasing.iPurchaseOrderDetail
Production.TransactionHistory
Purchasing.PurchaseOrderDetail
Purchasing.PurchaseOrderHeader
Purchasing.PurchaseOrderHeader
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError
dbo.uspPrintError
Purchasing.uPurchaseOrderDetail
Production.TransactionHistory
Purchasing.PurchaseOrderDetail
Purchasing.PurchaseOrderHeader
Purchasing.PurchaseOrderHeader
dbo.uspLogError
dbo.ErrorLog
dbo.uspPrintError
dbo.uspPrintError

Used by

Name
Purchasing.PurchaseOrderDetail
Purchasing.PurchaseOrderDetail
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail

Exported: 2019-02-04 23:13, Last imported: 2018-03-07 11:56