CREATE TABLE [dbo].[ca_model_def]
(
[subclass_id] [int] NULL,
[inactive] [int] NOT NULL CONSTRAINT [DF__ca_model___inact__2CA8951C] DEFAULT ('0'),
[name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__ca_model_d__name__2D9CB955] DEFAULT (' '),
[abbreviation] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[creation_date] [int] NULL,
[last_update_user] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_model___versi__2E90DD8E] DEFAULT ('0'),
[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_as_of_date] [int] NULL,
[class_id] [int] NULL,
[family_id] [int] NULL,
[preferred_seller_uuid] [binary] (16) NULL,
[manufacturer_uuid] [binary] (16) NULL,
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[operating_system] [int] NULL,
[capacity] [float] NULL,
[capacity_unit] [int] NULL,
[gl_code] [int] NULL,
[model_uuid] [binary] (16) NOT NULL,
[tenant] [binary] (16) NULL,
[asset_capable] [smallint] NULL CONSTRAINT [DF__ca_model___asset__4A18FC72] DEFAULT ('0'),
[authoritative] [smallint] NULL CONSTRAINT [DF__ca_model___autho__4B0D20AB] DEFAULT ('1')
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.ca_tr_del_ca_model_def
ON dbo.ca_model_def
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_model_def (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
subclass_id,
inactive,
name,
abbreviation,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
description,
current_as_of_date,
class_id,
family_id,
preferred_seller_uuid,
manufacturer_uuid,
exclude_registration,
delete_time,
operating_system,
capacity,
capacity_unit,
gl_code,
model_uuid,
tenant,
asset_capable,
authoritative) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.subclass_id,
old.inactive,
old.name,
old.abbreviation,
old.creation_user,
old.creation_date,
old.last_update_user,
old.last_update_date,
old.version_number,
old.description,
old.current_as_of_date,
old.class_id,
old.family_id,
old.preferred_seller_uuid,
old.manufacturer_uuid,
old.exclude_registration,
old.delete_time,
old.operating_system,
old.capacity,
old.capacity_unit,
old.gl_code,
old.model_uuid,
old.tenant,
old.asset_capable,
old.authoritative
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ca_model_def
ON dbo.ca_model_def
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_model_def (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
subclass_id,
inactive,
name,
abbreviation,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
description,
current_as_of_date,
class_id,
family_id,
preferred_seller_uuid,
manufacturer_uuid,
exclude_registration,
delete_time,
operating_system,
capacity,
capacity_unit,
gl_code,
model_uuid,
tenant,
asset_capable,
authoritative) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.subclass_id,
new.inactive,
new.name,
new.abbreviation,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.description,
new.current_as_of_date,
new.class_id,
new.family_id,
new.preferred_seller_uuid,
new.manufacturer_uuid,
new.exclude_registration,
new.delete_time,
new.operating_system,
new.capacity,
new.capacity_unit,
new.gl_code,
new.model_uuid,
new.tenant,
new.asset_capable,
new.authoritative
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ca_model_def
ON dbo.ca_model_def
FOR UPDATE AS
DECLARE
        @audit_product nvarchar(64),
        @last_update_user nvarchar(64),
        @version_number int


        SET @audit_product = APP_NAME()
SELECT  @version_number = version_number, @last_update_user = last_update_user FROM inserted

IF @version_number != -1
INSERT INTO dbo.aud_ca_model_def (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
subclass_id,
inactive,
name,
abbreviation,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
description,
current_as_of_date,
class_id,
family_id,
preferred_seller_uuid,
manufacturer_uuid,
exclude_registration,
delete_time,
operating_system,
capacity,
capacity_unit,
gl_code,
model_uuid,
tenant,
asset_capable,
authoritative) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.subclass_id,
new.inactive,
new.name,
new.abbreviation,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.description,
new.current_as_of_date,
new.class_id,
new.family_id,
new.preferred_seller_uuid,
new.manufacturer_uuid,
new.exclude_registration,
new.delete_time,
new.operating_system,
new.capacity,
new.capacity_unit,
new.gl_code,
new.model_uuid,
new.tenant,
new.asset_capable,
new.authoritative
  FROM inserted new
