USP_DATALIST_MKTSEGMENTATIONSEGMENT_MEMBERREVENUE

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTATIONSEGMENT_MEMBERREVENUE]
(
  @MAILINGSEGMENTID uniqueidentifier,
  @TOTALROWS int = 500,
  @FILTER nvarchar(253) = null
)
as
  set nocount on;

  declare @ISTESTSEGMENT bit;
  declare @PARENTTABLENAME nvarchar(128);
  declare @PARENTID nvarchar(255);
  declare @RECORDSOURCEID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @SQL nvarchar(max);
  declare @FIXEDWIDTH nvarchar(2);
  declare @ISBBEC bit;
  declare @DONORIDSQL nvarchar(128);

  begin try
    if not exists(select [ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] where [ID] = @MAILINGSEGMENTID)
      select
        @ISTESTSEGMENT = 0,
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
        @PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
        @FIXEDWIDTH = convert(nvarchar(2), dbo.[UFN_MKTFINDERNUMBER_GETFIXEDWIDTH](1, [MKTSEGMENTATION].[ID])),
        @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTATIONSEGMENT].[ID] = @MAILINGSEGMENTID;
    else
      select
        @ISTESTSEGMENT = 1,
        @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
        @RECORDSOURCEID = [MKTSEGMENT].[QUERYVIEWCATALOGID],
        @PARENTTABLENAME = [QUERYVIEWCATALOG].[OBJECTNAME],
        @PARENTID = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
        @FIXEDWIDTH = convert(nvarchar(2), dbo.[UFN_MKTFINDERNUMBER_GETFIXEDWIDTH](1, [MKTSEGMENTATION].[ID])),
        @ISBBEC = dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTSEGMENT].[QUERYVIEWCATALOGID])
      from dbo.[MKTSEGMENTATIONTESTSEGMENT]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
      where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @MAILINGSEGMENTID;

    -- these represent the mapped fields --

    declare @FULLNAMEFIELDNAME nvarchar(255);
    declare @FIRSTNAMEFIELDNAME nvarchar(255);
    declare @MIDDLENAMEFIELDNAME nvarchar(255);
    declare @LASTNAMEFIELDNAME nvarchar(255);
    declare @TITLEFIELDNAME nvarchar(255);
    declare @SUFFIXFIELDNAME nvarchar(255);
    declare @COUNTRYFIELDNAME nvarchar(255);
    declare @ADDRESSLINE1FIELDNAME nvarchar(255);
    declare @ADDRESSLINE2FIELDNAME nvarchar(255);
    declare @CITYFIELDNAME nvarchar(255);
    declare @STATEFIELDNAME nvarchar(255);
    declare @POSTCODEFIELDNAME nvarchar(255);
    declare @CARTFIELDNAME nvarchar(255);
    declare @DPCFIELDNAME nvarchar(255);
    declare @LOTFIELDNAME nvarchar(255);
    declare @PHONENUMBERFIELDNAME nvarchar(255);
    declare @EMAILADDRESSFIELDNAME nvarchar(255);
    declare @FIRSTGIFTDATEFIELDNAME nvarchar(255);
    declare @ADDRESSLINE3FIELDNAME nvarchar(255);
    declare @ADDRESSLINE4FIELDNAME nvarchar(255);
    declare @ADDRESSLINE5FIELDNAME nvarchar(255);

    declare @GIFTVIEWNAME nvarchar(128);
    declare @GIFTVIEWPRIMARYKEYFIELD nvarchar(255)
    declare @GIFTAMOUNTFIELDNAME nvarchar(255);
    declare @GIFTDATEFIELDNAME nvarchar(255);
    declare @GIFTLOOKUPIDFIELDNAME nvarchar(255);

    declare @BASECURRENCYIDFIELDNAME nvarchar(255);
    declare @LOOKUPIDFIELDNAME nvarchar(255);

    -- grab the field names --

    select
      @FULLNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FULLNAMEFIELD],
      @FIRSTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTNAMEFIELD],
      @MIDDLENAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[MIDDLENAMEFIELD],
      @LASTNAMEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LASTNAMEFIELD],
      @TITLEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[TITLEFIELD],
      @SUFFIXFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[SUFFIXFIELD],
      @COUNTRYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[COUNTRYFIELD],
      @ADDRESSLINE1FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE1FIELD],
      @ADDRESSLINE2FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE2FIELD],
      @ADDRESSLINE3FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE3FIELD],
      @ADDRESSLINE4FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE4FIELD],
      @ADDRESSLINE5FIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[ADDRESSLINE5FIELD],
      @CITYFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CITYFIELD],
      @STATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[STATEFIELD],
      @POSTCODEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[POSTCODEFIELD],
      @CARTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[CARTFIELD],
      @DPCFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[DPCFIELD],
      @LOTFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOTFIELD],
      @PHONENUMBERFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[PHONENUMBERFIELD],
      @EMAILADDRESSFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[EMAILADDRESSFIELD],
      @FIRSTGIFTDATEFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[FIRSTGIFTDATEFIELD],
      @GIFTVIEWNAME = [QUERYVIEWCATALOG].[OBJECTNAME],
      @GIFTVIEWPRIMARYKEYFIELD = [QUERYVIEWCATALOG].[PRIMARYKEYFIELD],
      @GIFTAMOUNTFIELDNAME = [MKTGIFTRECORDSOURCE].[AMOUNTFIELD],
      @GIFTDATEFIELDNAME = [MKTGIFTRECORDSOURCE].[DATEFIELD],
      @BASECURRENCYIDFIELDNAME = [MKTGIFTRECORDSOURCE].[BASECURRENCYIDFIELD],
      @GIFTLOOKUPIDFIELDNAME = [MKTGIFTRECORDSOURCE].[LOOKUPIDFIELD],
      @LOOKUPIDFIELDNAME = [MKTRECORDSOURCEFIELDMAPPINGS].[LOOKUPIDFIELD]
    from dbo.[MKTRECORDSOURCEFIELDMAPPINGS]
    inner join dbo.[MKTGIFTRECORDSOURCE] on [MKTGIFTRECORDSOURCE].[ID] = [MKTRECORDSOURCEFIELDMAPPINGS].[ID]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTGIFTRECORDSOURCE].[QUERYVIEWCATALOGID]
    where [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = @RECORDSOURCEID;

    if @ISBBEC = 1
      -- go straight to the FINANCIALTRANSACTION table - the gift record source query view filters out some gifts by gift type

      -- (export does something analogous to this)

      begin
        set @GIFTVIEWNAME = 'FINANCIALTRANSACTION';
        set @GIFTVIEWPRIMARYKEYFIELD = 'ID';
        set @GIFTAMOUNTFIELDNAME = 'BASEAMOUNT';
        set @GIFTDATEFIELDNAME = 'DATE';
        set @BASECURRENCYIDFIELDNAME = 'BASECURRENCYID';
        set @GIFTLOOKUPIDFIELDNAME = 'CALCULATEDUSERDEFINEDID';
      end

    -- see if the fields exist in the table/view and clear the name if necessary --

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FULLNAMEFIELDNAME)
      set @FULLNAMEFIELDNAME = '[P].[' + @FULLNAMEFIELDNAME + ']';
    else
      set @FULLNAMEFIELDNAME = '''''';

    set @FULLNAMEFIELDNAME = '(case when [P].[' + @PARENTID + '] is null then ''<Deleted>'' else ' + @FULLNAMEFIELDNAME + ' end)';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTNAMEFIELDNAME)
      set @FIRSTNAMEFIELDNAME = '[P].[' + @FIRSTNAMEFIELDNAME + ']';
    else
      set @FIRSTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @MIDDLENAMEFIELDNAME)
      set @MIDDLENAMEFIELDNAME = '[P].[' + @MIDDLENAMEFIELDNAME + ']';
    else
      set @MIDDLENAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LASTNAMEFIELDNAME)
      set @LASTNAMEFIELDNAME = '[P].[' + @LASTNAMEFIELDNAME + ']';
    else
      set @LASTNAMEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @TITLEFIELDNAME) 
      set @TITLEFIELDNAME = '[P].[' + @TITLEFIELDNAME + ']';
    else
      set @TITLEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @SUFFIXFIELDNAME)
      set @SUFFIXFIELDNAME = '[P].[' + @SUFFIXFIELDNAME + ']';
    else
      set @SUFFIXFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @COUNTRYFIELDNAME)
      set @COUNTRYFIELDNAME = '[P].[' + @COUNTRYFIELDNAME + ']';
    else
      set @COUNTRYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE1FIELDNAME)
      set @ADDRESSLINE1FIELDNAME = '[P].[' + @ADDRESSLINE1FIELDNAME + ']';
    else
      set @ADDRESSLINE1FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE2FIELDNAME)
      set @ADDRESSLINE2FIELDNAME = '[P].[' + @ADDRESSLINE2FIELDNAME + ']';
    else
      set @ADDRESSLINE2FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE3FIELDNAME)
      set @ADDRESSLINE3FIELDNAME = '[P].[' + @ADDRESSLINE3FIELDNAME + ']';
    else
      set @ADDRESSLINE3FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE4FIELDNAME)
      set @ADDRESSLINE4FIELDNAME = '[P].[' + @ADDRESSLINE4FIELDNAME + ']';
    else
      set @ADDRESSLINE4FIELDNAME = '''''';

    if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @ADDRESSLINE5FIELDNAME)
      set @ADDRESSLINE5FIELDNAME = '[P].[' + @ADDRESSLINE5FIELDNAME + ']';
    else
      set @ADDRESSLINE5FIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CITYFIELDNAME)
      set @CITYFIELDNAME = '[P].[' + @CITYFIELDNAME + ']';
    else
      set @CITYFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @STATEFIELDNAME)
      set @STATEFIELDNAME = '[P].[' + @STATEFIELDNAME + ']';
    else
      set @STATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @POSTCODEFIELDNAME)
      set @POSTCODEFIELDNAME = '[P].[' + @POSTCODEFIELDNAME + ']';
    else
      set @POSTCODEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @CARTFIELDNAME)
      set @CARTFIELDNAME = '[P].[' + @CARTFIELDNAME + ']';
    else
      set @CARTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @DPCFIELDNAME)
      set @DPCFIELDNAME = '[P].[' + @DPCFIELDNAME + ']';
    else
      set @DPCFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOTFIELDNAME)
      set @LOTFIELDNAME = '[P].[' + @LOTFIELDNAME + ']';
    else
      set @LOTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @PHONENUMBERFIELDNAME)
      set @PHONENUMBERFIELDNAME = '[P].[' + @PHONENUMBERFIELDNAME + ']';
    else
      set @PHONENUMBERFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @EMAILADDRESSFIELDNAME)
      set @EMAILADDRESSFIELDNAME = '[P].[' + @EMAILADDRESSFIELDNAME + ']';
    else
      set @EMAILADDRESSFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @FIRSTGIFTDATEFIELDNAME)
      set @FIRSTGIFTDATEFIELDNAME = '[P].[' + @FIRSTGIFTDATEFIELDNAME + ']';
    else
      set @FIRSTGIFTDATEFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTAMOUNTFIELDNAME)
      set @GIFTAMOUNTFIELDNAME = '[GIFTS].[' + @GIFTAMOUNTFIELDNAME + ']';
    else
      set @GIFTAMOUNTFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTDATEFIELDNAME)
      set @GIFTDATEFIELDNAME = '[GIFTS].[' + @GIFTDATEFIELDNAME + ']';
    else
      set @GIFTDATEFIELDNAME = '''''';

    if @ISBBEC = 1
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = 'CURRENCYSET' and [COLUMN_NAME] = @BASECURRENCYIDFIELDNAME)
        set @BASECURRENCYIDFIELDNAME = '[CURRENCYSET].[' + @BASECURRENCYIDFIELDNAME + ']';
      else
        set @BASECURRENCYIDFIELDNAME = '''''';
    end
    else
    begin
      if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @BASECURRENCYIDFIELDNAME)
        set @BASECURRENCYIDFIELDNAME = '[GIFTS].[' + @BASECURRENCYIDFIELDNAME + ']';
      else
        set @BASECURRENCYIDFIELDNAME = '''''';
    end

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @PARENTTABLENAME and [COLUMN_NAME] = @LOOKUPIDFIELDNAME)
      set @LOOKUPIDFIELDNAME = '[P].[' + @LOOKUPIDFIELDNAME + ']';
    else
      set @LOOKUPIDFIELDNAME = '''''';

    if exists (select * from INFORMATION_SCHEMA.COLUMNS where [TABLE_NAME] = @GIFTVIEWNAME and [COLUMN_NAME] = @GIFTLOOKUPIDFIELDNAME)
      set @GIFTLOOKUPIDFIELDNAME = '[GIFTS].[' + @GIFTLOOKUPIDFIELDNAME + ']';
    else
      set @GIFTLOOKUPIDFIELDNAME = '''''';

    --Build the SQL

    set @SQL = 'select top ' + cast(@TOTALROWS as nvarchar(10)) + char(13) +
               '  ' + @FULLNAMEFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @MIDDLENAMEFIELDNAME + ',' + char(13) +
               '  ' + @LASTNAMEFIELDNAME + ',' + char(13) +
               '  ' + @TITLEFIELDNAME + ',' + char(13) +
               '  ' + @SUFFIXFIELDNAME + ',' + char(13) +
               '  ' + @GIFTAMOUNTFIELDNAME + ',' + char(13) +
               '  ' + @GIFTDATEFIELDNAME + ',' + char(13) +
               '  ' + @COUNTRYFIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE1FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE2FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE3FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE4FIELDNAME + ',' + char(13) +
               '  ' + @ADDRESSLINE5FIELDNAME + ',' + char(13) +
               '  ' + @CITYFIELDNAME + ',' + char(13) +
               '  ' + @STATEFIELDNAME + ',' + char(13) +
               '  ' + @POSTCODEFIELDNAME + ',' + char(13) +
               '  ' + @CARTFIELDNAME + ',' + char(13) +
               '  ' + @DPCFIELDNAME + ',' + char(13) +
               '  ' + @LOTFIELDNAME + ',' + char(13) +
               '  ' + @PHONENUMBERFIELDNAME + ',' + char(13) +
               '  ' + @EMAILADDRESSFIELDNAME + ',' + char(13) +
               '  ' + @FIRSTGIFTDATEFIELDNAME + ',' + char(13) +
               '  (case when [DONORS].[FINDERNUMBER] < 0 then null else right(N''00000000000000000000'' + cast([DONORS].[FINDERNUMBER] as nvarchar(20)), ' + @FIXEDWIDTH + ') end) as [FINDERNUMBER],' + char(13) +
               '  ' + @BASECURRENCYIDFIELDNAME + ',' + char(13) +
               '  ' + @LOOKUPIDFIELDNAME + ',' + char(13) +
               '  ' + @GIFTLOOKUPIDFIELDNAME + ' [REVENUELOOKUPID],' + char(13) +
               '  [P].[' + @PARENTID + '],' + char(13) +
               '  [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + ']' + char(13);

    set @SQL += 'from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DONORS]' + char(13) +
                'inner join dbo.[' + @GIFTVIEWNAME + '] as [GIFTS] on [GIFTS].[' + @GIFTVIEWPRIMARYKEYFIELD + '] = [DONORS].[REVENUEID]' + char(13);

    if @ISBBEC = 1
      set @SQL = @SQL +
              'left outer join dbo.[PDACCOUNTSYSTEM] on [PDACCOUNTSYSTEM].[ID] = [GIFTS].[PDACCOUNTSYSTEMID]' + char(13) +
              'left outer join dbo.[CURRENCYSET] on [CURRENCYSET].[ID] = [PDACCOUNTSYSTEM].[CURRENCYSETID]' + char(13);

    --For BBEC only, check to see if we have any merged constituents in this mailing.  If so, then we want to show the original constituent.

    if @ISBBEC = 1 and exists(select * from dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] where [SEGMENTATIONID] = @SEGMENTATIONID)
      begin
        set @SQL += 'left join dbo.[MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS] on [MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[FINDERNUMBER] = [DONORS].[FINDERNUMBER]' + char(13);
        set @DONORIDSQL = 'isnull([MKTSEGMENTATIONMERGEDORIGINALCONSTITUENTS].[ORIGINALCONSTITUENTID], [DONORS].[DONORID])';
      end
    else
      set @DONORIDSQL = '[DONORS].[DONORID]';

    set @SQL += 'left join dbo.[' + @PARENTTABLENAME + '] as [P] on [P].[' + @PARENTID + '] = ' + @DONORIDSQL + char(13);

    if @ISTESTSEGMENT = 0
      set @SQL += 'where [DONORS].[SEGMENTID] = @MAILINGSEGMENTID' + char(13) +
                  'and [DONORS].[TESTSEGMENTID] is null' + char(13);
    else
      set @SQL += 'where [DONORS].[TESTSEGMENTID] = @MAILINGSEGMENTID' + char(13);

    --Filter the records, if specified

    declare @TEMPFILTER nvarchar(255);
    if @FILTER is not null and len(@FILTER) > 0
      begin
        set @TEMPFILTER = '%' + @FILTER + '%';
        set @SQL += 'and (' + @FULLNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @FIRSTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @MIDDLENAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LASTNAMEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @TITLEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @SUFFIXFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @GIFTAMOUNTFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @GIFTDATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @COUNTRYFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE1FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE2FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE3FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE4FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @ADDRESSLINE5FIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @CITYFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @STATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @POSTCODEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @CARTFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @DPCFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LOTFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @PHONENUMBERFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @EMAILADDRESSFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @FIRSTGIFTDATEFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @GIFTLOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                    '  or ' + @LOOKUPIDFIELDNAME + ' like @FILTER' + char(13) +
                    '  or (case when [DONORS].[FINDERNUMBER] < 0 then null else right(N''00000000000000000000'' + cast([DONORS].[FINDERNUMBER] as nvarchar(20)), ' + @FIXEDWIDTH + ') end) like @FILTER)' + char(13);
      end

    --Do NOT order the results here, for large amounts of data it takes too long for not much benefit.


    exec sp_executesql @SQL, N'@MAILINGSEGMENTID uniqueidentifier, @FILTER nvarchar(255)', @MAILINGSEGMENTID = @MAILINGSEGMENTID, @FILTER = @TEMPFILTER;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;