Tables [dbo].[ReductionTemplateDetail]
Properties
PropertyValue
HeapYes
Row Count (~)16
Created3:44:52 PM Tuesday, May 8, 2012
Last Modified7:26:34 PM Tuesday, June 11, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Primary Key PK_ReductionTemplateDetail: ReductionTemplateDetailIDReductionTemplateDetailIDint4
No
1 - 1
Foreign Keys FK_ReductionTemplateDetail_ReductionTemplateHeader: [dbo].[ReductionTemplateHeader].ReductionTemplateFIDReductionTemplateFIDint4
No
Foreign Keys FK_ReductionTemplateDetail_ReductionType: [dbo].[ReductionType].ReductionTypeFIDReductionTypeFIDint4
Yes
Foreign Keys FK_ReductionTemplateDetail_ReductionBasis: [dbo].[ReductionBasis].ReductionBasisFIDReductionBasisFIDint4
Yes
Percentagedecimal(10,3)9
Yes
ReductionAmountmoney8
No
((0))
Foreign Keys FK_RedutionTemplateDetail_ReductionTemplateRateLookup: [dbo].[ReductionTemplateRateLookup].ReductionTemplateRateLookupFIDReductionTemplateRateLookupFIDint4
Yes
EffectiveDateStartdatetime8
Yes
EffectiveDateEnddatetime8
Yes
Indexes Indexes
NameColumnsUnique
Primary Key PK_ReductionTemplateDetail: ReductionTemplateDetailIDPK_ReductionTemplateDetailReductionTemplateDetailID
Yes
Check Constraints Check Constraints
NameConstraint
CK_ReductionTemplateDetail_ValidBasis(NOT ([dbo].[GetReductionTypeID]('Percentage')=[ReductionTypeFID] AND [ReductionBasisFID] IS NULL))
CK_ReductionTemplateDetail_ValidEffectiveDates(isnull([EffectiveDateStart],(0))<isnull([EffectiveDateEnd],(100000)) AND (1)=[dbo].[ReductionValidEffectiveDates]([ReductionTemplateFID],[EffectiveDateStart],[EffectiveDateEnd]))
Foreign Keys Foreign Keys
NameColumns
FK_ReductionTemplateDetail_ReductionBasisReductionBasisFID->[dbo].[ReductionBasis].[ReductionBasisID]
FK_ReductionTemplateDetail_ReductionTemplateHeaderReductionTemplateFID->[dbo].[ReductionTemplateHeader].[ReductionTemplateID]
FK_RedutionTemplateDetail_ReductionTemplateRateLookupReductionTemplateRateLookupFID->[dbo].[ReductionTemplateRateLookup].[ReductionTemplateRateLookupID]
FK_ReductionTemplateDetail_ReductionTypeReductionTypeFID->[dbo].[ReductionType].[ReductionTypeID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses
Used By