UFN_MKTSOURCECODE_BUILDCODESFORSEGMENTATION

CREATE function dbo.[UFN_MKTSOURCECODE_BUILDCODESFORSEGMENTATION]
(
  @SEGMENTATIONID uniqueidentifier,
  @SOURCECODEITEMID uniqueidentifier,
  @SEGMENTTYPECODE tinyint -- 0 = segment, 1 = test segment, 2 = both

)
returns @SOURCECODE table
(
  [SEGMENTATIONSEGMENTID] uniqueidentifier,
  [SOURCECODE] nvarchar(50)
)
as
begin
  declare @ACTIVE bit;
  declare @SOURCECODEID uniqueidentifier;
  declare @SOURCECODEISHISTORICAL bit;

  -- get general segmentation information

  select 
    @ACTIVE = [ACTIVE],
    @SOURCECODEID = [SOURCECODEID],
    @SOURCECODEISHISTORICAL = isnull([MKTSOURCECODE].[ISHISTORICAL], 0)
  from dbo.[MKTSEGMENTATION]
  left outer join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID

  -- if there isn't a source code, don't do anything else

  if @SOURCECODEID is not null
    if @SOURCECODEISHISTORICAL = 0
      begin
        -- get the number of parts in the given source code

        declare @MAXSEQUENCE tinyint;
        select @MAXSEQUENCE = max(sequence) 
        from MKTSOURCECODEITEM 
        where SOURCECODEID = @SOURCECODEID;

        -- this table will hold information needed for list part types, which is defined on the segment

        declare @SEGMENTLISTCODE table
        (
          [SEGMENTID] uniqueidentifier,
          [ISCONSOLIDATEDVIEWSEGMENT] bit,
          [LISTCODE] nvarchar(10),
          [LISTPARTDEFINITIONVALUESID] uniqueidentifier
        );

        if @ACTIVE = 0
          -- get the list code from the record source, list, or consolidated list

          insert into @SEGMENTLISTCODE
          (
            [SEGMENTID],
            [ISCONSOLIDATEDVIEWSEGMENT],
            [LISTCODE],
            [LISTPARTDEFINITIONVALUESID]
          )
          select distinct
            [MKTSEGMENTATIONSEGMENT].[ID],
            dbo.[UFN_MKTSEGMENT_ISCONSOLIDATEDVIEWSEGMENT]([MKTSEGMENT].[ID]),
            case when exists (select [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] 
                              from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] 
                              inner join dbo.[QUERYVIEWCATALOG] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID] 
                              where [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID])
                  then ''
                  else isnull([MKTLIST].[CODE], (select [MKTRECORDSOURCE].[CODE]
                                                from dbo.[MKTRECORDSOURCE]
                                                where [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
                                                and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1))
            end as [LISTCODE],
            case when exists (select [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] 
                              from dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] 
                              inner join dbo.[QUERYVIEWCATALOG] on [MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID] 
                              where [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID])
                  then null
                  else isnull([MKTLIST].[PARTDEFINITIONVALUESID], (select [MKTRECORDSOURCE].[PARTDEFINITIONVALUESID] 
                                                                  from dbo.[MKTRECORDSOURCE] 
                                                                  where [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID] 
                                                                  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1))
            end as [LISTPARTDEFINITIONVALUESID]
          from dbo.[MKTSEGMENTATIONSEGMENT] 
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID] 
          left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          left join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
          left join dbo.[IDSETREGISTER] on [MKTSEGMENT].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
          left join dbo.[RECORDTYPE] on [IDSETREGISTER].[RECORDTYPEID] = [RECORDTYPE].[ID]
          where [MKTSEGMENTATIONSEGMENT].SEGMENTATIONID = @SEGMENTATIONID
          option (RECOMPILE);
        else
          insert into @SEGMENTLISTCODE
          (
            [SEGMENTID],
            [ISCONSOLIDATEDVIEWSEGMENT],
            [LISTCODE],
            [LISTPARTDEFINITIONVALUESID]
          )
          select 
            [MKTSEGMENTATIONSEGMENT].[ID],
            dbo.[UFN_MKTSEGMENT_ISCONSOLIDATEDVIEWSEGMENT]([MKTSEGMENT].[ID]),
            isnull([MKTSOURCECODEPART].[CODE], ''),
            [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
          from dbo.[MKTSEGMENTATIONSEGMENT]
          inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
          inner join dbo.[MKTRECORDSOURCE] on [MKTRECORDSOURCE].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID]
          left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENT].[IDSETREGISTERID]
          left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].SEGMENTATIONID 
          and [MKTSOURCECODEPART].[LISTID] = isnull([MKTSEGMENTLIST].[LISTID], [MKTRECORDSOURCE].[ID])
          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
          and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1
          option (RECOMPILE);

        -- if we want source codes for regular segments, add them to the output table

        if @SEGMENTTYPECODE = 0 or @SEGMENTTYPECODE = 2
          insert into @SOURCECODE
          (
            [SEGMENTATIONSEGMENTID],
            [SOURCECODE]
          )
          select
            [MKTSEGMENTATIONSEGMENT].[ID], 
            dbo.[UFN_MKTSOURCECODE_BUILDCODEWITHSEGMENTINFO] (
              [MKTSEGMENTATIONSEGMENT].[ID],
              @SEGMENTATIONID,
              @ACTIVE,
              @SOURCECODEID,
              @MAXSEQUENCE, 
              [LISTTABLE].[LISTCODE],
              [LISTTABLE].[LISTPARTDEFINITIONVALUESID],
              [LISTTABLE].[ISCONSOLIDATEDVIEWSEGMENT],
              @SOURCECODEITEMID) as [SOURCECODE]
            from dbo.[MKTSEGMENTATIONSEGMENT] 
            left join @SEGMENTLISTCODE as [LISTTABLE] on [LISTTABLE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
            where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;

        -- if we want source codes for test segment, add them to the output table

        if @SEGMENTTYPECODE = 1 or @SEGMENTTYPECODE = 2
          insert into @SOURCECODE
          (
            [SEGMENTATIONSEGMENTID],
            [SOURCECODE]
          )
          select
            [MKTSEGMENTATIONTESTSEGMENT].[ID], 
            dbo.[UFN_MKTSOURCECODE_BUILDCODEWITHSEGMENTINFO] (
              [MKTSEGMENTATIONTESTSEGMENT].[ID],
              @SEGMENTATIONID,
              @ACTIVE,
              @SOURCECODEID,
              @MAXSEQUENCE, 
              [LISTTABLE].[LISTCODE],
              [LISTTABLE].[LISTPARTDEFINITIONVALUESID],
              [LISTTABLE].[ISCONSOLIDATEDVIEWSEGMENT],
              @SOURCECODEITEMID)
          from dbo.[MKTSEGMENTATIONTESTSEGMENT] 
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
          left join @SEGMENTLISTCODE as [LISTTABLE] on [LISTTABLE].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;
      end
    else -- @SOURCECODEISHISTORICAL = 1

      begin
        if @SEGMENTTYPECODE = 0 or @SEGMENTTYPECODE = 2
          insert into @SOURCECODE
          (
            [SEGMENTATIONSEGMENTID],
            [SOURCECODE]
          )
          select
            [MKTSEGMENTATIONSEGMENT].[ID], 
            [MKTSOURCECODEMAP].[SOURCECODE]
          from dbo.[MKTSEGMENTATIONSEGMENT] 
          inner join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID
                                            and [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                                            and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] is null
          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;

        -- if we want source codes for test segment, add them to the output table

        if @SEGMENTTYPECODE = 1 or @SEGMENTTYPECODE = 2
          insert into @SOURCECODE
          (
            [SEGMENTATIONSEGMENTID],
            [SOURCECODE]
          )
          select
            [MKTSEGMENTATIONTESTSEGMENT].[ID], 
            [MKTSOURCECODEMAP].[SOURCECODE]
          from dbo.[MKTSEGMENTATIONTESTSEGMENT] 
          inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
          inner join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[SEGMENTATIONID] = @SEGMENTATIONID
                                            and [MKTSOURCECODEMAP].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                                            and [MKTSOURCECODEMAP].[SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]
          where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID;
      end

  return;
end