UFN_CONSTITUENTUNREALIZEDREVENUE_BYACCOUNTSYSTEM

CREATE function [dbo].[UFN_CONSTITUENTUNREALIZEDREVENUE_BYACCOUNTSYSTEM]
(
    @CONSTITUENTID uniqueidentifier = null,
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @LOADALL bit = 0,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
returns @R table
(
    REVENUEID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    CONSTITUENTNAME nvarchar(255),
    APPLIED money,
    BALANCE money,
    AMOUNTDUE money,
    DATEDUE datetime,
    REVENUETYPECODE tinyint,
    DESCRIPTION nvarchar(255),
    SORTORDER int,
    DESIGNATIONLIST nvarchar(500),
    SPONSORSHIPOPPORTUNITY nvarchar(255),
    APPLICATIONCURRENCYID uniqueidentifier,
    PDACCOUNTSYSTEMID uniqueidentifier,
    SPONSORSHIPRGADDITIONALGIFT bit,
  MEMBERSHIPPROGRAMNAME nvarchar(255),
  MEMBERSHIPLEVELNAME nvarchar(255)
)
as
begin     

    declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());     

    declare @APPUSERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

    declare @HOUSEHOLDSCANBEDONORS bit = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();  

    declare @HOUSEHOLDID uniqueidentifier;  
    select top(1) @HOUSEHOLDID = GROUPMEMBER.GROUPID  
    from dbo.GROUPMEMBER  
        left outer join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID  
    where GROUPMEMBER.MEMBERID = @CONSTITUENTID  
        and GROUPDATA.GROUPTYPECODE = 0  
        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1  
        and @HOUSEHOLDSCANBEDONORS = 1; 

    /* 
    * The site security check was removed from the initial select because it 
    * considerably slowed down the query for databases with large numbers of  
    * revenue records. Given that only a small number of records should be 
    * returned for a given constituent/household's unrealized revenue, it was 
    * quicker to insert all the records into a table variable, then remove any 
    * ones the user did not have site access to.
    */ 
    declare @REVENUECOMMITMENTS table(
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        CONSTITUENTNAME nvarchar(255),
        APPLIED money,
        BALANCE money,
        AMOUNTDUE money,
        DATEDUE datetime,
        REVENUETYPECODE tinyint,
        DESCRIPTION nvarchar(255),
        SORTORDER int,
        DESIGNATIONLIST nvarchar(500),
        SPONSORSHIPOPPORTUNITY nvarchar(255),
        APPLICATIONCURRENCYID uniqueidentifier,
        PDACCOUNTSYSTEMID uniqueidentifier,
        SPONSORSHIPRGADDITIONALGIFT bit,
    MEMBERSHIPPROGRAMNAME nvarchar(255),
    MEMBERSHIPLEVELNAME nvarchar(255)
    );

    declare @VALIDCONSTITUENT table
    (
        ID uniqueidentifier
    );

    insert into @VALIDCONSTITUENT(ID)  
    (  
        select   
            @HOUSEHOLDID as ID  

        union  

        select   
            case when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 and @LOADALL = 0 then   
                (case when @HOUSEHOLDSCANBEDONORS = 1 then @CONSTITUENTID else null end)  
            else   
                @CONSTITUENTID  
            end  

        union  

        select   
            GROUPMEMBER.MEMBERID  
        from   
            dbo.GROUPMEMBER  
            left outer join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID  
        where   
        (   
            GROUPMEMBER.GROUPID = @HOUSEHOLDID  
            or GROUPMEMBER.GROUPID = @CONSTITUENTID  
        )  
        and (  
            (GROUPMEMBERDATERANGE.DATEFROM is null and (GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE))  
            or (GROUPMEMBERDATERANGE.DATETO is null and (GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE))   
            or (GROUPMEMBERDATERANGE.DATEFROM <= @CURRENTDATE and GROUPMEMBERDATERANGE.DATETO > @CURRENTDATE)  
        )  
    )

    insert into @REVENUECOMMITMENTS
    select
        REVENUE.ID,
        CONSTITUENT.ID,
        case
            when MGREVENUE.ID is not null then
                case REVENUE.TYPECODE
                    when 3 then
                        CONSTITUENT.NAME + ' - ' + MGCONSTIT.NAME + ' (' + MGCONSTIT.LOOKUPID + ')'
                    else
                        CONSTITUENT.NAME + ' - ' + MGCONSTIT.NAME
                    end
            else
                CONSTITUENT.NAME
        end as CONSTITUENTNAME,
        0,
        V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE,
        INSTALLMENTBALANCE.BALANCE,
        NEXTINSTALLMENT.INSTALLMENTDATE,
        case REVENUE.TYPECODE 
            when 6 then 9 
            when 8 then 10 
            else REVENUE.TYPECODE 
        end,  -- handle grant awards differently to avoid type collision with event registrations

        '',
        case 
            when CONSTITUENT.ID = @CONSTITUENTID 
                then 1 
            else 0 
        end as SORTORDER,
        null as DESIGNATIONLIST,
        null as SPONSORSHIPOPPORTUNITY,
        REVENUE.TRANSACTIONCURRENCYID,
        REVENUE.PDACCOUNTSYSTEMID,
        0,
    null as MEMBERSHIPPROGRAMNAME,
    null as MEMBERSHIPLEVELNAME
    from 
        dbo.FINANCIALTRANSACTION REVENUE
        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REVENUE.CONSTITUENTID
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
        inner join dbo.UFN_REVENUE_GETNEXTINSTALLMENT_BULK() as NEXTINSTALLMENT on NEXTINSTALLMENT.REVENUEID = REVENUE.ID
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        left join dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
        left join dbo.FINANCIALTRANSACTION MGREVENUE on MGREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
        left join dbo.CONSTITUENT MGCONSTIT on MGCONSTIT.ID = MGREVENUE.CONSTITUENTID
        left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
        left join dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() as INSTALLMENTBALANCE on INSTALLMENTBALANCE.ID = NEXTINSTALLMENT.INSTALLMENTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MGCONSTIT.ID) NF
    where
        case 
            when V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0 
                then 1 
            else 0 
        end = 1
        and REVENUESCHEDULE.ISPENDING = 0 --Isn't Pending

        and( REVENUEMATCHINGGIFT.ISACTIVE = 1 
            or REVENUE.TYPECODE <> 3
        )
        and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) 
        and REVENUE.DELETEDON is null
        and NEXTINSTALLMENT.INSTALLMENTID is not null
        and 
        (
            REVENUE.TYPECODE in (2,3,8) --account system not valid for RG, MGC, and donor challenge claim.

            or REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or @PDACCOUNTSYSTEMID is null
        )

    union all  

    select
        REVENUE.ID,
        CONSTITUENT.ID,
        CONSTITUENT.NAME,
        0,
        REVENUE.TRANSACTIONAMOUNT,
        NEXTINSTALLMENT.BALANCE,
        NEXTINSTALLMENT.DATE,
        REVENUE.TYPECODE,
        '',
        case 
            when CONSTITUENT.ID = @CONSTITUENTID 
                then 1 
            else 0 
        end,
        null,  
        SPONSORSHIPINFO.SPONSORSHIPOPPORTUNITY as SPONSORSHIPOPPORTUNITY,
        REVENUE.TRANSACTIONCURRENCYID,
        null as PDACCOUNTSYSTEMID,
        case 
            when exists(
                    select 1
                    from dbo.FINANCIALTRANSACTIONLINEITEM 
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
                        and REVENUESPLIT_EXT.TYPECODE = 17
                ) 
                then 1 
            else 0 
        end,
    MEMBERSHIPINFO.PROGRAMNAME as MEMBERSHIPPROGRAMNAME,
        MEMBERSHIPINFO.LEVELNAME as MEMBERSHIPLEVELNAME
    from 
        dbo.FINANCIALTRANSACTION REVENUE
    inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REVENUE.CONSTITUENTID
    inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
    left outer join(
            select
                SP.REVENUEID,
                coalesce(
                    (
                        select NAME 
                        from SPONSORSHIPOPPORTUNITYCHILD 
                        where ID=S.SPONSORSHIPOPPORTUNITYID
                    ),  
                    (
                        select NAME 
                        from SPONSORSHIPOPPORTUNITYPROJECT 
                        where ID = S.SPONSORSHIPOPPORTUNITYID
                    )
                ) SPONSORSHIPOPPORTUNITY  
            from dbo.REVENUESPLIT SP
                inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID  
                inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID  
            where S.ISMOSTRECENTFORCOMMITMENT = 1  
        ) SPONSORSHIPINFO on SPONSORSHIPINFO.REVENUEID = REVENUE.ID
    left outer join(
            select
                MS.REVENUEID,
                MP.NAME as [PROGRAMNAME],
                ML.NAME as [LEVELNAME]
            from
                dbo.REVENUESPLIT MS
                inner join MEMBERSHIPTRANSACTION MT on MT.REVENUESPLITID = MS.ID
                inner join MEMBERSHIP M on M.ID = MT.MEMBERSHIPID
                inner join MEMBERSHIPPROGRAM MP on MP.ID = M.MEMBERSHIPPROGRAMID
                inner join MEMBERSHIPLEVEL ML on ML.ID = M.MEMBERSHIPLEVELID
        ) MEMBERSHIPINFO on MEMBERSHIPINFO.REVENUEID = REVENUE.ID
    outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null) NEXTINSTALLMENT
    where 
        REVENUE.TYPECODE = 2    --Recurring Gift  

        and REVENUESCHEDULE.STATUSCODE in (0,5)  --Active & Lapsed

        and REVENUESCHEDULE.ISPENDING = 0  --Isn't pending  

        and REVENUE.BASEAMOUNT > 0     --Has Base Value

        and REVENUE.DELETEDON is null


    if @APPUSERISSYSADMIN = 0
    begin
    -- Remove records that the user does not have site access to.

        delete from @REVENUECOMMITMENTS
        where not exists (
                select 1 
                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUEID) as SITE  
                where exists (
                    select 1 
                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                    where SITEID=[SITE].[SITEID] 
                        or (SITEID is null 
                            and [SITE].[SITEID] is null
                        )
                )
            );
    end

    -- Set DESIGNATIONLIST separately from the insert to get a better query plan.

    update REVENUECOMMITMENTS
    set
        DESIGNATIONLIST = (
            select
                left(dbo.UDA_BUILDLIST(DESIGNATION.USERID), 500) as DESIGNATIONLIST
            from dbo.REVENUESPLIT
                inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
            where REVENUESPLIT.REVENUEID = REVENUECOMMITMENTS.REVENUEID
        )
    from 
        @REVENUECOMMITMENTS REVENUECOMMITMENTS;

    insert into @REVENUECOMMITMENTS
    select  
        REGISTRANT.ID,  
        CONSTITUENT.ID,  
        CONSTITUENT.NAME,  
        0,  
        REGISTRANTBALANCE.BALANCE,  
        REGISTRANTBALANCE.BALANCE,  
        [EVENT].STARTDATE, --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date  

        6, --Event Registration  

        [EVENT].NAME,  
        case 
            when CONSTITUENT.ID = @CONSTITUENTID 
                then 1 
            else 0 
        end,  
        '' as DESIGNATIONLIST,  
        null as SPONSORSHIPOPPORTUNITY,
        EVENT.BASECURRENCYID,
        null  as PDACCOUNTSYSTEMID,
        0,
    null as MEMBERSHIPPROGRAMNAME,
    null as MEMBERSHIPLEVELNAME
    from 
        dbo.REGISTRANT  
        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REGISTRANT.CONSTITUENTID  
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID  
        inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID  
        left join V_QUERY_EVENTREGISTRANT_BALANCE REGISTRANTBALANCE on REGISTRANTBALANCE.REGISTRANTID = REGISTRANT.ID
    where 
        REGISTRANTBALANCE.BALANCE > 0  
        and (--Check site security  

            @APPUSERISSYSADMIN = 1
            or exists (
                select 1  
                from dbo.UFN_SITEID_MAPFROM_EVENTID([EVENT].ID) as SITE  
                where exists (
                    select 1 
                    from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) 
                    where SITEID=[SITE].[SITEID] 
                        or (SITEID is null 
                            and [SITE].[SITEID] is null
                        )
                )
            )
        )  

    --if @LOADALL = 0

    --begin

    --    insert into @REVENUECOMMITMENTS

    --    select  --only pull primary members, due for renewal  

    --        MEMBERSHIP.ID,  

    --        CONSTITUENT.ID,  

    --        CONSTITUENT.NAME,  

    --        0,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIP.EXPIRATIONDATE,  

    --        5, --Membership  

    --        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),  

    --        case when CONSTITUENT.ID = @CONSTITUENTID then 1 else 0 end,  

    --        '' as DESIGNATIONLIST,  

    --        null as SPONSORSHIPOPPORTUNITY,

    --        MEMBERSHIPLEVELTERM.BASECURRENCYID,

    --        null  as PDACCOUNTSYSTEMID,

    --        0

    --    from 

    --        dbo.MEMBERSHIP  

    --        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID

    --        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID  

    --        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = MEMBER.CONSTITUENTID  

    --        inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID  

    --        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID  

    --    where 

    --        MEMBER.ISPRIMARY = 1  

    --        and MEMBER.ISDROPPED = 0  

    --        and (MEMBERSHIP.ISGIFT = 0 

    --            or MEMBERSHIP.SENDRENEWALCODE <> 0

    --        )   

    --        and (

    --            @APPUSERISSYSADMIN = 1   

    --            or exists (

    --                select 1 

    --                from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)   

    --                where SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) 

    --                    or (SITEID is null 

    --                        and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null

    --                    )

    --            )

    --        )  



    --    insert into @REVENUECOMMITMENTS

    --    select  

    --        MEMBERSHIP.ID,  

    --        CONSTITUENT.ID,  

    --        CONSTITUENT.NAME,  

    --        0,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIP.EXPIRATIONDATE,  

    --        5, --Membership  

    --        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),  

    --        case 

    --            when CONSTITUENT.ID = @CONSTITUENTID 

    --                then 1 

    --            else 0 

    --        end,  

    --        '' as DESIGNATIONLIST,  

    --        null as SPONSORSHIPOPPORTUNITY,

    --        MEMBERSHIPLEVELTERM.BASECURRENCYID,

    --        null  as PDACCOUNTSYSTEMID,

    --        0

    --    from 

    --        dbo.MEMBERSHIP  

    --        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID

    --        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID  

    --        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = MEMBERSHIP.GIVENBYID  

    --        inner join dbo.CONSTITUENT on MEMBERSHIP.GIVENBYID = CONSTITUENT.ID  

    --        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID  

    --   where 

    --        MEMBERSHIP.SENDRENEWALCODE <> 1   

    --        and (

    --            @APPUSERISSYSADMIN = 1   

    --            or exists (

    --                select 1 

    --                from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)   

    --                where SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) 

    --                    or (SITEID is null 

    --                        and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null

    --                    )

    --            )

    --        )  

    --end


    --if @LOADALL <> 0

    --begin

    --    insert into @REVENUECOMMITMENTS

    --    select  --only pull primary members, due for renewal  

    --        MEMBERSHIP.ID,  

    --        CONSTITUENT.ID,  

    --        CONSTITUENT.NAME,  

    --        0,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIPLEVELTERM.AMOUNT,  

    --        MEMBERSHIP.EXPIRATIONDATE,  

    --        5, --Membership  

    --        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),  

    --        case 

    --            when CONSTITUENT.ID = @CONSTITUENTID 

    --                then 1 

    --            else 0 

    --        end,  

    --        '' as DESIGNATIONLIST,  

    --        null as SPONSORSHIPOPPORTUNITY,

    --        MEMBERSHIPLEVELTERM.BASECURRENCYID,

    --        null as PDACCOUNTSYSTEMID,

    --        0

    --    from 

    --        dbo.MEMBERSHIP  

    --        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID  

    --        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = MEMBER.CONSTITUENTID  

    --        inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID  

    --        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID  

    --    where 

    --        MEMBER.ISDROPPED = 0       

    --        and 

    --        (

    --            @APPUSERISSYSADMIN = 1   

    --            or exists (

    --                select 1 

    --                from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)   

    --                where 

    --                    SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) 

    --                    or (SITEID is null 

    --                        and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null

    --                    )

    --            )

    --        )  

    --end



    -- THIS SECTION MAY NOT BE USED, PLEASE CONSIDER FOR REMOVAL - DavidHe  

    insert into @REVENUECOMMITMENTS
    select  
        OPPORTUNITY.ID,  
        CONSTITUENT.ID,  
        CONSTITUENT.NAME,  
        0,  
        OPPORTUNITY.AMOUNT - dbo.UFN_OPPORTUNITY_GETAMOUNTPAID(OPPORTUNITY.ID),  
        null, -- amount due  

        null, -- date due  

        100, -- arbitrary application type code for opportunities, resolved in USP_REVENUE_APPLYTOREVENUESTREAMS  

        dbo.UFN_OPPORTUNITY_GETDESCRIPTION(OPPORTUNITY.ID),  
        case 
            when CONSTITUENT.ID = @CONSTITUENTID 
                then 1 
            else 0 
        end,  
        (  
            select top 1 left(dbo.UDA_BUILDLIST(DESIGNATION.USERID), 500) as DESIGNATIONLIST   
            from dbo.DESIGNATION  
                inner join dbo.OPPORTUNITYDESIGNATION on DESIGNATION.ID = OPPORTUNITYDESIGNATION.DESIGNATIONID  
            where OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID  
        ) as DESIGNATIONLIST,  
        null as SPONSORSHIPOPPORTUNITY,  
        null as APPLCATIONCURRENCYID ,
        null as PDACCOUNTSYSTEMID,
        0,
    null as MEMBERSHIPPROGRAMNAME,
    null as MEMBERSHIPLEVELNAME
    from 
        dbo.OPPORTUNITY  
        inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID  
        inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = PROSPECTPLAN.PROSPECTID  
        -- CR298118-041508 Only include opportunities which have designations associated with them so that  

        -- when we go to fill in a payment for them we have something to apply them to.  

        -- DavidHe (removed inner join and made a where exists clause to prevent multiple rows from pulling where only one should).  

        left outer join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLAN.PROSPECTID  
    where 
        exists (
            select ID 
            from dbo.OPPORTUNITYDESIGNATION 
            where OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID
        )   


    -- sort order elevates the constituent being queried for to the top  

    insert into @R
    (    
        REVENUEID,
        CONSTITUENTID,
        CONSTITUENTNAME,
        APPLIED,
        BALANCE,
        AMOUNTDUE,
        DATEDUE,
        REVENUETYPECODE,
        DESCRIPTION,
        SORTORDER,
        DESIGNATIONLIST,
        SPONSORSHIPOPPORTUNITY,
        APPLICATIONCURRENCYID,
        PDACCOUNTSYSTEMID,
        SPONSORSHIPRGADDITIONALGIFT,
    MEMBERSHIPPROGRAMNAME,
    MEMBERSHIPLEVELNAME
    )
    select
        REVENUEID,
        CONSTITUENTID,
        CONSTITUENTNAME,
        APPLIED,
        BALANCE,
        AMOUNTDUE,
        DATEDUE,
        REVENUETYPECODE,
        DESCRIPTION,
        SORTORDER,
        DESIGNATIONLIST,
        SPONSORSHIPOPPORTUNITY,
        APPLICATIONCURRENCYID,
        PDACCOUNTSYSTEMID,
        SPONSORSHIPRGADDITIONALGIFT,
    MEMBERSHIPPROGRAMNAME,
    MEMBERSHIPLEVELNAME
    from
        @REVENUECOMMITMENTS
    order by SORTORDER desc;

    return;
end