USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_MINIMAL_MULTIPLE

The load procedure used by the edit dataform template "Marketing Acknowledgement Template Multiple Rule Minimal Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(max)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ACKNOWLEDGEMENTMAILINGTEMPLATEID	uniqueidentifier	INOUT	Marketing Acknowledgement Template
@ASKLADDER	xml	INOUT	Ask ladder
@ASKLADDERLOADED	bit	INOUT	Ask ladder loaded
@ASKLADDERLOCKED	bit	INOUT	Ask ladder locked
@PACKAGEID	uniqueidentifier	INOUT	Package
@PACKAGECODE	nvarchar(10)	INOUT	Package code
@PACKAGELOADED	bit	INOUT	Package loaded
@RESPONSERATE	decimal(5, 2)	INOUT	Response rate
@RESPONSERATELOADED	bit	INOUT	Response rate loaded
@GIFTAMOUNT	money	INOUT	Gift amount
@GIFTAMOUNTLOADED	bit	INOUT	Gift amount loaded
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@MAXDAYSLOADED	bit	INOUT	Max days loaded
@MAXDAYSCHECKED	bit	INOUT	Process transactions within
@MAXDAYS	int	INOUT	Maximum days
@MINQUANTITYLOADED	bit	INOUT	Min quantity loaded
@MINQUANTITYCHECKED	bit	INOUT	Process when the number of transactions reaches
@MINQUANTITY	int	INOUT	Minimum quantity
@ACKNOWLEDGEMENTMAILINGTEMPLATESITEID	uniqueidentifier	INOUT	Marketing Acknowledgement Template site ID
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE_MINIMAL_MULTIPLE]
( 
  @ID nvarchar(max),
  @DATALOADED bit = 0 output,
  @ACKNOWLEDGEMENTMAILINGTEMPLATEID uniqueidentifier = null output,
  @ASKLADDER xml = null output,
  @ASKLADDERLOADED bit = null output,
  @ASKLADDERLOCKED bit = null output,
  @PACKAGEID uniqueidentifier = null output,
  @PACKAGECODE nvarchar(10) = null output,
  @PACKAGELOADED bit = null output,
  @RESPONSERATE decimal(5,2) = null output,
  @RESPONSERATELOADED bit = null output,
  @GIFTAMOUNT money = null output,
  @GIFTAMOUNTLOADED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @MAXDAYSLOADED bit = null output,
  @MAXDAYSCHECKED bit = null output,
  @MAXDAYS int = null output,
  @MINQUANTITYLOADED bit = null output,
  @MINQUANTITYCHECKED bit = null output,
  @MINQUANTITY int = null output,
  @ACKNOWLEDGEMENTMAILINGTEMPLATESITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  declare @RULESTABLE table([ID] uniqueidentifier primary key);
  declare @START int;
  declare @POS int;

  /* Parse out the multiple rule IDs */
  set @START = 0;
  select @POS = charindex(',', @ID, @START);

  while (@POS <> 0)
  begin
    insert into @RULESTABLE
      select substring(@ID, @START, @POS - @START);

    set @START = @POS + 1;
    select @POS = charindex(',', @ID, @START);
  end;

  if len(@ID) > 0
    insert into @RULESTABLE
      select substring(@ID, @START, 37);


  /* Acknowledgement Mailing Template information */
  select top 1
    @ACKNOWLEDGEMENTMAILINGTEMPLATEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID],
    @SOURCECODEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID],
    @ACKNOWLEDGEMENTMAILINGTEMPLATESITEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SITEID]
  from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
  inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID];

  /* Common record source and common ask ladder */
  if (select count(distinct([MKTSEGMENT].[QUERYVIEWCATALOGID])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID] inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID]) = 1
    begin
      if (select count(distinct(isnull([MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ASKLADDERID], '00000000-0000-0000-0000-000000000000'))) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
        select top 1
          @ASKLADDER = (select 
                          [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                          [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
                          (select top 1 [T1].[ASKLADDERID] from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] as [T1] inner join @RULESTABLE as [T2] on [T2].[ID] = [T1].[ID]) as [ASKLADDERID]
                        from dbo.[MKTRECORDSOURCE]
                        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                        where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID] and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
                        for xml raw('ITEM'), type, elements, root('ASKLADDER'), BINARY BASE64),
          @ASKLADDERLOADED = 1,
          @ASKLADDERLOCKED = 0
        from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID];
      else
        select top 1
          @ASKLADDER = (select 
                          [QUERYVIEWCATALOG].[ID] as [RECORDSOURCEID],
                          [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME]
                        from dbo.[MKTRECORDSOURCE]
                        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [MKTRECORDSOURCE].[ID]
                        where [QUERYVIEWCATALOG].[ID] = [MKTSEGMENT].[QUERYVIEWCATALOGID] and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
                        for xml raw('ITEM'), type, elements, root('ASKLADDER'), BINARY BASE64),
          @ASKLADDERLOADED = 0,
          @ASKLADDERLOCKED = 0
        from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
        inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[SEGMENTID];
    end
  else
    begin
      set @ASKLADDERLOADED = 0;
      set @ASKLADDERLOCKED = 1;
    end

  /* Common package */
  if (select count(distinct([PACKAGEID])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
    select top 1
      @PACKAGEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID],
      @PACKAGECODE = [MKTPACKAGE].[CODE],
      @PACKAGELOADED = 1
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]
    inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[PACKAGEID];
  else
    set @PACKAGELOADED = 0;

  /* Common response rate */
  if (select count(distinct([RESPONSERATE])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
    select top 1
      @RESPONSERATE = [RESPONSERATE],
      @RESPONSERATELOADED = 1
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID];
  else
    begin
      set @RESPONSERATE = 5;
      set @RESPONSERATELOADED = 0;
    end

  /* Common gift amount */
  if (select count(distinct([GIFTAMOUNT])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
    select top 1
      @GIFTAMOUNT = [GIFTAMOUNT],
      @GIFTAMOUNTLOADED = 1
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID];
  else
    set @GIFTAMOUNTLOADED = 0;

  /* Common max days */
  if (select count(distinct([MAXDAYSCHECKED])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1 and
     (select count(distinct([MAXDAYS])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
    select top 1
      @MAXDAYS = [MAXDAYS],
      @MAXDAYSCHECKED = [MAXDAYSCHECKED],
      @MAXDAYSLOADED = 1
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID];
  else
    begin
      set @MAXDAYS = 1;
      set @MAXDAYSCHECKED = 0;
      set @MAXDAYSLOADED = 0;
    end


  /* Common minquantity */
  if (select count(distinct([MINQUANTITYCHECKED])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1 and
     (select count(distinct([MINQUANTITY])) from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE] inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID]) = 1
    select top 1
      @MINQUANTITY = [MINQUANTITY],
      @MINQUANTITYCHECKED = [MINQUANTITYCHECKED],
      @MINQUANTITYLOADED = 1
    from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
    inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID];
  else
    begin
      set @MINQUANTITY = 1;
      set @MINQUANTITYCHECKED = 0;
      set @MINQUANTITYLOADED = 0;
    end

  select @TSLONG = max([TSLONG])
  from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
  inner join @RULESTABLE as [S] on [S].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ID];

  -- TODO:  this should not always return 1
  set @DATALOADED = 1;

  return 0;