USP_DATALIST_INSTALLMENTSBYTRANSACTIONWITHOUTRECEIPTAMOUNT

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

                    select * from 
                    (
                        select 
                            INSTALLMENT.ID as ID,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 0,
                            ACTIVITY = 'Installment '  + cast(INSTALLMENT.SEQUENCE as nvarchar(3)),
                            cast(INSTALLMENT.DATE as datetime) as Date,
                            INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
                            PAIDBY = null,
                            PAYMETHOD = NULL,
                            BALANCE = coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID),0),
                            ISVISIBLE = 
                                case 
                                    when @PAYMENTSWRITEOFFSONLY = 1 then 0
                                    else 1
                                end,
                            TRANSACTIONID = null,
                            RECORDTYPE = 0,
                            dbo.UDA_BUILDLIST(DISTINCT coalesce(DESIGNATION.NAME, 'None (Earned income)')) DESIGNATIONLIST,
                            PARENT = null,
                            INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
                            INSTALLMENT.ID as CHILDORPARENTID
                        from
                            dbo.INSTALLMENT
                        inner join 
                            dbo.FINANCIALTRANSACTION on INSTALLMENT.REVENUEID = FINANCIALTRANSACTION.ID
                        inner join
                            dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join 
                            dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID 
                        left join 
                            dbo.DESIGNATION on DESIGNATION.ID = INSTALLMENTSPLIT.DESIGNATIONID
                        where 
                            FINANCIALTRANSACTION.ID = @ID
                            and FINANCIALTRANSACTION.DELETEDON is null
                        group by 
                            INSTALLMENT.ID,
                            INSTALLMENT.SEQUENCE,
                            INSTALLMENT.DATE,
                            INSTALLMENT.TRANSACTIONAMOUNT,
                            INSTALLMENT.TRANSACTIONCURRENCYID

                        union all

                        select 
                            null as ID,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 1,
                            ACTIVITY = 'Payment',
                            cast(PAYMENT.DATE as datetime) as Date,
                            sum(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT,  -- In installment transaction currency

                            PAIDBY = NF.NAME,
                            PAYMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                            BALANCE = null,
                            ISVISIBLE = 1,
                            PAYMENT.ID as TRANSACTIONID,
                            RECORDTYPE = 1,
                            '' DESIGNATIONLIST,
                            PARENT = case when @PAYMENTSWRITEOFFSONLY = 1 then null
                                        else INSTALLMENT.ID    end,
                            INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
                            PAYMENT.ID as CHILDORPARENTID
                        from 
                            dbo.INSTALLMENT
                        inner join
                            dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
                        inner join
                            dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                        inner join
                            dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
                        inner join
                            dbo.REVENUESPLIT_EXT on PAYMENTSPLIT.ID = REVENUESPLIT_EXT.ID
                        inner join
                            dbo.FINANCIALTRANSACTION PAYMENT on PAYMENTSPLIT.FINANCIALTRANSACTIONID = PAYMENT.ID
                        inner join
                            dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
                        inner join
                            dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENT.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PAYMENT.CONSTITUENTID) NF
                        where INSTALLMENT.REVENUEID = @ID
                            and PAYMENT.DELETEDON is null
                            and PAYMENTSPLIT.DELETEDON is null
                            and PAYMENTSPLIT.TYPECODE <> 1
                        group by 
                            INSTALLMENT.SEQUENCE,
                            cast(PAYMENT.DATE as datetime),
                            NF.NAME,
                            REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                            PAYMENT.ID,
                            INSTALLMENT.ID,
                            INSTALLMENT.TRANSACTIONCURRENCYID

                        union all

                        select
                            null as ID,
                            INSTALLMENT.SEQUENCE,
                            LISTORDER = 2,
                            ACTIVITY = 'Write-off',
                            cast(WRITEOFF.DATE as datetime) as Date,
                            -- Needs to be updated when we address installment split writeoffs

                            sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT,
                            PAIDBY = null,
                            PAYMETHOD = null,
                            BALANCE = null,
                            ISVISIBLE = 1,
                            ID = null,
                            RECORDTYPE = 2,
                            '' DESIGNATIONLIST,
                            PARENT = case when @PAYMENTSWRITEOFFSONLY = 1 then null
                                        else INSTALLMENT.ID    end,
                            INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
                            WRITEOFF.ID as CHILDORPARENTID
                        from
                            dbo.INSTALLMENT
                        inner join 
                            dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                        inner join
                            dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                        inner join
                            dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        where 
                            INSTALLMENT.REVENUEID = @ID
                        group by 
                            INSTALLMENT.SEQUENCE,
                            WRITEOFF.DATE,
                            WRITEOFF.ID,
                            INSTALLMENT.ID,
                            INSTALLMENT.TRANSACTIONCURRENCYID

                    ) as INSTALLMENTDATA
                    where ISVISIBLE = 1
                    order by SEQUENCE, LISTORDER, Date;
                end