Tables [dbo].[CPDetail]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
HeapYes
Row Count (~)1549
Created4:57:09 PM Thursday, September 7, 2006
Last Modified7:30:03 PM Tuesday, September 24, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Primary Key PK_CPDetail: CPDPriKeyCPDPriKeyint4
No
1 - 1
Foreign Keys FK_CPDetail_CPMaster: [dbo].[CPMaster].CPPriKeyIndexes IX_CPDetail_CPMaster_ItemCode_Account: CPPriKey\ICPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_Contract: CPPriKey\ICPriKey\CMPriKey\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_RatePlan: CPPriKey\ICPriKey\RPPriKey\EffectiveStart\EffectiveEndCPPriKeyint4
No
CommissionAmountfloat8
No
Foreign Keys FK_CPDetail_CommissionType: [dbo].[CommissionType].CTPriKeyCTPriKeyint4
No
Foreign Keys FK_CPDetail_ItemCode: [dbo].[ItemCode].ICPriKeyIndexes IX_CPDetail_CPMaster_ItemCode_Account: CPPriKey\ICPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_Contract: CPPriKey\ICPriKey\CMPriKey\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_RatePlan: CPPriKey\ICPriKey\RPPriKey\EffectiveStart\EffectiveEndICPriKeyint4
No
IsDefaultvarchar(1)1
Yes
Foreign Keys FK_CPDetail_RatePlans: [dbo].[RatePlans].RPPriKeyIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_RatePlan: CPPriKey\ICPriKey\RPPriKey\EffectiveStart\EffectiveEndRPPriKeyint4
Yes
Foreign Keys FK_CPDetail_ContractMaster: [dbo].[ContractMaster].CMPriKeyIndexes IX_CPDetail_CPMaster_ItemCode_Contract: CPPriKey\ICPriKey\CMPriKey\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndCMPriKeyint4
Yes
Foreign Keys FK_CPDetail_CommissionBasis: [dbo].[CommissionBasis].CBPriKeyCBPriKeyint4
Yes
1099Flagbit1
No
((1))
Indexes IX_CPDetail_CPMaster_ItemCode_Account: CPPriKey\ICPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_Contract: CPPriKey\ICPriKey\CMPriKey\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_RatePlan: CPPriKey\ICPriKey\RPPriKey\EffectiveStart\EffectiveEndEffectiveStartdatetime8
Yes
Indexes IX_CPDetail_CPMaster_ItemCode_Account: CPPriKey\ICPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_Contract: CPPriKey\ICPriKey\CMPriKey\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPDetail_CPMaster_ItemCode_RatePlan: CPPriKey\ICPriKey\RPPriKey\EffectiveStart\EffectiveEndEffectiveEnddatetime8
Yes
Foreign Keys FK_CPDetail_Account: [dbo].[Accounts].AccountFIDIndexes IX_CPDetail_CPMaster_ItemCode_Account: CPPriKey\ICPriKey\AccountFID\EffectiveStart\EffectiveEndIndexes IX_CPMaster_ItemCode_Default: CPPriKey\ICPriKey\RPPriKey\CMPriKey\AccountFID\EffectiveStart\EffectiveEndAccountFIDint4
Yes
Indexes Indexes
NameColumnsUniqueFill Factor
Primary Key PK_CPDetail: CPDPriKeyPK_CPDetailCPDPriKey
Yes
80
IX_CPMaster_ItemCode_DefaultCPPriKey, ICPriKey, RPPriKey, CMPriKey, AccountFID, EffectiveStart, EffectiveEnd
Yes
IX_CPDetail_CPMaster_ItemCode_AccountCPPriKey, ICPriKey, AccountFID, EffectiveStart, EffectiveEnd
IX_CPDetail_CPMaster_ItemCode_ContractCPPriKey, ICPriKey, CMPriKey, EffectiveStart, EffectiveEnd
IX_CPDetail_CPMaster_ItemCode_RatePlanCPPriKey, ICPriKey, RPPriKey, EffectiveStart, EffectiveEnd
Check Constraints Check Constraints
NameConstraint
CK_CommissionPlan_EffectiveStart_EffectiveEnd(NOT ([EffectiveStart] IS NOT NULL AND [EffectiveEnd] IS NOT NULL AND [EffectiveStart]>[EffectiveEnd]))
CK_CommissionPlan_ItemCode_Account((1)=[dbo].[IsCommissionPlanItemCodeAccountUnique]([CPPriKey],[ICPriKey],[AccountFID],[EffectiveStart],[EffectiveEnd]))
CK_CommissionPlan_ItemCode_Contract((1)=[dbo].[IsCommissionPlanItemCodeContractUnique]([CPPriKey],[ICPriKey],[CMPriKey],[EffectiveStart],[EffectiveEnd]))
CK_CommissionPlan_ItemCode_Default((1)=[dbo].[IsCommissionPlanItemCodeDefaultUnique]([CPPriKey],[ICPriKey],[CMPriKey],[RPPriKey],[AccountFID],[EffectiveStart],[EffectiveEnd]))
CK_CommissionPlan_ItemCode_RatePlan((1)=[dbo].[IsCommissionPlanItemCodeRatePlanUnique]([CPPriKey],[ICPriKey],[RPPriKey],[EffectiveStart],[EffectiveEnd]))
CK_CommissionType_CommissionBasis((1)=[dbo].[IsBasisSetForPercentageCommissionType]([CTPriKey],[CBPriKey]))
Foreign Keys Foreign Keys
NameColumns
FK_CPDetail_AccountAccountFID->[dbo].[Accounts].[AccountPriKey]
FK_CPDetail_CommissionBasisCBPriKey->[dbo].[CommissionBasis].[CBPriKey]
FK_CPDetail_ContractMasterCMPriKey->[dbo].[ContractMaster].[CMPriKey]
FK_CPDetail_CPMasterCPPriKey->[dbo].[CPMaster].[CPPriKey]
FK_CPDetail_CommissionTypeCTPriKey->[dbo].[CommissionType].[CTPriKey]
FK_CPDetail_ItemCodeICPriKey->[dbo].[ItemCode].[ICPriKey]
FK_CPDetail_RatePlansRPPriKey->[dbo].[RatePlans].[RPPriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses