V_QUERY_INSTALLMENTPLANINSTALLMENTS

Fields

Field	Field Type	Null
ID	uniqueidentifier
PLANID	uniqueidentifier
DATE	datetime
AMOUNT	money
TRANSACTIONPLEDGEBALANCE	money	yes
AMOUNTAPPLIED	money	yes
WRITEOFFAMOUNT	money	yes
DATEADDED	datetime
DATECHANGED	datetime
TSLONG	bigint	yes
ADDEDBY_APPLICATION	nvarchar(200)	yes
ADDEDBY_USERNAME	nvarchar(128)	yes
CHANGEDBY_APPLICATION	nvarchar(200)	yes
CHANGEDBY_USERNAME	nvarchar(128)	yes
TRANSACTIONAMOUNT	money
ORGANIZATIONAMOUNT	money
BALANCE	money	yes
REVALUEDORGANIZATIONPLEDGEBALANCE	money	yes
TRANSACTIONCURRENCYID	uniqueidentifier	yes
BASECURRENCYID	uniqueidentifier	yes
BASEEXCHANGERATEID	uniqueidentifier	yes
ORGANIZATIONEXCHANGERATEID	uniqueidentifier	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  3/25/2013 9:29:41 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.513.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_INSTALLMENTPLANINSTALLMENTS AS



                select
                    PLANINSTALLMENT.ID,
                    FINANCIALTRANSACTION.ID as PLANID,
                    PLANINSTALLMENT.DATE,
                    PLANINSTALLMENT.AMOUNT,
                    dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(PLANINSTALLMENT.ID) TRANSACTIONPLEDGEBALANCE,
                    dbo.UFN_INSTALLMENT_GETINSTALLMENTAMOUNTAPPLIED(PLANINSTALLMENT.ID) AMOUNTAPPLIED,
                    dbo.UFN_INSTALLMENT_GETINSTALLMENTWRITEOFFAMOUNT(PLANINSTALLMENT.ID) WRITEOFFAMOUNT,
                    PLANINSTALLMENT.DATEADDED,
                    PLANINSTALLMENT.DATECHANGED,
                    PLANINSTALLMENT.TSLONG,
                    [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
                    [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
                    [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
                    [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
                    PLANINSTALLMENT.TRANSACTIONAMOUNT,
                    PLANINSTALLMENT.ORGANIZATIONAMOUNT,
                    BALANCES.BASEBALANCE BALANCE,
                    BALANCES.ORGANIZATIONBALANCE REVALUEDORGANIZATIONPLEDGEBALANCE,
                    PLANINSTALLMENT.TRANSACTIONCURRENCYID,
                    PLANINSTALLMENT.BASECURRENCYID,
                    PLANINSTALLMENT.BASEEXCHANGERATEID,
                    PLANINSTALLMENT.ORGANIZATIONEXCHANGERATEID
                    /*#EXTENSION*/ 
                from dbo.INSTALLMENT as PLANINSTALLMENT
                    inner join dbo.FINANCIALTRANSACTION
                        on PLANINSTALLMENT.REVENUEID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 15
                    left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = PLANINSTALLMENT.ADDEDBYID
                    left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = PLANINSTALLMENT.CHANGEDBYID
                    left join
                        (
                            select
                                INSTALLMENT.ID as INSTALLMENTID,
                                REVENUEBALANCES.BASEBALANCE,
                                REVENUEBALANCES.ORGANIZATIONBALANCE
                            from
                                dbo.INSTALLMENT
                                inner join dbo.FINANCIALTRANSACTION
                                    on INSTALLMENT.REVENUEID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 15
                                cross apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(INSTALLMENT.REVENUEID, getdate(), 0) as REVENUEBALANCES
                            where
                                REVENUEBALANCES.ID = INSTALLMENT.ID
                        ) as BALANCES on PLANINSTALLMENT.ID = BALANCES.INSTALLMENTID
                where
                    FINANCIALTRANSACTION.DELETEDON is null