CREATE TABLE [dbo].[LocServ]
(
[PriKey] [int] NOT NULL IDENTITY(1, 1),
[OrdPriKey] [int] NOT NULL,
[ServiceDate] [datetime] NULL,
[WkTicketNo] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServiceID] [int] NOT NULL,
[JobStart] [datetime] NULL,
[JobEnd] [datetime] NULL,
[TotCrewHrs] [decimal] (10, 2) NULL,
[HrsPerMan] [decimal] (10, 2) NULL,
[ServStatus] [smallint] NULL,
[HrsDrv1Way] [decimal] (10, 2) NULL,
[Quantity] [int] NULL,
[Crew] [int] NULL,
[OriginDestination] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedOn] [datetime] NOT NULL,
[CreatedBy] [int] NOT NULL,
[LastEditedOn] [datetime] NULL,
[LastEditedBy] [int] NULL,
[SubmittedOn] [datetime] NULL,
[SubmittedBy] [int] NULL,
[ClearedOn] [datetime] NULL,
[ClearedBy] [int] NULL,
[BranchPriKey] [int] NOT NULL,
[OriginXtraStopFID] [int] NULL,
[DestXtraStopFID] [int] NULL,
[DispatchStatusFID] [int] NULL,
[ContainerizationStatusFID] [int] NULL,
[VoidedBy] [int] NULL,
[VoidedOn] [datetime] NULL,
[CartonCountSource] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CancelledBy] [int] NULL,
[CancelledOn] [datetime] NULL,
[RequestedStartTime] [datetime] NULL,
[RequestedEndTime] [datetime] NULL,
[OperationalPlanFID] [int] NULL,
[OriginOrderLocationFID] [int] NULL,
[DestinationOrderLocationFID] [int] NULL,
[Sort1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sort2] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CrewReportTime] [datetime] NULL,
[CrewReportLocationFID] [int] NULL,
[IsPlaceHolder] [bit] NOT NULL CONSTRAINT [DF_LocServ_IsPlaceHolder] DEFAULT ((0))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [CK_CartonCountSource] CHECK (([CartonCountSource]='S' OR [CartonCountSource]='O' OR [CartonCountSource]='M'))
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [PK_LOCSERV] PRIMARY KEY CLUSTERED  ([PriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_BranchPriKey_ServiceDate] ON [dbo].[LocServ] ([BranchPriKey], [ServiceDate]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_Branch_ServStatus] ON [dbo].[LocServ] ([BranchPriKey], [ServStatus]) INCLUDE ([SubmittedOn]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_ClearedBy] ON [dbo].[LocServ] ([ClearedBy]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_CreatedBy] ON [dbo].[LocServ] ([CreatedBy]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_DestXtraStopFID] ON [dbo].[LocServ] ([DestXtraStopFID]) INCLUDE ([PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_LastEditedBy] ON [dbo].[LocServ] ([LastEditedBy]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_OperationalPlanFID] ON [dbo].[LocServ] ([OperationalPlanFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_OrdPriKey] ON [dbo].[LocServ] ([OrdPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_OriginXtraStopFID] ON [dbo].[LocServ] ([OriginXtraStopFID]) INCLUDE ([PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_ServiceDate] ON [dbo].[LocServ] ([ServiceDate], [PriKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_LocServ_SubmittedBy] ON [dbo].[LocServ] ([SubmittedBy]) ON [PRIMARY]
GO
CREATE STATISTICS [STAT_LocServ_ServiceData] ON [dbo].[LocServ] ([ServiceDate], [PriKey])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_BRANCH] FOREIGN KEY ([BranchPriKey]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_Sysuser4] FOREIGN KEY ([CancelledBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_Sysuser3] FOREIGN KEY ([ClearedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_ContainerizationStatus] FOREIGN KEY ([ContainerizationStatusFID]) REFERENCES [dbo].[ContainerizationStatus] ([ContainerizationStatusID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_Sysuser] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_CrewReportLocation] FOREIGN KEY ([CrewReportLocationFID]) REFERENCES [dbo].[CrewReportLocation] ([CrewReportLocationID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_OrderLocations2] FOREIGN KEY ([DestinationOrderLocationFID]) REFERENCES [dbo].[OrderLocations] ([OrderLocationID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_XtraStop2] FOREIGN KEY ([DestXtraStopFID]) REFERENCES [dbo].[XtraStop] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_DispatchStatusFID] FOREIGN KEY ([DispatchStatusFID]) REFERENCES [dbo].[DispatchStatus] ([DispatchStatusID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_Sysuser1] FOREIGN KEY ([LastEditedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_OperationalPlan] FOREIGN KEY ([OperationalPlanFID]) REFERENCES [dbo].[OperationalPlan] ([OperationalPlanID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_ORDERS] FOREIGN KEY ([OrdPriKey]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_OrderLocations1] FOREIGN KEY ([OriginOrderLocationFID]) REFERENCES [dbo].[OrderLocations] ([OrderLocationID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_XtraStop1] FOREIGN KEY ([OriginXtraStopFID]) REFERENCES [dbo].[XtraStop] ([PriKey])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_ServType] FOREIGN KEY ([ServiceID]) REFERENCES [dbo].[ServType] ([ServiceID])
GO
ALTER TABLE [dbo].[LocServ] ADD CONSTRAINT [FK_LOCSERV_Sysuser2] FOREIGN KEY ([SubmittedBy]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
GRANT SELECT ON  [dbo].[LocServ] TO [MssExec]
GRANT INSERT ON  [dbo].[LocServ] TO [MssExec]
GRANT DELETE ON  [dbo].[LocServ] TO [MssExec]
GRANT UPDATE ON  [dbo].[LocServ] TO [MssExec]
GO