CREATE TABLE [dbo].[SCQContracts]
(
[QContractID] [int] NOT NULL IDENTITY(1, 1),
[CustomerID] [int] NOT NULL,
[QContractStatusID] [int] NOT NULL,
[ContactID] [int] NULL,
[Contact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Phone] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fax] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillToID] [int] NULL,
[ContractCodeID] [int] NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BillCodeID] [int] NULL,
[BillGroupID] [int] NULL,
[BranchID] [int] NULL,
[StartDate] [datetime] NOT NULL,
[ExpDate] [datetime] NULL,
[BaseStartBillingDate] [datetime] NULL,
[BaseBillingCycleID] [int] NULL,
[BaseArrears] [bit] NOT NULL,
[Bill] [bit] NOT NULL,
[SumIndividualBaseRates] [bit] NOT NULL,
[BaseRate] [money] NOT NULL,
[BaseRatePeriod] [int] NOT NULL,
[BasePreBillingNoDays] [int] NOT NULL,
[BasePreBillingNoMonths] [int] NOT NULL,
[OverageStartBillingDate] [datetime] NULL,
[OverageBillingCycleID] [int] NULL,
[GroupInvoices] [bit] NOT NULL,
[PONumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiscChargeTaxFlag] [int] NOT NULL,
[MiscChargeDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiscChargeGLID] [int] NULL,
[MiscChargeDeptID] [int] NULL,
[MiscContinuous] [bit] NOT NULL,
[MiscChargeBillCycleFlag] [int] NOT NULL CONSTRAINT [DF_SCQContracts_MiscChargeBillCycleFlag] DEFAULT ((3)),
[Remarks] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OneTimeRemark] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OneTimeRemark] DEFAULT (''),
[Notes] [varchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_Notes] DEFAULT (''),
[TermID] [int] NULL,
[SalesRepID] [int] NULL,
[JobID] [int] NULL,
[ChargeMethodID] [int] NULL,
[ChargeAccountID] [int] NULL,
[ServiceHourCodeID] [int] NULL,
[Renewable] [bit] NOT NULL,
[RenewalCycleID] [int] NULL,
[TaxCodeID] [int] NULL,
[Taxable] [bit] NOT NULL,
[UseIndividualTaxCodes] [bit] NOT NULL,
[LeaseSchedule] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SyncBillToAddress] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_SyncBillToAddress] DEFAULT ((0)),
[BillToAttn] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToAttn] DEFAULT (''),
[BillToAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToAddress] DEFAULT (''),
[BillToCity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToCity] DEFAULT (''),
[BillToState] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToState] DEFAULT (''),
[BillToZip] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToZip] DEFAULT (''),
[BillToCountry] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_BillToCountry] DEFAULT (''),
[EnableAgentUsageAlert] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_EnableAgentUsageAlert] DEFAULT ((1)),
[PooledUsageLimit] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_PooledUsageLimit] DEFAULT ((0)),
[ContractAdjCodeID] [int] NULL,
[AllowMeterEstimation] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_AllowMeterEstimation] DEFAULT ((1)),
[UseAlternateOvgBillTo] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_UseAlternateOvgBillTo] DEFAULT ((0)),
[OvgBillToID] [int] NULL,
[OvgBillToAttn] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToAttn] DEFAULT (''),
[OvgBillToAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToAddress] DEFAULT (''),
[OvgBillToCity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToCity] DEFAULT (''),
[OvgBillToState] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToState] DEFAULT (''),
[OvgBillToZip] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToZip] DEFAULT (''),
[OvgBillToCountry] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToCountry] DEFAULT (''),
[SyncOvgBillToAddress] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_SyncOvgBillToAddress] DEFAULT ((0)),
[OvgBillToTermID] [int] NULL,
[OvgBillToChargeMethodID] [int] NULL,
[OvgBillToChargeAccountID] [int] NULL,
[OvgBillToTaxable] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToTaxable] DEFAULT ((0)),
[OvgBillToTaxCodeID] [int] NULL,
[OvgBillToUseIndividualTaxCodes] [bit] NOT NULL CONSTRAINT [DF_SCQContracts_OvgBillToUseIndividualTaxCodes] DEFAULT ((0)),
[ContractID] [int] NULL,
[CreatorID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UpdatorID] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_SCQContracts_CreateDate] DEFAULT (getdate()),
[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_SCQContracts_LastUpdate] DEFAULT (getdate()),
[timestamp] [timestamp] NULL,
[MiscChargeCodeID] [int] NULL,
[MiscChargeQuantity] [decimal] (18, 6) NULL,
[MiscChargeRate] [decimal] (18, 6) NULL,
[BillingEnabled] [bit] NULL,
[BillFrequencyID] [int] NULL,
[DivisionID] [int] NULL,
[ReportDefinitionGroupID] [int] NULL,
[OvgBillToReportDefinitionGroupID] [int] NULL,
[BaseRatePeriodType] [int] NOT NULL CONSTRAINT [DF_SCQContracts_BaseRatePeriodType] DEFAULT ((1)),
[TaxExemptCodeID] [int] NULL,
[OvgBillToTaxExemptCodeID] [int] NULL,
[ContractNumber] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QContractErrorStatusID] [int] NULL CONSTRAINT [DF_SCQContracts_QContractErrorStatusID] DEFAULT ((15)),
[QContractWarningStatusID] [int] NULL CONSTRAINT [DF_SCQContracts_QContractWarningStatusID] DEFAULT ((15)),
[MiscChargeTaxFlagID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [PK_SCQContracts] PRIMARY KEY CLUSTERED ([QContractID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_BaseBillingCycleID_SCBillingCycles] FOREIGN KEY ([BaseBillingCycleID]) REFERENCES [dbo].[SCBillingCycles] ([BillingCycleID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_BillCodeID_SCBillCodes] FOREIGN KEY ([BillCodeID]) REFERENCES [dbo].[SCBillCodes] ([BillCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_BillGroupID_SCContractBillGroups] FOREIGN KEY ([BillGroupID]) REFERENCES [dbo].[SCContractBillGroups] ([GroupID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_BillToID_ARCustomers] FOREIGN KEY ([BillToID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_BranchID_GLBranches] FOREIGN KEY ([BranchID]) REFERENCES [dbo].[GLBranches] ([BranchID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ChargeAccountID_ARChargeAccounts] FOREIGN KEY ([ChargeAccountID]) REFERENCES [dbo].[ARChargeAccounts] ([ChargeAccountID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ChargeMethodID_ARChargeMethods] FOREIGN KEY ([ChargeMethodID]) REFERENCES [dbo].[ARChargeMethods] ([ChargeMethodID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ContactID_CMContacts] FOREIGN KEY ([ContactID]) REFERENCES [dbo].[CMContacts] ([ContactID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ContractAdjCodeID_SCContractAdjustmentCodes] FOREIGN KEY ([ContractAdjCodeID]) REFERENCES [dbo].[SCContractAdjustmentCodes] ([ContractAdjCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ContractCodeID_SCContractCodes] FOREIGN KEY ([ContractCodeID]) REFERENCES [dbo].[SCContractCodes] ([ContractCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ContractID_SCContracts] FOREIGN KEY ([ContractID]) REFERENCES [dbo].[SCContracts] ([ContractID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_CustomerID_ARCustomers] FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_DivisionID_GLDivisions] FOREIGN KEY ([DivisionID]) REFERENCES [dbo].[GLDivisions] ([DivisionID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_JobID_ICJobs] FOREIGN KEY ([JobID]) REFERENCES [dbo].[ICJobs] ([JobID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_MiscChargeCodeID_SCContractMiscChargeCodes] FOREIGN KEY ([MiscChargeCodeID]) REFERENCES [dbo].[SCContractMiscChargeCodes] ([MiscChargeCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_MiscChargeTaxFlagID_ShTaxFlags] FOREIGN KEY ([MiscChargeTaxFlagID]) REFERENCES [dbo].[ShTaxFlags] ([TaxFlagID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OverageBillingCycleID_SCBillingCycles] FOREIGN KEY ([OverageBillingCycleID]) REFERENCES [dbo].[SCBillingCycles] ([BillingCycleID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToChargeAccountID_ARChargeAccounts] FOREIGN KEY ([OvgBillToChargeAccountID]) REFERENCES [dbo].[ARChargeAccounts] ([ChargeAccountID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToChargeMethodID_ARChargeMethods] FOREIGN KEY ([OvgBillToChargeMethodID]) REFERENCES [dbo].[ARChargeMethods] ([ChargeMethodID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToID_ARCustomers] FOREIGN KEY ([OvgBillToID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToReportDefinitionGroupID_RPReportDefinitionGroups] FOREIGN KEY ([OvgBillToReportDefinitionGroupID]) REFERENCES [dbo].[RPReportDefinitionGroups] ([ReportDefinitionGroupID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToTaxCodeID_ShTaxCodes] FOREIGN KEY ([OvgBillToTaxCodeID]) REFERENCES [dbo].[ShTaxCodes] ([TaxCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToTaxExemptCodeID_SHTaxExemptCodes] FOREIGN KEY ([OvgBillToTaxExemptCodeID]) REFERENCES [dbo].[SHTaxExemptCodes] ([TaxExemptCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_OvgBillToTermID_ShTerms] FOREIGN KEY ([OvgBillToTermID]) REFERENCES [dbo].[ShTerms] ([TermID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_QContractErrorStatusID_SCQContractErrorStatuses] FOREIGN KEY ([QContractErrorStatusID]) REFERENCES [dbo].[SCQContractErrorStatuses] ([QContractErrorStatusID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_QContractStatusID_SCQContractStatuses] FOREIGN KEY ([QContractStatusID]) REFERENCES [dbo].[SCQContractStatuses] ([QContractStatusID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_QContractWarningStatusID_SCQContractWarningStatuses] FOREIGN KEY ([QContractWarningStatusID]) REFERENCES [dbo].[SCQContractWarningStatuses] ([QContractWarningStatusID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_RenewalCycleID_SCBillingCycles] FOREIGN KEY ([RenewalCycleID]) REFERENCES [dbo].[SCBillingCycles] ([BillingCycleID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ReportDefinitionGroupID_RPReportDefinitionGroups] FOREIGN KEY ([ReportDefinitionGroupID]) REFERENCES [dbo].[RPReportDefinitionGroups] ([ReportDefinitionGroupID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_SalesRepID_ARSalesReps] FOREIGN KEY ([SalesRepID]) REFERENCES [dbo].[ARSalesReps] ([SalesRepID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_ServiceHourCodeID_SHServiceHourCodes] FOREIGN KEY ([ServiceHourCodeID]) REFERENCES [dbo].[SHServiceHourCodes] ([ServiceHourCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_TaxCodeID_ShTaxCodes] FOREIGN KEY ([TaxCodeID]) REFERENCES [dbo].[ShTaxCodes] ([TaxCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_TaxExemptCodeID_SHTaxExemptCodes] FOREIGN KEY ([TaxExemptCodeID]) REFERENCES [dbo].[SHTaxExemptCodes] ([TaxExemptCodeID])
GO
ALTER TABLE [dbo].[SCQContracts] ADD CONSTRAINT [FK_SCQContracts_TermID_ShTerms] FOREIGN KEY ([TermID]) REFERENCES [dbo].[ShTerms] ([TermID])
GO