USP_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH

create procedure dbo.USP_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH(
  @BATCHCONSTITUENTUPDATEID uniqueidentifier,
  @MATCHEDRECORDID uniqueidentifier,
  @NAMEEXCEPTIONCODE tinyint,
  @ADDRESSSIMILAREXCEPTIONCODE tinyint,
  @ADDRESSNOTSIMILAREXCEPTIONCODE tinyint,
  @DIFFERENTPHONECODE tinyint,
  @DIFFERENTEMAILCODE tinyint,
  @DOMANUALREVIEWFORAUTOMATCH bit = null output
  )  as
  set nocount on;

  declare @CHANGEAGENTID uniqueidentifier;
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate()

  declare @KEYNAME nvarchar(255);
  declare @FIRSTNAME nvarchar(255);
  declare @MIDDLENAME nvarchar(255);
  declare @TITLECODEID uniqueidentifier;
  declare @SUFFIXCODEID uniqueidentifier;
  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
  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;

  -- check names   

  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;

  -- check addresses and mark the incoming addresses that triggerd the manual review

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

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

      update dbo.BATCHCONSTITUENTUPDATEADDRESSES
            set ISMANUALEXCEPTION = 1, MANUALEXCEPTIONSIMILARADDRESSID = FA.ADDRESSID, @DOMANUALREVIEWFORAUTOMATCH = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
      from dbo.BATCHCONSTITUENTUPDATEADDRESSES BCUA
      outer apply dbo.UFN_FINDADDRESSMATCH(@MATCHEDRECORDID, ADDRESSBLOCK, POSTCODE, COUNTRYID, CITY, STATEID, ADDRESSTYPECODEID, @ADDRESSMATCHTHRESHOLD) 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))
    end

  -- check phones and mark the incoming phones that triggerd the manual review

  if @DIFFERENTPHONECODE = 2
    update dbo.BATCHCONSTITUENTUPDATEPHONES
          set ISMANUALEXCEPTION = 1, @DOMANUALREVIEWFORAUTOMATCH = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE  
    from dbo.BATCHCONSTITUENTUPDATEPHONES BP 
    left join dbo.PHONE P on P.NUMBER = BP.NUMBER and (P.PHONETYPECODEID is null or BP.PHONETYPECODEID is null or P.PHONETYPECODEID = BP.PHONETYPECODEID)
    where P.ID is null and BP.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID

  -- check emails and mark the incoming emails that triggerd the manual review

  if @DIFFERENTEMAILCODE = 2 
    update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES
          set ISMANUALEXCEPTION = 1, @DOMANUALREVIEWFORAUTOMATCH = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE   
    from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES BE 
    left join dbo.EMAILADDRESS E on E.EMAILADDRESS = BE.EMAILADDRESS and (E.EMAILADDRESSTYPECODEID is null or BE.EMAILADDRESSTYPECODEID is null or E.EMAILADDRESSTYPECODEID = BE.EMAILADDRESSTYPECODEID)
    where E.ID is null and BE.BATCHCONSTITUENTUPDATEID = @BATCHCONSTITUENTUPDATEID

  return 0;