USP_SMARTFIELD_RECURRINGGIFTNEXTTRANSACTIONDATE

            CREATE procedure dbo.USP_SMARTFIELD_RECURRINGGIFTNEXTTRANSACTIONDATE
            (
                @RECURRINGGIFTSELECTIONID uniqueidentifier,
                @ASOF datetime
            )
            as
                set nocount on;

                declare @FTSELECTCLAUSE as nvarchar(max) = N'select FINANCIALTRANSACTION.ID' + char(13);
                declare @FTFROMCLAUSE as nvarchar(max) = N'from dbo.FINANCIALTRANSACTION' + char(13);
                declare @FTWHERECLAUSE as nvarchar(max) = N'where FINANCIALTRANSACTION.TYPECODE = 2 and FINANCIALTRANSACTION.DELETEDON is null' + char(13); -- TYPECODE = 2 is for recurring gifts


                declare @RGCTEPRESQL as nvarchar(50) = N'with RECURRINGGIFT_CTE as (' + char(13);
                declare @RGCTEPOSTSQL as nvarchar(100) = N')' + char(13);
                declare @RGCTESQL as nvarchar(max);

                declare @ASOFSTRING as nvarchar(50) = convert(nvarchar(50), @ASOF, 121);
                declare @ASOFSQL as nvarchar(500) =
                    N'select RECURRINGGIFT_CTE.ID' + char(13) +
                    N'from RECURRINGGIFT_CTE' + char(13) +
                    N'where RECURRINGGIFT_CTE.DATECHANGED > ''' + @ASOFSTRING + '''' + char(13) +
                    N'union' + char(13) +
                    N'select RECURRINGGIFT_CTE.ID' + char(13) +
                    N'from RECURRINGGIFT_CTE' + char(13) +
                    N'left join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = RECURRINGGIFT_CTE.ID' + char(13) +
                    N'where REVENUESPLIT.DATECHANGED > ''' + @ASOFSTRING + '''' + char(13)
                ;

                declare @RGNTDPREWRAPSQL as nvarchar(500) =
                    N'select' + char(13) +
                    N'  RECURRINGGIFT.ID,' + char(13) +
                    N'  case RGS.FIRSTINSTALLMENTCODE' + char(13) +
                    N'    when 0 then SCHEDULE.NEXTTRANSACTIONDATE' + char(13) +
                    N'    else (select DATE from dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(RECURRINGGIFT.ID,null))' + char(13) +
                    N'  end NEXTTRANSACTIONDATE' + char(13) +
                    N'from (' + char(13)
                ;
                declare @RGNTDPOSTWRAPSQL as nvarchar(500) =
                    N') as RECURRINGGIFT' + char(13) +
                    N'left join dbo.REVENUESCHEDULE as SCHEDULE on SCHEDULE.ID = RECURRINGGIFT.ID' + char(13) +
                    N'outer apply dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT() RGS' + char(13)
                ;

                declare @SQL nvarchar(max);
                declare @ENDSTATEMENT nvarchar(5) = N';' + char(13);

                if @RECURRINGGIFTSELECTIONID is not null
                begin
                    set @FTFROMCLAUSE += N'inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(''' + convert(nvarchar(50), @RECURRINGGIFTSELECTIONID) + ''') S on S.ID = FINANCIALTRANSACTION.ID' + char(13);
                end

                if @ASOF is null
                begin
                    set @SQL = @RGNTDPREWRAPSQL + @FTSELECTCLAUSE + @FTFROMCLAUSE + @FTWHERECLAUSE + @RGNTDPOSTWRAPSQL + @ENDSTATEMENT;
                end
                else
                begin
                    set @FTSELECTCLAUSE += N',FINANCIALTRANSACTION.DATECHANGED' + char(13);

                    set @RGCTESQL = @RGCTEPRESQL + @FTSELECTCLAUSE + @FTFROMCLAUSE + @FTWHERECLAUSE + @RGCTEPOSTSQL;
                    set @SQL = @RGCTESQL + @RGNTDPREWRAPSQL + @ASOFSQL + @RGNTDPOSTWRAPSQL + @ENDSTATEMENT;
                end

                exec sp_executesql @SQL;