Page History: Purchasing PurchaseOrderDetail (table)
Compare Page Revisions
Page Revision: 2010/02/11 09:06
wikibot
| Table | Purchasing.PurchaseOrderDetail |
| Description | Individual products associated with a specific purchase order. See PurchaseOrderHeader. |
Columns ¶
| 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
| Primary Key | Columns |
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID | PurchaseOrderID, PurchaseOrderDetailID |
Indexes
| Index | Type | Columns |
| IX_PurchaseOrderDetail_ProductID | | ProductID |
Check Constraints
Foreign Keys
Triggers
| Triggers | Type |
| iPurchaseOrderDetail | ON INSERT |
| uPurchaseOrderDetail | ON UPDATE |
Trigger iPurchaseOrderDetail
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
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
Dependencies