Sales.SalesOrderHeader

Object type: Table

General sales order information.

Columns

Name Data type Nullable Description

SalesOrderID int No Column has identity seed value of 1 with a step value of 1.
Primary key.

RevisionNumber tinyint No Default value: 0
Incremental number to track changes to the sales order over time.

OrderDate datetime No Default value: GETDATE()
Dates the sales order was created.

DueDate datetime No Date the order is due to the customer.

ShipDate datetime Yes Date the order was shipped to the customer.

Status tinyint No Default value: 1
Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled

OnlineOrderFlag Flag No Default value: 1
0 = Order placed by sales person. 1 = Order placed online by customer.

SalesOrderNumber ISNULL(N'SO' + CONVERT(nvarchar(23) , [SalesOrderID]) , N'*** ERROR ***') Yes Unique sales order identification number.

PurchaseOrderNumber OrderNumber Yes Customer purchase order number reference.

AccountNumber AccountNumber Yes Financial accounting number reference.

CustomerID int No Customer identification number. Foreign key to Customer.CustomerID.

ContactID int No Customer contact identification number. Foreign key to Contact.ContactID.

SalesPersonID int Yes Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.

TerritoryID int Yes Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.

BillToAddressID int No Customer billing address. Foreign key to Address.AddressID.

ShipToAddressID int No Customer shipping address. Foreign key to Address.AddressID.

ShipMethodID int No Shipping method. Foreign key to ShipMethod.ShipMethodID.

CreditCardID int Yes Credit card identification number. Foreign key to CreditCard.CreditCardID.

CreditCardApprovalCode varchar(15) Yes Approval code provided by the credit card company.

CurrencyRateID int Yes Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.

SubTotal money No Default value: 0.00
Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.

TaxAmt money No Default value: 0.00
Tax amount.

Freight money No Default value: 0.00
Shipping cost.

TotalDue ISNULL([SubTotal] + [TaxAmt] + [Freight], 0) Yes Total due from customer. Computed as Subtotal + TaxAmt + Freight.

Comment nvarchar(128) Yes Sales representative comments.

rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_SalesOrderHeader_RevisionNumber DEFAULT RevisionNumber Value: 0
Default constraint value of 0

DF_SalesOrderHeader_OrderDate DEFAULT OrderDate Value: GETDATE()
Default constraint value of GETDATE()

DF_SalesOrderHeader_Status DEFAULT Status Value: 1
Default constraint value of 1

DF_SalesOrderHeader_OnlineOrderFlag DEFAULT OnlineOrderFlag Value: 1
Default constraint value of 1 (TRUE)

DF_SalesOrderHeader_SubTotal DEFAULT SubTotal Value: 0.00
Default constraint value of 0.0

DF_SalesOrderHeader_TaxAmt DEFAULT TaxAmt Value: 0.00
Default constraint value of 0.0

DF_SalesOrderHeader_Freight DEFAULT Freight Value: 0.00
Default constraint value of 0.0

DF_SalesOrderHeader_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()

DF_SalesOrderHeader_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_SalesOrderHeader_Status CHECK N/A Value: ([Status] BETWEEN 0 AND 8)
Check constraint [Status] BETWEEN (0) AND (8)

CK_SalesOrderHeader_DueDate CHECK N/A Value: ([DueDate] >= [OrderDate])
Check constraint [DueDate] >= [OrderDate]

CK_SalesOrderHeader_ShipDate CHECK N/A Value: (([ShipDate] >= [OrderDate]) OR ([ShipDate] IS NULL) )
Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL

CK_SalesOrderHeader_SubTotal CHECK N/A Value: ([SubTotal] >= 0.00)
Check constraint [SubTotal]>=(0.00)

CK_SalesOrderHeader_TaxAmt CHECK N/A Value: ([TaxAmt] >= 0.00)
Check constraint [TaxAmt]>=(0.00)

CK_SalesOrderHeader_Freight CHECK N/A Value: ([Freight] >= 0.00)
Check constraint [Freight]>=(0.00)

PK_SalesOrderHeader_SalesOrderID PRIMARY KEY SalesOrderID Clustered index created by a primary key constraint.

FK_SalesOrderHeader_Address_BillToAddressID FOREIGN KEY BillToAddressID Reference table: Person.Address
Foreign key constraint referencing Address.AddressID.

FK_SalesOrderHeader_Address_ShipToAddressID FOREIGN KEY ShipToAddressID Reference table: Person.Address
Foreign key constraint referencing Address.AddressID.

