CREATE TABLE [dbo].[al_license_flags]
(
[license_id] [int] NOT NULL,
[missing_products_covered] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__MISSI__42E1EEFE] DEFAULT ('1 '),
[processable] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__PROCE__43D61337] DEFAULT ('0 '),
[terminated] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__TERMI__44CA3770] DEFAULT ('0 '),
[terminating] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__TERMI__45BE5BA9] DEFAULT ('0 '),
[true_up_expiring] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__TRUE___46B27FE2] DEFAULT ('0 '),
[true_up_due] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__TRUE___47A6A41B] DEFAULT ('0 '),
[includes_metrics] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__INCLU__489AC854] DEFAULT ('0 '),
[maintenance_terminated] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__MAINT__498EEC8D] DEFAULT ('0 '),
[maintenance_payment_coming_due] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__MAINT__4A8310C6] DEFAULT ('0 '),
[maintenance_terminating] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__MAINT__4B7734FF] DEFAULT ('0 '),
[effective_future] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__EFFEC__4C6B5938] DEFAULT ('0 '),
[maintenance_effective_future] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__MAINT__4D5F7D71] DEFAULT ('0 '),
[automatic_coverage] [smallint] NOT NULL CONSTRAINT [DF__AL_LICENS__AUTOM__4E53A1AA] DEFAULT ('0 '),
[from_uapm] [smallint] NULL CONSTRAINT [DF__AL_LICENS__FROM___4F47C5E3] DEFAULT ('0 '),
[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_LICENS__VERSI__503BEA1C] DEFAULT ('0 ')
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.al_eval_processable
  ON  dbo.al_license_flags
   INSTEAD OF UPDATE
  AS
  BEGIN
    SET NOCOUNT ON;
    declare @processable smallint
    declare @license_id int
    declare @missing_products_covered smallint
    declare @terminated smallint
    declare    @terminating smallint
    declare    @true_up_expiring smallint
    declare    @includes_metrics smallint
    declare    @maintenance_terminated smallint
    declare    @maintenance_payment_coming_due smallint
    declare    @maintenance_terminating smallint
    declare @old_processable smallint
    declare @product_uuid as binary(16)
    declare @effective_future as smallint
    declare @maintenance_effective_future as smallint
    declare @old_missing_products_covered smallint
    declare @block_id int
    declare @license_metric_id int
    declare @old_includes_metrics smallint
    declare @inactive int
    declare @old_effective_future int
    declare @old_maintenance_effective_future int
    declare @old_terminated int
    declare @old_maintenance_terminated int
    declare @from_uapm smallint
    declare @old_inactive int
    declare @true_up_due smallint
    
    select @old_processable = processable, @old_missing_products_covered = missing_products_covered , @old_includes_metrics = includes_metrics,
           @old_effective_future = effective_future,
           @old_maintenance_effective_future = maintenance_effective_future,
                  @old_terminated = terminated,
           @old_maintenance_terminated = maintenance_terminated
    from deleted
    
    --4/28/08 DD added true up due
    SELECT  @license_id = license_id,
            @missing_products_covered = missing_products_covered,
            @terminated = terminated,
            @terminating = terminating,
            @true_up_expiring = true_up_expiring,
            @includes_metrics = includes_metrics,
            @maintenance_terminated = maintenance_terminated,
            @maintenance_payment_coming_due = maintenance_payment_coming_due,
            @maintenance_terminating = maintenance_terminating,
            @effective_future = effective_future,
            @maintenance_effective_future = maintenance_effective_future,
            @from_uapm = from_uapm,    
            @processable = processable,
            @true_up_due = true_up_due
