USP_SEARCHLIST_SPONSORSHIPBATCHDUPLICATECONSTITUENT

Parameters

Parameter	Parameter Type	Mode
@DONORID	uniqueidentifier	IN
@FINANCIALSPONSORLOOKUPID	uniqueidentifier	IN
@NEWDONORCONSTITUENT	xml	IN
@CONSTITUENTID	uniqueidentifier	IN
@CORRESPONDINGSPONSORLOOKUPID	uniqueidentifier	IN
@NEWCONSTITUENT	xml	IN
@MAXROWS	smallint	IN
Definition

Copy


            CREATE procedure dbo.USP_SEARCHLIST_SPONSORSHIPBATCHDUPLICATECONSTITUENT
            (
                @DONORID uniqueidentifier = null,
        @FINANCIALSPONSORLOOKUPID uniqueidentifier = null,
        @NEWDONORCONSTITUENT xml = null,
        @CONSTITUENTID uniqueidentifier = null,
        @CORRESPONDINGSPONSORLOOKUPID uniqueidentifier = null,        
        @NEWCONSTITUENT xml = null,
                @MAXROWS smallint = 500
            )
            as     
            set nocount on

      declare @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),
        FIELDTOUPDATE nvarchar(50)
            ) 

      if not exists (select 1 from dbo.BATCHIGNOREDUPLICATE where CONSTITUENTID = @DONORID)      
       begin
        insert into @DUPLICATECANDIDATES(ID, MATCHPERCENTAGE, NAME, ADDRESSBLOCK, CITY, ABBREVIATION, POSTCODE, LOOKUPID, FIELDTOUPDATE) 
                select
          ID, 
            MATCHPERCENTAGE,
            NAME,
            ADDRESSBLOCK,
            CITY,
            ABBREVIATION,
            POSTCODE,
            LOOKUPID,
          'DONORID'
        from dbo.UFN_SPONSORSHIP_GETDUPLICATECANDIDATES(@DONORID, @FINANCIALSPONSORLOOKUPID, @NEWDONORCONSTITUENT, @MAXROWS) 
       end

      if @DONORID <> @CONSTITUENTID and (select count(*) from @DUPLICATECANDIDATES) = 0 and not exists (select 1 from dbo.BATCHIGNOREDUPLICATE where CONSTITUENTID = @CONSTITUENTID)
       begin
        insert into @DUPLICATECANDIDATES(ID, MATCHPERCENTAGE, NAME, ADDRESSBLOCK, CITY, ABBREVIATION, POSTCODE, LOOKUPID, FIELDTOUPDATE) 
        select
                ID, 
            MATCHPERCENTAGE,
            NAME,
            ADDRESSBLOCK,
            CITY,
            ABBREVIATION,
            POSTCODE,
            LOOKUPID,
          'CONSTITUENTID'
        from dbo.UFN_SPONSORSHIP_GETDUPLICATECANDIDATES(@CONSTITUENTID, @CORRESPONDINGSPONSORLOOKUPID, @NEWCONSTITUENT, @MAXROWS) 
          end

      select 
          ID, 
            MATCHPERCENTAGE,
            NAME,
            ADDRESSBLOCK,
            CITY,
            ABBREVIATION,
            POSTCODE,
            LOOKUPID,
          FIELDTOUPDATE
      from @DUPLICATECANDIDATES