[dbo].[Orders]

Property	Value
Collation	SQL_Latin1_General_CP1_CI_AS
Heap
Row Count (~)	43676
Created	4:57:08 PM Thursday, September 7, 2006
Last Modified	1:57:11 PM Wednesday, April 10, 2024

Name	Columns
STATS_Orders_PriKey_OrderNo	PriKey, OrderNo
STATS_Orders_PriKey_OrderSeg_OrderNo	OrderSeg, OrderNo, PriKey

Name	ANSI Nulls On	Quoted Identifier On	On
OrderAuditFieldsChanged			After Update
OrderAuditFieldsInitiallySet			After Insert
OrdersDateChangeNotification			After Update
OrdersTaskFieldChanged			After Update

Name	On Column	Constraint
CK_Orders_OrderSeg	OrderSeg	(ltrim(isnull([OrderSeg],'A'))<>'')

Name	Columns
FK_Orders_AccountProfile	AccountProfileFID->[dbo].[AccountProfiles].[AccountProfileID]
FK_ORDERS_ACCOUNTS	AcctPriKey->[dbo].[Accounts].[AccountPriKey]
FK_ORDERS_AUTHORITYTYPES	AuthPriKey->[dbo].[AuthorityTypes].[AuthPriKey]
FK_Orders_AutoClass	AutoClassFID->[dbo].[AutoClass].[AutoClassID]
FK_Orders_AutoMake	AutoMakeFID->[dbo].[AutoMake].[AutoMakeID]
FK_ORDERS_AGENT2	BookAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_BookingAgentDivision	BookingAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_ORDERS_BRANCH	BranchPriKey->[dbo].[Branch].[BranchPriKey]
FK_Orders_ClaimAlertBy	ClaimAlertBy->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_ContractMaster	CMPriKey->[dbo].[ContractMaster].[CMPriKey]
FK_Orders_CommType	Commodity->[dbo].[CommType].[PriKey]
FK_Orders_ContactPreference	ContactPreferenceFID->[dbo].[ContactPreference].[ContactPreferenceID]
FK_ORDERS_Sysuser	Coordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_Sysuser5	CreatedBy->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_AGENT1	DestAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_DestinationAgentDivision	DestinationAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_DestinationAgentMarket	DestinationMarketFID->[dbo].[Market].[MarketID]
FK_Orders_Division	DivisionFID->[dbo].[Division].[DivisionID]
FK_ORDERS_AGENT3	HaulAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_HaulAs	HaulAsFID->[dbo].[HaulAs].[HaulAsID]
FK_Orders_HaulingAgentDivision	HaulingAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_InvoiceRequirementType	InvoiceRequirementTypeFID->[dbo].[InvoiceRequirementType].[InvoiceRequirementTypeID]
FK_Orders_JobCostingRevenueType	JobCostingRevenueTypeFID->[dbo].[JobCostingRevenueType].[JobCostingRevenueTypeID]
FK_ORDERS_LeadType	LeadType->[dbo].[LeadType].[PriKey]
FK_ORDERS_Sysuser4	LogisticCoordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_MOVETYPE	MoveType->[dbo].[MoveType].[PriKey]
FK_ORDERS_Sysuser2	OACoordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_Sysuser3	OASurveyor->[dbo].[Sysuser].[SysUserID]
FK_Orders_OrderAuditInfo	OrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_ORDERS_AGENT	OrgAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_OriginAgentDivision	OriginAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_OriginAgentMarket	OriginMarketFID->[dbo].[Market].[MarketID]
FK_ORDERS_PackType	PackType->[dbo].[PackType].[PackTypeID]
FK_Orders_PayMethod	PayMethodFID->[dbo].[PayMethod].[PayMethodID]
FK_Orders_PayType	PayTypeFID->[dbo].[PayType].[PayTypeID]
FK_Orders_ProductCode	ProductCodeFID->[dbo].[ProductCode].[ProductCodeID]
FK_Orders_ProductType	ProductTypeFID->[dbo].[ProductType].[ProductTypeID]
FK_ORDERS_PROFILES	ProfileID->[dbo].[Profiles].[ProfileID]
FK_Orders_ReductionProfile	ReductionProfileFID->[dbo].[ReductionProfile].[ReductionProfileID]
FK_Orders_Sysuser6	RevenueClerk->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_ROLLOUTS	RolloutID->[dbo].[RollOuts].[RollOutID]
FK_ORDERS_RatePlans	RPPriKey->[dbo].[RatePlans].[RPPriKey]
FK_ORDERS_Sysuser1	SalesPerson->[dbo].[Sysuser].[SysUserID]
FK_Orders_ServiceType	ServiceTypeFID->[dbo].[ServiceType].[ServiceTypeID]
FK_Orders_ShipmentType	ShipmentTypeFID->[dbo].[ShipmentType].[ShipmentTypeID]
FK_Orders_StorageRequirement	StorageRequirementFID->[dbo].[StorageRequirement].[StorageRequirementID]
FK_Orders_Valuation	ValuationFID->[dbo].[Valuation].[ValuationID]
FK_Orders_ValuationPerPound	ValuationPerPoundFID->[dbo].[ValuationPerPound].[ValuationPerPoundID]
FK_Orders_VanLine	VanLineFID->[dbo].[VanLine].[VLPriKey]

Type	Action	Owning Principal
Grant	Delete	MssExec
Grant	Insert	MssExec
Grant	Select	MssExec
Grant	Update	MssExec

SQL Script

