wikibot[edit]
| Table
|
Purchasing.PurchaseOrderDetail
|
| Description
|
Individual products associated with a specific purchase order. See PurchaseOrderHeader.
|
Columns[edit]
| Column
|
Data Type
|
Nullable
|
Default
|
Description
|
| PurchaseOrderID
|
int
|
not null
|
|
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
|
| PurchaseOrderDetailID
|
int
|
not null
|
|
Primary key. One line number per purchased product.
|
| DueDate
|
datetime
|
not null
|
|
Date the product is expected to be received.
|
| OrderQty
|
smallint
|
not null
|
|
Quantity ordered.
|
| ProductID
|
int
|
not null
|
|
Product identification number. Foreign key to Product.ProductID.
|
| UnitPrice
|
money
|
not null
|
|
Vendor's selling price of a single product.
|
| LineTotal
|
|
|
|
Per product subtotal. Computed as OrderQty * UnitPrice.
|
| ReceivedQty
|
decimal(8, 2)
|
not null
|
|
Quantity actually received from the vendor.
|
| RejectedQty
|
decimal(8, 2)
|
not null
|
|
Quantity rejected during inspection.
|
| StockedQty
|
|
|
|
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
|
| ModifiedDate
|
datetime
|
not null
|
(getdate())
|
Date and time the record was last updated.
|
Primary Key[edit]
| Primary Key
|
Columns
|
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
|
PurchaseOrderID, PurchaseOrderDetailID
|
Indexes[edit]
| Index
|
Type
|
Columns
|
| IX_PurchaseOrderDetail_ProductID
|
|
ProductID
|
Check Constraints[edit]
| Check Constraint
|
Expression
|
Description
|
| CK_PurchaseOrderDetail_OrderQty
|
([OrderQty]>(0))
|
Check constraint [OrderQty] > (0)
|
| CK_PurchaseOrderDetail_ReceivedQty
|
([ReceivedQty]>=(0.00))
|
Check constraint [ReceivedQty] >= (0.00)
|
| CK_PurchaseOrderDetail_RejectedQty
|
([RejectedQty]>=(0.00))
|
Check constraint [RejectedQty] >= (0.00)
|
| CK_PurchaseOrderDetail_UnitPrice
|
([UnitPrice]>=(0.00))
|
Check constraint [UnitPrice] >= (0.00)
|
Foreign Keys[edit]
Triggers[edit]
| Trigger
|
Type
|
| iPurchaseOrderDetail
|
ON INSERT
|
| uPurchaseOrderDetail
|
ON UPDATE
|
Trigger iPurchaseOrderDetail[edit]
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 [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
Trigger uPurchaseOrderDetail[edit]
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 [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
References[edit]
Dependencies[edit]
automatically generated[edit]
| Table
|
Purchasing.PurchaseOrderDetail
|
| Description
|
Individual products associated with a specific purchase order. See PurchaseOrderHeader.
|
| Column
|
Data Type
|
Nullable
|
Default
|
Description / PK / Index
|
| PurchaseOrderID
|
int
|
not null
|
|
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
|
| PurchaseOrderDetailID
|
int
|
not null
|
|
Primary key. One line number per purchased product.
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
|
| DueDate
|
datetime
|
not null
|
|
Date the product is expected to be received.
|
| OrderQty
|
smallint
|
not null
|
|
Quantity ordered.
|
| ProductID
|
int
|
not null
|
|
Product identification number. Foreign key to Product.ProductID.
IX_PurchaseOrderDetail_ProductID
|
| UnitPrice
|
money
|
not null
|
|
Vendor's selling price of a single product.
|
| LineTotal
|
|
|
|
Per product subtotal. Computed as OrderQty * UnitPrice.
|
| ReceivedQty
|
decimal(8, 2)
|
not null
|
|
Quantity actually received from the vendor.
|
| RejectedQty
|
decimal(8, 2)
|
not null
|
|
Quantity rejected during inspection.
|
| StockedQty
|
|
|
|
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
|
| ModifiedDate
|
datetime
|
not null
|
(GETDATE())
|
Date and time the record was last updated.
|
| Triggers
|
Type
|
| iPurchaseOrderDetail
|
ON INSERT
|
| uPurchaseOrderDetail
|
ON UPDATE
|
