USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHDUPLICATEMATCH_3

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@LOOKUPID	nvarchar(50)	IN
@FIRSTNAME	nvarchar(50)	IN
@MIDDLENAME	nvarchar(50)	IN
@LASTNAME	nvarchar(100)	IN
@SUFFIXCODEID	uniqueidentifier	IN
@TITLECODEID	uniqueidentifier	IN
@MAIDENNAME	nvarchar(100)	IN
@NICKNAME	nvarchar(50)	IN
@GENDERCODE	tinyint	IN
@BIRTHDATE	UDT_FUZZYDATE	IN
@ADDRESSID	uniqueidentifier	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@PHONEID	uniqueidentifier	IN
@PHONETYPECODEID	uniqueidentifier	IN
@PHONENUMBER	nvarchar(100)	IN
@EMAILID	uniqueidentifier	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@CREATEDON	datetime	IN
@DATECHANGED	datetime	IN
@ADDRESSES	xml	IN
@PHONES	xml	IN
@EMAILADDRESSES	xml	IN
@CONSTITUENTACTION	tinyint	IN
@ADDRESSACTION	tinyint	IN
@EMAILACTION	tinyint	IN
@PHONEACTION	tinyint	IN
@PRIMARYRECORDID	uniqueidentifier	IN
@ERBCONSTITUENTID	uniqueidentifier	IN
@EMAILISPRIMARY	bit	IN
@PHONEISPRIMARY	bit	IN
@ADDRESSISPRIMARY	bit	IN
@CURRENTAPPUSERID	uniqueidentifier	IN
@DECEASED	bit	IN
@DECEASEDDATE	UDT_FUZZYDATE	IN
@GIVESANONYMOUSLY	bit	IN
@MARITALSTATUSCODEID	uniqueidentifier	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@ADDRESSHISTORICALSTARTDATE	date	IN
@ADDRESSHISTORICALENDDATE	date	IN
@ADDRESSDONOTMAIL	bit	IN
@ADDRESSDONOTMAILREASONCODEID	uniqueidentifier	IN
@ADDRESSSTARTDATE	UDT_MONTHDAY	IN
@ADDRESSENDDATE	UDT_MONTHDAY	IN
@ADDRESSDPC	nvarchar(max)	IN
@ADDRESSCART	nvarchar(max)	IN
@ADDRESSLOT	nvarchar(5)	IN
@ADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@ADDRESSINFOSOURCECOMMENTS	nvarchar(256)	IN
@ADDRESSCOUNTYCODEID	uniqueidentifier	IN
@ADDRESSREGIONCODEID	uniqueidentifier	IN
@ADDRESSCONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSSTATEHOUSEDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSSTATESENATEDISTRICTCODEID	uniqueidentifier	IN
@ADDRESSLOCALPRECINCTCODEID	uniqueidentifier	IN
@ADDRESSCERTIFICATIONDATA	int	IN
@ADDRESSLASTVALIDATIONATTEMPTDATE	date	IN
@ADDRESSOMITFROMVALIDATION	bit	IN
@ADDRESSVALIDATIONMESSAGE	nvarchar(200)	IN
@PHONEDONOTCALL	bit	IN
@PHONESTARTTIME	UDT_HOURMINUTE	IN
@PHONEENDTIME	UDT_HOURMINUTE	IN
@PHONEINFOSOURCECODEID	uniqueidentifier	IN
@PHONECOUNTRYID	uniqueidentifier	IN
@PHONESTARTDATE	date	IN
@PHONEENDDATE	date	IN
@PHONESEASONALSTARTDATE	UDT_MONTHDAY	IN
@PHONESEASONALENDDATE	UDT_MONTHDAY	IN
@EMAILADDRESSDONOTEMAIL	bit	IN
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@EMAILADDRESSSTARTDATE	date	IN
@EMAILADDRESSENDDATE	date	IN
@GENDERCODEID	uniqueidentifier	IN

Definition

Copy


