CREATE TABLE [dbo].[ca_owned_resource]
(
[own_resource_uuid] [binary] (16) NOT NULL,
[inactive] [int] NOT NULL CONSTRAINT [DF__ca_owned___inact__3DD3211E] DEFAULT ('0'),
[asset_type_id] [int] NOT NULL CONSTRAINT [DF__ca_owned___asset__3EC74557] DEFAULT ('1'),
[resource_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[resource_description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[resource_family] [int] NULL,
[resource_class] [int] NOT NULL CONSTRAINT [DF__ca_owned___resou__3FBB6990] DEFAULT ('0'),
[resource_status] [int] NULL,
[manufacturer_uuid] [binary] (16) NULL,
[responsible_vendor_uuid] [binary] (16) NULL,
[maintenance_org_uuid] [binary] (16) NULL,
[responsible_org_uuid] [binary] (16) NULL,
[org_bought_for_uuid] [binary] (16) NULL,
[resource_contact_uuid] [binary] (16) NULL,
[resource_owner_uuid] [binary] (16) NULL,
[location_uuid] [binary] (16) NULL,
[floor_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[room_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cabinet_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shelf_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[slot_location] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[model_uuid] [binary] (16) NULL,
[host_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mac_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ip_address] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[resource_tag] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[operating_system] [int] NULL,
[product_version] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[serial_number] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[acquire_date] [int] NULL,
[installation_date] [int] NULL,
[cost_center] [int] NULL,
[gl_code] [int] NULL,
[resource_quantity] [int] NULL,
[requisition_id] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[purchase_order_id] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ufam] [smallint] NULL CONSTRAINT [DF__ca_owned_r__ufam__40AF8DC9] 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_owned___versi__41A3B202] DEFAULT ('0'),
[supply_vendor_uuid] [binary] (16) NULL,
[maintenance_vendor_uuid] [binary] (16) NULL,
[company_bought_for_uuid] [binary] (16) NULL,
[resource_capacity_unit] [int] NULL,
[resource_capacity] [float] NULL,
[resource_alias] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[asset_source_uuid] [binary] (16) NULL,
[license_uuid] [binary] (16) NULL,
[exclude_registration] [int] NULL,
[delete_time] [int] NULL,
[department] [int] NULL,
[status_date] [int] NULL,
[license_information] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[resource_subclass] [int] NULL,
[audit_date] [int] NULL,
[exclude_reconciliation] [smallint] NULL,
[dns_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alternate_host_name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[discovery_last_run_date] [int] NULL,
[previous_resource_tag] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[processor_count] [int] NULL,
[processor_speed] [float] NULL,
[processor_speed_unit] [int] NULL,
[processor_type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[reconciliation_date] [int] NULL,
[total_disk_space] [float] NULL,
[total_disk_space_unit] [int] NULL,
[total_memory] [float] NULL,
[total_memory_unit] [int] NULL,
[billing_contact_uuid] [binary] (16) NULL,
[support_contact1_uuid] [binary] (16) NULL,
[support_contact2_uuid] [binary] (16) NULL,
[support_contact3_uuid] [binary] (16) NULL,
[disaster_recovery_contact_uuid] [binary] (16) NULL,
[backup_services_contact_uuid] [binary] (16) NULL,
[network_contact_uuid] [binary] (16) NULL,
[tenant] [binary] (16) NULL,
[license_id] [int] NULL,
[resource_deployment_status] [int] NULL,
[is_asset] [smallint] NULL CONSTRAINT [DF__ca_owned___is_as__63D8CE75] DEFAULT ('0'),
[is_ci] [smallint] NULL CONSTRAINT [DF__ca_owned___is_ci__64CCF2AE] DEFAULT ('0'),
[lifecycle_status] [int] NULL,
[lifecycle_status_date] [int] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.al_d_ca_owned_resource
ON dbo.ca_owned_resource
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_owned_resource)'
    insert into al_attribute_tracking (object_uuid, table_name, [action], creation_user, creation_date, last_update_user, last_update_date)
        select own_resource_uuid, 'ca_owned_resource', 2, @user_name, @update_date, @user_name, @update_date from deleted
            where asset_type_id = 1
GO
create trigger al_i_ca_owned_resource
ON ca_owned_resource
after INSERT as
BEGIN
    DECLARE @licenseid int
    DECLARE @ownresourceuuid binary(16)    

    SELECT  @licenseid = license_id,
            @ownresourceuuid = own_resource_uuid
    FROM INSERTED

    /* Only execute if both SwCM and ITAM Installed
       with License ID supplied.
    */
    IF dbo.is_installed(2022) = 0 OR
       dbo.is_installed(2002) = 0 OR
       @licenseid IS NULL
        RETURN

    /* Check if relative License is without Asset ID */
    IF (SELECT count(0) FROM al_license
        WHERE license_id = @licenseid
        AND own_resource_uuid IS NULL) = 1
    BEGIN
        UPDATE al_license
        SET own_resource_uuid = @ownresourceuuid
        WHERE license_id = @licenseid
    END    
END
GO
CREATE TRIGGER dbo.al_u_ca_owned_resource
ON dbo.ca_owned_resource
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_owned_resource)'
    if update(resource_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.own_resource_uuid, 'ca_owned_resource', 'resource_contact_uuid', 0, d.resource_contact_uuid, i.resource_contact_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.resource_contact_uuid, i.resource_contact_uuid) = 1)
                where i.asset_type_id = 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.own_resource_uuid, 'ca_owned_resource', '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.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.location_uuid, i.location_uuid) = 1)
                where i.asset_type_id = 1
    end
    if update(company_bought_for_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.own_resource_uuid, 'ca_owned_resource', 'company_bought_for_uuid', 0, d.company_bought_for_uuid, i.company_bought_for_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.company_bought_for_uuid, i.company_bought_for_uuid) = 1)
                where i.asset_type_id = 1
    end
    if update(org_bought_for_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.own_resource_uuid, 'ca_owned_resource', 'org_bought_for_uuid', 0, d.org_bought_for_uuid, i.org_bought_for_uuid, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.org_bought_for_uuid, i.org_bought_for_uuid) = 1)
                where i.asset_type_id = 1
    end
    if dbo.is_installed(2002) = 0
        return
    if update(resource_class)
    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.own_resource_uuid, 'ca_owned_resource', 'resource_class', 0, d.resource_class, i.resource_class, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.resource_class, i.resource_class) = 1)
                where i.asset_type_id = 1
    end
    if update(resource_deployment_status)
    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.own_resource_uuid, 'ca_owned_resource', 'resource_deployment_status', 0, d.resource_deployment_status, i.resource_deployment_status, @user_name, @update_date, @user_name, @update_date
                from inserted i inner join deleted d on (d.own_resource_uuid = i.own_resource_uuid and dbo.is_different(d.resource_deployment_status, i.resource_deployment_status) = 1)
                where i.asset_type_id = 1
    end
