CREATE TABLE [dbo].[SCReports]
(
[ReportID] [int] NOT NULL,
[ReportNumber] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ReportBillGroupID] [int] NOT NULL,
[EquipmentID] [int] NULL,
[CustomerID] [int] NOT NULL,
[BillToID] [int] NOT NULL,
[ContractID] [int] NULL,
[BSABillForServices] [bit] NOT NULL,
[BSALaborDiscount] [float] NULL,
[BSAMaterialsDiscount] [float] NULL,
[BillCodeID] [int] NOT NULL,
[CallID] [int] NULL,
[Date] [datetime] NOT NULL,
[DatePeriod] [int] NOT NULL,
[PostPeriod] [int] NULL,
[VoidPeriod] [int] NULL,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PONumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TermID] [int] NULL,
[TermDiscountRate] [float] NOT NULL,
[TermDiscountDate] [datetime] NULL,
[DueDate] [datetime] NOT NULL,
[SalesRepID] [int] NULL,
[MailToAttn] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToCity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToState] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToZip] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MailToCountry] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToAttn] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToCity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToState] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToZip] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ShipToCountry] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MeterDate] [datetime] NULL,
[MeterDisplay] [int] NULL,
[MeterActual] [int] NULL,
[MeterOverride] [bit] NOT NULL,
[CopyCredits] [int] NOT NULL,
[IncompleteDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IncompleteCodeID] [int] NULL,
[OpenDate] [datetime] NOT NULL,
[ResponseHours] [float] NOT NULL,
[RepairHours] [float] NOT NULL,
[DownHours] [float] NOT NULL,
[CloseDate] [datetime] NOT NULL,
[TravelBase] [money] NOT NULL,
[TravelHours] [float] NOT NULL CONSTRAINT [DF_SCReports_TravelHours] DEFAULT ((0)),
[TravelRate] [money] NOT NULL,
[Mileage] [float] NOT NULL CONSTRAINT [DF_SCReports_Mileage] DEFAULT ((0)),
[MileageRate] [money] NOT NULL,
[TravelGLID] [int] NOT NULL,
[TravelDeptID] [int] NULL,
[TravelAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_TravelAmount] DEFAULT ((0)),
[UseBurdenRate] [bit] NOT NULL,
[TravelCostPerHour] [money] NOT NULL,
[TravelCostPerMile] [money] NOT NULL,
[TravelCostGLID] [int] NOT NULL,
[TravelCostDeptID] [int] NULL,
[TravelCostAppliedGLID] [int] NOT NULL,
[TravelCostAppliedDeptID] [int] NULL,
[TravelCost] [money] NOT NULL CONSTRAINT [DF_SCReports_TravelCost] DEFAULT ((0)),
[MiscDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiscGLID] [int] NOT NULL,
[MiscDeptID] [int] NULL,
[MiscEarnedAmount] [money] NOT NULL,
[MiscAmount] [money] NOT NULL,
[EarnedAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_EarnedAmount] DEFAULT ((0)),
[Remarks] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TaxCodeID] [int] NULL,
[LaborAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_LaborAmount] DEFAULT ((0)),
[MaterialAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_MaterialAmount] DEFAULT ((0)),
[TaxAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_TaxAmount] DEFAULT ((0)),
[MeterID] [int] NULL,
[GLID] [int] NOT NULL,
[PostID] [int] NULL,
[VoidID] [int] NULL,
[PreventativeMaintenance] [bit] NOT NULL CONSTRAINT [DF_SCReports_PreventativeMaintenance] DEFAULT ((0)),
[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_SCReports_CreateDate] DEFAULT (getdate()),
[LastUpdate] [datetime] NOT NULL CONSTRAINT [DF_SCReports_LastUpdate] DEFAULT (getdate()),
[timestamp] [timestamp] NULL,
[OnHoldCodeID] [int] NULL,
[OnHoldReleaseDate] [datetime] NULL,
[OnHoldReleaserID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ResponseCalcOverrideDate] [datetime] NULL,
[JobID] [int] NULL,
[MeterReadingGroupID] [int] NULL,
[LocationID] [int] NULL,
[LocationNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationAddress] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationCity] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationState] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationZip] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationCountry] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationContact] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationFax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocationNotes] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DelayCodeID] [int] NULL,
[Taxable] [bit] NOT NULL CONSTRAINT [DF_SCReports_Taxable] DEFAULT ((1)),
[EquipDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EquipSerialNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EquipModelID] [int] NULL,
[MileageGLID] [int] NOT NULL CONSTRAINT [DF_SCReports_MileageGLID] DEFAULT ((0)),
[MileageDeptID] [int] NULL,
[MileageCostGLID] [int] NOT NULL CONSTRAINT [DF_SCReports_MileageCostGLID] DEFAULT ((0)),
[MileageCostDeptID] [int] NULL,
[MileageCostAppliedGLID] [int] NOT NULL CONSTRAINT [DF_SCReports_MileageCostAppliedGLID] DEFAULT ((0)),
[MileageCostAppliedDeptID] [int] NULL,
[MileageAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_MileageAmount] DEFAULT ((0)),
[MileageCost] [money] NOT NULL CONSTRAINT [DF_SCReports_MileageCost] DEFAULT ((0)),
[LaborCost] [money] NOT NULL CONSTRAINT [DF_SCReports_LaborCost] DEFAULT ((0)),
[MaterialCost] [money] NOT NULL CONSTRAINT [DF_SCReports_MaterialCost] DEFAULT ((0)),
[VoidMaterialCost] [money] NOT NULL CONSTRAINT [DF_SCReports_VoidMaterialCost] DEFAULT ((0)),
[UseStandardAdjustment] [bit] NOT NULL CONSTRAINT [DF_SCReports_UseStandardAdjustment] DEFAULT ((0)),
[CallTerritoryID] [int] NULL,
[BillableMileage] [decimal] (16, 4) NOT NULL CONSTRAINT [DF_SCReports_BillableMileage] DEFAULT ((0)),
[DivisionID] [int] NULL,
[BalanceSheetGroupID] [int] NULL,
[MaterialEarnedAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_MaterialEarnedAmount] DEFAULT ((0)),
[LaborEarnedAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_LaborEarnedAmount] DEFAULT ((0)),
[TravelEarnedAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_TravelEarnedAmount] DEFAULT ((0)),
[MileageEarnedAmount] [money] NOT NULL CONSTRAINT [DF_SCReports_MileageEarnedAmount] DEFAULT ((0)),
[TaxExemptCodeID] [int] NULL,
[MiscTaxFlag] [int] NOT NULL CONSTRAINT [DF_SCReports_MiscTaxFlag] DEFAULT ((0)),
[TravelTaxFlag] [int] NOT NULL,
[ItemID] [int] NULL,
[NetResolutionHours] [float] NOT NULL CONSTRAINT [DF_SCReports_NetResolutionHours] DEFAULT ((0)),
[MiscTaxFlagID] [int] NOT NULL CONSTRAINT [DF_SCReports_MiscTaxFlagID] DEFAULT ((1)),
[TravelTaxFlagID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [PK_SCReports] PRIMARY KEY CLUSTERED ([ReportID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_BillCodeID_SCBillCodes] FOREIGN KEY ([BillCodeID]) REFERENCES [dbo].[SCBillCodes] ([BillCodeID])
GO
ALTER TABLE [dbo].[SCReports] WITH NOCHECK ADD CONSTRAINT [FK_SCReports_BillToID_ARCustomers] FOREIGN KEY ([BillToID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_CallID_SCCalls] FOREIGN KEY ([CallID]) REFERENCES [dbo].[SCCalls] ([CallID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_CallTerritoryID_SCTerritories] FOREIGN KEY ([CallTerritoryID]) REFERENCES [dbo].[SCTerritories] ([TerritoryID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_ContractID_SCContracts] FOREIGN KEY ([ContractID]) REFERENCES [dbo].[SCContracts] ([ContractID])
GO
ALTER TABLE [dbo].[SCReports] WITH NOCHECK ADD CONSTRAINT [FK_SCReports_CustomerID_ARCustomers] FOREIGN KEY ([CustomerID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_DelayCodeID_SCDelayCodes] FOREIGN KEY ([DelayCodeID]) REFERENCES [dbo].[SCDelayCodes] ([DelayCodeID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_DivisionID_GLDivisions] FOREIGN KEY ([DivisionID]) REFERENCES [dbo].[GLDivisions] ([DivisionID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_EquipmentID_SCEquipments] FOREIGN KEY ([EquipmentID]) REFERENCES [dbo].[SCEquipments] ([EquipmentID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_EquipModelID_ICModels] FOREIGN KEY ([EquipModelID]) REFERENCES [dbo].[ICModels] ([ModelID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_IncompleteCodeID_SCIncompleteCodes] FOREIGN KEY ([IncompleteCodeID]) REFERENCES [dbo].[SCIncompleteCodes] ([IncompleteCodeID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_ItemID_ICItems] FOREIGN KEY ([ItemID]) REFERENCES [dbo].[ICItems] ([ItemID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_JobID_ICJobs] FOREIGN KEY ([JobID]) REFERENCES [dbo].[ICJobs] ([JobID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_LocationID_ARCustomers] FOREIGN KEY ([LocationID]) REFERENCES [dbo].[ARCustomers] ([CustomerID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MeterReadingGroupID_MTMeterReadingGroups] FOREIGN KEY ([MeterReadingGroupID]) REFERENCES [dbo].[MTMeterReadingGroups] ([MeterReadingGroupID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageCostAppliedDeptID_GLDepts] FOREIGN KEY ([MileageCostAppliedDeptID]) REFERENCES [dbo].[GLDepts] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageCostAppliedGLID_GLAccounts] FOREIGN KEY ([MileageCostAppliedGLID]) REFERENCES [dbo].[GLAccounts] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageCostDeptID_GLDepts] FOREIGN KEY ([MileageCostDeptID]) REFERENCES [dbo].[GLDepts] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageCostGLID_GLAccounts] FOREIGN KEY ([MileageCostGLID]) REFERENCES [dbo].[GLAccounts] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageDeptID_GLDepts] FOREIGN KEY ([MileageDeptID]) REFERENCES [dbo].[GLDepts] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MileageGLID_GLAccounts] FOREIGN KEY ([MileageGLID]) REFERENCES [dbo].[GLAccounts] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MiscDeptID_GLDepts4] FOREIGN KEY ([MiscDeptID]) REFERENCES [dbo].[GLDepts4] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MiscGLID_GLAccounts4] FOREIGN KEY ([MiscGLID]) REFERENCES [dbo].[GLAccounts4] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_MiscTaxFlagID_ShTaxFlags] FOREIGN KEY ([MiscTaxFlagID]) REFERENCES [dbo].[ShTaxFlags] ([TaxFlagID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_OnHoldCodeID_ShOnHoldCodes] FOREIGN KEY ([OnHoldCodeID]) REFERENCES [dbo].[ShOnHoldCodes] ([OnHoldCodeID])
GO
ALTER TABLE [dbo].[SCReports] WITH NOCHECK ADD CONSTRAINT [FK_SCReports_PostID_ARInvoices] FOREIGN KEY ([PostID]) REFERENCES [dbo].[ARInvoices] ([InvoiceID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_ReportBillGroupID_SCReportBillGroups] FOREIGN KEY ([ReportBillGroupID]) REFERENCES [dbo].[SCReportBillGroups] ([ReportBillGroupID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_SalesRepID_ARSalesReps] FOREIGN KEY ([SalesRepID]) REFERENCES [dbo].[ARSalesReps] ([SalesRepID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TaxCodeID_ShTaxCodes] FOREIGN KEY ([TaxCodeID]) REFERENCES [dbo].[ShTaxCodes] ([TaxCodeID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TaxExemptCodeID_SHTaxExemptCodes] FOREIGN KEY ([TaxExemptCodeID]) REFERENCES [dbo].[SHTaxExemptCodes] ([TaxExemptCodeID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TermID_ShTerms] FOREIGN KEY ([TermID]) REFERENCES [dbo].[ShTerms] ([TermID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelCostAppliedDeptID_GLDepts4] FOREIGN KEY ([TravelCostAppliedDeptID]) REFERENCES [dbo].[GLDepts4] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelCostAppliedGLID_GLAccounts4] FOREIGN KEY ([TravelCostAppliedGLID]) REFERENCES [dbo].[GLAccounts4] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelCostDeptID_GLDepts4] FOREIGN KEY ([TravelCostDeptID]) REFERENCES [dbo].[GLDepts4] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelCostGLID_GLAccounts4] FOREIGN KEY ([TravelCostGLID]) REFERENCES [dbo].[GLAccounts4] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelDeptID_GLDepts4] FOREIGN KEY ([TravelDeptID]) REFERENCES [dbo].[GLDepts4] ([DeptID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelGLID_GLAccounts4] FOREIGN KEY ([TravelGLID]) REFERENCES [dbo].[GLAccounts4] ([AccountID])
GO
ALTER TABLE [dbo].[SCReports] ADD CONSTRAINT [FK_SCReports_TravelTaxFlagID_ShTaxFlags] FOREIGN KEY ([TravelTaxFlagID]) REFERENCES [dbo].[ShTaxFlags] ([TaxFlagID])
GO
ALTER TABLE [dbo].[SCReports] WITH NOCHECK ADD CONSTRAINT [FK_SCReports_VoidID_ARInvoices] FOREIGN KEY ([VoidID]) REFERENCES [dbo].[ARInvoices] ([InvoiceID])
GO