USP_REPORT_PLEDGESTATUSANDANALYSIS

Parameters

Parameter	Parameter Type	Mode
@DISPLAYFILTER	tinyint	IN
@MONEYFILTER	tinyint	IN
@FILTERAMOUNT	money	IN
@HIGHFILTERAMOUNT	money	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@SITEFILTERMODE	tinyint	IN
@SITESSELECTED	xml	IN
@CURRENCYCODE	tinyint	IN
@DESIGNATIONID	uniqueidentifier	IN
Definition

Copy

create procedure dbo.USP_REPORT_PLEDGESTATUSANDANALYSIS
(
    @DISPLAYFILTER tinyint = 0,
    @MONEYFILTER tinyint = 0,
    @FILTERAMOUNT money = 0.0,
    @HIGHFILTERAMOUNT money = 0.0,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @CURRENCYCODE tinyint = null,
    @DESIGNATIONID uniqueidentifier = null
)
as
  declare @ISADMIN bit;
  declare @APPUSER_IN_NONRACROLE bit;
  declare @APPUSER_IN_NOSECGROUPROLE bit;

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);


  declare @SELECTEDCURRENCYID uniqueidentifier;
  if coalesce(@CURRENCYCODE, 1) = 1
    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  else if @CURRENCYCODE = 3
    set @SELECTEDCURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @ISOCODE nvarchar(3);
  declare @SYMBOLDISPLAYSETTINGCODE tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);

  select
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE,
    @ISOCODE = ISO4217,
    @SYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE,
    @CURRENCYSYMBOL = CURRENCYSYMBOL
  from
    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);

  declare @USERGRANTEDTRANSACTIONPAGE bit = 0;
  declare @USERGRANTEDDESIGNATIONPAGE bit = 0;

  declare @ENDDATETIME datetime = dateadd(ms, 86399996, cast(@ENDDATE as datetime))

  if @ISADMIN = 1
  begin
    set @USERGRANTEDTRANSACTIONPAGE = 1;
    set @USERGRANTEDDESIGNATIONPAGE = 1;
  end
  else
  begin
    select
      @USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'd00e6c42-2434-4d85-8a04-2323ca6bb2e7'),
      @USERGRANTEDDESIGNATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '4EADC264-0A44-4DF5-8C8C-D89A1C48746C');
  end

  -- #774147 - Performance/Filter Changes

  if object_id('tempdb..#TMP_PLEDGES_SEMIFILTERED') is not null
    drop table #TMP_PLEDGES_SEMIFILTERED;

  create table #TMP_PLEDGES_SEMIFILTERED (
    ID uniqueidentifier not null,
    DATE datetime not null,
    CONSTITUENTID uniqueidentifier null,
    TRANSACTIONTYPECODE tinyint not null,
    TRANSACTIONCURRENCYID uniqueidentifier null,
    DELETEDON datetime null,
    GIVENANONYMOUSLY bit null,
    BASECURRENCYID uniqueidentifier null,
    TRANSACTIONAMOUNT money not null
  );

  insert into #TMP_PLEDGES_SEMIFILTERED
  select
    PLEDGE.ID,
    cast(PLEDGE.DATE as datetime),
    PLEDGE.CONSTITUENTID,
    PLEDGE.TYPECODE,
    case when PLEDGE.DELETEDON is null then PLEDGE.TRANSACTIONCURRENCYID else null end,
    PLEDGE.DELETEDON,
    null,
    null,
    PLEDGE.TRANSACTIONAMOUNT
  from dbo.FINANCIALTRANSACTION PLEDGE
  where
    (PLEDGE.TYPECODE = 1) and
    (@SITEFILTERMODE = 0
      or exists
      (
        select 1
        from dbo.UFN_SITEID_MAPFROM_REVENUEID_2(PLEDGE.ID) REVENUESITE
        inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER on REVENUESITE.SITEID = SITEFILTER.SITEID
      )
    ) and exists ( -- Site security filter

            select HASPERMISSION
            from dbo.UFN_SITEID_MAPFROM_REVENUEID_2(PLEDGE.ID) REVSITES
            cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, 'cb0297d3-c8cf-4f43-b308-166d339e2f84', REVSITES.SITEID)
    );

  if object_id('tempdb..#TMP_PLEDGES_FILTERED') is not null
    drop table #TMP_PLEDGES_FILTERED;

  create table #TMP_PLEDGES_FILTERED (
    ID uniqueidentifier not null,
    DATE datetime not null,
    CONSTITUENTID uniqueidentifier null,
    TRANSACTIONTYPECODE tinyint not null,
    TRANSACTIONCURRENCYID uniqueidentifier null,
    DELETEDON datetime null,
    GIVENANONYMOUSLY bit null,
    BASECURRENCYID uniqueidentifier null
  );

  --Only pledges with a balance

  if @DISPLAYFILTER = 0
  begin
    insert into #TMP_PLEDGES_FILTERED
    select *
    from (
      select
          ID,
          DATE,
        CONSTITUENTID,
        TRANSACTIONTYPECODE,
        TRANSACTIONCURRENCYID,
        DELETEDON,
        GIVENANONYMOUSLY,
        BASECURRENCYID
      from #TMP_PLEDGES_SEMIFILTERED PLEDGE
      where (PLEDGE.DATE between @STARTDATE and @ENDDATETIME)
    ) PLEDGE
    where
      (
       ((@MONEYFILTER = 0) and (dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2(PLEDGE.ID, @ENDDATETIME, @SELECTEDCURRENCYID) > @FILTERAMOUNT)) or
       ((@MONEYFILTER = 1) and (dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2(PLEDGE.ID, @ENDDATETIME, @SELECTEDCURRENCYID) < @FILTERAMOUNT)) or
       ((@MONEYFILTER = 2) and (dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2(PLEDGE.ID, @ENDDATETIME, @SELECTEDCURRENCYID) = @FILTERAMOUNT)) or
       ((@MONEYFILTER = 3) and (dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_2(PLEDGE.ID, @ENDDATETIME, @SELECTEDCURRENCYID) between @FILTERAMOUNT and @HIGHFILTERAMOUNT))
      );
  end
  --Only pledges that have been paid in full

  else if @DISPLAYFILTER = 1
  begin

    if object_id('tempdb..#TMP_PLEDGES_BALANCEASOF') is not null
      drop table #TMP_PLEDGES_BALANCEASOF;

    create table #TMP_PLEDGES_BALANCEASOF (
      PLEDGEID uniqueidentifier not null,
      ASOFBALANCE_ENDDATE money not null,
      ASOFBALANCE_STARTDATE money not null
    );

    insert into #TMP_PLEDGES_BALANCEASOF
    select
      PSF.ID,
      coalesce(ASOFAMOUNT_ENDDATE.AMOUNT, 0),
      coalesce(ASOFAMOUNT_STARTDATE.AMOUNT, 0)
    from #TMP_PLEDGES_SEMIFILTERED PSF
    left join ( --ASOFBALANCE - END DATE

      select
        PLEDGE.ID as PLEDGEID,
        PLEDGE.TRANSACTIONAMOUNT - (coalesce(ISPA.AMOUNT, 0) + coalesce(WOA.AMOUNT, 0)) as AMOUNT
      from #TMP_PLEDGES_SEMIFILTERED PLEDGE
        left join ( --INSTALLMENTSPLITPAYMENT AMOUNT - END DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID as PLEDGEID,
            coalesce(sum(ISP.AMOUNT), 0) as AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = ISP.PAYMENTID
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
          where cast(FT.DATE as date) <= @ENDDATETIME
            and FTLI.TYPECODE <> 1
            and FTLI.DELETEDON is null
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) ISPA on ISPA.PLEDGEID = PLEDGE.ID
        left join ( --WRITEOFF AMOUNT - END DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID PLEDGEID,
            coalesce(sum(ISWO.TRANSACTIONAMOUNT), 0) AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLIT ISPLT on ISPLT.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.INSTALLMENTSPLITID = ISPLT.ID
            left join dbo.WRITEOFF WO on WO.ID = ISWO.WRITEOFFID
          where WO.DATE <= @ENDDATETIME
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) WOA on WOA.PLEDGEID = PLEDGE.ID
    ) ASOFAMOUNT_ENDDATE on ASOFAMOUNT_ENDDATE.PLEDGEID = PSF.ID
    left join ( --ASOFBALANCE - START DATE

      select
        PLEDGE.ID as PLEDGEID,
        PLEDGE.TRANSACTIONAMOUNT - (coalesce(ISPA.AMOUNT, 0) + coalesce(WOA.AMOUNT, 0)) as AMOUNT
      from #TMP_PLEDGES_SEMIFILTERED PLEDGE
        left join ( --INSTALLMENTSPLITPAYMENT AMOUNT - START DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID PLEDGEID,
            coalesce(sum(ISP.AMOUNT), 0) AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = ISP.PAYMENTID
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
          where cast(FT.DATE as date) <= @STARTDATE
            and FTLI.TYPECODE <> 1
            and FTLI.DELETEDON is null
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) ISPA on ISPA.PLEDGEID = PLEDGE.ID
        left join ( --WRITEOFF AMOUNT - END DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID PLEDGEID,
            coalesce(sum(ISWO.TRANSACTIONAMOUNT), 0) AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLIT ISPLT on ISPLT.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.INSTALLMENTSPLITID = ISPLT.ID
            left join dbo.WRITEOFF WO on WO.ID = ISWO.WRITEOFFID
          where WO.DATE <= @STARTDATE
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) WOA on WOA.PLEDGEID = PLEDGE.ID
    ) ASOFAMOUNT_STARTDATE on ASOFAMOUNT_STARTDATE.PLEDGEID = PSF.ID;

    insert into #TMP_PLEDGES_FILTERED
    select
      PSF.ID,
      PSF.DATE,
      PSF.CONSTITUENTID,
      PSF.TRANSACTIONTYPECODE,
      PSF.TRANSACTIONCURRENCYID,
      PSF.DELETEDON,
      PSF.GIVENANONYMOUSLY,
      PSF.BASECURRENCYID
    from #TMP_PLEDGES_SEMIFILTERED PSF
      left join #TMP_PLEDGES_BALANCEASOF on #TMP_PLEDGES_BALANCEASOF.PLEDGEID = PSF.ID
    where
    --this will get pledges that had a balance as of the start date but have no balance at the end date

      (#TMP_PLEDGES_BALANCEASOF.ASOFBALANCE_ENDDATE = 0 and (#TMP_PLEDGES_BALANCEASOF.ASOFBALANCE_STARTDATE > 0 or (PSF.DATE between @STARTDATE and @ENDDATETIME and (#TMP_PLEDGES_BALANCEASOF.ASOFBALANCE_STARTDATE = 0))))
    and
    (
      ((@MONEYFILTER = 0) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) > @FILTERAMOUNT)) or
      ((@MONEYFILTER = 1) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) < @FILTERAMOUNT)) or
      ((@MONEYFILTER = 2) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) = @FILTERAMOUNT)) or
      ((@MONEYFILTER = 3) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) between @FILTERAMOUNT and @HIGHFILTERAMOUNT))
    );

    drop table #TMP_PLEDGES_BALANCEASOF;

  end
  --Only past due pledges

  else if @DISPLAYFILTER = 2
  begin
    insert into #TMP_PLEDGES_FILTERED
    select
      PSF.ID,
      PSF.DATE,
      PSF.CONSTITUENTID,
      PSF.TRANSACTIONTYPECODE,
      PSF.TRANSACTIONCURRENCYID,
      PSF.DELETEDON,
      PSF.GIVENANONYMOUSLY,
      PSF.BASECURRENCYID
    from #TMP_PLEDGES_SEMIFILTERED PSF
    --get past due amount by date - if it has a past due amount within the date-range, then include it in the set

    where (dbo.UFN_PLEDGE_GETPASTDUEAMOUNT_BYDATERANGE(PSF.ID, @STARTDATE, @ENDDATETIME) > 0)
      and (
        ((@MONEYFILTER = 0) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) > @FILTERAMOUNT)) or
        ((@MONEYFILTER = 1) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) < @FILTERAMOUNT)) or
        ((@MONEYFILTER = 2) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) = @FILTERAMOUNT)) or
        ((@MONEYFILTER = 3) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PSF.ID, @SELECTEDCURRENCYID) between @FILTERAMOUNT and @HIGHFILTERAMOUNT))
      );
  end
  --Only pledges with write-offs @FILTERAMOUNT IS BASED ON THE WRITE OFF AMOUNT TOTAL

  else if @DISPLAYFILTER = 3
  begin
    if object_id('tempdb..#TMP_PLEDGES_WRITEOFF') is not null
      drop table #TMP_PLEDGES_WRITEOFF;

