SET QUOTED_IDENTIFIER OFF
GO
create view al_compliance_status_view as
select
    isnull(isnull(pc.company_name, dsv.manufacturer), '(None)') as manufacturer,
    isnull(pf.name, '(None)') as product_family,
    isnull(pd.product_name, '('+dsv.product_name+')') as product_name,
    dsv.product_name as signature_definition,
    dsv.host_name,
    dsv.serial_number,
    dbo.secs2date(dsv.creation_date) as date_discovered,
    dsv.license_block_id, lb.block_id,
    lb.evaluate,
    isnull(dsv.drcs_content_type_id,0) * isnull(lb.block_id*0+1, 0-isnull(dsv.drcs_content_type_id,0))
        as compliance_status_id,
    ct.name as compliance_status,
    isnull(pc.company_uuid, dsv.company_uuid) as manufacturer_uuid,
    pd.family_uuid as product_family_id,
    pd.product_uuid,
    dsv.product_uuid as sw_def_uuid,
    dsv.release_uuid,
    dsv.dis_sw_uuid,
    dsv.dis_hw_uuid
from
    al_discovered_software_view dsv
    left outer join al_link_product_sw_def lp on lp.sw_def_uuid = dsv.product_uuid
    left outer join al_product_def pd on pd.product_uuid = lp.product_uuid
    left outer join al_product_family pf on pf.family_uuid = pd.family_uuid
    left outer join ca_company pc on pc.company_uuid = pf.manufacturer_uuid
    left outer join al_license_block lb on lb.block_id = dsv.license_block_id and lb.evaluate = 0
    left outer join al_compliance_status_type ct on ct.id =
        isnull(dsv.drcs_content_type_id,0) * isnull(lb.block_id*0+1, 0-isnull(dsv.drcs_content_type_id,0))
where
    (dsv.drcs_content_type_id = 3 or dsv.drcs_content_type_id is null)

GO