CREATE TABLE [dbo].[Rates]
(
[RPriKey] [int] NOT NULL IDENTITY(1, 1),
[ICPriKey] [int] NOT NULL,
[Rate] [float] NULL,
[OTRate] [float] NULL,
[RTypePriKey] [int] NOT NULL,
[RPPriKey] [int] NOT NULL,
[RateMatrixFID] [int] NULL,
[EffectiveStart] [datetime] NULL,
[EffectiveEnd] [datetime] NULL,
[DoubleRate] [float] NULL,
[Section] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginStateFID] [int] NULL,
[DestinationStateFID] [int] NULL,
[OriginAirportFID] [int] NULL,
[DestinationAirportFID] [int] NULL,
[OriginCountryCodeFID] [int] NULL,
[DestinationCountryCodeFID] [int] NULL,
[OriginPostalCode] [dbo].[AddressPostalCode] NULL,
[DestinationPostalCode] [dbo].[AddressPostalCode] NULL,
[MinCharge] [float] NULL,
[MaxCharge] [float] NULL,
[Note] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ForeignID] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Rates_CreatedOn] DEFAULT (getutcdate())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
/**
*
*    This "instead of delete" trigger handles deletes for the Rates table.
*    Deletes are only performed if the RatePlan and ItemCode are not in use.
*/

CREATE trigger [dbo].[RatesDeleteTrigger] on [dbo].[Rates] instead of delete
as
    declare @theCanDeleteFlag bit
    -- If one row fails, then we abort the delete operation.
    set @theCanDeleteFlag = isnull( ( select top 1 0 from deleted where dbo.CanRateBeDeleted( deleted.ICPriKey, deleted.RPPriKey, deleted.CreatedOn ) = 0 ), 1 )

    if( @theCanDeleteFlag = 1 )
    begin
        delete from Rates
        from Rates
        inner join deleted on Rates.RPriKey = deleted.RPriKey
    end
    else
    begin
        raiserror( 'You cannot delete this Rate because the RatePlan and ItemCode are in use.', 16, 1 )
    end
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_EffectiveStartEnd] CHECK ((NOT ([EffectiveStart] IS NOT NULL AND [EffectiveEnd] IS NOT NULL AND [EffectiveStart]>[EffectiveEnd])))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidDestinationPoint] CHECK (([DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NOT NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NOT NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NOT NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NOT NULL))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidEffectiveDates] CHECK (((1)=[dbo].[RateValidEffectiveDates]([ICPriKey],[RPPriKey],[EffectiveStart],[EffectiveEnd],[Section],[OriginStateFID],[DestinationStateFID],[OriginAirportFID],[DestinationAirportFID],[OriginCountryCodeFID],[DestinationCountryCodeFID],[OriginPostalCode],[DestinationPostalCode])))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidOriginPoint] CHECK (([OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NOT NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NOT NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NOT NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NOT NULL))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [PK_Rates] PRIMARY KEY CLUSTERED  ([RPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [IX_Rates_RatePlanItemCode] UNIQUE NONCLUSTERED  ([RPPriKey], [ICPriKey], [EffectiveStart], [EffectiveEnd], [Section], [OriginStateFID], [DestinationStateFID], [OriginAirportFID], [DestinationAirportFID], [OriginCountryCodeFID], [DestinationCountryCodeFID], [OriginPostalCode], [DestinationPostalCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Rates_RateMatrix] ON [dbo].[Rates] ([RateMatrixFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Rates_RateType] ON [dbo].[Rates] ([RTypePriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationAirport] FOREIGN KEY ([DestinationAirportFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationCountryCode] FOREIGN KEY ([DestinationCountryCodeFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationState] FOREIGN KEY ([DestinationStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_ItemCode] FOREIGN KEY ([ICPriKey]) REFERENCES [dbo].[ItemCode] ([ICPriKey])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginAirport] FOREIGN KEY ([OriginAirportFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginCountryCode] FOREIGN KEY ([OriginCountryCodeFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginState] FOREIGN KEY ([OriginStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RateMatrix] FOREIGN KEY ([RateMatrixFID]) REFERENCES [dbo].[RateMatrix] ([RateMatrixID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RatePlans] FOREIGN KEY ([RPPriKey]) REFERENCES [dbo].[RatePlans] ([RPPriKey])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RateTypes] FOREIGN KEY ([RTypePriKey]) REFERENCES [dbo].[RateTypes] ([RTypePriKey])
GO
GRANT SELECT ON  [dbo].[Rates] TO [MssExec]
GRANT INSERT ON  [dbo].[Rates] TO [MssExec]
GRANT DELETE ON  [dbo].[Rates] TO [MssExec]
GRANT UPDATE ON  [dbo].[Rates] TO [MssExec]
GO