V_FAF_DONATION

Fields

Field Field Type Null Description
ID uniqueidentifier
ConstituentID uniqueidentifier
EventID uniqueidentifier
FirstName nvarchar(50)
LastName nvarchar(100)
Address nvarchar(150)
AddressType nvarchar(100)
City nvarchar(50)
STATE nvarchar(50)
ZIP nvarchar(12)
Country nvarchar(100)
Email nvarchar(100)
PhoneNumber nvarchar(100)
PhoneType nvarchar(100)
Title nvarchar(100)
Suffix nvarchar(100)
Amount money
IsAnonymousGift bit
DoNotCall bit
DoNotMail bit
DoNotEmail bit
CreditcardType nvarchar(100)
CheckNumber nvarchar(20)
ReceiptNumber int
CheckDate char(8) yes
GiftDate datetime
InboundChannel nvarchar(100)
SOLICITATIONCHANNNEL nvarchar(12)
IsOnlineGift int
TransactionType nvarchar(27)
PaymentMethod nvarchar(14)
PostStatus nvarchar(11)
Tributee nvarchar(154)
TributeMessage nvarchar(255)
TributeType nvarchar(100)
MatchingGiftClaimed money
MatchingGiftPaid money
MatchingGiftOrganization nvarchar(100)
Recognition nvarchar(154)
ISPREVIOUSDONOR int
RegistrantRole varchar(17)
GroupName nvarchar(100)
GroupType nvarchar(9)
CompanyName nvarchar(100)
CompanyAddress nvarchar(150)
CompanyAddressType nvarchar(100)
CompanyCity nvarchar(50)
COMPANYSTATE nvarchar(50)
COMPANYZIP nvarchar(12)
COMPANYPhone nvarchar(100)
CompanyEmail nvarchar(100)
Companywebaddress UDT_WEBADDRESS
IsCompanyDonation int
RevenueDateChanged datetime
RevenueChangedBy nvarchar(128) yes
DonorLookupID nvarchar(100)
RevenueLookUpID nvarchar(100)
REVENUEID uniqueidentifier

Definition

