USP_SIMPLEDATALIST_REVENUEBATCHCONSTITUENTCOMMITMENTS

CREATE procedure dbo.USP_SIMPLEDATALIST_REVENUEBATCHCONSTITUENTCOMMITMENTS
(
    @CONSTITUENTID1 uniqueidentifier = null,
    @CONSTITUENTLOOKUPID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @PAYINGPENDINGREVENUEID uniqueidentifier = null,
    @COMMITMENTID nvarchar(60) = null,
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @BATCHREVENUEROWID uniqueidentifier = null
)
as
    set nocount on;

    if @PDACCOUNTSYSTEMID is null
        set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID);

    -- Fall back to the system default if the above doesn't work

    if @PDACCOUNTSYSTEMID is null
        set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

    --If the constituent pays on another constituents commitments we need to get the

    --ID of the other constituent so we can get their commitment information

    declare @CONSTITUENTID uniqueidentifier;
    declare @PAYERID uniqueidentifier;
    declare @SINGLEAPPLICATIONID uniqueidentifier = null;

    set @PAYERID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);

    if len(@COMMITMENTID) >= 39
    begin
        set @CONSTITUENTID = dbo.UFN_REVENUEBATCH_GETCONSTITUENTFOROTHERCOMMITEMENT(@COMMITMENTID)
    if @CONSTITUENTID is null
              set @CONSTITUENTID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);

          select @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID
          from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@COMMITMENTID)
    end
    else
    begin
        set @CONSTITUENTID = coalesce(@CONSTITUENTID1, @CONSTITUENTLOOKUPID);
    end

    declare @retval as table 
    (
        VALUE nvarchar(60), 
        LABEL nvarchar(255),
        SEQUENCE tinyint
    );

    declare @CONSTITIDS as table
    (
        ID uniqueidentifier
    );

    declare @SITES table (SITEID uniqueidentifier);

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
        or exists(
            select SYSTEMROLEAPPUSER.ID 
            from dbo.SYSTEMROLEAPPUSER 
            where SYSTEMROLEAPPUSER.APPUSERID = @CURRENTAPPUSERID and SYSTEMROLEAPPUSER.SECURITYMODECODE = 0
        )
    begin
        insert into @SITES(SITEID)
        values(null);

        insert into @SITES(SITEID) 
        select SITE.ID from dbo.SITE;
    end
    else
  begin
    if exists(select 1 from dbo.SYSTEMROLEAPPUSER where APPUSERID = @CURRENTAPPUSERID and SECURITYMODECODE = 1)
    begin
      insert into @SITES(SITEID)
      values(null);
    end

        insert into @SITES(SITEID)
        select distinct SITEID 
        from dbo.SITEPERMISSION 
        where SITEPERMISSION.APPUSERID = @CURRENTAPPUSERID;
  end

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

    declare @HOUSEHOLDSCANBEDONORS bit;
    set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();

    declare @HOUSEHOLDID uniqueidentifier;
    select top(1) 
        @HOUSEHOLDID = GROUPMEMBER.GROUPID
    from dbo.GROUPMEMBER
        left 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;            

    declare @BASECONSTITUENTID uniqueidentifier
    select 
        @BASECONSTITUENTID = 
            case 
                when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 
                    then case 
                        when @HOUSEHOLDSCANBEDONORS = 1 
                            then @CONSTITUENTID 
                        else null                     
                    end
                else @CONSTITUENTID
            end         

    -- include (1) the constituent, (2) the household a constituent is a member of, (3) members of that household, (4) members of the constituent if it is a household

    insert into @CONSTITIDS(ID)    (
        select @HOUSEHOLDID as ID
        where @HOUSEHOLDID is not null

        union all

        select @BASECONSTITUENTID as ID
        where @BASECONSTITUENTID is not null


        union all

        select 
            GROUPMEMBER.MEMBERID as ID
        from dbo.GROUPMEMBER
            left 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
                )
            )
    )

    /*
        The VALUE column holds a string that contains a coded application type as an integer.
        Those application types are:
        0 - Not an application (Donation, Other, Unapplied matching gift payment, etc.)
        1 - Sponsorship
        2 - Membership
        3 - Order/Reservation
        4 - Recurring gift
        5 - Pledge
        6 - Planned gift
        7 - Event registration
        8 - Matching gift claim
        9 - Grant award
        10 - Donor challenge
        These are also defined by the ApplicationInfo class in RevenueBatchHelper.vb.
    */

    -- 1. Sponsorships

    insert into @retval(VALUE, LABEL, SEQUENCE)
    select
        cast(REVENUE.ID as varchar(36)) + ':1:' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20)), 
        'Sponsorship recurring gift: ' 
            + (
                select top 1 
                    coalesce(
                        (select NAME from SPONSORSHIPOPPORTUNITYCHILD where ID=S.SPONSORSHIPOPPORTUNITYID),
                        (select NAME from SPONSORSHIPOPPORTUNITYPROJECT where ID = S.SPONSORSHIPOPPORTUNITYID)
                    ) SPONSORSHIPOPPORTUNITY
                from REVENUESPLIT SP
                    inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID
                    inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
                where SP.REVENUEID = REVENUE.ID
            ) 
            + ' for ' + CONSTITUENT_NF.NAME + ' ' 
            + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101)
            + coalesce(
                ' - ' +    (
                    select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                    from dbo.REVENUESPLIT 
                        inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                    where REVENUESPLIT.REVENUEID = REVENUE.ID
                ), 
                ''
            )
            + ' - ' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20)) 
            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
        1
    from dbo.FINANCIALTRANSACTION as REVENUE
        inner join @CONSTITIDS C on C.ID = REVENUE.CONSTITUENTID
        inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF        
    where REVENUE.TYPECODE = 2 and REVENUE.DELETEDON is null
        and REVENUESCHEDULE.STATUSCODE in (0,5)
        and REVENUESCHEDULE.ISPENDING = 0
        and REVENUE.TRANSACTIONAMOUNT > 0
        and exists(select 'x' from dbo.REVENUESPLIT where REVENUEID = REVENUE.ID and TYPECODE = 9)
        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists (
                select 1 from @SITES S
                    left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
                where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
            )
        )

    -- 2. Memberships

    declare @CONSTITUENTMEMBERSHIP table
    (
        MEMBERSHIPID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        ISDROPPED bit,
        ISPRIMARY bit
    )

    insert into @CONSTITUENTMEMBERSHIP (MEMBERSHIPID, CONSTITUENTID, ISDROPPED, ISPRIMARY)
    select 
        MEMBERSHIPID,
        C.ID,
        ISDROPPED,
        ISPRIMARY
    from @CONSTITIDS C
    inner join dbo.MEMBER on C.ID = MEMBER.CONSTITUENTID

    insert into @retval(VALUE, LABEL, SEQUENCE)
    select
        cast(MEMBERSHIP.ID as varchar(36)) + ':2:' + cast(cast(MEMBERSHIPLEVELTERM.AMOUNT as money) as varchar(20)),
        dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' 
            + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) + ' for ' 
            + CONSTITUENT_NF.NAME + coalesce(' ' + convert(varchar(10), MEMBERSHIP.EXPIRATIONDATE, 101), '') 
            + ' - ' + cast(cast(MEMBERSHIPLEVELTERM.AMOUNT as money) as varchar(20)) 
            + ' ' + dbo.UFN_CURRENCY_GETISO(MEMBERSHIPPROGRAM.BASECURRENCYID),
        2
    from @CONSTITUENTMEMBERSHIP as CONSTITUENTMEMBERSHIP
    inner join dbo.MEMBERSHIP on CONSTITUENTMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
    cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEMBERSHIP.CONSTITUENTID) CONSTITUENT_NF
    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
    where 
        --MEMBER.ISDROPPED = 0 and

        (CONSTITUENTMEMBERSHIP.ISPRIMARY = 1 or MEMBERSHIP.SENDRENEWALCODE <> 1)
        --and (

        --    (MEMBERSHIP.STATUSCODE = 0 

        --        and @CURRENTDATE >= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE)

        --        and @CURRENTDATE <= dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE)

        --    )

         --   or (MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE < @CURRENTDATE)

        --    or (MEMBERSHIP.STATUSCODE = 2 or MEMBERSHIP.STATUSCODE = 1)

        --)

        and (
            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists (
                select 1 
                from @SITES S
                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 @retval(VALUE, LABEL, SEQUENCE)
    -- 3. Order/Reservation, 5. Pledge, 6. Planned gift, 8. Matching gift claim, 9. Grant award, 10 Donor challenge

    select
        cast(REVENUE.ID as varchar(36)) + ':' 
            + case REVENUE.TYPECODE 
                when 5 then '3' 
                when 1 then '5' 
                when 4 then '6' 
                when 3 then '8' 
                when 6 then '9' 
                when 8 then '10' 
            end 
            + ':' + coalesce(cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as money) as varchar(20)),'0.00'),
        case REVENUE.TYPECODE
            when 5 then 'Order for ' + CONSTITUENT_NF.NAME + ' '
            when 1 then 'Pledge for ' + CONSTITUENT_NF.NAME + ' '
            when 4 then 'Planned gift for ' + CONSTITUENT_NF.NAME + ' '
            when 3 then 'Matching gift claim for ' + CONSTITUENT_NF.NAME + ' - ' 
                +(
                    select 
                        C1_NF.NAME 
                    from dbo.REVENUEMATCHINGGIFT MG1 
                        inner join dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID 
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(R1.CONSTITUENTID) C1_NF
                    where MG1.ID = REVENUE.ID
                ) + ' '
            when 6 then 'Grant award from ' + CONSTITUENT_NF.NAME + ' '
            when 8 then 'Donor challenge claim from ' + CONSTITUENT_NF.NAME + ' '
        end 
            + convert(varchar(10), INSTALLMENT.DATE, 101) 
            + coalesce(
                ' - ' 
                    + cast(
                        (
                            select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                            from dbo.REVENUESPLIT
                                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                            where REVENUESPLIT.REVENUEID = REVENUE.ID
                        ) as nvarchar(50)
                    ),
                '') 
            + ' - ' + coalesce(cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as money) as varchar(20)),'0.00') 
            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
        case REVENUE.TYPECODE 
            when 5 then 3 
            when 1 then 5 
            when 4 then 6 
            when 3 then 8 
            when 6 then 9
            when 8 then 10
        end
    from dbo.FINANCIALTRANSACTION as REVENUE
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
        -- Including the predicate "INSTALLMENT.REVENUEID = REVENUE.ID" in the join even though UFN_REVENUE_GETNEXTINSTALLMENT

        -- already includes that since without that join the estimated number of rows is wrong, resulting in an extremely poor plan.

        inner join dbo.INSTALLMENT 
            on INSTALLMENT.REVENUEID = REVENUE.ID 
                and (INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
          --Bug 148137 AdiSa - 5/9/11 - Include paid off commitment by selecting the correct installment if the next installment function returns nothing.

          or (dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID) is null and 
              INSTALLMENT.ID= (select top 1 ID 
                                from dbo.INSTALLMENT 
                                where INSTALLMENT.REVENUEID = @SINGLEAPPLICATIONID 
                                order by INSTALLMENT.SEQUENCE desc)
              )
                )
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        left join dbo.REVENUEMATCHINGGIFT  on REVENUE.ID = REVENUEMATCHINGGIFT.ID        
    where (dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
            --Bug 133370 - AdamBu - 1/10/11 - Include paid off commitment if it is what this revenue was applied to.

            or REVENUE.ID = @SINGLEAPPLICATIONID
        )
        and REVENUE.TYPECODE in (1,3,4,5,6,8) -- Pledge, Matching gift claim, Planned gift, Order, Grant award, Donor challenge claim

        and REVENUE.DELETEDON is NULL
        and REVENUESCHEDULE.ISPENDING = 0
        and REVENUE.TYPECODE <> 2
        and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3)
        and REVENUE.CONSTITUENTID in (select ID from @CONSTITIDS)
        and ((REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) or (dbo.UFN_VALID_BASICGL_INSTALLED() = 0) or REVENUE.TYPECODE in (3,8)) -- MGC and Donor Challenge

        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists 
            (
                select 1 
                from @SITES S
                    left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
                where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
            )
        )

    insert into @retval(VALUE, LABEL, SEQUENCE)
    -- 8 (again). Subsidiary matching gift claims (JamesWill WI76028 2011-02-01)

    select
        cast(REVENUE.ID as varchar(36)) + ':8'
            + ':' + coalesce(cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as money) as varchar(20)),'0.00'),
        'Matching gift claim for ' + CONSTITUENT_NF.NAME + ' - ' 
                +(
                    select 
                        C1_NF.NAME 
                    from dbo.REVENUEMATCHINGGIFT MG1 
                        inner join dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID 
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(R1.CONSTITUENTID) C1_NF
                    where MG1.ID = REVENUE.ID
                ) + ' '
            + convert(varchar(10), INSTALLMENT.DATE, 101) 
            + coalesce(
                ' - ' 
                    + cast(
                        (
                            select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                            from dbo.REVENUESPLIT
                                inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                            where REVENUESPLIT.REVENUEID = REVENUE.ID
                        ) as nvarchar(50)
                    ),
                '') 
            + ' - ' + coalesce(cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as money) as varchar(20)),'0.00') 
            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
        8
    from dbo.FINANCIALTRANSACTION as REVENUE
    inner join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = REVENUE.CONSTITUENTID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
        -- Including the predicate "INSTALLMENT.REVENUEID = REVENUE.ID" in the join even though UFN_REVENUE_GETNEXTINSTALLMENT

        -- already includes that since without that join the estimated number of rows is wrong, resulting in an extremely poor plan.

        inner join dbo.INSTALLMENT 
            on INSTALLMENT.REVENUEID = REVENUE.ID 
                and (INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
                    --Bug 133370 - AdamBu - 1/10/11 - Include paid off commitment if it is what this revenue was applied to.

                    or INSTALLMENT.REVENUEID = @SINGLEAPPLICATIONID
                )
        inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        left join dbo.REVENUEMATCHINGGIFT  on REVENUE.ID = REVENUEMATCHINGGIFT.ID
        left join dbo.PDACCOUNTSYSTEMFORREVENUE PDAS on REVENUE.ID = PDAS.ID
    where (dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
            --Bug 133370 - AdamBu - 1/10/11 - Include paid off commitment if it is what this revenue was applied to.

            or REVENUE.ID = @SINGLEAPPLICATIONID
        )
        and REVENUE.TYPECODE = 3 -- Matching gift claim

        and REVENUESCHEDULE.ISPENDING = 0
        and REVENUE.TYPECODE <> 2
        and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3)
        and ORGANIZATIONDATA.PARENTCORPID in (select ID from @CONSTITIDS)
        and ((PDAS.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) or (dbo.UFN_VALID_BASICGL_INSTALLED() = 0) or REVENUE.TYPECODE = 3)
        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists 
            (
                select 1 
                from @SITES S
                    left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
                where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
            )
        )

    -- 4. Recurring gift

    insert into @retval(VALUE, LABEL, SEQUENCE)
    select
        cast(REVENUE.ID as varchar(36)) + ':4:' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20)), 
    case when exists(select 'x' from dbo.REVENUESPLIT where REVENUEID = REVENUE.ID and TYPECODE = 17) 
    then 'Sponsorship recurring additional gift for ' else 'Recurring gift for ' end        
    + CONSTITUENT_NF.NAME + ' ' 
            + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101) 
            + coalesce(
                ' - ' + (
                    select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                    from dbo.REVENUESPLIT 
                        inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                    where REVENUESPLIT.REVENUEID = REVENUE.ID
                ), 
                '') 
            + ' - ' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20)) 
            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
        4
    from dbo.REVENUE                
        inner join @CONSTITIDS C on C.ID = REVENUE.CONSTITUENTID
        inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
    where REVENUE.TRANSACTIONTYPECODE = 2
        and REVENUESCHEDULE.STATUSCODE in (0,5)
        and REVENUESCHEDULE.ISPENDING = 0
        and REVENUE.AMOUNT > 0
        and not exists(select 'x' from dbo.REVENUESPLIT where REVENUEID = REVENUE.ID and TYPECODE = 9)
        and    (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
                or exists 
                (
                    select 1 
                    from @SITES S
                        left outer join dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) SITE on SITE.SITEID = S.SITEID 
                    where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
                )
        )

    -- 7. Event registration

    insert into @retval(VALUE, LABEL, SEQUENCE)
    select
        cast(REGISTRANT.ID as varchar(36)) + ':7:' + cast(cast(REGISTRANTBALANCE.BALANCEINCURRENCY as money) as varchar(20)),
        [EVENT].NAME 
            + ' registration for ' + CONSTITUENT_NF.NAME 
            + ' ' + convert(varchar(10), [EVENT].STARTDATE, 101) 
            + ' - ' + cast(cast(REGISTRANTBALANCE.BALANCEINCURRENCY as money) as varchar(20)) 
            + ' ' + dbo.UFN_CURRENCY_GETISO(EVENT.BASECURRENCYID),
        7
    from dbo.REGISTRANT
        inner join @CONSTITIDS C on C.ID = REGISTRANT.CONSTITUENTID
        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) CONSTITUENT_NF
        inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
        cross apply dbo.UFN_EVENTREGISTRANT_GETBALANCEINCURRENCY_BULK([EVENT].BASECURRENCYID) REGISTRANTBALANCE
    where 
        REGISTRANT.ID = REGISTRANTBALANCE.ID and
        (
            REGISTRANTBALANCE.BALANCEINCURRENCY > 0
            --Bug 133370 - AdamBu - 1/10/11 - Include paid off commitment if it is what this revenue was applied to.

            or REGISTRANT.ID = @SINGLEAPPLICATIONID
        )
        and    (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            or exists 
            (
                select 1 
                from @SITES S
                    left outer join dbo.UFN_SITEID_MAPFROM_REGISTRANTID(REGISTRANT.ID) SITE on SITE.SITEID = S.SITEID 
                where S.SITEID=SITE.SITEID or (S.SITEID is null and SITE.SITEID is null)
            )
        )

    if @PAYINGPENDINGREVENUEID is not null
    begin
        --If this batch row is paying a pending transaction, insert it into the list

        insert into @retval(VALUE, LABEL, SEQUENCE)
        select
            case REVENUE.TRANSACTIONTYPECODE
                when 2 
                    then cast(REVENUE.ID as varchar(36)) + ':4:' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20))
                when 1 
                    then cast(REVENUE.ID as varchar(36)) + ':5:' + cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) as money)  as varchar(20))
                else ''
            end,
            case REVENUE.TRANSACTIONTYPECODE
                when 2 
                    then
                        'Recurring gift for ' + CONSTITUENT_NF.NAME 
                            + ' ' + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE , 101) 
                            + coalesce(
                                ' - ' 
                                    + (
                                        select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                                        from dbo.REVENUESPLIT 
                                            inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                                        where REVENUESPLIT.REVENUEID = REVENUE.ID
                                    ), 
                                '') 
                            + ' - ' + cast(cast(REVENUE.TRANSACTIONAMOUNT as money) as varchar(20)) 
                            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID)
                when 1 
                    then
                        'Pledge for ' + CONSTITUENT_NF.NAME + ' ' + convert(varchar(10), INSTALLMENT.DATE, 101) 
                            + coalesce(
                                ' - ' 
                                    + (
                                        select top 1 dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                                        from dbo.REVENUESPLIT 
                                        inner join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                                        where REVENUESPLIT.REVENUEID = REVENUE.ID
                                    ), 
                                '') 
                            + ' - ' + cast(cast(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID)as money) as varchar(20)) 
                            + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID)
                else ''
            end,
            case REVENUE.TRANSACTIONTYPECODE
                when 2 then 4
                when 1 then 5
                else 99
            end
        from dbo.REVENUE
            inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) CONSTITUENT_NF
            left join dbo.INSTALLMENT on REVENUE.TRANSACTIONTYPECODE = 1 and INSTALLMENT.ID = dbo.UFN_REVENUE_GETNEXTINSTALLMENT(REVENUE.ID)
        where @PAYINGPENDINGREVENUEID is not null 
            and REVENUE.ID = @PAYINGPENDINGREVENUEID;
    end

    insert into @retval(VALUE, LABEL, SEQUENCE)
    values('0:0', 'Donation', 20)

    insert into @retval(VALUE, LABEL, SEQUENCE)
    values('0:1', 'Other', 21)

    insert into @retval(VALUE, LABEL, SEQUENCE)
    values('0:2', 'Unapplied matching gift payment', 22)

    --if the payer is an active sponsor, add 'Sponsorship additional donation'

    if exists(select 1
                from dbo.CONSTITUENT 
                where CONSTITUENT.ID = @PAYERID
                and exists (select 1 from dbo.REVENUESPLIT
                inner join dbo.SPONSORSHIP on SPONSORSHIP.REVENUESPLITID = REVENUESPLIT.ID
                inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                where REVENUE.CONSTITUENTID = CONSTITUENT.ID
                and SPONSORSHIP.STATUSCODE in (0,1) -- Pending, Active

                and REVENUE.TRANSACTIONTYPECODE = 2 -- Sponsorships use the Recurring gift type

                ))
            insert into @retval(VALUE, LABEL, SEQUENCE)
            values('0:3', 'Sponsorship additional donation', 23);

    if @BATCHREVENUEROWID is not null
    begin
        declare @APPLICATIONINFO nvarchar(60);
        select top 1 @APPLICATIONINFO = APPLICATIONINFO from dbo.BATCHREVENUE where ID = @BATCHREVENUEROWID;
        if @APPLICATIONINFO is not null
        begin
            declare @DESCRIPTION nvarchar(255) = dbo.UFN_BATCHREVENUECONSTITUENTCOMMITMENTS_GETDESCRIPTION(@APPLICATIONINFO);
            if (not exists (select VALUE from @retval where SUBSTRING(VALUE,1,32) = SUBSTRING(@APPLICATIONINFO,1,32))) and (@DESCRIPTION is not null and @DESCRIPTION <> '' )
                insert into @retval(VALUE, LABEL, SEQUENCE)
                values(@APPLICATIONINFO, @DESCRIPTION, 0)
        end
    end

    select VALUE, LABEL
    from @retval
    order by SEQUENCE