SET QUOTED_IDENTIFIER OFF
GO

create view  ca_coapi_agent_view AS
    SELECT dhw.label AS agv_label,
                 dhw.creation_user AS agv_creation_user,
                 dhw.creation_date AS agv_creation_date,
                 dhw.last_update_user AS agv_last_update_user,
                 dhw.last_update_date AS agv_last_update_date,
                 ag.object_uuid AS ag_object_uuid,
                 ag.domain_id AS ag_domain_id,
                 ag.server_uuid AS ag_server_uuid,
                 srv.host_name AS srv_host_name,
                 ag.boot_server_uuid AS ag_boot_server_uuid,
                 ag.unit_id AS ag_unit_id,
                 ag.last_run_date AS ag_last_run_date,
                 ag.agent_type AS ag_agent_type,
                 ag.agent_lock AS ag_agent_lock,
                 ag.agent_name AS ag_agent_name,
                 ag.agent_file_id AS ag_agent_file_id,
                 ag.agent_area AS ag_agent_area,
                 ag.agent_version AS ag_agent_version,
                 ag.description AS ag_description,
                 ag.prev_manager AS ag_prev_manager,
                 ag.ip_address AS ag_ip_address,
                 ag.registration_type AS ag_registration_type,
                 ag.proc_os_id AS ag_proc_os_id,
                 ag.class_id AS ag_class_id,
                 ag.derived_status_am AS ag_derived_status_am,
                 ag.derived_status_sd AS ag_derived_status_sd,
                 ag.derived_status_rc AS ag_derived_status_rc,
                 ag.derived_status_babld AS ag_derived_status_babld,
                 ag.user_def1 AS ag_user_def1,
                 ag.user_def2 AS ag_user_def2,
                 ag.user_def3 AS ag_user_def3,
                 ag.user_def4 AS ag_user_def4,
                 ag.creation_user AS ag_creation_user,
                 ag.creation_date AS ag_creation_date,
                 ag.last_update_date AS ag_last_update_date,
                 ag.last_update_user AS ag_last_update_user,
                 ag.version_number AS ag_version_number,
                 dom.label AS dom_label,
                 dhw.label AS dhw_label,
                 dhw.domain_uuid AS dhw_domain_uuid,
                 dhw.dis_hw_uuid AS dhw_dis_hw_uuid,
                 dhw.asset_source_uuid AS dhw_asset_source_uuid,
                 dhw.host_name AS dhw_host_name,
                 dhw.primary_network_address AS dhw_primary_network_address,
                 dhw.serial_number AS dhw_serial_number,
                 dhw.disc_serial_number AS dhw_disc_serial_number,
                 dhw.primary_mac_address AS dhw_primary_mac_address,
                 dhw.asset_tag AS dhw_asset_tag,
                 dhw.host_uuid AS dhw_host_uuid,
                 dhw.class_id AS dhw_class_id,
                 dhw.usage_list AS dhw_usage_list,
                 dhw.uri AS dhw_uri,
                 dhw.system_id AS dhw_system_id,
                 dhw.vendor_name AS dhw_vendor_name,
                 dhw.creation_user AS dhw_creation_user,
                 dhw.creation_date AS dhw_creation_date,
                 dhw.last_update_date AS dhw_last_update_date,
                 dhw.last_update_user AS dhw_last_update_user,
                 dhw.version_number AS dhw_version_number,
                 '' AS usr_label,
                 convert(binary(16),NULL) AS usr_domain_uuid,
                 convert(binary(16),NULL) AS usr_user_uuid,
                 '' AS usr_user_name,
                 convert(binary(32),0) AS usr_usage_list,
                 '' AS usr_uri,
                 '' AS usr_creation_user,
                 convert(integer,NULL) AS usr_creation_date,
                 convert(integer,NULL) AS usr_last_update_date,
                 '' AS usr_last_update_user,
                 convert(integer,NULL) AS usr_version_number,
                 '' AS cu_label,
                 convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
                 convert(binary(16),NULL) AS cu_dis_hw_uuid,
                 convert(binary(16),NULL) AS cu_user_uuid,
                 convert(integer,NULL) AS cu_ref_count,
                 convert(binary(32),0) AS cu_usage_list,
                 '' AS cu_creation_user,
                 convert(integer,NULL) AS cu_creation_date,
                 '' AS cu_last_update_user,
                 convert(integer,NULL) AS cu_last_update_date,
                 convert(integer,NULL) AS cu_version_number,
                 '' AS exa_label,
                 convert(binary(16),NULL) AS exa_device_uuid,
                 convert(binary(16),NULL) AS exa_domain_uuid,
                 '' AS exa_class_name,
                 '' AS exa_ipaddress,
                 '' AS exa_macaddress,
                 '' AS exa_last_update_user,
                 convert(integer,NULL) AS exa_last_update_date,
                 convert(integer,NULL) AS exa_creation_date,
                 '' AS exa_creation_user,
                 convert(integer,NULL) AS exa_version_number
    FROM  ca_discovered_hardware dhw,  ca_n_tier dom,  ca_agent AS ag LEFT OUTER JOIN  ca_server AS srv ON ag.server_uuid = srv.server_uuid
    WHERE (ag.object_uuid = dhw.dis_hw_uuid)
            AND (dhw.domain_uuid= dom.domain_uuid)
    UNION SELECT usr.label AS agv_label,
                 usr.creation_user AS agv_creation_user,
                 usr.creation_date AS agv_creation_date,
                 usr.last_update_user AS agv_last_update_user,
                 usr.last_update_date AS agv_last_update_date,
                 ag.object_uuid AS ag_object_uuid,
                 ag.domain_id AS ag_domain_id,
                 ag.server_uuid AS ag_server_uuid,
                 srv.host_name AS srv_host_name,
                 ag.boot_server_uuid AS ag_boot_server_uuid,
                 ag.unit_id AS ag_unit_id,
                 ag.last_run_date AS ag_last_run_date,
                 ag.agent_type AS ag_agent_type,
                 ag.agent_lock AS ag_agent_lock,
                 ag.agent_name AS ag_agent_name,
                 ag.agent_file_id AS ag_agent_file_id,
                 ag.agent_area AS ag_agent_area,
                 ag.agent_version AS ag_agent_version,
                 ag.description AS ag_description,
                 ag.prev_manager AS ag_prev_manager,
                 ag.ip_address AS ag_ip_address,
                 ag.registration_type AS ag_registration_type,
                 ag.proc_os_id AS ag_proc_os_id,
                 ag.class_id AS ag_class_id,
                 ag.derived_status_am AS ag_derived_status_am,
                 ag.derived_status_sd AS ag_derived_status_sd,
                 ag.derived_status_rc AS ag_derived_status_rc,
                 ag.derived_status_babld AS ag_derived_status_babld,
                 ag.user_def1 AS ag_user_def1,
                 ag.user_def2 AS ag_user_def2,
                 ag.user_def3 AS ag_user_def3,
                 ag.user_def4 AS ag_user_def4,
                 ag.creation_user AS ag_creation_user,
                 ag.creation_date AS ag_creation_date,
                 ag.last_update_date AS ag_last_update_date,
                 ag.last_update_user AS ag_last_update_user,
                 ag.version_number AS ag_version_number,
                 dom.label AS dom_label,
                 '' AS dhw_label,
                 convert(binary(16),NULL) AS dhw_domain_uuid,
                 convert(binary(16),NULL) AS dhw_dis_hw_uuid,
                 convert(binary(16),NULL) AS dhw_asset_source_uuid,
                 '' AS dhw_host_name,
                 '' AS dhw_primary_network_address,
                 '' AS dhw_serial_number,
                 '' AS dhw_disc_serial_number,
                 '' AS dhw_primary_mac_address,
                 '' ASdhw_asset_tag,
                 '' AS dhw_host_uuid,
                 convert(integer,NULL) AS dhw_class_id,
                 convert(binary(32),0) AS dhw_usage_list,
                 '' AS dhw_uri,
                 '' AS dhw_system_id,
                 '' AS dhw_vendor_name,
                 '' AS dhw_creation_user,
                 convert(integer,NULL) AS dhw_creation_date,
                 convert(integer,NULL) AS dhw_last_update_date,
                 '' AS dhw_last_update_user,
                 convert(integer,NULL) AS dhw_version_number,
                 usr.label AS usr_label,
                 usr.domain_uuid AS usr_domain_uuid,
                 usr.user_uuid AS usr_user_uuid,
                 usr.user_name AS usr_user_name,
                 usr.usage_list AS usr_usage_list,
                 usr.uri ASusr_uri,
                 usr.creation_user AS usr_creation_user,
                 usr.creation_date AS usr_creation_date,
                 usr.last_update_date AS usr_last_update_date,
                 usr.last_update_user AS usr_last_update_user,
                 usr.version_number AS usr_version_number,
                 '' AS cu_label,
                 convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
                 convert(binary(16),NULL) AS cu_dis_hw_uuid,
                 convert(binary(16),NULL) AS cu_user_uuid,
                 convert(integer,NULL) AS cu_ref_count,
                 convert(binary(32),0) AS cu_usage_list,
                 '' AS cu_creation_user,
                 convert(integer,NULL) AS cu_creation_date,
                 '' AS cu_last_update_user,
                 convert(integer,NULL) AS cu_last_update_date,
                 convert(integer,NULL) AS cu_version_number,
                 '' AS exa_label,
                 convert(binary(16),NULL) AS exa_device_uuid,
                 convert(binary(16),NULL) AS exa_domain_uuid,
                 '' AS exa_class_name,
             '' AS exa_ipaddress,
             '' AS exa_macaddress,
             '' AS exa_last_update_user,
             convert(integer,NULL) AS exa_last_update_date,
             convert(integer,NULL) AS exa_creation_date,
             '' AS exa_creation_user,
             convert(integer,NULL) AS exa_version_number
    FROM  ca_discovered_user usr,  ca_n_tier dom,  ca_agent AS ag LEFT OUTER JOIN  ca_server AS srv ON ag.server_uuid = srv.server_uuid
    WHERE (ag.object_uuid = usr.user_uuid)
            AND (usr.domain_uuid=dom.domain_uuid)
    UNION SELECT cu.label AS agv_label,
                 cu.creation_user AS agv_creation_user,
                 cu.creation_date AS agv_creation_date,
                 cu.last_update_user AS agv_last_update_user,
                 cu.last_update_date AS agv_last_update_date,
                 ag.object_uuid AS ag_object_uuid,
                 ag.domain_id AS ag_domain_id,
                 ag.server_uuid AS ag_server_uuid,
                 srv.host_name AS srv_host_name,
                 ag.boot_server_uuid AS ag_boot_server_uuid,
                 ag.unit_id AS ag_unit_id,
                 ag.last_run_date AS ag_last_run_date,
                 ag.agent_type AS ag_agent_type,
                 ag.agent_lock AS ag_agent_lock,
                 ag.agent_name AS ag_agent_name,
                 ag.agent_file_id AS ag_agent_file_id,
                 ag.agent_area AS ag_agent_area,
                 ag.agent_version AS ag_agent_version,
                 ag.description AS ag_description,
                 ag.prev_manager AS ag_prev_manager,
                 ag.ip_address AS ag_ip_address,
                 ag.registration_type AS ag_registration_type,
                 ag.proc_os_id AS ag_proc_os_id,
                 ag.class_id AS ag_class_id,
                 ag.derived_status_am AS ag_derived_status_am,
                 ag.derived_status_sd AS ag_derived_status_sd,
                 ag.derived_status_rc AS ag_derived_status_rc,
                 ag.derived_status_babld AS ag_derived_status_babld,
                 ag.user_def1 AS ag_user_def1,
                 ag.user_def2 AS ag_user_def2,
                 ag.user_def3 AS ag_user_def3,
                 ag.user_def4 AS ag_user_def4,
                 ag.creation_user AS ag_creation_user,
                 ag.creation_date AS ag_creation_date,
                 ag.last_update_date AS ag_last_update_date,
                 ag.last_update_user AS ag_last_update_user,
                 ag.version_number AS ag_version_number,
                 dom.label AS dom_label,
                 dhw.label AS dhw_label,
                 dhw.domain_uuid AS dhw_domain_uuid,
                 dhw.dis_hw_uuid AS dhw_dis_hw_uuid,
                 dhw.asset_source_uuid AS dhw_asset_source_uuid,
                 dhw.host_name AS dhw_host_name,
                 dhw.primary_network_address AS dhw_primary_network_address,
             dhw.serial_number AS dhw_serial_number,
             dhw.disc_serial_number AS dhw_disc_serial_number,
             dhw.primary_mac_address AS dhw_primary_mac_address,
             dhw.asset_tag AS dhw_asset_tag,
             dhw.host_uuid AS dhw_host_uuid,
             dhw.class_id AS dhw_class_id,
             dhw.usage_list AS dhw_usage_list,
             dhw.uri AS dhw_uri,
             dhw.system_id AS dhw_system_id,
             dhw.vendor_name AS dhw_vendor_name,
             dhw.creation_user AS dhw_creation_user,
             dhw.creation_date AS dhw_creation_date,
             dhw.last_update_date AS dhw_last_update_date,
             dhw.last_update_user AS dhw_last_update_user,
             dhw.version_number AS dhw_version_number,
             '' AS usr_label,
             convert(binary(16),NULL) AS usr_domain_uuid,
             convert(binary(16),NULL) AS usr_user_uuid,
             '' AS usr_user_name,
             convert(binary(32),0) AS usr_usage_list,
             '' AS usr_uri,
             '' AS usr_creation_user,
             convert(integer,NULL) AS usr_creation_date,
             convert(integer,NULL) AS usr_last_update_date,
             '' AS usr_last_update_user,
             convert(integer,NULL) AS usr_version_number,
             cu.label AS cu_label,
             cu.link_dis_hw_user_uuid AS cu_link_dis_hw_user_uuid,
             cu.dis_hw_uuid AS cu_dis_hw_uuid,
             cu.user_uuid AS cu_user_uuid,
             cu.ref_count AS cu_ref_count,
             cu.usage_list AS cu_usage_list,
             cu.creation_user AS cu_creation_user,
             cu.creation_date AS cu_creation_date,
             cu.last_update_user AS cu_last_update_user,
             cu.last_update_date AS cu_last_update_date,
             cu.version_number AS cu_version_number,
             '' AS exa_label,
             convert(binary(16),NULL) AS exa_device_uuid,
             convert(binary(16),NULL) AS exa_domain_uuid,
             '' AS exa_class_name,
             '' AS exa_ipaddress,
             '' AS exa_macaddress,
             '' AS exa_last_update_user,
             convert(integer,NULL) AS exa_last_update_date,
             convert(integer,NULL) AS exa_creation_date,
             '' AS exa_creation_user,
             convert(integer,NULL) AS exa_version_number
    FROM  ca_link_dis_hw_user cu,  ca_discovered_hardware dhw,  ca_n_tier dom,  ca_agent AS ag LEFT OUTER JOIN  ca_server AS srv ON ag.server_uuid = srv.server_uuid
    WHERE (ag.object_uuid = cu.link_dis_hw_user_uuid)
            AND (cu.dis_hw_uuid= dhw.dis_hw_uuid)
            AND (dhw.domain_uuid=dom.domain_uuid)
    UNION SELECT exa.label  COLLATE Latin1_General_CI_AI AS agv_label,
                 exa.creation_user AS agv_creation_user,
                 exa.creation_date AS agv_creation_date,
                 exa.last_update_user AS agv_last_update_user,
                 exa.last_update_date AS agv_last_update_date,
                 ag.object_uuid AS ag_object_uuid,
                 ag.domain_id AS ag_domain_id,
                 ag.server_uuid AS ag_server_uuid,
                 srv.host_name AS srv_host_name,
                 ag.boot_server_uuid AS ag_boot_server_uuid,
                 ag.unit_id AS ag_unit_id,
                 ag.last_run_date AS ag_last_run_date,
                 ag.agent_type AS ag_agent_type,
                 ag.agent_lock AS ag_agent_lock,
                 ag.agent_name AS ag_agent_name,
                 ag.agent_file_id AS ag_agent_file_id,
                 ag.agent_area AS ag_agent_area,
                 ag.agent_version AS ag_agent_version,
                 ag.description AS ag_description,
                 ag.prev_manager AS ag_prev_manager,
                 ag.ip_address AS ag_ip_address,
                 ag.registration_type AS ag_registration_type,
                 ag.proc_os_id AS ag_proc_os_id,
                 ag.class_id AS ag_class_id,
                 ag.derived_status_am AS ag_derived_status_am,
                 ag.derived_status_sd AS ag_derived_status_sd,
                 ag.derived_status_rc AS ag_derived_status_rc,
                 ag.derived_status_babld AS ag_derived_status_babld,
                 ag.user_def1 AS ag_user_def1,
                 ag.user_def2 AS ag_user_def2,
                 ag.user_def3 AS ag_user_def3,
                 ag.user_def4 AS ag_user_def4,
                 ag.creation_user AS ag_creation_user,
                 ag.creation_date AS ag_creation_date,
                 ag.last_update_date AS ag_last_update_date,
                 ag.last_update_user AS ag_last_update_user,
                 ag.version_number AS ag_version_number,
                 dom.label AS dom_label,
                 '' AS dhw_label,
                 convert(binary(16),NULL) AS dhw_domain_uuid,
                 convert(binary(16),NULL) AS dhw_dis_hw_uuid,
                 convert(binary(16),NULL) AS dhw_asset_source_uuid,
                 '' AS dhw_host_name,
                 '' AS dhw_primary_network_address,
                 '' AS dhw_serial_number,
                 '' AS dhw_disc_serial_number,
                 '' AS dhw_primary_mac_address,
                 '' AS dhw_asset_tag,
                 '' AS dhw_host_uuid,
                 convert(integer,NULL) AS dhw_class_id,
                 convert(binary(32),0) AS dhw_usage_list,
                 '' AS dhw_uri,
                 '' AS dhw_system_id,
                 '' AS dhw_vendor_name,
                 '' AS dhw_creation_user,
                 convert(integer,NULL) AS dhw_creation_date,
                 convert(integer,NULL) AS dhw_last_update_date,
                 '' AS dhw_last_update_user,
                 convert(integer,NULL) AS dhw_version_number,
                 '' AS usr_label,
                 convert(binary(16),NULL) AS usr_domain_uuid,
                 convert(binary(16),NULL) AS usr_user_uuid,
                 '' AS usr_user_name,
                 convert(binary(32),0) AS usr_usage_list,
                 '' AS usr_uri,
                 '' AS usr_creation_user,
                 convert(integer,NULL) AS usr_creation_date,
                 convert(integer,NULL) AS usr_last_update_date,
                 '' AS usr_last_update_user,
                 convert(integer,NULL) AS usr_version_number,
                 '' AS cu_label,
                 convert(binary(16),NULL) AS cu_link_dis_hw_user_uuid,
                 convert(binary(16),NULL) AS cu_dis_hw_uuid,
                 convert(binary(16),NULL) AS cu_user_uuid,
                 convert(integer,NULL) AS cu_ref_count,
                 convert(binary(32),0) AS cu_usage_list,
                 '' AS cu_creation_user,
                 convert(integer,NULL) AS cu_creation_date,
                 '' AS cu_last_update_user,
                 convert(integer,NULL) AS cu_last_update_date,
                 convert(integer,NULL) AS cu_version_number,
                 exa.label AS exa_label,
                 exa.device_uuid AS exa_device_uuid,
                 exa.domain_uuid AS exa_domain_uuid,
                 exa.class_name AS exa_class_name,
                 exa.ipaddress AS exa_ipaddress,
                 exa.macaddress AS exa_macaddress,
                 exa.last_update_user AS exa_last_update_user,
                 exa.last_update_date AS exa_last_update_date,
                 exa.creation_date AS exa_creation_date,
                 exa.creation_user AS exa_creation_user,
                 exa.version_number AS exa_version_number
    FROM  am_external_device exa,  ca_n_tier dom,  ca_agent AS ag LEFT OUTER JOIN  ca_server AS srv ON ag.server_uuid = srv.server_uuid
    WHERE (ag.object_uuid = exa.device_uuid)
            AND (exa.domain_uuid=dom.domain_uuid)


GO
GRANT SELECT ON  [dbo].[ca_coapi_agent_view] TO [ca_itrm_group]
GO