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

From dbscript Online Help
Jump to: navigation, search
Line 12: Line 12:
  
  
 +
=== 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 18: Line 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
 
 
 
|- valign="top"
 
|- valign="top"
Line 25: Line 26:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key.<br />PK_PurchaseOrderHeader_PurchaseOrderID
+
| Primary key.
 
|- valign="top"
 
|- valign="top"
 
| RevisionNumber
 
| RevisionNumber
Line 31: Line 32:
 
| not null
 
| not null
 
| ((0))
 
| ((0))
−
| Incremental number to track changes to the purchase order over time.<br />
+
| Incremental number to track changes to the purchase order over time.
 
|- valign="top"
 
|- valign="top"
 
| Status
 
| Status
Line 37: Line 38:
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
| Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete<br />
+
| Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
 
|- valign="top"
 
|- valign="top"
 
| EmployeeID
 
| EmployeeID
Line 43: Line 44:
 
| not null
 
| not null
 
|  
 
|  
−
| Employee who created the purchase order. Foreign key to Employee.EmployeeID.<br />IX_PurchaseOrderHeader_EmployeeID
+
| Employee who created the purchase order. Foreign key to Employee.EmployeeID.
 
|- valign="top"
 
|- valign="top"
 
| VendorID
 
| VendorID
Line 49: Line 50:
 
| not null
 
| not null
 
|  
 
|  
−
| Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.<br />IX_PurchaseOrderHeader_VendorID
+
| Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
 
|- valign="top"
 
|- valign="top"
 
| ShipMethodID
 
| ShipMethodID
Line 55: Line 56:
 
| not null
 
| not null
 
|  
 
|  
−
| Shipping method. Foreign key to ShipMethod.ShipMethodID.<br />
+
| Shipping method. Foreign key to ShipMethod.ShipMethodID.
 
|- valign="top"
 
|- valign="top"
 
| OrderDate
 
| OrderDate
Line 61: Line 62:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Purchase order creation date.<br />
+
| Purchase order creation date.
 
|- valign="top"
 
|- valign="top"
 
| ShipDate
 
| ShipDate
Line 67: Line 68:
 
| null
 
| null
 
|  
 
|  
−
| Estimated shipment date from the vendor.<br />
+
| Estimated shipment date from the vendor.
 
|- valign="top"
 
|- valign="top"
 
| SubTotal
 
| SubTotal
Line 73: Line 74:
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
| Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.<br />
+
| Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
 
|- valign="top"
 
|- valign="top"
 
| TaxAmt
 
| TaxAmt
Line 79: Line 80:
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
| Tax amount.<br />
+
| Tax amount.
 
|- valign="top"
 
|- valign="top"
 
| Freight
 
| Freight
Line 85: Line 86:
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
| Shipping cost.<br />
+
| Shipping cost.
 
|- valign="top"
 
|- valign="top"
 
| TotalDue
 
| TotalDue
Line 91: Line 92:
 
|  
 
|  
 
|  
 
|  
−
| Total due to vendor. Computed as Subtotal + TaxAmt + Freight.<br />
+
| Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 97: Line 98:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date and time the record was last updated.<br />
+
| 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_PurchaseOrderHeader_PurchaseOrderID
 +
| PurchaseOrderID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| IX_PurchaseOrderHeader_VendorID
 +
|
 +
| VendorID
 +
|-
 +
| IX_PurchaseOrderHeader_EmployeeID
 +
|
 +
| EmployeeID
 +
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''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 ===
 
{| 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 122: Line 178:
 
|}
 
|}
  
−
 
+
=== 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 135: Line 191:
 
|}
 
|}
  
−
 
+
=== 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"
Line 146: Line 202:
 
|}
 
|}
  
 +
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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 156: Line 251:
 
| '''Child Object
 
| '''Child Object
  
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Purchasing_(schema)|Purchasing]]
 +
 +
|
 +
|
 
|-
 
|-
 
| Update
 
| Update
Line 179: Line 281:
 
|}
 
|}
  
−
 
+
=== Dependencies ===
 
{| 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 218: Line 320:
 
| uPurchaseOrderHeader
 
| uPurchaseOrderHeader
 
|}
 
|}
−
 
  
  

Revision as of 23:05, 2 February 2010

wikibot

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


Columns

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

Primary Key Columns
PK_PurchaseOrderHeader_PurchaseOrderID PurchaseOrderID

Indexes

Index Type Columns
IX_PurchaseOrderHeader_VendorID VendorID
IX_PurchaseOrderHeader_EmployeeID EmployeeID

Check Constraints

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

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

Detail Tables

Detail Table Column Referencing Column
Purchasing.PurchaseOrderDetail PurchaseOrderID PurchaseOrderID

Triggers

Triggers Type
uPurchaseOrderHeader ON UPDATE

Trigger uPurchaseOrderHeader 

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

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

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

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=2103"