V_SEARCH_MGRELATIONREVENUE
Search screen for finding a matching gift relation revenue for an organization.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| CONSTITUENTID | uniqueidentifier | ||
| TYPECODE | tinyint | ||
| CONSTITUENTNAME | nvarchar(154) | yes | |
| AMOUNT | money | ||
| TYPE | nvarchar(27) | yes | |
| GIFTDATE | datetimeoffset | ||
| DESIGNATIONNAME | nvarchar(512) | ||
| CONSTITUENTLOOKUPID | nvarchar(100) | yes | |
| TRANSACTIONCURRENCYID | uniqueidentifier |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/1/2024 11:53:16 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3800.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_SEARCH_MGRELATIONREVENUE AS
select
FINANCIALTRANSACTIONLINEITEM.ID,
CONSTITUENT.ID as CONSTITUENTID,
FINANCIALTRANSACTION.TYPECODE,
CONSTITUENT.NAME as CONSTITUENTNAME,
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as AMOUNT,
FINANCIALTRANSACTION.TYPE,
FINANCIALTRANSACTION.DATE as GIFTDATE,
DESIGNATION.NAME as DESIGNATIONNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
from RELATIONSHIP
inner join CONSTITUENT on CONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
inner join FINANCIALTRANSACTION on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
inner join FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
where
not exists (select * from REVENUEMATCHINGGIFT where MGSOURCEREVENUEID = FINANCIALTRANSACTION.ID) and
FINANCIALTRANSACTION.TYPECODE = 0 and
REVENUESPLIT_EXT.APPLICATIONCODE IN (0,1,4,5)