Sales.SalesOrderHeader Table

In This Topic

Description

General sales order information.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	31465
Data Space Used	5,480.00 KB
Index Space Used	2,728.00 KB

Columns

Column Name	Description	Datatype	Length	Default	Formula
SalesOrderID	Primary key.	Integer	4
RevisionNumber	Incremental number to track changes to the sales order over time.	UnsignedTinyInt	1	((0))
OrderDate	Dates the sales order was created.	DBTimeStamp	4	(getdate())
DueDate	Date the order is due to the customer.	DBTimeStamp	4
ShipDate	Date the order was shipped to the customer.	DBTimeStamp	4
Status	Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled	UnsignedTinyInt	1	((1))
OnlineOrderFlag	0 = Order placed by sales person. 1 = Order placed online by customer.	dbo.Flag	1	((1))
SalesOrderNumber	Unique sales order identification number.	VarWChar	25		(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'* ERROR *'))
PurchaseOrderNumber	Customer purchase order number reference.	dbo.OrderNumber	25
AccountNumber	Financial accounting number reference.	dbo.AccountNumber	15
CustomerID	Customer identification number. Foreign key to Customer.BusinessEntityID.	Integer	4
SalesPersonID	Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID.	Integer	4
TerritoryID	Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.	Integer	4
BillToAddressID	Customer billing address. Foreign key to Address.AddressID.	Integer	4
ShipToAddressID	Customer shipping address. Foreign key to Address.AddressID.	Integer	4
ShipMethodID	Shipping method. Foreign key to ShipMethod.ShipMethodID.	Integer	4
CreditCardID	Credit card identification number. Foreign key to CreditCard.CreditCardID.	Integer	4
CreditCardApprovalCode	Approval code provided by the credit card company.	VarChar	15
CurrencyRateID	Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.	Integer	4
SubTotal	Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.	Currency	8	((0.00))
TaxAmt	Tax amount.	Currency	8	((0.00))
Freight	Shipping cost.	Currency	8	((0.00))
TotalDue	Total due from customer. Computed as Subtotal + TaxAmt + Freight.	Currency	8		(isnull(([SubTotal]+[TaxAmt])+[Freight],(0)))
Comment	Sales representative comments.	VarWChar	128
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_SalesOrderHeader_rowguid	Unique nonclustered index. Used to support replication samples.
AK_SalesOrderHeader_SalesOrderNumber	Unique nonclustered index.
IX_SalesOrderHeader_CustomerID	Nonclustered index.
IX_SalesOrderHeader_SalesPersonID	Nonclustered index.
PK_SalesOrderHeader_SalesOrderID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_SalesOrderHeader_DueDate	Check constraint [DueDate] >= [OrderDate]	([DueDate]>=[OrderDate])
CK_SalesOrderHeader_Freight	Check constraint [Freight] >= (0.00)	([Freight]>=(0.00))
CK_SalesOrderHeader_ShipDate	Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL	([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)
CK_SalesOrderHeader_Status	Check constraint [Status] BETWEEN (0) AND (8)	([Status]>=(0) AND [Status]<=(8))
CK_SalesOrderHeader_SubTotal	Check constraint [SubTotal] >= (0.00)	([SubTotal]>=(0.00))
CK_SalesOrderHeader_TaxAmt	Check constraint [TaxAmt] >= (0.00)	([TaxAmt]>=(0.00))

Triggers

Trigger	Description
uSalesOrderHeader	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.

Relationships

Relationship	Description
FK_SalesOrderHeader_Address_BillToAddressID	Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_Address_ShipToAddressID	Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_CreditCard_CreditCardID	Foreign key constraint referencing CreditCard.CreditCardID.
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID	Foreign key constraint referencing CurrencyRate.CurrencyRateID.
FK_SalesOrderHeader_Customer_CustomerID	Foreign key constraint referencing Customer.CustomerID.
FK_SalesOrderHeader_SalesPerson_SalesPersonID	Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_SalesOrderHeader_SalesTerritory_TerritoryID	Foreign key constraint referencing SalesTerritory.TerritoryID.
FK_SalesOrderHeader_ShipMethod_ShipMethodID	Foreign key constraint referencing ShipMethod.ShipMethodID.

Objects that depend on Sales.SalesOrderHeader

Database Object	Object Type	Description	Dep Level
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	3
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	3
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	3
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	3

Objects that Sales.SalesOrderHeader depends on

Database Object	Object Type	Description	Dep Level
dbo.AccountNumber	User Defined Data Type		2
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	2
Sales.CreditCard	Table	Customer credit card information.	2
Sales.Currency	Table	Lookup table containing standard ISO currencies.	3
Sales.CurrencyRate	Table	Currency exchange rates.	2
Sales.Customer	Table	Current customer information. Also see the Person and Store tables.	2
HumanResources.Employee	Table	Employee information such as salary, department, and title.	3
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	3
dbo.Flag	User Defined Data Type		4
dbo.NameStyle	User Defined Data Type		5
dbo.OrderNumber	User Defined Data Type		2
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	4
Sales.SalesPerson	Table	Sales representative current information.	2
Sales.SalesTerritory	Table	Sales territory lookup table.	3
Purchasing.ShipMethod	Table	Shipping company lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	1
dbo.ufnGetAccountingEndDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the starting account date.	2
dbo.ufnGetAccountingStartDate	User Defined Function	Scalar function used in the uSalesOrderHeader trigger to set the ending account date.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	3
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	2
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SalesOrderHeader](
    [SalesOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NULL,
    [Status] [tinyint] NOT NULL,
    [OnlineOrderFlag] [dbo].[Flag] NOT NULL,
    [SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),
    [PurchaseOrderNumber] [dbo].[OrderNumber] NULL,
    [AccountNumber] [dbo].[AccountNumber] NULL,
    [CustomerID] [int] NOT NULL,
    [SalesPersonID] [int] NULL,
    [TerritoryID] [int] NULL,
    [BillToAddressID] [int] NOT NULL,
    [ShipToAddressID] [int] NOT NULL,
    [ShipMethodID] [int] NOT NULL,
    [CreditCardID] [int] NULL,
    [CreditCardApprovalCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CurrencyRateID] [int] NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))),
    [Comment] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED 
