Difference between revisions of "Purchasing.PurchaseOrderDetail (table)"

From dbscript Online Help
Jump to: navigation, search
 
(8 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
 
| '''Table
 
| '''Table
 
| Purchasing.PurchaseOrderDetail
 
| 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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| PurchaseOrderID
 
| PurchaseOrderID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
+
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
−
|
+
|- valign="top"
−
|-
 
 
| PurchaseOrderDetailID
 
| PurchaseOrderDetailID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
+
| Primary key. One line number per purchased product.
−
|
+
|- valign="top"
−
|-
 
 
| DueDate
 
| DueDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Date the product is expected to be received.
−
|
+
|- valign="top"
−
|-
 
 
| OrderQty
 
| OrderQty
 
| smallint
 
| smallint
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Quantity ordered.
−
|
+
|- valign="top"
−
|-
 
 
| ProductID
 
| ProductID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Product identification number. Foreign key to Product.ProductID.
−
| IX_PurchaseOrderDetail_ProductID
+
|- valign="top"
−
|-
 
 
| UnitPrice
 
| UnitPrice
 
| money
 
| money
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Vendor's selling price of a single product.
−
|
+
|- valign="top"
−
|-
 
 
| LineTotal
 
| LineTotal
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Per product subtotal. Computed as OrderQty * UnitPrice.
−
|
+
|- valign="top"
−
|-
 
 
| ReceivedQty
 
| ReceivedQty
 
| decimal(8, 2)
 
| decimal(8, 2)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Quantity actually received from the vendor.
−
|
+
|- valign="top"
−
|-
 
 
| RejectedQty
 
| RejectedQty
 
| decimal(8, 2)
 
| decimal(8, 2)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Quantity rejected during inspection.
−
|
+
|- valign="top"
−
|-
 
 
| StockedQty
 
| StockedQty
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
−
|
+
|- valign="top"
−
|-
 
 
| ModifiedDate
 
| ModifiedDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (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
 
|  
 
|  
−
|  
+
| 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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 104: Line 141:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.Product_(table)|Production.Product]]
 
| [[Production.Product_(table)|Production.Product]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 
| PurchaseOrderID
 
| PurchaseOrderID
Line 115: Line 152:
 
|}
 
|}
  
−
 
+
=== Triggers ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
−
| '''Triggers
+
| '''Trigger
 
| '''Type
 
| '''Type
−
+
 
−
|-
+
|- valign="top"
 
| iPurchaseOrderDetail
 
| iPurchaseOrderDetail
 
| ON INSERT
 
| ON INSERT
−
|-
+
|- valign="top"
 
| uPurchaseOrderDetail
 
| uPurchaseOrderDetail
 
| ON UPDATE
 
| ON UPDATE
 
|}
 
|}
 +
 +
==== Trigger iPurchaseOrderDetail ====
 +
<pre>
 +
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;
 +
</pre>
 +
 +
==== Trigger uPurchaseOrderDetail ====
 +
<pre>
 +
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;
 +
</pre>
 +
 +
=== 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]]
 +
 +
|
 +
|
 +
|- 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
 +
|}
 +
 +
 
== automatically generated ==
 
== automatically generated ==
  
Line 134: Line 437:
 
| '''Table
 
| '''Table
 
| Purchasing.PurchaseOrderDetail
 
| Purchasing.PurchaseOrderDetail
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Individual products associated with a specific purchase order. See PurchaseOrderHeader.
 
| Individual products associated with a specific purchase order. See PurchaseOrderHeader.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 156: Line 457:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
+
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
 
|- valign="top"
 
|- valign="top"
 
| PurchaseOrderDetailID
 
| PurchaseOrderDetailID
Line 162: Line 463:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key. One line number per purchased product.PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
+
| Primary key. One line number per purchased product.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
 
|- valign="top"
 
|- valign="top"
 
| DueDate
 
| DueDate
Line 168: Line 469:
 
| not null
 
| not null
 
|  
 
|  
−
| Date the product is expected to be received.
+
| Date the product is expected to be received.<br />
 
|- valign="top"
 
|- valign="top"
 
| OrderQty
 
| OrderQty
Line 174: Line 475:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity ordered.
+
| Quantity ordered.<br />
 
|- valign="top"
 
|- valign="top"
 
| ProductID
 
| ProductID
Line 180: Line 481:
 
| not null
 
| not null
 
|  
 
|  
−
| Product identification number. Foreign key to Product.ProductID.IX_PurchaseOrderDetail_ProductID
+
| Product identification number. Foreign key to Product.ProductID.<br />IX_PurchaseOrderDetail_ProductID
 
|- valign="top"
 
|- valign="top"
 
| UnitPrice
 
| UnitPrice
Line 186: Line 487:
 
| not null
 
| not null
 
|  
 
|  
−
| Vendor's selling price of a single product.
+
| Vendor's selling price of a single product.<br />
 
|- valign="top"
 
|- valign="top"
 
| LineTotal
 
| LineTotal
Line 192: Line 493:
 
|  
 
|  
 
|  
 
|  
−
| Per product subtotal. Computed as OrderQty * UnitPrice.
+
| Per product subtotal. Computed as OrderQty * UnitPrice.<br />
 
|- valign="top"
 
|- valign="top"
 
| ReceivedQty
 
| ReceivedQty
Line 198: Line 499:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity actually received from the vendor.
+
| Quantity actually received from the vendor.<br />
 
|- valign="top"
 
|- valign="top"
 
| RejectedQty
 
| RejectedQty
Line 204: Line 505:
 
| not null
 
| not null
 
|  
 
|  
−
| Quantity rejected during inspection.
+
| Quantity rejected during inspection.<br />
 
|- valign="top"
 
|- valign="top"
 
| StockedQty
 
| StockedQty
Line 210: Line 511:
 
|  
 
|  
 
|  
 
|  
−
| Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
+
| Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.<br />
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 216: Line 517:
 
| not null
 
| not null
 
| (GETDATE())
 
| (GETDATE())
−
| Date and time the record was last updated.
+
| Date and time the record was last updated.<br />
 
|}
 
|}
  
Line 254: Line 555:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
+
| '''Child Type
−
+
| '''Child Object
 +
 
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Purchasing_(schema)|Purchasing]]
 +
 
 +
|
 +
|
 
|-
 
|-
 +
| Insert
 +
| Table
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|-
 +
| Insert
 
| Table
 
| Table
−
| Insert
+
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 +
 
 +
| Trigger
 +
| uPurchaseOrderDetail
 
|-
 
|-
−
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
+
| Select
 
| Table
 
| Table
−
| Insert
+
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 
 +
| Trigger
 +
| uPurchaseOrderDetail
 
|-
 
|-
 +
| Select
 +
| Table
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|-
 +
| Update
 
| Table
 
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 +
|-
 
| Select
 
| Select
−
|-
 
−
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
 
| Table
 
| Table
−
| Select
+
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 +
 
 +
| Trigger
 +
| iPurchaseOrderDetail
 
|-
 
|-
−
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
+
| Update
 
| Table
 
| Table
−
| Update
 
−
|-
 
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
−
| Table
+
 
−
| Select
+
| Trigger
 +
| iPurchaseOrderDetail
 
|-
 
|-
−
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
+
| Update
 
| Table
 
| Table
−
| Update
 
−
|-
 
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
−
| Table
+
 
−
| Update
+
| Trigger
 +
| uPurchaseOrderDetail
 
|-
 
|-
 +
| Execute
 +
| Procedure
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|-
 +
| Execute
 
| Procedure
 
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 +
|-
 
| Execute
 
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 
|-
 
|-
−
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 
−
| Procedure
 
 
| Execute
 
| Execute
−
|-
 
−
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 
 
| Procedure
 
| Procedure
−
| Execute
 
−
|-
 
 
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
−
| Procedure
+
 
−
| Execute
+
| Trigger
 +
| iPurchaseOrderDetail
 
|}
 
|}
  
Line 311: Line 658:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 
| '''Referencing Object
 
| '''Referencing Object
−
| '''Object Type
+
 
−
| '''Reference Type
+
| '''Child Type
−
+
| '''Child Object
 +
 
 
|-
 
|-
 +
| Select
 +
| Table
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|-
 +
| Select
 
| Table
 
| Table
−
| Select
+
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 
 +
| Trigger
 +
| uPurchaseOrderDetail
 
|-
 
|-
−
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
+
| Update
 
| Table
 
| Table
−
| Select
 
−
|-
 
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
−
| Table
+
 
−
| Update
+
| Trigger
 +
| uPurchaseOrderDetail
 
|}
 
|}

Latest revision as of 10:55, 27 August 2011

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]

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

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]

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[edit]

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


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.


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


Triggers Type
iPurchaseOrderDetail ON INSERT
uPurchaseOrderDetail ON UPDATE


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 uPurchaseOrderDetail
Select Table Purchasing.PurchaseOrderDetail Trigger iPurchaseOrderDetail
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 uPurchaseOrderDetail
Execute Procedure dbo.uspPrintError Trigger iPurchaseOrderDetail


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
Retrieved from "http://dbshelp.devio.at/index.php?title=Purchasing.PurchaseOrderDetail_(table)&oldid=2938"