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,
[AccountClientID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccountSubAccount] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IncludeCommissionExpenses] [tinyint] NOT NULL CONSTRAINT [DF_Orders_IncludeCommissionExpenses] DEFAULT ((0)),
[IncludeThirdPartyPayables] [bit] NOT NULL CONSTRAINT [DF_Orders_IncludeThirdPartyPayables] DEFAULT ((0))
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE trigger [dbo].[OrderAuditFieldsChanged] on [dbo].[Orders]
after update
as
set nocount on
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
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
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
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
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
CREATE trigger [dbo].[OrdersDateChangeNotification] on [dbo].[Orders] after update
as
set nocount on
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
set @theDeliveryDateChanged = 0
if( isnull( @theOldActDeliveryDate, '' ) != isnull( @theActDeliveryDate, '' ) )
begin
set @theDeliveryDateChanged = 1
end
if( @theLoadDateChanged = 1 or @theDeliveryDateChanged = 1 )
begin
declare @theMessageRecipents varchar(max)
declare @theMessageBody varchar(max)
declare @theMessageSubject varchar(100)
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
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
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_IncludeCommissionExpenses] CHECK (([IncludeCommissionExpenses]>=(0) AND [IncludeCommissionExpenses]<=(2)))
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
[dbo].[Orders]
![Foreign Keys FK_ORDERS_Sysuser: [dbo].[Sysuser].Coordinator](../../Images/fk.gif)
![Check Constraints CK_Orders_OrderSeg : (ltrim(isnull([OrderSeg],'A'))<>'')](../../Images/c-constraint.gif)
