USP_DATALIST_PLEDGEPAYMENTBYTRANSACTION

                CREATE procedure dbo.USP_DATALIST_PLEDGEPAYMENTBYTRANSACTION
                (
                    @ID uniqueidentifier
                )
                as
                set nocount on;

                select 
                    'Payment',
                    Convert(Date, [PAYMENTREVENUE].DATE) as DATE,
                    sum(INSTALLMENTPAYMENT.AMOUNT),
                    CASE CONSTITUENT.ISORGANIZATION WHEN 1 THEN
                        CASE CONSTITUENT.KEYNAMEPREFIX WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.KEYNAMEPREFIX + ' ' +  CONSTITUENT.KEYNAME END
                    ELSE
                        CASE CONSTITUENT.ISGROUP WHEN 1 THEN
                            CASE CONSTITUENT.DISPLAYNAME WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.DISPLAYNAME END
                        ELSE
                            CASE CONSTITUENT.FIRSTNAME WHEN '' THEN '' ELSE CONSTITUENT.FIRSTNAME + ' ' END
                            +
                            CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE LEFT(CONSTITUENT.MIDDLENAME,1) + '. ' END
                            + 
                            CONSTITUENT.KEYNAME
                        END
                    END as NAME,
                    [REVENUEPAYMENTMETHOD].PAYMENTMETHOD,
                    0 as [TYPECODE],
                    [PAYMENTREVENUE].ID,
                    REVENUE.TRANSACTIONCURRENCYID
                from dbo.INSTALLMENTPAYMENT
                    inner join dbo.FINANCIALTRANSACTION as REVENUE on REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID                
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as [PAYMENTREVENUESPLIT] on [PAYMENTREVENUESPLIT].ID = INSTALLMENTPAYMENT.PAYMENTID                
                    inner join dbo.FINANCIALTRANSACTION as PAYMENTREVENUE on PAYMENTREVENUE.ID = PAYMENTREVENUESPLIT.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUEPAYMENTMETHOD on PAYMENTREVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                    inner join dbo.CONSTITUENT as CONSTITUENT on PAYMENTREVENUE.CONSTITUENTID = CONSTITUENT.ID
                where REVENUE.ID = @ID
                group by
                    [PAYMENTREVENUE].DATE,
                    CASE CONSTITUENT.ISORGANIZATION WHEN 1 THEN
                        CASE CONSTITUENT.KEYNAMEPREFIX WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.KEYNAMEPREFIX + ' ' +  CONSTITUENT.KEYNAME END
                    ELSE
                        CASE CONSTITUENT.ISGROUP WHEN 1 THEN
                            CASE CONSTITUENT.DISPLAYNAME WHEN '' THEN CONSTITUENT.KEYNAME ELSE CONSTITUENT.DISPLAYNAME END
                        ELSE
                            CASE CONSTITUENT.FIRSTNAME WHEN '' THEN '' ELSE CONSTITUENT.FIRSTNAME + ' ' END
                            +
                            CASE CONSTITUENT.MIDDLENAME WHEN '' THEN '' ELSE LEFT(CONSTITUENT.MIDDLENAME,1) + '. ' END
                            + 
                            CONSTITUENT.KEYNAME
                        END
                    END,
                    [REVENUEPAYMENTMETHOD].PAYMENTMETHOD,
                    [PAYMENTREVENUE].ID,
                    REVENUE.TRANSACTIONCURRENCYID 

                union

                select
                    'Write-off',
                    Convert(Date, WRITEOFF.DATE) as DATE,
                    sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT),
                    '',
                    '',
                    1 as [TYPECODE],
                    null,
                    REVENUE.TRANSACTIONCURRENCYID
                from dbo.INSTALLMENTWRITEOFF 
                    inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.ID = INSTALLMENTWRITEOFF.WRITEOFFID
                    inner join dbo.FINANCIALTRANSACTION as REVENUE on REVENUE.ID = WRITEOFF.PARENTID
                where REVENUE.ID = @ID
                group by WRITEOFF.DATE, REVENUE.TRANSACTIONCURRENCYID
                order by DATE, [TYPECODE]