USP_BATCHCONSTITUENTAPPLYRULES_FORIMPORT

Parameters

Parameter	Parameter Type	Mode
@BATCHROWID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SIMILARADDRESSCODE	tinyint	IN
@UNSIMILARADDRESSCODE	tinyint	IN
@NEWADDRESSPRIMARYCODE	tinyint	IN
@DOMANUALREVIEWFORAUTOMATCH	bit	INOUT
@DIFFERENTPHONECODE	tinyint	IN
@NEWPHONEPRIMARYCODE	tinyint	IN
@DIFFERENTEMAILCODE	tinyint	IN
@NEWEMAILPRIMARYCODE	tinyint	IN
@ADDALL	bit	IN

Definition

Copy



CREATE procedure dbo.USP_BATCHCONSTITUENTAPPLYRULES_FORIMPORT(
  @BATCHROWID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier,  
  @CHANGEAGENTID uniqueidentifier,
  @SIMILARADDRESSCODE tinyint,
  @UNSIMILARADDRESSCODE tinyint,
  @NEWADDRESSPRIMARYCODE tinyint,
  @DOMANUALREVIEWFORAUTOMATCH bit output,
  @DIFFERENTPHONECODE tinyint,
  @NEWPHONEPRIMARYCODE tinyint,
  @DIFFERENTEMAILCODE tinyint,
  @NEWEMAILPRIMARYCODE tinyint,
  @ADDALL bit
)
    as
    begin

        declare @CURRENTDATE datetime = getdate();

        exec dbo.USP_CONSTITUENTUPDATEBATCH_DOMANUALREVIEWFORAUTOMATCH @BATCHROWID, @CONSTITUENTID, 0, @SIMILARADDRESSCODE, @UNSIMILARADDRESSCODE, @DIFFERENTPHONECODE, @DIFFERENTEMAILCODE, @DOMANUALREVIEWFORAUTOMATCH output;

        -- ensure the correct value is set for @DOMANUALREVIEWFORAUTOMATCH

        update dbo.BATCHCONSTITUENTUPDATE set 
            DOMANUALREVIEWFORAUTOMATCH = @DOMANUALREVIEWFORAUTOMATCH
        where ID = @BATCHROWID;     

        if @DOMANUALREVIEWFORAUTOMATCH = 0 -- apply the rules  

          begin
            -- the incoming primary address/phone/email should be made non-primary if the existing record has a primary address/email/phone since the rules are handling this below

            if exists (select 'x' from dbo.ADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
              update dbo.BATCHCONSTITUENTUPDATEADDRESSES set
                ISPRIMARY = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              where BATCHCONSTITUENTUPDATEID = @BATCHROWID
              and ISPRIMARY = 1
              and ADDRESSID is null;

            if exists (select 'x' from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
              update dbo.BATCHCONSTITUENTUPDATEPHONES set
                ISPRIMARY = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              where BATCHCONSTITUENTUPDATEID = @BATCHROWID
              and ISPRIMARY = 1
              and PHONEID is null;

            if exists (select 'x' from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and ISPRIMARY = 1)
              update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES set
                ISPRIMARY = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              where BATCHCONSTITUENTUPDATEID = @BATCHROWID
              and ISPRIMARY = 1
              and EMAILADDRESSID is null;

            exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYADDRESSRULES @BATCHROWID, @CONSTITUENTID, @CHANGEAGENTID, @ADDALL, @SIMILARADDRESSCODE, @UNSIMILARADDRESSCODE, @NEWADDRESSPRIMARYCODE; -- Apply address rules

            exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYPHONERULES @BATCHROWID, @CONSTITUENTID, @CHANGEAGENTID, @ADDALL, @DIFFERENTPHONECODE, @NEWPHONEPRIMARYCODE; -- Apply phone rules          

            exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYEMAILRULES @BATCHROWID, @CONSTITUENTID, @CHANGEAGENTID, @ADDALL, @DIFFERENTEMAILCODE, @NEWEMAILPRIMARYCODE; -- Apply email rules

          end 
    end