CREATE TABLE [dbo].[ReductionTemplateDetail]
(
[ReductionTemplateDetailID] [int] NOT NULL IDENTITY(1, 1),
[ReductionTemplateFID] [int] NOT NULL,
[ReductionTypeFID] [int] NULL,
[ReductionBasisFID] [int] NULL,
[Percentage] [decimal] (10, 3) NULL,
[ReductionAmount] [money] NOT NULL CONSTRAINT [DF_ReductionTemplateDetail_ReductionAmount] DEFAULT ((0)),
[ReductionTemplateRateLookupFID] [int] NULL,
[EffectiveDateStart] [datetime] NULL,
[EffectiveDateEnd] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [CK_ReductionTemplateDetail_ValidBasis] CHECK ((NOT ([dbo].[GetReductionTypeID]('Percentage')=[ReductionTypeFID] AND [ReductionBasisFID] IS NULL)))
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [CK_ReductionTemplateDetail_ValidEffectiveDates] CHECK ((isnull([EffectiveDateStart],(0))<isnull([EffectiveDateEnd],(100000)) AND (1)=[dbo].[ReductionValidEffectiveDates]([ReductionTemplateFID],[EffectiveDateStart],[EffectiveDateEnd])))
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [PK_ReductionTemplateDetail] PRIMARY KEY NONCLUSTERED  ([ReductionTemplateDetailID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [FK_ReductionTemplateDetail_ReductionBasis] FOREIGN KEY ([ReductionBasisFID]) REFERENCES [dbo].[ReductionBasis] ([ReductionBasisID])
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [FK_ReductionTemplateDetail_ReductionTemplateHeader] FOREIGN KEY ([ReductionTemplateFID]) REFERENCES [dbo].[ReductionTemplateHeader] ([ReductionTemplateID])
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [FK_RedutionTemplateDetail_ReductionTemplateRateLookup] FOREIGN KEY ([ReductionTemplateRateLookupFID]) REFERENCES [dbo].[ReductionTemplateRateLookup] ([ReductionTemplateRateLookupID])
GO
ALTER TABLE [dbo].[ReductionTemplateDetail] ADD CONSTRAINT [FK_ReductionTemplateDetail_ReductionType] FOREIGN KEY ([ReductionTypeFID]) REFERENCES [dbo].[ReductionType] ([ReductionTypeID])
GO
GRANT SELECT ON  [dbo].[ReductionTemplateDetail] TO [MssExec]
GRANT INSERT ON  [dbo].[ReductionTemplateDetail] TO [MssExec]
GRANT DELETE ON  [dbo].[ReductionTemplateDetail] TO [MssExec]
GRANT UPDATE ON  [dbo].[ReductionTemplateDetail] TO [MssExec]
GO