Editing 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 ====
<pre>
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;
</pre>

=== 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
		
|- valign="top"
| SalesOrderID
| int
| not null
| 
| Primary key.PK_SalesOrderHeader_SalesOrderID
|- 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
| Flag
| not null
| (1)
| 0 = Order placed by sales person. 1 = Order placed online by customer.
|- valign="top"
| SalesOrderNumber
| 
| 
| 
| Unique sales order identification number.AK_SalesOrderHeader_SalesOrderNumber
|- valign="top"
| PurchaseOrderNumber
| OrderNumber
| null
| 
| Customer purchase order number reference. 
|- valign="top"
| AccountNumber
| AccountNumber
| null
| 
| Financial accounting number reference.
|- valign="top"
| CustomerID
| int
| not null
| 
| Customer identification number. Foreign key to Customer.CustomerID.IX_SalesOrderHeader_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.IX_SalesOrderHeader_SalesPersonID
|- 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.AK_SalesOrderHeader_rowguid
|- valign="top"
| ModifiedDate
| datetime
| not null
| (GETDATE())
| Date and time the record was last updated.
|}



{| 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
|}