V_QUERY_CONSTITUENTUPDATEBATCHCONTROLREPORT

View used for output for Constituent Update Batch Control Report.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
CONSTITUENTLOOKUPID	nvarchar(100)	yes	Constituent lookup 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_CONSTITUENTUPDATEBATCHCONTROLREPORT AS



                select
                    CONSTITUENT.ID,
                    CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
                    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