V_QUERY_RECURRINGGIFTWRITEOFF
Provides the ability to query pledge write-offs.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| REVENUEID | uniqueidentifier | Revenue ID | |
| DATE | date | Date | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| AMOUNT | money | yes | Amount |
| 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 |
| WRITEOFFREASONCODE | nvarchar(63) | yes | Reason code |
| ORGANIZATIONAMOUNT | money | yes | Amount (organization currency) |
| TRANSACTIONAMOUNT | money | yes | Amount (transaction currency) |
| ORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| TYPE | nvarchar(9) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:28:30 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RECURRINGGIFTWRITEOFF AS
select
RECURRINGGIFTWRITEOFF.ID,
RECURRINGGIFTWRITEOFF.REVENUEID,
RECURRINGGIFTWRITEOFF.DATE,
RECURRINGGIFTWRITEOFF.DATEADDED,
RECURRINGGIFTWRITEOFF.DATECHANGED,
RECURRINGGIFTWRITEOFF.TSLONG,
[RECURRINGGIFTINSTALLMENTWRITEOFFTOTAL].[AMOUNT],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
case RECURRINGGIFTWRITEOFF.TYPECODE
when 0 then WRITEOFFREASONCODE.CODE + ' - ' + WRITEOFFREASONCODE.DESCRIPTION
when 1 then RECURRINGGIFTSTATUSREASONCODE.CODE + ' - ' + RECURRINGGIFTSTATUSREASONCODE.DESCRIPTION
end as WRITEOFFREASONCODE,
[RECURRINGGIFTINSTALLMENTWRITEOFFTOTAL].[ORGANIZATIONAMOUNT],
[RECURRINGGIFTINSTALLMENTWRITEOFFTOTAL].[TRANSACTIONAMOUNT],
REVENUE.ORGANIZATIONEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID,
REVENUE.BASECURRENCYID,
REVENUE.TRANSACTIONCURRENCYID,
RECURRINGGIFTWRITEOFF.TYPE
/*#EXTENSION*/
from dbo.RECURRINGGIFTWRITEOFF
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = RECURRINGGIFTWRITEOFF.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = RECURRINGGIFTWRITEOFF.CHANGEDBYID
left join dbo.WRITEOFFREASONCODE on WRITEOFFREASONCODE.ID = RECURRINGGIFTWRITEOFF.REASONCODEID
left join dbo.RECURRINGGIFTSTATUSREASONCODE on RECURRINGGIFTSTATUSREASONCODE.ID = RECURRINGGIFTWRITEOFF.SKIPREASONCODEID
left join dbo.REVENUE on REVENUE.ID = RECURRINGGIFTWRITEOFF.REVENUEID
outer apply
(
select
iw.WRITEOFFID,
case RECURRINGGIFTWRITEOFF.TYPECODE when 0 then sum(iw.AMOUNT) else sum(i.AMOUNT) end as [AMOUNT],
case RECURRINGGIFTWRITEOFF.TYPECODE when 0 then sum(iw.TRANSACTIONAMOUNT) else sum(i.TRANSACTIONAMOUNT) end as [TRANSACTIONAMOUNT],
case RECURRINGGIFTWRITEOFF.TYPECODE when 0 then sum(iw.ORGANIZATIONAMOUNT) else sum(i.ORGANIZATIONAMOUNT) end as [ORGANIZATIONAMOUNT]
from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw
left join dbo.RECURRINGGIFTINSTALLMENT i on i.ID = iw.RECURRINGGIFTINSTALLMENTID
where iw.WRITEOFFID = RECURRINGGIFTWRITEOFF.ID
group by iw.WRITEOFFID
) as [RECURRINGGIFTINSTALLMENTWRITEOFFTOTAL]