IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pr_psp_code_dt461]'))
	DROP FUNCTION [dbo].[pr_psp_code_dt461]
GO
CREATE FUNCTION [dbo].[pr_psp_code_dt461]
( 
	@project_id varchar(18)
)

RETURNS varchar(2000)
AS 
BEGIN		   
	DECLARE
         @psp_code varchar(2000);
	WITH ProjectCTE(project_id, super_project, main_project, psp_code)
        AS
        (
          -- anchor project
          SELECT di001001, di001003, di001052, cast(di001001 as varchar(2000))
          FROM dt461
          where di001052 = (SELECT di001052 FROM dt461 where di001001 = @project_id)
         
          UNION ALL
         
          -- recursive projects
          SELECT E.di001001, E.di001003, E.di001052, cast(M.psp_code+';'+E.di001001 as varchar(2000))
          FROM dt461 AS E
		  JOIN ProjectCTE AS M
              ON E.di001003 = M.project_id AND E.DI001052 = M.main_project
        )

        -- outer query
        SELECT @psp_code = psp_code FROM ProjectCTE WHERE project_id=@project_id
	
	RETURN @psp_code

END;
GO
