Purchasing.PurchaseOrderHeader (table)

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

Trigger	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