USP_MKTSOURCECODEPART_SAVEUSERDEFINEDPARTSFROMCODE

CREATE procedure dbo.[USP_MKTSOURCECODEPART_SAVEUSERDEFINEDPARTSFROMCODE]
(
  @SOURCECODE nvarchar(50),
  @SEGMENTATIONSEGMENTID uniqueidentifier,
  @SEGMENTATIONTESTSEGMENTID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTDATE datetime = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SOURCECODEID uniqueidentifier;
  declare @SOURCECODEITEMID uniqueidentifier;
  declare @INHERITEDCODE nvarchar(10);
  declare @INHERITEDCODEVALUEID uniqueidentifier;
  declare @SOURCECODEPARTDEFINITIONID uniqueidentifier;
  declare @SOURCECODEPARTID uniqueidentifier;
  declare @POSITION int;
  declare @LENGTH int;
  declare @CODE nvarchar(10);
  declare @CODEVALUEID uniqueidentifier;

  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATION].[ID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID] and @SEGMENTATIONTESTSEGMENTID is not null
  where (@SEGMENTATIONSEGMENTID is null or [MKTSEGMENTATIONSEGMENT].[ID] = @SEGMENTATIONSEGMENTID)
  and (@SEGMENTATIONTESTSEGMENTID is null or [MKTSEGMENTATIONTESTSEGMENT].[ID] = @SEGMENTATIONTESTSEGMENTID);


  declare INHERITEDCURSOR cursor local fast_forward for
    select
      [MKTSOURCECODEITEM].[ID],
      [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID],
      (select isnull(sum([T].[LENGTH] + [T].[DELIMLENGTH]), 0) + 1
       from (
         select distinct [SCI].[SEQUENCE], [SCI].[LENGTH], len([SCPDV].[DELIM]) as [DELIMLENGTH]
         from dbo.[MKTSOURCECODEITEM] as [SCI]
         inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [SCPDV] on [SCPDV].[MKTSOURCECODEPARTDEFINITIONID] = [SCI].[MKTSOURCECODEPARTDEFINITIONID]
         inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] as [SCVPV] on [SCVPV].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [SCPDV].[ID] and [SCVPV].[MKTSOURCECODEITEMID] = [SCI].[ID]
         where [SCI].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID]
         and [SCI].[SEQUENCE] < [MKTSOURCECODEITEM].[SEQUENCE]
       ) as [T]) as [POSITION],
      [MKTSOURCECODEITEM].[LENGTH],
      [MKTSOURCECODEPART].[CODE],
      [MKTSOURCECODEPART].[PARTDEFINITIONVALUESID]
    from dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    left join dbo.[MKTSOURCECODEPART] on [MKTSOURCECODEPART].[SOURCECODEITEMID] = [MKTSOURCECODEITEM].[ID]
    where [MKTSOURCECODEPART].[SEGMENTATIONID] = @SEGMENTATIONID
    and [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
    and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 5
    and [MKTSOURCECODEPART].[SEGMENTATIONSEGMENTID] is null
    and [MKTSOURCECODEPART].[SEGMENTATIONTESTSEGMENTID] is null
    order by [MKTSOURCECODEITEM].[SEQUENCE];

  open INHERITEDCURSOR;
  fetch next from INHERITEDCURSOR into @SOURCECODEITEMID, @SOURCECODEPARTDEFINITIONID, @POSITION, @LENGTH, @INHERITEDCODE, @INHERITEDCODEVALUEID;

  while (@@fetch_status = 0)
  begin
    set @CODE = substring(@SOURCECODE, @POSITION, @LENGTH);

    if @CODE <> @INHERITEDCODE
      begin
        --Try to figure out the value ID from the user defined code...

        set @CODEVALUEID = dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @CODE, 5, @SOURCECODEPARTDEFINITIONID);

        if @CODEVALUEID is null
          raiserror('A valid source code part format/value could not be found that matches the user defined code.', 13, 1);

        select
          @SOURCECODEPARTID = [ID]
        from dbo.[MKTSOURCECODEPART]
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [SOURCECODEITEMID] = @SOURCECODEITEMID
        and ((@SEGMENTATIONTESTSEGMENTID is null and [SEGMENTATIONSEGMENTID] = @SEGMENTATIONSEGMENTID and [SEGMENTATIONTESTSEGMENTID] is null) or
             (@SEGMENTATIONTESTSEGMENTID is not null and [SEGMENTATIONSEGMENTID] is null and [SEGMENTATIONTESTSEGMENTID] = @SEGMENTATIONTESTSEGMENTID));

        --Save the user defined source code part values...

        if @SOURCECODEPARTID is not null
          update dbo.[MKTSOURCECODEPART] set
            [PARTDEFINITIONVALUESID] = @CODEVALUEID,
            [CODE] = @CODE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @SOURCECODEPARTID;
        else
          insert into dbo.[MKTSOURCECODEPART] (
            [ID],
            [SEGMENTATIONID],
            [SOURCECODEITEMID],
            [CODE],
            [PARTDEFINITIONVALUESID],
            [SEGMENTATIONSEGMENTID],
            [SEGMENTATIONTESTSEGMENTID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            newid(),
            @SEGMENTATIONID,
            @SOURCECODEITEMID,
            @CODE,
            @CODEVALUEID,
            (case when @SEGMENTATIONTESTSEGMENTID is null then @SEGMENTATIONSEGMENTID else null end),
            @SEGMENTATIONTESTSEGMENTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
      end

    fetch next from INHERITEDCURSOR into @SOURCECODEITEMID, @SOURCECODEPARTDEFINITIONID, @POSITION, @LENGTH, @INHERITEDCODE, @INHERITEDCODEVALUEID;
  end

  close INHERITEDCURSOR;
  deallocate INHERITEDCURSOR;

  return 0;