SELECT DISTINCT
	AST.Configuration_Item_ID AS [Configuration item ID] 
	, AST.External_System_Number AS [External system number] 
	, STA.Value AS [CI Status] 
	, AST.Status_Reason AS [Status Reason] 
	, AST.Impact AS [Impact] 
	, AST.Urgency AS [Urgency] 
	, AST.On_Loan AS [On Loan] 
	, AST.Quantity AS [Quantity] 
	, AST.Quantity_Used AS [Quantity used] 
	, AST.Quantity_Remaining AS [Quantity remaining] 
	, AST.Tag_Number AS [Tag Number] 
	, AST.Serial_Number AS [Serial Number] 
	, AST.Name AS [CI Name] 
	, AST.System_Roles AS [CI Class] 
	, AST.System_Type AS [CI Type] 
	, AST.Configuration_Item_Role AS [CI Role] 
	, AST.Description AS [Description] 
	, AST.Environment AS [Environment] 
	, BCI.Value AS [Baseline CI] 
	, BUN.Value AS [Bundle] 
	, AST.Company AS [Company] 
	, AST.Product_Categorization_Tier_1 AS [Product Categorization Tier 1] 
	, AST.Product_Categorization_Tier_2 AS [Product Categorization Tier 2] 
	, AST.Product_Categorization_Tier_3 AS [Product Categorization Tier 3] 
	, AST.Product_Name AS [Product Name] 
	, AST.Product_Model_Version AS [Product Model] 
	, AST.Manufacturer AS [Manufacturer] 
	, AST.Specific_SLA_Criteria AS [Specific SLA criteria] 
	, AST.Company AS [Location Company] 
	, AST.Region AS [Region] 
	, AST.Site_Group AS [Site group] 
	, AST.Site AS [Site] 
	, AST.Site_Zone AS [Site Zone] 
	, AST.Additional_Location_Details AS [Additional Location Details] 
	, D.Association_type01 AS [Contact association type] 
	, D.Request_description01 AS [Contact name] 
	, D.Request_ID01 AS [Contact ID] 
	, D.Request_type01 AS [Contact type] 
	, dbo.cgi_fn_ConvertDateTime(AST.Request_Date, 120) AS [Requisition date] 
	, dbo.cgi_fn_ConvertDateTime(AST.PO_Date, 120) AS [Purchase date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Acquisition_Date, 120) AS [Acquisition date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Submit_Date, 120) AS [CMDB Submit date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Configuration_Date, 120) AS [Configuration date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Inventory_Date, 120) AS [Inventory date] 
	, dbo.cgi_fn_ConvertDateTime(AST.In_Service_Date, 120) AS [In Service date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Out_of_Service_Date, 120) AS [Out of Service date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Retired_Date, 120) AS [Retired date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Disposal_Date, 120) AS [Disposed date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Last_Audit_Date, 120) AS [Last Audit Date] 
	, dbo.cgi_fn_ConvertDateTime(AST.Next_Audit_Date, 120) AS [Next Audit Date] 
	, AST.Submitter AS [Submitter] 
	, AST.Last_Modified_By AS [Last Modified By] 
	, dbo.cgi_fn_ConvertDateTime(AST.Last_Modified_Date, 120) AS [Last modified date] 
FROM
	dbo.AST_Configuration_Item__CI_ AST (nolock) 

	LEFT OUTER JOIN dbo.AST_CTM_Associations D (nolock) ON D.Request_ID02 = AST.Configuration_Item_ID 

	LEFT OUTER JOIN dbo.ARSchema SST (NOLOCK) ON SST.Name = 'AST:Configuration Item (CI)' 

	LEFT OUTER JOIN dbo.field_enum_values STA (NOLOCK) ON
		STA.schemaid = sst.schemaid
		AND STA.fieldid = 7
		AND AST.CI_Status = STA.enumID 

	LEFT OUTER JOIN dbo.field_enum_values BCI (NOLOCK) ON
		BCI.schemaid = sst.schemaid
		AND BCI.fieldid = 1000001592
		AND AST.Baseline_CI = BCI.enumID 

	LEFT OUTER JOIN dbo.field_enum_values BUN (NOLOCK) ON
		BUN.schemaid = sst.schemaid
		AND BUN.fieldid = 1000004090
		AND AST.Bundle = BUN.enumID 
WHERE
	1=1 
	AND AST.Company = 'CGI.CANRGM' 
	AND AST.Product_Categorization_Tier_1 = 'Hardware'