V_QUERY_PLEDGEINSTALLMENTSPLITS
Provides ability to query on installment split information for pledges.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| PLEDGEINSTALLMENTID | uniqueidentifier | Pledge Installment ID | |
| AMOUNT | money | Amount | |
| TRANSACTIONPLEDGEBALANCE | money | yes | Balance (transaction currency) |
| AMOUNTAPPLIED | money | yes | Amount applied |
| WRITEOFFAMOUNT | money | yes | Write off amount |
| DESIGNATIONID | uniqueidentifier | Designation system record ID | |
| DESIGNATIONNAME | nvarchar(512) | Designation name | |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:52 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLEDGEINSTALLMENTSPLITS AS
select
PLEDGEINSTALLMENTSPLIT.ID,
INSTALLMENT.ID PLEDGEINSTALLMENTID,
PLEDGEINSTALLMENTSPLIT.AMOUNT,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(PLEDGEINSTALLMENTSPLIT.ID) TRANSACTIONPLEDGEBALANCE,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITAMOUNTAPPLIED(PLEDGEINSTALLMENTSPLIT.ID) AMOUNTAPPLIED,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITWRITEOFFAMOUNT(PLEDGEINSTALLMENTSPLIT.ID) WRITEOFFAMOUNT,
DESIGNATION.ID DESIGNATIONID,
DESIGNATION.NAME as [DESIGNATIONNAME],
PLEDGEINSTALLMENTSPLIT.DATEADDED,
PLEDGEINSTALLMENTSPLIT.DATECHANGED,
PLEDGEINSTALLMENTSPLIT.TSLONG,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
PLEDGEINSTALLMENTSPLIT.TRANSACTIONAMOUNT,
PLEDGEINSTALLMENTSPLIT.ORGANIZATIONAMOUNT,
BALANCES.BASEBALANCE BALANCE,
BALANCES.ORGANIZATIONBALANCE REVALUEDORGANIZATIONPLEDGEBALANCE,
PLEDGEINSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
PLEDGEINSTALLMENTSPLIT.BASECURRENCYID,
PLEDGEINSTALLMENTSPLIT.BASEEXCHANGERATEID,
PLEDGEINSTALLMENTSPLIT.ORGANIZATIONEXCHANGERATEID
/*#EXTENSION*/
from
dbo.INSTALLMENTSPLIT as PLEDGEINSTALLMENTSPLIT
inner join dbo.INSTALLMENT on PLEDGEINSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
inner join dbo.REVENUE on INSTALLMENT.REVENUEID = REVENUE.ID and REVENUE.TRANSACTIONTYPECODE = 1
inner join dbo.DESIGNATION on DESIGNATION.ID = PLEDGEINSTALLMENTSPLIT.DESIGNATIONID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = PLEDGEINSTALLMENTSPLIT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = PLEDGEINSTALLMENTSPLIT.CHANGEDBYID
cross apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES(REVENUE.ID, getdate(), 0) BALANCES
where BALANCES.ID = PLEDGEINSTALLMENTSPLIT.ID