CREATE TABLE [dbo].[ca_company]
(
[company_uuid] [binary] (16) NOT NULL,
[parent_company_uuid] [binary] (16) NULL,
[company_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[inactive] [int] NULL CONSTRAINT [DF__ca_compan__inact__6DEC4894] DEFAULT ('0'),
[description] [nvarchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[company_type] [int] NULL,
[alias] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[month_fiscal_year_ends] [int] NULL,
[web_address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bbs] [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,
[location_uuid] [binary] (16) NULL,
[primary_contact_uuid] [binary] (16) NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_compan__versi__6EE06CCD] DEFAULT ('0'),
[last_update_date] [int] NULL,
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[authentication_user_name] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[authentication_password] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[source_type_id] [int] NOT NULL CONSTRAINT [DF__ca_compan__sourc__6FD49106] DEFAULT ('0'),
[auto_rep_version] [timestamp] NULL,
[domain_uuid] [binary] (16) NULL,
[tenant] [binary] (16) NULL,
[bsa_flag] [int] NULL CONSTRAINT [DF__ca_compan__bsa_f__40F9A68C] DEFAULT ('0'),
[siia_flag] [int] NULL CONSTRAINT [DF__ca_compan__siia___41EDCAC5] DEFAULT ('0'),
[fast_flag] [int] NULL CONSTRAINT [DF__ca_compan__fast___42E1EEFE] DEFAULT ('0'),
[user_priority_flag] [int] NULL CONSTRAINT [DF__ca_compan__user___43D61337] DEFAULT ('0'),
[employee_count] [int] NULL,
[desktop_count] [int] NULL,
[asset_count] [int] NULL,
[authoritative] [int] NOT NULL CONSTRAINT [DF__ca_compan__autho__3704C31A] DEFAULT ('0'),
[duplicate_with_uuid] [binary] (16) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.al_u_ca_company
ON dbo.ca_company
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_company)'
    if update(company_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.company_uuid, 'ca_company', 'company_type', 0, d.company_type, i.company_type, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.company_uuid = i.company_uuid and dbo.is_different(d.company_type, i.company_type) = 1)
    end
GO
CREATE TRIGGER dbo.ca_tr_del_ca_company
ON dbo.ca_company
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_company (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
company_uuid,
parent_company_uuid,
company_name,
inactive,
description,
company_type,
alias,
month_fiscal_year_ends,
web_address,
bbs,
creation_user,
creation_date,
last_update_user,
location_uuid,
primary_contact_uuid,
version_number,
last_update_date,
exclude_registration,
delete_time,
authentication_user_name,
authentication_password,
source_type_id,
domain_uuid,
tenant,
bsa_flag,
siia_flag,
fast_flag,
user_priority_flag,
employee_count,
desktop_count,
asset_count,
authoritative,
duplicate_with_uuid) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.company_uuid,
old.parent_company_uuid,
old.company_name,
old.inactive,
old.description,
old.company_type,
old.alias,
old.month_fiscal_year_ends,
old.web_address,
old.bbs,
old.creation_user,
old.creation_date,
old.last_update_user,
old.location_uuid,
old.primary_contact_uuid,
old.version_number,
old.last_update_date,
old.exclude_registration,
old.delete_time,
old.authentication_user_name,
old.authentication_password,
old.source_type_id,
old.domain_uuid,
old.tenant,
old.bsa_flag,
old.siia_flag,
old.fast_flag,
old.user_priority_flag,
old.employee_count,
old.desktop_count,
old.asset_count,
old.authoritative,
old.duplicate_with_uuid
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ca_company
ON dbo.ca_company
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_company (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
company_uuid,
parent_company_uuid,
company_name,
inactive,
description,
company_type,
alias,
month_fiscal_year_ends,
web_address,
bbs,
creation_user,
creation_date,
last_update_user,
location_uuid,
primary_contact_uuid,
version_number,
last_update_date,
exclude_registration,
delete_time,
authentication_user_name,
authentication_password,
source_type_id,
domain_uuid,
tenant,
bsa_flag,
siia_flag,
fast_flag,
user_priority_flag,
employee_count,
desktop_count,
asset_count,
authoritative,
duplicate_with_uuid) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.company_uuid,
new.parent_company_uuid,
new.company_name,
new.inactive,
new.description,
new.company_type,
new.alias,
new.month_fiscal_year_ends,
new.web_address,
new.bbs,
new.creation_user,
new.creation_date,
new.last_update_user,
new.location_uuid,
new.primary_contact_uuid,
new.version_number,
new.last_update_date,
new.exclude_registration,
new.delete_time,
new.authentication_user_name,
new.authentication_password,
new.source_type_id,
new.domain_uuid,
new.tenant,
new.bsa_flag,
new.siia_flag,
new.fast_flag,
new.user_priority_flag,
new.employee_count,
new.desktop_count,
new.asset_count,
new.authoritative,
new.duplicate_with_uuid
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ca_company
ON dbo.ca_company
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_company (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
company_uuid,
parent_company_uuid,
company_name,
inactive,
description,
company_type,
alias,
month_fiscal_year_ends,
web_address,
bbs,
creation_user,
creation_date,
last_update_user,
location_uuid,
primary_contact_uuid,
version_number,
last_update_date,
exclude_registration,
delete_time,
authentication_user_name,
authentication_password,
source_type_id,
domain_uuid,
tenant,
bsa_flag,
siia_flag,
fast_flag,
user_priority_flag,
employee_count,
desktop_count,
asset_count,
authoritative,
duplicate_with_uuid) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.company_uuid,
new.parent_company_uuid,
new.company_name,
new.inactive,
new.description,
new.company_type,
new.alias,
new.month_fiscal_year_ends,
new.web_address,
new.bbs,
new.creation_user,
new.creation_date,
new.last_update_user,
new.location_uuid,
new.primary_contact_uuid,
new.version_number,
new.last_update_date,
new.exclude_registration,
new.delete_time,
new.authentication_user_name,
new.authentication_password,
new.source_type_id,
new.domain_uuid,
new.tenant,
new.bsa_flag,
new.siia_flag,
new.fast_flag,
new.user_priority_flag,
new.employee_count,
new.desktop_count,
new.asset_count,
new.authoritative,
new.duplicate_with_uuid
  FROM inserted new
GO
CREATE TRIGGER t_ci_ca_company ON dbo.ca_company
AFTER INSERT
AS
if update (source_type_id)
    begin

    /* If source_type_id = 1 (ca provided), then set authoritative = 1. */

    update ca_company set authoritative = 1
    where company_uuid in (select company_uuid from inserted where source_type_id = 1)


    /* If source_type_id <> 1 (not ca provided), then set authoritative = 0. */

    update ca_company set authoritative = 0
    where company_uuid in (select company_uuid from inserted where source_type_id <> 1)

    end
GO
CREATE TRIGGER [dbo].[t_u_ca_company]
ON [dbo].[ca_company]
AFTER UPDATE
AS
    if dbo.is_installed(2002) = 0
        return
   
    DECLARE @SysName varchar(10);
    DECLARE @DateInSeconds int;
    DECLARE @UpdateUser varchar(30);

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

    IF (UPDATE (company_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, 4, d.company_uuid,
               t.tenant, 1, @UpdateUser,
               @DateInSeconds, @UpdateUser, @DateInSeconds, 0
          from inserted i
          join deleted d
             on (d.company_uuid = i.company_uuid and
                 (dbo.is_different(d.company_name, i.company_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.company_uuid = a.manufacturer_uuid
                          and (t.tenant = a.tenant or
                               (t.tenant is null and a.tenant is null)))
    END
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [XPKca_company] PRIMARY KEY CLUSTERED ([company_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_company_idx_01] ON [dbo].[ca_company] ([company_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_company_idx_02] ON [dbo].[ca_company] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [ca_company_fk01] FOREIGN KEY ([primary_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [ca_company_fk02] FOREIGN KEY ([company_type]) REFERENCES [dbo].[ca_company_type] ([id])
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [ca_company_fk03] FOREIGN KEY ([parent_company_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [ca_company_fk04] FOREIGN KEY ([location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [cosljoin01] FOREIGN KEY ([month_fiscal_year_ends]) REFERENCES [dbo].[al_months] ([id])
GO
ALTER TABLE [dbo].[ca_company] ADD CONSTRAINT [fk_ca_company_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[ca_company] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_company] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_company] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_company] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_company] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_company] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_company] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_company] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_company] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_company] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_company] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_company] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_company] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_company] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_company] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_company] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_company] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_company] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_company] TO [upmadmin_group]
GRANT INSERT ON  [dbo].[ca_company] TO [upmadmin_group]
GRANT DELETE ON  [dbo].[ca_company] TO [upmadmin_group]
GRANT UPDATE ON  [dbo].[ca_company] TO [upmadmin_group]
GRANT SELECT ON  [dbo].[ca_company] TO [upmuser_group]
GRANT INSERT ON  [dbo].[ca_company] TO [upmuser_group]
GRANT DELETE ON  [dbo].[ca_company] TO [upmuser_group]
GRANT UPDATE ON  [dbo].[ca_company] TO [upmuser_group]
GO