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,
[tenant_group] [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
/****************************************************************************************/
/*  17707101_CATALOGUE CONTACT ROLES                            */
/*  slcm r12.0.sp1:kaman02-20091002-17626605-REQUESTED FOR GOING BLANK            */
/*  slcm r12.0.sp1:kaman02-20091015-17626605-REQUESTED FOR GOING BLANK                  */
/*                                            */
/*  slcm r12.5 Anderson (18998592)                            */
/*  the related triggers and procedures                            */
/*  fixed triggers to handle multiple row update and also review the solutions overall    */
/*  add a column usm_contact_uuid in the table usm_contact_extension            */
/*  usm_contact_extension.usm_contact_uuid is linked to the ca_contact.contact_id    */
/*    COLUMN usm_contact_extension.usm_contact_uuid                    */
/*    TRIGGER usm_trigger_update_all_table                        */
/*    TRIGGER usm_trigger_insert_contact                        */
/*    TRIGGER usm_trigger_delete_all_table                        */
/*    procedure usm_sp_delete_userid_all_table                    */
/*    procedure usm_sp_update_userid_all_table                    */
/*    procedure usm_sp_set_contact_uuid                        */
/*    com.ca.usm.user.CAContact.java                            */
/*    sql file usm_sql_set_contact_uuid                        */
/*    permission for usm_sp_set_userid_contact_uuid                    */
/****************************************************************************************/
CREATE TRIGGER usm_trigger_delete_all_table
ON ca_contact
AFTER DELETE
AS
BEGIN
    DECLARE @new_userid nvarchar(200)
    DECLARE @ca_contact_uuid varchar(50)
    --
    DECLARE ca_contact_uuid_cursor CURSOR FOR
        select userid,sys.fn_varbintohexsubstring(0,contact_uuid,1,0) from deleted

    OPEN ca_contact_uuid_cursor

    FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @ca_contact_uuid

    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute usm_sp_delete_userid_all_table @new_userid, @ca_contact_uuid
        FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @ca_contact_uuid

    END --while begin
    CLOSE ca_contact_uuid_cursor
    DEALLOCATE ca_contact_uuid_cursor
END
GO
/****************************************************************************************/
/*  17707101_CATALOGUE CONTACT ROLES                            */
/*  slcm r12.0.sp1:kaman02-20091002-17626605-REQUESTED FOR GOING BLANK            */
/*  slcm r12.0.sp1:kaman02-20091015-17626605-REQUESTED FOR GOING BLANK                  */
/*                                            */
/*  slcm r12.5 Anderson (18998592)                            */
/*  the related triggers and procedures                            */
/*  fixed triggers to handle multiple row update and also review the solutions overall    */
/*  add a column usm_contact_uuid in the table usm_contact_extension            */
/*  usm_contact_extension.usm_contact_uuid is linked to the ca_contact.contact_id    */
/*    COLUMN usm_contact_extension.usm_contact_uuid                    */
/*    TRIGGER usm_trigger_update_all_table                        */
/*    TRIGGER usm_trigger_insert_contact                        */
/*    TRIGGER usm_trigger_delete_all_table                        */
/*    procedure usm_sp_delete_userid_all_table                    */
/*    procedure usm_sp_update_userid_all_table                    */
/*    procedure usm_sp_set_contact_uuid                        */
/*    com.ca.usm.user.CAContact.java                            */
/*    sql file usm_sql_set_contact_uuid                        */
/*    permission for usm_sp_set_userid_contact_uuid                    */
/****************************************************************************************/
CREATE TRIGGER usm_trigger_insert_contact
ON ca_contact
AFTER INSERT
AS
BEGIN
    DECLARE @new_userid nvarchar(200)
    DECLARE @ca_contact_uuid varchar(50)
    DECLARE @inactive_flag int -- 1:inactive, 0:active
    --
    DECLARE ca_contact_uuid_cursor CURSOR FOR
        select userid,sys.fn_varbintohexsubstring(0,contact_uuid,1,0),inactive from inserted

    OPEN ca_contact_uuid_cursor

    FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @ca_contact_uuid, @inactive_flag

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- for each new inserted contact, if the new_userid is non-empty        
        if ((@new_userid is not null) and (len(@new_userid) > 0))
        begin
            -- but, created as in-active user
            if (@inactive_flag = 1)
            begin
                execute usm_sp_update_userid_all_table @new_userid, null, @ca_contact_uuid
            end
        end

        FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @ca_contact_uuid, @inactive_flag

    END -- while end
    CLOSE ca_contact_uuid_cursor
    DEALLOCATE ca_contact_uuid_cursor
END
GO
/****************************************************************************************/
/*  17707101_CATALOGUE CONTACT ROLES                            */
/*  slcm r12.0.sp1:kaman02-20091002-17626605-REQUESTED FOR GOING BLANK            */
/*  slcm r12.0.sp1:kaman02-20091015-17626605-REQUESTED FOR GOING BLANK                  */
/*                                            */
/*  slcm r12.5 Anderson (18998592)                            */
/*  the related triggers and procedures                            */
/*  fixed triggers to handle multiple row update and also review the solutions overall    */
/*  add a column usm_contact_uuid in the table usm_contact_extension            */
/*  usm_contact_extension.usm_contact_uuid is linked to the ca_contact.contact_id    */
/*    COLUMN usm_contact_extension.usm_contact_uuid                    */
/*    TRIGGER usm_trigger_update_all_table                        */
/*    TRIGGER usm_trigger_insert_contact                        */
/*    TRIGGER usm_trigger_delete_all_table                        */
/*    procedure usm_sp_delete_userid_all_table                    */
/*    procedure usm_sp_update_userid_all_table                    */
/*    procedure usm_sp_set_contact_uuid                        */
/*    com.ca.usm.user.CAContact.java                            */
/*    sql file usm_sql_set_contact_uuid                        */
/*    permission for usm_sp_set_userid_contact_uuid                    */
/****************************************************************************************/
CREATE TRIGGER usm_trigger_update_all_table
ON ca_contact
AFTER UPDATE
AS
IF EXISTS (SELECT * FROM inserted a JOIN deleted b ON a.contact_uuid=b.contact_uuid)
BEGIN
    DECLARE @new_userid nvarchar(200)
    DECLARE @old_userid nvarchar(200)
    DECLARE @ca_contact_uuid varchar(50)
    DECLARE @inactive_flag_new int -- 1:inactive, 0:active
    DECLARE @inactive_flag_old int
    --
    DECLARE ca_contact_uuid_cursor CURSOR FOR
        select a.userid, b.userid, sys.fn_varbintohexsubstring(0,a.contact_uuid,1,0), a.inactive, b.inactive
        from inserted a, deleted b
        where a.contact_uuid=b.contact_uuid and ((a.userid <> b.userid) or (a.inactive <> b.inactive))

    OPEN ca_contact_uuid_cursor

    FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @old_userid, @ca_contact_uuid, @inactive_flag_new, @inactive_flag_old

    WHILE @@FETCH_STATUS = 0
    BEGIN
        if (@inactive_flag_old = 0) and (@inactive_flag_new = 1) -- inactivate user
        begin
            execute usm_sp_update_userid_all_table null, @old_userid, @ca_contact_uuid
        end
        else if (@inactive_flag_old = 1) and (@inactive_flag_new = 0) -- activate user
        begin
            execute usm_sp_update_userid_all_table @new_userid, @old_userid, @ca_contact_uuid
        end
        else
            execute usm_sp_update_userid_all_table @new_userid, @old_userid, @ca_contact_uuid

        FETCH NEXT FROM ca_contact_uuid_cursor INTO @new_userid, @old_userid, @ca_contact_uuid, @inactive_flag_new, @inactive_flag_old

    END -- while end
    CLOSE ca_contact_uuid_cursor
    DEALLOCATE ca_contact_uuid_cursor
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
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [fk_ca_contact_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [incrdjoin01] FOREIGN KEY ([department]) REFERENCES [dbo].[ca_resource_department] ([id])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [ininjoin01] FOREIGN KEY ([supervisor_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [injfjoin01] FOREIGN KEY ([job_function]) REFERENCES [dbo].[ca_job_function] ([id])
GO
ALTER TABLE [dbo].[ca_contact] ADD CONSTRAINT [inrccjoin01] FOREIGN KEY ([cost_center]) REFERENCES [dbo].[ca_resource_cost_center] ([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