CREATE TABLE [dbo].[Orders]
(
[PriKey] [int] NOT NULL IDENTITY(1, 1),
[LastName] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderNo] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Coordinator] [int] NULL,
[SalesPerson] [int] NULL,
[FromCounty] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Email] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ToCounty] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MovingToCounty] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderStatus] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [int] NOT NULL,
[ApxMoveDate] [datetime] NULL,
[EstDate] [datetime] NULL,
[StartPack] [datetime] NULL,
[EndPack] [datetime] NULL,
[StartLoad] [datetime] NULL,
[EndLoad] [datetime] NULL,
[StartDeliv] [datetime] NULL,
[EndDeliv] [datetime] NULL,
[BookDate] [datetime] NULL,
[MoveType] [int] NULL,
[Commodity] [int] NULL,
[CubicFeet] [float] NULL,
[Weight] [int] NULL,
[Miles] [int] NULL,
[ActLineHaul] [money] NULL,
[LineHaul] [money] NULL,
[FirmPrice] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Automobile] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Orders_Automobile] DEFAULT ('F'),
[AutoModel] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoYear] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RelOrdNo] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SelfHaul] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Orders_SelfHaul] DEFAULT (''),
[SHaulAuth] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SHaulAutBy] [int] NULL,
[VanLineReg] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VanRegBy] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreditAuth] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EstAmt] [float] NULL,
[ActDelDate] [datetime] NULL,
[ReleaseAgt] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ReleaseDate] [datetime] NULL,
[AcctPriKey] [int] NULL,
[IsOrder] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsForOrd] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsLead] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrgAgent] [int] NULL,
[DestAgent] [int] NULL,
[VanNum] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VanRegDt] [datetime] NULL,
[OrgAgentName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgAgentContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrgAgentMemo] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestAgentName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestAgentContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestAgentMemo] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SelfHaulAuthorization] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountMemo] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EstimateNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PackType] [int] NULL,
[LinearFeet] [int] NULL,
[FlatFloor] [int] NULL,
[TruckLoad] [int] NULL,
[VanRequirement] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamRequired] [int] NULL,
[PurchaseOrderNo] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LogisticCoordinator] [int] NULL,
[ProfileID] [int] NULL,
[RolloutID] [int] NULL,
[Invno] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvDate] [datetime] NULL,
[ThirdPartyFreight] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamPay] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Orders_TeamPay] DEFAULT (0),
[PackTime] [datetime] NULL,
[LoadTime] [datetime] NULL,
[DeliveryTime] [datetime] NULL,
[PrintInvoice] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OACoordinator] [int] NULL,
[OASurveyor] [int] NULL,
[BookAgent] [int] NULL,
[BookAgentName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BookAgentContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BookAgentMemo] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PreSold] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceSuppNo] [int] NULL,
[PaperworkRecd] [datetime] NULL,
[CustomerNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActualCost] [float] NULL,
[Company] [varchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LeadType] [int] NULL,
[OrderSeg] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PSeg] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OSeg] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSeg] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SCNumPieces] [int] NULL,
[SCDeckingRequired] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SCNumPads] [int] NULL,
[SCNumStraps] [int] NULL,
[SCVanSize] [int] NULL,
[SCBlanket] [int] NULL,
[SCCartoned] [int] NULL,
[SCCrated] [int] NULL,
[SCBubbleWrapped] [int] NULL,
[SCSkidded] [int] NULL,
[HaulAgent] [int] NULL,
[HaulAgentName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HaulAgentContact] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HaulAgentMemo] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ActPackDate] [datetime] NULL,
[ActLoadDate] [datetime] NULL,
[Discount] [float] NULL,
[TariffContract] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Section] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ThirdPartyServices] [int] NULL,
[BillToContact] [varchar] (61) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToAddr] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToCity] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToZip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SSUserDefined8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CSUserDefined8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IntoStorage] [datetime] NULL,
[RPPriKey] [int] NULL,
[SentToQM] [int] NULL,
[QuickMoveID] [int] NULL,
[CMPriKey] [int] NULL,
[AuthPriKey] [int] NULL,
[BranchPriKey] [int] NOT NULL,
[RevenueClerk] [int] NULL,
[ValuationAmount] [money] NULL,
[AutoVIN] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoDescription] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoWeight] [int] NULL,
[AutoClassFID] [int] NULL,
[WarehouseFlag] [bit] NOT NULL CONSTRAINT [DF_Orders_WarehouseFlag] DEFAULT (0),
[ShipmentTypeFID] [int] NULL,
[ServiceTypeFID] [int] NULL,
[RegistrationAuthorization] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PreferredLoad] [datetime] NULL,
[PreferredPack] [datetime] NULL,
[PreferredDeliv] [datetime] NULL,
[OutsideSurveyor] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApplyPeakRateFlag] [bit] NOT NULL CONSTRAINT [DF_Orders_ApplyPeakRateFlag] DEFAULT (0),
[PreferredVanRequestFlag] [bit] NOT NULL CONSTRAINT [DF_Orders_PreferredVanRequestFlag] DEFAULT (0),
[StorageRequirementFID] [int] NULL,
[AutoWithShipmentFlag] [bit] NOT NULL CONSTRAINT [DF_Orders_AutoWithShipmentFlag] DEFAULT (0),
[AccountProfileFID] [int] NULL,
[DivisionFID] [int] NULL,
[OriginAgentDivisionFID] [int] NULL,
[DestinationAgentDivisionFID] [int] NULL,
[HaulingAgentDivisionFID] [int] NULL,
[BookingAgentDivisionFID] [int] NULL,
[ShipperDeductible] [money] NOT NULL CONSTRAINT [DF_Orders_ShipperDeductible] DEFAULT ((0)),
[AlertReceivedDate] [datetime] NULL,
[ClaimAlertBy] [int] NULL,
[CustomerAddressCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillToCountryName] [varchar] (61) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceRequirementTypeFID] [int] NOT NULL,
[PackEndTime] [datetime] NULL,
[LoadEndTime] [datetime] NULL,
[DeliveryEndTime] [datetime] NULL,
[VanLineFID] [int] NULL,
[PayTypeFID] [int] NULL,
[PayMethodFID] [int] NULL,
[ValuationFID] [int] NULL,
[ValuationPerPoundFID] [int] NULL,
[AutoMakeFID] [int] NULL,
[ProductTypeFID] [int] NULL,
[ProductCodeFID] [int] NULL,
[AutoLength] [decimal] (10, 2) NULL,
[AutoWidth] [decimal] (10, 2) NULL,
[AutoHeight] [decimal] (10, 2) NULL,
[ReductionProfileFID] [int] NULL,
[ETA] [datetime] NULL,
[ETATime] [datetime] NULL,
[AssignedOn] [datetime] NULL,
[AssignedOnTime] [datetime] NULL,
[BilledWeight] [int] NULL,
[EstimatedWeight] [int] NULL,
[HaulAsFID] [int] NULL,
[OriginShuttleMiles] [int] NULL,
[DestinationShuttleMiles] [int] NULL,
[AlternateWeight] [int] NULL,
[Archived] [bit] NOT NULL CONSTRAINT [DF_Orders_Archived] DEFAULT ((0)),
[ArchivedOn] [datetime] NULL,
[ArchivedOrderNo] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ArchivedEstimateNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginAgreedDiscount] [decimal] (12, 2) NULL,
[HaulingAgreedDiscount] [decimal] (12, 2) NULL,
[DestinationAgreedDiscount] [decimal] (12, 2) NULL,
[OriginAgreedDiscountLog] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HaulingAgreedDiscountLog] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DestinationAgreedDiscountLog] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GrossMargin] [decimal] (14, 4) NULL,
[ProfitOrLoss] [decimal] (12, 2) NULL,
[JobCostingCalculated] [bit] NOT NULL CONSTRAINT [DF_Orders_JobCostingCalculated] DEFAULT ((0)),
[JobCostingRevenueTypeFID] [int] NULL,
[JobCostingManualRevenue] [decimal] (12, 2) NULL,
[JobCostingRevenueAmount] [decimal] (12, 2) NOT NULL CONSTRAINT [DF_Orders_JobCostingRevenueAmount] DEFAULT ((0)),
[OrderPredatesJobCosting] [bit] NOT NULL CONSTRAINT [DF_Orders_OrderPredatesJobCosting] DEFAULT ((0)),
[SurveyStartTime] [datetime] NULL,
[SurveyEndTime] [datetime] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL,
[OpenOrderInModule] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Orders_OpenOrderInModule] DEFAULT ('Order Information'),
[OriginMarketFID] [int] NULL,
[DestinationMarketFID] [int] NULL,
[AutoColor] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AutoMileage] [int] NULL,
[AutoCubicFeet] [float] NULL,
[TenderedDate] [datetime] NULL,
[ContactPreferenceFID] [int] NULL,
[AccountLocationCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE trigger [dbo].[OrderAuditFieldsChanged] on [dbo].[Orders]
after update
as
 set nocount on

 -- When we clear the OrderAuditInfoFID field, no auditing is needed.
 if( update( OrderAuditInfoFID ) and not exists ( select OrderAuditInfoFID from inserted where OrderAuditInfoFID is not null ) )
 begin
 return
 end
 else
 begin
 declare @theAuditFields table
 (
 OrderFID int,
 FieldName varchar(128),
 Value varchar(256),
 customField varchar(256) null
 )

 insert into @theAuditFields
 (
 OrderFID,
 FieldName,
 [Value]
 )
 select inserted.PriKey, 'VanLineFID', VanLine.VanLineName
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.VanLineFID, -1 ) != isnull( deleted.VanLineFID, -1 )
 left outer join VanLine on inserted.VanLineFID = VanLine.VLPriKey
 where update( VanLineFID )
 union all
 select inserted.PriKey, 'OrderNo', inserted.OrderNo
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OrderNo, '' ) != isnull( deleted.OrderNo, '' )
 where update( OrderNo )
 union all
 select inserted.PriKey, 'OrderSeg', inserted.OrderSeg
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OrderSeg, '' ) != isnull( deleted.OrderSeg, '' )
 where update( OrderSeg )
 union all
 select inserted.PriKey, 'PSeg', inserted.PSeg
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.PSeg, '' ) != isnull( deleted.PSeg, '' )
 where update( PSeg )
 union all
 select inserted.PriKey, 'OSeg', inserted.OSeg
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OSeg, '' ) != isnull( deleted.OSeg, '' )
 where update( OSeg )
 union all
 select inserted.PriKey, 'SSeg', inserted.SSeg
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.SSeg, '' ) != isnull( deleted.SSeg, '' )
 where update( SSeg )
 union all
 select inserted.PriKey, 'LastName', inserted.LastName
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.LastName, '' ) != isnull( deleted.LastName, '' )
 where update( LastName )
 union all
 select inserted.PriKey, 'AccountProfileFID', AccountProfiles.[Name]
 from inserted
 left outer join AccountProfiles on inserted.AccountProfileFID = AccountProfiles.AccountProfileID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.AccountProfileFID, -1 ) != isnull( deleted.AccountProfileFID, -1 )
 where update ( AccountProfileFID )
 union all
 select inserted.PriKey, 'ActDelDate', convert( varchar, inserted.ActDelDate, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.ActDelDate, inserted.ActDelDate )
 where update (ActDelDate)
 union all
 select inserted.PriKey, 'ActLoadDate', convert( varchar, inserted.ActLoadDate, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.ActLoadDate, inserted.ActLoadDate )
 where update (ActLoadDate)
 union all
 select inserted.PriKey, 'AssignedOn', convert( varchar, inserted.AssignedOn, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.AssignedOn, inserted.AssignedOn )
 where update( AssignedOn )
 union all
 select inserted.PriKey, 'AssignedOnTime', convert( varchar, inserted.AssignedOnTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.AssignedOnTime, inserted.AssignedOnTime )
 where update( AssignedOnTime )
 union all
 select inserted.PriKey, 'AuthPriKey', AuthorityTypes.[Description]
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.AuthPriKey, -1 ) != isnull( deleted.AuthPriKey, -1 )
 left outer join AuthorityTypes on inserted.AuthPriKey = AuthorityTypes.AuthPriKey
 where update( AuthPriKey )
 union all
 select inserted.PriKey, 'Commodity', CommType.Commodity
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.Commodity, -1 ) != isnull( deleted.Commodity, -1 )
 left outer join CommType on inserted.Commodity = CommType.PriKey
 where update( Commodity )
 union all
 select inserted.PriKey, 'CustomerNumber', inserted.CustomerNumber
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.CustomerNumber, '' ) != isnull( deleted.CustomerNumber, '' )
 where update( CustomerNumber )
 union all
 select inserted.PriKey, 'StartDeliv', convert( varchar, inserted.StartDeliv, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.StartDeliv, inserted.StartDeliv )
 where update( StartDeliv )
 union all
 select inserted.PriKey, 'EndDeliv', convert( varchar, inserted.EndDeliv, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.EndDeliv, inserted.EndDeliv )
 where update( EndDeliv )
 union all
 select inserted.PriKey, 'DeliveryTime', convert( varchar, inserted.DeliveryTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.DeliveryTime, inserted.DeliveryTime )
 where update( DeliveryTime )
 union all
 select inserted.PriKey, 'DeliveryEndTime', convert( varchar, inserted.DeliveryEndTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.DeliveryEndTime, inserted.DeliveryEndTime )
 where update( DeliveryEndTime )
 union all
 select inserted.PriKey, 'Discount', convert( varchar, inserted.Discount )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.Discount ), '' ) != isnull ( convert(varchar, deleted.Discount ), '' )
 union all
 select inserted.PriKey, 'EstimatedWeight', convert( varchar, inserted.EstimatedWeight )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.EstimatedWeight ), '' ) != isnull ( convert(varchar, deleted.EstimatedWeight ), '' )
 union all
 select inserted.PriKey, 'ETA', convert( varchar, inserted.ETA )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.ETA, inserted.ETA )
 where update( ETA )
 union all
 select inserted.PriKey, 'ETATime', convert( varchar, inserted.ETATime )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.ETATime, inserted.ETATime )
 where update( ETATime )
 union all
 select inserted.PriKey, 'Weight', convert( varchar, inserted.Weight )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.Weight ), '' ) != isnull ( convert(varchar, deleted.Weight ), '' )
 where update( Weight )
 union all
 select inserted.PriKey, 'StartLoad', convert( varchar, inserted.StartLoad, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.StartLoad, inserted.StartLoad )
 where update( StartLoad )
 union all
 select inserted.PriKey, 'EndLoad', convert( varchar, inserted.EndLoad, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.EndLoad, inserted.EndLoad )
 where update( EndLoad )
 union all
 select inserted.PriKey, 'LoadTime', convert( varchar, inserted.LoadTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.LoadTime, inserted.LoadTime )
 where update( LoadTime )
 union all
 select inserted.PriKey, 'LoadEndTime', convert( varchar, inserted.LoadEndTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.LoadEndTime, inserted.LoadEndTime )
 where update( LoadEndTime )
 union all
 select inserted.PriKey, 'AccountName', inserted.AccountName
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.AccountName, '' ) != isnull( deleted.AccountName, '' )
 where update( AccountName )
 union all
 select inserted.PriKey, 'AcctPriKey', Accounts.AcctNo
 from inserted
 left outer join Accounts on inserted.AcctPriKey = Accounts.AccountPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.AcctPriKey, -1 ) != isnull( deleted.AcctPriKey, -1 )
 where update( AcctPriKey )
 union all
 select inserted.PriKey, 'StartPack', convert( varchar, inserted.StartPack, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.StartPack, inserted.StartPack )
 where update( StartPack )
 union all
 select inserted.PriKey, 'PackTime', convert( varchar, inserted.PackTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.PackTime, inserted.PackTime )
 where update( PackTime )
 union all
 select inserted.PriKey, 'EndPack', convert( varchar, inserted.EndPack, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.EndPack, inserted.EndPack )
 where update( EndPack )
 union all
 select inserted.PriKey, 'PackEndTime', convert( varchar, inserted.PackEndTime, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.PackEndTime, inserted.PackEndTime )
 where update( PackEndTime )
 union all
 select inserted.PriKey, 'ShipmentTypeFID', ShipmentType.ShipmentTypeDescription
 from inserted
 left outer join ShipmentType on inserted.ShipmentTypeFID = ShipmentType.ShipmentTypeID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ShipmentTypeFID, -1 ) != isnull( deleted.ShipmentTypeFID, -1 )
 where update( ShipmentTypeFID )
 union all
 select inserted.PriKey, 'MoveType', MoveType.MoveName
 from inserted
 left outer join MoveType on inserted.MoveType = MoveType.PriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.MoveType, -1 ) != isnull( deleted.MoveType, -1 )
 where update( MoveType )
 union all
 select inserted.PriKey, 'RPPriKey', RatePlans.[Description]
 from inserted
 left outer join RatePlans on inserted.RPPriKey = RatePlans.RPPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.RPPriKey, -1 ) != isnull( deleted.RPPriKey, -1 )
 where update( RPPriKey )
 union all
 select inserted.PriKey, 'CMPriKey', ContractMaster.[Description]
 from inserted
 left outer join ContractMaster on inserted.CMPriKey = ContractMaster.CMPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.CMPriKey, -1 ) != isnull( deleted.CMPriKey, -1 )
 where update( CMPriKey )
 union all
 select inserted.PriKey, 'BookAgent', Agent.AgentID
 from inserted
 left outer join Agent on inserted.BookAgent = Agent.AgentPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.BookAgent, -1 ) != isnull( deleted.BookAgent, -1 )
 where update( BookAgent )
 union all
 select inserted.PriKey, 'OrgAgent', Agent.AgentID
 from inserted
 left outer join Agent on inserted.OrgAgent = Agent.AgentPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OrgAgent, -1 ) != isnull( deleted.OrgAgent, -1 )
 where update( OrgAgent )
 union all
 select inserted.PriKey, 'DestAgent', Agent.AgentID
 from inserted
 left outer join Agent on inserted.DestAgent = Agent.AgentPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.DestAgent, -1 ) != isnull( deleted.DestAgent, -1 )
 where update( DestAgent )
 union all
 select inserted.PriKey, 'HaulAgent', Agent.AgentID
 from inserted
 left outer join Agent on inserted.HaulAgent = Agent.AgentPriKey
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.HaulAgent, -1 ) != isnull( deleted.HaulAgent, -1 )
 where update( HaulAgent )
 union all
 select inserted.PriKey, 'ToCounty', inserted.ToCounty
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ToCounty, '' ) != isnull( deleted.ToCounty, '' )
 where update( ToCounty )
 union all
 select inserted.PriKey, 'FromCounty', inserted.FromCounty
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.FromCounty, '' ) != isnull( deleted.FromCounty, '' )
 where update( FromCounty )
 union all
 select inserted.PriKey, 'EstimateNo', inserted.EstimateNo
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.EstimateNo, '' ) != isnull( deleted.EstimateNo, '' )
 where update( EstimateNo )
 union all
 select inserted.PriKey, 'ProfileID', Profiles.ProfileName
 from inserted
 left outer join Profiles on inserted.ProfileID = Profiles.ProfileID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ProfileID, -1 ) != isnull( deleted.ProfileID, -1 )
 where update( ProfileID )
 union all
 select inserted.PriKey, 'RolloutID', Rollouts.RolloutName
 from inserted
 left outer join Rollouts on inserted.RolloutID = Rollouts.RollOutID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.RolloutID, -1 ) != isnull( deleted.RolloutID, -1 )
 where update( RolloutID )
 union all
 select inserted.PriKey, 'ReleaseDate', convert( varchar, inserted.ReleaseDate, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.ReleaseDate, inserted.ReleaseDate )
 where update( ReleaseDate )
 union all
 select inserted.PriKey, 'OpenOrderInModule', inserted.OpenOrderInModule
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OpenOrderInModule, '' ) != isnull( deleted.OpenOrderInModule, '' )
 where update( OpenOrderInModule )
 union all
 select inserted.PriKey, 'OrderStatus', inserted.OrderStatus
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OrderStatus, '' ) != isnull( deleted.OrderStatus, '' )
 where update( OrderStatus )
 union all
 select inserted.PriKey, 'SalesPerson', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 left outer join Sysuser on inserted.SalesPerson = Sysuser.SysUserID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.SalesPerson, -1 ) != isnull( deleted.SalesPerson, -1 )
 where update( SalesPerson )
 union all
 select inserted.PriKey, 'Coordinator', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 left outer join Sysuser on inserted.Coordinator = Sysuser.SysUserID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.Coordinator, -1 ) != isnull( deleted.Coordinator, -1 )
 where update( Coordinator )
 union all
 select inserted.PriKey, 'LineHaul', convert( varchar, inserted.LineHaul )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.LineHaul ), '' ) != isnull ( convert(varchar, deleted.LineHaul ), '' )
 where update( LineHaul )
 union all
 select inserted.PriKey, 'EstAmt', convert( varchar, inserted.EstAmt )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.EstAmt ), '' ) != isnull ( convert(varchar, deleted.EstAmt ), '' )
 where update( EstAmt )
 union all
 select inserted.PriKey, 'ActualCost', convert( varchar, inserted.ActualCost )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( convert(varchar, inserted.ActualCost ), '' ) != isnull ( convert(varchar, deleted.ActualCost ), '' )
 where update( ActualCost )
 union all
 select inserted.PriKey, 'TenderedDate', convert( varchar, inserted.TenderedDate, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.TenderedDate, inserted.TenderedDate )
 union all
 select inserted.PriKey, 'PaperworkRecd', convert( varchar, inserted.PaperworkRecd, 126 )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 1 = dbo.DateFieldChanged( deleted.PaperworkRecd, inserted.PaperworkRecd )
 where update( PaperworkRecd )
 union all
 select inserted.PriKey, 'PurchaseOrderNo', inserted.PurchaseOrderNo
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.PurchaseOrderNo, '' ) <> isnull ( deleted.PurchaseOrderNo , '' )
 where update( PurchaseOrderNo )
 union all
 select inserted.PriKey, 'LogisticCoordinator', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 left outer join Sysuser on inserted.LogisticCoordinator = Sysuser.SysUserID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.LogisticCoordinator, -1 ) != isnull( deleted.LogisticCoordinator, -1 )
 where update( LogisticCoordinator )
 union all
 select inserted.PriKey, 'RevenueClerk', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 left outer join Sysuser on inserted.RevenueClerk = Sysuser.SysUserID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.RevenueClerk, -1 ) != isnull( deleted.RevenueClerk, -1 )
 where update( RevenueClerk )
 union all
 select inserted.PriKey, 'HaulingAgreedDiscount', convert( varchar, inserted.HaulingAgreedDiscount )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.HaulingAgreedDiscount, -1 ) <> isnull( deleted.HaulingAgreedDiscount, -1 )
 where update( HaulingAgreedDiscount )
 union all
 select inserted.PriKey, 'OriginAgreedDiscount', convert( varchar, inserted.OriginAgreedDiscount )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.OriginAgreedDiscount, -1 ) <> isnull( deleted.OriginAgreedDiscount, -1 )
 where update( OriginAgreedDiscount )
 union all
 select inserted.PriKey, 'DestinationAgreedDiscount', convert( varchar, inserted.DestinationAgreedDiscount)
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.DestinationAgreedDiscount, -1 ) <> isnull( deleted.DestinationAgreedDiscount, -1 )
 where update( DestinationAgreedDiscount )
 union all
 select inserted.PriKey, 'Valuation', Valuation.[Name]
 from inserted
 left outer join Valuation ON Valuation.ValuationID = Inserted.ValuationFID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ValuationFID, -1 ) != isnull( deleted.ValuationFID, -1 )
 where update( ValuationFID )
 union all
 select inserted.PriKey, 'PerPound', convert(varchar, ValuationPerPound.Amount )
 from inserted
 left outer join dbo.ValuationPerPound ON ValuationPerPound.ValuationPerPoundID = Inserted.ValuationPerPoundFID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ValuationPerPoundFID, -1 ) <> isnull( deleted.ValuationPerPoundFID, -1 )
 where update( ValuationPerPoundFID )
 union all
 select inserted.PriKey, 'Amount', convert(varchar, inserted.ValuationAmount )
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ValuationAmount, -1 ) <> isnull( deleted.ValuationAmount, -1 )
 where update( ValuationAmount )
 union all
 select inserted.PriKey, 'ServiceType', ServiceType.ServiceTypeDescription
 from inserted
 left outer join ServiceType ON ServiceType.ServiceTypeID = Inserted.ServiceTypeFID
 inner join deleted on inserted.PriKey = deleted.PriKey and
 isnull( inserted.ServiceTypeFID, -1 ) <> isnull( deleted.ServiceTypeFID, -1 )

 where update( ServiceTypeFID )

