USP_DATAFORMTEMPLATE_EDIT_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_DRAGDROPINSERT

The save procedure used by the edit dataform template "Marketing Acknowledgement Template Rule Drag and Drop Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@RULES	xml	IN	Rules
@RULESEQUENCE	int	IN	Rule Sequence

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_DRAGDROPINSERT]
(
  @ID uniqueidentifier,                            /* Required. The acknowledgement mailing template ID. */
  @CHANGEAGENTID uniqueidentifier = null,          /* Optional. The user ID that is making the change. */
  @RULES xml,                                      /* Optional. Rule IDs to insert at the specified rule sequence. */
  @RULESEQUENCE int                                /* Required. The sequence to insert the rules at. */
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SELECTEDTABLE table ([RULEID] uniqueidentifier, [CURRENTSEQ] int);
  declare @RULESTABLE table ([RULEID] uniqueidentifier, [CURRENTSEQ] int, [NEWSEQ] int IDENTITY (1,1));

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

    set @CURRENTDATE = getdate();

    if @RULES is not null
      begin
        --Put the rule IDs into a temp table...

        insert into @SELECTEDTABLE ([RULEID])
          select T.c.value('(ID)[1]','uniqueidentifier')
          from @RULES.nodes('/RULES/ITEM') T(c);

        --Update the @SELECTEDTABLE with the current sequences...

        update @SELECTEDTABLE set
          [CURRENTSEQ] = (select [SEQUENCE] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] where [ID] = [S].[RULEID])
        from @SELECTEDTABLE as [S];

        --Insert into the @RULESTABLE any rules with a sequence less than or equal to the insert point (RULESEQUENCE)...

        insert into @RULESTABLE ([RULEID], [CURRENTSEQ]) 
          select [ID], [SEQUENCE]
          from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
          where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
          and [SEQUENCE] < @RULESEQUENCE
          and [SEQUENCE] not in (select [CURRENTSEQ] from @SELECTEDTABLE)
          order by [SEQUENCE];

        --Copy over the selected rules...

        insert into @RULESTABLE ([RULEID], [CURRENTSEQ]) 
          select [RULEID], [CURRENTSEQ]
          from @SELECTEDTABLE
          order by [CURRENTSEQ];

        -- insert the remaining rules...

        insert into @RULESTABLE ([RULEID], [CURRENTSEQ]) 
          select [ID], [SEQUENCE]
          from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
          where [ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
          and [SEQUENCE] >= @RULESEQUENCE
          and    [SEQUENCE] not in (select [CURRENTSEQ] from @SELECTEDTABLE)
          order by [SEQUENCE];

        --Update the acknowledgement mailing template rule table...

        update dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] set
          [SEQUENCE] = [R].[NEWSEQ],
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] as [TEMPLATERULES]
        inner join @RULESTABLE as [R] on [R].[RULEID] = [TEMPLATERULES].[ID];
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;