ScrewTurn Wiki
Navigation
Main Page
Random Page
Create a new Page
All Pages
Categories
Administration
File Management
Login/Logout
Language Selection
Your Profile
Create Account
Quick Search
Advanced Search »
Back
History
Sales.SalesOrderHeader_(table)
== wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- valign="top" | '''Table''' | Sales.SalesOrderHeader |- valign="top" | '''Description''' | General sales order information. |} === Columns === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description''' |- valign="top" | SalesOrderID | int | not null | | Primary key. |- valign="top" | RevisionNumber | tinyint | not null | ((0)) | Incremental number to track changes to the sales order over time. |- valign="top" | OrderDate | datetime | not null | (getdate()) | Dates the sales order was created. |- valign="top" | DueDate | datetime | not null | | Date the order is due to the customer. |- valign="top" | ShipDate | datetime | null | | Date the order was shipped to the customer. |- valign="top" | Status | tinyint | not null | ((1)) | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled |- valign="top" | OnlineOrderFlag | dbo.Flag | not null | ((1)) | 0 = Order placed by sales person. 1 = Order placed online by customer. |- valign="top" | SalesOrderNumber | | | | Unique sales order identification number. |- valign="top" | PurchaseOrderNumber | dbo.OrderNumber | null | | Customer purchase order number reference. |- valign="top" | AccountNumber | dbo.AccountNumber | null | | Financial accounting number reference. |- valign="top" | CustomerID | int | not null | | Customer identification number. Foreign key to Customer.CustomerID. |- valign="top" | ContactID | int | not null | | Customer contact identification number. Foreign key to Contact.ContactID. |- valign="top" | SalesPersonID | int | null | | Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID. |- valign="top" | TerritoryID | int | null | | Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID. |- valign="top" | BillToAddressID | int | not null | | Customer billing address. Foreign key to Address.AddressID. |- valign="top" | ShipToAddressID | int | not null | | Customer shipping address. Foreign key to Address.AddressID. |- valign="top" | ShipMethodID | int | not null | | Shipping method. Foreign key to ShipMethod.ShipMethodID. |- valign="top" | CreditCardID | int | null | | Credit card identification number. Foreign key to CreditCard.CreditCardID. |- valign="top" | CreditCardApprovalCode | varchar(15) | null | | Approval code provided by the credit card company. |- valign="top" | CurrencyRateID | int | null | | Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID. |- valign="top" | SubTotal | money | not null | ((0.00)) | Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID. |- 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 from customer. Computed as Subtotal + TaxAmt + Freight. |- valign="top" | Comment | nvarchar(128) | null | | Sales representative comments. |- valign="top" | rowguid | uniqueidentifier | not null | (newid()) | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |- 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_SalesOrderHeader_SalesOrderID | SalesOrderID |} === Indexes === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Index''' | '''Type''' | '''Columns''' |- valign="top" | AK_SalesOrderHeader_rowguid | Unique | rowguid |- valign="top" | AK_SalesOrderHeader_SalesOrderNumber | Unique | SalesOrderNumber |- valign="top" | IX_SalesOrderHeader_CustomerID | | CustomerID |- valign="top" | IX_SalesOrderHeader_SalesPersonID | | SalesPersonID |} === Check Constraints === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Check Constraint''' | '''Expression''' | '''Description''' |- valign="top" | CK_SalesOrderHeader_DueDate | ([DueDate]>=[OrderDate]) | Check constraint [DueDate] >= [OrderDate] |- valign="top" | CK_SalesOrderHeader_Freight | ([Freight]>=(0.00)) | Check constraint [Freight] >= (0.00) |- valign="top" | CK_SalesOrderHeader_ShipDate | ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL) | Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL |- valign="top" | CK_SalesOrderHeader_Status | ([Status]>=(0) AND [Status]<=(8)) | Check constraint [Status] BETWEEN (0) AND (8) |- valign="top" | CK_SalesOrderHeader_SubTotal | ([SubTotal]>=(0.00)) | Check constraint [SubTotal] >= (0.00) |- valign="top" | CK_SalesOrderHeader_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" | [[Person.Address_(table)|Person.Address]] | BillToAddressID | AddressID |- valign="top" | [[Person.Address_(table)|Person.Address]] | ShipToAddressID | AddressID |- valign="top" | [[Person.Contact_(table)|Person.Contact]] | ContactID | ContactID |- valign="top" | [[Sales.CreditCard_(table)|Sales.CreditCard]] | CreditCardID | CreditCardID |- valign="top" | [[Sales.CurrencyRate_(table)|Sales.CurrencyRate]] | CurrencyRateID | CurrencyRateID |- valign="top" | [[Sales.Customer_(table)|Sales.Customer]] | CustomerID | CustomerID |- valign="top" | [[Sales.SalesPerson_(table)|Sales.SalesPerson]] | SalesPersonID | SalesPersonID |- valign="top" | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]] | TerritoryID | TerritoryID |- valign="top" | [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]] | ShipMethodID | ShipMethodID |} === Detail Tables === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Detail Table''' | '''Column''' | '''Referencing Column''' |- valign="top" | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | SalesOrderID | SalesOrderID |- valign="top" | [[Sales.SalesOrderHeaderSalesReason_(table)|Sales.SalesOrderHeaderSalesReason]] | SalesOrderID | SalesOrderID |} === Triggers === {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Trigger''' | '''Type''' |- valign="top" | uSalesOrderHeader | ON UPDATE |} ==== Trigger uSalesOrderHeader ==== {{{{<nowiki> CREATE TRIGGER [Sales].[uSalesOrderHeader] ON [Sales].[SalesOrderHeader] AFTER UPDATE NOT FOR REPLICATION 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 [Sales].[SalesOrderHeader] SET [Sales].[SalesOrderHeader].[RevisionNumber] = [Sales].[SalesOrderHeader].[RevisionNumber] + 1 WHERE [Sales].[SalesOrderHeader].[SalesOrderID] IN (SELECT inserted.[SalesOrderID] FROM inserted); END; -- Update the SalesPerson SalesYTD when SubTotal is updated IF UPDATE([SubTotal]) BEGIN DECLARE @StartDate datetime, @EndDate datetime SET @StartDate = [dbo].[ufnGetAccountingStartDate](); SET @EndDate = [dbo].[ufnGetAccountingEndDate](); UPDATE [Sales].[SalesPerson] SET [Sales].[SalesPerson].[SalesYTD] = (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) FROM [Sales].[SalesOrderHeader] WHERE [Sales].[SalesPerson].[SalesPersonID] = [Sales].[SalesOrderHeader].[SalesPersonID] AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) WHERE [Sales].[SalesPerson].[SalesPersonID] IN (SELECT DISTINCT inserted.[SalesPersonID] FROM inserted WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); -- Update the SalesTerritory SalesYTD when SubTotal is updated UPDATE [Sales].[SalesTerritory] SET [Sales].[SalesTerritory].[SalesYTD] = (SELECT SUM([Sales].[SalesOrderHeader].[SubTotal]) FROM [Sales].[SalesOrderHeader] WHERE [Sales].[SalesTerritory].[TerritoryID] = [Sales].[SalesOrderHeader].[TerritoryID] AND ([Sales].[SalesOrderHeader].[Status] = 5) -- Shipped AND [Sales].[SalesOrderHeader].[OrderDate] BETWEEN @StartDate AND @EndDate) WHERE [Sales].[SalesTerritory].[TerritoryID] IN (SELECT DISTINCT inserted.[TerritoryID] FROM inserted WHERE inserted.[OrderDate] BETWEEN @StartDate AND @EndDate); 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" | Data Type | Type | [[dbo.AccountNumber_(type)|dbo.AccountNumber]] | | |- valign="top" | Data Type | Type | [[dbo.Flag_(type)|dbo.Flag]] | | |- valign="top" | Data Type | Type | [[dbo.OrderNumber_(type)|dbo.OrderNumber]] | | |- valign="top" | Schema | Schema | [[Sales_(schema)|Sales]] | | |- valign="top" | Select | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- valign="top" | Update | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- valign="top" | Update | Table | [[Sales.SalesPerson_(table)|Sales.SalesPerson]] | Trigger | uSalesOrderHeader |- valign="top" | Update | Table | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]] | Trigger | uSalesOrderHeader |- valign="top" | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | uSalesOrderHeader |- valign="top" | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | uSalesOrderHeader |- valign="top" | Execute | Function | [[dbo.ufnGetAccountingEndDate_(function)|dbo.ufnGetAccountingEndDate]] | Trigger | uSalesOrderHeader |- valign="top" | Execute | Function | [[dbo.ufnGetAccountingStartDate_(function)|dbo.ufnGetAccountingStartDate]] | Trigger | uSalesOrderHeader |} === 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 | View | [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]] | | |- valign="top" | Select | Table | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | Trigger | iduSalesOrderDetail |- valign="top" | Update | Table | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | Trigger | iduSalesOrderDetail |- valign="top" | Select | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- valign="top" | Update | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |} == automatically generated == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''Table''' | Sales.SalesOrderHeader |- valign="top" | '''Description''' | General sales order information. |- |- |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Column''' | '''Data Type''' | '''Nullable''' | '''Default''' | '''Description / PK / Index''' |- | SalesOrderID | int | not null | | Primary key.<br />PK_SalesOrderHeader_SalesOrderID |- | RevisionNumber | tinyint | not null | (0) | Incremental number to track changes to the sales order over time.<br /> |- | OrderDate | datetime | not null | (GETDATE()) | Dates the sales order was created.<br /> |- | DueDate | datetime | not null | | Date the order is due to the customer.<br /> |- | ShipDate | datetime | null | | Date the order was shipped to the customer.<br /> |- | Status | tinyint | not null | (1) | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled<br /> |- | OnlineOrderFlag | Flag | not null | (1) | 0 = Order placed by sales person. 1 = Order placed online by customer.<br /> |- | SalesOrderNumber | | | | Unique sales order identification number.<br />AK_SalesOrderHeader_SalesOrderNumber |- | PurchaseOrderNumber | OrderNumber | null | | Customer purchase order number reference. <br /> |- | AccountNumber | AccountNumber | null | | Financial accounting number reference.<br /> |- | CustomerID | int | not null | | Customer identification number. Foreign key to Customer.CustomerID.<br />IX_SalesOrderHeader_CustomerID |- | ContactID | int | not null | | Customer contact identification number. Foreign key to Contact.ContactID.<br /> |- | SalesPersonID | int | null | | Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.<br />IX_SalesOrderHeader_SalesPersonID |- | TerritoryID | int | null | | Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.<br /> |- | BillToAddressID | int | not null | | Customer billing address. Foreign key to Address.AddressID.<br /> |- | ShipToAddressID | int | not null | | Customer shipping address. Foreign key to Address.AddressID.<br /> |- | ShipMethodID | int | not null | | Shipping method. Foreign key to ShipMethod.ShipMethodID.<br /> |- | CreditCardID | int | null | | Credit card identification number. Foreign key to CreditCard.CreditCardID.<br /> |- | CreditCardApprovalCode | varchar(15) | null | | Approval code provided by the credit card company.<br /> |- | CurrencyRateID | int | null | | Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.<br /> |- | SubTotal | money | not null | (0.00) | Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.<br /> |- | TaxAmt | money | not null | (0.00) | Tax amount.<br /> |- | Freight | money | not null | (0.00) | Shipping cost.<br /> |- | TotalDue | | | | Total due from customer. Computed as Subtotal + TaxAmt + Freight.<br /> |- | Comment | nvarchar(128) | null | | Sales representative comments.<br /> |- | rowguid | uniqueidentifier | not null | (NEWID()) | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.<br />AK_SalesOrderHeader_rowguid |- | ModifiedDate | datetime | not null | (GETDATE()) | Date and time the record was last updated.<br /> |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Relation''' | '''Column''' | '''Referenced Column''' |- | [[Person.Address_(table)|Person.Address]] | BillToAddressID | AddressID |- | [[Person.Address_(table)|Person.Address]] | ShipToAddressID | AddressID |- | [[Person.Contact_(table)|Person.Contact]] | ContactID | ContactID |- | [[Sales.CreditCard_(table)|Sales.CreditCard]] | CreditCardID | CreditCardID |- | [[Sales.CurrencyRate_(table)|Sales.CurrencyRate]] | CurrencyRateID | CurrencyRateID |- | [[Sales.Customer_(table)|Sales.Customer]] | CustomerID | CustomerID |- | [[Sales.SalesPerson_(table)|Sales.SalesPerson]] | SalesPersonID | SalesPersonID |- | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]] | TerritoryID | TerritoryID |- | [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]] | ShipMethodID | ShipMethodID |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Detail Table''' | '''Column''' | '''Referencing Column''' |- | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | SalesOrderID | SalesOrderID |- | [[Sales.SalesOrderHeaderSalesReason_(table)|Sales.SalesOrderHeaderSalesReason]] | SalesOrderID | SalesOrderID |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Triggers''' | '''Type''' |- | uSalesOrderHeader | ON UPDATE |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Dependency Type''' | '''Object Type''' | '''Referenced Object''' | '''Child Type''' | '''Child Object''' |- | Data Type | Type | [[dbo.AccountNumber_(type)|dbo.AccountNumber]] |- | Data Type | Type | [[dbo.Flag_(type)|dbo.Flag]] |- | Data Type | Type | [[dbo.OrderNumber_(type)|dbo.OrderNumber]] |- | Schema | Schema | [[Sales_(schema)|Sales]] |- | Select | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- | Update | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- | Update | Table | [[Sales.SalesPerson_(table)|Sales.SalesPerson]] | Trigger | uSalesOrderHeader |- | Update | Table | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]] | Trigger | uSalesOrderHeader |- | Execute | Procedure | [[dbo.uspLogError_(procedure)|dbo.uspLogError]] | Trigger | uSalesOrderHeader |- | Execute | Procedure | [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]] | Trigger | uSalesOrderHeader |- | Execute | Function | [[dbo.ufnGetAccountingEndDate_(function)|dbo.ufnGetAccountingEndDate]] | Trigger | uSalesOrderHeader |- | Execute | Function | [[dbo.ufnGetAccountingStartDate_(function)|dbo.ufnGetAccountingStartDate]] | Trigger | uSalesOrderHeader |} {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- style="background:silver" | '''Reference Type''' | '''Object Type''' | '''Referencing Object''' | '''Child Type''' | '''Child Object''' |- | Select | View | [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]] |- | Select | Table | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | Trigger | iduSalesOrderDetail |- | Update | Table | [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]] | Trigger | iduSalesOrderDetail |- | Select | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |- | Update | Table | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]] | Trigger | uSalesOrderHeader |}
ScrewTurn Wiki version 2.0.36. Some of the icons created by
FamFamFam
.