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

From dbscript Online Help
Jump to: navigation, search
 
(One intermediate revision 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
Line 8: Line 8:
 
| '''Description
 
| '''Description
 
| General purchase order information. See PurchaseOrderDetail.
 
| General purchase order information. See PurchaseOrderDetail.
−
|-
 
 
|}
 
|}
−
 
  
 
=== Columns ===
 
=== Columns ===
Line 20: Line 18:
 
| '''Default
 
| '''Default
 
| '''Description
 
| '''Description
−
+
 
 
|- valign="top"
 
|- valign="top"
 
| PurchaseOrderID
 
| PurchaseOrderID
Line 106: Line 104:
 
| '''Primary Key
 
| '''Primary Key
 
| '''Columns
 
| '''Columns
−
|-
+
|- valign="top"
 
| PK_PurchaseOrderHeader_PurchaseOrderID
 
| PK_PurchaseOrderHeader_PurchaseOrderID
 
| PurchaseOrderID
 
| PurchaseOrderID
Line 117: Line 115:
 
| '''Type
 
| '''Type
 
| '''Columns
 
| '''Columns
−
+
 
−
|-
+
|- valign="top"
 +
| IX_PurchaseOrderHeader_EmployeeID
 +
|
 +
| EmployeeID
 +
|- valign="top"
 
| IX_PurchaseOrderHeader_VendorID
 
| IX_PurchaseOrderHeader_VendorID
 
|  
 
|  
 
| VendorID
 
| VendorID
−
|-
 
−
| IX_PurchaseOrderHeader_EmployeeID
 
−
|
 
−
| EmployeeID
 
 
|}
 
|}
  
Line 134: Line 132:
 
| '''Expression
 
| '''Expression
 
| '''Description
 
| '''Description
−
+
 
−
|-
+
|- valign="top"
 
| CK_PurchaseOrderHeader_Freight
 
| CK_PurchaseOrderHeader_Freight
 
| ([Freight]>=(0.00))
 
| ([Freight]>=(0.00))
 
| Check constraint [Freight] >= (0.00)
 
| Check constraint [Freight] >= (0.00)
−
|-
+
|- valign="top"
 
| CK_PurchaseOrderHeader_ShipDate
 
| CK_PurchaseOrderHeader_ShipDate
 
| ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
 
| ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
 
| Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
 
| Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
−
|-
+
|- valign="top"
 
| CK_PurchaseOrderHeader_Status
 
| CK_PurchaseOrderHeader_Status
 
| ([Status]>=(1) AND [Status]<=(4))
 
| ([Status]>=(1) AND [Status]<=(4))
 
| Check constraint [Status] BETWEEN (1) AND (4)
 
| Check constraint [Status] BETWEEN (1) AND (4)
−
|-
+
|- valign="top"
 
| CK_PurchaseOrderHeader_SubTotal
 
| CK_PurchaseOrderHeader_SubTotal
 
| ([SubTotal]>=(0.00))
 
| ([SubTotal]>=(0.00))
 
| Check constraint [SubTotal] >= (0.00)
 
| Check constraint [SubTotal] >= (0.00)
−
|-
+
|- valign="top"
 
| CK_PurchaseOrderHeader_TaxAmt
 
| CK_PurchaseOrderHeader_TaxAmt
 
| ([TaxAmt]>=(0.00))
 
| ([TaxAmt]>=(0.00))
Line 163: 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 184: Line 182:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| PurchaseOrderID
 
| PurchaseOrderID
Line 194: Line 192:
 
{| 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
+
==== Trigger uPurchaseOrderHeader ====
 
<pre>
 
<pre>
 
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]  
 
CREATE TRIGGER [Purchasing].[uPurchaseOrderHeader] ON [Purchasing].[PurchaseOrderHeader]  
Line 240: Line 238:
 
END;
 
END;
 
</pre>
 
</pre>
−
+
 
 
=== References ===
 
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 251: Line 249:
 
| '''Child Object
 
| '''Child Object
  
−
|-
+
|- valign="top"
 
| Schema
 
| Schema
 
| Schema
 
| Schema
Line 258: Line 256:
 
|  
 
|  
 
|  
 
|  
−
|-
+
|- valign="top"
 
| Update
 
| Update
 
| Table
 
| Table
Line 265: Line 263:
 
| Trigger
 
| Trigger
 
| uPurchaseOrderHeader
 
| uPurchaseOrderHeader
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 272: Line 270:
 
| Trigger
 
| Trigger
 
| uPurchaseOrderHeader
 
| uPurchaseOrderHeader
−
|-
+
|- valign="top"
 
| Execute
 
| Execute
 
| Procedure
 
| Procedure
Line 291: Line 289:
 
| '''Child Object
 
| '''Child Object
  
−
|-
+
|- valign="top"
 
| Select
 
| Select
 
| Table
 
| Table
Line 298: Line 296:
 
| Trigger
 
| Trigger
 
| iPurchaseOrderDetail
 
| iPurchaseOrderDetail
−
|-
+
|- valign="top"
 
| Update
 
| Update
 
| Table
 
| Table
Line 305: Line 303:
 
| Trigger
 
| Trigger
 
| iPurchaseOrderDetail
 
| iPurchaseOrderDetail
−
|-
+
|- valign="top"
 
| Update
 
| Update
 
| Table
 
| Table
Line 312: Line 310:
 
| Trigger
 
| Trigger
 
| uPurchaseOrderDetail
 
| uPurchaseOrderDetail
−
|-
+
|- valign="top"
 
| Update
 
| Update
 
| Table
 
| Table

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"