USP_CONSTITUENT_CUMULATIVEGIVINGSUMMARYGET

CREATE procedure dbo.USP_CONSTITUENT_CUMULATIVEGIVINGSUMMARYGET
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  declare @TOTALNUMBER int = null;
  declare @TOTALAMOUNT money = null;
  declare @AVERAGEAMOUNT money = null;
  declare @MOSTFREQUENTAMOUNT money = null;
  declare @TOTALPLEDGEBALANCE money = null;
  declare @TOTALYEARS int = null;
  declare @CONSECUTIVEYEARS int = null;
  declare @GIVENSINCEFISCALYEAR datetime = null;
  declare @LARGESTID uniqueidentifier = null;
  declare @LARGESTRECORDID uniqueidentifier = null;
  declare @LARGESTDATE datetime = null;
  declare @LARGESTTYPECODE tinyint = null;
  declare @LARGESTTYPE nvarchar(22) = null;
  declare @LARGESTAMOUNT money = null;
  declare @LARGESTPLEDGEBALANCE money = null;
  declare @LARGESTSPLITS xml = null;
  declare @FIRSTID uniqueidentifier = null;
  declare @FIRSTRECORDID uniqueidentifier = null;
  declare @FIRSTDATE datetime = null;
  declare @FIRSTTYPECODE tinyint = null;
  declare @FIRSTTYPE nvarchar(22) = null;
  declare @FIRSTAMOUNT money = null;
  declare @FIRSTPLEDGEBALANCE money = null;
  declare @FIRSTSPLITS xml = null;
  declare @LATESTID uniqueidentifier = null;
  declare @LATESTRECORDID uniqueidentifier = null;
  declare @LATESTDATE datetime = null;
  declare @LATESTTYPECODE tinyint = null;
  declare @LATESTTYPE nvarchar(22) = null;
  declare @LATESTAMOUNT money = null;
  declare @LATESTPLEDGEBALANCE money = null;
  declare @LATESTSPLITS xml = null;
  declare @RECOGNITIONTOTALNUMBER int = null;
  declare @RECOGNITIONTOTALAMOUNT money = null;
  declare @RECOGNITIONAVERAGEAMOUNT money = null;
  declare @RECOGNITIONMOSTFREQUENTAMOUNT money = null;
  declare @RECOGNITIONTOTALYEARS int = null;
  declare @RECOGNITIONCONSECUTIVEYEARS int = null;
  declare @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null;
  declare @RECOGNITIONLARGESTID uniqueidentifier = null;
  declare @RECOGNITIONLARGESTRECORDID uniqueidentifier = null;
  declare @RECOGNITIONLARGESTDATE datetime = null;
  declare @RECOGNITIONLARGESTTYPECODE tinyint = null;
  declare @RECOGNITIONLARGESTTYPE nvarchar(22) = null;
  declare @RECOGNITIONLARGESTAMOUNT money = null;
  declare @RECOGNITIONFIRSTID uniqueidentifier = null;
  declare @RECOGNITIONFIRSTRECORDID uniqueidentifier = null;
  declare @RECOGNITIONFIRSTDATE datetime = null;
  declare @RECOGNITIONFIRSTTYPECODE tinyint = null;
  declare @RECOGNITIONFIRSTTYPE nvarchar(22) = null;
  declare @RECOGNITIONFIRSTAMOUNT money = null;
  declare @RECOGNITIONLATESTID uniqueidentifier = null;
  declare @RECOGNITIONLATESTRECORDID uniqueidentifier = null;
  declare @RECOGNITIONLATESTDATE datetime = null;
  declare @RECOGNITIONLATESTTYPECODE tinyint = null;
  declare @RECOGNITIONLATESTTYPE nvarchar(22) = null;
  declare @RECOGNITIONLATESTAMOUNT money = null;
  declare @HOUSEHOLDID uniqueidentifier = null;
  declare @TOTALHOUSEHOLDGIVING money = null;
  declare @TOTALGIVINGWITHGIFTAID money = null;
  declare @TOTALRECOGNITIONWITHGIFTAID money = null;
  declare @MATCHEDGIFTSAMOUNT money = null;

  declare @CURRENCYID uniqueidentifier
  if @CURRENCYCODE = 1
    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  else
    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);    

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @ORIGINCODE tinyint;

  select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

  select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
      @DECIMALDIGITS = DECIMALDIGITS,
      @ROUNDINGTYPECODE = ROUNDINGTYPECODE
  from 
    dbo.CURRENCY
  where
    CURRENCY.ID = @CURRENCYID;

  exec dbo.USP_CONSTITUENT_GIVINGSUMMARYGET
    @CONSTITUENTID,
    @TOTALNUMBER = @TOTALNUMBER output,
    @TOTALAMOUNT = @TOTALAMOUNT output,
    @AVERAGEAMOUNT = @AVERAGEAMOUNT output,
    @MOSTFREQUENTAMOUNT = @MOSTFREQUENTAMOUNT output,
    @TOTALPLEDGEBALANCE = @TOTALPLEDGEBALANCE output,
    @TOTALYEARS = @TOTALYEARS output,
    @CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,
    @GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output,
    @HOUSEHOLDID = @HOUSEHOLDID output,
    @TOTALHOUSEHOLDGIVING = @TOTALHOUSEHOLDGIVING output,
    @CURRENTAPPUSERID = @CURRENTAPPUSERID,
    @SITEFILTERMODE = @SITEFILTERMODE, 
    @SITESSELECTED = @SITESSELECTED,
    @SECURITYFEATUREID = @SECURITYFEATUREID,
    @SECURITYFEATURETYPE = @SECURITYFEATURETYPE,
    @TOTALGIVINGWITHGIFTAID = @TOTALGIVINGWITHGIFTAID output,
    @MATCHEDGIFTSAMOUNT = @MATCHEDGIFTSAMOUNT output,
    @CURRENCYID = @CURRENCYID;

  declare @ALLREVENUE table
  (
    REVENUEID uniqueidentifier,
    TRANSACTIONTYPECODE tinyint,
    TRANSACTIONTYPE nvarchar(21),
    REVENUEAMOUNT money,
    [DATE] datetime,
    DATEADDED datetime,
    SPLITID uniqueidentifier,
    APPLICATIONCODE tinyint,
    DESIGNATIONID uniqueidentifier,
    SPLITAMOUNT money,
    WRITEOFFAMOUNT money,
    SPLITNETAMOUNT money
  )    

  insert @ALLREVENUE
  select * 
  from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
    @CONSTITUENTID,
    @CURRENTAPPUSERID,
    @SITEFILTERMODE,
    @SITESSELECTED,
    @SECURITYFEATUREID,
    @SECURITYFEATURETYPE,
    @CURRENCYID,
    @ORGANIZATIONCURRENCYID,
    @DECIMALDIGITS,
    @ROUNDINGTYPECODE);

  --LARGEST GIFT

  select top 1
    @LARGESTID = ALLREVENUE.REVENUEID,
    @LARGESTRECORDID = ALLREVENUE.REVENUEID,
    @LARGESTDATE = ALLREVENUE.DATE,
    @LARGESTTYPECODE = ALLREVENUE.TRANSACTIONTYPECODE,
    @LARGESTTYPE = ALLREVENUE.TRANSACTIONTYPE,
    @LARGESTAMOUNT = sum(ALLREVENUE.SPLITNETAMOUNT),
    @LARGESTPLEDGEBALANCE = case ALLREVENUE.TRANSACTIONTYPECODE when 1 then coalesce(PLEDGEBALANCE.BALANCEINCURRENCY,0) else null end,
    --using this instead of TOXML function, because a different root element is needed

    @LARGESTSPLITS = (select
        [AMOUNT],
        [ID],
        [PURPOSE]
      from
        dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](ALLREVENUE.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
      order by
        ID
      for xml raw('ITEM'),type,elements,root('LARGESTSPLITS'),BINARY BASE64)
  from
    @ALLREVENUE ALLREVENUE
  left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = ALLREVENUE.REVENUEID
  group by
    ALLREVENUE.REVENUEID, ALLREVENUE.DATE, ALLREVENUE.TRANSACTIONTYPECODE, ALLREVENUE.TRANSACTIONTYPE, ALLREVENUE.DATEADDED, PLEDGEBALANCE.BALANCEINCURRENCY
  order by
    sum(ALLREVENUE.SPLITNETAMOUNT) desc, ALLREVENUE.DATE desc, ALLREVENUE.DATEADDED desc;

  --FIRST GIFT

  select top 1
    @FIRSTID = ALLREVENUE.REVENUEID,
    @FIRSTRECORDID = ALLREVENUE.REVENUEID,
    @FIRSTDATE = ALLREVENUE.DATE,
    @FIRSTTYPECODE = ALLREVENUE.TRANSACTIONTYPECODE,
    @FIRSTTYPE = ALLREVENUE.TRANSACTIONTYPE,
    @FIRSTAMOUNT = sum(ALLREVENUE.SPLITNETAMOUNT),
    @FIRSTPLEDGEBALANCE = case ALLREVENUE.TRANSACTIONTYPECODE when 1 then coalesce(PLEDGEBALANCE.BALANCEINCURRENCY,0) else null end,
    --using this instead of TOXML function, because a different root element is needed

    @FIRSTSPLITS = (select
        [AMOUNT],
        [ID],
        [PURPOSE]
      from
        dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](ALLREVENUE.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
      order by
        ID
      for xml raw('ITEM'),type,elements,root('FIRSTSPLITS'),BINARY BASE64)
  from
    @ALLREVENUE ALLREVENUE
  left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = ALLREVENUE.REVENUEID
  group by
    ALLREVENUE.REVENUEID, ALLREVENUE.DATE, ALLREVENUE.TRANSACTIONTYPECODE, ALLREVENUE.TRANSACTIONTYPE, ALLREVENUE.DATEADDED, PLEDGEBALANCE.BALANCEINCURRENCY
  order by
    ALLREVENUE.DATE asc, ALLREVENUE.DATEADDED asc;

  --LATEST GIFT

  select top 1
    @LATESTID = ALLREVENUE.REVENUEID,
    @LATESTRECORDID = ALLREVENUE.REVENUEID,
    @LATESTDATE = ALLREVENUE.DATE,
    @LATESTTYPECODE = ALLREVENUE.TRANSACTIONTYPECODE,
    @LATESTTYPE = ALLREVENUE.TRANSACTIONTYPE,
    @LATESTAMOUNT = sum(ALLREVENUE.SPLITNETAMOUNT),
    @LATESTPLEDGEBALANCE = case ALLREVENUE.TRANSACTIONTYPECODE when 1 then coalesce(PLEDGEBALANCE.BALANCEINCURRENCY, 0) else null end,
    --using this instead of TOXML function, because a different root element is needed

    @LATESTSPLITS = (select
        [AMOUNT],
        [ID],
        [PURPOSE]
      from
        dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](ALLREVENUE.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
      order by
        ID
      for xml raw('ITEM'),type,elements,root('LATESTSPLITS'),BINARY BASE64)
  from
    @ALLREVENUE ALLREVENUE
  left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = ALLREVENUE.REVENUEID
  group by
    ALLREVENUE.REVENUEID, ALLREVENUE.DATE, ALLREVENUE.TRANSACTIONTYPECODE, ALLREVENUE.TRANSACTIONTYPE, ALLREVENUE.DATEADDED, PLEDGEBALANCE.BALANCEINCURRENCY
  order by
    ALLREVENUE.DATE desc, ALLREVENUE.DATEADDED desc;

  -- Recognition credits

  exec dbo.USP_CONSTITUENT_RECOGNITIONSUMMARYGET
  @CONSTITUENTID,
  @RECOGNITIONTOTALNUMBER output,
  @RECOGNITIONTOTALAMOUNT output,
  @RECOGNITIONAVERAGEAMOUNT output,
  @RECOGNITIONMOSTFREQUENTAMOUNT output,
  @RECOGNITIONTOTALYEARS output,
  @RECOGNITIONCONSECUTIVEYEARS output,
  @RECOGNITIONGIVENSINCEFISCALYEAR output,
  @CURRENTAPPUSERID,
  @SITEFILTERMODE, 
  @SITESSELECTED,
  @SECURITYFEATUREID,
  @SECURITYFEATURETYPE,
  @TOTALRECOGNITIONWITHGIFTAID output,
  @CURRENCYID = @CURRENCYID;

  --LARGEST GIFT

  select top 1
    @RECOGNITIONLARGESTID = RR.ID,
    @RECOGNITIONLARGESTRECORDID = R.ID,
    @RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
    @RECOGNITIONLARGESTTYPECODE = R.TRANSACTIONTYPECODE,
    @RECOGNITIONLARGESTTYPE = R.TRANSACTIONTYPE,
    @RECOGNITIONLARGESTAMOUNT = RR.AMOUNTINCURRENCY
  from
    (
        select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID, RR.DATEADDED
        from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR

        union all

        select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID, RC.DATEADDED
        from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RC
    ) RR
  inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
  inner join dbo.REVENUE R on RS.REVENUEID = R.ID
  left join
    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
  left join
    dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
  where
    (R.TRANSACTIONTYPECODE = 1 or --Pledge

      R.TRANSACTIONTYPECODE = 7 or --Auction donation

      R.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

    R.TRANSACTIONTYPECODE = 3 or --Matching gift claim

    (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

    (R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations on orders

    (R.TRANSACTIONTYPECODE = 0 and (RS.APPLICATIONCODE in (0, 3, 7) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)))) and --Payment (Gift or Recurring gift payment)

    RR.CONSTITUENTID = @CONSTITUENTID
    and    exists 
    (
      select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
      cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
      where RSSUB.REVENUEID = R.ID
      /*next line is #SITEEXTENTION code*/
      and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
      and 
      (
        @SITEFILTERMODE = 0
        or 
        exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
      )
    )                    
  order by
    RR.AMOUNTINCURRENCY desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

  --FIRST GIFT

  select top 1
    @RECOGNITIONFIRSTID = RR.ID,
    @RECOGNITIONFIRSTRECORDID = R.ID,
    @RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
    @RECOGNITIONFIRSTTYPECODE = R.TRANSACTIONTYPECODE,
    @RECOGNITIONFIRSTTYPE = R.TRANSACTIONTYPE,
    @RECOGNITIONFIRSTAMOUNT = RR.AMOUNTINCURRENCY
  from
    (
        select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID, RR.DATEADDED
        from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR

        union all

        select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID, RC.DATEADDED
        from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RC
    ) RR
  inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
  inner join dbo.REVENUE R on RS.REVENUEID = R.ID
  left join
    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
  left join
    dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
  where
    (R.TRANSACTIONTYPECODE = 1 or --Pledge

      R.TRANSACTIONTYPECODE = 7 or -- Auction donation

      R.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

    R.TRANSACTIONTYPECODE = 3 or --Matching gift claim

    (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

    (R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations on orders

    (R.TRANSACTIONTYPECODE = 0 and (RS.APPLICATIONCODE in (0, 3, 7) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)))) and --Payment (Gift or Recurring gift payment)

    RR.CONSTITUENTID = @CONSTITUENTID
    and    exists 
    (
      select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
      cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
      where RSSUB.REVENUEID = R.ID
      /*next line is #SITEEXTENTION code*/
      and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
      and 
      (
        @SITEFILTERMODE = 0
        or 
        exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
      )
    )                    
  order by
    RR.EFFECTIVEDATE asc, RR.DATEADDED asc;

  --LATEST GIFT

  select top 1
    @RECOGNITIONLATESTID = RR.ID,
    @RECOGNITIONLATESTRECORDID = RR.REVENUEID,
    @RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
    @RECOGNITIONLATESTTYPECODE = RR.TRANSACTIONTYPECODE,
    @RECOGNITIONLATESTTYPE = RR.TRANSACTIONTYPE,
    @RECOGNITIONLATESTAMOUNT = RR.AMOUNTINCURRENCY
  from
    (
        select RR.ID, RR.CONSTITUENTID, RR.AMOUNTINCURRENCY, RR.EFFECTIVEDATE, RR.APPLICATIONCODE, RR.TRANSACTIONTYPECODE, RR.REVENUECONSTITUENTID, RR.DATE, RR.REVENUEID, RR.REVENUESPLITID, RR.DATEADDED, RR.TRANSACTIONTYPE, RR.REVENUESPLITTYPECODE
        from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR

        union all

        select RC.ID, RC.CONSTITUENTID, RC.AMOUNTINCURRENCY, RC.EFFECTIVEDATE, RC.APPLICATIONCODE, RC.TRANSACTIONTYPECODE, RC.REVENUECONSTITUENTID, RC.DATE, RC.REVENUEID, RC.REVENUESPLITID, RC.DATEADDED, RC.TRANSACTIONTYPE, RC.REVENUESPLITTYPECODE
        from dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RC
    ) RR
  left join
    dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = RR.REVENUEID
  left join
    dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
  where
    (RR.TRANSACTIONTYPECODE = 1 or --Pledge

    RR.TRANSACTIONTYPECODE = 7 or --Auction donation

    RR.TRANSACTIONTYPECODE = 8 or --Donor challenge claim

    RR.TRANSACTIONTYPECODE = 3 or --Matching gift claim

    (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9)) or --Planned gift

    (RR.TRANSACTIONTYPECODE = 5 and RR.APPLICATIONCODE = 0) or -- Donations on orders

    (RR.TRANSACTIONTYPECODE = 0 and (RR.APPLICATIONCODE in (0, 3, 7) or (RR.APPLICATIONCODE = 1 and RR.REVENUESPLITTYPECODE = 0)))) and --Payment (Gift or Recurring gift payment)

    RR.CONSTITUENTID = @CONSTITUENTID
    and    exists 
    (
      select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
      cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
      where RSSUB.REVENUEID = RR.REVENUEID
      /*next line is #SITEEXTENTION code*/
      and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
      and 
      (
        @SITEFILTERMODE = 0
        or 
        exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
      )
    )                    
  order by
    RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

  select
    @TOTALNUMBER as TOTALNUMBER,
    @TOTALAMOUNT as TOTALAMOUNT,
    @AVERAGEAMOUNT as AVERAGEAMOUNT,
    @MOSTFREQUENTAMOUNT as MOSTFREQUENTAMOUNT,
    @TOTALPLEDGEBALANCE as TOTALPLEDGEBALANCE,
    @TOTALYEARS as TOTALYEARS,
    @CONSECUTIVEYEARS as CONSECUTIVEYEARS,
    @GIVENSINCEFISCALYEAR as GIVENSINCEFISCALYEAR,
    @LARGESTID as LARGESTID,
    @LARGESTRECORDID as LARGESTRECORDID,
    @LARGESTDATE as LARGESTDATE,
    @LARGESTTYPECODE as LARGESTTYPECODE,
    @LARGESTTYPE as LARGESTTYPE,
    @LARGESTAMOUNT as LARGESTAMOUNT,
    @LARGESTPLEDGEBALANCE as LARGESTPLEDGEBALANCE,
    @LARGESTSPLITS as LARGESTSPLITS,
    @FIRSTID as FIRSTID,
    @FIRSTRECORDID as FIRSTRECORDID,
    @FIRSTDATE as FIRSTDATE,
    @FIRSTTYPECODE as FIRSTTYPECODE,
    @FIRSTTYPE as FIRSTTYPE,
    @FIRSTAMOUNT as FIRSTAMOUNT,
    @FIRSTPLEDGEBALANCE as FIRSTPLEDGEBALANCE,
    @FIRSTSPLITS as FIRSTSPLITS,
    @LATESTID as LATESTID,
    @LATESTRECORDID as LATESTRECORDID,
    @LATESTDATE as LATESTDATE,
    @LATESTTYPECODE as LATESTTYPECODE,
    @LATESTTYPE as LATESTTYPE,
    @LATESTAMOUNT as LATESTAMOUNT,
    @LATESTPLEDGEBALANCE as LATESTPLEDGEBALANCE,
    @LATESTSPLITS as LATESTSPLITS,
    @RECOGNITIONTOTALNUMBER as RECOGNITIONTOTALNUMBER,
    @RECOGNITIONTOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
    @RECOGNITIONAVERAGEAMOUNT as RECOGNITIONAVERAGEAMOUNT,
    @RECOGNITIONMOSTFREQUENTAMOUNT as RECOGNITIONMOSTFREQUENTAMOUNT,
    @RECOGNITIONTOTALYEARS as RECOGNITIONTOTALYEARS,
    @RECOGNITIONCONSECUTIVEYEARS as RECOGNITIONCONSECUTIVEYEARS,
    @RECOGNITIONGIVENSINCEFISCALYEAR as RECOGNITIONGIVENSINCEFISCALYEAR,
    @RECOGNITIONLARGESTID as RECOGNITIONLARGESTID,
    @RECOGNITIONLARGESTRECORDID as RECOGNITIONLARGESTRECORDID,
    @RECOGNITIONLARGESTDATE as RECOGNITIONLARGESTDATE,
    @RECOGNITIONLARGESTTYPECODE as RECOGNITIONLARGESTTYPECODE,
    @RECOGNITIONLARGESTTYPE as RECOGNITIONLARGESTTYPE,
    @RECOGNITIONLARGESTAMOUNT as RECOGNITIONLARGESTAMOUNT,
    @RECOGNITIONFIRSTID as RECOGNITIONFIRSTID,
    @RECOGNITIONFIRSTRECORDID as RECOGNITIONFIRSTRECORDID,
    @RECOGNITIONFIRSTDATE as RECOGNITIONFIRSTDATE,
    @RECOGNITIONFIRSTTYPECODE as RECOGNITIONFIRSTTYPECODE,
    @RECOGNITIONFIRSTTYPE as RECOGNITIONFIRSTTYPE,
    @RECOGNITIONFIRSTAMOUNT as RECOGNITIONFIRSTAMOUNT,
    @RECOGNITIONLATESTID as RECOGNITIONLATESTID,
    @RECOGNITIONLATESTRECORDID as RECOGNITIONLATESTRECORDID,
    @RECOGNITIONLATESTDATE as RECOGNITIONLATESTDATE,
    @RECOGNITIONLATESTTYPECODE as RECOGNITIONLATESTTYPECODE,
    @RECOGNITIONLATESTTYPE as RECOGNITIONLATESTTYPE,
    @RECOGNITIONLATESTAMOUNT as RECOGNITIONLATESTAMOUNT,
    @HOUSEHOLDID as HOUSEHOLDID,
    @TOTALHOUSEHOLDGIVING as TOTALHOUSEHOLDGIVING,
    @TOTALGIVINGWITHGIFTAID as TOTALGIVINGWITHGIFTAID,
    @TOTALRECOGNITIONWITHGIFTAID as TOTALRECOGNITIONWITHGIFTAID,
    @MATCHEDGIFTSAMOUNT as MATCHEDGIFTSAMOUNT,
    CURRENCY.ISO4217,
    CURRENCY.DECIMALDIGITS,
    CURRENCY.CURRENCYSYMBOL,
    CURRENCY.SYMBOLDISPLAYSETTINGCODE
  from
  -- Have a placeholder row so we can left join to CURRENCY and still have all other values returned

  -- if @CURRENCYID is null

  (
    select 1 as PLACEHOLDERVALUE
  ) as PLACEHOLDERROW
  left join dbo.CURRENCY on ID = @CURRENCYID;