Copy 

    CREATE view dbo.V_FAF_DONATION as 
    -- select statements here 1

  select distinct
     fr.ID as ID,
     c.ID as ConstituentID,
     e.ID as EventID,
     FirstName =
        case c.ISORGANIZATION
           when 0 then isnull(c.FIRSTNAME, '')
           else ''
        end,
     LastName =
        case c.ISORGANIZATION
           when 0 then isnull(c.KEYNAME,'')
           else ''
        end,
     [Address] =
        case c.ISORGANIZATION
           when 0 then isnull(a.ADDRESSBLOCK,'')
           else ''
        end,
     AddressType =
        case c.ISORGANIZATION
           when 0 then isnull(atc.[DESCRIPTION], '')
           else ''
        end,
     City =
         case c.ISORGANIZATION
           when 0 then isnull(a.[CITY], '')
           else ''
         end,
     [STATE] =
         case c.ISORGANIZATION
           when 0 then isnull(st.ABBREVIATION, '') 
           else ''
         end,
     ZIP =
         case c.ISORGANIZATION
           when 0 then isnull(a.POSTCODE, '')
           else ''
         end,
      Country =    
         case c.ISORGANIZATION
           when 0 then isnull(ct.DESCRIPTION, '')
           else ''
         end,
      Email =
         case c.ISORGANIZATION
           when 0 then isnull(ea.EMAILADDRESS, '')
           else ''
         end,
     PhoneNumber =
         case c.ISORGANIZATION
           when 0 then isnull(p.NUMBER, '')    
           else ''
         end,
     isnull(ptc.[DESCRIPTION], '') as PhoneType,   
     isnull(tc.[DESCRIPTION], '') as Title,
     isnull(sc.[DESCRIPTION], '') as Suffix, 
     rs.AMOUNT as Amount,
     R_EXT.GIVENANONYMOUSLY as IsAnonymousGift,
     isnull(p.DONOTCALL, 0) As DoNotCall, 
     isnull(a.DONOTMAIL, 0) As DoNotMail, 
     isnull(ea.DONOTEMAIL, 0) As DoNotEmail,
     isnull(ctp.[DESCRIPTION], '') As CreditcardType,
     isnull(ckpmd.CHECKNUMBER, '') As CheckNumber,
     isnull(rpt.RECEIPTNUMBER, '') as ReceiptNumber,
     CheckDate =  
             case isdate(ckpmd.CHECKDATE) 
               when 1 then ckpmd.CHECKDATE  
               else null  
             end,    
     fr.DATEADDED    As GiftDate,
     isnull(cc.[DESCRIPTION], '') as InboundChannel,
     isnull(fccl.Channel, '') as SOLICITATIONCHANNNEL,
     IsOnlineGift = 
         case rp.PAYMENTMETHODCODE
           when 2 Then 1
           else 0
         end,
     isnull(fr.[TYPE], '') as TransactionType, 
     isnull(rp.PAYMENTMETHOD, '') as PaymentMethod,
     isnull(fr.PostStatus, '') as PostStatus,
     isnull(c2.NAME, '') as Tributee,
     isnull(t.TRIBUTETEXT, '') as TributeMessage,
     isnull(ttc.Description, '') as TributeType,
     isnull(MatchingGift.ClaimAmount, 0) as  MatchingGiftClaimed,
     isnull(MatchingGift.PaidAmount, 0) as MatchingGiftPaid,
     isnull(MatchingGift.CompanyName, '') as MatchingGiftOrganization,
     isnull(C3.NAME, '') as Recognition,
     ISPREVIOUSDONOR =
         case isnull(PriorEvent.CurrentEVENTID, '00000000-0000-0000-0000-000000000000')
         when '00000000-0000-0000-0000-000000000000'  then 0
         else 1
         end,
     isnull(Registrant.[Role], 'Individual') as [RegistrantRole],
     isnull(Registrant.Groupname,'') As GroupName,
     isnull(Registrant.GroupType, '') As GroupType,
     CompanyName =
        case c.ISORGANIZATION
           when 1 then isnull(c.KEYNAME, '')
           else ''
        end,
     CompanyAddress =
         case c.ISORGANIZATION
           when 1 then isnull(a.ADDRESSBLOCK, '')
           else ''
        end,
     CompanyAddressType =
         case c.ISORGANIZATION
           when 1 then isnull(atc.[DESCRIPTION], '')
           else ''
        end,    
     CompanyCity =
         case c.ISORGANIZATION
           when 1 then isnull(a.[CITY], '')
           else ''
         end,
     COMPANYSTATE =
         case c.ISORGANIZATION
           when 1 then isnull(st.ABBREVIATION, '')
           else ''
         end,
     COMPANYZIP =
         case c.ISORGANIZATION
           when 1 then isnull(a.POSTCODE, '')
           else ''
         end,  

     COMPANYPhone =
         case c.ISORGANIZATION
           when 1 then isnull(p.NUMBER, '')     
           else ''
         end,

     CompanyEmail =
         case c.ISORGANIZATION
           when 1 then isnull(ea.EMAILADDRESS, '')
           else ''
         end, 
     isnull(c.WEBADDRESS, '') As Companywebaddress,

     IsCompanyDonation =
        case c.ISORGANIZATION
          when 1 Then 1
          else 0
        end,
     fr.DateChanged as RevenueDateChanged,
     ca.UserName as RevenueChangedBy,
     isnull(c.LOOKUPID, '') as DonorLookupID, 
     isnull(fr.USERDEFINEDID, '') as RevenueLookUpID,
     fr.ID as REVENUEID
  from dbo.FINANCIALTRANSACTION fr (nolock)    
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI (nolock)    
      on FTLI.FINANCIALTRANSACTIONID = fr.ID 
      inner join dbo.REVENUESPLIT_EXT RS_EXT (nolock)    
        on RS_EXT.ID = FTLI.ID 
    inner join dbo.REVENUE_EXT R_EXT (nolock)    
      on fr.ID = R_EXT.ID  
    inner Join dbo.[CONSTITUENT] c (nolock)    
          on fr.CONSTITUENTID = c.id
    left join dbo.CHANGEAGENT ca (nolock)    
      on fr.ChangedByID = ca.ID
    left outer join dbo.TITLECODE tc (nolock)    
          on c.TITLECODEID = tc.ID 
    left outer join dbo.SUFFIXCODE sc (nolock)    
          on c.SUFFIXCODEID = sC.ID
    left Join dbo.[ADDRESS] a (nolock)    
        on c.ID = a.CONSTITUENTID and a.ISPRIMARY = 1
    left outer join dbo.[STATE] st (nolock)    
       on a.STATEID = st.ID   
    left Join dbo.ADDRESSTYPECODE atc    (nolock)    
       on atc.ID = a.ADDRESSTYPECODEID
      left join dbo.EMAILADDRESS ea (nolock)    
         on ea.CONSTITUENTID = c.ID and ea.ISPRIMARY = 1
      left join dbo.phone p (nolock)    
             on p.CONSTITUENTID = c.ID and p.ISPRIMARY = 1
      left join dbo.PHONETYPECODE ptc (nolock)    
             on p.PHONETYPECODEID = ptc.ID 
      left join dbo.COUNTRY ct (nolock)    
             on ct.ID = a.COUNTRYID 
      left Join dbo.[REVENUETRIBUTE] rt (nolock)    
             on fr.ID = rt.REVENUEID
      left join dbo.TRIBUTE t (nolock)    
             on rt.TRIBUTEID = t.ID
    left join dbo.TRIBUTETYPECODE ttc (nolock) 
             on ttc.ID = t.TRIBUTETYPECODEID
      left join dbo.CONSTITUENT c2  (nolock)     -- tributee
           on t.TRIBUTEEID = c2.ID 
    left join dbo.REVENUEPAYMENTMETHOD rp (nolock)    
        on fr.ID = rp.REVENUEID
    left join dbo.CHECKPAYMENTMETHODDETAIL ckpmd (nolock)    
        on ckpmd.ID = rp.ID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL cpmd (nolock)    
        on rp.ID = cpmd.ID
    left join dbo.CREDITTYPECODE ctp (nolock)    
        on ctp.ID = cpmd.CREDITTYPECODEID
    inner join dbo.REVENUESPLIT rs (nolock)    
        on fr.ID = rs.REVENUEID 
    left join dbo.REVENUERECOGNITION rr (nolock)    
        on rs.ID = rr.REVENUESPLITID
    left join dbo.CONSTITUENT C3  (nolock)     -- recognition
        on rr.CONSTITUENTID = C3.Id 
    left join dbo.REVENUERECEIPT rpt (nolock)    
        on rpt.REVENUEID = fr.ID
    left Join  -- matching gift 
       (select il.REVENUEID As MatchingGiftRevenueID, 
            rm.MGSOURCEREVENUEID As SourceRevenueID, il.AMOUNT As ClaimAmount, 
            (Select Sum(isnull(AMOUNT,0)) From dbo.INSTALLMENTPAYMENT ip (nolock)    
                   where ip.INSTALLMENTID = il.ID
             ) As PaidAmount,
            c.KEYNAME As CompanyName
        from INSTALLMENT il (nolock)    
           inner join REVENUEMATCHINGGIFT rm (nolock)  on rm.ID = il.REVENUEID 
           inner join REVENUE r (nolock)  on r.ID = rm.ID
           inner join CONSTITUENT c (nolock) on c.ID = r.CONSTITUENTID and c.ISORGANIZATION=1) MatchingGift
        on MatchingGift.SourceRevenueID = fr.ID
    inner join dbo.[EVENT] e (nolock)    
        on e.APPEALID = R_EXT.APPEALID
    left join -- registrant
       (select distinct
                 rg.CONSTITUENTID,
                 rg.EVENTID,
                 tft.NAME as Groupname,
                 ts.[TYPE] As GroupType,
                 case when tftc.ID is not null and ts.TYPECODE = 3 then 'Head of household'  
                 when tftc.ID is null  and ts.TYPECODE = 3 then 'Household member'  
                 when tftc.ID is not null and ts.TYPECODE = 1 then 'Team leader'  
                 when tftc.ID is null and ts.TYPECODE = 1 then 'Team member'  
                 when tftc.ID is not null and ts.TYPECODE = 2 then 'Company leader'  
                 when tftc.ID is null and ts.TYPECODE = 2 then 'Individual'  
                 when tftc.ID is null then 'Individual' end as [Role]  
              from dbo.REGISTRANT rg With (NOLOCK)
                inner join dbo.TEAMFUNDRAISER TF With (NOLOCK) on TF.CONSTITUENTID = rg.CONSTITUENTID
                inner join dbo.TEAMFUNDRAISINGTEAMMEMBER tfm (nolock) on tfm.TEAMFUNDRAISERID = tf.ID
                inner join dbo.TEAMFUNDRAISINGTEAM tft (nolock)  on tfm.TEAMFUNDRAISINGTEAMID = tft.ID
                inner join dbo.TEAMEXTENSION ts (nolock)  on ts.TEAMFUNDRAISINGTEAMID = tft.ID
                left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN tftc (nolock)  on tftc.CONSTITUENTID = tf.CONSTITUENTID
            ) Registrant
           on Registrant.EVENTID = e.ID and C3.ID = Registrant.CONSTITUENTID 
     left join dbo.FAFEVENTCOMMUNICATIONCHANNEL fccl (nolock) 
         on fccl.TYPECODE = 1 and fccl.TYPEGUID = fr.ID 
     left join dbo.CHANNELCODE cc (nolock) 
         on cc.ID = R_EXT.CHANNELCODEID
     -- prior event
     left join
          (select distinct fr.CONSTITUENTID, 
                  et.EVENTID As CurrentEventID
              from dbo.EVENTEXTENSION et (nolock) 
                  inner join dbo.[Event] e (nolock) 
                       on et.PRIORYEAREVENTID = e.ID
                  inner join dbo.REVENUE fr (nolock) 
                       on fr.APPEALID = e.APPEALID) priorEvent
          on e.ID = priorEvent.CurrentEventID and c.ID = priorEvent.CONSTITUENTID
    --where rs.[APPLICATION] = 'Donation' and rp.PAYMENTMETHOD <> 'None'
    where (fr.TYPECODE = 0 and rs.APPLICATIONCODE in (0, 7, 17) OR fr.TYPECODE IN (1,2) )
      and e.ISACTIVE = 0 and fr.[TYPE] <> 'Pending Gift'