V_QUERY_PLEDGEINSTALLMENTS
Provides the ability to query installment information for pledges.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| PLEDGEID | uniqueidentifier | Pledge ID | |
| DATE | datetime | Date | |
| AMOUNT | money | Amount | |
| TRANSACTIONPLEDGEBALANCE | money | yes | Balance (transaction currency) |
| AMOUNTAPPLIED | money | yes | Amount applied |
| WRITEOFFAMOUNT | money | yes | Write off amount |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| TRANSACTIONAMOUNT | money | Amount (transaction currency) | |
| ORGANIZATIONAMOUNT | money | Amount (organization currency) | |
| BALANCE | money | yes | Balance |
| REVALUEDORGANIZATIONPLEDGEBALANCE | money | yes | Balance (organization currency) |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| RECEIPTAMOUNT | money | ||
| TRANSACTIONRECEIPTAMOUNT | money | ||
| ORGANIZATIONRECEIPTAMOUNT | money |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/25/2013 9:29:42 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.513.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLEDGEINSTALLMENTS AS
select
PLEDGEINSTALLMENT.ID,
FINANCIALTRANSACTION.ID PLEDGEID,
PLEDGEINSTALLMENT.DATE,
PLEDGEINSTALLMENT.AMOUNT,
dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(PLEDGEINSTALLMENT.ID) TRANSACTIONPLEDGEBALANCE,
dbo.UFN_INSTALLMENT_GETINSTALLMENTAMOUNTAPPLIED(PLEDGEINSTALLMENT.ID) AMOUNTAPPLIED,
dbo.UFN_INSTALLMENT_GETINSTALLMENTWRITEOFFAMOUNT(PLEDGEINSTALLMENT.ID) WRITEOFFAMOUNT,
PLEDGEINSTALLMENT.DATEADDED,
PLEDGEINSTALLMENT.DATECHANGED,
PLEDGEINSTALLMENT.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
PLEDGEINSTALLMENT.TRANSACTIONAMOUNT,
PLEDGEINSTALLMENT.ORGANIZATIONAMOUNT,
BALANCES.BASEBALANCE BALANCE,
BALANCES.ORGANIZATIONBALANCE REVALUEDORGANIZATIONPLEDGEBALANCE,
PLEDGEINSTALLMENT.TRANSACTIONCURRENCYID,
PLEDGEINSTALLMENT.BASECURRENCYID,
PLEDGEINSTALLMENT.BASEEXCHANGERATEID,
PLEDGEINSTALLMENT.ORGANIZATIONEXCHANGERATEID,
PLEDGEINSTALLMENT.RECEIPTAMOUNT,
PLEDGEINSTALLMENT.TRANSACTIONRECEIPTAMOUNT,
PLEDGEINSTALLMENT.ORGANIZATIONRECEIPTAMOUNT
/*#EXTENSION*/
from dbo.INSTALLMENT as PLEDGEINSTALLMENT
inner join dbo.FINANCIALTRANSACTION
on PLEDGEINSTALLMENT.REVENUEID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTION.TYPECODE = 1
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = PLEDGEINSTALLMENT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = PLEDGEINSTALLMENT.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 = 1
cross apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(INSTALLMENT.REVENUEID, getdate(), 0) as REVENUEBALANCES
where
REVENUEBALANCES.ID = INSTALLMENT.ID
) as BALANCES on PLEDGEINSTALLMENT.ID = BALANCES.INSTALLMENTID
where
FINANCIALTRANSACTION.DELETEDON is null