GO
CREATE TRIGGER dbo.ca_tr_del_ca_owned_resource
ON dbo.ca_owned_resource
FOR DELETE AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_owned_resource (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
own_resource_uuid,
inactive,
asset_type_id,
resource_name,
resource_description,
resource_family,
resource_class,
resource_status,
manufacturer_uuid,
responsible_vendor_uuid,
maintenance_org_uuid,
responsible_org_uuid,
org_bought_for_uuid,
resource_contact_uuid,
resource_owner_uuid,
location_uuid,
floor_location,
room_location,
cabinet_location,
shelf_location,
slot_location,
model_uuid,
host_name,
mac_address,
ip_address,
resource_tag,
operating_system,
product_version,
serial_number,
acquire_date,
installation_date,
cost_center,
gl_code,
resource_quantity,
requisition_id,
purchase_order_id,
ufam,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
supply_vendor_uuid,
maintenance_vendor_uuid,
company_bought_for_uuid,
resource_capacity_unit,
resource_capacity,
resource_alias,
asset_source_uuid,
license_uuid,
exclude_registration,
delete_time,
department,
status_date,
license_information,
resource_subclass,
audit_date,
exclude_reconciliation,
dns_name,
alternate_host_name,
discovery_last_run_date,
previous_resource_tag,
processor_count,
processor_speed,
processor_speed_unit,
processor_type,
reconciliation_date,
total_disk_space,
total_disk_space_unit,
total_memory,
total_memory_unit,
billing_contact_uuid,
support_contact1_uuid,
support_contact2_uuid,
support_contact3_uuid,
disaster_recovery_contact_uuid,
backup_services_contact_uuid,
network_contact_uuid,
tenant,
license_id,
resource_deployment_status,
is_asset,
is_ci,
lifecycle_status,
lifecycle_status_date) SELECT

