Purchasing PurchaseOrderDetail (table)

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 ¶

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 ¶

Relation	Column	Referenced Column
Production.Product	ProductID	ProductID
Purchasing.PurchaseOrderHeader	PurchaseOrderID	PurchaseOrderID

Triggers ¶

Trigger	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 ¶

Dependency Type	Object Type	Referenced Object	Child Type	Child Object
Schema	Schema	Purchasing
Insert	Table	Production.TransactionHistory	Trigger	iPurchaseOrderDetail
Insert	Table	Production.TransactionHistory	Trigger	uPurchaseOrderDetail
Select	Table	Purchasing.PurchaseOrderDetail	Trigger	iPurchaseOrderDetail
Select	Table	Purchasing.PurchaseOrderDetail	Trigger	uPurchaseOrderDetail
Update	Table	Purchasing.PurchaseOrderDetail	Trigger	uPurchaseOrderDetail
Select	Table	Purchasing.PurchaseOrderHeader	Trigger	iPurchaseOrderDetail
Update	Table	Purchasing.PurchaseOrderHeader	Trigger	iPurchaseOrderDetail
Update	Table	Purchasing.PurchaseOrderHeader	Trigger	uPurchaseOrderDetail
Execute	Procedure	dbo.uspLogError	Trigger	iPurchaseOrderDetail
Execute	Procedure	dbo.uspLogError	Trigger	uPurchaseOrderDetail
Execute	Procedure	dbo.uspPrintError	Trigger	iPurchaseOrderDetail
Execute	Procedure	dbo.uspPrintError	Trigger	uPurchaseOrderDetail

Dependencies ¶

Reference Type	Object Type	Referencing Object	Child Type	Child Object
Select	Table	Purchasing.PurchaseOrderDetail	Trigger	iPurchaseOrderDetail
Select	Table	Purchasing.PurchaseOrderDetail	Trigger	uPurchaseOrderDetail
Update	Table	Purchasing.PurchaseOrderDetail	Trigger	uPurchaseOrderDetail