V_QUERY_GIFTSTATUS
Provides information about the status of ongoing recurring gifts and pledges.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| PLEDGEMISSEDPAYMENTS | int | yes | Pledge missed payments |
| PLEDGEMISSEDFIRSTINSTALLMENT | int | yes | Pledge missed first payment |
| PLEDGEDURATION | int | yes | Pledge duration |
| PLEDGEREMAININGDURATION | int | yes | Pledge remaining duration |
| RECURRINGGIFTMISSEDFIRSTPAYMENT | int | yes | Recurring gift missed first payment |
| MISSEDPAYMENTS | int | yes | Recurring gift missed payments |
| LASTDIRECTDEBITRESULTCODE | nvarchar(10) | yes | Last revenue result code |
| LASTDIRECTDEBITISREJECTED | bit | yes | Last revenue is rejected |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:44 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_GIFTSTATUS AS
select
REVENUE.ID as ID,
case
when REVENUE.TRANSACTIONTYPECODE = 1 then
dbo.UFN_PLEDGE_GETMISSEDTRANSACTIONCOUNT(REVENUE.ID, getdate())
else
null end as [PLEDGEMISSEDPAYMENTS],
case
when REVENUE.TRANSACTIONTYPECODE = 1 then
(select top 1 case when INSTALLMENTPAYMENT.INSTALLMENTID is null and INSTALLMENT.DATE < getdate() then 1 else 0 end
from dbo.INSTALLMENT
left join dbo.INSTALLMENTPAYMENT
on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
where INSTALLMENT.REVENUEID = REVENUE.ID
order by DATE asc)
else
null end as [PLEDGEMISSEDFIRSTINSTALLMENT],
case
when REVENUE.TRANSACTIONTYPECODE = 1 then
datediff(day,coalesce((select min(INSTALLMENT.DATE)
from dbo.INSTALLMENT
where INSTALLMENT.REVENUEID = REVENUE.ID),getdate()),
coalesce((select max(INSTALLMENT.DATE)
from dbo.INSTALLMENT
where INSTALLMENT.REVENUEID = REVENUE.ID),getdate()))
else
null end as [PLEDGEDURATION],
case
when REVENUE.TRANSACTIONTYPECODE = 1 then
datediff(day,coalesce((select case when min(INSTALLMENT.DATE) > getdate() then min(INSTALLMENT.DATE) else getdate() end
from dbo.INSTALLMENT
where INSTALLMENT.REVENUEID = REVENUE.ID),getdate()),
coalesce((select max(INSTALLMENT.DATE)
from dbo.INSTALLMENT
where INSTALLMENT.REVENUEID = REVENUE.ID
and INSTALLMENT.DATE > getdate()),getdate()))
else
null end as [PLEDGEREMAININGDURATION],
case
when REVENUE.TRANSACTIONTYPECODE = 2 then
(select top 1 case when RGA.ID is null then 1 else 0 end
from dbo.REVENUESCHEDULE
left join dbo.RECURRINGGIFTACTIVITY as [RGA]
on REVENUESCHEDULE.ID = [RGA].SOURCEREVENUEID
and REVENUESCHEDULE.STARTDATE = RGA.SCHEDULEDATE
where REVENUESCHEDULE.ID = REVENUE.ID)
else
null end as [RECURRINGGIFTMISSEDFIRSTPAYMENT],
case
when REVENUE.TRANSACTIONTYPECODE = 2 then
dbo.UFN_REVENUE_GETMISSEDTRANSACTIONCOUNT(REVENUE.ID, getdate())
+
dbo.UFN_REVENUE_GETSKIPPEDTRANSACTIONCOUNT(REVENUE.ID, getdate())
else
null end as [MISSEDPAYMENTS],
case
when REVENUE.TRANSACTIONTYPECODE = 1 then
(select top 1 [DIRECTDEBIT].DIRECTDEBITRESULTCODE from
dbo.INSTALLMENT
inner join dbo.INSTALLMENTPAYMENT
on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
inner join dbo.REVENUESPLIT
on INSTALLMENTPAYMENT.PAYMENTID = REVENUESPLIT.ID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESPLIT.REVENUEID
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where INSTALLMENT.REVENUEID = REVENUE.ID
order by INSTALLMENT.DATE desc)
when REVENUE.TRANSACTIONTYPECODE = 2 then
(select top 1 [DIRECTDEBIT].DIRECTDEBITRESULTCODE
from dbo.RECURRINGGIFTACTIVITY as [RGA]
inner join dbo.REVENUESPLIT as [PAYMENT]
on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = [PAYMENT].REVENUEID
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where SOURCEREVENUEID = REVENUE.ID
order by [RGA].SCHEDULEDATE desc)
when REVENUE.TRANSACTIONTYPECODE = 0 then
(select top 1 [DIRECTDEBIT].DIRECTDEBITRESULTCODE
from dbo.REVENUEPAYMENTMETHOD
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID)
else
null end as [LASTDIRECTDEBITRESULTCODE],
cast(case
when REVENUE.TRANSACTIONTYPECODE = 1 then
(select top 1 [DIRECTDEBIT].ISREJECTED from
dbo.INSTALLMENT
inner join dbo.INSTALLMENTPAYMENT
on INSTALLMENT.ID = INSTALLMENTPAYMENT.INSTALLMENTID
inner join dbo.REVENUESPLIT
on INSTALLMENTPAYMENT.PAYMENTID = REVENUESPLIT.ID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESPLIT.REVENUEID
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where INSTALLMENT.REVENUEID = REVENUE.ID
order by INSTALLMENT.DATE desc)
when REVENUE.TRANSACTIONTYPECODE = 2 then
(select top 1 [DIRECTDEBIT].ISREJECTED
from dbo.RECURRINGGIFTACTIVITY as [RGA]
inner join dbo.REVENUESPLIT as [PAYMENT]
on [PAYMENT].ID = [RGA].PAYMENTREVENUEID
inner join dbo.REVENUEPAYMENTMETHOD
on REVENUEPAYMENTMETHOD.REVENUEID = [PAYMENT].REVENUEID
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where SOURCEREVENUEID = REVENUE.ID
order by [RGA].SCHEDULEDATE desc)
when REVENUE.TRANSACTIONTYPECODE = 0 then
(select top 1 [DIRECTDEBIT].ISREJECTED
from dbo.REVENUEPAYMENTMETHOD
inner join dbo.DIRECTDEBITPAYMENTMETHODDETAIL as [DIRECTDEBIT]
on [DIRECTDEBIT].ID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID)
else
0 end as bit) as [LASTDIRECTDEBITISREJECTED]
/*#EXTENSION*/
from dbo.REVENUE