Purchasing.PurchaseOrderHeader (table)

From dbscript Online Help
Jump to: navigation, search

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