Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

Page History: Purchasing PurchaseOrderDetail (table)

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: 2010/02/11 09:06


wikibot

TablePurchasing.PurchaseOrderDetail
DescriptionIndividual products associated with a specific purchase order. See PurchaseOrderHeader.

Columns

ColumnData TypeNullableDefaultDescription
PurchaseOrderIDintnot null Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
PurchaseOrderDetailIDintnot null Primary key. One line number per purchased product.
DueDatedatetimenot null Date the product is expected to be received.
OrderQtysmallintnot null Quantity ordered.
ProductIDintnot null Product identification number. Foreign key to Product.ProductID.
UnitPricemoneynot null Vendor's selling price of a single product.
LineTotal   Per product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQtydecimal(8, 2)not null Quantity actually received from the vendor.
RejectedQtydecimal(8, 2)not null Quantity rejected during inspection.
StockedQty   Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailIDPurchaseOrderID, PurchaseOrderDetailID

Indexes

IndexTypeColumns
IX_PurchaseOrderDetail_ProductID ProductID

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
Production.ProductProductIDProductID
Purchasing.PurchaseOrderHeaderPurchaseOrderIDPurchaseOrderID

Triggers

TriggersType
iPurchaseOrderDetailON INSERT
uPurchaseOrderDetailON 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 TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaPurchasing
InsertTableProduction.TransactionHistoryTriggeriPurchaseOrderDetail
InsertTableProduction.TransactionHistoryTriggeruPurchaseOrderDetail
SelectTablePurchasing.PurchaseOrderDetailTriggeruPurchaseOrderDetail
SelectTablePurchasing.PurchaseOrderDetailTriggeriPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderDetailTriggeruPurchaseOrderDetail
SelectTablePurchasing.PurchaseOrderHeaderTriggeriPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderHeaderTriggeriPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderHeaderTriggeruPurchaseOrderDetail
ExecuteProceduredbo.uspLogErrorTriggeriPurchaseOrderDetail
ExecuteProceduredbo.uspLogErrorTriggeruPurchaseOrderDetail
ExecuteProceduredbo.uspPrintErrorTriggeruPurchaseOrderDetail
ExecuteProceduredbo.uspPrintErrorTriggeriPurchaseOrderDetail

Dependencies

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectTablePurchasing.PurchaseOrderDetailTriggeriPurchaseOrderDetail
SelectTablePurchasing.PurchaseOrderDetailTriggeruPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderDetailTriggeruPurchaseOrderDetail

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.