create table #TMP_PLEDGES_WRITEOFF (
      PLEDGEID uniqueidentifier not null,
      WRITEOFFAMOUNT money not null
    );

    insert into #TMP_PLEDGES_WRITEOFF
    select
      PLEDGE.ID,
      SUM(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(ISWO.ID, @SELECTEDCURRENCYID))
    from #TMP_PLEDGES_SEMIFILTERED PLEDGE
      inner join WRITEOFF WO on WO.REVENUEID = PLEDGE.ID
      inner join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.WRITEOFFID = WO.ID
    where WO.DATE between @STARTDATE and @ENDDATETIME
    group by PLEDGE.ID;

    insert into #TMP_PLEDGES_FILTERED
    select
      PSF.ID,
      PSF.DATE,
      PSF.CONSTITUENTID,
      PSF.TRANSACTIONTYPECODE,
      PSF.TRANSACTIONCURRENCYID,
      PSF.DELETEDON,
      PSF.GIVENANONYMOUSLY,
      PSF.BASECURRENCYID
    from #TMP_PLEDGES_SEMIFILTERED PSF
    left join #TMP_PLEDGES_WRITEOFF PWO on PWO.PLEDGEID = PSF.ID
    where
      ((@MONEYFILTER = 0) and isnull(PWO.WRITEOFFAMOUNT, 0) > @FILTERAMOUNT) or
      ((@MONEYFILTER = 1) and isnull(PWO.WRITEOFFAMOUNT, 0) < @FILTERAMOUNT) or
      ((@MONEYFILTER = 2) and isnull(PWO.WRITEOFFAMOUNT, 0) = @FILTERAMOUNT) or
      ((@MONEYFILTER = 3) and isnull(PWO.WRITEOFFAMOUNT, 0) between @FILTERAMOUNT and @HIGHFILTERAMOUNT);

    drop table #TMP_PLEDGES_WRITEOFF;
  end
  --Only new pledges

  else if @DISPLAYFILTER = 4
  begin
    insert into #TMP_PLEDGES_FILTERED
    select
        PLEDGE.ID,
        PLEDGE.DATE,
      PLEDGE.CONSTITUENTID,
      PLEDGE.TRANSACTIONTYPECODE,
      PLEDGE.TRANSACTIONCURRENCYID,
      PLEDGE.DELETEDON,
      PLEDGE.GIVENANONYMOUSLY,
      PLEDGE.BASECURRENCYID
    from #TMP_PLEDGES_SEMIFILTERED PLEDGE
    where (PLEDGE.DATE between @STARTDATE and @ENDDATETIME)
      and (
          ((@MONEYFILTER = 0) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) > @FILTERAMOUNT)) or
        ((@MONEYFILTER = 1) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) < @FILTERAMOUNT)) or
        ((@MONEYFILTER = 2) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) = @FILTERAMOUNT)) or
        ((@MONEYFILTER = 3) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) between @FILTERAMOUNT and @HIGHFILTERAMOUNT))
      );
  end
  --All pledges (which ACTUALLY means new pledges in this time frame, any pledge with a write off during this time frame,

  --any pledge with payments in this time frame and any pledge with a balance in this time frame

  else if @DISPLAYFILTER = 5
  begin

    if object_id('tempdb..#TMP_PLEDGES_BALANCEASOF_D5') is not null
      drop table #TMP_PLEDGES_BALANCEASOF_D5;

    create table #TMP_PLEDGES_BALANCEASOF_D5 (
      PLEDGEID uniqueidentifier not null,
      ASOFBALANCE_STARTDATE money not null
    );

    insert into #TMP_PLEDGES_BALANCEASOF_D5
    select
      PSF.ID,
      coalesce(ASOFAMOUNT_STARTDATE.AMOUNT, 0)
    from #TMP_PLEDGES_SEMIFILTERED PSF
    left join ( --ASOFBALANCE - START DATE

      select
        PLEDGE.ID as PLEDGEID,
        PLEDGE.TRANSACTIONAMOUNT - (coalesce(ISPA.AMOUNT, 0) + coalesce(WOA.AMOUNT, 0)) as AMOUNT
      from #TMP_PLEDGES_SEMIFILTERED PLEDGE
        left join ( --INSTALLMENTSPLITPAYMENT AMOUNT - START DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID PLEDGEID,
            coalesce(sum(ISP.AMOUNT), 0) AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = ISP.PAYMENTID
            left join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
          where cast(FT.DATE as date) <= @STARTDATE
            and FTLI.TYPECODE <> 1
            and FTLI.DELETEDON is null
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) ISPA on ISPA.PLEDGEID = PLEDGE.ID
        left join ( --WRITEOFF AMOUNT - END DATE

          select
            #TMP_PLEDGES_SEMIFILTERED.ID PLEDGEID,
            coalesce(sum(ISWO.TRANSACTIONAMOUNT), 0) AMOUNT
          from #TMP_PLEDGES_SEMIFILTERED
            left join dbo.INSTALLMENTSPLIT ISPLT on ISPLT.PLEDGEID = #TMP_PLEDGES_SEMIFILTERED.ID
            left join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.INSTALLMENTSPLITID = ISPLT.ID
            left join dbo.WRITEOFF WO on WO.ID = ISWO.WRITEOFFID
          where WO.DATE <= @STARTDATE
          group by #TMP_PLEDGES_SEMIFILTERED.ID
        ) WOA on WOA.PLEDGEID = PLEDGE.ID
    ) ASOFAMOUNT_STARTDATE on ASOFAMOUNT_STARTDATE.PLEDGEID = PSF.ID;

    insert into #TMP_PLEDGES_FILTERED
    select
        ID,
        DATE,
      CONSTITUENTID,
      TRANSACTIONTYPECODE,
      TRANSACTIONCURRENCYID,
      DELETEDON,
      GIVENANONYMOUSLY,
      BASECURRENCYID
    from #TMP_PLEDGES_SEMIFILTERED PLEDGE
      left join #TMP_PLEDGES_BALANCEASOF_D5 on #TMP_PLEDGES_BALANCEASOF_D5.PLEDGEID = PLEDGE.ID
    where ((PLEDGE.DATE <= @ENDDATETIME) and ((#TMP_PLEDGES_BALANCEASOF_D5.ASOFBALANCE_STARTDATE > 0) or PLEDGE.DATE >= @STARTDATE))
      and (
          ((@MONEYFILTER = 0) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) > @FILTERAMOUNT)) or
        ((@MONEYFILTER = 1) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) < @FILTERAMOUNT)) or
        ((@MONEYFILTER = 2) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) = @FILTERAMOUNT)) or
        ((@MONEYFILTER = 3) and (dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(PLEDGE.ID, @SELECTEDCURRENCYID) between @FILTERAMOUNT and @HIGHFILTERAMOUNT))
      );

    drop table #TMP_PLEDGES_BALANCEASOF_D5;

  end

  drop table #TMP_PLEDGES_SEMIFILTERED;

  update #TMP_PLEDGES_FILTERED
  set
    #TMP_PLEDGES_FILTERED.BASECURRENCYID = case when #TMP_PLEDGES_FILTERED.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end,
    #TMP_PLEDGES_FILTERED.GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
  from
    #TMP_PLEDGES_FILTERED
    inner join dbo.REVENUE_EXT on #TMP_PLEDGES_FILTERED.ID = REVENUE_EXT.ID
    left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on #TMP_PLEDGES_FILTERED.ID = V.FINANCIALTRANSACTIONID;

  if object_id('tempdb..#TMP_PLEDGES_FILTERED_DATA') is not null
    drop table #TMP_PLEDGES_FILTERED_DATA;

  create table #TMP_PLEDGES_FILTERED_DATA (
    ID uniqueidentifier not null,
    DATE datetimeoffset not null,
    CONSTITUENTID uniqueidentifier null,
    TRANSACTIONTYPECODE tinyint not null,
    TRANSACTIONCURRENCYID uniqueidentifier null,
    GIVENANONYMOUSLY bit null,
    BASECURRENCYID uniqueidentifier null,
    INSTALLMENTID uniqueidentifier not null,
    INSTALLMENTDATE datetime not null,
    INSTALLMENTSPLITID uniqueidentifier not null,
    INSTALLMENTSPLITDESIGNATIONID uniqueidentifier not null,
    INSTALLMENTAMOUNT money not null
  );

  insert into #TMP_PLEDGES_FILTERED_DATA
  select
    PLEDGE.ID,
    PLEDGE.DATE,
    PLEDGE.CONSTITUENTID,
    PLEDGE.TRANSACTIONTYPECODE,
    PLEDGE.TRANSACTIONCURRENCYID,
    PLEDGE.GIVENANONYMOUSLY,
    PLEDGE.BASECURRENCYID,
    INSTALLMENT.ID,
    INSTALLMENT.DATE,
    INSTALLMENTSPLIT.ID,
    INSTALLMENTSPLIT.DESIGNATIONID,
    dbo.UFN_INSTALLMENTSPLIT_GETAMOUNTINCURRENCY(INSTALLMENTSPLIT.ID, @SELECTEDCURRENCYID)
  from #TMP_PLEDGES_FILTERED PLEDGE
    inner join dbo.INSTALLMENT on PLEDGE.ID = INSTALLMENT.REVENUEID
    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
  where (@DESIGNATIONID is null)
     or (@DESIGNATIONID is not null and INSTALLMENTSPLIT.DESIGNATIONID = @DESIGNATIONID);

  if object_id('tempdb..#TMP_PLEDGES_PAYMENTDATE') is not null
    drop table #TMP_PLEDGES_PAYMENTDATE;

  create table #TMP_PLEDGES_PAYMENTDATE (
    INSTALLMENTID uniqueidentifier not null,
    PAYMENTDATE datetime null
  );

  insert into #TMP_PLEDGES_PAYMENTDATE
  select 
    #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTID,
    MAX(R.DATE)
  from #TMP_PLEDGES_FILTERED_DATA
    inner join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID
    inner join dbo.REVENUESPLIT RS on RS.ID = ISP.PAYMENTID
    inner join dbo.REVENUE R on R.ID = RS.REVENUEID
  where @ENDDATETIME >= R.DATE
  group by #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTID;

  if object_id('tempdb..#TMP_PLEDGES_PAYMENTAMOUNT') is not null
    drop table #TMP_PLEDGES_PAYMENTAMOUNT;

  create table #TMP_PLEDGES_PAYMENTAMOUNT (
    INSTALLMENTSPLITID uniqueidentifier not null,
    PAYMENTAMOUNT money null
  );

  insert into #TMP_PLEDGES_PAYMENTAMOUNT
  select
    #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID,
    SUM(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID, @SELECTEDCURRENCYID))
  from #TMP_PLEDGES_FILTERED_DATA
    inner join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID
    inner join dbo.REVENUESPLIT RS on RS.ID = ISP.PAYMENTID
    inner join dbo.REVENUE R on R.ID = RS.REVENUEID
  where @ENDDATETIME >= R.DATE
  group by #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID;

  if object_id('tempdb..#TMP_PLEDGES_WRITEOFFAMOUNT') is not null
    drop table #TMP_PLEDGES_WRITEOFFAMOUNT;

  create table #TMP_PLEDGES_WRITEOFFAMOUNT (
    INSTALLMENTSPLITID uniqueidentifier not null,
    WRITEOFFAMOUNT money null
  );

  insert into #TMP_PLEDGES_WRITEOFFAMOUNT
  select
    #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID,
    SUM(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(ISWO.ID, @SELECTEDCURRENCYID))
  from #TMP_PLEDGES_FILTERED_DATA
    inner join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.INSTALLMENTSPLITID = #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID
    inner join dbo.WRITEOFF WO on WO.ID = ISWO.WRITEOFFID
  where @ENDDATETIME >= WO.DATE
  group by #TMP_PLEDGES_FILTERED_DATA.INSTALLMENTSPLITID;

  if object_id('tempdb..#TMP_PLEDGES_REMINDERSENTDATE') is not null
    drop table #TMP_PLEDGES_REMINDERSENTDATE;

  create table #TMP_PLEDGES_REMINDERSENTDATE (
    PLEDGEID uniqueidentifier not null,
    REMINDERSENTDATE datetime null
  );

  insert into #TMP_PLEDGES_REMINDERSENTDATE
  select
    #TMP_PLEDGES_FILTERED_DATA.ID,
    MAX(PLEDGEREMINDERSENT.SENTDATE) 
  from
    #TMP_PLEDGES_FILTERED_DATA
    inner join dbo.PLEDGEREMINDERSENT on PLEDGEREMINDERSENT.REVENUEID = #TMP_PLEDGES_FILTERED_DATA.ID
  group by #TMP_PLEDGES_FILTERED_DATA.ID;

  select
    PLEDGE.ID PLEDGEID,
    case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + convert(nvarchar(36),PLEDGE.ID) else null end PLEDGELINK,
    CONSTITUENT.NAME CONSTITUENTNAME,
    CONSTITUENT.ID CONSTITUENTID,
    'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36),CONSTITUENT.ID) CONSTITUENTLINK,
    PHONE.NUMBER PHONENUMBER,
    ADDRESS.DESCRIPTION ADDRESSBLOCK,
    EMAILADDRESS.EMAILADDRESS EMAILADDRESS,
    DESIGNATION.NAME DESIGNATIONNAME,
    DESIGNATION.ID DESIGNATIONID,
    case when @USERGRANTEDDESIGNATIONPAGE = 1 then 'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + convert(nvarchar(36),DESIGNATION.ID) else null end DESIGNATIONLINK,
    PLEDGE.DATE DATEPLEDGED,
    PLEDGE.INSTALLMENTDATE INSTALLMENTDUEDATES,
    dbo.UFN_INSTALLMENTSPLIT_GETAMOUNTINCURRENCY(PLEDGE.INSTALLMENTSPLITID, @SELECTEDCURRENCYID) INSTALLMENTAMOUNT,
    PLEDGE.INSTALLMENTID INSTALLMENTID,
    #TMP_PLEDGES_PAYMENTDATE.PAYMENTDATE PAYMENTDATE,
    #TMP_PLEDGES_PAYMENTAMOUNT.PAYMENTAMOUNT PAYMENTAMOUNT,
    #TMP_PLEDGES_WRITEOFFAMOUNT.WRITEOFFAMOUNT WRITEOFFAMOUNT,
    #TMP_PLEDGES_REMINDERSENTDATE.REMINDERSENTDATE REMINDERSENTDATE,
    PLEDGE.BASECURRENCYID BASECURRENCYID,
    PLEDGE.TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
    @ISOCODE PLEDGEISOCURRENCYCODE,
    @CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
    @SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
    @DECIMALDIGITS PLEDGEDECIMALDIGITS,
    case when RELATIONSHIP.ID is null then cast(0 as bit) else cast(1 as bit) end HASPRIMARYCONTACTINFO,      
    case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTNAME.KEYNAME else null end PCLASTNAME,
    case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTNAME.FIRSTNAME else null end PCFIRSTNAME,
    case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTEMAIL.EMAILADDRESS else null end PCEMAIL,
    case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTPHONE.NUMBER else null end PCPHONE,
    case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTADDRESS.DESCRIPTION else null end PCADDRESSBLOCK,
    PLEDGE.GIVENANONYMOUSLY ANONYMOUS,
    CONSTITUENT.KEYNAME LASTNAME,
    CONSTITUENT.FIRSTNAME FIRSTNAME,
    CONSTITUENT.ISORGANIZATION ISORG
  from
    #TMP_PLEDGES_FILTERED_DATA PLEDGE
    inner join dbo.DESIGNATION on DESIGNATION.ID = PLEDGE.INSTALLMENTSPLITDESIGNATIONID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = PLEDGE.CONSTITUENTID

    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
    left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
    left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1

    left join dbo.RELATIONSHIP on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISPRIMARYCONTACT = 1
    left join dbo.CONSTITUENT PRIMARYCONTACTNAME on CONSTITUENT.ISORGANIZATION = 1 and PRIMARYCONTACTNAME.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
    left join dbo.ADDRESS PRIMARYCONTACTADDRESS on CONSTITUENT.ISORGANIZATION = 1 and PRIMARYCONTACTADDRESS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and PRIMARYCONTACTADDRESS.ISPRIMARY = 1
    left join dbo.PHONE PRIMARYCONTACTPHONE on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PRIMARYCONTACTPHONE.CONSTITUENTID and PRIMARYCONTACTPHONE.ISPRIMARY = 1
    left join dbo.EMAILADDRESS PRIMARYCONTACTEMAIL on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PRIMARYCONTACTEMAIL.CONSTITUENTID and PRIMARYCONTACTEMAIL.ISPRIMARY = 1

    left join #TMP_PLEDGES_PAYMENTDATE on #TMP_PLEDGES_PAYMENTDATE.INSTALLMENTID = PLEDGE.INSTALLMENTID
    left join #TMP_PLEDGES_PAYMENTAMOUNT on #TMP_PLEDGES_PAYMENTAMOUNT.INSTALLMENTSPLITID = PLEDGE.INSTALLMENTSPLITID
    left join #TMP_PLEDGES_WRITEOFFAMOUNT on #TMP_PLEDGES_WRITEOFFAMOUNT.INSTALLMENTSPLITID = PLEDGE.INSTALLMENTSPLITID
    left join #TMP_PLEDGES_REMINDERSENTDATE on #TMP_PLEDGES_REMINDERSENTDATE.PLEDGEID = PLEDGE.ID
  where
    (   --constituent security  

      @ISADMIN = 1 or 
      @APPUSER_IN_NONRACROLE = 1 or
      dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
    );

  drop table #TMP_PLEDGES_FILTERED;
  drop table #TMP_PLEDGES_FILTERED_DATA;
  drop table #TMP_PLEDGES_PAYMENTDATE;
  drop table #TMP_PLEDGES_PAYMENTAMOUNT;
  drop table #TMP_PLEDGES_WRITEOFFAMOUNT;
  drop table #TMP_PLEDGES_REMINDERSENTDATE;