USP_DATALIST_DONORCHALLENGEMATCHEDFUNDS

CREATE procedure dbo.USP_DATALIST_DONORCHALLENGEMATCHEDFUNDS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATERANGECODE smallint = 6
)
as                 
  set nocount on;

  declare @STARTDATE datetime;
  declare @ENDDATE datetime;

  exec dbo.USP_RESOLVEDATEFILTER @DATERANGECODE, @STARTDATE output, @ENDDATE output

  declare @BASECURRENCYID uniqueidentifier;
  select
    @BASECURRENCYID = BASECURRENCYID
  from
    dbo.DONORCHALLENGE
  where
    ID = @ID;

  select
    DCE.ID,
    DCE.REVENUESPLITID,
    REVENUE.ID as REVENUEID,
    REVENUE.DATE,
    CONSTITUENT.NAME,
    CONSTITUENT.LOOKUPID,
    case
        when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
            then REVENUESPLIT.TRANSACTIONAMOUNT
        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
    end as AMOUNT,
    case when REVENUESPLIT.DESIGNATIONID is null then
      MEMBERSHIPLEVEL.NAME
    else
      dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID)
    end as DESIGNATIONNAME,
    DCE.MATCHEDREVENUEID,
    DCE.AMOUNT as MATCHEDAMOUNT,
    dbo.UFN_DESIGNATION_BUILDNAME(DCE.DESIGNATIONID) MATCHEDDESIGNATIONNAME,
    null as PARENTID,
    REVENUE.TRANSACTIONTYPECODE,
    DCE.BASECURRENCYID
  from
    dbo.DONORCHALLENGEENCUMBERED DCE
  inner join
    dbo.REVENUESPLIT on REVENUESPLIT.ID = DCE.REVENUESPLITID
  inner join
    dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
  inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
  left join
    dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
  left join
    dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
  where
    DCE.DONORCHALLENGEID = @ID
    and REVENUE.DATE between @STARTDATE and @ENDDATE
    and DCE.STATUSTYPECODE = 1
    and REVENUESPLIT.APPLICATIONCODE <> 2
union all    
  select
    DCE.ID,
    DCE.REVENUESPLITID,
    PAYMENT.ID as REVENUEID,
    PAYMENT.DATE,
    CONSTITUENT.NAME,
    CONSTITUENT.LOOKUPID,
    case
        when @BASECURRENCYID = PAYMENTSPLIT.TRANSACTIONCURRENCYID
            then PAYMENTSPLIT.TRANSACTIONAMOUNT
        else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
    end as AMOUNT,
    dbo.UFN_DESIGNATION_BUILDNAME(PAYMENTSPLIT.DESIGNATIONID) DESIGNATIONNAME,
    DCE.MATCHEDREVENUEID,
    DCE.AMOUNT as MATCHEDAMOUNT,
    dbo.UFN_DESIGNATION_BUILDNAME(DCE.DESIGNATIONID) MATCHEDDESIGNATIONNAME,
    (select top 1 REVENUESPLIT.ID 
    from dbo.INSTALLMENTSPLITPAYMENT
      inner join dbo.INSTALLMENTSPLIT 
        on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
      inner join dbo.REVENUESPLIT 
        on INSTALLMENTSPLIT.PLEDGEID = REVENUESPLIT.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
      where INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID) PARENTID,
    PAYMENT.TRANSACTIONTYPECODE,
    DCE.BASECURRENCYID
  from
    dbo.DONORCHALLENGEENCUMBERED DCE
  inner join dbo.REVENUESPLIT  PAYMENTSPLIT
    on PAYMENTSPLIT.ID = DCE.REVENUESPLITID
  inner join dbo.REVENUE PAYMENT
    on PAYMENT.ID = PAYMENTSPLIT.REVENUEID
  inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = PAYMENT.CONSTITUENTID
  where
    DCE.DONORCHALLENGEID = @ID
    and DCE.STATUSTYPECODE = 1 
    and PAYMENTSPLIT.APPLICATIONCODE = 2
    and PAYMENT.DATE between @STARTDATE and @ENDDATE
  order by
    DATE desc, AMOUNT desc