Tables [dbo].[Orders]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)43676
Created4:57:08 PM Thursday, September 7, 2006
Last Modified9:14:52 AM Thursday, May 23, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Primary Key PK_ORDERS: PriKeyIndexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNoIndexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNoIndexes 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\OrderStatusIndexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNoStatistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKeyStatistics STATS_Orders_PriKey_OrderNo: PriKey\OrderNoPriKeyint4
No
1 - 1
Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOnIndexes IX_Orders_LastName_FirstName: LastName\FirstNameIndexes 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\OrderStatusLastNamevarchar(26)26
No
Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOnIndexes IX_Orders_LastName_FirstName: LastName\FirstNameFirstNamevarchar(16)16
Yes
Indexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNoIndexes 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\OrderStatusIndexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNoStatistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKeyStatistics STATS_Orders_PriKey_OrderNo: PriKey\OrderNoOrderNovarchar(23)23
Yes
Foreign Keys FK_ORDERS_Sysuser: [dbo].[Sysuser].CoordinatorIndexes IX_Orders_Coordinator: CoordinatorIndexes 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\OrderStatusCoordinatorint4
Yes
Foreign Keys FK_ORDERS_Sysuser1: [dbo].[Sysuser].SalesPersonIndexes 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\OrderStatusIndexes IX_Orders_SalesPerson: SalesPersonSalesPersonint4
Yes
FromCountyvarchar(32)32
Yes
Emailnvarchar(120)240
Yes
ToCountyvarchar(32)32
Yes
MovingToCountyvarchar(32)32
Yes
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\OrderStatusOrderStatusvarchar(25)25
No
Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNoIndexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_CreatedOn: LastName\FirstName\CreatedOnIndexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNoIndexes 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\OrderStatusCreatedOndatetime8
No
Foreign Keys FK_ORDERS_Sysuser5: [dbo].[Sysuser].CreatedByIndexes IX_Orders_CreatedBy: CreatedByCreatedByint4
No
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\OrderStatusApxMoveDatedatetime8
Yes
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\OrderStatusEstDatedatetime8
Yes
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\OrderStatusStartPackdatetime8
Yes
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\OrderStatusEndPackdatetime8
Yes
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\OrderStatusStartLoaddatetime8
Yes
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\OrderStatusEndLoaddatetime8
Yes
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\OrderStatusStartDelivdatetime8
Yes
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\OrderStatusEndDelivdatetime8
Yes
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\OrderStatusBookDatedatetime8
Yes
Foreign Keys FK_ORDERS_MOVETYPE: [dbo].[MoveType].MoveTypeMoveTypeint4
Yes
Foreign Keys FK_Orders_CommType: [dbo].[CommType].CommodityCommodityint4
Yes
CubicFeetfloat8
Yes
Weightint4
Yes
Milesint4
Yes
ActLineHaulmoney8
Yes
LineHaulmoney8
Yes
FirmPricevarchar(1)1
Yes
Automobilevarchar(1)1
No
('F')
AutoModelvarchar(12)12
Yes
AutoYearvarchar(4)4
Yes
RelOrdNovarchar(23)23
Yes
SelfHaulvarchar(1)1
No
('')
SHaulAuthvarchar(10)10
Yes
SHaulAutByint4
Yes
VanLineRegvarchar(1)1
Yes
VanRegByvarchar(26)26
Yes
CreditAuthvarchar(30)30
Yes
EstAmtfloat8
Yes
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\OrderStatusActDelDatedatetime8
Yes
ReleaseAgtvarchar(30)30
Yes
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\OrderStatusReleaseDatedatetime8
Yes
Foreign Keys FK_ORDERS_ACCOUNTS: [dbo].[Accounts].AcctPriKeyAcctPriKeyint4
Yes
IsOrdervarchar(1)1
No
IsForOrdvarchar(1)1
No
IsLeadvarchar(1)1
No
Foreign Keys FK_ORDERS_AGENT: [dbo].[Agent].OrgAgentOrgAgentint4
Yes
Foreign Keys FK_ORDERS_AGENT1: [dbo].[Agent].DestAgentDestAgentint4
Yes
VanNumvarchar(12)12
Yes
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\OrderStatusVanRegDtdatetime8
Yes
OrgAgentNamevarchar(30)30
Yes
OrgAgentContactvarchar(30)30
Yes
OrgAgentMemovarchar(60)60
Yes
DestAgentNamevarchar(30)30
Yes
DestAgentContactvarchar(30)30
Yes
DestAgentMemovarchar(60)60
Yes
SelfHaulAuthorizationvarchar(26)26
Yes
AccountNamevarchar(30)30
Yes
AccountContactvarchar(30)30
Yes
AccountMemovarchar(120)120
Yes
Indexes IX_Orders_EstimateNo: EstimateNoEstimateNovarchar(32)32
Yes
Foreign Keys FK_ORDERS_PackType: [dbo].[PackType].PackTypePackTypeint4
Yes
LinearFeetint4
Yes
FlatFloorint4
Yes
TruckLoadint4
Yes
VanRequirementvarchar(30)30
Yes
TeamRequiredint4
Yes
PurchaseOrderNovarchar(30)30
Yes
Foreign Keys FK_ORDERS_Sysuser4: [dbo].[Sysuser].LogisticCoordinatorIndexes IX_Orders_LogisticCoordinator: LogisticCoordinatorIndexes 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\OrderStatusLogisticCoordinatorint4
Yes
Foreign Keys FK_ORDERS_PROFILES: [dbo].[Profiles].ProfileIDProfileIDint4
Yes
Foreign Keys FK_ORDERS_ROLLOUTS: [dbo].[RollOuts].RolloutIDRolloutIDint4
Yes
Invnovarchar(10)10
Yes
InvDatedatetime8
Yes
ThirdPartyFreightvarchar(10)10
Yes
TeamPayvarchar(1)1
Yes
(0)
PackTimedatetime8
Yes
LoadTimedatetime8
Yes
DeliveryTimedatetime8
Yes
PrintInvoicevarchar(1)1
Yes
Foreign Keys FK_ORDERS_Sysuser2: [dbo].[Sysuser].OACoordinatorIndexes IX_Orders_OACoordinator: OACoordinatorIndexes 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\OrderStatusOACoordinatorint4
Yes
Foreign Keys FK_ORDERS_Sysuser3: [dbo].[Sysuser].OASurveyorIndexes IX_Orders_OASurveyor: OASurveyorOASurveyorint4
Yes
Foreign Keys FK_ORDERS_AGENT2: [dbo].[Agent].BookAgentBookAgentint4
Yes
BookAgentNamevarchar(30)30
Yes
BookAgentContactvarchar(30)30
Yes
BookAgentMemovarchar(60)60
Yes
PreSoldvarchar(1)1
Yes
InvoiceSuppNoint4
Yes
PaperworkRecddatetime8
Yes
CustomerNumbervarchar(15)15
Yes
ActualCostfloat8
Yes
Companyvarchar(26)26
Yes
Foreign Keys FK_ORDERS_LeadType: [dbo].[LeadType].LeadTypeLeadTypeint4
Yes
Check Constraints CK_Orders_OrderSeg : (ltrim(isnull([OrderSeg],'A'))<>'')Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSegIndexes IX_Orders_PriKey_OrderSeg_OrderNo: PriKey\OrderSeg\OrderNoStatistics STATS_Orders_PriKey_OrderSeg_OrderNo: OrderSeg\OrderNo\PriKeyOrderSegvarchar(16)16
Yes
Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSegPSegvarchar(1)1
Yes
Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSegOSegvarchar(1)1
Yes
Indexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSegSSegvarchar(1)1
Yes
SCNumPiecesint4
Yes
SCDeckingRequiredvarchar(4)4
Yes
SCNumPadsint4
Yes
SCNumStrapsint4
Yes
SCVanSizeint4
Yes
SCBlanketint4
Yes
SCCartonedint4
Yes
SCCratedint4
Yes
SCBubbleWrappedint4
Yes
SCSkiddedint4
Yes
Foreign Keys FK_ORDERS_AGENT3: [dbo].[Agent].HaulAgentHaulAgentint4
Yes
HaulAgentNamevarchar(30)30
Yes
HaulAgentContactvarchar(30)30
Yes
HaulAgentMemovarchar(60)60
Yes
ActPackDatedatetime8
Yes
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\OrderStatusActLoadDatedatetime8
Yes
Discountfloat8
Yes
TariffContractvarchar(12)12
Yes
Sectionvarchar(4)4
Yes
ThirdPartyServicesint4
Yes
BillToContactvarchar(61)61
Yes
BillToAddrvarchar(256)256
Yes
BillToCityvarchar(35)35
Yes
BillToStatevarchar(2)2
Yes
BillToZipvarchar(10)10
Yes
SSUserDefined1varchar(30)30
Yes
SSUserDefined2varchar(30)30
Yes
SSUserDefined3varchar(30)30
Yes
SSUserDefined4varchar(30)30
Yes
SSUserDefined5varchar(30)30
Yes
SSUserDefined6varchar(30)30
Yes
SSUserDefined7varchar(30)30
Yes
SSUserDefined8varchar(30)30
Yes
CSUserDefined1varchar(30)30
Yes
CSUserDefined2varchar(30)30
Yes
CSUserDefined3varchar(30)30
Yes
CSUserDefined4varchar(30)30
Yes
CSUserDefined5varchar(30)30
Yes
CSUserDefined6varchar(30)30
Yes
CSUserDefined7varchar(30)30
Yes
CSUserDefined8varchar(30)30
Yes
IntoStoragedatetime8
Yes
Foreign Keys FK_ORDERS_RatePlans: [dbo].[RatePlans].RPPriKeyIndexes IX_Orders_RPPriKey: RPPriKeyRPPriKeyint4
Yes
SentToQMint4
Yes
QuickMoveIDint4
Yes
Foreign Keys FK_ORDERS_ContractMaster: [dbo].[ContractMaster].CMPriKeyCMPriKeyint4
Yes
Foreign Keys FK_ORDERS_AUTHORITYTYPES: [dbo].[AuthorityTypes].AuthPriKeyAuthPriKeyint4
Yes
Foreign Keys FK_ORDERS_BRANCH: [dbo].[Branch].BranchPriKeyIndexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNoIndexes IX_Orders_FirstName_BranchPriKey: PriKey\LastName\OrderNo\CreatedOn\BranchPriKey\FirstNameIndexes IX_Orders_BranchPriKey_LastName: PriKey\FirstName\OrderNo\CreatedOn\BranchPriKey\LastNameIndexes IX_Orders_OrderNo: PriKey\CreatedOn\BranchPriKey\OrderNoIndexes 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\OrderStatusBranchPriKeyint4
No
Foreign Keys FK_Orders_Sysuser6: [dbo].[Sysuser].RevenueClerkIndexes 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\OrderStatusIndexes IX_Orders_RevenueClerk: RevenueClerkRevenueClerkint4
Yes
ValuationAmountmoney8
Yes
AutoVINvarchar(32)32
Yes
AutoDescriptionvarchar(32)32
Yes
AutoWeightint4
Yes
Foreign Keys FK_Orders_AutoClass: [dbo].[AutoClass].AutoClassFIDAutoClassFIDint4
Yes
WarehouseFlagbit1
No
(0)
Foreign Keys FK_Orders_ShipmentType: [dbo].[ShipmentType].ShipmentTypeFIDShipmentTypeFIDint4
Yes
Foreign Keys FK_Orders_ServiceType: [dbo].[ServiceType].ServiceTypeFIDServiceTypeFIDint4
Yes
RegistrationAuthorizationvarchar(32)32
Yes
PreferredLoaddatetime8
Yes
PreferredPackdatetime8
Yes
PreferredDelivdatetime8
Yes
OutsideSurveyorvarchar(32)32
Yes
ApplyPeakRateFlagbit1
No
(0)
PreferredVanRequestFlagbit1
No
(0)
Foreign Keys FK_Orders_StorageRequirement: [dbo].[StorageRequirement].StorageRequirementFIDStorageRequirementFIDint4
Yes
AutoWithShipmentFlagbit1
No
(0)
Foreign Keys FK_Orders_AccountProfile: [dbo].[AccountProfiles].AccountProfileFIDAccountProfileFIDint4
Yes
Foreign Keys FK_Orders_Division: [dbo].[Division].DivisionFIDDivisionFIDint4
Yes
Foreign Keys FK_Orders_OriginAgentDivision: [dbo].[Division].OriginAgentDivisionFIDOriginAgentDivisionFIDint4
Yes
Foreign Keys FK_Orders_DestinationAgentDivision: [dbo].[Division].DestinationAgentDivisionFIDDestinationAgentDivisionFIDint4
Yes
Foreign Keys FK_Orders_HaulingAgentDivision: [dbo].[Division].HaulingAgentDivisionFIDHaulingAgentDivisionFIDint4
Yes
Foreign Keys FK_Orders_BookingAgentDivision: [dbo].[Division].BookingAgentDivisionFIDBookingAgentDivisionFIDint4
Yes
ShipperDeductiblemoney8
No
((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\OrderStatusAlertReceivedDatedatetime8
Yes
Foreign Keys FK_Orders_ClaimAlertBy: [dbo].[Sysuser].ClaimAlertByIndexes IX_Orders_ClaimAlertBy: ClaimAlertByClaimAlertByint4
Yes
CustomerAddressCodevarchar(15)15
Yes
BillToCountryNamevarchar(61)61
Yes
Foreign Keys FK_Orders_InvoiceRequirementType: [dbo].[InvoiceRequirementType].InvoiceRequirementTypeFIDInvoiceRequirementTypeFIDint4
No
PackEndTimedatetime8
Yes
LoadEndTimedatetime8
Yes
DeliveryEndTimedatetime8
Yes
Foreign Keys FK_Orders_VanLine: [dbo].[VanLine].VanLineFIDIndexes IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSeg: OrderSeg\VanLineFID\PSeg\SSeg\OSegVanLineFIDint4
Yes
Foreign Keys FK_Orders_PayType: [dbo].[PayType].PayTypeFIDPayTypeFIDint4
Yes
Foreign Keys FK_Orders_PayMethod: [dbo].[PayMethod].PayMethodFIDPayMethodFIDint4
Yes
Foreign Keys FK_Orders_Valuation: [dbo].[Valuation].ValuationFIDValuationFIDint4
Yes
Foreign Keys FK_Orders_ValuationPerPound: [dbo].[ValuationPerPound].ValuationPerPoundFIDValuationPerPoundFIDint4
Yes
Foreign Keys FK_Orders_AutoMake: [dbo].[AutoMake].AutoMakeFIDAutoMakeFIDint4
Yes
Foreign Keys FK_Orders_ProductType: [dbo].[ProductType].ProductTypeFIDProductTypeFIDint4
Yes
Foreign Keys FK_Orders_ProductCode: [dbo].[ProductCode].ProductCodeFIDProductCodeFIDint4
Yes
AutoLengthdecimal(10,2)9
Yes
AutoWidthdecimal(10,2)9
Yes
AutoHeightdecimal(10,2)9
Yes
Foreign Keys FK_Orders_ReductionProfile: [dbo].[ReductionProfile].ReductionProfileFIDReductionProfileFIDint4
Yes
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\OrderStatusETAdatetime8
Yes
ETATimedatetime8
Yes
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\OrderStatusAssignedOndatetime8
Yes
AssignedOnTimedatetime8
Yes
BilledWeightint4
Yes
EstimatedWeightint4
Yes
Foreign Keys FK_Orders_HaulAs: [dbo].[HaulAs].HaulAsFIDHaulAsFIDint4
Yes
OriginShuttleMilesint4
Yes
DestinationShuttleMilesint4
Yes
AlternateWeightint4
Yes
Archivedbit1
No
((0))
ArchivedOndatetime8
Yes
Indexes IX_Orders_ArchivedOrderNo: PriKey\CreatedOn\BranchPriKey\ArchivedOrderNoArchivedOrderNovarchar(23)23
Yes
ArchivedEstimateNovarchar(32)32
Yes
OriginAgreedDiscountdecimal(12,2)9
Yes
HaulingAgreedDiscountdecimal(12,2)9
Yes
DestinationAgreedDiscountdecimal(12,2)9
Yes
OriginAgreedDiscountLogvarchar(128)128
Yes
HaulingAgreedDiscountLogvarchar(128)128
Yes
DestinationAgreedDiscountLogvarchar(128)128
Yes
GrossMargindecimal(14,4)9
Yes
ProfitOrLossdecimal(12,2)9
Yes
JobCostingCalculatedbit1
No
((0))
Foreign Keys FK_Orders_JobCostingRevenueType: [dbo].[JobCostingRevenueType].JobCostingRevenueTypeFIDJobCostingRevenueTypeFIDint4
Yes
JobCostingManualRevenuedecimal(12,2)9
Yes
JobCostingRevenueAmountdecimal(12,2)9
No
((0))
OrderPredatesJobCostingbit1
No
((0))
SurveyStartTimedatetime8
Yes
SurveyEndTimedatetime8
Yes
Foreign Keys FK_Orders_OrderAuditInfo: [dbo].[OrderAuditInfo].OrderAuditInfoFIDIndexes IX_Orders_OrderAuditInfoFID: OrderAuditInfoFIDOrderAuditInfoFIDbigint8
Yes
OrderAuditInfoManualCleanupbigint8
Yes
OpenOrderInModulevarchar(20)20
Yes
('Order Information')
Foreign Keys FK_Orders_OriginAgentMarket: [dbo].[Market].OriginMarketFIDOriginMarketFIDint4
Yes
Foreign Keys FK_Orders_DestinationAgentMarket: [dbo].[Market].DestinationMarketFIDDestinationMarketFIDint4
Yes
AutoColorvarchar(30)30
Yes
AutoMileageint4
Yes
AutoCubicFeetfloat8
Yes
TenderedDatedatetime8
Yes
Foreign Keys FK_Orders_ContactPreference: [dbo].[ContactPreference].ContactPreferenceFIDContactPreferenceFIDint4
Yes
AccountLocationCodevarchar(15)15
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_ORDERS: PriKeyPK_ORDERSPriKey
Yes
80
IX_Orders_ArchivedOrderNoPriKey, CreatedOn, BranchPriKey, ArchivedOrderNo
IX_Orders_BranchPriKey_LastNamePriKey, FirstName, OrderNo, CreatedOn, BranchPriKey, LastName
IX_Orders_ClaimAlertByClaimAlertBy
IX_Orders_CoordinatorCoordinator
IX_Orders_CreatedByCreatedBy
IX_Orders_CreatedOnLastName, FirstName, CreatedOn
IX_Orders_EstimateNoEstimateNo80
IX_Orders_FirstName_BranchPriKeyPriKey, LastName, OrderNo, CreatedOn, BranchPriKey, FirstName
IX_Orders_LastName_FirstNameLastName, FirstName80
IX_Orders_LogisticCoordinatorLogisticCoordinator
IX_Orders_OACoordinatorOACoordinator
IX_Orders_OASurveyorOASurveyor
IX_Orders_OrderAuditInfoFIDOrderAuditInfoFID
IX_Orders_OrderNoPriKey, CreatedOn, BranchPriKey, OrderNo80
IX_Orders_OrderSeg_VanLine_PSeg_OSeg_SSegOrderSeg, VanLineFID, PSeg, SSeg, OSeg80
IX_Orders_OrderStatusPriKey, 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_OrderNoPriKey, OrderSeg, OrderNo
IX_Orders_RevenueClerkRevenueClerk
IX_Orders_RPPriKeyRPPriKey
IX_Orders_SalesPersonSalesPerson
Statistics Statistics
NameColumns
STATS_Orders_PriKey_OrderNoPriKey, OrderNo
STATS_Orders_PriKey_OrderSeg_OrderNoOrderSeg, OrderNo, PriKey
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
OrderAuditFieldsChanged
Yes
No
After Update
OrderAuditFieldsInitiallySet
Yes
No
After Insert
OrdersDateChangeNotification
Yes
No
After Update
OrdersTaskFieldChanged
Yes
No
After Update
Check Constraints Check Constraints
NameOn ColumnConstraint
CK_Orders_OrderSegOrderSeg(ltrim(isnull([OrderSeg],'A'))<>'')
Foreign Keys Foreign Keys
NameColumns
FK_Orders_AccountProfileAccountProfileFID->[dbo].[AccountProfiles].[AccountProfileID]
FK_ORDERS_ACCOUNTSAcctPriKey->[dbo].[Accounts].[AccountPriKey]
FK_ORDERS_AUTHORITYTYPESAuthPriKey->[dbo].[AuthorityTypes].[AuthPriKey]
FK_Orders_AutoClassAutoClassFID->[dbo].[AutoClass].[AutoClassID]
FK_Orders_AutoMakeAutoMakeFID->[dbo].[AutoMake].[AutoMakeID]
FK_ORDERS_AGENT2BookAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_BookingAgentDivisionBookingAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_ORDERS_BRANCHBranchPriKey->[dbo].[Branch].[BranchPriKey]
FK_Orders_ClaimAlertByClaimAlertBy->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_ContractMasterCMPriKey->[dbo].[ContractMaster].[CMPriKey]
FK_Orders_CommTypeCommodity->[dbo].[CommType].[PriKey]
FK_Orders_ContactPreferenceContactPreferenceFID->[dbo].[ContactPreference].[ContactPreferenceID]
FK_ORDERS_SysuserCoordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_Sysuser5CreatedBy->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_AGENT1DestAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_DestinationAgentDivisionDestinationAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_DestinationAgentMarketDestinationMarketFID->[dbo].[Market].[MarketID]
FK_Orders_DivisionDivisionFID->[dbo].[Division].[DivisionID]
FK_ORDERS_AGENT3HaulAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_HaulAsHaulAsFID->[dbo].[HaulAs].[HaulAsID]
FK_Orders_HaulingAgentDivisionHaulingAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_InvoiceRequirementTypeInvoiceRequirementTypeFID->[dbo].[InvoiceRequirementType].[InvoiceRequirementTypeID]
FK_Orders_JobCostingRevenueTypeJobCostingRevenueTypeFID->[dbo].[JobCostingRevenueType].[JobCostingRevenueTypeID]
FK_ORDERS_LeadTypeLeadType->[dbo].[LeadType].[PriKey]
FK_ORDERS_Sysuser4LogisticCoordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_MOVETYPEMoveType->[dbo].[MoveType].[PriKey]
FK_ORDERS_Sysuser2OACoordinator->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_Sysuser3OASurveyor->[dbo].[Sysuser].[SysUserID]
FK_Orders_OrderAuditInfoOrderAuditInfoFID->[dbo].[OrderAuditInfo].[OrderAuditInfoID]
FK_ORDERS_AGENTOrgAgent->[dbo].[Agent].[AgentPriKey]
FK_Orders_OriginAgentDivisionOriginAgentDivisionFID->[dbo].[Division].[DivisionID]
FK_Orders_OriginAgentMarketOriginMarketFID->[dbo].[Market].[MarketID]
FK_ORDERS_PackTypePackType->[dbo].[PackType].[PackTypeID]
FK_Orders_PayMethodPayMethodFID->[dbo].[PayMethod].[PayMethodID]
FK_Orders_PayTypePayTypeFID->[dbo].[PayType].[PayTypeID]
FK_Orders_ProductCodeProductCodeFID->[dbo].[ProductCode].[ProductCodeID]
FK_Orders_ProductTypeProductTypeFID->[dbo].[ProductType].[ProductTypeID]
FK_ORDERS_PROFILESProfileID->[dbo].[Profiles].[ProfileID]
FK_Orders_ReductionProfileReductionProfileFID->[dbo].[ReductionProfile].[ReductionProfileID]
FK_Orders_Sysuser6RevenueClerk->[dbo].[Sysuser].[SysUserID]
FK_ORDERS_ROLLOUTSRolloutID->[dbo].[RollOuts].[RollOutID]
FK_ORDERS_RatePlansRPPriKey->[dbo].[RatePlans].[RPPriKey]
FK_ORDERS_Sysuser1SalesPerson->[dbo].[Sysuser].[SysUserID]
FK_Orders_ServiceTypeServiceTypeFID->[dbo].[ServiceType].[ServiceTypeID]
FK_Orders_ShipmentTypeShipmentTypeFID->[dbo].[ShipmentType].[ShipmentTypeID]
FK_Orders_StorageRequirementStorageRequirementFID->[dbo].[StorageRequirement].[StorageRequirementID]
FK_Orders_ValuationValuationFID->[dbo].[Valuation].[ValuationID]
FK_Orders_ValuationPerPoundValuationPerPoundFID->[dbo].[ValuationPerPound].[ValuationPerPoundID]
FK_Orders_VanLineVanLineFID->[dbo].[VanLine].[VLPriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
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.<BR><BR>'
            end
            else if( @theOldActLoadDate is null )
            begin
                set @theMessageBody = @theMessageBody + 'set to ' + @theActLoadDate + '.<BR><BR>'
            end
            else
            begin
                set @theMessageBody = @theMessageBody + 'changed from ' + @theOldActLoadDate + ' to ' + @theActLoadDate + '.<BR><BR>'
            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
Uses
Used By