FK_SalesOrderHeader_Contact_ContactID FOREIGN KEY ContactID Reference table: Person.Contact
Foreign key constraint referencing Contact.ContactID.

FK_SalesOrderHeader_CreditCard_CreditCardID FOREIGN KEY CreditCardID Reference table: Sales.CreditCard
Foreign key constraint referencing CreditCard.CreditCardID.

FK_SalesOrderHeader_CurrencyRate_CurrencyRateID FOREIGN KEY CurrencyRateID Reference table: Sales.CurrencyRate
Foreign key constraint referencing CurrencyRate.CurrencyRateID.

FK_SalesOrderHeader_Customer_CustomerID FOREIGN KEY CustomerID Reference table: Sales.Customer
Foreign key constraint referencing Customer.CustomerID.

FK_SalesOrderHeader_SalesPerson_SalesPersonID FOREIGN KEY SalesPersonID Reference table: Sales.SalesPerson
Foreign key constraint referencing SalesPerson.SalesPersonID.

FK_SalesOrderHeader_ShipMethod_ShipMethodID FOREIGN KEY ShipMethodID Reference table: Purchasing.ShipMethod
Foreign key constraint referencing ShipMethod.ShipMethodID.

FK_SalesOrderHeader_SalesTerritory_TerritoryID FOREIGN KEY TerritoryID Reference table: Sales.SalesTerritory
Foreign key constraint referencing SalesTerritory.TerritoryID.

Indices

Name Description

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.>

Triggers

Name Description

Sales.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.

Linked tables

Name Description

Sales.SalesOrderDetail Individual products associated with a specific sales order. See SalesOrderHeader.

Sales.SalesOrderHeaderSalesReason Cross-reference table mapping sales orders to sales reason codes.

Accessed by

Name Description

Company Sales Adventure Works sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.

Employee Sales Summary Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.

Product Line Sales Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.

Sales Order Detail Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.

Sales.iduSalesOrderDetail AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Sales.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.

Sales.vSalesPersonSalesByFiscalYears Uses PIVOT to return aggregated sales information for each sales representative.

Territory Sales Drilldown Adventure Works sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.

Modified by

Name Description

Sales.iduSalesOrderDetail AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.

Sales.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.

Applies to

Tables | User

