Install.Cireson.AssetManagement.GetHardwareAssetList (Resource)

Element properties:

Type	Resource
File Name	Install.Cireson.AssetManagement.GetHardwareAssetList.sql
Accessibility	Public

Source Code:

<Resource ID="Install.Cireson.AssetManagement.GetHardwareAssetList" Accessibility="Public" FileName="Install.Cireson.AssetManagement.GetHardwareAssetList.sql" HasNullStream="false"/>

File Content: Install.Cireson.AssetManagement.GetHardwareAssetList.sql

SET ANSI_NULLS ON


GO





SET QUOTED_IDENTIFIER ON


GO





IF EXISTS (


  SELECT * 


    FROM INFORMATION_SCHEMA.ROUTINES 


   WHERE SPECIFIC_SCHEMA = N'dbo'


     AND SPECIFIC_NAME = N'Cireson_AssetManagement_GetHardwareAssetList' 


)


   DROP PROCEDURE dbo.Cireson_AssetManagement_GetHardwareAssetList


GO





CREATE PROCEDURE [dbo].[Cireson_AssetManagement_GetHardwareAssetList]


		@Name nvarchar(100),


		@Type nvarchar(max),


		@Vendor nvarchar(max),


		@LanguageCode nvarchar(5),	


		@Status nvarchar(max),


		@CostCenter nvarchar(max),


		@Model nvarchar(100),


		@Manufacturer nvarchar(100),


		@Location nvarchar(max),


		@Organisation nvarchar(max),


		@LocationDetails nvarchar(100),


		@ContractStatus nvarchar(max)


		


AS


