USP_DATALIST_ACQUISITIONLISTLOOKUP

CREATE procedure dbo.[USP_DATALIST_ACQUISITIONLISTLOOKUP]
(
  @FINDERNUMBER bigint = null
)
as
  set nocount on;

  declare @MAILINGID uniqueidentifier = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);
  declare @APPEALSYSTEMID nvarchar(36);
  declare @APPEALID uniqueidentifier;
  declare @APPEALNAME nvarchar(100);

  declare @FINDERFILETABLE nvarchar(128);
  declare @ACTIVATIONTABLENAME nvarchar(128);
  declare @SQL nvarchar(max);
  declare @PARAMDEF nvarchar(max);

  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @SEGMENTNAME nvarchar(203);
  declare @PACKAGENAME nvarchar(100);
  declare @SOURCECODE nvarchar(50);

  --Looking up the source code, segment, and package information...

  if @MAILINGID is not null
    begin
      --Check the finder file table first (if it exists) to see if we have a match...

      set @FINDERFILETABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME](@MAILINGID);
      if exists(select * from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @FINDERFILETABLE)
        begin
          --If they import multiple finder files and import the same FINDERNUMBER multiple times with different names, segments, or sourcecodes,

          --then we need to make sure we only grab info for the findernumber from the latest import file.

          set @SQL = 'with [RANKEDIMPORT] ([ID], [RANK]) as' + char(13) +
                     '(' + char(13) +
                     '  select' + char(13) +
                     '    [FT].[ID],' + char(13) +
                     '    row_number() over (partition by [FT].[FINDERNUMBER] order by [MKTFINDERFILEIMPORTPROCESS].[DATEADDED] desc) as [RANK]' + char(13) +
                     '  from dbo.[' + @FINDERFILETABLE + '] as [FT] with (index([IX_' + @FINDERFILETABLE + '_FINDERNUMBER]))' + char(13) +
                     '  inner join dbo.[MKTFINDERFILEIMPORTPROCESS] on [MKTFINDERFILEIMPORTPROCESS].[ID] = [FT].[FINDERFILEID]' + char(13) +
                     '  where [FT].[FINDERNUMBER] = @FINDERNUMBER' + char(13) +
                     ')' + char(13) +
                     'select' + char(13) +
                     '  @SEGMENTID = [SC].[SEGMENTATIONSEGMENTID],' + char(13) +
                     '  @TESTSEGMENTID = [SC].[SEGMENTATIONTESTSEGMENTID],' + char(13) +
                     '  @SOURCECODE = [FT].[SOURCECODE]' + char(13) +
                     'from [RANKEDIMPORT]' + char(13) +
                     'inner join dbo.[' + @FINDERFILETABLE + '] as [FT] on [FT].[ID] = [RANKEDIMPORT].[ID]' + char(13) +
                     'inner join dbo.[MKTSOURCECODEMAP] as [SC] on [FT].[SOURCECODE] = [SC].[SOURCECODE] and [SC].[SEGMENTATIONID] = @MAILINGID' + char(13) +
                     'where [RANKEDIMPORT].[RANK] = 1';

          set @PARAMDEF = N'@MAILINGID uniqueidentifier, @FINDERNUMBER bigint, @SEGMENTID uniqueidentifier output, @TESTSEGMENTID uniqueidentifier output, @SOURCECODE nvarchar(50) output';

          exec sp_executesql @SQL, @PARAMDEF,
            @MAILINGID = @MAILINGID,
            @FINDERNUMBER = @FINDERNUMBER,
            @SEGMENTID = @SEGMENTID output,
            @TESTSEGMENTID = @TESTSEGMENTID output,
            @SOURCECODE = @SOURCECODE output;
        end


      --If no match was found, then check the mailing data table...

      if @SEGMENTID is null
        begin
          set @ACTIVATIONTABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@MAILINGID);

          set @SQL = 'select' + char(13) +
                     '  @SEGMENTID = [SEGMENTID],' + char(13) +
                     '  @TESTSEGMENTID = [TESTSEGMENTID],' + char(13) +
                     '  @SOURCECODE = [SOURCECODE]' + char(13) +
                     'from dbo.[' + @ACTIVATIONTABLENAME + ']' + char(13) +
                     'where [FINDERNUMBER] = @FINDERNUMBER';

          set @PARAMDEF = N'@FINDERNUMBER bigint, @SEGMENTID uniqueidentifier output, @TESTSEGMENTID uniqueidentifier output, @SOURCECODE nvarchar(50) output';

          exec sp_executesql @SQL, @PARAMDEF,
            @FINDERNUMBER = @FINDERNUMBER,
            @SEGMENTID = @SEGMENTID output,
            @TESTSEGMENTID = @TESTSEGMENTID output,
            @SOURCECODE = @SOURCECODE output;
        end


      --Lookup segment and package info...

      if @SEGMENTID is not null
        begin
          if @TESTSEGMENTID is not null
            select
              @SEGMENTNAME = dbo.[UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME]([MKTSEGMENTATIONTESTSEGMENT].[ID]),
              @PACKAGENAME = [MKTPACKAGE].[NAME],
              @PACKAGEID = [MKTPACKAGE].[ID]
            from dbo.[MKTSEGMENTATIONTESTSEGMENT]
            inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
            where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;
          else
            select
              @SEGMENTNAME = [MKTSEGMENT].[NAME],
              @PACKAGENAME = [MKTPACKAGE].[NAME],
              @PACKAGEID = [MKTPACKAGE].[ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
            inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
            where [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTID;
        end


      --Lookup the appeal information from the mailing...

      select top 1
        @APPEALSYSTEMID = [APPEALSYSTEMID]
      from dbo.[MKTSEGMENTATIONACTIVATE]
      where [SEGMENTATIONID] = @MAILINGID
      and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([RECORDSOURCEID]) = 1;

      if len(@APPEALSYSTEMID) > 0
        begin
          --The marketing tables stores the appeal as an nvarchar(36). This is for RE7 compatibility.

          --Historically in early versions the appeal did not always exist, so this try/catch protects from this.

          begin try
            select
              @APPEALID = [ID],
              @APPEALNAME = [NAME]
            from dbo.[APPEAL]
            where [ID] = convert(uniqueidentifier, @APPEALSYSTEMID);
          end try
          begin catch
            set @APPEALID = null;
            set @APPEALNAME = N'';
          end catch
        end
    end


  --Return the results...

  select
    @MAILINGID as [MAILINGID],
    isnull((select [NAME] from dbo.[MKTSEGMENTATION] where [ID] = @MAILINGID), '') as [MAILINGNAME],
    @APPEALID as [APPEALID],
    isnull(@APPEALNAME, '') as [APPEALNAME],
    isnull(@SOURCECODE, '') as [SOURCECODE],
    isnull(@SEGMENTNAME, '') as [SEGMENTNAME],
    isnull(@PACKAGENAME, '') as [PACKAGENAME]
  where @MAILINGID is not null;

  return 0;