@audit_product,old.last_update_user, 'DELETE', datediff(ss, '1/1/1970', getutcdate()),
old.own_resource_uuid,
old.inactive,
old.asset_type_id,
old.resource_name,
old.resource_description,
old.resource_family,
old.resource_class,
old.resource_status,
old.manufacturer_uuid,
old.responsible_vendor_uuid,
old.maintenance_org_uuid,
old.responsible_org_uuid,
old.org_bought_for_uuid,
old.resource_contact_uuid,
old.resource_owner_uuid,
old.location_uuid,
old.floor_location,
old.room_location,
old.cabinet_location,
old.shelf_location,
old.slot_location,
old.model_uuid,
old.host_name,
old.mac_address,
old.ip_address,
old.resource_tag,
old.operating_system,
old.product_version,
old.serial_number,
old.acquire_date,
old.installation_date,
old.cost_center,
old.gl_code,
old.resource_quantity,
old.requisition_id,
old.purchase_order_id,
old.ufam,
old.creation_user,
old.creation_date,
old.last_update_user,
old.last_update_date,
old.version_number,
old.supply_vendor_uuid,
old.maintenance_vendor_uuid,
old.company_bought_for_uuid,
old.resource_capacity_unit,
old.resource_capacity,
old.resource_alias,
old.asset_source_uuid,
old.license_uuid,
old.exclude_registration,
old.delete_time,
old.department,
old.status_date,
old.license_information,
old.resource_subclass,
old.audit_date,
old.exclude_reconciliation,
old.dns_name,
old.alternate_host_name,
old.discovery_last_run_date,
old.previous_resource_tag,
old.processor_count,
old.processor_speed,
old.processor_speed_unit,
old.processor_type,
old.reconciliation_date,
old.total_disk_space,
old.total_disk_space_unit,
old.total_memory,
old.total_memory_unit,
old.billing_contact_uuid,
old.support_contact1_uuid,
old.support_contact2_uuid,
old.support_contact3_uuid,
old.disaster_recovery_contact_uuid,
old.backup_services_contact_uuid,
old.network_contact_uuid,
old.tenant,
old.license_id,
old.resource_deployment_status,
old.is_asset,
old.is_ci,
old.lifecycle_status,
old.lifecycle_status_date
FROM deleted old
GO
CREATE TRIGGER dbo.ca_tr_ins_ca_owned_resource
ON dbo.ca_owned_resource
FOR INSERT AS
DECLARE
        @audit_product nvarchar(64)
        SET @audit_product = APP_NAME()
INSERT INTO dbo.aud_ca_owned_resource (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
own_resource_uuid,
inactive,
asset_type_id,
resource_name,
resource_description,
resource_family,
resource_class,
resource_status,
manufacturer_uuid,
responsible_vendor_uuid,
maintenance_org_uuid,
responsible_org_uuid,
org_bought_for_uuid,
resource_contact_uuid,
resource_owner_uuid,
location_uuid,
floor_location,
room_location,
cabinet_location,
shelf_location,
slot_location,
model_uuid,
host_name,
mac_address,
ip_address,
resource_tag,
operating_system,
product_version,
serial_number,
acquire_date,
installation_date,
cost_center,
gl_code,
resource_quantity,
requisition_id,
purchase_order_id,
ufam,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
supply_vendor_uuid,
maintenance_vendor_uuid,
company_bought_for_uuid,
resource_capacity_unit,
resource_capacity,
resource_alias,
asset_source_uuid,
license_uuid,
exclude_registration,
delete_time,
department,
status_date,
license_information,
resource_subclass,
audit_date,
exclude_reconciliation,
dns_name,
alternate_host_name,
discovery_last_run_date,
previous_resource_tag,
processor_count,
processor_speed,
processor_speed_unit,
processor_type,
reconciliation_date,
total_disk_space,
total_disk_space_unit,
total_memory,
total_memory_unit,
billing_contact_uuid,
support_contact1_uuid,
support_contact2_uuid,
support_contact3_uuid,
disaster_recovery_contact_uuid,
backup_services_contact_uuid,
network_contact_uuid,
tenant,
license_id,
resource_deployment_status,
is_asset,
is_ci,
lifecycle_status,
lifecycle_status_date) SELECT

