UFN_REVENUE_DESIGNATIONLIST

            CREATE function dbo.UFN_REVENUE_DESIGNATIONLIST
            (
                @REVENUEID uniqueidentifier
            )
            returns nvarchar(3000)
            as 
            begin
                declare @DESIGNATION nvarchar(512);
                declare @r nvarchar(3000);            
                declare @SEPERATOR nvarchar(2);

                set @r=N'';

        select @r = replace(replace(replace ((select 
            CASE     
              WHEN (dl5.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + isnull(dl4.NAME, '<Unspecified>') + ' \ ' + dl5.NAME
              WHEN (dl4.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + dl4.NAME
              WHEN (dl3.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + dl3.NAME
              WHEN (dl2.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + dl2.NAME
              ELSE dl1.NAME
            END + '; '
        from dbo.FINANCIALTRANSACTIONLINEITEM as RDS
        inner join dbo.REVENUESPLIT_EXT on RDS.ID = REVENUESPLIT_EXT.ID
        inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
        inner join dbo.DESIGNATIONLEVEL dl1 on DESIGNATION.DESIGNATIONLEVEL1ID = dl1.id
        left join dbo.DESIGNATIONLEVEL dl2 on DESIGNATION.DESIGNATIONLEVEL2ID = dl2.id
        left join dbo.DESIGNATIONLEVEL dl3 on DESIGNATION.DESIGNATIONLEVEL3ID = dl3.id
        left join dbo.DESIGNATIONLEVEL dl4 on DESIGNATION.DESIGNATIONLEVEL4ID = dl4.id
        left join dbo.DESIGNATIONLEVEL dl5 on DESIGNATION.DESIGNATIONLEVEL5ID = dl5.id
        where RDS.FINANCIALTRANSACTIONID = @REVENUEID 
          and REVENUESPLIT_EXT.DESIGNATIONID is not null
          and RDS.DELETEDON is null and RDS.TYPECODE != 1 
          for XML PATH('')) ,'&amp;','&'),'&gt;','>'),'&lt;','<') 

        if @r != ''
            set @r = left(@r,LEN(@r) - 1)

/*
                declare DESIGNATIONCURSOR cursor local fast_forward for
                    select dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
                    from dbo.FINANCIALTRANSACTIONLINEITEM as RDS
                    inner join dbo.REVENUESPLIT_EXT on RDS.ID = REVENUESPLIT_EXT.ID
                    where RDS.FINANCIALTRANSACTIONID = @REVENUEID
                        and RDS.DELETEDON is null
                        and RDS.TYPECODE <> 1

                open DESIGNATIONCURSOR;
                    fetch next from DESIGNATIONCURSOR into @DESIGNATION;
                    set @r = '';
                    set @SEPERATOR = '';

                    while @@FETCH_STATUS = 0
                    begin
                        if @DESIGNATION is not null
                        begin
                            set @r = @r + @SEPERATOR + @DESIGNATION;
                            set @SEPERATOR = '; ';
                        end
                        fetch next from DESIGNATIONCURSOR into @DESIGNATION;
                    end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
                close DESIGNATIONCURSOR;
                deallocate DESIGNATIONCURSOR;
*/
                return @r;
            end