Sales SalesOrderHeader (table)

Modified on 2011/08/27 11:19 by wikibot Categorized as Uncategorized
== 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
| &nbsp;
| 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
| &nbsp;
| Date the order is due to the customer.
|- valign="top"
| ShipDate
| datetime
| null
| &nbsp;
| 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
| &nbsp;
| &nbsp;
| &nbsp;
| Unique sales order identification number.
|- valign="top"
| PurchaseOrderNumber
| dbo.OrderNumber
| null
| &nbsp;
| Customer purchase order number reference. 
|- valign="top"
| AccountNumber
| dbo.AccountNumber
| null
| &nbsp;
| Financial accounting number reference.
|- valign="top"
| CustomerID
| int
| not null
| &nbsp;
| Customer identification number. Foreign key to Customer.CustomerID.
|- valign="top"
| ContactID
| int
| not null
| &nbsp;
| Customer contact identification number. Foreign key to Contact.ContactID.
|- valign="top"
| SalesPersonID
| int
| null
| &nbsp;
| Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
|- valign="top"
| TerritoryID
| int
| null
| &nbsp;
| Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
|- valign="top"
| BillToAddressID
| int
| not null
| &nbsp;
| Customer billing address. Foreign key to Address.AddressID.
|- valign="top"
| ShipToAddressID
| int
| not null
| &nbsp;
| Customer shipping address. Foreign key to Address.AddressID.
|- valign="top"
| ShipMethodID
| int
| not null
| &nbsp;
| Shipping method. Foreign key to ShipMethod.ShipMethodID.
|- valign="top"
| CreditCardID
| int
| null
| &nbsp;
| Credit card identification number. Foreign key to CreditCard.CreditCardID.
|- valign="top"
| CreditCardApprovalCode
| varchar(15)
| null
| &nbsp;
| Approval code provided by the credit card company.
|- valign="top"
| CurrencyRateID
| int
| null
| &nbsp;
| 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
| &nbsp;
| &nbsp;
| &nbsp;
| Total due from customer. Computed as Subtotal + TaxAmt + Freight.
|- valign="top"
| Comment
| nvarchar(128)
| null
| &nbsp;
| 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
| &nbsp;
| CustomerID
|- valign="top"
| IX_SalesOrderHeader_SalesPersonID
| &nbsp;
| 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 ====
{{{{<nowiki>
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;
</nowiki>}}}}

=== 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]]

| &nbsp;
| &nbsp;
|- valign="top"
| Data Type
| Type
| [[dbo Flag (type)|dbo.Flag]]

| &nbsp;
| &nbsp;
|- valign="top"
| Data Type
| Type
| [[dbo OrderNumber (type)|dbo.OrderNumber]]

| &nbsp;
| &nbsp;
|- valign="top"
| Schema
| Schema
| [[Sales (schema)|Sales]]

| &nbsp;
| &nbsp;
|- 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]]

| &nbsp;
| &nbsp;
|- 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
|}
devio dbscript ScrewTurn Wiki

Navigation¶

Sales SalesOrderHeader (table)
