USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_MULTIPLE_2

The load procedure used by the edit dataform template "Marketing Effort Multiple Segment Edit Form 2"

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.
@SEGMENTATIONID	uniqueidentifier	INOUT	Marketing effort
@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
@SAMPLESIZE	int	INOUT	Sample size
@SAMPLESIZETYPECODE	tinyint	INOUT	Sample size type
@SAMPLESIZEMETHODCODE	tinyint	INOUT	Sample size method
@SAMPLESIZEEXCLUDEREMAINDER	bit	INOUT	Exclude remaining records from the marketing effort
@SAMPLESIZELOADED	bit	INOUT	Sample size loaded
@SAMPLESIZELOCKED	bit	INOUT	Sample size locked
@SOURCECODEID	uniqueidentifier	INOUT	Source code ID
@SEGMENTATIONSITEID	uniqueidentifier	INOUT	Marketing effort 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.
@MAILINGTYPECODE	tinyint	INOUT	Marketing effort type code
@ISTESTMAILING	bit	INOUT	Is test marketing effort
@PACKAGECHANNELCODE	tinyint	INOUT	Package channel code
@USEADDRESSPROCESSING	bit	INOUT	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing option ID

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONSEGMENT_MULTIPLE_2
( 
  @ID nvarchar(max),
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID 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,
  @SAMPLESIZE int = null output,
  @SAMPLESIZETYPECODE tinyint = null output,
  @SAMPLESIZEMETHODCODE tinyint = null output,
  @SAMPLESIZEEXCLUDEREMAINDER bit = null output,
  @SAMPLESIZELOADED bit = null output,
  @SAMPLESIZELOCKED bit = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @SEGMENTATIONSITEID uniqueidentifier = null output,
  @TSLONG bigint = 0 output,
  @MAILINGTYPECODE tinyint = null output,
  @ISTESTMAILING bit = null output,
  @PACKAGECHANNELCODE tinyint = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output
)
as
  set nocount on;

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

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


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

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

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

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


  /* Mailing information */
  select top 1
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @SEGMENTATIONSITEID = [MKTSEGMENTATION].[SITEID],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @ISTESTMAILING = (case when [MKTSEGMENTATION].[PARENTSEGMENTATIONID] is not null then 1 else 0 end),
    @USEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
  inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID];

  /* Check if the mailing is currently being activated */
  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

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

  /* Common package */
  if @MAILINGTYPECODE <> 1 and (select count(distinct([PACKAGEID])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
    select top 1
      @PACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @PACKAGECODE = [MKTPACKAGE].[CODE],
      @PACKAGECHANNELCODE = [MKTPACKAGE].[CHANNELCODE],
      @PACKAGELOADED = 1
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID];
  else
    set @PACKAGELOADED = 0;

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

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

  /* Common sample size */
  if (@MAILINGTYPECODE = 1 and @ISTESTMAILING = 0) or exists(select 1 from @SEGMENTSTABLE as [S] where dbo.[UFN_MKTSEGMENTATIONSEGMENT_ISVENDORMANAGEDLIST]([S].[ID]) = 1)
    begin
      set @SAMPLESIZE = 100;
      set @SAMPLESIZETYPECODE = 0;
      set @SAMPLESIZEMETHODCODE = 0;
      set @SAMPLESIZEEXCLUDEREMAINDER = 1;
      set @SAMPLESIZELOADED = 0;
      set @SAMPLESIZELOCKED = 1;
    end
  else
    begin
      if (select count(distinct([SAMPLESIZETYPECODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZEMETHODCODE])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1 and
         (select count(distinct([SAMPLESIZEEXCLUDEREMAINDER])) from dbo.[MKTSEGMENTATIONSEGMENT] inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID]) = 1
        select top 1
          @SAMPLESIZE = [SAMPLESIZE],
          @SAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
          @SAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
          @SAMPLESIZEEXCLUDEREMAINDER = [SAMPLESIZEEXCLUDEREMAINDER],
          @SAMPLESIZELOADED = 1
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTSTABLE as [S] on [S].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];
      else
        begin
          set @SAMPLESIZE = 100;
          set @SAMPLESIZETYPECODE = 0;
          set @SAMPLESIZEMETHODCODE = 0;
          set @SAMPLESIZEEXCLUDEREMAINDER = 1;
          set @SAMPLESIZELOADED = 0;
        end
      set @SAMPLESIZELOCKED = 0;
    end

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

  -- TODO:  this should not always return 1

  set @DATALOADED = 1;

  return 0;