V_QUERY_REVENUE_PLEDGEBALANCE

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:37:23 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUE_PLEDGEBALANCE AS



                -- NOTE:  This view's logic was inlined into RevenueCommitments.DataList.xml so any functionality changes made here

                -- should also be made there.

                select
                    FINANCIALTRANSACTION.ID,
                    cast(
                        FINANCIALTRANSACTION.TRANSACTIONAMOUNT -   
                            (coalesce((    
                                select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)   
                                from dbo.INSTALLMENTSPLITPAYMENT   
                                where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID)
                            , 0) + 
                            coalesce((
                                select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)   
                                from dbo.INSTALLMENTSPLITWRITEOFF   
                                    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID   
                                where INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTION.ID)
                            , 0)
                        ) as money
                    ) BALANCE  
                from dbo.FINANCIALTRANSACTION
                where FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)