CREATE TABLE [dbo].[ClaimSettlement]
(
[ClaimSettlementID] [int] NOT NULL IDENTITY(1, 1),
[ClaimFID] [int] NOT NULL,
[ClaimSettlementTypeFID] [int] NOT NULL,
[VendorNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PONumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InvoiceNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AssignedToVendorDate] [datetime] NULL,
[ShipperDeductibleApplyAmount] [money] NOT NULL CONSTRAINT [DF_ClaimSettlement_ShipperDeductibleApplyAmount] DEFAULT ((0)),
[1099Flag] [bit] NOT NULL CONSTRAINT [DF_ClaimSettlement_1099Flag] DEFAULT ((0)),
[Description] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[InvoiceDate] [datetime] NULL,
[DocDate] [datetime] NULL,
[JournalDate] [datetime] NULL,
[PostedDate] [datetime] NULL,
[PostedBySysuserFID] [int] NULL,
[DocumentNumber] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

create trigger [dbo].[ClaimSettlementTaskFieldChanged] on [dbo].[ClaimSettlement]
after update
as
set nocount on
    if( update( InvoiceDate ) or update( AssignedToVendorDate ) )
    begin
        update ToDo set ToDo.DueDate = dbo.udfTaskDueDate( ToDo.duetype, ToDo.duedays, dbo.GetClaimSettlementDateByClaimID( inserted.ClaimFID, DependencyDate.FieldName, ToDo.Identifier ) )
        from ToDo
        inner join Claim on Claim.OrdersFID = ToDo.OrderID
        inner join inserted on inserted.[Description] = ToDo.Identifier and inserted.ClaimFID = Claim.ClaimID
        inner join DependencyDate on ToDo.DependencyDateID = DependencyDate.DependencyDateID
        where DependencyDate.TableName = 'ClaimSettlement'
    end
GO
ALTER TABLE [dbo].[ClaimSettlement] ADD CONSTRAINT [PK_ClaimSettlement] PRIMARY KEY NONCLUSTERED  ([ClaimSettlementID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ClaimSettlement_ClaimDescription] ON [dbo].[ClaimSettlement] ([ClaimFID], [Description]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ClaimSettlement] ON [dbo].[ClaimSettlement] ([ClaimSettlementID]) INCLUDE ([ClaimSettlementTypeFID], [Description], [DocDate], [DocumentNumber], [InvoiceDate], [InvoiceNumber], [JournalDate], [PONumber], [PostedBySysuserFID], [PostedDate], [ShipperDeductibleApplyAmount], [VendorNumber]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_ClaimSettlement_PostedBySysuserFID] ON [dbo].[ClaimSettlement] ([PostedBySysuserFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClaimSettlement] ADD CONSTRAINT [FK_ClaimSettlement_Claim] FOREIGN KEY ([ClaimFID]) REFERENCES [dbo].[Claim] ([ClaimID]) ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ClaimSettlement] ADD CONSTRAINT [FK_ClaimSettlement_ClaimSettlementType] FOREIGN KEY ([ClaimSettlementTypeFID]) REFERENCES [dbo].[ClaimSettlementType] ([ClaimSettlementTypeID])
GO
ALTER TABLE [dbo].[ClaimSettlement] ADD CONSTRAINT [FK_ClaimSettlement_PostedbySysuser] FOREIGN KEY ([PostedBySysuserFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
GRANT SELECT ON  [dbo].[ClaimSettlement] TO [MssExec]
GRANT INSERT ON  [dbo].[ClaimSettlement] TO [MssExec]
GRANT DELETE ON  [dbo].[ClaimSettlement] TO [MssExec]
GRANT UPDATE ON  [dbo].[ClaimSettlement] TO [MssExec]
GO