Welcome Guest, you are in: <root> • Login

Page History: Sales SalesOrderHeader (table)

Compare Page Revisions

4 3 2 1 0 Current 4 3 2 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: 2010/06/24 01:10

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 ¶

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

Foreign Keys ¶

Relation	Column	Referenced Column
Person.Address	BillToAddressID	AddressID
Person.Address	ShipToAddressID	AddressID
Person.Contact	ContactID	ContactID
Sales.CreditCard	CreditCardID	CreditCardID
Sales.CurrencyRate	CurrencyRateID	CurrencyRateID
Sales.Customer	CustomerID	CustomerID
Sales.SalesPerson	SalesPersonID	SalesPersonID
Sales.SalesTerritory	TerritoryID	TerritoryID
Purchasing.ShipMethod	ShipMethodID	ShipMethodID

Detail Tables ¶

Detail Table	Column	Referencing Column
Sales.SalesOrderDetail	SalesOrderID	SalesOrderID
Sales.SalesOrderHeaderSalesReason	SalesOrderID	SalesOrderID

Triggers ¶

Trigger	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 ¶

Dependency Type	Object Type	Referenced Object	Child Type	Child Object
Data Type	Type	dbo.AccountNumber
Data Type	Type	dbo.Flag
Data Type	Type	dbo.OrderNumber
Schema	Schema	Sales
Select	Table	Sales.SalesOrderHeader	Trigger	uSalesOrderHeader
Update	Table	Sales.SalesOrderHeader	Trigger	uSalesOrderHeader
Update	Table	Sales.SalesPerson	Trigger	uSalesOrderHeader
Update	Table	Sales.SalesTerritory	Trigger	uSalesOrderHeader
Execute	Procedure	dbo.uspLogError	Trigger	uSalesOrderHeader
Execute	Procedure	dbo.uspPrintError	Trigger	uSalesOrderHeader
Execute	Function	dbo.ufnGetAccountingEndDate	Trigger	uSalesOrderHeader
Execute	Function	dbo.ufnGetAccountingStartDate	Trigger	uSalesOrderHeader

Dependencies ¶

Reference Type	Object Type	Referencing Object	Child Type	Child Object
Select	View	Sales.vSalesPersonSalesByFiscalYears
Select	Table	Sales.SalesOrderDetail	Trigger	iduSalesOrderDetail
Update	Table	Sales.SalesOrderDetail	Trigger	iduSalesOrderDetail
Select	Table	Sales.SalesOrderHeader	Trigger	uSalesOrderHeader
Update	Table	Sales.SalesOrderHeader	Trigger	uSalesOrderHeader