Name	Data type	Nullable	Description
SalesOrderID	int	No	Column has identity seed value of 1 with a step value of 1. Primary key.
RevisionNumber	tinyint	No	Default value: 0 Incremental number to track changes to the sales order over time.
OrderDate	datetime	No	Default value: GETDATE() Dates the sales order was created.
DueDate	datetime	No	Date the order is due to the customer.
ShipDate	datetime	Yes	Date the order was shipped to the customer.
Status	tinyint	No	Default value: 1 Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlag	Flag	No	Default value: 1 0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber	ISNULL(N'SO' + CONVERT(nvarchar(23) , [SalesOrderID]) , N'* ERROR *')	Yes	Unique sales order identification number.
PurchaseOrderNumber	OrderNumber	Yes	Customer purchase order number reference.
AccountNumber	AccountNumber	Yes	Financial accounting number reference.
CustomerID	int	No	Customer identification number. Foreign key to Customer.CustomerID.
ContactID	int	No	Customer contact identification number. Foreign key to Contact.ContactID.
SalesPersonID	int	Yes	Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
TerritoryID	int	Yes	Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressID	int	No	Customer billing address. Foreign key to Address.AddressID.
ShipToAddressID	int	No	Customer shipping address. Foreign key to Address.AddressID.
ShipMethodID	int	No	Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardID	int	Yes	Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCode	varchar(15)	Yes	Approval code provided by the credit card company.
CurrencyRateID	int	Yes	Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotal	money	No	Default value: 0.00 Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmt	money	No	Default value: 0.00 Tax amount.
Freight	money	No	Default value: 0.00 Shipping cost.
TotalDue	ISNULL([SubTotal] + [TaxAmt] + [Freight], 0)	Yes	Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Comment	nvarchar(128)	Yes	Sales representative comments.
rowguid	uniqueidentifier	No	Default value: NEWID() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_SalesOrderHeader_RevisionNumber	DEFAULT	RevisionNumber	Value: 0 Default constraint value of 0
DF_SalesOrderHeader_OrderDate	DEFAULT	OrderDate	Value: GETDATE() Default constraint value of GETDATE()
DF_SalesOrderHeader_Status	DEFAULT	Status	Value: 1 Default constraint value of 1
DF_SalesOrderHeader_OnlineOrderFlag	DEFAULT	OnlineOrderFlag	Value: 1 Default constraint value of 1 (TRUE)
DF_SalesOrderHeader_SubTotal	DEFAULT	SubTotal	Value: 0.00 Default constraint value of 0.0
DF_SalesOrderHeader_TaxAmt	DEFAULT	TaxAmt	Value: 0.00 Default constraint value of 0.0
DF_SalesOrderHeader_Freight	DEFAULT	Freight	Value: 0.00 Default constraint value of 0.0
DF_SalesOrderHeader_rowguid	DEFAULT	rowguid	Value: NEWID() Default constraint value of NEWID()
DF_SalesOrderHeader_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_SalesOrderHeader_Status	CHECK	N/A	Value: ([Status] BETWEEN 0 AND 8) Check constraint [Status] BETWEEN (0) AND (8)
CK_SalesOrderHeader_DueDate	CHECK	N/A	Value: ([DueDate] >= [OrderDate]) Check constraint [DueDate] >= [OrderDate]
CK_SalesOrderHeader_ShipDate	CHECK	N/A	Value: (([ShipDate] >= [OrderDate]) OR ([ShipDate] IS NULL) ) Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_SalesOrderHeader_SubTotal	CHECK	N/A	Value: ([SubTotal] >= 0.00) Check constraint [SubTotal]>=(0.00)
CK_SalesOrderHeader_TaxAmt	CHECK	N/A	Value: ([TaxAmt] >= 0.00) Check constraint [TaxAmt]>=(0.00)
CK_SalesOrderHeader_Freight	CHECK	N/A	Value: ([Freight] >= 0.00) Check constraint [Freight]>=(0.00)
PK_SalesOrderHeader_SalesOrderID	PRIMARY KEY	SalesOrderID	Clustered index created by a primary key constraint.
FK_SalesOrderHeader_Address_BillToAddressID	FOREIGN KEY	BillToAddressID	Reference table: Person.Address Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_Address_ShipToAddressID	FOREIGN KEY	ShipToAddressID	Reference table: Person.Address Foreign key constraint referencing Address.AddressID.
FK_SalesOrderHeader_Contact_ContactID	FOREIGN KEY	ContactID	Reference table: Person.Contact Foreign key constraint referencing Contact.ContactID.
FK_SalesOrderHeader_CreditCard_CreditCardID	FOREIGN KEY	CreditCardID	Reference table: Sales.CreditCard Foreign key constraint referencing CreditCard.CreditCardID.
FK_SalesOrderHeader_CurrencyRate_CurrencyRateID	FOREIGN KEY	CurrencyRateID	Reference table: Sales.CurrencyRate Foreign key constraint referencing CurrencyRate.CurrencyRateID.
FK_SalesOrderHeader_Customer_CustomerID	FOREIGN KEY	CustomerID	Reference table: Sales.Customer Foreign key constraint referencing Customer.CustomerID.
FK_SalesOrderHeader_SalesPerson_SalesPersonID	FOREIGN KEY	SalesPersonID	Reference table: Sales.SalesPerson Foreign key constraint referencing SalesPerson.SalesPersonID.
FK_SalesOrderHeader_ShipMethod_ShipMethodID	FOREIGN KEY	ShipMethodID	Reference table: Purchasing.ShipMethod Foreign key constraint referencing ShipMethod.ShipMethodID.
FK_SalesOrderHeader_SalesTerritory_TerritoryID	FOREIGN KEY	TerritoryID	Reference table: Sales.SalesTerritory Foreign key constraint referencing SalesTerritory.TerritoryID.

Name	Description
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.>

Name	Description
Sales.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.

Name	Description
Sales.SalesOrderDetail	Individual products associated with a specific sales order. See SalesOrderHeader.
Sales.SalesOrderHeaderSalesReason	Cross-reference table mapping sales orders to sales reason codes.

Name	Description
Company Sales	Adventure Works sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.
Employee Sales Summary	Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.
Product Line Sales	Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.
Sales Order Detail	Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.
Sales.iduSalesOrderDetail	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.
Sales.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.
Sales.vSalesPersonSalesByFiscalYears	Uses PIVOT to return aggregated sales information for each sales representative.
Territory Sales Drilldown	Adventure Works sales by territory. This report drills down through salesperson and order number with drillthrough to individual orders. This report illustrates the use of a table data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of drillthrough links and conditional formatting.