ScrewTurn Wiki

Edit

wikibot

TableSales.SalesOrderHeader
DescriptionGeneral sales order information.

Edit

Columns

ColumnData TypeNullableDefaultDescription
SalesOrderIDintnot null Primary key.
RevisionNumbertinyintnot null((0))Incremental number to track changes to the sales order over time.
OrderDatedatetimenot null(getdate())Dates the sales order was created.
DueDatedatetimenot null Date the order is due to the customer.
ShipDatedatetimenull Date the order was shipped to the customer.
Statustinyintnot null((1))Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlagdbo.Flagnot null((1))0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber   Unique sales order identification number.
PurchaseOrderNumberdbo.OrderNumbernull Customer purchase order number reference.
AccountNumberdbo.AccountNumbernull Financial accounting number reference.
CustomerIDintnot null Customer identification number. Foreign key to Customer.CustomerID.
ContactIDintnot null Customer contact identification number. Foreign key to Contact.ContactID.
SalesPersonIDintnull Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
TerritoryIDintnull Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressIDintnot null Customer billing address. Foreign key to Address.AddressID.
ShipToAddressIDintnot null Customer shipping address. Foreign key to Address.AddressID.
ShipMethodIDintnot null Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardIDintnull Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCodevarchar(15)null Approval code provided by the credit card company.
CurrencyRateIDintnull Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotalmoneynot null((0.00))Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmtmoneynot null((0.00))Tax amount.
Freightmoneynot null((0.00))Shipping cost.
TotalDue   Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Commentnvarchar(128)null Sales representative comments.
rowguiduniqueidentifiernot null(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Edit

Primary Key

Primary KeyColumns
PK_SalesOrderHeader_SalesOrderIDSalesOrderID

Edit

Indexes

IndexTypeColumns
AK_SalesOrderHeader_rowguidUniquerowguid
AK_SalesOrderHeader_SalesOrderNumberUniqueSalesOrderNumber
IX_SalesOrderHeader_CustomerID CustomerID
IX_SalesOrderHeader_SalesPersonID SalesPersonID

Edit

Check Constraints

Check ConstraintExpressionDescription
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)

Edit

Foreign Keys

RelationColumnReferenced Column
Person.AddressBillToAddressIDAddressID
Person.AddressShipToAddressIDAddressID
Person.ContactContactIDContactID
Sales.CreditCardCreditCardIDCreditCardID
Sales.CurrencyRateCurrencyRateIDCurrencyRateID
Sales.CustomerCustomerIDCustomerID
Sales.SalesPersonSalesPersonIDSalesPersonID
Sales.SalesTerritoryTerritoryIDTerritoryID
Purchasing.ShipMethodShipMethodIDShipMethodID

Edit

Detail Tables

Detail TableColumnReferencing Column
Sales.SalesOrderDetailSalesOrderIDSalesOrderID
Sales.SalesOrderHeaderSalesReasonSalesOrderIDSalesOrderID

Edit

Triggers

TriggerType
uSalesOrderHeaderON UPDATE

Edit

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;

Edit

References

Dependency TypeObject TypeReferenced ObjectChild TypeChild Object
Data TypeTypedbo.AccountNumber  
Data TypeTypedbo.Flag  
Data TypeTypedbo.OrderNumber  
SchemaSchemaSales  
SelectTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesPersonTriggeruSalesOrderHeader
UpdateTableSales.SalesTerritoryTriggeruSalesOrderHeader
ExecuteProceduredbo.uspLogErrorTriggeruSalesOrderHeader
ExecuteProceduredbo.uspPrintErrorTriggeruSalesOrderHeader
ExecuteFunctiondbo.ufnGetAccountingEndDateTriggeruSalesOrderHeader
ExecuteFunctiondbo.ufnGetAccountingStartDateTriggeruSalesOrderHeader

Edit

Dependencies

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectViewSales.vSalesPersonSalesByFiscalYears  
SelectTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
UpdateTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
SelectTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesOrderHeaderTriggeruSalesOrderHeader

Edit

automatically generated

TableSales.SalesOrderHeader
DescriptionGeneral sales order information.

