CREATE VIEW ARG_RECONCILE_OWNED_VIEW
AS
SELECT oa.own_resource_uuid,
       oa.mac_address,
       oa.resource_tag AS alt_asset_id,
       oa.resource_alias AS alias,
       oa.serial_number,
       oa.host_name,
       oa.host_name AS dns_name,
       mc.company_name AS manufacturer,
       sc.name AS subclass,
       cl.name AS class,
       oa.resource_name,
       oa.alternate_host_name,
       oa.previous_resource_tag,
       oa.asset_type_id,
       oa.model_uuid,
       md.name as model_name,
       oa.version_number,
       oa.resource_class,
       oa.resource_subclass,
       oa.resource_family,
       oa.tenant AS tenant_uuid,
       CASE
         WHEN LTRIM(oa.mac_address) IS NULL OR LEN(LTRIM(oa.mac_address)) = 0
         THEN oa.host_name
         ELSE oa.mac_address+' '+oa.host_name
       END AS mac_host
FROM ca_owned_resource oa
LEFT OUTER JOIN ca_company mc
  ON oa.manufacturer_uuid = mc.company_uuid
LEFT OUTER JOIN ca_resource_class sc
  ON oa.resource_subclass = sc.id
LEFT OUTER JOIN ca_resource_class cl
  ON oa.resource_class = cl.id
LEFT OUTER JOIN ca_resource_family rf
  ON oa.resource_family = rf.id
LEFT OUTER JOIN ca_model_def md
  ON oa.model_uuid = md.model_uuid
WHERE (oa.exclude_reconciliation = 0 OR oa.exclude_reconciliation IS NULL)
  AND rf.include_reconciliation = 1
  AND (rf.inactive = 0 or rf.inactive is null)
  AND (rf.is_software = 0 or rf.is_software is null)
  AND oa.inactive = 0
  AND oa.ufam = 1
  AND (md.inactive = 0 or md.inactive is null)
  AND (mc.inactive = 0 or mc.inactive is null)
  AND (cl.inactive = 0 or cl.inactive is null)
  AND (sc.inactive = 0 or sc.inactive is null)
GO