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

Element properties:

Source Code:

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

File Content: ProvanceFunc_GetParentCostCenter.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 id = object_id(N'[dbo].[ProvanceFunc_GetParentCostCenter]')


     AND (OBJECTPROPERTY(id, N'IsScalarFunction') = 1 OR OBJECTPROPERTY(id, N'IsTableFunction') = 1) 


)


   DROP FUNCTION dbo.ProvanceFunc_GetParentCostCenter


GO





CREATE FUNCTION [dbo].[ProvanceFunc_GetParentCostCenter] (@costCenterID int)


RETURNS int


AS


BEGIN


	DECLARE 


		@parentCostCenter	int,


		@rowcount		int


	SELECT


		@parentCostCenter	= @costCenterID,


		@rowcount		= -1








	WHILE @parentCostCenter IS NOT NULL AND @rowcount <> 0


	BEGIN


		SELECT @parentCostCenter = A.CostCenterDimKey


		FROM CostCenterChildCostCenterFactvw A


		WHERE A.CostCenterContainsChildCostCenter_CostCenterDimKey = @parentCostCenter


		AND DeletedDate IS NULL


		


		SELECT @rowcount = @@RowCount


	END








	-- Return the result of the function


	RETURN @parentCostCenter





END


GO








GRANT EXECUTE ON dbo.ProvanceFunc_GetParentCostCenter TO reportuser


GO