@audit_product, new.last_update_user, 'INSERT', datediff(ss, '1/1/1970', getutcdate()),
new.own_resource_uuid,
new.inactive,
new.asset_type_id,
new.resource_name,
new.resource_description,
new.resource_family,
new.resource_class,
new.resource_status,
new.manufacturer_uuid,
new.responsible_vendor_uuid,
new.maintenance_org_uuid,
new.responsible_org_uuid,
new.org_bought_for_uuid,
new.resource_contact_uuid,
new.resource_owner_uuid,
new.location_uuid,
new.floor_location,
new.room_location,
new.cabinet_location,
new.shelf_location,
new.slot_location,
new.model_uuid,
new.host_name,
new.mac_address,
new.ip_address,
new.resource_tag,
new.operating_system,
new.product_version,
new.serial_number,
new.acquire_date,
new.installation_date,
new.cost_center,
new.gl_code,
new.resource_quantity,
new.requisition_id,
new.purchase_order_id,
new.ufam,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.supply_vendor_uuid,
new.maintenance_vendor_uuid,
new.company_bought_for_uuid,
new.resource_capacity_unit,
new.resource_capacity,
new.resource_alias,
new.asset_source_uuid,
new.license_uuid,
new.exclude_registration,
new.delete_time,
new.department,
new.status_date,
new.license_information,
new.resource_subclass,
new.audit_date,
new.exclude_reconciliation,
new.dns_name,
new.alternate_host_name,
new.discovery_last_run_date,
new.previous_resource_tag,
new.processor_count,
new.processor_speed,
new.processor_speed_unit,
new.processor_type,
new.reconciliation_date,
new.total_disk_space,
new.total_disk_space_unit,
new.total_memory,
new.total_memory_unit,
new.billing_contact_uuid,
new.support_contact1_uuid,
new.support_contact2_uuid,
new.support_contact3_uuid,
new.disaster_recovery_contact_uuid,
new.backup_services_contact_uuid,
new.network_contact_uuid,
new.tenant,
new.license_id,
new.resource_deployment_status,
new.is_asset,
new.is_ci,
new.lifecycle_status,
new.lifecycle_status_date
FROM inserted new
GO
CREATE TRIGGER dbo.ca_tr_upd_ca_owned_resource
-- 12/7/2009 swawi03 - When saving an asset with CORA enabled, APM clears the asset_source_uuid on CORA
-- de-registration and sets it on CORA registration.  In order to avoid three audit records for one asset
-- update the last_update_user is suffixed with '_APM-CORA' and the trigger checks to see if the last_update_user
-- has this substring and does not create the audit record if found. '_APM-CORA' is only set when asset_source_uuid is updated.
ON dbo.ca_owned_resource
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
    IF dbo.instr2_varchar(@last_update_user,'APM-CORA') = 0
