Install.Cireson.AssetManagement.GetSoftwareAssetList (Resource)

Element properties:

Source Code:

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

File Content: Install.Cireson.AssetManagement.GetSoftwareAssetList.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_GetSoftwareAssetList' 


)


   DROP PROCEDURE dbo.Cireson_AssetManagement_GetSoftwareAssetList


GO





CREATE PROCEDURE [dbo].[Cireson_AssetManagement_GetSoftwareAssetList]


		@Name nvarchar(100),


		@LanguageCode nvarchar(5),


		@Status nvarchar(max),


		@LicenceStatus nvarchar(max),


		@CostCenter nvarchar(max),


		@Type nvarchar(max),


		@Vendor nvarchar(max),


		@ContractStatus nvarchar(max),


		@ShowExceeded bit = 0


AS


BEGIN


	


	-- V3


	SET NOCOUNT ON;


	


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


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


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


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


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


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


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


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


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


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


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


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


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


	


	DECLARE @tableStatus TABLE (value nvarchar(256))  


	INSERT @tableStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Status) 


	


	DECLARE @tableLicenceStatus TABLE (value nvarchar(256))  


	INSERT @tableLicenceStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@LicenceStatus) 


	


	DECLARE @tableCostCenter TABLE (value nvarchar(256))  


	INSERT @tableCostCenter (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@CostCenter) 


	


	DECLARE @tableVendor TABLE (value nvarchar(256))  


	INSERT @tableVendor (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Vendor) 


	


	DECLARE @tableType TABLE (value nvarchar(256))  


	INSERT @tableType (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@Type) 


	


	DECLARE @tableContractStatus TABLE (value nvarchar(256))  


	INSERT @tableContractStatus (value)


	SELECT * FROM dbo.fn_CSVToTableInt(@ContractStatus) 


  


	SELECT


	SWA.Name as [Software Asset Name],


	SWA.PurchaseCount as [Purchase Count],


	SWA.InstallCount as [Install Count],


	SWA.AvailableCount as [Available Count],


	SWA.SoftwareMeteringUsageCount as [UsageCount],


	SWA.Manufacturer,


	SWA.CiresonSoftwareAssetDimKey,


	SWA.Cost,


	(


	SELECT Sum(Cost)


    FROM CiresonPurchaseDim p


	LEFT JOIN CiresonSoftwareAssetRelatesToPurchaseFactvw pf on pf.CiresonSoftwareAssetDimKey = SWA.CiresonSoftwareAssetDimKey


	AND pf.DeletedDate Is Null


	WHERE p.CiresonPurchaseDimKey = pf.SoftwareAssetHasPurchase_CiresonPurchaseDimKey


	) AS [PurchasesCost],


	Vendor.DisplayName as Vendor,


	CostCenter.DisplayName as [Cost Center],


	StatusDS.DisplayName as [Software Asset Status],


	LicenceStatusDS.DisplayName as [Software Asset License Status],


	CurrencyDS.DisplayName as [Currency],


	SWA.TotalPurchaseCount,


	SWA.DowngradeLicences,


	SWA.DowngradesAvailable,


	SWA.UpgradeLicences,


	SWA.UpgradesAvailable,


	SWA.AuthorizedComputerCount,


	SWA.UnauthorizedComputerCount,


	SWA.AuthorizedInstallCount,


	SWA.IsNamed,


	SWA.CountLicenceSeats,


	CPUModeDS.DisplayName as [CPUMode],


	SWA.CountAllMatches,


	SWA.IsUnlimited,


	SWA.IsOS,


	TypeDS.DisplayName as [Software Asset Type]


	


	FROM CiresonSoftwareAssetDimvw SWA


	


	LEFT OUTER JOIN	


	dbo.CiresonSoftwareAssetRelatesToPrimaryUserFactvw


	ON dbo.CiresonSoftwareAssetRelatesToPrimaryUserFactvw.CiresonSoftwareAssetDimKey = SWA.CiresonSoftwareAssetDimKey


	AND CiresonSoftwareAssetRelatesToPrimaryUserFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN


	dbo.UserDimvw AS PrimaryUser 


	ON dbo.CiresonSoftwareAssetRelatesToPrimaryUserFactvw.SoftwareAssetHasPrimaryUser_UserDimKey = PrimaryUser.UserDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonSoftwareAssetRelatesToCostCenterFactvw


	ON dbo.CiresonSoftwareAssetRelatesToCostCenterFactvw.CiresonSoftwareAssetDimKey = SWA.CiresonSoftwareAssetDimKey


	AND CiresonSoftwareAssetRelatesToCostCenterFactvw.DeletedDate Is Null	


	


	LEFT OUTER JOIN	


	dbo.CiresonCostCenterDimVw AS CostCenter 


	ON dbo.CiresonSoftwareAssetRelatesToCostCenterFactvw.SoftwareAssetHasCostCenter_CiresonCostCenterDimKey = CostCenter.CiresonCostCenterDimKey 


	


	LEFT OUTER JOIN	


	dbo.CiresonSoftwareAssetRelatesToVendorFactvw


	ON dbo.CiresonSoftwareAssetRelatesToVendorFactvw.CiresonSoftwareAssetDimKey = SWA.CiresonSoftwareAssetDimKey


	AND CiresonSoftwareAssetRelatesToVendorFactvw.DeletedDate Is Null


	


	LEFT OUTER JOIN	


	dbo.CiresonVendorDimvw AS Vendor 


	ON dbo.CiresonSoftwareAssetRelatesToVendorFactvw.SoftwareAssetHasVendor_CiresonVendorDimKey = vendor.CiresonVendorDimKey		


		


	LEFT OUTER JOIN	


	dbo.CiresonSoftwareAssetStatusvw AS StatusEnum 


	ON StatusEnum.CiresonSoftwareAssetStatusId = SWA.SoftwareAssetStatus_CiresonSoftwareAssetStatusId


	


	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.CiresonSoftwareAssetTYpevw AS TypeEnum 


	ON TypeEnum.CiresonSoftwareAssetTypeId = SWA.SoftwareAssetType_CiresonSoftwareAssetTypeId


	


	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.CiresonSoftwareAssetCPUModevw CPUModeEnum 


		ON SWA.CPUMode_CiresonSoftwareAssetCPUModeId = CPUModeEnum.CiresonSoftwareAssetCPUModeId


	


	LEFT OUTER JOIN dbo.DisplayStringDimvw CPUModeDS


		ON CPUModeEnum.EnumTypeId = CPUModeDS.BaseManagedEntityId


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


	


	LEFT OUTER JOIN


	dbo.CiresonCurrencyvw AS CurrencyEnum 


	ON CurrencyEnum.CiresonCurrencyId = SWA.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.CiresonSoftwareAssetLicenceStatusvw AS LicenceStatusEnum 


	ON LicenceStatusEnum.CiresonSoftwareAssetLicenceStatusId = SWA.LicenceStatus_CiresonSoftwareAssetLicenceStatusId


	


	LEFT OUTER JOIN	


	dbo.DisplayStringDimvw LicenceStatusDS


	ON LicenceStatusEnum.EnumTypeId = LicenceStatusDS.BaseManagedEntityId


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


	


	LEFT OUTER JOIN	


	dbo.CiresonSoftwareAssetRelatesToSupportContractFactvw Con


		ON Con.CiresonSoftwareAssetDimKey = SWA.CiresonSoftwareAssetDimKey


		AND Con.DeletedDate is null





	LEFT OUTER JOIN	


	dbo.CiresonSupportContractDimvw SupportContract


		ON SupportContract.CiresonSupportContractDimKey = Con.SoftwareAssetHasSupportContract_CiresonSupportContractDimKey


		AND SupportContract.IsDeleted = 0


	


	WHERE 	


	


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


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


	((@LicenceStatus = '') OR (LicenceStatusDS.DisplayName <> '' AND SWA.LicenceStatus_CiresonSoftwareAssetLicenceStatusId IN (Select value from @tableLicenceStatus))) AND


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


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


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


	((@ContractStatus = '' ) OR (SupportContract.ContractStatus_CiresonSupportContractContractStatusId In (Select value from @tableContractStatus))) AND


	((@ShowExceeded = 0) OR (SWA.AvailableCount <= 0 AND SWA.IsUnlimited = 0))


	


	AND SWA.IsDeleted = 0


	


	ORDER BY SWA.Name


	


END





GO





GRANT EXECUTE ON dbo.Cireson_AssetManagement_GetSoftwareAssetList TO ReportUser


GO