CREATE TABLE [dbo].[al_usage_right]
(
[license_id] [int] NOT NULL,
[license_model_template_id] [int] NULL,
[license_model_altered] [smallint] NULL,
[true_up] [int] NULL,
[baseline_amount] [int] NULL,
[true_up_unit] [smallint] NULL,
[renewal_period] [smallint] NULL,
[renewal_period_unit] [smallint] NULL,
[true_up_last_performed_date] [datetime] NULL,
[growth] [smallint] NULL,
[growth_percentage] [smallint] NULL,
[growth_cap] [smallint] NULL,
[growth_period] [smallint] NULL,
[growth_period_unit] [smallint] NULL,
[growth_unit] [smallint] NULL,
[source_flag] [smallint] NULL,
[creation_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__AL_USAGE___VERSI__3F865F66] DEFAULT ('0 ')
) ON [PRIMARY]

GO
CREATE TRIGGER al_u_usage_right
ON  al_usage_right
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    declare @old_include int
    declare @include int
    declare @license_id int
    declare @old_renewal_period smallint
    declare @old_renewal_period_unit smallint
    declare @renewal_period smallint
    declare @renewal_period_unit smallint


    select @old_include = true_up, @old_renewal_period = renewal_period,
           @old_renewal_period_unit = renewal_period_unit
    from deleted
    
    select @include = true_up, @license_id =license_id,
           @renewal_period = renewal_period, @renewal_period_unit = renewal_period_unit
    from inserted

    --True up changed, clear flags and recalculate next true up date.
    if @old_renewal_period <> @renewal_period or
       @old_renewal_period_unit <> @renewal_period_unit or
       @old_include  <> @include
        update al_license_flags set true_up_due = 0, true_up_expiring = 0 where license_id = @license_id

    
    EXEC al_sp_check_true_up_due @license_id
END
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [pk_al_usage_right] PRIMARY KEY CLUSTERED ([license_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif3al_usage_right] ON [dbo].[al_usage_right] ([growth_period_unit]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif6al_usage_right] ON [dbo].[al_usage_right] ([growth_unit]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif4al_usage_right] ON [dbo].[al_usage_right] ([license_model_template_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif2al_usage_right] ON [dbo].[al_usage_right] ([renewal_period_unit]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif5al_usage_right] ON [dbo].[al_usage_right] ([true_up_unit]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK01] FOREIGN KEY ([license_id]) REFERENCES [dbo].[al_license] ([license_id])
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK02] FOREIGN KEY ([renewal_period_unit]) REFERENCES [dbo].[al_time_unit] ([time_unit_id])
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK03] FOREIGN KEY ([growth_period_unit]) REFERENCES [dbo].[al_time_unit] ([time_unit_id])
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK04] FOREIGN KEY ([license_model_template_id]) REFERENCES [dbo].[al_license_model_template] ([license_model_template_id])
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK05] FOREIGN KEY ([true_up_unit]) REFERENCES [dbo].[al_growth_unit] ([id])
GO
ALTER TABLE [dbo].[al_usage_right] ADD CONSTRAINT [AL_USAGE_RIGHT_FK06] FOREIGN KEY ([growth_unit]) REFERENCES [dbo].[al_growth_unit] ([id])
GO
GRANT SELECT ON  [dbo].[al_usage_right] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_usage_right] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_usage_right] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_usage_right] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_usage_right] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_usage_right] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_usage_right] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_usage_right] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_usage_right] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_usage_right] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_usage_right] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_usage_right] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_usage_right] TO [uapmreporting_group]
GO