CREATE TABLE [dbo].[ca_contact]
(
[contact_uuid] [binary] (16) NOT NULL,
[middle_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alias] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[last_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[first_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pri_phone_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alt_phone_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fax_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mobile_phone_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pager_number] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[email_address] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[location_uuid] [binary] (16) NULL,
[floor_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pager_email_address] [nvarchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[room_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contact_type] [int] NULL,
[inactive] [int] NOT NULL CONSTRAINT [DF__ca_contac__inact__7C3A67EB] DEFAULT ((0)),
[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_contac__versi__7D2E8C24] DEFAULT ((0)),
[department] [int] NULL,
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[company_uuid] [binary] (16) NULL,
[organization_uuid] [binary] (16) NULL,
[admin_organization_uuid] [binary] (16) NULL,
[alternate_identifier] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[job_title] [int] NULL,
[job_function] [int] NULL,
[mail_stop] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost_center] [int] NULL,
[userid] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[supervisor_contact_uuid] [binary] (16) NULL,
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[inrdid] [int] NULL,
[tenant] [binary] (16) NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE TRIGGER dbo.al_d_ca_contact
ON dbo.ca_contact
AFTER DELETE
AS
    if dbo.is_installed(2022) = 0
        return
    declare @update_date int
    set @update_date = datediff(ss, '1/1/1970', getutcdate())    
    declare @user_name nvarchar(255)
    set @user_name = SYSTEM_USER + '(al_d_ca_contact)'
    insert into al_attribute_tracking (object_uuid, table_name, [action], creation_user, creation_date, last_update_user, last_update_date)
        select contact_uuid, 'ca_contact', 2, @user_name, @update_date, @user_name, @update_date from deleted
    delete from al_link_dis_hw_contact where contact_uuid in (select contact_uuid from deleted)


GO

CREATE TRIGGER dbo.al_u_ca_contact
ON dbo.ca_contact
AFTER UPDATE
AS
    if dbo.is_installed(2022) = 0
        return
    declare @update_date int
    set @update_date = datediff(ss, '1/1/1970', getutcdate())
    declare @user_name nvarchar(255)
    set @user_name = SYSTEM_USER + '(al_u_ca_contact)'
    if update(supervisor_contact_uuid)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'supervisor_contact_uuid', 0, d.supervisor_contact_uuid, i.supervisor_contact_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.supervisor_contact_uuid, i.supervisor_contact_uuid) = 1)
    end
    if update(department)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'department', 0, d.department, i.department, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.department, i.department) = 1)
    end
    if update(contact_type)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'contact_type', 0, d.contact_type, i.contact_type, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.contact_type, i.contact_type) = 1)
    end
    if update(company_uuid)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'company_uuid', 0, d.company_uuid, i.company_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.company_uuid, i.company_uuid) = 1)
    end
    if update(organization_uuid)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'organization_uuid', 0, d.organization_uuid, i.organization_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.organization_uuid, i.organization_uuid) = 1)
    end
    if update(location_uuid)
    begin
        insert into al_attribute_tracking (object_uuid, table_name, field_name, [action], old_value, new_value, creation_user, creation_date, last_update_user, last_update_date)
            select i.contact_uuid, 'ca_contact', 'location_uuid', 0, d.location_uuid, i.location_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.contact_uuid = i.contact_uuid and dbo.is_different(d.location_uuid, i.location_uuid) = 1)
    end


