USP_DATALIST_DONORCHALLENGEENCUMBEREDFUNDS

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

  declare @STARTDATE datetime;
  declare @ENDDATE datetime;
  exec dbo.USP_RESOLVEDATEFILTER @DATERANGECODE, @STARTDATE output, @ENDDATE output

  declare @BASECURRENCYID uniqueidentifier;
  declare @BASECURRENCYDECIMALDIGITS tinyint;
  declare @BASECURRENCYROUNDINGTYPECODE tinyint;

  declare @MINGIFTAMOUNT money;
  declare @MATCHINGFACTOR money;
  declare @PLEDGEEND datetime;
  declare @MAXMATCHPERGIFT money;
  declare @MATCHTYPECODE tinyint;

  select
    @MINGIFTAMOUNT = DONORCHALLENGE.MINGIFTAMOUNT,
    @MATCHTYPECODE = DONORCHALLENGE.MATCHTYPECODE,
    @MATCHINGFACTOR =
      case
        when DONORCHALLENGE.TYPECODE = 1 then
          1
        else
          DONORCHALLENGE.MATCHINGFACTOR
      end,
    @PLEDGEEND =
      dateadd
      (
        month,
        DONORCHALLENGE.PAYPLEDGESWITHIN * 
          case
            when DONORCHALLENGE.PAYPLEDGESWITHINUNITCODE = 0 then
              12
            else
              1
          end, 
        DONORCHALLENGE.ENDDATE
      ),
    @MAXMATCHPERGIFT = DONORCHALLENGE.MAXMATCHPERGIFT,
    @BASECURRENCYID = DONORCHALLENGE.BASECURRENCYID
  from
    dbo.DONORCHALLENGE
  where
    ID = @ID;

  select
    @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS,
    @BASECURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
  from
    dbo.CURRENCY
  where
    ID = @BASECURRENCYID;

  select
    null as PARENTID,
    DCE.ID,
    FTLI.ID as REVENUESPLITID,
    FT.ID as REVENUEID,
    cast(FT.[DATE] as datetime) as [DATE],
    NF.NAME,
    CONSTITUENT.LOOKUPID,
    case
      when @BASECURRENCYID = FT.TRANSACTIONCURRENCYID then
        FTLI.TRANSACTIONAMOUNT
      else
        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FTLI.ID, @BASECURRENCYID)
    end as AMOUNT,
    case
      when RSE.DESIGNATIONID is null then
        MEMBERSHIPLEVEL.NAME
      else
        dbo.UFN_DESIGNATION_BUILDNAME(RSE.DESIGNATIONID)
    end as DESIGNATIONNAME,
    --Bug#254084 - Only a payment(TRANSACTIONTYPECODE = 0) can to applied to some application

    --so set application to null for all other transaction types

    case
      when FT.TYPECODE = 0 then 
        RSE.[APPLICATION]
      else
        null
    end as [APPLICATION],
    case
      when FT.TYPECODE = 1 then
        dbo.UFN_DONORCHALLENGEENCUMBERED_PLEDGEENCUMBEREDAMOUNT(DCE.ID)
      else
        DCE.AMOUNT
    end as ENCUMBEREDAMOUNT,
    DCE.STATUSTYPECODE,
    dbo.UFN_DESIGNATION_BUILDNAME(DCE.DESIGNATIONID),
    dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(DCE.REVENUERECOGNITIONTYPECODEID) as REVENUERECOGNITIONTYPE,
    cast(DCE.ID as nvarchar(36)) + cast(DCE.ID as nvarchar(36)) as PAYMENTAPPROVECONTEXT,
    FT.TYPECODE,
    @BASECURRENCYID as BASECURRENCYID
  from
    dbo.DONORCHALLENGEENCUMBERED as DCE
  left join
    dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = DCE.REVENUESPLITID
  left join
    dbo.REVENUESPLIT_EXT as RSE on RSE.ID = FTLI.ID
  left join
    dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
  left join
    dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FTLI.ID
  left join
    dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
  inner join
    dbo.CONSTITUENT on CONSTITUENT.ID = coalesce(FT.CONSTITUENTID, DCE.CONSTITUENTID)
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) as NF
  where
    DCE.DONORCHALLENGEID = @ID and
    FTLI.DELETEDON is null and
    (FT.[DATE] is null or FT.[DATE] between @STARTDATE and @ENDDATE) and
    DCE.STATUSTYPECODE = @STATUSTYPECODE

  union all

  select distinct
    FTLI.ID as PARENTID,
    DCE.ID,
    PAYMENTSPLIT.ID as REVENUESPLITID,
    PAYMENT.ID as REVENUEID,
    cast(PAYMENT.[DATE] as datetime) as [DATE],
    NF.NAME,
    PAYMENTCONSTITUENT.LOOKUPID,
    case
      when @BASECURRENCYID = PAYMENT.TRANSACTIONCURRENCYID then
        PAYMENTSPLIT.TRANSACTIONAMOUNT
      else
        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
    end as AMOUNT,
    dbo.UFN_DESIGNATION_BUILDNAME(PAYMENTSPLIT_RSE.DESIGNATIONID) DESIGNATIONNAME,
    PAYMENTSPLIT_RSE.[APPLICATION],
    case
      when dbo.UFN_CURRENCY_ROUND
      (
        case
          when @MATCHTYPECODE = 1 then
            case
              when PAYMENT.TRANSACTIONAMOUNT = 0 then
                0
              else
                PAYMENT_RE.RECEIPTAMOUNT / PAYMENT.TRANSACTIONAMOUNT *
                  case
                    when @BASECURRENCYID = PAYMENT.TRANSACTIONCURRENCYID then
                      PAYMENTSPLIT.TRANSACTIONAMOUNT
                    else
                      dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
                  end
            end
          else
            case
              when @BASECURRENCYID = PAYMENT.TRANSACTIONCURRENCYID then
                PAYMENTSPLIT.TRANSACTIONAMOUNT
              else
                dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
            end
        end * @MATCHINGFACTOR,
        @BASECURRENCYDECIMALDIGITS,
        @BASECURRENCYROUNDINGTYPECODE
      ) > DCE.AMOUNT then
        DCE.AMOUNT
      else
        dbo.UFN_CURRENCY_ROUND
        (
          case
            when @MATCHTYPECODE = 1 then
              case
                when PAYMENT.TRANSACTIONAMOUNT = 0 then
                  0
                else
                  PAYMENT_RE.RECEIPTAMOUNT / PAYMENT.TRANSACTIONAMOUNT *
                    case
                      when @BASECURRENCYID = PAYMENT.TRANSACTIONCURRENCYID then
                        PAYMENTSPLIT.TRANSACTIONAMOUNT
                      else
                        dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
                    end
              end
            else
              case
                when @BASECURRENCYID = PAYMENT.TRANSACTIONCURRENCYID then
                  PAYMENTSPLIT.TRANSACTIONAMOUNT
                else
                  dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYMENTSPLIT.ID, @BASECURRENCYID)
                end
          end * @MATCHINGFACTOR, 
          @BASECURRENCYDECIMALDIGITS,
          @BASECURRENCYROUNDINGTYPECODE
        )
    end as ENCUMBEREDAMOUNT,
    DCE.STATUSTYPECODE,
    dbo.UFN_DESIGNATION_BUILDNAME(DCE.DESIGNATIONID),
    dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(DCE.REVENUERECOGNITIONTYPECODEID) as REVENUERECOGNITIONTYPE,
    cast(DCE.DONORCHALLENGEID as nvarchar(36)) + cast(PAYMENTSPLIT.ID as nvarchar(36)) as PAYMENTAPPROVECONTEXT,
    FT.TYPECODE,
    @BASECURRENCYID
  from
    dbo.DONORCHALLENGEENCUMBERED as DCE
  inner join
    dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = DCE.REVENUESPLITID
  inner join
    dbo.REVENUESPLIT_EXT as RSE on RSE.ID = FTLI.ID
  inner join
    dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
  inner join
    dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.PLEDGEID = FT.ID and INSTALLMENTSPLIT.DESIGNATIONID = RSE.DESIGNATIONID
  inner join
    dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
  inner join
    dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
  inner join
    dbo.REVENUESPLIT_EXT as PAYMENTSPLIT_RSE on PAYMENTSPLIT_RSE.ID = PAYMENTSPLIT.ID
  inner join
    dbo.FINANCIALTRANSACTION as PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
  inner join
    dbo.REVENUE_EXT as PAYMENT_RE on PAYMENT_RE.ID = PAYMENT.ID
  inner join
    dbo.CONSTITUENT as PAYMENTCONSTITUENT on PAYMENTCONSTITUENT.ID = PAYMENT.CONSTITUENTID
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(PAYMENTCONSTITUENT.ID) as NF
  left join
    dbo.DONORCHALLENGEENCUMBERED on DONORCHALLENGEENCUMBERED.REVENUESPLITID = PAYMENTSPLIT.ID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @ID
  where
    DCE.DONORCHALLENGEID = @ID and
    --we only need to add payments for encumbered

    DCE.STATUSTYPECODE = 0 and
    PAYMENT.[DATE] <= @PLEDGEEND and
    PAYMENT.[DATE] between @STARTDATE and @ENDDATE and
    DONORCHALLENGEENCUMBERED.ID is null and
    FTLI.DELETEDON is null and
    PAYMENTSPLIT.DELETEDON is null
  order by
    [DATE],
    AMOUNT