UFN_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH

CREATE function dbo.UFN_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH(
  @BATCHCONSTITUENTUPDATEID uniqueidentifier,
  @MATCHEDRECORDID uniqueidentifier
)
returns bit
with execute as caller
as begin

  declare @DOMANUALREVIEWFORAUTOMATCH bit;
  declare @KEYNAME nvarchar(255);
  declare @FIRSTNAME nvarchar(255);
  declare @MIDDLENAME nvarchar(255);
  declare @TITLECODEID uniqueidentifier;
  declare @SUFFIXCODEID uniqueidentifier;
  declare @NAMEEXCEPTIONCODE tinyint;
  declare @ADDRESSSIMILAREXCEPTIONCODE tinyint;
  declare @ADDRESSNOTSIMILAREXCEPTIONCODE tinyint;
  declare @EXISTINGFIRSTNAME nvarchar(100);
  declare @EXISTINGKEYNAME nvarchar(200);
  declare @EXISTINGMIDDLENAME nvarchar(100);
  declare @EXISTINGSUFFIXCODEID uniqueidentifier;
  declare @EXISTINGTITLECODEID uniqueidentifier;
  declare @ADDRESSMATCHTHRESHOLD decimal(20,4);

  set @DOMANUALREVIEWFORAUTOMATCH = 0;

  select
    @KEYNAME = KEYNAME,
    @FIRSTNAME = FIRSTNAME,
    @MIDDLENAME = MIDDLENAME,
    @TITLECODEID = TITLECODEID,
    @SUFFIXCODEID = SUFFIXCODEID,
    @NAMEEXCEPTIONCODE = NAMECODE,
    @ADDRESSSIMILAREXCEPTIONCODE = SIMILARADDRESSCODE,
    @ADDRESSNOTSIMILAREXCEPTIONCODE = UNSIMILARADDRESSCODE
  from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHCONSTITUENTUPDATEID;

  if @NAMEEXCEPTIONCODE = 2
      select
        @EXISTINGKEYNAME = KEYNAME,
        @EXISTINGFIRSTNAME = FIRSTNAME,
        @EXISTINGMIDDLENAME = MIDDLENAME,
        @EXISTINGTITLECODEID = TITLECODEID,
        @EXISTINGSUFFIXCODEID = SUFFIXCODEID
      from dbo.CONSTITUENT where ID = @MATCHEDRECORDID;


  if @NAMEEXCEPTIONCODE = 2 and ((@EXISTINGFIRSTNAME <> @FIRSTNAME and @FIRSTNAME <> '') or 
                                  (@EXISTINGKEYNAME <> @KEYNAME  and @KEYNAME <> '') or 
                                  (@EXISTINGMIDDLENAME <> @MIDDLENAME  and @MIDDLENAME <> '') or 
                                  (@EXISTINGSUFFIXCODEID <> @SUFFIXCODEID  and @SUFFIXCODEID is not null) or 
                                  (@EXISTINGTITLECODEID <> @TITLECODEID and @TITLECODEID is not null))
    set @DOMANUALREVIEWFORAUTOMATCH = 1;

  else if (@ADDRESSSIMILAREXCEPTIONCODE = 2 or @ADDRESSNOTSIMILAREXCEPTIONCODE = 2)
  begin
    -- Get the address match threshold 

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

    if exists(select 'x'
          from dbo.BATCHCONSTITUENTUPDATEADDRESSES  BCUA
          outer apply dbo.UFN_FINDADDRESSMATCH2(@MATCHEDRECORDID, ADDRESSBLOCK, POSTCODE, COUNTRYID, CITY, STATEID, ADDRESSTYPECODEID, @ADDRESSMATCHTHRESHOLD, BCUA.HISTORICALENDDATE) FA
          where BCUA.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID
          and ((FA.ADDRESSID is null and @ADDRESSNOTSIMILAREXCEPTIONCODE = 2)
          or (FA.ADDRESSID is not null and FA.NOCONFLICTS = 0 and @ADDRESSSIMILAREXCEPTIONCODE = 2)))
    set @DOMANUALREVIEWFORAUTOMATCH = 1;

  end

    return @DOMANUALREVIEWFORAUTOMATCH;
end