if ( update(SSUserDefined1) or update( SSUserDefined2 ) or update( SSUserDefined3 ) or update( SSUserDefined4 ) or update( SSUserDefined5 )
 or update( SSUserDefined6 ) or update( SSUserDefined7 ) or update( SSUserDefined8 ))
 begin

 declare @UDFLablesTable as table (
 UDFLablesPriKey int not null,
 UDFSActive1 bit, UDFSActive2 bit, UDFSActive3 bit, UDFSActive4 bit,
 UDFSActive5 bit, UDFSActive6 bit, UDFSActive7 bit, UDFSActive8 bit,
 UDF1S varchar(30), UDF2S varchar(30), UDF3S varchar(30), UDF4S varchar(30),
 UDF5S varchar(30), UDF6S varchar(30), UDF7S varchar(30), UDF8S varchar(30),
 unique (UDFLablesPriKey)
 )

 insert into @UDFLablesTable
 select
 UDFLablesPriKey = Inserted.PriKey,
 SysFile.UserDefinedActive1,
 SysFile.UserDefinedActive2,
 SysFile.UserDefinedActive3,
 SysFile.UserDefinedActive4,
 SysFile.UserDefinedActive5,
 SysFile.UserDefinedActive6,
 SysFile.UserDefinedActive7,
 SysFile.UserDefinedActive8,
 isnull( SUDFMoveTypeLabels.UserDefinedLabel1, SysFile.UserDefinedLabel1 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel2, SysFile.UserDefinedLabel2 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel3, SysFile.UserDefinedLabel3 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel4, SysFile.UserDefinedLabel4 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel5, SysFile.UserDefinedLabel5 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel6, SysFile.UserDefinedLabel6 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel7, SysFile.UserDefinedLabel7 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel8, SysFile.UserDefinedLabel8 )
 from dbo.SysFile
 cross join Inserted
 left outer join dbo.SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey =Inserted.MoveType;

 insert into @theAuditFields
 (
 OrderFID,
 FieldName,
 [Value],
 customField
 )
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined1, concat(UDFLables.UDF1S,' (UDF-1S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined1, '' ) <> isnull( deleted.SSUserDefined1, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive1 = 1
 where update( SSUserDefined1 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined2, concat(UDFLables.UDF2S,' (UDF-2S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined2, '' ) <> isnull( deleted.SSUserDefined2, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive2 = 1
 where update( SSUserDefined2 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined3, concat(UDFLables.UDF3S,' (UDF-3S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined3, '' ) <> isnull( deleted.SSUserDefined3, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive3 = 1
 where update( SSUserDefined3 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined4, concat(UDFLables.UDF4S,' (UDF-4S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined4, '' ) <> isnull( deleted.SSUserDefined4, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive4 = 1
 where update( SSUserDefined4 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined5, concat(UDFLables.UDF5S,' (UDF-5S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined5, '' ) <> isnull( deleted.SSUserDefined5, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive5 = 1
 where update( SSUserDefined5 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined6, concat(UDFLables.UDF6S,' (UDF-6S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined6, '' ) <> isnull( deleted.SSUserDefined6, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive6 = 1
 where update( SSUserDefined6 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined7, concat(UDFLables.UDF7S,' (UDF-7S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined7, '' ) <> isnull( deleted.SSUserDefined7, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive7 = 1
 where update( SSUserDefined7 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined8, concat(UDFLables.UDF8S,' (UDF-8S)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.SSUserDefined8, '' ) <> isnull( deleted.SSUserDefined8, '' )
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive8 = 1
 where update( SSUserDefined8 )
 end

 if ( update(CSUserDefined1) or update( CSUserDefined2 ) or update( CSUserDefined3 ) or update( CSUserDefined4 ) or update( CSUserDefined5 )
 or update( CSUserDefined6 ) or update( CSUserDefined7 ) or update( CSUserDefined8 ))
 begin

 declare @NALablesTable as table (
 UDFLablesPriKey int not null,
 UDFAActive1 bit, UDFAActive2 bit, UDFAActive3 bit, UDFAActive4 bit,
 UDFAActive5 bit, UDFAActive6 bit, UDFAActive7 bit, UDFAActive8 bit,
 UDF1A varchar(30), UDF2A varchar(30), UDF3A varchar(30), UDF4A varchar(30),
 UDF5A varchar(30), UDF6A varchar(30), UDF7A varchar(30), UDF8A varchar(30),
 unique (UDFLablesPriKey)
 )

 insert into @NALablesTable
 select
 UDFLablesPriKey = Inserted.PriKey,
 Accounts.UserDefinedActive1,
 Accounts.UserDefinedActive2,
 Accounts.UserDefinedActive3,
 Accounts.UserDefinedActive4,
 Accounts.UserDefinedActive5,
 Accounts.UserDefinedActive6,
 Accounts.UserDefinedActive7,
 Accounts.UserDefinedActive8,
 Accounts.UserDefinedLabel1,
 Accounts.UserDefinedLabel2,
 Accounts.UserDefinedLabel3,
 Accounts.UserDefinedLabel4,
 Accounts.UserDefinedLabel5,
 Accounts.UserDefinedLabel6,
 Accounts.UserDefinedLabel7,
 Accounts.UserDefinedLabel8
 from Inserted
 inner join dbo.Accounts on Inserted.AcctPriKey = Accounts.AccountPriKey;

 insert into @theAuditFields
 (
 OrderFID,
 FieldName,
 [Value],
 customField
 )
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined1, concat( isnull(concat(UDFLables.UDF1A,' '),''),'(UDF-1A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined1, '' ) <> isnull( deleted.CSUserDefined1, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive1 = 1
 where update( CSUserDefined1 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined2, concat( isnull(concat(UDFLables.UDF2A,' '),''),'(UDF-2A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined2, '' ) <> isnull( deleted.CSUserDefined2, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive2 = 1
 where update( CSUserDefined2 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined3, concat( isnull(concat(UDFLables.UDF3A,' '),''),'(UDF-3A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined3, '' ) <> isnull( deleted.CSUserDefined3, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive3 = 1
 where update( CSUserDefined3 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined4, concat( isnull(concat(UDFLables.UDF4A,' '),''),'(UDF-4A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined4, '' ) <> isnull( deleted.CSUserDefined4, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive4 = 1
 where update( CSUserDefined4 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined5, concat( isnull(concat(UDFLables.UDF5A,' '),''),'(UDF-5A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined5, '' ) <> isnull( deleted.CSUserDefined5, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive5 = 1
 where update( CSUserDefined5 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined6, concat( isnull(concat(UDFLables.UDF6A,' '),''),'(UDF-6A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined6, '' ) <> isnull( deleted.CSUserDefined6, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive6 = 1
 where update( CSUserDefined6 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined7, concat( isnull(concat(UDFLables.UDF7A,' '),''),'(UDF-7A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined7, '' ) <> isnull( deleted.CSUserDefined7, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive7 = 1
 where update( CSUserDefined7 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined8, concat( isnull(concat(UDFLables.UDF8A,' '),''),'(UDF-8A)')
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and isnull( inserted.CSUserDefined8, '' ) <> isnull( deleted.CSUserDefined8, '' )
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive8 = 1
 where update( CSUserDefined8 )
 end

 declare @theChangedOn datetime
 set @theChangedOn = dbo.GetMssDateTime()

 declare @theExtAppUser int

 if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
 begin
 select @theExtAppUser = SysUser.SysUserID
 from dbo.Sysuser
 where SysUser.FIRSTNAME = 'External' and
 SysUser.LASTNAME = 'Application'
 end

 insert into OrderHistory
 (
 OrderFID,
 ChangedBy,
 ChangedOn,
 OrderHistoryFieldFID,
 ChangedTo,
 ChangedIn,
 CustomDescription
 )
 select
 OrderFID = theAuditFields.OrderFID,
 ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
 ChangedOn = @theChangedOn,
 OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
 ChangedTo = Value,
 ChangedIn = case
 when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
 else OrderAuditInfo.UpdateSource
 end,
 CustomDescription = isnull(theAuditFields.customField,null)
 from @theAuditFields theAuditFields
 inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
 inner join inserted on theAuditFields.OrderFID = inserted.PriKey
 left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID

 -- Track specific OrderStatus changed history
 declare @theCompletedOrderStatus varchar(25)
 select
 @theCompletedOrderStatus = [Value]
 from GlobalSystemOption
 where [Name] = 'OrderCompletedOrderStatus'

 if( @theCompletedOrderStatus is not null )
 begin
 insert into OrderCompletedStatusHistory
 (
 OrderFID,
 ChangedBy,
 ChangedOn,
 ChangedIn
 )
 select
 OrderFID = inserted.PriKey,
 ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
 ChangedOn = @theChangedOn,
 ChangedIn = case
 when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
 else OrderAuditInfo.UpdateSource
 end
 from inserted
 inner join deleted on inserted.PriKey = deleted.PriKey and
 inserted.OrderStatus = @theCompletedOrderStatus and
 @theCompletedOrderStatus != deleted.OrderStatus
 left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
 where update( OrderStatus )
 end

 -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
 -- which case its the responsibility of the updater to clean up this record ).
 if not(update( OrderAuditInfoManualCleanup ) )
 begin
 update Orders
 set OrderAuditInfoFID = NULL
 from inserted
 inner join Orders on inserted.PriKey = Orders.PriKey

 delete OrderAuditInfo
 from inserted
 inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
 end
 end -- if update( OrderAuditInfoFID )
GO

CREATE trigger [dbo].[OrderAuditFieldsInitiallySet] on [dbo].[Orders]
after insert
as
set nocount on
 declare @theAuditFields table
 (
 OrderID int,
 FieldName varchar(128),
 Value varchar(256),
 customField varchar(256) null
 )
 insert into @theAuditFields
 (
 OrderID,
 FieldName,
 Value
 )
 select inserted.PriKey, 'VanLineFID', VanLine.VanLineName
 from inserted
 inner join VanLine on inserted.VanLineFID = VanLine.VLPriKey
 union all
 select inserted.PriKey, 'OrderNo', inserted.OrderNo
 from inserted
 where isnull( inserted.OrderNo, '' ) != ''
 union all
 select inserted.PriKey, 'OrderSeg', inserted.OrderSeg
 from inserted
 where isnull( inserted.OrderSeg, '' ) != ''
 union all
 select inserted.PriKey, 'PSeg', inserted.PSeg
 from inserted
 where isnull( inserted.PSeg, '' ) != ''
 union all
 select inserted.PriKey, 'OSeg', inserted.OSeg
 from inserted
 where isnull( inserted.OSeg, '' ) != ''
 union all
 select inserted.PriKey, 'SSeg', inserted.SSeg
 from inserted
 where isnull( inserted.SSeg, '' ) != ''
 union all
 select inserted.PriKey, 'LastName', inserted.LastName
 from inserted
 union all
 select inserted.PriKey, 'AccountProfileFID', AccountProfiles.Name
 from inserted
 inner join AccountProfiles on inserted.AccountProfileFID = AccountProfiles.AccountProfileID
 union all
 select inserted.PriKey, 'ActDelDate', convert(varchar, inserted.ActDelDate, 126 )
 from inserted
 union all
 select inserted.PriKey, 'ActLoadDate', convert(varchar, inserted.ActLoadDate, 126 )
 from inserted
 union all
 select inserted.PriKey, 'AssignedOn', convert(varchar, inserted.AssignedOn, 126 )
 from inserted
 union all
 select inserted.PriKey, 'AssignedOnTime', convert(varchar, inserted.AssignedOnTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'AuthPriKey', AuthorityTypes.Description
 from inserted
 inner join AuthorityTypes on inserted.AuthPriKey = AuthorityTypes.AuthPriKey
 union all
 select inserted.PriKey, 'Commodity', CommType.Commodity
 from inserted
 inner join CommType on inserted.Commodity = CommType.PriKey
 union all
 select inserted.PriKey, 'CustomerNumber', inserted.CustomerNumber
 from inserted
 union all
 select inserted.PriKey, 'StartDeliv', convert(varchar, inserted.StartDeliv, 126 )
 from inserted
 union all
 select inserted.PriKey, 'EndDeliv', convert(varchar, inserted.EndDeliv, 126 )
 from inserted
 union all
 select inserted.PriKey, 'DeliveryTime', convert(varchar, inserted.DeliveryTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'DeliveryEndTime', convert(varchar, inserted.DeliveryEndTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'Discount', convert(varchar, inserted.Discount )
 from inserted
 union all
 select inserted.PriKey, 'EstimatedWeight', convert(varchar, inserted.EstimatedWeight )
 from inserted
 union all
 select inserted.PriKey, 'ETA', convert(varchar, inserted.ETA, 126 )
 from inserted
 union all
 select inserted.PriKey, 'ETATime', convert(varchar, inserted.ETATime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'Weight', convert(varchar, inserted.Weight )
 from inserted
 union all
 select inserted.PriKey, 'StartLoad', convert(varchar, inserted.StartLoad, 126 )
 from inserted
 union all
 select inserted.PriKey, 'EndLoad', convert(varchar, inserted.EndLoad, 126 )
 from inserted
 union all
 select inserted.PriKey, 'LoadTime', convert(varchar, inserted.LoadTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'LoadEndTime', convert(varchar, inserted.LoadEndTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'AccountName', inserted.AccountName
 from inserted
 union all
 select inserted.PriKey, 'AcctPriKey', Accounts.AcctNo
 from inserted
 inner join Accounts on inserted.AcctPriKey = Accounts.AccountPriKey
 union all
 select inserted.PriKey, 'StartPack', convert(varchar, inserted.StartPack, 126 )
 from inserted
 union all
 select inserted.PriKey, 'PackTime', convert(varchar, inserted.PackTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'EndPack', convert(varchar, inserted.EndPack, 126 )
 from inserted
 union all
 select inserted.PriKey, 'PackEndTime', convert(varchar, inserted.PackEndTime, 126 )
 from inserted
 union all
 select inserted.PriKey, 'ShipmentTypeFID', ShipmentType.ShipmentTypeDescription
 from inserted
 inner join ShipmentType on inserted.ShipmentTypeFID = ShipmentType.ShipmentTypeID
 union all
 select inserted.PriKey, 'MoveType', MoveType.MoveName
 from inserted
 inner join MoveType on inserted.MoveType = MoveType.PriKey
 union all
 select inserted.PriKey, 'RPPriKey', RatePlans.Description
 from inserted
 inner join RatePlans on inserted.RPPriKey = RatePlans.RPPriKey
 union all
 select inserted.PriKey, 'CMPriKey', ContractMaster.Description
 from inserted
 inner join ContractMaster on inserted.CMPriKey = ContractMaster.CMPriKey
 union all
 select inserted.PriKey, 'BookAgent', Agent.AgentID
 from inserted
 inner join Agent on inserted.BookAgent = Agent.AgentPriKey
 union all
 select inserted.PriKey, 'OrgAgent', Agent.AgentID
 from inserted
 inner join Agent on inserted.OrgAgent = Agent.AgentPriKey
 union all
 select inserted.PriKey, 'DestAgent', Agent.AgentID
 from inserted
 inner join Agent on inserted.DestAgent = Agent.AgentPriKey
 union all
 select inserted.PriKey, 'HaulAgent', Agent.AgentID
 from inserted
 inner join Agent on inserted.HaulAgent = Agent.AgentPriKey
 union all
 select inserted.PriKey, 'ToCounty', inserted.ToCounty
 from inserted
 where isnull( inserted.ToCounty, '' ) != ''
 union all
 select inserted.PriKey, 'FromCounty', inserted.FromCounty
 from inserted
 where isnull( inserted.FromCounty, '' ) != ''
 union all
 select inserted.PriKey, 'EstimateNo', inserted.EstimateNo
 from inserted
 where isnull( inserted.EstimateNo, '' ) != ''
 union all
 select inserted.PriKey, 'ProfileID', Profiles.ProfileName
 from inserted
 inner join Profiles on inserted.ProfileID = Profiles.ProfileID
 union all
 select inserted.PriKey, 'RolloutID', Rollouts.RolloutName
 from inserted
 inner join Rollouts on inserted.RolloutID = Rollouts.RolloutID
 union all
 select inserted.PriKey, 'ReleaseDate', convert(varchar, inserted.ReleaseDate, 126 )
 from inserted
 union all
 select inserted.PriKey, 'OpenOrderInModule', inserted.OpenOrderInModule
 from inserted
 where isnull( inserted.OpenOrderInModule, '' ) != ''
 union all
 select inserted.PriKey, 'OrderStatus', inserted.OrderStatus
 from inserted
 where isnull( inserted.OrderStatus, '' ) != ''
 union all
 select inserted.PriKey, 'SalesPerson', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 inner join Sysuser on inserted.SalesPerson = Sysuser.SysUserID
 union all
 select inserted.PriKey, 'Coordinator', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 inner join Sysuser on inserted.Coordinator = Sysuser.SysUserID
 union all
 select inserted.PriKey, 'LineHaul', convert( varchar, inserted.LineHaul )
 from inserted
 union all
 select inserted.PriKey, 'EstAmt', convert( varchar, inserted.EstAmt )
 from inserted
 union all
 select inserted.PriKey, 'ActualCost', convert( varchar, inserted.ActualCost )
 from inserted
 union all
 select inserted.PriKey, 'TenderedDate', convert( varchar, inserted.TenderedDate, 126 )
 from inserted
 union all
 select inserted.PriKey, 'PaperworkRecd', convert( varchar, inserted.PaperworkRecd, 126 )
 from inserted
 union all
 select inserted.PriKey, 'PurchaseOrderNo', inserted.PurchaseOrderNo
 from inserted
 union all
 select inserted.PriKey, 'LogisticCoordinator', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 inner join Sysuser on inserted.LogisticCoordinator = Sysuser.SysUserID
 union all
 select inserted.PriKey, 'RevenueClerk', dbo.FormatFirstNameLastName( Sysuser.FIRSTNAME, Sysuser.LASTNAME )
 from inserted
 inner join Sysuser on inserted.RevenueClerk = Sysuser.SysUserID
 union all
 select inserted.PriKey, 'HaulingAgreedDiscount', convert(varchar, inserted.HaulingAgreedDiscount )
 from inserted
 union all
 select inserted.PriKey, 'OriginAgreedDiscount', convert(varchar, inserted.OriginAgreedDiscount )
 from inserted
 union all
 select inserted.PriKey, 'DestinationAgreedDiscount', convert(varchar, inserted.DestinationAgreedDiscount )
 from inserted
 union all
 select inserted.PriKey, 'Valuation', Valuation.[Name]
 from inserted
 inner join dbo.Valuation ON Valuation.ValuationID = Inserted.ValuationFID
 union all
 select inserted.PriKey, 'PerPound', convert(varchar, ValuationPerPound.Amount )
 from inserted
 inner join dbo.ValuationPerPound ON ValuationPerPound.ValuationPerPoundID = Inserted.ValuationPerPoundFID
 union all
 select inserted.PriKey, 'Amount', convert(varchar, Inserted.ValuationAmount )
 from inserted
 union all
 select inserted.PriKey, 'ServiceType', ServiceType.ServiceTypeDescription
 from inserted
 inner join dbo.ServiceType ON ServiceType.ServiceTypeID = Inserted.ServiceTypeFID

 if ( update(SSUserDefined1) or update( SSUserDefined2 ) or update( SSUserDefined3 ) or update( SSUserDefined4 ) or update( SSUserDefined5 )
 or update( SSUserDefined6 ) or update( SSUserDefined7 ) or update( SSUserDefined8 ))
 begin

 declare @UDFLablesTable as table (
 UDFLablesPriKey int not null,
 UDFSActive1 bit, UDFSActive2 bit, UDFSActive3 bit, UDFSActive4 bit,
 UDFSActive5 bit, UDFSActive6 bit, UDFSActive7 bit, UDFSActive8 bit,
 UDF1S varchar(30), UDF2S varchar(30), UDF3S varchar(30), UDF4S varchar(30),
 UDF5S varchar(30), UDF6S varchar(30), UDF7S varchar(30), UDF8S varchar(30),
 unique (UDFLablesPriKey)
 )

 insert into @UDFLablesTable
 select
 UDFLablesPriKey = Inserted.PriKey,
 SysFile.UserDefinedActive1,
 SysFile.UserDefinedActive2,
 SysFile.UserDefinedActive3,
 SysFile.UserDefinedActive4,
 SysFile.UserDefinedActive5,
 SysFile.UserDefinedActive6,
 SysFile.UserDefinedActive7,
 SysFile.UserDefinedActive8,
 isnull( SUDFMoveTypeLabels.UserDefinedLabel1, SysFile.UserDefinedLabel1 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel2, SysFile.UserDefinedLabel2 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel3, SysFile.UserDefinedLabel3 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel4, SysFile.UserDefinedLabel4 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel5, SysFile.UserDefinedLabel5 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel6, SysFile.UserDefinedLabel6 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel7, SysFile.UserDefinedLabel7 ),
 isnull( SUDFMoveTypeLabels.UserDefinedLabel8, SysFile.UserDefinedLabel8 )
 from dbo.SysFile
 cross join Inserted
 left outer join dbo.SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey =Inserted.MoveType;

 insert into @theAuditFields
 (
 OrderID,
 FieldName,
 [Value],
 customField
 )
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined1, concat(UDFLables.UDF1S,' (UDF-1S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive1 = 1
 where update( SSUserDefined1 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined2, concat(UDFLables.UDF2S,' (UDF-2S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive2 = 1
 where update( SSUserDefined2 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined3, concat(UDFLables.UDF3S,' (UDF-3S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive3 = 1
 where update( SSUserDefined3 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined4, concat(UDFLables.UDF4S,' (UDF-4S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive4 = 1
 where update( SSUserDefined4 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined5, concat(UDFLables.UDF5S,' (UDF-5S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive5 = 1
 where update( SSUserDefined5 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined6, concat(UDFLables.UDF6S,' (UDF-6S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive6 = 1
 where update( SSUserDefined6 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined7, concat(UDFLables.UDF7S,' (UDF-7S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive7 = 1
 where update( SSUserDefined7 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.SSUserDefined8, concat(UDFLables.UDF8S,' (UDF-8S)')
 from inserted
 inner join @UDFLablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFSActive8 = 1
 where update( SSUserDefined8 )

 end


 if ( update(CSUserDefined1) or update( CSUserDefined2 ) or update( CSUserDefined3 ) or update( CSUserDefined4 ) or update( CSUserDefined5 )
 or update( CSUserDefined6 ) or update( CSUserDefined7 ) or update( CSUserDefined8 ))
 begin

 declare @NALablesTable as table (
 UDFLablesPriKey int not null,
 UDFAActive1 bit, UDFAActive2 bit, UDFAActive3 bit, UDFAActive4 bit,
 UDFAActive5 bit, UDFAActive6 bit, UDFAActive7 bit, UDFAActive8 bit,
 UDF1A varchar(30), UDF2A varchar(30), UDF3A varchar(30), UDF4A varchar(30),
 UDF5A varchar(30), UDF6A varchar(30), UDF7A varchar(30), UDF8A varchar(30),
 unique (UDFLablesPriKey)
 )

 insert into @NALablesTable
 select
 UDFLablesPriKey = Inserted.PriKey,
 Accounts.UserDefinedActive1,
 Accounts.UserDefinedActive2,
 Accounts.UserDefinedActive3,
 Accounts.UserDefinedActive4,
 Accounts.UserDefinedActive5,
 Accounts.UserDefinedActive6,
 Accounts.UserDefinedActive7,
 Accounts.UserDefinedActive8,
 Accounts.UserDefinedLabel1,
 Accounts.UserDefinedLabel2,
 Accounts.UserDefinedLabel3,
 Accounts.UserDefinedLabel4,
 Accounts.UserDefinedLabel5,
 Accounts.UserDefinedLabel6,
 Accounts.UserDefinedLabel7,
 Accounts.UserDefinedLabel8
 from Inserted
 inner join dbo.Accounts on Inserted.AcctPriKey = Accounts.AccountPriKey;

 insert into @theAuditFields
 (
 OrderID,
 FieldName,
 [Value],
 customField
 )
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined1, concat( isnull(concat(UDFLables.UDF1A,' '),''),'(UDF-1A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive1 = 1
 where update( CSUserDefined1 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined2, concat( isnull(concat(UDFLables.UDF2A,' '),''),'(UDF-2A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive2 = 1
 where update( CSUserDefined2 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined3, concat( isnull(concat(UDFLables.UDF3A,' '),''),'(UDF-3A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive3 = 1
 where update( CSUserDefined3 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined4, concat( isnull(concat(UDFLables.UDF4A,' '),''),'(UDF-4A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive4 = 1
 where update( CSUserDefined4 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined5, concat( isnull(concat(UDFLables.UDF5A,' '),''),'(UDF-5A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive5 = 1
 where update( CSUserDefined5 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined6, concat( isnull(concat(UDFLables.UDF6A,' '),''),'(UDF-6A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive6 = 1
 where update( CSUserDefined6 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined7, concat( isnull(concat(UDFLables.UDF7A,' '),''),'(UDF-7A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive7 = 1
 where update( CSUserDefined7 )
 union all
 select Inserted.PriKey,'UDFLabels',Inserted.CSUserDefined8, concat( isnull(concat(UDFLables.UDF8A,' '),''),'(UDF-8A)')
 from inserted
 left join @NALablesTable as UDFLables on Inserted.PriKey = UDFLables.UDFLablesPriKey and UDFLables.UDFAActive8 = 1
 where update( CSUserDefined8 )
 end

 declare @theChangedOn datetime
 set @theChangedOn = dbo.GetMssDateTime()

 declare @theExtAppUser int

 if exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null )
 begin
 select @theExtAppUser = SysUser.SysUserID
 from dbo.Sysuser
 where SysUser.FIRSTNAME = 'External' and
 SysUser.LASTNAME = 'Application'
 end

 insert into OrderHistory
 (
 OrderFID,
 ChangedBy,
 ChangedOn,
 OrderHistoryFieldFID,
 ChangedTo,
 ChangedIn,
 CustomDescription
 )
 select
 OrderFID = inserted.PriKey,
 ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
 ChangedOn = @theChangedOn,
 OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
 ChangedTo = theAuditFields.[Value],
 ChangedIn = case
 when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
 else OrderAuditInfo.UpdateSource
 end,
 CustomDescription = isnull(theAuditFields.customField,null)
 from @theAuditFields theAuditFields
 inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
 inner join inserted on theAuditFields.OrderID = inserted.PriKey
 left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
 where theAuditFields.Value is not null

 -- No OrderCompletedStatusHistory is needed on insert since its impossible to work an
 -- order to completion when initially inserting an order. If you are, then you are just
 -- doing a bulk import process of orders from a non-MoversSuite system.

 -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
 -- which case its the responsibility of the inserter to clean up this record ).
 update Orders
 set OrderAuditInfoFID = NULL
 from inserted
 inner join Orders on inserted.PriKey = Orders.PriKey
 where inserted.OrderAuditInfoManualCleanup is null

 delete OrderAuditInfo
 from inserted
 inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
 where inserted.OrderAuditInfoManualCleanup is null
GO
/**
*
* Send email when certain order dates change
*/

CREATE trigger [dbo].[OrdersDateChangeNotification] on [dbo].[Orders] after update
as
set nocount on

-- This trigger is meant to send notification for single order changes only. Ignore bulk changes.
if 1 < ( select count(*) from deleted ) or 1 < ( select count(*) from inserted )
begin
 return
end

declare @theLoadDateChanged bit
declare @theDeliveryDateChanged bit

declare @theOldActLoadDate varchar(10)
declare @theActLoadDate varchar(10)
declare @theOldActDeliveryDate varchar(10)
declare @theActDeliveryDate varchar(10)

declare @theDefaultDateFormat int
set @theDefaultDateFormat = dbo.GetDefaultDateFormat()

set @theOldActLoadDate = ( select convert( varchar(10), ActLoadDate, @theDefaultDateFormat )from deleted )
set @theActLoadDate = ( select convert( varchar(10), ActLoadDate, @theDefaultDateFormat )from inserted )

set @theOldActDeliveryDate = ( select convert( varchar(10), ActDelDate, @theDefaultDateFormat ) from deleted )
set @theActDeliveryDate = ( select convert( varchar(10), ActDelDate, @theDefaultDateFormat ) from inserted )

set @theLoadDateChanged = 0
if( isnull( @theOldActLoadDate, '' ) != isnull( @theActLoadDate, '' ) )
begin
 set @theLoadDateChanged = 1
end

/*
 Note: the call: update( ActDelDate ) returns true if the fields was included in the update statement
 even if the value did not change. Therefore, to see if the value changed we have to look at the values.
*/
set @theDeliveryDateChanged = 0
if( isnull( @theOldActDeliveryDate, '' ) != isnull( @theActDeliveryDate, '' ) )
begin
 set @theDeliveryDateChanged = 1
end

if( @theLoadDateChanged = 1 or @theDeliveryDateChanged = 1 )
begin
 -- Call Database mail
 declare @theMessageRecipents varchar(max)
 declare @theMessageBody varchar(max)
 declare @theMessageSubject varchar(100)

 -- get recipents from the AccountProfileContacts table
 declare @theOrderID int
 declare @theOrderNo varchar(23)
 declare @thePurchaseNo varchar(30)
 declare @theLastName varchar(26)
 declare @theOriginCity AddressCity
 declare @theOriginState AddressState
 declare @theDestinationCity AddressCity
 declare @theDestinationState AddressState

 select
 @theOrderID = inserted.PriKey,
 @theOrderNo = inserted.OrderNo,
 @theLastName = inserted.LastName,
 @thePurchaseNo = inserted.PurchaseOrderNo,
 @theOriginCity = OrderAddresses.OriginCity,
 @theOriginState = OrderAddresses.OriginState,
 @theDestinationCity = OrderAddresses.DestinationCity,
 @theDestinationState = OrderAddresses.DestinationState
 from inserted
 inner join OrderAddresses on inserted.PriKey = OrderAddresses.OrderFID

 --SQL20XX
 select @theMessageRecipents = isnull( @theMessageRecipents + ';', '' ) + AccountProfileContacts.Email
 from Orders
 inner join AccountProfiles on Orders.AccountProfileFID = AccountProfiles.AccountProfileID
 inner join AccountProfileContacts on AccountProfiles.AccountProfileID = AccountProfileContacts.AccountProfileFID
 where
 (
 Orders.PriKey = @theOrderID and
 AccountProfileContacts.MonitorOrders = 1 and
 AccountProfileContacts.Email is not null
 )

 if( @theOrderNo is not null and @theMessageRecipents is not null )
 begin
 -- Setup the order info that will be reported in the email
 declare @theOrderMessageSegment varchar(max)
 set @theOrderMessageSegment = @theOrderNo + ' (' + @theLastName + ') '
 if( @theOriginCity is not null or @theOriginState is not null or @theDestinationCity is not null or @theDestinationState is not null )
 begin
 set @theOrderMessageSegment = @theOrderMessageSegment + 'moving '
 set @theOrderMessageSegment = @theOrderMessageSegment + isnull( 'from ' + @theOriginCity + isnull( ', ' + @theOriginState, '' ) + ' ', isnull( 'from ' + @theOriginState + ' ', '' ) )
 set @theOrderMessageSegment = @theOrderMessageSegment + isnull( 'to ' + @theDestinationCity + isnull( + ', ' + @theDestinationState, '' ) + ' ', isnull( 'to ' + @theDestinationState + ' ', '' ) )
 end
 set @theOrderMessageSegment = @theOrderMessageSegment + isnull( 'on PO ' + @thePurchaseNo + ' ', '' ) + 'has been '

 set @theMessageSubject = 'Order ' + @theOrderNo + ' has been modified.'
 set @theMessageBody = ''
 if( @theLoadDateChanged = 1 )
 begin
 set @theMessageBody = @theMessageBody + 'The actual load date for order ' + @theOrderMessageSegment + ' '

 if( @theActLoadDate is null )
 begin
 set @theMessageBody = @theMessageBody + 'removed. '
 end
 else if( @theOldActLoadDate is null )
 begin
 set @theMessageBody = @theMessageBody + 'set to ' + @theActLoadDate + '. '
 end
 else
 begin
 set @theMessageBody = @theMessageBody + 'changed from ' + @theOldActLoadDate + ' to ' + @theActLoadDate + '. '
 end
 end
 if( @theDeliveryDateChanged = 1 )
 begin
 set @theMessageBody = @theMessageBody + 'The actual delivery date for order ' + @theOrderMessageSegment + ' '

 if( @theActDeliveryDate is null )
 begin
 set @theMessageBody = @theMessageBody + 'removed.'
 end
 else if( @theOldActDeliveryDate is null )
 begin
 set @theMessageBody = @theMessageBody + 'set to ' + @theActDeliveryDate + '.'
 end
 else
 begin
 set @theMessageBody = @theMessageBody + 'changed from ' + @theOldActDeliveryDate + ' to ' + @theActDeliveryDate + '.'
 end
 end

 exec SendEmail
 @inProfileName = 'MoversSuiteDBMail',
 @inBccRecipients = @theMessageRecipents,
 @inSubject = @theMessageSubject,
 @inFormat = 'HTML',
 @inEmailBody = @theMessageBody,
 @inOrderId = @theOrderID
 end
end
GO

CREATE trigger [dbo].[OrdersTaskFieldChanged] on [dbo].[Orders]
after update
as
set nocount on
 if( update( CreatedOn ) or update( ApxMoveDate ) or update( EstDate ) or update( StartPack ) or update( EndPack ) or
 update( StartLoad ) or update( EndLoad ) or update( StartDeliv ) or update( EndDeliv ) or update( BookDate ) or
 update( ActDelDate ) or update( ReleaseDate ) or update( VanRegDt ) or update( ActLoadDate ) or update( AlertReceivedDate ) or
 update( ETA ) or update( AssignedOn ) )
 begin
 update ToDo set ToDo.DueDate =
 case DependencyDate.FieldName
 when 'CreatedOn' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.CreatedOn )
 when 'ApxMoveDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ApxMoveDate )
 when 'EstDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.EstDate )
 when 'StartPack' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.StartPack )
 when 'EndPack' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.EndPack )
 when 'StartLoad' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.StartLoad )
 when 'EndLoad' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.EndLoad )
 when 'StartDeliv' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.StartDeliv )
 when 'EndDeliv' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.EndDeliv )
 when 'BookDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.BookDate )
 when 'ActDelDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ActDelDate )
 when 'ReleaseDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ReleaseDate )
 when 'VanRegDt' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.VanRegDt )
 when 'ActLoadDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.ActLoadDate )
 when 'AlertReceivedDate' then dbo.udfTaskDueDate( ToDo.Duetype, ToDo.DueDays, inserted.AlertReceivedDate )
 when 'ETA' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.ETA )
 when 'AssignedOn' then dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, inserted.AssignedOn )
 else ToDo.DueDate
 end
 from ToDo
 inner join inserted on inserted.PriKey = ToDo.OrderID
 inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
 where DependencyDate.TableName = 'Orders'
 end
 if( update( Coordinator ) or update( LogisticCoordinator ) or update( OACoordinator ) or update( SalesPerson ) or update( RevenueClerk ) )
 begin
 update ToDo set TaskUserFID =
 case CoordinatorType.FieldName
 when 'Coordinator' then inserted.Coordinator
 when 'LogisticCoordinator' then inserted.LogisticCoordinator
 when 'OACoordinator' then inserted.OACoordinator
 when 'SalesPerson' then inserted.Salesperson
 when 'RevenueClerk' then inserted.RevenueClerk
 end
 from ToDo
 inner join inserted on inserted.PriKey = ToDo.OrderID
 inner join CoordinatorType on ToDo.CoordinatorTypeFID = CoordinatorType.CoordinatorTypeID
 where CoordinatorType.TableName = 'Orders'
 end
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [CK_Orders_OrderSeg] CHECK ((ltrim(isnull([OrderSeg],'A'))<>''))
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_ORDERS] PRIMARY KEY NONCLUSTERED ([PriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_ArchivedOrderNo] ON [dbo].[Orders] ([ArchivedOrderNo]) INCLUDE ([BranchPriKey], [CreatedOn], [PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_FirstName_BranchPriKey] ON [dbo].[Orders] ([BranchPriKey], [FirstName]) INCLUDE ([CreatedOn], [LastName], [OrderNo], [PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_BranchPriKey_LastName] ON [dbo].[Orders] ([BranchPriKey], [LastName]) INCLUDE ([CreatedOn], [FirstName], [OrderNo], [PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_ClaimAlertBy] ON [dbo].[Orders] ([ClaimAlertBy]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_Coordinator] ON [dbo].[Orders] ([Coordinator]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_CreatedBy] ON [dbo].[Orders] ([CreatedBy]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_CreatedOn] ON [dbo].[Orders] ([CreatedOn] DESC) INCLUDE ([FirstName], [LastName]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_EstimateNo] ON [dbo].[Orders] ([EstimateNo]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_LastName_FirstName] ON [dbo].[Orders] ([LastName], [FirstName]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_LogisticCoordinator] ON [dbo].[Orders] ([LogisticCoordinator]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OACoordinator] ON [dbo].[Orders] ([OACoordinator]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OASurveyor] ON [dbo].[Orders] ([OASurveyor]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderAuditInfoFID] ON [dbo].[Orders] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderNo] ON [dbo].[Orders] ([OrderNo]) INCLUDE ([BranchPriKey], [CreatedOn], [PriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg] ON [dbo].[Orders] ([OrderSeg], [VanLineFID], [PSeg], [SSeg], [OSeg]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderStatus] ON [dbo].[Orders] ([OrderStatus]) INCLUDE ([ActDelDate], [ActLoadDate], [AlertReceivedDate], [ApxMoveDate], [AssignedOn], [BookDate], [BranchPriKey], [Coordinator], [CreatedOn], [EndDeliv], [EndLoad], [EndPack], [EstDate], [ETA], [LastName], [LogisticCoordinator], [OACoordinator], [OrderNo], [PriKey], [ReleaseDate], [RevenueClerk], [SalesPerson], [StartDeliv], [StartLoad], [StartPack], [VanRegDt]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_PriKey_OrderSeg_OrderNo] ON [dbo].[Orders] ([PriKey], [OrderSeg], [OrderNo]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_RevenueClerk] ON [dbo].[Orders] ([RevenueClerk]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_RPPriKey] ON [dbo].[Orders] ([RPPriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_SalesPerson] ON [dbo].[Orders] ([SalesPerson]) ON [PRIMARY]
GO
CREATE STATISTICS [STATS_Orders_PriKey_OrderSeg_OrderNo] ON [dbo].[Orders] ([OrderSeg], [OrderNo], [PriKey])
GO
CREATE STATISTICS [STATS_Orders_PriKey_OrderNo] ON [dbo].[Orders] ([PriKey], [OrderNo])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_AccountProfile] FOREIGN KEY ([AccountProfileFID]) REFERENCES [dbo].[AccountProfiles] ([AccountProfileID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_ACCOUNTS] FOREIGN KEY ([AcctPriKey]) REFERENCES [dbo].[Accounts] ([AccountPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_AUTHORITYTYPES] FOREIGN KEY ([AuthPriKey]) REFERENCES [dbo].[AuthorityTypes] ([AuthPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_AutoClass] FOREIGN KEY ([AutoClassFID]) REFERENCES [dbo].[AutoClass] ([AutoClassID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_AutoMake] FOREIGN KEY ([AutoMakeFID]) REFERENCES [dbo].[AutoMake] ([AutoMakeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_AGENT2] FOREIGN KEY ([BookAgent]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_BookingAgentDivision] FOREIGN KEY ([BookingAgentDivisionFID]) REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_BRANCH] FOREIGN KEY ([BranchPriKey]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ClaimAlertBy] FOREIGN KEY ([ClaimAlertBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_ContractMaster] FOREIGN KEY ([CMPriKey]) REFERENCES [dbo].[ContractMaster] ([CMPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_CommType] FOREIGN KEY ([Commodity]) REFERENCES [dbo].[CommType] ([PriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ContactPreference] FOREIGN KEY ([ContactPreferenceFID]) REFERENCES [dbo].[ContactPreference] ([ContactPreferenceID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser] FOREIGN KEY ([Coordinator]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser5] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_AGENT1] FOREIGN KEY ([DestAgent]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_DestinationAgentDivision] FOREIGN KEY ([DestinationAgentDivisionFID]) REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_DestinationAgentMarket] FOREIGN KEY ([DestinationMarketFID]) REFERENCES [dbo].[Market] ([MarketID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Division] FOREIGN KEY ([DivisionFID]) REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_AGENT3] FOREIGN KEY ([HaulAgent]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_HaulAs] FOREIGN KEY ([HaulAsFID]) REFERENCES [dbo].[HaulAs] ([HaulAsID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_HaulingAgentDivision] FOREIGN KEY ([HaulingAgentDivisionFID]) REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_InvoiceRequirementType] FOREIGN KEY ([InvoiceRequirementTypeFID]) REFERENCES [dbo].[InvoiceRequirementType] ([InvoiceRequirementTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_JobCostingRevenueType] FOREIGN KEY ([JobCostingRevenueTypeFID]) REFERENCES [dbo].[JobCostingRevenueType] ([JobCostingRevenueTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_LeadType] FOREIGN KEY ([LeadType]) REFERENCES [dbo].[LeadType] ([PriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser4] FOREIGN KEY ([LogisticCoordinator]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_MOVETYPE] FOREIGN KEY ([MoveType]) REFERENCES [dbo].[MoveType] ([PriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser2] FOREIGN KEY ([OACoordinator]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser3] FOREIGN KEY ([OASurveyor]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_AGENT] FOREIGN KEY ([OrgAgent]) REFERENCES [dbo].[Agent] ([AgentPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_OriginAgentDivision] FOREIGN KEY ([OriginAgentDivisionFID]) REFERENCES [dbo].[Division] ([DivisionID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_OriginAgentMarket] FOREIGN KEY ([OriginMarketFID]) REFERENCES [dbo].[Market] ([MarketID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_PackType] FOREIGN KEY ([PackType]) REFERENCES [dbo].[PackType] ([PackTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_PayMethod] FOREIGN KEY ([PayMethodFID]) REFERENCES [dbo].[PayMethod] ([PayMethodID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_PayType] FOREIGN KEY ([PayTypeFID]) REFERENCES [dbo].[PayType] ([PayTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ProductCode] FOREIGN KEY ([ProductCodeFID]) REFERENCES [dbo].[ProductCode] ([ProductCodeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ProductType] FOREIGN KEY ([ProductTypeFID]) REFERENCES [dbo].[ProductType] ([ProductTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_PROFILES] FOREIGN KEY ([ProfileID]) REFERENCES [dbo].[Profiles] ([ProfileID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ReductionProfile] FOREIGN KEY ([ReductionProfileFID]) REFERENCES [dbo].[ReductionProfile] ([ReductionProfileID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Sysuser6] FOREIGN KEY ([RevenueClerk]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_ROLLOUTS] FOREIGN KEY ([RolloutID]) REFERENCES [dbo].[RollOuts] ([RollOutID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_RatePlans] FOREIGN KEY ([RPPriKey]) REFERENCES [dbo].[RatePlans] ([RPPriKey])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_ORDERS_Sysuser1] FOREIGN KEY ([SalesPerson]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ServiceType] FOREIGN KEY ([ServiceTypeFID]) REFERENCES [dbo].[ServiceType] ([ServiceTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ShipmentType] FOREIGN KEY ([ShipmentTypeFID]) REFERENCES [dbo].[ShipmentType] ([ShipmentTypeID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_StorageRequirement] FOREIGN KEY ([StorageRequirementFID]) REFERENCES [dbo].[StorageRequirement] ([StorageRequirementID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_Valuation] FOREIGN KEY ([ValuationFID]) REFERENCES [dbo].[Valuation] ([ValuationID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_ValuationPerPound] FOREIGN KEY ([ValuationPerPoundFID]) REFERENCES [dbo].[ValuationPerPound] ([ValuationPerPoundID])
GO
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [FK_Orders_VanLine] FOREIGN KEY ([VanLineFID]) REFERENCES [dbo].[VanLine] ([VLPriKey])
GO
GRANT SELECT ON [dbo].[Orders] TO [MssExec]
GRANT INSERT ON [dbo].[Orders] TO [MssExec]
GRANT DELETE ON [dbo].[Orders] TO [MssExec]
GRANT UPDATE ON [dbo].[Orders] TO [MssExec]
GO

	Name	Data Type	Max Length (Bytes)	Identity	Default
$Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNo$ $Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNo$ $Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$ $Indexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNo$ $Statistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKey$ $Statistics STATS_Orders_PriKey_OrderNo: PriKey\OrderNo$	PriKey	int	4	1 - 1
$Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOn$ $Indexes IX_Orders_LastName_FirstName: LastName\FirstName$ $Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	LastName	varchar(26)	26
$Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOn$ $Indexes IX_Orders_LastName_FirstName: LastName\FirstName$	FirstName	varchar(16)	16
$Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNo$ $Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$ $Indexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNo$ $Statistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKey$ $Statistics STATS_Orders_PriKey_OrderNo: PriKey\OrderNo$	OrderNo	varchar(23)	23
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	Coordinator	int	4
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	SalesPerson	int	4
	FromCounty	varchar(32)	32
	Email	nvarchar(120)	240
	ToCounty	varchar(32)	32
	MovingToCounty	varchar(32)	32
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	OrderStatus	varchar(25)	25
$Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNo$ $Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOn$ $Indexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNo$ $Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	CreatedOn	datetime	8
	CreatedBy	int	4
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	ApxMoveDate	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	EstDate	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	StartPack	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	EndPack	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	StartLoad	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	EndLoad	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	StartDeliv	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	EndDeliv	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	BookDate	datetime	8
	MoveType	int	4
	Commodity	int	4
	CubicFeet	float	8
	Weight	int	4
	Miles	int	4
	ActLineHaul	money	8
	LineHaul	money	8
	FirmPrice	varchar(1)	1
	Automobile	varchar(1)	1		('F')
	AutoModel	varchar(12)	12
	AutoYear	varchar(4)	4
	RelOrdNo	varchar(23)	23
	SelfHaul	varchar(1)	1		('')
	SHaulAuth	varchar(10)	10
	SHaulAutBy	int	4
	VanLineReg	varchar(1)	1
	VanRegBy	varchar(26)	26
	CreditAuth	varchar(30)	30
	EstAmt	float	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	ActDelDate	datetime	8
	ReleaseAgt	varchar(30)	30
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	ReleaseDate	datetime	8
	AcctPriKey	int	4
	IsOrder	varchar(1)	1
	IsForOrd	varchar(1)	1
	IsLead	varchar(1)	1
	OrgAgent	int	4
	DestAgent	int	4
	VanNum	varchar(12)	12
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	VanRegDt	datetime	8
	OrgAgentName	varchar(30)	30
	OrgAgentContact	varchar(30)	30
	OrgAgentMemo	varchar(60)	60
	DestAgentName	varchar(30)	30
	DestAgentContact	varchar(30)	30
	DestAgentMemo	varchar(60)	60
	SelfHaulAuthorization	varchar(26)	26
	AccountName	varchar(30)	30
	AccountContact	varchar(30)	30
	AccountMemo	varchar(120)	120
	EstimateNo	varchar(32)	32
	PackType	int	4
	LinearFeet	int	4
	FlatFloor	int	4
	TruckLoad	int	4
	VanRequirement	varchar(30)	30
	TeamRequired	int	4
	PurchaseOrderNo	varchar(30)	30
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	LogisticCoordinator	int	4
	ProfileID	int	4
	RolloutID	int	4
	Invno	varchar(10)	10
	InvDate	datetime	8
	ThirdPartyFreight	varchar(10)	10
	TeamPay	varchar(1)	1		(0)
	PackTime	datetime	8
	LoadTime	datetime	8
	DeliveryTime	datetime	8
	PrintInvoice	varchar(1)	1
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	OACoordinator	int	4
	OASurveyor	int	4
	BookAgent	int	4
	BookAgentName	varchar(30)	30
	BookAgentContact	varchar(30)	30
	BookAgentMemo	varchar(60)	60
	PreSold	varchar(1)	1
	InvoiceSuppNo	int	4
	PaperworkRecd	datetime	8
	CustomerNumber	varchar(15)	15
	ActualCost	float	8
	Company	varchar(26)	26
	LeadType	int	4
$Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSeg$ $Indexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNo$ $Statistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKey$	OrderSeg	varchar(16)	16
$Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSeg$	PSeg	varchar(1)	1
$Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSeg$	OSeg	varchar(1)	1
$Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSeg$	SSeg	varchar(1)	1
	SCNumPieces	int	4
	SCDeckingRequired	varchar(4)	4
	SCNumPads	int	4
	SCNumStraps	int	4
	SCVanSize	int	4
	SCBlanket	int	4
	SCCartoned	int	4
	SCCrated	int	4
	SCBubbleWrapped	int	4
	SCSkidded	int	4
	HaulAgent	int	4
	HaulAgentName	varchar(30)	30
	HaulAgentContact	varchar(30)	30
	HaulAgentMemo	varchar(60)	60
	ActPackDate	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	ActLoadDate	datetime	8
	Discount	float	8
	TariffContract	varchar(12)	12
	Section	varchar(4)	4
	ThirdPartyServices	int	4
	BillToContact	varchar(61)	61
	BillToAddr	varchar(256)	256
	BillToCity	varchar(35)	35
	BillToState	varchar(2)	2
	BillToZip	varchar(10)	10
	SSUserDefined1	varchar(30)	30
	SSUserDefined2	varchar(30)	30
	SSUserDefined3	varchar(30)	30
	SSUserDefined4	varchar(30)	30
	SSUserDefined5	varchar(30)	30
	SSUserDefined6	varchar(30)	30
	SSUserDefined7	varchar(30)	30
	SSUserDefined8	varchar(30)	30
	CSUserDefined1	varchar(30)	30
	CSUserDefined2	varchar(30)	30
	CSUserDefined3	varchar(30)	30
	CSUserDefined4	varchar(30)	30
	CSUserDefined5	varchar(30)	30
	CSUserDefined6	varchar(30)	30
	CSUserDefined7	varchar(30)	30
	CSUserDefined8	varchar(30)	30
	IntoStorage	datetime	8
	RPPriKey	int	4
	SentToQM	int	4
	QuickMoveID	int	4
	CMPriKey	int	4
	AuthPriKey	int	4
$Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNo$ $Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstName$ $Indexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastName$ $Indexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNo$ $Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	BranchPriKey	int	4
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	RevenueClerk	int	4
	ValuationAmount	money	8
	AutoVIN	varchar(32)	32
	AutoDescription	varchar(32)	32
	AutoWeight	int	4
	AutoClassFID	int	4
	WarehouseFlag	bit	1		(0)
	ShipmentTypeFID	int	4
	ServiceTypeFID	int	4
	RegistrationAuthorization	varchar(32)	32
	PreferredLoad	datetime	8
	PreferredPack	datetime	8
	PreferredDeliv	datetime	8
	OutsideSurveyor	varchar(32)	32
	ApplyPeakRateFlag	bit	1		(0)
	PreferredVanRequestFlag	bit	1		(0)
	StorageRequirementFID	int	4
	AutoWithShipmentFlag	bit	1		(0)
	AccountProfileFID	int	4
	DivisionFID	int	4
	OriginAgentDivisionFID	int	4
	DestinationAgentDivisionFID	int	4
	HaulingAgentDivisionFID	int	4
	BookingAgentDivisionFID	int	4
	ShipperDeductible	money	8		((0))
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	AlertReceivedDate	datetime	8
	ClaimAlertBy	int	4
	CustomerAddressCode	varchar(15)	15
	BillToCountryName	varchar(61)	61
	InvoiceRequirementTypeFID	int	4
	PackEndTime	datetime	8
	LoadEndTime	datetime	8
	DeliveryEndTime	datetime	8
$Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSeg$	VanLineFID	int	4
	PayTypeFID	int	4
	PayMethodFID	int	4
	ValuationFID	int	4
	ValuationPerPoundFID	int	4
	AutoMakeFID	int	4
	ProductTypeFID	int	4
	ProductCodeFID	int	4
	AutoLength	decimal(10,2)	9
	AutoWidth	decimal(10,2)	9
	AutoHeight	decimal(10,2)	9
	ReductionProfileFID	int	4
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	ETA	datetime	8
	ETATime	datetime	8
$Indexes IX_Orders_OrderStatus: PriKey\LastName\OrderNo\Coordinator\SalesPerson\CreatedOn\ApxMoveDate\EstDate\StartPack\EndPack\StartLoad\EndLoad\StartDeliv\EndDeliv\BookDate\ActDelDate\ReleaseDate\VanRegDt\LogisticCoordinator\OACoordinator\ActLoadDate\BranchPriKey\RevenueClerk\AlertReceivedDate\ETA\AssignedOn\OrderStatus$	AssignedOn	datetime	8
	AssignedOnTime	datetime	8
	BilledWeight	int	4
	EstimatedWeight	int	4
	HaulAsFID	int	4
	OriginShuttleMiles	int	4
	DestinationShuttleMiles	int	4
	AlternateWeight	int	4
	Archived	bit	1		((0))
	ArchivedOn	datetime	8
$Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNo$	ArchivedOrderNo	varchar(23)	23
	ArchivedEstimateNo	varchar(32)	32
	OriginAgreedDiscount	decimal(12,2)	9
	HaulingAgreedDiscount	decimal(12,2)	9
	DestinationAgreedDiscount	decimal(12,2)	9
	OriginAgreedDiscountLog	varchar(128)	128
	HaulingAgreedDiscountLog	varchar(128)	128
	DestinationAgreedDiscountLog	varchar(128)	128
	GrossMargin	decimal(14,4)	9
	ProfitOrLoss	decimal(12,2)	9
	JobCostingCalculated	bit	1		((0))
	JobCostingRevenueTypeFID	int	4
	JobCostingManualRevenue	decimal(12,2)	9
	JobCostingRevenueAmount	decimal(12,2)	9		((0))
	OrderPredatesJobCosting	bit	1		((0))
	SurveyStartTime	datetime	8
	SurveyEndTime	datetime	8
	OrderAuditInfoFID	bigint	8
	OrderAuditInfoManualCleanup	bigint	8
	OpenOrderInModule	varchar(20)	20		('Order Information')
	OriginMarketFID	int	4
	DestinationMarketFID	int	4
	AutoColor	varchar(30)	30
	AutoMileage	int	4
	AutoCubicFeet	float	8
	TenderedDate	datetime	8
	ContactPreferenceFID	int	4
	AccountLocationCode	varchar(15)	15

Name	Columns	Fill Factor
PK_ORDERS	PriKey	80
IX_Orders_ArchivedOrderNo	PriKey, CreatedOn, BranchPriKey, ArchivedOrderNo
IX_Orders_BranchPriKey_LastName	PriKey, FirstName, OrderNo, CreatedOn, BranchPriKey, LastName
IX_Orders_ClaimAlertBy	ClaimAlertBy
IX_Orders_Coordinator	Coordinator
IX_Orders_CreatedBy	CreatedBy
IX_Orders_CreatedOn	LastName, FirstName, CreatedOn
IX_Orders_EstimateNo	EstimateNo	80
IX_Orders_FirstName_BranchPriKey	PriKey, LastName, OrderNo, CreatedOn, BranchPriKey, FirstName
IX_Orders_LastName_FirstName	LastName, FirstName	80
IX_Orders_LogisticCoordinator	LogisticCoordinator
IX_Orders_OACoordinator	OACoordinator
IX_Orders_OASurveyor	OASurveyor
IX_Orders_OrderAuditInfoFID	OrderAuditInfoFID
IX_Orders_OrderNo	PriKey, CreatedOn, BranchPriKey, OrderNo	80
IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg	OrderSeg, VanLineFID, PSeg, SSeg, OSeg	80
IX_Orders_OrderStatus	PriKey, LastName, OrderNo, Coordinator, SalesPerson, CreatedOn, ApxMoveDate, EstDate, StartPack, EndPack, StartLoad, EndLoad, StartDeliv, EndDeliv, BookDate, ActDelDate, ReleaseDate, VanRegDt, LogisticCoordinator, OACoordinator, ActLoadDate, BranchPriKey, RevenueClerk, AlertReceivedDate, ETA, AssignedOn, OrderStatus
IX_Orders_PriKey_OrderSeg_OrderNo	PriKey, OrderSeg, OrderNo
IX_Orders_RevenueClerk	RevenueClerk
IX_Orders_RPPriKey	RPPriKey
IX_Orders_SalesPerson	SalesPerson