UFN_TRANSLATIONFUNCTION_MKTMAILINGHIERACHRY

CREATE function dbo.[UFN_TRANSLATIONFUNCTION_MKTMAILINGHIERACHRY]
(
  @ID nvarchar(64)
) 
returns nvarchar(max) 
as
begin
  declare @RETURN nvarchar(max);
  declare @IDTYPE nvarchar(16);
  set @IDTYPE = upper(substring(@ID,1,charindex('|',@ID)-1));
  set @ID = substring(@ID,charindex('|',@ID)+1,len(@ID));


  if @IDTYPE = 'PLANITEM'
  begin
    with [PLANS]([ID], [HIERARCHY]) as
    (
      select [ID], convert(nvarchar(max),[NAME]) as [HIERARCHY]
      from dbo.[MKTMARKETINGPLANITEM]
      where [PARENTMARKETINGPLANITEMID] is null
      union all
      select [MKTMARKETINGPLANITEM].[ID],convert(nvarchar(max),[PLANS].[HIERARCHY] + '\' + [MKTMARKETINGPLANITEM].[NAME]) as [HIERARCHY]
      from dbo.[MKTMARKETINGPLANITEM]
      inner join [PLANS]
      on [MKTMARKETINGPLANITEM].[PARENTMARKETINGPLANITEMID] = [PLANS].[ID]
    )
    select @RETURN = [HIERARCHY]
    from [PLANS]
    where [ID] = @ID;
  end;
  if @IDTYPE = 'MARKETING EFFORT'
  begin
    select @RETURN = [MKTSEGMENTATIONACTIVATE].[APPEALDESCRIPTION] + '\' + [MKTSEGMENTATION].[NAME]
    from 
      dbo.[MKTSEGMENTATIONACTIVATE]
      inner join dbo.[MKTSEGMENTATION]
      on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;
  end;
  if @IDTYPE = 'APPEAL'
  begin
    select distinct @RETURN = [APPEALDESCRIPTION]
    from dbo.[MKTSEGMENTATIONACTIVATE]
    where [APPEALID] = @ID;
  end    ;

  return @RETURN;
end