INSERT INTO dbo.aud_ca_owned_resource (
AUDIT_TRAIL_PRODUCT,
AUDIT_TRAIL_USER,
AUDIT_TRAIL_TYPE,
AUDIT_TRAIL_DATE,
own_resource_uuid,
inactive,
asset_type_id,
resource_name,
resource_description,
resource_family,
resource_class,
resource_status,
manufacturer_uuid,
responsible_vendor_uuid,
maintenance_org_uuid,
responsible_org_uuid,
org_bought_for_uuid,
resource_contact_uuid,
resource_owner_uuid,
location_uuid,
floor_location,
room_location,
cabinet_location,
shelf_location,
slot_location,
model_uuid,
host_name,
mac_address,
ip_address,
resource_tag,
operating_system,
product_version,
serial_number,
acquire_date,
installation_date,
cost_center,
gl_code,
resource_quantity,
requisition_id,
purchase_order_id,
ufam,
creation_user,
creation_date,
last_update_user,
last_update_date,
version_number,
supply_vendor_uuid,
maintenance_vendor_uuid,
company_bought_for_uuid,
resource_capacity_unit,
resource_capacity,
resource_alias,
asset_source_uuid,
license_uuid,
exclude_registration,
delete_time,
department,
status_date,
license_information,
resource_subclass,
audit_date,
exclude_reconciliation,
dns_name,
alternate_host_name,
discovery_last_run_date,
previous_resource_tag,
processor_count,
processor_speed,
processor_speed_unit,
processor_type,
reconciliation_date,
total_disk_space,
total_disk_space_unit,
total_memory,
total_memory_unit,
billing_contact_uuid,
support_contact1_uuid,
support_contact2_uuid,
support_contact3_uuid,
disaster_recovery_contact_uuid,
backup_services_contact_uuid,
network_contact_uuid,
tenant,
license_id,
resource_deployment_status,
is_asset,
is_ci,
lifecycle_status,
lifecycle_status_date) SELECT