ColumnData TypeNullableDefaultDescription / PK / Index
SalesOrderIDintnot null Primary key.
PK_SalesOrderHeader_SalesOrderID
RevisionNumbertinyintnot null(0)Incremental number to track changes to the sales order over time.
OrderDatedatetimenot null(GETDATE())Dates the sales order was created.
DueDatedatetimenot null Date the order is due to the customer.
ShipDatedatetimenull Date the order was shipped to the customer.
Statustinyintnot null(1)Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlagFlagnot null(1)0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber   Unique sales order identification number.
AK_SalesOrderHeader_SalesOrderNumber
PurchaseOrderNumberOrderNumbernull Customer purchase order number reference.
AccountNumberAccountNumbernull Financial accounting number reference.
CustomerIDintnot null Customer identification number. Foreign key to Customer.CustomerID.
IX_SalesOrderHeader_CustomerID
ContactIDintnot null Customer contact identification number. Foreign key to Contact.ContactID.
SalesPersonIDintnull Sales person who created the sales order. Foreign key to SalesPerson.SalePersonID.
IX_SalesOrderHeader_SalesPersonID
TerritoryIDintnull Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID.
BillToAddressIDintnot null Customer billing address. Foreign key to Address.AddressID.
ShipToAddressIDintnot null Customer shipping address. Foreign key to Address.AddressID.
ShipMethodIDintnot null Shipping method. Foreign key to ShipMethod.ShipMethodID.
CreditCardIDintnull Credit card identification number. Foreign key to CreditCard.CreditCardID.
CreditCardApprovalCodevarchar(15)null Approval code provided by the credit card company.
CurrencyRateIDintnull Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID.
SubTotalmoneynot null(0.00)Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmtmoneynot null(0.00)Tax amount.
Freightmoneynot null(0.00)Shipping cost.
TotalDue   Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Commentnvarchar(128)null Sales representative comments.
rowguiduniqueidentifiernot null(NEWID())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
AK_SalesOrderHeader_rowguid
ModifiedDatedatetimenot null(GETDATE())Date and time the record was last updated.



RelationColumnReferenced Column
Person.AddressBillToAddressIDAddressID
Person.AddressShipToAddressIDAddressID
Person.ContactContactIDContactID
Sales.CreditCardCreditCardIDCreditCardID
Sales.CurrencyRateCurrencyRateIDCurrencyRateID
Sales.CustomerCustomerIDCustomerID
Sales.SalesPersonSalesPersonIDSalesPersonID
Sales.SalesTerritoryTerritoryIDTerritoryID
Purchasing.ShipMethodShipMethodIDShipMethodID

Detail TableColumnReferencing Column
Sales.SalesOrderDetailSalesOrderIDSalesOrderID
Sales.SalesOrderHeaderSalesReasonSalesOrderIDSalesOrderID

TriggersType
uSalesOrderHeaderON UPDATE

Dependency TypeObject TypeReferenced ObjectChild TypeChild Object
Data TypeTypedbo.AccountNumber
Data TypeTypedbo.Flag
Data TypeTypedbo.OrderNumber
SchemaSchemaSales
SelectTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesPersonTriggeruSalesOrderHeader
UpdateTableSales.SalesTerritoryTriggeruSalesOrderHeader
ExecuteProceduredbo.uspLogErrorTriggeruSalesOrderHeader
ExecuteProceduredbo.uspPrintErrorTriggeruSalesOrderHeader
ExecuteFunctiondbo.ufnGetAccountingEndDateTriggeruSalesOrderHeader
ExecuteFunctiondbo.ufnGetAccountingStartDateTriggeruSalesOrderHeader

Reference TypeObject TypeReferencing ObjectChild TypeChild Object
SelectViewSales.vSalesPersonSalesByFiscalYears
SelectTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
UpdateTableSales.SalesOrderDetailTriggeriduSalesOrderDetail
SelectTableSales.SalesOrderHeaderTriggeruSalesOrderHeader
UpdateTableSales.SalesOrderHeaderTriggeruSalesOrderHeader

ScrewTurn Wiki version 2.0.36. Some of the icons created by FamFamFam.