UFN_MKTSOURCECODE_AUTOINCREMENTCODE

CREATE function dbo.[UFN_MKTSOURCECODE_AUTOINCREMENTCODE]
(
  @PARTDEFINITIONVALUESID uniqueidentifier,
  @MAILINGTYPECODE tinyint = 0
)
/*
  Finds the next available source code for a given source code part definition values ID.
*/
returns nvarchar(10)
as
  begin
    declare @ITEMTYPECODE tinyint;
    declare @FORMAT nvarchar(10);
    declare @NEXTSOURCECODEPART nvarchar(10);
    declare @CODEEXISTS bit;
    declare @COUNT integer;
    declare @LASTCODE nvarchar(50);
    declare @ROWCOUNT int;

    set @NEXTSOURCECODEPART = '';

    select
      @ITEMTYPECODE = [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
      @FORMAT = [MKTSOURCECODEPARTDEFINITIONVALUES].[FORMAT]
    from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[MKTSOURCECODEPARTDEFINITIONID]
    where [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID;

    if (@ITEMTYPECODE = 0)  -- Mailing

      begin
        set @LASTCODE = '';

        /* White mail segment */
        if @MAILINGTYPECODE = 254
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            select @ROWCOUNT = count([CODE]) from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Direct/Appeal/Public media */
        else if @MAILINGTYPECODE in (0, 4)
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTSEGMENTATION] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            set @ROWCOUNT = (select count([CODE]) from dbo.[MKTSEGMENTATION] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID)
                          + (select count([CODE]) from dbo.[MKTMARKETINGPLANITEM] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID);
          end

        /* Acknowledgement */
        else if @MAILINGTYPECODE = 1
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            select @ROWCOUNT = count([CODE]) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Membership */
        else if @MAILINGTYPECODE = 2
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            select @ROWCOUNT = count([CODE]) from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Sponsorship */
        else if @MAILINGTYPECODE = 3
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            select @ROWCOUNT = count([CODE]) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)

        set @CODEEXISTS = case
          when @MAILINGTYPECODE = 254 then
            case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE in (0, 4) then
            case when exists (select top 1 1 from dbo.[MKTSEGMENTATION] where [CODE] = @NEXTSOURCECODEPART) or exists (select top 1 1 from dbo.[MKTMARKETINGPLANITEM] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 1 then
            case when exists (select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 2 then
            case when exists (select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 3 then
            case when exists (select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          end;

        set @COUNT = 0;

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)

            set @CODEEXISTS = case 
              when @MAILINGTYPECODE = 254 then
                case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE in (0, 4) then
                case when exists (select top 1 1 from dbo.[MKTSEGMENTATION] where [CODE] = @NEXTSOURCECODEPART) or exists (select top 1 1 from dbo.[MKTMARKETINGPLANITEM] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 1 then
                case when exists (select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 2 then
                case when exists (select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 3 then
                case when exists (select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              end;

            set @COUNT += 1;
          end
      end

    else if (@ITEMTYPECODE = 1) -- Segment

      begin
        set @LASTCODE = '';

        select top 1 @LASTCODE = [CODE] from dbo.[MKTSEGMENT] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)
        set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTSEGMENT] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end;
        set @COUNT = 0;

        /* Need to set rowcount to number of segments with the code value */
        select @ROWCOUNT = count([CODE]) from dbo.[MKTSEGMENT] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)
            set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTSEGMENT] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end;
            set @COUNT += 1;
          end
      end

    else if (@ITEMTYPECODE = 2) -- Package

      begin
        set @LASTCODE = '';

        select top 1 @LASTCODE = [CODE]
        from (
          select [CODE] from dbo.[MKTPACKAGE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES]
        order by upper([CODE]) desc;

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)    
        set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTPACKAGE] where [CODE] = @NEXTSOURCECODEPART) 
                                 or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                               then 1 else 0 end;
        set @COUNT = 0;

        /* Need to set rowcount to number of packages with the code value */
        select @ROWCOUNT = count([CODE])
        from (
          select [CODE] from dbo.[MKTPACKAGE] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES];

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)
            set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTPACKAGE] where [CODE] = @NEXTSOURCECODEPART) 
                                     or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                                   then 1 else 0 end;
            set @COUNT += 1;
          end
      end

    else if (@ITEMTYPECODE = 3) -- Channel

      begin
        set @LASTCODE = '';

        select top 1 @LASTCODE = [CODE]
        from (
          select [CHANNELSOURCECODE] as [CODE] from dbo.[MKTPACKAGE] where [CHANNELPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES]
        order by upper([CODE]) desc;

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)  
        set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTPACKAGE] where [CHANNELSOURCECODE] = @NEXTSOURCECODEPART)
                                 or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                               then 1 else 0 end;
        set @COUNT = 0;

        /* Need to set rowcount to number of packages with the code value */
        select @ROWCOUNT = count([CODE])
        from (
          select [CHANNELSOURCECODE] as [CODE] from dbo.[MKTPACKAGE] where [CHANNELPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES];

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)
            set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTPACKAGE] where [CHANNELSOURCECODE] = @NEXTSOURCECODEPART)
                                     or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                                   then 1 else 0 end;
            set @COUNT += 1;
          end
      end

    else if (@ITEMTYPECODE = 5) -- User defined

      begin
        set @LASTCODE = '';

        select top 1 @LASTCODE = [CODE] from dbo.[MKTSOURCECODEPART] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)  
        set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end;
        set @COUNT = 0;

        /* Need to set rowcount to number of user defined codes with the code value */
        select @ROWCOUNT = count([CODE]) from dbo.[MKTSOURCECODEPART] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)
            set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end;
            set @COUNT += 1;
          end
      end

    else if (@ITEMTYPECODE = 6) -- List

      begin
        set @LASTCODE = '';

        select top 1 @LASTCODE = [CODE] 
        from (
          select [CODE] from dbo.[MKTLIST] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES]
        order by upper([CODE]) desc;

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)      
        set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTLIST] where [CODE] = @NEXTSOURCECODEPART) 
                                 or exists (select top 1 1 from dbo.[MKTRECORDSOURCE] where [CODE] = @NEXTSOURCECODEPART)
                                 or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                               then 1 else 0 end;
        set @COUNT = 0;

        /* Need to set rowcount to number of lists with the code value */
        select @ROWCOUNT = count([CODE]) 
        from (
          select [CODE] from dbo.[MKTLIST] where [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
          union
          select [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID
        ) as [CODES];          

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)
            set @CODEEXISTS = case when exists (select top 1 1 from dbo.[MKTLIST] where [CODE] = @NEXTSOURCECODEPART) 
                                     or exists (select top 1 1 from dbo.[MKTRECORDSOURCE] where [CODE] = @NEXTSOURCECODEPART)
                                     or exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART)
                                   then 1 else 0 end;
            set @COUNT += 1;
          end
      end 

    else if (@ITEMTYPECODE = 7) -- Test segment

      begin
        set @LASTCODE = '';

        /* White mail segment */
        if @MAILINGTYPECODE = 254
          begin
            select top 1 @LASTCODE = [CODE] from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([CODE]) desc;
            select @ROWCOUNT = count([CODE]) from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Appeal */
        else if @MAILINGTYPECODE = 0
          begin
            select top 1 @LASTCODE = [TESTSEGMENTCODE] from dbo.[MKTSEGMENTATIONSEGMENT] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([TESTSEGMENTCODE]) desc;
            select @ROWCOUNT = count([TESTSEGMENTCODE]) from dbo.[MKTSEGMENTATIONSEGMENT] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Acknowledgement */
        else if @MAILINGTYPECODE = 1
          begin
            select top 1 @LASTCODE = [TESTSEGMENTCODE] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([TESTSEGMENTCODE]) desc;
            select @ROWCOUNT = count([TESTSEGMENTCODE]) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Membership */
        else if @MAILINGTYPECODE = 2
          begin
            select top 1 @LASTCODE = [TESTSEGMENTCODE] from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([TESTSEGMENTCODE]) desc;
            select @ROWCOUNT = count([TESTSEGMENTCODE]) from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        /* Sponsorship */
        else if @MAILINGTYPECODE = 3
          begin
            select top 1 @LASTCODE = [TESTSEGMENTCODE] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID order by upper([TESTSEGMENTCODE]) desc;
            select @ROWCOUNT = count([TESTSEGMENTCODE]) from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [TESTPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID;
          end

        set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @LASTCODE)

        set @CODEEXISTS = case 
          when @MAILINGTYPECODE = 254 then
            case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 0 then
            case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 1 then
            case when exists (select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 2 then
            case when exists (select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          when @MAILINGTYPECODE = 3 then
            case when exists (select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
          end;

        set @COUNT = 0;

        /* Loop until we find a unique code or all the code possibilities have been exhausted */
        while (@CODEEXISTS = 1 and @COUNT < @ROWCOUNT)
          begin
            set @NEXTSOURCECODEPART = dbo.[UFN_MKTSOURCECODE_BUILDNEXTVALUE](@FORMAT, @NEXTSOURCECODEPART)

            set @CODEEXISTS = case 
              when @MAILINGTYPECODE = 254 then
                case when exists (select top 1 1 from dbo.[MKTSOURCECODEPART] where [WHITEMAILSEGMENTID] is not null and [PARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID and [CODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 0 then
                case when exists (select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 1 then
                case when exists (select top 1 1 from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
    when @MAILINGTYPECODE = 2 then
                case when exists (select top 1 1 from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              when @MAILINGTYPECODE = 3 then
                case when exists (select top 1 1 from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [TESTSEGMENTCODE] = @NEXTSOURCECODEPART) then 1 else 0 end
              end;

            set @COUNT += 1;
          end
      end

  return @NEXTSOURCECODEPART;
end