USP_DATALIST_CONSTITUENTMATCHEDGIFTS

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

declare @CURRENCYID uniqueidentifier
declare @ORGTOMYBASEEXCHANGERATE uniqueidentifier = null

declare @MULTICURRENCYENABLED bit;
set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
if @MULTICURRENCYENABLED = 0 
  set @CURRENCYCODE = 1;

if @CURRENCYCODE = 1
  set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

if @CURRENCYCODE = 3
begin
  set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
  if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
  begin     
    set @CURRENCYCODE = 1
  end
end;

    with CONSTITUENTREVENUE_CTE as
    (
        select
            REVENUE.ID,
            REVENUE.TYPE TRANSACTIONTYPE,      
            REVENUE.BASEAMOUNT AMOUNT,
            REVENUE.TRANSACTIONAMOUNT,
            cast(REVENUE.DATE as datetime) as DATE,
            REVENUE.TRANSACTIONCURRENCYID,
            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
            REVENUE_EXT.GIVENANONYMOUSLY
        from 
            dbo.FINANCIALTRANSACTION REVENUE
            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
            inner join dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID            
        where
            REVENUE.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 = REVENUE.ID
                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)
                )
            )
    )

    select
        CONSTITUENTREVENUE_CTE.ID as REVENUEID,
        null as MGCLAIMID,
        'Original gift' as TYPE,
        CONSTITUENTREVENUE_CTE.DATE as DATE,
        null as MGORGID,
        null as MGORGNAME,
        case @CURRENCYCODE
            when 2 then CONSTITUENTREVENUE_CTE.TRANSACTIONAMOUNT
            when 1 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(CONSTITUENTREVENUE_CTE.ID,@CURRENCYID)
            when 3 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(CONSTITUENTREVENUE_CTE.ID,@CURRENCYID)
            else CONSTITUENTREVENUE_CTE.AMOUNT
        end as AMOUNT,
        null as MGCLAIMAMOUNT,
        null as PARENT,
        row_number() over (order by CONSTITUENTREVENUE_CTE.DATE, CONSTITUENTREVENUE_CTE.AMOUNT desc) as SEQUENCE,
        dbo.UFN_REVENUE_BUILDSITELIST(CONSTITUENTREVENUE_CTE.ID) as SITES,
        case @CURRENCYCODE
            when 2 then CONSTITUENTREVENUE_CTE.TRANSACTIONCURRENCYID
            when 0 then CONSTITUENTREVENUE_CTE.BASECURRENCYID
            else @CURRENCYID
        end as DISPLAYCURRENCY,
        case when CONSTITUENTREVENUE_CTE.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY
    from
        CONSTITUENTREVENUE_CTE
    where exists(select top(1) ID from dbo.REVENUEMATCHINGGIFT where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = CONSTITUENTREVENUE_CTE.ID)

    union all

    select
        REVENUE.ID as REVENUEID,
        REVENUE.ID as MGCLAIMID,
        REVENUE.TYPE as TYPE,
        cast(REVENUE.DATE as datetime) as DATE,
        CONSTITUENT.ID as MGORGID,
        CONSTITUENT.NAME as MGORGNAME,
        case @CURRENCYCODE
            when 2 then (select TRANSACTIONAMOUNT from dbo.REVENUE where ID=REVENUEMATCHINGGIFT.ID)
            when 1 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
            when 3 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID,@CURRENCYID)
            else REVENUE.BASEAMOUNT
        end as AMOUNT,
        case @CURRENCYCODE 
            when 2 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, REVENUE.TRANSACTIONCURRENCYID)
            when 0 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID))
            else dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, @CURRENCYID) 
        end as MGCLAIMBALANCE,        
        --dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(REVENUE.ID, CURRENCYCODE) as MGCLAIMBALANCE,

        REVENUEMATCHINGGIFT.MGSOURCEREVENUEID as PARENT,
        row_number() over (order by REVENUE.DATE, REVENUE.BASEAMOUNT desc) as SEQUENCE,
        null as SITES,
        case @CURRENCYCODE
            when 2 then REVENUE.TRANSACTIONCURRENCYID
            when 0 then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
            else @CURRENCYID
        end as DISPLAYCURRENCY,
        case when REVENUE_EXT.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY
    from
        dbo.REVENUEMATCHINGGIFT
    inner join
        CONSTITUENTREVENUE_CTE on CONSTITUENTREVENUE_CTE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
    inner join
        dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUEMATCHINGGIFT.ID
    inner join
        dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
    inner join 
        dbo.PDACCOUNTSYSTEM on REVENUE.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    inner join 
        dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID        
    inner join
        dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID