V_QUERY_CONSTITUENTUPDATEBATCHEXCEPTIONREPORT

View used for output for Constituent Update Batch Exception Report

Fields

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

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  8/17/2011 2:35:50 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTUPDATEBATCHEXCEPTIONREPORT AS



                select
                    BCU.ID,
                    BCU.CONSTITUENTTYPE as ISORGANIZATION,
                    coalesce(nullif(BCU.LOOKUP_ID, ''), C.LOOKUPID) as CONSTITUENTLOOKUPID,
                    dbo.UFN_NAMEFORMATFUNCTION_SIMPLE(BCU.ID, BCU.KEYNAME, BCU.FIRSTNAME, BCU.MIDDLENAME, (select DESCRIPTION from dbo.TITLECODE where ID = BCU.TITLECODEID), (select DESCRIPTION from dbo.SUFFIXCODE where ID = BCU.SUFFIXCODEID),'','','','','') as CONSTITUENTNAME,
                    BCUA.ADDRESSBLOCK as ADDRESSBLOCK,
                    BCUA.CITY as CITY,
                    (select ABBREVIATION from dbo.STATE where STATE.ID = BCUA.STATEID) as STATE
                from dbo.BATCHCONSTITUENTUPDATE BCU
                left join dbo.BATCHCONSTITUENTUPDATEADDRESSES BCUA on BCUA.ID = (
                        select top(1)
                            ID
                        from
                            dbo.BATCHCONSTITUENTUPDATEADDRESSES 
                        where 
                            BATCHCONSTITUENTUPDATEID = BCU.ID
                        order by ISPRIMARY desc, SEQUENCE asc)
                left join dbo.CONSTITUENT C on C.ID = BCU.PRIMARYRECORDID
                left join dbo.GROUPDATA GD on GD.ID = C.ID