CREATE TABLE [dbo].[CPDetail]
(
[CPDPriKey] [int] NOT NULL IDENTITY(1, 1),
[CPPriKey] [int] NOT NULL,
[CommissionAmount] [float] NOT NULL,
[CTPriKey] [int] NOT NULL,
[ICPriKey] [int] NOT NULL,
[IsDefault] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RPPriKey] [int] NULL,
[CMPriKey] [int] NULL,
[CBPriKey] [int] NULL,
[1099Flag] [bit] NOT NULL CONSTRAINT [DF_CPDetail_1099Flag] DEFAULT ((1)),
[EffectiveStart] [datetime] NULL,
[EffectiveEnd] [datetime] NULL,
[AccountFID] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionPlan_EffectiveStart_EffectiveEnd] CHECK ((NOT ([EffectiveStart] IS NOT NULL AND [EffectiveEnd] IS NOT NULL AND [EffectiveStart]>[EffectiveEnd])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionPlan_ItemCode_Account] CHECK (((1)=[dbo].[IsCommissionPlanItemCodeAccountUnique]([CPPriKey],[ICPriKey],[AccountFID],[EffectiveStart],[EffectiveEnd])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionPlan_ItemCode_Contract] CHECK (((1)=[dbo].[IsCommissionPlanItemCodeContractUnique]([CPPriKey],[ICPriKey],[CMPriKey],[EffectiveStart],[EffectiveEnd])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionPlan_ItemCode_Default] CHECK (((1)=[dbo].[IsCommissionPlanItemCodeDefaultUnique]([CPPriKey],[ICPriKey],[CMPriKey],[RPPriKey],[AccountFID],[EffectiveStart],[EffectiveEnd])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionPlan_ItemCode_RatePlan] CHECK (((1)=[dbo].[IsCommissionPlanItemCodeRatePlanUnique]([CPPriKey],[ICPriKey],[RPPriKey],[EffectiveStart],[EffectiveEnd])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [CK_CommissionType_CommissionBasis] CHECK (((1)=[dbo].[IsBasisSetForPercentageCommissionType]([CTPriKey],[CBPriKey])))
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [PK_CPDetail] PRIMARY KEY NONCLUSTERED  ([CPDPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [IX_CPMaster_ItemCode_Default] UNIQUE NONCLUSTERED  ([CPPriKey], [ICPriKey], [RPPriKey], [CMPriKey], [AccountFID], [EffectiveStart] DESC, [EffectiveEnd] DESC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CPDetail_CPMaster_ItemCode_Account] ON [dbo].[CPDetail] ([CPPriKey], [ICPriKey], [AccountFID], [EffectiveStart] DESC, [EffectiveEnd] DESC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CPDetail_CPMaster_ItemCode_Contract] ON [dbo].[CPDetail] ([CPPriKey], [ICPriKey], [CMPriKey], [EffectiveStart] DESC, [EffectiveEnd] DESC) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_CPDetail_CPMaster_ItemCode_RatePlan] ON [dbo].[CPDetail] ([CPPriKey], [ICPriKey], [RPPriKey], [EffectiveStart] DESC, [EffectiveEnd] DESC) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_Account] FOREIGN KEY ([AccountFID]) REFERENCES [dbo].[Accounts] ([AccountPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_CommissionBasis] FOREIGN KEY ([CBPriKey]) REFERENCES [dbo].[CommissionBasis] ([CBPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_ContractMaster] FOREIGN KEY ([CMPriKey]) REFERENCES [dbo].[ContractMaster] ([CMPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_CPMaster] FOREIGN KEY ([CPPriKey]) REFERENCES [dbo].[CPMaster] ([CPPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_CommissionType] FOREIGN KEY ([CTPriKey]) REFERENCES [dbo].[CommissionType] ([CTPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_ItemCode] FOREIGN KEY ([ICPriKey]) REFERENCES [dbo].[ItemCode] ([ICPriKey])
GO
ALTER TABLE [dbo].[CPDetail] ADD CONSTRAINT [FK_CPDetail_RatePlans] FOREIGN KEY ([RPPriKey]) REFERENCES [dbo].[RatePlans] ([RPPriKey])
GO
GRANT SELECT ON  [dbo].[CPDetail] TO [MssExec]
GRANT INSERT ON  [dbo].[CPDetail] TO [MssExec]
GRANT DELETE ON  [dbo].[CPDetail] TO [MssExec]
GRANT UPDATE ON  [dbo].[CPDetail] TO [MssExec]
GO