SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW dbo.arg_web_deleted_ownership_view
AS
SELECT             rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            rm.own_resource_serial_num,
                   rm.own_resource_alias,
            rm.own_resource_alternate_id AS own_resource_item_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        arg_reconcile_modification rm ON rd.own_resource_uuid = rm.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 = 1)

UNION ALL

SELECT             rd.task_uuid AS task_id,
            rd.reconcile_data_id,
            rm.own_resource_serial_num,
                   rm.own_resource_alias,
            rm.own_resource_alternate_id AS own_resource_item_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        arg_reconcile_modification rm ON rd.own_resource_uuid = rm.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 = 1)

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