USP_DATALIST_PLEDGEPAYMENT

                create procedure dbo.USP_DATALIST_PLEDGEPAYMENT
                (
                    @ID uniqueidentifier
                )
                as
                set nocount on;

                select 
                    'Payment',
                    [PAYMENTREVENUE].DATE,
                    INSTALLMENTPAYMENT.AMOUNT,
                    [CONSTITUENT].NAME,
                    [REVENUEPAYMENTMETHOD].PAYMENTMETHOD,
                    0 as [TYPECODE],
                    [PAYMENTREVENUE].ID
                from dbo.INSTALLMENTPAYMENT
                inner join dbo.REVENUE on REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID                
                inner join dbo.REVENUE as [PAYMENTREVENUE] on [PAYMENTREVENUE].ID = INSTALLMENTPAYMENT.PAYMENTID
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENTREVENUE.ID
                inner join dbo.CONSTITUENT on CONSTITUENT.ID = [PAYMENTREVENUE].CONSTITUENTID
                where REVENUE.ID = @ID
                union
                select
                    'Write-off',
                    WRITEOFF.DATE,
                    INSTALLMENTWRITEOFF.AMOUNT,
                    '',
                    '',
                    1 as [TYPECODE],
                    null
                from dbo.INSTALLMENTWRITEOFF 
                inner join dbo.WRITEOFF on WRITEOFF.ID = INSTALLMENTWRITEOFF.WRITEOFFID
                inner join dbo.REVENUE on REVENUE.ID = WRITEOFF.REVENUEID
                where REVENUE.ID = @ID
                order by DATE, [TYPECODE]