SET QUOTED_IDENTIFIER OFF
GO
create view al_compliance_status_view as  
select   
isnull(isnull(isnull(pca.company_name,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 ca_company pca on pca.company_uuid = pc.duplicate_with_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