USP_SMARTFIELD_PLEDGEINSTALLMENTSBALANCE

            CREATE procedure dbo.USP_SMARTFIELD_PLEDGEINSTALLMENTSBALANCE
            (
                @PLEDGESELECTIONID 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 = 1 and FINANCIALTRANSACTION.DELETEDON is  null' + char(13); -- TYPECODE = 1 is for pledges


                declare @PLEDGECTEPRESQL as nvarchar(50) = N'with PLEDGE_CTE as (' + char(13);
                declare @PLEDGECTEPOSTSQL as nvarchar(10) = N')' + char(13);
                declare @PLEDGECTESQL as nvarchar(max);

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

                declare @PIBPREWRAPSQL as nvarchar(200) =
                    N'select' + char(13) +
                    N'  INSTALLMENT.ID as ID,' + char(13) +
                    N'  REVENUEBALANCES.BASEBALANCE as BALANCE' + char(13) +
                    N'from (' + char(13)
                ;

                declare @PIBPOSTWRAPSQL as nvarchar(500) =
                    N') as PLEDGE' + char(13) +
                    N'outer apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(PLEDGE.ID, getdate(), 0) as REVENUEBALANCES' + char(13) +
                    N'inner join dbo.INSTALLMENT on REVENUEBALANCES.ID = INSTALLMENT.ID' + char(13)
                ;

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

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

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

                    set @PLEDGECTESQL = @PLEDGECTEPRESQL + @FTSELECTCLAUSE + @FTFROMCLAUSE + @FTWHERECLAUSE + @PLEDGECTEPOSTSQL;
                    set @SQL = @PLEDGECTESQL + @PIBPREWRAPSQL + @ASOFSQL + @PIBPOSTWRAPSQL + @ENDSTATEMENT;
                end

                exec sp_executesql @SQL;