USP_DATALIST_CONSTITUENTUNREALIZEDREVENUE

        CREATE procedure dbo.USP_DATALIST_CONSTITUENTUNREALIZEDREVENUE  
        (  
         @CONSTITUENTID uniqueidentifier = null,  
         @LOADALL bit = 0,  
         @CURRENTAPPUSERID uniqueidentifier = null,  
         @SECURITYFEATUREID uniqueidentifier = null,  
         @SECURITYFEATURETYPE tinyint = null,
         @PDACCOUNTSYSTEMID uniqueidentifier = null
        )  
        as  

    set nocount on;

    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.
    */ 
    if object_id('tempdb..#CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS') is not null
        drop table #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS;

    create table #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS 
    (
        REVENUEID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        CONSTITUENTNAME nvarchar(255) collate database_default,
        APPLIED money,
        BALANCE money,
        AMOUNTDUE money,
        DATEDUE datetime,
        REVENUETYPECODE tinyint,
        DESCRIPTION nvarchar(255) collate database_default,
        SORTORDER int,
        DESIGNATIONLIST nvarchar(500) collate database_default,
        SPONSORSHIPOPPORTUNITY nvarchar(255) collate database_default,
        APPLICATIONCURRENCYID uniqueidentifier,
        PDACCOUNTSYSTEMID uniqueidentifier,
        SPONSORSHIPRGADDITIONALGIFT bit,
        MEMBERSHIPPROGRAMNAME nvarchar(255) collate database_default,
        MEMBERSHIPLEVELNAME nvarchar(255) collate database_default
    );

    if object_id('tempdb..#CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS') is not null
        drop table #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS;

    create table #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS
    (
        ID uniqueidentifier
    );

    insert into #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS(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)  
        )  
    )

    if object_id('tempdb..#CONSTITUENTUNREALIZEDREVENUE_POTENTIALCOMMITMENTS') is not null
        drop table #CONSTITUENTUNREALIZEDREVENUE_POTENTIALCOMMITMENTS;

    create table #CONSTITUENTUNREALIZEDREVENUE_POTENTIALCOMMITMENTS
    (
        ID uniqueidentifier,
        TYPECODE tinyint,
        TRANSACTIONCURRENCYID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        PDACCOUNTSYSTEMID uniqueidentifier,
        PLEDGEBALANCE money,
        INSTALLMENTBALANCE money,
        INSTALLMENTDATE datetime,
        INSTALLMENTID uniqueidentifier
    );

    --For performance reasons, use a temp table for all potential constituent revenue.


    insert into #CONSTITUENTUNREALIZEDREVENUE_POTENTIALCOMMITMENTS (ID, TYPECODE, TRANSACTIONCURRENCYID, CONSTITUENTID, PDACCOUNTSYSTEMID, PLEDGEBALANCE, INSTALLMENTBALANCE, INSTALLMENTDATE, INSTALLMENTID)
    select
        RES.ID, RES.TYPECODE, RES.TRANSACTIONCURRENCYID, RES.CONSTITUENTID, RES.PDACCOUNTSYSTEMID, RES.BALANCE, NEXTINSTALLMENT.BALANCE, NEXTINSTALLMENT.DATE, NEXTINSTALLMENT.ID
    from
    (
        select
            FT.ID,
            C.ID CONSTITUENTID,
            FT.TYPECODE,
            FT.TRANSACTIONCURRENCYID,
            FT.PDACCOUNTSYSTEMID,
            FT.TRANSACTIONAMOUNT - isnull(sum(SPLITS.AMOUNT),0) BALANCE
        from
            #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS C
        inner join 
            dbo.FINANCIALTRANSACTION FT  on C.ID = FT.CONSTITUENTID
        left join 
            dbo.INSTALLMENT INSTALLMENTS on INSTALLMENTS.REVENUEID = FT.ID
        left join (
            select 
                isnull(sum(ISP.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID
            union all
            select 
                isnull(sum(ISW.AMOUNT),0) AMOUNT,
                ISPLIT.INSTALLMENTID
            from 
                dbo.INSTALLMENTSPLIT ISPLIT 
            inner join 
                dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
            group by
                ISPLIT.INSTALLMENTID) as SPLITS on SPLITS.INSTALLMENTID = INSTALLMENTS.ID
        where 
            FT.DELETEDON is null
        group by FT.ID, C.ID, TYPECODE, FT.TRANSACTIONCURRENCYID, FT.PDACCOUNTSYSTEMID, FT.TRANSACTIONAMOUNT

    ) RES

    outer apply (
        select
            top 1 INSTALLMENTS.ID,
            INSTALLMENTS.[DATE],
            sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0) BALANCE
        from
            dbo.INSTALLMENT INSTALLMENTS
        left join (
            select
                sum(SPLITS.AMOUNT) as AMOUNT,
                SPLITS.ID 
            from 
            (
                select 
                    isnull(sum(ISP.AMOUNT),0) as AMOUNT,
                    ISPLIT.INSTALLMENTID as ID
                from 
                    dbo.INSTALLMENTSPLIT ISPLIT 
                inner join 
                    dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = ISPLIT.ID
                group by
                    ISPLIT.INSTALLMENTID
                union all
                select 
                    isnull(sum(ISW.AMOUNT),0) as AMOUNT,
                    ISPLIT.INSTALLMENTID as ID
                from 
                    dbo.INSTALLMENTSPLIT ISPLIT 
                inner join 
                    dbo.INSTALLMENTSPLITWRITEOFF ISW on ISW.INSTALLMENTSPLITID = ISPLIT.ID
                group by
                    ISPLIT.INSTALLMENTID
            ) SPLITS
            inner join 
                INSTALLMENT on SPLITS.ID = INSTALLMENT.ID
            group by 
                SPLITS.ID 
        ) as SUMMEDSPLITS on SUMMEDSPLITS.ID = INSTALLMENTS.ID
        where 
            INSTALLMENTS.REVENUEID = RES.ID
        group by 
            INSTALLMENTS.ID, INSTALLMENTS.[DATE]
        having 
            (sum(INSTALLMENTS.TRANSACTIONAMOUNT) - isnull(sum(SUMMEDSPLITS.AMOUNT),0)) > 0
        order by 
            INSTALLMENTS.[DATE] asc
    ) as NEXTINSTALLMENT

    where RES.BALANCE > 0;

    insert into #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
    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,
        REVENUE.PLEDGEBALANCE,
        REVENUE.INSTALLMENTBALANCE,
        REVENUE.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 
        #CONSTITUENTUNREALIZEDREVENUE_POTENTIALCOMMITMENTS REVENUE
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
        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
    where
        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.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
        );

    insert into #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
    select
        REVENUE.ID,
        CONSTITUENT.ID,
        CONSTITUENT.NAME,
        0,
        REVENUE.TRANSACTIONAMOUNT, -- TRANSACTIONAMOUNT

        (select BALANCE from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null)),
        REVENUESCHEDULE.NEXTTRANSACTIONDATE,
        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 #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS 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
    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 #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
        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
                        )
                )
            )option(recompile);
    end

    -- Gather all the designation names for each row in results table 

    if object_id('tempdb..#REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS') is not null
    drop table #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS;

    create table #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS
    (
        ID uniqueidentifier,
        DESIGNATION varchar(max) collate database_default
    )

    insert into #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS (ID, DESIGNATION)
    select
        REVENUECOMMITMENTS.REVENUEID,
        dbo.UDA_BUILDLIST(DESIGNATION.USERID)
    from 
        #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS REVENUECOMMITMENTS
    inner join 
        FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID  = REVENUECOMMITMENTS.REVENUEID
    inner join 
        dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
    inner join 
        dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
    where
        FTLI.DELETEDON is null
    group by 
        REVENUECOMMITMENTS.REVENUEID;

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

    update REVENUECOMMITMENTS
    set
        DESIGNATIONLIST = coalesce(
        (
            select
                left(D.DESIGNATION, 500)
            from 
                #REVENUEBATCH_CONSTITUENTCOMMITMENT_DESIGNATIONS D
            where 
                D.ID = REVENUECOMMITMENTS.REVENUEID
        ), '')    
    from 
        #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS REVENUECOMMITMENTS;

    insert into #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
    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 #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS 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
                        )
                )
            )
        )option(recompile);

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

    insert into #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
    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 #CONSTITUENTUNREALIZEDREVENUE_CONSTITUENTS 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
        )   

    select
        REVENUEID,
        CONSTITUENTID,
        CONSTITUENTNAME,
        APPLIED,
        BALANCE,
        AMOUNTDUE,
        DATEDUE,
        REVENUETYPECODE,
        DESCRIPTION,
        SORTORDER,
        DESIGNATIONLIST,
        SPONSORSHIPOPPORTUNITY,
        APPLICATIONCURRENCYID,
        PDACCOUNTSYSTEMID,
        SPONSORSHIPRGADDITIONALGIFT,
        MEMBERSHIPPROGRAMNAME,
        MEMBERSHIPLEVELNAME
    from
        #CONSTITUENTUNREALIZEDREVENUE_COMMITMENTS
    order by SORTORDER desc;