Welcome
Guest
, you are in:
<root>
•
Login
devio dbscript ScrewTurn Wiki
Navigation
¶
Main Page
Random Page
Create a new Page
All Pages
Categories
Navigation Paths
Administration
File Management
Create Account
Search the wiki
»
Back
Purchasing PurchaseOrderHeader (table)
Modified on 2011/08/27 11:18
by
wikibot
Categorized as
Uncategorized
== wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- valign="top" | '''Table''' | Purchasing.PurchaseOrderHeader |- valign="top" | '''Description''' | General purchase order information. See PurchaseOrderDetail. |} === Columns === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description''' |- valign="top" | PurchaseOrderID | int | not null | | Primary key. |- valign="top" | RevisionNumber | tinyint | not null | ((0)) | Incremental number to track changes to the purchase order over time. |- valign="top" | Status | tinyint | not null | ((1)) | Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete |- valign="top" | EmployeeID | int | not null | | Employee who created the purchase order. Foreign key to Employee.EmployeeID. |- valign="top" | VendorID | int | not null | | Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID. |- valign="top" | ShipMethodID | int | not null | | Shipping method. Foreign key to ShipMethod.ShipMethodID. |- valign="top" | OrderDate | datetime | not null | (getdate()) | Purchase order creation date. |- valign="top" | ShipDate | datetime | null | | Estimated shipment date from the vendor. |- valign="top" | SubTotal | money | not null | ((0.00)) | Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID. |- valign="top" | TaxAmt | money | not null | ((0.00)) | Tax amount. |- valign="top" | Freight | money | not null | ((0.00)) | Shipping cost. |- valign="top" | TotalDue | | | | Total due to vendor. Computed as Subtotal + TaxAmt + Freight. |- valign="top" | ModifiedDate | datetime | not null | (getdate()) | Date and time the record was last updated. |} === Primary Key === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Primary Key''' | '''Columns''' |- valign="top" | PK_PurchaseOrderHeader_PurchaseOrderID | PurchaseOrderID |} === Indexes === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Index''' | '''Type''' | '''Columns''' |- valign="top" | IX_PurchaseOrderHeader_EmployeeID | | EmployeeID |- valign="top" | IX_PurchaseOrderHeader_VendorID | | VendorID |} === Check Constraints === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Check Constraint''' | '''Expression''' | '''Description''' |- valign="top" | CK_PurchaseOrderHeader_Freight | ([Freight]>=(0.00)) | Check constraint [Freight] >= (0.00) |- valign="top" | CK_PurchaseOrderHeader_ShipDate | ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) | Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL |- valign="top" | CK_PurchaseOrderHeader_Status | ([Status]>=(1) AND [Status]<=(4)) | Check constraint [Status] BETWEEN (1) AND (4) |- valign="top" | CK_PurchaseOrderHeader_SubTotal | ([SubTotal]>=(0.00)) | Check constraint [SubTotal] >= (0.00) |- valign="top" | CK_PurchaseOrderHeader_TaxAmt | ([TaxAmt]>=(0.00)) | Check constraint [TaxAmt] >= (0.00) |} === Foreign Keys === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Relation''' | '''Column''' | '''Referenced Column''' |- valign="top" | [[HumanResources Employee (table)|HumanResources.Employee]] | EmployeeID | EmployeeID |- valign="top" | [[Purchasing ShipMethod (table)|Purchasing.ShipMethod]] | ShipMethodID | ShipMethodID |- valign="top" | [[Purchasing Vendor (table)|Purchasing.Vendor]] | VendorID | VendorID |} === Detail Tables === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Detail Table''' | '''Column''' | '''Referencing Column''' |- valign="top" | [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]] | PurchaseOrderID | PurchaseOrderID |} === Triggers === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Trigger''' | '''Type''' |- valign="top" | uPurchaseOrderHeader | ON UPDATE |} ==== Trigger uPurchaseOrderHeader ==== {{{{<nowiki> 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; </nowiki>}}}} === References === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' | '''Child Type''' | '''Child Object''' |- valign="top" | Schema | Schema | [[Purchasing (schema)|Purchasing]] | | |- valign="top" | Update | Table | [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]] | Trigger | uPurchaseOrderHeader |- valign="top" | Execute | Procedure | [[dbo uspLogError (procedure)|dbo.uspLogError]] | Trigger | uPurchaseOrderHeader |- valign="top" | Execute | Procedure | [[dbo uspPrintError (procedure)|dbo.uspPrintError]] | Trigger | uPurchaseOrderHeader |} === Dependencies === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Reference Type''' | '''Object Type''' | '''Referencing Object''' | '''Child Type''' | '''Child Object''' |- valign="top" | Select | Table | [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]] | Trigger | iPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing PurchaseOrderDetail (table)|Purchasing.PurchaseOrderDetail]] | Trigger | uPurchaseOrderDetail |- valign="top" | Update | Table | [[Purchasing PurchaseOrderHeader (table)|Purchasing.PurchaseOrderHeader]] | Trigger | uPurchaseOrderHeader |}
ScrewTurn Wiki
version 3.0.1.400. Some of the icons created by
FamFamFam
.