USP_SIMPLEDATALIST_RECOGNITIONCREDIT_DONORCHALLENGE

CREATE procedure dbo.USP_SIMPLEDATALIST_RECOGNITIONCREDIT_DONORCHALLENGE
(
  @REVENUESPLITID uniqueidentifier,
  @INCLUDEORIGINAL bit = 1
)
as
    select
      REVENUESPLIT.ID as VALUE,
      NF.NAME as LABEL,      
      '0' + cast(REVENUESPLIT.DESIGNATIONID as nvarchar(36)) as DESCRIPTION --original

    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 = @REVENUESPLITID and
      @INCLUDEORIGINAL = 1

    union

    select
      case when DONORCHALLENGE.EXTERNALSPONSORID is null then DONORCHALLENGEENCUMBERED.ID else MATCHEDSPLIT.ID end as VALUE,
      case when DONORCHALLENGE.EXTERNALSPONSORID is null then DONORCHALLENGESPONSORCODE.DESCRIPTION else NF.NAME end + ' matched ' + dbo.UFN_CURRENCY_GETSYMBOL(DONORCHALLENGEENCUMBERED.BASECURRENCYID) + cast(DONORCHALLENGEENCUMBERED.AMOUNT as varchar(20)) + ' for ' + DONORCHALLENGE.NAME as LABEL,      
      case when DONORCHALLENGE.EXTERNALSPONSORID is null then '2' else '1' end + cast(DONORCHALLENGEENCUMBERED.DESIGNATIONID as nvarchar(36)) as DESCRIPTION --external sponsor = 1, internal sponsor = 2

    from
      dbo.DONORCHALLENGEENCUMBERED
    inner join dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
    inner join dbo.REVENUESPLIT on DONORCHALLENGEENCUMBERED.REVENUESPLITID = REVENUESPLIT.ID
    inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    left join dbo.CONSTITUENT on DONORCHALLENGE.EXTERNALSPONSORID = CONSTITUENT.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF  
    left join dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID
    left join dbo.REVENUESPLIT MATCHEDSPLIT on DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID = MATCHEDSPLIT.REVENUEID and DONORCHALLENGEENCUMBERED.DESIGNATIONID = MATCHEDSPLIT.DESIGNATIONID
    where DONORCHALLENGEENCUMBERED.REVENUESPLITID = @REVENUESPLITID
      and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 1 --matched

      and DONORCHALLENGE.TYPECODE = 0 --match per gift

      and REVENUE.TRANSACTIONTYPECODE = 0 --donation or pledge payment