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_EmployeeID | | EmployeeID |
| IX_PurchaseOrderHeader_VendorID | | VendorID |
Check Constraints
Foreign Keys
Detail Tables
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
Dependencies