Tables [dbo].[CTBaseData]
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)0
Created12:56:15 AM Thursday, March 21, 2019
Last Modified1:10:43 AM Thursday, March 21, 2019
KeyNameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Cluster Primary Key PK_CTBaseData: BaseDataIDBaseDataIDint4
False
1 - 1
Foreign Keys FK_CTBASEDATA_StateID_CTCommissionStates: [dbo].[CTCommissionStates].StateIDStateIDint4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsCommissionPeriodint4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsInvoiceIDint4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsSourceID1int4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsSourceID2int4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsForeign Keys FK_CTBaseData_CommissionTypeID_CTCommissionTypes: [dbo].[CTCommissionTypes].CommissionTypeIDCommissionTypeIDint4
False
Indexes IX_CTBaseData_CommissionPeriod_6ColsSalesRepIDint4
False
RateIDint4
False
BillingPeriodint4
False
ActualPeriodint4
False
IsFromSplitbit1
False
((0))
ToBeProcessedbit1
False
((1))
InvHasEquipmentbit1
False
((0))
InvoiceDatedatetime8
False
InvoicePeriodint4
False
ItemIDint4
True
EquipmentIDint4
True
SerialNumbervarchar(32)32
True
Descriptionvarchar(255)255
False
Quantitydecimal(18,6)9
False
Costmoney8
False
Revenuemoney8
False
Outcostmoney8
True
TransactionTypevarchar(64)64
True
ApplyToIDint4
True
ContractAgeint4
True
BoardAmountmoney8
False
CommissionableAmountmoney8
False
Activebit1
False
((1))
CreatorIDchar(8)8
False
UpdaterIDchar(8)8
False
CreateDatedatetime8
False
LastUpdatedatetime8
False
timestamptimestamp8
False
ContractIDint4
True
DetailOrdinalint4
False
((1))
KeyNameKey ColumnsUniqueFill Factor
Cluster Primary Key PK_CTBaseData: BaseDataIDPK_CTBaseDataBaseDataID
True
80
IX_CTBaseData_CommissionPeriod_6ColsCommissionPeriod, InvoiceID, SourceID1, SourceID2, CommissionTypeID, SalesRepID
True
80
NameColumns
FK_CTBaseData_CommissionTypeID_CTCommissionTypesCommissionTypeID->[dbo].[CTCommissionTypes].[CommissionTypeID]
FK_CTBASEDATA_StateID_CTCommissionStatesStateID->[dbo].[CTCommissionStates].[StateID]
CREATE TABLE [dbo].[CTBaseData]
(
[BaseDataID] [int] NOT NULL IDENTITY(1, 1),
[StateID] [int] NOT NULL,
[CommissionPeriod] [int] NOT NULL,
[InvoiceID] [int] NOT NULL,
[SourceID1] [int] NOT NULL,
[SourceID2] [int] NOT NULL,
[CommissionTypeID] [int] NOT NULL,
[SalesRepID] [int] NOT NULL,
[RateID] [int] NOT NULL,
[BillingPeriod] [int] NOT NULL,
[ActualPeriod] [int] NOT NULL,
[IsFromSplit] [bit] NOT NULL CONSTRAINT [DF_CTBaseData_IsFromSplit] DEFAULT ((0)),
[ToBeProcessed] [bit] NOT NULL CONSTRAINT [DF_CTBaseData_ToBeProcessed] DEFAULT ((1)),
[InvHasEquipment] [bit] NOT NULL CONSTRAINT [DF_CTBaseData_InvHasEquipment] DEFAULT ((0)),
[InvoiceDate] [datetime] NOT NULL,
[InvoicePeriod] [int] NOT NULL,
[ItemID] [int] NULL,
[EquipmentID] [int] NULL,
[SerialNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [decimal] (18, 6) NOT NULL,
[Cost] [money] NOT NULL,
[Revenue] [money] NOT NULL,
[Outcost] [money] NULL,
[TransactionType] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ApplyToID] [int] NULL,
[ContractAge] [int] NULL,
[BoardAmount] [money] NOT NULL,
[CommissionableAmount] [money] NOT NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_CTBaseData_Active] DEFAULT ((1)),
[CreatorID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UpdaterID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastUpdate] [datetime] NOT NULL,
[timestamp] [timestamp] NOT NULL,
[ContractID] [int] NULL,
[DetailOrdinal] [int] NOT NULL CONSTRAINT [DF_CTBaseData_DetailOrdinal] DEFAULT ((1))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CTBaseData] ADD CONSTRAINT [PK_CTBaseData] PRIMARY KEY CLUSTERED  ([BaseDataID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CTBaseData] ADD CONSTRAINT [IX_CTBaseData_CommissionPeriod_6Cols] UNIQUE NONCLUSTERED  ([CommissionPeriod], [InvoiceID], [SourceID1], [SourceID2], [CommissionTypeID], [SalesRepID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CTBaseData] ADD CONSTRAINT [FK_CTBaseData_CommissionTypeID_CTCommissionTypes] FOREIGN KEY ([CommissionTypeID]) REFERENCES [dbo].[CTCommissionTypes] ([CommissionTypeID])
GO
ALTER TABLE [dbo].[CTBaseData] ADD CONSTRAINT [FK_CTBASEDATA_StateID_CTCommissionStates] FOREIGN KEY ([StateID]) REFERENCES [dbo].[CTCommissionStates] ([StateID])
GO