@audit_product, new.last_update_user, 'UPDATE', datediff(ss, '1/1/1970', getutcdate()),
new.own_resource_uuid,
new.inactive,
new.asset_type_id,
new.resource_name,
new.resource_description,
new.resource_family,
new.resource_class,
new.resource_status,
new.manufacturer_uuid,
new.responsible_vendor_uuid,
new.maintenance_org_uuid,
new.responsible_org_uuid,
new.org_bought_for_uuid,
new.resource_contact_uuid,
new.resource_owner_uuid,
new.location_uuid,
new.floor_location,
new.room_location,
new.cabinet_location,
new.shelf_location,
new.slot_location,
new.model_uuid,
new.host_name,
new.mac_address,
new.ip_address,
new.resource_tag,
new.operating_system,
new.product_version,
new.serial_number,
new.acquire_date,
new.installation_date,
new.cost_center,
new.gl_code,
new.resource_quantity,
new.requisition_id,
new.purchase_order_id,
new.ufam,
new.creation_user,
new.creation_date,
new.last_update_user,
new.last_update_date,
new.version_number,
new.supply_vendor_uuid,
new.maintenance_vendor_uuid,
new.company_bought_for_uuid,
new.resource_capacity_unit,
new.resource_capacity,
new.resource_alias,
new.asset_source_uuid,
new.license_uuid,
new.exclude_registration,
new.delete_time,
new.department,
new.status_date,
new.license_information,
new.resource_subclass,
new.audit_date,
new.exclude_reconciliation,
new.dns_name,
new.alternate_host_name,
new.discovery_last_run_date,
new.previous_resource_tag,
new.processor_count,
new.processor_speed,
new.processor_speed_unit,
new.processor_type,
new.reconciliation_date,
new.total_disk_space,
new.total_disk_space_unit,
new.total_memory,
new.total_memory_unit,
new.billing_contact_uuid,
new.support_contact1_uuid,
new.support_contact2_uuid,
new.support_contact3_uuid,
new.disaster_recovery_contact_uuid,
new.backup_services_contact_uuid,
new.network_contact_uuid,
new.tenant,
new.license_id,
new.resource_deployment_status,
new.is_asset,
new.is_ci,
new.lifecycle_status,
new.lifecycle_status_date
FROM inserted new
GO
CREATE TRIGGER dbo.t_d_ca_owned_resource
ON dbo.ca_owned_resource
AFTER DELETE
AS
BEGIN
DECLARE
        @vCount int,
        @vDisHwUUID binary(16),
        @vSysName varchar(10),
        @vDateInSeconds int,
        @vUpdateUser varchar(30),
        @vStartDays int,
        @vEndDays int,
        @vEndSeconds int;
     
    SET @vCount = 0;
        SET @vSysName = 'ITAM';
        SET @vDateInSeconds = 0;
        SET @vUpdateUser = 't_d_ca_owned_resource';
        SET @vStartDays = 2440588;  /* 1/1/1970 in Julian format. */

    /* Check whether the owned resource record is linked to a discovered hardware record. */
    /* If it is, then continue.                                                           */
    /* Count is checked first to avoid NO_DATA_FOUND error when using SELECT INTO.        */

    SELECT @vCount = count(*) FROM dbo.arg_reconcile_links as a, deleted as d WHERE a.own_resource_uuid = d.own_resource_uuid;

    IF @vCount = 1 BEGIN

        /* Capture the dis_hw_uuid of the linked discovered asset. */

        SELECT @vDisHwUUID = dis_hw_uuid FROM dbo.arg_reconcile_links as a, deleted as d WHERE a.own_resource_uuid = d.own_resource_uuid;

        /* Delete the arg_reconcile_links record. */

        DELETE  FROM arg_reconcile_links where own_resource_uuid in ( select own_resource_uuid from deleted);
        

        /* Check whether an arg_reconcile_modification record already exists for this asset. */
          /* If not, BEGIN continue.                                                            */

        SELECT @vCount = count(*) FROM dbo.arg_reconcile_modification as a, deleted as d WHERE a.own_resource_uuid = d.own_resource_uuid
        AND sys_name = @vSysName;

        IF @vCount = 0 BEGIN

            /* Set up creation_date, last_update_date  */
            /*prasu05 - needs to revisit and correct, for now adding dummy values
            @vEndDays := TO_NUMBER(TO_CHAR(SYSDATE, 'J'));
            @vEndSeconds := TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS'));
            @vDateInSeconds := ((@vEndDays - @vStartDays) * 86400) + @vEndSeconds; */

        SET @vEndDays = 1;
            SET @vEndSeconds = 2;
            SET @vDateInSeconds = 3;
            
            
            /* Create the arg_reconcile_modification record. */

        INSERT INTO dbo.arg_reconcile_modification
                       (sys_name,
                    reconcile_action,
                    dis_hw_uuid,
                    own_resource_uuid,
                    own_resource_serial_num,
                    own_resource_host_name,
                    own_resource_alias,
                    own_resource_alternate_id,
                    tenant,
                    subschema_id,
                    creation_user,
                    creation_date,
                    last_update_user,
                    last_update_date,
                    version_number)

            select
                @vSysName,
                3,
                @vDisHwUUID,
                deleted.own_resource_uuid,
                deleted.serial_number,
                deleted.host_name,
                deleted.resource_alias,
                deleted.resource_tag,
                deleted.tenant,
                1,
                @vUpdateUser,
                @vDateInSeconds,
                @vUpdateUser,
                @vDateInSeconds,
                0 from deleted;
        END
    END
END
GO
CREATE trigger dbo.t_u_ca_owned_resource
   on dbo.ca_owned_resource
   after update
as
BEGIN
    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_owned_resource';

    IF (UPDATE(alternate_host_name) or UPDATE(resource_tag) or UPDATE(resource_alias) or
        UPDATE(resource_name) or UPDATE(resource_class) or UPDATE(host_name) or
        UPDATE(manufacturer_uuid) or UPDATE(model_uuid) or UPDATE(previous_resource_tag) or
        UPDATE(serial_number) or UPDATE(resource_subclass) or UPDATE(inactive) or
        UPDATE(exclude_reconciliation) or UPDATE(ufam) or UPDATE(mac_address))
    BEGIN
        insert into arg_reconcile_modification
                    (sys_name, reconcile_action,
                     own_resource_uuid, dis_hw_uuid,
                     tenant, subschema_id, creation_user, creation_date,
                     last_update_user, last_update_date, version_number)
        select @SysName, 2,
               d.own_resource_uuid, lnk.dis_hw_uuid,
               d.tenant, 1,
               @UpdateUser, @DateInSeconds, @UpdateUser, @DateInSeconds, 0
          from inserted i inner join deleted d  
            on (d.own_resource_uuid = i.own_resource_uuid and
                ((dbo.is_different(d.alternate_host_name, i.alternate_host_name) = 1) or
                  (dbo.is_different(d.resource_tag, i.resource_tag) = 1) or
                  (dbo.is_different(d.resource_alias, i.resource_alias) = 1) or
                  (dbo.is_different(d.resource_name, i.resource_name) = 1) or
                  (dbo.is_different(d.resource_class, i.resource_class) = 1) or
                  (dbo.is_different(d.host_name, i.host_name) = 1) or
                  (dbo.is_different(d.manufacturer_uuid, i.manufacturer_uuid) = 1) or
                  (dbo.is_different(d.model_uuid, i.model_uuid) = 1) or
                  (dbo.is_different(d.previous_resource_tag, i.previous_resource_tag) = 1) or
                  (dbo.is_different(d.serial_number, i.serial_number) = 1) or
                  (dbo.is_different(d.resource_subclass, i.resource_subclass) = 1) or
                  (dbo.is_different(d.mac_address, i.mac_address) = 1) or
                  (dbo.is_different(d.inactive, i.inactive) = 1 and i.inactive = 1) or
                  (dbo.is_different(d.exclude_reconciliation, i.exclude_reconciliation) = 1 and i.exclude_reconciliation = 1) or
                  (dbo.is_different(d.ufam, i.ufam) = 1 and i.ufam = 0)))
          join arg_reconcile_links lnk
            on d.own_resource_uuid = lnk.own_resource_uuid
    END            
