Difference between revisions of "Sales.SalesOrderHeader (table)"

From dbscript Online Help
Jump to: navigation, search
 
(6 intermediate revisions by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
 
| '''Table
 
| '''Table
 
| Sales.SalesOrderHeader
 
| Sales.SalesOrderHeader
−
|-
+
|- valign="top"
 +
| '''Description
 +
| General sales order information.
 
|}
 
|}
  
−
 
+
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| SalesOrderID
 
| SalesOrderID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_SalesOrderHeader_SalesOrderID
+
| Primary key.
−
|
+
|- valign="top"
−
|-
 
 
| RevisionNumber
 
| RevisionNumber
 
| tinyint
 
| tinyint
 
| not null
 
| not null
 
| ((0))
 
| ((0))
−
|  
+
| Incremental number to track changes to the sales order over time.
−
|
+
|- valign="top"
−
|-
 
 
| OrderDate
 
| OrderDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
|  
+
| Dates the sales order was created.
−
|
+
|- valign="top"
−
|-
 
 
| DueDate
 
| DueDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Date the order is due to the customer.
−
|
+
|- valign="top"
−
|-
 
 
| ShipDate
 
| ShipDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Date the order was shipped to the customer.
−
|
+
|- valign="top"
−
|-
 
 
| Status
 
| Status
 
| tinyint
 
| tinyint
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
|  
+
| Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
−
|
+
|- valign="top"
−
|-
 
 
| OnlineOrderFlag
 
| OnlineOrderFlag
 
| dbo.Flag
 
| dbo.Flag
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
|  
+
| 0 = Order placed by sales person. 1 = Order placed online by customer.
−
|
+
|- valign="top"
−
|-
 
 
| SalesOrderNumber
 
| SalesOrderNumber
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Unique sales order identification number.
−
| AK_SalesOrderHeader_SalesOrderNumber
+
|- valign="top"
−
|-
 
 
| PurchaseOrderNumber
 
| PurchaseOrderNumber
 
| dbo.OrderNumber
 
| dbo.OrderNumber
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Customer purchase order number reference.
−
|
+
|- valign="top"
−
|-
 
 
| AccountNumber
 
| AccountNumber
 
| dbo.AccountNumber
 
| dbo.AccountNumber
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Financial accounting number reference.
−
|
+
|- valign="top"
−
|-
 
 
| CustomerID
 
| CustomerID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Customer identification number. Foreign key to Customer.CustomerID.
−
| IX_SalesOrderHeader_CustomerID
+
|- valign="top"
−
|-
 
 
| ContactID
 
| ContactID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Customer contact identification number. Foreign key to Contact.ContactID.
−
|
+
|- valign="top"
−
|-
 
 
| SalesPersonID
 
| SalesPersonID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
−
| IX_SalesOrderHeader_SalesPersonID
+
|- valign="top"
−
|-
 
 
| TerritoryID
 
| TerritoryID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
−
|
+
|- valign="top"
−
|-
 
 
| BillToAddressID
 
| BillToAddressID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Customer billing address. Foreign key to Address.AddressID.
−
|
+
|- valign="top"
−
|-
 
 
| ShipToAddressID
 
| ShipToAddressID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Customer shipping address. Foreign key to Address.AddressID.
−
|
+
|- valign="top"
−
|-
 
 
| ShipMethodID
 
| ShipMethodID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Shipping method. Foreign key to ShipMethod.ShipMethodID.
−
|
+
|- valign="top"
−
|-
 
 
| CreditCardID
 
| CreditCardID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Credit card identification number. Foreign key to CreditCard.CreditCardID.
−
|
+
|- valign="top"
−
|-
 
 
| CreditCardApprovalCode
 
| CreditCardApprovalCode
 
| varchar(15)
 
| varchar(15)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Approval code provided by the credit card company.
−
|
+
|- valign="top"
−
|-
 
 
| CurrencyRateID
 
| CurrencyRateID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
−
|
+
|- valign="top"
−
|-
 
 
| SubTotal
 
| SubTotal
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
−
|
+
|- valign="top"
−
|-
 
 
| TaxAmt
 
| TaxAmt
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Tax amount.
−
|
+
|- valign="top"
−
|-
 
 
| Freight
 
| Freight
 
| money
 
| money
 
| not null
 
| not null
 
| ((0.00))
 
| ((0.00))
−
|  
+
| Shipping cost.
−
|
+
|- valign="top"
−
|-
 
 
| TotalDue
 
| TotalDue
 
|  
 
|  
 
|  
 
|  
 
|  
 
|  
−
|  
+
| Total due from customer. Computed as Subtotal + TaxAmt + Freight.
−
|
+
|- valign="top"
−
|-
 
 
| Comment
 
| Comment
 
| nvarchar(128)
 
| nvarchar(128)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Sales representative comments.
−
|
+
|- valign="top"
−
|-
 
 
| rowguid
 
| rowguid
 
| uniqueidentifier
 
| uniqueidentifier
 
| not null
 
| not null
 
| (newid())
 
| (newid())
−
|  
+
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
−
| AK_SalesOrderHeader_rowguid
+
|- valign="top"
−
|-
 
 
| ModifiedDate
 
| ModifiedDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (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"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 216: Line 257:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Person.Address_(table)|Person.Address]]
 
| [[Person.Address_(table)|Person.Address]]
 
| BillToAddressID
 
| BillToAddressID
 
| AddressID
 
| AddressID
−
|-
+
|- valign="top"
 
| [[Person.Address_(table)|Person.Address]]
 
| [[Person.Address_(table)|Person.Address]]
 
| ShipToAddressID
 
| ShipToAddressID
 
| AddressID
 
| AddressID
−
|-
+
|- valign="top"
 
| [[Person.Contact_(table)|Person.Contact]]
 
| [[Person.Contact_(table)|Person.Contact]]
 
| ContactID
 
| ContactID
 
| ContactID
 
| ContactID
−
|-
+
|- valign="top"
 
| [[Sales.CreditCard_(table)|Sales.CreditCard]]
 
| [[Sales.CreditCard_(table)|Sales.CreditCard]]
 
| CreditCardID
 
| CreditCardID
 
| CreditCardID
 
| CreditCardID
−
|-
+
|- valign="top"
 
| [[Sales.CurrencyRate_(table)|Sales.CurrencyRate]]
 
| [[Sales.CurrencyRate_(table)|Sales.CurrencyRate]]
 
| CurrencyRateID
 
| CurrencyRateID
 
| CurrencyRateID
 
| CurrencyRateID
−
|-
+
|- valign="top"
 
| [[Sales.Customer_(table)|Sales.Customer]]
 
| [[Sales.Customer_(table)|Sales.Customer]]
 
| CustomerID
 
| CustomerID
 
| CustomerID
 
| CustomerID
−
|-
+
|- valign="top"
 
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 
| SalesPersonID
 
| SalesPersonID
 
| SalesPersonID
 
| SalesPersonID
−
|-
+
|- valign="top"
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 
| TerritoryID
 
| TerritoryID
 
| TerritoryID
 
| TerritoryID
−
|-
+
|- valign="top"
 
| [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]]
 
| [[Purchasing.ShipMethod_(table)|Purchasing.ShipMethod]]
 
| ShipMethodID
 
| ShipMethodID
Line 255: Line 296:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 261: Line 302:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 
| SalesOrderID
 
| SalesOrderID
 
| SalesOrderID
 
| SalesOrderID
−
|-
+
|- valign="top"
 
| [[Sales.SalesOrderHeaderSalesReason_(table)|Sales.SalesOrderHeaderSalesReason]]
 
| [[Sales.SalesOrderHeaderSalesReason_(table)|Sales.SalesOrderHeaderSalesReason]]
 
| SalesOrderID
 
| SalesOrderID
Line 272: Line 313:
 
|}
 
|}
  
−
 
+
=== Triggers ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
−
| '''Triggers
+
| '''Trigger
 
| '''Type
 
| '''Type
−
+
 
−
|-
+
|- valign="top"
 
| uSalesOrderHeader
 
| uSalesOrderHeader
 
| ON UPDATE
 
| 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 ==
 
== automatically generated ==
  
Line 288: Line 546:
 
| '''Table
 
| '''Table
 
| Sales.SalesOrderHeader
 
| Sales.SalesOrderHeader
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| General sales order information.
 
| General sales order information.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 546: Line 802:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
+
| '''Child Type
−
+
| '''Child Object
 +
 
 
|-
 
|-
 +
| Data Type
 +
| Type
 
| [[dbo.AccountNumber_(type)|dbo.AccountNumber]]
 
| [[dbo.AccountNumber_(type)|dbo.AccountNumber]]
 +
 +
|
 +
|
 +
|-
 +
| Data Type
 
| Type
 
| Type
 +
| [[dbo.Flag_(type)|dbo.Flag]]
 +
 +
|
 +
|
 +
|-
 
| Data Type
 
| Data Type
−
|-
 
−
| [[dbo.Flag_(type)|dbo.Flag]]
 
 
| Type
 
| Type
−
| Data Type
+
| [[dbo.OrderNumber_(type)|dbo.OrderNumber]]
 +
 
 +
|
 +
|
 
|-
 
|-
−
| [[dbo.OrderNumber_(type)|dbo.OrderNumber]]
+
| Schema
−
| Type
+
| Schema
−
| Data Type
+
| [[Sales_(schema)|Sales]]
 +
 
 +
|  
 +
|  
 
|-
 
|-
 +
| Select
 +
| Table
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|-
 +
| Update
 
| Table
 
| Table
−
| Select
+
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 
 +
| Trigger
 +
| uSalesOrderHeader
 
|-
 
|-
−
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
+
| Update
 
| Table
 
| Table
 +
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|-
 
| Update
 
| Update
−
|-
 
−
| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 
 
| Table
 
| Table
−
| Update
 
−
|-
 
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 
| [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
−
| Table
+
 
−
| Update
+
| Trigger
 +
| uSalesOrderHeader
 
|-
 
|-
 +
| Execute
 +
| Procedure
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 
| [[dbo.uspLogError_(procedure)|dbo.uspLogError]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|-
 +
| Execute
 
| Procedure
 
| Procedure
 +
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 +
|-
 
| Execute
 
| Execute
 +
| Function
 +
| [[dbo.ufnGetAccountingEndDate_(function)|dbo.ufnGetAccountingEndDate]]
 +
 +
| Trigger
 +
| uSalesOrderHeader
 
|-
 
|-
−
| [[dbo.uspPrintError_(procedure)|dbo.uspPrintError]]
 
−
| Procedure
 
 
| Execute
 
| Execute
−
|-
 
−
| [[dbo.ufnGetAccountingEndDate_(function)|dbo.ufnGetAccountingEndDate]]
 
 
| Function
 
| Function
−
| Execute
 
−
|-
 
 
| [[dbo.ufnGetAccountingStartDate_(function)|dbo.ufnGetAccountingStartDate]]
 
| [[dbo.ufnGetAccountingStartDate_(function)|dbo.ufnGetAccountingStartDate]]
−
| Function
+
 
−
| Execute
+
| Trigger
 +
| uSalesOrderHeader
 
|}
 
|}
  
Line 599: Line 898:
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 
| '''Referencing Object
 
| '''Referencing Object
−
| '''Object Type
+
 
−
| '''Reference Type
+
| '''Child Type
−
+
| '''Child Object
 +
 
 
|-
 
|-
 +
| Select
 +
| View
 
| [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]]
 
| [[Sales.vSalesPersonSalesByFiscalYears_(view)|Sales.vSalesPersonSalesByFiscalYears]]
−
| View
+
 
 +
|  
 +
|
 +
|-
 
| Select
 
| Select
 +
| Table
 +
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 +
 +
| Trigger
 +
| iduSalesOrderDetail
 
|-
 
|-
 +
| Update
 +
| Table
 
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 +
 +
| Trigger
 +
| iduSalesOrderDetail
 +
|-
 +
| Select
 
| Table
 
| Table
−
| Select
+
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 +
 
 +
| Trigger
 +
| uSalesOrderHeader
 
|-
 
|-
−
| [[Sales.SalesOrderDetail_(table)|Sales.SalesOrderDetail]]
 
−
| Table
 
 
| Update
 
| Update
−
|-
 
−
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
 
| Table
 
| Table
−
| Select
 
−
|-
 
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
 
| [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
−
| Table
+
 
−
| Update
+
| Trigger
 +
| uSalesOrderHeader
 
|}
 
|}

Latest revision as of 23:17, 23 June 2010

wikibot[edit]

Table Sales.SalesOrderHeader
Description General sales order information.

Columns[edit]

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[edit]

Primary Key Columns
PK_SalesOrderHeader_SalesOrderID SalesOrderID

Indexes[edit]

Index Type Columns
AK_SalesOrderHeader_rowguid Unique rowguid
AK_SalesOrderHeader_SalesOrderNumber Unique SalesOrderNumber
IX_SalesOrderHeader_CustomerID CustomerID
IX_SalesOrderHeader_SalesPersonID SalesPersonID

Check Constraints[edit]

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[edit]

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[edit]

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

Triggers[edit]

Trigger Type
uSalesOrderHeader ON UPDATE

Trigger uSalesOrderHeader[edit]

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[edit]

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[edit]

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


automatically generated[edit]

Table Sales.SalesOrderHeader
Description General sales order information.


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


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 Table Column Referencing Column
Sales.SalesOrderDetail SalesOrderID SalesOrderID
Sales.SalesOrderHeaderSalesReason SalesOrderID SalesOrderID


Triggers Type
uSalesOrderHeader ON UPDATE


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


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
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.SalesOrderHeader_(table)&oldid=2669"