GO
CREATE TRIGGER [dbo].[t_u_ca_model_def]
ON [dbo].[ca_model_def]
AFTER UPDATE
AS
    if dbo.is_installed(2002) = 0
        return
        
    DECLARE @ModCount integer;
    DECLARE @DisHwUUID [binary](16);    
    DECLARE @SysName varchar(10);
    DECLARE @DateInSeconds int;
    DECLARE @UpdateUser varchar(30);

    Set @ModCount = 0;                
    Set @SysName = 'ITAM';
    Set @DateInSeconds = datediff(ss, '1/1/1970', getutcdate());
    Set @UpdateUser = 't_u_ca_model_def';

    IF(UPDATE (name) or UPDATE(inactive))
    BEGIN
        insert into arg_reconcile_modification
        (sys_name, reconcile_action, object_uuid, tenant, subschema_id,
         creation_user, creation_date, last_update_user, last_update_date, version_number)
        select @SysName, 5, i.model_uuid, t.tenant, 1,
               @UpdateUser, @DateInSeconds, @UpdateUser, @DateInSeconds, 1  
          from inserted i
          join deleted d
            on ((i.model_uuid = d.model_uuid) and
                 (dbo.is_different(d.name, i.name) = 1 or
                  (dbo.is_different_integer(d.inactive, i.inactive) = 1 and i.inactive = 1)))
          left join arg_reconcile_task t
            on i.tenant = t.tenant or i.tenant is null
         where exists (select a.own_resource_uuid
                         from ca_owned_resource a
                         join arg_reconcile_links l
                           on a.own_resource_uuid = l.own_resource_uuid
                        where i.model_uuid = a.model_uuid
                          and (t.tenant = a.tenant or
                               (t.tenant is null and a.tenant is null)))
    END
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [XPKca_model_def] PRIMARY KEY CLUSTERED ([model_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_04] ON [dbo].[ca_model_def] ([abbreviation]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_05] ON [dbo].[ca_model_def] ([class_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_03] ON [dbo].[ca_model_def] ([family_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_02] ON [dbo].[ca_model_def] ([manufacturer_uuid]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [ca_model_def_idx_01] ON [dbo].[ca_model_def] ([name], [manufacturer_uuid], [tenant]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_06] ON [dbo].[ca_model_def] ([subclass_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_model_def_idx_07] ON [dbo].[ca_model_def] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk01] FOREIGN KEY ([class_id]) REFERENCES [dbo].[ca_resource_class] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk02] FOREIGN KEY ([subclass_id]) REFERENCES [dbo].[ca_resource_class] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk03] FOREIGN KEY ([gl_code]) REFERENCES [dbo].[ca_resource_gl_code] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk04] FOREIGN KEY ([family_id]) REFERENCES [dbo].[ca_resource_family] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk05] FOREIGN KEY ([manufacturer_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk06] FOREIGN KEY ([preferred_seller_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk07] FOREIGN KEY ([operating_system]) REFERENCES [dbo].[ca_resource_operating_system] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [ca_model_def_fk08] FOREIGN KEY ([capacity_unit]) REFERENCES [dbo].[ca_capacity_unit] ([id])
GO
ALTER TABLE [dbo].[ca_model_def] ADD CONSTRAINT [fk_ca_model_def_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[ca_model_def] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_model_def] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_model_def] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_model_def] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_model_def] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_model_def] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_model_def] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_model_def] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_model_def] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_model_def] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_model_def] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_model_def] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_model_def] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_model_def] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_model_def] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_model_def] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_model_def] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_model_def] TO [upmuser_group]
GRANT SELECT ON  [dbo].[ca_model_def] TO [usmgroup]
GRANT INSERT ON  [dbo].[ca_model_def] TO [usmgroup]
GRANT DELETE ON  [dbo].[ca_model_def] TO [usmgroup]
GRANT UPDATE ON  [dbo].[ca_model_def] TO [usmgroup]
GO