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

From dbscript Online Help
Jump to: navigation, search
 
(4 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.PurchaseOrderHeader
 
| Purchasing.PurchaseOrderHeader
−
|-
+
|- valign="top"
 +
| '''Description
 +
| General purchase order information. See PurchaseOrderDetail.
 
|}
 
|}
  
−
 
+
=== 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_PurchaseOrderHeader_PurchaseOrderID
+
| Primary key.
−
|
+
|- valign="top"
−
|-
 
 
| RevisionNumber
 
| RevisionNumber
 
| tinyint
 
| tinyint
 
| not null
 
| not null
 
| ((0))
 
| ((0))
−
|  
+
| Incremental number to track changes to the purchase order over time.
−
|
+
|- valign="top"
−
|-
 
 
| Status
 
| Status
 
| tinyint
 
| tinyint
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
|  
+
| Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
−
|
+
|- valign="top"
−
|-
 
 
| EmployeeID
 
| EmployeeID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Employee who created the purchase order. Foreign key to Employee.EmployeeID.
−
| IX_PurchaseOrderHeader_EmployeeID
+
|- valign="top"
−
|-
 
 
| VendorID
 
| VendorID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
−
| IX_PurchaseOrderHeader_VendorID
+
|- valign="top"
−
|-
 
 
| ShipMethodID
 
| ShipMethodID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Shipping method. Foreign key to ShipMethod.ShipMethodID.
−
|
+
|- valign="top"
−
|-
 
 
| OrderDate
 
| OrderDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
|  
+
| Purchase order creation date.
−
|
+
|- valign="top"
−
|-
 
 
| ShipDate
 
| ShipDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Estimated shipment date from the vendor.
−
|
+
|- valign="top"
−
|-
 
 
| SubTotal
 
| SubTotal
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
−
|
+
|- valign="top"
−
|-
 
 
| TaxAmt
 
| TaxAmt
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Tax amount.
−
|
+
|- valign="top"
−
|-
 
 
| Freight
 
| Freight
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Shipping cost.
−
|
+
|- valign="top"
−
|-
 
 
| TotalDue
 
| TotalDue
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
−
|
+
|- 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_PurchaseOrderHeader_PurchaseOrderID
 +
| PurchaseOrderID
 +
|}
 +
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|- valign="top"
 +
| IX_PurchaseOrderHeader_EmployeeID
 
|  
 
|  
 +
| EmployeeID
 +
|- valign="top"
 +
| IX_PurchaseOrderHeader_VendorID
 
|  
 
|  
 +
| VendorID
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
  
 +
|- valign="top"
 +
| CK_PurchaseOrderHeader_Freight
 +
| ([Freight]>=(0.00))
 +
| Check constraint [Freight] >= (0.00)
 +
|- valign="top"
 +
| CK_PurchaseOrderHeader_ShipDate
 +
| ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
 +
| Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
 +
|- valign="top"
 +
| CK_PurchaseOrderHeader_Status
 +
| ([Status]>=(1) AND [Status]<=(4))
 +
| Check constraint [Status] BETWEEN (1) AND (4)
 +
|- valign="top"
 +
| CK_PurchaseOrderHeader_SubTotal
 +
| ([SubTotal]>=(0.00))
 +
| Check constraint [SubTotal] >= (0.00)
 +
|- valign="top"
 +
| CK_PurchaseOrderHeader_TaxAmt
 +
| ([TaxAmt]>=(0.00))
 +
| Check constraint [TaxAmt] >= (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 118: Line 161:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 
| [[HumanResources.Employee_(table)|HumanResources.Employee]]
 
| EmployeeID
 
| EmployeeID
 
| EmployeeID
 
| EmployeeID
−
|-
+
|- valign="top"
 
| [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]]
 
| [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]]
 
| ShipMethodID
 
| ShipMethodID
 
| ShipMethodID
 
| ShipMethodID
−
|-
+
|- valign="top"
 
| [[Purchasing.Vendor_(table)|Purchasing.Vendor]]
 
| [[Purchasing.Vendor_(table)|Purchasing.Vendor]]
 
| VendorID
 
| VendorID
Line 133: Line 176:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| 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 139: Line 182:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| PurchaseOrderID
 
| PurchaseOrderID
Line 146: Line 189:
 
|}
 
|}
  
−
 
+
=== 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"
 
| uPurchaseOrderHeader
 
| uPurchaseOrderHeader
 
| ON UPDATE
 
| ON UPDATE
 
|}
 
|}
 +
 +
==== Trigger uPurchaseOrderHeader ====
 +
<pre>
 +
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]
 +
AFTER UPDATE AS
 +
BEGIN
 +
    DECLARE @Count int;
 +
 +
    SET @Count = @@ROWCOUNT;
 +
    IF @Count = 0
 +
        RETURN;
 +
 +
    SET NOCOUNT ON;
 +
 +
    BEGIN TRY
 +
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
 +
        IF NOT UPDATE([Status])
 +
        BEGIN
 +
            UPDATE [Purchasing].[PurchaseOrderHeader]
 +
            SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] =
 +
                [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
 +
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN
 +
                (SELECT inserted.[PurchaseOrderID] FROM inserted);
 +
        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"
 +
| Update
 +
| Table
 +
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|- valign="top"
 +
| Execute
 +
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|}
 +
 +
=== 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"
 +
| Update
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| iPurchaseOrderDetail
 +
|- valign="top"
 +
| Update
 +
| Table
 +
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 +
 +
| Trigger
 +
| uPurchaseOrderDetail
 +
|- valign="top"
 +
| Update
 +
| Table
 +
| [[Purchasing.PurchaseOrderHeader_(table)|Purchasing.PurchaseOrderHeader]]
 +
 +
| Trigger
 +
| uPurchaseOrderHeader
 +
|}
 +
 +
 
== automatically generated ==
 
== automatically generated ==
  
Line 163: Line 327:
 
| Purchasing.PurchaseOrderHeader
 
| Purchasing.PurchaseOrderHeader
 
|- valign="top"
 
|- valign="top"
−
 
 
| '''Description
 
| '''Description
 
| General purchase order information. See PurchaseOrderDetail.
 
| General purchase order information. See PurchaseOrderDetail.
 
|-
 
|-
−
 
 
|}
 
|}
  

Latest revision as of 09:56, 27 August 2011

wikibot[edit]

Table Purchasing.PurchaseOrderHeader
Description General purchase order information. See PurchaseOrderDetail.

Columns[edit]

Column Data Type Nullable Default Description
PurchaseOrderID int not null Primary key.
RevisionNumber tinyint not null ((0)) Incremental number to track changes to the purchase order over time.
Status tinyint not null ((1)) Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID int not null Employee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorID int not null Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodID int not null Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate datetime not null (getdate()) Purchase order creation date.
ShipDate datetime null Estimated shipment date from the vendor.
SubTotal money not null ((0.00)) Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt money not null ((0.00)) Tax amount.
Freight money not null ((0.00)) Shipping cost.
TotalDue Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key[edit]

Primary Key Columns
PK_PurchaseOrderHeader_PurchaseOrderID PurchaseOrderID

Indexes[edit]

Index Type Columns
IX_PurchaseOrderHeader_EmployeeID EmployeeID
IX_PurchaseOrderHeader_VendorID VendorID

Check Constraints[edit]

Check Constraint Expression Description
CK_PurchaseOrderHeader_Freight ([Freight]>=(0.00)) Check constraint [Freight] >= (0.00)
CK_PurchaseOrderHeader_ShipDate ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_PurchaseOrderHeader_Status ([Status]>=(1) AND [Status]<=(4)) Check constraint [Status] BETWEEN (1) AND (4)
CK_PurchaseOrderHeader_SubTotal ([SubTotal]>=(0.00)) Check constraint [SubTotal] >= (0.00)
CK_PurchaseOrderHeader_TaxAmt ([TaxAmt]>=(0.00)) Check constraint [TaxAmt] >= (0.00)

Foreign Keys[edit]

Relation Column Referenced Column
HumanResources.Employee EmployeeID EmployeeID
Purchasing.ShipMethod ShipMethodID ShipMethodID
Purchasing.Vendor VendorID VendorID

Detail Tables[edit]

Detail Table Column Referencing Column
Purchasing.PurchaseOrderDetail PurchaseOrderID PurchaseOrderID

Triggers[edit]

Trigger Type
uPurchaseOrderHeader ON UPDATE

Trigger uPurchaseOrderHeader[edit]

CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        -- Update RevisionNumber for modification of any field EXCEPT the Status.
        IF NOT UPDATE([Status])
        BEGIN
            UPDATE [Purchasing].[PurchaseOrderHeader]
            SET [Purchasing].[PurchaseOrderHeader].[RevisionNumber] = 
                [Purchasing].[PurchaseOrderHeader].[RevisionNumber] + 1
            WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN 
                (SELECT inserted.[PurchaseOrderID] FROM inserted);
        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
Update Table Purchasing.PurchaseOrderHeader Trigger uPurchaseOrderHeader
Execute Procedure dbo.uspLogError Trigger uPurchaseOrderHeader
Execute Procedure dbo.uspPrintError Trigger uPurchaseOrderHeader

Dependencies[edit]

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


automatically generated[edit]

Table Purchasing.PurchaseOrderHeader
Description General purchase order information. See PurchaseOrderDetail.


Column Data Type Nullable Default Description / PK / Index
PurchaseOrderID int not null Primary key.
PK_PurchaseOrderHeader_PurchaseOrderID
RevisionNumber tinyint not null (0) Incremental number to track changes to the purchase order over time.
Status tinyint not null (1) Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID int not null Employee who created the purchase order. Foreign key to Employee.EmployeeID.
IX_PurchaseOrderHeader_EmployeeID
VendorID int not null Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
IX_PurchaseOrderHeader_VendorID
ShipMethodID int not null Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate datetime not null (GETDATE()) Purchase order creation date.
ShipDate datetime null Estimated shipment date from the vendor.
SubTotal money not null (0.00) Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt money not null (0.00) Tax amount.
Freight money not null (0.00) Shipping cost.
TotalDue Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
HumanResources.Employee EmployeeID EmployeeID
Purchasing.ShipMethod ShipMethodID ShipMethodID
Purchasing.Vendor VendorID VendorID


Detail Table Column Referencing Column
Purchasing.PurchaseOrderDetail PurchaseOrderID PurchaseOrderID


Triggers Type
uPurchaseOrderHeader ON UPDATE


Dependency Type Object Type Referenced Object Child Type Child Object
Schema Schema Purchasing
Update Table Purchasing.PurchaseOrderHeader Trigger uPurchaseOrderHeader
Execute Procedure dbo.uspLogError Trigger uPurchaseOrderHeader
Execute Procedure dbo.uspPrintError Trigger uPurchaseOrderHeader


Reference Type Object Type Referencing Object Child Type Child Object
Select Table Purchasing.PurchaseOrderDetail Trigger iPurchaseOrderDetail
Update Table Purchasing.PurchaseOrderDetail Trigger iPurchaseOrderDetail
Update Table Purchasing.PurchaseOrderDetail Trigger uPurchaseOrderDetail
Update Table Purchasing.PurchaseOrderHeader Trigger uPurchaseOrderHeader
Retrieved from "http://dbshelp.devio.at/index.php?title=Purchasing.PurchaseOrderHeader_(table)&oldid=2939"