UFN_STEWARDSHIPPLAN_RECENTGIFTS2

CREATE function dbo.UFN_STEWARDSHIPPLAN_RECENTGIFTS2
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
) 
returns table
as return

select
    distinct top 4
    REVENUE.ID,
    REVENUE.TRANSACTIONAMOUNT as AMOUNT,
    REVENUE.DATE,
    REVENUE.DATEADDED,
    REVENUE.TRANSACTIONCURRENCYID,
    REVENUE.GIVENANONYMOUSLY
from dbo.REVENUE
inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.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  
                -- 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 --JamesWill 2009-02-11 Work Item 22433 Only include certain types of revenue records

        (
            (REVENUE.TRANSACTIONTYPECODE in (1, 4)) --Pledges, Planned Gifts

            or
            (REVENUE.TRANSACTIONTYPECODE= 0 and 
                (
                    REVENUESPLIT.APPLICATIONCODE in (0, 1, 3, 4, 5, 7, 8, 13) --Donations, Event registration payment, Recurring gift payment, Other, Membership payment, Matching gift payment, Grant payment, Donor challenge payment

                )
            )
        )
order by REVENUE.DATE desc, REVENUE.DATEADDED desc