Editing Purchasing.PurchaseOrderDetail (table)

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''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
| 
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
|- valign="top"
| PurchaseOrderDetailID
| int
| not null
| 
| Primary key. One line number per purchased product.
|- valign="top"
| DueDate
| datetime
| not null
| 
| Date the product is expected to be received.
|- valign="top"
| OrderQty
| smallint
| not null
| 
| Quantity ordered.
|- valign="top"
| ProductID
| int
| not null
| 
| Product identification number. Foreign key to Product.ProductID.
|- valign="top"
| UnitPrice
| money
| not null
| 
| Vendor's selling price of a single product.
|- valign="top"
| LineTotal
| 
| 
| 
| Per product subtotal. Computed as OrderQty * UnitPrice.
|- valign="top"
| ReceivedQty
| decimal(8, 2)
| not null
| 
| Quantity actually received from the vendor.
|- valign="top"
| RejectedQty
| decimal(8, 2)
| not null
| 
| Quantity rejected during inspection.
|- valign="top"
| StockedQty
| 
| 
| 
| 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
|-
| PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
| PurchaseOrderID, PurchaseOrderDetailID
|}

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

=== Check Constraints ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''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 ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Production.Product_(table)|Production.Product]]
| ProductID
| ProductID
|-
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| PurchaseOrderID
| PurchaseOrderID
|}

=== Triggers ===
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| iPurchaseOrderDetail
| ON INSERT
|-
| uPurchaseOrderDetail
| 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

|-
| Schema
| Schema
| [[Purchasing_(schema)|Purchasing]]

| 
| 
|-
| Insert
| Table
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]

| Trigger
| iPurchaseOrderDetail
|-
| Insert
| Table
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| iPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| iPurchaseOrderDetail
|}

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

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

|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|}


== automatically generated ==

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


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Column
| '''Data Type
| '''Nullable
| '''Default
| '''Description / PK / Index
		
|- valign="top"
| PurchaseOrderID
| int
| not null
| 
| Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
|- valign="top"
| PurchaseOrderDetailID
| int
| not null
| 
| Primary key. One line number per purchased product.<br />PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID
|- valign="top"
| DueDate
| datetime
| not null
| 
| Date the product is expected to be received.<br />
|- valign="top"
| OrderQty
| smallint
| not null
| 
| Quantity ordered.<br />
|- valign="top"
| ProductID
| int
| not null
| 
| Product identification number. Foreign key to Product.ProductID.<br />IX_PurchaseOrderDetail_ProductID
|- valign="top"
| UnitPrice
| money
| not null
| 
| Vendor's selling price of a single product.<br />
|- valign="top"
| LineTotal
| 
| 
| 
| Per product subtotal. Computed as OrderQty * UnitPrice.<br />
|- valign="top"
| ReceivedQty
| decimal(8, 2)
| not null
| 
| Quantity actually received from the vendor.<br />
|- valign="top"
| RejectedQty
| decimal(8, 2)
| not null
| 
| Quantity rejected during inspection.<br />
|- valign="top"
| StockedQty
| 
| 
| 
| Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.<br />
|- valign="top"
| ModifiedDate
| datetime
| not null
| (GETDATE())
| Date and time the record was last updated.<br />
|}



{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Relation
| '''Column
| '''Referenced Column
		
|-
| [[Production.Product_(table)|Production.Product]]
| ProductID
| ProductID
|-
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
| PurchaseOrderID
| PurchaseOrderID
|}


{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|- style="background:silver"
| '''Triggers
| '''Type
		
|-
| iPurchaseOrderDetail
| ON INSERT
|-
| uPurchaseOrderDetail
| ON UPDATE
|}


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

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

|-
| Schema
| Schema
| [[Purchasing_(schema)|Purchasing]]

| 
| 
|-
| Insert
| Table
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]

| Trigger
| iPurchaseOrderDetail
|-
| Insert
| Table
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| iPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| iPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| uPurchaseOrderDetail
|-
| Execute
| Procedure
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]

| Trigger
| iPurchaseOrderDetail
|}


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

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

|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| iPurchaseOrderDetail
|-
| Select
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|-
| Update
| Table
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]

| Trigger
| uPurchaseOrderDetail
|}