V_QUERY_DESIGNATIONLEVELDONORS

Provides the ability to query fundraising purpose donor information.2

Fields

Field	Field Type	Null	Description
DESIGNATIONLEVELID	uniqueidentifier		Designation level ID
CONSTITUENTID	uniqueidentifier	yes	Constituent ID
REVENUEID	uniqueidentifier		Revenue
NAME	nvarchar(154)	yes	Name
DATE	datetime	yes	Date
AMOUNT	money		Amount
ISBENEFACTOR	int	yes	Is benefactor
ORGANIZATIONAMOUNT	money		Amount (organization currency)
TRANSACTIONAMOUNT	money		Amount (transaction currency)
ORGANIZATIONEXCHANGERATEID	uniqueidentifier	yes	Organization exchange rate
BASEEXCHANGERATEID	uniqueidentifier	yes	Base exchange rate
BASECURRENCYID	uniqueidentifier	yes	Base currency ID
TRANSACTIONCURRENCYID	uniqueidentifier	yes	Transaction currency ID
ISCORPUS	int	yes	Is corpus

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  2/1/2011 9:06:11 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_DESIGNATIONLEVELDONORS AS



                select  DL.ID as DESIGNATIONLEVELID, 
                        R.CONSTITUENTID,                        
                        R.ID as REVENUEID,
                        dbo.UFN_CONSTITUENT_BUILDNAME(R.CONSTITUENTID) NAME,
                        R.DATE,
                        R.AMOUNT,
                        COALESCE(DESIGNATIONLEVELDONORINFORMATION.ISBENEFACTOR, 0) as ISBENEFACTOR,
                        R.ORGANIZATIONAMOUNT,
                        R.TRANSACTIONAMOUNT,
                        R.ORGANIZATIONEXCHANGERATEID,
                        R.BASEEXCHANGERATEID,
                        R.BASECURRENCYID,
                        R.TRANSACTIONCURRENCYID,
                        COALESCE(DESIGNATIONLEVELREVENUEINFORMATION.ISORIGINALFUNDING, 0) as ISCORPUS
                from dbo.DESIGNATIONLEVEL DL
                    inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                                 D.DESIGNATIONLEVEL2ID = DL.ID or
                                 D.DESIGNATIONLEVEL3ID = DL.ID or             
                                 D.DESIGNATIONLEVEL4ID = DL.ID or
                                 D.DESIGNATIONLEVEL5ID = DL.ID)
                    inner join dbo.REVENUESPLIT RS on D.ID = RS.DESIGNATIONID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                    left outer join dbo.DESIGNATIONLEVELDONORINFORMATION on DESIGNATIONLEVELDONORINFORMATION.CONSTITUENTID = R.CONSTITUENTID and DESIGNATIONLEVELDONORINFORMATION.DESIGNATIONLEVELID = DL.ID
                    left outer join dbo.DESIGNATIONLEVELREVENUEINFORMATION on DESIGNATIONLEVELREVENUEINFORMATION.REVENUEID = R.ID and DESIGNATIONLEVELREVENUEINFORMATION.DESIGNATIONLEVELID = DL.ID
                where (R.TRANSACTIONTYPECODE in (1,3,7) or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,3)))