FROM INSERTED
    -- 4/16/08 DD I'm setting old inactive based on processable because I have no way to get old
    --            inactive from this trigger as inactive exists in the al_license table.
    --            The goal here is to set processable from the al_license trigger, causing this
    --            trigger to fire in turn.  I will check to see what processable is and assume
    --            if processable has changed, that inactive is what caused the change.  Then it
    --            will run through the processable calculation a few lines below and determine processable
    --            based on all the other criteria.  If that in fact shows a change in processable then
    --            it will hit my further check after the update to license flags to invalidate any other
    --            direct request to change processable.
    if @old_processable = 0 and @processable = 1
    begin
        set @old_inactive = 1
    end

    if @old_processable = 1 and @processable = 0
    begin
        set @old_inactive = 0
    end

    SELECT @processable = ~ (@effective_future * -1 | al_license.inactive * - 1 | @terminated * - 1 | @missing_products_covered * - 1 | (@includes_metrics ^ 1) * - 1) * - 1, @inactive = al_license.inactive
    FROM al_license, al_license_flags
    WHERE al_license.license_id = al_license_flags.license_id and al_license.license_id = @license_id
    
    --4/28/08 DD added true up due
    update al_license_flags set processable=@processable, missing_products_covered = @missing_products_covered, terminated=@terminated,
                                terminating=@terminating, true_up_expiring = @true_up_expiring, includes_metrics = @includes_metrics,
                                maintenance_terminated = @maintenance_terminated, true_up_due = @true_up_due,
                                maintenance_payment_coming_due = @maintenance_payment_coming_due, maintenance_terminating = @maintenance_terminating,
                                effective_future = @effective_future, maintenance_effective_future = @maintenance_effective_future,
                                from_uapm = @from_uapm
                                where license_id = @license_id    

    -- 4/16/08 DD inactive wasn't the cause of the processable change request.  Some other request
    --            to change processable directly was made other than because inactive changed and
    --            that will be ingored.
    if @old_processable = @processable
        set @inactive = @old_inactive
    

    -- If effective future, maintenance effective future, terminated, or effective future
        -- changed.
    if  (@old_effective_future <> @effective_future) or
        (@old_maintenance_effective_future <> @maintenance_effective_future) or
            (@old_terminated <> @terminated) or
        (@old_maintenance_terminated <> @maintenance_terminated) or (@old_inactive <> @inactive)
        begin

            --license became not processable.
            --unlink all the license metrics.  We don't need to unlink those metrics with
            --maintenance because they have already been unlinked if the maintenance has expired.
            if @processable = 0 and @old_processable = 1
                begin
                    exec al_unlink_license_metric @license_id, null
                end

            --license became processable
            --link in all the license metrics (that don't have maintenance expired or effective future)
            if (@processable = 1 and @old_processable = 0)
                begin                                                            
                    if @maintenance_terminated = 1 or @maintenance_effective_future = 1
                        exec al_link_license_metric @license_id, 0
                    else                    
                        exec al_link_license_metric @license_id, null            
                end

            -- When a license is processable and maintenance expires, we need to only unlink
                    -- the license metrics that have been marked as maintenance.
            if (@processable = 1 and @old_processable = 1) and
               ((@maintenance_terminated = 1 and @old_maintenance_terminated = 0) or
                (@maintenance_effective_future = 1 and @old_maintenance_effective_future = 0))
                begin
                    exec al_unlink_license_metric @license_id, 1
                end
                
            if (@processable = 1 and @old_processable = 1) and
                ((@maintenance_terminated = 0 and @old_maintenance_terminated = 1) or
                (@maintenance_effective_future = 0 and @old_maintenance_effective_future = 1))
               begin
                    exec al_link_license_metric @license_id, 1
               end
        end
  end
GO
ALTER TABLE [dbo].[al_license_flags] ADD CONSTRAINT [pk_al_license_flags] PRIMARY KEY CLUSTERED ([license_id]) ON [PRIMARY]
GO
GRANT SELECT ON  [dbo].[al_license_flags] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_license_flags] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_license_flags] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_license_flags] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_license_flags] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_license_flags] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_license_flags] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_license_flags] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_license_flags] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_license_flags] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_license_flags] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_license_flags] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_license_flags] TO [uapmreporting_group]
GO