USP_SMARTFIELD_LAST_RECURRINGGIFTINSTALLMENT

            CREATE procedure dbo.USP_SMARTFIELD_LAST_RECURRINGGIFTINSTALLMENT
            (
                @CONSTITUENTSELECTIONID uniqueidentifier = null,
                @DESIGNATIONS xml = null,
                @CAMPAIGNS xml = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @ASOF datetime,
                @SITES xml = null
            )
            as
                set nocount on;
                declare @DESIGNATIONSFILTER table(ID uniqueidentifier primary key);
                declare @CAMPAIGNSFILTER table(ID uniqueidentifier primary key);
                declare @SELECTIONFILTER table(ID uniqueidentifier primary key);
                declare @CONSTITUENTFILTER table(ID uniqueidentifier primary key);

                -- Build table of designations to filter on

                if @DESIGNATIONS is not null
                begin
                    insert into @DESIGNATIONSFILTER(ID) 
                        select distinct
                            T.c.value('(ID)[1]','uniqueidentifier')
                        from   
                            @DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c)
                end

                --Build table of campaigns to filter on        

                if @CAMPAIGNS is not null
                begin
                    insert into @CAMPAIGNSFILTER(ID)
                        select distinct
                            T.c.value('(ID)[1]','uniqueidentifier')
                        from 
                             @CAMPAIGNS.nodes('/CAMPAIGNS/ITEM') T(c)
                end


                declare @SITESFILTER table(ID uniqueidentifier primary key);
                insert into 
                    @SITESFILTER(ID) 
                select distinct
                    T.c.value('(SITEID)[1]','uniqueidentifier')
                FROM
                    @SITES.nodes('/SITES/ITEM') T(c)

                create table #CONSTITUENTSITEFILTER (ID uniqueidentifier primary key);
                insert into
                    #CONSTITUENTSITEFILTER(ID)
                select distinct
                    CONSTITUENTSITE.CONSTITUENTID
                from
                    CONSTITUENTSITE
                inner join
                    @SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID

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

                create table #TMP_REVENUESITEFILTER(ID uniqueidentifier);

                if @SITES is not null
                begin
                    insert into #TMP_REVENUESITEFILTER
                    select distinct
                        REVENUE.ID
                    from
                        REVENUE
                    cross apply 
                        dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITES
                    inner join
                        @SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
                end

                --Build table of constituents

                if @CONSTITUENTSELECTIONID is not null
                begin
                    if @SITES is null
                    begin
                        insert into @SELECTIONFILTER(ID)
                        select 
                            ID
                        from
                            dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID);
                    end
                    else
                    begin
                        insert into @SELECTIONFILTER(ID)
                        select 
                            CONSTITUENTSELECTION.ID
                        from
                            dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) as CONSTITUENTSELECTION
                        inner join
                            #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTSELECTION.ID
                    end
                end
                else
                begin
                    if @SITES is null
                    begin
                    insert into @SELECTIONFILTER(ID)
                        select distinct    
                            ID 
                        from 
                            dbo.CONSTITUENT        
                    end
                    else
                    begin
                    insert into @SELECTIONFILTER(ID)
                        select distinct    
                            CONSTITUENT.ID
                        from 
                            dbo.CONSTITUENT
                        inner join    
                            #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENT.ID
                    end
                end

                --Filter constituent list based on ASOF date                        

                if @ASOF is not null 
                begin
                    insert into @CONSTITUENTFILTER(ID) 
                        select 
                            CONSTITUENTID 
                        from 
                            dbo.REVENUE 
                        inner join     @SELECTIONFILTER as SELECTIONFILTER on 
                            SELECTIONFILTER.ID = REVENUE.CONSTITUENTID 
                        where 
                            REVENUE.DATECHANGED > @ASOF
                    union 
                        select 
                            CONSTITUENTID 
                        from 
                            dbo.REVENUEAUDIT 
                        inner join @SELECTIONFILTER as SELECTIONFILTER on
                            SELECTIONFILTER.ID = REVENUEAUDIT.CONSTITUENTID
                         where 
                            REVENUEAUDIT.AUDITDATE > @ASOF
                    union 
                        select
                            CONSTITUENTID 
                        from 
                            dbo.REVENUE 
                        inner join @SELECTIONFILTER as SELECTIONFILTER on
                             SELECTIONFILTER.ID = REVENUE.CONSTITUENTID 
                        inner join dbo.REVENUESPLIT on 
                            REVENUE.ID = REVENUESPLIT.REVENUEID 
                        where 
                            REVENUESPLIT.DATECHANGED > @ASOF
                    union 
                        select
                            CONSTITUENT.ID 
                        from 
                            dbo.CONSTITUENT 
                        inner join @SELECTIONFILTER as SELECTIONFILTER on
                             SELECTIONFILTER.ID = CONSTITUENT.ID     
                        where 
                            CONSTITUENT.DATEADDED > @ASOF;
                end
                else
                begin
                    insert into @CONSTITUENTFILTER(ID) 
                        select
                             ID 
                        from 
                            @SELECTIONFILTER;
                end;

        drop table #CONSTITUENTSITEFILTER;

        if @SITES is null
        begin
            with REVENUE_CTE (ID, CONSTITUENTID, DATE) as
                  (select    RECURRINGGIFT.ID ,
                      CONSTITUENTFILTER.ID,
                      RECURRINGGIFTINSTALLMENT.DATE 
               from @CONSTITUENTFILTER as CONSTITUENTFILTER
               left join dbo.REVENUE as RECURRINGGIFT on RECURRINGGIFT.CONSTITUENTID = CONSTITUENTFILTER.ID
               left join dbo.RECURRINGGIFTINSTALLMENT on
                                                         RECURRINGGIFTINSTALLMENT.REVENUEID = RECURRINGGIFT.ID and
                                                                   RECURRINGGIFTINSTALLMENT.AMOUNT != dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) and
                                                                   (@STARTDATE is null or RECURRINGGIFTINSTALLMENT.DATE >= @STARTDATE) and
                                                                   (@ENDDATE is null or RECURRINGGIFTINSTALLMENT.DATE <= @ENDDATE) 
               where 

                        RECURRINGGIFT.TRANSACTIONTYPECODE = 2 and
                        (@DESIGNATIONS is null or RECURRINGGIFT.ID in (select REVENUESPLIT.REVENUEID from dbo.REVENUESPLIT 
                                                                       inner join @DESIGNATIONSFILTER as DESIGNATIONSFILTER on DESIGNATIONSFILTER.ID = REVENUESPLIT.DESIGNATIONID)) and
                        (@CAMPAIGNS is null    or RECURRINGGIFT.ID in (select REVENUESPLIT.REVENUEID from dbo.REVENUESPLIT 
                                                                    inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID 
                                                                    inner join @CAMPAIGNSFILTER as CAMPAIGNSFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNSFILTER.ID))
              group by RECURRINGGIFT.ID,CONSTITUENTFILTER.ID,RECURRINGGIFTINSTALLMENT.DATE)


                    select 
                        CONSTITUENTFILTER.ID,
                        max(REVENUERECORDS.DATE)as LASTINSTALLMENTPAYMENT
                    from
                        @CONSTITUENTFILTER as CONSTITUENTFILTER 
              left join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
              group by CONSTITUENTFILTER.ID;
        end
        else
        begin
               with REVENUE_CTE (ID, CONSTITUENTID, DATE) as
                  (select    RECURRINGGIFT.ID ,
                      CONSTITUENTFILTER.ID,
                      RECURRINGGIFTINSTALLMENT.DATE 
               from @CONSTITUENTFILTER as CONSTITUENTFILTER
               left join dbo.REVENUE as RECURRINGGIFT on RECURRINGGIFT.CONSTITUENTID = CONSTITUENTFILTER.ID
               inner join #TMP_REVENUESITEFILTER as REVENUESITEFILTER on REVENUESITEFILTER.ID = RECURRINGGIFT.ID
               left join dbo.RECURRINGGIFTINSTALLMENT on
                                                         RECURRINGGIFTINSTALLMENT.REVENUEID = RECURRINGGIFT.ID and
                                                                   RECURRINGGIFTINSTALLMENT.AMOUNT != dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) and
                                                                   (@STARTDATE is null or RECURRINGGIFTINSTALLMENT.DATE >= @STARTDATE) and
                                                                   (@ENDDATE is null or RECURRINGGIFTINSTALLMENT.DATE <= @ENDDATE) 
               where 

                        RECURRINGGIFT.TRANSACTIONTYPECODE = 2 and
                        (@DESIGNATIONS is null or RECURRINGGIFT.ID in (select REVENUESPLIT.REVENUEID from dbo.REVENUESPLIT 
                                                                       inner join @DESIGNATIONSFILTER as DESIGNATIONSFILTER on DESIGNATIONSFILTER.ID = REVENUESPLIT.DESIGNATIONID)) and
                        (@CAMPAIGNS is null    or RECURRINGGIFT.ID in (select REVENUESPLIT.REVENUEID from dbo.REVENUESPLIT 
                                                                    inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID 
                                                                    inner join @CAMPAIGNSFILTER as CAMPAIGNSFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNSFILTER.ID))
              group by RECURRINGGIFT.ID,CONSTITUENTFILTER.ID,RECURRINGGIFTINSTALLMENT.DATE)


                    select 
                        CONSTITUENTFILTER.ID,
                        max(REVENUERECORDS.DATE)as LASTINSTALLMENTPAYMENT
                    from
                        @CONSTITUENTFILTER as CONSTITUENTFILTER 
              left join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
              group by CONSTITUENTFILTER.ID;
        end