(
    [SalesOrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_RevisionNumber]  DEFAULT ((0)) FOR [RevisionNumber]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_Status]  DEFAULT ((1)) FOR [Status]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_OnlineOrderFlag]  DEFAULT ((1)) FOR [OnlineOrderFlag]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_SubTotal]  DEFAULT ((0.00)) FOR [SubTotal]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_TaxAmt]  DEFAULT ((0.00)) FOR [TaxAmt]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_Freight]  DEFAULT ((0.00)) FOR [Freight]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SalesOrderHeader] ADD  CONSTRAINT [DF_SalesOrderHeader_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID] FOREIGN KEY([BillToAddressID])
REFERENCES [Person].[Address] ([AddressID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_Address_BillToAddressID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID] FOREIGN KEY([ShipToAddressID])
REFERENCES [Person].[Address] ([AddressID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_Address_ShipToAddressID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_CreditCard_CreditCardID] FOREIGN KEY([CreditCardID])
REFERENCES [Sales].[CreditCard] ([CreditCardID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_CreditCard_CreditCardID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_CurrencyRate_CurrencyRateID] FOREIGN KEY([CurrencyRateID])
REFERENCES [Sales].[CurrencyRate] ([CurrencyRateID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_CurrencyRate_CurrencyRateID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] FOREIGN KEY([CustomerID])
REFERENCES [Sales].[Customer] ([CustomerID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_SalesPerson_SalesPersonID] FOREIGN KEY([SalesPersonID])
REFERENCES [Sales].[SalesPerson] ([BusinessEntityID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_SalesPerson_SalesPersonID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_SalesTerritory_TerritoryID] FOREIGN KEY([TerritoryID])
REFERENCES [Sales].[SalesTerritory] ([TerritoryID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_SalesTerritory_TerritoryID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_SalesOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY([ShipMethodID])
REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID])
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [FK_SalesOrderHeader_ShipMethod_ShipMethodID]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_DueDate] CHECK  (([DueDate]>=[OrderDate]))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_DueDate]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_Freight] CHECK  (([Freight]>=(0.00)))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_Freight]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_ShipDate] CHECK  (([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_ShipDate]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_Status] CHECK  (([Status]>=(0) AND [Status]<=(8)))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_Status]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_SubTotal] CHECK  (([SubTotal]>=(0.00)))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_SubTotal]
ALTER TABLE [Sales].[SalesOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_SalesOrderHeader_TaxAmt] CHECK  (([TaxAmt]>=(0.00)))
ALTER TABLE [Sales].[SalesOrderHeader] CHECK CONSTRAINT [CK_SalesOrderHeader_TaxAmt]

Related Objects

Sales Schema
AdventureWorks Database

Send comments on this topic.