USP_SMARTFIELD_MISSED_RECURRINGGIFTINSTALLMENTS

            CREATE procedure dbo.USP_SMARTFIELD_MISSED_RECURRINGGIFTINSTALLMENTS
            (
                @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 #CONSTITUENTSITEFITLER (ID uniqueidentifier primary key);
                insert into
                    #CONSTITUENTSITEFITLER(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
                            #CONSTITUENTSITEFITLER 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    
                            #CONSTITUENTSITEFITLER 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.REVENUE as R
                        inner join dbo.REVENUESCHEDULE as RS on
                            RS.ID = R.ID
                        inner join @SELECTIONFILTER as SELECTIONFILTER on
                            SELECTIONFILTER.ID = R.CONSTITUENTID
                        where 
                            RS.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 #CONSTITUENTSITEFITLER;

                if @SITES is null
                    select
                        CONSTITUENTFILTER.ID,
                        count(I.ID) as MISSEDINSTALLMENTS
                    from
                        @CONSTITUENTFILTER as CONSTITUENTFILTER 
                    outer apply (
                        select 
                            RECURRINGGIFT.CONSTITUENTID as ID
                        from 
                            dbo.REVENUE as RECURRINGGIFT
                        inner join dbo.REVENUESCHEDULE as RS on 
                            RS.ID = RECURRINGGIFT.ID 
                        inner join dbo.RECURRINGGIFTINSTALLMENT on 
                            RECURRINGGIFTINSTALLMENT.REVENUEID = RECURRINGGIFT.ID 
                        where
                            RECURRINGGIFT.CONSTITUENTID = CONSTITUENTFILTER.ID and
                            RECURRINGGIFT.TRANSACTIONTYPECODE = 2 and
                            RS.STATUSCODE IN (0, 1, 5) and --ACTIVE statuses

                            RECURRINGGIFTINSTALLMENT.AMOUNT = dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) and
                            RECURRINGGIFTINSTALLMENT.DATE < getdate() and
                            (@STARTDATE is null or RECURRINGGIFTINSTALLMENT.DATE >= @STARTDATE) and
                            (@ENDDATE is null or RECURRINGGIFTINSTALLMENT.DATE <= @ENDDATE) 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))
                        ) as I
                    group by 
                        CONSTITUENTFILTER.ID
                else
                    select
                        CONSTITUENTFILTER.ID,
                        count(I.ID) as MISSEDINSTALLMENTS
                    from
                        @CONSTITUENTFILTER as CONSTITUENTFILTER 
                    outer apply (
                        select 
                            RECURRINGGIFT.CONSTITUENTID as ID
                        from 
                            dbo.REVENUE as RECURRINGGIFT
                        inner join dbo.REVENUESCHEDULE as RS on 
                            RS.ID = RECURRINGGIFT.ID 
                        inner join dbo.RECURRINGGIFTINSTALLMENT on 
                            RECURRINGGIFTINSTALLMENT.REVENUEID = RECURRINGGIFT.ID 
                        inner join
                            #TMP_REVENUESITEFILTER as REVENUESITEFILTER on REVENUESITEFILTER.ID = RECURRINGGIFT.ID
                        where
                            RECURRINGGIFT.CONSTITUENTID = CONSTITUENTFILTER.ID and
                            RECURRINGGIFT.TRANSACTIONTYPECODE = 2 and
                            RS.STATUSCODE IN (0, 1, 5) and --ACTIVE statuses

                            RECURRINGGIFTINSTALLMENT.AMOUNT = dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RECURRINGGIFTINSTALLMENT.ID) and
                            RECURRINGGIFTINSTALLMENT.DATE < getdate() and
                            (@STARTDATE is null or RECURRINGGIFTINSTALLMENT.DATE >= @STARTDATE) and
                            (@ENDDATE is null or RECURRINGGIFTINSTALLMENT.DATE <= @ENDDATE) 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))
                        ) as I
                    group by 
                        CONSTITUENTFILTER.ID