UFN_TRANSLATIONFUNCTION_RECOGNITIONCREDIT_GETDONORCHALLENGESPONSOR

CREATE function dbo.UFN_TRANSLATIONFUNCTION_RECOGNITIONCREDIT_GETDONORCHALLENGESPONSOR
(
  @ID uniqueidentifier
) 
returns nvarchar(max)
as
begin
  declare @s nvarchar(max);
  set @s = N'';

  --external sponsors

  select
    @s = SPONSOR_NF.NAME + ' matched ' + dbo.UFN_CURRENCY_GETSYMBOL(DONORCHALLENGEENCUMBERED.BASECURRENCYID) + cast(DONORCHALLENGEENCUMBERED.AMOUNT as varchar(20)) + ' for ' + DONORCHALLENGE.NAME
  from
      dbo.DONORCHALLENGEENCUMBERED
  inner join 
    dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
  inner join 
    dbo.CONSTITUENT EXTERNALSPONSOR on DONORCHALLENGE.EXTERNALSPONSORID = EXTERNALSPONSOR.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EXTERNALSPONSOR.ID) SPONSOR_NF  
  inner join
    dbo.REVENUESPLIT MATCHED on DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID = MATCHED.REVENUEID and DONORCHALLENGEENCUMBERED.DESIGNATIONID = MATCHED.DESIGNATIONID
  where
    MATCHED.ID = @ID
    and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 1

  if @s = ''
  begin    
    --internal sponsors

    select
      @s = DONORCHALLENGESPONSORCODE.DESCRIPTION + ' matched ' + dbo.UFN_CURRENCY_GETSYMBOL(DONORCHALLENGEENCUMBERED.BASECURRENCYID) + cast(DONORCHALLENGEENCUMBERED.AMOUNT as varchar(20)) + ' for ' + DONORCHALLENGE.NAME
    from
      dbo.DONORCHALLENGEENCUMBERED
    inner join 
      dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
    inner join 
      dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID
    where DONORCHALLENGEENCUMBERED.ID = @ID
      and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 1
  end    

  if @s = ''
  begin  
    --original

    select
      @s = NF.NAME
    from
      dbo.REVENUESPLIT
    inner join
      dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
      cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF      
    where
      REVENUESPLIT.ID = @ID
  end

  return @s;
end