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