Page History: Sales SalesOrderHeader (table)
Compare Page Revisions
Page Revision: 2010/02/11 09:07
wikibot
| Table | Sales.SalesOrderHeader |
| Description | General sales order information. |
Columns ¶
| Column | Data Type | Nullable | Default | Description |
| SalesOrderID | int | not null | | Primary key. |
| RevisionNumber | tinyint | not null | ((0)) | Incremental number to track changes to the sales order over time. |
| OrderDate | datetime | not null | (getdate()) | Dates the sales order was created. |
| DueDate | datetime | not null | | Date the order is due to the customer. |
| ShipDate | datetime | null | | Date the order was shipped to the customer. |
| Status | tinyint | not null | ((1)) | Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled |
| OnlineOrderFlag | dbo.Flag | not null | ((1)) | 0 = Order placed by sales person. 1 = Order placed online by customer. |
| SalesOrderNumber | | | | Unique sales order identification number. |
| PurchaseOrderNumber | dbo.OrderNumber | null | | Customer purchase order number reference. |
| AccountNumber | dbo.AccountNumber | null | | Financial accounting number reference. |
| CustomerID | int | not null | | Customer identification number. Foreign key to Customer.CustomerID. |
| ContactID | int | not null | | Customer contact identification number. Foreign key to Contact.ContactID. |
| SalesPersonID | int | null | | Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID. |
| TerritoryID | int | null | | Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID. |
| BillToAddressID | int | not null | | Customer billing address. Foreign key to Address.AddressID. |
| ShipToAddressID | int | not null | | Customer shipping address. Foreign key to Address.AddressID. |
| ShipMethodID | int | not null | | Shipping method. Foreign key to ShipMethod.ShipMethodID. |
| CreditCardID | int | null | | Credit card identification number. Foreign key to CreditCard.CreditCardID. |
| CreditCardApprovalCode | varchar(15) | null | | Approval code provided by the credit card company. |
| CurrencyRateID | int | null | | Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID. |
| SubTotal | money | not null | ((0.00)) | Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID. |
| TaxAmt | money | not null | ((0.00)) | Tax amount. |
| Freight | money | not null | ((0.00)) | Shipping cost. |
| TotalDue | | | | Total due from customer. Computed as Subtotal + TaxAmt + Freight. |
| Comment | nvarchar(128) | null | | Sales representative comments. |
| rowguid | uniqueidentifier | not null | (newid()) | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
| ModifiedDate | datetime | not null | (getdate()) | Date and time the record was last updated. |
Primary Key
| Primary Key | Columns |
| PK_SalesOrderHeader_SalesOrderID | SalesOrderID |
Indexes
| Index | Type | Columns |
| AK_SalesOrderHeader_rowguid | Unique | rowguid |
| AK_SalesOrderHeader_SalesOrderNumber | Unique | SalesOrderNumber |
| IX_SalesOrderHeader_CustomerID | | CustomerID |
| IX_SalesOrderHeader_SalesPersonID | | SalesPersonID |
Check Constraints
Foreign Keys
Detail Tables
Triggers
| Triggers | Type |
| uSalesOrderHeader | ON UPDATE |
Trigger uSalesOrderHeader
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;
References
Dependencies