USP_DATALIST_REVENUEBATCHDEFAULTAPPLICATION

        CREATE procedure [dbo].[USP_DATALIST_REVENUEBATCHDEFAULTAPPLICATION]
        (
            @CONSTITUENTID uniqueidentifier = null,
            @AMOUNT money = null,
            @CURRENTAPPUSERID uniqueidentifier = null,
            @SECURITYFEATUREID uniqueidentifier = null,
            @SECURITYFEATURETYPE tinyint = null,
            @APPLICATIONCURRENCYID uniqueidentifier = null,
            @PDACCOUNTSYSTEMID uniqueidentifier = null

        )
        as
            set nocount on;

            declare @ISSYSADMIN bit = 0
            select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

            declare @BASICGLINSTALLED bit = 0
            select @BASICGLINSTALLED = dbo.UFN_VALID_BASICGL_INSTALLED()

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

            create table #TMP_REVENUEBATCHDEFAULTAPPLICATION_SITE
            (
                SITEID uniqueidentifier unique clustered
            );

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

            create table #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION
            (
                VALUE nvarchar(60) collate DATABASE_DEFAULT,
                LABEL nvarchar(max) collate DATABASE_DEFAULT,
                SEQUENCE tinyint,
                CONSTITUENTID uniqueidentifier,
                REVENUEID uniqueidentifier,
                SOURCEREVENUECONSTITUENTID uniqueidentifier
            );

            insert into #TMP_REVENUEBATCHDEFAULTAPPLICATION_SITE(SITEID)
            select SITESFORUSER.SITEID from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) SITESFORUSER

            if @APPLICATIONCURRENCYID is null
                set @APPLICATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

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

            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 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;            

            declare @VALIDCONSTITUENT table
            (
                ID uniqueidentifier
            )

            -- 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 @VALIDCONSTITUENT(ID)
            (
                select @HOUSEHOLDID as ID

                union

                select 
                    case 
                        when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1 
                            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)
                    )
            )


            /*
                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 #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION(VALUE, LABEL, SEQUENCE, CONSTITUENTID, REVENUEID, SOURCEREVENUECONSTITUENTID)
            select
                cast(REVENUE.ID as varchar(36)) + ':1:' + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)), 
                'Recurring gift for <REPLACE_WITH_NAME> '
                    + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101)
                    + '<REPLACE_WITH_DESIGNATIONS>'
                    + ' - ' 
                    + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
                case 
                    when REVENUE.TRANSACTIONAMOUNT = @AMOUNT
                        then 1 
                    else 11 
                end,
                REVENUE.CONSTITUENTID,
                REVENUE.ID,
                null
            from 
                dbo.FINANCIALTRANSACTION as REVENUE                
                inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REVENUE.CONSTITUENTID
                inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
            where 
                REVENUE.TYPECODE = 2
                and REVENUESCHEDULE.STATUSCODE in (0,5)
                and REVENUESCHEDULE.ISPENDING = 0
                and REVENUE.TRANSACTIONAMOUNT > 0
                and exists(
                            select 1 
                            from 
                                dbo.FINANCIALTRANSACTIONLINEITEM 
                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID and REVENUESPLIT_EXT.TYPECODE = 9
                )
                and REVENUE.TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                and REVENUE.DELETEDON is null 

            -- 3. Order/Reservation, 5. Pledge, 6. Planned gift, 8. Matching gift claim, 9. Grant award, 10. Donor challenge

            insert into #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION(VALUE, LABEL, SEQUENCE, CONSTITUENTID, REVENUEID, SOURCEREVENUECONSTITUENTID)
      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 
                    + ':' + cast(INSTALLMENTBALANCEBULK.BALANCE as varchar(20)),
                case REVENUE.TYPECODE
                    when 5 then 'Order for <REPLACE_WITH_NAME> '
                    when 1 then 'Pledge for <REPLACE_WITH_NAME> '
                    when 4 then 'Planned gift for <REPLACE_WITH_NAME> '
                    when 3 then 'Matching gift claim for <REPLACE_WITH_NAME> - <REPLACE_WITH_SOURCE_NAME> '
                    when 6 then 'Grant award from <REPLACE_WITH_NAME> '
                    when 8 then 'Donor challenge claim from <REPLACE_WITH_NAME> '
                end 
                    + convert(varchar(10), INSTALLMENT.DATE, 101) 
                    + '<REPLACE_WITH_DESIGNATIONS>'
                    + ' - ' + cast(INSTALLMENTBALANCEBULK.BALANCE as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
                case 
                    when INSTALLMENTBALANCEBULK.BALANCE = @AMOUNT 
                        then 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
                    else case REVENUE.TYPECODE 
                        when 5 then 13 
                        when 1 then 15 
                        when 4 then 16 
                        when 3 then 18 
                        when 6 then 19 
                        when 8 then 20
                    end
                end,
                REVENUE.CONSTITUENTID,
                REVENUE.ID,
                SOURCEREVENUE.CONSTITUENTID
            from 
                dbo.FINANCIALTRANSACTION as REVENUE
                inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REVENUE.CONSTITUENTID
                -- Adding join predicate on REVENUE.ID = INSTALLMENT.REVENUEID even though it isn't necessary to get the correct result

                -- since without it, a table scan is done on INSTALLMENT.  

                inner join dbo.INSTALLMENT on (REVENUE.ID = INSTALLMENT.REVENUEID)
                inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() INSTALLMENTBALANCEBULK on INSTALLMENTBALANCEBULK.ID = INSTALLMENT.ID
                inner join dbo.UFN_REVENUE_GETNEXTINSTALLMENT_BULK() 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 SOURCEREVENUE on SOURCEREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
            where 
                INSTALLMENT.ID = NEXTINSTALLMENT.INSTALLMENTID
                and REVENUESCHEDULE.ISPENDING = 0
                and REVENUE.TYPECODE <> 2
                and INSTALLMENTBALANCEBULK.BALANCE > 0
                and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3)
                and ((REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID) or (@BASICGLINSTALLED = 0) or (REVENUE.TYPECODE in (3,8))) --MG Claims and donor challenge claims don't post so they won't have an accounting system

                and INSTALLMENT.TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) 
                and REVENUE.DELETEDON is null  


            -- 8 (again). Subsidiary matching gift claims (JamesWill WI76028 2011-02-01)

            insert into #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION(VALUE, LABEL, SEQUENCE, CONSTITUENTID, REVENUEID, SOURCEREVENUECONSTITUENTID)
            select
                cast(REVENUE.ID as varchar(36)) + ':8'
                    + ':' + cast(INSTALLMENTBALANCEBULK.BALANCE as varchar(20)),
                'Matching gift claim for <REPLACE_WITH_NAME> - <REPLACE_WITH_SOURCE_NAME> '
                    + convert(varchar(10), INSTALLMENT.DATE, 101) 
                    + '<REPLACE_WITH_DESIGNATIONS>'
                    + ' - ' + cast(INSTALLMENTBALANCEBULK.BALANCE as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
                case 
                    when INSTALLMENTBALANCEBULK.BALANCE = @AMOUNT 
                        then 8 
                    else 18 
                end,
                REVENUE.CONSTITUENTID,
                REVENUE.ID,
                SOURCEREVENUE.CONSTITUENTID
            from 
                dbo.FINANCIALTRANSACTION as REVENUE
                inner join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = REVENUE.CONSTITUENTID
                inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = ORGANIZATIONDATA.PARENTCORPID
                -- Adding join predicate on REVENUE.ID = INSTALLMENT.REVENUEID even though it isn't necessary to get the correct result

                -- since without it, a table scan is done on INSTALLMENT.  

                inner join dbo.INSTALLMENT on (REVENUE.ID = INSTALLMENT.REVENUEID)
                inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() INSTALLMENTBALANCEBULK on INSTALLMENTBALANCEBULK.ID = INSTALLMENT.ID
                inner join dbo.UFN_REVENUE_GETNEXTINSTALLMENT_BULK() 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 SOURCEREVENUE on SOURCEREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
            where
                INSTALLMENT.ID = NEXTINSTALLMENT.INSTALLMENTID
                and REVENUESCHEDULE.ISPENDING = 0
                and REVENUE.TYPECODE <> 2
                and INSTALLMENTBALANCEBULK.BALANCE > 0
                and (REVENUEMATCHINGGIFT.ISACTIVE = 1 or REVENUE.TYPECODE <> 3)
                and INSTALLMENT.TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null  

            -- 4. Recurring gift

            insert into #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION(VALUE, LABEL, SEQUENCE, CONSTITUENTID, REVENUEID, SOURCEREVENUECONSTITUENTID)
            select
                cast(REVENUE.ID as varchar(36)) + ':4:' + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)), 
                'Recurring gift for <REPLACE_WITH_NAME> ' 
                    + convert(varchar(10), REVENUESCHEDULE.NEXTTRANSACTIONDATE, 101) 
                    + '<REPLACE_WITH_DESIGNATIONS>'
                    + ' - ' + cast(REVENUE.TRANSACTIONAMOUNT as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO(REVENUE.TRANSACTIONCURRENCYID),
                case 
                    when REVENUE.TRANSACTIONAMOUNT = @AMOUNT and REVENUE.TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                        then 4 
                    else 14 
                end,
                REVENUE.CONSTITUENTID,
                REVENUE.ID,
                null
            from 
                dbo.FINANCIALTRANSACTION as REVENUE                
                inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REVENUE.CONSTITUENTID
                inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
            where 
                REVENUE.TYPECODE = 2
               and REVENUESCHEDULE.STATUSCODE  in (0,5)
                and REVENUESCHEDULE.ISPENDING = 0
                and REVENUE.TRANSACTIONAMOUNT > 0
                and not exists(
                            select 1 
                            from 
                                dbo.FINANCIALTRANSACTIONLINEITEM 
                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID and REVENUESPLIT_EXT.TYPECODE = 9
                )
                and REVENUE.TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
                and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null


            if @ISSYSADMIN = 0
            begin
                delete #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION
                where
                not exists(
                    select 1
                    from 
                        dbo.UFN_SITEID_MAPFROM_REVENUEID(#TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION.REVENUEID) as SITE
                    where
                    (
                        exists(
                            select 1 
                            from 
                                #TMP_REVENUEBATCHDEFAULTAPPLICATION_SITE SITESFORUSER 
                            where 
                                SITESFORUSER.SITEID=[SITE].[SITEID] 
                                or (SITESFORUSER.SITEID is null and [SITE].[SITEID] is null)
                        )
                    )
                )
            end



            -- 7. Event registration

            insert into #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION(VALUE, LABEL, SEQUENCE, CONSTITUENTID, REVENUEID, SOURCEREVENUECONSTITUENTID)
            select
                cast(REGISTRANT.ID as varchar(36)) + ':7:' + cast(dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as varchar(20)),
                [EVENT].NAME + ' registration for <REPLACE_WITH_NAME> ' + convert(varchar(10), [EVENT].STARTDATE, 101) 
                    + ' - ' + cast(dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as varchar(20)) + ' ' + dbo.UFN_CURRENCY_GETISO([EVENT].BASECURRENCYID),
                case 
                    when dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) = @AMOUNT and EVENT.BASECURRENCYID = @APPLICATIONCURRENCYID 
                        then 7 
                    else 17 
                end,
                REGISTRANT.CONSTITUENTID,
                null,
                null
            from 
                dbo.REGISTRANT
                inner join @VALIDCONSTITUENT VALIDCONSTITUENT on VALIDCONSTITUENT.ID = REGISTRANT.CONSTITUENTID
                inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
            where 
                dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) > 0
                and
                (    --Check site security

                    select count(*) 
                    from dbo.UFN_SITEID_MAPFROM_REGISTRANTID(REGISTRANT.ID) as SITE
                    where 
                    ( 
                        @ISSYSADMIN = 1 
                        or exists(
                            select 1 
                            from 
                                #TMP_REVENUEBATCHDEFAULTAPPLICATION_SITE SITESFORUSER 
                            where 
                                SITESFORUSER.SITEID=[SITE].[SITEID] 
                                or (SITESFORUSER.SITEID is null and [SITE].[SITEID] is null)
                            )
                    )
                ) > 0
                and EVENT.BASECURRENCYID = @APPLICATIONCURRENCYID;

            --BBNT\RyanDow (Ryan Dowacter) 03/01/2012

            --Rather than calling the constituent name format function several times and building a designation list several times

            --I'm inserting placeholders into the LABEL to be replaced now

            update APPLICATIONS
            set APPLICATIONS.LABEL = REPLACE(APPLICATIONS.LABEL,'<REPLACE_WITH_NAME>' COLLATE DATABASE_DEFAULT, CONSTITUENT_NF.NAME COLLATE DATABASE_DEFAULT)
            from
                #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION APPLICATIONS
                cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPLICATIONS.CONSTITUENTID) CONSTITUENT_NF

            update APPLICATIONS
            set APPLICATIONS.LABEL = REPLACE(APPLICATIONS.LABEL,'<REPLACE_WITH_SOURCE_NAME>' COLLATE DATABASE_DEFAULT, CONSTITUENT_NF.NAME COLLATE DATABASE_DEFAULT)
            from
                #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION APPLICATIONS
                cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPLICATIONS.SOURCEREVENUECONSTITUENTID) CONSTITUENT_NF;

            update APPLICATIONS
            set APPLICATIONS.LABEL = REPLACE(APPLICATIONS.LABEL, '<REPLACE_WITH_DESIGNATIONS>' COLLATE DATABASE_DEFAULT, coalesce(' - ' + DESIGNATIONS.DESIGNATIONLIST, '') COLLATE DATABASE_DEFAULT)
            from
                #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION APPLICATIONS
                outer apply
                (
                    select top 1 
                        dbo.UDA_BUILDLIST(DESIGNATION.USERID) as DESIGNATIONLIST 
                    from 
                        dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                    where 
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = APPLICATIONS.REVENUEID
                ) DESIGNATIONS

            select VALUE, left(LABEL,255)
            from #TMP_REVENUEBATCHDEFAULTAPPLICATION_APPLICATION
            order by SEQUENCE