V_QUERY_EVENTDONOR

Query view used for event donor.

Fields

Field Field Type Null Description
ID uniqueidentifier System record ID
AMOUNT money Amount
CONSTITUENTID uniqueidentifier yes Constituent ID
REGISTRANTID uniqueidentifier yes Registrant ID
KEYNAME nvarchar(100) Last Name
FIRSTNAME nvarchar(50) First name
MIDDLENAME nvarchar(50) Middle name
MAIDENNAME nvarchar(100) Maiden name
NICKNAME nvarchar(50) Nick name
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
DATEADDED datetime Date added
DATECHANGED datetime Date changed
TSLONG bigint yes Timestamp value
EventID uniqueidentifier
DESIGNATIONLIST nvarchar(3000) yes

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:09:26 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTDONOR AS



        select R.ID ID
    ,RS.AMOUNT
    ,R.CONSTITUENTID
  ,REGISTRANT.ID REGISTRANTID
  ,CONSTITUENT.KEYNAME
    ,CONSTITUENT.FIRSTNAME
    ,CONSTITUENT.MIDDLENAME
    ,CONSTITUENT.MAIDENNAME
    ,CONSTITUENT.NICKNAME

  --system fields

    ,ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
    ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
    CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
    CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
    R.DATEADDED,
    R.DATECHANGED,
    R.TSLONG,

  EVENT.ID EventID,
  dbo.UFN_REVENUE_DESIGNATIONLIST(R.ID) as [DESIGNATIONLIST]

    from dbo.REVENUE R
    inner join dbo.REVENUESPLIT RS ON R.ID = RS.REVENUEID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID=R.CONSTITUENTID
    inner join dbo.EVENT on EVENT.APPEALID = R.APPEALID
    left join dbo.REGISTRANT on REGISTRANT.CONSTITUENTID = constituent.ID 
    left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = R.ADDEDBYID
    left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = R.CHANGEDBYID        
    where 
    /*include Donation, Pledge, Recurring gift*/
    (R.TRANSACTIONTYPECODE = 9 AND RS.APPLICATIONCODE = 0) -- 'Offline Donation'

    OR (R.TRANSACTIONTYPECODE = 0 AND RS.APPLICATIONCODE = 17) -- 'Offline Donation'

    OR (R.TRANSACTIONTYPECODE = 0 AND RS.APPLICATIONCODE = 0) -- 'Donation' 

    OR (R.TRANSACTIONTYPECODE = 1 AND RS.APPLICATIONCODE = 0) -- 'Pledge' 

    OR (R.TRANSACTIONTYPECODE = 0 AND RS.APPLICATIONCODE = 2) -- 'Pledge' 

    OR (R.TRANSACTIONTYPECODE = 2 AND RS.APPLICATIONCODE = 0) -- 'Recurring gift'                 

    OR (R.TRANSACTIONTYPECODE = 0 AND RS.APPLICATIONCODE = 3) -- 'Recurring gift'