CREATE TABLE [dbo].[al_license]
(
[license_id] [int] NOT NULL,
[inactive] [int] NULL CONSTRAINT [DF__al_licens__inact__14E87C29] DEFAULT ('0'),
[effective_date] [datetime] NOT NULL,
[termination_date] [datetime] NULL,
[license_term_type] [int] NULL,
[match_flag] [smallint] NULL,
[category_flag] [tinyint] NULL,
[locking_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_licens__versi__15DCA062] DEFAULT ('0')
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER al_c_license_tr ON dbo.al_license
AFTER INSERT
AS
BEGIN

    DECLARE @licenseid int
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int
    DECLARE @versionnumber int
    DECLARE @terminated int
    

    SELECT  @licenseid = license_id,
            @creationuser = creation_user,
            @creationdate = creation_date,
            @lastupdateuser = last_update_user,
            @lastupdatedate = last_update_date,
            @versionnumber = version_number
    FROM INSERTED
    

    --4/28/08 DD added true_up_due to the flags insert statement.
    INSERT INTO al_license_flags
    (license_id, missing_products_covered, includes_metrics,
     processable, maintenance_terminating,  maintenance_terminated,
     true_up_expiring,true_up_due, terminated, terminating, maintenance_payment_coming_due,
     creation_user, creation_date, last_update_user, last_update_date,  version_number)
    VALUES
    (@licenseid, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0,@creationuser,@creationdate, @lastupdateuser, @lastupdatedate,@versionnumber )
END

GO
CREATE TRIGGER al_cu_license_tr ON dbo.al_license
AFTER INSERT, UPDATE
AS
BEGIN

    DECLARE @licenseid int
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int
    DECLARE @versionnumber int
    DECLARE @terminated int
    DECLARE @term_date DateTime
    DECLARE @old_inactive int
    DECLARE @inactive int
    declare @effective_date datetime
    declare @termination_date datetime
    declare @old_effective_date datetime
    declare @old_termination_date datetime

    SELECT  @licenseid = license_id,
            @term_date = termination_date,
            @creationuser = creation_user,
            @creationdate = creation_date,
            @lastupdateuser = last_update_user,
            @lastupdatedate = last_update_date,
            @versionnumber = version_number,
            @inactive = inactive,
            @effective_date = effective_date,
            @termination_date = termination_date

    FROM INSERTED

    SELECT @old_inactive = inactive,
           @old_effective_date = effective_date,
           @old_termination_date = termination_date
    FROM DELETED

    -- 4/28/08 DD if effective date changes, reset the flags so that recalcuation can happen for
    --            true up calculation.
    if @effective_date <> @old_effective_date or
       @termination_date <> @old_termination_date
        begin
            update al_license_flags set true_up_due = 0, true_up_expiring = 0 where license_id = @licenseid
            exec al_sp_check_true_up_due @licenseid
        end

    -- 4/16/08 DD Added checks for inactive.  Forcing processable on license flags.
    --            The al_evaluate_processable trigger will in fact override the processable value
    --            based on the processable calculation in that trigger, however this is the only
    --            way I can signal a change in inactive to that trigger.

    if @old_inactive = 1 and @inactive = 0
        update al_license_flags set processable = 1 where license_id = @licenseid
    
    if @old_inactive = 0 and @inactive = 1
        update al_license_flags set processable = 0 where license_id = @licenseid

    EXECUTE dbo.al_sp_set_terminated @licenseid

END

GO
CREATE TRIGGER al_d_license_tr ON al_license
AFTER DELETE
AS
BEGIN
    -- delete child flag records
    DELETE al_license_flags
    FROM al_license_flags f
    INNER JOIN deleted d ON f.license_id = d.license_id
END

GO
CREATE TRIGGER al_DeleteProductionChilds ON al_license
INSTEAD OF DELETE
AS
BEGIN
    -- delete child records first
    DELETE al_product_maintenance_info
    FROM al_product_maintenance_info f
    INNER JOIN deleted d ON f.license_id = d.license_id

    DELETE al_usage_right
    FROM al_usage_right f
    INNER JOIN deleted d ON f.license_id = d.license_id

    -- now delete parent
    DELETE al_license
    FROM al_license f
    INNER JOIN deleted d ON f.license_id = d.license_id
END

GO
CREATE TRIGGER al_InsertProductionChilds ON al_license
AFTER INSERT
AS
BEGIN

    DECLARE @licenseid int
    DECLARE @creationuser nvarchar(255)
    DECLARE @creationdate int
    DECLARE @lastupdateuser nvarchar(255)
    DECLARE @lastupdatedate int
    DECLARE @versionnumber int
    DECLARE @terminated int

    SELECT  @licenseid = license_id,
            @creationuser = creation_user,
            @creationdate = creation_date,
            @lastupdateuser = last_update_user,
            @lastupdatedate = last_update_date,
            @versionnumber = version_number
    FROM INSERTED


    INSERT INTO al_product_maintenance_info
    (license_id, include, creation_user, creation_date, last_update_user, last_update_date,  version_number)
    VALUES (@licenseid, 0, @creationuser, @creationdate,@lastupdateuser, @lastupdatedate,  0)
    
    INSERT INTO al_usage_right (license_id, source_flag, creation_user, creation_date, last_update_user, last_update_date,  version_number)
    VALUES (@licenseid, 1, @creationuser, @creationdate,@lastupdateuser, @lastupdatedate,  0)
END

GO
ALTER TABLE [dbo].[al_license] ADD CONSTRAINT [PK_al_license] PRIMARY KEY CLUSTERED ([license_id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_license] ADD CONSTRAINT [FK_al_lic_al_license_term_type] FOREIGN KEY ([license_term_type]) REFERENCES [dbo].[al_license_term_type] ([id])
GO
GRANT SELECT ON  [dbo].[al_license] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_license] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_license] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_license] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_license] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_license] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_license] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_license] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_license] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_license] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_license] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_license] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_license] TO [uapmreporting_group]
GO