UFN_SPONSORSHIP_GETDUPLICATECANDIDATES

create function dbo.UFN_SPONSORSHIP_GETDUPLICATECANDIDATES
(
@CONSTITUENTID uniqueidentifier,
@CONSTITUENTLOOKUPID uniqueidentifier,
@NEWCONSTITUENT xml = null,
@MAXROWS smallint = 500
)

returns @DUPLICATECANDIDATES table(
                ID uniqueidentifier,
                MATCHPERCENTAGE numeric(5, 2),
        NAME nvarchar(100),
          ADDRESSBLOCK nvarchar(150),
          CITY nvarchar(100),
          ABBREVIATION nvarchar(50),
          POSTCODE nvarchar(50),
          LOOKUPID nvarchar(50)
            ) 
as begin



            declare @NAME nvarchar(100), @KEYNAME nvarchar(100), @FIRSTNAME nvarchar(50), @POSTCODE nvarchar(12), @ADDRESSBLOCK nvarchar(150), @ISORGANIZATION bit, @ISGROUP bit

          if (@CONSTITUENTID is null and @CONSTITUENTLOOKUPID is null)
              begin
                  select
                      @NAME = LASTNAME,
                      @KEYNAME = LASTNAME,
                      @FIRSTNAME = FIRSTNAME,
                      @POSTCODE = ADDRESS_POSTCODE,
                      @ADDRESSBLOCK = ADDRESS_ADDRESSBLOCK,
                      @ISORGANIZATION = ISORGANIZATION,
                      @ISGROUP = ISGROUP
                  from dbo.UFN_REVENUEBATCH_GETNEWCONSTITUENT_FROMITEMLISTXML(@NEWCONSTITUENT)
                    end

            if @KEYNAME is null
            begin
              if exists (select ID from dbo.BATCHSPONSORSHIPCONSTITUENT where ID = @CONSTITUENTID)
              begin
                  select
                  @KEYNAME = KEYNAME,
                  @NAME = NAME,
                  @FIRSTNAME = FIRSTNAME,
                  @POSTCODE = POSTCODE,
                  @ADDRESSBLOCK = ADDRESSBLOCK,
                  @ISORGANIZATION = ISORGANIZATION,
                  @ISGROUP = ISGROUP
                  from dbo.BATCHSPONSORSHIPCONSTITUENT
                  where ID = @CONSTITUENTID
              end
           end 

          if @KEYNAME is not null
          begin
            if @ISORGANIZATION = 0 and @ISGROUP = 0
              insert into @DUPLICATECANDIDATES(ID, MATCHPERCENTAGE, NAME, ADDRESSBLOCK, CITY, ABBREVIATION, POSTCODE, LOOKUPID)         
              select top(@MAXROWS)
                  C.ID,
                  D.MATCHPERCENTAGE,
                  C.NAME,
                  A.ADDRESSBLOCK,
                  A.CITY,
                  S.ABBREVIATION,
                  A.POSTCODE,
                  C.LOOKUPID
              from dbo.UFN_CONSTITUENT_GETDUPLICATECANDIDATES(@KEYNAME, @FIRSTNAME, @POSTCODE, @ADDRESSBLOCK) D
              inner join dbo.CONSTITUENT C on D.CONSTITUENTID = C.ID
              inner join dbo.ADDRESS A on D.ADDRESSID = A.ID
              left join dbo.STATE S on A.STATEID = S.ID
              order by D.MATCHPERCENTAGE desc, C.KEYNAME, C.FIRSTNAME

            else
              if @ISORGANIZATION = 1
                insert into @DUPLICATECANDIDATES(ID, MATCHPERCENTAGE, NAME, ADDRESSBLOCK, CITY, ABBREVIATION, POSTCODE, LOOKUPID)         
                select top(@MAXROWS)
                    C.ID,
                    D.MATCHPERCENTAGE,
                    C.NAME,
                    A.ADDRESSBLOCK,
                    A.CITY,
                    S.ABBREVIATION,
                    A.POSTCODE,
                    C.LOOKUPID
                from dbo.UFN_CONSTITUENT_ORGANIZATION_GETFUZZYDUPLICATES(@NAME, @KEYNAME, @POSTCODE, @ADDRESSBLOCK) D
                inner join dbo.CONSTITUENT C on D.CONSTITUENTID = C.ID
                inner join dbo.ADDRESS A on D.ADDRESSID = A.ID
                left join dbo.STATE S on A.STATEID = S.ID
                order by D.MATCHPERCENTAGE desc, C.KEYNAME, C.FIRSTNAME
          end

  return;
end