Provance.ITAssetManagement.Report.ProvanceProc_GetParameterDisplayName.Install (Resource)

Element properties:

Source Code:

<Resource ID="Provance.ITAssetManagement.Report.ProvanceProc_GetParameterDisplayName.Install" Accessibility="Public" FileName="ProvanceProc_GetParameterDisplayName.sql" HasNullStream="false"/>

File Content: ProvanceProc_GetParameterDisplayName.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- Drop stored procedure if it already exists


IF EXISTS (


  SELECT * 


    FROM dbo.sysobjects 


   WHERE name = N'ProvanceProc_GetParameterDisplayName'


     AND type = 'P' 


)


   DROP PROCEDURE dbo.ProvanceProc_GetParameterDisplayName


GO





CREATE PROCEDURE [dbo].[ProvanceProc_GetParameterDisplayName]


	@LocationId		INT,


	@CostCenterId	INT,


	@OrganizationId	INT,


	@SupplierId		INT,


	@ManufacturerId	INT,


	@StatusId		INT,


	@MakeModelId	INT


AS


BEGIN








	


--temporary tables


CREATE TABLE #displayNames (


	Id						int,


	LocationDisplayName		varchar(255), 


	CostCenterDisplayName	varchar(255), 


	OrganizationDisplayName	varchar(255),


	SupplierDisplayName		varchar(255),


	ManufacturerDisplayName	varchar(255),


	StatusDisplayName		varchar(255),


	MakeModelDisplayName	varchar(255)


	)





	DECLARE @ObjectStatusPendingDelete	VARCHAR(255)


	SELECT @ObjectStatusPendingDelete = 'System.ConfigItem.ObjectStatusEnum.PendingDelete'


	


	INSERT INTO #displayNames (Id) Values (1);


	


	-- organization display name


	IF @OrganizationId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET OrganizationDisplayName = 


		(SELECT A.DisplayName


		FROM OrganizationDimvw A


		WHERE A.OrganizationDimKey = @OrganizationId)


		


	END


	


	


	-- get cost center display name


	IF @CostCenterId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET CostCenterDisplayName =


		(SELECT A.DisplayName


		FROM CostCenterDimvw A


		WHERE A.CostCenterDimKey = @CostCenterId


		AND A.IsDeleted = 0)


	END


	


	


	-- get location display name


	IF @LocationId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET LocationDisplayName =


		(SELECT A.DisplayName


		FROM LocationDimvw A


		WHERE A.LocationDimKey = @LocationId


		AND A.IsDeleted = 0)


	END








	-- get supplier display name


	IF @SupplierId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET SupplierDisplayName =


		(SELECT A.DisplayName


		FROM CompanyDimvw A


		WHERE A.CompanyDimKey = @SupplierId


		AND A.IsDeleted = 0)


	END


	


	-- get manufacturer display name


	IF @ManufacturerId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET ManufacturerDisplayName =


		(SELECT A.DisplayName


		FROM CompanyDimvw A


		WHERE A.CompanyDimKey = @ManufacturerId


		AND A.IsDeleted = 0)


	END


	


	-- get status display name


	IF @StatusId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET StatusDisplayName =


		(SELECT A.HardwareAssetLifecycleStatusValue


		FROM HardwareAssetLifecycleStatus A


		WHERE A.HardwareAssetLifecycleStatusId = @StatusId)


	END


	


	-- get make/model display name


	IF @MakeModelId IS NOT NULL


	BEGIN


		UPDATE #displayNames SET MakeModelDisplayName =


		(SELECT A.DisplayName


		FROM HardwareCatalogDimvw A


		WHERE A.HardwareCatalogDimKey = @MakeModelId


		AND A.IsDeleted = 0)


	END


	








	-- retrieve data


	SELECT A.LocationDisplayName, A.CostCenterDisplayName, A.ManufacturerDisplayName,


			A.OrganizationDisplayName, A.StatusDisplayName, A.SupplierDisplayName,


			A.MakeModelDisplayName


	FROM #displayNames A





END


GO








GRANT EXECUTE ON dbo.ProvanceProc_GetParameterDisplayName TO reportuser


GO