USP_BBNC_BATCH_FINDERNUMBERLOOKUP

CREATE procedure dbo.[USP_BBNC_BATCH_FINDERNUMBERLOOKUP]
(
  @FINDERNUMBER bigint,
  @CONSTITUENTID uniqueidentifier output,
  @MAILINGID uniqueidentifier output,
  @SOURCECODE nvarchar(50) output,
  @APPEALID uniqueidentifier output
)
as
  set nocount on;

  set @CONSTITUENTID = null;

  if @FINDERNUMBER > 0
    begin
      -- quick and dirty, but the CONSTITUENTAPPEAL row created by the mailing activate process can be deleted

      select top 1
        @CONSTITUENTID = [CONSTITUENTID],
        @MAILINGID = [MKTSEGMENTATIONID],
        @SOURCECODE = [SOURCECODE],
        @APPEALID = [APPEALID]
      from dbo.[CONSTITUENTAPPEAL]
      where [FINDERNUMBER] = @FINDERNUMBER;

      if @CONSTITUENTID is null
        begin
          -- slow and thorough, but guaranteed to work

          select @MAILINGID = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);

          -- this table structure must match the one defined in 

          -- Marketing.Catalog\Segmentation\FinderNumber\FinderNumberLookup.DataList.xml

          declare @LOOKUP table (
            [ID] nvarchar(36),
            [CONSTITUENTID] nvarchar(36),
            [FULLNAME] nvarchar(255),
            [FIRSTNAME] nvarchar(255),
            [MIDDLENAME] nvarchar(255),
            [LASTNAME] nvarchar(255),
            [TITLE] nvarchar(255),
            [SUFFIX] nvarchar(255),
            [COUNTRY] nvarchar(255),
            [ADDRESSLINE1] nvarchar(255),
            [ADDRESSLINE2] nvarchar(255),
            [ADDRESSLINE3] nvarchar(255),
            [ADDRESSLINE4] nvarchar(255),
            [ADDRESSLINE5] nvarchar(255),
            [CITY] nvarchar(255),
            [STATE] nvarchar(255),
            [POSTCODE] nvarchar(255),
            [CART] nvarchar(255),
            [DPC] nvarchar(255),
            [LOT] nvarchar(255),
            [PHONENUMBER] nvarchar(255),
            [EMAILADDRESS] nvarchar(255),
            [MAILINGIDINTEGER] int,
            [MAILDATE] datetime,
            [SOURCECODE] nvarchar(50),
            [APPEALSYSTEMID] nvarchar(36),
            [APPEALID] nvarchar(100),
            [APPEALDESCRIPTION] nvarchar(100),
            [PACKAGECODE] nvarchar(10),
            [PACKAGENAME] nvarchar(100),
            [PACKAGEDESCRIPTION] nvarchar(255),
            [SEGMENTID] uniqueidentifier,
            [SEGMENTNAME] nvarchar(203),
            [LISTNAME] nvarchar(100),
            [TITLE2] nvarchar(255),
            [SUFFIX2] nvarchar(255)
          );

          declare @RECORDSOURCEID uniqueidentifier
          set @RECORDSOURCEID = (select  dbo.UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID());

          insert into @LOOKUP
            exec dbo.[USP_DATALIST_MKTFINDERNUMBERLOOKUP] @FINDERNUMBER, @RECORDSOURCEID;

          declare @CONSTITUENTIDFROMFINDERNUMBER nvarchar(36);

          select top 1 @CONSTITUENTIDFROMFINDERNUMBER = [CONSTITUENTID], @SOURCECODE = [SOURCECODE] from @LOOKUP;

          if @CONSTITUENTIDFROMFINDERNUMBER is not null
            if len(@CONSTITUENTIDFROMFINDERNUMBER) = 36 and exists (select top 1 1 from dbo.[CONSTITUENT] where [ID] = convert(uniqueidentifier, @CONSTITUENTIDFROMFINDERNUMBER))
              set @CONSTITUENTID = convert(uniqueidentifier, @CONSTITUENTIDFROMFINDERNUMBER);

          declare @APPEALIDFROMMAILING nvarchar(36);

          select
            @APPEALIDFROMMAILING = [APPEALSYSTEMID]
          from dbo.[MKTSEGMENTATIONACTIVATE]
          where [SEGMENTATIONID] = @MAILINGID;

          if @APPEALIDFROMMAILING is not null 
            if len(@APPEALIDFROMMAILING) = 36 and exists (select top 1 1 from dbo.[APPEAL] where [ID] = convert(uniqueidentifier, @APPEALIDFROMMAILING))
              set @APPEALID = convert(uniqueidentifier, @APPEALIDFROMMAILING);
        end
    end

  -- source code cannot be null; must be an empty string if one isn't found

  if @SOURCECODE is null set @SOURCECODE = '';

  return 0;