USP_MKTSEGMENT_UPDATECODE

CREATE procedure dbo.[USP_MKTSEGMENT_UPDATECODE]
(
  @SEGMENTID uniqueidentifier,
  @CODE nvarchar(10),
  @CODEVALUEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @FORCEUPDATE bit = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @OLDCODE nvarchar(10);
  declare @OLDCODEVALUEID uniqueidentifier;
  declare @SEGMENTTYPECODE tinyint;
  declare @CURRENTDATE datetime;
  declare @UPDATECODE bit;

  if @CHANGEAGENTID is null  
    exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

  select
    @OLDCODE = isnull([CODE], ''),
    @OLDCODEVALUEID = [PARTDEFINITIONVALUESID],
    @CODE = isnull(@CODE, ''),
    @SEGMENTTYPECODE = [SEGMENTTYPECODE],
    @CURRENTDATE = getdate()
  from dbo.[MKTSEGMENT]
  where [ID] = @SEGMENTID;

  set @UPDATECODE = case when @FORCEUPDATE = 1 or (@CODEVALUEID is not null and (@OLDCODEVALUEID is null or not (@OLDCODE = @CODE and @OLDCODEVALUEID = @CODEVALUEID))) then 1 else 0 end;

  if @UPDATECODE = 1
    set @UPDATECODE = dbo.[UFN_MKTSEGMENT_APPUSER_GRANTED_EDITFORM](@SEGMENTID, @CURRENTAPPUSERID);

  /* Update the segment code if the old code value ID is nothing and the segment code value ID is supplied */
  if @UPDATECODE = 1
    begin
      /* Update the base segment code */
      update dbo.[MKTSEGMENT] set
        [CODE] = @CODE,
        [PARTDEFINITIONVALUESID] = @CODEVALUEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @SEGMENTID;

      /* Update all the non-activated mailing segments that use the same base segment */
      update dbo.[MKTSEGMENTATIONSEGMENT] set
        [CODE] = @CODE,
        [PARTDEFINITIONVALUESID] = @CODEVALUEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [SS].[SEGMENTATIONID]
      inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
      inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
      inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
      where [SS].[SEGMENTID] = @SEGMENTID
      and [MKTSEGMENTATION].[ACTIVE] = 0  --Not activated

      and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part


      /* Update all the non-activated mailing test segments that use the same base segment */
      update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
        [CODE] = @CODE,
        [PARTDEFINITIONVALUESID] = @CODEVALUEID,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [STS]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [STS].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
      inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
      inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSEGMENTATION].[SOURCECODEID]
      inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
      inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = @SEGMENTID
      and [MKTSEGMENTATION].[ACTIVE] = 0  --Not activated

      and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part


      if @SEGMENTTYPECODE = 3
        /* Update all the acknowledgment rules that use the same base segment */
        update dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] set
          [CODE] = @CODE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] as [RULE]
       inner join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = [RULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID]
        inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID]
        inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        where [RULE].[SEGMENTID] = @SEGMENTID
        and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part


      else if @SEGMENTTYPECODE = 4
        /* update all the membership template rules that use the same base segment */
        update dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] set
          [CODE] = @CODE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from dbo.[MKTMEMBERSHIPMAILINGTEMPLATERULE] as [RULE]
        inner join dbo.[MKTMEMBERSHIPMAILINGTEMPLATE] on [MKTMEMBERSHIPMAILINGTEMPLATE].[ID] = [RULE].[MEMBERSHIPMAILINGTEMPLATEID]
        inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTMEMBERSHIPMAILINGTEMPLATE].[SOURCECODEID]
        inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        where [RULE].[SEGMENTID] = @SEGMENTID
        and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part


      else if @SEGMENTTYPECODE = 5
        /* update all the sponsorship template rules that use the same base segment */
        update dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] set
          [CODE] = @CODE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE] as [RULE]
        inner join dbo.[MKTSPONSORSHIPMAILINGTEMPLATE] on [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = [RULE].[SPONSORSHIPMAILINGTEMPLATEID]
        inner join dbo.[MKTSOURCECODE] on [MKTSOURCECODE].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID]
        inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]
        inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
        where [RULE].[SEGMENTID] = @SEGMENTID
        and [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE] = 1;  --Segment sourcecode part

    end

  return 0;