UFN_MKTSEGMENTWHITEMAIL_SOURCECODEISUNIQUE

create function dbo.[UFN_MKTSEGMENTWHITEMAIL_SOURCECODEISUNIQUE]
(
  @SEGMENTID uniqueidentifier
)
returns bit
as
begin
  declare @UNIQUE bit;
  declare @SOURCECODE nvarchar(50);
  declare @SEGMENTATIONID uniqueidentifier;

  set @UNIQUE = 1;

  select
    @SOURCECODE = dbo.[UFN_MKTSOURCECODE_BUILDCODE](@SEGMENTID, null, null),
    @SEGMENTATIONID = [SEGMENTATIONID]
  from dbo.[MKTSEGMENTWHITEMAIL]
  where [ID] = @SEGMENTID;

  if @SEGMENTATIONID is null
    -- the source code has to be unique among all marketing effort source codes and white mail segment source codes

    if exists (select top 1 1 from dbo.[MKTSOURCECODEMAP] where [SOURCECODE] = @SOURCECODE and isnull([WHITEMAILSEGMENTID], '00000000-0000-0000-0000-000000000000') <> @SEGMENTID)
      set @UNIQUE = 0
  else
    -- the source code has to be unique among all source codes in its marketing effort

    if exists (select top 1 1 from dbo.[MKTSOURCECODEMAP] where [SOURCECODE] = @SOURCECODE and [SEGMENTATIONID] = @SEGMENTATIONID and isnull([WHITEMAILSEGMENTID], '00000000-0000-0000-0000-000000000000') <> @SEGMENTID)
      set @UNIQUE = 0

  return @UNIQUE;
end