Purchasing PurchaseOrderHeader (table)

wikibot

TablePurchasing.PurchaseOrderHeader
DescriptionGeneral purchase order information. See PurchaseOrderDetail.

Columns

ColumnData TypeNullableDefaultDescription
PurchaseOrderIDintnot null Primary key.
RevisionNumbertinyintnot null((0))Incremental number to track changes to the purchase order over time.
Statustinyintnot null((1))Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeIDintnot null Employee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorIDintnot null Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodIDintnot null Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDatedatetimenot null(getdate())Purchase order creation date.
ShipDatedatetimenull Estimated shipment date from the vendor.
SubTotalmoneynot null((0.00))Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmtmoneynot null((0.00))Tax amount.
Freightmoneynot null((0.00))Shipping cost.
TotalDue   Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_PurchaseOrderHeader_PurchaseOrderIDPurchaseOrderID

Indexes

IndexTypeColumns
IX_PurchaseOrderHeader_EmployeeID EmployeeID
IX_PurchaseOrderHeader_VendorID VendorID

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
HumanResources.EmployeeEmployeeIDEmployeeID
Purchasing.ShipMethodShipMethodIDShipMethodID
Purchasing.VendorVendorIDVendorID

Detail Tables

Detail TableColumnReferencing Column
Purchasing.PurchaseOrderDetailPurchaseOrderIDPurchaseOrderID

Triggers

TriggerType
uPurchaseOrderHeaderON 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 TypeObject TypeReferenced ObjectChild TypeChild Object
SchemaSchemaPurchasing  
UpdateTablePurchasing.PurchaseOrderHeaderTriggeruPurchaseOrderHeader
ExecuteProceduredbo.uspLogErrorTriggeruPurchaseOrderHeader
ExecuteProceduredbo.uspPrintErrorTriggeruPurchaseOrderHeader

Dependencies

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectTablePurchasing.PurchaseOrderDetailTriggeriPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderDetailTriggeriPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderDetailTriggeruPurchaseOrderDetail
UpdateTablePurchasing.PurchaseOrderHeaderTriggeruPurchaseOrderHeader