UFN_MKTSPONSORSHIPMAILINGTEMPLATE_BUILDSOURCECODE

CREATE function dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATE_BUILDSOURCECODE]
(
  @SPONSORSHIPMAILINGTEMPLATERULEID uniqueidentifier,
  @QUERYVIEWCATALOGID uniqueidentifier,
  @SOURCECODEITEMID uniqueidentifier = null 
)
returns nvarchar(50)
as
begin
  declare @TYPECODE tinyint;
  declare @VALUE nvarchar(10);
  declare @LISTCODE nvarchar(10);
  declare @LISTPARTDEFINITIONVALUESID uniqueidentifier;
  declare @DELIM nvarchar(1);
  declare @LENGTH tinyint;
  declare @SOURCECODE nvarchar(50);
  declare @ITEMID uniqueidentifier;
  declare @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier;
  declare @PACKAGEID uniqueidentifier;
  declare @CHANNELCODE tinyint;
  declare @SEGMENTID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @RECORDTYPEID uniqueidentifier;
  declare @SOURCECODEPARTDEFINITIONID uniqueidentifier;
  declare @SEQUENCE tinyint;
  declare @MAXSEQUENCE tinyint;

  set @SOURCECODE = '';

  select
    @SPONSORSHIPMAILINGTEMPLATEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID],
    @PACKAGEID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID],
    @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
    @SEGMENTID = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SEGMENTID],
    @SOURCECODEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID]
  from 
    dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
  inner join 
    dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[PACKAGEID]
  inner join 
    dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
  where 
    [MKTSPONSORSHIPMAILINGTEMPLATERULE].[ID] = @SPONSORSHIPMAILINGTEMPLATERULEID;

  -- get the list code from the QueryViewCatalogID passed in...

  set @LISTCODE = isnull((select [CODE] from dbo.[MKTLIST] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[LISTID] = [MKTLIST].[ID] where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID),
                         (select [CODE] from dbo.[MKTRECORDSOURCE] where [ID] = @QUERYVIEWCATALOGID));

  -- get the list code from the QueryViewCatalogID passed in...

  set @LISTPARTDEFINITIONVALUESID = isnull((select [PARTDEFINITIONVALUESID] from dbo.[MKTLIST] inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[LISTID] = [MKTLIST].[ID] where [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID),
                         (select [PARTDEFINITIONVALUESID] from dbo.[MKTRECORDSOURCE] where [ID] = @QUERYVIEWCATALOGID and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1));

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

  declare PARTCURSOR cursor local fast_forward for

  select 
    [MKTSOURCECODEITEM].[ID], 
    [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], 
    [MKTSOURCECODEITEM].[LENGTH],
    [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID],
    [MKTSOURCECODEITEM].[SEQUENCE]
  from dbo.[MKTSOURCECODEITEM]
  inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
  where [SOURCECODEID] = @SOURCECODEID
  and (@SOURCECODEITEMID is null or [MKTSOURCECODEITEM].[ID] = @SOURCECODEITEMID)
  order by [SEQUENCE];

  open PARTCURSOR;
  fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @LENGTH, @SOURCECODEPARTDEFINITIONID, @SEQUENCE;

  while (@@FETCH_STATUS = 0)
  begin

    declare @PARTDEFINITIONVALUESID uniqueidentifier;
    set @DELIM = '';

    /* Get the part definition values ID for itemtype if not already set */
    set @PARTDEFINITIONVALUESID =
      case
        when @TYPECODE = 0 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [ID] = @SPONSORSHIPMAILINGTEMPLATEID)
        when @TYPECODE = 1 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [ID] = @SPONSORSHIPMAILINGTEMPLATERULEID)
        when @TYPECODE = 2 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
        when @TYPECODE = 3 then (select [CHANNELPARTDEFINITIONVALUESID] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
        when @TYPECODE = 5 then (select [PARTDEFINITIONVALUESID] from dbo.[MKTSOURCECODEPART] where [SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID and [SOURCECODEITEMID] = @ITEMID)
        when @TYPECODE = 6 then (select @LISTPARTDEFINITIONVALUESID)
        when @TYPECODE = 7 then (select [TESTPARTDEFINITIONVALUESID] from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [ID] = @SPONSORSHIPMAILINGTEMPLATERULEID)
      end

    /* Set true if value is a valid value for the given source code. */
    declare @VALIDVALUE bit;
    set @VALIDVALUE = 
      case when exists(select 1 from dbo.[MKTSOURCECODEVALIDPARTVALUES]
                       inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID]
                       where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID and [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] = @PARTDEFINITIONVALUESID) then 1 else 0 end;

    set @SOURCECODE = @SOURCECODE + 
      (select right(replicate('_', @LENGTH) +
        (case 
          -- 0 = mailing

          when @TYPECODE = 0 
            then (select isnull([CODE], '') from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] where [ID] = @SPONSORSHIPMAILINGTEMPLATEID) 
          -- 1 = segment 

          when (@TYPECODE = 1 and @VALIDVALUE = 1)
            then (select isnull([CODE], '') from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [ID] = @SPONSORSHIPMAILINGTEMPLATERULEID) 
          -- 2 = package, inactive

          when (@TYPECODE = 2) 
            then (select isnull([CODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
          -- 3 = channel

          when @TYPECODE = 3 
            then (select isnull([CHANNELSOURCECODE], '') from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
          -- 5 = user defined

          when @TYPECODE = 5
            then (select isnull([CODE], '') from dbo.[MKTSOURCECODEPART] where [SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID and [SOURCECODEITEMID] = @ITEMID)
          -- 6 = list

          when (@TYPECODE = 6 and @VALIDVALUE = 1)
            then '**********' + @LISTCODE
          -- 7 = test segment

          when @TYPECODE = 7
            then (select isnull([TESTSEGMENTCODE], '') from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] where [ID] = @SPONSORSHIPMAILINGTEMPLATERULEID)
        else
          replicate('_', @LENGTH)
        end), @LENGTH)) + 
        (case when @SOURCECODEITEMID is null then isnull(@DELIM, '') else '' end);

        select 
          @DELIM = isnull([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM], '')
        from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
        where [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = @PARTDEFINITIONVALUESID;

        /* Don't add the delimiter on the end of the source code. */
        if @SEQUENCE < @MAXSEQUENCE
          set @SOURCECODE = @SOURCECODE + @DELIM;

    fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @LENGTH, @SOURCECODEPARTDEFINITIONID, @SEQUENCE;
  end

  close PARTCURSOR;
  deallocate PARTCURSOR;

  return @SOURCECODE;  
end