BEGIN


	


	-- V3


	SET NOCOUNT ON;


	


	IF (@Name Is Null) BEGIN SET @Name = '' END	


	IF (@Type Is Null) BEGIN SET @Type = '' END	


	IF (@Type = '-1') BEGIN SET @Type = '' END


	IF (@Status Is Null) BEGIN SET @Status = '' END	


	IF (@Status = '-1') BEGIN SET @Status = '' END


	IF (@Vendor Is Null) BEGIN SET @Vendor = '' END


	IF (@Vendor = '-1') BEGIN SET @Vendor = '' END


	IF (@CostCenter = '-1') BEGIN SET @CostCenter = '' END


	IF (@CostCenter IS Null) BEGIN SET @CostCenter = '' END


	IF (@Model Is Null) BEGIN SET @Model = '' END


	IF (@Manufacturer Is Null) BEGIN SET @Manufacturer = '' END	


	IF (@LocationDetails Is Null) BEGIN SET @LocationDetails = '' END


	IF (@Location = '-1') BEGIN SET @Location = '' END


	IF (@Location Is Null) BEGIN SET @Location = '' END


	IF (@Organisation = '-1') BEGIN SET @Organisation = '' END


	IF (@Organisation Is Null) BEGIN SET @Organisation = '' END


	IF (@ContractStatus Is Null) BEGIN SET @ContractStatus = '' END	


	IF (@ContractStatus = '-1') BEGIN SET @ContractStatus = '' END


	


	DECLARE @tableType TABLE (value nvarchar(256))  


	INSERT @tableType (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Type) 


	


	DECLARE @tableStatus TABLE (value nvarchar(256))  


	INSERT @tableStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Status)


	


	DECLARE @tableVendor TABLE (value nvarchar(256))  


	INSERT @tableVendor (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Vendor) 


	


	DECLARE @tableCostCenter TABLE (value nvarchar(256))  


	INSERT @tableCostCenter (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@CostCenter)   


  


  	DECLARE @tableLocation TABLE (value nvarchar(256))  


	INSERT @tableLocation (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Location)


	


	DECLARE @tableOrganisation TABLE (value nvarchar(256))  


	INSERT @tableOrganisation (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Organisation) 


	


	DECLARE @tableContractStatus TABLE (value nvarchar(256))  


	INSERT @tableContractStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@ContractStatus) 


	 


	SELECT


	HWA.Name as [Hardware Asset Name],


	HWA.Cost, 


	HWA.SerialNumber,


	HWA.CiresonHardwareAssetDimKey,


	PrimaryUser.DisplayName as [Primary User],


	Custodian.DisplayName as [Custodian],


	CostCenter.DisplayName as [Cost Center],


	Vendor.DisplayName as [Vendor],


	TypeDS.DisplayName as [Hardware Asset Type],


	StatusDS.DisplayName as [Hardware Asset Status],


	CurrencyDS.DisplayName as [Currency]


	


	FROM CiresonHardwareAssetDimvw HWA


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToPrimaryUserFactvw


	ON dbo.CiresonHardwareAssetRelatesToPrimaryUserFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToPrimaryUserFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS PrimaryUser 


	ON dbo.CiresonHardwareAssetRelatesToPrimaryUserFactvw.HardwareAssetHasPrimaryUser_UserDimKey = PrimaryUser.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToOwnedByUserFactvw


	ON dbo.CiresonHardwareAssetRelatesToOwnedByUserFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToOwnedByUserFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS Custodian 


	ON dbo.CiresonHardwareAssetRelatesToOwnedByUserFactvw.ConfigItemOwnedByUser_UserDimKey = Custodian.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToCostCenterFactvw


	ON dbo.CiresonHardwareAssetRelatesToCostCenterFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToCostCenterFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.CiresonCostCenterDimVw AS CostCenter 


	ON dbo.CiresonHardwareAssetRelatesToCostCenterFactvw.HardwareAssetHasCostCenter_CiresonCostCenterDimKey = CostCenter.CiresonCostCenterDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToVendorFactvw


	ON dbo.CiresonHardwareAssetRelatesToVendorFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToVendorFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN	


	dbo.CiresonVendorDimvw AS Vendor 


	ON dbo.CiresonHardwareAssetRelatesToVendorFactvw.HardwareAssetHasVendor_CiresonVendorDimKey = Vendor.CiresonVendorDimKey				


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToLocationFactvw


	ON dbo.CiresonHardwareAssetRelatesToLocationFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToLocationFactvw.DeletedDate Is Null		


	


	LEFT OUTER JOIN	


	dbo.CiresonLocationDimvw AS Location 


	ON dbo.CiresonHardwareAssetRelatesToLocationFactvw.HardwareAssetHasLocation_CiresonLocationDimKey = Location.CiresonLocationDimKey


	


	LEFT OUTER JOIN


	dbo.CiresonHardwareAssetTypevw AS TypeEnum 


	ON TypeEnum.CiresonHardwareAssetTypeId = HWA.HardwareAssetType_CiresonHardwareAssetTypeId


	


	LEFT OUTER JOIN 


	dbo.DisplayStringDimvw TypeDS


	ON TypeEnum.EnumTypeId = TypeDS.BaseManagedEntityId


	AND ((TypeDS.LanguageCode = @LanguageCode AND TypeDS.DisplayName != null) OR TypeDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN


	dbo.CiresonHardwareAssetStatusvw AS StatusEnum 


	ON StatusEnum.CiresonHardwareAssetStatusId = HWA.HardwareAssetStatus_CiresonHardwareAssetStatusId


	


	LEFT OUTER JOIN 


	dbo.DisplayStringDimvw StatusDS


	ON StatusEnum.EnumTypeId = StatusDS.BaseManagedEntityId


	AND ((StatusDS.LanguageCode = @LanguageCode AND StatusDS.DisplayName != null) OR StatusDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN


	dbo.CiresonCurrencyvw AS CurrencyEnum 


	ON CurrencyEnum.CiresonCurrencyId = HWA.Currency_CiresonCurrencyId


	


	LEFT OUTER JOIN 


	dbo.DisplayStringDimvw CurrencyDS


	ON CurrencyEnum.EnumTypeId = CurrencyDS.BaseManagedEntityId


	AND ((CurrencyDS.LanguageCode = @LanguageCode AND CurrencyDS.DisplayName != null) OR CurrencyDS.LanguageCode = 'ENU')


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToOrganizationFactvw


	ON dbo.CiresonHardwareAssetRelatesToOrganizationFactvw.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


	AND CiresonHardwareAssetRelatesToOrganizationFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN	


	dbo.CiresonOrganizationDimvw AS Organisation 


	ON dbo.CiresonHardwareAssetRelatesToOrganizationFactvw.HardwareAssetHasOrganization_CiresonOrganizationDimKey = Organisation.CiresonOrganizationDimKey	


	


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToSupportContractFactvw Con


		ON Con.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


		AND Con.DeletedDate is null





	LEFT OUTER JOIN	


	dbo.CiresonSupportContractDimvw SupportContract


		ON SupportContract.CiresonSupportContractDimKey = Con.HardwareAssetHasSupportContract_CiresonSupportContractDimKey


		AND SupportContract.IsDeleted = 0


		


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToWarrantyFactvw War


		ON War.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


		AND War.DeletedDate is null





	LEFT OUTER JOIN	


	dbo.CiresonWarrantyDimvw Warranty


		ON Warranty.CiresonWarrantyDimKey = War.HardwareAssetHasWarranty_CiresonWarrantyDimKey


		AND Warranty.IsDeleted = 0


		


	LEFT OUTER JOIN	


	dbo.CiresonHardwareAssetRelatesToLeaseFactvw Lse


		ON Lse.CiresonHardwareAssetDimKey = HWA.CiresonHardwareAssetDimKey


		AND Lse.DeletedDate is null





	LEFT OUTER JOIN	


	dbo.CiresonLeaseDimvw Lease


		ON Lease.CiresonLeaseDimKey = Lse.HardwareAssetHasLease_CiresonLeaseDimKey


		AND Lease.IsDeleted = 0	


	


	WHERE 	


	


	((@Name = '') OR HWA.Name LIKE '%' + @Name + '%') AND


	((@Type = '') OR (TypeDS.DisplayName <> '' AND hwa.HardwareAssetType_CiresonHardwareAssetTypeId IN (Select value from @tableType))) AND


	((@Status = '') OR (StatusDS.DisplayName <> '' AND hwa.HardwareAssetStatus_CiresonHardwareAssetStatusId IN (Select value from @tableStatus))) AND


	((0 IN (SELECT value FROM @tableCostCenter)) OR (CostCenter.CiresonCostCenterDimKey IN (SELECT value FROM @tableCostCenter))) AND


	((0 IN (SELECT value FROM @tableVendor)) OR (Vendor.CiresonVendorDimKey IN (SELECT value FROM @tableVendor))) AND


	((@Model = '') OR HWA.Model LIKE '%' + @Model + '%') AND


	((@Manufacturer = '') OR HWA.Manufacturer LIKE '%' + @Manufacturer + '%') AND


	((0 IN (SELECT value FROM @tableLocation)) OR (Location.CiresonLocationDimKey IN (SELECT value FROM @tableLocation))) AND


	((0 IN (SELECT value FROM @tableOrganisation)) OR (Organisation.CiresonOrganizationDimKey IN (SELECT value FROM @tableOrganisation))) AND


	((@LocationDetails = '') OR HWA.LocationDetails LIKE '%' + @LocationDetails + '%') AND


	


	(


		(@ContractStatus = '' OR (HWA.MasterContractStatus_CiresonSupportContractContractStatusId IN (Select value from @tableContractStatus)))


		OR


		(


			(@ContractStatus <> '' AND Warranty.DisplayName Is Not Null AND Warranty.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus))


			OR


			(@ContractStatus <> '' AND Lease.DisplayName Is Not Null AND Lease.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus))


			OR


			(@ContractStatus <> '' AND SupportContract.DisplayName Is Not Null AND SupportContract.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus))


		)


	)


	


	AND HWA.IsDeleted = 0


	


	ORDER BY HWA.Name


	


END





GO





GRANT EXECUTE ON dbo.Cireson_AssetManagement_GetHardwareAssetList TO ReportUser


GO