USP_DATALIST_INSTALLMENTS

                CREATE procedure dbo.USP_DATALIST_INSTALLMENTS 
                (
                    @ID uniqueidentifier,
                    @PAYMENTSWRITEOFFSONLY bit = 0
                )
                as 
                begin
                    set nocount on;

                    select * from 
                    (
                        select 
                            INSTALLMENT.ID,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 0,
                            ACTIVITY = 'Installment '  + cast(INSTALLMENT.SEQUENCE as nvarchar(3)),
                            INSTALLMENT.DATE as Date,
                            INSTALLMENT.AMOUNT,
                            PAIDBY = null,
                            PAYMETHOD = NULL,
                            BALANCE = coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID),0),
                            ISVISIBLE = 
                                case 
                                    when @PAYMENTSWRITEOFFSONLY = 1 then 0
                                    else 1
                                end,
                            REVENUE.ID as RevenueID,
                            RECORDTYPE = 0
                        from
                            dbo.INSTALLMENT
                        inner join 
                            dbo.REVENUE on INSTALLMENT.REVENUEID = REVENUE.ID
                        where 
                            REVENUE.ID = @ID

                        union all

                        select 
                            null,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 1,
                            ACTIVITY = space(10) + 'Payment',
                            PAYMENT.DATE as Date,
                            INSTALLMENTPAYMENT.AMOUNT,
                            PAIDBY = CONSTITUENT.NAME,
                            PAYMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                            BALANCE = null,
                            ISVISIBLE = 1,
                            PAYMENT.ID as RevenueID,
                            RECORDTYPE = 1
                        from 
                            dbo.REVENUE                        
                        inner join
                            dbo.INSTALLMENT on REVENUE.ID = INSTALLMENT.REVENUEID
                        inner join
                            dbo.INSTALLMENTPAYMENT on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
                        inner join
                            dbo.REVENUESPLIT PAYMENTSPLIT on INSTALLMENTPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
                        inner join
                            dbo.REVENUE PAYMENT on PAYMENTSPLIT.REVENUEID = PAYMENT.ID
                        inner join 
                            dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENT.ID
                        inner join
                            dbo.CONSTITUENT on PAYMENT.CONSTITUENTID = CONSTITUENT.ID
                        where REVENUE.ID = @ID

                        union all

                        select
                            null,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 2,
                            ACTIVITY = space(10) + 'Write-off',
                            WRITEOFF.DATE as Date,
                            INSTALLMENTWRITEOFF.AMOUNT,
                            PAIDBY = null,
                            PAYMETHOD = null,
                            BALANCE = null,
                            ISVISIBLE = 1,
                            REVENUEID = null,
                            RECORDTYPE = 2
                        from
                            dbo.REVENUE
                        inner join
                            dbo.INSTALLMENT on REVENUE.ID = INSTALLMENT.REVENUEID
                        inner join
                            dbo.INSTALLMENTWRITEOFF on INSTALLMENT.ID = INSTALLMENTWRITEOFF.INSTALLMENTID
                        inner join
                            dbo.WRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        where 
                            REVENUE.ID = @ID
                    ) as INSTALLMENTDATA
                    where ISVISIBLE = 1
                    order by SEQUENCE, LISTORDER, Date;
                end