V_QUERY_CONSTITUENTBATCHCONTROLREPORT

View used for output for Constituent Batch Control Report.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
ISORGANIZATION	int		Is organization
CONSTITUENTNAME	nvarchar(700)	yes	Constituent name
ADDRESSBLOCK	nvarchar(150)	yes	Address
CITY	nvarchar(50)	yes	City
STATE	nvarchar(50)	yes	State

Definition

Copy

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



select
    CONSTITUENT.ID,
    case 
        when CONSTITUENT.ISGROUP = 0
            then CONSTITUENT.ISORGANIZATION
        else 
            case
                when GROUPDATA.GROUPTYPECODE = 0
                    then 2
                else 3
            end
    end ISORGANIZATION,
    dbo.UFN_NAMEFORMATFUNCTION_SIMPLE(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, (select DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID), (select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.SUFFIXCODEID),'','','','','') as CONSTITUENTNAME,
    ADDRESS.ADDRESSBLOCK,
    ADDRESS.CITY,
    STATE.ABBREVIATION as STATE
from 
    dbo.CONSTITUENT
    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
    left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    left join dbo.STATE on ADDRESS.STATEID = STATE.ID