SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW al_software_discovered_view AS
SELECT DISTINCT
casd2.sw_def_uuid AS sw_def_uuid,
casd2.name AS name,
casd2.sw_version_label AS sw_version_label,
casd2.language AS language,
casd2.sw_version_number AS sw_version_number,
casd2.replication_flag AS replication_flag,
casd2.software_type_id AS software_type_id,
casd2.source_type_id AS source_type_id,
casd2.description AS description,
casd2.auto_rep_version AS auto_rep_version,
casd2.manufacturer_uuid AS manufacturer_uuid,
casd2.bit_support_id AS bit_support_id,
casd2.chip_set_id AS chip_set_id,
casd2.domain_uuid AS domain_uuid,
casd2.impact AS impact,
casd2.severity AS severity,
casd2.is_active AS is_active,
casd2.no_longer_available AS no_longer_available,
casd2.family_sw_def_uuid AS family_sw_def_uuid,
casd2.lang_code AS lang_code,
casd2.class_id AS class_id,
casd2.linear_sequence_number AS linear_sequence_number,
casd2.filter_from_view AS filter_from_view,
casd2.drcs_content_type_id AS drcs_content_type_id,
casd2.requires_review AS requires_review,
casd2.creation_user AS creation_user,
casd2.creation_date AS creation_date,
casd2.last_update_user AS last_update_user,
casd2.last_update_date AS last_update_date,
casd2.version_number AS version_number
FROM ca_discovered_software AS cads
INNER JOIN dbo.ca_software_def AS casd1 ON casd1.sw_def_uuid = cads.sw_def_uuid
INNER JOIN dbo.ca_link_sw_def AS calswd ON calswd.secondary_sw_def_uuid = casd1.sw_def_uuid
INNER JOIN dbo.ca_software_def AS casd2 ON casd2.sw_def_uuid = calswd.primary_sw_def_uuid AND calswd.link_type_id = 3

GO