create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHDUPLICATEMATCH_3(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LOOKUPID nvarchar(50),
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
    @LASTNAME nvarchar(100),
    @SUFFIXCODEID uniqueidentifier,
    @TITLECODEID uniqueidentifier,
    @MAIDENNAME nvarchar(100),
    @NICKNAME nvarchar(50),
    @GENDERCODE tinyint,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @ADDRESSID uniqueidentifier,
    @ADDRESSTYPECODEID uniqueidentifier,
    @ADDRESS_ADDRESSBLOCK nvarchar(150),
    @ADDRESS_CITY nvarchar(50),
    @ADDRESS_STATEID uniqueidentifier,
    @ADDRESS_POSTCODE nvarchar(12),
    @ADDRESS_COUNTRYID uniqueidentifier,
    @PHONEID uniqueidentifier,
    @PHONETYPECODEID uniqueidentifier,
    @PHONENUMBER nvarchar(100),
    @EMAILID uniqueidentifier,
    @EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @CREATEDON datetime,
    @DATECHANGED datetime,
    @ADDRESSES xml,
    @PHONES xml,
    @EMAILADDRESSES xml,
    @CONSTITUENTACTION tinyint,
    @ADDRESSACTION tinyint,
    @EMAILACTION tinyint,
    @PHONEACTION tinyint,
    @PRIMARYRECORDID uniqueidentifier,
  @ERBCONSTITUENTID uniqueidentifier,
    @EMAILISPRIMARY bit,
    @PHONEISPRIMARY bit,
    @ADDRESSISPRIMARY bit,
  @CURRENTAPPUSERID uniqueidentifier,
  @DECEASED bit,
  @DECEASEDDATE dbo.UDT_FUZZYDATE,
  @GIVESANONYMOUSLY bit,
  @MARITALSTATUSCODEID uniqueidentifier,
  @WEBADDRESS dbo.UDT_WEBADDRESS,
  @ADDRESSHISTORICALSTARTDATE date,
  @ADDRESSHISTORICALENDDATE date,
  @ADDRESSDONOTMAIL bit,
  @ADDRESSDONOTMAILREASONCODEID uniqueidentifier,
  @ADDRESSSTARTDATE dbo.UDT_MONTHDAY,
  @ADDRESSENDDATE dbo.UDT_MONTHDAY,
  @ADDRESSDPC nvarchar(max),
  @ADDRESSCART nvarchar(max),
  @ADDRESSLOT nvarchar(5),
  @ADDRESSINFOSOURCECODEID uniqueidentifier,
  @ADDRESSINFOSOURCECOMMENTS nvarchar(256),
  @ADDRESSCOUNTYCODEID uniqueidentifier,
  @ADDRESSREGIONCODEID uniqueidentifier,
  @ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier,
  @ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier,
  @ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier,
  @ADDRESSLOCALPRECINCTCODEID uniqueidentifier,
  @ADDRESSCERTIFICATIONDATA int,
  @ADDRESSLASTVALIDATIONATTEMPTDATE date,
  @ADDRESSOMITFROMVALIDATION bit,
  @ADDRESSVALIDATIONMESSAGE nvarchar(200),
  @PHONEDONOTCALL bit,
  @PHONESTARTTIME dbo.UDT_HOURMINUTE,
    @PHONEENDTIME dbo.UDT_HOURMINUTE,
    @PHONEINFOSOURCECODEID uniqueidentifier,
    @PHONECOUNTRYID uniqueidentifier,
    @PHONESTARTDATE date,
    @PHONEENDDATE date,
  @PHONESEASONALSTARTDATE dbo.UDT_MONTHDAY,
    @PHONESEASONALENDDATE dbo.UDT_MONTHDAY,
  @EMAILADDRESSDONOTEMAIL bit,
  @EMAILADDRESSINFOSOURCECODEID uniqueidentifier,
  @EMAILADDRESSSTARTDATE date,
    @EMAILADDRESSENDDATE date,
    @GENDERCODEID uniqueidentifier
)
as
     set nocount on;

    declare @CURRENTDATE datetime;    
    declare @INCOMINGADDRESSID uniqueidentifier, @INCOMINGEMAILID uniqueidentifier, @INCOMINGPHONEID uniqueidentifier;

    set @CURRENTDATE = getdate();

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

      begin try
      -- first load the new constituent's data and save the matched constituent to the CUB tables
      exec dbo.USP_REVENUEBATCH_DUPLICATEMATCH_EDITSAVE_CONSTITUENT @ID, @ERBCONSTITUENTID, @PRIMARYRECORDID, @CURRENTAPPUSERID;

      select @INCOMINGADDRESSID = (select ID from dbo.BATCHCONSTITUENTUPDATEADDRESSES where BATCHCONSTITUENTUPDATEID = @ID), 
            @INCOMINGEMAILID = (select ID from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES where BATCHCONSTITUENTUPDATEID = @ID), 
            @INCOMINGPHONEID = (select ID from dbo.BATCHCONSTITUENTUPDATEPHONES where BATCHCONSTITUENTUPDATEID = @ID);          

      -- make the CUB updates based on the changes the user made on the reconciliation screen
      exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHDUPLICATEMATCH_7
            @ID,
            @CHANGEAGENTID,
            @LOOKUPID,
            @FIRSTNAME,
            @MIDDLENAME,
            @LASTNAME,
            @SUFFIXCODEID,
            @TITLECODEID,
            @MAIDENNAME,
            @NICKNAME,
            @GENDERCODE,
            @BIRTHDATE,
            @ADDRESSID,
            @ADDRESSTYPECODEID,
            @ADDRESS_ADDRESSBLOCK,
            @ADDRESS_CITY,
            @ADDRESS_STATEID,
            @ADDRESS_POSTCODE,
            @ADDRESS_COUNTRYID,
            @PHONEID,
            @PHONETYPECODEID,
            @PHONENUMBER,
            @EMAILID,
            @EMAILADDRESSTYPECODEID,
            @EMAILADDRESS,
            @CREATEDON,
            @DATECHANGED,
            @ADDRESSES,
            @PHONES,
            @EMAILADDRESSES,
            @CONSTITUENTACTION,
            @ADDRESSACTION,
            @EMAILACTION,
            @PHONEACTION,
            @PRIMARYRECORDID,
            @EMAILISPRIMARY,
            @PHONEISPRIMARY,
            @ADDRESSISPRIMARY,
          @INCOMINGADDRESSID,
          @INCOMINGEMAILID,
          @INCOMINGPHONEID,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          @DECEASED,
          @DECEASEDDATE,
          @GIVESANONYMOUSLY,
          @MARITALSTATUSCODEID,
          @WEBADDRESS,
          @ADDRESSHISTORICALSTARTDATE,
          @ADDRESSHISTORICALENDDATE,
          @ADDRESSDONOTMAIL,
          @ADDRESSDONOTMAILREASONCODEID,
          @ADDRESSSTARTDATE,
          @ADDRESSENDDATE,
          @ADDRESSDPC,
          @ADDRESSCART,
          @ADDRESSLOT,
          @ADDRESSINFOSOURCECODEID,
          @ADDRESSINFOSOURCECOMMENTS,
          @ADDRESSCOUNTYCODEID,
          @ADDRESSREGIONCODEID,
          @ADDRESSCONGRESSIONALDISTRICTCODEID,
          @ADDRESSSTATEHOUSEDISTRICTCODEID,
          @ADDRESSSTATESENATEDISTRICTCODEID,
          @ADDRESSLOCALPRECINCTCODEID,
          @ADDRESSCERTIFICATIONDATA,
          @ADDRESSLASTVALIDATIONATTEMPTDATE,
          @ADDRESSOMITFROMVALIDATION,
          @ADDRESSVALIDATIONMESSAGE,
          @PHONEDONOTCALL,
              @PHONESTARTTIME,
              @PHONEENDTIME,
              @PHONEINFOSOURCECODEID,
              @PHONECOUNTRYID,
              @PHONESTARTDATE,
              @PHONEENDDATE,
              @PHONESEASONALSTARTDATE,
              @PHONESEASONALENDDATE,
              @EMAILADDRESSDONOTEMAIL,
              @EMAILADDRESSINFOSOURCECODEID,
              @EMAILADDRESSSTARTDATE,
              @EMAILADDRESSENDDATE,
                    null,
                    null,
                    null,
                    null,
                    @GENDERCODEID;

            update dbo.BATCHREVENUERECOGNITION set
                CONSTITUENTID = @PRIMARYRECORDID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                BATCHREVENUEID = @ID and
                CONSTITUENTID = @ERBCONSTITUENTID;

            update dbo.BATCHREVENUEAPPLICATIONPLEDGE set
                CONSTITUENTID = @PRIMARYRECORDID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.BATCHREVENUEAPPLICATIONPLEDGE
            inner join dbo.BATCHREVENUEAPPLICATION on BATCHREVENUEAPPLICATIONPLEDGE.ID = BATCHREVENUEAPPLICATION.BATCHREVENUEAPPLICATIONPLEDGEID
            where
                BATCHREVENUEAPPLICATION.BATCHREVENUEID = @ID and
                BATCHREVENUEAPPLICATIONPLEDGE.CONSTITUENTID = @ERBCONSTITUENTID;

      update dbo.BATCHREVENUE set
        CONSTITUENTID = @PRIMARYRECORDID,
                GIVENANONYMOUSLY = 
          case
            when
              (@GIVESANONYMOUSLY = 1 or BATCHREVENUE.GIVENANONYMOUSLY = 1) then
                1
            else
              @GIVESANONYMOUSLY
          end,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;    

      --update the constituent accounts
      exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
              @ERBCONSTITUENTID, 
              @PRIMARYRECORDID, 
              @CHANGEAGENTID

    -- remove the exception row when the duplicate is resolved
    delete from dbo.BATCHREVENUEBATCHSYSTEMMESSAGES where BATCHREVENUEID = @ID and MESSAGETYPECODE = 1;    

      end try
      begin catch
          exec dbo.USP_RAISE_ERROR
          return 1
      end catch

return 0;