USP_EXCHANGECONTACTSYNC_GETCONSTITUENTINFORMATION

            CREATE procedure dbo.USP_EXCHANGECONTACTSYNC_GETCONSTITUENTINFORMATION
            (
                @CONSTITUENTID uniqueidentifier
            )
            as
                set nocount on;

                select
                    CONSTITUENT.ID,
                    CONSTITUENT.FIRSTNAME,
                    CONSTITUENT.KEYNAME,
                    dbo.UFN_TITLECODE_GETDESCRIPTION(CONSTITUENT.TITLECODEID) as TITLE,
                    dbo.UFN_SUFFIXCODE_GETDESCRIPTION(CONSTITUENT.SUFFIXCODEID) as SUFFIX,
                    coalesce((select dbo.UFN_CONSTITUENT_BUILDNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) from dbo.RELATIONSHIP where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and ISSPOUSE = 1),'') as SPOUSENAME,
                    CONSTITUENT.BIRTHDATE,
                    dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTPHONES_TOITEMLISTXML(CONSTITUENT.ID) as PHONES,
                    dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTADDRESSES_TOITEMLISTXML(CONSTITUENT.ID) as ADDRESSES,
                    dbo.UFN_EXCHANGECONTACTSYNC_GETCONSTITUENTEMAILADDRESSES_TOITEMLISTXML(CONSTITUENT.ID) as EMAILADDRESSES,
                    CONSTITUENT.WEBADDRESS,
                    CONSTITUENT.NICKNAME,
                    coalesce((select dbo.UFN_CONSTITUENT_BUILDNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) from dbo.RELATIONSHIP where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and ISPRIMARYBUSINESS = 1),'') as COMPANYNAME,
                    (select max(DATECHANGEDJOIN.DATECHANGED)
                        FROM (select C.DATECHANGED, C.ID AS CONSTITUENTID from dbo.CONSTITUENT C 
                        UNION ALL 
                        select DATECHANGED, CONSTITUENTID from dbo.ADDRESS where  ISPRIMARY = 1
                        UNION ALL
                        select DATECHANGED, CONSTITUENTID from dbo.PHONE where ISPRIMARY = 1
                        UNION ALL
                        select DATECHANGED, CONSTITUENTID from dbo.EMAILADDRESS where ISPRIMARY = 1) AS DATECHANGEDJOIN where DATECHANGEDJOIN.CONSTITUENTID = CONSTITUENT.ID) as DATELASTCHANGED,
                    CONSTITUENT.MIDDLENAME
                from
                    dbo.CONSTITUENT
                where
                    CONSTITUENT.ID = @CONSTITUENTID;