UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY

CREATE function [dbo].[UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY]
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CURRENCYID uniqueidentifier = null
)
returns table
as
return(
    with CONSTITUENTREVENUE_CTE as
    (
        select
            CONSTITUENTID,
            ID,
            TRANSACTIONTYPECODE,
            TRANSACTIONTYPE,
            dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(ID, @CURRENCYID) as AMOUNT,
            DATE,
            DATEADDED
        from dbo.REVENUE
        where
            CONSTITUENTID = @CONSTITUENTID
    ), REVHISTORY_CTE as
    (
        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT as REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            RS.ID SPLITID,
            RS.APPLICATIONCODE,
            RS.DESIGNATIONID,
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID, @CURRENCYID) as SPLITAMOUNT,
            dbo.UFN_PLEDGESPLIT_GETNETAMOUNTINCURRENCY(RS.ID, @CURRENCYID) as SPLITNETAMOUNT
        from
            CONSTITUENTREVENUE_CTE R 
        inner join
            dbo.REVENUESPLIT RS 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 = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,11,12,13))) --Planned gifts other than lead trusts and bequests

    union all
        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT as REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            RS.ID SPLITID,
            RS.APPLICATIONCODE,
            RS.DESIGNATIONID,
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID, @CURRENCYID) as SPLITAMOUNT,
            dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID, @CURRENCYID) as SPLITNETAMOUNT
        from
            CONSTITUENTREVENUE_CTE R 
        inner join
            dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
        left join 
            dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
        left join
            dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on ISP.PLEDGEID = PGR.REVENUEID
        left join
            dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
            ((R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations made on orders

            (R.TRANSACTIONTYPECODE = 0 and -- payments made on...

                (
                RS.APPLICATIONCODE in (0, 3, 7, 13, 12) -- ...donations, recurring gifts, matching gifts, donor challenge, auction purchase...

                 or
                (RS.APPLICATIONCODE = 6 and PG.VEHICLECODE in (3,4,10)) -- ...lead trusts and bequests

                 or
                (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0) -- gift revenue applied to event registration

                )
            ))
    )
    select 
        REVENUEID,
        TRANSACTIONTYPECODE,
        TRANSACTIONTYPE,
        REVENUEAMOUNT,
        [DATE],
        DATEADDED,
        SPLITID,
        APPLICATIONCODE,
        DESIGNATIONID,
        SPLITAMOUNT,
        (SPLITAMOUNT - SPLITNETAMOUNT) as WRITEOFFAMOUNT,
        SPLITNETAMOUNT
    from 
        REVHISTORY_CTE RH
    where 
        exists 
        (
            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
            where RSSUB.REVENUEID = RH.REVENUEID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and (case 
                    when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                    when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                    else 0 
                end) = 1
            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)
            )
        )
)