UFN_AMPROIMPORT_CONSTITUENTDATA

            CREATE function dbo.UFN_AMPROIMPORT_CONSTITUENTDATA
            (
                @CONSTITUENTID uniqueidentifier
            )
            returns table 
            as
            return
            (
                select top 1
                    CONSTITUENT.SEQUENCEID - 9999999 as [MAPPINGID],
                    CONSTITUENT.LOOKUPID,
                    coalesce(TITLECODE.DESCRIPTION, N'') as [TITLE],
                    CONSTITUENT.FIRSTNAME,
                    CONSTITUENT.KEYNAME,
                    coalesce(ADDRESS.ADDRESSBLOCK, N'') as [ADDRESSBLOCK],
                    coalesce(ADDRESS.CITY, N'') as [CITY],
                    coalesce(ADDRESS.POSTCODE, N'') as [POSTCODE],
                    coalesce(STATE.ABBREVIATION, N'') as [STATE],
                    coalesce(COUNTRY.DESCRIPTION, N'') as [COUNTRY],
                    coalesce(PHONE.NUMBER, N'') as [PHONE],
                    coalesce(EMAILADDRESS.EMAILADDRESS, N'') as [EMAILADDRESS],
                    STATE.ID as [STATEID],
                    COUNTRY.ID as [COUNTRYID]
                from dbo.CONSTITUENT
                left join dbo.TITLECODE on TITLECODE.ID = CONSTITUENT.TITLECODEID
                left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                left join dbo.STATE on STATE.ID = ADDRESS.STATEID
                left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
                left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1 
                left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
                where CONSTITUENT.ID = @CONSTITUENTID
            )