CREATE TABLE [dbo].[Tasks]
(
[TaskID] [int] NOT NULL IDENTITY(1, 1),
[ProfileID] [int] NOT NULL,
[TaskDescription] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CoordinatorTypeID] [int] NULL,
[DependencyDateID] [int] NULL,
[DueDays] [int] NOT NULL,
[DueType] [int] NOT NULL,
[IsActive] [smallint] NULL,
[FreeTask] [smallint] NOT NULL CONSTRAINT [DF_Tasks_FreeTask] DEFAULT ((0)),
[FreeTaskDate] [datetime] NULL,
[SysUserFID] [int] NULL,
[TaskPriorityFID] [int] NULL,
[DependencyTaskFID] [int] NULL,
[DocumentTypeFID] [int] NULL,
[SourceAncestorTaskFID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [CK_Tasks_CoordinatorType_SysUser] CHECK (([CoordinatorTypeID] IS NULL AND [SysUserFID] IS NULL OR [CoordinatorTypeID] IS NULL AND [SysUserFID] IS NOT NULL OR [CoordinatorTypeID] IS NOT NULL AND [SysUserFID] IS NULL))
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [PK_TASKS] PRIMARY KEY CLUSTERED  ([TaskID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Tasks_CoordinatorTypeID] ON [dbo].[Tasks] ([CoordinatorTypeID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Tasks_DependencyDateID] ON [dbo].[Tasks] ([DependencyDateID]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tasks_ProfileID_DocumentTypeFID] ON [dbo].[Tasks] ([ProfileID], [DocumentTypeFID]) WHERE ([DocumentTypeFID] IS NOT NULL) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Tasks_SysUserID] ON [dbo].[Tasks] ([SysUserFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_CoordinatorType] FOREIGN KEY ([CoordinatorTypeID]) REFERENCES [dbo].[CoordinatorType] ([CoordinatorTypeID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_DependencyDate] FOREIGN KEY ([DependencyDateID]) REFERENCES [dbo].[DependencyDate] ([DependencyDateID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_DependencyTask] FOREIGN KEY ([DependencyTaskFID]) REFERENCES [dbo].[Tasks] ([TaskID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_DocumentType] FOREIGN KEY ([DocumentTypeFID]) REFERENCES [dbo].[DocumentType] ([DocumentTypeID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_Profiles] FOREIGN KEY ([ProfileID]) REFERENCES [dbo].[Profiles] ([ProfileID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_SourceAncestorTask] FOREIGN KEY ([SourceAncestorTaskFID]) REFERENCES [dbo].[Tasks] ([TaskID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_SysUser] FOREIGN KEY ([SysUserFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Tasks] ADD CONSTRAINT [FK_Tasks_TaskPriority] FOREIGN KEY ([TaskPriorityFID]) REFERENCES [dbo].[TaskPriority] ([TaskPriorityID])
GO
GRANT SELECT ON  [dbo].[Tasks] TO [MssExec]
GRANT INSERT ON  [dbo].[Tasks] TO [MssExec]
GRANT DELETE ON  [dbo].[Tasks] TO [MssExec]
GRANT UPDATE ON  [dbo].[Tasks] TO [MssExec]
GO