SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW dbo.arg_web_proposed_matches_view
AS
SELECT            rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            oa.serial_number AS owned_serial_number,
            oa.resource_alias AS alias,
                oa.resource_name AS item_name,
                oa.host_name,
                dh.serial_number as discovery_asset_serial_num,
            dh.host_name AS discovery_asset_name,
                i1.item_value_text AS sys_vendor,
            i2.item_value_text AS sys_model   
        
FROM            arg_reconcile_data rd

INNER JOIN        ca_owned_resource oa ON rd.own_resource_uuid = oa.own_resource_uuid
INNER JOIN         ca_discovered_hardware dh ON rd.dis_hw_uuid = dh.dis_hw_uuid
INNER JOIN        ca_discovered_hardware_ext_sys es ON es.dis_hw_uuid = dh.dis_hw_uuid
LEFT OUTER JOIN        inv_reconcile_item i1 ON i1.object_uuid = dh.dis_hw_uuid and  i1.item_parent_name_id = 2 and i1.item_name_id = 6
LEFT OUTER JOIN     inv_reconcile_item i2 ON i2.object_uuid = dh.dis_hw_uuid and  i2.item_parent_name_id = 2 and i2.item_name_id = 5
                
WHERE           (rd.reconcile_data_type = 5)

UNION ALL

SELECT            rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            oa.serial_number AS owned_serial_number,
            oa.resource_alias AS alias,
                oa.resource_name AS item_name,
                oa.host_name,
                dh.serial_number as discovery_asset_serial_num,
            dh.host_name AS discovery_asset_name,
                i1.item_value_text AS sys_vendor,
            i2.item_value_text AS sys_model    
        
FROM            arg_reconcile_data rd

INNER JOIN        ca_owned_resource oa ON rd.own_resource_uuid = oa.own_resource_uuid
INNER JOIN         ca_discovered_hardware dh ON rd.dis_hw_uuid = dh.dis_hw_uuid
INNER JOIN        ca_agent ag ON ag.object_uuid = dh.dis_hw_uuid
LEFT OUTER JOIN        inv_generalinventory_item i1 ON i1.object_uuid = dh.dis_hw_uuid and  i1.item_parent_name_id = 2 and i1.item_name_id = 6
LEFT OUTER JOIN     inv_generalinventory_item i2 ON i2.object_uuid = dh.dis_hw_uuid and  i2.item_parent_name_id = 2 and i2.item_name_id = 5

WHERE           (rd.reconcile_data_type = 5)

GO
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [swcmadmin]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmadmin]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmadmin_group]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmbatch]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmbatch_group]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmreporting]
GRANT SELECT ON  [dbo].[arg_web_proposed_matches_view] TO [uapmreporting_group]
GO