USP_DATAFORMTEMPLATE_PRELOAD_OPPORTUNITYREVENUE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_OPPORTUNITYREVENUE
(
    @OPPORTUNITYID uniqueidentifier,
    @REVENUE xml = null output
)
as
begin

    declare @CONSTITUENTID uniqueidentifier;
    select
        @CONSTITUENTID = PROSPECTPLAN.PROSPECTID
    from
        dbo.OPPORTUNITY
    inner join
        dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
    where
        OPPORTUNITY.ID = @OPPORTUNITYID;

    -- In order to use aggregators, unions, and [for xml] at the same time, the select list feeding the for must be wrapped in a CTE or sub-select.

    -- Sorry for the ugliness.

    with REVENUE_CTE as
    (
        select
            REVENUE.ID [RECORDID],
            REVENUE.DATE [DATE],
            REVENUE.TRANSACTIONTYPE [APPLICATION],
            sum(REVENUESPLIT.AMOUNT) [AMOUNT],
            dbo.UDA_BUILDLIST(dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID)) [DESIGNATIONS]
        from
            dbo.REVENUE
        inner join
            dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
        where
            REVENUE.CONSTITUENTID = @CONSTITUENTID
        and
            REVENUE.TRANSACTIONTYPECODE = 1
        and
            not exists(select top(1) 1 from dbo.REVENUEOPPORTUNITY where REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID)
        group by
            REVENUE.ID, REVENUE.DATE, REVENUE.TRANSACTIONTYPE

        union all

        select
            REVENUESPLIT.ID,
            REVENUE.DATE,
            REVENUESPLIT.TYPE,
            REVENUESPLIT.AMOUNT,
            dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID)
        from
            dbo.REVENUE
        inner join
            dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
        where
            REVENUE.CONSTITUENTID = @CONSTITUENTID
        and
            (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 0)
        and
            not exists(select top(1) 1 from dbo.REVENUEOPPORTUNITY where REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID)
    )
    select 
        @REVENUE = (
            select 
                RECORDID,
                DATE,
                APPLICATION,
                AMOUNT,
                DESIGNATIONS
            from 
                REVENUE_CTE 
            order by
                DATE desc            
            for xml raw('ITEM'), type, elements, root('REVENUE'), binary base64
        );

    return 0;
end