USP_SEARCHLIST_MEMBERSHIPDUESBATCHDUPLICATECONSTITUENT

                CREATE procedure dbo.USP_SEARCHLIST_MEMBERSHIPDUESBATCHDUPLICATECONSTITUENT
                (
                    @BILLTOCONSTITUENTID uniqueidentifier = null,
                    @BILLTOCONSTITUENTLOOKUPID uniqueidentifier = null,
                    @NEWBILLTOCONSTITUENT xml = null,
                    @MAXROWS smallint = 500,
                    @OVERALLMATCHTHRESHOLD decimal(20, 4) = null,
                    @AUTOMATCHTHRESHOLD decimal(20, 4) = null,
                    @EDITCONSTITUENTCONTEXT nvarchar(110) = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @CONSTITUENTDUPLICATEMATCHINGENABLED bit = null
                )
                as
                    set nocount on

                    declare @NAME nvarchar(100),
                            @KEYNAME nvarchar(100),
                            @FIRSTNAME nvarchar(50),
                            @POSTCODE nvarchar(12),
                            @ADDRESSBLOCK nvarchar(150),
                            @ISORGANIZATION bit,
                            @ISGROUP bit,
                            @TITLECODEID uniqueidentifier,
                            @MIDDLENAME nvarchar(50),
                            @SUFFIXCODEID uniqueidentifier,
                            @COUNTRYID uniqueidentifier,
                            @EMAILADDRESS nvarchar(100),
                            @PHONENUMBER nvarchar(100),
                            @ADDRESSTYPECODEID uniqueidentifier,
                            @PHONETYPECODEID uniqueidentifier,
                            @EMAILTYPECODEID uniqueidentifier

                    if (@BILLTOCONSTITUENTID is null and @BILLTOCONSTITUENTLOOKUPID is null)
                    begin
                        select
                            @NAME = LASTNAME,
                            @KEYNAME = LASTNAME,
                            @FIRSTNAME = FIRSTNAME,
                            @POSTCODE = ADDRESS_POSTCODE,
                            @ADDRESSBLOCK = ADDRESS_ADDRESSBLOCK,
                            @ISORGANIZATION = ISORGANIZATION,
                            @ISGROUP = ISGROUP,
                            @TITLECODEID = TITLECODEID,
                            @MIDDLENAME = MIDDLENAME,
                            @SUFFIXCODEID = SUFFIXCODEID,
                            @COUNTRYID = ADDRESS_COUNTRYID,
                            @EMAILADDRESS = EMAILADDRESS_EMAILADDRESS,
                            @PHONENUMBER = PHONE_NUMBER,
                            @ADDRESSTYPECODEID = ADDRESS_ADDRESSTYPECODEID,
                            @PHONETYPECODEID = PHONE_PHONETYPECODEID,
                            @EMAILTYPECODEID = EMAILADDRESS_EMAILADDRESSTYPECODEID                
                        from dbo.UFN_REVENUEBATCH_GETNEWCONSTITUENT_FROMITEMLISTXML(@NEWBILLTOCONSTITUENT)
                    end

                        if @KEYNAME is null
                        begin
                        if exists (select ID from dbo.BATCHREVENUECONSTITUENT where ID = @BILLTOCONSTITUENTID)
                        begin
                            select
                                @KEYNAME = KEYNAME,
                                @NAME = NAME,
                                @FIRSTNAME = FIRSTNAME,
                                @POSTCODE = POSTCODE,
                                @ADDRESSBLOCK = ADDRESSBLOCK,
                                @ISORGANIZATION = ISORGANIZATION,
                                @ISGROUP = ISGROUP,
                                @TITLECODEID = TITLECODEID,
                                @MIDDLENAME = MIDDLENAME,
                                @SUFFIXCODEID = SUFFIXCODEID,
                                @COUNTRYID = COUNTRYID,
                                @EMAILADDRESS = EMAILADDRESS,
                                @PHONENUMBER = NUMBER,
                                @ADDRESSTYPECODEID = ADDRESSTYPECODEID,
                                @PHONETYPECODEID = PHONETYPECODEID,
                                @EMAILTYPECODEID = EMAILADDRESSTYPECODEID
                            from dbo.BATCHREVENUECONSTITUENT
                            where ID = @BILLTOCONSTITUENTID
                        end
                    end

                    declare @CANDIDATE table
                    (
                        BILLTOCONSTITUENTID uniqueidentifier,
                        ADDRESSID uniqueidentifier,
                        EMAILADDRESSID uniqueidentifier,
                        PHONEID uniqueidentifier,
                        MATCHPERCENTAGE numeric(5, 2),
                        STREETNUMBERRESULT tinyint,
                        STREETNAMERESULT tinyint,
                        POSTCODERESULT tinyint  
                    )

                    if @KEYNAME is not null
                        insert into @CANDIDATE (BILLTOCONSTITUENTID, ADDRESSID, EMAILADDRESSID, PHONEID, MATCHPERCENTAGE,STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT)
                        select
                            CONSTITUENTID,
                            ADDRESSID,
                            EMAILADDRESSID,
                            PHONEID,
                            MATCHPERCENTAGE,
                            STREETNUMBERRESULT,
                            STREETNAMERESULT,
                            POSTCODERESULT
                        from
                            dbo.UFN_FINDCONSTITUENTMATCHES_COMPOSITETHRESHOLD_3
                                (
                                    @TITLECODEID,
                                    @FIRSTNAME,
                                    @MIDDLENAME,
                                    @KEYNAME,
                                    @SUFFIXCODEID,
                                    @ADDRESSBLOCK,
                                    @POSTCODE,
                                    @COUNTRYID,
                                    @ISORGANIZATION,
                                    @ISGROUP,
                                    null,
                                    null,
                                    @EMAILADDRESS,
                                    @PHONENUMBER,
                                    @OVERALLMATCHTHRESHOLD,
                                    @AUTOMATCHTHRESHOLD,
                                    @CURRENTAPPUSERID,
                                    @CONSTITUENTDUPLICATEMATCHINGENABLED
                                ) DUPLICATES
                    else if @EDITCONSTITUENTCONTEXT is not null
                                    begin
                                        declare @BATCHCONSTITUENTUPDATEID uniqueidentifier = convert(uniqueidentifier, substring(@EDITCONSTITUENTCONTEXT,75,36))
                                        declare @PRIMARYRECORDID uniqueidentifier = convert(uniqueidentifier, substring(@EDITCONSTITUENTCONTEXT,1,36))

                                        if exists(select DOMANUALREVIEWFORAUTOMATCH from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHCONSTITUENTUPDATEID  and DOMANUALREVIEWFORAUTOMATCH = 1)
                                        begin
                                                    -- if none of the addresses generated an exception we need to find a possible match for it

                                                    if not exists (select 'x' from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID and ISMANUALEXCEPTION = 1)
                                                    begin 
                                                        select top 1
                                                            @POSTCODE = POSTCODE,
                                                            @ADDRESSBLOCK = ADDRESSBLOCK,
                                                            @COUNTRYID = COUNTRYID
                                                        from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
                                                        order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE   

                                                        declare @ADDRESSMATCHTHRESHOLD decimal(20,4);

                                                        select top 1
                                                            @ADDRESSMATCHTHRESHOLD = ADDRESSMATCHTHRESHOLD
                                                        from dbo.CONSTITUENTDUPLICATESEARCHSETTINGS;

                                                        insert into @CANDIDATE (BILLTOCONSTITUENTID, ADDRESSID, STREETNUMBERRESULT, STREETNAMERESULT, POSTCODERESULT)
                                                        select
                                                            @PRIMARYRECORDID,
                                                            DUPLICATES.ADDRESSID,                      
                                                            STREETNUMBERRESULT, 
                                                            STREETNAMERESULT, 
                                                            POSTCODERESULT
                                                        from dbo.UFN_FINDADDRESSMATCH_CONSTITUENTUPDATEBATCH(@PRIMARYRECORDID, @ADDRESSBLOCK, @POSTCODE, @COUNTRYID, '', null, null, @ADDRESSMATCHTHRESHOLD, null) DUPLICATES                        
                                                    end
                                        else -- if there where addresses that generate an exception select the one that was similar, otherwise do nothing.

                                        begin
                                            insert into @CANDIDATE(BILLTOCONSTITUENTID, ADDRESSID) 
                                            select top 1 @PRIMARYRECORDID, MANUALEXCEPTIONSIMILARADDRESSID
                                            from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID and ISMANUALEXCEPTION = 1
                                            order by case when ISPRIMARY = 1 then 1 else 2 end, SEQUENCE;
                                        end
                                        --make sure we always return the auto-matched constituent id

                                        if not exists(select BILLTOCONSTITUENTID from @CANDIDATE)
                                        insert into @CANDIDATE(BILLTOCONSTITUENTID) values(@PRIMARYRECORDID);    
                                    end
                        end

                        declare @NULLVALUE uniqueidentifier = (select cast(cast(0 as binary) as uniqueidentifier))

                        select top(@MAXROWS)
                            C.ID,
                            D.MATCHPERCENTAGE,
                            C.NAME,
                            A.ADDRESSBLOCK,
                            A.CITY,
                            S.ABBREVIATION,
                            A.POSTCODE,
                            C.LOOKUPID,
                            C.DATECHANGED,
                            A.ID ADDRESSID,
                            D.EMAILADDRESSID,
                            D.PHONEID,
                            D.STREETNUMBERRESULT,
                            D.STREETNAMERESULT,
                            D.POSTCODERESULT,
                            dbo.UFN_BUILDFULLADDRESS(ADDRESSID,a.ADDRESSBLOCK,a.CITY,a.STATEID,a.POSTCODE,a.COUNTRYID) as FULLADDRESS 
                        from @CANDIDATE D
                            inner join dbo.CONSTITUENT C on D.BILLTOCONSTITUENTID = C.ID
                            left join dbo.ADDRESS A on D.ADDRESSID = A.ID
                            left join dbo.STATE S on A.STATEID = S.ID
                            left join dbo.PHONE P on D.PHONEID = P.ID
                            left join dbo.EMAILADDRESS E on D.EMAILADDRESSID = E.ID
                        order by D.MATCHPERCENTAGE desc, 
                                C.KEYNAME, 
                                C.FIRSTNAME,
                                case when isnull(A.ADDRESSTYPECODEID,@NULLVALUE) = isnull(@ADDRESSTYPECODEID,@NULLVALUE) then 1 else 2 end,
                                case when isnull(P.PHONETYPECODEID,@NULLVALUE) = isnull(@PHONETYPECODEID,@NULLVALUE) then 1 else 2 end, 
                                case when isnull(E.EMAILADDRESSTYPECODEID,@NULLVALUE) = isnull(@EMAILTYPECODEID,@NULLVALUE) then 1 else 2 end,
                                case when isnull(A.ADDRESSBLOCK,'') = isnull(@ADDRESSBLOCK,'') then 1 else 2 end,
                                A.ISPRIMARY desc,
                                P.ISPRIMARY desc,
                                E.ISPRIMARY desc