END
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [XPKca_owned_resource] PRIMARY KEY CLUSTERED ([own_resource_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_17] ON [dbo].[ca_owned_resource] ([asset_source_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_07] ON [dbo].[ca_owned_resource] ([company_bought_for_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_20] ON [dbo].[ca_owned_resource] ([dns_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_02] ON [dbo].[ca_owned_resource] ([host_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_04] ON [dbo].[ca_owned_resource] ([ip_address]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_21] ON [dbo].[ca_owned_resource] ([last_update_date]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_19] ON [dbo].[ca_owned_resource] ([license_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_09] ON [dbo].[ca_owned_resource] ([location_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_03] ON [dbo].[ca_owned_resource] ([mac_address]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_05] ON [dbo].[ca_owned_resource] ([model_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_13] ON [dbo].[ca_owned_resource] ([purchase_order_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_14] ON [dbo].[ca_owned_resource] ([requisition_id]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_10] ON [dbo].[ca_owned_resource] ([resource_alias]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_15] ON [dbo].[ca_owned_resource] ([resource_capacity_unit]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_16] ON [dbo].[ca_owned_resource] ([resource_class]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_08] ON [dbo].[ca_owned_resource] ([resource_contact_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_01] ON [dbo].[ca_owned_resource] ([resource_name]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_18] ON [dbo].[ca_owned_resource] ([resource_subclass]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_11] ON [dbo].[ca_owned_resource] ([resource_tag]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_12] ON [dbo].[ca_owned_resource] ([serial_number]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_06] ON [dbo].[ca_owned_resource] ([supply_vendor_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_owned_resource_idx_22] ON [dbo].[ca_owned_resource] ([tenant]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [atlijoin01] FOREIGN KEY ([license_uuid]) REFERENCES [dbo].[ca_software_license] ([license_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk01] FOREIGN KEY ([asset_type_id]) REFERENCES [dbo].[ca_asset_type] ([asset_type_id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk02] FOREIGN KEY ([location_uuid]) REFERENCES [dbo].[ca_location] ([location_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk03] FOREIGN KEY ([resource_owner_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk04] FOREIGN KEY ([resource_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk05] FOREIGN KEY ([org_bought_for_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk06] FOREIGN KEY ([responsible_org_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk07] FOREIGN KEY ([maintenance_org_uuid]) REFERENCES [dbo].[ca_organization] ([organization_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk08] FOREIGN KEY ([responsible_vendor_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk09] FOREIGN KEY ([manufacturer_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk10] FOREIGN KEY ([resource_capacity_unit]) REFERENCES [dbo].[ca_capacity_unit] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk11] FOREIGN KEY ([model_uuid]) REFERENCES [dbo].[ca_model_def] ([model_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk12] FOREIGN KEY ([resource_status]) REFERENCES [dbo].[ca_resource_status] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk13] FOREIGN KEY ([resource_class]) REFERENCES [dbo].[ca_resource_class] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk14] FOREIGN KEY ([resource_family]) REFERENCES [dbo].[ca_resource_family] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk15] FOREIGN KEY ([supply_vendor_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk16] FOREIGN KEY ([maintenance_vendor_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk17] FOREIGN KEY ([company_bought_for_uuid]) REFERENCES [dbo].[ca_company] ([company_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk19] FOREIGN KEY ([lifecycle_status]) REFERENCES [dbo].[ca_asset_lifecycle_status] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk20] FOREIGN KEY ([backup_services_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk21] FOREIGN KEY ([network_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk22] FOREIGN KEY ([billing_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk23] FOREIGN KEY ([support_contact1_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk24] FOREIGN KEY ([support_contact2_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk25] FOREIGN KEY ([support_contact3_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk26] FOREIGN KEY ([disaster_recovery_contact_uuid]) REFERENCES [dbo].[ca_contact] ([contact_uuid])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk27] FOREIGN KEY ([processor_speed_unit]) REFERENCES [dbo].[ca_processor_speed_units] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk28] FOREIGN KEY ([total_disk_space_unit]) REFERENCES [dbo].[ca_disk_space_units] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk29] FOREIGN KEY ([total_memory_unit]) REFERENCES [dbo].[ca_disk_space_units] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk30] FOREIGN KEY ([resource_subclass]) REFERENCES [dbo].[ca_resource_class] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk31] FOREIGN KEY ([cost_center]) REFERENCES [dbo].[ca_resource_cost_center] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk32] FOREIGN KEY ([department]) REFERENCES [dbo].[ca_resource_department] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk33] FOREIGN KEY ([gl_code]) REFERENCES [dbo].[ca_resource_gl_code] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [ca_owned_resource_fk34] FOREIGN KEY ([operating_system]) REFERENCES [dbo].[ca_resource_operating_system] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [fk_ca_own_res_deploy_sts] FOREIGN KEY ([resource_deployment_status]) REFERENCES [dbo].[ca_resource_deployment_status] ([id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [FK_ca_owned_resource_al_license] FOREIGN KEY ([license_id]) REFERENCES [dbo].[al_license] ([license_id])
GO
ALTER TABLE [dbo].[ca_owned_resource] ADD CONSTRAINT [fk_ca_owned_resource_tenant] FOREIGN KEY ([tenant]) REFERENCES [dbo].[ca_tenant] ([id])
GO
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [aiadmin]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [ams_group]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [amsgroup]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [ca_itrm_group]
GRANT INSERT ON  [dbo].[ca_owned_resource] TO [ca_itrm_group]
GRANT DELETE ON  [dbo].[ca_owned_resource] TO [ca_itrm_group]
GRANT UPDATE ON  [dbo].[ca_owned_resource] TO [ca_itrm_group]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [ca_itrm_group_ams]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [regadmin]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [service_desk_admin_group]
GRANT INSERT ON  [dbo].[ca_owned_resource] TO [service_desk_admin_group]
GRANT DELETE ON  [dbo].[ca_owned_resource] TO [service_desk_admin_group]
GRANT UPDATE ON  [dbo].[ca_owned_resource] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [service_desk_ro_group]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [swcmadmin]
GRANT INSERT ON  [dbo].[ca_owned_resource] TO [swcmadmin]
GRANT DELETE ON  [dbo].[ca_owned_resource] TO [swcmadmin]
GRANT UPDATE ON  [dbo].[ca_owned_resource] TO [swcmadmin]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [upmuser_group]
GRANT SELECT ON  [dbo].[ca_owned_resource] TO [usmgroup]
GRANT INSERT ON  [dbo].[ca_owned_resource] TO [usmgroup]
GRANT DELETE ON  [dbo].[ca_owned_resource] TO [usmgroup]
GRANT UPDATE ON  [dbo].[ca_owned_resource] TO [usmgroup]
GO