V_SEARCH_OUTSTANDING_PLEDGE

Fields

Field	Field Type	Null
ID	uniqueidentifier
LOOKUPID	nvarchar(100)	yes
name	nvarchar(154)	yes
TRANSACTIONTYPE	tinyint
AMOUNT	money	yes
PAYMENTMETHOD	nvarchar(14)	yes
date	datetimeoffset
DETAILS	nvarchar(max)	yes
REVENUELOOKUPID	nvarchar(100)
TRANSACTIONCURRENCYID	uniqueidentifier
Definition

Copy
/*
Generated by Blackbaud AppFx Platform
Date:  9/1/2024 11:53:09 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3800.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_SEARCH_OUTSTANDING_PLEDGE AS



select REVENUE.ID [ID]
    ,CONSTITUENT.LOOKUPID
    ,CONSTITUENT.name
    ,REVENUE.TYPECODE as TRANSACTIONTYPE
    ,sum(REVENUE.TRANSACTIONAMOUNT) AMOUNT
    ,REVENUEPAYMENTMETHOD.PAYMENTMETHOD
    ,REVENUE.date
    ,dbo.UDA_BUILDLIST(' ' + case REX.APPLICATIONCODE
            when 0
                then N'Donation'
            when 1
                then N'Event registration'
            when 2
                then N'Pledge'
            when 3
                then N'Recurring gift'
            when 4
                then N'Other'
            when 5
                then N'Membership'
            when 6
                then N'Planned gift'
            when 7
                then N'Matching gift'
            when 8
                then N'Grant award'
            when 9
                then N'Admission'
            when 10
                then N'Order'
            when 11
                then N'Miscellaneous'
            when 12
                then N'Auction purchase'
            when 13
                then N'Donor challenge'
            when 15
                then N'Event sponsorship'
            when 16
                then N'Merchandise'
            when 17
                then N'Pending gift'
            when 18
                then N'Membership add-on'
            end + '(' + convert(nvarchar(20), REVENUESPLIT.BASEAMOUNT) + ' ' + CURRENCY.ISO4217 + ')') as [DETAILS]
    ,REVENUE.USERDEFINEDID [REVENUELOOKUPID]
    ,REVENUE.TRANSACTIONCURRENCYID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT as REX on REVENUESPLIT.ID = REX.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.TRANSACTIONCURRENCYID
where dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) > 0
group by REVENUE.ID
    ,CONSTITUENT.LOOKUPID
    ,CONSTITUENT.name
    ,REVENUE.TYPECODE
    ,PAYMENTMETHOD
    ,date
    ,REVENUE.USERDEFINEDID
    ,REVENUE.TRANSACTIONCURRENCYID;