UFN_STEWARDSHIPPLAN_TOP4PURPOSES_TOITEMLISTXML

CREATE function dbo.UFN_STEWARDSHIPPLAN_TOP4PURPOSES_TOITEMLISTXML
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
) 
returns xml
as begin
    declare @PURPOSES xml;

    declare @TempTbl table (
       [ID] uniqueidentifier,
       [DESIGNATIONLEVEL] nvarchar(100),
       [TOTALGIVEN] money)

    insert into @TempTbl
        select 
            DESIGNATIONLEVEL.ID,            
            DESIGNATIONLEVEL.NAME as DESIGNATIONLEVEL,            
            SUM(REVENUESPLIT.AMOUNT) as TOTALGIVEN
        from dbo.REVENUE
            inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
            inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
            inner join dbo.DESIGNATIONLEVEL
                on (
                    DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID or
                    DESIGNATION.DESIGNATIONLEVEL2ID = DESIGNATIONLEVEL.ID or
                    DESIGNATION.DESIGNATIONLEVEL3ID = DESIGNATIONLEVEL.ID or             
                    DESIGNATION.DESIGNATIONLEVEL4ID = DESIGNATIONLEVEL.ID or
                    DESIGNATION.DESIGNATIONLEVEL5ID = DESIGNATIONLEVEL.ID)        
        where REVENUE.CONSTITUENTID = @CONSTITUENTID 
            and
            (
                (REVENUE.TRANSACTIONTYPECODE in (1, 4)) --Pledges, Planned Gifts

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

                    )
                )
            )

        Group by DESIGNATIONLEVEL.ID, DESIGNATIONLEVEL.NAME, DESIGNATIONLEVEL.USERID

    set @PURPOSES = (
        Select
            top 4(T.ID),
            T.DESIGNATIONLEVEL,
            T.TOTALGIVEN

        from @TempTbl T

        inner join dbo.DESIGNATIONLEVEL DL
            on DL.ID = T.ID

        where dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,DL.SITEID) = 1

        order by T.TOTALGIVEN desc

        for xml raw('ITEM'), type, elements, root('PURPOSES'), binary base64
    );

    return @PURPOSES;
end