USP_MAPENTITY_CONSTITUENT_TO_CONSTITUENT

                    CREATE procedure dbo.USP_MAPENTITY_CONSTITUENT_TO_CONSTITUENT(
                        @RECORDID uniqueidentifier,
                        @ADDRESSID uniqueidentifier = null
                    ) as
                        set nocount on;

                        select 
                            c.ID MAPPEDRECORDID,
                            c.NAME DESCRIPTION,
                            a.ID ADDRESSID,
                            a.ADDRESSBLOCK,
                            a.CITY,
                            s.ABBREVIATION STATE,
                            a.POSTCODE,
                            COUNTRY.ABBREVIATION COUNTRY,
                            case when ac.LATITUDE is null then pc.LATITUDE else ac.LATITUDE end LATITUDE,
                            case when ac.LONGITUDE is null then pc.LONGITUDE else ac.LONGITUDE end LONGITUDE,
                            null as GROUPID,
                            null as GROUPNAME
                        from dbo.CONSTITUENT c
                        left join dbo.ADDRESS a    on a.CONSTITUENTID = c.ID
                        left join dbo.STATE s    on a.STATEID = s.ID
                        left join dbo.COUNTRY on COUNTRY.ID = a.COUNTRYID
                        left join dbo.POSTCODECOORDINATES  pc on a.POSTCODE = pc.POSTCODE
                        left join dbo.ADDRESSCOORDINATES ac    on ac.ADDRESSID = a.ID
                        where 
                            c.ID = @RECORDID and 
                            (a.ID = @ADDRESSID or (@ADDRESSID is null and a.ISPRIMARY = 1)) and
                            ac.LATITUDE is not null and 
                            ac.LONGITUDE is not null and
                            not ac.PENDINGGEOCODE  = 1 and
                            not ac.INVALIDGEOCODE = 1