CREATE TABLE [dbo].[al_product_rights]
(
[product_rights_id] [int] NOT NULL,
[license_id] [int] NOT NULL,
[selected] [int] NULL CONSTRAINT [DF__AL_PRODUC__SELEC__049AA3C2] DEFAULT ('1 '),
[product_uuid] [binary] (16) NOT NULL,
[coverage_source] [int] NULL CONSTRAINT [DF__AL_PRODUC__COVER__058EC7FB] DEFAULT ('1 '),
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS 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_PRODUC__VERSI__0682EC34] DEFAULT ('0 ')
) ON [PRIMARY]

GO
CREATE TRIGGER al_c_product_rights
ON  dbo.al_product_rights
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    declare @license_id int
    declare @product_uuid binary(16)
    declare @has_metrics smallint
    declare @processable smallint

    select @product_uuid = product_uuid,
           @license_id = license_id from INSERTED
    
    -- 12/13/07 JMP: Removed logic to check processable and includes_metrics.  No longer applies to product rights.
    --               Always set missing_products_covered flag off.

    --SELECT @has_metrics = includes_metrics, @processable = ~ (effective_future * -1 | al_license.inactive * - 1 | terminated * - 1 | missing_license_count * -1) * - 1
    --FROM al_license, al_license_flags
    --WHERE al_license.license_id = al_license_flags.license_id and al_license.license_id = @license_id
    
    --if @processable = 1 and @has_metrics = 0
    --    exec al_link_prod_to_lic @license_id, @product_uuid, 0    
    --else
    --    update al_license_flags set missing_products_covered = 0 where license_id = @license_id

    update al_license_flags set missing_products_covered = 0 where license_id = @license_id
end
GO
CREATE TRIGGER al_d_product_rights
    ON  dbo.al_product_rights
   AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    declare @license_id int
    declare @product_uuid binary(16)
    declare @has_metrics smallint
    declare @processable smallint
    declare @count int

    select @product_uuid = product_uuid,
           @license_id = license_id from DELETED

    -- 12/13/07 JMP: Removed logic to check processable and includes_metrics.  No longer applies to product rights.
    --               Continue setting missing_products_covered flag when appropriate.

    --SELECT @has_metrics = includes_metrics, @processable = ~ (effective_future * -1 | al_license.inactive * - 1 | terminated * - 1 | missing_license_count * -1) * - 1
    --FROM al_license, al_license_flags
    --WHERE al_license.license_id = al_license_flags.license_id and al_license.license_id = @license_id
    
    --if @processable = 1 and @has_metrics = 0
    --    exec al_unlink_prod_to_lic @license_id, @product_uuid, 0
    --else
    --    begin
    --        select @count = count(*) from al_product_rights where license_id = @license_id
    --        if @count = 0
    --            update al_license_flags set missing_products_covered = 1 where license_id = @license_id
    --    end

    select @count = count(*) from al_product_rights where license_id = @license_id
    if @count = 0
        update al_license_flags set missing_products_covered = 1 where license_id = @license_id    
END
GO
ALTER TABLE [dbo].[al_product_rights] ADD CONSTRAINT [al_product_rights_pk01] PRIMARY KEY CLUSTERED ([product_rights_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [xif1al_product_rights] ON [dbo].[al_product_rights] ([coverage_source]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [al_product_rights_idx_01] ON [dbo].[al_product_rights] ([license_id]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [al_product_rights_idx_04] ON [dbo].[al_product_rights] ([license_id], [product_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [al_product_rights_idx_03] ON [dbo].[al_product_rights] ([product_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [al_product_rights_idx_02] ON [dbo].[al_product_rights] ([selected]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[al_product_rights] ADD CONSTRAINT [AL_PROD_RIGHTS_AL_CVRG_TYP_FK0] FOREIGN KEY ([coverage_source]) REFERENCES [dbo].[al_coverage_type] ([id])
GO
ALTER TABLE [dbo].[al_product_rights] ADD CONSTRAINT [al_prod_rights_al_cvrg_typ_fk01] FOREIGN KEY ([coverage_source]) REFERENCES [dbo].[al_coverage_type] ([id])
GO
ALTER TABLE [dbo].[al_product_rights] ADD CONSTRAINT [AL_PROD_RIGHTS_AL_LICNS_FK01] FOREIGN KEY ([license_id]) REFERENCES [dbo].[al_license] ([license_id])
GO
ALTER TABLE [dbo].[al_product_rights] ADD CONSTRAINT [al_prod_rights_al_prod_def_fk01] FOREIGN KEY ([product_uuid]) REFERENCES [dbo].[al_product_def] ([product_uuid])
GO
GRANT SELECT ON  [dbo].[al_product_rights] TO [swcmadmin]
GRANT INSERT ON  [dbo].[al_product_rights] TO [swcmadmin]
GRANT DELETE ON  [dbo].[al_product_rights] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[al_product_rights] TO [swcmadmin]
GRANT SELECT ON  [dbo].[al_product_rights] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[al_product_rights] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[al_product_rights] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[al_product_rights] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[al_product_rights] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[al_product_rights] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[al_product_rights] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[al_product_rights] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[al_product_rights] TO [uapmreporting_group]
GO