CREATE TABLE [dbo].[Jobs]
(
[JobsID] [int] NOT NULL IDENTITY(1, 1),
[OrdersPriKey] [int] NOT NULL,
[StoresID] [int] NULL,
[ClientJobNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LabelPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DCAgentID] [int] NULL,
[TruckNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LogisticsOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONo] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DCDeliveryConfirmed] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateOfDCContact] [datetime] NULL,
[CallToDCToDeliver] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstDayOf3DaySpread] [datetime] NULL,
[StoredAtStore] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DropSequence] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StoreContactDate] [datetime] NULL,
[Comments] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PODReturnDate] [datetime] NULL,
[AllActionsComplete] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceDate] [datetime] NULL,
[InstallDateTime] [datetime] NULL,
[SchedDCDeliveryDateL] [datetime] NULL,
[StoreContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TransFee] [money] NULL,
[InvoiceSuppNo] [int] NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[JobsTaskFieldChanged] on [dbo].[Jobs]
after update
as
set nocount on
    if( update( DateOfDCContact ) or update( FirstDayOf3DaySpread ) or update( StoreContactDate ) or update( PODReturnDate ) or update( InvoiceDate ) or
        update( InstallDatetime ) or update( SchedDCDeliveryDateL ) )
    begin
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetJobsDateByOrderAndType( ToDo.OrderID, inserted.JobsID, DependencyDate.FieldName ) )
        from ToDo
        inner join inserted on inserted.OrdersPriKey = ToDo.OrderID and inserted.ClientJobNo = ToDo.Identifier
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'Jobs'
    end
GO
ALTER TABLE [dbo].[Jobs] ADD CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED  ([JobsID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Jobs_OrdersPriKey] ON [dbo].[Jobs] ([OrdersPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Jobs_OrdersPriKey_ClientJobNo] ON [dbo].[Jobs] ([OrdersPriKey], [ClientJobNo]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Jobs] ADD CONSTRAINT [FK_Jobs_ORDERS] FOREIGN KEY ([OrdersPriKey]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[Jobs] ADD CONSTRAINT [FK_Jobs_Stores] FOREIGN KEY ([StoresID]) REFERENCES [dbo].[Stores] ([StoreID])
GO
GRANT SELECT ON  [dbo].[Jobs] TO [MssExec]
GRANT INSERT ON  [dbo].[Jobs] TO [MssExec]
GRANT DELETE ON  [dbo].[Jobs] TO [MssExec]
GRANT UPDATE ON  [dbo].[Jobs] TO [MssExec]
GO