SET QUOTED_IDENTIFIER OFF
GO

        CREATE VIEW dbo.arg_reconcile_view
        AS
        SELECT  ca_owned_resource.own_resource_uuid,
    ca_model_def.name AS item_name,
    ca_model_def.abbreviation AS item_abrev,
    ca_owned_resource.asset_source_uuid,
    ca_owned_resource.mac_address,
    ca_owned_resource.dns_name,
    ca_owned_resource.resource_capacity,
    ca_owned_resource.resource_tag AS alt_asset_id,
    ca_owned_resource.resource_alias AS alias,
    ca_resource_operating_system.name AS operating_system,
    ca_owned_resource.serial_number,
    ca_owned_resource.host_name,
    ca_contact.last_name AS contact_name,
    ca_contact.contact_uuid,
    vendor_company.company_name AS vendor,
    manufacturer_company.company_name AS manufacturer,
    ca_capacity_unit.name AS capacity_units,
    subclass.name AS subclass,
    class.name AS class,
    ca_discovered_hardware.dis_hw_uuid,
    ca_discovered_hardware_ext_sys.external_sys_name,
    ca_model_def.model_uuid,
    dis_asset_source.subschema_id,
    ca_owned_resource.resource_name,
    ca_owned_resource.floor_location,
    ca_owned_resource.room_location,
    ca_owned_resource.cabinet_location,
    ca_owned_resource.shelf_location,
    ca_owned_resource.slot_location,
    ca_owned_resource.purchase_order_id,
    ca_resource_family.name as resource_family,
    ca_location.location_name,
    ca_resource_department.name AS department,
    ca_resource_cost_center.name AS cost_center,
    ca_owned_resource.exclude_reconciliation,
    ca_owned_resource.alternate_host_name,
    ca_owned_resource.discovery_last_run_date,
    ca_owned_resource.previous_resource_tag,
    ca_owned_resource.processor_count,
    ca_owned_resource.processor_speed,
    processor_unit.name AS processor_speed_units,
    ca_owned_resource.processor_type,
    ca_owned_resource.reconciliation_date,
    ca_owned_resource.total_disk_space,
    total_disk_unit.name AS total_disk_units,
    ca_owned_resource.total_memory,
    total_memory_unit.name AS total_memory_units,
    CASE WHEN LTRIM(ca_owned_resource.mac_address) is null OR DATALENGTH(LTRIM(ca_owned_resource.mac_address)) = 0 THEN ca_owned_resource.host_name ELSE ca_owned_resource.mac_address + ' ' + ca_owned_resource.host_name END AS mac_host,
    bought_for_company.company_name AS company_bought_for,
    ca_resource_status.name AS portfolio_status        
FROM    ca_owned_resource
    LEFT OUTER JOIN ca_model_def
        ON ca_owned_resource.model_uuid = ca_model_def.model_uuid
    LEFT OUTER JOIN ca_resource_family
        ON ca_owned_resource.resource_family = ca_resource_family.id
    LEFT OUTER JOIN ca_company manufacturer_company
        ON ca_owned_resource.manufacturer_uuid = manufacturer_company.company_uuid
    LEFT OUTER JOIN ca_company vendor_company
        ON ca_owned_resource.supply_vendor_uuid = vendor_company.company_uuid
    LEFT OUTER JOIN ca_capacity_unit
        ON ca_owned_resource.resource_capacity_unit = ca_capacity_unit.id
    LEFT OUTER JOIN ca_resource_class subclass
        ON ca_owned_resource.resource_subclass = subclass.id
    LEFT OUTER JOIN ca_resource_class class
        ON ca_owned_resource.resource_class = class.id
    LEFT OUTER JOIN ca_resource_operating_system
        ON ca_owned_resource.operating_system = ca_resource_operating_system.id
    LEFT OUTER JOIN ca_contact
        ON ca_owned_resource.resource_contact_uuid = ca_contact.contact_uuid     
    LEFT OUTER JOIN ca_asset_source arg_asset_source
        ON ca_owned_resource.asset_source_uuid = arg_asset_source.asset_source_uuid and arg_asset_source.subschema_id = 1 and arg_asset_source.delete_time is null
      LEFT OUTER JOIN ca_asset_source dis_asset_source
        ON arg_asset_source.logical_asset_uuid = dis_asset_source.logical_asset_uuid and (dis_asset_source.subschema_id = 3 or (dis_asset_source.subschema_id > 9 and dis_asset_source.subschema_id < 25)) and dis_asset_source.delete_time is null     
      LEFT OUTER JOIN ca_discovered_hardware
        ON dis_asset_source.asset_source_uuid = ca_discovered_hardware.dis_hw_uuid
    LEFT OUTER JOIN ca_discovered_hardware_ext_sys
        ON ca_discovered_hardware.dis_hw_uuid = ca_discovered_hardware_ext_sys.dis_hw_uuid
    LEFT OUTER JOIN ca_location
        ON ca_owned_resource.location_uuid = ca_location.location_uuid
    LEFT OUTER JOIN ca_resource_department
        ON ca_owned_resource.department = ca_resource_department.id
    LEFT OUTER JOIN ca_resource_cost_center
        ON ca_owned_resource.cost_center = ca_resource_cost_center.id
    LEFT OUTER JOIN ca_capacity_unit processor_unit
        ON ca_owned_resource.processor_speed_unit = processor_unit.id
    LEFT OUTER JOIN ca_capacity_unit total_disk_unit
        ON ca_owned_resource.total_disk_space_unit = total_disk_unit.id
    LEFT OUTER JOIN ca_capacity_unit total_memory_unit
        ON ca_owned_resource.total_memory_unit = total_memory_unit.id
    LEFT OUTER JOIN ca_company bought_for_company
            ON ca_owned_resource.company_bought_for_uuid = bought_for_company.company_uuid
    LEFT OUTER JOIN arg_assetver
            ON ca_owned_resource.own_resource_uuid = arg_assetver.own_resource_uuid
    LEFT OUTER JOIN ca_resource_status
            ON arg_assetver.avstatus = ca_resource_status.id
     WHERE     (ca_resource_family.include_reconciliation = 1) AND (ca_owned_resource.inactive = 0)
GO
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmadmin]
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmbatch]
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmreporting]
GRANT SELECT ON  [dbo].[arg_reconcile_view] TO [uapmreporting_group]
GO