UFN_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTATIONPACKAGELISTFOREXPORTDEFINITION

CREATE function dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESS_GETSEGMENTATIONPACKAGELISTFOREXPORTDEFINITION]
(
  @EXPORTDEFINITIONID uniqueidentifier,
  @SEGMENTATIONID uniqueidentifier,
  @SEGMENTATIONEXPORTPROCESSID uniqueidentifier = null
)
returns nvarchar(max)
as
  begin
    declare @PACKAGES nvarchar(max);
    declare @CHANNELCODE tinyint;

    select @PACKAGES = coalesce(@PACKAGES + ', ', '') + [NAME]
    from (
      -- phone packages, mail and email packages without content: MKTPACKAGE.EXPORTDEFINITIONID

      select distinct [MKTPACKAGE].[NAME]
      from dbo.[MKTSEGMENTATIONSEGMENT] 
      left outer join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] 
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] 
      left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID] 
      inner join dbo.[MKTPACKAGE] on ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID]) 
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID 
      and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) 
      and [MKTPACKAGE].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID  

      union

      -- mail packages with content: LETTERCODE.EXPORTDEFINITIONID

      select distinct [MKTPACKAGE].[NAME]
      from dbo.[MKTSEGMENTATIONSEGMENT] 
      left outer join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] 
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] 
      left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID] 
      inner join dbo.[MKTPACKAGE] on ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID]) 
      inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID] 
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID 
      and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) 
      and [LETTERCODE].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID

      union

      -- email packages with content: MKTPACKAGE.NETCOMMUNITYDATASOURCEID

      select distinct [MKTPACKAGE].[NAME]
      from dbo.[MKTSEGMENTATIONSEGMENT] 
      left outer join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] 
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] 
      left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID] 
      inner join dbo.[MKTPACKAGE] on ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID]) 
      inner join dbo.[MKTEXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on ([EMAILEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0) 
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID 
      and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) 
      and [EMAILEXPORTDEFINITION].[EXPORTDEFINITIONID] = @EXPORTDEFINITIONID
    ) as [TEMP];

    if @SEGMENTATIONEXPORTPROCESSID is not null and
       (exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [MAILEXPORTDEFINITIONID] = @EXPORTDEFINITIONID) or
        exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [EMAILEXPORTDEFINITIONID] = @EXPORTDEFINITIONID) or
        exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [PHONEEXPORTDEFINITIONID] = @EXPORTDEFINITIONID))
      -- tack on any packages associated with this export definition by virtue of the default setting for the export

      begin
        if len(@PACKAGES) = 0 set @PACKAGES = null;

        if exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [MAILEXPORTDEFINITIONID] = @EXPORTDEFINITIONID)
          set @CHANNELCODE = 0;
        else if exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [EMAILEXPORTDEFINITIONID] = @EXPORTDEFINITIONID)
          set @CHANNELCODE = 1;
        else if exists (select top 1 1 from dbo.[MKTSEGMENTATIONEXPORTPROCESS] where [ID] = @SEGMENTATIONEXPORTPROCESSID and [PHONEEXPORTDEFINITIONID] = @EXPORTDEFINITIONID)
          set @CHANNELCODE = 2;

        select @PACKAGES = coalesce(@PACKAGES + ', ', '') + [NAME]
          from (select distinct [MKTPACKAGE].[NAME]
                  from dbo.[MKTSEGMENTATIONSEGMENT]
                    left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
                    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
                    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[SEGMENTID] = [MKTSEGMENT].[ID]
                    inner join dbo.[MKTPACKAGE] on ([MKTSEGMENTATIONSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID] or [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = [MKTPACKAGE].[ID])
                  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
                    and [MKTPACKAGE].[CHANNELCODE] = @CHANNELCODE and ([MKTPACKAGE].[LETTERCODEID] is null and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] = 0 and [MKTPACKAGE].[EXPORTDEFINITIONID] is null)
                    and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)) as [TEMP];
      end;

    return @PACKAGES;
  end;