GO
SET QUOTED_IDENTIFIER ON
GO
create trigger usm_trigger_update_all_table
on ca_contact
after update
as
begin
        declare @new_userid nvarchar(200);
        declare @old_userid nvarchar(200);
                declare @contactid  binary(16);
                declare @old_inactive int;
                declare @new_inactive int;
        set @old_userid= ( select userid from deleted);
                set @new_userid= (select userid from inserted);
                set @old_inactive  = ( select inactive from deleted);
                set @new_inactive  = ( select inactive from inserted);
                  if(@old_userid<>@new_userid)
          begin
           execute usm_sp_update_userid_all_table @new_userid, @old_userid
          end
          else
          if(@new_userid is null and @old_userid is not null )
          begin
          set @new_userid = @old_userid +  '_' + (SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21));
                  set @contactid = ( select contact_uuid from deleted);
          execute usm_sp_delete_userid_all_table @new_userid, @old_userid, @contactid
          end
                  else
                  if(@new_inactive = 1 and @old_inactive =0)
          begin
          set @new_userid = @new_userid +  '_' + (SELECT CONVERT(CHAR(23), CURRENT_TIMESTAMP, 21));
          set @contactid = ( select contact_uuid from deleted);
          execute usm_sp_delete_userid_all_table @new_userid, @old_userid, @contactid
          end     
end
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [XPKca_contact] PRIMARY KEY CLUSTERED ([contact_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_04] ON [dbo].[ca_contact] ([alternate_identifier]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_09] ON [dbo].[ca_contact] ([company_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_06] ON [dbo].[ca_contact] ([contact_type], [last_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_10] ON [dbo].[ca_contact] ([cost_center]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_12] ON [dbo].[ca_contact] ([department]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_05] ON [dbo].[ca_contact] ([email_address]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_08] ON [dbo].[ca_contact] ([first_name], [middle_name], [last_name], [company_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_13] ON [dbo].[ca_contact] ([inrdid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_11] ON [dbo].[ca_contact] ([job_function]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_02] ON [dbo].[ca_contact] ([last_name], [first_name], [middle_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_01] ON [dbo].[ca_contact] ([last_update_date]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_07] ON [dbo].[ca_contact] ([pri_phone_number]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_14] ON [dbo].[ca_contact] ([tenant]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_contact_idx_03] ON [dbo].[ca_contact] ([userid], [contact_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk01] FOREIGN KEY ([contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk02] FOREIGN KEY ([location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk03] FOREIGN KEY ([admin_organization_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk04] FOREIGN KEY ([organization_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk05] FOREIGN KEY ([company_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk06] FOREIGN KEY ([job_title]) REFERENCES [dbo].[ca_job_title] ([id])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ca_contact_fk07] FOREIGN KEY ([contact_type]) REFERENCES [dbo].[ca_contact_type] ([id])
GO
GRANT SELECT ON  [dbo].[ca_contact] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_contact] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_contact] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_contact] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_contact] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_contact] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_contact] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_contact] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_contact] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_contact] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_contact] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_contact] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_contact] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_contact] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmadmin]
GRANT INSERT ON  [dbo].[ca_contact] TO [uapmadmin]
GRANT DELETE ON  [dbo].[ca_contact] TO [uapmadmin]
GRANT UPDATE ON  [dbo].[ca_contact] TO [uapmadmin]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmadmin_group]
GRANT INSERT ON  [dbo].[ca_contact] TO [uapmadmin_group]
GRANT DELETE ON  [dbo].[ca_contact] TO [uapmadmin_group]
GRANT UPDATE ON  [dbo].[ca_contact] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmbatch]
GRANT INSERT ON  [dbo].[ca_contact] TO [uapmbatch]
GRANT DELETE ON  [dbo].[ca_contact] TO [uapmbatch]
GRANT UPDATE ON  [dbo].[ca_contact] TO [uapmbatch]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmbatch_group]
GRANT INSERT ON  [dbo].[ca_contact] TO [uapmbatch_group]
GRANT DELETE ON  [dbo].[ca_contact] TO [uapmbatch_group]
GRANT UPDATE ON  [dbo].[ca_contact] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmreporting]
GRANT SELECT ON  [dbo].[ca_contact] TO [uapmreporting_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [upmuser_group]
GRANT SELECT ON  [dbo].[ca_contact] TO [usmgroup]
GRANT INSERT ON  [dbo].[ca_contact] TO [usmgroup]
GRANT DELETE ON  [dbo].[ca_contact] TO [usmgroup]
GRANT UPDATE ON  [dbo].[ca_contact] TO [usmgroup]
GO