Purchasing PurchaseOrderDetail (table)

Modified on 2011/08/27 11:18 by wikibot Categorized as Uncategorized
== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- valign="top"
| '''Table'''
| Purchasing.PurchaseOrderDetail
|- valign="top"
| '''Description'''
| Individual products associated with a specific purchase order. See PurchaseOrderHeader.
|}

=== Columns ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column'''
| '''Data Type'''
| '''Nullable'''
| '''Default'''
| '''Description'''

|- valign="top"
| PurchaseOrderID
| int
| not null
| &nbsp;
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
|- valign="top"
| PurchaseOrderDetailID
| int
| not null
| &nbsp;
| Primary key. One line number per purchased product.
|- valign="top"
| DueDate
| datetime
| not null
| &nbsp;
| Date the product is expected to be received.
|- valign="top"
| OrderQty
| smallint
| not null
| &nbsp;
| Quantity ordered.
|- valign="top"
| ProductID
| int
| not null
| &nbsp;
| Product identification number. Foreign key to Product.ProductID.
|- valign="top"
| UnitPrice
| money
| not null
| &nbsp;
| Vendor's selling price of a single product.
|- valign="top"
| LineTotal
| &nbsp;
| &nbsp;
| &nbsp;
| Per product subtotal. Computed as OrderQty * UnitPrice.
|- valign="top"
| ReceivedQty
| decimal(8, 2)
| not null
| &nbsp;
| Quantity actually received from the vendor.
|- valign="top"
| RejectedQty
| decimal(8, 2)
| not null
| &nbsp;
| Quantity rejected during inspection.
|- valign="top"
| StockedQty
| &nbsp;
| &nbsp;
| &nbsp;
| Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
|- valign="top"
| ModifiedDate
| datetime
| not null
| (getdate())
| Date and time the record was last updated.
|}

=== Primary Key ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Primary Key'''
| '''Columns'''
|- valign="top"
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
| PurchaseOrderID, PurchaseOrderDetailID
|}

=== Indexes ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Index'''
| '''Type'''
| '''Columns'''

|- valign="top"
| IX_PurchaseOrderDetail_ProductID
| &nbsp;
| ProductID
|}

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Check Constraint'''
| '''Expression'''
| '''Description'''

|- valign="top"
| CK_PurchaseOrderDetail_OrderQty
| ([OrderQty]>(0))
| Check constraint [OrderQty] > (0)
|- valign="top"
| CK_PurchaseOrderDetail_ReceivedQty
| ([ReceivedQty]>=(0.00))
| Check constraint [ReceivedQty] >= (0.00)
|- valign="top"
| CK_PurchaseOrderDetail_RejectedQty
| ([RejectedQty]>=(0.00))
| Check constraint [RejectedQty] >= (0.00)
|- valign="top"
| CK_PurchaseOrderDetail_UnitPrice
| ([UnitPrice]>=(0.00))
| Check constraint [UnitPrice] >= (0.00)
|}

=== Foreign Keys ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation'''
| '''Column'''
| '''Referenced Column'''

|- valign="top"
| [[Production Product (table)|Production.Product]]
| ProductID
| ProductID
|- valign="top"
| [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]]
| PurchaseOrderID
| PurchaseOrderID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Trigger'''
| '''Type'''

|- valign="top"
| iPurchaseOrderDetail
| ON INSERT
|- valign="top"
| uPurchaseOrderDetail
| ON UPDATE
|}

==== Trigger iPurchaseOrderDetail ====
{{{{<nowiki>
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;
</nowiki>}}}}

==== Trigger uPurchaseOrderDetail ====
{{{{<nowiki>
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;
</nowiki>}}}}

=== References ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Dependency Type'''
| '''Object Type'''
| '''Referenced Object'''

| '''Child Type'''
| '''Child Object'''

|- valign="top"
| Schema
| Schema
| [[Purchasing (schema)|Purchasing]]

| &nbsp;
| &nbsp;
|- valign="top"
| Insert
| Table
| [[Production TransactionHistory (table)|Production.TransactionHistory]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Insert
| Table
| [[Production TransactionHistory (table)|Production.TransactionHistory]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Select
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Select
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Update
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Select
| Table
| [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Update
| Table
| [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Update
| Table
| [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Execute
| Procedure
| [[dbo uspLogError (procedure)|dbo.uspLogError]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Execute
| Procedure
| [[dbo uspLogError (procedure)|dbo.uspLogError]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Execute
| Procedure
| [[dbo uspPrintError (procedure)|dbo.uspPrintError]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Execute
| Procedure
| [[dbo uspPrintError (procedure)|dbo.uspPrintError]]

| Trigger
| uPurchaseOrderDetail
|}

=== Dependencies ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Reference Type'''
| '''Object Type'''
| '''Referencing Object'''

| '''Child Type'''
| '''Child Object'''

|- valign="top"
| Select
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|- valign="top"
| Select
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|- valign="top"
| Update
| Table
| [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|}
devio dbscript ScrewTurn Wiki

Navigation¶

Purchasing PurchaseOrderDetail (table)
