USP_REPORT_RECURRINGGIFTMISSED

                    create procedure dbo.USP_REPORT_RECURRINGGIFTMISSED
                    (
                        @DATETYPE tinyint = null,
                        @DATERANGEDISPLAY nvarchar(100) = '',
                        @ASOFDATE datetime = null, 
                        @NUMMISSED int = 0,
                        @REVENUETRANSACTIONQUERY uniqueidentifier = null,
                        @CONSTITUENTID uniqueidentifier = null,
                        @REPORTUSERID nvarchar(128) = null,
                        @CURRENCYCODE tinyint = null,
                        @ALTREPORTUSERID nvarchar(128) = null
                    )
                    with execute as owner
                    as
                    set nocount on;

                    declare @CURRENTAPPUSERID uniqueidentifier;
                    declare @ISADMIN bit;
                    declare @APPUSER_IN_NONRACROLE bit;
                    declare @APPUSER_IN_NOSECGROUPROLE bit;

                    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
                    set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                    set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);

                    declare @SQLTOEXEC nvarchar(max);

                    declare @DBOBJECTNAME nvarchar(128);
                    declare @DBOBJECTTYPE smallint;

                    if @REVENUETRANSACTIONQUERY is not null begin
                        if not exists(select ID from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
                        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
                        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
                        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
                    end


                    set @SQLTOEXEC = 
                        'select ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT_NF.NAME CONSTITUENTNAME,
                            REVENUE.DATE REVENUEDATE,
                            dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) DESIGNATIONLIST,
                            FIRSTPAYMENT.DATE FIRSTPAYMENT_DATE,
                            FIRSTPAYMENT.AMOUNT FIRSTPAYMENT_AMOUNT,
                            LASTPAYMENT.DATE LASTPAYMENT_DATE,
                            LASTPAYMENT.AMOUNT LASTPAYMENT_AMOUNT,
                            REVENUESCHEDULE.FREQUENCY,
                            count(distinct REVPAYMENTS.ID) TOTALPAYMENTSGIVEN,
                            sum(case @CURRENCYCODE when 0 then REVSPLITPAYMENTS.AMOUNT when 2 then REVSPLITPAYMENTS.TRANSACTIONAMOUNT else REVSPLITPAYMENTS.ORGANIZATIONAMOUNT end) TOTALAMOUNTGIVEN,
                            FIRSTPAYMENT.ISOCURRENCYCODE [FIRSTISOCURRENCYCODE],
                            FIRSTPAYMENT.CURRENCYSYMBOL [FIRSTCURRENCYSYMBOL],
                            FIRSTPAYMENT.CURRENCYSYMBOLDISPLAYSETTINGCODE [FIRSTCURRENCYSYMBOLDISPLAYSETTINGCODE],
                            FIRSTPAYMENT.DECIMALDIGITS [FIRSTDECIMALDIGITS],
                            LASTPAYMENT.ISOCURRENCYCODE [LASTISOCURRENCYCODE],
                            LASTPAYMENT.CURRENCYSYMBOL [LASTCURRENCYSYMBOL],
                            LASTPAYMENT.CURRENCYSYMBOLDISPLAYSETTINGCODE [LASTCURRENCYSYMBOLDISPLAYSETTINGCODE],
                            LASTPAYMENT.DECIMALDIGITS [LASTDECIMALDIGITS],
                            case @CURRENCYCODE when 0 then count(distinct REVSPLITPAYMENTS.BASECURRENCYID) when 2 then count(distinct REVSPLITPAYMENTS.TRANSACTIONCURRENCYID) else 1 end TOTALCURRENCYCOUNT
                        from 
                            dbo.REVENUE with (nolock)
                            inner join dbo.REVENUESPLIT    
                                on REVENUESPLIT.REVENUEID = REVENUE.ID
                            inner join REVENUESCHEDULE
                                on REVENUESCHEDULE.ID = REVENUE.ID
                            inner join CONSTITUENT with (nolock)
                                on REVENUE.CONSTITUENTID = CONSTITUENT.ID
                            left join DESIGNATION
                                on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                            left outer join RECURRINGGIFTACTIVITY
                                on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = REVENUE.ID
                            left outer join REVENUESPLIT REVSPLITPAYMENTS
                                on REVSPLITPAYMENTS.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID and REVSPLITPAYMENTS.DESIGNATIONID = DESIGNATION.ID
                            left outer join REVENUE REVPAYMENTS with (nolock)
                                on REVPAYMENTS.ID = REVSPLITPAYMENTS.REVENUEID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF' + nchar(13);

                            set @SQLTOEXEC = @SQLTOEXEC + 'outer apply
                                    (select top 1 REVFIRST.DATE, 
                                                sum(case @CURRENCYCODE when 0 then REVSPLITFIRST.AMOUNT when 2 then REVSPLITFIRST.TRANSACTIONAMOUNT else REVSPLITFIRST.ORGANIZATIONAMOUNT end) AMOUNT,
                                                FIRSTCURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                                                FIRSTCURRENCYPROPERTIES.CURRENCYSYMBOL,
                                                FIRSTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                                                FIRSTCURRENCYPROPERTIES.DECIMALDIGITS
                                        from REVENUE REVFIRST with (nolock)
                                        inner join dbo.REVENUESPLIT REVSPLITFIRST on REVSPLITFIRST.REVENUEID = REVFIRST.ID
                                        inner join dbo.RECURRINGGIFTACTIVITY RGASUB on RGASUB.PAYMENTREVENUEID = REVSPLITFIRST.ID
                                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then REVSPLITFIRST.BASECURRENCYID when 2 then REVSPLITFIRST.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) FIRSTCURRENCYPROPERTIES
                                        where RGASUB.SOURCEREVENUEID = REVENUE.ID
                                        group by REVFIRST.ID, REVFIRST.DATE, REVFIRST.DATEADDED, FIRSTCURRENCYPROPERTIES.ISO4217, FIRSTCURRENCYPROPERTIES.CURRENCYSYMBOL, FIRSTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, FIRSTCURRENCYPROPERTIES.DECIMALDIGITS
                                        order by REVFIRST.DATE, REVFIRST.DATEADDED) FIRSTPAYMENT
                            outer apply
                                    (select top 1 REVLAST.DATE, 
                                                sum(case @CURRENCYCODE when 0 then REVSPLITLAST.AMOUNT when 2 then REVSPLITLAST.TRANSACTIONAMOUNT else REVSPLITLAST.ORGANIZATIONAMOUNT end) AMOUNT,
                                                LASTCURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                                                LASTCURRENCYPROPERTIES.CURRENCYSYMBOL,
                                                LASTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                                                LASTCURRENCYPROPERTIES.DECIMALDIGITS
                                        from REVENUE REVLAST with (nolock)
                                        inner join dbo.REVENUESPLIT REVSPLITLAST on REVSPLITLAST.REVENUEID = REVLAST.ID
                                        inner join dbo.RECURRINGGIFTACTIVITY RGASUB on RGASUB.PAYMENTREVENUEID = REVSPLITLAST.ID
                                        outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then REVSPLITLAST.BASECURRENCYID when 2 then REVSPLITLAST.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) LASTCURRENCYPROPERTIES
                                        where RGASUB.SOURCEREVENUEID = REVENUE.ID
                                        group by REVLAST.ID, REVLAST.DATE, REVLAST.DATEADDED, LASTCURRENCYPROPERTIES.ISO4217, LASTCURRENCYPROPERTIES.CURRENCYSYMBOL, LASTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, LASTCURRENCYPROPERTIES.DECIMALDIGITS
                                        order by REVLAST.DATE desc, REVLAST.DATEADDED desc) LASTPAYMENT' + nchar(13);

                    if @REVENUETRANSACTIONQUERY is not null
                        set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVENUE.[ID] = SELECTION.[ID]' + nchar(13);

                    set @SQLTOEXEC = @SQLTOEXEC + 
                        'where REVENUE.TRANSACTIONTYPECODE = 2--recurring gift

                            and [dbo].[UFN_RECURRINGGIFT_GETMISSEDINSTALLMENTSCOUNT] (REVENUE.ID, @ASOFDATE) >= @NUMMISSED
                            and (@CONSTITUENTID is null or CONSTITUENT.ID = @CONSTITUENTID)
                            --and not (REVENUE.AMOUNT = 0 and REVENUE.TRANSACTIONAMOUNT > 0)

                            and (@ISADMIN = 1 or 
                                    @APPUSER_IN_NONRACROLE = 1 or
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                            -- Check site security

                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''A9F0E7A8-D2B0-47E7-8B73-C9C2FE21E9FD'', REVSITES.SITEID)
                            )                  
                        group by CONSTITUENT.ID,
                            REVENUE.ID, 
                            CONSTITUENT.KEYNAME,
                            CONSTITUENT_NF.NAME,
                            REVENUE.DATE,
                            FIRSTPAYMENT.DATE,
                            FIRSTPAYMENT.AMOUNT,
                            LASTPAYMENT.DATE,
                            LASTPAYMENT.AMOUNT,
                            REVENUESCHEDULE.FREQUENCY,
                            FIRSTPAYMENT.ISOCURRENCYCODE,
                            FIRSTPAYMENT.CURRENCYSYMBOL,
                            FIRSTPAYMENT.CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            FIRSTPAYMENT.DECIMALDIGITS,
                            LASTPAYMENT.ISOCURRENCYCODE,
                            LASTPAYMENT.CURRENCYSYMBOL,
                            LASTPAYMENT.CURRENCYSYMBOLDISPLAYSETTINGCODE,
                            LASTPAYMENT.DECIMALDIGITS
                        order by 
                            CONSTITUENT.KEYNAME, REVENUE.DATE'

                    exec sp_executesql @SQLTOEXEC, 
                        N'@ASOFDATE datetime, @NUMMISSED int, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @CURRENCYCODE tinyint',
                        @ASOFDATE=@ASOFDATE, @NUMMISSED=@NUMMISSED, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CURRENCYCODE=@CURRENCYCODE;