V_CONSTITUENTALLNAMES

Fields

Field	Field Type	Null
ID	uniqueidentifier
FIRSTNAME	nvarchar(4000)	yes
KEYNAME	nvarchar(4000)	yes
NAMETYPECODE	int
ISORGANIZATION	bit
ISGROUP	bit
ALIASID	uniqueidentifier	yes
MIDDLENAME	nvarchar(4000)	yes
TITLECODEID	uniqueidentifier	yes
SUFFIXCODEID	uniqueidentifier	yes
Definition

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



                select
          ID,
          upper(ltrim(rtrim(replace(FIRSTNAME,'.','')))) FIRSTNAME,
          upper(ltrim(rtrim(replace(replace(KEYNAME,'.',''),'''','')))) KEYNAME,
          0 NAMETYPECODE,
          ISORGANIZATION,
          ISGROUP,
          null ALIASID,
          upper(ltrim(rtrim(replace(MIDDLENAME,'.','')))) MIDDLENAME,
          TITLECODEID,
          SUFFIXCODEID
        from dbo.CONSTITUENT
        where ISCONSTITUENT = 1
        and (ISINACTIVE = 0 or (ISINACTIVE = 1 and ID not in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)))                
        union all
        -- Maiden name

                select
          ID,
          upper(ltrim(rtrim(replace(FIRSTNAME,'.','')))),
          upper(ltrim(rtrim(replace(replace(MAIDENNAME,'.',''),'''','')))),
          1,
          ISORGANIZATION,
          ISGROUP,
          null,
          upper(ltrim(rtrim(replace(MIDDLENAME,'.','')))) MIDDLENAME,
          TITLECODEID,
          SUFFIXCODEID
        from dbo.CONSTITUENT
        where MAIDENNAME <> ''
        and ISCONSTITUENT = 1
        and (ISINACTIVE = 0 or (ISINACTIVE = 1 and ID not in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)))                
        union all
        -- Nickname

                select
          ID,
          upper(ltrim(rtrim(replace(NICKNAME,'.','')))),
          upper(ltrim(rtrim(replace(replace(KEYNAME,'.',''),'''','')))),
          2,
          ISORGANIZATION,
          ISGROUP,
          null,
          upper(ltrim(rtrim(replace(MIDDLENAME,'.','')))) MIDDLENAME,
          TITLECODEID,
          SUFFIXCODEID
        from dbo.CONSTITUENT
        where ltrim(rtrim(replace(NICKNAME,'.',''))) <> ''
        and ISCONSTITUENT = 1
        and (ISINACTIVE = 0 or (ISINACTIVE = 1 and ID not in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)))                
        union all
        -- Aliases

                select
          CONSTITUENT.ID,
          ALIASFIRSTNAME,
          ALIASKEYNAME,
          3,
          CONSTITUENT.ISORGANIZATION,
          CONSTITUENT.ISGROUP,
          ALIAS.ID,
          upper(ltrim(rtrim(replace(ALIAS.MIDDLENAME,'.','')))),
          ALIAS.TITLECODEID,
          ALIAS.SUFFIXCODEID
        from dbo.ALIAS
        inner join dbo.SEARCHCONSTITUENTALIASTYPE on SEARCHCONSTITUENTALIASTYPE.ID = ALIAS.ALIASTYPECODEID
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = ALIAS.CONSTITUENTID
        cross apply (select upper(ltrim(rtrim(replace(ALIAS.FIRSTNAME,'.','')))) ALIASFIRSTNAME,
                            upper(ltrim(rtrim(replace(replace(ALIAS.KEYNAME,'.',''),'''','')))) ALIASKEYNAME,
                            upper(ltrim(rtrim(replace(CONSTITUENT.FIRSTNAME,'.','')))) CONSTITUENTFIRSTNAME,
                            upper(ltrim(rtrim(replace(replace(CONSTITUENT.KEYNAME,'.',''),'''','')))) CONSTITUENTKEYNAME) as CLEANNAME
        where (ALIASFIRSTNAME<>CONSTITUENTFIRSTNAME and ALIASFIRSTNAME<>'') or
              (ALIASKEYNAME<>CONSTITUENTKEYNAME and ALIASKEYNAME<>'')
        and CONSTITUENT.ISCONSTITUENT = 1        
        and (CONSTITUENT.ISINACTIVE = 0 or (CONSTITUENT.ISINACTIVE = 1 and CONSTITUENT.ID not in (select SOURCEID from dbo.CONSTITUENTMERGEOPERATIONS)))