CREATE TABLE [dbo].[ShipmentStatusHistory]
(
[ShipmentStatusHistoryID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[DateTime] [datetime] NOT NULL,
[ShipmentStatusFID] [int] NOT NULL,
[ChangedBySysuserFID] [int] NOT NULL,
[Description] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VanNo] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DriverID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DriverName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DriverCellPhone] [dbo].[PhoneCombined] NULL,
[VanlineUserInitials] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[KeyDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ShipmentStatusHistory] ADD CONSTRAINT [PK_ShipmentStatusHistory] PRIMARY KEY NONCLUSTERED  ([ShipmentStatusHistoryID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ShipmentStatusHistory_ChangedBySysuserFID] ON [dbo].[ShipmentStatusHistory] ([ChangedBySysuserFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ShipmentStatusHistory_OrderFID_ShipmentStatusHistoryID] ON [dbo].[ShipmentStatusHistory] ([OrderFID], [ShipmentStatusHistoryID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ShipmentStatusHistory] ADD CONSTRAINT [FK_ShipmentStatusHistory_Sysuser] FOREIGN KEY ([ChangedBySysuserFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[ShipmentStatusHistory] ADD CONSTRAINT [FK_ShipmentStatusHistory_Orders] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[ShipmentStatusHistory] ADD CONSTRAINT [FK_ShipmentStatusHistory_ShipmentStatus] FOREIGN KEY ([ShipmentStatusFID]) REFERENCES [dbo].[ShipmentStatus] ([ShipmentStatusID])
GO
GRANT SELECT ON  [dbo].[ShipmentStatusHistory] TO [MssExec]
GRANT INSERT ON  [dbo].[ShipmentStatusHistory] TO [MssExec]
GRANT DELETE ON  [dbo].[ShipmentStatusHistory] TO [MssExec]
GRANT UPDATE ON  [dbo].[ShipmentStatusHistory] TO [MssExec]
GO
EXEC sp_addextendedproperty N'Description', N'Holds a history of changes to vanline shipment status for a particular order.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', NULL, NULL
GO
EXEC sp_addextendedproperty N'Description', N'Foreign key to a moverssuite user that changed the vanline shipment status.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'ChangedBySysuserFID'
GO
EXEC sp_addextendedproperty N'Description', N'Date and time that a status update occurred.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'DateTime'
GO
EXEC sp_addextendedproperty N'Description', N'Description of a change to vanline shipment status.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'Description'
GO
EXEC sp_addextendedproperty N'Description', N'Cell phone of a driver associated to a dispatch.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'DriverCellPhone'
GO
EXEC sp_addextendedproperty N'Description', N'ID of a driver associated to a dispatch.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'DriverID'
GO
EXEC sp_addextendedproperty N'Description', N'Name of a driver associated to a dispatch.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'DriverName'
GO
EXEC sp_addextendedproperty N'Description', N'Key date generated from shipment status activity.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'KeyDate'
GO
EXEC sp_addextendedproperty N'Description', N'Foreign key to an order.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'OrderFID'
GO
EXEC sp_addextendedproperty N'Description', N'Foreign key to a vanline shipment status.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'ShipmentStatusFID'
GO
EXEC sp_addextendedproperty N'Description', N'Primary key of the ShipmentStatusHistory table.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'ShipmentStatusHistoryID'
GO
EXEC sp_addextendedproperty N'Description', N'Van number associated to a dispatch.', 'SCHEMA', N'dbo', 'TABLE', N'ShipmentStatusHistory', 'COLUMN', N'VanNo'
GO