V_QUERY_INSTALLMENTWRITEOFF
Provides the ability to query write-offs.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| REVENUEID | uniqueidentifier | Revenue ID | |
| DATE | datetime | yes | Date |
| POSTDATE | datetime | yes | Post date |
| POSTSTATUS | nvarchar(11) | yes | Post status |
| REASON | nvarchar(300) | Details | |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:16 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_INSTALLMENTWRITEOFF AS
select
FINANCIALTRANSACTION.ID,
isnull(FINANCIALTRANSACTION.PARENTID,'00000000-0000-0000-0000-000000000000') as REVENUEID,
convert(datetime,FINANCIALTRANSACTION.DATE) as DATE,
convert(datetime,FINANCIALTRANSACTION.POSTDATE) as POSTDATE,
cast(case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 'Posted' when 1 then 'Not posted' when 3 then 'Do not post' end as nvarchar(11))as POSTSTATUS,
WRITEOFF_EXT.REASON,
FINANCIALTRANSACTION.DATEADDED,
FINANCIALTRANSACTION.DATECHANGED,
FINANCIALTRANSACTION.TSLONG,
case
when FINANCIALTRANSACTION.TYPECODE = 7 then
(
select sum(AMOUNT)
from dbo.WRITEOFFSPLIT
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
else
(
select sum(AMOUNT)
from dbo.INSTALLMENTWRITEOFF
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
end as [AMOUNT],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
WRITEOFFREASONCODE.CODE + ' - ' + WRITEOFFREASONCODE.DESCRIPTION as WRITEOFFREASONCODE,
case
when FINANCIALTRANSACTION.TYPECODE = 7 then
(
select sum(ORGANIZATIONAMOUNT)
from dbo.WRITEOFFSPLIT
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
else
(
select sum(ORGANIZATIONAMOUNT)
from dbo.INSTALLMENTSPLITWRITEOFF
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
end as [ORGANIZATIONAMOUNT],
case
when FINANCIALTRANSACTION.TYPECODE = 7 then
(
select sum(TRANSACTIONAMOUNT)
from dbo.WRITEOFFSPLIT
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
else
(
select sum(TRANSACTIONAMOUNT)
from dbo.INSTALLMENTSPLITWRITEOFF
where WRITEOFFID = FINANCIALTRANSACTION.ID
)
end as [TRANSACTIONAMOUNT],
FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
FINANCIALTRANSACTION.BASEEXCHANGERATEID,
case when FINANCIALTRANSACTION.DELETEDON is null then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, BASECURRENCYFORFTVIEW.BASECURRENCYID) else null end as BASECURRENCYID,
case when FINANCIALTRANSACTION.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end as TRANSACTIONCURRENCYID
/*#EXTENSION*/
from dbo.FINANCIALTRANSACTION FINANCIALTRANSACTION
inner join dbo.WRITEOFF_EXT on FINANCIALTRANSACTION.ID = WRITEOFF_EXT.ID and FINANCIALTRANSACTION.TYPECODE = 20 and FINANCIALTRANSACTION.DELETEDON is null
left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FINANCIALTRANSACTION.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FINANCIALTRANSACTION.CHANGEDBYID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I BASECURRENCYFORFTVIEW with (noexpand) on FINANCIALTRANSACTION.ID = BASECURRENCYFORFTVIEW.FINANCIALTRANSACTIONID
left join dbo.WRITEOFFREASONCODE on WRITEOFFREASONCODE.ID = WRITEOFF_EXT.REASONCODEID